Get the last ID after INSERT the PostgreSQL database with PgBouncer
0
votes
1
answer
4352
views
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.
Asked by Tom
(438 rep)
Oct 21, 2020, 06:42 PM
Last activity: Apr 13, 2022, 09:00 PM
Last activity: Apr 13, 2022, 09:00 PM