Postgres Pro
Downloads
Home   >   mailing lists

Thread: why it doesn't work? referential integrity

why it doesn't work? referential integrity

From
"Pavel Stehule"
Date:
Hello

I found strange postgresql's behave. Can somebody explain it?

Regards
Pavel Stehule

CREATE TABLE users (
  id integer NOT NULL,
  name VARCHAR NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO users VALUES (1, 'Jozko');
INSERT INTO users VALUES (2, 'Ferko');
INSERT INTO users VALUES (3, 'Samko');

CREATE TABLE tasks (
  id integer NOT NULL,
  owner INT REFERENCES  users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
  PRIMARY KEY (id)
);
INSERT INTO tasks VALUES (1,1,NULL,NULL);
INSERT INTO tasks VALUES (2,2,2,NULL);
INSERT INTO tasks VALUES (3,3,3,3);

DELETE FROM users WHERE id = 1; -- works simple
DELETE FROM users WHERE id = 2; -- works ok
DELETE FROM users WHERE id = 3; -- doesn't work, why?

ERROR:  insert or update on table "tasks" violates foreign key
constraint "tasks_checked_by_fkey"
DETAIL:  Key (checked_by)=(3) is not present in table "users".
CONTEXT:  SQL statement "UPDATE ONLY "public"."tasks" SET "worker" =
NULL WHERE $1 OPERATOR(pg_catalog.=) "worker""

Re: why it doesn't work? referential integrity

From
Gregory Stark
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:

>   checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,

> CONTEXT:  SQL statement "UPDATE ONLY "public"."tasks" SET "worker" =
> NULL WHERE $1 OPERATOR(pg_catalog.=) "worker""

This says you mistyped the constraint above to refer to tasks(worker) instead
of users(id). Did you?

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Re: why it doesn't work? referential integrity

From
"Pavel Stehule"
Date:
2007/8/11, Gregory Stark <stark@enterprisedb.com>:
>
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>
> >   checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
>
> > CONTEXT:  SQL statement "UPDATE ONLY "public"."tasks" SET "worker" =
> > NULL WHERE $1 OPERATOR(pg_catalog.=) "worker""
>
> This says you mistyped the constraint above to refer to tasks(worker) instead
> of users(id). Did you?
>
> --

Im sorry. I don't understand.  It's look like wrong evaluation order:

1. delete from users
2. update tab set col = NULL

insead

1. update ... -- remove references
2. delete from users ...

Pavel Stehule



>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>
>

Re: why it doesn't work? referential integrity

From
Gregory Stark
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:

> 2007/8/11, Gregory Stark <stark@enterprisedb.com>:
>>
>> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>>
>> >   checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
>>
>> > CONTEXT:  SQL statement "UPDATE ONLY "public"."tasks" SET "worker" =
>> > NULL WHERE $1 OPERATOR(pg_catalog.=) "worker""
>>
>> This says you mistyped the constraint above to refer to tasks(worker) instead
>> of users(id). Did you?
>>
>> --
>
> Im sorry. I don't understand.  It's look like wrong evaluation order:
>
> 1. delete from users

There's no delete from users in evidence here.

Check how your constraints are actually defined, it doesn't look like they're
defined they way you claimed they are


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Re: why it doesn't work? referential integrity

From
Gregory Stark
Date:
Sorry, I reread your original post. My initial reading was wrong.

To make this work I think you'll need to set these constraints to be deferred.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Re: why it doesn't work? referential integrity

From
"Pavel Stehule"
Date:
2007/8/11, Gregory Stark <stark@enterprisedb.com>:
>
> Sorry, I reread your original post. My initial reading was wrong.
>
> To make this work I think you'll need to set these constraints to be deferred.
>
> --

it works with deferred constraints .  It's strange, it works with two
columns but dowsn't work with three columns (without deferred c.).

Pavel

Re: why it doesn't work? referential integrity

From
Stephan Szabo
Date:
On Sat, 11 Aug 2007, Pavel Stehule wrote:

> Hello
>
> I found strange postgresql's behave. Can somebody explain it?

There's a bug since it should work for any number, but we've likely missed
something. I'm not sure why 2 references work, as I'd expect it to stop
working after 1 with the likely causes, but one of the constraint checks
is happening before the row is finished being updated.

I don't think it'll help for this case (since it revolved around multiple
tables), but could you try the patch from
 http://archives.postgresql.org/pgsql-bugs/2007-05/msg00177.php
to see if it helps this case?

Re: why it doesn't work? referential integrity

From
"Pavel Stehule"
Date:
2007/8/11, Stephan Szabo <sszabo@megazone.bigpanda.com>:
> On Sat, 11 Aug 2007, Pavel Stehule wrote:
>
> > Hello
> >
> > I found strange postgresql's behave. Can somebody explain it?
>
> There's a bug since it should work for any number, but we've likely missed
> something. I'm not sure why 2 references work, as I'd expect it to stop
> working after 1 with the likely causes, but one of the constraint checks
> is happening before the row is finished being updated.
>
> I don't think it'll help for this case (since it revolved around multiple
> tables), but could you try the patch from
>  http://archives.postgresql.org/pgsql-bugs/2007-05/msg00177.php
> to see if it helps this case?
>

This patch doesn't help. I'll report it as bug.

Regards
Pavel Stehule

Re: why it doesn't work? referential integrity

From
Janning Vygen
Date:
On Saturday 11 August 2007 12:28:45 Pavel Stehule wrote:
> Hello
>
> I found strange postgresql's behave. Can somebody explain it?
>
> Regards
> Pavel Stehule
>
> CREATE TABLE users (
>   id integer NOT NULL,
>   name VARCHAR NOT NULL,
>   PRIMARY KEY (id)
> );
>
> INSERT INTO users VALUES (1, 'Jozko');
> INSERT INTO users VALUES (2, 'Ferko');
> INSERT INTO users VALUES (3, 'Samko');
>
> CREATE TABLE tasks (
>   id integer NOT NULL,
>   owner INT REFERENCES  users (id) ON UPDATE CASCADE ON DELETE SET NULL,
>   worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL,
>   checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET
> NULL, PRIMARY KEY (id)
> );
> INSERT INTO tasks VALUES (1,1,NULL,NULL);
> INSERT INTO tasks VALUES (2,2,2,NULL);
> INSERT INTO tasks VALUES (3,3,3,3);
>
> DELETE FROM users WHERE id = 1; -- works simple
> DELETE FROM users WHERE id = 2; -- works ok
> DELETE FROM users WHERE id = 3; -- doesn't work, why?
>
> ERROR:  insert or update on table "tasks" violates foreign key
> constraint "tasks_checked_by_fkey"
> DETAIL:  Key (checked_by)=(3) is not present in table "users".
> CONTEXT:  SQL statement "UPDATE ONLY "public"."tasks" SET "worker" =
> NULL WHERE $1 OPERATOR(pg_catalog.=) "worker""

looks strange to me too, but i never had foreign keys to the same table.
it works if you define your chekced_by FK deferrable with

checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL
DEFERRABLE INITIALLY DEFERRED,

it seams that postgresql does its job in a procedural way instead of
relational.

kind regards,
Janning

Re: why it doesn't work? referential integrity

From
"Pavel Stehule"
Date:
>
> looks strange to me too, but i never had foreign keys to the same table.
> it works if you define your chekced_by FK deferrable with
>
> checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL
> DEFERRABLE INITIALLY DEFERRED,
>
> it seams that postgresql does its job in a procedural way instead of
> relational.
>

It is solved. It was PostgreSQL bug repaired
http://archives.postgresql.org/pgsql-committers/2007-08/msg00207.php
Thank you
nice a day
Pavel Stehule