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

Problem with sequence et rule

From: Marc Boucher <pgml(at)gmx(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with sequence et rule
Date: 2004-07-31 22:57:38
Message-ID: 3.0.5.32.20040801005738.007f7100@mymail (view raw or flat)
Thread:
Lists: pgsql-bugs
Forgive me if this is not a bug. But I have a problem with a rule on a
table which has a column with a sequence.

I'm using postgres 7.3.4.
I have a table named "album" with the following structure (part only)

CREATE TABLE album (
    id integer DEFAULT nextval('"album_id_seq"'::text) NOT NULL,
    isbn character varying(10),
    flags smallint DEFAULT 0,

and many more columns that are not relevant here.

I have another table "album_edit_tst"

    alb_id integer NOT NULL,
    ed_ref character varying(30) NOT NULL,
    isbn character varying(30)
    flags smallint DEFAULT 0,

whose purpose is to gather additional information (only related to the
"album" table by the alb_id (if value is >0)).

Currently I have some queries that are interrogating both table (with a
UNION) to get complete relevant information. My main objective is to get
all data from "album" inserted into "album_edit_tst" so that I can use a
single select. Since I want to gain execution time by this method, views
are not suited.

So I've created the following rule to update "album_edit_tst" in
conjunction with "album".

CREATE RULE albed_setalb_rl AS ON INSERT TO album DO
  INSERT INTO album_edit_tst (alb_id,ed_ref,isbn,flags)
         VALUES (NEW.id,'',NEW.isbn,NEW.flags);


Note: The insert queries on table "album" do not specify the "id" column. I
leave it to PG.

When I insert new values the rule work but the value for "id" is wrong.
Instead of getting the same value used in the insert on "table" I get the
next one.
example:   id = '8225' in "album", but is set to "8226" in the record
inserted in "album_edit_tst"

Now if I play dumb with PG and use this rule instead:

CREATE RULE albed_setalb_rl AS ON INSERT TO album DO
  INSERT INTO album_edit_tst (alb_id,ed_ref,isbn,flags)
         VALUES (NEW.id,'',NEW.id,NEW.id);

(I place the "id" value in 3 columns)

I get this result:
record in "album":  id=8230, ...
record in "album_edit_tst": alb_id=8231,isbn=8232,flags=8233


Now my questions are:
 - Is this an expected behavior ?
 - How can I bypass this problem and ensure that I use the correct value,
   and that it's not incremented once more ?


Thanks

-- 
Marc


Responses

pgsql-bugs by date

Next:From: Oliver ElphickDate: 2004-08-01 01:09:08
Subject: Re: Problem with sequence et rule
Previous:From: Volodymyr KostyrkoDate: 2004-07-31 17:43:28
Subject: unicode strings are not sorted alphabetically

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