Extra data or data not completed in SELECT for hierarchical data in FOREIGN TABLE

[mkgrgis]

Hello @ibarwick. Thanks for Your foreign data program for firebird and fbsql! I have phantom data in simple hierarchical query, if it use firebird_fdw. The test enviroment for some tree data:

1. In firebird

   
   CREATE DATABASE '/tmp/Test.fdb' page_size 8192 user 'SYSDBA' password '**************' DEFAULT CHARACTER SET UTF8;

CREATE TABLE "fb_T" ( "i1" integer NOT NULL, "i0" integer, "t" character varying(16) );

INSERT INTO "fb_T" ("i1","i0", "t") VALUES (1,NULL,'a'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (2,1,'b'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (3,1,'c');; INSERT INTO "fb_T" ("i1","i0", "t") VALUES (4,2,'d'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (5,2,'e'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (6,4,'f'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (7,1,'g'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (8,7,'h'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (9,8,'i'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (10,6,'j'); INSERT INTO "fb_T" ("i1","i0", "t") VALUES (11,8,'k'); COMMIT;

SELECT * FROM "fb_T";

WITH RECURSIVE r_struct ( "i1", "i0", "t", "adr" ) AS ( SELECT T1."i1", T1."i0", T1."t", T1."t" "adr" FROM "fb_T" T1 WHERE T1."i0" is null UNION ALL SELECT T2."i1", T2."i0", T2."t", r_struct."adr" ||'->'|| T2."t" FROM "fb_T" T2 INNER JOIN r_struct ON (r_struct."i1" = T2."i0") ) SELECT * FROM r_struct ORDER BY "adr";

My correct result is

      i1           i0 t                adr              

============ ============ ================ ================ 1 a a
2 1 b a->b
4 2 d a->b->d
6 4 f a->b->d->f
10 6 j a->b->d->f->j
5 2 e a->b->e
3 1 c a->c
7 1 g a->g
8 7 h a->g->h
9 8 i a->g->h->i
11 8 k a->g->h->k

2. Than i prepare to use Your program by Your instruction.

-- Postgres, from user postgres

CREATE EXTENSION firebird_fdw;

CREATE FOREIGN DATA WRAPPER "firebird" HANDLER firebird_fdw_handler VALIDATOR firebird_fdw_validator;

CREATE SERVER "fb_Test" FOREIGN DATA WRAPPER "firebird" OPTIONS ( address 'localhost', database '/tmp/Test.fdb' );

GRANT USAGE ON FOREIGN SERVER "fb_Test" TO "pg_user_for_tests";

CREATE USER MAPPING FOR "pg_user_for_tests" SERVER "fb_Test" OPTIONS ( username 'sysdba', password '**' );

3. Foreign object for Firebird

-- Postgres, from pg_user_for_tests CREATE FOREIGN TABLE "pg_T" ( "i1" integer NOT NULL, "i0" integer, "t" character varying(16) ) SERVER "fb_Test" OPTIONS( query 'select * from "fb_T"');

SELECT * FROM "pg_T";

So, by `SELECT *  FROM "pg_T";` we have the same data from Firebird, no problems.
But by the same hierarchical query from Firebird we have phantom lines

WITH RECURSIVE r_struct ( "i1", "i0", "t", "adr" ) AS ( SELECT T1."i1", T1."i0", T1."t", T1."t"::text "adr" FROM "pg_T" T1 WHERE T1."i0" IS NULL UNION ALL SELECT T2."i1", T2."i0", T2."t", r_struct."adr" ||'->'|| T2."t" FROM "pg_T" T2 INNER JOIN r_struct ON (r_struct."i1" = T2."i0") ) SELECT * FROM r_struct ORDER BY "adr";

**11 lines in Firebird query, 11 lines in `SELECT * FROM "pg_T"`, but 33 lines in hierarchical query!**

WITH RECURSIVE r_struct ( "i1", "i0", "t", "adr" ) AS ( SELECT T1."i1", T1."i0", T1."t", T1."t"::text "adr" FROM "pg_T" T1 WHERE T1."i0" IS NULL UNION ALL SELECT T2."i1", T2."i0", T2."t", r_struct."adr" ||'->'|| T2."t" FROM "pg_T" T2 INNER JOIN r_struct ON (r_struct."i1" = T2."i0") ) SELECT * FROM r_struct ORDER BY "adr";

So, if it is problem of postgres or my query? No. There is test with the same data and the same query.

-- Postgres, from pg_user_for_tests -- The same data !!! CREATE TABLE "pg_T_1" AS SELECT * FROM "pg_T";

-- The same query !!! WITH RECURSIVE r_struct ( "i1", "i0", "t", "adr" ) AS ( SELECT T1."i1", T1."i0", T1."t", T1."t"::text "adr" FROM "pg_T_1" T1 WHERE T1."i0" IS NULL UNION ALL SELECT T2."i1", T2."i0", T2."t", r_struct."adr" ||'->'|| T2."t" FROM "pg_T_1" T2 INNER JOIN r_struct ON (r_struct."i1" = T2."i0") ) SELECT * FROM r_struct ORDER BY "adr";

Than i have the same result as in Firebird!

i1 | i0 | t | adr
----+----+---+--------------- 1 | | a | a 2 | 1 | b | a->b 4 | 2 | d | a->b->d 6 | 4 | f | a->b->d->f 10 | 6 | j | a->b->d->f->j 5 | 2 | e | a->b->e 3 | 1 | c | a->c 7 | 1 | g | a->g 8 | 7 | h | a->g->h 9 | 8 | i | a->g->h->i 11 | 8 | k | a->g->h->k

In this test we have extra lines, but i saw the situation with not completed data. I'm sorry, there is no test for it.
Thanks for attention! How can i help?

[ibarwick]

Your problem is here:

-- Postgres, from pg_user_for_tests
CREATE FOREIGN TABLE "pg_T" (
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  query 'select * from "fb_T"');

where you're creating the foreign table with the query option, which means (probably) that the planner is getting a fixed dataset rather than an iterable table and hence the strange results you're seeing.

Create the table like this:

CREATE FOREIGN TABLE "pg_T" (
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

and it should work as expected.

[mkgrgis]

Thank You for Your time, @ibarwick ! A problem with names

CREATE FOREIGN TABLE "pg_T0" (
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  query 'select * from "fb_T"');

SELECT *
  FROM "pg_T0";

=> OK

CREATE FOREIGN TABLE "pg_T1" (
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

SELECT *
  FROM "pg_T1";

=> Column unknown Detail: I1

CREATE FOREIGN TABLE "pg_T2" (
  I1 integer OPTIONS (column_name 'i1') NOT NULL,
  I0 integer OPTIONS (column_name 'i0'),
  T  character varying(16) OPTIONS (column_name 't')
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

SELECT *
  FROM "pg_T2";

=> Column unknown Detail: I1

CREATE FOREIGN TABLE "pg_T3" (
  I1 integer OPTIONS (column_name 'I1') NOT NULL,
  I0 integer OPTIONS (column_name 'I0'),
  T  character varying(16) OPTIONS (column_name 'T')
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

SELECT *
  FROM "pg_T3";

=> Column unknown Detail: I1

CREATE FOREIGN TABLE "pg_T4" (
  I1 integer OPTIONS (column_name '"i1"') NOT NULL,
  I0 integer OPTIONS (column_name '"i0"'),
  T  character varying(16) OPTIONS (column_name '"t"')
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

SELECT *
  FROM "pg_T4";

=> Column unknown Detail: i1

CREATE FOREIGN TABLE "pg_T5" (
  I1 integer OPTIONS (column_name '"I1"') NOT NULL,
  I0 integer OPTIONS (column_name '"I0"'),
  T  character varying(16) OPTIONS (column_name '"T"')
)
       SERVER "fb_Test"
OPTIONS(
  table_name 'fb_T');

SELECT *
  FROM "pg_T5";

=> Column unknown Detail: I1

What is the right variant of quoting and case? Uppercase "I1" is unknown and lowercase "i1" is unknown...

Without quoting TaBlEnAmE is "tablename" in PostgreSQL and "TABLENAME" in FireBird. With quoting any names are equal in PostgreSQL and FireBird.

[ibarwick]

I'll have to point you to the item in the "Limitations" section of the README file which states:

No consideration given to object names which may require quoting when passed between PostgreSQL and Firebird

Not this is not strictly true, as currently firebird_fdw will pass identifiers which need to be quoted according to PostgreSQL's identifier rules to Firebird as quoted identifiers, but does not do that for Firebird identifiers.

So if you have a foreign table column defined like this:

id int OPTIONS (column_name 'Id')

firebird_fdw will pass that as "Id" to Firebird; but if the foreign table column defined like this:

id int OPTIONS (column_name 'id')

firebird_fdw will pass that as id to Firebird (which of course assumes it's ID rather than "id").

The same mechanism causes any quotes supplied to be taken as a literal part of the column name so you end up with the query executed on Firebird like this:

SELECT """i1""", """i0""", """t""" FROM "fb_T"

which obviously doesn't work.

I'll see if there's a way of handling this better, but can't guarantee anything.

[ibarwick]

Your problem is here:

-- Postgres, from pg_user_for_tests
CREATE FOREIGN TABLE "pg_T" (
  "i1" integer NOT NULL,
  "i0" integer,
  "t"  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  query 'select * from "fb_T"');

where you're creating the foreign table with the query option, which means (probably) that the planner is getting a fixed dataset rather than an iterable table and hence the strange results you're seeing.

FYI I've located the underlying issue here and applied a fix.

[mkgrgis]

Test In Firebird

SQL> CREATE TABLE fb_T
CON> (
CON>   i1 integer NOT NULL,
CON>   i0 integer,
CON>   t  character varying(16)
CON> );
SQL> 
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (1,NULL,'a');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (2,1,'b');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (3,1,'c');;
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (4,2,'d');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (5,2,'e');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (6,4,'f');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (7,1,'g');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (8,7,'h');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (9,8,'i');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (10,6,'j');
SQL> INSERT INTO fb_T (i1,i0, t) VALUES (11,8,'k');
SQL> COMMIT;
SQL> 
SQL> SELECT *
CON>   FROM fb_T;

          I1           I0 T                
============ ============ ================ 
           1       <null> a                
           2            1 b                
           3            1 c                
           4            2 d                
           5            2 e                
           6            4 f                
           7            1 g                
           8            7 h                
           9            8 i                
          10            6 j                
          11            8 k                

SQL> 
SQL>   WITH RECURSIVE r_struct ( i1, i0, t, adr ) AS (
CON> SELECT T1.i1, 
CON>        T1.i0,
CON>    T1.t,
CON>        T1.t adr 
CON>   FROM fb_T T1
CON>  WHERE T1.i0 is null 
CON>  UNION ALL
CON> SELECT T2.i1, 
CON>        T2.i0,
CON>        T2.t,
CON>        r_struct.adr ||'->'|| T2.t
CON>   FROM fb_T T2
CON>  INNER JOIN r_struct
CON>     ON (r_struct.i1 = T2.i0)
CON> )
CON> SELECT * 
CON>   FROM r_struct
CON>  ORDER BY adr;

          I1           I0 T                ADR              
============ ============ ================ ================ 
           1       <null> a                a                
           2            1 b                a->b             
           4            2 d                a->b->d          
           6            4 f                a->b->d->f       
          10            6 j                a->b->d->f->j    
           5            2 e                a->b->e          
           3            1 c                a->c             
           7            1 g                a->g             
           8            7 h                a->g->h          
           9            8 i                a->g->h->i       
          11            8 k                a->g->h->k

In Postgres, by test user

CREATE FOREIGN TABLE pg_t0 (
  i1 integer NOT NULL,
  i0 integer,
  t  character varying(16)
)
       SERVER "fb_Test"
OPTIONS(
  query 'select * from fb_t');
CREATE FOREIGN TABLE

Than

SELECT *                    
  FROM pg_t0;
 i1 | i0 | t 
----+----+---
  1 |    | a
  2 |  1 | b
  3 |  1 | c
  4 |  2 | d
  5 |  2 | e
  6 |  4 | f
  7 |  1 | g
  8 |  7 | h
  9 |  8 | i
 10 |  6 | j
 11 |  8 | k

And the main

WITH RECURSIVE r_struct ( i1, i0, t, adr ) AS (
SELECT T1.i1, 
       T1.i0,
       T1.t,
       T1.t::text adr
  FROM pg_t0 T1
 WHERE T1.i0 IS NULL 
 UNION ALL
SELECT T2.i1, 
       T2.i0,
       T2.t,
       r_struct.adr ||'->'|| T2.t
  FROM pg_t0 T2
 INNER JOIN r_struct
    ON (r_struct.i1 = T2.i0)
)
SELECT * 
  FROM r_struct
ORDER BY adr;

Result is

 i1 | i0 | t |      adr      
----+----+---+---------------
  1 |    | a | a
  2 |  1 | b | a->b
  4 |  2 | d | a->b->d
  6 |  4 | f | a->b->d->f
 10 |  6 | j | a->b->d->f->j
  5 |  2 | e | a->b->e
  3 |  1 | c | a->c
  7 |  1 | g | a->g
  8 |  7 | h | a->g->h
  9 |  8 | i | a->g->h->i
 11 |  8 | k | a->g->h->k
(11 строк)

Thanks, @ibarwick, it works fine! For uppercse/lowercase/mixed names we will continue in https://github.com/ibarwick/firebird_fdw/issues/15.