Thread: Replicating sequences

I asked this on the slony list, but maybe more appropriate to ask here...

I'm using slony to replicate a database with 200 sequences.  To replicate
these with slony or our modified dbmirror, the replicator polls every
sequence relation (select last_value...) on each syncronization interval.
That amounts to 200 select queries per sync interval.  That can be a
significant load in our case if we want short sync intervals of 1-2
seconds.  Naturally, this leads to the idea of triggers on sequences, which
I know do not exist at present in pgsql.

Are triggers on sequences a reasonable feature request/hope for Pgsql?

Short of simply increasing the sync interval, what other options are there
for addressing the performance load in a case like this?

Ed

"Ed L." <pgsql@bluepolka.net> writes:
> Are triggers on sequences a reasonable feature request/hope for Pgsql?

I don't think so.  Since sequences are inherently not transactional,
it seems wrong to fire a transactional behavior as a side effect of
nextval().  What exactly are you expecting the trigger to do, and
how is it going to do it in a way that won't get rolled back if the
calling transaction fails later?

            regards, tom lane

On Sunday October 31 2004 8:25, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > Are triggers on sequences a reasonable feature request/hope for Pgsql?
>
> I don't think so.  Since sequences are inherently not transactional,
> it seems wrong to fire a transactional behavior as a side effect of
> nextval().  What exactly are you expecting the trigger to do, and
> how is it going to do it in a way that won't get rolled back if the
> calling transaction fails later?

I don't know how to implement a solution to my problem.  What seems evident
is that polling hundreds of sequence objects to tell if they've changed is
an unscalable aspect of current async replication solutions.  My only
thought in raising the idea is that notification of a change to a sequence
value is highly preferrable over performance-intensive polling.  Whether
that's done via triggers inside a transaction, or some other mechanism, I
don't care much as long as polling is not required.

I wonder if these async replication triggered procedures might be able to
detect changes to their related sequence objects, and issue a NOTIFY for
the updated sequence?  The replicator could be listening on the NOTIFY and
update accordingly.  The NOTIFY wouldn't go out until/unless the
transaction completed, and would obviate the need for sequence polling.
That'd save us 100-200 queries/second if it were possible if we're
attempting to replicate with a very short (1s) sync interval.

Ed