Re: Global Sequences

On 16 October 2012 15:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> So, proposal is to allow nextval() allocation to access a plugin,
>> rather than simply write a WAL record and increment. If the plugin is
>> loaded all sequences call it (not OIDs).
>
> I think this is a fundamentally wrong way to go about doing what you
> want to do. It presumes that DDL-level manipulation of global sequences
> is exactly like local sequences; an assumption that is obviously false.
> What you really want is something vaguely like nextval but applied to
> a distinct type of object. That is, I think we first need a different
> kind of object called a "global sequence" with its own DDL operations.
>
> The nearby thread about "message queue" objects seems rather closely
> related. Perhaps it would be fruitful to think about the commonalities
> involved in two (or more?) new relkinds for global objects.

The message queue concept doesn't exist at all yet, so when we create
it we can specify anything we want. That is a different situation and
hence a different solution. CREATE SEQUENCE is SQL Standard and used
by SERIAL, many people's SQL, SQL generation tools etc.. My objective
is to come up with something that makes the standard code work
correctly in a replicated environment.

If rewriting the application was acceptable, we could just do as Peter
suggests and say "use UUIDs". Many other people who think rewriting
everything is OK spell that "CouchDB" etc.. But that doesn't solve the
problem at hand, which is making existing things work, rather than
force people to rethink and rewrite.

So CREATE GLOBAL SEQUENCE as a new kind of object altogether wouldn't
solve the problem I'm trying to address.

I guess we could use a decoration syntax on a sequence, like this...

ALTER SEQUENCE foo GLOBAL
or
ALTER SEQUENCE foo ALLOCATION FUNCTION myglobalalloc(); -- if we cared
to specify the alloc function on a per object basis.
or
ALTER SEQUENCE foo WITH (allocation=global);

So that we can explicitly specify which sequences to replicate
globally and which locally.

We'd need to have a userset GUC
default_sequence_allocation = local (default) | global
so that SERIAL and other new sequences could pick up the required
behaviour when created.

In any case, we need to have a plugin/user definable allocation
function for next few years at least.

Thoughts?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services