Thread: Rule/currval() issue

This is related to the plpgsql project I was working on this morning. I'm
trying to create a rule, so that when a row is inserted into a certain table,
we also create a row over in a "counter table". The problem lies in getting
the primary key value (from the sequence) so it can be inserted in that
related table.

I tried a couple different approaches. Neither works.

artifact_group_list is a table where the primary key, group_artifact_id is
SERIAL type. When I insert a row, I want to get that new value in my rule.

--
--  Define a rule so when you create a new ArtifactType
--  You automatically create a related row over in the counters table
--
CREATE RULE artifactgroup_insert_agg AS   ON INSERT TO artifact_group_list       DO INSERT INTO
artifact_counts_agg(group_artifact_id,count,open_count)           VALUES
(currval('artifact_grou_group_artifac_seq'),0,0);

I get this:

ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session


If I write the rule this way:

CREATE RULE artifactgroup_insert_agg AS   ON INSERT TO artifact_group_list       DO INSERT INTO
artifact_counts_agg(group_artifact_id,count,open_count)           VALUES (new.group_artifact_id,0,0);
 

...it doesn't fail with an error, but the sequence increments twice.

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems

Entirely untested, but how about replacing currval() in your first try
with nextval()?  My theory is that the compilation of the function is
happening before the INSERT happens; therefore the sequence hasn't been
incremented yet; therefore there's no currval() for this backend
instance. If you use nextval(), you'll get the next increment, which
should be appropriate.

As I think about it, this could have record-locking implications in a
heavy use environment, since the possibility exists of another INSERT
between the nextval() and the INSERT in this situation - I don't know if
that's actually an issue, or if there would be a way around it.

Andy Perrin

Tim Perdue wrote:
> 
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (from the sequence) so it can be inserted in that
> related table.
> 
> I tried a couple different approaches. Neither works.
> 
> artifact_group_list is a table where the primary key, group_artifact_id is
> SERIAL type. When I insert a row, I want to get that new value in my rule.
> 
> --
> --  Define a rule so when you create a new ArtifactType
> --  You automatically create a related row over in the counters table
> --
> CREATE RULE artifactgroup_insert_agg AS
>     ON INSERT TO artifact_group_list
>         DO INSERT INTO
>             artifact_counts_agg (group_artifact_id,count,open_count)
>             VALUES (currval('artifact_grou_group_artifac_seq'),0,0);
> 
> I get this:
> 
> ERROR: artifact_grou_group_artifac_seq.currval is not yet defined in this session
> 
> If I write the rule this way:
> 
> CREATE RULE artifactgroup_insert_agg AS
>     ON INSERT TO artifact_group_list
>         DO INSERT INTO
>             artifact_counts_agg (group_artifact_id,count,open_count)
>             VALUES (new.group_artifact_id,0,0);
> 
> ...it doesn't fail with an error, but the sequence increments twice.
> 
> Tim
> 
> --
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
--------------------------------------------------------------
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * andrew_perrin@unc.edu

Tim Perdue <tim@perdue.net> writes:
> This is related to the plpgsql project I was working on this morning. I'm
> trying to create a rule, so that when a row is inserted into a certain table,
> we also create a row over in a "counter table". The problem lies in getting
> the primary key value (from the sequence) so it can be inserted in that
> related table.

You probably should be using a trigger, not a rule at all.
        regards, tom lane

On Wed, Mar 14, 2001 at 01:09:18PM -0500, Tom Lane wrote:
> Tim Perdue <tim@perdue.net> writes:
> > This is related to the plpgsql project I was working on this morning. I'm
> > trying to create a rule, so that when a row is inserted into a certain table,
> > we also create a row over in a "counter table". The problem lies in getting
> > the primary key value (from the sequence) so it can be inserted in that
> > related table.
> 
> You probably should be using a trigger, not a rule at all.

OK - so another rule like this one, is probably ill-advised as well? It seems
a lot easier than going into the triggers:

CREATE RULE forum_delete_agg AS   ON DELETE TO forum   DO UPDATE forum_agg_msg_count SET count=count-1       WHERE
group_forum_id=old.group_forum_id;

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems

Tim Perdue <tim@perdue.net> writes:
>> You probably should be using a trigger, not a rule at all.

> OK - so another rule like this one, is probably ill-advised as well? It seems
> a lot easier than going into the triggers:

> CREATE RULE forum_delete_agg AS
>     ON DELETE TO forum
>     DO UPDATE forum_agg_msg_count SET count=count-1
>         WHERE group_forum_id=old.group_forum_id;

That seems OK offhand, although it's hard to guess whether it will be
more or less efficient than a trigger.
        regards, tom lane

Tom,

You indicate trigger, rather than rule.  Going by Momjian's book, he
indicates that rules are "...ideal for when the action affects other
tables."  Can you clarify why you would use a trigger for this?  I'm asking
because I have no clue how to use rules or triggers, but need one or the
other to modify a second table on inserts/deletes to the first table.  I'd
like to make the best choice first if possible.

Thanks,
Rob

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, March 14, 2001 11:09 AM
> To: Tim Perdue
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Rule/currval() issue 
> 
> 
> Tim Perdue <tim@perdue.net> writes:
> > This is related to the plpgsql project I was working on 
> this morning. I'm
> > trying to create a rule, so that when a row is inserted 
> into a certain table,
> > we also create a row over in a "counter table". The problem 
> lies in getting
> > the primary key value (from the sequence) so it can be 
> inserted in that
> > related table.
> 
> You probably should be using a trigger, not a rule at all.
> 
>             regards, tom lane
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to 
> majordomo@postgresql.org)
> 

"Creager, Robert S" <CreagRS@LOUISVILLE.STORTEK.COM> writes:
> You indicate trigger, rather than rule.  Going by Momjian's book, he
> indicates that rules are "...ideal for when the action affects other
> tables."  Can you clarify why you would use a trigger for this?

Primarily because there's a need to get at the default values that will
be computed for the inserted tuple (ie, the serial number it will be
assigned).  An ON INSERT trigger has access to those values because it's
handed the fully-constructed tuple.  A rule does not.  QED.

My take on the rule vs. trigger issue is a little different from
Bruce's.  To me, a trigger is ideal for actions that you want to drive
off insertion/deletion/update of individual tuples --- ie, all that you
need to look at to know what to do is the single tuple being processed.
However, that's also a trigger's weak spot: it will be fired again,
separately, for every inserted/deleted/updated tuple.  In contrast,
a rule specifies a transformation of the original query, which makes it
good for bulk operations.

For example: suppose I'm deleting a whole ton of tuples in table A, say
delete from a where a.date < '2000-01-01'

and my application logic dictates that associated tuples in table B also
go away.  If I make that happen with a trigger then I'll be executing
something like
delete from b where b.id = old.id

separately for each deleted A tuple.  That amounts to a nested-loop join
between A and B, since B is scanned separately (hopefully with an
indexscan!) for each A tuple.  On the other hand I could write a rule
on delete to a do    delete from b where b.id = old.id

This will expand my above query into
delete from b where b.id = a.id and a.date < '2000-01-01';delete from a where a.date < '2000-01-01';

Now the planner can turn the B delete into a merge or hash join between
A and B.  For large numbers of tuples that could make for a huge
speedup.

So basically, rules are good for specifying bulk operations between
related tables, whereas a trigger is good for more "retail" kinds of
things.  Also, I think a trigger is a lot easier to understand, even
if there's a little more learning curve involved to write one (because
you also have to know some plpgsql).  The transformational nature of
rules is harder to get a handle on; they seem trivial but they're really
not.
        regards, tom lane