Обсуждение: BUG #4688: Bug in cache.
The following bug has been logged online: Bug reference: 4688 Logged by: Oleg Email address: serovOv@gmail.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.linnakangas@enterprisedb.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.linnakangas@enterprisedb.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.linnakangas@enterprisedb.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@sss.pgh.pa.us> wrote: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.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. =A0Maybe 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. --=20 Guillaume
Tom Lane wrote: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.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.linnakangas@enterprisedb.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.linnakangas@enterprisedb.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