Re: table unreadable after altering related table embedded via a view

Le lundi 22 novembre 2021, 03:44:37 CET Miles Delahunty a écrit :
> $ ./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
>

I started to take a look at this, and while I haven't gotten to the bottom of
it yet, here are my observations.

What I notice is that every field of the view type inserted before the change
is correctly typed as a composite, with type = foo_view, and it's content is a
composite, with type =  foo.

But after the change, a newly inserted tuple in foo_log is still correctly
identified as a composite of type foo_view, but it's content is now typed as
anonymous record (datum_typeid=2249, c9080000 on disk).

Here is the slightly changed test case showing the difference in the stored
datum_typeid stored before and after the base table change:

create extension pageinspect;
CREATE EXTENSION
--- foo is the main table
create table foo (data int);
CREATE TABLE
create view foo_view as select foo from foo;
CREATE VIEW
create table foo_log (id serial, what foo_view);
CREATE TABLE
insert into foo values (1);
INSERT 0 1
insert into foo_log (what) select foo_view from foo_view;
INSERT 0 1
-- Now check the oids of various types.
SELECT typname, oid FROM pg_type WHERE typname in ('record', 'foo');
 typname |  oid
---------+--------
 record  |   2249
 foo     | 154218
(2 rows)

-- Check what is the type stored for the composited type of what.foo
-- It is the encoded representation of the oid of foo.
select v from (select (encode(substr(t_data, 31, 4), 'hex')) as v from
heap_page_items(get_raw_page('foo_log', 0))) t;
    v
----------
 6a5a0200
(1 row)

alter table foo add column big_data bigint;
ALTER TABLE
update foo set big_data = data;
UPDATE 1
update foo_log set what.foo.big_data = (what).foo.data;
UPDATE 1
alter table foo drop column data;
ALTER TABLE
alter table foo rename column big_data to data;
ALTER TABLE
insert into foo values (2);
INSERT 0 1
insert into foo_log (what) select foo_view from foo_view where (foo).data = 2;
INSERT 0 1
-- Now perform the same check
-- The first tuple, and it's updated versions both have the correct values for
-- the type of what.foo, but the newly inserted one has 'c9080000' which is
the
-- oid for record.
select v from (select (encode(substr(t_data, 31, 4), 'hex')) as v from
heap_page_items(get_raw_page('foo_log', 0))) t;
    v
----------
 6a5a0200
 6a5a0200
 c9080000
(3 rows)

+ psql -a mytest
\set verbosity verbose
-- This one is ok
select * from foo_log where id = 1;
 id |  what
----+---------
  1 | ("(1)")
(1 row)

-- This one is unreadable
select * from foo_log where id = 2;
ERROR:  record type has not been registered

--
Ronan Dunklau