SEO

Richard, I was convinced that it was not necessary to provide detailed
SQL to not introduce any noise in the information I was giving to the list.
Anyway you proved right at last, since trying to sample some sql to make
others able to reproduce the problem we were also able to identify the
cause of the problem.

If two insert statements ST1 and ST2, to which are attached triggers
that create a postgreSQL schema with the same name, are executed in two
concurrent transaction T1 and T2 started from two different connections
C1 and C2, then ST2 must wait until T1 has ended (rolled back or
commited) before being executed. By consequence the client executing ST2
hangs until T1 has ended.

The fact that both statement try to create a schema with the same name
make the second one to hang.

Now after that we have found in a PostgreSQL book we have that when in
the scenario above the 2 statements try to insert a row in a table with
the same primary key the second statement hangs.

So in some way this behavior is documented so it is not a bug.
Anyway I am a little surprised by this thing cause I thought that in a
case like this the habgs should happen only at commit/rollback time.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote:

> Anyway I am a little surprised by this thing cause I thought that in a case
> like this the habgs should happen only at commit/rollback time.
>

I think that's because Postgres does not have deferred constraint checks.
They are checked at the execution time, instead of commit time.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
> On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote:
>
> I think that's because Postgres does not have deferred constraint checks.

I believe it does. See
http://www.postgresql.org/docs/8.3/i...nstraints.html
and the DEFERRABLE keyword in CREATE TABLE.

Or am I missing something here?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dennis Brakhane wrote:
> On Thu, Apr 10, 2008 at 4:40 PM, Pavan Deolasee
> <pavan.deolasee@gmail.com> wrote:
>
>> On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti <luberti@archicoop.it> wrote:
>>
>> I think that's because Postgres does not have deferred constraint checks.
>>
>
> I believe it does. See
> http://www.postgresql.org/docs/8.3/i...nstraints.html
> and the DEFERRABLE keyword in CREATE TABLE.
>
> Or am I missing something here?
>
>
As far as I know UNIQUE and CHECK constraints cannot be deferrable; only
FOREIGN KEY constraints can be deferrable. You can use a CONSTRAINT
TRIGGER to emulate others though.

I'm told some other databases can defer UNIQUE constraint checks, but I
haven't the foggiest how that can work in a remotely sane way. Wouldn't
a deferred UNIQUE constraint be useless to the query planner (which
can't trust that the data is really unique right now) and cause
confusing behaviour with scalar subqueries (that might suddenly not
return a single result) and stored procedures that rely on the unique
constraint?

I guess the same thing applies to a deferred foreign key constraint,
really - you can't actually trust it in any context where you're
modifying the data involved. It just seems a lot simpler to think about
the effects of deferred foreign key constraints.

--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Apr 10, 2008 at 11:29 PM, Dennis Brakhane
<brakhane@googlemail.com> wrote:

>
> I believe it does. See
> http://www.postgresql.org/docs/8.3/i...nstraints.html
> and the DEFERRABLE keyword in CREATE TABLE.
>
> Or am I missing something here?
>

Only foreign key contrains checks (and triggers) can be deferred, not the
primary or unique key checks. See the following statement in the same doc
page:

"Currently, only foreign key constraints are affected by this setting. Check
and unique constraints are always effectively not deferrable. Triggers that
are declared as "constraint triggers" are also affected."

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general