#473 Search Path Propagation Issue

[David E. Wheeler]

I have a script that looks like this:

BEGIN;
SET client_min_messages TO warning;
SET search_path TO geo;

CREATE TABLE continents (
    code      CHARACTER(2) PRIMARY KEY,
    continent TEXT         NOT NULL
);

I ran this against a coordinator, and it seemed to work. But when I tried to select from the table, XC said it could not be found. \d geo.continents shows it, but only on the coordinator on which I ran the script. If I connect to any other coordinator or any data node, the continents table is in the public schema rather than the geo schema.

Bug? I don't see any mentinon of SET not being distributed in the SET docs. I can work around it by schema-qualifying objects, but would rather not have to.

[David E. Wheeler]

Another search_path issue: If you have schema-qualified a data type in a table column, queries will fail against that table:

dwheeler=# create schema bar;
CREATE SCHEMA
dwheeler=# create type bar.lol as enum ('a', 'b');
CREATE TYPE
dwheeler=# create table bar.hey (lol bar.lol) DISTRIBUTE BY REPLICATION;
CREATE TABLE
dwheeler=# select * from bar.hey;
ERROR:  type "lol" does not exist

I guess the workaround would be to set the search_path to include all my schemas on in all the coordinator and data node postgresql.conf files?

[Koichi Suzuki]

For the first case, here's a result with current master (as of Feb.3rd) and 1.1.

koichi=# create schema geo;
CREATE SCHEMA
koichi=# set search_path to geo;
SET
koichi=# CREATE TABLE continents (
koichi(#            code      CHARACTER(2) PRIMARY KEY,
koichi(#            continent TEXT         NOT NULL
koichi(#             );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index      "continents_pkey" for table "continents"
CREATE TABLE
koichi=# select * from continents;
 code | continent 
------+-----------
(0 rows)

koichi=# insert into continents values (1, 'abc');
INSERT 0 1
koichi=# select * from continents;
 code | continent 
------+-----------
 1    | abc
(1 row)

koichi=# select * from geo.continents;
 code | continent 
------+-----------
 1    | abc
(1 row)

koichi=#

It seems to work. Can we share more specific issue you met?

[David E. Wheeler]

Ah, the problem is setting the role. This works:

$ cat schema.sql 
BEGIN;
SET client_min_messages TO warning;
CREATE SCHEMA geo AUTHORIZATION owner_references;
COMMIT;

$ psql -f schema.sql 
BEGIN
SET
CREATE SCHEMA
COMMIT

$ cat continents.sql 
BEGIN;
SET client_min_messages TO warning;
SET search_path TO geo;
-- SET ROLE TO owner_references;
CREATE TABLE continents (
    code      CHARACTER(2) PRIMARY KEY,
    continent TEXT         NOT NULL
) DISTRIBUTE BY REPLICATION;
COMMIT;

$ psql -f continents.sql 
BEGIN
SET
SET
CREATE TABLE
COMMIT

$ cat verify.sql 
BEGIN;
SELECT code, continent FROM geo.continents WHERE FALSE; 
ROLLBACK;

$ psql -f verify.sql 
BEGIN
 code | continent 
------+-----------
(0 rows)

ROLLBACK

However, if I uncomment the SET ROLE line in continents.sql, I instead get:

$ cat schema.sql 
BEGIN;
SET client_min_messages TO warning;
CREATE SCHEMA geo AUTHORIZATION owner_references;
COMMIT;

$ psql -f schema.sql 
BEGIN
SET
CREATE SCHEMA
COMMIT

$ cat continents.sql 
BEGIN;
SET client_min_messages TO warning;
SET search_path TO geo;
SET ROLE TO owner_references;
CREATE TABLE continents (
    code      CHARACTER(2) PRIMARY KEY,
    continent TEXT         NOT NULL
) DISTRIBUTE BY REPLICATION;
COMMIT;

$ psql -f continents.sql 
BEGIN
SET
SET
SET
CREATE TABLE
COMMIT

$ cat verify.sql 
BEGIN;
SELECT code, continent FROM geo.continents WHERE FALSE; 
ROLLBACK;

$ psql -f verify.sql 
BEGIN
psql:verify.sql:2: ERROR:  relation "geo.continents" does not exist
ROLLBACK

Cute, huh? Curiously, I don't seem to get this problem if I combine schemas.sql and continents.sql into a single file:

$ dropdb dwheeler
$ createdb dwheeler
$ cat schema.sql 
BEGIN;

SET client_min_messages TO warning;
CREATE SCHEMA geo AUTHORIZATION owner_references;

SET search_path TO geo;
SET ROLE TO owner_references;

CREATE TABLE continents (
    code      CHARACTER(2) PRIMARY KEY,
    continent TEXT         NOT NULL
) DISTRIBUTE BY REPLICATION;

COMMIT;

$ psql -f schema.sql 
BEGIN
SET
CREATE SCHEMA
SET
SET
CREATE TABLE
COMMIT

$ psql -c 'SELECT * FROM geo.continents where false'
 code | continent 
------+-----------
(0 rows)

Furthermore, note that the owner_references role, while I can set it as the current role, has no login privileges. It was created like so:

CREATE ROLE owner_references WITH NOLOGIN;

—David

[David E. Wheeler]

Ah, it also works if I call SET ROLE before SET search_path.

[Koichi Suzuki]

create schema bar;
CREATE SCHEMA
For the second case, I tested with 1.1 as follows:

koichi=# create type bar.lol as enum ('a', 'b');
CREATE TYPE
koichi=# create table bar.hey (lol bar.lol) DISTRIBUTE BY REPLICATION;
CREATE TABLE
koichi=# select * from bar.hey;
ERROR:  type "lol" does not exist
koichi=# set search_path to bar;
SET
koichi=# select * from bar.hey;
 lol 
-----
(0 rows)

koichi=#

It seems that the problem is not search_path propagation but handling aliases in the type.

Current master (as of 3rd Feb) had the same result. I don't think this is because the search_path is not propagated properly. Rather, the cause maybe XC schema does not handle type name qualification correctly.

[David E. Wheeler]

Thanks for the confirmation. Can you think of a workaround other than including the bar schema in the search path before executing the query?