Re: Global Sequences

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On 16 October 2012 15:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
>>
> 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.

I think we still can have both. I like Tom's suggestion better, as it
provides for a cleaner implementation in the long run, I think.

Now, the way I see how to get a GLOBAL SEQUENCE by default when creating
a SERIAL column would be with an Event Trigger. To get there, we need to
implement some kind of "INSTEAD OF" Event Trigger, and the good news is
that we only need to do that in a very specialized command, not as a
generic facility. At least as a starter.

CREATE EVENT TRIGGER global_sequences_please
ON ddl_create_sequence
WHEN context in ('generated')
EXECUTE PROCEDURE create_global_sequence_instead_thanks();

That would take care of any SERIAL or BIGSERIAL column creation and
leave alone manual CREATE SEQUENCE commands, as those would have a
context of 'toplevel' as opposed to 'generated'.

This context exposing and filtering is included in my current patch for
Event Triggers that I'm submitting quite soon to the next commit fest.

We still need to implement the "ddl_create_sequence" event that only
fires before create sequence and refrain from creating a sequence if it
did have an event trigger attached, whatever that did.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support