• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CodeIgniter with PostgreSQL and PgBouncer in Transaction mode

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 db->insert_id('table', 'pk_column').

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, which is used by insert_id(), 

Example of Insert created by CodeIgniter 3:
INSERT INTO "table" ("column") VALUES ('ABC');
SELECT pg_get_serial_sequence('table', 'tableid') AS seq;
SELECT CURRVAL('public.table_tableid_seq') AS ins_id;

This code will trigger the error:
Quote:currval has not yet been defined this session

Is there any configuration that must be done in CodeIgniter to work with PgBouncer in transaction mode?

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.