Transactions and sessions

Every Query Tool tab has a session of its own: one PostgreSQL connection, opened for that tab and closed with it. This page explains how statements commit in it, how to see and change its state, and what happens when the connection goes away.

Each tab has its own connection

A tab’s connection is separate from the Explorer’s and from every other tab’s. A transaction you open, a SET you run or a temporary table you create stays in that tab’s session, and other tabs do not see it. Locks are the exception, as between any two PostgreSQL sessions: rows or a table that the tab’s open transaction has locked make other sessions, other tabs included, wait until it ends. A data tab has a connection of its own too.

The tab’s session shows in three places:

• The status bar gives its state and its process id on the server, such as pid 4002. Hover the pid for This tab's server process: find it by its pid in Server Activity.
• Hovering the tab shows the same, as Session pid 4002 · Idle.
• After a run, the last line of Messages says Transaction:, the state in small letters, and whether auto-commit is on, such as Transaction: idle · auto-commit on.

State	What it means
Connecting…	The tab is opening its connection.
Idle	Connected, with no transaction open.
Active	A statement, an Explain or a save of changed rows is running.
In transaction	A transaction is open and waits for a commit or a rollback.
Failed, roll back to continue	A statement failed inside the open transaction. See When a statement fails inside a transaction.
Disconnected	The tab has no connection. See When the connection closes or is lost.

A tab that is waiting for its server has no session yet; the status bar then shows the server’s own state, such as Not connected (see Tabs that wait for their server).

The process id is the one PostgreSQL shows for the session in pg_stat_activity, where its application_name is pgjumbo/query. Look for it in Activity to see what the tab’s session holds or waits for, or to find it when another session is blocked behind it.

Auto-commit

Auto-commit is on in every new tab: each statement commits on its own as soon as it succeeds, as in psql.

Untick Auto-commit in the toolbar, or choose Query▸Auto-commit, to work in a transaction:

• The first statement you run opens one: pgjumbo sends BEGIN before it when no transaction is open.
• What you run next goes into the same transaction, run after run, until you commit or roll back.
• Statements that PostgreSQL cannot run inside a transaction are sent without BEGIN: VACUUM, CREATE DATABASE, DROP DATABASE, CREATE TABLESPACE, DROP TABLESPACE, ALTER SYSTEM, DISCARD ALL, CLUSTER without a table, CREATE, ALTER and DROP SUBSCRIPTION, COMMIT PREPARED, ROLLBACK PREPARED, REINDEX DATABASE, REINDEX SYSTEM, and any CONCURRENTLY index statement. pgjumbo only leaves out the BEGIN: when a transaction is already open, PostgreSQL still refuses them.
• Your own BEGIN, START TRANSACTION, COMMIT, END, ROLLBACK, ABORT, SAVEPOINT, RELEASE and PREPARE TRANSACTION run as you wrote them; pgjumbo adds nothing around them.

• 1 Auto-commit, unticked.
• 2 Commit sends COMMIT. While a transaction is open it is the filled button, and Run becomes a plain one.
• 3 Rollback sends ROLLBACK.
• 4 The transaction’s state at the end of Messages.
• 5 The same state in the status bar.
• 6 The session’s process id.

Commit and Rollback are also Query▸Commit and Query▸Rollback. They have no keys. The buttons show only while Auto-commit is off; the menu items are always there, greyed while Auto-commit is on. Both are greyed while something runs and while no transaction is open, and Commit is also greyed in a failed transaction. After either one, Messages adds an entry Transaction with COMMIT · 3 ms (or ROLLBACK) under it; if the server refuses, its error shows there instead.

While this transaction is open, the tab cannot switch database.

Ticking Auto-commit again does not end the transaction

You can tick Auto-commit again while a transaction is open (the box is greyed only while something runs or the tab has no session). The transaction stays open, and the Commit and Rollback buttons go away with the tick. Commit or roll back before you tick it, or run COMMIT or ROLLBACK yourself afterwards.

With Auto-commit on, a BEGIN you write yourself also opens a transaction. The status bar then says In transaction, but there are no Commit and Rollback buttons: run COMMIT or ROLLBACK, or untick Auto-commit to get the buttons.

When a statement fails inside a transaction

• 1 Commit is greyed: a failed transaction cannot commit.
• 2 Rollback is the filled button.
• 3 The statement that failed and PostgreSQL’s error.
• 4 Transaction: failed, roll back to continue · auto-commit off.
• 5 The status bar says Failed, roll back to continue.

Once a statement fails, PostgreSQL aborts the whole transaction. Every statement you run after it is refused with current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02) until you roll back. Click Rollback, choose Query▸Rollback, or run ROLLBACK. If you made a savepoint before the failure, ROLLBACK TO SAVEPOINT and its name take you back to it and the transaction goes on. A COMMIT you type in a failed transaction ends it as a rollback; that is PostgreSQL’s rule.

Stopping a query inside a transaction fails it the same way.

Close a tab with a transaction open

pgjumbo does not ask. Closing the tab closes its connection, after cancelling a statement that is still running, and PostgreSQL rolls back whatever the transaction had not committed. The same happens when the tab switches database (which is blocked while Auto-commit is off and a transaction is open, but not for a BEGIN you ran with Auto-commit on), when you disconnect the server, and when you quit pgjumbo.

A tab asks before it closes only when it holds work pgjumbo would lose: text not saved to its file, or changed rows not saved. See Files, drafts and history and Edit data.

Session settings

Change the search path, the role, the timeouts and work_mem of the tab’s session without typing SET. Click the Session settings button (the sliders icon) in the toolbar, choose Session Settings… in the toolbar’s More menu, or choose Query▸Session Settings…. It opens while the tab’s session is open and nothing runs.

The dialog reads the session’s current values first (Reading the session's settings…) and shows them as SHOW writes them; if they cannot be read, it says why instead. Its subtitle is the tab’s name, and its first line says For this tab's session only, as SET would change them; the server checks each value.

• 1 Role: the role statements run as. The list has the login role, marked (the login role), and the roles it is a member of, directly or through other roles, which it may SET ROLE to; for a superuser that is every role. Roles whose names start with pg_ are not listed. Choosing the login role again goes back to it.
• 2 Search path: the schemas names are looked up in, in order, such as sales, public.
• 3 Statement timeout: how long a statement may run before the server cancels it. 0 waits for ever; 30s and 5min are examples.
• 4 Lock timeout: how long a statement waits for a lock before it gives up. 0 waits for ever.
• 5 work_mem: the memory a sort or a hash may use before it spills to disk, such as 64MB.
• 6 Apply sets the fields you changed and closes the dialog. It is greyed until you change one, and says Applying… while it works. Cancel, the × and Esc close the dialog and change nothing.

pgjumbo sets each changed value as SET would, and the server checks it. It sets them one at a time, in the order search path, statement timeout, lock timeout, work_mem, role, and stops at the first value the server refuses: the server’s message shows at the bottom of the dialog, the dialog stays open, and the values set before it stay set. Only this tab’s session changes; nothing on the server does.

While a transaction is open, the dialog also says Inside the open transaction, a change lasts only if it commits. That is how SET behaves too.

The settings last as long as the session. A new session, after you switch database or click Reconnect, starts with the server’s defaults again. Suggestions and Quick Info keep using your role’s default search path; see Writing SQL.

When the connection closes or is lost

pgjumbo never opens a new connection for a tab behind your back, and never runs anything again by itself. A statement that broke off halfway, or a transaction that went with the connection, is for you to judge before you run it again.

• 1 The tab says why it has no connection.
• 2 Reconnect opens a new connection for the tab.
• 3 The status bar says Disconnected.

Banner	When it shows
Disconnected from the server.	The server is no longer connected in the Explorer: you disconnected it, or its connection failed. Every tab on that server that had a session shows it.
Could not connect: and the reason	The tab could not open its connection, or Reconnect failed (for example because the server is not connected).
This tab's session is closed.	You ran something, and pgjumbo no longer had the tab’s session.
This tab's server was deleted. Its SQL is still here to copy.	A tab brought back from its draft after its server was deleted. It has no Reconnect.

The editor keeps its text and Messages its log, but the rows of the last result went with the session. Reconnect opens a new session with a new process id, Auto-commit on and the server’s default settings; what was not committed has been rolled back by the server. When the server itself is not connected, connect it first (see Manage your servers), then click Reconnect.

If the tab’s connection breaks (the server restarts, the network drops) while the server still shows as connected, the statement running then, or the next one you run, fails with the connection’s own message, such as server closed the connection unexpectedly, and the tab does not reconnect. Disconnect the server and connect it again, then click Reconnect in the tab.

Tabs that wait for their server

• 1 A tab that has never had a session says which server it needs.
• 2 Connect connects that server, asking for its password when none is saved.
• 3 The status bar shows the server’s state, here Not connected.

Tabs brought back when pgjumbo starts wait like this (see Files, drafts and history), and so does a tab opened on a server that is not connected: a file opened from the start page, a snippet from the Library, or New query while the tab in front waits. As soon as the server connects, by this button or from the Explorer, each waiting tab opens its first session by itself. That is the only time a tab connects without you asking it to, because it never had a session to lose.

Read-only connections

A server whose connection settings tick Read-only sessions (under Certificates and session in Connection settings) starts every session with default_transaction_read_only = on, each Query Tool tab’s too. Choosing Production in the dialog ticks it for you (you can untick it again), and moving a server from Production to another environment unticks it.

In such a tab, a statement that writes fails with PostgreSQL’s cannot execute … in a read-only transaction (SQLSTATE 25006). The Query Tool shows no mark of its own for it, and on a Production server the confirmation before destructive statements still comes first.

A default, not a permission

default_transaction_read_only only sets how transactions start. SET default_transaction_read_only = off or BEGIN READ WRITE in the tab writes again. When writes must be impossible, sign in with a role that has no write privileges.