Excuse me, i forgot to bind attachments with previous letter.
PS: In pgsql_bug_result.txt there is a output that shows behavior that i described in
first message.
PPS: check_uq.sh is the script to execute.
#!/bin/sh
clear
su - postgres -c "/usr/local/pgsql/bin/createdb test_uq" > /dev/null
su - postgres -c "/usr/local/pgsql/bin/psql -d test_uq -f /tmp/pgsql_bug_create.sql"
1>/dev/null 2>/dev/null
su - postgres -c "/usr/local/pgsql/bin/psql -d test_uq -f /tmp/pgsql_bug_testcase.sql"
su - postgres -c "/usr/local/pgsql/bin/dropdb test_uq" > /dev/null
create table buggy_uq (
i integer unique not null
);
create table buggy_uq_parent (
i integer primary key
);
create table buggy_uq_child (
i integer unique references buggy_uq_parent on delete cascade
);
create or replace function bug_case1()
returns void as '
--case 1 prepare
delete from buggy_uq;
insert into buggy_uq values (1);
insert into buggy_uq values (2);
--case 1 test
update buggy_uq set i = i + 1;
' language sql;
create or replace function bug_case2()
returns void as '
--case 2 prepare
delete from buggy_uq_child;
delete from buggy_uq_parent;
insert into buggy_uq_parent values (1);
insert into buggy_uq_child values (1);
--case 2 test
delete from buggy_uq_parent;
insert into buggy_uq_child values (1);
' language sql;
create or replace function bug_case3()
returns void as '
--case 3 prepare
delete from buggy_uq_child;
delete from buggy_uq_parent;
insert into buggy_uq_parent values (1);
insert into buggy_uq_child values (1);
delete from buggy_uq_child;
delete from buggy_uq_parent;
--case 3 test
insert into buggy_uq_child values (1);
' language sql;select bug_case1();
select bug_case2();
select bug_case3();
psql:/tmp/pgsql_bug_testcase.sql:1: ERROR: duplicate key violates unique constraint
"buggy_uq_i_key"
CONTEXT: SQL function "bug_case1" statement 4
psql:/tmp/pgsql_bug_testcase.sql:3: ERROR: duplicate key violates unique constraint
"buggy_uq_child_i_key"
CONTEXT: SQL function "bug_case2" statement 6
bug_case3
-----------
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
