wrong search_path being used

From: Rodrigo Rosenfeld Rosas <rr(dot)rosas(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: wrong search_path being used
Date: 2013-01-07 18:50:17
Message-ID: 50EB18E9.8020202@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2013-01-07 19:17:27 Re: wrong search_path being used
Previous Message Tom Lane 2013-01-07 18:06:55 Re: BUG #7797: datetime + '1 month'::interval is going outside of a month's bounds