Removendo colunas com OIDs no PostgreSQL

A partir da versão 12 (já tem um tempinho…) o Postgres deixa de suportar o uso da coluna oculta “OID” ou “Object Identifier” que até então era opcional em tabelas normais, e também se torna uma coluna explícita nas tabelas do catálogo do sistema. Até a versão 11, você poderia criar uma tabela com a opção “WITH OIDS” que criava uma coluna oculta, chamada ‘oid’, que funcionava como uma chave primária (PK) artificial. Desde a versão 8.0 do Postgres (que foi lançada em janeiro de 2005!), o parâmetro “default_with_oids” está desligado por padrão, o que significa que se você não disser nada, por padrão, ao criar uma tabela, a coluna oculta oid não será criada. Então 14 anos depois, o pessoal resolveu aposentar esta opção e finalmente eliminar definitivamente esta coluna de tabelas criadas pelo usuário.

E não é que vira e mexe ainda tem gente que usa? Bom, então como lidar com elas? Existem algumas situações diferentes.

• Se você nem sabe que a coluna está lá na tabela ou não usa ela para nada e já tem sua própria chave primária (PK), então você só precisa remover a coluna OID com um simples comando como:

ALTER TABLE minha_tabela SET WITHOUT OIDS;

• Se você usa esta coluna como uma chave artificial, seria melhor criar uma nova coluna com um nome como “ID” para a sua tabela. Isso exige alguns passos, como:
  • Criar a coluna ID
  • Ajustar a coluna para que novas linhas recebam um novo número automaticamente a partir do último número da coluna OID.
  • Popular a coluna ID com os valores da coluna OID
  • Alterar a tabela para que a coluna ID se torne uma chave primária (PK)
  • Agora sim remover a coluna OID.

SELECT coalesce(max(oid)::integer,0) + 1 FROM minha_tabela;
ALTER TABLE minha_tabela ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 12345);
UPDATE minha_tabela SET id = OID;
ALTER TABLE minha_tabela ADD PRIMARY KEY (id);
ALTER TABLE minha_tabela SET WITHOUT OIDS;

Algumas obervações:

• Note que aqui eu peguei o maior valor da coluna OID na primeira consulta, e devo utilizar no comando seguinte na cláusula “START WITH”, para dizer qual será o próximo número da sequência.
• O nome ID para a nova coluna, é absolutamente arbitrário aqui, você pode utilizar o nome que quiser.
• O tipo de dado para a coluna ID aqui é o INTEGER, mas você pode utilizar o BIGINT se for uma tabela com muitos registros também.
• Eu criei uma sequence com a cláusula GENERATED BY DEFAULT AS IDENTITY. Esse é o padrão ao invés de utilizar o antigo DEFAULT nextval('nome_da_sequence'), desde a versão 10 do Postgres. Tenho um artigo aqui no blog falando especificamente disso, vale a pena conferir, se estiver confuso.
• Não é absolutamente obrigatório ter uma chave primária em toda tabela. Sim, exitem raros casos em que isso é justificável não ter, como numa tabela transitória para fazer uma carga de dados. Vamos tratar isso como uma exceção e vamos tocar o barco pensando apenas na regra geral.

Automatizando tarefas

Bom, tá na hora de fazer esse negócio andar de forma um pouco mais rápida. Pequei esses dias um cliente com centenas de tabelas que ainda utilizam OID. Então fiz este roteiro aqui para facilitar.

Verificando tabelas com OID

SELECT relnamespace::regnamespace AS table_schema, relname AS table_name
FROM pg_class c
WHERE 
	relkind IN ('r', 'm', 'p') AND
	relhasoids = TRUE AND
	relnamespace NOT IN ('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
ORDER BY relnamespace::regnamespace, relname;

Criando a coluna ID em tabelas sem chave primária (PK)

BEGIN;
DO $$DECLARE 
    r	    record;
    v_start integer;
BEGIN
    FOR r IN SELECT relnamespace::regnamespace AS table_schema, relname AS table_name
        FROM pg_class c
	WHERE 
	    relkind IN ('r', 'm', 'p') AND
	    relhasoids = TRUE AND
	    NOT EXISTS (SELECT 1 FROM pg_constraint WHERE contype = 'p' AND conrelid = c.oid) AND
	    NOT EXISTS (SELECT 1 FROM pg_attribute a WHERE c.oid = a.attrelid AND attname = 'id') AND
	    relnamespace NOT IN ('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
	ORDER BY relnamespace::regnamespace, relname
    LOOP
        EXECUTE format('SELECT coalesce(max(oid)::integer,0) + 1 FROM %I.%I',r.table_schema, r.table_name)
	    INTO v_start;
	RAISE WARNING 'Creating identity column for table %.% starting with %', 
            r.table_schema, r.table_name, v_start;
	EXECUTE format('ALTER TABLE %I.%I ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY 
            (START WITH ' || v_start || ')',r.table_schema, r.table_name);
	RAISE WARNING 'UPDATE id';
	EXECUTE format('UPDATE %I.%I SET id = oid',r.table_schema, r.table_name);
	RAISE WARNING 'CREATE PK';
	EXECUTE format('ALTER TABLE %I.%I ADD PRIMARY KEY(id)',r.table_schema, r.table_name);
	RAISE WARNING 'REMOVE oid';
	EXECUTE format('ALTER TABLE %I.%I SET WITHOUT OIDS',r.table_schema, r.table_name);
    END LOOP;
END$$;
COMMIT;

Removendo a coluna OID em tabelas com chave primária (PK)

BEGIN;
SELECT 'ALTER TABLE ' || relnamespace::regnamespace || '.' || relname || ' SET WITHOUT OIDS; --' command
FROM pg_class c
WHERE 
    relkind IN ('r', 'm', 'p') AND
    relhasoids = TRUE AND
    EXISTS (SELECT 1 FROM pg_constraint WHERE contype = 'p' AND conrelid = c.oid) AND
    relnamespace NOT IN ('pg_catalog'::regnamespace::oid,'information_schema'::regnamespace::oid)
ORDER BY relnamespace::regnamespace, relname
\gexec 

E por enquanto é isso… Espero que você não se depare com esse tipo de situação no quotidiano, mas se aparecer, isso deve quebrar um galho!