table unreadable after altering related table embedded via a view - Mailing list pgsql-bugs
From | Miles Delahunty |
---|---|
Subject | table unreadable after altering related table embedded via a view |
Date | |
Msg-id | CAOFAq3BeawPiw9pc3bVGZ=Rint2txWEBCeDC2wNAhtCZoo2ZqA@mail.gmail.com Whole thread Raw |
Responses |
Re: table unreadable after altering related table embedded via a view
|
List | pgsql-bugs |
Hi there,
After altering a table definition with the aim of promoting an int column to bigint, I found that a related table (that references the original table's definition by way of a view) became unreadable. Selecting from the table errors out with "record type has not been registered", pg_dump also fails to dump out the data with the same error. Data can still be inserted but not read back. I have included a minimal example below.
Interestingly, if I change the view in the example into a regular table the error does not appear, so the problem seems specific in some way to usage of a view's record type as a column. Also, I can select from the table fine in the session that altered the table, it's only subsequent sessions that start seeing the error.
Postgres 14.1 on Ubuntu 20.04 (though I got the same result with 13.2 on CentOS 7)
Cheers,
Miles
---
#!/bin/bash
set -x
createdb mytest
psql mytest <<EOF
--- foo is the main table
create table foo (data int);
--- foo_view embellishes the main table with some other data (omitted here for clarity)
create view foo_view as select foo from foo;
--- foo_log records changes to the view
create table foo_log (ts timestamp, what foo_view);
--- change data from int to bigint
alter table foo add column big_data bigint;
update foo set big_data = data;
update foo_log set what.foo.big_data = (what).foo.data;
alter table foo drop column data;
alter table foo rename column big_data to data;
--- insert a row into foo and foo_log
insert into foo values (123456);
insert into foo_log select current_timestamp, foo_view from foo_view;
--- we can still select from foo_log in this session
select * from foo_log;
EOF
psql mytest <<EOF
\set verbosity verbose
--- but this one errors out
select * from foo_log;
EOF
--- Output ---
$ ./record_type_not_registered.sh
+ createdb mytest+ psql mytest
CREATE TABLE
CREATE VIEW
CREATE TABLE
ALTER TABLE
UPDATE 0
UPDATE 0
ALTER TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
ts | what
----------------------------+--------------
2021-11-22 13:29:46.775704 | ("(123456)")
(1 row)
+ psql mytest
ERROR: record type has not been registered
--- Server log ---
2021-11-22 13:29:44.217 AEDT [2291] LOG: 00000: starting PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc-9 (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-11-22 13:29:44.217 AEDT [2291] LOCATION: PostmasterMain, postmaster.c:1128
2021-11-22 13:29:44.217 AEDT [2291] LOG: 00000: listening on IPv4 address "127.0.0.1", port 5432
2021-11-22 13:29:44.217 AEDT [2291] LOCATION: StreamServerPort, pqcomm.c:582
2021-11-22 13:29:44.223 AEDT [2291] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-11-22 13:29:44.223 AEDT [2291] LOCATION: StreamServerPort, pqcomm.c:577
2021-11-22 13:29:44.230 AEDT [2292] LOG: 00000: database system was shut down at 2021-11-22 13:29:40 AEDT
2021-11-22 13:29:44.230 AEDT [2292] LOCATION: StartupXLOG, xlog.c:6536
2021-11-22 13:29:44.235 AEDT [2291] LOG: 00000: database system is ready to accept connections
2021-11-22 13:29:44.235 AEDT [2291] LOCATION: reaper, postmaster.c:3066
2021-11-22 13:29:46.781 AEDT [2356] ERROR: 42809: record type has not been registered
2021-11-22 13:29:46.781 AEDT [2356] LOCATION: lookup_rowtype_tupdesc_internal, typcache.c:1809
2021-11-22 13:29:46.781 AEDT [2356] STATEMENT: select * from foo_log;
2021-11-22 13:29:44.217 AEDT [2291] LOCATION: PostmasterMain, postmaster.c:1128
2021-11-22 13:29:44.217 AEDT [2291] LOG: 00000: listening on IPv4 address "127.0.0.1", port 5432
2021-11-22 13:29:44.217 AEDT [2291] LOCATION: StreamServerPort, pqcomm.c:582
2021-11-22 13:29:44.223 AEDT [2291] LOG: 00000: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-11-22 13:29:44.223 AEDT [2291] LOCATION: StreamServerPort, pqcomm.c:577
2021-11-22 13:29:44.230 AEDT [2292] LOG: 00000: database system was shut down at 2021-11-22 13:29:40 AEDT
2021-11-22 13:29:44.230 AEDT [2292] LOCATION: StartupXLOG, xlog.c:6536
2021-11-22 13:29:44.235 AEDT [2291] LOG: 00000: database system is ready to accept connections
2021-11-22 13:29:44.235 AEDT [2291] LOCATION: reaper, postmaster.c:3066
2021-11-22 13:29:46.781 AEDT [2356] ERROR: 42809: record type has not been registered
2021-11-22 13:29:46.781 AEDT [2356] LOCATION: lookup_rowtype_tupdesc_internal, typcache.c:1809
2021-11-22 13:29:46.781 AEDT [2356] STATEMENT: select * from foo_log;
pgsql-bugs by date: