Re: referential integrity problem

On Sun, 17 Feb 2002, Joseph Artsimovich wrote:

> Here is my problem:
>
> CREATE TABLE users (
> id SERIAL PRIMARY KEY
> );
>
> CREATE TABLE orders (
> id SERIAL PRIMARY KEY,
> user_id INT NOT NULL REFERENCES users ON DELETE CASCADE
> );
>
> CREATE TABLE orders_log (
> order_id INT NOT NULL REFERENCES orders ON DELETE CASCADE,
> by_user INT REFERENCES users ON DELETE SET NULL
> );
>
> Now suppose i do:
>
> INSERT INTO users DEFAULT VALUES;
> INSERT INTO orders (user_id) VALUES (currval('users_id_seq'));
> INSERT INTO orders_log (order_id, by_user) VALUES (currval('orders_id_seq'),
> currval('users_id_seq'));
> DELETE FROM users WHERE id=currval('users_id_seq');
>
> That last delete gives me a referential integrity violation error.
> I've figured out that if I mark the by_user reference as INITIALLY DEFERRED,
> then it works fine. But I don't understand why it refuses to work as is.
> I use PostgreSQL 7.1.3

I think this is possibly the known bug where intermediate states can
sometimes been seen by the referential integrity constraints. I believe
that part of a patch I'd sent to -patches a while back would probably
correct the situtation. I didn't try applying it to 7.1, but it probably
would apply. You can probably find it in the archives, or I can try to
find it and send it if you can't.