Prevent drop column with index on it in PostgreSQL

Question

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?

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
– Akina, Commented Oct 24, 2019 at 6:58

Laurenz Albe · Accepted Answer · 2019-10-24 06:55:19Z

1

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.

answered Oct 24, 2019 at 6:55

Laurenz Albe

62.7k4 gold badges58 silver badges94 bronze badges

Add a comment |

Stack Exchange Network

Prevent drop column with index on it in PostgreSQL

1 Answer 1

Your Answer

Hot Network Questions

Prevent drop column with index on it in PostgreSQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions