Thread: INSERT RULE QUERY ORDER
When my RULE takes the form of:
CREATE RULE name AS ON INSERT TO table DO INSTEAD ( UPDATE query ; INSERT query) ;
The INSERT query doesn't fire and there is no error. Putting the INSERT first allows them to both fire. Can anyone tell me why? I think it has something to do with *NEW* and *OLD* being initialized differently for an UPDATE than for an INSERT.
I'd like to know what's going on so I can be confident I'm writing my rules correctly.
Thanks all.
-------------------------------------------
jtocci
Fort Wayne, IN
Justin Tocci <jtocci@tlcusa.com> writes:
> When my RULE takes the form of:
> CREATE RULE name AS ON INSERT TO table DO INSTEAD ( UPDATE query ; INSERT
> query) ;
> The INSERT query doesn't fire and there is no error.
What do you mean by "doesn't fire"? I think you'd better provide a
complete example of what you're doing.
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 05, 2003 2:13 PM
To: Justin Tocci
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] INSERT RULE QUERY ORDER
Justin Tocci <jtocci@tlcusa.com> writes:
> When my RULE takes the form of:
> CREATE RULE name AS ON INSERT TO table
> DO INSTEAD ( UPDATE query ; INSERT query);
> The INSERT query doesn't fire and there is no error.
What do you mean by "doesn't fire"? I think you'd better provide a
complete example of what you're doing.
regards, tom lane
------------------------------------------
Thanks for the reply Tom, here's the rule that works:
CREATE RULE tquotehistory_update AS ON UPDATE TO vtquotehistory DO INSTEAD
(
INSERT INTO tquotehistory_log ("ID", "Item", "Quote1", "DemandCost1",
"Quote2", "DemandCost2", "DueDate", "POIntoInmass", "Weeks", "QuoteSent",
"Reference", "Supplier", "TLCProposal", "Counter", "Notes") VALUES
(old."ID", old."Item", old."Quote1", old."DemandCost1", old."Quote2",
old."DemandCost2", old."DueDate", old."POIntoInmass", old."Weeks",
old."QuoteSent", old."Reference", old."Supplier", old."TLCProposal",
old."Counter", old."Notes");
UPDATE tquotehistory SET "Item" = new."Item", "Quote1" = new."Quote1",
"DemandCost1" = new."DemandCost1", "Quote2" = new."Quote2", "DemandCost2" =
new."DemandCost2", "DueDate" = new."DueDate", "POIntoInmass" =
new."POIntoInmass", "Weeks" = new."Weeks", "QuoteSent" = new."QuoteSent",
"Reference" = new."Reference", "Supplier" = new."Supplier", "TLCProposal" =
new."TLCProposal", "Counter" = new."Counter", "Notes" = new."Notes" WHERE
(tquotehistory."ID" = old."ID");
);
Switch the order and the INSERT doesn't insert a record into the log, but
the UPDATE updates and there is no error.
The docs say:
13.4.2. How These Rules Work
...The parse trees found in the actions of the pg_rewrite system catalog are
only templates. Since they can reference the range-table entries for NEW and
OLD, some substitutions have to be made before they can be used. For any
reference to NEW, the target list of the original query is searched for a
corresponding entry. If found, that entry's expression replaces the
reference. Otherwise NEW means the same as OLD (for an UPDATE) or is
replaced by NULL (for an INSERT). Any reference to OLD is replaced by a
reference to the range-table entry which is the result relation.
After we are done applying update rules, we apply view rules to the produced
parse tree(s). Views cannot insert new update actions so there is no need to
apply update rules to the output of view rewriting.
But try as I might I don't understand what exactly this means to me. If it
all boils down to keeping your INSERTs before your UPDATEs that's fine I
guess ,but I'd like to understand it. Plus I'd like to know enough about it
to understand where my DELETEs should go.
-------------------------------------------
jtocci
Fort Wayne, IN
Justin Tocci <jtocci@tlcusa.com> writes:
> Thanks for the reply Tom, here's the rule that works:
> CREATE RULE tquotehistory_update AS ON UPDATE TO vtquotehistory DO INSTEAD
> (
> INSERT INTO tquotehistory_log ("ID", "Item", "Quote1", "DemandCost1",
> "Quote2", "DemandCost2", "DueDate", "POIntoInmass", "Weeks", "QuoteSent",
> "Reference", "Supplier", "TLCProposal", "Counter", "Notes") VALUES
> (old."ID", old."Item", old."Quote1", old."DemandCost1", old."Quote2",
> old."DemandCost2", old."DueDate", old."POIntoInmass", old."Weeks",
> old."QuoteSent", old."Reference", old."Supplier", old."TLCProposal",
> old."Counter", old."Notes");
> UPDATE tquotehistory SET "Item" = new."Item", "Quote1" = new."Quote1",
> "DemandCost1" = new."DemandCost1", "Quote2" = new."Quote2", "DemandCost2" =
> new."DemandCost2", "DueDate" = new."DueDate", "POIntoInmass" =
> new."POIntoInmass", "Weeks" = new."Weeks", "QuoteSent" = new."QuoteSent",
> "Reference" = new."Reference", "Supplier" = new."Supplier", "TLCProposal" =
> new."TLCProposal", "Counter" = new."Counter", "Notes" = new."Notes" WHERE
> (tquotehistory."ID" = old."ID");
> );
> Switch the order and the INSERT doesn't insert a record into the log, but
> the UPDATE updates and there is no error.
Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.
If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.
regards, tom lane
You are correct Tom, vtquotehistory is a view of tquotehistory, and I am
trying to log in this example. Sorry I didn't point that out. The view is a
straight view with no WHERE clause or criteria of any kind. The UPDATE does
cause the OLD record to become updated to whatever changes have been set as
needed in the NEW record. In that sense, the OLD record is discarded, but my
code sometimes assumes the keyword OLD is still a valid reference even after
an UPDATE. Is that my problem?
ARGGHH!!! I just re-read 9.2.1. Read Committed Isolation Level and I am
screwed! It was my understanding that all the queries in parenthesis in a
RULE were evaluated within a transaction, and that that meant that all
queries saw the same snapshot of the database and changes were committed all
at once at the end. I see now that queries within a transaction are only
ever free from seeing the COMMITs of _other_ transactions, and even that is
only for SERIALIZABLE transactions.
I've got over twenty rules with multiple updates within them that could be
affecting each other in ways I haven't evaluated. I've got my work cut out
for me. You're a lifesaver Tom, thanks.
-------------------------------------------
jtocci
Fort Wayne, IN
PS - I can't abandon my beautiful rules for triggers just yet :-) With only
five pages of code, all rules, I've built a replacement for the DOS
application we use (soon to be 'used') to keep track of inventory,
shipping/receiving, purchasing and manufacturing('build' from a bill of
materials).
-------------------------------------------
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.
If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.
regards, tom lane