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
Last activity: Apr 17, 2023, 02:47 PM