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:
- 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 <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
- 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 '**************'
);
- 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?