Page MenuHomePhabricator

PostgreSQL schema change for consistency with MySQL
Open, LowPublic

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).

Details

ProjectBranchLines +/-Subject
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+239 -106
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

Event Timeline

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

Change 618409 merged by jenkins-bot:
[mediawiki/core@master] Migrate l10n_cache and module_deps to abstract schema

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

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

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

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

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

pagelinks table indexes on MySQL/Sqlite:

CREATE INDEX pl_namespace ON pagelinks (pl_namespace,pl_title,pl_from);
CREATE INDEX pl_backlinks_namespace ON pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);

On Postgres:

CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title);
CREATE INDEX pagelinks_title ON pagelinks (pl_title);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Change 634943 merged by jenkins-bot:
[mediawiki/core@master] Migrate watchlist table to Abstract schema

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Change 642927 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Fix some PG page table indexes drift with MySQL

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

Change 642927 merged by jenkins-bot:
[mediawiki/core@master] Fix some PG page table indexes drift with MySQL

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

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

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

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

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

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

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

Change 646193 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Rename all PG logging indexes

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

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

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

Change 646193 merged by jenkins-bot:
[mediawiki/core@master] Rename all PG logging indexes

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

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

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

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

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

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

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

Change 649120 had a related patch set uploaded (by Ammarpad; owner: Ammarpad):
[mediawiki/core@master] Add uploadstash.us_sha1 nullability change to PG update list

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

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