Re: BUG #12553: Altering search_path between function calls

rs(at)plusw(dot)de writes:
> Bug reference: 12553
> Logged by: Rolf Schaufelberger
> Email address: rs(at)plusw(dot)de
> PostgreSQL version: 9.2.4
> Operating system: Ubuntu 4.4.3-4ubuntu5.1

[ this function acts funny if the search_path is changed between calls: ]

> create or replace function public.xxx( coid integer)
> returns integer as $$ DECLARE newid integer;
> BEGIN
> insert into company_objects(company_id, obj_class) values (coid, 'TEST')
> returning id into newid;
> return newid;
> END;
> $$ language plpgsql;

This example works as you're expecting in 9.3 and up, as per this 9.3
release note item:

Force cached plans to be replanned if the search_path changes (Tom Lane)

Previously, cached plans already generated in the current session
were not redone if the query was re-executed with a new
search_path setting, resulting in surprising behavior.

So in previous versions, the function latches onto whichever
company_objects table it sees during its first execution. Although that's
arguably a bug, we felt it was not safe to change the behavior so
fundamentally in minor releases. 9.2.x will not get changed in this way.

regards, tom lane