NAME
DBIO::DuckDB::Storage - DuckDB storage driver for DBIO
VERSION
version 0.900007
DESCRIPTION
DuckDB storage driver for DBIO. Extends DBIO::Storage::DBI with DuckDB-specific behavior and escape-hatch methods for DuckDB-native features that do not fit the row-oriented DBI model.
Sits on top of DBD::DuckDB, a pure-FFI DBI driver. No XS compile, libduckdb must be installed at runtime.
Registered as the driver for DuckDB and loaded automatically when connecting to a dbi:DuckDB:* DSN.
METHODS
_mask_error_values
my $text = $storage->_mask_error_values($text, \@values, $statement);
Core's seam for the text an engine writes into an error (ADR 0075), with what DuckDB adds to it. DuckDB writes the offending value into the error, also when it was a bind -- Could not convert string 'x' to INT32, Duplicate key "email: x" violates unique constraint. -- and a message names keys, columns and options, never values. What the caller needs stays: the type, the column, the constraint, the table.
The LINE 1: ... excerpt DuckDB appends is the caller's statement and stays as it is, when it is DuckDB's own -- the end of the text, a piece of a line of $statement, the caret line. The text before it passes "mask_error_details" in DBIO::DuckDB::Util (the sentences of the engine, and the bound values where a key is ambiguous) and then core's masking of the bound values (each value of four characters or more, as written and as the handle quotes it); the excerpt is put back untouched, so a bound value like INSERT does not change it. A text with something that only looks like the excerpt (a bound value can) is masked whole.
Core hands in the values bound to the statement for every text that reaches an exception, so the masking does not depend on the DBD::DuckDB version or on the statement handle callbacks ("DISCONNECT AND THE DATABASE FILE"). The appender (append_row, flush, close) has no bound values: its text is masked by the sentences alone. The Arrow path ("duckdb_arrow_fetch") masks its engine errors through this method with its binds. DuckDB reports no constraint, table or detail of its own, so _dbi_error_fields stays core's.
duckdb_appender
my $appender = $storage->duckdb_appender($table);
my $appender = $storage->duckdb_appender($table, $schema);
Returns a DBD::DuckDB::Appender bound to $table (optionally qualified with $schema, default main). The Appender is DuckDB's native bulk-insert API -- dramatically faster than row-by-row INSERT for loading large batches.
my $app = $storage->duckdb_appender('events');
for my $event (@events) {
$app->append_row(
id => $event->{id},
name => $event->{name},
ts => $event->{ts},
);
}
$app->flush;
Uses "x_duckdb_appender" in DBD::DuckDB under the hood.
duckdb_arrow_fetch
my $t = $storage->duckdb_arrow_fetch($sql, \@binds);
# $t->{names} [ 'decade', 'n' ]
# $t->{formats} [ 'i', 'l' ]
# $t->{columns} [ [ 1970, 1980 ], [ 200, 310 ] ]
# $t->{rows} 2
Runs $sql with @binds and reads the result through libduckdb's Arrow interface (duckdb_data_chunk_to_arrow), block by block and column by column, without DBI's row iterator. The answer is columnar:
names-
The column names, in the order of the result. Columns are told apart by position, since a result may have two columns of one name.
formats-
The Arrow format string of each column, as libduckdb reports it (
iINTEGER,lBIGINT,gDOUBLE,uVARCHAR,+lLIST, ...). columns-
One array reference of values per column, in the same order. Every column holds
rowsvalues; NULL isundef. rows-
The number of rows.
An empty result has names and formats, an empty array in every column and rows 0.
The values are those a selectall_arrayref of the same statement returns, laid on their side: integers and floats as numbers, HUGEINT and DECIMAL as exact decimal strings with the scale of the column, text as characters (UTF-8 decoded), BLOB as bytes, a LIST and an ARRAY as an array reference, a STRUCT as a hash reference, a MAP as a hash reference of its pairs, an ENUM as its text. Text inside LIST, STRUCT and MAP is decoded to characters as well.
DATE, TIME, TIMESTAMP (every unit) and INTERVAL are DuckDB's own text, exactly what CAST(v AS VARCHAR) returns: the fraction is padded to the precision of the type and loses its trailing zeros (11:30:00.12, 11:30:00.000005), years up to 0 are written as 0044-03-15 (BC) with the year counted as 1 - year (in a timestamp (BC) stands between date and time: 0044-03-15 (BC) 12:00:00.5), an infinite value is infinity or -infinity, and an INTERVAL carries its sign and time part (1 day 03:00:00). The row path of DBD::DuckDB 0.16 differs in the fraction, the BC years, INTERVAL (the time part is left out) and infinity (the far dates 5881580-07-11 and 294247-01-10 04:00:54.775807).
TIMESTAMP WITH TIME ZONE is in UTC, as CAST(v AS VARCHAR) has it under SET TimeZone = 'UTC' (2026-10-31 01:02:03+00), whatever the zone of the session is; this is the one place where the values differ from the row path on purpose.
The buffers of a block are read once per column.
A result that holds a type the decoder does not render -- UNION, BIT, BIGNUM, TIME WITH TIME ZONE, in a column of their own or inside a LIST, ARRAY, STRUCT or MAP, and a MAP whose key is a LIST, ARRAY, STRUCT or MAP (a hash has text keys) -- throws before the first row is read. The exception names the column and the Arrow format, never a value. A statement the engine refuses throws its error. The statement and everything libduckdb handed out for the Arrow conversion are released on every way out, so the connection stays usable and a disconnect gives the database file back.
duckdb_read_csv
my $rs = $storage->duckdb_read_csv('/path/to/file.csv', \%opts);
Wraps DuckDB's read_csv table function. Returns an arrayref of row hashrefs. %opts is passed through to "x_duckdb_read_csv" in DBD::DuckDB.
duckdb_read_parquet
my $rs = $storage->duckdb_read_parquet('/path/to/file.parquet');
Runs SELECT * FROM read_parquet(?) against DuckDB. Returns an arrayref of row hashrefs. Requires DuckDB's parquet extension (bundled with most builds).
duckdb_read_json
my $rs = $storage->duckdb_read_json('/path/to/file.json', \%opts);
Wraps DuckDB's read_json table function via "x_duckdb_read_json" in DBD::DuckDB.
duckdb_version
my $v = $storage->duckdb_version;
Returns the linked libduckdb version string (e.g. v1.0.0).
duckdb_install_extension
$storage->duckdb_install_extension('httpfs');
$storage->duckdb_install_extension('httpfs', load => 1);
Runs INSTALL $name. With load => 1 also runs LOAD $name.
_extension_activation
my $activation = $storage->_extension_activation($namespace);
What the declared extensions want of a new connection (ADR 0066, activation), merged by core in the name order of the extensions ("activation" in DBIO::Extension::Protocol): { load => [ ... ], search_path => [ ... ] }. The classes are resolved and their driver assertion is checked ("resolve_list" in DBIO::DuckDB::Extension). A schema that declares no extension has an empty activation and nothing is loaded.
_activate_extensions
$storage->_activate_extensions($activation);
Writes the activation of "_extension_activation" as statements: for every library of load, INSTALL name and then LOAD name, through _do_query. INSTALL of an installed extension does nothing (measured), so the same statements run on every connection; an extension DuckDB does not have, or cannot fetch, is an error and the connect fails with it. DuckDB has no search_path of extensions; an entry there is refused. The name is checked as in "duckdb_install_extension".
_extension_activation_sql
my @sql = $storage->_extension_activation_sql($activation);
The statements of "_activate_extensions" without running them, for a caller that runs them on a connection of its own (the throwaway target database of "_build_target_model" in DBIO::DuckDB::Deploy).
duckdb_checkpoint
$storage->duckdb_checkpoint;
Issues a CHECKPOINT to flush the WAL and compact storage.
quack_serve
$storage->quack_serve('quack:localhost:9500');
$storage->quack_serve('quack:localhost:9500', token => 'my-secret');
Loads the quack extension (INSTALL quack; LOAD quack) then starts a Quack server on the given $addr. The server continues to serve the current database until the process exits or the connection is closed.
$addr must start with quack: and must not contain single quotes or newlines. token is optional; when given it is embedded as a SQL string literal (single quotes escaped).
Returns 1 on success. Throws a DBIO::Exception on validation failure or SQL error.
quack_attach
$storage->quack_attach('quack:localhost:9500', as => 'remote');
$storage->quack_attach('quack:localhost:9500',
as => 'remote',
token => 'my-secret',
read_only => 1,
);
Loads quack, then ATTACHes the remote Quack server as a named catalog.
as is required and must be a valid SQL identifier (\A[A-Za-z_][A-Za-z0-9_]*\z). $addr must start with quack: and contain no single quotes or newlines. read_only attaches the catalog in read-only mode.
With token, the token goes into a temporary DuckDB secret of its own, named dbio_quack_<alias> after the as alias and scoped to $addr:
CREATE OR REPLACE SECRET dbio_quack_remote
(TYPE quack, TOKEN '...', SCOPE 'quack:localhost:9500')
So several Quack servers, each with its own token, attach to one connection, and "quack_detach" drops the secret of its alias again. Without token no secret is created or touched -- a TYPE quack secret you created yourself is picked up by DuckDB as usual.
The secret and the ATTACH run as one transaction ("txn_do" in DBIO::Storage). When the ATTACH fails -- token rejected, server unreachable, alias still attached -- DuckDB's exception is rethrown and the secret store is as it was before the call: nothing is left behind, and a secret that already existed under that name (the alias is still attached) keeps its token. Called inside a transaction of your own, a failed ATTACH aborts that transaction -- DuckDB answers every further statement with Current transaction is aborted until it is rolled back -- and its rollback restores the secrets the same way.
Two limits of DuckDB's secret lookup, measured on libduckdb 1.5.3:
A scope is a prefix, not an exact address. The secret whose scope is the longest prefix of the address wins, and a scoped secret beats an unscoped one. The token for
quack:localhost:95is therefore also offered toquack:localhost:9511as long as that address has no secret of its own. Still far narrower than an unscoped secret, which is sent to every Quack address.Two secrets with the same scope are not ambiguous to DuckDB: the name that sorts first wins, whichever was created last. Two aliases for one address should carry the same token.
What the attached catalog shows, measured on libduckdb 1.5.3 (t/63-quack-client-catalog.t; behaviour of the extension, not of this driver):
Tables the server creates after the ATTACH are invisible to the client ("Table with name ... does not exist"), even on the first query and with the secret intact. The client's catalog is a snapshot taken at attach time; rows of tables that were there are read live. Detach and attach again ("quack_detach", then
quack_attach) takes a new snapshot, and so does attaching a second alias.The database listings throw.
duckdb_databases(),PRAGMA database_listandSHOW DATABASESfail with "InMemory not implemented yet" as long as a Quack catalog is attached. To test whether an alias is attached, useinformation_schema.schemata(catalog_name) orduckdb_schemas()(database_name), which do list it.information_schema.tablesandduckdb_tables()list none of its tables.
Returns 1 on success.
quack_detach
$storage->quack_detach('remote');
Detaches a previously attached Quack (or any) catalog by name, then drops the secret "quack_attach" created for that alias, if there is one (DROP SECRET IF EXISTS dbio_quack_<name>). Secrets of other aliases and secrets you created yourself stay. Name must be a valid SQL identifier.
The drop also runs when the DETACH fails (an alias that is not attached, say), so a secret left behind is cleaned up by calling this again. The DETACH error is then thrown after the drop; if the drop fails as well, the DETACH error is still the one thrown.
Nothing here needs the quack extension, so this also detaches a file or in-memory catalog on a connection that never loaded it.
Inside a transaction that is rolled back afterwards, DuckDB restores the dropped secret but not the attachment -- DETACH is not undone. Calling quack_detach for that alias again throws the DETACH error (the alias is gone) and drops the restored secret.
Returns 1 on success.
connect_call_quack_attach
on_connect_call => [[ quack_attach => {
addr => 'quack:localhost:9500',
as => 'remote',
token => 'my-secret',
read_only => 0,
}]]
On-connect hook that calls "quack_attach" with the supplied hashref. Use via on_connect_call in the connection options to auto-attach a Quack server whenever the storage connects.
addr and as are required in the hashref. token and read_only are optional. See "quack_attach" for validation rules.
bind_attribute_by_data_type
my $attr = $storage->bind_attribute_by_data_type($data_type);
Returns DBI::SQL_BIGINT for the numeric types whose values a C double cannot carry exactly -- bigint, ubigint, hugeint, uhugeint, int128, varint, decimal / numeric and money, plus the alias spellings of those this driver accepts (int8, int64, long, bigserial, uint64, uint128, bignum, dec). A size or precision suffix is ignored, so decimal(38,10) is recognised as decimal. Returns undef for everything else, including integer and narrower.
Why an override is needed at all. DBD::DuckDB chooses the libduckdb bind function from the value when no DBI bind attribute is supplied, and every scalar that looks like a number takes its duckdb_bind_double branch. On libduckdb v1.5.3 / DBD::DuckDB 0.16 that means SELECT typeof(?) with a plain 3 answers DOUBLE, and inserting 9007199254740993 into a BIGINT column stores 9007199254740992 -- silently, no warning, no error. Two adjacent BIGINT keys past 2^53 collapse onto one double, so a later $rs->find($key) can answer with the wrong row. DECIMAL loses its tail the same way; HUGEINT and UBIGINT past the int64 range fail outright with a conversion error.
Why SQL_BIGINT rather than SQL_INTEGER. DBD::DuckDB's bind_param offers exactly two useful branches: SQL_INTEGER binds through duckdb_bind_int64, anything else falls through to duckdb_bind_varchar and lets DuckDB cast the string to the parameter's own type -- exactly, at any width. The int64 branch cannot carry the wide types (2^127-1 arrives as -1, silently) and truncates DECIMAL, and because it runs the value through perl's int() it would turn a non-numeric bind into a stored 0 where DuckDB currently raises. The string route keeps every one of those failures loud.
What that route costs, and the workaround. A parameter DuckDB cannot type from its context arrives as STRING_LITERAL, and a few operator overloads are then ambiguous: measured on 1.5.3, bigint_col * ? and decimal_col * ? inside a literal-with-bind expression raise Binder Error: Could not choose a best candidate function. It is a loud, immediate failure with a local fix -- cast in the SQL (\[ 'b * CAST(? AS BIGINT)', [ b => 2 ] ]) or bypass this method with an explicit dbd_attrs bind attribute.
Why the integer family is deliberately excluded. Every value an INTEGER or narrower column can hold is already exact in a double, so those types have nothing to gain -- while integer_col + ? is one of the ambiguous overloads (+(INTEGER, STRING_LITERAL) collides with +(INTEGER, DATE)), which would break the counter-increment idiom \[ 'n + ?', [ n => 1 ] ]. The one thing they forgo is the plan-shape win: an unattributed bind makes DuckDB widen the column to meet the parameter (CAST(id AS DOUBLE) = 3.0), and a cast on the column side disables zone-map pruning. The types listed above no longer pay that; INTEGER columns still do.
_get_server_version
The engine version, with DuckDB's leading v taken off.
libduckdb spells its own version v1.5.3: that is what duckdb_library_version() answers, and DBD::DuckDB hands the string through unchanged for SQL_DBMS_VER -- DBD::DuckDB::GetInfo::sql_dbms_ver is a FETCH of duckdb_version. Core's normaliser in "_server_info" in DBIO::Storage::DBI reads the number off the front of that string:
my ($numeric_version) = $server_version =~ /^([\d\.]+)/;
my @verparts = split (/\./, $numeric_version);
The v sits in front of the anchor, so the match fails, and two things follow. Every call warns Use of uninitialized value $numeric_version in split; and -- the half that makes no noise -- normalized_dbms_version is never set, so anything gating a feature on the engine version reads undef and decides wrong.
That regex is right for the rest of the family, which answers with a bare number. DuckDB is the odd one out, and _get_server_version is the seam provided for exactly that, so the correction lives here rather than in the shared normaliser.
Only the leading v goes. duckdb.h documents duckdb_library_version() as returning "the version of the linked DuckDB, with a version postfix for dev versions", and no dev build was available to measure that postfix's shape, so nothing behind the v is touched: core's own regex decides where the number ends, as it already does for DBIO::MySQL::Storage's 11.8.8-MariaDB-ubu2404.
What this hook returns is what core stores in dbms_version -- _server_info keeps no second, rawer slot -- so dbms_version reads 1.5.3 on this driver. DuckDB's own spelling stays reachable, verbatim and unnormalised, through "duckdb_version". Pinned in t/46-server-version.t.
CAPABILITIES
supports_exists_query
Declared true. "results_exist" in DBIO::ResultSet and its two siblings probe this capability and throw without it (ADR 0037), so the declaration is what makes them usable on DuckDB at all.
This driver does not override "_exists_value_sql" in DBIO::Storage::DBI: core's base rendering,
SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END
is already valid DuckDB. That form makes three portability assumptions, and DuckDB's documentation covers all three:
EXISTSas a boolean value expression, not merely as a predicate. The SQL dialect reference documentsEXISTSas returning "either true when the subquery returns one or more records, and false otherwise", and its own example selects that value directly --SELECT EXISTS (FROM grades WHERE course = 'Math') AS math_grades_present;(https://duckdb.org/docs/stable/sql/expressions/subqueries).A
SELECTwith noFROMclause. The same documented example is exactly that: a complete statement whose outer query has noFROM. DuckDB treats theFROMclause as optional in both directions -- it also allows the converse, aFROMwith noSELECT(https://duckdb.org/docs/stable/sql/query_syntax/from).A boolean as the
WHENcondition of aCASE.CASE WHEN cond THEN a ELSE b ENDis documented as "identical to the ternary condition used in many programming languages", i.e.condis any boolean-valued expression, which is whatEXISTSyields (https://duckdb.org/docs/stable/sql/expressions/case).
The third assumption ADR 0037 catalogued -- AS before a derived-table alias -- no longer applies to anyone: core dropped the SELECT * FROM ( ... ) AS _existence_subq wrapper when it introduced this hook. It was never a DuckDB problem, but the statement is now one DuckDB accepts and shorter.
Note that the base rendering also settles the boolean-transport footgun ADR 0037 describes: the statement yields an integer 1/0 rather than an engine boolean, so no DBD-level true/false spelling can be misread.
transactional_ddl
Declared true, and unlike every other 1 in this family that is not a statement about the engine. DuckDB has transactional DDL: CREATE / DROP / ALTER TABLE, CREATE INDEX, CREATE SEQUENCE and CREATE VIEW all roll back inside an ordinary transaction, and this driver's own install batch commits as one unit.
The blocker used to be underneath, in DBD::DuckDB: its rollback reopens a transaction and the AutoCommit handler that would close it again is unreachable code, so any txn_do that rolls back left the connection inside a transaction DBI did not know about and the next txn_begin died. This storage's _exec_txn_rollback override now ends the stray transaction itself (ADR 0005), so a failed apply rolls back whole and the connection survives -- core's documented recovery, re-running the upgrade against live state, works. The full reasoning and the measurement are in "Transactional DDL -- the engine has it, the rollback override ends the orphan" in DBIO::DuckDB::Deploy.
savepoints
None. DuckDB has no SAVEPOINT: measured on libduckdb v1.5.3, SAVEPOINT, RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT are each a Parser Error, inside a transaction and outside one. This storage therefore defines none of _exec_svp_begin, _exec_svp_release and _exec_svp_rollback, which is what core goes by: "svp_begin" in DBIO::Storage, "svp_release" in DBIO::Storage and "svp_rollback" in DBIO::Storage throw Your Storage implementation doesn't support savepoints and send the engine nothing.
That makes auto_savepoint ("auto_savepoint" in DBIO::Storage::DBI) a setting to leave off on this driver. With it on, every nested txn_do and every nested txn_scope_guard is refused with that exception, before the inner block runs. The refusal itself costs the outer transaction nothing -- an outer block that catches it carries on and commits -- and one that lets it pass rolls back, as for any other exception. With it off, which is the default, the inner level joins the outer transaction: there is one transaction and it ends as a whole ("txn_rollback" in DBIO::Storage says what a rollback inside a nested level does). Pinned in t/48-no-savepoints.t.
SET OPERATIONS
DuckDB has all six, spelled the ANSI way: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT and EXCEPT ALL, each with its own section in the dialect reference (https://duckdb.org/docs/stable/sql/query_syntax/setops). This driver therefore does not override "_set_operation_keyword" in DBIO::Storage::DBI (ADR 0039): it inherits core's base map unchanged, and all six of DBIO::ResultSet's set-operation methods work with nothing declared here.
The one version boundary, and why it is not refused
The ... ALL variants are not as old as the rest. Until DuckDB 0.10.0 (2024-02-13) the ALL modifier on INTERSECT and EXCEPT was parsed and then silently ignored, so EXCEPT ALL ran with EXCEPT semantics and returned de-duplicated rows. It was implemented for real in 0.10.0 (duckdb/duckdb PR #9636, merged 2023-11-22, first released in v0.10.0; a competing proposal to raise an error instead, PR #9553, was closed unmerged).
That is a version-bounded gap, which is what ADR 0039 section 7 is about, so it was weighed rather than waved through -- and the answer is still to declare nothing:
Refusing unconditionally would withhold
intersect_allandexcept_allfrom every DuckDB released since February 2024, the whole of the 1.x line included, to protect users of a pre-1.0 beta. That is exactly the trade ADR 0039 section 3 rejects for a capability gate -- a real outage on every working version in exchange for a defect on none that are in practice reachable.A class-level opt-in would have to default to off to mean anything, which is the same regression wearing a switch, and nobody would ever set it.
The boundary is documented here instead, because the pre-0.10.0 failure mode is a silent wrong answer rather than a syntax error, and a silent wrong answer is worth writing down even where it is not worth a refusal. It is also hard to reach on purpose: DBD::DuckDB loads Alien::DuckDB unless DUCKDB_NO_ALIEN is set, and Alien::DuckDB 0.04 ships libduckdb 1.5.3 (0.03 shipped 1.2.2). Running this driver against a libduckdb older than 0.10.0 means deliberately pointing it at a 2023 pre-1.0 build.
RANDOM ORDERING
DuckDB spells it random(), a per-row DOUBLE in [0,1), and function names are case-insensitive -- so core's inherited RANDOM() is already the right call and this driver does not override "_random_function" in DBIO::Storage::DBI. Measured on libduckdb v1.5.3: eight rows of SELECT RANDOM() give eight distinct values, and ORDER BY RANDOM() permutes; DuckDB has no query-constant variant to be caught by, the way ClickHouse's randConstant() catches that engine.
The other spelling in this family, MySQL's RAND(), does not exist on DuckDB -- Catalog Error: Scalar Function with name rand does not exist! -- so the inheritance is not "either would do". A wrong default here fails loudly, by naming the function it could not find, which is core's stated reason for having no capability flag beside this hook. Pinned in t/43-random-and-explain.t, engine side included.
QUERY PLANS
DuckDB has a plain EXPLAIN that returns the plan without running the query, so this driver does not override "_explain_query" in DBIO::Storage::DBI either (ADR 0040). $rs->explain answers with a single row of two columns, explain_key and explain_value -- the key is physical_plan and the value is DuckDB's box-drawn plan as one string. Its contents are engine-specific, as ADR 0040 section 2 says of every engine here; nothing in this family formats plans alike.
DuckDB also prepares a parameterised EXPLAIN, which is what lets core's default hand the explained query's bind list straight through without the driver taking the statement apart.
What is deliberately not reached for is EXPLAIN ANALYZE. On DuckDB that is a different operation rather than a formatting option -- it answers under the key analyzed_plan, and it runs the query: measured on libduckdb v1.5.3, an explained-and-analyzed SELECT nextval(...) advances the sequence and an explained-and-analyzed INSERT writes the row, while the plain EXPLAIN of either does neither. A caller asking what the planner intends does not expect its data to move, so a future override adding timings would be buying them with side effects. Pinned in t/43-random-and-explain.t, including the requirement of ADR 0040 section 3 that the statement travel "_execute_rendered" in DBIO::Storage::DBI -- free while the hook is inherited, and the first thing an override loses.
NO STATEMENT CACHE
This storage prepares every statement afresh. "disable_sth_caching" in DBIO::Storage::DBI is always on here: the accessor answers true whatever it is given, and disable_sth_caching => 0 in the connect attributes is accepted and changes nothing. There is no way to switch the cache on.
The reason is DBD::DuckDB. Core prepares through "prepare_cached" in DBI, which hands out the same statement handle again when the same SQL comes again, and a DBD::DuckDB 0.16 handle does not survive that, in three ways that are independent of one another (measured on libduckdb v1.5.3):
finishdestroys the prepared statement, and the handle stays in the cache. The secondcreatethat lets the engine assign the key, the secondfindand the secondsingleof one shape died withbind_param failed; the secondpopulatewith the same columns ended the process with a segmentation fault.executenever marks the handleActive, so DBI cannot tell a handle that is being read from a free one. Two result sets of one SQL shape that were read side by side shared a handle and got each other's rows.executekeeps the rows left unread by the run before. After$rs->search({ rank => 1 })->first, the same search forrank => 2answered the second row of the first search. No error and no warning -- which is why this is not left to a setting.
What it costs is one duckdb_prepare per statement: a few tenths of a millisecond in-process, which showed as roughly a tenth on top of an ORM statement that does next to nothing.
Handles you prepare yourself through dbh_do are outside this. One from $dbh->prepare that is executed once is fine; one that is executed again, or one from $dbh->prepare_cached, has the defects above.
Pinned in t/49-statement-reuse.t, which also carries the plain-DBI sentinel that shows when a DBD::DuckDB release has fixed them; ADR 0007 has the decision and what was rejected.
DISCONNECT AND THE DATABASE FILE
DuckDB locks a read-write database file exclusively from connect on, and while the lock is held no other process can open the file -- not even read-only. $storage->disconnect gives the file back, with one rule and two exceptions.
The rule: DuckDB keeps the database open for as long as a prepared statement on it exists, whatever happened to the connection. A statement handle that outlives disconnect -- a cursor still being iterated, an $sth the caller holds -- therefore keeps the lock until it is gone.
What this storage does about DBD::DuckDB. DBD::DuckDB 0.16 never frees a prepared statement: finish hands duckdb_destroy_prepare the handle by value where the function takes a pointer to it, which reaches C as NULL, and there is no DESTROY for a handle nobody called finish on, such as every $dbh->do. On a plain DBI handle the lock consequently survives disconnect until the process exits. This storage frees the statements of its own connection through a DBI per-handle callback ("Callbacks" in DBI), in front of finish and DESTROY; callbacks the application installed there are chained behind it and keep firing. Nothing outside the storage's own handle is changed.
The exceptions:
A DBD::DuckDB newer than 0.16. The callback frees memory DBD::DuckDB owns, so it runs only against the releases whose
finishhas been read. Against a newer one the storage does nothing and that release's own behaviour shows.AutoCommit => 0. DBD::DuckDB issuesBEGIN TRANSACTIONinsideDBI->connect, before the storage has a handle to put a callback on. That statement is never freed, and the lock is held until the process exits.
Measured on libduckdb v1.5.3 and pinned in t/47-disconnect-releases-file-lock.t, which asks a second process to open the file and reads /proc/locks. (Opening the file from the same process proves nothing: an advisory lock does not stop open, and closing any descriptor on the file drops the process's own lock on it.)
FINISH ON A HANDLE WITHOUT A RESULT
A statement the engine refuses is an exception on this storage, also where the statement handle is finished afterwards.
DBD::DuckDB 0.16 ends the process with a segmentation fault when finish is called on a statement handle that has no result: one that was never executed, one whose only execute the engine refused, or one that was finished before. Cleanup code calls finish exactly then, and so does core after a bulk insert -- a populate in void context whose first row was refused (a key that is taken, a value that does not convert) did not throw, it took the process down.
The per-handle callback described above also stands in front of that: a handle without a result is given an empty one for finish to destroy. The populate throws with the engine's reason, its transaction is rolled back and the connection carries on; a finish of your own on a handle from dbh_do returns as usual. Like the statement release, this is installed for DBD::DuckDB up to 0.16 only and changes nothing outside the storage's own connection -- a plain DBI handle on DuckDB still crashes there.
Measured on libduckdb v1.5.3 and pinned in t/51-finish-without-result.t, which runs every case in a process of its own.
ESCAPE HATCHES
DBI is row-oriented. DuckDB is columnar. These methods bypass the DBI statement-handle path where it hurts:
QUACK (CLIENT-SERVER RPC)
Quack is a DuckDB extension (v1.5+) that exposes an embedded DuckDB instance over HTTP-based RPC, turning it into a lightweight server. The client remains a normal in-process DuckDB using DBD::DuckDB -- no new transport layer.
Requires libduckdb >= 1.5. Alien::DuckDB >= 0.04 ships 1.5.3, so these methods work out of the box; the older 0.03 shipped 1.2.2, which does not include quack. Only against such an older installation must you supply a newer libduckdb via DUCKDB_NO_ALIEN=1 LD_LIBRARY_PATH=/path/to/1.5.x.
SEE ALSO
DBIO::DuckDB - Schema component that activates this storage
DBIO::DuckDB::SQLMaker - SQL generation for DuckDB
DBIO::DuckDB::Deploy - test-deploy-and-compare schema deploy
DBD::DuckDB - underlying DBI driver
DBIO::Storage::DBI - Base DBI storage class
AUTHOR
DBIO Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.