0

I was redesigning legacy database and have merged lots of tables into just four and now I'm looking for a way to drop a several thousand obsolete tables as quickly as it possible.

As far as I know we do not have anything similar to DATABASE LOCK to avoid heavy locking on each DROP.

Single-user mode is not easy in my case because database is running in a managed environment inside a cloud(but if this the most reliable way I might try to ask cloud support).

Third option, I potentially see, is copying everything except obsolete tables, but merged tables are really huge now and dump & restore will probably take ages as well.

Database is under permanent load, and DROP most of the time is aborted due to timeout because of unsuccessful lock.

These tables have foreign keys on a highly referenced table and lock is required by DROP.

Dropping the foreign keys would also needs a lock on each ALTER TABLE ... DROP CONSTRAINT.

0

2 Answers 2

3

these tables have foreign keys on a highly referenced table and lock is required by drop

If FK constraints point to a table to be deleted, add CASCADE to also drop any such FK constraint (not the referencing tables). The manual:

(CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

If FK constraints point from a table to be deleted (your case, as clarified in an update) then it dies with the table. Unfortunately, dropping an FK constraint also requires a brief ACCESS EXCLUSIVE lock on the referenced table. The manual:

ALTER TABLE changes the definition of an existing table. [...] An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.

This leaves room for interpretation. Indeed an ACCESS EXCLUSIVE lock is taken on both tables, referenced and referencing. (I verified in a quick test on Postgres 14.)

The lock on the referenced table is very brief and shouldn't be a problem unless there is heavy concurrent access on that table - especially if there are long-running transactions.

To avoid locking the referenced table longer than absolutely necessary, COMMIT after every DROP. Like (requires Postgres 11 or later):

DO
$do$
DECLARE
   _schema name;
   _tbl name;
   _sql text;
BEGIN
   FOR _schema, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_catalog.pg_tables
      WHERE  schemaname = 'public'        -- or what you need
      AND    tablename = ANY('{t2, t3}')  -- array of tables to delete
   LOOP
      _sql := format('DROP TABLE %I.%I CASCADE', _schema, _tbl);  -- CASCADE needed?
      RAISE NOTICE '%', _sql;
      -- EXECUTE _sql;        -- un-comment once you are sure
      COMMIT;                 -- !!!
   END LOOP;
END;
$do$

I put in RAISE NOTICE and commented the actual DROP as child safety device. Un-comment the EXECUTE line (and optionally comment the RAISE) to prime the bomb.

Since that commits after every DROP, you don't collect locks along the way. In particular, that highly contested target table of your FK constraints is only blocked for a brief moment for each DROP command.

Dropping the FK constraint with ALTER TABLE ... DROP CONSTRAINT ... separately will hardly help, as that runs into the same problem. It can be an option, though, for multiple FK constraints from the same table (in separate transactions like above) - so you only have to wait for a single target table at a time.

If you still get stuck, just execute the same command again: only tables that still exist are dropped.

You could add IF EXISTS to the DROP, but that only makes sense if you expect concurrent transactions to DROP tables, which doesn't seem to apply.

If you still get stuck, take an exclusive lock on the target table in question and DROP all tables in the same transaction at once. Concurrent access on that table will be halted during this, obviously, so best at low activity times or during a maintenance window. (OTOH, if there is no concurrent access, you can simply DROP without contention anyway.)

Maybe you can identify and fix long-running transactions that don't need to stay open for that long? Those are a general burden for DBs with concurrent access in any case.

3
  • Thank you @Erwin, I do have nearly the same procedure as you gave me as an example and the problem is on the DROP because these calls are aborted most of the time due to timeout(referenced table is using a lot in production mode). I'm able to "stop" everything for a short period of the night time(as it technically possibly) and run such procedures what I actually have tried already but visual interval between "RASING NOTICE" is a second or even a bit more. I assume that bottleneck is locking of the same referenced tables in each iteration of the loop. Jan 4 at 9:56
  • @danilabagroff: I elaborated a bit and added possible solutions. Jan 4 at 23:38
  • I ended up with pg_dump where unwanted tables can be excluded with --exclude-table argument and finally the whole process pg_dump | psql took just 20 mins of downtime. Jan 20 at 6:28
-2

I'm only writing this as an answer due to the severity of your actions that I reprieve.

I was redesigning legacy database and have merged lots of tables into just four

This is a major Red Flag to me. It screams "EAV".

Even if it is not, do the following:

  1. Make sure you benchmark your new design with the application
  2. Make sure the application is using only the new tables
  3. Make sure a noob can find information easy while you're on vacation
  4. Make sure you have validated backups before you run your first DROP command.

That last step is essential for ensuring that the 3rd step is not permanent.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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