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

If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method:

• Introduce 2 new columns as C and D2.

• Copy the contents of D to D2.

• Delete D.

• Rename D2 to D.

The new order would be A, B, C and D.

I thought this was a legitimate practice as (so far) it produced no issues.

However, today I came across a problem when a function carrying out a statement on the same table returned the following error:

table row type and query-specified row type do not match

And the following detail:

Query provides a value for a dropped column at ordinal position 13

I tried restarting PostgreSQL, doing a VACUUM FULL and finally deleting and re-creating the function as suggested here and here but these solutions did not work (aside from the fact that they try tackling a situation where a system table has been altered).

Having the luxury of working with a very small database I exported it, deleted it and then re-imported it and that fixed the issue with my function.

I was aware of the fact that one should not mess around with the natural order of columns by modifying system tables (getting hands dirty with pg_attribute, etc.) as seen here:

Is it possible to change the natural order of columns in Postgres?

Judging by the error thrown by my function I now realize that shifting the order of columns with my method is also a no-no. Can anyone shine some light as to why what I am doing is also wrong?

Postgres version is 9.6.0.

Here is the function:

```
CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '

-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),

-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
aut

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('a@b.com');

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('a@b.com');

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('a@b.com');

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"

StackExchange Database Administrators Q#153981, answer score: 16