reverted: --- b/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php +++ a/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php @@ -9,7 +9,7 @@ use Drupal\Core\Database\StatementInterface; use Drupal\Core\Database\StatementWrapper; +// cSpell:ignore ilike nextval -// cSpell:ignore ilike nextval relname relkind relname /** * @addtogroup database @@ -368,45 +368,6 @@ } } - /** - * Retrieves a sequence name that is owned by the table and column.. - * - * @param string $table - * A table name that is not prefixed or quoted. - * @param string $column - * The column name. - * - * @return string|null - * The name of the sequence or NULL if it does not exist. - */ - public function getSequence($table, $column) { - $args = [ - ':table' => $this->prefixTables('{' . $table . '}'), - ':column' => $column, - ]; - return $this - ->query("SELECT pg_get_serial_sequence(:table, :column)", $args) - ->fetchField(); - } - - /** - * Checks if a sequence exists. - * - * @param string $name - * The fully-qualified sequence name. - * - * @return bool - * TRUE if the sequence exists by the name. - * - * @see \Drupal\Core\Database\Connection::makeSequenceName() - */ - public function sequenceExists($name) { - $args = [':name' => $name]; - return (bool) $this - ->query("SELECT c.relname FROM pg_class as c WHERE c.relkind = 'S' AND c.relname = :name", $args) - ->fetchField(); - } - } /** reverted: --- b/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php +++ a/core/lib/Drupal/Core/Database/Driver/pgsql/Schema.php @@ -971,7 +971,7 @@ // not when altering. Because of that, the sequence needs to be created // and initialized by hand. $seq = $this->connection->makeSequenceName($table, $field_new); + $this->connection->query("CREATE SEQUENCE " . $seq); - $this->connection->query("CREATE SEQUENCE " . $seq . " OWNED BY {" . $table . "}." . $field_new); // Set sequence to maximal field value to not conflict with existing // entries. $this->connection->query("SELECT setval('" . $seq . "', MAX(\"" . $field . '")) FROM {' . $table . "}"); @@ -1029,25 +1029,6 @@ } } - /** - * Alters the ownership of a sequence. - * - * This is used for updating orphaned sequences. - * See issue https://www.drupal.org/project/drupal/issues/3028706 - * - * @param string $table - * The unquoted or prefixed table name. - * @param string $column - * The column name for the sequence. - * - * @internal - */ - public function updateSequenceOwnership($table, $column) { - $seq = $this->connection->makeSequenceName($table, $column); - $table_name = $this->connection->prefixTables('{' . $table . '}'); - $this->connection->query('ALTER SEQUENCE IF EXISTS ' . $seq . ' OWNED BY ' . $table_name . '.' . $column); - } - /** * Retrieve a table or column comment. */ diff -u b/core/modules/system/system.install b/core/modules/system/system.install --- b/core/modules/system/system.install +++ b/core/modules/system/system.install @@ -1656,7 +1656,7 @@ /** * Fix any orphan sequences created from column changes in PostgreSQL. */ -function system_update_9101(&$sandbox) { +function system_update_9401(&$sandbox) { $connection = \Drupal::database(); if ($connection->databaseType() !== 'pgsql') { // This database update is a no-op for all other core database drivers. @@ -1734,12 +1734,13 @@ // sequence exists at all before trying to alter it. foreach ($to_process as $table_info) { if ($connection->schema()->tableExists($table_info['table'])) { - $owned = (bool) $connection->getSequence($table_info['table'], $table_info['column']); + $owned = (bool) $connection->schema()->getSequenceName($table_info['table'], $table_info['column']); if (!$owned) { $sequence_name = $connection ->makeSequenceName($table_info['table'], $table_info['column']); $exists = $connection + ->schema() ->sequenceExists($sequence_name); if ($exists) { $transaction = $connection->startTransaction($sequence_name); diff -u b/core/modules/system/tests/src/Functional/Database/PostgreSqlSequenceUpdateTest.php b/core/modules/system/tests/src/Functional/Database/PostgreSqlSequenceUpdateTest.php --- b/core/modules/system/tests/src/Functional/Database/PostgreSqlSequenceUpdateTest.php +++ b/core/modules/system/tests/src/Functional/Database/PostgreSqlSequenceUpdateTest.php @@ -3,6 +3,7 @@ namespace Drupal\Tests\system\Functional\Database; use Drupal\FunctionalTests\Update\UpdatePathTestBase; +use Drupal\Core\Database\Database; /** * Tests that any unowned sequences created previously have a table owner. @@ -34,10 +35,13 @@ $this->runUpdates(); if ($db_type === 'pgsql') { - $sequence_exists = (bool) $database - ->query("SELECT pg_get_serial_sequence('{" . $database->prefixTables('pgsql_sequence_test') . "}', 'sequence_field')") - ->fetchField(); - $this->assertTrue($sequence_exists, 'Sequence is owned by the table and column.'); + $connection = Database::getConnection(); + $schema = $connection->schema(); + $seq_owner = $schema->getSequenceOwner(); + if ($seq_owner) { + $this->assertEquals($connection->tablePrefix('users') . 'users', $seq_owner->table_name); + $this->assertEquals('uid', $seq_owner->field_name, 'Sequence is owned by the table and column.'); + } } else { $this->assertTrue(TRUE, 'Database update ran successfully on non-pgsql driver.'); diff -u b/core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php b/core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php --- b/core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php +++ b/core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php @@ -166,10 +166,11 @@ $this->schema->changeField('test_table', 'test_serial', 'test_serial', ['type' => 'serial', 'not null' => TRUE, 'description' => 'Changed column description.'], ['primary key' => ['test_serial']]); // Confirms that the new sequence is owned by the table in PostgreSQL. if ($db_type == 'pgsql') { - $sequence_exists = (bool) $this->connection - ->query("SELECT pg_get_serial_sequence('{test_table}', 'test_serial')") - ->fetchField(); - $this->assertTrue($sequence_exists, 'New sequence is owned by its table.'); + $seq_owner = $this->schema->getSequenceOwner(); + if ($seq_owner) { + $this->assertEquals($this->connection->tablePrefix('users') . 'users', $seq_owner->table_name); + $this->assertEquals('uid', $seq_owner->field_name, 'New sequence is owned by its table.'); + } } // Assert that the column comment has been set. only in patch2: unchanged: --- a/core/modules/pgsql/src/Driver/Database/pgsql/Connection.php +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Connection.php @@ -9,7 +9,7 @@ use Drupal\Core\Database\StatementInterface; use Drupal\Core\Database\StatementWrapper; -// cSpell:ignore ilike nextval +// cSpell:ignore ilike nextval relname relkind relname. /** * @addtogroup database only in patch2: unchanged: --- a/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php +++ b/core/modules/pgsql/src/Driver/Database/pgsql/Schema.php @@ -971,7 +971,7 @@ public function changeField($table, $field, $field_new, $spec, $new_keys = []) { // not when altering. Because of that, the sequence needs to be created // and initialized by hand. $seq = $this->connection->makeSequenceName($table, $field_new); - $this->connection->query("CREATE SEQUENCE " . $seq); + $this->connection->query("CREATE SEQUENCE " . $seq . " OWNED BY {" . $table . "}." . $field_new); // Set sequence to maximal field value to not conflict with existing // entries. $this->connection->query("SELECT setval('" . $seq . "', MAX(\"" . $field . '")) FROM {' . $table . "}"); @@ -1077,6 +1077,82 @@ public function extensionExists($name): bool { ])->fetchField(); } + /** + * Alters the ownership of a sequence. + * + * This is used for updating orphaned sequences. + * See issue https://www.drupal.org/project/drupal/issues/3028706 + * + * @param string $table + * The unquoted or prefixed table name. + * @param string $column + * The column name for the sequence. + * + * @internal + */ + public function updateSequenceOwnership(string $table, string $column) { + $seq = $this->connection->makeSequenceName($table, $column); + $table_name = $this->connection->prefixTables('{' . $table . '}'); + $this->connection->query('ALTER SEQUENCE IF EXISTS ' . $seq . ' OWNED BY ' . $table_name . '.' . $column); + } + + /** + * Retrieves a sequence name that is owned by the table and column.. + * + * @param string $table + * A table name that is not prefixed or quoted. + * @param string $column + * The column name. + * + * @return string|null + * The name of the sequence or NULL if it does not exist. + */ + public function getSequenceName(string $table, string $column): ?string { + $args = [ + ':table' => $this->prefixTables('{' . $table . '}'), + ':column' => $column, + ]; + return $this + ->query("SELECT pg_get_serial_sequence(:table, :column)", $args) + ->fetchField(); + } + + /** + * Checks if a sequence exists. + * + * @param string $name + * The fully-qualified sequence name. + * + * @return bool + * TRUE if the sequence exists by the name. + * + * @see \Drupal\Core\Database\Connection::makeSequenceName() + */ + public function sequenceExists(string $name): bool { + $args = [':name' => $name]; + return (bool) $this + ->query("SELECT c.relname FROM pg_class as c WHERE c.relkind = 'S' AND c.relname = :name", $args) + ->fetchField(); + } + + /** + * Retrives the sequence owner object. + * + * @return array|null + * Returns the sequence owner object or NULL if it does not exist.. + */ + public function getSequenceOwner(): ?array { + $seq_name = $connection->makeSequenceName('users', 'uid'); + $seq_owner = $connection->query("SELECT d.refobjid::regclass as table_name, a.attname as field_name + FROM pg_depend d + JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid + WHERE d.objid = :seq_name::regclass + AND d.refobjsubid > 0 + AND d.classid = 'pg_class'::regclass", [':seq_name' => 'public.' . $seq_name])->fetchObject(); + return $seq_owner; + + } + } /**