Thread: pg_class.reltype -> pg_type.oid missing for pg_toast table
I have a memory of the catalog not being MVCC,
so maybe this is normal and expected,
but I wanted to report it in case it's not.
When copying all tables in pg_catalog, to a separate schema with the purpose
of testing if foreign keys could be added for all oid columns, I got an error for a toast table:
ERROR: insert or update on table "pg_class" violates foreign key constraint "pg_class_reltype_fkey"
DETAIL: Key (reltype)=(86987582) is not present in table "pg_type".
CONTEXT: SQL statement "
ALTER TABLE catalog_fks.pg_class ADD FOREIGN KEY (reltype) REFERENCES catalog_fks.pg_type (oid)
"
The copies of pg_catalog were executed in one and the same transaction,
but as separate queries in a PL/pgSQL function using EXECUTE.
/Joel
"Joel Jacobson" <joel@compiler.org> writes:
> When copying all tables in pg_catalog, to a separate schema with the purpose
> of testing if foreign keys could be added for all oid columns, I got an error for a toast table:
> ERROR: insert or update on table "pg_class" violates foreign key constraint "pg_class_reltype_fkey"
> DETAIL: Key (reltype)=(86987582) is not present in table "pg_type".
I'm too lazy to check the code right now, but my recollection is that we
do not bother to make composite-type entries for toast tables. However,
they should have reltype = 0 if so, so I'm not quite sure where the
above failure is coming from.
regards, tom lane
On Tue, Jan 19, 2021, at 17:43, Tom Lane wrote:
>I'm too lazy to check the code right now, but my recollection is that we
>do not bother to make composite-type entries for toast tables. However,
>they should have reltype = 0 if so, so I'm not quite sure where the
>above failure is coming from.
My apologies, false alarm.
The problem turned out to be due to doing
CREATE TABLE catalog_fks.%1$I AS
SELECT * FROM pg_catalog.%1$I
which causes changes to e.g. pg_catalog.pg_class during the command is running.
Solved by instead using COPY ... TO to first copy catalogs to files on disk,
which doesn't cause changes to the catalogs,
and then using COPY .. FROM to copy the data into the replicated table structures.
/Joel