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