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.