Operations.execute creates stored procedure but strips inline comments

[dixonwhitmire]

Hi Folks,

I am in the process of reviewing Alembic to see if it's a good fit for a forthcoming project my company is working on. From a table/schema management standpoint things are working as expected. However, I am finding an issue with executing DDL for stored procedures.

I do understand that managing stored procedures is not Alembic's primary focus and have taken a look at the "Repeatable Migration" pattern in the cookbook. For my use-case I simply want to use op.execute() to manage the upgrade and downgrade for our application's stored procedures.

Basically, I'm defining a literal """ string variable, and then passing that off to op.execute(). I have verified that the procedure is created, but what's odd is that the comments in the procedure are stripped out.

Here's an example where I've redacted actual table names and what not

def upgrade() -> None:
    ddl = """
CREATE OR REPLACE PROCEDURE my_schema.sp_upsert_the_thing(p_metadata  JSONB)
-- comment comment comment
-- comment comment comment
--	
--	Parameter:
--	-p_metadata: JSON metadata document for the thing.

AS $$
BEGIN
	INSERT INTO my_schema.coffee_table(col2, col3)
	VALUES(p_metadata->>'col2, p_metadata->>'col3')
	ON CONFLICT(col1)
	DO UPDATE
		SET col2 = p_metadata->>'col2',
			col3 = p_metadata->>'col3',
		WHERE col1  = p_metadata->>'col1';
END;$$
LANGUAGE plpgsql;    
"""
   op.execute(ddl)

I've also tried using /* comment */ style comments with no luck.

At this point I was just curious if the comments are being stripped out by alembic or the pyscopg driver for PostgreSQL (I'm using version 3.x). So I thought I would just post here first to see if perhaps my code was a bit off, or if this is a "known" thing, before I go down the rabbit hole and create a stand-alone test case I can iterate on.

So if anyone reads this, I thank you for your time. Any suggestions are welcome even if it's hey please read this :)

Thanks!
Dixon

[CaselIT]

Hi,

As far as I know alembic or sqlalchemy do not strip comments. You can log what is sent to the driver by sqlalchemy by setting the echo=True in the create engine call

[CaselIT]

Also to manage stored procedures you may look into https://github.com/olirice/alembic_utils that IIRC has some utilities for that

[dixonwhitmire]

Thank you for the suggestions! I will take a look in the next few days and will report back.

[dixonwhitmire]

Hello @CaselIT - Thank you again for your suggestions. I was able to confirm via echo=True that the complete DDL including comments is passed along to the database driver. Once I have my prototype working I'll return to debugging this issue.

I appreciate your help.

Have a great day!

[dixonwhitmire]

For anyone who also runs into this, I've found that the comments will carry over if they are placed following the DECLARE block in a plpgsql procedure. In my case, I refactored my initial example from an INSERT with an ON CONFLICT clause which uses an implicit commit to a more procedure approach. The pseudo code is similar to

    ddl = """
CREATE OR REPLACE PROCEDURE my_schema.sp_upsert_the_thing(p_metadata  JSONB)
-- comment comment comment
-- comment comment comment
--	
--	Parameter:
--	-p_metadata: JSON metadata document for the thing.

AS $$
DECLARE
-- some comments
-- more comments
l_col1 my_schema.coffee_table.col1%TYPE; 
BEGIN
	INSERT INTO my_schema.coffee_table(col2, col3)
	VALUES(p_metadata->>'col2, p_metadata->>'col3')
	ON CONFLICT(col1)
	DO UPDATE
		SET col2 = p_metadata->>'col2',
			col3 = p_metadata->>'col3',
		WHERE col1  = p_metadata->>'col1';
END;$$
LANGUAGE plpgsql;    
"""