Re: [Pg-migrator-general] Composite types break pg_migrated tables - Mailing list pgsql-hackers

I received the following pg_migrator bug report today and was able to
reproduce the reported failure when using composite types:
test=> SELECT * FROM breakmigrator;ERROR:  cache lookup failed for type 27604
test=> ANALYZE VERBOSE public.breakmigrator;INFO:  analyzing "public.breakmigrator"INFO:  "breakmigrator": scanned 1 of
1pages, containing 3 live rows and0 dead rows; 3 rows in sample, 3 estimated total rowsERROR:  cache lookup failed for
type27604
 

There is no pg_type row with oid 27604.

Can anyone suggest the cause?  Do we embed the object oid in the
composite object?  Did we change the composite object storage layout
between 8.3 and 8.4?  I am surprised the regression tests didn't show
this error.  (I just tried ANALYZE on the regression database and it
succeeded.)

---------------------------------------------------------------------------

Jeff wrote:
> I'm running some tests of pg_migrator and at first glance it appeared  
> things were fine, but alas, that was not the truth.
> 
> In a nutshell: if you have a table with a composite type as a column  
> the migrated table is unusable (cache lookup errors)
> I'm testing with 8.3.7 and 8.4.0 on osx (it also happens on linux -  
> where I first observed it)
> 
> Here's how to reproduce:
> 
> Fire up an 8.3 instance and install the following sql:
> 
> create type footype as
> (
>     x double precision,
>     y double precision,
>     z double precision
> );
> 
> create table breakmigrator
> (
>     id int,
>     foo_a footype
> );
> 
> insert into breakmigrator (id, foo_a)
>     values (1, (1,2,3));
> insert into breakmigrator (id, foo_a)
>         values (2, (1,2,3));
> insert into breakmigrator (id, foo_a)
>         values (3, (1,2,3));
> 
> 
> then run pg_migrator to upgrade it to 8.4
> ... "*Upgrade complete*...
> 
> fire up 8.4 and then try to vacuum the breakmigrator table:
> 
> jeff=# vacuum analyze verbose breakmigrator;
> INFO:  vacuuming "public.breakmigrator"
> INFO:  "breakmigrator": found 0 removable, 3 nonremovable row versions  
> in 1 out of 1 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.01 sec.
> INFO:  vacuuming "pg_toast.pg_toast_16406"
> INFO:  index "pg_toast_16406_index" now contains 0 row versions in 1  
> pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "pg_toast_16406": found 0 removable, 0 nonremovable row  
> versions in 0 out of 0 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  analyzing "public.breakmigrator"
> INFO:  "breakmigrator": scanned 1 of 1 pages, containing 3 live rows  
> and 0 dead rows; 3 rows in sample, 3 estimated total rows
> ERROR:  cache lookup failed for type 16387
> STATEMENT:  vacuum analyze verbose breakmigrator;
> ERROR:  cache lookup failed for type 16387
> 
> 
> thanks!
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.stuarthamm.net/
> http://www.dellsmartexitin.com/
> 
> 
> 
> _______________________________________________
> Pg-migrator-general mailing list
> Pg-migrator-general@pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pg-migrator-general

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +