Início / user-284983

Firat Oezcan's questions

Martin Hope
Firat Oezcan
Asked: 2024-01-12 20:21:10 +0800 CST
  • 7

Atualmente, estou desenvolvendo alguns recursos para um projeto onde os clientes são capazes de alterar o esquema do banco de dados por meio de uma UI e nós cuidamos de todos os detalhes essenciais sobre a execução das instruções necessárias para atualizar o banco de dados (PostgreSQL).

Eu pesquisei um pouco sobre quais poderiam ser algumas pegadinhas e uma das pegadinhas que encontramos são os nomes dos índices, especialmente aqueles que são gerados automaticamente ao criar UNIQUErestrições.

Permitimos que os usuários declarem qual coluna desejam indexar, mas não percebemos que agora criamos dois índices sempre que eles definem indexedcomo verdadeiro em uma UNIQUEcoluna.

Isso já está resolvido, mas levantou a questão sobre o que acontece quando as colunas são renomeadas. No que diz respeito aos nossos testes, renomear colunas não altera os nomes dos índices e, ao criar uma nova coluna com o nome ocupado anteriormente, é criado um índice com um número anexado:

CREATE TABLE users (
        user_id SERIAL PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL
);

ALTER TABLE "users" RENAME COLUMN "email" TO "email_one";
ALTER TABLE "users" ADD COLUMN "email" INTEGER UNIQUE;

/*
postgres@localhost:postgres> \d users
+-----------+------------------------+----------------------------------------------------------+
| Column    | Type                   | Modifiers                                                |
|-----------+------------------------+----------------------------------------------------------|
| user_id   | integer                |  not null default nextval('users_user_id_seq'::regclass) |
| email_one | character varying(255) |  not null                                                |
| email     | integer                |                                                          |
+-----------+------------------------+----------------------------------------------------------+
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email_one)
    "users_email_key1" UNIQUE CONSTRAINT, btree (email)
*/

Como não sou especialista em bancos de dados e nunca mantive bancos de dados grandes, não tenho certeza se isso poderia causar problemas. Perguntei a um amigo meu e ele me deu a seguinte função que eu poderia usar em vez de RENAME TO:

CREATE OR REPLACE FUNCTION rename_column_and_index(
    tbl_name TEXT,
    old_col_name TEXT,
    new_col_name TEXT
) RETURNS void AS $$
DECLARE
    index_record RECORD;
    old_index_suffix TEXT;
    new_index_name TEXT;
BEGIN
    -- Find all indexes that contain the old column name
    FOR index_record IN
        SELECT indexname
        FROM pg_indexes
        WHERE tablename = tbl_name
          AND indexdef LIKE old_col_name || '%'
    LOOP
        -- Start a savepoint
        EXECUTE 'SAVEPOINT before_rename';

        BEGIN
            -- Extract the suffix from the old index name
            old_index_suffix := substring(index_record.indexname from length(quote_ident(old_col_name)) + 1);
            -- Create the new index name
            new_index_name := quote_ident(new_col_name) || old_index_suffix;

            -- Rename the column
            EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) || 
                    ' RENAME COLUMN ' || quote_ident(old_col_name) || 
                    ' TO ' || quote_ident(new_col_name);

            -- Rename the index
            EXECUTE 'ALTER INDEX ' || quote_ident(index_record.indexname) || 
                    ' RENAME TO ' || new_index_name;
        EXCEPTION
            WHEN OTHERS THEN
                -- Rollback to the savepoint in case of error
                EXECUTE 'ROLLBACK TO SAVEPOINT before_rename';
                RAISE;
        END;

        -- Release the savepoint
        EXECUTE 'RELEASE SAVEPOINT before_rename';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- SELECT rename_column_and_btree_index('users', 'email', 'email_one');

Agora minha pergunta é: estou com muito medo dessa questão? Esta função é boa/facilita a manutenção futura? Ou posso simplesmente parar de me preocupar completamente com os índices automáticos?

postgresql