Extension tracking temp table and causing update failure

From: Phil Sorber <phil(at)omniti(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Extension tracking temp table and causing update failure
Date: 2012-03-06 20:09:00
Message-ID: CADAkt-i4kJn_ht=+6x408uVjQG4XKjvy3FbyMKer+oJATLWykQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Running Postgres 9.1.3. As far as I can tell, when you do an 'alter
table' and add a new column with a new default value a temp table is
created and tracked by the extension as a new object, but when the
'alter table' statement tries to drop the temp table at the end, the
extension complains. I recreated it with a simple test case. I've
attached the test extension, but I think the important piece is this
line from the update script:

alter table t1 add column created_at timestamp with time zone not null
default now();

Example output:

postgres=# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.6.2 20111027 (Red Hat 4.6.2-1), 64-bit
(1 row)

postgres=# CREATE SCHEMA myext;
CREATE SCHEMA
postgres=# CREATE EXTENSION myext WITH SCHEMA myext;
CREATE EXTENSION
postgres=# \d myext.t1
Table "myext.t1"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------
id | integer | not null default nextval('myext.t1_id_seq'::regclass)
foo | text |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)

postgres=# ALTER EXTENSION myext UPDATE TO '0.2';
ERROR: cannot drop table pg_temp_28906 because extension myext requires it
HINT: You can drop extension myext instead.
postgres=# DROP EXTENSION myext;
DROP EXTENSION
postgres=# CREATE EXTENSION myext WITH SCHEMA myext VERSION '0.2';
CREATE EXTENSION
postgres=# \d myext.t1
Table "myext.t1"
Column | Type |
Modifiers
------------+--------------------------+-------------------------------------------------------
id | integer | not null default
nextval('myext.t1_id_seq'::regclass)
foo | text |
created_at | timestamp with time zone | not null default now()
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)

Attachment Content-Type Size
myext.tar.gz application/x-gzip 549 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Matteo Sgalaberni 2012-03-06 20:24:20 corrupted table postgresql 8.3
Previous Message Sergey Burladyan 2012-03-06 19:36:28 Re: BUG #6480: NLS text width problem