Re: [GENERAL] Column rename in an extension update script

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:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/extension.c;h=33b0de0a7657298729ad5c3b185dc2f4aab0bb73;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7

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.klaver@aklaver.com