php Postgresql pg_query():重复键值违反了唯一性约束
问题描述:
我们正在重构我们的Framework Postgresql驱动程序以允许进行事务处理。在这个过程中,我们已经介绍了导致以下错误php Postgresql pg_query():重复键值违反了唯一性约束
pg_query(): duplicate key value violates unique constraint DETAIL: Key (id)=(1) already exists
链接一些问题,一些细节 https://travis-ci.org/photodude/database/jobs/175596877
有问题的驱动程序的有关部分在此链接
特拉维斯测试相关的测试(S)这个问题是
https://github.com/joomla-framework/database/blob/master/Tests/DriverPostgresqlTest.php#L1116-L1163
我拿到表序列以某种方式搞砸了,但我在为什么表顺序弄乱了,甚至只是如何修复代码,以便测试正常功能丧失。
注:我相信这个故障有关的准备和无准备的语句
答
在线路519重新启动序列和truncate table看起来不错,但如果一个rollbacked事务中运行,截断不会发生,但序列重启will
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=>
答
我们仍然不确定根本原因,但整个问题都与单元测试有关。即使仅仅检查了一个失败的测试,也有/正在全局重新启动表序列。我们找到了解决问题的方法,但仍在寻找根本原因。
我们还发现我们需要改进所有驱动程序测试的tearDown()方法。
“TRUNCATE ... RESTART IDENTITY”也是如此。 –
这是一个有趣的笔记。但我不确定它适用于这里的具体失败。隔离测试我仍然失败。这似乎与使用预处理语句有关,因为它们只是运行我们没有使用事务的执行测试。 –