Thread: Another thought about search_path semantics
Back in 9.2 (commit 880bfc328) we decided that nonexistent schemas listed in search_path should be silently ignored, reasoning by analogy with Unix PATH settings where nonexistent directories in the path don't result in error reports. This remains imperfect though, cf commit 15386281a and the similar report today at http://www.postgresql.org/message-id/533ED1EC.4080102@abshere.net It strikes me that the real issue here is that the analogy to PATH is fine for search_path's role as a *search* path, but it's not so good for determining the creation target schema. I wonder if we should further redefine things so that the creation target schema is always the first thing named in search_path, and if that doesn't exist, we throw an error rather than silently creating in some schema further down the list. Thoughts? regards, tom lane
Hi, On 2014-04-04 13:33:59 -0400, Tom Lane wrote: > It strikes me that the real issue here is that the analogy to PATH is > fine for search_path's role as a *search* path, but it's not so good for > determining the creation target schema. I wonder if we should further > redefine things so that the creation target schema is always the first > thing named in search_path, and if that doesn't exist, we throw an > error rather than silently creating in some schema further down the > list. Wouldn't that devolve into an even messier behaviour because of the historical "$user",public search path? I wonder if we could extend the search path syntax to specify whether a schema should be used for creation of objects or not. Sounds somewhat nasty, but I don't really have a better idea :(. Something like search_patch=public,!pg_catalog. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-04 13:33:59 -0400, Tom Lane wrote:
>> It strikes me that the real issue here is that the analogy to PATH is
>> fine for search_path's role as a *search* path, but it's not so good for
>> determining the creation target schema. I wonder if we should further
>> redefine things so that the creation target schema is always the first
>> thing named in search_path, and if that doesn't exist, we throw an
>> error rather than silently creating in some schema further down the
>> list.
> Wouldn't that devolve into an even messier behaviour because of the
> historical "$user",public search path?
Ugh, right. I think we had this discussion before actually, I'd just
forgotten it.
> I wonder if we could extend the search path syntax to specify whether a
> schema should be used for creation of objects or not. Sounds somewhat
> nasty, but I don't really have a better idea :(. Something like
> search_patch=public,!pg_catalog.
Hm ... doesn't fix the problem for existing dump files, which are going to
say "search_path = foo, pg_catalog". However, we could modify it a bit,
so that the marker is put on schemas that can be skipped if missing for
creation purposes. Then the default could look like "search_path =
!$user, public", while we still get safe behavior for pg_dump's commands.
regards, tom lane
On 04/04/2014 01:47 PM, Andres Freund wrote: > I wonder if we could extend the search path syntax to specify whether a > schema should be used for creation of objects or not. Sounds somewhat > nasty, but I don't really have a better idea :(. Something like > search_patch=public,!pg_catalog. No, if we're fixing this, then we should have a separate "creation_target_schema" GUC. The fact that the only way to designate creation target schema was to put it at the start of the search path has *always* been a problem, since 7.3. Non-atomic data sucks. ;-b -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 2014-04-04 13:58:53 -0400, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > I wonder if we could extend the search path syntax to specify whether a > > schema should be used for creation of objects or not. Sounds somewhat > > nasty, but I don't really have a better idea :(. Something like > > search_patch=public,!pg_catalog. > > Hm ... doesn't fix the problem for existing dump files, which are going to > say "search_path = foo, pg_catalog". However, we could modify it a bit, > so that the marker is put on schemas that can be skipped if missing for > creation purposes. Then the default could look like "search_path = > !$user, public", while we still get safe behavior for pg_dump's commands. Unfortunately the curren tsearch_path is probably enshrined in a couple of thousand postgresql.confs... How about simply refusing to create anything in pg_catalog unless it's explicitly schema qualified? Looks a bit nasty to implement but doable? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-04 13:58:53 -0400, Tom Lane wrote:
>> Hm ... doesn't fix the problem for existing dump files, which are going to
>> say "search_path = foo, pg_catalog". However, we could modify it a bit,
>> so that the marker is put on schemas that can be skipped if missing for
>> creation purposes. Then the default could look like "search_path =
>> !$user, public", while we still get safe behavior for pg_dump's commands.
> Unfortunately the curren tsearch_path is probably enshrined in a couple
> of thousand postgresql.confs...
Uncommented? Anyway, we never have and never will promise that you don't
have to revisit your postgresql.conf during a major version upgrade.
> How about simply refusing to create anything in pg_catalog unless it's
> explicitly schema qualified? Looks a bit nasty to implement but doable?
That's what happens already. The point is to do better. What we want
for pg_dump's case is to get a complaint that schema foo doesn't exist,
*not* an attempt to create in pg_catalog. That's what you got (though
at the SET command not the CREATE command) in all versions before 9.2.
regards, tom lane
On 2014-04-04 14:13:43 -0400, Tom Lane wrote: > > How about simply refusing to create anything in pg_catalog unless it's > > explicitly schema qualified? Looks a bit nasty to implement but doable? > > That's what happens already. The point is to do better. What we want > for pg_dump's case is to get a complaint that schema foo doesn't exist, > *not* an attempt to create in pg_catalog. That's what you got (though > at the SET command not the CREATE command) in all versions before 9.2. I was thinking - but not saying explicitly - of rigging things so that pg_catalog is ignored when searching for the target schema for object creation unless explicitly specified. So if there's no other schema in the search path you'd get the error about no "no schema has been selected to create in", even if pg_catalog is somewhere in there. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Josh Berkus <josh@agliodbs.com> writes:
> No, if we're fixing this, then we should have a separate
> "creation_target_schema" GUC. The fact that the only way to designate
> creation target schema was to put it at the start of the search path has
> *always* been a problem, since 7.3.
Well, if we were doing this in a green field we might do that, but we
don't have a green field. 7.3 was released in 2002. We need to find
some reasonably upward-compatible reinterpretation of what pg_dump
has been doing since then.
I'm not saying that we couldn't *also* invent a creation_target_schema
GUC, as long as its default setting means "consult search_path for the
schema to use". What I'm saying is that having such a GUC won't solve
the existing problem for existing dump files. (And in fact, a non-default
setting of it could completely break existing dump files, so we'd have
to tread carefully.)
regards, tom lane
Andres Freund <andres@2ndquadrant.com> writes:
> I was thinking - but not saying explicitly - of rigging things so that
> pg_catalog is ignored when searching for the target schema for object
> creation unless explicitly specified. So if there's no other schema in
> the search path you'd get the error about no "no schema has been
> selected to create in", even if pg_catalog is somewhere in there.
Hm. Seems pretty grotty, but it'd at least fix pg_dump's problem,
since pg_dump's lists are always "foo, pg_catalog" with no third
schema mentioned. I think what we'd actually need is to say
"pg_catalog cannot be selected as the creation target unless it's
the *first* entry in the search_path list".
The larger issue here is that if search_path is say "a, b, c" and
"a" doesn't exist, is it really sane to create in "b" instead?
Another relatively narrow fix we could consider is to revert to
treating "$user" specially, such that it can be skipped over if
nonexistent for the purpose of selecting a creation target, but
actual schema names cannot be.
regards, tom lane
On 2014-04-04 14:32:46 -0400, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > I was thinking - but not saying explicitly - of rigging things so that > > pg_catalog is ignored when searching for the target schema for object > > creation unless explicitly specified. So if there's no other schema in > > the search path you'd get the error about no "no schema has been > > selected to create in", even if pg_catalog is somewhere in there. > > Hm. Seems pretty grotty, but it'd at least fix pg_dump's problem, > since pg_dump's lists are always "foo, pg_catalog" with no third > schema mentioned. I think what we'd actually need is to say > "pg_catalog cannot be selected as the creation target unless it's > the *first* entry in the search_path list". I was actually suggesting that the only way to create something in pg_catalog is to do it with a explicit schema qualified id. I realize that that's not something backpatchable... > The larger issue here is that if search_path is say "a, b, c" and > "a" doesn't exist, is it really sane to create in "b" instead? I think "$user" really nailed that behaviour down. Everything else just seems really confusing. We could of course make that behave special as you suggest, but yuck. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-04 14:32:46 -0400, Tom Lane wrote:
>> Hm. Seems pretty grotty, but it'd at least fix pg_dump's problem,
>> since pg_dump's lists are always "foo, pg_catalog" with no third
>> schema mentioned. I think what we'd actually need is to say
>> "pg_catalog cannot be selected as the creation target unless it's
>> the *first* entry in the search_path list".
> I was actually suggesting that the only way to create something in
> pg_catalog is to do it with a explicit schema qualified id. I realize
> that that's not something backpatchable...
I don't find that to be a good idea at all. pg_dump is probably not the
only code that believes it can select a creation target with search_path,
no matter what that target is.
As for back-patchability, I was initially thinking of only fixing this in
HEAD. If the behavior change is small enough, maybe we could get away
with back-patching 9.2 and 9.3; but I don't think we should start with
the assumption that we must do that.
regards, tom lane
On 2014-04-04 14:56:54 -0400, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > I was actually suggesting that the only way to create something in > > pg_catalog is to do it with a explicit schema qualified id. I realize > > that that's not something backpatchable... > > I don't find that to be a good idea at all. pg_dump is probably not the > only code that believes it can select a creation target with search_path, > no matter what that target is. Sure, but how many of those are trying to put things in pg_catalog? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-04 14:56:54 -0400, Tom Lane wrote:
>> I don't find that to be a good idea at all. pg_dump is probably not the
>> only code that believes it can select a creation target with search_path,
>> no matter what that target is.
> Sure, but how many of those are trying to put things in pg_catalog?
Maybe not many, but pg_dump itself certainly can try to do that.
(Most of the time, pg_dump won't dump things in pg_catalog, but there
are exceptions, eg --binary-upgrade dump of an extension containing
objects in pg_catalog.)
regards, tom lane
On 2014-04-04 17:24:00 -0400, Tom Lane wrote: > Andres Freund <andres@2ndquadrant.com> writes: > > On 2014-04-04 14:56:54 -0400, Tom Lane wrote: > >> I don't find that to be a good idea at all. pg_dump is probably not the > >> only code that believes it can select a creation target with search_path, > >> no matter what that target is. > > > Sure, but how many of those are trying to put things in pg_catalog? > > Maybe not many, but pg_dump itself certainly can try to do that. > (Most of the time, pg_dump won't dump things in pg_catalog, but there > are exceptions, eg --binary-upgrade dump of an extension containing > objects in pg_catalog.) If we're not backpatching, fixing that seems easy enough? pg_upgrade definitely needs the pg_dump around, so that should be fine. I don't like my own suggestion, which isn't a good sign, but I haven't heard anything I like more :(. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-04-04 17:24:00 -0400, Tom Lane wrote:
>> Maybe not many, but pg_dump itself certainly can try to do that.
>> (Most of the time, pg_dump won't dump things in pg_catalog, but there
>> are exceptions, eg --binary-upgrade dump of an extension containing
>> objects in pg_catalog.)
> If we're not backpatching, fixing that seems easy enough?
Not especially. As I said, pg_dump believes that setting search_path is
an appropriate way to control where things get created, and that's wired
into its structure pretty deeply. I would have exactly zero faith in a
hack that tried to change that just for objects in pg_catalog. In any
case, it's not clear that the case can't arise in pre-existing dump files,
even if you discount --binary-upgrade cases.
> I don't like my own suggestion, which isn't a good sign, but I haven't
> heard anything I like more :(.
Don't see why you don't find what I suggested to be just a small variant
on it.
regards, tom lane