Support sequences

[jberkus]

pg_shard needs to support sequences and SERIAL/BIGSERIAL. The simplest way to do these for now is to have the sequence live on the master. Later we'll need to do something more complicated, but 9.5 will have machinery for that, I think.

[jasonmp85]

Having the sequence live on the master is essentially a must right now, as INSERT statements are commutable: conditions could arise wherein two racing INSERT commands result in different sequence values across replicas. Perhaps that's an argument for looking into logical replication, but the feature wasn't mature enough for us to investigate earlier in pg_shard's lifecycle.

Right now we eagerly evaluate any "constant expressions" during planning, e.g. we transform char_length('Jason') into the integer 5. Certain other functions could reasonably be supported by eagerly evaluating them on the master during planning, even things like rand or in your case, nextval. Since sequence values are never rolled back, our loose transactional semantics don't even get in the way here.

For the immediate future, we could expand the types of expressions we eagerly evaluate, or even offer a special eager function to allow users to force eager evaluation of functions on the master.

[jasonmp85]

Nothing stands out in my mind about special support for this kind of thing in 9.5… do you have a quick link to a pgsql-hackers thread on the topic? I'm a pretty heavy lurker in that list but don't remember anything about this.

[jberkus]

Jason: there's some stuff buried in the Bi-directional replication code which allows replicas to pull "chunks" of sequences, in effect allowing for distributed sequences. However, this doesn't at all solve the consistency issue between shards.

Maybe we'd be better off implementing a UUID which is compatible with pg_shard instead, something which will be consistent yet unique across shards. Not quite sure how that would work, though.

[jberkus]

Maybe we should borrow code from here? https://github.com/OptionsHouse/shard_manager

Shaun has a fairly advanced distributed ID-generation widget. We may need to work out licensing issues though.

[digi604]

implementing this would be highly appriciated.

[rodo]

Support sequences is definitely a nice to have.
Thanks for pg_shard it's a huge improvement for postgresql.