Postgresql - use Identity Columns vs Sequences in 3.0

[shawnb61]

(I was actually working on a PR for this a while back but with the installer/upgrader rewrite that work is obsolete...  😆)

PG Sequences = how PG does 'AUTO_INCREMENT'...

SMF has always created Postgresql sequences the old "manual" way, where the sequence & table are created separately.  Unfortunately, SMF skipped a step, and no ownership was established between the table/column and the sequence.  This can create odd problems with operations, e.g., you could drop the table or column and the sequence remains forever, etc.  Backups & restores are affected, requiring custom scripts to address sequence number issues. 

Later versions of PG tried to make sequences a little easier to use by adding the SERIAL pseudo-datatype, but that had some of the same issues as sequences - under the covers, it was still a sequence.

Starting in PG10, a new standards-compliant mechanism is available, Identity Columns.

Identity Columns have two modes, 'ALWAYS' and 'BY DEFAULT', with ALWAYS being much more strict.  In my testing, SMF didn't like ALWAYS, as we include IDs in some updates/inserts (which is maybe a little messy...), but SMF seems to work just fine if you replace all the sequenced columns with identity columns using BY DEFAULT.  If we go with ALWAYS there will be a chunk of query cleanup work to do.

See also PR#6323 by @albertlast that attempted to address the issues with sequences a different way.

IMO, this should be a 3.0/3.1 thing. 

Reading:
https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial
https://wiki.postgresql.org/wiki/Fixing_Sequences
https://www.reddit.com/r/PostgreSQL/comments/1ae4lee/create_sequence_and_table_in_one_transaction/?rdt=50403
https://github.com/SimpleMachines/SMF/pull/6323

[Oldiesmann]

This sounds like a good idea for 3.0. The minimum supported version of Postgres for 3.0 is 12.17 currently so we shouldn't run into any issues there with the implementation of this.