Failed to Import Table from Foreign Schema

[HideyoshiNakazone]

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

[ibarwick]

I need further details to be able to reproduce this; please provide:

• the definition of table_name in Firebird
• the output of \d+ "_fdw".table_name in PostgreSQL

Thanks

[ibarwick]

Also if you could retry the IMPORT FOREIGN SCHEMA with client_min_messages set to debug3 and report the output, it might provide some useful information.

[HideyoshiNakazone]

I need further details to be able to reproduce this; please provide:

• the definition of table_name in Firebird
• the output of \d+ "_fdw".table_name in PostgreSQL

Thanks

Table Definition:

CREATE TABLE TABLE_NAME (
    COLUMN_1 INTEGER NOT NULL,
    COLUMN_2 VARCHAR(30) NOT NULL,
    COLUMN_3 TIMESTAMP NOT NULL,
    COLUMN_4 DOUBLE PRECISION DEFAULT 0 NOT NULL,
    COLUMN_5 TIMESTAMP NOT NULL,
    COLUMN_6 VARCHAR(1),
    COLUMN_7 BLOB SUB_TYPE TEXT,
    COLUMN_8 SMALLINT GENERATED ALWAYS AS (EXTRACT(MONTH FROM COLUMN_3))
);

PostgreSQL Output

# \d+ "_fdw".table_name
                                  Foreign table "_fdw.table_name"
 Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+------+-----------+----------+---------+-------------+---------+--------------+-------------
Server: firebird_server

[HideyoshiNakazone]

@ibarwick, thank you very much for the quick response and availability to help. May i ask a question? If the Firebird database stores its data in ANSI and my PostgreSQL database uses UTF-8 will these cause any problem?

[mkgrgis]

@HideyoshiNakazone there is PR about encodings transformations. If your ANSI encoding described in the metadata of Firebird database there is full automated transformation of all text data, marked by DDL.

[HideyoshiNakazone]

@mkgrgis and @ibarwick, after running the IMPORT FOREIGN SCHEMA with SET client_min_messages = 'debug3'; i got the following log:

object: table_name r
importing table 'table_name'
object_name: table_name; table_name: table_name; pg_name: NULL
CREATE FOREIGN TABLE _fdw.table_name (
) SERVER firebird_server

After some checking the character encoding is in the Firebird Metadata. I'm really confused in why didn't the FDW even find a table definition. If i'm missing a really obvious solution i'm really sorry.

[HideyoshiNakazone]

I also tested creating the foreign table manually and it worked:

CREATE FOREIGN TABLE _fdw.table_name (
    COLUMN_1 INTEGER NOT NULL,
    COLUMN_2 VARCHAR(30) NOT NULL,
    COLUMN_3 TIMESTAMP NOT NULL,
    COLUMN_4 DOUBLE PRECISION DEFAULT 0 NOT NULL,
    COLUMN_5 TIMESTAMP NOT NULL,
    COLUMN_6 VARCHAR(1),
    COLUMN_7 TEXT,
    COLUMN_8 SMALLINT
) SERVER firebird_server;

But the problem is that i have 668 tables in this firebird database and and 330 tables failed importing and had similar exists as this one.

@mkgrgis and @ibarwick, after running the IMPORT FOREIGN SCHEMA with SET client_min_messages = 'debug3'; i got the following log:

object: table_name r
importing table 'table_name'
object_name: table_name; table_name: table_name; pg_name: NULL
CREATE FOREIGN TABLE _fdw.table_name (
) SERVER firebird_server

After some checking the character encoding is in the Firebird Metadata. I'm really confused in why didn't the FDW even find a table definition. If i'm missing a really obvious solution i'm really sorry.

[ibarwick]

Character encoding etc. is probably not an issue with the schema import (unless the Firebird table names contain non-ASCII characters).

Best I can tell from the available information is that the Firebird metadata query which returns the column definitions is not returning any rows for some tables, resulting in the "empty" PostgreSQL foreign table definitions. I can't reproduce that on Firebird 3.0.x or 4.0.x; I don't have access to 2.5.x at the moment.

You reported this debug output:

object: table_name r
importing table 'table_name'
object_name: table_name; table_name: table_name; pg_name: NULL

Is that the verbatim output? I ask because at this point the table names should be reported as stored in the Firebird metadata, where they would normally be upper case, e.g.:

DEBUG:  object: TABLE_NAME r
INFO:  importing table 'TABLE_NAME'
DEBUG:  object_name: TABLE_NAME; table_name: TABLE_NAME; pg_name: NULL

If you had created the table in Firebird explicitly with a lower-case name, i.e. CREATE TABLE "table_name" the debug output would be like this:

DEBUG:  object: table_name r
INFO:  importing table 'table_name'
DEBUG:  object_name: table_name; table_name: "table_name"; pg_name: NULL

(note the additional quotes around "table_name") so I am wondering how you got the exact output you reported.

Anyway to try and dig deeper into the issue, it would be helpful if you could execute the below query with the name of one of the failing tables, i.e. replace TABLE_NAME in this part of the query: WHERE TRIM(rf.rdb$relation_name) = 'TABLE_NAME' with the exact name of the table as stored in the Firebird catalog (which is the value in single quotes reported by firebird_fdw when it emits INFO: importing table 'TABLE_NAME').

SELECT TRIM(rf.rdb$field_name) AS column_name,
       f.rdb$field_type,
       CASE f.rdb$field_type
         WHEN 261 THEN
           CASE f.rdb$field_sub_type
             WHEN 1 THEN 'TEXT'
             ELSE 'BYTEA'
           END
         WHEN 14  THEN 'CHAR(' || f.rdb$field_length|| ')'
         WHEN 40  THEN 'CSTRING'
         WHEN 11  THEN 'D_FLOAT'
         WHEN 27  THEN 'DOUBLE PRECISION'
         WHEN 10  THEN 'FLOAT'
         WHEN 16  THEN
           CASE f.rdb$field_sub_type
             WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             ELSE 'BIGINT'
           END
         WHEN 8   THEN
           CASE f.rdb$field_sub_type
             WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             ELSE 'INTEGER'
           END
         WHEN 9   THEN 'QUAD'
         WHEN 7   THEN
           CASE f.rdb$field_sub_type
             WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')'
             ELSE 'SMALLINT'
           END
         WHEN 12  THEN 'DATE'
         WHEN 13  THEN 'TIME'
         WHEN 35  THEN 'TIMESTAMP'
         WHEN 37  THEN 'VARCHAR(' || f.rdb$field_length|| ')'
         WHEN 23  THEN 'BOOLEAN'
         ELSE 'UNKNOWN'
       END AS data_type,
      COALESCE(CAST(rf.rdb$default_source AS VARCHAR(80)), '')
        AS "Default value",
      rf.rdb$null_flag AS null_flag,
      COALESCE(CAST(rf.rdb$description AS VARCHAR(80)), '')
        AS "Description"
   FROM rdb$relation_fields rf
 LEFT JOIN rdb$fields f
     ON rf.rdb$field_source = f.rdb$field_name
  WHERE TRIM(rf.rdb$relation_name) = 'TABLE_NAME' -- table name as stored in Firebird catalog
  ORDER BY rf.rdb$field_position;

[HideyoshiNakazone]

Is that the verbatim output? I ask because at this point the table names should be reported as stored in the Firebird metadata, where they would normally be upper case, e.g.:

No it's not, the original table name was indeed in uppercase.

SELECT TRIM(rf.rdb$field_name) AS column_name, f.rdb$field_type, CASE f.rdb$field_type WHEN 261 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'TEXT' ELSE 'BYTEA' END WHEN 14 THEN 'CHAR(' || f.rdb$field_length|| ')' WHEN 40 THEN 'CSTRING' WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE PRECISION' WHEN 10 THEN 'FLOAT' WHEN 16 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'BIGINT' END WHEN 8 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'INTEGER' END WHEN 9 THEN 'QUAD' WHEN 7 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'SMALLINT' END WHEN 12 THEN 'DATE' WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR(' || f.rdb$field_length|| ')' WHEN 23 THEN 'BOOLEAN' ELSE 'UNKNOWN' END AS data_type, COALESCE(CAST(rf.rdb$default_source AS VARCHAR(80)), '') AS "Default value", rf.rdb$null_flag AS null_flag, COALESCE(CAST(rf.rdb$description AS VARCHAR(80)), '') AS "Description" FROM rdb$relation_fields rf LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name WHERE TRIM(rf.rdb$relation_name) = 'TABLE_NAME' -- table name as stored in Firebird catalog ORDER BY rf.rdb$field_position;

And when running the sql i got the following error:

arithmetic exception, numeric overflow, or string truncation; string right truncation

[HideyoshiNakazone]

SELECT TRIM(rf.rdb$field_name) AS column_name, f.rdb$field_type, CASE f.rdb$field_type WHEN 261 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'TEXT' ELSE 'BYTEA' END WHEN 14 THEN 'CHAR(' || f.rdb$field_length|| ')' WHEN 40 THEN 'CSTRING' WHEN 11 THEN 'D_FLOAT' WHEN 27 THEN 'DOUBLE PRECISION' WHEN 10 THEN 'FLOAT' WHEN 16 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'BIGINT' END WHEN 8 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'INTEGER' END WHEN 9 THEN 'QUAD' WHEN 7 THEN CASE f.rdb$field_sub_type WHEN 1 THEN 'NUMERIC(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' WHEN 2 THEN 'DECIMAL(' || f.rdb$field_precision || ',' || (-f.rdb$field_scale) || ')' ELSE 'SMALLINT' END WHEN 12 THEN 'DATE' WHEN 13 THEN 'TIME' WHEN 35 THEN 'TIMESTAMP' WHEN 37 THEN 'VARCHAR(' || f.rdb$field_length|| ')' WHEN 23 THEN 'BOOLEAN' ELSE 'UNKNOWN' END AS data_type, COALESCE(CAST(rf.rdb$default_source AS VARCHAR(80)), '') AS "Default value", rf.rdb$null_flag AS null_flag, COALESCE(CAST(rf.rdb$description AS VARCHAR(80)), '') AS "Description" FROM rdb$relation_fields rf LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name WHERE TRIM(rf.rdb$relation_name) = 'TABLE_NAME' -- table name as stored in Firebird catalog ORDER BY rf.rdb$field_position;

@ibarwick, is this query used for the creation of the foreign table?

[HideyoshiNakazone]

Found the cause of the error, the table that i am using has descriptions with the letgh of 294 characters and because of that the cast to VARCHAR(80) is throwing a arithmetic exception, numeric overflow, or string truncation; string right truncation

[ibarwick]

Is that the verbatim output? I ask because at this point the table names should be reported as stored in the Firebird metadata, where they would normally be upper case, e.g.:

No it's not, the original table name was indeed in uppercase.

Thanks for confirming; with this kind of error reporting it's really important you provide exact, verbatim output otherwise important clues might be missed or time wasted pursuing non-issues.

Found the cause of the error, the table that i am using has descriptions with the letgh of 294 characters and because of that the cast to VARCHAR(80) is throwing a arithmetic exception, numeric overflow, or string truncation; string right truncation

Right... I think that query derived from some kind of query for displaying table metadata, and there's no reason to arbitrarily shorten the values in this context [*].

[*] Not that that would have worked anyway as Firebird raises an error in this case; PostgreSQL behaves differently in this case and will truncate the value if it exceeds the specified length.

Thanks for the report; fix added, I am intending to put out a release (1.3.0) soon.

[HideyoshiNakazone]

Thanks alot @ibarwick ! I'll be closing this issue 😀