[HACKERS] Multiple-statement Rules Incompatible With Constraints

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[HACKERS] Multiple-statement Rules Incompatible With Constraints

Curt Sampson

I've got a table I've split into two, a portion that can be modified
under normal circumstances, and a portion that can't be. (In a testing
mode they both can be, but in production the user IDs doing the work
don't have update or delete access on the immutable portion. BTW, I'm
open to better ways of doing this.)

So it's along the lines of

     CREATE TABLE offer_immutable (offer_id serial PRIMARY KEY );
     CREATE TABLE offer_mutable (
  offer_id int PRIMARY KEY REFERENCES offer_immutable);
     ALTER TABLE offer_immutable ADD CONSTRAINT offer_immutable_offer_id_fkey
  FOREIGN KEY (offer_id) REFERENCES offer_mutable
  DEFERRABLE INITIALLY DEFERRED;
     CREATE VIEW offer AS
  SELECT * FROM offer_immutable NATURAL JOIN offer_mutable;

In a transaction, when I try to commit, this does not work:

     CREATE OR REPLACE RULE offer_delete AS
  ON DELETE TO offer DO INSTEAD (
     DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id;
     DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id;
  );

It gives me:

     ERROR:  insert or update on table "offer_immutable" violates foreign
     key constraint "offer_immutable_offer_id_fkey"
     DETAIL:  Key (offer_id)=(77) is not present in table "offer_mutable".

On the other hand, if I use this instead:

     CREATE OR REPLACE FUNCTION offer_delete(int) RETURNS VOID AS $$
  DELETE FROM offer_mutable WHERE offer_id = $1;
  DELETE FROM offer_immutable WHERE offer_id = $1;
     $$ LANGUAGE 'SQL' VOLATILE SECURITY INVOKER;

     CREATE OR REPLACE RULE offer_delete AS
  ON DELETE TO offer DO INSTEAD SELECT offer_delete(OLD.offer_id);

It works fine.

I can also just do the two separate deletes in a transaction, and it
works ok.

     BEGIN;
     DELETE FROM offer_mutable WHERE offer_id = 123;
     DELETE FROM offer_immutable WHERE offer_id = 123;
     COMMIT;

Bug?

cjs
--
Curt Sampson  <[hidden email]>   +81 90 7737 2974   http://www.NetBSD.org
      Make up enjoying your city life...produced by BIC CAMERA

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Multiple-statement Rules Incompatible With Constraints

Tom Lane-2
Curt Sampson <[hidden email]> writes:
>      CREATE VIEW offer AS
>   SELECT * FROM offer_immutable NATURAL JOIN offer_mutable;

> In a transaction, when I try to commit, this does not work:

>      CREATE OR REPLACE RULE offer_delete AS
>   ON DELETE TO offer DO INSTEAD (
>      DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id;
>      DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id;
>   );

Same old same old: as soon as you've deleted from offer_mutable, there
is no row in the view with the given offer_id; and since OLD is a macro
for the view, the second delete finds nothing to do.

You might be able to fix this by making the view an outer join, and
deleting from the nullable side first.  The whole data structure seems a
tad weird though ...

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] Multiple-statement Rules Incompatible With Constraints

Curt Sampson
On Fri, 27 May 2005, Tom Lane wrote:

> Same old same old: as soon as you've deleted from offer_mutable, there
> is no row in the view with the given offer_id; and since OLD is a macro
> for the view, the second delete finds nothing to do.

Oh, now I'm starting to see how this behavior does make sense...unwanted
as it is in my case. Maybe I find it a bit weird that the sense of
OLD.something can change within a rule, though.

> You might be able to fix this by making the view an outer join, and
> deleting from the nullable side first.

Yuck. Though I'm already deep in "yuck" anyway.

> The whole data structure seems a tad weird though ...

More than a tad. I'm not happy with this thing I've come up with. I'm
really starting to think that I took the wrong path. What's a better way
to express my intention?

Perhaps the thing to do is go back to one table, and have a trigger that
throws an exception if you try to update the "non-updatable" columns.
(And only the test account can do deletes--if I need to do an update
during testing, delete and insert is the answer.) What do you think?

But as well, this is really also another instance of an area where
functions work differently from things not in functions (and I tend to
think that the way things work in functions in most of these cases is
right).

cjs
--
Curt Sampson  <[hidden email]>   +81 90 7737 2974   http://www.NetBSD.org
      Make up enjoying your city life...produced by BIC CAMERA

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly