MediaWiki REL1_39
DatabasePostgres.php
Go to the documentation of this file.
1<?php
20namespace Wikimedia\Rdbms;
21
22use RuntimeException;
24use Wikimedia\WaitConditionLoop;
25
33 private $port;
35 private $tempSchema;
37 private $keywordTableMap;
39 private $numericVersion;
40
42 private $lastResultHandle;
43
45 protected $platform;
46
54 public function __construct( array $params ) {
55 $this->port = intval( $params['port'] ?? null );
56
57 if ( isset( $params['keywordTableMap'] ) ) {
58 wfDeprecatedMsg( 'Passing keywordTableMap parameter to ' .
59 'DatabasePostgres::__construct() is deprecated', '1.37'
60 );
61
62 $this->keywordTableMap = $params['keywordTableMap'];
63 }
64
65 parent::__construct( $params );
66 $this->platform = new PostgresPlatform(
67 $this,
68 $params['queryLogger'],
69 $this->currentDomain,
70 $this->errorLogger
71 );
72 }
73
74 public function getType() {
75 return 'postgres';
76 }
77
78 protected function open( $server, $user, $password, $db, $schema, $tablePrefix ) {
79 if ( !function_exists( 'pg_connect' ) ) {
81 "Postgres functions missing, have you compiled PHP with the --with-pgsql\n" .
82 "option? (Note: if you recently installed PHP, you may need to restart your\n" .
83 "webserver and database)"
84 );
85 }
86
87 $this->close( __METHOD__ );
88
89 $connectVars = [
90 // A database must be specified in order to connect to Postgres. If $dbName is not
91 // specified, then use the standard "postgres" database that should exist by default.
92 'dbname' => ( $db !== null && $db !== '' ) ? $db : 'postgres',
93 'user' => $user,
94 'password' => $password
95 ];
96 if ( $server !== null && $server !== '' ) {
97 $connectVars['host'] = $server;
98 }
99 if ( $this->port > 0 ) {
100 $connectVars['port'] = $this->port;
101 }
102 if ( $this->ssl ) {
103 $connectVars['sslmode'] = 'require';
104 }
105 $connectString = $this->makeConnectionString( $connectVars );
106
107 $this->installErrorHandler();
108 try {
109 $this->conn = pg_connect( $connectString, PGSQL_CONNECT_FORCE_NEW ) ?: null;
110 } catch ( RuntimeException $e ) {
111 $this->restoreErrorHandler();
112 throw $this->newExceptionAfterConnectError( $e->getMessage() );
113 }
114 $error = $this->restoreErrorHandler();
115
116 if ( !$this->conn ) {
117 throw $this->newExceptionAfterConnectError( $error ?: $this->lastError() );
118 }
119
120 try {
121 // Since no transaction is active at this point, any SET commands should apply
122 // for the entire session (e.g. will not be reverted on transaction rollback).
123 // See https://www.postgresql.org/docs/8.3/sql-set.html
124 $variables = [
125 'client_encoding' => 'UTF8',
126 'datestyle' => 'ISO, YMD',
127 'timezone' => 'GMT',
128 'standard_conforming_strings' => 'on',
129 'bytea_output' => 'escape',
130 'client_min_messages' => 'ERROR'
131 ];
132 foreach ( $variables as $var => $val ) {
133 $this->query(
134 'SET ' . $this->platform->addIdentifierQuotes( $var ) . ' = ' . $this->addQuotes( $val ),
135 __METHOD__,
136 self::QUERY_NO_RETRY | self::QUERY_CHANGE_TRX
137 );
138 }
139 $this->determineCoreSchema( $schema );
140 $this->currentDomain = new DatabaseDomain( $db, $schema, $tablePrefix );
141 $this->platform->setCurrentDomain( $this->currentDomain );
142 } catch ( RuntimeException $e ) {
143 throw $this->newExceptionAfterConnectError( $e->getMessage() );
144 }
145 }
146
147 public function databasesAreIndependent() {
148 return true;
149 }
150
151 public function doSelectDomain( DatabaseDomain $domain ) {
152 if ( $this->getDBname() !== $domain->getDatabase() ) {
153 // Postgres doesn't support selectDB in the same way MySQL does.
154 // So if the DB name doesn't match the open connection, open a new one
155 $this->open(
156 $this->connectionParams[self::CONN_HOST],
157 $this->connectionParams[self::CONN_USER],
158 $this->connectionParams[self::CONN_PASSWORD],
159 $domain->getDatabase(),
160 $domain->getSchema(),
161 $domain->getTablePrefix()
162 );
163 } else {
164 $this->currentDomain = $domain;
165 $this->platform->setCurrentDomain( $this->currentDomain );
166 }
167
168 return true;
169 }
170
175 private function makeConnectionString( $vars ) {
176 $s = '';
177 foreach ( $vars as $name => $value ) {
178 $s .= "$name='" . str_replace( [ "\\", "'" ], [ "\\\\", "\\'" ], $value ) . "' ";
179 }
180
181 return $s;
182 }
183
184 protected function closeConnection() {
185 return $this->conn ? pg_close( $this->conn ) : true;
186 }
187
188 public function doSingleStatementQuery( string $sql ): QueryStatus {
189 $conn = $this->getBindingHandle();
190
191 $sql = mb_convert_encoding( $sql, 'UTF-8' );
192 // Clear any previously left over result
193 while ( $priorRes = pg_get_result( $conn ) ) {
194 pg_free_result( $priorRes );
195 }
196
197 if ( pg_send_query( $conn, $sql ) === false ) {
198 throw new DBUnexpectedError( $this, "Unable to post new query to PostgreSQL\n" );
199 }
200
201 // Newer PHP versions use PgSql\Result instead of resource variables
202 // https://www.php.net/manual/en/function.pg-get-result.php
203 $pgRes = pg_get_result( $conn );
204 $this->lastResultHandle = $pgRes;
205 $res = pg_result_error( $pgRes ) ? false : $pgRes;
206
207 return new QueryStatus(
208 is_bool( $res ) ? $res : new PostgresResultWrapper( $this, $conn, $res ),
209 $this->affectedRows(),
210 $this->lastError(),
211 $this->lastErrno()
212 );
213 }
214
215 protected function doMultiStatementQuery( array $sqls ): array {
216 $qsByStatementId = [];
217
218 $conn = $this->getBindingHandle();
219 // Clear any previously left over result
220 while ( $pgResultSet = pg_get_result( $conn ) ) {
221 pg_free_result( $pgResultSet );
222 }
223
224 $combinedSql = mb_convert_encoding( implode( ";\n", $sqls ), 'UTF-8' );
225 pg_send_query( $conn, $combinedSql );
226
227 reset( $sqls );
228 while ( ( $pgResultSet = pg_get_result( $conn ) ) !== false ) {
229 $this->lastResultHandle = $pgResultSet;
230
231 $statementId = key( $sqls );
232 if ( $statementId !== null ) {
233 if ( pg_result_error( $pgResultSet ) ) {
234 $res = false;
235 } else {
236 $res = new PostgresResultWrapper( $this, $conn, $pgResultSet );
237 }
238 $qsByStatementId[$statementId] = new QueryStatus(
239 $res,
240 pg_affected_rows( $pgResultSet ),
241 (string)pg_result_error( $pgResultSet ),
242 pg_result_error_field( $pgResultSet, PGSQL_DIAG_SQLSTATE )
243 );
244 }
245 next( $sqls );
246 }
247 // Fill in status for statements aborted due to prior statement failure
248 while ( ( $statementId = key( $sqls ) ) !== null ) {
249 $qsByStatementId[$statementId] = new QueryStatus( false, 0, 'Query aborted', 0 );
250 next( $sqls );
251 }
252
253 return $qsByStatementId;
254 }
255
256 protected function dumpError() {
257 $diags = [
258 PGSQL_DIAG_SEVERITY,
259 PGSQL_DIAG_SQLSTATE,
260 PGSQL_DIAG_MESSAGE_PRIMARY,
261 PGSQL_DIAG_MESSAGE_DETAIL,
262 PGSQL_DIAG_MESSAGE_HINT,
263 PGSQL_DIAG_STATEMENT_POSITION,
264 PGSQL_DIAG_INTERNAL_POSITION,
265 PGSQL_DIAG_INTERNAL_QUERY,
266 PGSQL_DIAG_CONTEXT,
267 PGSQL_DIAG_SOURCE_FILE,
268 PGSQL_DIAG_SOURCE_LINE,
269 PGSQL_DIAG_SOURCE_FUNCTION
270 ];
271 foreach ( $diags as $d ) {
272 $this->queryLogger->debug( sprintf( "PgSQL ERROR(%d): %s",
273 $d, pg_result_error_field( $this->lastResultHandle, $d ) ) );
274 }
275 }
276
277 public function insertId() {
278 $res = $this->query(
279 "SELECT lastval()",
280 __METHOD__,
281 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
282 );
283 $row = $res->fetchRow();
284
285 // @phan-suppress-next-line PhanTypeMismatchReturnProbablyReal Return type is undefined for no lastval
286 return $row[0] === null ? null : (int)$row[0];
287 }
288
289 public function lastError() {
290 if ( $this->conn ) {
291 if ( $this->lastResultHandle ) {
292 return pg_result_error( $this->lastResultHandle );
293 } else {
294 return pg_last_error();
295 }
296 }
297
298 return $this->getLastPHPError() ?: 'No database connection';
299 }
300
301 public function lastErrno() {
302 if ( $this->lastResultHandle ) {
303 $lastErrno = pg_result_error_field( $this->lastResultHandle, PGSQL_DIAG_SQLSTATE );
304 if ( $lastErrno !== false ) {
305 return $lastErrno;
306 }
307 }
308
309 return '00000';
310 }
311
312 protected function fetchAffectedRowCount() {
313 if ( !$this->lastResultHandle ) {
314 return 0;
315 }
316
317 return pg_affected_rows( $this->lastResultHandle );
318 }
319
335 public function estimateRowCount( $table, $var = '*', $conds = '',
336 $fname = __METHOD__, $options = [], $join_conds = []
337 ) {
338 $conds = $this->platform->normalizeConditions( $conds, $fname );
339 $column = $this->platform->extractSingleFieldFromList( $var );
340 if ( is_string( $column ) && !in_array( $column, [ '*', '1' ] ) ) {
341 $conds[] = "$column IS NOT NULL";
342 }
343
344 $options['EXPLAIN'] = true;
345 $res = $this->select( $table, $var, $conds, $fname, $options, $join_conds );
346 $rows = -1;
347 if ( $res ) {
348 $row = $res->fetchRow();
349 $count = [];
350 if ( preg_match( '/rows=(\d+)/', $row[0], $count ) ) {
351 $rows = (int)$count[1];
352 }
353 }
354
355 return $rows;
356 }
357
358 public function indexInfo( $table, $index, $fname = __METHOD__ ) {
359 $res = $this->query(
360 "SELECT indexname FROM pg_indexes WHERE tablename='$table'",
361 $fname,
362 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
363 );
364 if ( !$res ) {
365 return null;
366 }
367 foreach ( $res as $row ) {
368 if ( $row->indexname == $this->indexName( $index ) ) {
369 return $row;
370 }
371 }
372
373 return false;
374 }
375
376 public function indexAttributes( $index, $schema = false ) {
377 if ( $schema === false ) {
378 $schemas = $this->getCoreSchemas();
379 } else {
380 $schemas = [ $schema ];
381 }
382
383 $eindex = $this->addQuotes( $index );
384
385 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
386 foreach ( $schemas as $schema ) {
387 $eschema = $this->addQuotes( $schema );
388 /*
389 * A subquery would be not needed if we didn't care about the order
390 * of attributes, but we do
391 */
392 $sql = <<<__INDEXATTR__
393
394 SELECT opcname,
395 attname,
396 i.indoption[s.g] as option,
397 pg_am.amname
398 FROM
399 (SELECT generate_series(array_lower(isub.indkey,1), array_upper(isub.indkey,1)) AS g
400 FROM
401 pg_index isub
402 JOIN pg_class cis
403 ON cis.oid=isub.indexrelid
404 JOIN pg_namespace ns
405 ON cis.relnamespace = ns.oid
406 WHERE cis.relname=$eindex AND ns.nspname=$eschema) AS s,
407 pg_attribute,
408 pg_opclass opcls,
409 pg_am,
410 pg_class ci
411 JOIN pg_index i
412 ON ci.oid=i.indexrelid
413 JOIN pg_class ct
414 ON ct.oid = i.indrelid
415 JOIN pg_namespace n
416 ON ci.relnamespace = n.oid
417 WHERE
418 ci.relname=$eindex AND n.nspname=$eschema
419 AND attrelid = ct.oid
420 AND i.indkey[s.g] = attnum
421 AND i.indclass[s.g] = opcls.oid
422 AND pg_am.oid = opcls.opcmethod
423__INDEXATTR__;
424 $res = $this->query( $sql, __METHOD__, $flags );
425 $a = [];
426 if ( $res ) {
427 foreach ( $res as $row ) {
428 $a[] = [
429 $row->attname,
430 $row->opcname,
431 $row->amname,
432 $row->option ];
433 }
434 return $a;
435 }
436 }
437 return null;
438 }
439
440 public function indexUnique( $table, $index, $fname = __METHOD__ ) {
441 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
442 $sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'" .
443 " AND indexdef LIKE 'CREATE UNIQUE%(" .
444 $this->strencode( $this->indexName( $index ) ) .
445 ")'";
446 $res = $this->query( $sql, $fname, $flags );
447 if ( !$res ) {
448 return false;
449 }
450
451 return $res->numRows() > 0;
452 }
453
472 protected function doInsertSelectNative(
473 $destTable,
474 $srcTable,
475 array $varMap,
476 $conds,
477 $fname,
478 array $insertOptions,
479 array $selectOptions,
480 $selectJoinConds
481 ) {
482 if ( in_array( 'IGNORE', $insertOptions ) ) {
483 // Use "ON CONFLICT DO" if we have it for IGNORE
484 $destTable = $this->tableName( $destTable );
485
486 $selectSql = $this->selectSQLText(
487 $srcTable,
488 array_values( $varMap ),
489 $conds,
490 $fname,
491 $selectOptions,
492 $selectJoinConds
493 );
494
495 $sql = "INSERT INTO $destTable (" . implode( ',', array_keys( $varMap ) ) . ') ' .
496 $selectSql . ' ON CONFLICT DO NOTHING';
497
498 $this->query( $sql, $fname, self::QUERY_CHANGE_ROWS );
499 } else {
500 parent::doInsertSelectNative( $destTable, $srcTable, $varMap, $conds, $fname,
501 $insertOptions, $selectOptions, $selectJoinConds );
502 }
503 }
504
510 public function remappedTableName( $name ) {
511 wfDeprecated( __METHOD__, '1.37' );
512
513 return $this->keywordTableMap[$name] ?? $name;
514 }
515
521 public function realTableName( $name, $format = 'quoted' ) {
522 return parent::tableName( $name, $format );
523 }
524
525 public function nextSequenceValue( $seqName ) {
526 return new NextSequenceValue;
527 }
528
535 public function currentSequenceValue( $seqName ) {
536 $res = $this->query(
537 "SELECT currval('" . str_replace( "'", "''", $seqName ) . "')",
538 __METHOD__,
539 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
540 );
541 $row = $res->fetchRow();
542 $currval = $row[0];
543
544 return $currval;
545 }
546
547 public function textFieldSize( $table, $field ) {
548 $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
549 $encTable = $this->tableName( $table );
550 $sql = "SELECT t.typname as ftype,a.atttypmod as size
551 FROM pg_class c, pg_attribute a, pg_type t
552 WHERE relname='$encTable' AND a.attrelid=c.oid AND
553 a.atttypid=t.oid and a.attname='$field'";
554 $res = $this->query( $sql, __METHOD__, $flags );
555 $row = $res->fetchObject();
556 if ( $row->ftype == 'varchar' ) {
557 $size = $row->size - 4;
558 } else {
559 $size = $row->size;
560 }
561
562 return $size;
563 }
564
565 public function wasDeadlock() {
566 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
567 return $this->lastErrno() === '40P01';
568 }
569
570 public function wasLockTimeout() {
571 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
572 return $this->lastErrno() === '55P03';
573 }
574
575 protected function isConnectionError( $errno ) {
576 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
577 static $codes = [ '08000', '08003', '08006', '08001', '08004', '57P01', '57P03', '53300' ];
578
579 return in_array( $errno, $codes, true );
580 }
581
582 protected function isQueryTimeoutError( $errno ) {
583 // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
584 return ( $errno === '57014' );
585 }
586
587 protected function isKnownStatementRollbackError( $errno ) {
588 return false; // transaction has to be rolled-back from error state
589 }
590
591 public function duplicateTableStructure(
592 $oldName, $newName, $temporary = false, $fname = __METHOD__
593 ) {
594 $newNameE = $this->platform->addIdentifierQuotes( $newName );
595 $oldNameE = $this->platform->addIdentifierQuotes( $oldName );
596
597 $temporary = $temporary ? 'TEMPORARY' : '';
598
599 $ret = $this->query(
600 "CREATE $temporary TABLE $newNameE " .
601 "(LIKE $oldNameE INCLUDING DEFAULTS INCLUDING INDEXES)",
602 $fname,
603 self::QUERY_PSEUDO_PERMANENT | self::QUERY_CHANGE_SCHEMA
604 );
605 if ( !$ret ) {
606 return $ret;
607 }
608
609 $res = $this->query(
610 'SELECT attname FROM pg_class c'
611 . ' JOIN pg_namespace n ON (n.oid = c.relnamespace)'
612 . ' JOIN pg_attribute a ON (a.attrelid = c.oid)'
613 . ' JOIN pg_attrdef d ON (c.oid=d.adrelid and a.attnum=d.adnum)'
614 . ' WHERE relkind = \'r\''
615 . ' AND nspname = ' . $this->addQuotes( $this->getCoreSchema() )
616 . ' AND relname = ' . $this->addQuotes( $oldName )
617 . ' AND pg_get_expr(adbin, adrelid) LIKE \'nextval(%\'',
618 $fname,
619 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
620 );
621 $row = $res->fetchObject();
622 if ( $row ) {
623 $field = $row->attname;
624 $newSeq = "{$newName}_{$field}_seq";
625 $fieldE = $this->platform->addIdentifierQuotes( $field );
626 $newSeqE = $this->platform->addIdentifierQuotes( $newSeq );
627 $newSeqQ = $this->addQuotes( $newSeq );
628 $this->query(
629 "CREATE $temporary SEQUENCE $newSeqE OWNED BY $newNameE.$fieldE",
630 $fname,
631 self::QUERY_CHANGE_SCHEMA
632 );
633 $this->query(
634 "ALTER TABLE $newNameE ALTER COLUMN $fieldE SET DEFAULT nextval({$newSeqQ}::regclass)",
635 $fname,
636 self::QUERY_CHANGE_SCHEMA
637 );
638 }
639
640 return $ret;
641 }
642
643 protected function doTruncate( array $tables, $fname ) {
644 $encTables = $this->tableNamesN( ...$tables );
645 $sql = "TRUNCATE TABLE " . implode( ',', $encTables ) . " RESTART IDENTITY";
646 $this->query( $sql, $fname, self::QUERY_CHANGE_SCHEMA );
647 }
648
655 public function listTables( $prefix = '', $fname = __METHOD__ ) {
656 $eschemas = implode( ',', array_map( [ $this, 'addQuotes' ], $this->getCoreSchemas() ) );
657 $result = $this->query(
658 "SELECT DISTINCT tablename FROM pg_tables WHERE schemaname IN ($eschemas)",
659 $fname,
660 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
661 );
662 $endArray = [];
663
664 foreach ( $result as $table ) {
665 $vars = get_object_vars( $table );
666 $table = array_pop( $vars );
667 if ( $prefix == '' || strpos( $table, $prefix ) === 0 ) {
668 $endArray[] = $table;
669 }
670 }
671
672 return $endArray;
673 }
674
693 private function pg_array_parse( $text, &$output, $limit = false, $offset = 1 ) {
694 if ( $limit === false ) {
695 $limit = strlen( $text ) - 1;
696 $output = [];
697 }
698 if ( $text == '{}' ) {
699 return $output;
700 }
701 do {
702 if ( $text[$offset] != '{' ) {
703 preg_match( "/(\\{?\"([^\"\\\\]|\\\\.)*\"|[^,{}]+)+([,}]+)/",
704 $text, $match, 0, $offset );
705 $offset += strlen( $match[0] );
706 $output[] = ( $match[1][0] != '"'
707 ? $match[1]
708 : stripcslashes( substr( $match[1], 1, -1 ) ) );
709 if ( $match[3] == '},' ) {
710 return $output;
711 }
712 } else {
713 $offset = $this->pg_array_parse( $text, $output, $limit, $offset + 1 );
714 }
715 } while ( $limit > $offset );
716
717 return $output;
718 }
719
720 public function getSoftwareLink() {
721 return '[{{int:version-db-postgres-url}} PostgreSQL]';
722 }
723
731 public function getCurrentSchema() {
732 $res = $this->query(
733 "SELECT current_schema()",
734 __METHOD__,
735 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
736 );
737 $row = $res->fetchRow();
738
739 return $row[0];
740 }
741
752 public function getSchemas() {
753 $res = $this->query(
754 "SELECT current_schemas(false)",
755 __METHOD__,
756 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
757 );
758 $row = $res->fetchRow();
759 $schemas = [];
760
761 /* PHP pgsql support does not support array type, "{a,b}" string is returned */
762
763 return $this->pg_array_parse( $row[0], $schemas );
764 }
765
775 public function getSearchPath() {
776 $res = $this->query(
777 "SHOW search_path",
778 __METHOD__,
779 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
780 );
781 $row = $res->fetchRow();
782
783 /* PostgreSQL returns SHOW values as strings */
784
785 return explode( ",", $row[0] );
786 }
787
795 private function setSearchPath( $search_path ) {
796 $this->query(
797 "SET search_path = " . implode( ", ", $search_path ),
798 __METHOD__,
799 self::QUERY_CHANGE_TRX
800 );
801 }
802
817 public function determineCoreSchema( $desiredSchema ) {
818 if ( $this->trxLevel() ) {
819 // We do not want the schema selection to change on ROLLBACK or INSERT SELECT.
820 // See https://www.postgresql.org/docs/8.3/sql-set.html
821 throw new DBUnexpectedError(
822 $this,
823 __METHOD__ . ": a transaction is currently active"
824 );
825 }
826
827 if ( $this->schemaExists( $desiredSchema ) ) {
828 if ( in_array( $desiredSchema, $this->getSchemas() ) ) {
829 $this->platform->setCoreSchema( $desiredSchema );
830 $this->queryLogger->debug(
831 "Schema \"" . $desiredSchema . "\" already in the search path\n" );
832 } else {
833 // Prepend the desired schema to the search path (T17816)
834 $search_path = $this->getSearchPath();
835 array_unshift( $search_path, $this->platform->addIdentifierQuotes( $desiredSchema ) );
836 $this->setSearchPath( $search_path );
837 $this->platform->setCoreSchema( $desiredSchema );
838 $this->queryLogger->debug(
839 "Schema \"" . $desiredSchema . "\" added to the search path\n" );
840 }
841 } else {
842 $this->platform->setCoreSchema( $this->getCurrentSchema() );
843 $this->queryLogger->debug(
844 "Schema \"" . $desiredSchema . "\" not found, using current \"" .
845 $this->getCoreSchema() . "\"\n" );
846 }
847 }
848
855 public function getCoreSchema() {
856 return $this->platform->getCoreSchema();
857 }
858
865 public function getCoreSchemas() {
866 if ( $this->tempSchema ) {
867 return [ $this->tempSchema, $this->getCoreSchema() ];
868 }
869
870 $res = $this->query(
871 "SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = pg_my_temp_schema()",
872 __METHOD__,
873 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
874 );
875 $row = $res->fetchObject();
876 if ( $row ) {
877 $this->tempSchema = $row->nspname;
878 return [ $this->tempSchema, $this->getCoreSchema() ];
879 }
880
881 return [ $this->getCoreSchema() ];
882 }
883
884 public function getServerVersion() {
885 if ( !isset( $this->numericVersion ) ) {
886 $conn = $this->getBindingHandle();
887 $versionInfo = pg_version( $conn );
888 if ( version_compare( $versionInfo['client'], '7.4.0', 'lt' ) ) {
889 // Old client, abort install
890 $this->numericVersion = '7.3 or earlier';
891 } elseif ( isset( $versionInfo['server'] ) ) {
892 // Normal client
893 $this->numericVersion = $versionInfo['server'];
894 } else {
895 // T18937: broken pgsql extension from PHP<5.3
896 $this->numericVersion = pg_parameter_status( $conn, 'server_version' );
897 }
898 }
899
900 return $this->numericVersion;
901 }
902
911 private function relationExists( $table, $types, $schema = false ) {
912 if ( !is_array( $types ) ) {
913 $types = [ $types ];
914 }
915 if ( $schema === false ) {
916 $schemas = $this->getCoreSchemas();
917 } else {
918 $schemas = [ $schema ];
919 }
920 $table = $this->realTableName( $table, 'raw' );
921 $etable = $this->addQuotes( $table );
922 foreach ( $schemas as $schema ) {
923 $eschema = $this->addQuotes( $schema );
924 $sql = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "
925 . "WHERE c.relnamespace = n.oid AND c.relname = $etable AND n.nspname = $eschema "
926 . "AND c.relkind IN ('" . implode( "','", $types ) . "')";
927 $res = $this->query(
928 $sql,
929 __METHOD__,
930 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
931 );
932 if ( $res && $res->numRows() ) {
933 return true;
934 }
935 }
936
937 return false;
938 }
939
947 public function tableExists( $table, $fname = __METHOD__, $schema = false ) {
948 return $this->relationExists( $table, [ 'r', 'v' ], $schema );
949 }
950
951 public function sequenceExists( $sequence, $schema = false ) {
952 return $this->relationExists( $sequence, 'S', $schema );
953 }
954
955 public function triggerExists( $table, $trigger ) {
956 $q = <<<SQL
957 SELECT 1 FROM pg_class, pg_namespace, pg_trigger
958 WHERE relnamespace=pg_namespace.oid AND relkind='r'
959 AND tgrelid=pg_class.oid
960 AND nspname=%s AND relname=%s AND tgname=%s
961SQL;
962 foreach ( $this->getCoreSchemas() as $schema ) {
963 $res = $this->query(
964 sprintf(
965 $q,
966 $this->addQuotes( $schema ),
967 $this->addQuotes( $table ),
968 $this->addQuotes( $trigger )
969 ),
970 __METHOD__,
971 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
972 );
973 if ( $res && $res->numRows() ) {
974 return true;
975 }
976 }
977
978 return false;
979 }
980
981 public function ruleExists( $table, $rule ) {
982 $exists = $this->selectField( 'pg_rules', 'rulename',
983 [
984 'rulename' => $rule,
985 'tablename' => $table,
986 'schemaname' => $this->getCoreSchemas()
987 ],
988 __METHOD__
989 );
990
991 return $exists === $rule;
992 }
993
994 public function constraintExists( $table, $constraint ) {
995 foreach ( $this->getCoreSchemas() as $schema ) {
996 $sql = sprintf( "SELECT 1 FROM information_schema.table_constraints " .
997 "WHERE constraint_schema = %s AND table_name = %s AND constraint_name = %s",
998 $this->addQuotes( $schema ),
999 $this->addQuotes( $table ),
1000 $this->addQuotes( $constraint )
1001 );
1002 $res = $this->query(
1003 $sql,
1004 __METHOD__,
1005 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1006 );
1007 if ( $res && $res->numRows() ) {
1008 return true;
1009 }
1010 }
1011 return false;
1012 }
1013
1019 public function schemaExists( $schema ) {
1020 if ( !strlen( $schema ?? '' ) ) {
1021 return false; // short-circuit
1022 }
1023
1024 $res = $this->query(
1025 "SELECT 1 FROM pg_catalog.pg_namespace " .
1026 "WHERE nspname = " . $this->addQuotes( $schema ) . " LIMIT 1",
1027 __METHOD__,
1028 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1029 );
1030
1031 return ( $res->numRows() > 0 );
1032 }
1033
1039 public function roleExists( $roleName ) {
1040 $res = $this->query(
1041 "SELECT 1 FROM pg_catalog.pg_roles " .
1042 "WHERE rolname = " . $this->addQuotes( $roleName ) . " LIMIT 1",
1043 __METHOD__,
1044 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1045 );
1046
1047 return ( $res->numRows() > 0 );
1048 }
1049
1055 public function fieldInfo( $table, $field ) {
1056 return PostgresField::fromText( $this, $table, $field );
1057 }
1058
1059 public function encodeBlob( $b ) {
1060 $conn = $this->getBindingHandle();
1061
1062 return new PostgresBlob( pg_escape_bytea( $conn, $b ) );
1063 }
1064
1065 public function decodeBlob( $b ) {
1066 if ( $b instanceof PostgresBlob ) {
1067 $b = $b->fetch();
1068 } elseif ( $b instanceof Blob ) {
1069 return $b->fetch();
1070 }
1071
1072 return pg_unescape_bytea( $b );
1073 }
1074
1075 public function strencode( $s ) {
1076 // Should not be called by us
1077 return pg_escape_string( $this->getBindingHandle(), (string)$s );
1078 }
1079
1080 public function addQuotes( $s ) {
1081 $conn = $this->getBindingHandle();
1082
1083 if ( $s === null ) {
1084 return 'NULL';
1085 } elseif ( is_bool( $s ) ) {
1086 return (string)intval( $s );
1087 } elseif ( is_int( $s ) ) {
1088 return (string)$s;
1089 } elseif ( $s instanceof Blob ) {
1090 if ( $s instanceof PostgresBlob ) {
1091 $s = $s->fetch();
1092 } else {
1093 $s = pg_escape_bytea( $conn, $s->fetch() );
1094 }
1095 return "'$s'";
1096 } elseif ( $s instanceof NextSequenceValue ) {
1097 return 'DEFAULT';
1098 }
1099
1100 return "'" . pg_escape_string( $conn, (string)$s ) . "'";
1101 }
1102
1103 public function streamStatementEnd( &$sql, &$newLine ) {
1104 # Allow dollar quoting for function declarations
1105 if ( substr( $newLine, 0, 4 ) == '$mw$' ) {
1106 if ( $this->delimiter ) {
1107 $this->delimiter = false;
1108 } else {
1109 $this->delimiter = ';';
1110 }
1111 }
1112
1113 return parent::streamStatementEnd( $sql, $newLine );
1114 }
1115
1116 public function doLockIsFree( string $lockName, string $method ) {
1117 $res = $this->query(
1118 $this->platform->lockIsFreeSQLText( $lockName ),
1119 $method,
1120 self::QUERY_CHANGE_LOCKS
1121 );
1122 $row = $res->fetchObject();
1123
1124 return ( $row->unlocked === 't' );
1125 }
1126
1127 public function doLock( string $lockName, string $method, int $timeout ) {
1128 $sql = $this->platform->lockSQLText( $lockName, $timeout );
1129
1130 $acquired = null;
1131 $loop = new WaitConditionLoop(
1132 function () use ( $lockName, $sql, $timeout, $method, &$acquired ) {
1133 $res = $this->query(
1134 $sql,
1135 $method,
1136 self::QUERY_CHANGE_LOCKS
1137 );
1138 $row = $res->fetchObject();
1139
1140 if ( $row->acquired !== null ) {
1141 $acquired = (float)$row->acquired;
1142
1143 return WaitConditionLoop::CONDITION_REACHED;
1144 }
1145
1146 return WaitConditionLoop::CONDITION_CONTINUE;
1147 },
1148 $timeout
1149 );
1150 $loop->invoke();
1151
1152 return $acquired;
1153 }
1154
1155 public function doUnlock( string $lockName, string $method ) {
1156 $result = $this->query(
1157 $this->platform->unlockSQLText( $lockName ),
1158 $method,
1159 self::QUERY_CHANGE_LOCKS
1160 );
1161 $row = $result->fetchObject();
1162
1163 return ( $row->released === 't' );
1164 }
1165
1166 protected function doFlushSession( $fname ) {
1167 $flags = self::QUERY_CHANGE_LOCKS | self::QUERY_NO_RETRY;
1168
1169 // https://www.postgresql.org/docs/9.1/functions-admin.html
1170 $sql = "SELECT pg_advisory_unlock_all()";
1171 $qs = $this->executeQuery( $sql, __METHOD__, $flags, $sql );
1172 if ( $qs->res === false ) {
1173 $this->reportQueryError( $qs->message, $qs->code, $sql, $fname, true );
1174 }
1175 }
1176
1177 public function serverIsReadOnly() {
1178 $res = $this->query(
1179 "SHOW default_transaction_read_only",
1180 __METHOD__,
1181 self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1182 );
1183 $row = $res->fetchObject();
1184
1185 return $row ? ( strtolower( $row->default_transaction_read_only ) === 'on' ) : false;
1186 }
1187
1188 public static function getAttributes() {
1189 return [ self::ATTR_SCHEMAS_AS_TABLE_GROUPS => true ];
1190 }
1191}
1192
1196class_alias( DatabasePostgres::class, 'DatabasePostgres' );
wfDeprecatedMsg( $msg, $version=false, $component=false, $callerOffset=2)
Log a deprecation warning with arbitrary message text.
wfDeprecated( $function, $version=false, $component=false, $callerOffset=2)
Logs a warning that a deprecated feature was used.
Class to handle database/schema/prefix specifications for IDatabase.
Postgres database abstraction layer.
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.Note that unlike most database abstract...
isQueryTimeoutError( $errno)
Checks whether the cause of the error is detected to be a timeout.
doLock(string $lockName, string $method, int $timeout)
insertId()
Get the inserted value of an auto-increment row.
indexAttributes( $index, $schema=false)
databasesAreIndependent()
Returns true if DBs are assumed to be on potentially different servers.In systems like mysql/mariadb,...
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 query statement.
addQuotes( $s)
Escape and quote a raw value string for use in a SQL query.stringto override
sequenceExists( $sequence, $schema=false)
wasDeadlock()
Determines if the last failure was due to a deadlock.Note that during a deadlock, the prior transacti...
currentSequenceValue( $seqName)
Return the current value of a sequence.
doMultiStatementQuery(array $sqls)
Execute a batch of query statements, aborting remaining statements if one fails.
lastErrno()
Get the RDBMS-specific error code from the last query statement.
getCoreSchema()
Return schema name for core application tables.
strencode( $s)
Wrapper for addslashes()
wasLockTimeout()
Determines if the last failure was due to a lock timeout.Note that during a lock wait timeout,...
isConnectionError( $errno)
Do not use this method outside of Database/DBError classes.
indexUnique( $table, $index, $fname=__METHOD__)
Determines if a given index is unique.boolto override
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, like from mysql_get_server_info()
textFieldSize( $table, $field)
Returns the size of a text field, or -1 for "unlimited".intto override
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 (...
tableExists( $table, $fname=__METHOD__, $schema=false)
For backward compatibility, this function checks both tables and views.
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...
realTableName( $name, $format='quoted')
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 the DB is marked as read-only server-side query} 1.28to override
Relational database abstraction object.
Definition Database.php:43
string null $password
Password used to establish the current connection.
Definition Database.php:78
restoreErrorHandler()
Restore the previous error handler and return the last PHP error for this DB.
Definition Database.php:628
object resource null $conn
Database connection.
Definition Database.php:68
newExceptionAfterConnectError( $error)
installErrorHandler()
Set a custom error handler for logging errors during database connection.
Definition Database.php:617
affectedRows()
Get the number of rows affected by the last write query.
string null $server
Server that this instance is currently connected to.
Definition Database.php:74
close( $fname=__METHOD__)
Close the database connection.
Definition Database.php:680
string null $user
User that this instance is currently connected under the name of.
Definition Database.php:76
query( $sql, $fname=__METHOD__, $flags=0)
Run an SQL query statement and return the result.
Definition Database.php:932
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...
foreach( $mmfl['setupFiles'] as $fileName) if($queue) if(empty( $mmfl['quiet'])) $s
return true
Definition router.php:92