Postgre drop bytea[] colum take very long

Question

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:

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|

Laurenz Albe · Accepted Answer · 2020-11-16T09:29:28.100

1

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.

edited Nov 16 '20 at 09:29

answered Nov 16 '20 at 07:29

Laurenz Albe

209,280
17
206
263

Did not work. I closed all open transactions show image on main post. – Kevin V Nov 16 '20 at 08:19
Obviously you didn't. Find out the `pid` of the blocked session running the `ALTER TABLE` and post both `SELECT * FROM pg_stat_activity;` and `SELECT * FROM pg_locks;`, but as formatted text, not as images. – Laurenz Albe Nov 16 '20 at 08:30
That is completely garbled. You can do better. – Laurenz Albe Nov 16 '20 at 08:44
Sorry. Now in a better format. Maybe i found the issue. There is active lock without a pid -> active locks how can i remove the lock? – Kevin V Nov 16 '20 at 09:11
Hero! Thank you!! – Kevin V Nov 16 '20 at 09:38

Postgre drop bytea[] colum take very long

1 Answers1

Update: