Silent failure in Postgres extension when using unsupported type

[prrao87]

I'm noticing silent failure of the Postgres table scan when using an unsupported type (Kùzu doesn't tell me there's an issue, but the table isn't listed as available even though it exists in Postgres)

I have a Postgres schema in which I want to use the DECIMAL data type to represent money values correctly with exactly two decimals, and with 6 significant digits. Here's my desired schema:

CREATE TABLE IF NOT EXISTS wines(
    wine_id INT PRIMARY KEY,
    title VARCHAR(1028) NOT NULL,
    variety VARCHAR(255) NOT NULL,
    points SMALLINT,
    price DECIMAL(6, 2)
)

When I load this data into Postgres, I verify that the table exists, and my SQL query to list the data works.

However, when I list the table in Kùzu's Postgres extension via CALL show_tables() RETURN *, the table doesn't appear (it's not accessible via the schema cache, presumably because Kùzu doesn't correctly cast the DECIMAL type in Postgres to our internal representation of DOUBLE).

I know this is the case, because when I replace the DECIMAL(6,2) with a FLOAT8, i.e., the following schema, it works:

CREATE TABLE IF NOT EXISTS wines(
    wine_id INT PRIMARY KEY,
    title VARCHAR(1028) NOT NULL,
    variety VARCHAR(255) NOT NULL,
    points SMALLINT,
    price FLOAT8
)

Then, the table appears in Kùzu as follows:

┌────────────────┬──────────┬────────────────┬─────────┐
│ name           ┆ type     ┆ database name  ┆ comment │
│ ---            ┆ ---      ┆ ---            ┆ ---     │
│ str            ┆ str      ┆ str            ┆ str     │
╞════════════════╪══════════╪════════════════╪═════════╡
│ wines          ┆ ATTACHED ┆ wine(POSTGRES) ┆         │
└────────────────┴──────────┴────────────────┴─────────┘

Desirable behaviour

If, for some reason, the table that exists in Postgres can't be scanned in Kùzu, we should display an appropriate error so that the user knows something is wrong. In this case, it's not clear why switching the price schema from DECIMAL(6,2) to FLOAT8 actually worked, so that's something to test out.

In the longer run, we should extend support for specialized Postgres data types (like NUMERIC or DECIMAL) as handling money values in Postgres should never be done with FLOAT4 or FLOAT8 as it's fundamentally imprecise due to floating point precision limitations.
https://stackoverflow.com/questions/15726535/which-datatype-should-be-used-for-currency

[acquamarin]

The difficulty of giving a warning message to user is that: we have to propagate the warning message all the way from the extension to the physical operator which complicates the design.
Can we give them a generic warning message telling the user that: if their table has unsupported type in kuzu, the table will be ignored?
For DECIMAL type:
we are going to support DECIMAL as our internal type soon.

[prrao87]

Yes, literally any sort of information we can provide to the user is better than providing no information (as it is currently). I think we should do what we can for now without complicating the design. Looking forward to seeing the DECIMAL data type on our side!

[acquamarin]

I am thinking of a new design of attaching a duckdb/postgres table that contains unsupported types:

We should support a new option(skip_malformed_table) in the attach statement:
ATTACH /tmp/duckdb (dbtype duckdb, skip_ malformed_table = true)

1. If the option is true, we skip the tables that contain unsupported types.
2. If the option is false, we throw an exception if one table contains unsupported types.

Instead of making the choice implicitly for the user, i think it is better for them to explicitly specify whether to skip.

[prrao87]

Hmm, ideally the user shouldn't have to specify this - it could just be a warning that one of the tables had malformed/unsupported types and the table name would just be listed so the user is aware. Choosing to skip or not skip isn't really a "useful" option in any case, until we can support all the common types 😅 .