From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Philippe BEAUDOIN <phb(dot)emaj(at)free(dot)fr>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Column rename in an extension update script |
Date: | 2017-05-03 17:10:44 |
Message-ID: | 331e2430-9c74-ebeb-6e6c-e2257058bf92@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote:
> Hi all,
>
> I am coding an update script for an extension. And I am in trouble when
> trying to rename a column of an existing table.
>
> Just after the ALTER TABLE statement, I want to access this table. But
> at this time, the altered column is not visible with its new name.
>
>
From the error it looks to me like the statements are each run in a
separate session and the UPDATE is not seeing the ALTER TABLE. A quick
search of the source indicates this is handled in extension.c:
In particular execute_sql_string line 684. I do not understand C well
enough to figure out if the above is actually creating separate sessions
or not. Maybe you understand it or someone else can chime in.
>
> # issue in postgres extension when trying to access a column that has
> been renamed inside an extension update script
> #
> export EXTDIR="/tmp"
> export PGDIR="/usr/local/pg962/share/postgresql/extension"
> export PGHOST=localhost
> export PGPORT=5496
> export PGDATABASE='postgres'
>
> echo "create files for the extension"
> echo "------------------------------"
> cat >$EXTDIR/myextension.control <<*END*
> default_version = '1'
> directory = '$EXTDIR'
> *END*
> sudo ln -s $EXTDIR/myextension.control $PGDIR/myextension.control
>
> cat >$EXTDIR/myextension--1.sql <<*END*
> CREATE TABLE mytable (col_old INT);
> *END*
>
> cat >$EXTDIR/myextension--1--2.sql <<*END*
> ALTER TABLE mytable RENAME col_old TO col_new;
> UPDATE mytable SET col_new = 0;
> *END*
>
> echo "psql: run the test ==> FAILS"
> echo "----------------------------"
> psql -a <<*END*
> select version();
> CREATE EXTENSION myextension VERSION '1';
> ALTER EXTENSION myextension UPDATE TO '2';
> DROP EXTENSION IF EXISTS myextension;
> *END*
>
> echo "psql: similar statements outside extension ==> WORKS"
> echo "----------------------------------------------------"
> psql -a <<*END*
> CREATE TABLE mytable (col_old INT);
> BEGIN;
> ALTER TABLE mytable RENAME col_old TO col_new;
> UPDATE mytable SET col_new = 0;
> COMMIT;
> DROP TABLE IF EXISTS mytable;
> *END*
>
> sudo rm $PGDIR/myextension.control
> rm $EXTDIR/myextension*
>
> And here is the result:
>
> create files for the extension
> ------------------------------
> psql: run the test ==> FAILS
> ----------------------------
> select version();
>
> version
> -----------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
> (1 row)
>
> CREATE EXTENSION myextension VERSION '1';
> CREATE EXTENSION
> ALTER EXTENSION myextension UPDATE TO '2';
> ERROR: column "col_new" of relation "mytable" does not exist
> DROP EXTENSION IF EXISTS myextension;
> DROP EXTENSION
> psql: similar statements outside extension ==> WORKS
> ----------------------------------------------------
> CREATE TABLE mytable (col_old INT);
> CREATE TABLE
> BEGIN;
> BEGIN
> ALTER TABLE mytable RENAME col_old TO col_new;
> ALTER TABLE
> UPDATE mytable SET col_new = 0;
> UPDATE 0
> COMMIT;
> COMMIT
> DROP TABLE IF EXISTS mytable;
> DROP TABLE
>
> As you can see:
>
> - the error message is "ERROR: column "col_new" of relation "mytable"
> does not exist", while the ALTER TABLE statement doesn't return any error,
>
> - the same statements in a simple psql script works fine,
>
> - I reproduce this with all supported postgres versions.
>
> As a workaround, I perform the UPDATE statement before the ALTER TABLE
> operation, using of course the old column name.
>
> I probably do something wrong. But I can't see what.
>
> Thanks by advance for any piece of advise.
>
> Best regards. Philippe Beaudoin.
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-05-03 17:29:57 | Re: Column rename in an extension update script |
Previous Message | Julien Rouhaud | 2017-05-03 15:23:02 | Re: Difficult while acquiring LWLocks |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2017-05-03 17:27:46 | Re: WITH clause in CREATE STATISTICS |
Previous Message | Tom Lane | 2017-05-03 17:08:40 | How huge does mvtest_huge need to be? |