Page MenuHomePhabricator

PostgreSQL schema change for consistency with MySQL
Closed, ResolvedPublic

Description

The inconsistencies between the MySQL and PostgreSQL schemas cause a constant maintenance burden. PostgreSQL support is frequently broken in master. The PostgreSQL schema is certainly better than the MySQL one, if analysed in isolation, but the advantages to PostgreSQL users of having an improved schema are more than outweighed by the disadvantages of inconsistency with the remainder of the installed MediaWiki user base.

We should aim to have a schema which can be automatically generated from maintenance/tables.sql, which is already shared between MySQL and SQLite.

Proposal:

  • Require PostgreSQL 9.2 or later. Remove the many special cases from DatabasePostgres supporting earlier versions. It was released in 2012 and is the oldest version which still receives bugfixes. PostgreSQL 9.1 was EOL in September 2016.
  • Rename tables mwuser to user, and pagecontent to text. Table names are always quoted, so it doesn't matter if they are keywords. A table prefix can be used if desired to avoid having table names that are keywords.
  • Remove foreign key constraints. These were a nice idea, but have flow-on effects, causing inconsistencies and run-time errors.
    • Remove the dummy anonymous user
    • Remove the page_deleted trigger
  • Change old_text to BYTEA, for T26607 etc.
  • Change rc_ip to TEXT
  • Remove search update triggers, use SearchPostgres::update() instead.
  • Remove the profiling table.
  • Map AUTO_INCREMENT in the shared schema to PostgreSQL's SERIAL. This is essentially an abbreviated syntax for what the PostgreSQL schema does already. Rename any existing sequences which do not fit the resulting naming scheme.
  • Re-implement DatabasePostgres::insertId() using SELECT lastval(), so that it does not require nextSequenceValue() to be called first.
  • Change page_random to FLOAT(53). Also change it in the shared schema. This is an SQL-standard synonym for the existing column type on MySQL, and is also understood by PostgreSQL to mean the same thing. It is accepted on SQLite (which stores numbers as strings).

Implementation:

  • Require PostgreSQL 9.2 or later.
  • Rename tables mwuser to user, and pagecontent to text.
  • Remove foreign key constraints. These were a nice idea, but have flow-on effects, causing inconsistencies and run-time errors.
    • Remove the dummy anonymous user
    • Remove the page_deleted trigger
  • Change old_text to BYTEA, for T26607 etc.
  • Change rc_ip to TEXT
  • Remove search update triggers, use SearchPostgres::update() instead.
  • Remove the profiling table (T231366).
  • Map AUTO_INCREMENT in the shared schema to PostgreSQL's SERIAL.
  • Re-implement DatabasePostgres::insertId() using SELECT lastval(), so that it does not require nextSequenceValue() to be called first. (Done in Ib308190c52673a)
  • Change page_random to FLOAT(53). Also change it in the shared schema.

Details

SubjectRepoBranchLines +/-
mediawiki/coremaster+18 -37
mediawiki/coremaster+104 -65
mediawiki/coremaster+223 -81
mediawiki/coremaster+0 -35
mediawiki/extensions/CheckUsermaster+2 -1
mediawiki/extensions/CheckUsermaster+6 -3
mediawiki/coremaster+15 -5
mediawiki/coremaster+280 -141
mediawiki/extensions/FlaggedRevsmaster+10 -5
mediawiki/extensions/CreditsSourcemaster+35 -19
mediawiki/coreREL1_36+5 -2
mediawiki/coremaster+5 -2
mediawiki/coremaster+252 -4
mediawiki/coremaster+0 -11
mediawiki/coremaster+7 -3
mediawiki/coremaster+247 -106
mediawiki/coremaster+6 -1
mediawiki/coremaster+282 -86
mediawiki/coremaster+22 -9
mediawiki/coremaster+16 -6
mediawiki/coremaster+335 -81
mediawiki/coremaster+393 -119
mediawiki/coremaster+80 -20
mediawiki/coremaster+5 -1
mediawiki/coremaster+308 -60
mediawiki/coremaster+85 -75
mediawiki/coremaster+4 -0
mediawiki/coremaster+417 -95
mediawiki/coremaster+1 -0
mediawiki/coremaster+299 -84
mediawiki/coremaster+266 -83
mediawiki/coremaster+15 -7
mediawiki/coremaster+189 -74
mediawiki/coremaster+8 -3
mediawiki/coremaster+192 -66
mediawiki/coremaster+124 -42
mediawiki/coremaster+93 -30
mediawiki/coremaster+114 -35
mediawiki/coremaster+55 -24
mediawiki/coremaster+86 -35
mediawiki/coremaster+110 -61
mediawiki/coremaster+138 -29
mediawiki/coremaster+79 -42
mediawiki/coremaster+179 -76
mediawiki/coremaster+49 -16
mediawiki/coremaster+74 -39
mediawiki/coremaster+159 -39
mediawiki/coremaster+80 -27
mediawiki/coremaster+63 -29
mediawiki/coremaster+79 -29
mediawiki/coremaster+53 -17
mediawiki/coremaster+105 -41
mediawiki/coremaster+101 -29
mediawiki/coremaster+90 -26
mediawiki/coremaster+106 -33
mediawiki/coremaster+120 -36
mediawiki/coremaster+112 -43
mediawiki/coremaster+91 -31
mediawiki/coremaster+100 -38
mediawiki/coremaster+57 -33
mediawiki/coremaster+77 -36
mediawiki/coremaster+52 -22
mediawiki/coremaster+87 -40
mediawiki/coremaster+75 -42
mediawiki/coremaster+60 -49
mediawiki/coremaster+73 -36
mediawiki/coremaster+122 -58
mediawiki/coremaster+37 -13
mediawiki/coremaster+2 -2
Show related patches Customize query in gerrit

Related Objects

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Change 649120 merged by jenkins-bot:
[mediawiki/core@master] Add uploadstash.us_sha1 nullability change to PG update list

https://gerrit.wikimedia.org/r/649120

Change 651001 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] [WIP] Migrate filearchive to abstract schema

https://gerrit.wikimedia.org/r/651001

Change 651001 merged by jenkins-bot:
[mediawiki/core@master] Migrate filearchive to abstract schema

https://gerrit.wikimedia.org/r/651001

Change 651680 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate text table to abstract schema

https://gerrit.wikimedia.org/r/651680

Change 655198 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Add missing PG indexes of ipblocks

https://gerrit.wikimedia.org/r/655198

Change 655198 merged by jenkins-bot:
[mediawiki/core@master] Add missing PG indexes of ipblocks

https://gerrit.wikimedia.org/r/655198

Change 651680 merged by jenkins-bot:
[mediawiki/core@master] Migrate text table to abstract schema

https://gerrit.wikimedia.org/r/651680

Change 656586 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate oldimage to abstract schema

https://gerrit.wikimedia.org/r/656586

Change 656586 merged by jenkins-bot:
[mediawiki/core@master] Migrate oldimage to abstract schema

https://gerrit.wikimedia.org/r/656586

Change 656587 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Fix Postgres index drifts on recentchanges table

https://gerrit.wikimedia.org/r/656587

Change 656587 merged by jenkins-bot:
[mediawiki/core@master] Fix Postgres index drifts on recentchanges table

https://gerrit.wikimedia.org/r/656587

Change 636049 merged by jenkins-bot:
[mediawiki/core@master] Migrate objectcache to abstract schema

https://gerrit.wikimedia.org/r/636049

Change 657949 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate ipblocks to abstract schema

https://gerrit.wikimedia.org/r/657949

Change 657957 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Standardize archive indexes

https://gerrit.wikimedia.org/r/657957

Change 657949 merged by jenkins-bot:
[mediawiki/core@master] Migrate ipblocks to abstract schema

https://gerrit.wikimedia.org/r/657949

Change 659385 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate image table to abstract schema

https://gerrit.wikimedia.org/r/659385

Change 657957 merged by jenkins-bot:
[mediawiki/core@master] Standardize archive indexes

https://gerrit.wikimedia.org/r/657957

Change 659385 merged by jenkins-bot:
[mediawiki/core@master] Migrate image table to abstract schema

https://gerrit.wikimedia.org/r/659385

Change 662027 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Fix drifts in columns of recentchanges in PG

https://gerrit.wikimedia.org/r/662027

Change 662027 merged by jenkins-bot:
[mediawiki/core@master] Fix drifts in columns of recentchanges in PG

https://gerrit.wikimedia.org/r/662027

Change 671679 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Migrate archive table to abstract schema

https://gerrit.wikimedia.org/r/671679

Change 671679 merged by jenkins-bot:
[mediawiki/core@master] Migrate archive table to abstract schema

https://gerrit.wikimedia.org/r/671679

Change 673508 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Fix drifts of indexes in postgres and mysql in user table

https://gerrit.wikimedia.org/r/673508

Change 673508 merged by jenkins-bot:
[mediawiki/core@master] postgres: Fix drifts of indexes in user table compared to mysql

https://gerrit.wikimedia.org/r/673508

Change 643435 merged by jenkins-bot:
[mediawiki/core@master] Migrate page to abstract schema

https://gerrit.wikimedia.org/r/643435

Change 673784 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Postgres: Fix revision table indexes drifts with MySQL

https://gerrit.wikimedia.org/r/673784

Change 673784 merged by jenkins-bot:
[mediawiki/core@master] Postgres: Fix revision table indexes drifts with MySQL

https://gerrit.wikimedia.org/r/673784

Change 674139 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Migrate revision table to abstract schema

https://gerrit.wikimedia.org/r/674139

Change 675208 had a related patch set uploaded (by Ammarpad; author: Ammarpad):
[mediawiki/core@master] Postgres: Remove dummy anonymous user and page_deleted trigger

https://gerrit.wikimedia.org/r/675208

Change 675208 merged by jenkins-bot:
[mediawiki/core@master] Postgres: Remove page_deleted trigger

https://gerrit.wikimedia.org/r/675208

Change 675281 had a related patch set uploaded (by Ammarpad; author: Ammarpad):
[mediawiki/core@master] Prepare for migrating user to abstract schema

https://gerrit.wikimedia.org/r/675281

Change 675281 merged by jenkins-bot:
[mediawiki/core@master] Prepare for migrating user to abstract schema

https://gerrit.wikimedia.org/r/675281

Change 675332 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/core@master] WIP: Migrate user table to abstract schema

https://gerrit.wikimedia.org/r/675332

Change 678331 had a related patch set uploaded (by Ladsgroup; author: Ladsgroup):

[mediawiki/core@master] Final fixes of Postgres updater before the branch cut

https://gerrit.wikimedia.org/r/678331

Change 678331 merged by jenkins-bot:

[mediawiki/core@master] Final fixes of Postgres updater for REL1_36

https://gerrit.wikimedia.org/r/678331

Change 678348 had a related patch set uploaded (by Jforrester; author: Ladsgroup):

[mediawiki/core@REL1_36] Final fixes of Postgres updater for REL1_36

https://gerrit.wikimedia.org/r/678348

Change 678348 merged by jenkins-bot:

[mediawiki/core@REL1_36] Final fixes of Postgres updater for REL1_36

https://gerrit.wikimedia.org/r/678348

Change 678426 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/extensions/CheckUser@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/678426

Change 680790 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/extensions/CreditsSource@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/680790

Change 680791 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/extensions/FlaggedRevs@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/680791

Change 678426 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/678426

Change 680791 merged by jenkins-bot:

[mediawiki/extensions/FlaggedRevs@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/680791

Change 680790 merged by jenkins-bot:

[mediawiki/extensions/CreditsSource@master] Postgres schema: Drop foreign key references to mwuser

https://gerrit.wikimedia.org/r/680790

Change 675332 merged by jenkins-bot:

[mediawiki/core@master] Migrate user table to abstract schema

https://gerrit.wikimedia.org/r/675332

Change 682745 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/core@master] Postgres: Deprecate table name remapping parameter and function

https://gerrit.wikimedia.org/r/682745

Change 682745 merged by jenkins-bot:

[mediawiki/core@master] Postgres: Deprecate table name remapping parameter and function

https://gerrit.wikimedia.org/r/682745

Change 688349 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/core@master] PostgresUpdater: Make updateUserTableSequence work for any table

https://gerrit.wikimedia.org/r/688349

Change 692414 had a related patch set uploaded (by Ammarpad; author: Ammarpad):

[mediawiki/extensions/CheckUser@master] Postgres schema: Drop foreign key to page

https://gerrit.wikimedia.org/r/692414

Change 692414 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Postgres schema: Drop foreign key to page

https://gerrit.wikimedia.org/r/692414

Change 688349 merged by jenkins-bot:

[mediawiki/core@master] PostgresUpdater: Drop updateUserTableSequence function

https://gerrit.wikimedia.org/r/688349

Change 674139 merged by jenkins-bot:

[mediawiki/core@master] Migrate revision table to abstract schema

https://gerrit.wikimedia.org/r/674139

So basically this is mostly done, the only thing we haven't fixed in tables schema is searchindex table. It is not there in PG at all. Maybe create it but not use it at all?

If we keep the current documentation, I would say not to create there:

"If using Postgres, this table does not exist: the full text information is stored as columns in the page and pagecontent tables directly."

Of course, there is nothing preventing to change that to "This table will be empty and not used".

Yeah, I think we should just create an empty table and change the documentation to reflect that.

T220450 needs to be done eventually. But for short term, I was initially thinking of adding some logic to specify that "generate schema for this table only if the db is not postgres." But we can also create the table for "future use" if there's no problem with that.

If there's a plan to use that table in PG later, I think it's fine to just add an empty unused table (actor table was in one or two releases of mediawiki with the same situation). Just make sure link to T220450 in the documentation.

Change 696411 had a related patch set uploaded (by Ladsgroup; author: Ladsgroup):

[mediawiki/core@master] Migrate searchindex to abstract schema

https://gerrit.wikimedia.org/r/696411

Change 696411 merged by jenkins-bot:

[mediawiki/core@master] Migrate searchindex to abstract schema

https://gerrit.wikimedia.org/r/696411

Change 703908 had a related patch set uploaded (by Ladsgroup; author: Ladsgroup):

[mediawiki/core@master] [POC] Migrate PG search triggers to SearchPostgres

https://gerrit.wikimedia.org/r/703908

This RfC has been virtually all implemented. Now PG and MySQL have one source of truth and they are much more consistent than they used to be. In order to avoid keeping this ticket open forever, I close this ticket and I have filed for everything that has been left:

Change 703908 abandoned by Ladsgroup:

[mediawiki/core@master] [POC] Migrate PG search triggers to SearchPostgres

Reason:

I can't work on it atm, feel free to pick it up

https://gerrit.wikimedia.org/r/703908