Skip site navigation (1) Skip section navigation (2)

Re: Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)

From: "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com>
To: "Jim Nasby" <jnasby(at)pervasive(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)
Date: 2006-08-11 13:13:08
Message-ID: e431ff4c0608110613ne0ebf3fs7ce9ffb2fd6fd00c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
yes, I've found some words about similar sutuation on Varlena:

"... The SERIAL column NEW.job_id is effectively replaced by
nextval(...) and the nextval() function is called twice--once for the
original row and once for that traced row."
(http://www.varlena.com/GeneralBits/101.php)

What am I doing? I've just wanted to introduce the rule that allows to
retrieve "last inserted ID" on every INSERT executed.

I'm afraid that this is not expected behaviour. OK, let it be so for
some time...

Simple workaround is: to use currval() instead of NEW.id.

On 8/8/06, Jim Nasby <jnasby(at)pervasive(dot)com> wrote:
> More of a gotcha than a bug... basically, your select rule is hitting
> the sequence again. I think there's a section in the rules chapter
> that talks about this. GeneralBits might also have info.
>
> Probably a better question is, what are you trying to do?
>
> On Aug 4, 2006, at 4:50 AM, Nikolay Samokhvalov wrote:
>
> > I still think that this is quite strange behaviour. When I write
> > '...SELECT NEW.id...' I don't expect that another calling of column's
> > default expr will take place. I just want to have access to "id"
> > column of just-created row.
> >
> > Any thoughts?
> >
> > ---------- Forwarded message ----------
> > From: Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>
> > Date: Jul 13, 2006 6:15 PM
> > Subject: Strange behaviour of RULE (selecting last inserted ID of
> > 'sequenced' column)
> > To: PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
> >
> >
> > Is this a bug?
> >
> > test=> create sequence strange_seq;
> > CREATE SEQUENCE
> > test=> create table strange(id integer not null default
> > nextval('strange_seq') primary key, data text);
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> > "strange_pkey" for table "strange"
> > CREATE TABLE
> > test=> create rule strange_rule as on insert to strange do select
> > new.id as id;
> > CREATE RULE
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> >  2
> > (1 row)
> >
> > test=> select * from strange;
> > id | data
> > ----+------
> >  1 | adas
> > (1 row)
> >
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> >  4
> > (1 row)
> >
> > test=> insert into strange(data) values('adas');
> > id
> > ----
> >  6
> > (1 row)
> >
> > test=> select * from strange;
> > id | data
> > ----+------
> >  1 | adas
> >  3 | adas
> >  5 | adas
> > (3 rows)
> >
> >
> > --
> > Best regards,
> > Nikolay
> >
> >
> > --
> > Best regards,
> > Nikolay
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
>
>


-- 
Best regards,
Nikolay

In response to

pgsql-bugs by date

Next:From: Jie LiangDate: 2006-08-11 16:47:11
Subject: Re: Restoring 7.4.2 pg_dumpall output in 8.1.4 fails
Previous:From: David FetterDate: 2006-08-11 01:31:24
Subject: Re: Restoring 7.4.2 pg_dumpall output in 8.1.4 fails

pgsql-general by date

Next:From: Francis GUDINDate: 2006-08-11 13:49:56
Subject: VACUUM VERBOSE output to STDERR
Previous:From: Alban HertroysDate: 2006-08-11 13:04:07
Subject: Re: Help.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group