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