Обсуждение: pg_toast.pg_toast_relfilenode not exist due to vacuum full tablename

Поиск
Список
Период
Сортировка

pg_toast.pg_toast_relfilenode not exist due to vacuum full tablename

От
"walker"
Дата:
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

Re: pg_toast.pg_toast_relfilenode not exist due to vacuum full tablename

От
Tom Lane
Дата:
"=?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