For what it's worth, we have several unique constraints in the tables we
replicate and I haven't seen this issue.

--S

-----Original Message-----
From: slony1-general-bounces at lists.slony.info
[mailto:slony1-general-bounces at lists.slony.info] On Behalf Of Thomas
Spreng
Sent: Tuesday, August 19, 2008 5:26 AM
To: glynastill at yahoo.co.uk
Cc: slony1-general at lists.slony.info
Subject: Re: [Slony1-general] Random Duplicates Error


On 19. Aug, 2008, at 12:56, Glyn Astill wrote:
>> Every now and then (around once a week) one (any of them)
>> of the
>> subscribers fails to replicate the origin's data with
>> the following
>> message in the log:
>> ERROR:  duplicate key value violates unique constraint
>> "xyz"
>>
>> There is no further indication in the logs what could have
>> been the
>> cause for this problem.

> Do you have any sequences on the table?

Yes, the primary key field uses a sequence:

<sql>
CREATE SEQUENCE records_id_seq
   INCREMENT 1
   MINVALUE 1
   MAXVALUE 9223372036854775807
   START 624877670
   CACHE 1;
</sql>

> And if so are they replicated?

Yes, it's in the slony replication set.
But the sql insert error is always caused by the constraint
'records_vendor_id_key' AFAIK, whose fields don't use any sequence.

> Can you post the table definition?


<sql>
CREATE TABLE records
(
   id serial NOT NULL,
   vendor_id character varying NOT NULL,
   subvendor_id integer NOT NULL,
   destination character varying NOT NULL,
   departure_city character varying NOT NULL,
   departure_date date NOT NULL,
   duration integer NOT NULL,
   description text NOT NULL,
   price numeric(10,2) NOT NULL,
   price_inaccurate boolean NOT NULL DEFAULT false,
   adults character varying NOT NULL DEFAULT ''::character varying,
   children character varying NOT NULL DEFAULT ''::character varying,
   babies character varying NOT NULL DEFAULT ''::character varying,
   total_price numeric(10,2),
   url character varying NOT NULL,
   accomodation boolean NOT NULL,
   transport boolean NOT NULL,
   car boolean NOT NULL,
   offer_type integer,
   offer_id character varying,
   offer_name character varying,
   attributes character varying NOT NULL DEFAULT ''::character varying,
   location_id integer,
   vendor_rec_id character varying NOT NULL,
   vendor_group_id character varying NOT NULL,
   unlinked integer NOT NULL DEFAULT 0,
   created_on timestamp without time zone NOT NULL DEFAULT now(),
   modified_on timestamp without time zone NOT NULL DEFAULT now(),
   enabled boolean NOT NULL DEFAULT true,
   price_foreign numeric(10,2),
   currency_foreign character varying(3),
   CONSTRAINT records_pkey PRIMARY KEY (id),
   CONSTRAINT records_location_id_fkey FOREIGN KEY (location_id)
       REFERENCES locations (id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE SET NULL,
   CONSTRAINT records_vendor_id_key UNIQUE (vendor_id, vendor_rec_id)
)
WITH (OIDS=FALSE);
ALTER TABLE records ALTER COLUMN id SET DEFAULT  
nextval('records_id_seq'::regclass);
</sql>

cheers,

tom.

_______________________________________________
Slony1-general mailing list
Slony1-general at lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general