How does duckdb execution work?

[TianzeMYou]

Curious how pg_duckdb "pushes" queries to use duckdb execution (yes, by setting duckdb.force_execution, but how does that work?). For example, the definition for @[email protected]_parquet at https://github.com/duckdb/pg_duckdb/blob/main/sql/pg_duckdb--0.1.0.sql#L18 seems to only raise exceptions when duckdb execution is off. So if it is turned on, where/what calls the duckdb implementation?

Seems like by using hooks? I see some initializations,

pg_duckdb/src/pgduckdb_hooks.cpp

Line 424 in 66cb6e6

planner_hook = DuckdbPlannerHook;

Is the BGW always running in the background to check all queries?

pg_duckdb/src/pgduckdb_background_worker.cpp

Line 115 in 66cb6e6

while (true) {

Greatly appreciate if anyone can help me understand better!

[JelteF]

From the readme:

DuckDB execution is usually enabled automatically when needed. It's enabled whenever you use DuckDB functions (such as read_csv), when you query DuckDB tables, and when running COPY table TO 's3://...'. However, if you want queries which only touch Postgres tables to use DuckDB execution you need to run SET duckdb.force_execution TO true'. This feature is opt-in to avoid breaking existing queries. To avoid doing that for every session, you can configure it for a certain user by doing ALTER USER my_analytics_user SET duckdb.force_execution TO true.

If you're curious about the details, this function determines if a query needs duckdb execution:

pg_duckdb/src/pgduckdb_hooks.cpp

Lines 126 to 128 in 66cb6e6

	NeedsDuckdbExecution(Query *query) {
	return ContainsDuckdbItems((Node *)query, NULL);
	}

[TianzeMYou]

I was able to find some answers digging through the code a bit. Seems like BGW is only used when MotherDuck is enabled.

I see how the planner hook creates a duckdb_plan, which IIUC is what shows up when using EXPLAIN ... with duckdb queries.

pg_duckdb/src/pgduckdb_planner.cpp

Line 146 in 405ff80

Plan *duckdb_plan = InvokeCPPFunc(CreatePlan, parse, throw_error);

However, I am having trouble seeing where this is actually executed by duckdb?
I had suspected it's from executor hook (from the name), but it just does this

pg_duckdb/src/pgduckdb_hooks.cpp

Line 295 in 66cb6e6

pgduckdb::ClaimCurrentCommandId();

And where is the instance creation according to

pg_duckdb/docs/settings.md

Line 102 in 405ff80

Since any connection that uses DuckDB will have its own DuckDB instance, these settings are per-connection. When using `pg_duckdb` in many concurrent connections it can be a good idea to set some of these more conservatively than their defaults.

?

[JelteF]

The duckdb execution takes place in the DuckDB CustomScan implementation:

pg_duckdb/src/pgduckdb_node.cpp

Lines 184 to 191 in 405ff80

	Duckdb_ExecCustomScan_Cpp(CustomScanState *node) {
	DuckdbScanState *duckdb_scan_state = (DuckdbScanState *)node;
	TupleTableSlot *slot = duckdb_scan_state->css.ss.ss_ScanTupleSlot;
	MemoryContext old_context;
	bool already_executed = duckdb_scan_state->is_executed;
	if (!already_executed) {
	ExecuteQuery(duckdb_scan_state);

The DuckDB initialization takes place here:

pg_duckdb/src/pgduckdb_duckdb.cpp

Line 178 in 405ff80

database = new duckdb::DuckDB(connection_string, &config);

When that is actually called the first time depends on the query you're using. But often that happens implicitly when calling pgduckdb::DuckDBManager::GetConnection() such as here:

pg_duckdb/src/pgduckdb_planner.cpp

Line 49 in 405ff80

auto con = pgduckdb::DuckDBManager::GetConnection();

[TianzeMYou]

Thanks, I can see how this interacts with PG's implementation of CustomScanMethods, etc.

A follow up to this is: node is initialized in PG_init, how is this code re-entered when a new query is ran because isn't PG_init just ran one time usually?

pg_duckdb/src/pgduckdb_node.cpp

Line 299 in 405ff80

duckdb_scan_exec_methods.ExecCustomScan = Duckdb_ExecCustomScan;

and how does this work in junction with the planner hook when deciding if duckdb execution is needed?

pg_duckdb/src/pgduckdb_hooks.cpp

Lines 126 to 128 in 66cb6e6

	NeedsDuckdbExecution(Query *query) {
	return ContainsDuckdbItems((Node *)query, NULL);
	}

[JelteF]

A follow up to this is: node is initialized in PG_init, how is this code re-entered when a new query is ran because isn't PG_init just ran one time usually?

The DuckdbInitNode function is indeed ran at PG_init time once. The only thing it does is configure callbacks which then get called when the executor fiends a node of our CustomScan type.

and how does this work in junction with the planner hook when deciding if duckdb execution is needed?

The planner puts our CustumScan node into the plan when duckdb execution is needed (based on NeedsDuckdbExecution). Then the executor executes the callbacks configured for that node.