By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Rule uses wrong value

P: n/a
(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 <>< jb***@qtm.net
Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Jeff Boes <jb***@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
Tom Lane wrote:
Jeff Boes <jb***@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3

P: n/a
Jeff Boes <jb***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #4

P: n/a
Tom Lane wrote:
Jeff Boes <jb***@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/s...reaterule.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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.