[openstreetmap/openstreetmap-website] Align structure.sql with generated output (Issue #4298)

[Andy Allan]

The `AS integer` bit is super interesting and surprisingly complicated!

* All sequences created on postgres 9 were bigint.
* Then https://github.com/postgres/postgres/commit/2ea5b06c7a7056dca0af1610aadebe608fbcca08 happened.
* Sequences created on postgres 10+ are now either integer or bigint, depending on the type of the primary key.

We have 9 tables with `integer` id columns, where if you created the table on postgres 9 (veteran dev, production) the sequence will have the `bigint` type, but other people (new devs, CI) will have the sequence with `integer` type, which is also what we have in structure.sql. These 9 sequences are:

- changeset_comments_id_seq
- client_applications_id_seq
- issue_comments_id_seq
- issues_id_seq
- oauth_tokens_id_seq
- redactions_id_seq
- reports_id_seq
- user_blocks_id_seq
- user_roles_id_seq

There are 3 further tables that have an additional complication:
* Primary keys created on rails < 5.1 were integer by default, rails >= 5.1 primary keys are bigint by default. ([Override for old versions](https://github.com/rails/rails/blob/9c22f35440ab85718ebf48e26b8944032c737193/activerecord/lib/active_record/migration/compatibility.rb#L333)).
* We have three migrations to convert integer primary keys to bigint for three specific tables. When these were run on postgres 9 (veteran dev, production), we ended up with bigint sequences, since all sequences were bigint to start with. However, when these migrations are run on postgres 10+ (new dev, CI) we end up with a mismatch between primary key and sequence, since updating the primary key to bigint does not also change the sequence to bigint.

These three tables are:

* oauth_nonces (db/migrate/20201214144017_expand_nonce_id.rb:4:      change_column :oauth_nonces, :id, :bigint)
* notes (db/migrate/053_add_map_bug_tables.rb:16:    change_column :map_bugs, :id, :bigint)
* note_comments (db/migrate/054_refactor_map_bug_tables.rb:15:    change_column :map_bug_comment, :id, :bigint)

I need to have a think about what the best thing to do is. 

For the first set of 9, I'm tempted to just run "ALTER SEQUENCE foo_seq AS integer;" locally, since then my machine matches CI and new devs, and although production will still be bigint I can't see any harm in that. Alternatively, we could instead upgrade the affected tables to have bigint ids, for consistency if nothing else.

For the second set of 3, it's potentially confusing to new developers that the sequences are only integer despite the primary keys being bigint, and they might worry that it's also bug in production (without being able to know that production has postgres-9-era bigint sequences). It's also a gotcha for anyone else who deploys the code, since they will have a "for real" mismatch lurking in their production database. For these tables, it might be worth either adding migrations to alter the sequences to bigint (which will make no difference in production, but will fix everything else). 

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/4298#issuecomment-1832615439
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/issues/4298/1832615439 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20231129/3b46d50b/attachment.htm>