The following bug has been logged on the website:
Bug reference: 11867
Logged by: Marc Munro
Email address: marc@bloodnok.com
PostgreSQL version: 9.3.5
Operating system: Linux Debian 7.6 (Wheezy) on x86_64
Description:
I have a script (below) that sets and resets the tablespace for a database
and drops and recreates a composite type. The script fails when trying to
re-create the previously dropped composite type because the type has
apparently been magically resuscitated by changing the tablespace for the
database. I assume this is a bug.
I have reproduced this on postgres 8.4 and 9.3
This is not mission-critical for me but I'd be grateful for suggestions for
work-arounds.
I am running Debian Wheezy (release 7.6) on x86_64, with the following
postgres versions:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
PostgreSQL 8.4.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian
4.7.2-5) 4.7.2, 64-bit
---Shell script follows---
TBS3="/var/lib/postgresql/8.4/main/tbs3"
#TBS3="/var/lib/postgresql/9.3/main/tbs3"
psql -d postgres -v tbs3=$TBS3 <<'CLUSTEREOF'
\set tbs3dir '''':tbs3''''
create tablespace "tbs3"
location :tbs3dir;
create database "regressdb" with
encoding 'UTF8'
tablespace "tbs3"
connection limit = -1;
CLUSTEREOF
psql -d regressdb <<'DBEOF'
create type "public"."vv2_t" as (
"name" "pg_catalog"."text",
"type" "pg_catalog"."text",
"shared" "pg_catalog"."bool"
);
DBEOF
psql -d postgres <<'CLUSTEREOF'
alter database regressdb set tablespace pg_default;
CLUSTEREOF
psql -d regressdb <<'DBEOF'
drop type public.vv2_t;
DBEOF
psql -d postgres <<'CLUSTEREOF'
alter database regressdb set tablespace tbs3;
CLUSTEREOF
psql -d regressdb <<'DBEOF'
create type public.vv2_t as (
name pg_catalog.text,
type pg_catalog.text,
shared pg_catalog.bool);
DBEOF
---Shell script ends---
__
Marc