MediaWiki master
DatabasePostgres.php
Go to the documentation of this file.
1<?php
21// Suppress UnusedPluginSuppression because Phan on PHP 7.4 and PHP 8.1 need different suppressions
22// @phan-file-suppress UnusedPluginSuppression,UnusedPluginFileSuppression
23
24namespace Wikimedia\Rdbms;
25
26use RuntimeException;
29use Wikimedia\WaitConditionLoop;
30
38 private $port;
40 private $tempSchema;
42 private $numericVersion;
43
45 private $lastResultHandle;
46
48 protected $platform;
49
55 public function __construct( array $params ) {
56 $this->port = intval( $params['port'] ?? null );
57 parent::__construct( $params );
58
59 $this->platform = new PostgresPlatform(
60 $this,
61 $this->logger,
62 $this->currentDomain,
63 $this->errorLogger
64 );
65 $this->replicationReporter = new ReplicationReporter(
66 $params['topologyRole'],
67 $this->logger,
68 $params['srvCache']
69 );
70 }
71
72 public function getType() {
73 return 'postgres';
74 }
75
76 protected function open( $server, $user, $password, $db, $schema, $tablePrefix ) {
77 if ( !function_exists( 'pg_connect' ) ) {
79 "Postgres functions missing, have you compiled PHP with the --with-pgsql\n" .
80 "option? (Note: if you recently installed PHP, you may need to restart your\n" .
81 "webserver and database)"
82 );
83 }
84
85 $this->close( __METHOD__ );
86
87 $connectVars = [
88 // A database must be specified in order to connect to Postgres. If $dbName is not
89 // specified, then use the standard "postgres" database that should exist by default.
90 'dbname' => ( $db !== null && $db !== '' ) ? $db : 'postgres',
91 'user' => $user,
92 'password' => $password
93 ];
94 if ( $server !== null && $server !== '' ) {
95 $connectVars['host'] = $server;
96 }
97 if ( $this->port > 0 ) {
98 $connectVars['port'] = $this->port;
99 }
100 if ( $this->ssl ) {
101 $connectVars['sslmode'] = 'require';
102 }
103 $connectString = $this->makeConnectionString( $connectVars );
104
105 $this->installErrorHandler();
106 try {
107 $this->conn = pg_connect( $connectString, PGSQL_CONNECT_FORCE_NEW ) ?: null;
108 } catch ( RuntimeException $e ) {
109 $this->restoreErrorHandler();
110 throw $this->newExceptionAfterConnectError( $e->getMessage() );
111 }
112 $error = $this->restoreErrorHandler();
113
114 if ( !$this->conn ) {
115 throw $this->newExceptionAfterConnectError( $error ?: $this->lastError() );
116 }
117
118 try {
119 // Since no transaction is active at this point, any SET commands should apply
120 // for the entire session (e.g. will not be reverted on transaction rollback).
121 // See https://www.postgresql.org/docs/8.3/sql-set.html
122 $variables = [
123 'client_encoding' => 'UTF8',
124 'datestyle' => 'ISO, YMD',
125 'timezone' => 'GMT',
126 'standard_conforming_strings' => 'on',
127 'bytea_output' => 'escape',
128 'client_min_messages' => 'ERROR'
129 ];
130 foreach ( $variables as $var => $val ) {
131 $sql = 'SET ' . $this->platform->addIdentifierQuotes( $var ) . ' = ' . $this->addQuotes( $val );
132 $query = new Query( $sql, self::QUERY_NO_RETRY | self::QUERY_CHANGE_TRX, 'SET' );
133 $this->query( $query, __METHOD__ );
134 }
135 $this->determineCoreSchema( $schema );
136 $this->currentDomain = new DatabaseDomain( $db, $schema, $tablePrefix );
137 $this->platform->setCurrentDomain( $this->currentDomain );
138 } catch ( RuntimeException $e ) {
139 throw $this->newExceptionAfterConnectError( $e->getMessage() );
140 }
141 }
142
143 public function databasesAreIndependent() {
144 return true;
145 }
146
147 public function doSelectDomain( DatabaseDomain $domain ) {
148 $database = $domain->getDatabase();
149 if ( $database === null ) {
150 // A null database means "don't care" so leave it as is and update the table prefix
151 $this->currentDomain = new DatabaseDomain(
152 $this->currentDomain->getDatabase(),
153 $domain->getSchema() ?? $this->currentDomain->getSchema(),
154 $domain->getTablePrefix()
155 );
156 $this->platform->setCurrentDomain( $this->currentDomain );
157 } elseif ( $this->getDBname() !== $database ) {
158 // Postgres doesn't support selectDB in the same way MySQL does.
159 // So if the DB name doesn't match the open connection, open a new one
160 $this->open(
161 $this->connectionParams[self::CONN_HOST],
162 $this->connectionParams[self::CONN_USER],
163 $this->connectionParams[self::CONN_PASSWORD],
164 $database,
165 $domain->getSchema(),
166 $domain->getTablePrefix()
167 );
168 } else {
169 $this->currentDomain = $domain;
170 $this->platform->setCurrentDomain( $domain );
171 }
172
173 return true;
174 }
175
180 private function makeConnectionString( $vars ) {
181 $s = '';
182 foreach ( $vars as $name => $value ) {
183 $s .= "$name='" . str_replace( [ "\\", "'" ], [ "\\\\", "\\'" ], $value ) . "' ";
184 }
185
186 return $s;
187 }
188
189 protected function closeConnection() {
190 return $this->conn ? pg_close( $this->conn ) : true;
191 }
192
193 public function doSingleStatementQuery( string $sql ): QueryStatus {
194 $conn = $this->getBindingHandle();
195
196 $sql = mb_convert_encoding( $sql, 'UTF-8' );
197 // Clear any previously left over result
198 while ( $priorRes = pg_get_result( $conn ) ) {
199 pg_free_result( $priorRes );
200 }
201
202 if ( pg_send_query( $conn, $sql ) === false ) {
203 throw new DBUnexpectedError( $this, "Unable to post new query to PostgreSQL\n" );
204 }
205
206 // Newer PHP versions use PgSql\Result instead of resource variables
207 // https://www.php.net/manual/en/function.pg-get-result.php
208 $pgRes = pg_get_result( $conn );
209 // Phan on PHP 7.4 and PHP 8.1 need different suppressions
210 // @phan-suppress-next-line PhanTypeMismatchProperty,PhanTypeMismatchPropertyProbablyReal
211 $this->lastResultHandle = $pgRes;
212 $res = pg_result_error( $pgRes ) ? false : $pgRes;
213
214 return new QueryStatus(
215 // @phan-suppress-next-line PhanTypeMismatchArgument
216 is_bool( $res ) ? $res : new PostgresResultWrapper( $this, $conn, $res ),
217 $pgRes ? pg_affected_rows( $pgRes ) : 0,
218 $this->lastError(),
219 $this->lastErrno()
220 );
221 }
222
223 protected function dumpError() {
224 $diags = [
225 PGSQL_DIAG_SEVERITY,
226 PGSQL_DIAG_SQLSTATE,
227 PGSQL_DIAG_MESSAGE_PRIMARY,
228 PGSQL_DIAG_MESSAGE_DETAIL,
229 PGSQL_DIAG_MESSAGE_HINT,
230 PGSQL_DIAG_STATEMENT_POSITION,
231 PGSQL_DIAG_INTERNAL_POSITION,
232 PGSQL_DIAG_INTERNAL_QUERY,
233 PGSQL_DIAG_CONTEXT,
234 PGSQL_DIAG_SOURCE_FILE,
235 PGSQL_DIAG_SOURCE_LINE,
236 PGSQL_DIAG_SOURCE_FUNCTION
237 ];
238 foreach ( $diags as $d ) {
239 $this->logger->debug( sprintf( "PgSQL ERROR(%d): %s",
240 // @phan-suppress-next-line PhanTypeMismatchArgumentInternal
241 $d, pg_result_error_field( $this->lastResultHandle, $d ) ) );
242 }
243 }
244
245 protected function lastInsertId() {
246 // Avoid using query() to prevent unwanted side-effects like changing affected
247 // row counts or connection retries. Note that lastval() is connection-specific.
248 // Note that this causes "lastval is not yet defined in this session" errors if
249 // nextval() was never directly or implicitly triggered (error out any transaction).
250 $qs = $this->doSingleStatementQuery( "SELECT lastval() AS id" );
251
252 return $qs->res ? (int)$qs->res->fetchRow()['id'] : 0;
253 }
254
255 public function lastError() {
256 if ( $this->conn ) {
257 if ( $this->lastResultHandle ) {
258 // @phan-suppress-next-line PhanTypeMismatchArgumentInternal
259 return pg_result_error( $this->lastResultHandle );
260 } else {
261 return pg_last_error() ?: $this->lastConnectError;
262 }
263 }
264
265 return $this->getLastPHPError() ?: 'No database connection';
266 }
267
268 public function lastErrno() {
269 if ( $this->lastResultHandle ) {
270 // @phan-suppress-next-line PhanTypeMismatchArgumentInternal
271 $lastErrno = pg_result_error_field( $this->lastResultHandle, PGSQL_DIAG_SQLSTATE );
272 if ( $lastErrno !== false ) {
273 return $lastErrno;
274 }
275 }
276
277 return '00000';
278 }
279
295 public function estimateRowCount( $table, $var = '*', $conds = '',
296 $fname = __METHOD__, $options = [], $join_conds = []
297 ): int {
298 $conds = $this->platform->normalizeConditions( $conds, $fname );
299 $column = $this->platform->extractSingleFieldFromList( $var );
300 if ( is_string( $column ) && !in_array( $column, [ '*', '1' ] ) ) {
301 $conds[] = "$column IS NOT NULL";
302 }
303
304 $options['EXPLAIN'] = true;
305 $res = $this->select( $table, $var, $conds, $fname, $options, $join_conds );
306 $rows = -1;
307 if ( $res ) {
308 $row = $res->fetchRow();
309 $count = [];
310 if ( preg_match( '/rows=(\d+)/', $row[0], $count ) ) {
311 $rows = (int)$count[1];
312 }
313 }
314
315 return $rows;
316 }
317
318 public function indexInfo( $table, $index, $fname = __METHOD__ ) {
319 $query = new Query(
320 "SELECT indexname FROM pg_indexes WHERE tablename='$table'",
321 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
322 'SELECT'
323 );
324 $res = $this->query( $query );
325 if ( !$res ) {
326 return null;
327 }
328 foreach ( $res as $row ) {
329 if ( $row->indexname == $this->platform->indexName( $index ) ) {
330 return $row;
331 }
332 }
333
334 return false;
335 }
336
337 public function indexAttributes( $index, $schema = false ) {
338 if ( $schema === false ) {
339 $schemas = $this->getCoreSchemas();
340 } else {
341 $schemas = [ $schema ];
342 }
343
344 $eindex = $this->addQuotes( $index );
345
346 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
347 foreach ( $schemas as $schema ) {
348 $eschema = $this->addQuotes( $schema );
349 /*
350 * A subquery would be not needed if we didn't care about the order
351 * of attributes, but we do
352 */
353 $sql = <<<__INDEXATTR__
354
355 SELECT opcname,
356 attname,
357 i.indoption[s.g] as option,
358 pg_am.amname
359 FROM
360 (SELECT generate_series(array_lower(isub.indkey,1), array_upper(isub.indkey,1)) AS g
361 FROM
362 pg_index isub
363 JOIN pg_class cis
364 ON cis.oid=isub.indexrelid
365 JOIN pg_namespace ns
366 ON cis.relnamespace = ns.oid
367 WHERE cis.relname=$eindex AND ns.nspname=$eschema) AS s,
368 pg_attribute,
369 pg_opclass opcls,
370 pg_am,
371 pg_class ci
372 JOIN pg_index i
373 ON ci.oid=i.indexrelid
374 JOIN pg_class ct
375 ON ct.oid = i.indrelid
376 JOIN pg_namespace n
377 ON ci.relnamespace = n.oid
378 WHERE
379 ci.relname=$eindex AND n.nspname=$eschema
380 AND attrelid = ct.oid
381 AND i.indkey[s.g] = attnum
382 AND i.indclass[s.g] = opcls.oid
383 AND pg_am.oid = opcls.opcmethod
384__INDEXATTR__;
385 $query = new Query( $sql, $flags, 'SELECT' );
386 $res = $this->query( $query, __METHOD__ );
387 $a = [];
388 if ( $res ) {
389 foreach ( $res as $row ) {
390 $a[] = [
391 $row->attname,
392 $row->opcname,
393 $row->amname,
394 $row->option ];
395 }
396 return $a;
397 }
398 }
399 return null;
400 }
401
402 public function indexUnique( $table, $index, $fname = __METHOD__ ) {
403 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'" .
404 " AND indexdef LIKE 'CREATE UNIQUE%(" .
405 $this->strencode( $this->platform->indexName( $index ) ) .
406 ")'";
407 $query = new Query( $sql, self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE, 'SELECT' );
408 $res = $this->query( $query, $fname );
409 return $res && $res->numRows() > 0;
410 }
411
430 protected function doInsertSelectNative(
431 $destTable,
432 $srcTable,
433 array $varMap,
434 $conds,
435 $fname,
436 array $insertOptions,
437 array $selectOptions,
438 $selectJoinConds
439 ) {
440 if ( in_array( 'IGNORE', $insertOptions ) ) {
441 // Use "ON CONFLICT DO" if we have it for IGNORE
442 $destTableEnc = $this->tableName( $destTable );
443
444 $selectSql = $this->selectSQLText(
445 $srcTable,
446 array_values( $varMap ),
447 $conds,
448 $fname,
449 $selectOptions,
450 $selectJoinConds
451 );
452
453 $sql = "INSERT INTO $destTableEnc (" . implode( ',', array_keys( $varMap ) ) . ') ' .
454 $selectSql . ' ON CONFLICT DO NOTHING';
455 $query = new Query( $sql, self::QUERY_CHANGE_ROWS, 'INSERT', $destTable );
456 $this->query( $query, $fname );
457 } else {
458 parent::doInsertSelectNative( $destTable, $srcTable, $varMap, $conds, $fname,
459 $insertOptions, $selectOptions, $selectJoinConds );
460 }
461 }
462
463 public function nextSequenceValue( $seqName ) {
464 return new NextSequenceValue;
465 }
466
471 public function getValueTypesForWithClause( $table ) {
472 $typesByColumn = [];
473
474 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
475 $encTable = $this->addQuotes( $table );
476 foreach ( $this->getCoreSchemas() as $schema ) {
477 $encSchema = $this->addQuotes( $schema );
478 $sql = "SELECT column_name,udt_name " .
479 "FROM information_schema.columns " .
480 "WHERE table_name = $encTable AND table_schema = $encSchema";
481 $query = new Query( $sql, $flags, 'SELECT' );
482 $res = $this->query( $query, __METHOD__ );
483 if ( $res->numRows() ) {
484 foreach ( $res as $row ) {
485 $typesByColumn[$row->column_name] = $row->udt_name;
486 }
487 break;
488 }
489 }
490
491 return $typesByColumn;
492 }
493
494 public function textFieldSize( $table, $field ) {
495 wfDeprecated( __METHOD__, '1.43' );
496 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
497 $encTable = $this->tableName( $table );
498 $sql = "SELECT t.typname as ftype,a.atttypmod as size
499 FROM pg_class c, pg_attribute a, pg_type t
500 WHERE relname='$encTable' AND a.attrelid=c.oid AND
501 a.atttypid=t.oid and a.attname='$field'";
502 $query = new Query( $sql, $flags, 'SELECT' );
503 $res = $this->query( $query, __METHOD__ );
504 $row = $res->fetchObject();
505 if ( $row->ftype == 'varchar' ) {
506 $size = $row->size - 4;
507 } else {
508 $size = $row->size;
509 }
510
511 return $size;
512 }
513
514 public function wasDeadlock() {
515 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
516 return $this->lastErrno() === '40P01';
517 }
518
519 protected function isConnectionError( $errno ) {
520 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
521 static $codes = [ '08000', '08003', '08006', '08001', '08004', '57P01', '57P03', '53300' ];
522
523 return in_array( $errno, $codes, true );
524 }
525
526 protected function isQueryTimeoutError( $errno ) {
527 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
528 return ( $errno === '57014' );
529 }
530
531 protected function isKnownStatementRollbackError( $errno ) {
532 return false; // transaction has to be rolled-back from error state
533 }
534
535 public function duplicateTableStructure(
536 $oldName, $newName, $temporary = false, $fname = __METHOD__
537 ) {
538 $newNameE = $this->platform->addIdentifierQuotes( $newName );
539 $oldNameE = $this->platform->addIdentifierQuotes( $oldName );
540
541 $temporary = $temporary ? 'TEMPORARY' : '';
542 $query = new Query(
543 "CREATE $temporary TABLE $newNameE " .
544 "(LIKE $oldNameE INCLUDING DEFAULTS INCLUDING INDEXES)",
545 self::QUERY_PSEUDO_PERMANENT | self::QUERY_CHANGE_SCHEMA,
546 $temporary ? 'CREATE TEMPORARY' : 'CREATE',
547 // Use a dot to avoid double-prefixing in Database::getTempTableWrites()
548 '.' . $newName
549 );
550 $ret = $this->query( $query, $fname );
551 if ( !$ret ) {
552 return $ret;
553 }
554
555 $sql = 'SELECT attname FROM pg_class c'
556 . ' JOIN pg_namespace n ON (n.oid = c.relnamespace)'
557 . ' JOIN pg_attribute a ON (a.attrelid = c.oid)'
558 . ' JOIN pg_attrdef d ON (c.oid=d.adrelid and a.attnum=d.adnum)'
559 . ' WHERE relkind = \'r\''
560 . ' AND nspname = ' . $this->addQuotes( $this->getCoreSchema() )
561 . ' AND relname = ' . $this->addQuotes( $oldName )
562 . ' AND pg_get_expr(adbin, adrelid) LIKE \'nextval(%\'';
563 $query = new Query(
564 $sql,
565 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
566 'SELECT'
567 );
568
569 $res = $this->query( $query, $fname );
570 $row = $res->fetchObject();
571 if ( $row ) {
572 $field = $row->attname;
573 $newSeq = "{$newName}_{$field}_seq";
574 $fieldE = $this->platform->addIdentifierQuotes( $field );
575 $newSeqE = $this->platform->addIdentifierQuotes( $newSeq );
576 $newSeqQ = $this->addQuotes( $newSeq );
577 $query = new Query(
578 "CREATE $temporary SEQUENCE $newSeqE OWNED BY $newNameE.$fieldE",
579 self::QUERY_CHANGE_SCHEMA,
580 'CREATE',
581 // Do not treat this is as a table modification on top of the CREATE above.
582 null
583 );
584 $this->query( $query, $fname );
585 $query = new Query(
586 "ALTER TABLE $newNameE ALTER COLUMN $fieldE SET DEFAULT nextval({$newSeqQ}::regclass)",
587 self::QUERY_CHANGE_SCHEMA,
588 'ALTER',
589 // Do not treat this is as a table modification on top of the CREATE above.
590 null
591 );
592 $this->query( $query, $fname );
593 }
594
595 return $ret;
596 }
597
598 public function truncateTable( $table, $fname = __METHOD__ ) {
599 $sql = "TRUNCATE TABLE " . $this->tableName( $table ) . " RESTART IDENTITY";
600 $query = new Query( $sql, self::QUERY_CHANGE_SCHEMA, 'TRUNCATE', $table );
601 $this->query( $query, $fname );
602 }
603
610 public function listTables( $prefix = '', $fname = __METHOD__ ) {
611 $eschemas = implode( ',', array_map( [ $this, 'addQuotes' ], $this->getCoreSchemas() ) );
612 $query = new Query(
613 "SELECT DISTINCT tablename FROM pg_tables WHERE schemaname IN ($eschemas)",
614 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
615 'SELECT'
616 );
617 $result = $this->query( $query, $fname );
618 $endArray = [];
619
620 foreach ( $result as $table ) {
621 $vars = get_object_vars( $table );
622 $table = array_pop( $vars );
623 if ( $prefix == '' || strpos( $table, $prefix ) === 0 ) {
624 $endArray[] = $table;
625 }
626 }
627
628 return $endArray;
629 }
630
649 private function pg_array_parse( $text, &$output, $limit = false, $offset = 1 ) {
650 if ( $limit === false ) {
651 $limit = strlen( $text ) - 1;
652 $output = [];
653 }
654 if ( $text == '{}' ) {
655 return $output;
656 }
657 do {
658 if ( $text[$offset] != '{' ) {
659 preg_match( "/(\\{?\"([^\"\\\\]|\\\\.)*\"|[^,{}]+)+([,}]+)/",
660 $text, $match, 0, $offset );
661 $offset += strlen( $match[0] );
662 $output[] = ( $match[1][0] != '"'
663 ? $match[1]
664 : stripcslashes( substr( $match[1], 1, -1 ) ) );
665 if ( $match[3] == '},' ) {
666 return $output;
667 }
668 } else {
669 $offset = $this->pg_array_parse( $text, $output, $limit, $offset + 1 );
670 }
671 } while ( $limit > $offset );
672
673 return $output;
674 }
675
676 public function getSoftwareLink() {
677 return '[{{int:version-db-postgres-url}} PostgreSQL]';
678 }
679
687 public function getCurrentSchema() {
688 $query = new Query(
689 "SELECT current_schema()",
690 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
691 'SELECT'
692 );
693 $res = $this->query( $query, __METHOD__ );
694 $row = $res->fetchRow();
695
696 return $row[0];
697 }
698
709 public function getSchemas() {
710 $query = new Query(
711 "SELECT current_schemas(false)",
712 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
713 'SELECT'
714 );
715 $res = $this->query( $query, __METHOD__ );
716 $row = $res->fetchRow();
717 $schemas = [];
718
719 /* PHP pgsql support does not support array type, "{a,b}" string is returned */
720
721 return $this->pg_array_parse( $row[0], $schemas );
722 }
723
733 public function getSearchPath() {
734 $query = new Query(
735 "SHOW search_path",
736 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
737 'SHOW'
738 );
739 $res = $this->query( $query, __METHOD__ );
740 $row = $res->fetchRow();
741
742 /* PostgreSQL returns SHOW values as strings */
743
744 return explode( ",", $row[0] );
745 }
746
754 private function setSearchPath( $search_path ) {
755 $query = new Query(
756 "SET search_path = " . implode( ", ", $search_path ),
757 self::QUERY_CHANGE_TRX,
758 'SET'
759 );
760 $this->query( $query, __METHOD__ );
761 }
762
777 public function determineCoreSchema( $desiredSchema ) {
778 if ( $this->trxLevel() ) {
779 // We do not want the schema selection to change on ROLLBACK or INSERT SELECT.
780 // See https://www.postgresql.org/docs/8.3/sql-set.html
781 throw new DBUnexpectedError(
782 $this,
783 __METHOD__ . ": a transaction is currently active"
784 );
785 }
786
787 if ( $this->schemaExists( $desiredSchema ) ) {
788 if ( in_array( $desiredSchema, $this->getSchemas() ) ) {
789 $this->platform->setCoreSchema( $desiredSchema );
790 $this->logger->debug(
791 "Schema \"" . $desiredSchema . "\" already in the search path\n" );
792 } else {
793 // Prepend the desired schema to the search path (T17816)
794 $search_path = $this->getSearchPath();
795 array_unshift( $search_path, $this->platform->addIdentifierQuotes( $desiredSchema ) );
796 $this->setSearchPath( $search_path );
797 $this->platform->setCoreSchema( $desiredSchema );
798 $this->logger->debug(
799 "Schema \"" . $desiredSchema . "\" added to the search path\n" );
800 }
801 } else {
802 $this->platform->setCoreSchema( $this->getCurrentSchema() );
803 $this->logger->debug(
804 "Schema \"" . $desiredSchema . "\" not found, using current \"" .
805 $this->getCoreSchema() . "\"\n" );
806 }
807 }
808
815 public function getCoreSchema() {
816 return $this->platform->getCoreSchema();
817 }
818
825 public function getCoreSchemas() {
826 if ( $this->tempSchema ) {
827 return [ $this->tempSchema, $this->getCoreSchema() ];
828 }
829 $query = new Query(
830 "SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = pg_my_temp_schema()",
831 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
832 'SELECT'
833 );
834 $res = $this->query( $query, __METHOD__ );
835 $row = $res->fetchObject();
836 if ( $row ) {
837 $this->tempSchema = $row->nspname;
838 return [ $this->tempSchema, $this->getCoreSchema() ];
839 }
840
841 return [ $this->getCoreSchema() ];
842 }
843
844 public function getServerVersion() {
845 if ( !isset( $this->numericVersion ) ) {
846 // Works on PG 7.4+
847 $this->numericVersion = pg_version( $this->getBindingHandle() )['server'];
848 }
849
850 return $this->numericVersion;
851 }
852
860 private function relationExists( $table, $types ) {
861 if ( !is_array( $types ) ) {
862 $types = [ $types ];
863 }
864 $schemas = $this->getCoreSchemas();
865 $components = $this->platform->qualifiedTableComponents( $table );
866 $etable = $this->addQuotes( end( $components ) );
867 foreach ( $schemas as $schema ) {
868 $eschema = $this->addQuotes( $schema );
869 $sql = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "
870 . "WHERE c.relnamespace = n.oid AND c.relname = $etable AND n.nspname = $eschema "
871 . "AND c.relkind IN ('" . implode( "','", $types ) . "')";
872 $query = new Query(
873 $sql,
874 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
875 'SELECT'
876 );
877 $res = $this->query( $query, __METHOD__ );
878 if ( $res && $res->numRows() ) {
879 return true;
880 }
881 }
882
883 return false;
884 }
885
886 public function tableExists( $table, $fname = __METHOD__ ) {
887 return $this->relationExists( $table, [ 'r', 'v' ] );
888 }
889
890 public function sequenceExists( $sequence ) {
891 return $this->relationExists( $sequence, 'S' );
892 }
893
894 public function constraintExists( $table, $constraint ) {
895 foreach ( $this->getCoreSchemas() as $schema ) {
896 $sql = sprintf( "SELECT 1 FROM information_schema.table_constraints " .
897 "WHERE constraint_schema = %s AND table_name = %s AND constraint_name = %s",
898 $this->addQuotes( $schema ),
899 $this->addQuotes( $table ),
900 $this->addQuotes( $constraint )
901 );
902 $query = new Query(
903 $sql,
904 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
905 'SELECT'
906 );
907 $res = $this->query( $query, __METHOD__ );
908 if ( $res && $res->numRows() ) {
909 return true;
910 }
911 }
912 return false;
913 }
914
920 public function schemaExists( $schema ) {
921 if ( !strlen( $schema ?? '' ) ) {
922 return false; // short-circuit
923 }
924 $query = new Query(
925 "SELECT 1 FROM pg_catalog.pg_namespace " .
926 "WHERE nspname = " . $this->addQuotes( $schema ) . " LIMIT 1",
927 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
928 'SELECT'
929 );
930 $res = $this->query( $query, __METHOD__ );
931
932 return ( $res->numRows() > 0 );
933 }
934
940 public function roleExists( $roleName ) {
941 $query = new Query(
942 "SELECT 1 FROM pg_catalog.pg_roles " .
943 "WHERE rolname = " . $this->addQuotes( $roleName ) . " LIMIT 1",
944 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
945 'SELECT'
946 );
947 $res = $this->query( $query, __METHOD__ );
948
949 return ( $res->numRows() > 0 );
950 }
951
957 public function fieldInfo( $table, $field ) {
958 return PostgresField::fromText( $this, $table, $field );
959 }
960
961 public function encodeBlob( $b ) {
962 $conn = $this->getBindingHandle();
963
964 return new PostgresBlob( pg_escape_bytea( $conn, $b ) );
965 }
966
967 public function decodeBlob( $b ) {
968 if ( $b instanceof PostgresBlob ) {
969 $b = $b->fetch();
970 } elseif ( $b instanceof Blob ) {
971 return $b->fetch();
972 }
973
974 return pg_unescape_bytea( $b );
975 }
976
977 public function strencode( $s ) {
978 // Should not be called by us
979 return pg_escape_string( $this->getBindingHandle(), (string)$s );
980 }
981
982 public function addQuotes( $s ) {
983 $conn = $this->getBindingHandle();
984
985 if ( $s === null ) {
986 return 'NULL';
987 } elseif ( is_bool( $s ) ) {
988 return (string)intval( $s );
989 } elseif ( is_int( $s ) ) {
990 return (string)$s;
991 } elseif ( $s instanceof Blob ) {
992 if ( $s instanceof PostgresBlob ) {
993 $s = $s->fetch();
994 } else {
995 $s = pg_escape_bytea( $conn, $s->fetch() );
996 }
997 return "'$s'";
998 } elseif ( $s instanceof NextSequenceValue ) {
999 return 'DEFAULT';
1000 }
1001
1002 return "'" . pg_escape_string( $conn, (string)$s ) . "'";
1003 }
1004
1005 public function streamStatementEnd( &$sql, &$newLine ) {
1006 # Allow dollar quoting for function declarations
1007 if ( str_starts_with( $newLine, '$mw$' ) ) {
1008 if ( $this->delimiter ) {
1009 $this->delimiter = false;
1010 } else {
1011 $this->delimiter = ';';
1012 }
1013 }
1014
1015 return parent::streamStatementEnd( $sql, $newLine );
1016 }
1017
1018 public function doLockIsFree( string $lockName, string $method ) {
1019 $query = new Query(
1020 $this->platform->lockIsFreeSQLText( $lockName ),
1021 self::QUERY_CHANGE_LOCKS,
1022 'SELECT'
1023 );
1024 $res = $this->query( $query, $method );
1025 $row = $res->fetchObject();
1026
1027 return (bool)$row->unlocked;
1028 }
1029
1030 public function doLock( string $lockName, string $method, int $timeout ) {
1031 $query = new Query(
1032 $this->platform->lockSQLText( $lockName, $timeout ),
1033 self::QUERY_CHANGE_LOCKS,
1034 'SELECT'
1035 );
1036
1037 $acquired = null;
1038 $loop = new WaitConditionLoop(
1039 function () use ( $query, $method, &$acquired ) {
1040 $res = $this->query( $query, $method );
1041 $row = $res->fetchObject();
1042
1043 if ( $row->acquired !== null ) {
1044 $acquired = (float)$row->acquired;
1045
1046 return WaitConditionLoop::CONDITION_REACHED;
1047 }
1048
1049 return WaitConditionLoop::CONDITION_CONTINUE;
1050 },
1051 $timeout
1052 );
1053 $loop->invoke();
1054
1055 return $acquired;
1056 }
1057
1058 public function doUnlock( string $lockName, string $method ) {
1059 $query = new Query(
1060 $this->platform->unlockSQLText( $lockName ),
1061 self::QUERY_CHANGE_LOCKS,
1062 'SELECT'
1063 );
1064 $result = $this->query( $query, $method );
1065 $row = $result->fetchObject();
1066
1067 return (bool)$row->released;
1068 }
1069
1070 protected function doFlushSession( $fname ) {
1071 $flags = self::QUERY_CHANGE_LOCKS | self::QUERY_NO_RETRY;
1072
1073 // https://www.postgresql.org/docs/9.1/functions-admin.html
1074 $sql = "SELECT pg_advisory_unlock_all()";
1075 $query = new Query( $sql, $flags, 'UNLOCK' );
1076 $qs = $this->executeQuery( $query, __METHOD__, $flags );
1077 if ( $qs->res === false ) {
1078 $this->reportQueryError( $qs->message, $qs->code, $sql, $fname, true );
1079 }
1080 }
1081
1082 public function serverIsReadOnly() {
1083 $query = new Query(
1084 "SHOW default_transaction_read_only",
1085 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
1086 'SHOW'
1087 );
1088 $res = $this->query( $query, __METHOD__ );
1089 $row = $res->fetchObject();
1090
1091 return $row && strtolower( $row->default_transaction_read_only ) === 'on';
1092 }
1093
1094 protected function getInsertIdColumnForUpsert( $table ) {
1095 $column = null;
1096
1097 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
1098 $components = $this->platform->qualifiedTableComponents( $table );
1099 $encTable = $this->addQuotes( end( $components ) );
1100 foreach ( $this->getCoreSchemas() as $schema ) {
1101 $encSchema = $this->addQuotes( $schema );
1102 $query = new Query(
1103 "SELECT column_name,data_type,column_default " .
1104 "FROM information_schema.columns " .
1105 "WHERE table_name = $encTable AND table_schema = $encSchema",
1106 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE,
1107 'SELECT'
1108 );
1109 $res = $this->query( $query, __METHOD__ );
1110 if ( $res->numRows() ) {
1111 foreach ( $res as $row ) {
1112 if (
1113 $row->column_default !== null &&
1114 str_starts_with( $row->column_default, "nextval(" ) &&
1115 in_array( $row->data_type, [ 'integer', 'bigint' ], true )
1116 ) {
1117 $column = $row->column_name;
1118 }
1119 }
1120 break;
1121 }
1122 }
1123
1124 return $column;
1125 }
1126
1127 public static function getAttributes() {
1128 return [ self::ATTR_SCHEMAS_AS_TABLE_GROUPS => true ];
1129 }
1130}
wfDeprecated( $function, $version=false, $component=false, $callerOffset=2)
Logs a warning that a deprecated feature was used.
array $params
The job parameters.
Class to handle database/schema/prefix specifications for IDatabase.
Postgres database abstraction layer.
truncateTable( $table, $fname=__METHOD__)
Delete all data in a table and reset any sequences owned by that table.
getCoreSchemas()
Return schema names for temporary tables and core application tables.
determineCoreSchema( $desiredSchema)
Determine default schema for the current application Adjust this session schema search path if desire...
duplicateTableStructure( $oldName, $newName, $temporary=false, $fname=__METHOD__)
Creates a new table with structure copied from existing table.
isQueryTimeoutError( $errno)
Checks whether the cause of the error is detected to be a timeout.
doLock(string $lockName, string $method, int $timeout)
indexAttributes( $index, $schema=false)
databasesAreIndependent()
Returns true if DBs are assumed to be on potentially different servers.
doUnlock(string $lockName, string $method)
doSingleStatementQuery(string $sql)
Run a query and return a QueryStatus instance with the query result information.
streamStatementEnd(&$sql, &$newLine)
Called by sourceStream() to check if we've reached a statement end.
doLockIsFree(string $lockName, string $method)
nextSequenceValue( $seqName)
Deprecated method, calls should be removed.
doSelectDomain(DatabaseDomain $domain)
roleExists( $roleName)
Returns true if a given role (i.e.
getSchemas()
Return list of schemas which are accessible without schema name This is list does not contain magic k...
indexInfo( $table, $index, $fname=__METHOD__)
Get information about an index into an object.
schemaExists( $schema)
Query whether a given schema exists.
estimateRowCount( $table, $var=' *', $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Estimate rows in dataset Returns estimated count, based on EXPLAIN output This is not necessarily an ...
lastError()
Get the RDBMS-specific error description from the last attempted query statement.
addQuotes( $s)
Escape and quote a raw value string for use in a SQL query.
wasDeadlock()
Determines if the last failure was due to a deadlock.
tableExists( $table, $fname=__METHOD__)
Query whether a given table exists.
lastErrno()
Get the RDBMS-specific error code from the last attempted query statement.
getCoreSchema()
Return schema name for core application tables.
strencode( $s)
Wrapper for addslashes()
lastInsertId()
Get a row ID from the last insert statement to implicitly assign one within the session.
isConnectionError( $errno)
Do not use this method outside of Database/DBError classes.
indexUnique( $table, $index, $fname=__METHOD__)
Determines if a given index is unique.
open( $server, $user, $password, $db, $schema, $tablePrefix)
Open a new connection to the database (closing any existing one)
getServerVersion()
A string describing the current software version.
textFieldSize( $table, $field)
Returns the size of a text field, or -1 for "unlimited".
getCurrentSchema()
Return current schema (executes SELECT current_schema()) Needs transaction.
getSearchPath()
Return search patch for schemas This is different from getSchemas() since it contain magic keywords (...
decodeBlob( $b)
Some DBMSs return a special placeholder object representing blob fields in result objects.
encodeBlob( $b)
Some DBMSs have a special format for inserting into blob fields, they don't allow simple quoted strin...
closeConnection()
Closes underlying database connection.
constraintExists( $table, $constraint)
getType()
Get the RDBMS type of the server (e.g.
doFlushSession( $fname)
Reset the server-side session state for named locks and table locks.
listTables( $prefix='', $fname=__METHOD__)
getSoftwareLink()
Returns a wikitext style link to the DB's website (e.g.
doInsertSelectNative( $destTable, $srcTable, array $varMap, $conds, $fname, array $insertOptions, array $selectOptions, $selectJoinConds)
INSERT SELECT wrapper $varMap must be an associative array of the form [ 'dest1' => 'source1',...
serverIsReadOnly()
bool Whether this DB server is running in server-side read-only mode query} 1.28
Relational database abstraction object.
Definition Database.php:43
restoreErrorHandler()
Restore the previous error handler and return the last PHP error for this DB.
Definition Database.php:435
object resource null $conn
Database connection.
Definition Database.php:64
newExceptionAfterConnectError( $error)
installErrorHandler()
Set a custom error handler for logging errors during database connection.
Definition Database.php:424
close( $fname=__METHOD__)
Close the database connection.
Definition Database.php:487
query( $sql, $fname=__METHOD__, $flags=0)
Run an SQL query statement and return the result.
Definition Database.php:631
getBindingHandle()
Get the underlying binding connection handle.
getDBname()
Get the current database name; null if there isn't one.
Used by Database::nextSequenceValue() so Database::insert() can detect values coming from the depreca...
Holds information on Query to be executed.
Definition Query.php:31