On 31 August 2016 at 21:17, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > While I was hacking around sequence stuff, I felt the urge to look into > an old peeve: That sequence metadata is not stored in a proper catalog. > Right now in order to find out some metadata about sequences (min, max, > increment, etc.), you need to look into the sequence. That is like > having to query a table in order to find out about its schema. > > There are also known issues with the current storage such as that we > can't safely update the sequence name stored in the sequence when we > rename, so we just don't.
... and don't have a comment warning the poor confused reader about that, either. As I discovered when doing my first pass at sequence decoding. > I don't know if this is a net improvement. Maybe this introduces as > many new issues as it removes. But I figured I'll post this, so that at > least we can discuss it. This will change behaviour subtly. Probably not in ways we care much about, but I'd rather that be an informed decision than an implicit "oops we didn't think about that" one. Things stored in the Form_pg_sequence are affected immediately, non-transactionally, by ALTER SEQUENCE. If you: BEGIN; ALTER SEQUENCE myseq INTERVAL 10; ROLLBACK; then the *next call to nextval* after the ALTER will step by 10. Well, roughly, there's some slush there due to caching etc. Rolling back has no effect. Yet other effects of ALTER SEQUENCE, notably RENAME, are transactional and are subject to normal locking, visibility and rollback rules. Even more fun, ALTER SEQUENCE ... RESTART is immediate and non-transactional .... but TRUNCATE ... RESTART IDENTITY *is* transactional and takes effect only at commit. ALTER SEQUENCE writes a new Form_pg_sequence with the new value to the existing relfilenode. TRUNCATE instead updates pg_class for the sequence with a new relfilenode and writes its changes to the new relfilenode. So even two operations that seem the same are very different. If understand the proposed change correctly, this change will make previously non-transactional ALTER SEQUENCE operations transactional and subject to normal rules, since the relevant information is now in a proper catalog. Personally I think that's a big improvement. The current situation is warts upon warts. Prior proposals to move sequences away from a one-relation-and-one-filenode-per-sequence model have fallen down in early planning stages. This seems like a simpler, more sensible step to take, and it won't block later cleanups of how we store sequences if we decide to go there. It'll also make it slightly easier to handle logical decoding of sequence advances, since it'll be possible to send *just* the new sequence value. Right now we can't tell if interval, etc, also got changed, and have to send most of the Form_pg_sequence on the wire for every sequence advance, which is a little sucky. This change won't solve the problem I outlined in the other thread though, that sequences are transactional sometimes and not other times. So +1 for the idea from me. It'll just need relnotes warning of the subtle behaviour change. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
