Thread: Rule uses wrong value
(I thought I posted this yesterday from Google Groups, but it doesn't
appear to have "taken".)
I'm having a problem with a rule designed to log new rows inserted into
one table. The base table is very volatile; rows are inserted from
various places, including both application code and triggers. Then they
are read out by another application (the table is used as a job queue)
and deleted. My rule attempts to re-record the rows in another table for
audit and debug purposes.
Here's the important bits of the base table:
Table "public.job_queue"
Column |Type |Modifiers
--------+--------+------------------
job_id |integer |not null default
nextval('"job_queue_job_id_seq"'::text)
...
The rule looks like this:
rul_job_queue_trace_log AS
ON INSERT TO job_queue
DO INSERT INTO job_queue_trace (job_id, ...)
VALUES (new.job_id, ...)
It appears that the rule is inserting the row copies into
job_queue_trace with a job_id value that is one higher than the job_id
from the original row. Almost as though it was re-evaluating the
sequence ...
--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jboes@qtm.net
Jeff Boes <jboes@qtm.net> writes:
> It appears that the rule is inserting the row copies into
> job_queue_trace with a job_id value that is one higher than the job_id
> from the original row. Almost as though it was re-evaluating the
> sequence ...
No kidding. A rule is a macro and therefore has the usual risks of
multiple evaluations of arguments.
The only way to do what you want is with a trigger.
regards, tom lane
Tom Lane wrote: >Jeff Boes <jboes@qtm.net> writes: > > >>It appears that the rule is inserting the row copies into >>job_queue_trace with a job_id value that is one higher than the job_id >>from the original row. Almost as though it was re-evaluating the >>sequence ... >> >> > >No kidding. A rule is a macro and therefore has the usual risks of >multiple evaluations of arguments. > >The only way to do what you want is with a trigger. > > regards, tom lane > > But shouldn't "new.job_id" use the value that was already recorded in the original row? I'm not using -- INSERT INTO job_queue_trace (job_id) VALUES (nextval(...)) but INSERT INTO job_queue_trace (job_id) VALUES (new.job_id) Why is the sequence involved? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
Jeff Boes <jboes@nexcerpt.com> writes:
> Tom Lane wrote:
>> No kidding. A rule is a macro and therefore has the usual risks of
>> multiple evaluations of arguments.
> But shouldn't "new.job_id" use the value that was already recorded in
> the original row?
There is no "value that was already recorded in the original row";
if you want to think in those terms you should use a trigger. It's
fundamentally wrong to think of a rule in that way.
In the rule, "new.job_id" is effectively a macro parameter that gets
replaced by the INSERT's corresponding expression, ie, nextval(...).
regards, tom lane
Tom Lane wrote: >Jeff Boes <jboes@nexcerpt.com> writes: > > >>Tom Lane wrote: >> >> >>>No kidding. A rule is a macro and therefore has the usual risks of >>>multiple evaluations of arguments. >>> >>> > > > >>But shouldn't "new.job_id" use the value that was already recorded in >>the original row? >> >> > >There is no "value that was already recorded in the original row"; >if you want to think in those terms you should use a trigger. It's >fundamentally wrong to think of a rule in that way. > >In the rule, "new.job_id" is effectively a macro parameter that gets >replaced by the INSERT's corresponding expression, ie, nextval(...). > > regards, tom lane > > Aha! Well, that certainly wasn't clear from the documentation: http://www.postgresql.org/docs/7.4/static/sql-createrule.html "... the special table names NEW and OLD may be used to refer to values in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules to refer to *the new row being inserted* or updated. OLD is valid in ON UPDATE and ON DELETE rules to refer to the existing row being updated or deleted." To me, "new row" and "old row" imply what's already in the table. On the other hand, I hadn't seen this before: http://www.postgresql.org/docs/7.4/static/rules.html "For INSERT commands, the target list describes the new rows that should go into the result relation. It consists of the *expressions in the VALUES clause* or the ones from the SELECT clause in INSERT ... SELECT. The first step of the rewrite process adds target list entries for any columns that were not assigned to by the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression." -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise