pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew Perrin <andrew_perrin@unc.edu>
To: Tim Perdue <tim@perdue.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule/currval() issue
Date: Wed, 14 Mar 2001 13:04:42 -0500
Message-ID: <3AAFB2BA.D14649F1@unc.edu> (raw)
References: <20010314115015.R670@mail.perdue.net>

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



view thread (7+ messages)  latest in thread

Message-ID: <3AAFB2BA.D14649F1@unc.edu>
Permalink:  ../3AAFB2BA.D14649F1@unc.edu/
Also on:    postgresql.org/message-id/3AAFB2BA.D14649F1@unc.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: andrew_perrin@unc.edu, tim@perdue.net
  Subject: Re: Rule/currval() issue
  In-Reply-To: <3AAFB2BA.D14649F1@unc.edu>
  References: <20010314115015.R670@mail.perdue.net> <3AAFB2BA.D14649F1@unc.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox