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
