Обсуждение: 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