Sample Header Ad - 728x90

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