Thread: pg_toast.pg_toast_relfilenode not exist due to vacuum full tablename
hi,
this morning i met an issue, that after vacuum full tablename, the associated toast table shows not exist.
here is the operation steps:
drop table if exists reymont;
create table reymont ( id bigint primary key, data bytea not null);
alter table reymont alter column data set compression pglz;
insert into reymont values(1, pg_read_binary_file('filename'));
vacuum full reymont;
select relname, relfilenode, reltoastrelid from pg_class where relname='reymont';
\d+ pg_toast.pg_toast_relfilenode
Did not find any relation named "pg_toast.pg_toast_relfilenode".
however, if display toast table before vacuum full operation, no problem.
drop table if exists reymont;
create table reymont ( id bigint primary key, data bytea not null);
alter table reymont alter column data set compression pglz;
insert into reymont values(1, pg_read_binary_file('filename'));
\d+ pg_toast.pg_toast_relfilenode --- it's ok, the toast table exists
vacuum full reymont;
\d+ pg_toast.pg_toast_relfilenode --- it's ok, the toast table exists
it looks a little strange, any ideas? appreciate your help.
env:
pg14.4
linux 3.10.0-693.17.1.e17
thanks
walker
"=?ISO-8859-1?B?d2Fsa2Vy?=" <failaway@qq.com> writes:
> this morning i met an issue, that after vacuum full tablename, the associated toast table shows not exist.
Your example doesn't show what you actually did, but I think what is
fooling you is that VACUUM FULL changes the relfilenode of the table
but not the name of its toast table. So the situation afterwards
might look like
regression=# select relname, relfilenode, reltoastrelid from pg_class where relname='reymont';
relname | relfilenode | reltoastrelid
---------+-------------+---------------
reymont | 40616 | 40611
(1 row)
regression=# select relname from pg_class where oid = 40611;
relname
----------------
pg_toast_40608
(1 row)
regression=# \d+ pg_toast.pg_toast_40608
TOAST table "pg_toast.pg_toast_40608"
Column | Type | Storage
------------+---------+---------
chunk_id | oid | plain
chunk_seq | integer | plain
chunk_data | bytea | plain
Owning table: "public.reymont"
Indexes:
"pg_toast_40608_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Access method: heap
(where 40608 is reymont's original relfilenode).
I'm not sure if this should be considered a bug or not. Everything still
works well enough, but conceivably we could have a TOAST name collision
down the road when we recycle the 40608 number --- I don't recall if
the TOAST logic is able to cope with that or not.
In any case, you should not be making assumptions about the name of
a TOAST table without verifying it.
regards, tom lane