php Postgresql pg_query(): duplicate key value violates unique constraint

Question

We are working on refactoring our Framework Postgresql driver to allow for transactions. In the process, we have introduced some issues resulting in the following error

pg_query(): duplicate key value violates unique constraint DETAIL: Key (id)=(1) already exists

link to travis test with some more details https://travis-ci.org/photodude/database/jobs/175596877

The relevant portion of the driver having issues is at this link

https://github.com/joomla-framework/database/blob/master/src/Postgresql/PostgresqlDriver.php#L711-L819

The related test(s) with the issue is

https://github.com/joomla-framework/database/blob/master/Tests/DriverPostgresqlTest.php#L1116-L1163

I get that the table sequence is somehow messed up, but I'm at a loss on why the table sequence is messed up or even just how to fix the code so the tests function correctly.

note: I believe this failure is related to prepared and unprepared statements

Answer 1

At line 519 restarting sequence and truncate table which looks ok, but if that runs inside a rollbacked transaction, truncate will not happen, but sequence restart will

    Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

see:

s1=> create table test1 ( id serial primary key, a text not null);
CREATE TABLE
s1=> \d
             List of relations
 Schema |     Name     |   Type   | Owner  
--------+--------------+----------+--------
 public | test1        | table    | albert
 public | test1_id_seq | sequence | albert
(2 rows)

s1=> insert into test1(a) values ('apple');
INSERT 0 1
s1=> select * from test1;
 id |   a   
----+-------
  1 | apple
(1 row)

s1=> select * from test1_id_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 test1_id_seq  |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)

s1=> insert into test1(a) values ('bannana');
INSERT 0 1
s1=> select * from test1;
 id |    a    
----+---------
  1 | apple
  2 | bannana
(2 rows)

s1=> insert into test1(a) values ('bannana');
INSERT 0 1
s1=> select * from test1;
 id |    a    
----+---------
  1 | apple
  2 | bannana
  3 | bannana
(3 rows)

s1=> select * from test1_id_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 test1_id_seq  |          3 |           1 |            1 | 9223372036854775807 |         1 |           1 |      30 | f         | t
(1 row)

s1=> begin;
BEGIN
s1=> alter sequence test1_id_seq RESTART WITH 1;
ALTER SEQUENCE
s1=> truncate table test1;
TRUNCATE TABLE
s1=> rollback;
ROLLBACK
s1=> select * from test1_id_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 test1_id_seq  |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 row)

s1=> select * from test1;
 id |    a    
----+---------
  1 | apple
  2 | bannana
  3 | bannana
(3 rows)

s1=>

Answer 2

We are still not sure of the root cause but the whole issue is related to the unit tests. something is/was globally restarting the table sequence even when just the one failing test was checked. We found a way around the issue but are still looking for the root cause.

We also found we needed to improve the tearDown() methods for all the driver tests.