Thread: Problem with sequence et rule

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

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@lfix.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

Oliver Elphick <olly@lfix.co.uk> writes:
> On Sat, 2004-07-31 at 23:57, Marc Boucher wrote:
>> Now my questions are:
>> - Is this an expected behavior ?

> I couldn't answer that.

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

At 21:28 31/07/2004 -0400, Tom Lane wrote:
>>> Now my questions are:
>>> - Is this an expected behavior ?
> It is.  Rules are essentially macros and so you have all the usual
> potential gotchas with multiple evaluations of their input arguments.
I've understood what was done by the evaluation process. I was just
expecting that the "NEW" variable would contain the inserted values (after
all it contains correct values for non-sequence columns).

> The recommended way to handle this type of problem is with a trigger
> rather than a rule.
I've changed this operation into a trigger, and it works like a charm. The
function receives the correct values, even the oid (which "rule" doesn't
provide).

I've since modified my queries to use the unified table, and I've gained
approx. 25-35% of execution time. Interesting on an admin page that takes
seconds to generate (hundreds of table lookups).


Thanks for your help.

--
Marc