Re: BUG #1266: Improper unique constraint / MVCCactivitieswithin single transaction

> 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.