Re: rule which unpredictable modify a sequence number

On Sat, Oct 23, 2010 at 9:53 AM, Armand Turpel <geocontexter(at)gmail(dot)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