Thread: Re: BUG #1266: Improper unique constraint / MVCC

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
PHPdriven invocation lead to fail (even with own Slackware 10 driven host server under pgsql 8.0.0 beta 2 (seems to be
thesame 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-serializableexecution? If true then it seems to be a architectual issue (i could expect parallel execution within
singlesql statement but all constraints have to be checked right after it finished - not before and not after, just at
astatement 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.