PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

Probable bug on 9.6 and 9.6.1

This completely looks like a bug to me...

I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1).

CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    column_that_we_will_drop TEXT
) ;

-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
    (_useremail 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 = _useremail
RETURNING
    id
$$
LANGUAGE "sql" ;

After this setup, the next statement just works

SELECT * FROM __post_users('[email protected]');

At this point, we DROP one column:

ALTER TABLE users 
    DROP COLUMN column_that_we_will_drop ;

This change makes the next statement to generate an error

SELECT * FROM __post_users('[email protected]');

which is the same as mentioned by @Andy:

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 "__post_users" statement 1
    SELECT * FROM __post_users('[email protected]');

Dropping and recreating the function does NOT solve the problem.
VACUUM FULL (the table or the whole database) does not solve the problem.

---

The bug report was passed to the appropriate PostgreSQL mailing list and we had a very fast response:

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

---

Version 9.6.2

On 2017-03-06, I can confirm that I cannot reproduce this behaviour on version 9.6.2. That is, the bug seems to have been corrected on this release.

UPDATE

Per comment of @Jana: "I can confirm the bug is present in 9.6.1 and was fixed in 9.6.2. The fix is also listed on postgres release website: Fix spurious "query provides a value for a dropped column" errors during INSERT or UPDATE on a table with a dropped column"

---