PostgreSQL-compatible functions

Functions a PostgreSQL app expects: arrays, sequence numbers (nextval), who you are and what you may do (current_user, has_table_privilege), comments (COMMENT ON), advisory locks and session settings, so app code runs on KDB without changes.

• Category: Postgres protocol
• Keywords: PostgreSQL functions, to_regclass, pg_relation_size, pg_total_relation_size, pg_table_size, pg_indexes_size, pg_database_size, pg_size_pretty, table size, database size, current_database, postgres compatibility, array, ARRAY[], arrays, list of values, watchlist, tags, sequence, auto number, order number, invoice number, advisory lock, named lock, job lock, one worker at a time, pg_advisory, nextval, currval, lastval, setval, unnest, cardinality, current_setting, set_config, SET LOCAL, session setting, custom setting, current_user, session_user, current_role, current_schema, current_schemas, current_catalog, version, has_table_privilege, has_column_privilege, has_database_privilege, has_schema_privilege, pg_has_role, privileges, rights, COMMENT ON, comment, description, obj_description, col_description, pg_get_indexdef, pg_get_constraintdef, pg_get_viewdef, pg_get_expr, format_type, pg_table_is_visible
• See also: PostgreSQL protocol (port 8832), JSON functions, Text functions, Date and time functions, Constraints, Transactions

A PostgreSQL app sends more than plain SQL. It also calls arrays, sequences and advisory locks. KDB answers all three with the PostgreSQL names, so the same app code runs here.

• An array is a list of values in one value: ARRAY['CITY', 'COAST']. KDB keeps an array as a JSON list, so a JSON column can hold one and every JSON function still works on it.
• A sequence is a counter the database keeps for you: order numbers, invoice numbers. nextval gives the next one. See CREATE SEQUENCE in constraints for how to make one.
• An advisory lock is a name (a number) your app takes so two copies of it do not do the same job twice.

The other PostgreSQL function names live with their own kind: the JSON ones in JSON functions, the time ones in date and time, initcap, md5, to_str and string_to_array in text functions.

Example: A watchlist as one array value (sample data: sample)

SELECT ARRAY['CITY', 'COAST', 'VOLT'] AS watchlist,
       cardinality(ARRAY['CITY', 'COAST', 'VOLT']) AS how_many,
       array_to_string(ARRAY['CITY', 'COAST', 'VOLT'], ', ') AS shown;

Result:

(1 row)

Arrays in KDB

Syntax:

ARRAY[value, value, ...]        an array value
'["CITY","COAST"]'              JSON text an array function reads as an array
'{CITY,COAST}'                  PostgreSQL array text an array function reads too

An array is a list of values kept as one value. KDB stores it as a JSON list, so ARRAY['CITY','COAST'] shows as ["CITY","COAST"] and fits in a JSON column.

• Counting starts at 1, as in PostgreSQL: the first element is number 1.
• An array may hold text, numbers, TRUE/FALSE and NULL mixed together. KDB does not check that all elements are of one type.
• The functions on this page also read text: text that starts with [ as JSON, text that starts with { as PostgreSQL array text ('{CITY,COAST}'). So a value a PostgreSQL driver sends still works.
• A JSON column keeps JSON only. To save a PostgreSQL array text in one, read it first: string_to_array(t, ','), or array_cat(t, ARRAY[]).
• There are no arrays of arrays with their own functions. array_length(a, 2) (a second dimension) always gives NULL.
• To store a list, use a JSON column. To take a list apart into rows, use unnest.

Example: A watchlist column an app reads and writes (sample data: sample)

Setup:

CREATE TABLE watchlists (user_id INT PRIMARY KEY, symbols JSON);
INSERT INTO watchlists VALUES (1, '["CITY","COAST"]'), (2, array_cat('{VOLT,ACME}', ARRAY[]));

SELECT user_id, symbols, cardinality(symbols) AS how_many,
       symbols ->> 0 AS first_symbol
FROM watchlists ORDER BY user_id;

Result:

(2 rows)

array_append

Syntax:

array_append(array, value)

The array with one more value at the end.

• Returns: the new array (JSON). The old one is not changed.
• NULL: array_append(NULL, 'CITY') is ["CITY"] (a NULL array counts as empty). A NULL value is added as JSON null.
• Notes: PostgreSQL writes this as array || value. In KDB || joins text, so use the function name.

Example: Add a company to a user's watchlist (sample data: sample)

Setup:

CREATE TABLE watchlists (user_id INT PRIMARY KEY, symbols JSON);
INSERT INTO watchlists VALUES (1, '["CITY","COAST"]');

UPDATE watchlists SET symbols = array_append(symbols, 'VOLT') WHERE user_id = 1;
SELECT user_id, symbols FROM watchlists;

Result:

UPDATE watchlists SET symbols = array_append(symbols, 'VOLT') WHERE user_id = 1

1 row(s) updated

SELECT user_id, symbols FROM watchlists

(1 row)

array_prepend

Syntax:

array_prepend(value, array)

The array with one more value at the front. The value comes first, the array second (as in PostgreSQL).

• Returns: the new array (JSON).
• NULL: a NULL array counts as empty. A NULL value is put in front as JSON null.

Example (sample data: sample)

SELECT array_prepend('ACME', ARRAY['CITY', 'COAST']) AS watchlist;

Result:

(1 row)

array_cat

Syntax:

array_cat(array1, array2)

The two arrays joined into one, in order.

• Returns: the joined array (JSON).
• NULL: only when both are NULL. One NULL counts as an empty list.

Example: One watchlist for two users (sample data: sample)

SELECT array_cat(ARRAY['CITY', 'COAST'], ARRAY['VOLT']) AS together,
       array_cat(NULL, ARRAY['TELCO']) AS one_side_null,
       array_cat(NULL, NULL) AS both_null;

Result:

(1 row)

array_remove

Syntax:

array_remove(array, value)

The array without every element equal to value.

• Returns: the shorter array (JSON). Nothing equal: the same array.
• NULL: array_remove(NULL, x) is NULL. array_remove(a, NULL) takes the null elements out.

Example: A user removes a company from the watchlist (sample data: sample)

Setup:

CREATE TABLE watchlists (user_id INT PRIMARY KEY, symbols JSON);
INSERT INTO watchlists VALUES (1, '["CITY","COAST","CITY"]');

UPDATE watchlists SET symbols = array_remove(symbols, 'CITY') WHERE user_id = 1;
SELECT user_id, symbols FROM watchlists;

Result:

UPDATE watchlists SET symbols = array_remove(symbols, 'CITY') WHERE user_id = 1

1 row(s) updated

SELECT user_id, symbols FROM watchlists

(1 row)

array_position

Syntax:

array_position(array, value [, start])

Which place value has in the array. The first place is 1.

• Returns: LONG, the place of the first match, or NULL when the value is not there.
• NULL: a NULL array gives NULL. array_position(a, NULL) finds a null element.

Example: Where is VOLT in the watchlist? (sample data: sample)

SELECT array_position(ARRAY['CITY', 'COAST', 'VOLT'], 'VOLT') AS at_place,
       array_position(ARRAY['CITY', 'COAST', 'VOLT'], 'TELCO') AS not_there,
       array_position(ARRAY['CITY', 'COAST', 'CITY'], 'CITY', 2) AS from_place_2;

Result:

(1 row)

array_length, array_upper

Syntax:

array_length(array, dimension)
array_upper(array, dimension)

How many elements the array has. dimension must be 1; KDB has one dimension only. array_upper gives the number of the last place, which is the same number.

• Returns: LONG.
• NULL: an empty array gives NULL (that is what PostgreSQL does; use cardinality for 0). A NULL array, or a dimension that is not 1, also gives NULL.

Example (sample data: sample)

SELECT array_length(ARRAY['CITY', 'COAST'], 1) AS how_many,
       array_upper(ARRAY['CITY', 'COAST'], 1) AS last_place,
       array_length(ARRAY[], 1) AS empty_list,
       array_length(ARRAY['CITY'], 2) AS no_second_dimension;

Result:

(1 row)

cardinality

Syntax:

cardinality(array)

How many elements the array has, counting an empty array as 0.

• Returns: LONG.
• NULL: cardinality(NULL) is NULL. An empty array gives 0, not NULL.
• Notes: this is the safe one to use in WHERE: cardinality(symbols) > 0.

Example: How many companies each user follows (sample data: sample)

Setup:

CREATE TABLE watchlists (user_id INT PRIMARY KEY, symbols JSON);
INSERT INTO watchlists VALUES (1, '["CITY","COAST"]'), (2, '[]'), (3, NULL);

SELECT user_id, cardinality(symbols) AS follows, array_length(symbols, 1) AS same_but_null_when_empty
FROM watchlists ORDER BY user_id;

Result:

(3 rows)

array_to_string

Syntax:

array_to_string(array, separator [, null_text])

The elements written one after the other with separator between them.

• Returns: STRING.
• NULL: a NULL array or a NULL separator gives NULL.

Example: A watchlist in one line for an email (sample data: sample)

SELECT array_to_string(ARRAY['CITY', 'COAST', 'VOLT'], ', ') AS watchlist,
       array_to_string(ARRAY['CITY', NULL, 'VOLT'], ', ') AS nulls_left_out,
       array_to_string(ARRAY['CITY', NULL, 'VOLT'], ', ', '?') AS nulls_shown;

Result:

(1 row)

unnest

Syntax:

unnest(array)

Turns one array into many rows, one row per element, in order.

• Returns: one row per element.
• NULL: a NULL array gives no rows.
• Notes: this is how a PostgreSQL app sends a list of symbols in one parameter: WHERE symbol IN (SELECT unnest($1)).

Example: One row per company in the list (sample data: sample)

SELECT unnest(ARRAY['CITY', 'COAST', 'VOLT']) AS symbol;

Result:

(3 rows)

Example: An app sends a watchlist and asks for those companies (sample data: sample)

SELECT symbol, name, sector
FROM securities
WHERE symbol IN (SELECT unnest(ARRAY['CITY', 'VOLT']))
ORDER BY symbol;

Result:

(2 rows)

nextval

Syntax:

nextval('sequence_name')

The next number of a sequence. Each call gives a number that was never given before, even when many connections ask at the same time.

• Returns: LONG.
• NULL: a NULL name gives NULL.
• Notes: make the sequence first with CREATE SEQUENCE order_no_seq START WITH 1001. A number that was given is never given again, also when the transaction is rolled back - so there can be gaps. A sequence that does not exist is an error. For a simple id column, PRIMARY KEY AUTO is easier (see constraints).

Example: Order numbers from a sequence

Setup:

CREATE SEQUENCE order_no_seq START WITH 1001;
CREATE TABLE orders (order_no LONG PRIMARY KEY, symbol SYMBOL, quantity LONG);

INSERT INTO orders VALUES (nextval('order_no_seq'), 'CITY', 100),
                          (nextval('order_no_seq'), 'COAST', 50);
SELECT * FROM orders ORDER BY order_no;

Result:

INSERT INTO orders VALUES (nextval('order_no_seq'), 'CITY', 100), (nextval('order_no_seq'), 'COAST',

2 row(s) inserted

SELECT * FROM orders ORDER BY order_no

(2 rows)

currval

Syntax:

currval('sequence_name')

The number this connection got last from that sequence. It does not move the sequence on.

• Returns: LONG.
• NULL: a NULL name gives NULL.
• Notes: it is an error before this connection has called nextval on that sequence. Another connection's numbers are never shown, so currval is safe to read after an insert.

Example: The order number just given

Setup:

CREATE SEQUENCE order_no_seq START WITH 1001;
CREATE TABLE orders (order_no LONG PRIMARY KEY, symbol SYMBOL);

INSERT INTO orders VALUES (nextval('order_no_seq'), 'CITY');
SELECT currval('order_no_seq') AS my_order_no;

Result:

INSERT INTO orders VALUES (nextval('order_no_seq'), 'CITY')

1 row(s) inserted

SELECT currval('order_no_seq') AS my_order_no

(1 row)

Example: currval before nextval is an error (shows an error)

Setup:

CREATE SEQUENCE order_no_seq;

SELECT currval('order_no_seq');

Result:

Error: currval of sequence 'order_no_seq' is not yet defined in this session (call nextval first)

lastval

Syntax:

lastval()

The number this connection got last from any sequence.

• Returns: LONG.
• NULL: never NULL.
• Notes: it is an error before this connection has called nextval at all. With more than one sequence, currval('name') says clearly which one you mean.

Example: The last number of any sequence

Setup:

CREATE SEQUENCE order_no_seq START WITH 1001;
CREATE SEQUENCE invoice_no_seq START WITH 500;

SELECT nextval('order_no_seq') AS order_no, nextval('invoice_no_seq') AS invoice_no;
SELECT lastval() AS last_of_any, currval('order_no_seq') AS last_order_no;

Result:

SELECT nextval('order_no_seq') AS order_no, nextval('invoice_no_seq') AS invoice_no

(1 row)

SELECT lastval() AS last_of_any, currval('order_no_seq') AS last_order_no

(1 row)

setval

Syntax:

setval('sequence_name', value)
setval('sequence_name', value, is_called)

Sets the sequence to value. Use it after loading old rows, so new numbers start above them.

• Returns: LONG, the value you gave.
• NULL: a NULL name or a NULL value gives NULL and changes nothing.
• Notes: a value outside the sequence's MINVALUE/MAXVALUE is an error. setval is written to disk at once; a ROLLBACK does not take it back.

Example: Move the counter above the order numbers already loaded

Setup:

CREATE SEQUENCE order_no_seq START WITH 1;
CREATE TABLE orders (order_no LONG PRIMARY KEY, symbol SYMBOL);
INSERT INTO orders VALUES (5000, 'CITY'), (5001, 'COAST');

SELECT setval('order_no_seq', (SELECT max(order_no) FROM orders)) AS set_to;
INSERT INTO orders VALUES (nextval('order_no_seq'), 'VOLT');
SELECT * FROM orders ORDER BY order_no;

Result:

SELECT setval('order_no_seq', (SELECT max(order_no) FROM orders)) AS set_to

(1 row)

INSERT INTO orders VALUES (nextval('order_no_seq'), 'VOLT')

1 row(s) inserted

SELECT * FROM orders ORDER BY order_no

(3 rows)

Example: is_called = FALSE: the next number is the one you gave

Setup:

CREATE SEQUENCE order_no_seq;

SELECT setval('order_no_seq', 7000, FALSE) AS set_to;
SELECT nextval('order_no_seq') AS next_number;

Result:

SELECT setval('order_no_seq', 7000, FALSE) AS set_to

(1 row)

SELECT nextval('order_no_seq') AS next_number

(1 row)

Names and sizes: to_regclass, pg_relation_size, ...

Syntax:

to_regclass('name')
pg_relation_size(relation [, 'main'])
pg_table_size(relation)
pg_indexes_size(relation)
pg_total_relation_size(relation)
pg_database_size(name_or_oid)
pg_size_pretty(bytes)
current_database()

• Arguments: a relation is a name in single quotes ('public.orders' and '"Orders"' work) or an oid from pg_class.
• Returns: a table's size is its partition files plus its new rows that are still in the log (they move into the files at the next checkpoint), so a table that was just filled is never 0.
• NULL: a NULL argument, or an oid nothing has, gives NULL. A name that does not exist is an error (42P01), as in PostgreSQL.
• Notes: KDB keeps indexes in memory only (built from the rows when the database opens), so they take 0 bytes on disk. pg_database_size measures only the database the statement runs in.
• Waits: a size reads the table the way a query of it does. pg_total_relation_size('t') waits for a statement that is writing t (never for writers of other tables), even with snapshot reads on. With an oid or a column instead of a name in quotes - pg_total_relation_size(c.oid) over pg_class - the size waits for every writer, as SHOW TABLES does.

Example: How big are the tables?

Setup:

CREATE TABLE orders (id LONG PRIMARY KEY, symbol SYMBOL, quantity LONG);
INSERT INTO orders VALUES (1, 'CITY', 100), (2, 'VOLT', 50);

SELECT to_regclass('orders') AS found, to_regclass('orderz') AS missing,
       pg_total_relation_size('orders') > 0 AS has_bytes,
       pg_size_pretty(10240) AS ten_kb, pg_size_pretty(1610612736) AS big;

Result:

(1 row)

Who and where: current_user, version()

Syntax:

current_user   current_role   user   session_user
current_schema   current_schemas(include_implicit)   current_catalog
version()

• The same answers over HTTP /exec and the Postgres port. With an API token you are its user.
• No user: with the server's --token or a local tool (kdb sql) there is no user; the name is kdb. On the Postgres port of a server without users it is the name the client connected as.
• Write the first four without brackets, as SQL does. A column of that name wins: SELECT user FROM _audit_log reads the column.

Example: Who am I, and on what?

SELECT current_user, session_user, current_schema, current_catalog,
       version() LIKE 'PostgreSQL 14%' AS pg14;

Result:

(1 row)

Rights: has_table_privilege and friends

Syntax:

has_table_privilege([user,] table, privilege)
has_column_privilege([user,] table, column, privilege)
has_database_privilege([user,] database, privilege)
has_schema_privilege([user,] schema, privilege)
pg_has_role([user,] role, privilege)

TRUE when the user may do it now: the same rules the statements are checked with - the user's role, changed by GRANT / REVOKE ... ON TABLE.

• user: a user name; without it, the current user. public is PostgreSQL's everyone and has no rights. A user whose own ROLE is admin has every right, as a PostgreSQL superuser.
• privilege: one word or a list, 'SELECT, INSERT': TRUE when any of them is allowed. ... WITH GRANT OPTION is TRUE only for an admin: KDB has no grant options.
• Objects: a name ('orders', 'public.orders') or an oid from pg_class or pg_database. An oid nothing has gives NULL; a name that does not exist is an error.
• Errors, as PostgreSQL: 22023 unrecognized privilege type, 42704 a user that does not exist, 42P01 a table, 42703 a column, 3D000 a database, 3F000 a schema.

Example: What a read-only user may do

Setup:

CREATE USER admin WITH PASSWORD 'Long-Admin-Passw0rd' ROLE admin;
CREATE USER charts WITH PASSWORD 'Chart-app-secret-11' ROLE read;
CREATE TABLE orders (id LONG PRIMARY KEY, quantity LONG);

SELECT has_table_privilege('charts', 'orders', 'SELECT') AS reads,
       has_table_privilege('charts', 'orders', 'INSERT') AS writes,
       has_schema_privilege('charts', 'public', 'CREATE') AS creates,
       pg_has_role('charts', 'read', 'MEMBER') AS is_reader;

Result:

(1 row)

Example: A privilege PostgreSQL does not have (shows an error)

Setup:

CREATE TABLE orders (id LONG PRIMARY KEY);

SELECT has_table_privilege('orders', 'DROP');

Result:

Error: line 1, column 8: has_table_privilege: unrecognized privilege type: "DROP"

Comments and definitions: COMMENT ON, obj_description, pg_get_indexdef

Syntax:

COMMENT ON TABLE table IS 'text' | NULL
COMMENT ON COLUMN table.column IS 'text' | NULL
obj_description(table [, 'pg_class'])       col_description(table, column_number)
pg_get_indexdef(index [, column, pretty])   pg_get_constraintdef(constraint_oid [, pretty])
pg_get_viewdef(view [, pretty])             format_type(type_oid, typmod)
pg_table_is_visible(relation)               pg_get_expr(text, relation)

COMMENT ON keeps a description with a table or a column. It stays after a restart, and \d+ in psql shows it. IS NULL (or IS '') removes it. It needs role ddl, like ALTER TABLE.

• COMMENT ON other objects (an index, a function, a schema) is accepted and says it is not kept. A view is not a table: COMMENT ON TABLE v is error 42809.
• pg_get_partkeydef and pg_get_functiondef give NULL: KDB tables are not PostgreSQL partitioned tables and pg_proc lists no functions. pg_get_serial_sequence('t', 'id') gives public.t_id_seq for a SERIAL column: that is the name of the column's counter, which nextval, currval, setval, lastval and ALTER SEQUENCE ... RESTART WITH n take, so a pg_dump restore that ends with SELECT setval('t_id_seq', max) works as it is. The counter only goes up: a setval below the ids already in the table means their largest + 1.
• Over HTTP, 'orders'::regclass stays the name 'orders'; these functions take the name as well as an oid.

Example: Describe a table and a column

Setup:

CREATE TABLE orders (id LONG PRIMARY KEY, quantity LONG);

COMMENT ON TABLE orders IS 'one row per order';
COMMENT ON COLUMN orders.quantity IS 'shares, never negative';
SELECT obj_description('orders'::regclass, 'pg_class') AS about_table,
       col_description('orders'::regclass, 2) AS about_quantity;

Result:

COMMENT ON TABLE orders IS 'one row per order'

COMMENT

COMMENT ON COLUMN orders.quantity IS 'shares, never negative'

COMMENT

SELECT obj_description(CAST('orders' AS STRING), 'pg_class') AS about_table, col_description(CAST('o

(1 row)

Example: The SQL behind an index, a key and a type

Setup:

CREATE TABLE orders (id LONG PRIMARY KEY, symbol VARCHAR(12));
CREATE INDEX orders_symbol ON orders (symbol);

SELECT pg_get_indexdef('orders_symbol'::regclass) AS index_sql,
       pg_get_indexdef('orders_pkey'::regclass) AS key_sql,
       format_type(1043, 16) AS symbol_type;

Result:

(1 row)

Advisory locks

Syntax:

pg_advisory_lock(key)            take a lock, wait if someone else has it
pg_try_advisory_lock(key)        take it only if it is free (TRUE / FALSE)
pg_advisory_unlock(key)          give it back

An advisory lock is a name your app agrees on. It locks no table and no row - it only stops two copies of your program from doing the same job at the same time.

Say your app closes the trading day at 15:05: it reads the trade log and writes the daily candles. If two copies of the app run, the job must run once. Each copy asks for the lock 918273; the one that gets it does the job, the other one skips it.

• Locks of different databases never meet, even with the same number.
• A session lock is held until it is given back as many times as it was taken, or until the connection closes. Over HTTP, a statement without a session is its own session, so its locks end with the statement.
• Waiting has a limit of 30 seconds (PostgreSQL waits forever). After that the statement fails with a "locked" error; try again later.
• A SELECT pg_advisory_xact_lock(...) sent on its own waits without holding up the database, also inside BEGIN ... COMMIT: other readers and writers go on, and the waiter gets the lock as soon as the holder commits. A lock call inside a procedure or a longer statement list waits in place.
• On the Postgres port, pg_try_* and pg_advisory_unlock* are typed bool before they run, so a driver can read them into a boolean (Go: bool).
• SET statement_timeout also ends the wait (see protection).

Example: Only one copy of the app closes the day (sample data: sample)

SELECT pg_try_advisory_lock(918273) AS i_do_the_job;
SELECT count(*) AS trades_to_sum FROM trade_log;
SELECT pg_advisory_unlock(918273) AS given_back;

Result:

SELECT pg_try_advisory_lock(918273) AS i_do_the_job

(1 row)

SELECT count(*) AS trades_to_sum FROM trade_log

(1 row)

SELECT pg_advisory_unlock(918273) AS given_back

(1 row)

pg_advisory_lock, pg_advisory_lock_shared

Syntax:

pg_advisory_lock(key)
pg_advisory_lock(key1, key2)
pg_advisory_lock_shared(key)
pg_advisory_lock_shared(key1, key2)

Takes a session lock and waits until it is free. The lock is held until pg_advisory_unlock gives it back, or the connection closes.

• Returns: nothing (PostgreSQL calls this void; KDB shows an empty value).
• NULL: a NULL key gives NULL and takes no lock.
• Notes: _shared lets several connections hold the same key at once; a lock without _shared waits for all of them. Taking the same key twice in one connection works and needs two unlocks. Waiting stops after 30 seconds with an error.

Example: Take the day-close lock, do the work, give it back (sample data: sample)

SELECT pg_advisory_lock(918273) AS taken;
SELECT symbol, sum(quantity) AS shares FROM trade_log GROUP BY symbol ORDER BY symbol;
SELECT pg_advisory_unlock(918273) AS given_back;

Result:

SELECT pg_advisory_lock(918273) AS taken

(1 row)

SELECT symbol, sum(quantity) AS shares FROM trade_log GROUP BY symbol ORDER BY symbol

(4 rows)

SELECT pg_advisory_unlock(918273) AS given_back

(1 row)

pg_advisory_xact_lock, pg_advisory_xact_lock_shared

Syntax:

pg_advisory_xact_lock(key)
pg_advisory_xact_lock(key1, key2)
pg_advisory_xact_lock_shared(key)
pg_advisory_xact_lock_shared(key1, key2)

The same, but the lock ends with the transaction. There is no unlock function for it.

• Returns: nothing (void).
• NULL: a NULL key gives NULL and takes no lock.
• Notes: COMMIT and ROLLBACK free it. A statement outside BEGIN ... COMMIT is its own transaction, so the lock ends with that statement. This is the safest kind: a crash in the middle cannot leave the lock behind.

Example: The lock is gone after COMMIT (sample data: sample)

Setup:

BEGIN;
SELECT pg_advisory_xact_lock(918273) AS taken_for_this_transaction;
COMMIT;

SELECT pg_try_advisory_lock(918273) AS free_again;

Result:

(1 row)

pg_try_advisory_lock, pg_try_advisory_lock_shared

Syntax:

pg_try_advisory_lock(key)
pg_try_advisory_lock(key1, key2)
pg_try_advisory_lock_shared(key)
pg_try_advisory_lock_shared(key1, key2)

Takes a session lock only if it is free right now. It never waits.

• Returns: BOOL. TRUE the lock is yours, FALSE someone else holds it.
• NULL: a NULL key gives NULL.
• Notes: this is the one to use for "do this job only if nobody else is doing it". Remember to call pg_advisory_unlock with the same key when the job is done.

Example: Skip the job when another copy already has the lock (sample data: sample)

SELECT pg_try_advisory_lock(918273) AS first_call,
       pg_try_advisory_lock(918273) AS same_connection_again;
SELECT pg_advisory_unlock(918273) AS unlock_1, pg_advisory_unlock(918273) AS unlock_2;

Result:

SELECT pg_try_advisory_lock(918273) AS first_call, pg_try_advisory_lock(918273) AS same_connection_a

(1 row)

SELECT pg_advisory_unlock(918273) AS unlock_1, pg_advisory_unlock(918273) AS unlock_2

(1 row)

pg_try_advisory_xact_lock, pg_try_advisory_xact_lock_shared

Syntax:

pg_try_advisory_xact_lock(key)
pg_try_advisory_xact_lock(key1, key2)
pg_try_advisory_xact_lock_shared(key)
pg_try_advisory_xact_lock_shared(key1, key2)

Takes a transaction lock only if it is free right now. It never waits, and it ends with the transaction.

• Returns: BOOL. TRUE the lock is yours until the transaction ends, FALSE someone else holds it.
• NULL: a NULL key gives NULL.
• Notes: nothing to unlock: COMMIT or ROLLBACK frees it.

Example: Take it only if free, inside a transaction (sample data: sample)

BEGIN;
SELECT pg_try_advisory_xact_lock(918273) AS got_it;
COMMIT;

Result:

BEGIN

transaction started

SELECT pg_try_advisory_xact_lock(918273) AS got_it

(1 row)

COMMIT

transaction committed: 0 change(s) saved

pg_advisory_unlock, pg_advisory_unlock_shared

Syntax:

pg_advisory_unlock(key)
pg_advisory_unlock(key1, key2)
pg_advisory_unlock_shared(key)
pg_advisory_unlock_shared(key1, key2)

Gives back one session lock this connection holds.

• Returns: BOOL. TRUE a lock was given back, FALSE this connection did not hold that key.
• NULL: a NULL key gives NULL.
• Notes: use _shared for a lock taken with pg_advisory_lock_shared. A key taken twice needs two unlocks. Transaction locks (_xact) cannot be unlocked this way; they end with the transaction.

Example: Unlocking a key you do not hold gives FALSE (sample data: sample)

SELECT pg_try_advisory_lock(918273) AS taken;
SELECT pg_advisory_unlock(918273) AS given_back,
       pg_advisory_unlock(918273) AS again,
       pg_advisory_unlock(55) AS never_held;

Result:

SELECT pg_try_advisory_lock(918273) AS taken

(1 row)

SELECT pg_advisory_unlock(918273) AS given_back, pg_advisory_unlock(918273) AS again, pg_advisory_un

(1 row)

pg_advisory_unlock_all

Syntax:

pg_advisory_unlock_all()

Gives back every session lock this connection holds.

• Returns: nothing (void).
• NULL: never NULL.
• Notes: transaction locks are not touched; they end with the transaction. A closing connection does this by itself, so you rarely need to call it.

Example: Clean up after a job (sample data: sample)

SELECT pg_advisory_lock(918273) AS lock_1;
SELECT pg_advisory_lock(918274) AS lock_2;
SELECT pg_advisory_unlock_all() AS all_given_back;
SELECT pg_try_advisory_lock(918273) AS free_again;

Result:

SELECT pg_advisory_lock(918273) AS lock_1

(1 row)

SELECT pg_advisory_lock(918274) AS lock_2

(1 row)

SELECT pg_advisory_unlock_all() AS all_given_back

(1 row)

SELECT pg_try_advisory_lock(918273) AS free_again

(1 row)

Session settings: current_setting, set_config

Syntax:

current_setting(name)               the value, an error when it was never set
current_setting(name, missing_ok)   the value, or NULL when missing_ok is TRUE
set_config(name, value, is_local)   sets it and returns the value
SET name = 'value'                  the same as set_config(name, value, FALSE) (Postgres port)
SET LOCAL name = 'value'            the same as set_config(name, value, TRUE) (Postgres port)

A setting is a named text value your connection keeps. Apps use names of their own with a dot in them, like app.allow_edit, to tell a trigger what the next statement may do.

• A value belongs to one connection (over HTTP, one session). Another connection never sees it.
• Triggers and procedures that your statement sets off see your values. Triggers made DEFERRED run later, outside your connection, so they do not.
• A LOCAL value outside BEGIN ... COMMIT lasts for the call only, as each call is its own transaction. On the Postgres port, SET LOCAL outside a transaction does nothing and sends warning 25P01, as PostgreSQL does.
• After a LOCAL value ends, the name reads '' (empty text), as in PostgreSQL.
• Built-in names answer too: current_setting('TimeZone') is the session's zone (UTC unless SET TIME ZONE or kdb serve --timezone names one).

Example: Set a value, read it back, and ask for one never set (sample data: sample)

SELECT set_config('app.allow_edit', 'on', TRUE) AS set_to,
       current_setting('app.allow_edit') AS read_back,
       current_setting('app.never_set', TRUE) AS missing,
       current_setting('TimeZone') AS time_zone;

Result:

(1 row)

A trigger that lets a DBA through only when the statement asked for it:

CREATE FUNCTION guard_email() RETURNS trigger AS $$
BEGIN
  IF current_setting('app.allow_edit', true) = 'on' THEN
    RETURN NEW;
  END IF;
  IF NEW.email IS DISTINCT FROM OLD.email THEN
    RAISE EXCEPTION 'email is write-once';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- the DBA, on the Postgres port:
BEGIN;
SET LOCAL app.allow_edit = 'on';
UPDATE users SET email = 'new@x.com' WHERE id = 7;
COMMIT;   -- the setting ends here