why it doesn't work? referential integrity

"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

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

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

"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

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

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

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?

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

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