TOAST questions

From: Maciek Sakrejda <m(dot)sakrejda(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: TOAST questions
Date: 2021-07-07 18:52:55
Message-ID: CAOtHd0DPXe38p3-Vi27xj+fS4xmcBFZJsztNFccJWjQ8M7e8=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

(I hope it's okay to ask general internals questions here; if this list is
strictly for development, I'll keep my questions on -general but since I'm
asking about internal behavior, this seemed more appropriate.)

I was playing around with inspecting TOAST tables in order to understand
the mechanism better, and I ran across a strange issue: I've created a
table that has a text column, inserted and then deleted some data, and the
TOAST table still has some entries even though the owning table is now
empty:

maciek=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname =
'users';
reltoastrelid
---------------------------
pg_toast.pg_toast_4398034
(1 row)

maciek=# select chunk_id, chunk_seq from pg_toast.pg_toast_4398034;
chunk_id | chunk_seq
----------+-----------
4721665 | 0
4721665 | 1
(2 rows)

maciek=# select * from users;
id | created_at | is_admin | username
----+------------+----------+----------
(0 rows)

I've tried to reproduce this with a new table in the same database, but
could not see the same behavior (the TOAST table entries are deleted when I
delete rows from the main table). This is 11.12. Is this expected?

In case it's relevant, this table originally had the first three columns. I
inserted one row, then added the text column, set its STORAGE to EXTERNAL,
and set toast_tuple_target to the minimum of 128. I inserted a few more
rows until I found one large enough to go in the TOAST table (It looks like
Postgres attempts to compress values and store them inline first even when
STORAGE is EXTERNAL? I don't recall the exact size, but I had to use a
value much larger than 128 before it hit the TOAST table. The TOAST docs
allude to this behavior but just making sure I understand correctly.), then
I deleted the rows with non-NULL values in the text column, and noticed the
TOAST table entries were still there. So I deleted everything in the users
table and still see the two rows above in the TOAST table. I've tried this
sequence of steps again with a new table and could not reproduce the issue.

Thanks,
Maciek

Browse pgsql-hackers by date

  From Date Subject
Next Message Dagfinn Ilmari Mannsåker 2021-07-07 19:12:20 Replace remaining castNode(…, lfirst(…)) and friends calls with l*_node()
Previous Message Dean Rasheed 2021-07-07 18:42:43 Re: Numeric x^y for negative x