table unreadable after altering related table embedded via a view

Поиск
Список
Период
Сортировка
От Miles Delahunty
Тема table unreadable after altering related table embedded via a view
Дата
Msg-id CAOFAq3BeawPiw9pc3bVGZ=Rint2txWEBCeDC2wNAhtCZoo2ZqA@mail.gmail.com
обсуждение исходный текст
Ответы Re: table unreadable after altering related table embedded via a view  (Ronan Dunklau <ronan.dunklau@aiven.io>)
Список 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

---

$ cat record_type_not_registered.sh
#!/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;

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
Следующее
От: Jobin Augustine
Дата:
Сообщение: Re: [BUG] Autovacuum not dynamically decreasing cost_limit and cost_delay