Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL.
INSERT INTO "table" ("column") VALUES ('ABC');
SELECT pg_get_serial_sequence('table', 'tableid') AS seq;
SELECT CURRVAL('public.table_tableid_seq') AS ins_id;
But the use of the Session mode of PgBouncer proved to be extremely problematic for us, with several operations per second, the Pool easily reached the capacity of connections, so we switched to PostgreSQL 12 with PgBouncer in Transaction mode.
One problem that has begun to occur with this mode is that using operations that require being in the same session does not work, not allowing to use CURRVAL.
What is the best alternative to continue working in Transaction mode and get the ID after INSERT, without the risk of returning the ID generated by the same operation by another user?
An important detail is that we use a PHP Framework that does not allow to add an INSERT ... RETURNING for each operation.
Best Answer
PostgreSQL creates a sequence by default per table. e.g actor_id_seq for the actor table on the column id.
So, this will return the last inserted id value:
The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.