![]() ![]() ![]() Concurrent access on that table will be halted during this, obviously, so best at low activity times or during a maintenance window. 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. 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, just execute the same command again: only tables that still exist are dropped. 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. separately will hardly help, as that runs into the same problem. In particular, that highly contested target table of your FK constraints is only blocked for a brief moment for each DROP command.ĭropping the FK constraint with ALTER TABLE. Since that commits after every DROP, you don't collect locks along the way. Un-comment the EXECUTE line (and optionally comment the RAISE) to prime the bomb. I put in RAISE NOTICE and commented the actual DROP as child safety device. EXECUTE _sql - un-comment once you are sure _sql := format('DROP TABLE %I.%I CASCADE', _schema, _tbl) - CASCADE needed? WHERE schemaname = 'public' - or what you needĪND tablename = ANY('') - array of tables to delete To avoid locking the referenced table longer than absolutely necessary, COMMIT after every DROP. 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. (I verified in a quick test on Postgres 14.) Indeed an ACCESS EXCLUSIVE lock is taken on both tables, referenced and referencing. AnĪCCESS EXCLUSIVE lock is acquired unless explicitly noted. The manual:ĪLTER TABLE changes the definition of an existing table. Unfortunately, dropping an FK constraint also requires a brief ACCESS EXCLUSIVE lock on the referenced table. If FK constraints point from a table to be deleted (your case, as clarified in an update) then it dies with the table. ( 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 to a table to be deleted, add CASCADE to also drop any such FK constraint (not the referencing tables). For example, a value of -1 implies that all values in the column are distinct, while a value of -0.These tables have foreign keys on a highly referenced table and lock is required by drop When set to a negative value, which must be greater than or equal to -1, ANALYZE will assume that the number of distinct nonnull values in the column is linear in the size of the table the exact count is to be computed by multiplying the estimated table size by the absolute value of the given number. When set to a positive value, ANALYZE will assume that the column contains exactly the specified number of distinct nonnull values. n_distinct affects the statistics for the table itself, while n_distinct_inherited affects the statistics gathered for the table plus its inheritance children. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. This form sets or resets per-attribute options. SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock. For more information on the use of statistics by the PostgreSQL query planner, refer to Section 14.2. The target can be set in the range 0 to 10000 alternatively, set it to -1 to revert to using the system default statistics target ( default_statistics_target). This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. sequence_option is an option supported by ALTER SEQUENCE such as INCREMENT BY. These forms alter the sequence that underlies an existing identity column. If DROP IDENTITY IF EXISTS is specified and the column is not an identity column, no error is thrown. Like SET DEFAULT, these forms only affect the behavior of subsequent INSERT and UPDATE commands they do not cause rows already in the table to change. These forms change whether a column is an identity column or change the generation attribute of an existing identity column. RENAME CONSTRAINT constraint_name TO new_constraint_nameĪLTER TABLE ALL IN TABLESPACE name ]ĪTTACH PARTITION partition_name AS IDENTITY ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |