search

duckdb / postgres_scanner

https://duckdb.org/docs/extensions/postgres
MIT License
227 stars 36 forks source link

[Feature request] - Add support for `UNLOGGED` #184

Closed ketan-mck closed 6 months ago

ketan-mck commented 7 months ago

I'm not sure if this is the right place for it, or if this should go to the main repo. Please advice.

I'm looking to import huge amounts of data from parquet to postgres via duckdb. This seems like the perfect tool for the job. I was looking to see if there's a way that I could use unlogged tables (Unlogged tables disable WAL to significantly improve write speeds).

I tried the following snippets to no avail:

INSTALL postgres;
SET pg_debug_show_queries=true;
SET pg_pages_per_task=10000;
ATTACH 'dbname=mydb' AS mydb (TYPE postgres);
DROP TABLE IF EXISTS mydb.userdata1;
CREATE UNLOGGED TABLE mydb.userdata1 AS FROM (SELECT * FROM 'foo.parquet' LIMIT 0);

However, it seems to simply ignore the UNLOGGED keyword. Is there any possibility to "escape out" to execute native postgresql? I'm happy to do ALTER TABLE ... SET UNLOGGED via native postgresql query as well.

Thanks

Mytherin commented 7 months ago

Thanks for the suggestion!

I have thought about adding support for a postgres_execute function that allows you to execute arbitrary SQL within Postgres, would that work for you?

e.g.:

CALL postgres_execute('mydb', 'ALTER TABLE userdata1 SET UNLOGGED');
ketan-mck commented 7 months ago

That would absolutely work fine, and very much in line with what I was hoping/expecting. It would not be practical for duckdb to expose every single SQL feature. Where there are gaps between the different SQL dialects, going down to native SQL would be absolutely perfect.

Mytherin commented 6 months ago

Implemented in https://github.com/duckdb/postgres_scanner/pull/193