Re: Problem with sequence et rule

On Sat, 2004-07-31 at 23:57, Marc Boucher wrote:
> 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.
...
> 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 ?

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.

> - How can I bypass this problem and ensure that I use the correct value,
> and that it's not incremented once more ?

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

--
Oliver Elphick olly(at)lfix(dot)co(dot)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