Andrew Sullivan wrote:
> Dane Miller wrote:
> > Consider the below example. Does Slony guarantee that the updated
> > sequence will always appear after (or concurrently with) the
> > result of the I/U/D statements on subscriber nodes?
> 
> Not necessarily.  Slony sets the sequence on the target to the value
> of the sequence at the time of the sync, because there isn't any
> other way to look at the sequence value.  The idea is that it's ok
> for a sequence to be _ahead_ of the rest of the database, because
> this is consistent with the semantics of sequences in an
> unreplicated database (they always advance, so they're
> never-go-backwards series that possibly have gaps).  
> 
> If you want this, you could probably do it using a table that is
> replicated and that gets updated with currval() using a trigger or
> something. 

I'd like to solicit advice on an efficient way to update a column with
currval() that doesn't incur lots of dead tuple bloat.  In a separate
post Bill Moran made it clear I shouldn't truncate replicated tables --
which makes perfect sense, thanks for the clue bat Bill.  But I *think* I
also want to avoid having to constantly vacuum this table during
frequent UPDATEs (see #3 below for why this is couched in 'I think').

This question might be better posed on one of the postgresql-* lists, so
please point me away if necessary.  However, I'm hoping this is relevant
insofar as it relates to handling replication lag.

Here's the table definition for reference:

CREATE TABLE transaction_log
( 
  tid integer default nextval('transaction_id'::regclass) not null
);


So far I've thought of three ways to store the latest sequence value...

1) Append-only into 1 table
  INSERT into transaction_log default values;
  -- schedule periodic DELETE && VACUUM jobs to clear old tid rows

2) Append-only into 2 tables
  -- same as 1) but alternating which table to insert into based on a
datetime range.  E.g. use transaction_log1 on odd days and
transaction_log2 on even days.  Similar to Slony's use of sl_log{1,2}
  -- schedule table maintenance while alternate table is in use.

3) use UPDATE statements 
  UPDATE transcation_log set tid=DEFAULT;
  -- autovacuum will clean up dead tuples as needed
  -- Heap-Only Tuples (Postgresql 8.3) reclaim space and reduce need to
vacuum




-- 
Dane Miller
Systems Administrator
Greatschools, Inc
http://www.greatschools.net