Re: [Pg-migrator-general] Composite types break pg_migrated tables

Bruce Momjian wrote:

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 1 pages, containing 3 live rows and
0 dead rows; 3 rows in sample, 3 estimated total rows
ERROR: cache lookup failed for type 27604

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.)

More info: I found 27604 in the old 8.3 database:

test=> SELECT * FROM pg_type WHERE oid = 27604;
-[ RECORD 1 ]-+------------
typname | footype
typnamespace | 2200
typowner | 10
typlen | -1
typbyval | f
typtype | c
typisdefined | t
typdelim | ,
typrelid | 27602
typelem | 0
typarray | 27603
typinput | record_in
typoutput | record_out
typreceive | record_recv
typsend | record_send
typmodin | -
typmodout | -
typanalyze | -
typalign | d
typstorage | x
typnotnull | f
typbasetype | 0
typtypmod | -1
typndims | 0
typdefaultbin |
typdefault |

'footype' has a different oid in the new 8.4 database:

test=> SELECT oid, * FROM pg_type WHERE typname = 'footype';
-[ RECORD 1 ]--+------------
oid | 17580
typname | footype
typnamespace | 2200
typowner | 10
typlen | -1
typbyval | f
typtype | c
typcategory | C
typispreferred | f
typisdefined | t
typdelim | ,
typrelid | 17578
typelem | 0
typarray | 17579
typinput | record_in
typoutput | record_out
typreceive | record_recv
typsend | record_send
typmodin | -
typmodout | -
typanalyze | -
typalign | d
typstorage | x
typnotnull | f
typbasetype | 0
typtypmod | -1
typndims | 0
typdefaultbin |
typdefault |

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

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

Hm ... has anyone tested pg_migrator using either composite types or
arrays of user-defined types? Both of them have got user-defined-type
OIDs in on-disk data, now that I think about it. For that matter, enums
are going to be a problem too.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

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

Hm ... has anyone tested pg_migrator using either composite types or
arrays of user-defined types? Both of them have got user-defined-type
OIDs in on-disk data, now that I think about it. For that matter, enums
are going to be a problem too.

Don't arrays have embedded element OIDs too?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

Hm ... has anyone tested pg_migrator using either composite types or
arrays of user-defined types? Both of them have got user-defined-type
OIDs in on-disk data, now that I think about it. For that matter, enums
are going to be a problem too.

Don't arrays have embedded element OIDs too?

Er, that's what I said. It looks nasty :-(

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

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

Hm ... has anyone tested pg_migrator using either composite types or
arrays of user-defined types? Both of them have got user-defined-type
OIDs in on-disk data, now that I think about it. For that matter, enums
are going to be a problem too.

Yep, I realized that since I posted. It seems composite types are
mini-heap tuples, except that instead of xmin/xmax, they have type
information:

typedef struct DatumTupleFields
{
int32 datum_len_; /* varlena header (do not touch directly!) */

int32 datum_typmod; /* -1, or identifier of a record type */

Oid datum_typeid; /* composite type OID, or RECORDOID */

/*
* Note: field ordering is chosen with thought that Oid might someday
* widen to 64 bits.
*/
} DatumTupleFields;

datum_typeid is where the composite type oid is stored.

Do we have no composite types in the regression tests, or do we not
store any in the database? Same the enums.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

Hm ... has anyone tested pg_migrator using either composite types or
arrays of user-defined types? Both of them have got user-defined-type
OIDs in on-disk data, now that I think about it. For that matter, enums
are going to be a problem too.

Don't arrays have embedded element OIDs too?

Er, that's what I said. It looks nasty :-(

Seems we have two possible directions to go in. First I can easily
cause pg_migrator to exit if it finds any of these issues in any
database.

To allow pg_migrator to work, I would need to reserve the oids in
pg_type, import the dump, and renumber the pg_type entries (and
everything pointing to them) to the proper pg_type.oid. The big problem
there is that I don't have access at the SQL level to set or change
oids. I am afraid the oid remumbering is something we would have to do
in the backend by walking through the pg_depend entries for the pg_type
row. Yuck.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

To allow pg_migrator to work, I would need to reserve the oids in
pg_type, import the dump, and renumber the pg_type entries (and
everything pointing to them) to the proper pg_type.oid. The big problem
there is that I don't have access at the SQL level to set or change
oids. I am afraid the oid remumbering is something we would have to do
in the backend by walking through the pg_depend entries for the pg_type
row. Yuck.

Renumbering type OIDs after-the-fact seems impossibly messy --- there's
not even any support in the backend for changing the OID of an existing
row, let alone any way to do it from the SQL level. And you'd have to
find and fix all the references elsewhere in the system catalogs. And
what about collisions?

ISTM the only reasonable way to deal with this would be to have some way
for pg_dump to emit commands to create types with specific OIDs. While
we were at it, we might as well add the ability to specify toast-table
OIDs so as to get rid of the kluge that's doing that now.

At the moment it looks to me like pg_migrator has crashed and burned
for 8.4, at least for general-purpose usage. We might be able to have
support for this stuff in 8.5. But not being able to deal with any
user-defined types is too much of a restriction to make it of general
interest.

regards, tom lane

Bruce Momjian wrote:

Do we have no composite types in the regression tests, or do we not
store any in the database? Same the enums.

Looks like the enum regression tests at least drop all their tables :-(

To allow pg_migrator to work, I would need to reserve the oids in
pg_type, import the dump, and renumber the pg_type entries (and
everything pointing to them) to the proper pg_type.oid. The big problem
there is that I don't have access at the SQL level to set or change
oids. I am afraid the oid remumbering is something we would have to do
in the backend by walking through the pg_depend entries for the pg_type
row. Yuck.

Yeah. Maybe we need some special way of setting the oids explicitly. But
preventing a clash might be fairly difficult.

Excluding every database that has a composite/array-of
user-defined-type/enum type would be pretty nasty. After all, these are
features we boast of.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

preventing a clash might be fairly difficult.

Yeah, I was just thinking about that. The easiest way to avoid
collisions would be to make pg_dump (in --binary-upgrade mode)
responsible for being sure that *every* new pg_type and pg_class row
OID matches what it was in the old DB. We could stop doing that
once we have all the user tables in place --- I don't believe it's
necessary to preserve the OIDs of user indexes. But we need to
preserve toast table OIDs, and toast table index OIDs too if those
are created at the same time they are now (else we risk one of them
colliding with a toast table OID we want to create later).

Oh, and pg_enum rows too.

It seems doable, but we're certainly not going to back-patch
any such thing into 8.4 ...

regards, tom lane

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

preventing a clash might be fairly difficult.

Yeah, I was just thinking about that. The easiest way to avoid
collisions would be to make pg_dump (in --binary-upgrade mode)
responsible for being sure that *every* new pg_type and pg_class row
OID matches what it was in the old DB. We could stop doing that
once we have all the user tables in place --- I don't believe it's
necessary to preserve the OIDs of user indexes. But we need to
preserve toast table OIDs, and toast table index OIDs too if those
are created at the same time they are now (else we risk one of them
colliding with a toast table OID we want to create later).

Oh, and pg_enum rows too.

It seems doable, but we're certainly not going to back-patch
any such thing into 8.4 ...

Is there any danger that an oid used in, say, pg_enum in the old version
will be used in the catalog bootstrap in the new version?

cheers

andrew

Andrew Dunstan wrote:

Bruce Momjian wrote:

Do we have no composite types in the regression tests, or do we not
store any in the database? Same the enums.

Looks like the enum regression tests at least drop all their tables :-(

To allow pg_migrator to work, I would need to reserve the oids in
pg_type, import the dump, and renumber the pg_type entries (and
everything pointing to them) to the proper pg_type.oid. The big problem
there is that I don't have access at the SQL level to set or change
oids. I am afraid the oid remumbering is something we would have to do
in the backend by walking through the pg_depend entries for the pg_type
row. Yuck.

Yeah. Maybe we need some special way of setting the oids explicitly. But
preventing a clash might be fairly difficult.

Excluding every database that has a composite/array-of
user-defined-type/enum type would be pretty nasty. After all, these are
features we boast of.

Well, pg_migrator has gotten pretty far without supporting these
features, and I think I would have heard about it if someone had these
and migrated because vacuum analyze found it right away. I am afraid
the best we can do is to throw an error when we see these cases and hope
we can improve things for 8.5.

As I understand it I have to look for the _use_ of these in user tables,
not the existance of them in pg_type --- for example, there is
certainly an array for every user type, but it might not be used by any
user tables, and that would be OK.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Andrew Dunstan <andrew@dunslane.net> writes:

Is there any danger that an oid used in, say, pg_enum in the old version
will be used in the catalog bootstrap in the new version?

No. All initdb-assigned OIDs are less than 16K, and we never assign
such an OID post-initdb (not even when wrapping around). We might get
into trouble if we ever run out of OIDs below 16K, but I don't foresee
that happening anytime soon.

Also, the design I sketched depends on the fact that it doesn't matter
if, say, a pg_proc row gets an OID that we also need to use in pg_enum.
We only need OID uniqueness within each specific catalog. So we don't
need to control the OID assignments in catalogs other than the three
we are interested in.

regards, tom lane

Andrew Dunstan wrote:

Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

preventing a clash might be fairly difficult.

Yeah, I was just thinking about that. The easiest way to avoid
collisions would be to make pg_dump (in --binary-upgrade mode)
responsible for being sure that *every* new pg_type and pg_class row
OID matches what it was in the old DB. We could stop doing that
once we have all the user tables in place --- I don't believe it's
necessary to preserve the OIDs of user indexes. But we need to
preserve toast table OIDs, and toast table index OIDs too if those
are created at the same time they are now (else we risk one of them
colliding with a toast table OID we want to create later).

Oh, and pg_enum rows too.

It seems doable, but we're certainly not going to back-patch
any such thing into 8.4 ...

Is there any danger that an oid used in, say, pg_enum in the old version
will be used in the catalog bootstrap in the new version?

No because the catalog bootstrap oids are all lower than
FirstNormalObjectId. The _big_ problem is the creation of pg_type oids
while other things are being created, e.g. you say to create an object
of fixed oid 123 and the array is created as 124, and later you need to
use 124 as a fixed oid. We will need to assign _every_ pg_type oid from
pg_dump so we are sure there are not some assigned that we will need
later.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Andrew Dunstan wrote:

Bruce Momjian wrote:

Do we have no composite types in the regression tests, or do we not
store any in the database? Same the enums.

Looks like the enum regression tests at least drop all their tables :-(

To allow pg_migrator to work, I would need to reserve the oids in
pg_type, import the dump, and renumber the pg_type entries (and
everything pointing to them) to the proper pg_type.oid. The big problem
there is that I don't have access at the SQL level to set or change
oids. I am afraid the oid remumbering is something we would have to do
in the backend by walking through the pg_depend entries for the pg_type
row. Yuck.

Yeah. Maybe we need some special way of setting the oids explicitly. But
preventing a clash might be fairly difficult.

Excluding every database that has a composite/array-of
user-defined-type/enum type would be pretty nasty. After all, these are
features we boast of.

Well, pg_migrator has gotten pretty far without supporting these
features, and I think I would have heard about it if someone had these
and migrated because vacuum analyze found it right away. I am afraid
the best we can do is to throw an error when we see these cases and hope
we can improve things for 8.5.

As I understand it I have to look for the _use_ of these in user tables,
not the existance of them in pg_type --- for example, there is
certainly an array for every user type, but it might not be used by any
user tables, and that would be OK.

I have applied the attached patch to pg_migrator to detect enum,
composites, and arrays. I tested it and the only error I got was with
the breakmigrator table that was supplied by Jeff, and once I removed
that table the migration went fine, meaning there are no cases of these
stored in the regression test database.

I will release a new version of pg_migrator with these new detection
routines.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane <tgl@sss.pgh.pa.us> writes:

Andrew Dunstan <andrew@dunslane.net> writes:

preventing a clash might be fairly difficult.

Yeah, I was just thinking about that. The easiest way to avoid
collisions would be to make pg_dump (in --binary-upgrade mode)
responsible for being sure that *every* new pg_type and pg_class row
OID matches what it was in the old DB.

As we already have WITH OIDS for CREATE TABLE command, maybe adding
support for WITH OID ... to the necessary commands would do the trick?

Instead of messing with pg_type, pg_dump would then have to issue a OID
'decorated' command such as
CREATE TYPE footype ... WITH OID 27604;

We could stop doing that
once we have all the user tables in place --- I don't believe it's
necessary to preserve the OIDs of user indexes. But we need to
preserve toast table OIDs, and toast table index OIDs too if those
are created at the same time they are now (else we risk one of them
colliding with a toast table OID we want to create later).

It seems harder to come up with a general purpose syntax to support the
feature in case of toast tables, though.

Regards,
--
dim

Tom Lane �rta:

At the moment it looks to me like pg_migrator has crashed and burned
for 8.4, at least for general-purpose usage.

It means that you don't have the restraint that
you thought you have. So you can change the
RedHat/Fedora PostgreSQL 8.4 packages to use
the upstream default for integer timestamps...

Best regards,
Zolt�n B�sz�rm�nyi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

On Thursday 06 August 2009 06:32:06 Bruce Momjian wrote:

I have applied the attached patch to pg_migrator to detect enum,
composites, and arrays. I tested it and the only error I got was with
the breakmigrator table that was supplied by Jeff, and once I removed
that table the migration went fine, meaning there are no cases of these
stored in the regression test database.

That might be a bit excessive. As I understand it, arrays of built-in types
(e.g., int[]) should work fine. I suspect the majority of uses of arrays will
be with built-in types, so allowing that would help a significant portion of
installations.

Dimitri Fontaine wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

We could stop doing that
once we have all the user tables in place --- I don't believe it's
necessary to preserve the OIDs of user indexes. But we need to
preserve toast table OIDs, and toast table index OIDs too if those
are created at the same time they are now (else we risk one of them
colliding with a toast table OID we want to create later).

It seems harder to come up with a general purpose syntax to support the
feature in case of toast tables, though.

There's already general purpose syntax for relation options which can be
used to get options that do not ultimately end up in
pg_class.reloptions. An existing example is WITH (oids). One such
option could be used here.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Andrew Dunstan <andrew@dunslane.net> wrote:

Excluding every database that has a composite/array-of
user-defined-type/enum type would be pretty nasty. After all, these
are features we boast of.

Any idea whether domains are an issue? I was thinking of trying this
tool soon, and we don't seem to be using any of the problem features
-- unless type issues include domains.

-Kevin