Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d5aNr-0001nr-FO for pgsql-general@arkaria.postgresql.org; Tue, 02 May 2017 16:10:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d5aNq-0002Ax-SR for pgsql-general@arkaria.postgresql.org; Tue, 02 May 2017 16:10:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d55Ei-0008KX-2j for pgsql-general@postgresql.org; Mon, 01 May 2017 06:55:08 +0000 Received: from smtp5-g21.free.fr ([212.27.42.5]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1d55EZ-0004D9-0u for pgsql-general@postgresql.org; Mon, 01 May 2017 06:55:07 +0000 Received: from [192.168.1.22] (unknown [82.233.212.24]) by smtp5-g21.free.fr (Postfix) with ESMTP id 754F25FF69 for ; Mon, 1 May 2017 08:54:57 +0200 (CEST) From: Philippe BEAUDOIN Subject: Column rename in an extension update script To: pgsql-general@postgresql.org Message-ID: <2cf7941e-4e41-7714-3de8-37b1a8f74dff@free.fr> Date: Mon, 1 May 2017 08:54:56 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.8.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------0D90F7A7CC944718266CEC07" X-Pg-Spam-Score: -5.4 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org This is a multi-part message in MIME format. --------------0D90F7A7CC944718266CEC07 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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. --------------0D90F7A7CC944718266CEC07 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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.


--------------0D90F7A7CC944718266CEC07--