FAQ
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.

Search Discussions

  • Tom Lane at Jan 7, 2013 at 7:17 pm

    Rodrigo Rosenfeld Rosas writes:
    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;
    If this is inside a plpgsql function that's been executed more than
    once, the SELECTs would have plans that were cached the first time
    around, so that what would matter is the search_path that prevailed
    during the first execution. There have been discussions about changing
    that but we wouldn't treat it as a back-patchable bug fix, because
    it would almost certainly break things for somebody.

        regards, tom lane
  • Rodrigo Rosenfeld Rosas at Jan 7, 2013 at 7:26 pm

    Em 07-01-2013 17:17, Tom Lane escreveu:
    Rodrigo Rosenfeld Rosas<rr.rosas@gmail.com> writes:
    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;
    If this is inside a plpgsql function that's been executed more than
    once, the SELECTs would have plans that were cached the first time
    around, so that what would matter is the search_path that prevailed
    during the first execution. There have been discussions about changing
    that but we wouldn't treat it as a back-patchable bug fix, because
    it would almost certainly break things for somebody.
    So, Tom, if I understand it correctly, you do consider it a bug but you
    don't want to backport a fix because it might break existent behavior in
    some dbs, right?

    But it is not clear to me if you're willing to fix it for 9.2.3 for
    instance?

    It is likely I'll be creating lots of functions to perform the same
    operations in different schemas as my application is being designed to
    handle multiple field templates using separate schemas and the
    application will switch what templates are used based on current_path
    but lots of database migrations (not sure if you know what I mean for
    that, but I'm using Rails web framework terminology here) will perform
    the same changes to the database for each of the supported
    schemas/templates.

    So, I'd really like this behavior to be fixed in future versions of PG
    if possible.

    What is your opinion on the subject?

    Thanks for the prompt answer!

    Cheers,
    Rodrigo.
  • Kevin Grittner at Jan 9, 2013 at 10:09 pm

    Rodrigo Rosenfeld Rosas wrote:
    Tom Lane wrote:
    There have been discussions about changing that
    if I understand it correctly, you do consider it a bug but you
    don't want to backport a fix because it might break existent
    behavior in some dbs, right?
    No, there has been discussion about whether different behavior
    would be better in future major releases, but no consensus has been
    reached.
    but we wouldn't treat it as a back-patchable bug fix, because
    it would almost certainly break things for somebody.
    But it is not clear to me if you're willing to fix it for 9.2.3
    for instance?
    Back-patching means changing things in a minor release, where
    things only change after the second dot. We don't make changes in
    user-visible behavior like this in minor releases; so no, we would
    not make a change like this in 9.2.3 or any other 9.2 version.

    -Kevin
  • Rodrigo Rosenfeld Rosas at Jan 10, 2013 at 1:09 am

    Em 09-01-2013 20:09, Kevin Grittner escreveu:
    Rodrigo Rosenfeld Rosas wrote:
    Tom Lane wrote:
    There have been discussions about changing that
    if I understand it correctly, you do consider it a bug but you
    don't want to backport a fix because it might break existent
    behavior in some dbs, right?
    No, there has been discussion about whether different behavior
    would be better in future major releases, but no consensus has been
    reached.
    but we wouldn't treat it as a back-patchable bug fix, because
    it would almost certainly break things for somebody.
    But it is not clear to me if you're willing to fix it for 9.2.3
    for instance?
    Back-patching means changing things in a minor release, where
    things only change after the second dot. We don't make changes in
    user-visible behavior like this in minor releases; so no, we would
    not make a change like this in 9.2.3 or any other 9.2 version.
    Ok, thanks for the explanation, Kevin.

    I'm curious though. Why wouldn't this behavior be considered a bug? Is
    there any link to previous discussions about this subject that I could read?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJan 7, '13 at 6:50p
activeJan 10, '13 at 1:09a
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase