Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: myext.tar.gz
Description: application/x-gzip (549 bytes)

Responses

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group