PostgreSQL search_path Behaviour
By Szymon Lipiński
November 15, 2012
PostgreSQL has a great feature: schemas. So you have one database with multiple schemas. This is a really great solution for the data separation between different applications. Each of them can use different schema, and they also can share some schemas between them.
I have noticed that some programmers tend to name the working schema as their user name. This is not a bad idea, however once I had a strange behaviour with such a solution.
I’m using user name szymon in the database szymon.
First let’s create a simple table and add some values. I will add one row with information about the table name.
# CREATE TABLE a (t TEXT);
# INSERT INTO a(t) VALUES ('This is table a');
Let’s check if the row is where it should be:
# SELECT t FROM a;
t
-----------------
This is table a
(1 row)
Now let’s create another schema and name it like my user’s name.
# CREATE SCHEMA szymon;
Let’s now create table a in the new schema.
# CREATE TABLE szymon.a (t TEXT);
So now there are two tables a in different schemas:
# SELECT t FROM pg_tables WHERE tablename = 'a';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
public | a | szymon | \N | f | f | f
szymon | a | szymon | \N | f | f | f
(2 rows)
I will just add a row similar to the previous one:
# INSERT INTO szymon.a (t) VALUES ('This is table szymon.a');
Let’s check the data in the table szymon.a:
# SELECT t FROM szymon.a;
t
------------------------
This is table szymon.a
(1 row)
OK, now I have all the data prepared for showing the quite interesting behaviour.
As you might see in the above queries, selecting table a when there is only one schema works. What’s more, selecting szymon.a works as well.
What will happen when I get data from the table a?
# SELECT t FROM a;
t
------------------------
This is table szymon.a
(1 row)
Suddenly PostgreSQL selects data from another table than at the beginning. The reason for this is the schema search mechanism.
There is a PostgreSQL environment variable search_path. If you set the value of this variable to x,a,public then PostgreSQL will look for all the tables, types, and function names in the schema x. If there is no such table in this schema, then it will look for this table in the next schema, which is a in this example. And finally it will look in the public schema.
What’s the default value of the search_path variable? You can check the current value of this variable with the following query:
# SHOW search_path;
search_path
----------------
"$user",public
(1 row)
The default search path makes PostgreSQL search first in the schema named exactly as the user name you used for logging into the database. If the user name is different from the schema names, or there is no table szymon.a then it would look in the public.a table.
The situation can be hard to understand because using simple EXPLAIN doesn’t help, as it shows only table name omitting the schema name. So the plan for this query looks exactly the same, regardless of the schema used:
# EXPLAIN SELECT * FROM a;
QUERY PLAN
------------------------------------------------------
Seq Scan on a (cost=0.00..1.01 rows=1 width=32)
(1 row)
To see a plan with more information you should use EXPLAIN VERBOSE to see the plan with schema name, so it will be easier to spot the usage of a different schema:
# EXPLAIN VERBOSE SELECT * FROM a;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on szymon.a (cost=0.00..1.01 rows=1 width=32)
Output: t
(2 rows)
Comments