PostgreSQL: Reset wszystkich sekwencji

Problem

Czasami udaje się nam doprowadzić swoją niemałą bazę danych do takiego stanu, w którym część sekwencji będzie źle ustawiona, a tym samym nie będzie możliwe dodawanie nowych danych w standardowy sposób, gdyż kolejnym id wg sekwencji, będzie takie, które już w bazie istnieje.

Możemy wtedy ręcznie naprawić daną sekwencję, zgodnie z manualem:

ALTER SEQUENCE serial RESTART WITH 105;

Gdzie serial to nazwa sekwencji. Co jednak, gdy mamy kilkaset popsutych tabelek?

Rozwiązanie — teoria

Na szczęście PostgreSQL udostępnia nam duuużo informacji o każdej swojej bazie danych w schemie information_schema, która to zawiera zestaw zawsze dostępnych widoków. My tutaj wykorzystamy dwa:

• columns — informacje o wszystkich kolumnach i widokach w bazie
• constraint_column_usage — informacje o kolumnach, które są obłożone jakimś constraintem

To co chcemy zrobić to:

1. Znaleźć wszystkie zestawy tabela-kolumna-sekwencja (bazując na tym, że definicja sekwencji jest stała, zawiera wywołanie nextval().
2. Dla każdej kolumny znaleźć największą wartość identyfikatora.
3. Zrestartować każdą sekwencję z wartością znalezioną w poprzednim kroku i zwiększoną o 1.

Rozwiązanie — praktyka

W praktyce wystarczy wykonać poniższy kod w swojej bazie danych. Tworzy on funkcję naprawiającą sekwencje, wykonuje ją raz i a następnie ją kasuje. Należy pamiętać, że działanie jest ograniczone do schemy public. Jeśli chcemy naprawić inną, trzeba sobie kod lekko zmodyfikować (np. dodając nazwę schemy jako paramert).

CREATE OR REPLACE FUNCTION rebuild_sequences() RETURNS INTEGER AS
$body$
DECLARE
	sequencedefs RECORD;
	c INTEGER;
BEGIN
	-- Find sequence name and primary key column for every table
	FOR sequencedefs IN
	SELECT DISTINCT
		constraint_column_usage.TABLE_NAME AS TABLE_NAME,
		constraint_column_usage.column_name AS column_name,
		REPLACE(REPLACE(COLUMNS.column_default,'''::regclass)',''),'nextval(''','') AS sequence_name
		FROM
			information_schema.constraint_column_usage, information_schema.COLUMNS
		WHERE
			constraint_column_usage.table_schema = 'public' AND 
			COLUMNS.table_schema = 'public' AND
			COLUMNS.TABLE_NAME = constraint_column_usage.TABLE_NAME AND
			constraint_column_usage.column_name = COLUMNS.column_name AND
			COLUMNS.column_default IS NOT NULL AND
			COLUMNS.column_default LIKE ('nextval%')
	LOOP
		-- Find current max PK value
		EXECUTE 'SELECT max('||sequencedefs.column_name||') FROM ' || sequencedefs.TABLE_NAME INTO c;
 
		IF c IS NULL THEN
			c = 1;
		ELSE
			c = c + 1;
		END IF;
 
		-- Rebuild sequence
		EXECUTE 'ALTER SEQUENCE ' || sequencedefs.sequence_name ||' RESTART WITH ' || c;
	END LOOP;
 
	RETURN 1;
END;
$body$ LANGUAGE plpgsql;
 
SELECT rebuild_sequences();
 
DROP FUNCTION rebuild_sequences();

Pierwsza wersja funkcji została napisana przez Klausa ze StackOverflow. Wersja powyżej zawiera poprawki błędów, niedociągnięć oraz formatowania.

Podobne wpisy:

1. Logowanie do pliku wszystkich zapytań w PostgreSQL
2. Migracja MySQL → PostgreSQL aplikacji w Symfony 1.1
3. PostgreSQL: obliczanie odstępów czasu

Autorzy zdjęć: Marcin Wichary