Re: invalid search_path complaints

On Tue, Apr 3, 2012 at 2:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > So we have an established precedent that it is right to warn about
> > things that are sketchy at the time that they are defined, but not
> > every time they are used.
>
> Sure, but we don't have that option available to us here --- or more
> accurately, ALTER USER/DATABASE SET *does* warn if the search_path value
> looks like it might be invalid according to the current context, but
> that helps little for this problem. What's important is whether the
> value is valid when we attempt to apply it.
>

> Basically, I don't think you've made a strong case for changing this
> behavior; nor have you explained what you think we should do instead.
>
> I have a legacy application now that relies heavily on multiple databases
and multiple schemas. The issue I have is that we have postgres deployed
very widely and have a cookie-cutter script for everything. We know for
example:

(each schema exists only in its respective DB)

user oltp should be able to see schemaA in db1, schemaB in db2 and
schemaC in db3
user reporting should be able to see biSchema in db1, reporting schema
in db2 and schemaC in db3

This is across a large multitude of databases and hosts. One of the
things I've loved about this is the ability to hide certain things from
users ( of course they can do a \dn and fully-qualify, which is why we have
permissions too, but I really appreciate the 'hidden-ness' of my tables ).

Because our schemas are all over the place, now I've got to setup a
hard-coded search_path in postgresql.conf which feels even worse to me than
the per-user setup.

Personally, I feel that if unix will let you be stupid:
$ export PATH=/usr/bin:/this/invalid/crazy/path
$ echo $PATH
/usr/bin:/this/invalid/crazy/path

PG should trust that I'll get where I'm going eventually :)

Just my two cents.

--Scott
OpenSCG
http://www.openscg.com

> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>