[GENERAL] Column rename in an extension update script - Mailing list pgsql-general

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.