Search code examples
arraysdatabasepostgresqlentity-frameworkbytea

Postgre drop bytea[] colum take very long

i have a problem with dropping a bytea[] column from my postgree database.

First: The column had a size of 1.4GB so i set the column to null => Update XX set BYTACOLUMN = null. Result => The column have the same size 1.4GB. This is a litte bit confused. How can i reset the size of the column?

Second: Oke, the size is 1.4GB. Let's try to drop the column -> ALTER TABLE XX DROP COLUMN XX. Result: The Drop take very very very long (full weekend) with no result and the database blocked all incomming connections.

Questions: How can i drop the bytea[] column and reset the full size of the column?

EDIT:

enter image description here

EDIT 2 :

active locks

|schemaname|relname|locktype|page|virtualtransaction|pid|mode|granted|
|----------|-------|--------|----|------------------|---|----|-------|
|GDI|Order|relation||-1/84181397||AccessShareLock|true|

pg_locks

|locktype|database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid|mode|granted|fastpath|
|--------|--------|--------|----|-----|----------|-------------|-------|-----|--------|------------------|---|----|-------|--------|
|relation|730418|1247||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|1249||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|12248||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2685||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2684||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2679||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2678||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|3455||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2663||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2662||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2615||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|2610||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|1259||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|12186||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|12143||||||||9/87381|12284|AccessShareLock|true|true|
|relation|730418|3200026||||||||9/87381|12284|AccessShareLock|true|true|
|virtualxid|||||9/87381|||||9/87381|12284|ExclusiveLock|true|true|
|relation|730418|882100||||||||11/58020|2332|RowExclusiveLock|true|true|
|relation|730418|882099||||||||11/58020|2332|RowExclusiveLock|true|true|
|relation|730418|882098||||||||11/58020|2332|RowExclusiveLock|true|true|
|relation|730418|882003||||||||11/58020|2332|RowExclusiveLock|true|true|
|virtualxid|||||11/58020|||||11/58020|2332|ExclusiveLock|true|true|
|relation|730418|1462743||||||||8/132576|7956|RowExclusiveLock|true|true|
|virtualxid|||||8/132576|||||8/132576|7956|ExclusiveLock|true|true|
|relation|730418|1284520||||||||10/68630|11140|RowExclusiveLock|true|true|
|virtualxid|||||10/68630|||||10/68630|11140|ExclusiveLock|true|true|
|relation|730418|2656||||||||9/87381|12284|AccessShareLock|true|false|
|relation|75098|151106||||||||-1/91524731||AccessShareLock|true|false|
|relation|730418|882111||||||||-1/84181397||AccessShareLock|true|false|
|relation|730418|2703||||||||9/87381|12284|AccessShareLock|true|false|
|relation|75098|151112||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|150950||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|151103||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|151107||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|151111||||||||-1/91524731||AccessShareLock|true|false|
|relation|0|2676||||||||9/87381|12284|AccessShareLock|true|false|
|relation|730418|881809||||||||-1/84181397||AccessShareLock|true|false|
|relation|75098|151102||||||||-1/91524731||AccessShareLock|true|false|
|relation|730418|2604||||||||9/87381|12284|AccessShareLock|true|false|
|relation|730418|881815||||||||-1/84181397||AccessShareLock|true|false|
|relation|0|1262||||||||9/87381|12284|AccessShareLock|true|false|
|relation|730418|2658||||||||9/87381|12284|AccessShareLock|true|false|
|relation|0|2677||||||||9/87381|12284|AccessShareLock|true|false|
|relation|75098|151108||||||||-1/91524731||AccessShareLock|true|false|
|relation|730418|2704||||||||9/87381|12284|AccessShareLock|true|false|
|relation|75098|150943||||||||-1/91524731||AccessShareLock|true|false|
|transactionid||||||84181397||||-1/84181397||ExclusiveLock|true|false|
|relation|0|2672||||||||9/87381|12284|AccessShareLock|true|false|
|transactionid||||||91524731||||-1/91524731||ExclusiveLock|true|false|
|relation|730418|2659||||||||9/87381|12284|AccessShareLock|true|false|
|relation|75098|151109||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|151104||||||||-1/91524731||AccessShareLock|true|false|
|relation|75098|151110||||||||-1/91524731||AccessShareLock|true|false|
|relation|730418|2299546||||||||-1/84181397||AccessShareLock|true|false|
|relation|730418|1284518||||||||10/68630|11140|ShareUpdateExclusiveLock|true|false|
|relation|0|2671||||||||9/87381|12284|AccessShareLock|true|false|
|relation|730418|2657||||||||9/87381|12284|AccessShareLock|true|false|
|relation|730418|1462741||||||||8/132576|7956|ShareUpdateExclusiveLock|true|false|
|relation|730418|957042||||||||-1/84181397||AccessShareLock|true|false|
|relation|730418|881997||||||||11/58020|2332|ShareUpdateExclusiveLock|true|false|
|relation|75098|151105||||||||-1/91524731||AccessShareLock|true|false|
|relation|730418|957034||||||||-1/84181397||AccessShareLock|true|false|
|relation|0|1260||||||||9/87381|12284|AccessShareLock|true|false|
|page|730418|881815|7|||||||-1/84181397||SIReadLock|true|false|

pg_stat_activity

|datid|datname|pid|usesysid|usename|application_name|client_addr|client_hostname|client_port|backend_start|xact_start|query_start|state_change|wait_event_type|wait_event|state|backend_xid|backend_xmin|query|backend_type|
|-----|-------|---|--------|-------|----------------|-----------|---------------|-----------|-------------|----------|-----------|------------|---------------|----------|-----|-----------|------------|-----|------------|
|||6320|10|postgres|||||2020-11-15 19:23:08||||Activity|LogicalLauncherMain|||||logical replication launcher|
|||5808|||||||2020-11-15 19:23:08||||Activity|AutoVacuumMain|||||autovacuum launcher|
|730418|AlfaGateWay|1868|10|postgres|DBeaver 7.1.3 - Main <AlfaGateWay>|127.0.0.1||49568|2020-11-16 09:58:24||2020-11-16 09:58:24|2020-11-16 09:58:24|Client|ClientRead|idle|||SET application_name = 'DBeaver 7.1.3 - Main <AlfaGateWay>'|client backend|
|730418|AlfaGateWay|7136|10|postgres|DBeaver 7.1.3 - Metadata <AlfaGateWay>|127.0.0.1||49570|2020-11-16 09:58:24||2020-11-16 09:58:24|2020-11-16 09:58:24|Client|ClientRead|idle|||SET application_name = 'DBeaver 7.1.3 - Metadata <AlfaGateWay>'|client backend|
|730418|AlfaGateWay|9572|10|postgres|DBeaver 7.1.3 - SQLEditor <Script-4.sql>|127.0.0.1||49572|2020-11-16 09:58:24||2020-11-16 09:58:24|2020-11-16 09:58:24|Client|ClientRead|idle|||SET application_name = 'DBeaver 7.1.3 - SQLEditor <Script-4.sql>'|client backend|
|730418|AlfaGateWay|9416|10|postgres|DBeaver 7.1.3 - SQLEditor <Script-4.sql>|127.0.0.1||49574|2020-11-16 09:58:24||2020-11-16 09:59:00|2020-11-16 09:59:00|Client|ClientRead|idle|||SHOW search_path|client backend|
|730418|AlfaGateWay|11360|10|postgres|DBeaver 7.1.3 - SQLEditor <Script-3.sql>|127.0.0.1||49576|2020-11-16 09:58:24||2020-11-16 09:58:24|2020-11-16 09:58:24|Client|ClientRead|idle|||SET application_name = 'DBeaver 7.1.3 - SQLEditor <Script-3.sql>'|client backend|
|730418|AlfaGateWay|7956|||||||2020-11-16 09:55:04|2020-11-16 09:58:27|2020-11-16 09:58:27|2020-11-16 09:58:27|||active||84181397|autovacuum: VACUUM pg_toast.pg_toast_881742|autovacuum worker|
|730418|AlfaGateWay|12284|10|postgres|DBeaver 7.1.3 - SQLEditor <Script.sql>|127.0.0.1||49578|2020-11-16 09:58:24|2020-11-16 10:02:24|2020-11-16 10:03:42|2020-11-16 10:03:42|||active||84181397|SELECT * FROM pg_stat_activity|client backend|
|730418|AlfaGateWay|11140|||||||2020-11-16 10:00:09|2020-11-16 10:03:35|2020-11-16 10:03:35|2020-11-16 10:03:35|||active||84181397|autovacuum: VACUUM pg_toast.pg_toast_881809|autovacuum worker|
|730418|AlfaGateWay|4588|||||||2020-11-16 10:01:45|2020-11-16 10:03:40|2020-11-16 10:03:40|2020-11-16 10:03:40|||active||84181397|autovacuum: VACUUM pg_catalog.pg_class|autovacuum worker|
|||5176|||||||2020-11-15 19:23:08||||Activity|BgWriterMain|||||background writer|
|||1624|||||||2020-11-15 19:23:08||||Activity|CheckpointerMain|||||checkpointer|
|||6524|||||||2020-11-15 19:23:08||||Activity|WalWriterMain|||||walwriter|
Solution

  • Dropping a column is very fast. There must be an open transaction that has a lock on the table and blocks you. Close all long running open transactions and retry. Long running transactions are very bad for your database.

    Neither dropping the column nor updating it to NULL will shrink your table (the update might even make it grow). To reclaim unused space in the table, run VACUUM (FULL) on it (but be aware that that blocks all access to the table until it is done).

    Update:

    In your special case, the transaction that blocks the ALTER TABLE is a prepared transaction, that is why it does not have a pid associated.

    Look into pg_prepared_xacts, and you will find some abandoned prepared transactions. Roll them back using the gid from pg_prepared_xacts:

    ROLLBACK PREPARED 'transactionname';

    If you don't need prepared transactions, set max_prepared_xacts to 0. If you need them , you also need to provide a transaction manager that cleans up such prepared transactions if a problem occurs, since stale prepared transactions are just as bad for your database as long running open transactions.