Hi!
I have Firebird-based system with Firebird DB. And I have data consumer that know (and able connect) about PostgreSQL only. Can this extension (firebird_fdw) helps me connect my postgres-only client to Firebird? Will it be realtime data access or have some delay?

Table definitions:

SQL> \d instest
						 Table "instest"
 Column | Field type  | Modifiers | Default value | Description
--------+-------------+-----------+---------------+-------------
 id     | INTEGER     |           |               |
 val    | VARCHAR(20) |           |               |


postgres=# \d fb_instest
					   Foreign table "repmgr.fb_instest"
 Column |         Type          | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+-------------
 id     | integer               |           |          |         |
 val    | character varying(20) |           |          |         |
Server: fb_test
FDW options: (table_name 'instest')

Insert attempts:

postgres=# insert into fb_instest values(1,'QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ');
ERROR:  value too long for type character varying(20)

postgres=# insert into fb_instest values(1,'なんじゃこれ');
INSERT 0 1

postgres=# insert into fb_instest values(1,'なんじゃこれあ');

server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>
!?> \q

Firebird counts bytes, not characters?

postgres=# select id, val, length(val) from fb_instest ;
 id |     val      | length
----+--------------+--------
  1 | foo          |      3
  1 | xxx          |      3
  1 | なんじゃこれ |      6
(3 rows)


SQL> SELECT id, val, char_length(val) from instest;
 id | val          | char_length
----+--------------+-------------
  1 | foo          |           3
  1 | xxx          |           3
  1 | なんじゃこれ |          18
(3 rows)
Time: 38.438 ms

This should at least result in an error being raised by Firebird and handled by the FDW; direct insert attempt:

SQL> INSERT INTO instest values(1,'なんじゃこれあ');
error executing non-SELECT
arithmetic exception, numeric overflow, or string truncation

Hi.

I run: yum install postgresql12-firebird_fdw but as a result i got that do not exist package to postgresql12-firebird_fdw

Could you see this to? https://copr.fedorainfracloud.org/coprs/ibarwick/firebird_fdw/packages/

Could you help me?

This is about SELECT * FROM firebird_version(); command.
There is

SELECT fs.oid, fs.srvname, um.umuser
  FROM pg_foreign_data_wrapper fdw
 INNER JOIN pg_catalog.pg_foreign_server fs
       ON fs.srvfdw = fdw.oid
 INNER JOIN pg_catalog.pg_user_mapping um
       ON um.umserver=fs.oid
 WHERE fdw.fdwname = 'firebird_fdw'

query in algorithm of firebird_version function.
By default pg_catalog.pg_user_mapping isn't accessible for a users without superuser's rights. Non privileged user get ERROR: no access to pg_user_mapping table.

Steps to reproduce:

1. Make user without superuser's rights in Pg13.
2. Make DB for this user as owner.
3. From superuser. Create in the new DB of new user firebird_fdwextension and foreign server and user mapping for the user with the server.
4. Try to execute SELECT * FROM firebird_version();.

Feature request or discussion.

I have found some examples

• Firebird array IO operations
  https://github.com/FirebirdSQL/firebird/blob/master/examples/api/api10.c

• PostgreSQL array IO operations
  https://stackoverflow.com/questions/20396546/create-simple-c-function-for-postgresql-with-array-input-and-array-output
  https://doxygen.postgresql.org/arrayfuncs_8c.html

Firebird:

CREATE TABLE "!IE" (
	"c₀" INTEGER NOT NULL,
	"c₁" INTEGER NOT NULL
);

Sample data for Firebird
Hierarchy.txt
Some first lines is here.

SQL> SELECT * FROM "!IE";

          c₀           c₁ 
============ ============ 
         394          300 
         395          300 
         396          394 
         397          395 
         398          300 
         399          398 

Query for Firebird

WITH RECURSIVE r AS (
SELECT p."c₀",
       0 AS level,       
       CAST (p."c₀" AS VARCHAR(256)) AS ADR 
 FROM "!IE" p
WHERE p."c₁" = 12
UNION ALL 
SELECT p."c₀",
      r.level+1 AS level,  
      r.ADR || ' ' || p."c₀" AS ADR
 FROM "!IE" p 
 JOIN r
   ON p."c₁" = r."c₀"
)
SELECT * FROM r;

No problems. First lines of result

          c₀        LEVEL ADR                                                                                                                                                                                                                                                              
======= ======= ============================ 
         241            0 241                                                                                                                                                                                                                                                              
         481            1 241 481                                                                                                                                                                                                                                                          
         482            2 241 481 482                                                                                                                                                                                                                                                      
         483            3 241 481 482 483                                                                                                                                                                                                                                                  
         484            3 241 481 482 484                                                                                                                                                                                                                                                  
         485            3 241 481 482 485                                                                                                                                                                                                                                                  
         486            3 241 481 482 486                                                                                                                                                                                                                                                  
         487            3 241 481 482 487                                                                                                                                                                                                                                                  
         488            3 241 481 482 488                                                                                                                                                                                                                                                  
         526            1 241 526                                                                                                                                                                                                                                                          
       14288            2 241 526 14288                                                                                                                                                                                                                                                    
       14286            3 241 526 14288 14286                                                                                                                                                                                                                                              
       14287            3 241 526 14288 14287                                                                                                                                                                                                                                              
       14338            1 241 14338                                                                                                                                                                                                                                                        
       14566            1 241 14566             

and what we have in PostgreSQL 13?

CREATE FOREIGN TABLE hier(
c₀      INTEGER OPTIONS (column_name 'c₀'),
c₁	INTEGER OPTIONS (column_name 'c₁')
)
SERVER "fb_Test"
OPTIONS ( table_name '!IE' );

The query select * from hier; is similar to query for Firebird.
But for query

WITH RECURSIVE r AS (
SELECT p.c₀,
       0 AS level,       
       p.c₀::text AS ADR 
 FROM hier p
WHERE p.c₁ = 12
UNION ALL 
SELECT p.c₀,
      r.level+1 AS level,  
      r.ADR || ' ' || p.c₀ AS ADR
 FROM hier p 
 JOIN r
   ON p.c₁ = r.c₀
)
SELECT * FROM r;

we get only levels 0 and 1 - this is the problem.

  c₀   | level |    adr    
-------+-------+-----------
   241 |     0 | 241
   481 |     1 | 241 481
 14566 |     1 | 241 14566
   242 |     1 | 241 242
 14338 |     1 | 241 14338
   245 |     1 | 241 245
   323 |     1 | 241 323
   526 |     1 | 241 526
   244 |     1 | 241 244
   243 |     1 | 241 243
   246 |     1 | 241 246

Final test for pg query. If we execute create table hier2 as select * from hier; and rewrite query to hier2, there is no problems with all levels.

  c₀   | level |                           adr                           
-------+-------+---------------------------------------------------------
   241 |     0 | 241
   481 |     1 | 241 481
   526 |     1 | 241 526
 14338 |     1 | 241 14338
 14566 |     1 | 241 14566
   242 |     1 | 241 242
   243 |     1 | 241 243
   244 |     1 | 241 244
   245 |     1 | 241 245
   246 |     1 | 241 246
   323 |     1 | 241 323
   482 |     2 | 241 481 482
   582 |     2 | 241 244 582
 14288 |     2 | 241 526 14288
 14515 |     2 | 241 245 14515
 14568 |     2 | 241 14566 14568
   247 |     2 | 241 242 247
   248 |     2 | 241 242 248
   268 |     2 | 241 243 268
   273 |     2 | 241 244 273
   483 |     3 | 241 481 482 483
   484 |     3 | 241 481 482 484

When I create the foreign table with the option 'query' I get the error:
   ERROR: Query returned unexpected number of rows
Ps: The query returns thousands of rows.

We use firebird_fdw to connect our postgres instance to a fairly large-sized firebird instance(s). When one of our users makes a query that calls a significantly-large dataset, the query will spin and eventually cause a memory leak that will cause oomkiller to terminate the process. Doing that results in the database crashing and going into recovery mode. We are investigating ways to mitigate this, including communicating with users to appropriately limit their queries to something that will return, but try as we might, people write queries they shouldn't. Doing so shouldn't result in a crash of the database.

We have similar queries every now and again in our postgres fdws, but they seem to handle themselves much better. I am curious if part of the reason why is that postgres_fdw has the fetch_size parameter that allows you to limit the number of rows that are pulled in a single query. Doing that seems to slow things down enough for us to be able to catch the issue and deal with it properly before things spiral out of control affecting other users, or allows postgres to manage it more appropriately in memory. Either way, I'm wondering if that feature can be implemented in firebird_fdw, or if some other means of managing memory usage can be created.

Let me know what, if any, log files or error messages I can provide.

This is not real issue, but discussion about quote_identifiers to continue #15 (comment).

I think it will be simple for refactoring, if IMPORT FOREIGN SCHEMA behaviour with LIMIT TO and EXCEPT will be managed by servel-level option quote_identifiers.
By ISO basal SQL behaviour with object's identifier is quoting letter to letter between different DBMSs. So, real Firebird or PostgreSQL behaviour (if there is no quoting characters "name" ) by ISO is modification of this basal behaviour. If we consider to automate IMPORT FOREIGN SCHEMA, we needn't precise naming management for separate tables or columns. At this moment user know naming style of Firebird database as value of option quote_identifiers. So, it is possible not to analyse the case of the object name if we know the value of quote_identifiers.

If quote_identifiers= false we always have a risk of unacessed lowercase name of table or column, but no way to provide some options for separate tables or columns for export.

Hi

As the maintainer of firebird_fdw, I'd be very interested to know how and why people are using it. Answers to some or all of the following questions would be highly appreciated; feel free to contact me at barwick {at} gmail.com if you don't want to leave a reply here.

• Please describe how you use firebird_fdw.
• Which PostgreSQL version(s) do you use it with?
• Which Firebird version(s) do you use it with?
• Are there any features you'd like to see?

Any other constructive feedback is more than welcome.

• Firebird_fdw 1.3.0
• PostgreSQL 14

The name of server is "fb テスト".
Console output after set client_min_messages='debug3';

DEBUG:  CREATE FOREIGN TABLE fbTest."ab" (
	AB INTEGER,
	"Ab" INTEGER,
	"aB" INTEGER OPTIONS (quote_identifier 'true'),
	"ab" INTEGER OPTIONS (quote_identifier 'true'),
	"c" BOOLEAN OPTIONS (quote_identifier 'true')
) SERVER fb テスト
OPTIONS(
	quote_identifier 'true'
)
DEBUG:  entering function fb_xact_callback
DEBUG:  closing remote transaction on connection 0x563c60e00a10
DEBUG:  ROLLBACK
DEBUG:  leaving fb_xact_callback()
ERROR: syntax (near: "テスト")
行 7: ) SERVER fb テスト
                  ^
QUERY:  CREATE FOREIGN TABLE fbTest.AB (
	AB INTEGER,
	"Ab" INTEGER,
	"aB" INTEGER OPTIONS (quote_identifier 'true'),
	"ab" INTEGER OPTIONS (quote_identifier 'true'),
	"c" BOOLEAN OPTIONS (quote_identifier 'true')
) SERVER fb テスト

Hi,

PostgreSQL 17 Beta1 is out. firebird_fdw fails to build against v17. I had to edit Makefile to add 17, but still:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -flto=auto -ffat-lto-objects -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-U_FORTIFY_SOURCE,-D_FORTIFY_SOURCE=3 -Wp,-D_GLIBCXX_ASSERTIONS -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -fstack-protector-strong -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -march=x86-64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fno-omit-frame-pointer -mno-omit-leaf-frame-pointer -fPIC -fvisibility=hidden -I. -I./ -I/usr/pgsql-17/include/server -I/usr/pgsql-17/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -DWITH_GZFILEOP -I/usr/include -c -o src/firebird_fdw.o src/firebird_fdw.c
src/firebird_fdw.c: In function ‘firebirdGetForeignPaths’:
src/firebird_fdw.c:1224:26: error: too few arguments to function ‘create_foreignscan_path’
1224 | create_foreignscan_path(root, baserel,
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from src/firebird_fdw.c:50:
/usr/pgsql-17/include/server/optimizer/pathnode.h:125:21: note: declared here
125 | extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
| ^~~~~~~~~~~~~~~~~~~~~~~

Can you please take a look?

Thanks!

Cheers, Devrim

Firebird: 2.5.9
Postgres: 14.5
Firebird_Fdw: latest

After importing my tables via the following script:

CREATE EXTENSION IF NOT EXISTS firebird_fdw;

DROP SERVER IF EXISTS firebird_server CASCADE;
CREATE SERVER IF NOT EXISTS firebird_server
    FOREIGN DATA WRAPPER firebird_fdw
    OPTIONS (
        address :host,
        port :port,
        database :file
    );

CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER 
    SERVER firebird_server
    OPTIONS(username :user, password :password);

DROP SCHEMA IF EXISTS _fdw CASCADE;
CREATE SCHEMA IF NOT EXISTS _fdw;

IMPORT FOREIGN SCHEMA :schema
    FROM SERVER firebird_server
    INTO _fdw;

And trying to select the data from the imported table:

SELECT * FROM "_fdw".table_name;

I get the following error:

SQL Error [HV00L]: ERROR: no column definitions provided for foreign table table_name

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=73508475d69e90f98ebd9b7e1a5933a26a49c5e9 has removed has removed pg_atoi

As a result packaged firebird_fdw cannot be loaded into PostgreSQL 15:

postgres=# create extension firebird_fdw;
ERROR:  could not load library "/usr/pgsql-15/lib/firebird_fdw.so": /usr/pgsql-15/lib/firebird_fdw.so: undefined symbol: pg_atoi

Similar problem impacted postgis → https://trac.osgeo.org/postgis/ticket/5100
The patch provided might suggest sollution

FIREBIRD VERSION: 2.5.3
POSTGRE VERSION: 10.11

Some forewords:
I'm using this firebird_fdw to query data from a database for which I have no control over how the data is stored nor it's structure, also I'm not a C developer, I can compile libs and understand the code, but not much else.

I've set up the data wrapper and have been querying the database for almost 3 months now, so it works but I had to write the queries converting every varchar to ISO-8859-1 with convert_from(COLUMNNAME::BYTEA, 'ISO8859-1') which is annoying but works fine for me for now...

I'm still trying to fix this to avoid having to deal with the conversion in the future, I tried forcing LATIN1 or ISO8859-1 (and even NONE since it's the original fb database's default) into the client_encoding on firebirdGetConnection() inside connection.c , by changing the lines:

val[i] = GetDatabaseEncodingName(); to val[i] = 'LATIN1'; or val[i] = 'ISO8859-1';

to see if maybe I could try and implement a flag to force the encoding on the connection,
but data still comes jumbled,

DBEAVER will show the following exception on the collumn: "DBCException: SQL Error [22000]: Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database."

PSQL will just ignore the character and show the rest of the string, like this query for the name of the contact:

NOME_CONTATO | NOME_CONTATO::BYTEA | convert_from(NOME_CONTATO::BYTEA, 'ISO8859-1')
ANDRIA | \x414e4452c94941 | ANDRÉIA |

I'm kind of stuck in the mud here, any tips would be appreciated, even some guidance on testing the issue

Hi,

1.3.0 fails to build on Fedora 37 (GCC 12). Same warnings appear on RHEL 9 (GCC 11), but build does not fail:

Can you please take a look? Thanks!

I./ -I/usr/pgsql-15/include/server -I/usr/pgsql-15/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o src/firebird_fdw.bc src/firebird_fdw.c
/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Xclang -no-opaque-pointers -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -Wno-deprecated-non-prototype -O2 -I/usr/include/firebird -I. -I./ -I/usr/pgsql-15/include/server -I/usr/pgsql-15/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o src/options.bc src/options.c
src/firebird_fdw.c: In function 'firebirdExecForeignBatchInsert':
src/firebird_fdw.c:2555:18: warning: implicit declaration of function 'FQprepare'; did you mean 'SPI_prepare'? [-Wimplicit-function-declaration]
2555 | result = FQprepare(fmstate->conn,
| ^~~~~~~~~
| SPI_prepare
src/firebird_fdw.c:2555:16: warning: assignment to 'FBresult *' from 'int' makes pointer from integer without a cast [-Wint-conversion]
2555 | result = FQprepare(fmstate->conn,
| ^
src/firebird_fdw.c:2569:26: warning: implicit declaration of function 'FQexecPrepared' [-Wimplicit-function-declaration]
2569 | result = FQexecPrepared(fmstate->conn,
| ^~~~~~~~~~~~~~
src/firebird_fdw.c:2569:24: warning: assignment to 'FBresult *' from 'int' makes pointer from integer without a cast [-Wint-conversion]
2569 | result = FQexecPrepared(fmstate->conn,
| ^
src/firebird_fdw.c:2581:9: warning: implicit declaration of function 'FQdeallocatePrepared' [-Wimplicit-function-declaration]
2581 | FQdeallocatePrepared(fmstate->conn, result);
| ^~~~~~~~~~~~~~~~~~~~
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -flto=auto -ffat-lto-objects -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -fstack-protector-strong -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -shared -o firebird_fdw.so src/connection.o src/convert.o src/firebird_fdw.o src/options.o -L/usr/pgsql-15/lib -Wl,-z,relro -Wl,--as-needed -Wl,-z,now -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -Wl,--build-id=sha1 -L/usr/lib64 -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-15/lib',--enable-new-dtags -lfq -lfbclient
src/firebird_fdw.c:2555:11: warning: call to undeclared function 'FQprepare'; ISO C99 and later do not support implicit function declarations [-Wimplicit-function-declaration]
result = FQprepare(fmstate->conn,
^
src/firebird_fdw.c:2555:9: error: incompatible integer to pointer conversion assigning to 'FBresult *' (aka 'struct FBresult *') from 'int' [-Wint-conversion]
result = FQprepare(fmstate->conn,
^ ~~~~~~~~~~~~~~~~~~~~~~~~
src/firebird_fdw.c:2569:12: warning: call to undeclared function 'FQexecPrepared'; ISO C99 and later do not support implicit function declarations [-Wimplicit-function-declaration]
result = FQexecPrepared(fmstate->conn,
^
src/firebird_fdw.c:2569:10: error: incompatible integer to pointer conversion assigning to 'FBresult *' (aka 'struct FBresult *') from 'int' [-Wint-conversion]
result = FQexecPrepared(fmstate->conn,
^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
src/firebird_fdw.c:2581:2: warning: call to undeclared function 'FQdeallocatePrepared'; ISO C99 and later do not support implicit function declarations [-Wimplicit-function-declaration]
FQdeallocatePrepared(fmstate->conn, result);
^
3 warnings and 2 errors generated.
make[1]: *** [/usr/pgsql-15/lib/pgxs/src/makefiles/../../src/Makefile.global:1080: src/firebird_fdw.bc] Error 1

Hello, i would like very much to know if there is any option in this project that enables me to force the conversion of string to a specify charset when running IMPORT SCHEMA?
If not, is there any alternatives? Or would you recommend me somewhere to start to implement this feature?

Thank you very much,
Vitor Hideyoshi.

Hi,

I'm getting the error below while compiling 1.2.0 on all Fedora releases. Can you please take a look? I can build packages fine on RHEL 7 and 8.

FWIW, Fedora 31 uses gcc 9.3, and Fedora 32 and 33 use gcc 10.

Thanks! Devrim

/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/include/firebird -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/options.bc src/options.c
/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/include/firebird -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/firebird_fdw.bc src/firebird_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -shared -o firebird_fdw.so src/connection.o src/convert.o src/options.o src/firebird_fdw.o -L/usr/pgsql-12/lib   -L/usr/lib64  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-12/lib',--enable-new-dtags  -lfq -lfbclient 
/usr/bin/ld: src/convert.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: multiple definition of `opttype'; src/connection.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: first defined here
/usr/bin/ld: src/options.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: multiple definition of `opttype'; src/connection.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: first defined here
/usr/bin/ld: src/firebird_fdw.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: multiple definition of `opttype'; src/connection.o:/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/firebird_fdw/main/firebird_fdw-1.2.0/src/firebird_fdw.h:70: first defined here
collect2: error: ld returned 1 exit status

I tried to install the library on both version 14 and 15 on debian and ubuntu. I always get the same message

postgres=# create extension firebird_fdw; ERROR: could not load library "/usr/lib/postgresql/15/lib/firebird_fdw.so": libfq-0.5.0.so: cannot open shared object file: No such file or directory

The libfq-0.5.0.so library installed without errors and is in /usr/local/lib

whereis libfq-0.5.0.so

libfq-0.5.0: /usr/local/lib/libfq-0.5.0.so`

I have no more ideas for this error

the postgresql10-firebird_fdw package requires libfq-0.2.0.so dependency. But the libfq current version is 0.3.0 (updated 28.09.18). Thus it is impossible to install the postgresql10-firebird_fdw package from repo.

Resolving Dependencies
--> Running transaction check
---> Package postgresql10-firebird_fdw.x86_64 0:0.3.0-1 will be installed
--> Processing Dependency: libfq for package: postgresql10-firebird_fdw-0.3.0-1.x86_64
--> Processing Dependency: libfq-0.2.0.so()(64bit) for package: postgresql10-firebird_fdw-0.3.0-1.x86_64
--> Running transaction check
---> Package libfq.x86_64 0:0.3.0-1 will be installed
---> Package postgresql10-firebird_fdw.x86_64 0:0.3.0-1 will be installed
--> Processing Dependency: libfq-0.2.0.so()(64bit) for package: postgresql10-firebird_fdw-0.3.0-1.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql10-firebird_fdw-0.3.0-1.x86_64 (ibarwick-firebird_fdw)
Requires: libfq-0.2.0.so()(64bit)

Installing package libfq.x86_64 0:0.3.0-1 does not solve the problem, since libfq-0.3.0.so file is being installed.

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       <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       

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?

Hello, @ibarwick!

You have powerful inspired me for C FDW contributing. Some hours ago I have implemented UUID support for sqlite_fdw and check SQLite to Firebird data transport. Unfortunately there is some problems. Let's test.

Firebird gives us some UUID functions. Recommended special storage mode is CHAR(16) CHARACTER SET OCTETS.

Firebird

CREATE TABLE "UUID" ("UUID" CHAR(16) CHARACTER SET OCTETS NOT NULL);
-- Many times, 15-20 examples
INSERT INTO "UUID" ("UUID")  
SELECT gen_uuid() FROM rdb$database;

PostgreSQL

CREATE FOREIGN TABLE "uuid" (
  "UUID" uuid
)
SERVER firebird_server OPTIONS (table_name 'UUID', quote_identifier 'true');

First problem
During select * from uuid; there is error with malformed input.

Note: for text stored UUIDs there is no SELECT problems
Firebird

CREATE TABLE "UUIDt" ("UUID" VARCHAR (40) NOT NULL);
-- Many times, 15-20 examples
INSERT INTO "UUIDt" ("UUID")
SELECT uuid_to_char(gen_uuid()) FROM rdb$database;

PostgreSQL

CREATE FOREIGN TABLE "uuid text"(
  "UUID" uuid
)
SERVER firebird_server
OPTIONS (table_name 'UUIDt', quote_identifier 'true');

Sample listing

select * from "uuid text";
                 UUID                 
--------------------------------------
 91f8b0f1-db13-44f0-91c8-71a1acc402cf
 da89d679-8d3a-4a81-8027-f1a0af0dd38a
 29138623-a57f-44eb-9dc1-25641c8e66a1
 09e570c0-367a-4249-af30-62ff4667afe0
(4 行)

Second problem
Let's SELECT from text stored UUID column

select * from "uuid text" where "UUID" = '91f8b0f1-db13-44f0-91c8-71a1acc402cf';
 UUID 
------
(0 行)
select * from "uuid text" where "UUID" = '91F8B0F1-DB13-44F0-91C8-71A1ACC402CF';
 UUID 
------
(0 行)

Normalized FirebirdSQL form for UUID look like
91F8B0F1-DB13-44F0-91C8-71A1ACC402CF
Normalized PostgreSQL form for UUID look like
91f8b0f1-db13-44f0-91c8-71a1acc402cf

Before WHERE PostgreSQL normalize UUID value. Hence firebird_fdw should make upper transformation.

My help
I have resolved a problem with malformed UUID input from SQLite blob affinity and binding PostgreSQL UUID value to SQLite blob. Some solution there is in https://github.com/mkgrgis/sqlite_fdw/blob/draft_uuid/sqlite_query.c You can borrow this code.

Also you can point me to some lines in firebird_fdw code where i can try to add UUID support.

Many thanks for the long and hard work with firebird_fdw code!

Hello,

I have compiled and installed on FreeBSD 12 with PG 11.3.
When try to activate the fdw extension obtain:

ERROR:  could not load library "/usr/local/lib/postgresql/firebird_fdw.so": dlopen (/usr/local/lib/postgresql/firebird_fdw.so) failed: /usr/local/lib/libfq-0.4.1.so: Undefined symbol "isc_modify_dpb"	

Any idea?

P.S. I use fbsql on the same machine without problem

Thanks
Franco

Any query to a foreign table was returning:
"ERROR: Unable to to connect to foreign server - error 1"

Eventually, i commented out in function firebirdGetConnection (from connection.c) the lines:

kw[i] = "client_encoding";
val[i] = GetDatabaseEncodingName();
i++;

After that the query ran fine.
FYI, the value returned by GetDatabaseEncodingName() was UTF8.

Hi @ibarwick ,

V16 beta1 is out, and firebird_fdw fails to compile against it. Can you please take a look? Thanks! Devrim

/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Xclang -no-opaque-pointers -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -O2  -I/usr/include/firebird -I. -I./ -I/usr/pgsql-16/include/server -I/usr/pgsql-16/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/options.bc src/options.c
src/firebird_fdw.c: In function 'firebirdPlanForeignModify':
src/firebird_fdw.c:2103:31: warning: implicit declaration of function 'bms_first_member'; did you mean 'bms_is_member'? [-Wimplicit-function-declaration]
 2103 |                 while ((col = bms_first_member(tmpset)) >= 0)
      |                               ^~~~~~~~~~~~~~~~
      |                               bms_is_member
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -flto=auto -ffat-lto-objects -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-U_FORTIFY_SOURCE,-D_FORTIFY_SOURCE=3 -Wp,-D_GLIBCXX_ASSERTIONS -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -fstack-protector-strong -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fno-omit-frame-pointer -mno-omit-leaf-frame-pointer -fPIC -fvisibility=hidden -shared -o firebird_fdw.so src/connection.o src/convert.o src/firebird_fdw.o src/options.o -L/usr/pgsql-16/lib  -Wl,-z,relro -Wl,--as-needed  -Wl,-z,now -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1  -Wl,--build-id=sha1   -L/usr/lib64  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-16/lib',--enable-new-dtags -fvisibility=hidden -lfq -lfbclient 
src/firebird_fdw.c:2103:17: error: call to undeclared function 'bms_first_member'; ISO C99 and later do not support implicit function declarations [-Wimplicit-function-declaration]
                while ((col = bms_first_member(tmpset)) >= 0)
                              ^
src/firebird_fdw.c:2103:17: note: did you mean 'bms_is_member'?
/usr/pgsql-16/include/server/nodes/bitmapset.h:92:13: note: 'bms_is_member' declared here
extern bool bms_is_member(int x, const Bitmapset *a);
            ^
1 error generated.

Firebird (2.5, Linux)

CREATE TABLE "fb_t" ("i" SMALLINT, "n" DOUBLE PRECISION);
INSERT INTO "fb_t" ("i", "n") VALUES (0, 0.45454);
INSERT INTO "fb_t" ("i", "n") VALUES (1, 3.1415296);
INSERT INTO "fb_t" ("i", "n") VALUES (0, 2.7182818);

De facto smallint i={0|1}, pseudo-bool;

Postgres (12, Linux)

CREATE EXTENSION firebird_fdw;
CREATE FOREIGN DATA WRAPPER firebird  HANDLER firebird_fdw_handler  VALIDATOR firebird_fdw_validator;

CREATE SERVER firebird_server
  FOREIGN DATA WRAPPER firebird
  OPTIONS <...>

CREATE USER MAPPING <...>

CREATE FOREIGN TABLE "fb_probe"(
  "i" BOOL OPTIONS (column_name 'i'),
  "n" DOUBLE OPTIONS (column_name 'n')
)
SERVER firebird_server
OPTIONS(
  table_name 'fb_t'
);

SELECT * FROM "fb_probe" WHERE "i";
=> ERROR!

SELECT * FROM "fb_probe" WHERE NOT "i";
=> ERROR!

Hi,

v14 entered beta. 1.2.1 fails to build against v14. Below is the log. Can you please take a look?

Regards, Devrim

/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/usr/include/firebird -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o src/options.bc src/options.c
src/firebird_fdw.c: In function 'firebird_fdw_handler':
src/firebird_fdw.c:760:45: warning: assignment to 'AddForeignUpdateTargets_function' {aka 'void (*)(PlannerInfo *, unsigned int, RangeTblEntry *, struct RelationData )'} from incompatible pointer type 'void ()(Query *, RangeTblEntry *, struct RelationData )' [-Wincompatible-pointer-types]
760 | fdwroutine->AddForeignUpdateTargets = firebirdAddForeignUpdateTargets;
| ^
src/firebird_fdw.c: In function 'firebirdBeginForeignModify':
src/firebird_fdw.c:2320:80: error: 'ModifyTableState' has no member named 'mt_plans'
2320 | mtstate->mt_plans[subplan_index]->plan,
| ^~
src/firebird_fdw.c: In function 'firebirdBeginForeignInsert':
src/firebird_fdw.c:2766:65: error: 'ModifyTableState' has no member named 'mt_whichplan'
2766 | resultRelInfo > mtstate->resultRelInfo + mtstate->mt_whichplan)
| ^~
make[1]: *** [: src/firebird_fdw.o] Error 1
make[1]: *** Waiting for unfinished jobs....
src/firebird_fdw.c:760:38: warning: incompatible function pointer types assigning to 'AddForeignUpdateTargets_function' (aka 'void ()(struct PlannerInfo *, unsigned int, struct RangeTblEntry *, struct RelationData *)') from 'void (Query *, RangeTblEntry *, Relation)' (aka 'void (struct Query *, struct RangeTblEntry *, struct RelationData *)') [-Wincompatible-function-pointer-types]
fdwroutine->AddForeignUpdateTargets = firebirdAddForeignUpdateTargets;
^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
src/firebird_fdw.c:2320:19: error: no member named 'mt_plans' in 'struct ModifyTableState'
mtstate->mt_plans[subplan_index]->plan,
~~~~~~~ ^
src/firebird_fdw.c:2766:53: error: no member named 'mt_whichplan' in 'struct ModifyTableState'
resultRelInfo > mtstate->resultRelInfo + mtstate->mt_whichplan)
~~~~~~~ ^
1 warning and 2 errors generated.
make[1]: *** [/usr/pgsql-14/lib/pgxs/src/makefiles/../../src/Makefile.global:1051: src/firebird_fdw.bc] Error 1

Port option for foreign gerver + some notes about debian/ubuntu from 4ce1f1a ( inside of #14 ).

Is this module still considered experiemental?
Is it still being maintained?

Continue for tests from #13.

There is no correct form in PostgreSQL for this Firebird table

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

My propositions for You

1. New options allowed for foreign server, table and column "preserve_table_case" (for server and foreign table) and "preserve_column_case"(for server, foreign table and column). Options must have overlapping "preserve_table_case" for a table and overlapping "preserve_column_case" for a column.
2. Allow quoting in literal of options "table_name" and "column_name" with new c error "quote not closed". Will it rational?

What is recommended default behaviour by ISO if case not preserved? All PostgreSQL names uppercased to Firebird, all Firebird names lowercased to PostgreSQL. Transformation of character by ISO must pass "turkish test" by current locale of PostgreSQL database. If locale=tr_TR there is special toupper/tolower rules.

As discussed by lots and lots of people, the “I” in Turkish behaves differently than in most languages. Per the Unicode standard, our lowercase “i” becomes “İ” (U+0130 “Latin Capital Letter I With Dot Above”) when it moves to uppercase. Similarly, our uppercase “I” becomes “ı” (U+0131 “Latin Small Letter Dotless I”) when it moves to lowercase.

See http://www.moserware.com/2008/02/does-your-code-pass-turkey-test.html

To libfq 0.4.3.