4

In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in production.

Here is a demonstration:

CREATE TABLE test (
    id INT     NOT NULL PRIMARY KEY,
    a  VARCHAR NOT NULL,
    b  VARCHAR NOT NULL
);
CREATE INDEX test_index ON test(a, b);

SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
test      | test_index
*/ 

ALTER TABLE test DROP COLUMN b;
SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
*/

Is it possible to make PostgreSQL return error when you drop a column with an index on? Or maybe some other ways to prevent described situation?

1
  • CREATE EVENT TRIGGER allows to check index drop. Check if such trigger type is fired by chain DDL operations (drop index chained from drop column).
    – Akina
    Oct 24, 2019 at 6:58

1 Answer 1

0

There is no way to do that except writing an event trigger that throws an exception whenever certain users try to run schema modifications.

You should make sure that any database user that connects and doesn't know better than randomly dropping columns without understanding the consequences does not own any database object and doesn't have the CREATE privilege on any schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.