Creating functions and triggers in PostgreSQL with SQLAlchemy

Question

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 and pass it to engine.execute(). It throws no errors, however the functions are not created in the database, but if I run the same SQL file through pgAdmin, everything works fine.

My SQL file:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'plpython3u') THEN
    CREATE EXTENSION plpython3u;
  END IF;
END;
$$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'my_func') THEN
    CREATE FUNCTION public.my_func() RETURNS TRIGGER LANGUAGE 'plpython3u' NOT LEAKPROOF AS $BODY$
    -- definition
    $BODY$;

    GRANT EXECUTE ON FUNCTION my_func() TO public;
  END IF;
END;
$$;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'my_func2') THEN
    CREATE FUNCTION public.my_func2() RETURNS TRIGGER LANGUAGE 'plpython3u' NOT LEAKPROOF AS $BODY$
    -- definition
    $BODY$;

    GRANT EXECUTE ON FUNCTION my_func2() TO public;
  END IF;
END;
$$;

And I run this as follows:

def execute_sql_file(engine, path):
    try:
        with open(path) as file:
            engine.execute(file.read())
    except ProgrammingError:
        raise MyCustomError
    except FileNotFoundError:
        raise MyCustomError

If I run this without superuser privilege, it throws ProgrammingError, as expected. In my understanding END; commits the transaction, so it this code is really run, the functions should be available for the public, however they are not even created. Any ideas are welcome, thanks!

Hodossy Szabolcs · Accepted Answer · 2019-04-04 06:04:46Z

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.

Wow, huge thanks, this was the problem. This is a very detailed answer!

Collectives™ on Stack Overflow

Creating functions and triggers in PostgreSQL with SQLAlchemy

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related