Creating Functions And Triggers In PostgreSQL With SQLAlchemy

I use SQLAlchemy Engine to create some functions and triggers, but I did not want to mix Python and SQL, so I have created a separate file for my SQL statements, I read the content

Solution 1:

I believe you may have mixed the BEGIN SQL command (a Postgresql extension) and a PL/pgSQL block. The SQL command DO executes an anonymous code block, as if it were an anonymous function with no parameters and returning void. In other words in

DO $$
BEGIN
    ...
END;
$$;

the BEGIN / END; pair denotes the code block, not a transaction. It is worth noting that starting from Postgresql version 11 it is possible to manage transactions in a DO block, given that it is not executed in a transaction block, but the commands for that are COMMIT and ROLLBACK, not the keyword END.

The problem then is that your changes are not committed, though your commands clearly are executed – as proven by the error, if not running with suitable privileges. This issue is caused by how SQLAlchemy autocommit feature works. In short, it inspects your statement / command and tries to determine if it is a data changing operation, or a DDL statement. This works for the basic operations such as INSERT, DELETE, UPDATE, and the like, but is not perfect. In fact it is impossible for it to always correctly determine if a statement changes data; for example SELECT my_mutating_procedure() is such a statement. So it needs some help, if doing more complex operations. One way is to instruct the autocommit machinery that it should commit by wrapping the SQL string in a text() construct and using execution_options():

engine.execute(text("SELECT my_mutating_procedure()").
               execution_options(autocommit=True))

It is also possible to explicitly instruct SQLAlchemy that the command is a literal DDL statement using the DDL construct:

from sqlalchemy.schema import DDL

def execute_sql_file(engine, path):
    try:
        with open(path) as file:
            stmt = file.read()

        # Not strictly DDL, but a series of DO commands that execute DDL
        ddl_stmt = DDL(stmt)
        engine.execute(ddl_stmt)

    except ProgrammingError:
        raise MyCustomError

    except FileNotFoundError:
        raise MyCustomError

As to why it works with pgAdmin, it probably by default commits, if no error was raised.