38. PostgreSQL: Transactions and session

This chapter describes, for a MIRAJ instance using the PostgreSQL language:

• transaction control (BEGIN, COMMIT, ROLLBACK, SAVEPOINT) and what happens after an error in a transaction block;
• definition statements (CREATE TABLE, DROP TABLE...) in a transaction block;
• MIRAJ isolation levels and concurrency model: serializable isolation without row locks, where a conflict is resolved by retrying the transaction;
• locking reads (SELECT … FOR UPDATE), LOCK TABLE and advisory locks;
• session parameters (SET, SHOW, RESET), the search path (search_path), the time zone and the statement timeout;
• SQL cursors, prepared statements and session reset.

The examples use the sample schema ventes (tables client, article, commande, ligne_commande) created and populated in chapter 32. Getting started: three customers, three items (stocks 500, 200 and 80), three orders. The results shown are those displayed by a PostgreSQL client: statement tag (BEGIN, UPDATE 1...), rows, or error with its SQLSTATE. Two-session examples read from top to bottom, session A on the left, session B on the right.

38.1 Autocommit and explicit transactions#

Autocommit#

Outside a transaction block, each statement forms its own transaction, committed as soon as it succeeds and rolled back entirely if it fails. This is PostgreSQL behavior, and what psql and drivers expect.

A query that contains several statements separated by ; (sent at once by psql -c or by a driver) forms an implicit transaction, as in PostgreSQL: if one of them fails, the writes of the preceding statements are rolled back and the following ones are not executed.

UPDATE article SET stock = stock - 1 WHERE id = 1; SELECT 1/0; UPDATE article SET stock = stock - 1 WHERE id = 2

UPDATE 1
ERROR:  division by zero   (22012)

After that, the stocks of items 1 and 2 are still 500 and 200. A COMMIT written in the query commits what precedes it (UPDATE …; COMMIT; SELECT 1/0 keeps the UPDATE), and a BEGIN there opens an ordinary block, which stays open after the query if it is not closed. A definition statement (CREATE TABLE...) in the query is not undone by the failure of a statement that follows it.

Opening a transaction#

BEGIN [WORK | TRANSACTION] [mode [, mode] …];
START TRANSACTION [mode [, mode] …];

where mode is:

ISOLATION LEVEL {SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED}
READ WRITE | READ ONLY
[NOT] DEFERRABLE

Modes are separated by a comma or by a space. [NOT] DEFERRABLE is accepted with no effect. The isolation level is described in 38.3.

BEGIN;
INSERT INTO commande (client_id) VALUES (3) RETURNING id;
INSERT INTO ligne_commande VALUES (4, 1, 10, 1.20);
UPDATE article SET stock = stock - 10 WHERE id = 1;
COMMIT;

BEGIN
 id
----
  4
(1 ligne)

INSERT 0 1
INSERT 0 1
UPDATE 1
COMMIT

A BEGIN sent while a transaction is already open is accepted with no error or notice, and the current transaction continues.

Committing or rolling back#

COMMIT [WORK | TRANSACTION] [AND [NO] CHAIN];
END [WORK | TRANSACTION];
ROLLBACK [WORK | TRANSACTION] [AND [NO] CHAIN];
ABORT;

• COMMIT (or END) commits all the writes of the transaction: they become visible to other sessions and durable.
• ROLLBACK (or ABORT) cancels them all: each row returns to its value from before the transaction.
• AND CHAIN immediately opens a new transaction, with the same mode (READ ONLY or READ WRITE); AND NO CHAIN is the default.
• COMMIT or ROLLBACK outside any transaction are accepted with no error or notice.

BEGIN;
UPDATE article SET stock = stock - 100 WHERE id = 1;
SELECT stock FROM article WHERE id = 1;     -- 400: the transaction sees its own writes
ROLLBACK;
SELECT stock FROM article WHERE id = 1;     -- 500

Error in a transaction block#

As in PostgreSQL, an error puts the block in a failed state: until its end, every statement receives 25P02. Only ROLLBACK, ROLLBACK TO SAVEPOINT (see 38.2) and COMMIT (which then rolls back, and returns the ROLLBACK tag) are accepted.

BEGIN;
SELECT 1/0;
SELECT 1;
COMMIT;

BEGIN
ERROR:  division by zero                                   (22012)
ERROR:  current transaction is aborted, commands ignored until end of transaction block   (25P02)
ROLLBACK

A concurrency conflict (SQLSTATE 40001) or a deadlock (40P01), see 38.4, also puts the block in a failed state, but it has already rolled back the entire transaction: ROLLBACK TO SAVEPOINT does not recover it, only a ROLLBACK (or a COMMIT, which returns ROLLBACK) ends the block.

Read-only transaction#

READ ONLY forbids any write in the transaction (25006), and the error puts the block in a failed state like any other:

BEGIN READ ONLY;
SELECT count(*) FROM client;                       -- 3
UPDATE client SET ville = 'x' WHERE id = 1;
ROLLBACK;

ERROR:  Cannot execute statement in a READ ONLY transaction.   (25006)

SHOW transaction_read_only returns on in such a transaction, off otherwise.

Definition statements in a transaction#

The most common definition statements run inside the transaction of the block: ROLLBACK undoes them, COMMIT keeps them.

BEGIN;
CREATE TABLE remise (code varchar(20) PRIMARY KEY, taux numeric(4,2));
ALTER TABLE article ADD COLUMN poids integer;
CREATE INDEX idx_article_stock ON article (stock);
ROLLBACK;
SELECT count(*) FROM remise;
SELECT poids FROM article;

BEGIN
CREATE TABLE
ALTER TABLE
CREATE INDEX
ROLLBACK
ERROR:  relation "remise" does not exist   (42P01)
ERROR:  Unknown column 'poids' in 'field list'   (42703)

• ROLLBACK TO SAVEPOINT likewise undoes those executed since the savepoint (38.2); a failed COMMIT and closing the session without COMMIT also undo them.
• They are applied immediately: other sessions see the created table or the added column before the COMMIT.
• TRUNCATE in a block can be rolled back too (35. DML language).
• Any other definition statement (CREATE VIEW, ALTER TABLE … RENAME, DROP COLUMN, CREATE FUNCTION...), or one of those in the table outside the stated conditions, first commits the current transaction, with its writes and its rollbackable definition statements, then runs; a ROLLBACK that follows has nothing left to undo:

BEGIN;
INSERT INTO client (nom, email) VALUES ('Paul Leroy', '[email protected]');
CREATE VIEW v_ville AS SELECT DISTINCT ville FROM client;    -- commits the preceding INSERT
ROLLBACK;                                                    -- no longer cancels anything
SELECT nom FROM client WHERE nom = 'Paul Leroy';             -- the row is there, and so is the view

A migration script that uses statements other than those in the table therefore provides the inverse statements, or a backup (41. Catalog and tools).

What does not depend on the transaction#

• A sequence: a value served by nextval is never returned by ROLLBACK. The same applies to the counter of a serial or identity column: a rolled-back INSERT leaves a gap.
• Session-level advisory locks (pg_advisory_lock, see 38.6): neither COMMIT nor ROLLBACK releases them.

Session parameters, on the other hand, follow the transaction: a SET made in a rolled-back transaction is undone (see 38.7).

The autocommit variable#

A MIRAJ extension, useful for tools that cannot open a block: SET autocommit = 0 makes the first following statement open a transaction, until the next COMMIT or ROLLBACK. SET autocommit = 1 (or TO on) returns to the default mode. PostgreSQL drivers do not need it: their own "autocommit" mode (psycopg2 connection.autocommit, JDBC setAutoCommit) simply sends BEGIN and COMMIT.

38.2 Savepoints#

SAVEPOINT name;
ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] name;
RELEASE [SAVEPOINT] name;

• SAVEPOINT name sets a restart point in the open transaction.
• ROLLBACK TO name undoes everything the transaction has written since that point, removes the savepoints set after it, and keeps the savepoint itself as well as the transaction. It is also the way to get a block out of the failed state without losing everything.
• RELEASE name removes the savepoint and those set after it, without undoing anything.
• COMMIT and ROLLBACK erase all savepoints.

BEGIN;
INSERT INTO client (nom, email, ville) VALUES ('Léa Petit', '[email protected]', 'Lille');
SAVEPOINT avant_commande;
INSERT INTO commande (client_id) VALUES (99);          -- unknown customer
ROLLBACK TO SAVEPOINT avant_commande;                   -- the block is usable again
INSERT INTO commande (client_id) VALUES (4);
COMMIT;                                                  -- the customer and the order are written

BEGIN
INSERT 0 1
SAVEPOINT
ERROR:  Cannot add or update a child row: a foreign key constraint fails ("ventes"."commande",
        CONSTRAINT "commande_ibfk_1" FOREIGN KEY ("client_id") REFERENCES "client" ("id"))   (23503)
ROLLBACK
INSERT 0 1
COMMIT

As in PostgreSQL:

• savepoints with the same name stack up: ROLLBACK TO and RELEASE target the most recent one, and the old one becomes visible again once the new one is released;
• an unknown name gives savepoint "name" does not exist (3B001);
• outside a transaction block, SAVEPOINT, RELEASE and ROLLBACK TO are refused (25P01, SAVEPOINT can only be used in transaction blocks, likewise RELEASE SAVEPOINT … and ROLLBACK TO SAVEPOINT …);
• ROLLBACK TO also undoes the SET statements made since the savepoint (38.7), and the rollbackable definition statements executed since the savepoint (38.1);
• after an error of class 40 (conflict, deadlock), the transaction is already rolled back entirely: ROLLBACK TO is refused (25P01) and the block ends with ROLLBACK.

Differences from PostgreSQL:

• rows held since the savepoint (FOR UPDATE) stay held until the end of the transaction;
• Cluster edition: SAVEPOINT is refused in a transaction that writes the partitions of another node.

38.3 Isolation levels#

MIRAJ has only one behavior, the strictest: SERIALIZABLE. Everything happens as if transactions ran one after the other. The four standard levels are accepted and remembered (drivers send them when opening a transaction, and read them back), but none of them relaxes isolation: a transaction opened in READ COMMITTED (the level reported by default) also reads a fixed snapshot and can receive a conflict at commit.

SHOW transaction_isolation;                 -- read committed (default value)
BEGIN ISOLATION LEVEL SERIALIZABLE;
SHOW TRANSACTION ISOLATION LEVEL;           -- serializable
COMMIT;
SHOW TRANSACTION ISOLATION LEVEL;           -- read committed
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SHOW transaction_isolation;                 -- repeatable read
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;                 -- read uncommitted
COMMIT;
SHOW default_transaction_isolation;         -- repeatable read

The level reported by default for a session is read committed, as in PostgreSQL; RESET ALL and SET default_transaction_isolation TO DEFAULT return to it. SET default_transaction_isolation TO 'serializable' and SET default_transaction_read_only TO on are equivalent to SET SESSION CHARACTERISTICS AS TRANSACTION …: they set the level and mode of the following transactions of the session (a write then receives 25006, including outside a block; BEGIN READ WRITE still opens a read-write transaction). SHOW default_transaction_isolation and SHOW default_transaction_read_only return the level and mode of the following transactions, as the last of these statements set them, SHOW transaction_isolation and SHOW transaction_read_only the level and mode in effect (those of the current transaction inside a block, those of the following transactions outside a block). SET transaction_isolation and SET transaction_read_only are equivalent to SET TRANSACTION …: the current transaction only.

SET default_transaction_read_only TO on;
UPDATE client SET ville = ville WHERE id = 1;
BEGIN READ WRITE;
UPDATE client SET ville = ville WHERE id = 1;
COMMIT;
RESET default_transaction_read_only;

SET
ERROR:  Cannot execute statement in a READ ONLY transaction.   (25006)
BEGIN
UPDATE 1
COMMIT
RESET

With psycopg2, connection.set_session(isolation_level='REPEATABLE READ', readonly=True) sends these statements, and SHOW transaction_isolation then returns repeatable read.

38.4 Concurrency model#

Serializable isolation is obtained by optimistic multiversion control: each transaction works on its own snapshot of the data, without taking row locks. Nothing waits, except a row held explicitly by SELECT … FOR UPDATE (38.6). In return, a transaction can be cancelled by a conflict; the application retries it (38.5).

Readers read a snapshot#

-- Session A                                   -- Session B
BEGIN;
SELECT stock FROM article WHERE id = 2;        -- 200
                                                UPDATE article SET stock = stock + 5 WHERE id = 2;
SELECT stock FROM article WHERE id = 2;        -- 200: A's snapshot does not move
COMMIT;
SELECT stock FROM article WHERE id = 2;        -- 205

The same applies to a transaction opened in READ COMMITTED. A transaction that only reads is never cancelled.

The conflict: SQLSTATE 40001#

ERROR:  could not serialize access due to concurrent update
SQLSTATE : 40001

MIRAJ returns this code (PostgreSQL's serialization_failure, class 40 "transaction rollback") for any concurrency conflict: writing a row that another transaction holds, or a stale read discovered at commit. A true deadlock (a cycle of waits between locking reads, 38.6) returns 40P01 (deadlock_detected, message deadlock detected). A driver groups both in the same family (psycopg2: TransactionRollbackError, which includes SerializationFailure and DeadlockDetected), and the response is the same: no data is lost or damaged, this transaction only has to be retried from its beginning. A DDL statement (ALTER TABLE, DROP TABLE, CREATE INDEX, TRUNCATE...) refused because an open transaction holds the table is neither: it returns 55P03 (lock_not_available, message could not obtain lock on relation: it is being written by an open transaction), like a lock that PostgreSQL would not obtain within the lock_timeout delay, here without waiting. The statement can be replayed as is once the transaction has ended.

-- Session A                                   -- Session B
BEGIN;                                          BEGIN;
UPDATE client SET ville = 'Lyon 3'
 WHERE nom = 'Claire Dupont';                   -- UPDATE 1
                                                UPDATE client SET ville = 'Lyon 7'
                                                 WHERE nom = 'Claire Dupont';
                                                -- ERROR 40001, immediate
                                                ROLLBACK;
COMMIT;                                         -- COMMIT: 'Lyon 3'

The conflict can also appear only at commit, when what the transaction has read is no longer true:

-- Session A                                   -- Session B
BEGIN;                                          BEGIN;
SELECT stock FROM article WHERE id = 2;        -- 200
                                                UPDATE article SET stock = stock + 10
                                                 WHERE code = 'CAH-96';
                                                COMMIT;
UPDATE article SET stock = 0
 WHERE code = 'CLA-A4';                         -- UPDATE 1
COMMIT;                                         -- ERROR 40001: A's read is stale

After a 40001 or a 40P01, the transaction is already rolled back entirely, and the block stays in a failed state, as in PostgreSQL: every statement receives 25P02 until the ROLLBACK (a COMMIT returns ROLLBACK). A ROLLBACK TO SAVEPOINT does not recover it (25P01): there is no longer a restart point. The application therefore restarts the transaction at BEGIN, after the ROLLBACK.

COMMIT;      ERROR:  could not serialize access due to concurrent update   (40001)
SELECT 1;    ERROR:  current transaction is aborted, commands ignored until end of transaction block   (25P02)
ROLLBACK;    ROLLBACK

Outside a block, a standalone statement that conflicts is replayed by the engine (three attempts in quick succession) before returning 40001: it fails only if the other transaction still holds the row at that moment.

Deferred update of a heavily contended row#

A counter, or the stock of an item sold by every checkout, is a row that many transactions modify at the same time. MIRAJ handles suitable UPDATE statements separately: such an UPDATE does not take the row, it is recomputed at COMMIT on the last committed value.

-- stock of item 1: 500
-- Session A                                   -- Session B
BEGIN;                                          BEGIN;
UPDATE article SET stock = stock - 1
 WHERE id = 1;                                  -- UPDATE 1
                                                UPDATE article SET stock = stock - 2
                                                 WHERE id = 1;       -- UPDATE 1, no conflict
                                                COMMIT;              -- stock = 498
COMMIT;                                         -- stock = 497, no error

Until it commits, the transaction that made the UPDATE rereads the row according to its own snapshot; such a read makes the UPDATE depend on what it read, and the COMMIT then receives 40001 if another transaction modified the row in the meantime.

An UPDATE is deferred when it targets a row designated by its key (equalities on all columns of the primary key or of a uniqueness constraint), when it modifies no key, no foreign key and no identity column, when it has no subquery and no non-deterministic function (random()...), no RETURNING clause, and when the table's triggers allow it. Otherwise it takes the row, as in the earlier examples.

What this changes for the application:

• Errors at COMMIT. If the row has disappeared, or if the WHERE condition no longer holds at commit time, the COMMIT returns 40001. If the recomputation itself fails, the COMMIT returns the corresponding error, for example 23514 when two concurrent sales would bring the stock below zero while a CHECK (stock >= 0) constraint forbids it (new row for relation "article" violates check constraint "stock_positif"). In both cases, the whole transaction is rolled back.
• An UPDATE that carries its condition (… WHERE id = 3 AND stock >= 2) fails only if the condition no longer holds at COMMIT: this is the form to prefer over "read then write".
• Tuning: SET deferred_update TO off restores immediate row locking for the session (SHOW deferred_update then returns 0); RESET deferred_update returns to the default value (1, enabled).

38.5 Retrying a transaction#

Any transaction that writes must be ready to receive a class 40 error and to restart entirely, after ROLLBACK, from BEGIN: not just the last statement. With psycopg2:

import time
import psycopg2

conn = psycopg2.connect(host="127.0.0.1", port=7007, user="app", password="…", dbname="ventes")

def commander(conn, client_id, article_id, quantite, essais=5):
    for essai in range(1, essais + 1):
        try:
            with conn:                           # BEGIN … COMMIT, ROLLBACK if an exception escapes
                with conn.cursor() as cur:
                    cur.execute("INSERT INTO commande (client_id) VALUES (%s) RETURNING id", (client_id,))
                    commande_id = cur.fetchone()[0]
                    cur.execute("INSERT INTO ligne_commande "
                                "SELECT %s, id, %s, prix FROM article WHERE id = %s",
                                (commande_id, quantite, article_id))
                    cur.execute("UPDATE article SET stock = stock - %s WHERE id = %s",
                                (quantite, article_id))
            return commande_id
        except psycopg2.errors.TransactionRollbackError:   # class 40: 40P01, 40001
            time.sleep(0.01 * essai)             # short pause, then retry
    raise RuntimeError("commande abandonnée après %d essais" % essais)

commander(conn, 1, 2, 5)

Practical rules:

• test the class 40 (the first two characters of the SQLSTATE) rather than a specific code;
• redo the reads too: the values read during the cancelled attempt are no longer valid;
• keep transactions short, and prefer an UPDATE that carries its condition to a SELECT followed by an UPDATE;
• for a "read, decide, write" that must not fail, hold the row with SELECT … FOR UPDATE (38.6).

38.6 Locking reads and locks#

SELECT … FOR UPDATE#

SELECT … FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [NOWAIT | SKIP LOCKED];

The query reads the rows and holds them until the end of the transaction: no other transaction can write them, or hold them in turn. FOR SHARE and FOR KEY SHARE let other FOR SHARE readers hold them too; FOR NO KEY UPDATE behaves like FOR UPDATE.

BEGIN;
SELECT stock FROM article WHERE id = 3 FOR UPDATE;    -- the row is held
-- … stock check by the application …
UPDATE article SET stock = stock - 1 WHERE id = 3;
COMMIT;                                                -- the row is released

• A row that is already held is waited for: another SELECT … FOR UPDATE, an UPDATE or a DELETE of that row waits for the end of the transaction that holds it, then runs on the committed value. Two transactions that take their rows in the same order receive no error.
• Timeout: beyond lock_wait_timeout seconds (50 by default), the statement fails with 55P03 (Lock wait timeout exceeded); inside a block, the block enters a failed state. SET lock_wait_timeout TO 5 changes this timeout for the session; SET lock_timeout TO '500ms' (the PostgreSQL parameter, in milliseconds or with a unit) replaces it as soon as it is set. SET lock_timeout TO 0 means, as in PostgreSQL, wait without limit; RESET lock_timeout restores the lock_wait_timeout timeout. As long as lock_timeout has not been set, SHOW lock_timeout returns 0 but it is lock_wait_timeout that bounds the wait.
• NOWAIT: 55P03 immediately if a row is held.
• SKIP LOCKED: held rows are left out of the result (the "job queue" pattern).
• Deadlock (A holds 1 and requests 2, B holds 2 and requests 1): the transaction that would close the cycle receives 40P01 and is rolled back; the other obtains its row.
• Outside a transaction block, the row is held only for the duration of the statement.

-- Session A holds item 3 with FOR UPDATE.         Session B:
SELECT stock FROM article WHERE id = 3 FOR UPDATE NOWAIT;
-- ERROR:  Lock wait timeout exceeded; try restarting transaction   (55P03)
SELECT id, stock FROM article ORDER BY id FOR UPDATE SKIP LOCKED;
--  id | stock
-- ----+-------
--   1 |   500
--   2 |   200
-- (2 lignes)

FOR SHARE held by A lets B take the row with FOR KEY SHARE or FOR SHARE, but a FOR UPDATE or FOR NO KEY UPDATE … NOWAIT from B returns 55P03.

LOCK TABLE#

LOCK [TABLE] name [, …] [IN mode MODE] [NOWAIT] is accepted in its eight modes (ACCESS SHARE ... ACCESS EXCLUSIVE). Outside a transaction block, it is refused as in PostgreSQL (25P01, LOCK TABLE can only be used in transaction blocks).

Two modes set a lock, in both concurrency models, held until COMMIT or ROLLBACK (XA COMMIT / XA ROLLBACK for an XA branch, even a prepared one, including across a server shutdown, 38.11):

The lock is taken only once the table is free: LOCK TABLE waits for the end of other transactions that hold rows in it, have written it or hold its lock, and, for ACCESS EXCLUSIVE only, of those that have simply read it (PostgreSQL's ACCESS SHARE, held until the end of the transaction; EXCLUSIVE does not wait for it, as in PostgreSQL), in both concurrency models. An autocommit read holds nothing once finished. A session that finds the table locked likewise waits for the end of the transaction that holds it. The wait is bounded by lock_timeout (then 55P03, lock_not_available); with NOWAIT, 55P03 immediately; a cycle of waits returns 40P01 to one of the transactions. The transaction that holds the lock keeps reading and writing the table normally.

BEGIN;
LOCK TABLE article IN ACCESS EXCLUSIVE MODE;   -- other sessions wait
UPDATE article SET prix = prix * 1.02;
COMMIT;                                         -- lock released

The other modes (ACCESS SHARE ... SHARE ROW EXCLUSIVE) set no lock: MIRAJ's serializable isolation makes them unnecessary. A view or a temporary table is not locked.

Advisory locks#

Locks that the application names itself, to serialize a task (a closing run, an import) without locking data.

The key is a bigint, or two integer values (pg_try_advisory_lock(1, 2)). A given lock can be held by only one session at a time, whether it was taken for the session or for the transaction.

• Session locks are cumulative (taken twice, they are released twice): neither COMMIT nor ROLLBACK releases them, only pg_advisory_unlock and closing the session.
• Transaction locks are released automatically at the end of the transaction, committed or rolled back; outside a block, at the end of the statement.

SELECT pg_try_advisory_lock(42);     -- t
-- … exclusive processing …
SELECT pg_advisory_unlock(42);       -- t

BEGIN;
SELECT pg_advisory_xact_lock(7);     -- another session: pg_try_advisory_lock(7) returns f
-- … processing …
COMMIT;                              -- lock 7 released

38.7 Session parameters: SET, SHOW, RESET#

SET [SESSION | LOCAL] name {TO | =} {value | 'value' | DEFAULT};
SHOW name;
SHOW ALL;
RESET name;
RESET ALL;

• SET changes a parameter for the session. Made in a transaction block, it is undone if the transaction is rolled back (ROLLBACK, failed COMMIT) and by a ROLLBACK TO SAVEPOINT to a savepoint set before it. A list (SET name TO a, b) is a single value, the text a, b.
• SET LOCAL applies only until the end of the current transaction, committed or rolled back; a session SET of the same parameter made after it takes precedence. Outside a transaction block, it has no effect and returns the warning SET LOCAL can only be used in transaction blocks (25P01).
• A name that is neither a PostgreSQL parameter nor a MIRAJ variable is refused (42704, unrecognized configuration parameter "name"), by SET as by RESET (SHOW name: 42704, Unknown system variable 'name'). All PostgreSQL 16 and 17 configuration parameters are recognized, regardless of case: those that have no purpose here are remembered with no effect (table below), so that the SET statements issued by pg_dump, pg_restore, psql and drivers go through.
• A dotted name (SET monappli.locataire TO '42') is a custom parameter: remembered for the session, read back by SHOW monappli.locataire and current_setting('monappli.locataire') (42704 until it has been set), and subject to the same transaction rules.
• SHOW name returns one row and one column, named like the parameter. An unknown name gives 42704.
• SHOW ALL returns all parameters (name, setting, description), like the table pg_catalog.pg_settings; MIRAJ variables and custom parameters that have been set are included too.
• RESET name and SET name TO DEFAULT give the parameter back its value from the start of the session: that of the connection options if they set it (41.5), otherwise its default value. This applies to PostgreSQL parameters as to MIRAJ variables (lock_wait_timeout, statement_timeout...); a MIRAJ variable that the options do not set returns to the current server value (a SET GLOBAL made since the start of the session is picked up).
• RESET ALL gives all parameters their value from the start of the session, current schema (search_path) included (38.8).
• current_setting('name') reads a parameter in an expression (42704 if it is unknown, NULL with current_setting('name', true)).
• set_config('name', 'value', is_local) sets a parameter from an expression and returns the value: it is a SET (is_local false) or a SET LOCAL (is_local true), with the same checks and the same transaction rules. See "set_config" below.

Parameters are of two kinds: those of PostgreSQL, which clients and drivers send and read back, and MIRAJ variables, which tune the engine. Both are read with SHOW.

PostgreSQL parameters#

MIRAJ variables useful in a session#

SHOW VARIABLES LIKE 'lock%', @@lock_wait_timeout and user variables (SET @v = 5, SELECT @v) remain available as MIRAJ extensions.

SET statement_timeout TO '5s';
BEGIN;
SET LOCAL statement_timeout TO '30s';     -- for this transaction only
SET monappli.locataire TO '42';
SAVEPOINT s;
SET lock_timeout TO '2s';
ROLLBACK TO SAVEPOINT s;                  -- lock_timeout returns to 0
COMMIT;
SHOW statement_timeout;                   -- 5s
SHOW monappli.locataire;                  -- 42

set_config#

SELECT set_config('monappli.locataire', '42', false);   -- like SET: for the session
BEGIN;
SELECT set_config('monappli.locataire', '7', true);     -- like SET LOCAL: for the transaction
SELECT current_setting('monappli.locataire');           -- 7
COMMIT;
SELECT current_setting('monappli.locataire');           -- 42

• The name is that of a PostgreSQL parameter, a MIRAJ variable or a custom parameter; search_path, TimeZone, statement_timeout and role are set this way just as with their SET. An unknown name returns 42704, a malformed name 42602, a server parameter 55P02, a refused value 22023 or 0A000.
• A NULL value gives the parameter its value from the start of the session (like RESET); the function then returns NULL.
• is_local true outside a transaction block: the value applies only to the statement, with no warning; nothing remains afterward.
• The value can be passed as a bound parameter (SELECT set_config('monappli.locataire', $1, true)) and the function can be used in any statement, including in the body of a PL/pgSQL function or procedure (PERFORM set_config(…)), whose following statements read the value with current_setting.
• The parameter is applied at the end of the statement that calls set_config, if it succeeds (a failed statement sets nothing).

Differences from PostgreSQL#

• set_config applies its value at the end of the statement and not at the time of the call: in the same SQL statement, current_setting can still return the old value (SELECT set_config('a.b', 'x', false), current_setting('a.b')); read it in the following statement. An invalid value (22023) is reported at that point: in autocommit, the statement's writes are then already committed.
• session_replication_role = replica suspends all user triggers: there is no ENABLE REPLICA or ENABLE ALWAYS trigger that would run anyway. The history of a versioned table continues to be maintained.
• An XA transaction (38.11) undoes its SET statements at XA ROLLBACK and ends its SET LOCAL at its end, including after XA PREPARE, whereas PostgreSQL's PREPARE TRANSACTION keeps the SET statements of the transaction as if they were committed.

38.8 Current schema and search_path#

Each MIRAJ database is a PostgreSQL schema, and there is only one PostgreSQL database, miraj. The session has a current schema: the one where a table named without a schema is looked up and created. It is set at connection by the dbname parameter (when it names a MIRAJ database), then by:

SET search_path TO ventes;
SET search_path TO ventes, public;

SET search_path makes the first named schema that exists (and that the account can read) the current schema. There is no multi-level path: the following schemas in the list are not consulted.

SET search_path TO ventes, archives;
SELECT count(*) FROM client;              -- ventes.client, never archives.client
SELECT count(*) FROM archives.client;     -- a table in another schema is named in full
SHOW search_path;                         -- ventes

pg_catalog comes before any schema, as in PostgreSQL: SELECT * FROM pg_class reads the catalog table (41. Catalog and tools). A name qualified with public. designates the current schema.

38.9 Time zone and statement timeout#

Session time zone#

SET TIME ZONE 'Europe/Paris';
SET TimeZone TO 'UTC';
SET TIME ZONE -5;            -- offset in hours
SET TIME ZONE LOCAL;         -- time zone of the server machine
SET TIME ZONE DEFAULT;       -- server default value
SHOW TIME ZONE;              -- or SHOW timezone

The session time zone governs now(), the reading and display of timestamptz, AT TIME ZONE and current_setting('TimeZone') (33. Data types, 37. SQL functions). An unknown time zone is refused:

ERROR:  invalid value for parameter "TimeZone": "Mars/Olympus"   (22023)

The default time zone is the server's; a client can request another one when opening the connection through its options (options=-c TimeZone=Europe/Paris, see 41.5), or through the TimeZone parameter of the startup message, which pgjdbc and Npgsql send on their own: the session then takes the client's time zone. A time zone unknown to the server is ignored there. An offset written as text (SET TIME ZONE '+02:00') is read east of Greenwich (the session time is then two hours ahead of UTC), unlike PostgreSQL's POSIX notation: prefer a time zone name. After SET TIME ZONE LOCAL, SHOW TimeZone returns the name of the server machine's time zone (for example Etc/GMT-1).

Maximum duration of a statement#

SET statement_timeout TO 10000;     -- milliseconds: SHOW returns 10s
SET statement_timeout TO 1500;      -- SHOW returns 1500ms
SET statement_timeout TO '2s';      -- or '1min'
SHOW statement_timeout;             -- 2s
SELECT pg_sleep(3);
RESET statement_timeout;            -- server value

ERROR:  Query was interrupted: execution time limit 2.0 sec exceeded   (57014)

RESET statement_timeout returns the maximum duration set on the server (max_statement_time, 300 seconds with no other setting: SHOW statement_timeout then returns 5min); SET statement_timeout TO 0 removes any limit for the session.

A statement cancelled by the client (Ctrl-C in psql, connection.cancel() in psycopg2) or by KILL QUERY receives the same SQLSTATE 57014. Cancelled during a wait (row held by another transaction, table busy with the long statement of another session), it writes nothing; a COMMIT cancelled before it has started commits nothing and leaves the block in a failed state.

Transaction time#

As in PostgreSQL, now(), current_timestamp, transaction_timestamp(), current_date, current_time, localtimestamp and DEFAULT now() columns return, in a transaction block, the time of the start of the transaction (its BEGIN): all the rows it writes carry the same instant. statement_timestamp() returns the time the statement started and clock_timestamp() the clock time at each call. Outside a block, each statement is its own transaction: now() and statement_timestamp() are equal there.

BEGIN;
SELECT now() = statement_timestamp();
SELECT pg_sleep(1);
SELECT now() = statement_timestamp(), now() = transaction_timestamp(), now() < clock_timestamp();
COMMIT;

BEGIN
 now() = statement_timestamp()
-------------------------------
 t
(1 ligne)

 pg_sleep(1)
-------------
           0
(1 ligne)

 now() = statement_timestamp() | now() = transaction_timestamp() | now() < clock_timestamp()
-------------------------------+---------------------------------+---------------------------
 f                             | t                               | t
(1 ligne)

COMMIT

38.10 Cursors, prepared statements, reset#

SQL cursors#

DECLARE name [SCROLL | NO SCROLL] CURSOR [WITH HOLD | WITHOUT HOLD] FOR query;
FETCH [direction] [FROM | IN] name;
MOVE [direction] [FROM | IN] name;
CLOSE {name | ALL};

direction : NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n | n | ALL
          | FORWARD [n | ALL] | BACKWARD [n | ALL]

Without a direction, FETCH name reads the next row (NEXT). Numbers are signed (FETCH -1 moves back one row, ABSOLUTE -1 is the last row); 0 (FETCH 0, RELATIVE 0) rereads the current row.

A cursor reads the result of a query in pieces; this is what psycopg2's "named cursors" (conn.cursor(name='lecture')) use. The entire result is computed at DECLARE and kept by the server; reading goes in both directions, with the same positions as PostgreSQL (beyond the last row, FETCH PRIOR returns the last one; MOVE returns the number of rows that FETCH would have returned).

BEGIN;
DECLARE c_art CURSOR FOR SELECT id, code FROM article ORDER BY id;
FETCH 2 FROM c_art;
FETCH NEXT FROM c_art;
FETCH ALL FROM c_art;
CLOSE c_art;
COMMIT;

BEGIN
DECLARE CURSOR
 id |  code
----+--------
  1 | STY-01
  2 | CAH-96
(2 lignes)

FETCH 2
 id |  code
----+--------
  3 | CLA-A4
(1 ligne)

FETCH 1
 id | code
----+------
(0 lignes)

FETCH 0
CLOSE CURSOR
COMMIT

• Without WITH HOLD, a cursor requires a transaction block (25P01, DECLARE CURSOR can only be used in transaction blocks) and disappears at its end.
• WITH HOLD is declared outside a transaction and survives COMMIT; close it with CLOSE.
• CLOSE ALL closes all cursors of the session.
• A closed or unknown cursor: 34000 (cursor "c5" does not exist); inside a block, the error puts it in a failed state like any other.
• SCROLL and NO SCROLL together: 42P11 (cannot specify both SCROLL and NO SCROLL).
• A NO SCROLL cursor refuses any backward move, including FETCH 0 and returning to the start: 55000 (cursor can only scan forward). With neither SCROLL nor NO SCROLL, the cursor can be read in both directions (PostgreSQL decides based on the query plan: declare SCROLL for a program that must work on both).

BEGIN;
DECLARE c_dos SCROLL CURSOR FOR SELECT id FROM article ORDER BY id;
FETCH LAST FROM c_dos;        -- 3
FETCH BACKWARD 2 FROM c_dos;  -- 2, then 1
FETCH ABSOLUTE 2 FROM c_dos;  -- 2
MOVE BACKWARD ALL FROM c_dos; -- MOVE 1
COMMIT;

Prepared statements#

PREPARE name [(types)] AS statement;     -- parameters $1, $2...
EXECUTE name [(values)];
DEALLOCATE [PREPARE] {name | ALL};

PREPARE art_par_prix (numeric) AS
  SELECT code, prix FROM article WHERE prix >= $1 ORDER BY prix;
EXECUTE art_par_prix(2);
DEALLOCATE art_par_prix;
PREPARE maj_stock (int, int) AS UPDATE article SET stock = stock + $2 WHERE id = $1;
EXECUTE maj_stock(1, 0);
DEALLOCATE ALL;

PREPARE
  code  | prix
--------+------
 CAH-96 | 2.50
 CLA-A4 | 4.90
(2 lignes)

SELECT 2
DEALLOCATE
PREPARE
UPDATE 1
DEALLOCATE ALL

The type list is accepted without constraining the parameters. EXECUTE returns the tag of the prepared statement (SELECT 2, UPDATE 1...). An unknown name gives 26000 (Unknown prepared statement handler (art_par_prix) given to EXECUTE). Preparing the same name twice is refused (42P05, prepared statement "q" already exists): release the statement first with DEALLOCATE. Drivers usually prepare through the protocol (Parse, Bind) rather than with these statements (32. Getting started).

DISCARD#

A connection pool can therefore recycle a session with DISCARD ALL, as with PostgreSQL: the current schema also returns to that of the start of the session (38.8). An application that chooses its schema with SET search_path after connecting therefore sets it again after each DISCARD ALL, as with PostgreSQL; give it at connection instead (dbname, or search_path in the startup parameters).

Running sessions#

pg_catalog.pg_stat_activity lists sessions (pid, usename, application_name, state, query); pg_backend_pid() returns the number of one's own. application_name there is the one the session gave at connection or by SET, picked up at the end of its last command. To interrupt a session, PostgreSQL functions and MIRAJ's KILL statement have the same effect:

SELECT pid, usename, state, query FROM pg_stat_activity ORDER BY pid;
SELECT pg_cancel_backend(14);      -- interrupts the current statement of session 14: t
KILL QUERY 14;                     -- likewise
SELECT pg_terminate_backend(14);   -- closes session 14: t
KILL 14;                           -- likewise
-- all idle sessions of an application, except one's own
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND application_name = 'rapport' AND state = 'idle';

pg_cancel_backend(pid) and pg_terminate_backend(pid) return t as soon as the request is sent, without waiting for its effect, and NULL for a NULL pid. An unknown pid gives f with the warning PID 99 is not a PostgreSQL backend process (KILL answers 42704). The request is sent at the call, row by row: it is not taken back if the statement fails afterward, nor by a ROLLBACK. pg_terminate_backend(pid, timeout) waits at most timeout milliseconds for the session to have left the server, and returns f with a warning if it is still there. The privileges are those of KILL: an account reaches its own sessions, all of them with the PROCESS privilege (or SUPER, CONNECTION_ADMIN); otherwise 42501, permission denied to terminate process (or to cancel query). A session that targets itself immediately receives 57014 (pg_cancel_backend) or the fatal error 57P01 (pg_terminate_backend).

The session closed by pg_terminate_backend or KILL (or KILL CONNECTION) receives, before the end of its connection, the same fatal error as from PostgreSQL: FATAL, 57P01, terminating connection due to administrator command. It receives it in every state: waiting for a command, during a query (abandoned; the fatal error replaces that of the statement), in a transaction block (rolled back, its locks released at the latest at the end of the stream), during a COPY, while waiting for a lock (the waiting statement writes nothing, nothing is committed). The server sends the same error to all connections when it stops with SHUTDOWN. A session that has sent nothing for longer than the server's idle timeout (option --idle-timeout, 8 hours by default) receives FATAL, 57P05, terminating connection due to idle-session timeout. The message is not awaited by a client that no longer reads what the server sends it: its connection is closed without it.

38.11 XA transactions (two-phase commit)#

Two-phase commit is written with MIRAJ's XA statements, available in the PostgreSQL language (PostgreSQL's PREPARE TRANSACTION and COMMIT PREPARED forms are not):

XA START 'cmd-42';
UPDATE article SET stock = stock - 1 WHERE id = 1;
XA END 'cmd-42';
XA PREPARE 'cmd-42';
XA RECOVER;                 -- lists the prepared branches
XA COMMIT 'cmd-42';         -- or XA ROLLBACK 'cmd-42'

A prepared branch survives the closing of its session and a server shutdown, clean or abrupt, until a coordinator commits or rolls it back, along with its table locks (LOCK TABLE … IN [ACCESS] EXCLUSIVE MODE, TRUNCATE in the branch), like a prepared PostgreSQL transaction: after a restart, other sessions still wait for its decision (lock_timeout, then 55P03); a table that it emptied with TRUNCATE and then wrote is restored emptied, with only the rows written since. A branch that holds only a table lock, without writing anything, survives too. The SET statements made in the branch follow its fate like those of a BEGIN block: undone by XA ROLLBACK, kept by XA COMMIT, and SET LOCAL statements end with it. The session that resolves a branch prepared by another session (closed since) keeps its own parameters. The states, rules and limits are described in chapter 14.

38.12 Not supported and differences#

Behavior differences, detailed in this chapter:

• isolation levels relax nothing: all read a fixed snapshot (38.3);
• a conflict (40001) or a deadlock (40P01) rolls back the whole transaction; ROLLBACK TO SAVEPOINT does not recover it (38.4);
• a writer does not wait behind another: it fails immediately, except for a row held by FOR UPDATE (38.4, 38.6); NOWAIT and an exceeded timeout return 55P03;
• only some definition statements are rolled back with the transaction; the others commit it first (38.1);
• COMMIT, ROLLBACK outside a transaction and a repeated BEGIN give no warning;
• LOCK TABLE sets a lock only in EXCLUSIVE and ACCESS EXCLUSIVE modes (38.6);
• set_config applied at the end of the statement, SET in a routine (38.7);
• a DDL statement refused by an open transaction returns 55P03 immediately, without waiting (38.4);
• TRUNCATE in a block: can be rolled back and emptied in bulk, on-disk tables and partitioned tables of the Cluster edition included; only a partitioned table with a partition held by another node has its rows deleted one by one, more slowly (35.6);
• search_path keeps only one schema (38.8);
• txid_current() returns a positive number, the same throughout the transaction, but specific to MIRAJ: it is neither increasing from one transaction to the next, nor comparable to PostgreSQL's.

The condensed grammar list is in chapter 30.

See also#

• 35. DML language for INSERT, UPDATE, DELETE, RETURNING and ON CONFLICT.
• 39. Accounts and privileges for what a session is allowed to do.
• 40. Routines and triggers (PL/pgSQL) for transactions as seen from a function.
• 14. Transactions and Concurrency, for the same model described in the MIRAJ language.