Безопасно и чисто переименовать таблицы, которые используют столбцы последовательного первичного ключа в Postgres?

Я знаю, что таблицы PostgreSQL, которые используют первичный ключ SERIAL, в конечном итоге имеют неявный индекс, последовательность и ограничение, создаваемые PostgreSQL. Вопрос в том, как переименовать эти неявные объекты при переименовании таблицы. Ниже приведена моя попытка разобраться с конкретными вопросами в конце.

Для таблицы, такой как

CREATE TABLE foo (
    pkey SERIAL PRIMARY KEY,
    value INTEGER
);

Выходы Postgres

NOTICE:  CREATE TABLE will create implicit sequence "foo_pkey_seq" for serial column "foo.pkey"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
Query returned successfully with no result in 52 ms.

PgAdmin III показывает следующее в качестве DDL для таблицы

CREATE TABLE foo
(
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE foo
  OWNER TO postgres;

Теперь переименуем таблицу

ALTER table foo RENAME TO bar;

Выход в Postgres

Query returned successfully with no result in 17 ms.

Панель PgAdmin III SQL для таблицы

CREATE TABLE bar
(
  pkey integer NOT NULL DEFAULT nextval('foo_pkey_seq'::regclass),
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bar
  OWNER TO postgres;

Обратите внимание на дополнительный DEFAULT nextval('foo_pkey_seq'::regclass),, это означает, что переименование таблицы не переименовывает последовательность для первичных ключей, но теперь у нас есть это явное nextval().

Теперь переименуем последовательность

Я хочу, чтобы имя базы данных было согласовано, поэтому я попытался

ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq;
Query returned successfully with no result in 17 ms.

Глядя на панель SQL в pgAdmin III, я вижу

CREATE TABLE bar
(
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE bar
  OWNER TO postgres;

DEFAULT nextval('foo_pkey_seq'::regclass), отсутствует.

ВОПРОСЫ

  • Почему оператор DEFAULT nextval('foo_pkey_seq'::regclass) появился и исчез?
  • Есть ли способ переименовать таблицу и перенесите первичную последовательность клавиш одновременно?
  • Можно ли переименовать таблицу, затем последовательность, когда клиенты подключены к базе данных, есть ли какие-либо проблемы concurrency?
  • Как postgres знают, какую последовательность использовать? Существует ли встроенный триггер базы данных? Есть ли что-нибудь еще, чтобы переименовать, кроме таблицы и последовательности?
  • Как насчет неявного индекса, созданного первичным ключом? Следует ли переименовать? Если да, то как это можно сделать?
  • Как насчет имени ограничения выше? Он по-прежнему foo_pkey. Как переименовано ограничение?

Ответы

Ответ 1

serial не является фактическим типом данных. В руководстве четко указано:

Типы данных smallserial, serial и bigserial не являются истинными типами, а просто удобством для создания уникальных столбцов идентификаторов

Псевдо-тип данных разрешен, выполняя все это:

  • Создайте последовательность с именем tablename_colname_seq

  • Создайте столбец с типом integer (или int2/int8 соответственно для smallserial/bigserial)

  • Сделать столбец NOT NULL DEFAULT nextval('tablename_colname_seq')

  • Сделайте столбец собственной последовательностью, чтобы он автоматически удалялся.

Система не знает, делали ли вы все это вручную или путем псевдо-типа данных serial. pgAdmin проверяет перечисленные функции и, если все они выполнены, обратный инженерный DDL script упрощается с помощью соответствующего типа последовательного интерфейса. Если одна из функций не выполняется, это упрощение не выполняется. Это то, что делает pgAdmin. Для базовых таблиц каталога это все равно. Нет типа serial как такового.

Я довольно уверен, что невозможно автоматически переименовать принадлежащие ему последовательности. Вы можете запустить

ALTER SEQUENCE ... RENAME TO ...

как и вы. Сама система не заботится о названии. В столбце DEFAULT хранится OID ('foo_pkey_seq'::regclass), вы можете изменить имя последовательности без нарушения этого - OID остается тоже самое. То же самое касается внешних ключей и аналогичных ссылок внутри базы данных.

Неявный индекс для первичного ключа привязан к имени ограничения PK, которое не изменится, если вы измените имя таблицы. В Postgres 9.2 или новее вы можете использовать

ALTER TABLE ... RENAME CONSTRAINT ..

чтобы исправить это тоже.

Также могут быть индексы, указанные в названии таблицы. Аналогичная процедура:

ALTER INDEX .. RENAME TO  ..

У вас могут быть все виды неофициальных ссылок на имя таблицы. Система не может принудительно переименовать объекты, которые можно назвать чем угодно. И это все равно.

Конечно, вы не хотите делать недействительным код SQL, который ссылается на эти имена. Очевидно, что вы не хотите изменять имена, пока их ссылается на логику приложений. Обычно это не будет проблемой для имен индексов, последовательностей или ограничений, поскольку они обычно не упоминаются по имени.

Postgres также получает блокировку объектов перед их переименованием. Поэтому, если существует параллельная транзакция, которая имеет какой-либо замок для объектов, о которых идет речь, ваша операция RENAME останавливается до тех пор, пока эти транзакции не совершают или откатываются.

Системные каталоги и OID

Схема базы данных хранится в таблицах системного каталога в системной схеме pg_catalog. Все подробности в руководстве здесь. Если вы не знаете точно, что вы делаете, , вы не должны возиться с этими таблицами вообще. Один ложный ход, и вы можете разбить свою базу данных. Используйте команды DDL, которые предоставляет Postgres.

Для некоторых из наиболее важных таблиц Postgres предоставляет типы идентификаторов объектов и набирает броски, чтобы быстро получить имя для OID и наоборот. Как:

SELECT 'foo_pkey_seq'::regclass

Если имя схемы находится в search_path, а имя таблицы уникально, это дает вам то же самое, что:

SELECT oid FROM pg_class WHERE relname = 'foo_pkey_seq';

Первичный ключ большинства таблиц каталога OID и внутри, большинство ссылок используют OID.