From: Ronan Dunklau <ronan.dunklau@aiven.io>
To: pgsql-bugs@lists.postgresql.org
Cc: Miles Delahunty <miles.delahunty@gmail.com>
Subject: Re: table unreadable after altering related table embedded via a view
Date: Mon, 22 Nov 2021 17:14:27 +0100
Message-ID: <7355541.EvYhyI6sBW@aivenronan>
Organization: aiven
In-Reply-To: 
 <CAOFAq3BeawPiw9pc3bVGZ=Rint2txWEBCeDC2wNAhtCZoo2ZqA@mail.gmail.com>
References: 
 <CAOFAq3BeawPiw9pc3bVGZ=Rint2txWEBCeDC2wNAhtCZoo2ZqA@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"
Archived-At: 
 <https://www.postgresql.org/message-id/7355541.EvYhyI6sBW%40aivenronan>
Precedence: bulk

Le lundi 22 novembre 2021, 03:44:37 CET Miles Delahunty a =E9crit :
> $ ./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
>=20
> + psql mytest
> ERROR:  record type has not been registered
>=20

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

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

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

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

create extension pageinspect;
CREATE EXTENSION
=2D-- 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
=2D- Now check the oids of various types.
SELECT typname, oid FROM pg_type WHERE typname in ('record', 'foo');
 typname |  oid  =20
=2D--------+--------
 record  |   2249
 foo     | 154218
(2 rows)

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

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

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

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

=2D-=20
Ronan Dunklau