PostgreSQL: Fix syntax error in page_props index

Follows up I217c42656fb877ff35a36eb446a22bdaf119faac

That change introduced a syntax error during update.  I have verified
that this fixes the syntax problem so that update.php runs, but have
not verified that the new functionality behaves as intended.

Also, the PostgreSQL schema changes introduced during the update
process were not present at all in a fresh install to PostgreSQL.  Add
analogous changes to tables.sql.

Bug: 64807
Change-Id: I736344839ec478216402fdfe260968afbd663498

diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php
index a6d7cb2..8f6aac7 100644 (file)
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -406,9 +406,11 @@ class PostgresUpdater extends DatabaseUpdater {
                        array( 'addPgField', 'recentchanges', 'rc_source', "TEXT NOT NULL DEFAULT ''" ),
                        array( 'addPgField', 'page', 'page_links_updated', "TIMESTAMPTZ NULL" ),
                        array( 'addPgField', 'mwuser', 'user_password_expires', 'TIMESTAMPTZ NULL' ),
+
+                       // 1.24
                        array( 'addPgField', 'page_props', 'pp_sortkey', 'float NULL' ),
                        array( 'addPgIndex', 'page_props', 'pp_propname_sortkey_page',
-                                       '( pp_propname, pp_sortkey, pp_page ) WHERE ( pp_sortkey NOT NULL )' ),
+                                       '( pp_propname, pp_sortkey, pp_page ) WHERE ( pp_sortkey IS NOT NULL )' ),
                );
        }
 

diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index a3fb042..6a2c41d 100644 (file)
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -159,11 +159,13 @@ ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (p
 CREATE TABLE page_props (
   pp_page      INTEGER  NOT NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
   pp_propname  TEXT     NOT NULL,
-  pp_value     TEXT     NOT NULL
+  pp_value     TEXT     NOT NULL,
+  pp_sortkey   FLOAT
 );
 ALTER TABLE page_props ADD CONSTRAINT page_props_pk PRIMARY KEY (pp_page,pp_propname);
 CREATE INDEX page_props_propname ON page_props (pp_propname);
 CREATE UNIQUE INDEX pp_propname_page ON page_props (pp_propname,pp_page);
+CREATE INDEX pp_propname_sortkey_page ON page_props (pp_propname, pp_sortkey, pp_page) WHERE (pp_sortkey IS NOT NULL);
 
 CREATE SEQUENCE archive_ar_id_seq;
 CREATE TABLE archive (