Postgres Pro
Downloads
Home   >   mailing lists

Thread: Schema (Search path issue) on PostgreSQL9.2

Schema (Search path issue) on PostgreSQL9.2

From
chiru r
Date:
Hi All,

I have seen strange behaviour in PostgreSQL9.2 version,it has been allowing to set search path any name,even the name is not created as a schema in database.

Please find the below case between PostgreSQL9.1 and PostgreSQL9.2.

PostgreSQL9.2:
+++++++++++++

postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

postgres=# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
(5 rows)

postgres=# SET  search_path to  chiru92;
SET


PostgreSQL9.1:
++++++++++++

postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

postgres=# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
(5 rows)

postgres=# SET  search_path to  chiru91;
ERROR:  invalid value for parameter "search_path": "chiru91"
DETAIL:  schema "chiru91" does not exist


Please comment on below point.

Is there any schema(set search_path) behaviour changes from  PostgreSQL9.1 to PostgreSQL9.2 ?, or Is there any schema(set search_path) issue in PostgreSQL9.2 version?.

Thanks in Advance.

Best Regards,
Chiru

Re: Schema (Search path issue) on PostgreSQL9.2

From
Raghavendra
Date:

postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)

postgres=# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
(5 rows)


Apart from your actual question, am just curious to see this output, how come all schema's displayed whereas in latest releases only PUBLIC schema will be displayed if you use meta command \dn. 

-bash-4.1$ ./psql -p 5555
psql (9.3beta1)
Type "help" for help.

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

--Raghav


Re: Schema (Search path issue) on PostgreSQL9.2

From
Tom Lane
Date:
chiru r <chirupg@gmail.com> writes:
> Is there any schema(set search_path) behaviour changes from  PostgreSQL9.1
> to PostgreSQL9.2 ?

Please read the release notes when updating to a new major version.
The first item under "Server Settings" in the 9.2 release notes is:

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


            regards, tom lane