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+179 -76
mediawiki/coremaster+79 -42
mediawiki/coremaster+124 -42
mediawiki/coremaster+49 -16
mediawiki/coremaster+74 -39
mediawiki/coremaster+159 -39
mediawiki/coremaster+80 -27
mediawiki/coremaster+63 -29
mediawiki/coremaster+66 -35
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
Anomie added a comment.Apr 2 2018, 6:21 PM

Or we could go one better and turn tables.sql into a "MediaWiki DDL" rather than MySQL/SQLite DDL statements (is there already a Phab task about that?). That could be anything from something that looks very much like it does now (but maybe with column types using keywords that are less MySQL-specific) to a PHP array structure or JSON.

I wrote a task for that: T191231: RFC: Abstract schemas and schema changes

lilydjwg removed a subscriber: lilydjwg.Oct 2 2018, 5:38 AM
Ladsgroup added a subscriber: Ladsgroup.

As part of T230428: Migrate tables.sql to abstract schema I'm sorta dragged to implement this.

Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptMay 10 2020, 1:25 AM

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

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

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

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

Ladsgroup added a comment.EditedMay 10 2020, 2:30 AM

Hello,
Does anyone here know why postgres schema doesn't have searchindex table??

Krinkle added a comment.EditedMay 11 2020, 9:43 PM

Hello,
Does anyone here know why postgres schema doesn't have searchindex table??

(Copy from IRC) We found at https://www.mediawiki.org/wiki/Manual:Searchindex_table that the SearchPostgres backend doesn't use/need this table. Instead, it uses a full text index directly on the text table (which, in turn, is called pagecontent in Postgres).

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

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

Krinkle removed a subscriber: Krinkle.Jun 14 2020, 5:39 PM

Change 595289 merged by jenkins-bot:
[mediawiki/core@master] Migrate actor and user_former_groups tables to abstract schema

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

I'm sorry for adding lots of tasks here but each one of these tables has a schema drift between Postgres and MySQL, from one column being bigint and the other being integer to complicated issues.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Aklapper removed a subscriber: Anomie.Fri, Oct 16, 5:02 PM

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