BUG #1266: Improper unique constraint / MVCC activities within single transaction

The following bug has been logged online:

Bug reference: 1266
Logged by: Aleksey Fedorchenko

Email address: alexey_f(at)ukr(dot)net

PostgreSQL version: 8.0 Beta

Operating system: MS Windows 2003

Description: Improper unique constraint / MVCC activities within
single transaction

Details:

The following problems were discovered under:
1. postgres (PostgreSQL) 8.0.0beta1 / psql (PostgreSQL) 8.0.0beta1 (native
Win32 release)
2. postgres (PostgreSQL) 7.4.2 / psql (PostgreSQL) 7.4.2 (own CygWin 1.5.5
source based build)

Test tables definition:

/* ======== */
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
);
/* ======== */

Test cases:

/* ======== */
--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;
select * from buggy_uq;
--expect - SUCCESS
--result - ERROR: duplicate key violates unique constraint "buggy_uq_i_key"

--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);
select * from buggy_uq_child;
--expect - ERROR: insert or update on table "buggy_uq_child" violates
foreign key constraint "$1"
--result - ERROR: duplicate key violates unique constraint
"buggy_uq_child_i_key"

--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);
select * from buggy_uq_child;
--expect - ERROR: insert or update on table "buggy_uq_child" violates
foreign key constraint "$1"
--result - SUCCESS
/* ======== */

Notes and description:

As you could you see, the first one is related only to unique constraint
itself while second and third are connected with MVCC.

On case 1.
The result is dependant on values insertion order. For example, if we insert
a set of descendant values (e.g. 10,9,8,...) then it works fine othervise
(e.g. 1,2,3,...) it fails. Due to the standard it sould work fine in both
cases.

On case 2 and 3.
They are the variances of the single problem - it seems that unique
constraint's implicit index is not updated in a moment of value deletion.

In the second case we face with problem that values that have to be
implicitly deleted from the child table with cascade option still exists in
unique index and violates other operation (the shown example slightly
differs from real application scenario).

In the third case we face with problem that values that were explicitly
deleted both from the parent and child tables doesn't exists by itself but
still contained in indecies (success child insertion after deletion of
parent/child records). It violates operation silently only on transaction
commit and followed select returns empty record set.

PS: Possibly i'm wrong and all mentioned isn't a bug but feature however in
other RDBMS systems (Oracle / MSSQL) such scenarios works fine.

On Thu, 23 Sep 2004, PostgreSQL Bugs List wrote:

> /* ======== */
> --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;
> select * from buggy_uq;
> --expect - SUCCESS
> --result - ERROR: duplicate key violates unique constraint "buggy_uq_i_key"

This is a known issue with the unique constraint.

> --case 2 prepare
> --case 3 prepare

I seem to get foreign key violations on both of these on both 7.4.x and
8.0 cvs using the scripts you provided just by running them in psql. I
think you need to give more details about what you were trying.

Stephan!

> This is a known issue with the unique constraint.

Ok. Do you know any plans on this issue fix?

> > --case 2 prepare
> > --case 3 prepare
>
> I seem to get foreign key violations on both of these on both 7.4.x and
> 8.0 cvs using the scripts you provided just by running them in psql. I
> think you need to give more details about what you were trying.

Hmmm....
In the nearest couple of days i'll be able to check this under linux psql 8 beta 2 but under windows it is a repeatable.

What is your target platform? *nix?

On Fri, 24 Sep 2004, Alexey Fedorchenko wrote:

> Stephan!
>
> > This is a known issue with the unique constraint.
>
> Ok. Do you know any plans on this issue fix?
>
> > > --case 2 prepare
> > > --case 3 prepare
> >
> > I seem to get foreign key violations on both of these on both 7.4.x and
> > 8.0 cvs using the scripts you provided just by running them in psql. I
> > think you need to give more details about what you were trying.
>
> Hmmm....
> In the nearest couple of days i'll be able to check this under linux psql 8 beta 2 but under windows it is a repeatable.
>
> What is your target platform? *nix?

I was on linux, although I just installed 8.0b2 dev 3 to my windows box
and tried #2 and still got a success.

Are you sure that the constraint wasn't deferred and/or that you weren't
doing this inside a function? In the former case there's a reading of spec
question for the timing of the actions (are they on the statement or at
check time -- we've done the latter although a rereading implies that we
may have previously read it wrong) and the latter, up until Tom's very
recent patch, any AFTER triggers (or foreign keys) waited until the end of
the original statement from the user to run.

> I was on linux, although I just installed 8.0b2 dev 3 to my windows box
> and tried #2 and still got a success.

Let me guess - did you use psql? I found that mentioned scenarios run successfuly under psql but pgAdmin's console and PHP driven invocation lead to fail (even with own Slackware 10 driven host server under pgsql 8.0.0 beta 2 (seems to be the same as you)).

> Are you sure that the constraint wasn't deferred and/or that you weren't
> doing this inside a function?

As i told, under pgAdmin's console and PHP it fails anyway but psql falls only with function invocation.

> In the former case there's a reading of spec
> question for the timing of the actions (are they on the statement or at
> check time -- we've done the latter although a rereading implies that we
> may have previously read it wrong) and the latter, up until Tom's very
> recent patch, any AFTER triggers (or foreign keys) waited until the end of
> the original statement from the user to run.

I misunderstood this sentence... Do you wanna told me that within single statements batch there could be non-serializable execution? If true then it seems to be a architectual issue (i could expect parallel execution within single sql statement but all constraints have to be checked right after it finished - not before and not after, just at a statement execution finish moment). Otherwise it is a bug anyway, imho.

In attachment you'll find sample scenarios that lead psql to fail under *nix.

NB: Scripts have to be placed at /tmp folder otherwise you'll need to fix check_uq.sh.

Excuse me, i forgot to bind attatcments with previous letter.

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.

On Sun, 26 Sep 2004, Aleksey Fedorchenko wrote:

> As i told, under pgAdmin's console and PHP it fails anyway but psql
> falls only with function invocation.

Hmm, for PHP was it sent as one big string with all the statements or a
separate statements? There's a side issue that I believe you can send
multiple statements in one query string, but they're treated specially if
you do so.

> > In the former case there's a reading of spec
> > question for the timing of the actions (are they on the statement or at
> > check time -- we've done the latter although a rereading implies that we
> > may have previously read it wrong) and the latter, up until Tom's very
> > recent patch, any AFTER triggers (or foreign keys) waited until the end of
> > the original statement from the user to run.
>
> I misunderstood this sentence... Do you wanna told me that within single
> statements batch there could be non-serializable execution? If true then
> it seems to be a architectual issue (i could expect parallel execution
> within single sql statement but all constraints have to be checked right
> after it finished - not before and not after, just at a statement
> execution finish moment). Otherwise it is a bug anyway, imho.

What I'm saying is that, constraints are checked at end of statement. In
current releases (8.0b2 included I believe but not 8.0b3) functions are
considered part of the statement that called them, so the constraints are
check at the end of the full outer statement that called the function.
8.0b3 should change this so that the inner statements are considered
separately. In addition, referential actions are currently (but may not be
in 8.0 final) considered to occur at constraint check time, which means
for a deferred constraint, at end of transaction or when set constraints
is used to make the constraint immediate. The reason the latter may
change is that with rereading the spec we are not sure if that is actually
what the spec intends and are still investigating that.

On Sun, 26 Sep 2004, Stephan Szabo wrote:

> What I'm saying is that, constraints are checked at end of statement. In
> current releases (8.0b2 included I believe but not 8.0b3) functions are
> considered part of the statement that called them, so the constraints are
> check at the end of the full outer statement that called the function.
> 8.0b3 should change this so that the inner statements are considered
> separately. In addition, referential actions are currently (but may not be

And in current sources, functions 2 and 3 error with foreign key
violations rather than unique constraint violations. If the constraint
was deferred you'd still get the unique violations, but we're still
looking to see which error is correct in that case.

> And in current sources, functions 2 and 3 error with foreign key
> violations rather than unique constraint violations. If the constraint
> was deferred you'd still get the unique violations, but we're still
> looking to see which error is correct in that case.

Does it mean that scripts that i sent you yesterday works fine and their results are proper (in mean of proper reaction on improper user activities) and different from described by me in first letter?

Its strange because i tuned SET CONSTRAINTS in different modes but it didn't take any effect - i still have same results!

Did you apply any patches on your pgsql instance? Because i have 3 distribs (7.4.2/8.0.0b1/8.0.0b2) and all fails the same way.

On Mon, 27 Sep 2004, Aleksey Fedorchenko wrote:

> > And in current sources, functions 2 and 3 error with foreign key
> > violations rather than unique constraint violations. If the constraint
> > was deferred you'd still get the unique violations, but we're still
> > looking to see which error is correct in that case.
>
> Does it mean that scripts that i sent you yesterday works fine and their
> results are proper (in mean of proper reaction on improper user
> activities) and different from described by me in first letter?
>
> Its strange because i tuned SET CONSTRAINTS in different modes but it
> didn't take any effect - i still have same results!
>
> Did you apply any patches on your pgsql instance? Because i have 3
> distribs (7.4.2/8.0.0b1/8.0.0b2) and all fails the same way.

IIRC, b2 was before Tom changed the code for doing triggers between
function statements, current sources have that code. So, b3 should have
the change I mentioned which makes the above work (for immediate
constraints).

> IIRC, b2 was before Tom changed the code for doing triggers between
> function statements, current sources have that code. So, b3 should have
> the change I mentioned which makes the above work (for immediate
> constraints).

You're right!
Cases 2 and 3 are processed correctly by b3. Thanks a lot for support.

PS: Do you know when developers team plans to fix case 1 issue?

On Wed, 29 Sep 2004, Aleksey Fedorchenko wrote:

> > IIRC, b2 was before Tom changed the code for doing triggers between
> > function statements, current sources have that code. So, b3 should have
> > the change I mentioned which makes the above work (for immediate
> > constraints).
>
> You're right!
> Cases 2 and 3 are processed correctly by b3. Thanks a lot for support.
>
> PS: Do you know when developers team plans to fix case 1 issue?

Not really. It's been a known issue for a while, but I don't think
anyone's come up with a good attack plan. We could always use help. ;)