On Tuesday October 26 2004 11:21, Ed L. wrote:
> On Tuesday October 26 2004 10:56, Jan Wieck wrote:
> > Sequences replicated by Slony do not follow exactly the same semantics
> > as tables. Each time a SYNC event is generated on the origin of data,
> > the current values of all sequences is recorded. because of the
> > difference in concurrency and visibility of changes between tables and
> > sequences, these recorded values might include changes to the sequence
> > that are not yet visible (committed) and therefore the corresponding
> > table data changes will not yet get replicated with that SYNC event.
> > Since sequences in PostgreSQL are not guaranteed to be gap free and
> > don't roll back, this is acceptable. The do get correctly adjusted on
> > switchover and they might be more advanced due to lost transactions in
> > the case of a failover. So they follow exactly the PostgreSQL sequence
> > semantics.
>
> If I'm reading you correctly, I think you're saying that sequences will
> be not syncronized between master and slave until failover.  Is that
> correct?

This is a major issue for us; we would like to replicate precisely because 
there might be an interruption or hardware failure for the master, and need 
to count on having a consistent database on the slave, even if it doesn't 
have a transaction or two at the end.  If a master failure occurs, the 
slave is certainly not going to be able to retrieve the latest sequence 
values from the master in a failover mode, so putting off sequence 
replication until then is not adequate.  If sequences are behind on the 
slave, and serial id columns are present, then after prepping the slave to 
takeover, new inserts will attempt to insert duplicate keys and fail.  Jan 
is usually a few steps ahead of me and has probably already thought about 
this, so I'm interested to hear how this is supposed to work.  I'm sure I'm 
not the only one with this concern; how are others handling the sync'ing of 
sequence values??

Is there a way we could add a slon option that says:  sync the sequences 
every time you sync the tables?

For dbmirror, we modified it to sync all sequence values on every sync.  Not 
a perfect solution by any stretch, but it works pretty well at keeping the 
master and slave seqs in sync.  It's a little cpu-intensive when you have 
200 sequence objects and you want them in sync within 3 secs or so...

An alternative would be to have a process continuously update the slave's 
sequences to the max (yuck).


Ed