Adrian Klaver wrote:
>>> For the reason why this is happening see:
>>>
>>> http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>>
>> Yes, but the ALTER TABLE causes the plan to be recreated the next time.
>
> But does it? From the link above:
>
> "Because PL/pgSQL saves prepared statements and sometimes execution
> plans in this way, SQL commands that appear directly in a PL/pgSQL
> function must refer to the same tables and columns on every execution;
> that is, you cannot use a parameter as the name of a table or column in
> an SQL command. To get around this restriction, you can construct
> dynamic commands using the PL/pgSQL EXECUTE statement — at the price of
> performing new parse analysis and constructing a new execution plan on
> every execution."
>
> I see '*' as a parameter. Or to put it another way '*' is not referring
> to the same thing on each execution when you change the table definition
> under the function. Now if I can only get the brain to wake up I could
> find the post where Tom Lane explained this more coherently then I can:)
Session 1:
test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT NULL, to_be_removed integer NOT NULL);
CREATE TABLE
test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
$$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
Session 2:
test=> SELECT id, name FROM select_users(18);
id | name
----+------
(0 rows)
Ok, now the plan is cached.
Now in Session 1:
test=> ALTER TABLE users DROP COLUMN to_be_removed;
ALTER TABLE
Session2:
test=> SELECT id, name FROM select_users(18);
id | name
----+------
(0 rows)
No error. This is 9.4.4.
Yours,
Laurenz Albe