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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sat, 2004-07-31 at 23:57, Marc Boucher wrote:
I couldn't answer that. I can see what is happening: the rule is
rewriting the query and must be substituting the default value of
NEW.id, which is nextval('"album_id_seq"'::text). Therefore nextval is
being executed multiple times.

Presumably, the rule should use currval('"album_id_seq"'::text) instead
of NEW.id.

--
Oliver Elphick olly (AT) lfix (DOT) co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Have not I commanded thee? Be strong and of a good
courage; be not afraid, neither be thou dismayed; for
the LORD thy God is with thee whithersoever thou
goest." Joshua 1:9


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Oliver Elphick <olly (AT) lfix (DOT) co.uk> writes:
It is. Rules are essentially macros and so you have all the usual
potential gotchas with multiple evaluations of their input arguments.

The recommended way to handle this type of problem is with a trigger
rather than a rule.

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 (AT) postgresql (DOT) org)