MediaWiki  master
DatabasePostgres.php
Go to the documentation of this file.
1 <?php
23 namespace Wikimedia\Rdbms;
24 
25 use RuntimeException;
26 use Wikimedia\AtEase\AtEase;
27 use Wikimedia\Timestamp\ConvertibleTimestamp;
28 use Wikimedia\WaitConditionLoop;
29 
33 class DatabasePostgres extends Database {
35  private $port;
37  private $coreSchema;
39  private $tempSchema;
41  private $keywordTableMap = [];
43  private $numericVersion;
44 
47 
53  public function __construct( array $params ) {
54  $this->port = intval( $params['port'] ?? null );
55  $this->keywordTableMap = $params['keywordTableMap'] ?? [];
56 
57  parent::__construct( $params );
58  }
59 
60  public function getType() {
61  return 'postgres';
62  }
63 
64  public function implicitOrderby() {
65  return false;
66  }
67 
68  public function hasConstraint( $name ) {
69  foreach ( $this->getCoreSchemas() as $schema ) {
70  $sql = "SELECT 1 FROM pg_catalog.pg_constraint c, pg_catalog.pg_namespace n " .
71  "WHERE c.connamespace = n.oid AND conname = " .
72  $this->addQuotes( $name ) . " AND n.nspname = " .
73  $this->addQuotes( $schema );
74  $res = $this->doQuery( $sql );
75  if ( $res && $this->numRows( $res ) ) {
76  return true;
77  }
78  }
79  return false;
80  }
81 
82  protected function open( $server, $user, $password, $dbName, $schema, $tablePrefix ) {
83  if ( !function_exists( 'pg_connect' ) ) {
84  throw $this->newExceptionAfterConnectError(
85  "Postgres functions missing, have you compiled PHP with the --with-pgsql\n" .
86  "option? (Note: if you recently installed PHP, you may need to restart your\n" .
87  "webserver and database)"
88  );
89  }
90 
91  $this->close( __METHOD__ );
92 
93  $this->server = $server;
94  $this->user = $user;
95  $this->password = $password;
96 
97  $connectVars = [
98  // A database must be specified in order to connect to Postgres. If $dbName is not
99  // specified, then use the standard "postgres" database that should exist by default.
100  'dbname' => strlen( $dbName ) ? $dbName : 'postgres',
101  'user' => $user,
102  'password' => $password
103  ];
104  if ( strlen( $server ) ) {
105  $connectVars['host'] = $server;
106  }
107  if ( $this->port > 0 ) {
108  $connectVars['port'] = $this->port;
109  }
110  if ( $this->getFlag( self::DBO_SSL ) ) {
111  $connectVars['sslmode'] = 'require';
112  }
113  $connectString = $this->makeConnectionString( $connectVars );
114 
115  $this->installErrorHandler();
116  try {
117  $this->conn = pg_connect( $connectString, PGSQL_CONNECT_FORCE_NEW ) ?: null;
118  } catch ( RuntimeException $e ) {
119  $this->restoreErrorHandler();
120  throw $this->newExceptionAfterConnectError( $e->getMessage() );
121  }
122  $error = $this->restoreErrorHandler();
123 
124  if ( !$this->conn ) {
125  throw $this->newExceptionAfterConnectError( $error ?: $this->lastError() );
126  }
127 
128  try {
129  // Since no transaction is active at this point, any SET commands should apply
130  // for the entire session (e.g. will not be reverted on transaction rollback).
131  // See https://www.postgresql.org/docs/8.3/sql-set.html
132  $variables = [
133  'client_encoding' => 'UTF8',
134  'datestyle' => 'ISO, YMD',
135  'timezone' => 'GMT',
136  'standard_conforming_strings' => 'on',
137  'bytea_output' => 'escape',
138  'client_min_messages' => 'ERROR'
139  ];
140  foreach ( $variables as $var => $val ) {
141  $this->query(
142  'SET ' . $this->addIdentifierQuotes( $var ) . ' = ' . $this->addQuotes( $val ),
143  __METHOD__,
144  self::QUERY_IGNORE_DBO_TRX | self::QUERY_NO_RETRY | self::QUERY_CHANGE_TRX
145  );
146  }
147  $this->determineCoreSchema( $schema );
148  $this->currentDomain = new DatabaseDomain( $dbName, $schema, $tablePrefix );
149  } catch ( RuntimeException $e ) {
150  throw $this->newExceptionAfterConnectError( $e->getMessage() );
151  }
152  }
153 
154  protected function relationSchemaQualifier() {
155  if ( $this->coreSchema === $this->currentDomain->getSchema() ) {
156  // The schema to be used is now in the search path; no need for explicit qualification
157  return '';
158  }
159 
160  return parent::relationSchemaQualifier();
161  }
162 
163  public function databasesAreIndependent() {
164  return true;
165  }
166 
167  public function doSelectDomain( DatabaseDomain $domain ) {
168  if ( $this->getDBname() !== $domain->getDatabase() ) {
169  // Postgres doesn't support selectDB in the same way MySQL does.
170  // So if the DB name doesn't match the open connection, open a new one
171  $this->open(
172  $this->server,
173  $this->user,
174  $this->password,
175  $domain->getDatabase(),
176  $domain->getSchema(),
177  $domain->getTablePrefix()
178  );
179  } else {
180  $this->currentDomain = $domain;
181  }
182 
183  return true;
184  }
185 
190  private function makeConnectionString( $vars ) {
191  $s = '';
192  foreach ( $vars as $name => $value ) {
193  $s .= "$name='" . str_replace( "'", "\\'", $value ) . "' ";
194  }
195 
196  return $s;
197  }
198 
199  protected function closeConnection() {
200  return $this->conn ? pg_close( $this->conn ) : true;
201  }
202 
203  protected function isTransactableQuery( $sql ) {
204  return parent::isTransactableQuery( $sql ) &&
205  !preg_match( '/^SELECT\s+pg_(try_|)advisory_\w+\‍(/', $sql );
206  }
207 
212  public function doQuery( $sql ) {
213  $conn = $this->getBindingHandle();
214 
215  $sql = mb_convert_encoding( $sql, 'UTF-8' );
216  // Clear previously left over PQresult
217  while ( $res = pg_get_result( $conn ) ) {
218  pg_free_result( $res );
219  }
220  if ( pg_send_query( $conn, $sql ) === false ) {
221  throw new DBUnexpectedError( $this, "Unable to post new query to PostgreSQL\n" );
222  }
223  $this->lastResultHandle = pg_get_result( $conn );
224  if ( pg_result_error( $this->lastResultHandle ) ) {
225  return false;
226  }
227 
229  }
230 
231  protected function dumpError() {
232  $diags = [
233  PGSQL_DIAG_SEVERITY,
234  PGSQL_DIAG_SQLSTATE,
235  PGSQL_DIAG_MESSAGE_PRIMARY,
236  PGSQL_DIAG_MESSAGE_DETAIL,
237  PGSQL_DIAG_MESSAGE_HINT,
238  PGSQL_DIAG_STATEMENT_POSITION,
239  PGSQL_DIAG_INTERNAL_POSITION,
240  PGSQL_DIAG_INTERNAL_QUERY,
241  PGSQL_DIAG_CONTEXT,
242  PGSQL_DIAG_SOURCE_FILE,
243  PGSQL_DIAG_SOURCE_LINE,
244  PGSQL_DIAG_SOURCE_FUNCTION
245  ];
246  foreach ( $diags as $d ) {
247  $this->queryLogger->debug( sprintf( "PgSQL ERROR(%d): %s",
248  $d, pg_result_error_field( $this->lastResultHandle, $d ) ) );
249  }
250  }
251 
252  public function freeResult( $res ) {
253  AtEase::suppressWarnings();
254  $ok = pg_free_result( ResultWrapper::unwrap( $res ) );
255  AtEase::restoreWarnings();
256  if ( !$ok ) {
257  throw new DBUnexpectedError( $this, "Unable to free Postgres result\n" );
258  }
259  }
260 
261  public function fetchObject( $res ) {
262  AtEase::suppressWarnings();
263  $row = pg_fetch_object( ResultWrapper::unwrap( $res ) );
264  AtEase::restoreWarnings();
265  # @todo FIXME: HACK HACK HACK HACK debug
266 
267  # @todo hashar: not sure if the following test really trigger if the object
268  # fetching failed.
269  $conn = $this->getBindingHandle();
270  if ( pg_last_error( $conn ) ) {
271  throw new DBUnexpectedError(
272  $this,
273  'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) )
274  );
275  }
276 
277  return $row;
278  }
279 
280  public function fetchRow( $res ) {
281  AtEase::suppressWarnings();
282  $row = pg_fetch_array( ResultWrapper::unwrap( $res ) );
283  AtEase::restoreWarnings();
284 
285  $conn = $this->getBindingHandle();
286  if ( pg_last_error( $conn ) ) {
287  throw new DBUnexpectedError(
288  $this,
289  'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) )
290  );
291  }
292 
293  return $row;
294  }
295 
296  public function numRows( $res ) {
297  if ( $res === false ) {
298  return 0;
299  }
300 
301  AtEase::suppressWarnings();
302  $n = pg_num_rows( ResultWrapper::unwrap( $res ) );
303  AtEase::restoreWarnings();
304 
305  $conn = $this->getBindingHandle();
306  if ( pg_last_error( $conn ) ) {
307  throw new DBUnexpectedError(
308  $this,
309  'SQL error: ' . htmlspecialchars( pg_last_error( $conn ) )
310  );
311  }
312 
313  return $n;
314  }
315 
316  public function numFields( $res ) {
317  return pg_num_fields( ResultWrapper::unwrap( $res ) );
318  }
319 
320  public function fieldName( $res, $n ) {
321  return pg_field_name( ResultWrapper::unwrap( $res ), $n );
322  }
323 
324  public function insertId() {
325  $res = $this->query(
326  "SELECT lastval()",
327  __METHOD__,
328  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
329  );
330  $row = $this->fetchRow( $res );
331 
332  return $row[0] === null ? null : (int)$row[0];
333  }
334 
335  public function dataSeek( $res, $row ) {
336  return pg_result_seek( ResultWrapper::unwrap( $res ), $row );
337  }
338 
339  public function lastError() {
340  if ( $this->conn ) {
341  if ( $this->lastResultHandle ) {
342  return pg_result_error( $this->lastResultHandle );
343  } else {
344  return pg_last_error();
345  }
346  }
347 
348  return $this->getLastPHPError() ?: 'No database connection';
349  }
350 
351  public function lastErrno() {
352  if ( $this->lastResultHandle ) {
353  return pg_result_error_field( $this->lastResultHandle, PGSQL_DIAG_SQLSTATE );
354  } else {
355  return false;
356  }
357  }
358 
359  protected function fetchAffectedRowCount() {
360  if ( !$this->lastResultHandle ) {
361  return 0;
362  }
363 
364  return pg_affected_rows( $this->lastResultHandle );
365  }
366 
382  public function estimateRowCount( $table, $var = '*', $conds = '',
383  $fname = __METHOD__, $options = [], $join_conds = []
384  ) {
385  $conds = $this->normalizeConditions( $conds, $fname );
386  $column = $this->extractSingleFieldFromList( $var );
387  if ( is_string( $column ) && !in_array( $column, [ '*', '1' ] ) ) {
388  $conds[] = "$column IS NOT NULL";
389  }
390 
391  $options['EXPLAIN'] = true;
392  $res = $this->select( $table, $var, $conds, $fname, $options, $join_conds );
393  $rows = -1;
394  if ( $res ) {
395  $row = $this->fetchRow( $res );
396  $count = [];
397  if ( preg_match( '/rows=(\d+)/', $row[0], $count ) ) {
398  $rows = (int)$count[1];
399  }
400  }
401 
402  return $rows;
403  }
404 
405  public function indexInfo( $table, $index, $fname = __METHOD__ ) {
406  $res = $this->query(
407  "SELECT indexname FROM pg_indexes WHERE tablename='$table'",
408  $fname,
409  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
410  );
411  if ( !$res ) {
412  return null;
413  }
414  foreach ( $res as $row ) {
415  if ( $row->indexname == $this->indexName( $index ) ) {
416  return $row;
417  }
418  }
419 
420  return false;
421  }
422 
423  public function indexAttributes( $index, $schema = false ) {
424  if ( $schema === false ) {
425  $schemas = $this->getCoreSchemas();
426  } else {
427  $schemas = [ $schema ];
428  }
429 
430  $eindex = $this->addQuotes( $index );
431 
432  $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
433  foreach ( $schemas as $schema ) {
434  $eschema = $this->addQuotes( $schema );
435  /*
436  * A subquery would be not needed if we didn't care about the order
437  * of attributes, but we do
438  */
439  $sql = <<<__INDEXATTR__
440 
441  SELECT opcname,
442  attname,
443  i.indoption[s.g] as option,
444  pg_am.amname
445  FROM
446  (SELECT generate_series(array_lower(isub.indkey,1), array_upper(isub.indkey,1)) AS g
447  FROM
448  pg_index isub
449  JOIN pg_class cis
450  ON cis.oid=isub.indexrelid
451  JOIN pg_namespace ns
452  ON cis.relnamespace = ns.oid
453  WHERE cis.relname=$eindex AND ns.nspname=$eschema) AS s,
454  pg_attribute,
455  pg_opclass opcls,
456  pg_am,
457  pg_class ci
458  JOIN pg_index i
459  ON ci.oid=i.indexrelid
460  JOIN pg_class ct
461  ON ct.oid = i.indrelid
462  JOIN pg_namespace n
463  ON ci.relnamespace = n.oid
464  WHERE
465  ci.relname=$eindex AND n.nspname=$eschema
466  AND attrelid = ct.oid
467  AND i.indkey[s.g] = attnum
468  AND i.indclass[s.g] = opcls.oid
469  AND pg_am.oid = opcls.opcmethod
470 __INDEXATTR__;
471  $res = $this->query( $sql, __METHOD__, $flags );
472  $a = [];
473  if ( $res ) {
474  foreach ( $res as $row ) {
475  $a[] = [
476  $row->attname,
477  $row->opcname,
478  $row->amname,
479  $row->option ];
480  }
481  return $a;
482  }
483  }
484  return null;
485  }
486 
487  public function indexUnique( $table, $index, $fname = __METHOD__ ) {
488  $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
489  $sql = "SELECT indexname FROM pg_indexes WHERE tablename='{$table}'" .
490  " AND indexdef LIKE 'CREATE UNIQUE%(" .
491  $this->strencode( $this->indexName( $index ) ) .
492  ")'";
493  $res = $this->query( $sql, $fname, $flags );
494  if ( !$res ) {
495  return null;
496  }
497 
498  return $res->numRows() > 0;
499  }
500 
501  public function selectSQLText(
502  $table, $vars, $conds = '', $fname = __METHOD__, $options = [], $join_conds = []
503  ) {
504  if ( is_string( $options ) ) {
505  $options = [ $options ];
506  }
507 
508  // Change the FOR UPDATE option as necessary based on the join conditions. Then pass
509  // to the parent function to get the actual SQL text.
510  // In Postgres when using FOR UPDATE, only the main table and tables that are inner joined
511  // can be locked. That means tables in an outer join cannot be FOR UPDATE locked. Trying to
512  // do so causes a DB error. This wrapper checks which tables can be locked and adjusts it
513  // accordingly.
514  // MySQL uses "ORDER BY NULL" as an optimization hint, but that is illegal in PostgreSQL.
515  if ( is_array( $options ) ) {
516  $forUpdateKey = array_search( 'FOR UPDATE', $options, true );
517  if ( $forUpdateKey !== false && $join_conds ) {
518  unset( $options[$forUpdateKey] );
519  $options['FOR UPDATE'] = [];
520 
521  $toCheck = $table;
522  reset( $toCheck );
523  while ( $toCheck ) {
524  $alias = key( $toCheck );
525  $name = $toCheck[$alias];
526  unset( $toCheck[$alias] );
527 
528  $hasAlias = !is_numeric( $alias );
529  if ( !$hasAlias && is_string( $name ) ) {
530  $alias = $name;
531  }
532 
533  if ( !isset( $join_conds[$alias] ) ||
534  !preg_match( '/^(?:LEFT|RIGHT|FULL)(?: OUTER)? JOIN$/i', $join_conds[$alias][0] )
535  ) {
536  if ( is_array( $name ) ) {
537  // It's a parenthesized group, process all the tables inside the group.
538  $toCheck = array_merge( $toCheck, $name );
539  } else {
540  // Quote alias names so $this->tableName() won't mangle them
541  $options['FOR UPDATE'][] = $hasAlias ? $this->addIdentifierQuotes( $alias ) : $alias;
542  }
543  }
544  }
545  }
546 
547  if ( isset( $options['ORDER BY'] ) && $options['ORDER BY'] == 'NULL' ) {
548  unset( $options['ORDER BY'] );
549  }
550  }
551 
552  return parent::selectSQLText( $table, $vars, $conds, $fname, $options, $join_conds );
553  }
554 
556  return [ 'INSERT INTO', 'ON CONFLICT DO NOTHING' ];
557  }
558 
559  public function doInsertNonConflicting( $table, array $rows, $fname ) {
560  // Postgres 9.5 supports "ON CONFLICT"
561  if ( $this->getServerVersion() >= 9.5 ) {
562  parent::doInsertNonConflicting( $table, $rows, $fname );
563 
564  return;
565  }
566 
567  $affectedRowCount = 0;
568  // Emulate INSERT IGNORE via savepoints/rollback
569  $tok = $this->startAtomic( "$fname (outer)", self::ATOMIC_CANCELABLE );
570  try {
571  $encTable = $this->tableName( $table );
572  foreach ( $rows as $row ) {
573  list( $sqlColumns, $sqlTuples ) = $this->makeInsertLists( [ $row ] );
574  $tempsql = "INSERT INTO $encTable ($sqlColumns) VALUES ($sqlTuples)";
575 
576  $this->startAtomic( "$fname (inner)", self::ATOMIC_CANCELABLE );
577  try {
578  $this->query( $tempsql, $fname, self::QUERY_CHANGE_ROWS );
579  $this->endAtomic( "$fname (inner)" );
581  } catch ( DBQueryError $e ) {
582  $this->cancelAtomic( "$fname (inner)" );
583  // Our IGNORE is supposed to ignore duplicate key errors, but not others.
584  // (even though MySQL's version apparently ignores all errors)
585  if ( $e->errno !== '23505' ) {
586  throw $e;
587  }
588  }
589  }
590  } catch ( RuntimeException $e ) {
591  $this->cancelAtomic( "$fname (outer)", $tok );
592  throw $e;
593  }
594  $this->endAtomic( "$fname (outer)" );
595  // Set the affected row count for the whole operation
596  $this->affectedRowCount = $affectedRowCount;
597  }
598 
599  protected function makeUpdateOptionsArray( $options ) {
600  $options = $this->normalizeOptions( $options );
601  // PostgreSQL doesn't support anything like "ignore" for UPDATE.
602  $options = array_diff( $options, [ 'IGNORE' ] );
603 
604  return parent::makeUpdateOptionsArray( $options );
605  }
606 
625  protected function doInsertSelectNative(
626  $destTable,
627  $srcTable,
628  array $varMap,
629  $conds,
630  $fname,
631  array $insertOptions,
632  array $selectOptions,
633  $selectJoinConds
634  ) {
635  if ( in_array( 'IGNORE', $insertOptions ) ) {
636  if ( $this->getServerVersion() >= 9.5 ) {
637  // Use "ON CONFLICT DO" if we have it for IGNORE
638  $destTable = $this->tableName( $destTable );
639 
640  $selectSql = $this->selectSQLText(
641  $srcTable,
642  array_values( $varMap ),
643  $conds,
644  $fname,
645  $selectOptions,
646  $selectJoinConds
647  );
648 
649  $sql = "INSERT INTO $destTable (" . implode( ',', array_keys( $varMap ) ) . ') ' .
650  $selectSql . ' ON CONFLICT DO NOTHING';
651 
652  $this->query( $sql, $fname, self::QUERY_CHANGE_ROWS );
653  } else {
654  // IGNORE and we don't have ON CONFLICT DO NOTHING, so just use the non-native version
655  $this->doInsertSelectGeneric(
656  $destTable, $srcTable, $varMap, $conds, $fname,
657  $insertOptions, $selectOptions, $selectJoinConds
658  );
659  }
660  } else {
661  parent::doInsertSelectNative( $destTable, $srcTable, $varMap, $conds, $fname,
662  $insertOptions, $selectOptions, $selectJoinConds );
663  }
664  }
665 
666  public function tableName( $name, $format = 'quoted' ) {
667  // Replace reserved words with better ones
668  $name = $this->remappedTableName( $name );
669 
670  return parent::tableName( $name, $format );
671  }
672 
677  public function remappedTableName( $name ) {
678  return $this->keywordTableMap[$name] ?? $name;
679  }
680 
686  public function realTableName( $name, $format = 'quoted' ) {
687  return parent::tableName( $name, $format );
688  }
689 
690  public function nextSequenceValue( $seqName ) {
691  return new NextSequenceValue;
692  }
693 
700  public function currentSequenceValue( $seqName ) {
701  $res = $this->query(
702  "SELECT currval('" . str_replace( "'", "''", $seqName ) . "')",
703  __METHOD__,
704  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
705  );
706  $row = $this->fetchRow( $res );
707  $currval = $row[0];
708 
709  return $currval;
710  }
711 
712  public function textFieldSize( $table, $field ) {
713  $flags = self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE;
714  $encTable = $this->tableName( $table );
715  $sql = "SELECT t.typname as ftype,a.atttypmod as size
716  FROM pg_class c, pg_attribute a, pg_type t
717  WHERE relname='$encTable' AND a.attrelid=c.oid AND
718  a.atttypid=t.oid and a.attname='$field'";
719  $res = $this->query( $sql, __METHOD__, $flags );
720  $row = $this->fetchObject( $res );
721  if ( $row->ftype == 'varchar' ) {
722  $size = $row->size - 4;
723  } else {
724  $size = $row->size;
725  }
726 
727  return $size;
728  }
729 
730  public function limitResult( $sql, $limit, $offset = false ) {
731  return "$sql LIMIT $limit " . ( is_numeric( $offset ) ? " OFFSET {$offset} " : '' );
732  }
733 
734  public function wasDeadlock() {
735  // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
736  return $this->lastErrno() === '40P01';
737  }
738 
739  public function wasLockTimeout() {
740  // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
741  return $this->lastErrno() === '55P03';
742  }
743 
744  public function wasConnectionError( $errno ) {
745  // https://www.postgresql.org/docs/9.2/static/errcodes-appendix.html
746  static $codes = [ '08000', '08003', '08006', '08001', '08004', '57P01', '57P03', '53300' ];
747 
748  return in_array( $errno, $codes, true );
749  }
750 
751  protected function wasKnownStatementRollbackError() {
752  return false; // transaction has to be rolled-back from error state
753  }
754 
755  public function duplicateTableStructure(
756  $oldName, $newName, $temporary = false, $fname = __METHOD__
757  ) {
758  $newNameE = $this->addIdentifierQuotes( $newName );
759  $oldNameE = $this->addIdentifierQuotes( $oldName );
760 
761  $temporary = $temporary ? 'TEMPORARY' : '';
762 
763  $ret = $this->query(
764  "CREATE $temporary TABLE $newNameE " .
765  "(LIKE $oldNameE INCLUDING DEFAULTS INCLUDING INDEXES)",
766  $fname,
767  self::QUERY_PSEUDO_PERMANENT | self::QUERY_CHANGE_SCHEMA
768  );
769  if ( !$ret ) {
770  return $ret;
771  }
772 
773  $res = $this->query(
774  'SELECT attname FROM pg_class c'
775  . ' JOIN pg_namespace n ON (n.oid = c.relnamespace)'
776  . ' JOIN pg_attribute a ON (a.attrelid = c.oid)'
777  . ' JOIN pg_attrdef d ON (c.oid=d.adrelid and a.attnum=d.adnum)'
778  . ' WHERE relkind = \'r\''
779  . ' AND nspname = ' . $this->addQuotes( $this->getCoreSchema() )
780  . ' AND relname = ' . $this->addQuotes( $oldName )
781  . ' AND pg_get_expr(adbin, adrelid) LIKE \'nextval(%\'',
782  $fname,
783  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
784  );
785  $row = $this->fetchObject( $res );
786  if ( $row ) {
787  $field = $row->attname;
788  $newSeq = "{$newName}_{$field}_seq";
789  $fieldE = $this->addIdentifierQuotes( $field );
790  $newSeqE = $this->addIdentifierQuotes( $newSeq );
791  $newSeqQ = $this->addQuotes( $newSeq );
792  $this->query(
793  "CREATE $temporary SEQUENCE $newSeqE OWNED BY $newNameE.$fieldE",
794  $fname,
795  self::QUERY_CHANGE_SCHEMA
796  );
797  $this->query(
798  "ALTER TABLE $newNameE ALTER COLUMN $fieldE SET DEFAULT nextval({$newSeqQ}::regclass)",
799  $fname,
800  self::QUERY_CHANGE_SCHEMA
801  );
802  }
803 
804  return $ret;
805  }
806 
807  protected function doTruncate( array $tables, $fname ) {
808  $encTables = $this->tableNamesN( ...$tables );
809  $sql = "TRUNCATE TABLE " . implode( ',', $encTables ) . " RESTART IDENTITY";
810  $this->query( $sql, $fname, self::QUERY_CHANGE_SCHEMA );
811  }
812 
819  public function listTables( $prefix = '', $fname = __METHOD__ ) {
820  $eschemas = implode( ',', array_map( [ $this, 'addQuotes' ], $this->getCoreSchemas() ) );
821  $result = $this->query(
822  "SELECT DISTINCT tablename FROM pg_tables WHERE schemaname IN ($eschemas)",
823  $fname,
824  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
825  );
826  $endArray = [];
827 
828  foreach ( $result as $table ) {
829  $vars = get_object_vars( $table );
830  $table = array_pop( $vars );
831  if ( $prefix == '' || strpos( $table, $prefix ) === 0 ) {
832  $endArray[] = $table;
833  }
834  }
835 
836  return $endArray;
837  }
838 
839  public function timestamp( $ts = 0 ) {
840  $ct = new ConvertibleTimestamp( $ts );
841 
842  return $ct->getTimestamp( TS_POSTGRES );
843  }
844 
863  private function pg_array_parse( $text, &$output, $limit = false, $offset = 1 ) {
864  if ( $limit === false ) {
865  $limit = strlen( $text ) - 1;
866  $output = [];
867  }
868  if ( $text == '{}' ) {
869  return $output;
870  }
871  do {
872  if ( $text[$offset] != '{' ) {
873  preg_match( "/(\\{?\"([^\"\\\\]|\\\\.)*\"|[^,{}]+)+([,}]+)/",
874  $text, $match, 0, $offset );
875  $offset += strlen( $match[0] );
876  $output[] = ( $match[1][0] != '"'
877  ? $match[1]
878  : stripcslashes( substr( $match[1], 1, -1 ) ) );
879  if ( $match[3] == '},' ) {
880  return $output;
881  }
882  } else {
883  $offset = $this->pg_array_parse( $text, $output, $limit, $offset + 1 );
884  }
885  } while ( $limit > $offset );
886 
887  return $output;
888  }
889 
890  public function aggregateValue( $valuedata, $valuename = 'value' ) {
891  return $valuedata;
892  }
893 
894  public function getSoftwareLink() {
895  return '[{{int:version-db-postgres-url}} PostgreSQL]';
896  }
897 
905  public function getCurrentSchema() {
906  $res = $this->query(
907  "SELECT current_schema()",
908  __METHOD__,
909  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
910  );
911  $row = $this->fetchRow( $res );
912 
913  return $row[0];
914  }
915 
926  public function getSchemas() {
927  $res = $this->query(
928  "SELECT current_schemas(false)",
929  __METHOD__,
930  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
931  );
932  $row = $this->fetchRow( $res );
933  $schemas = [];
934 
935  /* PHP pgsql support does not support array type, "{a,b}" string is returned */
936 
937  return $this->pg_array_parse( $row[0], $schemas );
938  }
939 
949  public function getSearchPath() {
950  $res = $this->query(
951  "SHOW search_path",
952  __METHOD__,
953  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
954  );
955  $row = $this->fetchRow( $res );
956 
957  /* PostgreSQL returns SHOW values as strings */
958 
959  return explode( ",", $row[0] );
960  }
961 
969  private function setSearchPath( $search_path ) {
970  $this->query(
971  "SET search_path = " . implode( ", ", $search_path ),
972  __METHOD__,
973  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_TRX
974  );
975  }
976 
991  public function determineCoreSchema( $desiredSchema ) {
992  if ( $this->trxLevel() ) {
993  // We do not want the schema selection to change on ROLLBACK or INSERT SELECT.
994  // See https://www.postgresql.org/docs/8.3/sql-set.html
995  throw new DBUnexpectedError(
996  $this,
997  __METHOD__ . ": a transaction is currently active"
998  );
999  }
1000 
1001  if ( $this->schemaExists( $desiredSchema ) ) {
1002  if ( in_array( $desiredSchema, $this->getSchemas() ) ) {
1003  $this->coreSchema = $desiredSchema;
1004  $this->queryLogger->debug(
1005  "Schema \"" . $desiredSchema . "\" already in the search path\n" );
1006  } else {
1007  // Prepend the desired schema to the search path (T17816)
1008  $search_path = $this->getSearchPath();
1009  array_unshift( $search_path, $this->addIdentifierQuotes( $desiredSchema ) );
1010  $this->setSearchPath( $search_path );
1011  $this->coreSchema = $desiredSchema;
1012  $this->queryLogger->debug(
1013  "Schema \"" . $desiredSchema . "\" added to the search path\n" );
1014  }
1015  } else {
1016  $this->coreSchema = $this->getCurrentSchema();
1017  $this->queryLogger->debug(
1018  "Schema \"" . $desiredSchema . "\" not found, using current \"" .
1019  $this->coreSchema . "\"\n" );
1020  }
1021  }
1022 
1029  public function getCoreSchema() {
1030  return $this->coreSchema;
1031  }
1032 
1039  public function getCoreSchemas() {
1040  if ( $this->tempSchema ) {
1041  return [ $this->tempSchema, $this->getCoreSchema() ];
1042  }
1043 
1044  $res = $this->query(
1045  "SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = pg_my_temp_schema()",
1046  __METHOD__,
1047  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1048  );
1049  $row = $this->fetchObject( $res );
1050  if ( $row ) {
1051  $this->tempSchema = $row->nspname;
1052  return [ $this->tempSchema, $this->getCoreSchema() ];
1053  }
1054 
1055  return [ $this->getCoreSchema() ];
1056  }
1057 
1058  public function getServerVersion() {
1059  if ( !isset( $this->numericVersion ) ) {
1060  $conn = $this->getBindingHandle();
1061  $versionInfo = pg_version( $conn );
1062  if ( version_compare( $versionInfo['client'], '7.4.0', 'lt' ) ) {
1063  // Old client, abort install
1064  $this->numericVersion = '7.3 or earlier';
1065  } elseif ( isset( $versionInfo['server'] ) ) {
1066  // Normal client
1067  $this->numericVersion = $versionInfo['server'];
1068  } else {
1069  // T18937: broken pgsql extension from PHP<5.3
1070  $this->numericVersion = pg_parameter_status( $conn, 'server_version' );
1071  }
1072  }
1073 
1074  return $this->numericVersion;
1075  }
1076 
1085  private function relationExists( $table, $types, $schema = false ) {
1086  if ( !is_array( $types ) ) {
1087  $types = [ $types ];
1088  }
1089  if ( $schema === false ) {
1090  $schemas = $this->getCoreSchemas();
1091  } else {
1092  $schemas = [ $schema ];
1093  }
1094  $table = $this->realTableName( $table, 'raw' );
1095  $etable = $this->addQuotes( $table );
1096  foreach ( $schemas as $schema ) {
1097  $eschema = $this->addQuotes( $schema );
1098  $sql = "SELECT 1 FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n "
1099  . "WHERE c.relnamespace = n.oid AND c.relname = $etable AND n.nspname = $eschema "
1100  . "AND c.relkind IN ('" . implode( "','", $types ) . "')";
1101  $res = $this->query(
1102  $sql,
1103  __METHOD__,
1104  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1105  );
1106  if ( $res && $res->numRows() ) {
1107  return true;
1108  }
1109  }
1110 
1111  return false;
1112  }
1113 
1121  public function tableExists( $table, $fname = __METHOD__, $schema = false ) {
1122  return $this->relationExists( $table, [ 'r', 'v' ], $schema );
1123  }
1124 
1125  public function sequenceExists( $sequence, $schema = false ) {
1126  return $this->relationExists( $sequence, 'S', $schema );
1127  }
1128 
1129  public function triggerExists( $table, $trigger ) {
1130  $q = <<<SQL
1131  SELECT 1 FROM pg_class, pg_namespace, pg_trigger
1132  WHERE relnamespace=pg_namespace.oid AND relkind='r'
1133  AND tgrelid=pg_class.oid
1134  AND nspname=%s AND relname=%s AND tgname=%s
1135 SQL;
1136  foreach ( $this->getCoreSchemas() as $schema ) {
1137  $res = $this->query(
1138  sprintf(
1139  $q,
1140  $this->addQuotes( $schema ),
1141  $this->addQuotes( $table ),
1142  $this->addQuotes( $trigger )
1143  ),
1144  __METHOD__,
1145  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1146  );
1147  if ( $res && $res->numRows() ) {
1148  return true;
1149  }
1150  }
1151 
1152  return false;
1153  }
1154 
1155  public function ruleExists( $table, $rule ) {
1156  $exists = $this->selectField( 'pg_rules', 'rulename',
1157  [
1158  'rulename' => $rule,
1159  'tablename' => $table,
1160  'schemaname' => $this->getCoreSchemas()
1161  ],
1162  __METHOD__
1163  );
1164 
1165  return $exists === $rule;
1166  }
1167 
1168  public function constraintExists( $table, $constraint ) {
1169  foreach ( $this->getCoreSchemas() as $schema ) {
1170  $sql = sprintf( "SELECT 1 FROM information_schema.table_constraints " .
1171  "WHERE constraint_schema = %s AND table_name = %s AND constraint_name = %s",
1172  $this->addQuotes( $schema ),
1173  $this->addQuotes( $table ),
1174  $this->addQuotes( $constraint )
1175  );
1176  $res = $this->query(
1177  $sql,
1178  __METHOD__,
1179  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1180  );
1181  if ( $res && $res->numRows() ) {
1182  return true;
1183  }
1184  }
1185  return false;
1186  }
1187 
1193  public function schemaExists( $schema ) {
1194  if ( !strlen( $schema ) ) {
1195  return false; // short-circuit
1196  }
1197 
1198  $res = $this->query(
1199  "SELECT 1 FROM pg_catalog.pg_namespace " .
1200  "WHERE nspname = " . $this->addQuotes( $schema ) . " LIMIT 1",
1201  __METHOD__,
1202  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1203  );
1204 
1205  return ( $this->numRows( $res ) > 0 );
1206  }
1207 
1213  public function roleExists( $roleName ) {
1214  $res = $this->query(
1215  "SELECT 1 FROM pg_catalog.pg_roles " .
1216  "WHERE rolname = " . $this->addQuotes( $roleName ) . " LIMIT 1",
1217  __METHOD__,
1218  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1219  );
1220 
1221  return ( $this->numRows( $res ) > 0 );
1222  }
1223 
1229  public function fieldInfo( $table, $field ) {
1230  return PostgresField::fromText( $this, $table, $field );
1231  }
1232 
1239  public function fieldType( $res, $index ) {
1240  return pg_field_type( ResultWrapper::unwrap( $res ), $index );
1241  }
1242 
1243  public function encodeBlob( $b ) {
1244  return new PostgresBlob( pg_escape_bytea( $b ) );
1245  }
1246 
1247  public function decodeBlob( $b ) {
1248  if ( $b instanceof PostgresBlob ) {
1249  $b = $b->fetch();
1250  } elseif ( $b instanceof Blob ) {
1251  return $b->fetch();
1252  }
1253 
1254  return pg_unescape_bytea( $b );
1255  }
1256 
1257  public function strencode( $s ) {
1258  // Should not be called by us
1259  return pg_escape_string( $this->getBindingHandle(), (string)$s );
1260  }
1261 
1262  public function addQuotes( $s ) {
1263  $conn = $this->getBindingHandle();
1264 
1265  if ( $s === null ) {
1266  return 'NULL';
1267  } elseif ( is_bool( $s ) ) {
1268  return (string)intval( $s );
1269  } elseif ( is_int( $s ) ) {
1270  return (string)$s;
1271  } elseif ( $s instanceof Blob ) {
1272  if ( $s instanceof PostgresBlob ) {
1273  $s = $s->fetch();
1274  } else {
1275  $s = pg_escape_bytea( $conn, $s->fetch() );
1276  }
1277  return "'$s'";
1278  } elseif ( $s instanceof NextSequenceValue ) {
1279  return 'DEFAULT';
1280  }
1281 
1282  return "'" . pg_escape_string( $conn, (string)$s ) . "'";
1283  }
1284 
1285  protected function makeSelectOptions( array $options ) {
1286  $preLimitTail = $postLimitTail = '';
1287  $startOpts = $useIndex = $ignoreIndex = '';
1288 
1289  $noKeyOptions = [];
1290  foreach ( $options as $key => $option ) {
1291  if ( is_numeric( $key ) ) {
1292  $noKeyOptions[$option] = true;
1293  }
1294  }
1295 
1296  $preLimitTail .= $this->makeGroupByWithHaving( $options );
1297 
1298  $preLimitTail .= $this->makeOrderBy( $options );
1299 
1300  if ( isset( $options['FOR UPDATE'] ) ) {
1301  $postLimitTail .= ' FOR UPDATE OF ' .
1302  implode( ', ', array_map( [ $this, 'tableName' ], $options['FOR UPDATE'] ) );
1303  } elseif ( isset( $noKeyOptions['FOR UPDATE'] ) ) {
1304  $postLimitTail .= ' FOR UPDATE';
1305  }
1306 
1307  if ( isset( $noKeyOptions['DISTINCT'] ) || isset( $noKeyOptions['DISTINCTROW'] ) ) {
1308  $startOpts .= 'DISTINCT';
1309  }
1310 
1311  return [ $startOpts, $useIndex, $preLimitTail, $postLimitTail, $ignoreIndex ];
1312  }
1313 
1314  public function buildConcat( $stringList ) {
1315  return implode( ' || ', $stringList );
1316  }
1317 
1318  public function buildGroupConcatField(
1319  $delimiter, $table, $field, $conds = '', $options = [], $join_conds = []
1320  ) {
1321  $fld = "array_to_string(array_agg($field)," . $this->addQuotes( $delimiter ) . ')';
1322 
1323  return '(' . $this->selectSQLText( $table, $fld, $conds, null, [], $join_conds ) . ')';
1324  }
1325 
1326  public function buildStringCast( $field ) {
1327  return $field . '::text';
1328  }
1329 
1330  public function streamStatementEnd( &$sql, &$newLine ) {
1331  # Allow dollar quoting for function declarations
1332  if ( substr( $newLine, 0, 4 ) == '$mw$' ) {
1333  if ( $this->delimiter ) {
1334  $this->delimiter = false;
1335  } else {
1336  $this->delimiter = ';';
1337  }
1338  }
1339 
1340  return parent::streamStatementEnd( $sql, $newLine );
1341  }
1342 
1343  public function doLockTables( array $read, array $write, $method ) {
1344  $tablesWrite = [];
1345  foreach ( $write as $table ) {
1346  $tablesWrite[] = $this->tableName( $table );
1347  }
1348  $tablesRead = [];
1349  foreach ( $read as $table ) {
1350  $tablesRead[] = $this->tableName( $table );
1351  }
1352 
1353  // Acquire locks for the duration of the current transaction...
1354  if ( $tablesWrite ) {
1355  $this->query(
1356  'LOCK TABLE ONLY ' . implode( ',', $tablesWrite ) . ' IN EXCLUSIVE MODE',
1357  $method,
1358  self::QUERY_CHANGE_ROWS
1359  );
1360  }
1361  if ( $tablesRead ) {
1362  $this->query(
1363  'LOCK TABLE ONLY ' . implode( ',', $tablesRead ) . ' IN SHARE MODE',
1364  $method,
1365  self::QUERY_CHANGE_ROWS
1366  );
1367  }
1368 
1369  return true;
1370  }
1371 
1372  public function lockIsFree( $lockName, $method ) {
1373  if ( !parent::lockIsFree( $lockName, $method ) ) {
1374  return false; // already held
1375  }
1376  // http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
1377  $key = $this->addQuotes( $this->bigintFromLockName( $lockName ) );
1378  $res = $this->query(
1379  "SELECT (CASE(pg_try_advisory_lock($key))
1380  WHEN 'f' THEN 'f' ELSE pg_advisory_unlock($key) END) AS lockstatus",
1381  $method,
1382  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1383  );
1384  $row = $this->fetchObject( $res );
1385 
1386  return ( $row->lockstatus === 't' );
1387  }
1388 
1389  public function lock( $lockName, $method, $timeout = 5 ) {
1390  // http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
1391  $key = $this->addQuotes( $this->bigintFromLockName( $lockName ) );
1392  $loop = new WaitConditionLoop(
1393  function () use ( $lockName, $key, $timeout, $method ) {
1394  $res = $this->query(
1395  "SELECT pg_try_advisory_lock($key) AS lockstatus",
1396  $method,
1397  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_ROWS
1398  );
1399  $row = $this->fetchObject( $res );
1400  if ( $row->lockstatus === 't' ) {
1401  parent::lock( $lockName, $method, $timeout ); // record
1402  return true;
1403  }
1404 
1405  return WaitConditionLoop::CONDITION_CONTINUE;
1406  },
1407  $timeout
1408  );
1409 
1410  return ( $loop->invoke() === $loop::CONDITION_REACHED );
1411  }
1412 
1413  public function unlock( $lockName, $method ) {
1414  // http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
1415  $key = $this->addQuotes( $this->bigintFromLockName( $lockName ) );
1416  $result = $this->query(
1417  "SELECT pg_advisory_unlock($key) as lockstatus",
1418  $method,
1419  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_ROWS
1420  );
1421  $row = $this->fetchObject( $result );
1422 
1423  if ( $row->lockstatus === 't' ) {
1424  parent::unlock( $lockName, $method ); // record
1425  return true;
1426  }
1427 
1428  $this->queryLogger->debug( __METHOD__ . " failed to release lock" );
1429 
1430  return false;
1431  }
1432 
1433  public function serverIsReadOnly() {
1434  $res = $this->query(
1435  "SHOW default_transaction_read_only",
1436  __METHOD__,
1437  self::QUERY_IGNORE_DBO_TRX | self::QUERY_CHANGE_NONE
1438  );
1439  $row = $this->fetchObject( $res );
1440 
1441  return $row ? ( strtolower( $row->default_transaction_read_only ) === 'on' ) : false;
1442  }
1443 
1444  protected static function getAttributes() {
1445  return [ self::ATTR_SCHEMAS_AS_TABLE_GROUPS => true ];
1446  }
1447 
1452  private function bigintFromLockName( $lockName ) {
1453  return \Wikimedia\base_convert( substr( sha1( $lockName ), 0, 15 ), 16, 10 );
1454  }
1455 }
1456 
1460 class_alias( DatabasePostgres::class, 'DatabasePostgres' );
Wikimedia\Rdbms\DatabasePostgres\freeResult
freeResult( $res)
Free a result object returned by query() or select()It's usually not necessary to call this,...
Definition: DatabasePostgres.php:252
Wikimedia\Rdbms\Database\getLastPHPError
getLastPHPError()
Definition: Database.php:859
Wikimedia\Rdbms\Database
Relational database abstraction object.
Definition: Database.php:50
Wikimedia\Rdbms\DatabasePostgres\doLockTables
doLockTables(array $read, array $write, $method)
Helper function for lockTables() that handles the actual table locking.
Definition: DatabasePostgres.php:1343
Wikimedia\Rdbms\DatabasePostgres\numFields
numFields( $res)
Get the number of fields in a result object.
Definition: DatabasePostgres.php:316
Wikimedia\Rdbms\DatabasePostgres\bigintFromLockName
bigintFromLockName( $lockName)
Definition: DatabasePostgres.php:1452
Wikimedia\Rdbms\Database\getBindingHandle
getBindingHandle()
Get the underlying binding connection handle.
Definition: Database.php:5464
Wikimedia\Rdbms\DatabasePostgres\tableName
tableName( $name, $format='quoted')
Format a table name ready for use in constructing an SQL query.This does two important things: it quo...
Definition: DatabasePostgres.php:666
Wikimedia\Rdbms\DatabasePostgres\getSoftwareLink
getSoftwareLink()
Returns a wikitext style link to the DB's website (e.g.
Definition: DatabasePostgres.php:894
Wikimedia\Rdbms\DatabasePostgres\closeConnection
closeConnection()
Closes underlying database connection.
Definition: DatabasePostgres.php:199
Wikimedia\Rdbms\DatabasePostgres\relationExists
relationExists( $table, $types, $schema=false)
Query whether a given relation exists (in the given schema, or the default mw one if not given)
Definition: DatabasePostgres.php:1085
Wikimedia\Rdbms\DatabasePostgres\makeUpdateOptionsArray
makeUpdateOptionsArray( $options)
Make UPDATE options array for Database::makeUpdateOptions.
Definition: DatabasePostgres.php:599
Wikimedia\Rdbms\DatabasePostgres\getSchemas
getSchemas()
Return list of schemas which are accessible without schema name This is list does not contain magic k...
Definition: DatabasePostgres.php:926
Wikimedia\Rdbms\DatabasePostgres\remappedTableName
remappedTableName( $name)
Definition: DatabasePostgres.php:677
Wikimedia\Rdbms\DatabasePostgres\addQuotes
addQuotes( $s)
Escape and quote a raw value string for use in a SQL query.string Stable to override Stable to overri...
Definition: DatabasePostgres.php:1262
Wikimedia\Rdbms\Database\tableNamesN
tableNamesN(... $tables)
Fetch a number of table names into an zero-indexed numerical array This is handy when you need to con...
Definition: Database.php:2921
Wikimedia\Rdbms\DatabasePostgres\fetchAffectedRowCount
fetchAffectedRowCount()
Definition: DatabasePostgres.php:359
Wikimedia\Rdbms\DatabasePostgres\getAttributes
static getAttributes()
Stable to override.
Definition: DatabasePostgres.php:1444
Wikimedia\Rdbms\DatabasePostgres\getCoreSchemas
getCoreSchemas()
Return schema names for temporary tables and core application tables.
Definition: DatabasePostgres.php:1039
n
while(( $__line=Maintenance::readconsole()) !==false) print n
Definition: eval.php:64
Wikimedia\Rdbms\DatabasePostgres\fieldInfo
fieldInfo( $table, $field)
Definition: DatabasePostgres.php:1229
Wikimedia\Rdbms\DatabasePostgres\makeSelectOptions
makeSelectOptions(array $options)
Returns an optional USE INDEX clause to go after the table, and a string to go at the end of the quer...
Definition: DatabasePostgres.php:1285
Wikimedia\Rdbms\Database\$password
string $password
Password used to establish the current connection.
Definition: Database.php:82
Wikimedia\Rdbms\DatabasePostgres\unlock
unlock( $lockName, $method)
Release a lock.Named locks are not related to transactionsName of lock to release Name of the calling...
Definition: DatabasePostgres.php:1413
true
return true
Definition: router.php:90
Wikimedia\Rdbms\DatabasePostgres\listTables
listTables( $prefix='', $fname=__METHOD__)
Definition: DatabasePostgres.php:819
Wikimedia\Rdbms\Database\$delimiter
string $delimiter
Current SQL query delimiter.
Definition: Database.php:103
Wikimedia\Rdbms\Database\endAtomic
endAtomic( $fname=__METHOD__)
Ends an atomic section of SQL statements.
Definition: Database.php:4337
Wikimedia\Rdbms\Database\indexName
indexName( $index)
Allows for index remapping in queries where this is not consistent across DBMS.
Definition: Database.php:3102
Wikimedia\Rdbms\DatabasePostgres\fieldType
fieldType( $res, $index)
pg_field_type() wrapper
Definition: DatabasePostgres.php:1239
Wikimedia\Rdbms\DatabaseDomain\getTablePrefix
getTablePrefix()
Definition: DatabaseDomain.php:189
Wikimedia\Rdbms\DatabasePostgres\$numericVersion
float string $numericVersion
Definition: DatabasePostgres.php:43
Wikimedia\Rdbms
Definition: ChronologyProtector.php:24
Wikimedia\Rdbms\Database\normalizeConditions
normalizeConditions( $conds, $fname)
Definition: Database.php:2143
Wikimedia\Rdbms\Database\normalizeOptions
normalizeOptions( $options)
Definition: Database.php:2216
Wikimedia\Rdbms\DatabasePostgres\doInsertSelectNative
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',...
Definition: DatabasePostgres.php:625
$s
$s
Definition: mergeMessageFileList.php:184
Wikimedia\Rdbms\Database\extractSingleFieldFromList
extractSingleFieldFromList( $var)
Definition: Database.php:2246
DBO_SSL
const DBO_SSL
Definition: defines.php:17
Wikimedia\Rdbms\DatabasePostgres\makeInsertNonConflictingVerbAndOptions
makeInsertNonConflictingVerbAndOptions()
Stable to override.
Definition: DatabasePostgres.php:555
Wikimedia\Rdbms\DatabaseDomain\getDatabase
getDatabase()
Definition: DatabaseDomain.php:175
Wikimedia\Rdbms\DatabasePostgres
Definition: DatabasePostgres.php:33
Wikimedia\Rdbms\DatabasePostgres\textFieldSize
textFieldSize( $table, $field)
Returns the size of a text field, or -1 for "unlimited".int Stable to override Stable to override
Definition: DatabasePostgres.php:712
$res
$res
Definition: testCompression.php:57
Wikimedia\Rdbms\Database\cancelAtomic
cancelAtomic( $fname=__METHOD__, AtomicSectionIdentifier $sectionId=null)
Cancel an atomic section of SQL statements.
Definition: Database.php:4371
Wikimedia\Rdbms\Database\getFlag
getFlag( $flag)
Returns a boolean whether the flag $flag is set for this connection.
Definition: Database.php:805
Wikimedia\Rdbms\DatabasePostgres\indexUnique
indexUnique( $table, $index, $fname=__METHOD__)
Determines if a given index is unique.Calling function namebool Stable to override Stable to override
Definition: DatabasePostgres.php:487
Wikimedia\Rdbms\Database\doInsertSelectGeneric
doInsertSelectGeneric( $destTable, $srcTable, array $varMap, $conds, $fname, array $insertOptions, array $selectOptions, $selectJoinConds)
Implementation of insertSelect() based on select() and insert()
Definition: Database.php:3527
Wikimedia\Rdbms\DatabasePostgres\insertId
insertId()
Get the inserted value of an auto-increment row.
Definition: DatabasePostgres.php:324
Wikimedia\Rdbms\DatabasePostgres\fetchObject
fetchObject( $res)
Fetch the next row from the given result object, in object form.
Definition: DatabasePostgres.php:261
Wikimedia\Rdbms\Database\$affectedRowCount
int null $affectedRowCount
Rows affected by the last query to query() or its CRUD wrappers.
Definition: Database.php:176
Wikimedia\Rdbms\DatabasePostgres\aggregateValue
aggregateValue( $valuedata, $valuename='value')
Return aggregated value alias.array|string Since 1.33 Stable to override Stable to override
Definition: DatabasePostgres.php:890
Wikimedia\Rdbms\DatabasePostgres\open
open( $server, $user, $password, $dbName, $schema, $tablePrefix)
Open a new connection to the database (closing any existing one)
Definition: DatabasePostgres.php:82
Wikimedia\Rdbms\DatabasePostgres\roleExists
roleExists( $roleName)
Returns true if a given role (i.e.
Definition: DatabasePostgres.php:1213
Wikimedia\Rdbms\DatabasePostgres\$coreSchema
string $coreSchema
Definition: DatabasePostgres.php:37
Wikimedia\Rdbms\Database\close
close( $fname=__METHOD__, $owner=null)
Close the database connection.
Definition: Database.php:898
Wikimedia\Rdbms\DatabasePostgres\encodeBlob
encodeBlob( $b)
Some DBMSs have a special format for inserting into blob fields, they don't allow simple quoted strin...
Definition: DatabasePostgres.php:1243
Wikimedia\Rdbms\DatabasePostgres\makeConnectionString
makeConnectionString( $vars)
Definition: DatabasePostgres.php:190
Wikimedia\Rdbms\DatabasePostgres\lockIsFree
lockIsFree( $lockName, $method)
Check to see if a named lock is not locked by any thread (non-blocking)Name of lock to poll Name of m...
Definition: DatabasePostgres.php:1372
Wikimedia\Rdbms\DatabasePostgres\isTransactableQuery
isTransactableQuery( $sql)
Determine whether a SQL statement is sensitive to isolation level.
Definition: DatabasePostgres.php:203
Wikimedia\Rdbms\DatabasePostgres\schemaExists
schemaExists( $schema)
Query whether a given schema exists.
Definition: DatabasePostgres.php:1193
Wikimedia\Rdbms\DatabasePostgres\implicitOrderby
implicitOrderby()
Returns true if this database does an implicit order by when the column has an index For example: SEL...
Definition: DatabasePostgres.php:64
Wikimedia\Rdbms\Database\startAtomic
startAtomic( $fname=__METHOD__, $cancelable=self::ATOMIC_NOT_CANCELABLE)
Begin an atomic section of SQL statements.
Definition: Database.php:4307
Wikimedia\Rdbms\DatabasePostgres\limitResult
limitResult( $sql, $limit, $offset=false)
Construct a LIMIT query with optional offset.The SQL should be adjusted so that only the first $limit...
Definition: DatabasePostgres.php:730
Wikimedia\Rdbms\DatabasePostgres\duplicateTableStructure
duplicateTableStructure( $oldName, $newName, $temporary=false, $fname=__METHOD__)
Creates a new table with structure copied from existing table.Note that unlike most database abstract...
Definition: DatabasePostgres.php:755
Wikimedia\Rdbms\PostgresField\fromText
static fromText(DatabasePostgres $db, $table, $field)
Definition: PostgresField.php:15
Wikimedia\Rdbms\Database\trxLevel
trxLevel()
Gets the current transaction level.
Definition: Database.php:548
Wikimedia\Rdbms\DatabasePostgres\doQuery
doQuery( $sql)
Definition: DatabasePostgres.php:212
Wikimedia\Rdbms\DatabasePostgres\buildStringCast
buildStringCast( $field)
Field or column to cast string 1.28 Stable to override Stable to override
Definition: DatabasePostgres.php:1326
Wikimedia\Rdbms\DatabasePostgres\$lastResultHandle
resource null $lastResultHandle
Definition: DatabasePostgres.php:46
Wikimedia\Rdbms\Database\makeInsertLists
makeInsertLists(array $rows)
Make SQL lists of columns, row tuples for INSERT/VALUES expressions.
Definition: Database.php:2383
Wikimedia\Rdbms\DatabasePostgres\pg_array_parse
pg_array_parse( $text, &$output, $limit=false, $offset=1)
Posted by cc[plus]php[at]c2se[dot]com on 25-Mar-2009 09:12 to https://www.php.net/manual/en/ref....
Definition: DatabasePostgres.php:863
Wikimedia\Rdbms\DatabasePostgres\lastErrno
lastErrno()
Get the last error number.
Definition: DatabasePostgres.php:351
Wikimedia\Rdbms\Database\$user
string $user
User that this instance is currently connected under the name of.
Definition: Database.php:80
Wikimedia\Rdbms\DatabasePostgres\selectSQLText
selectSQLText( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Take the same arguments as IDatabase::select() and return the SQL it would use.This can be useful for...
Definition: DatabasePostgres.php:501
Wikimedia\Rdbms\DatabasePostgres\numRows
numRows( $res)
Get the number of rows in a query result.
Definition: DatabasePostgres.php:296
Wikimedia\Rdbms\NextSequenceValue
Used by Database::nextSequenceValue() so Database::insert() can detect values coming from the depreca...
Definition: NextSequenceValue.php:11
Wikimedia\Rdbms\DatabasePostgres\strencode
strencode( $s)
Wrapper for addslashes()
Definition: DatabasePostgres.php:1257
Wikimedia\Rdbms\DBQueryError
@newable Stable to extend
Definition: DBQueryError.php:29
Wikimedia\Rdbms\DatabasePostgres\wasConnectionError
wasConnectionError( $errno)
Do not use this method outside of Database/DBError classes.
Definition: DatabasePostgres.php:744
Wikimedia\Rdbms\Database\installErrorHandler
installErrorHandler()
Set a custom error handler for logging errors during database connection.
Definition: Database.php:836
Wikimedia\Rdbms\Database\restoreErrorHandler
restoreErrorHandler()
Restore the previous error handler and return the last PHP error for this DB.
Definition: Database.php:847
Wikimedia\Rdbms\DatabasePostgres\serverIsReadOnly
serverIsReadOnly()
bool Whether the DB is marked as read-only server-side 1.28 Stable to override Stable to override
Definition: DatabasePostgres.php:1433
Wikimedia\Rdbms\DatabasePostgres\doTruncate
doTruncate(array $tables, $fname)
Definition: DatabasePostgres.php:807
Wikimedia\Rdbms\DatabasePostgres\timestamp
timestamp( $ts=0)
Convert a timestamp in one of the formats accepted by ConvertibleTimestamp to the format used for ins...
Definition: DatabasePostgres.php:839
Wikimedia\Rdbms\DatabasePostgres\relationSchemaQualifier
relationSchemaQualifier()
Stable to override.
Definition: DatabasePostgres.php:154
Wikimedia\Rdbms\Database\query
query( $sql, $fname=__METHOD__, $flags=self::QUERY_NORMAL)
Run an SQL query and return the result.
Definition: Database.php:1216
Wikimedia\Rdbms\DatabasePostgres\decodeBlob
decodeBlob( $b)
Some DBMSs return a special placeholder object representing blob fields in result objects....
Definition: DatabasePostgres.php:1247
Wikimedia\Rdbms\DatabasePostgres\getCurrentSchema
getCurrentSchema()
Return current schema (executes SELECT current_schema()) Needs transaction.
Definition: DatabasePostgres.php:905
Wikimedia\Rdbms\DBUnexpectedError
@newable Stable to extend
Definition: DBUnexpectedError.php:29
Wikimedia\Rdbms\DatabasePostgres\realTableName
realTableName( $name, $format='quoted')
Definition: DatabasePostgres.php:686
Wikimedia\Rdbms\Database\selectField
selectField( $table, $var, $cond='', $fname=__METHOD__, $options=[], $join_conds=[])
A SELECT wrapper which returns a single field from a single result row.
Definition: Database.php:1736
Wikimedia\Rdbms\DatabasePostgres\nextSequenceValue
nextSequenceValue( $seqName)
Deprecated method, calls should be removed.
Definition: DatabasePostgres.php:690
Wikimedia\Rdbms\DatabasePostgres\buildConcat
buildConcat( $stringList)
Build a concatenation list to feed into a SQL query.Raw SQL expression list; caller is responsible fo...
Definition: DatabasePostgres.php:1314
Wikimedia\Rdbms\DatabasePostgres\getType
getType()
Get the type of the DBMS (e.g.
Definition: DatabasePostgres.php:60
Wikimedia\Rdbms\Database\addIdentifierQuotes
addIdentifierQuotes( $s)
Escape a SQL identifier (e.g.table, column, database) for use in a SQL queryDepending on the database...
Definition: Database.php:3129
Wikimedia\Rdbms\DatabasePostgres\getServerVersion
getServerVersion()
A string describing the current software version, like from mysql_get_server_info()
Definition: DatabasePostgres.php:1058
Wikimedia\Rdbms\DatabasePostgres\lastError
lastError()
Get a description of the last error.
Definition: DatabasePostgres.php:339
Wikimedia\Rdbms\Database\newExceptionAfterConnectError
newExceptionAfterConnectError( $error)
Definition: Database.php:1707
Wikimedia\Rdbms\DatabasePostgres\__construct
__construct(array $params)
Definition: DatabasePostgres.php:53
Wikimedia\Rdbms\DatabasePostgres\constraintExists
constraintExists( $table, $constraint)
Definition: DatabasePostgres.php:1168
Wikimedia\Rdbms\DatabasePostgres\wasLockTimeout
wasLockTimeout()
Determines if the last failure was due to a lock timeout.Note that during a lock wait timeout,...
Definition: DatabasePostgres.php:739
Wikimedia\Rdbms\DatabasePostgres\$port
int null $port
Definition: DatabasePostgres.php:35
Wikimedia\Rdbms\DatabasePostgres\streamStatementEnd
streamStatementEnd(&$sql, &$newLine)
Called by sourceStream() to check if we've reached a statement end.
Definition: DatabasePostgres.php:1330
Wikimedia\Rdbms\Database\makeGroupByWithHaving
makeGroupByWithHaving( $options)
Returns an optional GROUP BY with an optional HAVING.
Definition: Database.php:1864
Wikimedia\Rdbms\DatabaseDomain\getSchema
getSchema()
Definition: DatabaseDomain.php:182
Wikimedia\Rdbms\DatabasePostgres\sequenceExists
sequenceExists( $sequence, $schema=false)
Definition: DatabasePostgres.php:1125
Wikimedia\Rdbms\Database\$server
string $server
Server that this instance is currently connected to.
Definition: Database.php:78
Wikimedia\Rdbms\DatabasePostgres\tableExists
tableExists( $table, $fname=__METHOD__, $schema=false)
For backward compatibility, this function checks both tables and views.
Definition: DatabasePostgres.php:1121
Wikimedia\Rdbms\DatabasePostgres\$keywordTableMap
string[] $keywordTableMap
Map of (reserved table name => alternate table name)
Definition: DatabasePostgres.php:41
Wikimedia\Rdbms\DatabasePostgres\buildGroupConcatField
buildGroupConcatField( $delimiter, $table, $field, $conds='', $options=[], $join_conds=[])
Definition: DatabasePostgres.php:1318
Wikimedia\Rdbms\Database\makeOrderBy
makeOrderBy( $options)
Returns an optional ORDER BY.
Definition: Database.php:1890
Wikimedia\Rdbms\Database\$flags
int $flags
Current bit field of class DBO_* constants.
Definition: Database.php:99
Wikimedia\Rdbms\DatabasePostgres\dataSeek
dataSeek( $res, $row)
Change the position of the cursor in a result object.
Definition: DatabasePostgres.php:335
Wikimedia\Rdbms\DatabasePostgres\ruleExists
ruleExists( $table, $rule)
Definition: DatabasePostgres.php:1155
Wikimedia\Rdbms\Database\select
select( $table, $vars, $conds='', $fname=__METHOD__, $options=[], $join_conds=[])
Execute a SELECT query constructed using the various parameters provided.
Definition: Database.php:1902
Wikimedia\Rdbms\DatabasePostgres\wasKnownStatementRollbackError
wasKnownStatementRollbackError()
Stable to override.
Definition: DatabasePostgres.php:751
Wikimedia\Rdbms\DatabasePostgres\fetchRow
fetchRow( $res)
Fetch the next row from the given result object, in associative array form.
Definition: DatabasePostgres.php:280
Wikimedia\Rdbms\DatabasePostgres\fieldName
fieldName( $res, $n)
Get a field name in a result object.
Definition: DatabasePostgres.php:320
Wikimedia\Rdbms\DatabasePostgres\dumpError
dumpError()
Definition: DatabasePostgres.php:231
Wikimedia\Rdbms\DatabasePostgres\determineCoreSchema
determineCoreSchema( $desiredSchema)
Determine default schema for the current application Adjust this session schema search path if desire...
Definition: DatabasePostgres.php:991
Wikimedia\Rdbms\DatabasePostgres\getSearchPath
getSearchPath()
Return search patch for schemas This is different from getSchemas() since it contain magic keywords (...
Definition: DatabasePostgres.php:949
Wikimedia\Rdbms\DatabasePostgres\getCoreSchema
getCoreSchema()
Return schema name for core application tables.
Definition: DatabasePostgres.php:1029
Wikimedia\Rdbms\DatabaseDomain
Class to handle database/schema/prefix specifications for IDatabase.
Definition: DatabaseDomain.php:40
Wikimedia\Rdbms\DatabasePostgres\indexInfo
indexInfo( $table, $index, $fname=__METHOD__)
Get information about an index into an object.
Definition: DatabasePostgres.php:405
Wikimedia\Rdbms\DatabasePostgres\$tempSchema
string $tempSchema
Definition: DatabasePostgres.php:39
Wikimedia\Rdbms\DatabasePostgres\triggerExists
triggerExists( $table, $trigger)
Definition: DatabasePostgres.php:1129
Wikimedia\Rdbms\DatabasePostgres\currentSequenceValue
currentSequenceValue( $seqName)
Return the current value of a sequence.
Definition: DatabasePostgres.php:700
Wikimedia\Rdbms\DatabasePostgres\setSearchPath
setSearchPath( $search_path)
Update search_path, values should already be sanitized Values may contain magic keywords like "$user"...
Definition: DatabasePostgres.php:969
Wikimedia\Rdbms\DatabasePostgres\wasDeadlock
wasDeadlock()
Determines if the last failure was due to a deadlock.Note that during a deadlock, the prior transacti...
Definition: DatabasePostgres.php:734
Wikimedia\Rdbms\DatabasePostgres\doSelectDomain
doSelectDomain(DatabaseDomain $domain)
Stable to override.
Definition: DatabasePostgres.php:167
Wikimedia\Rdbms\ResultWrapper\unwrap
static & unwrap(&$res)
Get the underlying RDBMS driver-specific result resource.
Definition: ResultWrapper.php:59
Wikimedia\Rdbms\PostgresBlob
@newable
Definition: PostgresBlob.php:8
Wikimedia\Rdbms\DatabasePostgres\lock
lock( $lockName, $method, $timeout=5)
Acquire a named lock.Named locks are not related to transactionsName of lock to aquire Name of the ca...
Definition: DatabasePostgres.php:1389
Wikimedia\Rdbms\DatabasePostgres\estimateRowCount
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 ...
Definition: DatabasePostgres.php:382
Wikimedia\Rdbms\DatabasePostgres\hasConstraint
hasConstraint( $name)
Definition: DatabasePostgres.php:68
Wikimedia\Rdbms\Database\$conn
object resource null $conn
Database connection.
Definition: Database.php:72
Wikimedia\Rdbms\DatabasePostgres\databasesAreIndependent
databasesAreIndependent()
Returns true if DBs are assumed to be on potentially different servers.In systems like mysql/mariadb,...
Definition: DatabasePostgres.php:163
Wikimedia\Rdbms\Database\getDBname
getDBname()
Get the current DB name.
Definition: Database.php:2790
Wikimedia\Rdbms\DatabasePostgres\indexAttributes
indexAttributes( $index, $schema=false)
Definition: DatabasePostgres.php:423
Wikimedia\Rdbms\DatabasePostgres\doInsertNonConflicting
doInsertNonConflicting( $table, array $rows, $fname)
Definition: DatabasePostgres.php:559
Wikimedia\Rdbms\Blob
@newable Stable to extend
Definition: Blob.php:9