1

Let's say I have this two tables

CREATE TABLE users (
    id_user serial primary key,
    name text NOT NULL
);
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?

1
  • I’d try dropping the constraint and then drop the table.
    – RonJohn
    Commented Apr 17, 2023 at 16:35

1 Answer 1

2

No. The users of "users" have to somehow know that they are no longer required to ensure the referential integrity to "useless_table", and the only safe way for them to know that is by changing the metadata of "users", which requires a lock.

You will either need to declare a maintenance window, or just get lucky enough to catch "users" in a state where it is unlocked. You could run the below in a loop until on some fortunate attempt it succeeds (The nowait will throw an error immediately if the lock is not available). You would probably want to throttle it to not loop as fast possible, as otherwise it will put a lot of contention on some LWLocks/spinlocks, which you probably don't want.

BEGIN;
LOCK TABLE "users" nowait;
DROP TABLE "useless_table";
COMMIT;
2
  • What about dropping the FK constraint, and then dropping the table?
    – RonJohn
    Commented Apr 17, 2023 at 16:34
  • @RonJohn Dropping the constraint still needs the lock on the other table, so I don't see the point in doing it separately.
    – jjanes
    Commented Apr 17, 2023 at 17:23

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.