Problems with enums after pg_upgrade

On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:

Hello together,

last thursday I upgraded one of our 9.0.6 postgresql servers to
9.2.2 with pg_upgrade. So far everything seemed to work but we now
discover problems with the enum types. If we run one specific query
it breaks all time with such an error message:

ERROR: invalid internal value for enum: 520251

if this number should represent the enumtypid it is not existing
anymore in pg_enum.

How could i solve this problem? should we regenerate all enums? or
what could we do?
Hopefully anyone has a clue, google doesn't seem to be the ressource
for this problem.

We seriously tested the enum code so I am pretty confused why this is
failing. If you do pg_dump --binary-upgrade --schema-only, do you see
that a number like this being defined just before the enum is added?

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

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:

Hello together,

last thursday I upgraded one of our 9.0.6 postgresql servers to
9.2.2 with pg_upgrade. So far everything seemed to work but we now
discover problems with the enum types. If we run one specific query
it breaks all time with such an error message:

ERROR: invalid internal value for enum: 520251

if this number should represent the enumtypid it is not existing
anymore in pg_enum.

How could i solve this problem? should we regenerate all enums? or
what could we do?
Hopefully anyone has a clue, google doesn't seem to be the ressource
for this problem.

Can anyone suggest why enum upgrade is failing for this user? What
should he be checking for?

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

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 02:41 AM, Bruce Momjian wrote:

On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:

Hello together,

last thursday I upgraded one of our 9.0.6 postgresql servers to
9.2.2 with pg_upgrade. So far everything seemed to work but we now
discover problems with the enum types. If we run one specific query
it breaks all time with such an error message:

ERROR: invalid internal value for enum: 520251

if this number should represent the enumtypid it is not existing
anymore in pg_enum.

How could i solve this problem? should we regenerate all enums? or
what could we do?
Hopefully anyone has a clue, google doesn't seem to be the ressource
for this problem.

We seriously tested the enum code so I am pretty confused why this is
failing. If you do pg_dump --binary-upgrade --schema-only, do you see
that a number like this being defined just before the enum is added?

Hi Bruce,

if i am dumping this db and grepping through the dump, i can't find the
number.
As far as we can see, the enum that is affected has now the enumtypid 16728.

is there a table which keeps the possible typecasts from enum to
text/text to enum etc.? if so, maybe the mapping in here is corrupt
since the upgrade.

regards

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com – bernhard.schrader@innogames.de

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 02:41 AM, Bruce Momjian wrote:

On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:

Hello together,

last thursday I upgraded one of our 9.0.6 postgresql servers to
9.2.2 with pg_upgrade. So far everything seemed to work but we now
discover problems with the enum types. If we run one specific query
it breaks all time with such an error message:

ERROR: invalid internal value for enum: 520251

if this number should represent the enumtypid it is not existing
anymore in pg_enum.

How could i solve this problem? should we regenerate all enums? or
what could we do?
Hopefully anyone has a clue, google doesn't seem to be the ressource
for this problem.

We seriously tested the enum code so I am pretty confused why this is
failing. If you do pg_dump --binary-upgrade --schema-only, do you see
that a number like this being defined just before the enum is added?

Hi Bruce,

if i am dumping this db and grepping through the dump, i can't find the
number.

As far as we can see, the enum that is affected has now the enumtypid 16728.

is there a table which keeps the possible typecasts from enum to
text/text to enum etc.? if so, maybe the mapping in here is corrupt
since the upgrade.

regards

###########

Hi again,

maybe there are more information needed to point this stuff out. I'm not
quite sure what would be useful, so i just give you that stuff where is
stumpled upon.

1.) We have some staging servers, where i first used pg_upgrade to make
sure everything is running and nothing breaks on our beta/live servers.
And it worked there, without any problem i can use the enums which break
on the beta servers

2.) As mentioned, on beta servers the usage of the enum fails with error
message:

ERROR: invalid internal value for enum: 520251

3.) If i search for the enumtypid or oid in pg_enum, it is obviously not
there.

select * from pg_enum where enumtypid=520251;
(No rows)

select * from pg_enum where oid=520251;
(No rows)

4.) If i am searching for the enumlabels which are used by the query i
am getting as enumtypid 16728 which also belongs to the expected pg_type

5.) pg_enum of the enumtypid looks like this

select oid,* from pg_enum where enumtypid=16728;

oid | enumtypid | enumsortorder | enumlabel
--------+-----------+---------------+-----------
16729 | 16728 | 1 | att
16730 | 16728 | 2 | def
16731 | 16728 | 3 | all
646725 | 16728 | 4 | adm_att
646726 | 16728 | 5 | adm_def

6.) enumlabels adm_att and adm_def are also defined under another
enumtypid, but i think this shouldn't affect anything. just wanted to
mention this.

7.) during pg_upgrade i used --link method

Well, if you need any other info please ask. i just can't imagine why
this stuff worked on staging servers but not on beta, as they are
identical on database point of view.

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com -- bernhard.schrader@innogames.de

On 12/18/2012 03:45 AM, Bernhard Schrader wrote:

On 12/18/2012 02:41 AM, Bruce Momjian wrote:

On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote:

Hello together,

last thursday I upgraded one of our 9.0.6 postgresql servers to
9.2.2 with pg_upgrade. So far everything seemed to work but we now
discover problems with the enum types. If we run one specific query
it breaks all time with such an error message:

ERROR: invalid internal value for enum: 520251

if this number should represent the enumtypid it is not existing
anymore in pg_enum.

How could i solve this problem? should we regenerate all enums? or
what could we do?
Hopefully anyone has a clue, google doesn't seem to be the ressource
for this problem.

We seriously tested the enum code so I am pretty confused why this is
failing. If you do pg_dump --binary-upgrade --schema-only, do you see
that a number like this being defined just before the enum is added?

Hi Bruce,

if i am dumping this db and grepping through the dump, i can't find
the number.
As far as we can see, the enum that is affected has now the enumtypid
16728.

is there a table which keeps the possible typecasts from enum to
text/text to enum etc.? if so, maybe the mapping in here is corrupt
since the upgrade.

The translations from oid to label are in pg_enum, but it looks like
somehow you have lost that mapping. I'm not sure what you've done but
AFAICT pg_upgrade is doing the right thing.

I just did this (from 9.0 to 9.2) and the pg_upgrade_dump_all.sql that
is used to create the new catalog has these lines:

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('40804'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
binary_upgrade.set_next_array_pg_type_oid('40803'::pg_catalog.oid);

CREATE TYPE myenum AS ENUM (
);

-- For binary upgrade, must preserve pg_enum oids
SELECT binary_upgrade.set_next_pg_enum_oid('40805'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'foo';

SELECT binary_upgrade.set_next_pg_enum_oid('40806'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'bar';

SELECT binary_upgrade.set_next_pg_enum_oid('40807'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'baz';

and this worked exactly as expected, with a table using this type
showing the expected values.

Can you produce a test case demonstrating the error?

When you run pg_upgrade, use the -r flag to keep all the intermediate
files so we can see what's going on.

It's no good dumping the new db looking for these values if they have
been lost. You would need to have a physical copy of the old db and dump
that in binary upgrade mode looking for the Oid. If you don't have a
physical copy of the old db or the intermediate dump file pg_upgrade
used then recovery is going to be pretty difficult. It's not necessarily
impossible, but it might involve you getting some outside help.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Dec 18, 2012 at 10:52:46AM -0500, Andrew Dunstan wrote:

The translations from oid to label are in pg_enum, but it looks like
somehow you have lost that mapping. I'm not sure what you've done
but AFAICT pg_upgrade is doing the right thing.

I just did this (from 9.0 to 9.2) and the pg_upgrade_dump_all.sql
that is used to create the new catalog has these lines:

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('40804'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
binary_upgrade.set_next_array_pg_type_oid('40803'::pg_catalog.oid);

CREATE TYPE myenum AS ENUM (
);

-- For binary upgrade, must preserve pg_enum oids
SELECT binary_upgrade.set_next_pg_enum_oid('40805'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'foo';

SELECT binary_upgrade.set_next_pg_enum_oid('40806'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'bar';

SELECT binary_upgrade.set_next_pg_enum_oid('40807'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'baz';

and this worked exactly as expected, with a table using this type
showing the expected values.

Can you produce a test case demonstrating the error?

When you run pg_upgrade, use the -r flag to keep all the
intermediate files so we can see what's going on.

It's no good dumping the new db looking for these values if they
have been lost. You would need to have a physical copy of the old db
and dump that in binary upgrade mode looking for the Oid. If you
don't have a physical copy of the old db or the intermediate dump
file pg_upgrade used then recovery is going to be pretty difficult.
It's not necessarily impossible, but it might involve you getting
some outside help.

Yes, this matches what I thought too. You see the
binary_upgrade.set_next_pg_enum_oid() calls in pg_dump --binary-upgrade
--schema-only and those set the oid of the newly created enum.

I agree you would need to run this on the _old_ cluster for us to figure
out how it failed.

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

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 05:22 PM, Bruce Momjian wrote:

On Tue, Dec 18, 2012 at 10:52:46AM -0500, Andrew Dunstan wrote:

The translations from oid to label are in pg_enum, but it looks like
somehow you have lost that mapping. I'm not sure what you've done
but AFAICT pg_upgrade is doing the right thing.

I just did this (from 9.0 to 9.2) and the pg_upgrade_dump_all.sql
that is used to create the new catalog has these lines:

-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('40804'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
binary_upgrade.set_next_array_pg_type_oid('40803'::pg_catalog.oid);

CREATE TYPE myenum AS ENUM (
);

-- For binary upgrade, must preserve pg_enum oids
SELECT binary_upgrade.set_next_pg_enum_oid('40805'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'foo';

SELECT binary_upgrade.set_next_pg_enum_oid('40806'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'bar';

SELECT binary_upgrade.set_next_pg_enum_oid('40807'::pg_catalog.oid);
ALTER TYPE public.myenum ADD VALUE 'baz';

and this worked exactly as expected, with a table using this type
showing the expected values.

Can you produce a test case demonstrating the error?

When you run pg_upgrade, use the -r flag to keep all the
intermediate files so we can see what's going on.

It's no good dumping the new db looking for these values if they
have been lost. You would need to have a physical copy of the old db
and dump that in binary upgrade mode looking for the Oid. If you
don't have a physical copy of the old db or the intermediate dump
file pg_upgrade used then recovery is going to be pretty difficult.
It's not necessarily impossible, but it might involve you getting
some outside help.

Yes, this matches what I thought too. You see the
binary_upgrade.set_next_pg_enum_oid() calls in pg_dump --binary-upgrade
--schema-only and those set the oid of the newly created enum.

I agree you would need to run this on the _old_ cluster for us to figure
out how it failed.

Hey,
i just made a testrun, i restored a dump to a testmachine with 9.0
running, made a pg_dump --binary-upgrade --schema-only of that, made my
upgrade to 9.2, after that i checked the schema dump and the values of
the enumtypid in the 9.2 database and they were identically. Thats how
it is expected to be.

Nevertheless this didn't worked with the beta server. but i have no dump
to prove this. Beside the fact that i want to fix my db's, i would also
like to help to improve the upgrade process, but i have no clue right
now how i could do this. i think i will try some other dbs to check if
there maybe an error occurs.

Beside of that, we tested a little bit more with the failing query:
The statement which is causing the error is a big UPDATE-statement with
FROM. After some testing we figured out that the subselect in the
FROM-clause is working fine. And if we simplify the UPDATE-statement
it's also working. We're able to show the data and we're able to do
simple updates on the table. But the two things combined are not
working. We checked the data from the subselect - it's correct. In the
FROM-clause we're using a window-function to calculate a ranking. Do you
know, if there is any mapping for window-functions which has to deal
with enums?

regards

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com – bernhard.schrader@innogames.de

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Bernhard Schrader <bernhard.schrader@innogames.de> writes:

Beside of that, we tested a little bit more with the failing query:
The statement which is causing the error is a big UPDATE-statement with
FROM. After some testing we figured out that the subselect in the
FROM-clause is working fine. And if we simplify the UPDATE-statement
it's also working. We're able to show the data and we're able to do
simple updates on the table. But the two things combined are not
working.

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

If that is the answer, then reindexing said index would get rid of
the problem (as well as all evidence that would help us find out how
it happened ...)

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 01:24 PM, Tom Lane wrote:

Bernhard Schrader <bernhard.schrader@innogames.de> writes:

Beside of that, we tested a little bit more with the failing query:
The statement which is causing the error is a big UPDATE-statement with
FROM. After some testing we figured out that the subselect in the
FROM-clause is working fine. And if we simplify the UPDATE-statement
it's also working. We're able to show the data and we're able to do
simple updates on the table. But the two things combined are not
working.

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

If that is the answer, then reindexing said index would get rid of
the problem (as well as all evidence that would help us find out how
it happened ...)

Unless they can make a physical backup of the datadir first.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2012-12-18 13:24:12 -0500, Tom Lane wrote:

Bernhard Schrader <bernhard.schrader@innogames.de> writes:

Beside of that, we tested a little bit more with the failing query:
The statement which is causing the error is a big UPDATE-statement with
FROM. After some testing we figured out that the subselect in the
FROM-clause is working fine. And if we simplify the UPDATE-statement
it's also working. We're able to show the data and we're able to do
simple updates on the table. But the two things combined are not
working.

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

I already wondered whether it could be a problem caused by pg_upgrade
not ignoring invalid indexes until recently, but I don't really see how
it could cause an invalid oid to end up in the index.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2012-12-18 13:24:12 -0500, Tom Lane wrote:

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

I already wondered whether it could be a problem caused by pg_upgrade
not ignoring invalid indexes until recently, but I don't really see how
it could cause an invalid oid to end up in the index.

It seems like this might indicate a flaw in our scheme for preventing
uncommitted enum values from getting into tables/indexes. Hard to see
what though.

Bernhard, if you do identify a particular index as being the source of
the failure, that would at least tell us for sure which enum type is
at fault. I don't suppose you would have any info about the history
of that enum type in your database? The fact that the OID is odd
implies that it belonged to a value that was added by ALTER TYPE ADD
VALUE, but what we'd want is some context around any past uses of
that command, especially if they failed or were rolled back.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 02:34 PM, Tom Lane wrote:

Andres Freund <andres@2ndquadrant.com> writes:

On 2012-12-18 13:24:12 -0500, Tom Lane wrote:

Does the table being updated have any indexes on enum columns? I'm
suspicious that the bogus OID is in an index page somewhere, and not
in the table at all.

I already wondered whether it could be a problem caused by pg_upgrade
not ignoring invalid indexes until recently, but I don't really see how
it could cause an invalid oid to end up in the index.

It seems like this might indicate a flaw in our scheme for preventing
uncommitted enum values from getting into tables/indexes. Hard to see
what though.

Bernhard, if you do identify a particular index as being the source of
the failure, that would at least tell us for sure which enum type is
at fault. I don't suppose you would have any info about the history
of that enum type in your database? The fact that the OID is odd
implies that it belonged to a value that was added by ALTER TYPE ADD
VALUE, but what we'd want is some context around any past uses of
that command, especially if they failed or were rolled back.

He's upgrading from 9.0, which didn't have enum extension at all, and
where odd enums didn't mean anything special.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:

He's upgrading from 9.0, which didn't have enum extension at all, and
where odd enums didn't mean anything special.

Really? The noncontiguous pg_enum OIDs shown in
http://archives.postgresql.org/pgsql-hackers/2012-12/msg01089.php
suggest strongly that *something's* been done to that type since
it was created.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 02:58 PM, Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

He's upgrading from 9.0, which didn't have enum extension at all, and
where odd enums didn't mean anything special.

Really? The noncontiguous pg_enum OIDs shown in
http://archives.postgresql.org/pgsql-hackers/2012-12/msg01089.php
suggest strongly that *something's* been done to that type since
it was created.

That's what he said.

People have been known to hack pg_enum on their own, especially before
we added enum extension.

Of course, if they do that they get to keep both pieces.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:

People have been known to hack pg_enum on their own, especially before
we added enum extension.
Of course, if they do that they get to keep both pieces.

Yeah ... this would be very readily explainable if there had been a
manual deletion from pg_enum somewhere along the line. Even if there
were at that time no instances of the OID left in tables, there could
be some in upper btree pages. They'd have caused no trouble in 9.0
but would (if odd) cause trouble in 9.2.

Of course, this theory doesn't explain why the problem was seen on some
copies and not others cloned from the same database --- unless maybe
there had been an index page split on the master in between the
clonings, and that moved the troublesome OID into a position where it
was more likely to get compared-to. That's not a hugely convincing
explanation though.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/18/2012 09:38 PM, Tom Lane wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

People have been known to hack pg_enum on their own, especially before
we added enum extension.
Of course, if they do that they get to keep both pieces.

Yeah ... this would be very readily explainable if there had been a
manual deletion from pg_enum somewhere along the line. Even if there
were at that time no instances of the OID left in tables, there could
be some in upper btree pages. They'd have caused no trouble in 9.0
but would (if odd) cause trouble in 9.2.

Of course, this theory doesn't explain why the problem was seen on some
copies and not others cloned from the same database --- unless maybe
there had been an index page split on the master in between the
clonings, and that moved the troublesome OID into a position where it
was more likely to get compared-to. That's not a hugely convincing
explanation though.

regards, tom lane

Guys, thaaaaank youuu aaaall. :) reindex helped, did reindex on two
tables, and everything is now working like expected.

I will provide tomorrow all information which could help to understand
everything in detail, but now it's gonna be late in germany :). and i
got a headache of all this stuff ^^

Thanks so much!!!

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com – bernhard.schrader@innogames.de

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello again,

well, still everything is working.

What information do you need to get into this issue?

Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some
more or less changed enum_add and enum_del (Which are appended at the
end) to be able to change enums within transactions.

And that this happened to the beta server and not to the staging server,
might be because we sometimes have to drop the whole stuff of staging,
because of some failures we did, so old enum values will not be
persistent in old indexes.

if you need more info, just ask. :)

regards Bernhard

SET check_function_bodies = false;
CREATE OR REPLACE FUNCTION enum_add (enum_name character varying,
enum_elem character varying) RETURNS void
AS
$body$
DECLARE
_enum_typid INTEGER;
version_int INTEGER;
_highest_enumsortorder REAL;
BEGIN
-- get enumtypid
SELECT oid FROM pg_type WHERE typtype='e' AND typname=enum_name
INTO _enum_typid;

SELECT INTO version_int setting FROM pg_settings WHERE name =
'server_version_num';
--postgres 9.2 or higher
IF version_int > 90200 THEN
SELECT MAX(enumsortorder) FROM pg_enum WHERE enumtypid =
_enum_typid INTO _highest_enumsortorder;
-- check if elem already exists in enum
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumlabel =
enum_elem AND enumtypid = _enum_typid) THEN
INSERT INTO pg_enum(enumtypid, enumlabel, enumsortorder)
VALUES (
_enum_typid,
enum_elem,
_highest_enumsortorder + 1
);
END IF;
ELSE
-- check if elem already exists in enum
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumlabel =
enum_elem AND enumtypid = _enum_typid) THEN
INSERT INTO pg_enum(enumtypid, enumlabel) VALUES (
_enum_typid,
enum_elem
);
END IF;
END IF;
END;
$body$
LANGUAGE plpgsql;
--
-- Definition for function enum_del:
--
CREATE OR REPLACE FUNCTION enum_del (enum_name character varying,
enum_elem character varying) RETURNS void
AS
$body$
DECLARE
type_oid INTEGER;
rec RECORD;
sql VARCHAR;
ret INTEGER;
BEGIN

SELECT pg_type.oid
FROM pg_type
WHERE typtype = 'e' AND typname = enum_name
INTO type_oid;

-- check if enum exists
IF NOT EXISTS (SELECT * FROM pg_enum WHERE enumtypid = type_oid) THEN
RETURN;
END IF;

-- check if element in enum exists
IF NOT FOUND THEN
RAISE EXCEPTION 'Cannot find a enum: %', enum_name;
END IF;

-- Check column DEFAULT value references.
SELECT *
FROM
pg_attrdef
JOIN pg_attribute ON attnum = adnum AND atttypid = type_oid
JOIN pg_class ON pg_class.oid = attrelid
JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE
adsrc = quote_literal(enum_elem) || '::' || quote_ident(enum_name)
LIMIT 1
INTO rec;

IF FOUND THEN
RAISE EXCEPTION
'Cannot delete the ENUM element %.%: column %.%.% has
DEFAULT value of ''%''',
quote_ident(enum_name), quote_ident(enum_elem),
quote_ident(rec.nspname), quote_ident(rec.relname),
rec.attname, quote_ident(enum_elem);
END IF;

-- Check data references.
FOR rec IN
SELECT *
FROM
pg_attribute
JOIN pg_class ON pg_class.oid = attrelid
JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE
atttypid = type_oid
AND relkind = 'r'
LOOP
sql :=
'SELECT 1 FROM ONLY '
|| quote_ident(rec.nspname) || '.'
|| quote_ident(rec.relname) || ' '
|| ' WHERE '
|| quote_ident(rec.attname) || ' = '
|| quote_literal(enum_elem)
|| ' LIMIT 1';
EXECUTE sql INTO ret;
IF ret IS NOT NULL THEN
RAISE EXCEPTION
'Cannot delete the ENUM element %.%: column %.%.%
contains references',
quote_ident(enum_name), quote_ident(enum_elem),
quote_ident(rec.nspname), quote_ident(rec.relname),
rec.attname;
END IF;
END LOOP;

-- OK. We may delete.
DELETE FROM pg_enum WHERE enumtypid = type_oid AND enumlabel =
enum_elem;
END;
$body$
LANGUAGE plpgsql;

--
Bernhard Schrader
System Administration

InnoGames GmbH
Harburger Schloßstraße 28 (Channel 4) - 21079 Hamburg - Germany
Tel +49 40 7889335-53
Fax +49 40 7889335-22

Managing Directors: Hendrik Klindworth, Eike Klindworth, Michael Zillmer
VAT-ID: DE264068907 Amtsgericht Hamburg, HRB 108973

http://www.innogames.com -- bernhard.schrader@innogames.de

On 2012-12-19 16:51:32 +0100, Bernhard Schrader wrote:

Hello again,

well, still everything is working.

What information do you need to get into this issue?

Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some more
or less changed enum_add and enum_del (Which are appended at the end) to be
able to change enums within transactions.

That explains everything. You *CANNOT* do that. There are some pretty
fundamental reasons why you are not allowed to add enums in a
transaction. And even more reasons why deleting from enums isn't allowed
at all.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/19/2012 10:56 AM, Andres Freund wrote:

On 2012-12-19 16:51:32 +0100, Bernhard Schrader wrote:

Hello again,

well, still everything is working.

What information do you need to get into this issue?

Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some more
or less changed enum_add and enum_del (Which are appended at the end) to be
able to change enums within transactions.

That explains everything. You *CANNOT* do that. There are some pretty
fundamental reasons why you are not allowed to add enums in a
transaction. And even more reasons why deleting from enums isn't allowed
at all.

Yes, this is exactly what I referred to in my recent reply to Tom. This
is a recipe for database corruption.

Hacking the catalog generally is something to be done only with the most
extreme caution, IMNSHO.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2012-12-19 16:51:32 +0100, Bernhard Schrader wrote:

Well, so far i can say, we dont use ALTER TYPE ADD VALUE. We use some more
or less changed enum_add and enum_del (Which are appended at the end) to be
able to change enums within transactions.

That explains everything. You *CANNOT* do that.

Yeah. So this was not pg_upgrade's fault at all: that code would have
created problems in 9.1 or later even without using pg_upgrade.

For the record, the reason you can't safely do this is exactly what we
saw here: it's possible for deleted/never-committed values of the type
to remain behind in upper levels of btree indexes. Since we now need
to be able to consult pg_enum to know how to compare values of an enum
type, deleted values are uncomparable.

enum_add is all right as long as you are careful to commit its
transaction before inserting the new value anywhere. enum_del is quite
unsafe unless you REINDEX all indexes on columns of the type after
making sure the value is gone from the tables.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/19/2012 11:31 AM, Tom Lane wrote:

enum_add is all right as long as you are careful to commit its
transaction before inserting the new value anywhere.

It's not really all right for post-9.0 versions. For example, this is wrong:

--postgres 9.2 or higher
IF version_int > 90200 THEN

It should really be "IF version_int >= 90100 THEN"

what is even worse is that this procedure doesn't take any care at all
of the ordering rule for even numbered enum oids. We could have oid
wraparound to an even numbered oid and it would break the rule.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:

what is even worse is that this procedure doesn't take any care at all
of the ordering rule for even numbered enum oids.

Good point. You really should use ALTER TYPE ADD VALUE, on versions
where that's available.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers