Sample Header Ad - 728x90

How to drop a table referincing a very trafficated table easily?

1 vote
1 answer
585 views
Let's say I have this two tables
`sql
CREATE TABLE users (
	id_user serial primary key,
	name text NOT NULL
);
`
`sql
CREATE TABLE useless_table (
	id serial primary key,
	id_user int4 NOT NULL,
	CONSTRAINT useless_table_id_user_fk FOREIGN KEY (id_user) REFERENCES users(id_user)
);
` users is the most congested table in all the database, with queries going non-stop, even long and important ones, so I just cannot kill all of them. I want to drop useless_table that, as you can see, has a foreign key referencing users, and every time I launch the DROP command it gets a lock with even the simplest SELECT on users (there aren't any query going on useless_table). I even tried to drop the foreign key on useless_table but it gets the same locks as the drop of the table. Maybe I'm dreamer, but is there a way to don't make it lock with everything and just drop useless_table? I'm thinking about NOT VALID foreign keys, disabling triggers, deferrable constraints or things of this type, but I'm not very savvy about them and don't want to make a mess using them. Any suggestion?
Asked by Sotis (328 rep)
Apr 17, 2023, 01:17 PM
Last activity: Apr 17, 2023, 02:47 PM