I didn't want to report the bug using the form before confirming it here.
Here is a gist of what I'm trying:
https://gist.github.com/49fcc8c4a5a810f66833#file-cleanup-sql
The relevant part being this:
old_path := pg_catalog.current_setting('search_path');
raise notice 'setting search_path from % to %', old_path, templ;
perform pg_catalog.set_config('search_path', templ, true);
...
select count(distinct transaction_id) from public.transaction_condition
into temp_count;
raise notice '% remaining transactions in public!', temp_count;
select count(distinct transaction_id) from transaction_condition into
temp_count;
raise notice '% remaining transactions', temp_count;
For which I get this result (NOTA is Portuguese word for NOTE or NOTICE,
not sure...):
NOTA: setting search_path from "$user",public to public
NOTA: 1030 remaining transactions in public!
NOTA: 66 remaining transactions
Why do I get different results for both count() queries? The only
difference between them is that I made the schema explicit in the first
call but since current_path is set to "public" there shouldn't be any
difference, right?
By the way, 66 is the record count for stock.transaction_condition after
calling that function the first time for the stock schema.
I've tested the above using PG 9.2.2. Any ideas on what is happening?
Thanks in advance,
Rodrigo.
