Re: [GENERAL] Column rename in an extension update script
Le 03/05/2017 à 19:29, Tom Lane a écrit : Adrian Klaverwrites: 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Column rename in an extension update script
Adrian Klaverwrites: > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Column rename in an extension update script
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.