Re: BUG #4688: Bug in cache.

The following bug has been logged online:

Bug reference: 4688
Logged by: Oleg
Email address: serovOv(at)gmail(dot)com
PostgreSQL version: last stable
Operating system: CentOS
Description: Bug in cache.
Details:

Demo sql:
ROLLBACK;
BEGIN;

CREATE TABLE bug_composite_type (
text character varying(50)
);

CREATE TABLE bug_list (
test bug_composite_type
);

INSERT INTO bug_list VALUES (ROW('text'));

ALTER TABLE bug_composite_type RENAME TO tmp_table;

CREATE TABLE bug_composite_type
(
text character varying(250)
);

CREATE CAST (tmp_table AS composite_ad_texts)
WITHOUT FUNCTION AS ASSIGNMENT;

ALTER TABLE bug_list ALTER test TYPE composite_ad_texts;

DROP CAST (tmp_table AS composite_ad_texts);
DROP TABLE tmp_table;

SELECT * FROM bug_list; -- bug
-- ERROR: could not open relation with OID 395705050

Oleg wrote:
> CREATE CAST (tmp_table AS composite_ad_texts)
> WITHOUT FUNCTION AS ASSIGNMENT;

"WITHOUT FUNCTION" can only be used when both types are binary
compatible. You might think that two composite types with the same
fields are, but they're not: we store the OID of the composite type in
the records.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Oleg wrote:
>> CREATE CAST (tmp_table AS composite_ad_texts)
>> WITHOUT FUNCTION AS ASSIGNMENT;

> "WITHOUT FUNCTION" can only be used when both types are binary
> compatible. You might think that two composite types with the same
> fields are, but they're not: we store the OID of the composite type in
> the records.

Although this qualifies as pilot error (superusers are expected to know
what they're doing), should we attempt to prevent the case? It doesn't
seem like a tremendously unlikely mistake to make, and AFAICS there is
no easy way to recover your data once you've done it.

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Oleg wrote:
>>> CREATE CAST (tmp_table AS composite_ad_texts)
>>> WITHOUT FUNCTION AS ASSIGNMENT;
>
>> "WITHOUT FUNCTION" can only be used when both types are binary
>> compatible. You might think that two composite types with the same
>> fields are, but they're not: we store the OID of the composite type in
>> the records.
>
> Although this qualifies as pilot error (superusers are expected to know
> what they're doing), should we attempt to prevent the case?

We can't detect binary-incompatibility in general, so I presume you
meant just for the case of composite types. Hmm, I guess we could do it
in that case.

> It doesn't
> seem like a tremendously unlikely mistake to make, and AFAICS there is
> no easy way to recover your data once you've done it.

I believe the command has been like that for a long time, and this is
the first time someone managed to shoot one's foot. It was made much
worse by the ALTER TABLE and DROP TABLE. But yeah, it seems easy enough
to check for the composite types case, so let's do that.

Oleg replied off-list asking how to recover the data. I suggested
resetting the OID counter to the OID of the dropped table with
pg_resetxlog, recreating it, and doing a pg_dump/restore.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> Although this qualifies as pilot error (superusers are expected to know
>> what they're doing), should we attempt to prevent the case?

> We can't detect binary-incompatibility in general, so I presume you
> meant just for the case of composite types. Hmm, I guess we could do it
> in that case.

Right, I was envisioning "if both types are composite and there's no
function supplied, throw error".

> I believe the command has been like that for a long time, and this is
> the first time someone managed to shoot one's foot.

True. Maybe it's not worth the trouble.

regards, tom lane

On Tue, Mar 3, 2009 at 4:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> I believe the command has been like that for a long time, and this is
>> the first time someone managed to shoot one's foot.
>
> True.  Maybe it's not worth the trouble.

IMHO, the consequences are far from being negligible and can put a
cluster down for quite a long time if the database is large
(considering the method suggested by Heikki to fix the problem). So if
we can avoid this sort of problem in a few cases without too much
work, it seems like something we should fix.

--
Guillaume

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Tom Lane wrote:
>>> Although this qualifies as pilot error (superusers are expected to know
>>> what they're doing), should we attempt to prevent the case?
>
>> We can't detect binary-incompatibility in general, so I presume you
>> meant just for the case of composite types. Hmm, I guess we could do it
>> in that case.
>
> Right, I was envisioning "if both types are composite and there's no
> function supplied, throw error".

If we go down that path, how far do we go? We also know that two enums
are never binary-compatible, right? Composite type and a user-defined
base type? Hardly, unless you're doing something very hacky...

Disallowing binary casts when any composite types or enums are involved
seems sane, but that's as far as we can go with a few lines of code.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> If we go down that path, how far do we go? We also know that two enums
> are never binary-compatible, right? Composite type and a user-defined
> base type? Hardly, unless you're doing something very hacky...

> Disallowing binary casts when any composite types or enums are involved
> seems sane, but that's as far as we can go with a few lines of code.

Arrays have embedded type OIDs too ...

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> If we go down that path, how far do we go? We also know that two enums
>> are never binary-compatible, right? Composite type and a user-defined
>> base type? Hardly, unless you're doing something very hacky...
>
>> Disallowing binary casts when any composite types or enums are involved
>> seems sane, but that's as far as we can go with a few lines of code.
>
> Arrays have embedded type OIDs too ...

I've committed a simple check, disallowing composite types, enums and
arrays in binary casts.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com