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

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

From: Jim Nasby <jnasby(at)pervasive(dot)com>
To: nikolay(at)samokhvalov(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-08 17:35:19
Message-ID: 02156ABB-5341-4A9D-A69F-12F7FC5D7625@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
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



In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-08-08 19:18:18
Subject: Re: [BUGS] Patch to allow C extension modules to initialize/finish
Previous:From: Jim NasbyDate: 2006-08-08 17:33:20
Subject: Re: [BUGS] BUG #2560: Web page documentation hard to use

pgsql-general by date

Next:From: Christoph PingelDate: 2006-08-08 17:36:22
Subject: DISTINCT to get distinct *substrings*?
Previous:From: Carl R. BruneDate: 2006-08-08 17:25:07
Subject: read only transaction, temporary tables

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