pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedset search_path command
4+ messages / 4 participants
[nested] [flat]
* set search_path command
@ 2014-06-02 21:50 Radovan Jablonovsky <radovan.jablonovsky@replicon.com>
2014-06-02 21:58 ` Re: set search_path command Vibhor Kumar <vibhor.aim@gmail.com>
2014-06-02 22:00 ` Re: set search_path command David G Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Radovan Jablonovsky @ 2014-06-02 21:50 UTC (permalink / raw)
To: pgsql-admin
Hello,
When we compared behaviour of "set search_path" between 9.1.9 and 9.3.4,
there is difference. On 9.1.9 the command "set search_path to
schema1,public" checks if the schema exists and if not it return error.
Example:
set search_path to "schema123",public;
ERROR: invalid value for parameter "search_path": "schema123, public"
DETAIL: schema "schema123" does not exist
On 9.3.4 the same command silently ignore the schema does not exists.
Example:
set search_path to "schema123",public;
SET
select * from pg_namespace where nspname ilike '%schema123%';
nspname | nspowner | nspacl
---------+----------+--------
(0 rows)
Is this new behaviour intentional?
Sincerely,
--
*Radovan Jablonovsky* | SaaS DBA | Phone 1-403-262-6519 (ext. 256) | Fax
1-403-233-8046
* Replicon | Hassle-Free Time & Expense Management Software - 7,300
Customers - 70 Countrieswww.replicon.com
<http://www.replicon.com/; | facebook
<http://www.facebook.com/Replicon.inc; | twitter
<http://twitter.com/Replicon; | blog
<http://www.replicon.com/blog/; | contact us
<http://www.replicon.com/about_replicon/contact_us.aspx; We are
hiring! | search jobs
<http://tbe.taleo.net/NA2/ats/careers/searchResults.jsp?org=REPLICON&cws=1&act=sort&sortC...;
*
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set search_path command
2014-06-02 21:50 set search_path command Radovan Jablonovsky <radovan.jablonovsky@replicon.com>
@ 2014-06-02 21:58 ` Vibhor Kumar <vibhor.aim@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Vibhor Kumar @ 2014-06-02 21:58 UTC (permalink / raw)
To: Radovan Jablonovsky <radovan.jablonovsky@replicon.com>; +Cc: pgsql-admin
On Monday, June 2, 2014 at 5:50 PM, Radovan Jablonovsky wrote:
> When we compared behaviour of "set search_path" between 9.1.9 and 9.3.4, there is difference. On 9.1.9 the command "set search_path to schema1,public" checks if the schema exists and if not it return error. Example:
> set search_path to "schema123",public;
> ERROR: invalid value for parameter "search_path": "schema123, public"
> DETAIL: schema "schema123" does not exist
>
>
> On 9.3.4 the same command silently ignore the schema does not exists. Example:
> set search_path to "schema123",public;
> SET
>
> select * from pg_namespace where nspname ilike '%schema123%';
> nspname | nspowner | nspacl
> ---------+----------+--------
> (0 rows)
I think so. It was fixed in 9.2.
E.14.3.1.7. Server Settings
Silently ignore nonexistent schemas specified in search_path (Tom Lane) :
This makes it more convenient to use generic path settings, which might include some schemas that don't exist in all databases.
Thanks & Regards,
Vibhor Kumar
Blogs:http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set search_path command
2014-06-02 21:50 set search_path command Radovan Jablonovsky <radovan.jablonovsky@replicon.com>
@ 2014-06-02 22:00 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-02 23:15 ` Re: set search_path command Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: David G Johnston @ 2014-06-02 22:00 UTC (permalink / raw)
To: pgsql-admin
Radovan Jablonovsky wrote
> Hello,
>
> When we compared behaviour of "set search_path" between 9.1.9 and 9.3.4,
> there is difference. On 9.1.9 the command "set search_path to
> schema1,public" checks if the schema exists and if not it return error.
> Example:
> set search_path to "schema123",public;
> ERROR: invalid value for parameter "search_path": "schema123, public"
> DETAIL: schema "schema123" does not exist
>
> On 9.3.4 the same command silently ignore the schema does not exists.
> Example:
> set search_path to "schema123",public;
> SET
> select * from pg_namespace where nspname ilike '%schema123%';
> nspname | nspowner | nspacl
> ---------+----------+--------
> (0 rows)
>
> Is this new behaviour intentional?
Yes. It was felt the 9.1 behavior was too punishing. I do not recall the
specifics at the moment but I believe 9.0- and 9.2+ simply ignore the
missing schema while 9.1 has the obnoxious behavior that you see. Though if
any version other than 9.1 and 9.3 matter you should confirm this for
yourself.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/set-search-path-command-tp5805807p5805809.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set search_path command
2014-06-02 21:50 set search_path command Radovan Jablonovsky <radovan.jablonovsky@replicon.com>
2014-06-02 22:00 ` Re: set search_path command David G Johnston <david.g.johnston@gmail.com>
@ 2014-06-02 23:15 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2014-06-02 23:15 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
David G Johnston <david.g.johnston@gmail.com> writes:
> Radovan Jablonovsky wrote
>> Is this new behaviour intentional?
> Yes. It was felt the 9.1 behavior was too punishing. I do not recall the
> specifics at the moment but I believe 9.0- and 9.2+ simply ignore the
> missing schema while 9.1 has the obnoxious behavior that you see. Though if
> any version other than 9.1 and 9.3 matter you should confirm this for
> yourself.
A quick check says that an interactive "SET search_path = nosuchschema"
command fails in previous versions as well as 9.1. However, I believe that
noninteractive settings (eg, from postgresql.conf or ALTER DATABASE SET)
have always accepted paths including nonexistent schemas. This was felt
to be inconsistent; and also, IIRC, in 9.1 or 9.2 we had made some code
refactorings that made it difficult to sustain such inconsistent behavior.
So we decided to go for the laxer behavior in all cases rather than the
stricter behavior in all cases.
Another rationale for this change was the analogy between search_path
and Unix PATH settings. AFAIK, no Unix-oid platform anywhere will
complain if your PATH mentions a nonexistent directory.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-06-02 23:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-02 21:50 set search_path command Radovan Jablonovsky <radovan.jablonovsky@replicon.com>
2014-06-02 21:58 ` Vibhor Kumar <vibhor.aim@gmail.com>
2014-06-02 22:00 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-02 23:15 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox