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