Postgres Pro
Downloads
Home   >   mailing lists

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

[GENERAL] Column rename in an extension update script

From
Philippe BEAUDOIN
Date:

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.

I wrote a simple test case to show this. Here is the shell script that can be easily adapted.

# 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.


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

From
Adrian Klaver
Date:
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

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

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote:
>> 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.

No, it's in the same session; the problem is the lack of a
CommandCounterIncrement call between the ALTER's update and the parsing
of the next statement.  That means the update isn't visible yet,
even in its own session.  See the fix here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9209e07605afe0349660447f20d83ef165cdd0ae

            regards, tom lane

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

From
Philippe BEAUDOIN
Date:
Le 03/05/2017 à 19:29, Tom Lane a écrit :
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote:
>>> 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.
> No, it's in the same session; the problem is the lack of a
> CommandCounterIncrement call between the ALTER's update and the parsing
> of the next statement.  That means the update isn't visible yet,
> even in its own session.  See the fix here:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9209e07605afe0349660447f20d83ef165cdd0ae
>
>             regards, tom lane
Thanks Tom for the fix. And thanks to Julien and Adrian too, for the
time spent on this issue.

Regards.