arrays - Postgre drop bytea[] 列需要很长时间

Question

我从我的 postgree 数据库中删除一个 bytea[] 列时遇到问题。

首先：该列的大小为 1.4GB，因此我将列设置为 null => Update XX set BYTACOLUMN = null。结果 => 该列具有相同的大小 1.4GB。这有点困惑。如何重置列的大小？

第二：好的，大小是1.4GB。让我们尝试删除列 -> ALTER TABLE XX DROP COLUMN XX。结果：Drop 花费了非常非常长（整个周末）没有结果，并且数据库阻止了所有传入连接。

问题：如何删除 bytea[] 列并重置列的完整大小？

编辑：

编辑 2：

主动锁

|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|

标签： arraysdatabasepostgresqlentity-frameworkbytea

Answer 1

删除一列非常快。必须有一个打开的事务在表上具有锁并阻止您。关闭所有长时间运行的打开事务并重试。长时间运行的事务对您的数据库非常不利。

删除该列或将其更新为 NULL 都不会缩小您的表（更新甚至可能使其增长）。要回收表中未使用的空间，请在其上运行VACUUM (FULL)（但请注意，这会阻止对表的所有访问，直到完成为止）。

更新：

在您的特殊情况下，阻止的事务ALTER TABLE是准备好的事务，这就是它没有pid关联的原因。

查看pg_prepared_xacts，您会发现一些废弃的准备好的交易。gid使用from回滚它们pg_prepared_xacts：

ROLLBACK PREPARED 'transactionname';

如果您不需要准备好的事务，则设置max_prepared_xacts为 0。如果您需要它们，您还需要提供一个事务管理器，在出现问题时清理此类准备好的事务，因为过时的准备好的事务对您的数据库同样有害运行未结交易。