Обсуждение: [BUGS] BUG #14470: Dropping a column produces "table row type andquery-specified row type do not match" error
[BUGS] BUG #14470: Dropping a column produces "table row type andquery-specified row type do not match" error
От
joan@sanchezsabe.com
Дата:
The following bug has been logged on the website: Bug reference: 14470 Logged by: Joan Sánchez Sabé Email address: joan@sanchezsabe.com PostgreSQL version: 9.6.1 Operating system: Mac OS X 10.12.2 Description: Steps to reproduce: CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL, column_that_we_will_drop TEXT ) ; CREATE OR REPLACE FUNCTION fun (_email text) RETURNS integer AS $$ -- Need a CTE to produce the error. A 'constant' one suffices. WITH something_even_if_useless(a) AS ( VALUES (1) ) UPDATE users SET id = id WHERE -- The CTE needs to be referenced, if the next -- condition were not in place, the problem is not reproduced EXISTS (SELECT * FROM something_even_if_useless) AND email = _email RETURNING id $$ LANGUAGE "sql" ; At this point, calls to the function such as SELECT * FROM fun('a@b.com'); Work without problems. At this point, if we drop one column (not used at all by the previous function)... ALTER TABLE users DROP COLUMN column_that_we_will_drop ; ... this makes the next statement to generate an error SELECT * FROM fun('a@b.com'); ERROR: table row type and query-specified row type do not match SQL state: 42804 Detail: Query provides a value for a dropped column at ordinal position 3. Context: SQL function "fun" statement 1 SELECT * FROM fun('a@b.com'); The function is not explicitly using the "users TABLE" type, and doesn't use at all the "column_that_we_will_drop". If the function doesn't have a CTE, or if the CTE itself is not later on used, the error is not produced. This problem was originally pointed out by @Andy at http://dba.stackexchange.com/questions/153981/postgresql-column-renaming-reordering-and-side-effects-on-sql-functions/158749?noredirect=1#comment304595_158749. I managed to reproduce it with a simpler version. The bug does not show using PostgreSQL version 9.4.10. [I have no other versions to check.] -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
joan@sanchezsabe.com writes: > At this point, if we drop one column (not used at all by the previous > function)... > ALTER TABLE users > DROP COLUMN column_that_we_will_drop ; > ... this makes the next statement to generate an error > SELECT * FROM fun('a@b.com'); > ERROR: table row type and query-specified row type do not match > SQL state: 42804 > Detail: Query provides a value for a dropped column at ordinal position 3. > Context: SQL function "fun" statement 1 > SELECT * FROM fun('a@b.com'); I can't reproduce this in HEAD or 9.6 branch tip. I believe it was already fixed by this patch, which went in a bit after 9.6.1: https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22 But thanks for the report! regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs