rule which unpredictable modify a sequence number

From: Armand Turpel <armand(dot)turpel(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: rule which unpredictable modify a sequence number
Date: 2010-10-23 13:38:40
Message-ID: 4CC2E560.2070309@mnhn.lu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Armand Turpel 2010-10-23 13:53:42 rule which unpredictable modify a sequence number
Previous Message zhong ming wu 2010-10-23 12:01:51 What is "return code" for WAL send command