Search code examples
pythonpostgresqlplpython

Is it possible to maintain state between Postgres trigger invocations?

I am implementing a trigger in PostgreSQL using Python that sends an AMQP message via amqpy.

A connection is required to send the message. Currently, the only way I know to do this is, when the trigger is invoked, open a connection, send the message, and close the connection.

I would like to not have to keep opening a new connection for every trigger invocation. Is there a way to initialize the connection once, and then make it available to future trigger invocations?

There would also have to be a way to detect if the connection went down and re-establish it.

Solution

  • You can share data between pl/python functions.

    http://www.postgresql.org/docs/9.2/static/plpython-sharing.html

    It should be possible to open a connection, stash it in "GD" and reuse it. Don't know if AMPQ needs a clean close on its connection though.