Skip site navigation (1) Skip section navigation (2)

Re: Replicating sequences

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Replicating sequences
Date: 2004-10-31 16:10:08
Message-ID: 200410310910.08798.pgsql@bluepolka.net (view raw or flat)
Thread:
Lists: pgsql-general
On Sunday October 31 2004 8:25, Tom Lane wrote:
> "Ed L." <pgsql(at)bluepolka(dot)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


In response to

pgsql-general by date

Next:From: Randall NortmanDate: 2004-10-31 16:17:16
Subject: Daylight Savings Time handling on persistent connections
Previous:From: Joshua D. DrakeDate: 2004-10-31 15:42:10
Subject: Re: procedural languages in 7.4.6

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group