PostgreSQL Bugs - BUG #2375: ALTER COLUMN TYPE on composite types

Author

BUG #2375: ALTER COLUMN TYPE on composite types

George Barbarosie

2006-04-04, 8:29 pm

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-04-04, 8:29 pm

"George Barbarosie" <george.barbarosie@gmail.com> writes:
> CREATE TYPE composite AS (
> label varchar(20),
> data varchar(1000) );

> CREATE TYPE composite2 AS (
> label varchar(20),
> data text );

> CREATE CAST (composite as composite2) WITHOUT FUNCTION AS IMPLICIT;

The above is invalid --- since values of composite types have their type
OID embedded in them, a coercion can't simply be a relabeling. You've
used your superuser powers to break the system, as is all too easy with
binary casts :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links