Thread: rule which unpredictable modify a sequence number

I have a problem with a rule which unpredictable modify a sequence
number. When I add a new table entry, the new id_keyword hasnt the value
as expected. ex.: 1000000000000000, 1000000000000001,
1000000000000002,...... If i remove the rule it works.

Here the table, rule and sequence definitions:

CREATE TABLE geocontexter.gc_keyword(
   id_keyword bigint NOT NULL DEFAULT nextval('geocontexter.seq_gc_keyword'::regclass),
   id_parent bigint NOT NULL DEFAULT 0,
   id_attribute_group bigint,
   id_status smallint NOT NULL DEFAULT 100,
   update_time timestamp without time zone NOT NULL,
   preferred_order smallint,
   lang character varying(30) NOT NULL DEFAULT 'en'::character varying,
   title character varying(126) NOT NULL,
   description character varying(10000) NOT NULL DEFAULT ''::character varying,
   attribute_value text,
   CONSTRAINT gc_keyword_id_keyword PRIMARY KEY (id_keyword)
)
WITH (
   OIDS=FALSE
);

CREATE OR REPLACE RULE keyword_insert_or_replace AS
     ON INSERT TO geocontexter.gc_keyword
    WHERE (EXISTS ( SELECT 1 FROM geocontexter.gc_keyword
                    WHERE gc_keyword.id_keyword = new.id_keyword))
    DO INSTEAD
           UPDATE geocontexter.gc_keyword SET lang = new.lang, description = new.description,
                                              title = new.title, update_time = new.update_time, id_parent =
new.id_parent,
                                              preferred_order = new.preferred_order, id_attribute_group =
new.id_attribute_group,
                                              attribute_value = new.attribute_value
           WHERE gc_keyword.id_keyword = new.id_keyword AND gc_keyword.update_time<   new.update_time;

CREATE SEQUENCE geocontexter.seq_gc_keyword
   INCREMENT 1
   MINVALUE -1999999999999999
   MAXVALUE 1999999999999999
   START           1000000000000000
   CACHE 1
   CYCLE;

On Sat, Oct 23, 2010 at 9:53 AM, Armand Turpel <geocontexter@gmail.com> wrote:
>
> I have a problem with a rule which unpredictable modify a sequence
> number. When I add a new table entry, the new id_keyword hasnt the value
> as expected. ex.: 1000000000000000, 1000000000000001,
> 1000000000000002,...... If i remove the rule it works.
>
> Here the table, rule and sequence definitions:
>
> CREATE TABLE geocontexter.gc_keyword(
>  id_keyword bigint NOT NULL DEFAULT
> nextval('geocontexter.seq_gc_keyword'::regclass),
>  id_parent bigint NOT NULL DEFAULT 0,
>  id_attribute_group bigint,
>  id_status smallint NOT NULL DEFAULT 100,
>  update_time timestamp without time zone NOT NULL,
>  preferred_order smallint,
>  lang character varying(30) NOT NULL DEFAULT 'en'::character varying,
>  title character varying(126) NOT NULL,
>  description character varying(10000) NOT NULL DEFAULT ''::character
> varying,
>  attribute_value text,
>  CONSTRAINT gc_keyword_id_keyword PRIMARY KEY (id_keyword)
> )
> WITH (
>  OIDS=FALSE
> );
>
> CREATE OR REPLACE RULE keyword_insert_or_replace AS
>    ON INSERT TO geocontexter.gc_keyword
>   WHERE (EXISTS ( SELECT 1 FROM geocontexter.gc_keyword
>                   WHERE gc_keyword.id_keyword = new.id_keyword))
>   DO INSTEAD
>          UPDATE geocontexter.gc_keyword SET lang = new.lang, description =
> new.description,
>                                             title = new.title, update_time =
> new.update_time, id_parent = new.id_parent,
>                                             preferred_order =
> new.preferred_order, id_attribute_group = new.id_attribute_group,
>                                             attribute_value =
> new.attribute_value
>          WHERE gc_keyword.id_keyword = new.id_keyword AND
> gc_keyword.update_time<   new.update_time;
>
> CREATE SEQUENCE geocontexter.seq_gc_keyword
>  INCREMENT 1
>  MINVALUE -1999999999999999
>  MAXVALUE 1999999999999999
>  START           1000000000000000
>  CACHE 1
>  CYCLE;

default values unfortunately don't play well with rules.  rules in
fact are a giant headache and your strategy of upsert in rule is
probably going to need a rethink if you want to expose sql-like
behaviors to the calling query.  I would advise moving your upsert
into a function call, or doing it in the application.

merlin