The following bug has been logged online:
Bug reference: 2375
Logged by: George Barbarosie
Email address: george.barbarosie@gmail.com
PostgreSQL version: 8.1.3
Operating system: Gentoo 2006.0
Description: ALTER COLUMN TYPE on composite types
Details:
Trying to change the type of a column from one composite type to a
compatible composite type and then dropping the original type results in
"ERROR: could not open relation with oid %". I've also seen the error "cache
lookup failed for type %" on one of my servers, but that was a much more
complex scenario than the following testcase, and I have not been able to
reproduce that error reliably.
testcase.sql:
BEGIN;
CREATE TYPE composite AS (
label varchar(20),
data varchar(1000) );
CREATE TABLE test (
id SERIAL PRIMARY KEY,
contents composite );
INSERT INTO test (contents) VALUES (row('entry1', 'data for entry 1'));
CREATE TYPE composite2 AS (
label varchar(20),
data text );
CREATE CAST (composite as composite2) WITHOUT FUNCTION AS IMPLICIT;
SELECT contents::composite2 from test;
ALTER TABLE test ALTER COLUMN contents type composite2;
SELECT contents from test;
UPDATE test SET contents = contents::composite2;
DROP TYPE composite CASCADE;
SELECT contents from test;
ROLLBACK;
Output of "createdb test; psql -f testcase.sql test":
BEGIN
CREATE TYPE
psql:testcase.sql:7: NOTICE: CREATE TABLE will create implicit sequence
"test_id_seq" for serial column "test.id"
psql:testcase.sql:7: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "test_pkey" for table "test"
CREATE TABLE
INSERT 0 1
CREATE TYPE
CREATE CAST
contents
-----------------------------
(entry1,"data for entry 1")
(1 row)
ALTER TABLE
contents
-----------------------------
(entry1,"data for entry 1")
(1 row)
UPDATE 1
psql:testcase.sql:17: NOTICE: drop cascades to cast from composite to
composite2
DROP TYPE
psql:testcase.sql:18: ERROR: could not open relation with OID 976808
ROLLBACK