ALTER TABLE uses a bistate but not for toast tables

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: ALTER TABLE uses a bistate but not for toast tables
Date: 2022-06-22 14:38:41
Message-ID: 20220622143841.GS22452@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

ATRewriteTable() calls table_tuple_insert() with a bistate, to avoid clobbering
and polluting the buffers.

But heap_insert() then calls
heap_prepare_insert() >
heap_toast_insert_or_update >
toast_tuple_externalize >
toast_save_datum >
heap_insert(toastrel, toasttup, mycid, options, NULL /* without bistate:( */);

I came up with this patch. I'm not sure but maybe it should be implemented at
the tableam layer and not inside heap. Maybe the BulkInsertState should have a
2nd strategy buffer for toast tables.

CREATE TABLE t(i int, a text, b text, c text,d text,e text,f text,g text);
INSERT INTO t SELECT 0, array_agg(a),array_agg(a),array_agg(a),array_agg(a),array_agg(a),array_agg(a) FROM generate_series(1,999)n,repeat(n::text,99)a,generate_series(1,99)b GROUP BY b;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;
INSERT INTO t SELECT * FROM t;

ALTER TABLE t ALTER i TYPE smallint;
SELECT COUNT(1), relname, COUNT(1) FILTER(WHERE isdirty) FROM pg_buffercache b JOIN pg_class c ON c.oid=b.relfilenode GROUP BY 2 ORDER BY 1 DESC LIMIT 9;

Without this patch:
postgres=# SELECT COUNT(1), relname, COUNT(1) FILTER(WHERE isdirty) FROM pg_buffercache b JOIN pg_class c ON c.oid=b.relfilenode GROUP BY 2 ORDER BY 1 DESC LIMIT 9;
10283 | pg_toast_55759 | 8967

With this patch:
1418 | pg_toast_16597 | 1418

--
Justin

Attachment Content-Type Size
0001-WIP-use-BulkInsertState-for-toast-tuples-too.patch text/x-diff 10.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2022-06-22 14:38:55 Re: [BUG] Panic due to incorrect missingContrecPtr after promotion
Previous Message Joe Conway 2022-06-22 14:12:26 Re: SYSTEM_USER reserved word implementation