CODEWITHSUNDEEP
sqlpostgresql
user187676
user187676

Reputation:

Scope of transactions and prepared statements

I wonder how transactions and prepared statements are scoped. With scoped, I mean if they are bound to a specific connection or environment in wich they were created.

More specifically:

Limited to one client of course.

Upvotes: 1

Views: 676

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324265

Can a prepared statement be reused on a connection other the one it was created on?

No, it's session (connection) specific.

The terms "session" and "connection" are currently interchangeable in PostgreSQL, as there's no way to save a session and restore it on a later connection.

Can a prepared statement be used inside a transaction if it was created outside of that transaction and vice versa?

Yes. Prepared statements are session-level, not transaction-level. There's no PREPARE LOCAL for a transaction-level prepared statement, like SET LOCAL for transaction-local variables.

You can use the DISCARD command to forget prepared statements, or DEALLOCATE to forget one particular prepared statement. This is used heavily in transaction/connection poolers like PgBouncer.

(Of course, all involved transactions must be on the same session, as prepared statements are session-specific).

Is a transaction bound to the connection it was created on?

Yes. Transactions are properties of a session. You can't suspend a transaction in one session and restore it in another. It'd be quite handy, but is extremely impractical given PostgreSQL's process-based architecture.

A tool like PgBouncer or PgPool-II lets you abstract connections by a layer of indirection that offers some more flexibility.

Upvotes: 3

Related Questions