php Postgresql pg_query():重复键值违反唯一约束

Question

我们正在重构框架Postgresql驱动程序以允许事务。在此过程中，我们引入了一些问题，导致以下错误

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

链接到特拉维斯测试与一些更多的细节https://travis-ci.org/photodude/database/jobs/175596877

有问题的驱动程序的相关部分在这个链接

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

与该问题相关的测试为

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

我得到的表序列是搞砸了，但我在为什么表序列搞砸了，甚至只是如何修复代码，使测试功能正确的损失。

注意:我认为这个失败与准备好的和未准备好的语句有关

Answer 1

在第519行重新启动序列和截断表，这看起来不错，但如果在回滚事务中运行，将不会发生截断，但序列将重新启动

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

:

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

我们仍然不确定根本原因，但整个问题都与单元测试有关。某些东西正在/正在全局重新启动表序列，即使只检查了一个失败的测试。我们找到了解决问题的方法，但仍在寻找根本原因。

我们还发现需要改进所有驱动程序测试的tearDown()方法。