When replication is implemented, what is going to happen with database
systems that rely heavily on sequences for primary keys?

For example, consider this simple table:


CREATE SEQUENCE "IntervalType_IntervalTypeID_seq" start 1 increment 1
maxvalue 2147483647 minvalue 1  cache 1 ;

CREATE TABLE "IntervalType" (
 "IntervalTypeID" integer DEFAULT
nextval('"IntervalType_IntervalTypeID_seq"'::text) NOT NULL,
 "IntervalTypeDescription" character varying(255),
 "MaximumValue" integer,
 "FrequencyUnits" double precision,
 Constraint "PK_IntervalType" Primary Key ("IntervalTypeID")
);

Now, when we replicate this table to other systems, will the sequence
parameters also flow to the targets?

A more interesting question is what happens if we need to revert to a
prior version (where -- for instance -- the maximum value of the
sequence was smaller than it is right now).  Will the target system have
the sequence value reduced?

Fri, 16 Jan 2004 15:21:36 -0800
"Dann Corbit" <DCorbit@connx.com>
Replication question
Dann Corbit wrote:

>When replication is implemented, what is going to happen with database
>systems that rely heavily on sequences for primary keys?
>

Don't know which replication system you mean, there are some implemented 
already.

As for Slony, I plan to have the functions setval(), nextval() and 
curval() get renamed and replication wrappers put around them that a) 
disable non-replication access to the latter two in slave configuration 
and b) capture the modifications to the sequence and cause the slave 
engines to issue appropriate setval() calls.

This is independant from the values assigned to the data rows on INSERT 
since Slony captures all table updates via AFTER triggers, so the 
replication information contains the correct final value of the columns 
no matter how they got there.


Jan

>
>For example, consider this simple table:
>
>
>CREATE SEQUENCE "IntervalType_IntervalTypeID_seq" start 1 increment 1
>maxvalue 2147483647 minvalue 1  cache 1 ;
>
>CREATE TABLE "IntervalType" (
> "IntervalTypeID" integer DEFAULT
>nextval('"IntervalType_IntervalTypeID_seq"'::text) NOT NULL,
> "IntervalTypeDescription" character varying(255),
> "MaximumValue" integer,
> "FrequencyUnits" double precision,
> Constraint "PK_IntervalType" Primary Key ("IntervalTypeID")
>);
>
>Now, when we replicate this table to other systems, will the sequence
>parameters also flow to the targets?
>
>A more interesting question is what happens if we need to revert to a
>prior version (where -- for instance -- the maximum value of the
>sequence was smaller than it is right now).  Will the target system have
>the sequence value reduced?
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>  
>


-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #




Tue, 20 Jan 2004 07:56:48 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Replication question