From 20cfed18d902526219c3542f29a4073575c22715 Mon Sep 17 00:00:00 2001 From: Tim Starling Date: Sat, 6 Sep 2008 07:05:14 +0000 Subject: [PATCH] Fixed bug 15148, total breakage of Special:BlockIP on PostgreSQL: * Migrated to a unique index for IP block conflict detection, like we did for MySQL some aeons ago. * Modified DatabasePostgres to return a correct affected row count for INSERT IGNORE. Tested for single and multi. * Fixed an unrelated bug: duplicate index rc_timestamp in tables.sql * Tested for install and upgrade on PG 8.3. No changelog because I'll backport it to 1.13. --- includes/db/DatabasePostgres.php | 19 +++++++++++++------ .../archives/patch-ipb_address_unique.sql | 2 ++ maintenance/postgres/tables.sql | 5 ++--- maintenance/updaters.inc | 10 +++++++++- 4 files changed, 26 insertions(+), 10 deletions(-) create mode 100644 maintenance/postgres/archives/patch-ipb_address_unique.sql diff --git a/includes/db/DatabasePostgres.php b/includes/db/DatabasePostgres.php index 70a635bdf7..7a117e79a3 100644 --- a/includes/db/DatabasePostgres.php +++ b/includes/db/DatabasePostgres.php @@ -72,6 +72,7 @@ class DatabasePostgres extends Database { var $mInsertId = NULL; var $mLastResult = NULL; var $numeric_version = NULL; + var $mAffectedRows = NULL; function DatabasePostgres($server = false, $user = false, $password = false, $dbName = false, $failFunction = false, $flags = 0 ) @@ -546,9 +547,11 @@ class DatabasePostgres extends Database { function doQuery( $sql ) { if (function_exists('mb_convert_encoding')) { - return $this->mLastResult=pg_query( $this->mConn , mb_convert_encoding($sql,'UTF-8') ); + $sql = mb_convert_encoding($sql,'UTF-8'); } - return $this->mLastResult=pg_query( $this->mConn , $sql); + $this->mLastResult = pg_query( $this->mConn, $sql); + $this->mAffectedRows = NULL; // use pg_affected_rows(mLastResult) + return $this->mLastResult; } function queryIgnore( $sql, $fname = '' ) { @@ -641,9 +644,12 @@ class DatabasePostgres extends Database { } function affectedRows() { - if( !isset( $this->mLastResult ) or ! $this->mLastResult ) + if ( !is_null( $this->mAffectedRows ) ) { + // Forced result for simulated queries + return $this->mAffectedRows; + } + if( empty( $this->mLastResult ) ) return 0; - return pg_affected_rows( $this->mLastResult ); } @@ -809,7 +815,6 @@ class DatabasePostgres extends Database { $sql .= '(' . $this->makeList( $args ) . ')'; $res = (bool)$this->query( $sql, $fname, $ignore ); - if ( $ignore ) { $bar = pg_last_error(); if ($bar != false) { @@ -821,13 +826,15 @@ class DatabasePostgres extends Database { } } } - if ( $ignore ) { $olde = error_reporting( $olde ); if ($didbegin) { $this->commit(); } + // Set the affected row count for the whole operation + $this->mAffectedRows = $numrowsinserted; + // IGNORE always returns true return true; } diff --git a/maintenance/postgres/archives/patch-ipb_address_unique.sql b/maintenance/postgres/archives/patch-ipb_address_unique.sql new file mode 100644 index 0000000000..9cfc6318c0 --- /dev/null +++ b/maintenance/postgres/archives/patch-ipb_address_unique.sql @@ -0,0 +1,2 @@ +DROP INDEX IF EXISTS ipb_address; +CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only); diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 44156c7189..1b5358df5b 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -241,9 +241,8 @@ CREATE TABLE ipblocks ( ipb_range_end TEXT, ipb_deleted SMALLINT NOT NULL DEFAULT 0, ipb_block_email SMALLINT NOT NULL DEFAULT 0 - ); -CREATE INDEX ipb_address ON ipblocks (ipb_address); +CREATE UNIQUE INDEX ipb_address_unique ON ipblocks (ipb_address,ipb_user,ipb_auto,ipb_anon_only); CREATE INDEX ipb_user ON ipblocks (ipb_user); CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end); @@ -352,7 +351,7 @@ CREATE TABLE recentchanges ( rc_params TEXT ); CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); -CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp) WHERE rc_bot = '0'; +CREATE INDEX rc_timestamp_bot ON recentchanges (rc_timestamp) WHERE rc_bot = '0'; CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title); CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id); CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp); diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index 63c00c060f..09e4680167 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -1686,7 +1686,7 @@ function do_postgres_updates() { $wgDatabase->query("CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title)"); } else - echo "... index \"pagelink_unique_index\" aready exists\n"; + echo "... index \"pagelink_unique_index\" already exists\n"; if (pg_fkey_deltype("revision_rev_user_fkey") == 'r') { echo "... constraint \"revision_rev_user_fkey\" is ON DELETE RESTRICT\n"; @@ -1696,6 +1696,14 @@ function do_postgres_updates() { dbsource(archive('patch-revision_rev_user_fkey.sql')); } + # Fix ipb_address index + if (pg_index_exists('ipblocks', 'ipb_address_unique' )) { + echo "... have ipb_address_unique\n"; + } else { + echo "Adding ipb_address_unique index\n"; + dbsource(archive('patch-ipb_address_unique.sql')); + } + global $wgExtNewTables, $wgExtPGNewFields, $wgExtNewIndexes; # Add missing extension tables foreach ( $wgExtNewTables as $nt ) { -- 2.20.1