Inconsistent results from querying postgres numeric columns

[pquadri]

What happens?

While using duckdb to query postgres, we are getting inconsistent results.

To Reproduce

in postgres

create TABLE public.amounts (
    id bigint NOT NULL,
    rate numeric NOT NULL
);


insert into public.amounts values (1, 0.000067) 

reading the value with duckdb 0.9.2 or 0.9.3 nightly yields a IO Error: Postgres scanner - out of buffer in ReadInteger

(i tested also with previous duckdb versions and i got inconsistent results, with 0.9.0 i got 0.67 with the wrong number of zeros)

OS:

Mac

PostgreSQL Version:

16.1, observed on 14.9 too

DuckDB Version:

0.9.3.dev42, different issues on previous versions

DuckDB Client:

Python

Full Name:

Paolo Quadri

Affiliation:

Remote.com

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[pquadri]

also, i'm still getting Error: Invalid Error: Failed to execute query "SELECT pg_is_in_recovery(), pg_export_snapshot(), (select count(*) from pg_stat_wal_receiver)": ERROR: Function pg_stat_get_wal_receiver() is currently not supported in Aurora on nightly (should have been fixed by #131 ?)

am using con.execute("install postgres_scanner from 'http://nightly-extensions.duckdb.org';") to install the extension nightly version

[Mytherin]

Thanks for the report! I've pushed a fix for the decimal issue in #138. I will have another look at the Aurora issue.

[Mytherin]

The aurora issue seems to be fixed for me, you should likely run FORCE INSTALL to upgrade to the latest nightly - i.e.:

force install postgres_scanner from 'http://nightly-extensions.duckdb.org';

INSTALL has no effect if the extension is already installed.

[pquadri]

testing right away :)

[pquadri]

now i'm getting

Error: Invalid Error: Failed to execute query "
SELECT relpages, attname,
    pg_type.typname type_name, atttypmod type_modifier, pg_attribute.attndims ndim
FROM pg_class
JOIN pg_namespace ON relnamespace = pg_namespace.oid
JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
JOIN pg_type ON atttypid=pg_type.oid
WHERE pg_namespace.nspname='public' AND relname='amounts' AND attnum > 0
ORDER BY relname, attnum;
": ERROR:  current transaction is aborted, commands ignored until end of transaction block

(but only if using postgres_scan, if i use attach it seems to work besides the integer overflow)

[Mytherin]

Ah yes. In general we recommend moving over to the ATTACH workflow. The old postgres_attach and postgres_scan methods are deprecated.

[pquadri]

is the fix for IOException: IO Error: Postgres scanner - out of buffer in ReadInteger supposed to be already available in nightly?

[Mytherin]

The binaries are uploaded but the caches are likely not invalidated yet, so you would need to wait a bit (or build from source yourself)

[pquadri]

The binaries are uploaded but the caches are likely not invalidated yet, so you would need to wait a bit (or build from source yourself)

trying to do that :)

[pquadri]

@Mytherin just an update,

• building the extension locally seems to work (load 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension')
• using the nightly version is still not working for me (force install postgres_scanner from 'http://nightly-extensions.duckdb.org';)
• even with the extension working, if i do two queries in a row (a really simple SELECT rate FROM s.public.amounts) on the second one i get the ERROR: current transaction is aborted, commands ignored until end of transaction block (i have no idea about this one though)
• i think you might have an issue with the repository CI publishing the extension on all the MR/commits in any branch