Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#473 Search Path Propagation Issue

V1.1 maintenance
open
nobody
search_path (1)
2
2014-02-03
2014-01-31
David E. Wheeler
No

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.

Discussion

  • 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
    Koichi Suzuki
    2014-02-03

    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?

     
    • 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

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

         
        Last edit: David E. Wheeler 2014-02-03
  • Koichi Suzuki
    Koichi Suzuki
    2014-02-03

    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.

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