SQL reference

Every function is in the pgcolumnar schema. Types are shown as in the function signature. For server settings, see Configuration reference.

Table management

pgcolumnar.alter_table_set_access_method(t text, method text)

Converts a table to another access method, for example from the default heap to pgcolumnar or back.

On PostgreSQL 15 and later this runs ALTER TABLE ... SET ACCESS METHOD, which rewrites the table in place and preserves its identity and dependents. PostgreSQL 13 and 14 do not have that command. On those two versions, the function makes a second table with LIKE ... INCLUDING ALL. It then copies each row through the target method and exchanges the names. On those two majors the conversion does not preserve the original table's OID or objects that depend on it, such as views and foreign keys.

SELECT pgcolumnar.alter_table_set_access_method('events', 'pgcolumnar');

pgcolumnar.set_options(...) and pgcolumnar.reset_options(...)

Set or reset per-table storage options (row group and vector row limits, compression codec and level, encode effort, and the declared sort_by key). See Configuration reference.

sort_by name[] declares a physical sort key, applied by pgcolumnar.vacuum_sorted with no explicit columns. It is not auto-maintained; rows inserted after a sort append in insertion order, so re-run vacuum_sorted to re-establish it, like PostgreSQL CLUSTER. Column names must exist and cannot be virtual generated columns.

ttl_column name and ttl_interval interval declare a retention. They are read only by pgcolumnar.expire. Declaring a retention does not delete anything on its own. expire runs when you call it, or when the maintenance daemon calls it with pgcolumnar.autovacuum_expire on. Both are needed: either one alone means no retention.

ttl_interval must be a positive interval. Zero and negative intervals raise 22023. A negative interval would put the cutoff in the future, so expire would drop rows that are still inside their retention.

SELECT pgcolumnar.set_options('events', sort_by => ARRAY['customer_id','ts']);
SELECT pgcolumnar.reset_options('events', sort_by => true);   -- clear it

-- declare a retention; nothing is deleted until expire runs
SELECT pgcolumnar.set_options('events', ttl_column => 'ts',
                              ttl_interval => '90 days');

pgcolumnar.approx_count_distinct(value anyelement) returns bigint

An aggregate: the approximate number of distinct non-NULL values, from a HyperLogLog of 2048 registers. The standard error is about 2.3%, so most answers fall within 5% of the exact count(DISTINCT value). It runs on any table, heap or columnar, and in parallel plans, where it returns the serial answer exactly.

SELECT pgcolumnar.approx_count_distinct(device_id) FROM readings;

The value's type needs an extended hash function, and a text value a deterministic collation; anything else is refused with SQLSTATE 0A000. Its hash and registers are those of the sketches a column named in set_options(t, sketch_columns => ...) stores per row group. A query with no WHERE and no GROUP BY is answered from those sketches, unless another aggregate in it needs the rows. Each row group's sketch is merged, and only a group with deleted rows, or one written before the column was named, is read. The answer is the same as reading every row. EXPLAIN ANALYZE shows Columnar Sketch Groups Folded and Columnar Sketch Groups Scanned.

pgcolumnar.get_storage_id(rel regclass) returns bigint

Returns the internal storage identifier of a columnar table. Used to join the pgcolumnar catalog tables. Most users read pgcolumnar.stats instead.

Maintenance

pgcolumnar.vacuum(tablename regclass, stripe_count int DEFAULT 0)

Compacts a columnar table by combining small row groups and reclaiming space held by rows that were deleted or updated. Use it after bulk deletes or updates, or after many small load transactions have produced many small row groups.

stripe_count is not supported and a non-zero value raises an error. The argument is retained so existing calls that pass the default keep working. It was documented as bounding how many row groups are combined in one call. It was never read, and every call rewrote the whole relation regardless of the value.

To bound the work, use pgcolumnar.compact_rewrite, whose max_groups does limit how many row groups are rewritten.

SELECT pgcolumnar.vacuum('events');

pgcolumnar.vacuum_sorted(tablename regclass, VARIADIC sort_columns name[])

Compacts a columnar table and stores its rows sorted ascending (NULLS LAST) on the given columns. Sorted storage makes the per-chunk minimum and maximum values tight on the sort columns, and it stops them overlapping. Equality filters and range filters on those columns therefore skip more chunk groups. Any column with a btree ordering works, and this includes text. The Z-order cluster() takes numeric columns only. Use it for a segment key (e.g. customer_id, hostname) whose values are scattered in insertion order but are often filtered on.

With no columns, it applies the sort_by key that set_options declared for the table. A bare CLUSTER re-applies a remembered index in the same way. It raises an error if the table declares no key:

SELECT pgcolumnar.vacuum_sorted('events', 'customer_id');          -- explicit
SELECT pgcolumnar.set_options('events', sort_by => ARRAY['customer_id']);
SELECT pgcolumnar.vacuum_sorted('events');                         -- declared key

Re-running vacuum_sorted with the same key on a table that is still in that order is a fast no-op. It skips the rewrite on four conditions. The recorded key matches. The recorded kind is a sort and not a Z-order. The sorted run already covers every row group. No row group holds a deleted row.

That last condition is part of the gate because vacuum_sorted both orders the rows and reclaims deleted-row space. A table that is in order but holds deleted rows is rewritten, so the space is still reclaimed. Deletes, inserts, a different key, or a table arranged by cluster() all fall through to the full rewrite.

A sort key is a trade and not a free gain. A sort by a segment key puts the rows of each segment together, so a filter on that key skips most groups. It also spreads each other dimension across every group.

For time-series data, a sort by (segment_key, time) keeps time in order inside each segment. A query that filters on time alone, across all segments, then prunes less than the natural time order permits.

Sort by the key that your selective queries filter on. This is a one-shot reorder, and no operation maintains it. Read pgcolumnar.sort_status to measure how much of the order remains.

pgcolumnar.cluster(tablename regclass, VARIADIC columns name[])

Rewrites a columnar table with its rows ordered by a Z-order (Morton) space-filling curve on the columns that you give. vacuum_sorted sorts in ascending order. Thus it makes the minimum and maximum of its first column tighter. Z-order makes each clustered column tighter at the same time. Range filters and point filters on more than one column then skip more vectors and more chunk groups. The results do not change. Only the physical order changes.

Holds AccessExclusiveLock for the duration, like PostgreSQL's own CLUSTER and VACUUM FULL, because it rewrites the relation and swaps its file. Reads and writes on the table block until it completes. Use it for an initial bulk reorganisation; use pgcolumnar.recluster to reorder a live table without an exclusive lock.

SELECT pgcolumnar.cluster('events', 'customer_id', 'ts');

pgcolumnar.cluster_hilbert(tablename regclass, VARIADIC columns name[])

The same reorganisation on the Hilbert curve instead of the Z-order one. Take it when the clustered columns carry range filters. The Hilbert index has no jumps at a bit boundary. Keys that are close in the data therefore stay close in storage, and a range filter reads fewer chunk groups. Everything else matches cluster: the same arguments, the same refusals, the same AccessExclusiveLock.

SELECT pgcolumnar.cluster_hilbert('events', 'customer_id', 'ts');

The curve is sticky. The table records which curve it was laid on, and pgcolumnar.sort_status reports it as sorted_kind. Once a table is on the Hilbert curve, plain cluster and recluster on the same key maintain that curve rather than converting it back. vacuum_sorted leaves the table alone, and the maintenance daemon re-clusters it with Hilbert. To switch curves, name the other verb, or recluster on a different key.

pgcolumnar.recluster(tablename regclass, VARIADIC columns name[]) returns bigint

The online counterpart to cluster. Re-establishes the same Z-order clustering over the given columns, but under ShareUpdateExclusiveLock, so concurrent reads and writes continue instead of blocking. Returns the number of row groups reclustered.

SELECT pgcolumnar.recluster('events', 'customer_id', 'ts');

Re-running recluster with the same key on a table whose clustering is intact is a fast no-op. The function records the key it last established. It returns 0 without rewriting anything when the recorded key matches, the kind is Z-order, and the existing sorted run already covers every row group. This is what lets the maintenance daemon call it on a schedule without churning storage.

On a table laid on the Hilbert curve over the key you name, recluster maintains that curve. It does not convert the table to Z-order.

pgcolumnar.recluster_hilbert(tablename regclass, VARIADIC columns name[]) returns bigint

The online counterpart to cluster_hilbert, and the way to move a Z-ordered table onto the Hilbert curve. Same arguments, same lock and same return value as recluster; it re-establishes Hilbert clustering rather than Z-order, and records the curve it applied.

SELECT pgcolumnar.recluster_hilbert('events', 'customer_id', 'ts');

pgcolumnar.compact(tablename regclass) returns bigint

Retires row groups that are fully deleted, dropping their metadata so scans skip them. Holds only ShareUpdateExclusiveLock, so it runs against a live table. Returns the number of groups retired.

A group counts as fully deleted only when no transaction can still see its rows. While a transaction in the same database is older than the DELETE, compact retires none of those groups and can return 0. Such a transaction holds a snapshot or a transaction ID from before the DELETE committed. A prepared transaction or a replication slot holds rows back the same way. Call compact again after that transaction ends.

SELECT pgcolumnar.compact('events');

pgcolumnar.expire(tablename regclass) returns bigint

Drops row groups whose rows are all older than the retention declared by set_options. Returns the number of groups dropped. Holds only ShareUpdateExclusiveLock, so it runs against a live table.

This deletes rows. It runs when you call it, or when the maintenance daemon calls it with both pgcolumnar.autovacuum and pgcolumnar.autovacuum_expire on. The second is off by default. No other operation applies a retention, and VACUUM never does.

Each group it drops is recorded in pgcolumnar.retired_group, in the same transaction. An incremental rollup reads that record to keep the summary of rows that retention dropped (#1294). No other operation writes there.

It reads no data. A row group records the maximum value of each of its columns. A group whose maximum is older than the cutoff holds no row still inside the retention. Its metadata is dropped without decoding anything.

A group is kept whole or dropped whole. A group holding rows on both sides of the cutoff is kept, and its expired rows stay until every row in that group has expired. Retention is therefore approximate at the group boundary, and it errs toward keeping data. A smaller stripe_row_limit narrows the boundary.

The retention column must be date, timestamp or timestamptz. The table must have both ttl_column and ttl_interval declared, or the function raises an error rather than reporting that it did nothing.

On a date column the cutoff is the day containing now() - ttl_interval. A row dated exactly on that day is kept, so a date retention keeps a row up to a day longer than the interval asks. That is the same direction the group boundary errs in, and for the same reason. It applies to a whole-day interval too, because the cutoff is measured from the current instant rather than from midnight.

The cutoff is the calling session's date, not the server's. expire converts the current instant through the session's TimeZone. Two sessions can therefore retire different groups. Take a three-day retention, with the server at 2026-09-22 03:21+00. A session in UTC cuts at 2026-09-19. One in Pacific/Midway cuts at 2026-09-18, a day earlier. A timestamptz column does not have this behaviour, because it carries its own zone. Set TimeZone explicitly in the session that calls expire if you need the boundary to be the same every time.

expire works on whole row groups and never rewrites them, so it drops a group only when every row in it is past the retention. A group that straddles the cutoff stays whole, and rows older than the retention survive in it.

It decides from row-group metadata alone on a table with no deletes. On a table that has any deletes it reads the retention column of a candidate group. The recorded NULL count is written once, so a NULL that has since been deleted must not go on pinning the group. Nothing is rewritten either way.

One live NULL in the retention column pins its entire row group. A NULL has no age, so the group cannot be known to be wholly expired. Deleting the NULL rows releases the group, and the next expire can drop it. This is stronger than the straddling rule above. A straddling group is released once its newest row ages past the cutoff. A group holding a NULL never is. Keep the retention column NOT NULL if you want expire to reclaim the space.

SELECT pgcolumnar.set_options('events', ttl_column => 'ts',
                              ttl_interval => '90 days');
SELECT pgcolumnar.expire('events');    -- returns groups dropped

pgcolumnar.compact_rewrite(tablename regclass, min_deleted_fraction float8 DEFAULT 0.2, max_groups int DEFAULT 0) returns bigint

Rewrites partially-deleted row groups, those whose deleted fraction is at least min_deleted_fraction, to drop their dead rows and reclaim the space, under ShareUpdateExclusiveLock. max_groups caps how many groups a single call rewrites; 0 means no cap. Returns the number of groups rewritten.

min_deleted_fraction must be a number from 0 to 1, both ends included. The function rejects NaN, a negative value and a value above 1. Each raises SQLSTATE 22023, invalid_parameter_value. NaN needs a test of its own because it compares false against every bound. An accepted NaN would match no row group, so the call would reclaim nothing and still report success.

SELECT pgcolumnar.compact_rewrite('events', 0.3);

pgcolumnar.truncate(tablename regclass) returns bigint

Gives the reclaimed blocks at the end of the file back to the operating system. The function does what it can. It takes AccessExclusiveLock for the short physical step, but only if the lock is available. If the table is busy, the function returns 0 and does not wait. It removes only the space that became free before the oldest-xmin horizon. Gated by pgcolumnar.enable_end_truncation, which is off by default. Returns the number of blocks truncated.

SELECT pgcolumnar.truncate('events');

pgcolumnar.vacuum_full(schema name DEFAULT 'public', sleep_time real DEFAULT 0.0, stripe_count int DEFAULT 0)

Runs pgcolumnar.vacuum on every columnar table in a schema. sleep_time is a pause in seconds between tables.

stripe_count is passed through to pgcolumnar.vacuum and is subject to the same restriction: a non-zero value raises an error.

SELECT pgcolumnar.vacuum_full('public');

pgcolumnar.analyze(rel regclass, columns text[] DEFAULT NULL)

Collects planner statistics for named columns of a columnar table. It reads each column once instead of sampling whole rows. PostgreSQL 18 or later. On 17 and below it raises an error. It writes through pg_restore_attribute_stats, which does not exist before 18.

SELECT pgcolumnar.analyze('events', ARRAY['customer_id']);   -- one column
SELECT pgcolumnar.analyze('events');                         -- every column

This is not a faster ANALYZE. That difference decides whether you want it. It reads every value of the columns you name. Core samples 30,000 rows, whatever the size of the table. Measured on 3,000,000 rows across 20 columns:

	time
core ANALYZE, all 20 columns	7,169 ms
pgcolumnar.analyze(rel, ARRAY['k']), 1 column	1,092 ms
pgcolumnar.analyze(rel), all 20 columns	90,611 ms

It wins when you want a subset of the columns of a wide table. Core cannot do that at all. ANALYZE t (k) still materialises whole rows, so naming one column saves about 6 percent. It loses badly for every column. On this shape the crossover is two to three columns. Prefer core ANALYZE unless you analyse a few columns of a wide table.

The extra cost buys exactness. A sample can miss a value held by one row in 500,000. It also collapses range estimates above the largest value it saw. This function reads the column, so the frequencies and the bounds are counted.

Statistics written, for each named column:

statistic
null_frac	exact, from the same read as the rest
n_distinct	exact
most_common_vals	exact, and with no significance filter
most_common_freqs	exact
histogram_bounds	over the rows the most-common list does not hold

Core applies a significance filter to its most-common list because that list is sampled. This one is counted, so the filter does not apply.

Not written: correlation, and nothing at the level of the relation. pg_class.reltuples stays at -1 after this function. That looks alarming and is not. The row estimate of a columnar table comes from the access method, not from the catalog, so plans still get the right row count. Run core ANALYZE if you want the catalog populated.

Nothing schedules this. Autovacuum does not call it. The same is true of pgcolumnar.vacuum, but the consequence differs. A stale vacuum wastes space. Stale statistics produce bad plans, and they do it silently. Re-run this function when the data changes, or keep core ANALYZE scheduled and use this only to sharpen particular columns.

pgcolumnar.stats(rel regclass)

Requires SELECT on the table. The function runs with the privileges of the extension owner, because it reads pgcolumnar's own catalog tables. It checks the calling role's privilege on the table before it returns anything.

Returns one row per row group, with these columns:

Column	Type	Meaning
stripeid	bigint	Row group number within the table.
fileoffset	bigint	Byte offset of the row group in the relation file.
rowcount	bigint	Rows written into the row group.
deletedrows	bigint	Rows in the row group marked deleted.
chunkcount	integer	Vectors in the row group.
datalength	bigint	On-disk length of the row group in bytes.

-- total live rows, deleted rows, and size
SELECT sum(rowcount) AS rows,
       sum(deletedrows) AS deleted,
       pg_size_pretty(sum(datalength)) AS size
FROM pgcolumnar.stats('events');

pgcolumnar.sort_status(rel regclass)

Reports how much of a table's sorted order is still in place. Returns one row:

Column	Type	Meaning
sort_key	name[]	The clustering key in effect. It is the key the last ordering rewrite recorded, or the sort_by declared by set_options, or NULL.
sorted_kind	text	How that key is applied: lexicographic, zorder, or NULL. NULL means the table was never ordered, or was ordered before pgColumnar recorded this.
total_groups	bigint	Row groups in the table.
sorted_groups	bigint	Row groups written by the last ordering rewrite.
appended_groups	bigint	Row groups written after it.
sorted_rows	bigint	Rows stored in the sorted groups.
appended_rows	bigint	Rows stored in the appended groups.

pgcolumnar.vacuum_sorted and pgcolumnar.cluster order a table once. Rows inserted afterwards go in at the end, in insertion order. The sorted part therefore shrinks in proportion as the table grows. This function measures that proportion, so you can decide when another sort is worth its cost.

-- what fraction of the table is still in sorted order, and by what arrangement
SELECT sort_key,
       sorted_kind,
       sorted_rows,
       appended_rows,
       round(100.0 * sorted_rows / nullif(sorted_rows + appended_rows, 0), 1)
         AS percent_sorted
FROM pgcolumnar.sort_status('events');

A table that was never sorted reports zero sorted groups. An unsorted pgcolumnar.vacuum returns it to that state, because it rewrites the table without ordering it.

sort_key names the columns, not the arrangement. pgcolumnar.vacuum_sorted sorts on those columns in order. pgcolumnar.cluster and pgcolumnar.recluster arrange the same columns on a Z-order curve, which is not a sort on any one of them.

Read sorted_kind to tell the two apart. Before 1.0-alpha3 this function could not report it, and the documented way to read it was pgcolumnar.storage. That table carries no GRANT, so only a superuser could follow that advice.

The row counts are stored rows. Deleted rows stay stored until a maintenance operation reclaims them, so they are still counted here. Use pgcolumnar.stats to read the deleted count per group.

Three limits apply. The online pgcolumnar.recluster records only the part of its output it can prove is one contiguous ordered run. With no concurrent writer that is the whole relation. If another session inserts while it runs, it records less, sometimes much less, and reports the rest as decay. It never reports less decay than there is. The counts describe where rows are stored, not whether their values are still in order. An UPDATE stores the new row version at the end, which counts as appended. The record is internal storage metadata and pg_dump does not carry it, so a restored table reports no sorted groups until you sort it again.

pgcolumnar.column_extent(rel regclass, col name)

Shows the bound the planner uses for a column's smallest and largest value, when it estimates how many time buckets a GROUP BY date_trunc(...) or date_bin(...) produces. Returns one row, or none:

Column	Type	Meaning
complete	bool	Always true when a row is returned.
minimum	text	The smallest value, printed by the column's type. NULL if the column holds no non-NULL value.
maximum	text	The largest value, printed the same way.

The bound is kept in pgcolumnar.column_extent, and the planner reads it in one index lookup however many row groups the table has. Writes widen it when they commit. Every operation that drops or rewrites row groups recomputes it before committing: expire, compact_rewrite, VACUUM, recluster. So it can be wider than the data only by rows that are deleted but still stored.

It needs SELECT on the table, and it refuses a table whose row-level security policies apply to the caller (see limitations).

No row means the planner reads every row group's zone map instead, which gives the same answer at a cost that grows with the table. That happens for:

• a column added after some row groups were written;
• a type with no ordering;
• a table written by an earlier version, until its next recompute.

The values are column data, so the caller needs SELECT on the table.

SELECT minimum, maximum FROM pgcolumnar.column_extent('events', 'ts');

pgcolumnar.maintenance_due(rel regclass, compact_due_fraction float8 DEFAULT 0.2, recluster_due_fraction float8 DEFAULT 0.05)

Reports whether an online maintenance verb is worth running on a table, from its statistics alone. It takes no lock and rewrites nothing. This is the policy the pgcolumnar.autovacuum daemon consults on each sweep. You can also call it from a monitoring query. Returns one row:

Column	Type	Meaning
total_rows	bigint	Stored rows, including deleted rows not yet reclaimed.
deleted_rows	bigint	Rows deleted but still stored.
deleted_fraction	float8	deleted_rows over total_rows.
sort_key	name[]	The recorded clustering key, or NULL.
appended_groups	bigint	Row groups written after the last ordering.
appended_rows	bigint	Rows in those appended groups.
appended_fraction	float8	Appended rows over the sorted plus appended rows.
compact_rewrite_due	boolean	True when deleted_fraction reaches compact_due_fraction.
recluster_due	boolean	True when a sorted run exists and appended_fraction reaches recluster_due_fraction.
recommendation	text	The verbs to run, comma-separated, or NULL when nothing is due.

The two thresholds default to the values the daemon uses. Each must be a number from 0 to 1, both ends included. The function rejects NaN, NULL, a negative value and a value above 1. Each raises SQLSTATE 22023, the same code and the same bounds as pgcolumnar.compact_rewrite. An unchecked threshold fails silently rather than loudly. NaN, NULL or a value above 1 reports nothing as due, which suppresses maintenance for good. A negative value reports every table as due on every sweep.

The function is SECURITY DEFINER and checks that the caller may SELECT the table. A monitoring role that owns the table can therefore call it without superuser rights.

SELECT recommendation FROM pgcolumnar.maintenance_due('events');

Projections

A projection is a named subset of a table's columns stored a second time, optionally sorted on a key. When a projection covers a query and serves it better than the base table, the planner scans the projection instead. See Administration.

pgcolumnar.add_projection(rel regclass, name text, columns text[], sort_key text[] DEFAULT '{}')

Declares a projection on rel named name, storing columns, sorted on sort_key. When you add the projection, pgColumnar fills it with the rows that exist.

SELECT pgcolumnar.add_projection(
    'events', 'events_by_customer',
    columns  => ARRAY['customer_id', 'amount', 'ts'],
    sort_key => ARRAY['customer_id']);

pgcolumnar.drop_projection(rel regclass, name text)

Drops a projection and frees its storage. It also removes the declaration, so a later rebuild does not create the projection again.

SELECT pgcolumnar.drop_projection('events', 'events_by_customer');

pgcolumnar.rebuild_projections(rel regclass DEFAULT NULL)

Builds each declared projection that has no storage, and returns the number that it built. Give a relation to limit it to one table. Give no argument to cover each table in the database.

pg_dump carries the projection declarations, in pgcolumnar.projection_declaration, but it cannot carry the projection storage. Run this after a logical restore. A second run builds nothing, so it is safe to run at any time.

You no longer need it after a rewrite. TRUNCATE, a rewriting ALTER TABLE, REFRESH MATERIALIZED VIEW and the maintenance rewrites re-record their projections themselves.

Two cases still need this function. The first is a logical restore. The second is a rewrite that does not pass through ProcessUtility, so nothing observes it. A TRUNCATE replicated to a subscriber is one: the logical replication worker calls ExecuteTruncateGuts directly. Run this on the subscriber after one.

A third case looks like this function's job and is not. A declaration that names a column the table no longer has is reported by a rewrite as

WARNING:  42703: could not restore projection "p" on "public.t" after rewrite
DETAIL:   Its declaration names a column the table no longer has.

Do not run this function for that. It re-runs the same declaration and raises the same missing-column error. Call pgcolumnar.add_projection() again instead, with the same projection name and columns the table has, which is what the HINT on that WARNING tells you.

SELECT pgcolumnar.rebuild_projections();

pgcolumnar.read_projection(rel regclass, name text) and pgcolumnar.reconstruct_via_projection(rel regclass, name text)

Return a projection's stored rows as text, for verification. They are for inspection and testing, not for query use.

Import and export

These functions read and write Arrow IPC files and Parquet files. export_arrow writes the IPC stream format; import_arrow reads the stream format and the IPC file format. They read and write files on the server host, so a reader needs the pg_read_server_files role and a writer needs the pg_write_server_files role. Superusers hold both. The Parquet functions also read from and write to object storage. See Object storage. They run on little-endian hosts only. They support scalar column types, one-dimensional arrays, and composite types, with nulls at every level. The functions refuse multi-dimensional arrays and types that they do not support. See Limitations and compatibility.

pgcolumnar.export_arrow(rel regclass, path text) returns bigint

Writes the live rows of rel to an Arrow IPC stream file at path. Returns the number of rows written. The file has no ARROW1 header or footer, so pyarrow reads it with pyarrow.ipc.open_stream, not open_file. path can be a local file or an s3:// URL. See Object storage. A record batch holds at most 16384 rows and at most pgcolumnar.export_group_bytes of buffered data, 256 MiB by default, unless one row alone is larger. See Export size limits.

pgcolumnar.export_parquet(rel regclass, path text) returns bigint

Writes the live rows of rel to a Parquet file at path. Returns the number of rows written. path can be a local file or an s3:// URL. See Object storage. A row group holds at most 65536 rows and at most pgcolumnar.export_group_bytes of buffered data, 256 MiB by default, unless one row alone is larger. See Export size limits.

pgcolumnar.export_parquet(rel regclass, path text, server name) returns bigint

Writes the file as the two-argument form does, but a remote path is written with the settings of server. These are the settings that a foreign table of server reads with, so a foreign table on path can read the file back.

• The endpoint and region come from the server's endpoint and region options. If the server has no such option, the value comes from AWS_ENDPOINT_URL or AWS_REGION in the environment, whoever the caller is. A read does the same.
• The credentials come from your user mapping on the server, or from its PUBLIC mapping. The environment's keys are used only where a read may use them: see Endpoints and credentials. A role that is not a superuser and has no mapping is refused (28000).

The function refuses these before it writes anything:

• a null server (22004);
• a server that does not exist (42704);
• a server whose wrapper does not have pgcolumnar's Parquet handler, pgcolumnar.parquet_fdw_handler (42809). A wrapper made with that handler under another name is accepted, as tier_partition accepts it (#1963);
• a caller without USAGE on server (42501).

A local path writes no object and uses no endpoint, region or key. The function still makes every check above and still looks up your credentials on the server. So a mapping that holds only half a credential refuses a local path too (28000).

pgcolumnar.partition_create_ahead(parent regclass, width interval, ahead int) returns int

Creates columnar partitions of parent, each width wide, until they reach now plus ahead widths. Returns the number of partitions created. A second call creates none. The first new partition starts at the highest existing upper bound. With no partitions, it starts at the width's boundary that holds now. parent must be partitioned by RANGE on one timestamp or timestamptz column. It must have no DEFAULT partition and no partition bounded by MAXVALUE (55000). A call that would create more than 1000 partitions is refused before it creates any (22023). The function takes SHARE UPDATE EXCLUSIVE on the parent, never ACCESS EXCLUSIVE. See Create time partitions ahead.

pgcolumnar.partition_drop_plan(parent regclass, cutoff timestamptz | timestamp) returns setof text

Returns the statements that retire every partition of parent whose upper bound is at or before cutoff, in range order. Each partition gets ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, then a DO block that runs DROP TABLE, or DROP FOREIGN TABLE for a partition tier_partition moved to Parquet (the file stays). The block refuses (55000) while the partition is still attached, so a failed detach is never followed by a drop that locks the parent. A partition that an interrupted concurrent detach left pending gets DETACH PARTITION ... FINALIZE instead. A partition that is itself partitioned is listed whole. The names are schema-qualified. The function runs nothing: run each statement at top level, for example with \gexec, because a concurrent detach cannot run inside a function. cutoff must have the key's type; the other type is refused (42804), and an untyped literal is a timestamptz. It refuses a parent with the same codes as partition_create_ahead (55000), and a NULL cutoff (22023). pgcolumnar.partition_drop_candidates(parent, cutoff_type) holds the checks the two overloads share. See Drop old time partitions.

pgcolumnar.create_rollup(rollup name, source regclass, ts_column name, width interval, origin timestamptz, group_by name[], agg_functions text[], agg_columns name[], access_method name DEFAULT 'pgcolumnar') returns regclass

Creates a rollup of source in the source's schema. A rollup is a view of date_bin(width, ts_column, origin) buckets per group_by key, with one column per aggregate. Each aggregate is a function from agg_functions (count, sum, min, max or avg) applied to the column at the same position in agg_columns. * goes only with count. ts_column must be timestamptz. The data is kept in a table named <rollup>__partials, with access method access_method. The function refuses a width with months, an unknown function, arrays of different lengths, and a column the source does not have (22023). See Keep a rollup up to date.

pgcolumnar.refresh_rollup(rollup regclass) returns bigint

Brings the rollup up to date in one statement, and returns the number of source rows it read. It folds the rows of the row groups added to the source since the last refresh. For each row group deleted from or rewritten since, it recomputes from the source the buckets that the group's rows fall in, and nothing else. Runs at READ COMMITTED only (25000).

Row groups that pgcolumnar.expire retired are dropped from the refresh's record, and the rollup keeps their buckets as history. Refuses, changing nothing, when a bucket to recompute holds history (0A000), and after a rewrite of the source (55000).

pgcolumnar.row_tid(row_number bigint) returns tid

Returns the item pointer that a columnar table gives the row with this row number. Row numbers start at 1 (22003 otherwise).

pgcolumnar.rebase_rollup(rollup regclass) returns bigint

Records the source's current row groups as folded, without changing the rollup, and returns the number of row groups recorded. It reads every row group to record the time range of its rows, and keeps the rollup's history. Use it after a rewrite of the source, or after a refusal to recompute history, when the rollup is right as it stands.

pgcolumnar.tier_partition(partition regclass, server name, path text, lock_wait interval DEFAULT '10 seconds') returns bigint

Moves the columnar partition partition to a Parquet file at path. Then attaches the file as a foreign table of server. The server's wrapper must have pgcolumnar's Parquet handler, pgcolumnar.parquet_fdw_handler. That is the pgcolumnar_parquet wrapper, or a wrapper you made with that handler under another name (#1963). The foreign table takes the partition's name, columns (with their collations and generated expressions), owner, bounds, grants and comment. Returns the number of rows moved.

The function writes the file with the settings of server, as export_parquet(partition, path, server) does: the server's endpoint and region, and your user mapping on the server. A mapping that the object store rejects stops the move before anything is detached, and the rows stay in the columnar partition.

After the attach, the function counts the rows through the new foreign partition, as you, before it commits. If that read fails or counts a different number, the move rolls back and the rows stay in the columnar partition. This catches a key that can write but not read. The file that the export wrote stays in place; a retry with the same path overwrites it.

You need both pg_write_server_files and pg_read_server_files, or superuser: the function writes the file and then reads it. Every role that queries the parent after the move needs pg_read_server_files and a mapping on server that can read the file.

The function runs as one transaction, at READ COMMITTED only (25000 otherwise). It holds ACCESS EXCLUSIVE on the parent from the detach until commit. Every lock waits at most lock_wait (55P03).

The function refuses these:

• a path that a foreign table in this database reads (58P02). That is the same path, a path under the table's directory, or a path that matches the table's glob (#1941). A glob is matched by path_glob_matches, as the reader matches it (#1957). The scheme and the host of an http(s) URL match in any case, and s3a:// or s3n:// match s3://. A local path is compared as written, and again in a folded form (#1965, #1959). The folded form reads // as /, drops a . name, and drops a name followed by ... In a glob, a name such as \. counts as ., because glob(3) removes the backslash. So /w//*.parquet, /w/./*.parquet, /w/\./*.parquet and /w/x/../*.parquet refuse /w/a.parquet. The folded form is the file the kernel opens only when no symbolic link comes before a ... When one does, the kernel goes up from the directory the link leads to. The check can then refuse a file the reader does not read, and allow the file it does read. A symbolic link is not resolved, and a relative path is not resolved against the data directory. A remote key is compared as written, because // is part of a key. A new file under a directory a foreign table reads is refused too; docs/how-to.md shows ways around it. Only a wrapper with pgcolumnar's Parquet handler has its path option read. For a pgcolumnar_iceberg foreign table, a path under the table location is refused: the parent of the directory that holds metadata_path (#1949). The same two comparisons apply to that location. The Iceberg reader resolves a symbolic link in it, but this check does not. So a path in the directory the link leads to is not refused. The error detail names the foreign table and its path. A control character in either prints as \xHH, such as \x0A for a newline, so the detail stays on one line (#1960). A backslash before an x prints as \x5C, so a path that holds the characters \x0A does not print like a newline. In a WIN1252 database the euro sign, byte 0x80, prints as written;
• a path when a foreign table's glob is too large to match (54000, see path_glob_matches below). This is a local glob for a local path, and an s3:// glob with more than 128 * for an s3:// path. The function cannot tell whether that table reads path, so it refuses. The error detail names the foreign table and its glob, escaped as above (#1974). A table that does read path is reported as 58P02 instead. This holds also when its glob is too large as written but not in the folded form. The reader bounds a local glob in its own folded form too, so it reads such a table (see Glob patterns too large to match);
• a DEFAULT partition (0A000);
• a parent with a UNIQUE index or primary key (0A000);
• a partition that other objects depend on (2BP01);
• a relation that is not a columnar partition (42809);
• a server whose wrapper does not have pgcolumnar's Parquet handler (42809);
• a caller that is not a member of both pg_write_server_files and pg_read_server_files (42501), before anything is written.

See Move cold partitions to Parquet.

pgcolumnar.path_glob_matches(pattern text, path text) returns boolean

Returns true when a foreign table whose path is pattern expands it, as a glob, to path (#1957). tier_partition uses it to find a file that a foreign table reads. It matches with the Parquet reader's own code:

• For an s3:// key, the reader's fnmatch, with FNM_PATHNAME and FNM_PERIOD. The scheme matches in any case, and s3a:// and s3n:// match s3://.
• For a local path, glob(3), as the reader calls it. The function runs it over a tree in memory that holds only path, so it reads no directory and path need not exist. A path that ends in / is a directory; otherwise its last name is a file.

It returns false for a pattern that the reader does not read as a glob:

• a local path without *, ? or [;
• an s3:// URL with none of them in its key;
• any other remote URL.

A local pattern does not match a remote path. A path of 4096 bytes (PATH_MAX) or more returns false, because no file can be opened by such a path.

A pattern that is too large for glibc to match safely raises error 54000 (program_limit_exceeded) instead of an answer:

• a pattern of 4096 bytes or more;
• a local pattern with more than 128 / after its first *, ? or [;
• an s3:// pattern with more than 128 *.

glob(3) uses stack space for each / after the first *, ? or [. fnmatch uses stack space for each *. A larger pattern could crash the server. The Parquet reader refuses the same patterns with the same error (#1968, #1972). It counts the levels of a local pattern in a folded form; see Glob patterns too large to match.

A local pattern that makes the walk too long also raises error 54000 (#1973). Each directory in the tree in memory lists . and .., as a real one does, and a name such as .* matches both. So each level of /.* doubles the walk. The function stops after 1,000,000 steps of the walk. The number of steps depends on the glibc glob(3) code, not on the PostgreSQL version. With glibc 2.43, repeat('/.*', 17) still answers, and repeat('/.*', 18) is refused after about 60 ms. The function never answers false for a walk it stopped. The step count starts again at zero for each call, so a refused pattern does not affect later calls in the same session.

For a local pattern, the function compares each path glob(3) returns with path, first as spelled and then in a folded form (#1982). The folded form reads a run of / as one, and drops . names and a name followed by ... So /w//x matches /w/x*. glob(3) lists . and .. in each directory, and a name that spells the leading dot matches them, such as .*, .?, .[.] or \.*. So /w/x/.*/a.parquet matches /w/x/a.parquet, through /w/x/./a.parquet. A *, ? or [.] alone does not match a leading ., so /w/x/*/a.parquet does not match /w/x/a.parquet. The directories that the pattern names before its first *, ?, [ or \ are taken to exist, so /w/x/sub/.?/a.parquet matches /w/x/a.parquet, through sub/... The function cannot know which directory a wildcard matched. So when a wildcard name comes before a name that can match .., it also assumes that a directory the wildcard matches exists (#1991). It assumes as well that each name the pattern spells there exists as a directory. So /w/x/*/.?/a.parquet matches /w/x/a.parquet, which the reader reads when /w/x holds any directory. Without such a directory, tier_partition then refuses more than it must. A name such as ?? cannot match .., so /w/x/*/??/a.parquet does not match /w/x/a.parquet. If the walk with these assumptions passes 1,000,000 steps, the function answers true. The folded form does not resolve a symbolic link before a ... So it can match a file the reader does not open, and tier_partition then refuses more than it must. An s3:// key is not folded: // and . are part of a key. tier_partition also compares the folded forms of the foreign table's path and the target. So it refuses what the reader opens under either spelling, unless a symbolic link comes before a .. (#1965).

SELECT pgcolumnar.path_glob_matches('s3://b/x[a/b]', 's3://b/xa');  -- true
SELECT pgcolumnar.path_glob_matches('/w/x[a/b]', '/w/xa');          -- false

In an s3:// key a class can match /. A local glob is split at every / first, so there [a and b] are two names. It reads no file, so any role may call it.

A range such as [a-c] follows the database's LC_COLLATE setting (datcollate), as in the reader. In en_US.UTF-8, [a-c] matches á; in C it does not. A database with the ICU provider still uses its LC_COLLATE, not its ICU locale. Take a range that ends in a collating symbol, such as [!-[.a.]]. In a UTF-8 locale such as en_US.UTF-8, glibc can answer it differently from one call to the next. The reader has the same limit, because it calls the same fnmatch. So the function is VOLATILE, not IMMUTABLE: do not use it in an index or a generated column. It is STRICT and PARALLEL SAFE.

pgcolumnar.import_arrow(rel regclass, path text) returns bigint

Inserts the rows of an Arrow IPC file at path into the existing table rel. The column types of the table define the types that the function accepts. Returns the number of rows inserted.

path is a local file. An object-storage URL is refused with 0A000; see Object storage. The function checks INSERT on rel first, so a caller without INSERT gets 42501 for a URL too.

The file can be in the IPC stream format, which export_arrow writes, or in the IPC file format, which starts and ends with ARROW1. Feather v2 is the IPC file format. The function reads the stream inside an IPC file and ignores its footer. A message or body length larger than the rest of the file is refused with XX001 before any memory is allocated for it.

Temporal columns are read in the unit the file declares, not the unit export_arrow writes. A date column accepts date32 and date64. A time column accepts time32 in seconds or milliseconds, and time64 in microseconds or nanoseconds. A timestamp column accepts any of the four Timestamp units. Values are converted to PostgreSQL's own units, and a value that cannot be represented is refused with 22008 rather than stored wrong.

Nanoseconds are narrowed to microseconds, the finest resolution PostgreSQL stores. Narrowing floors, so an instant before 1970 reports the microsecond it falls in. Sub-microsecond precision is lost; nothing else is.

A temporal Arrow type the target column cannot hold is refused with 42804 rather than read. A time64 file does not import into a date column. A file whose type is not temporal is unaffected.

A nested Arrow type the target column cannot hold is refused with 42804, and the message names the column (#1932). An array column reads one List level. A Map reads into an array of a two-field composite. A list of lists, or a plain column, is refused for an array column. A list, map or struct is refused where the column, or an array's element type, is a scalar. A large list, fixed-size list, list view, union or run-end encoded column is refused for every column type. A file whose offsets run past its buffers is still refused with XX001.

pgcolumnar.import_parquet(rel regclass, path text) returns bigint

Inserts the rows of a Parquet file at path into the existing table rel. The reader handles uncompressed, Snappy, GZIP, ZSTD, and LZ4_RAW pages, PLAIN and dictionary encodings, and data-page versions 1 and 2. path may name a single file, a directory (every *.parquet file below it is imported, at any depth), or a glob pattern. Returns the number of rows inserted.

-- round-trip a table through Parquet
SELECT pgcolumnar.export_parquet('events', '/tmp/events.parquet');   -- returns row count
CREATE TABLE events_copy (LIKE events) USING pgcolumnar;
SELECT pgcolumnar.import_parquet('events_copy', '/tmp/events.parquet');

-- import an entire directory of Parquet files
SELECT pgcolumnar.import_parquet('events_copy', '/data/events/');

pgcolumnar.parallel_copy(target regclass, filename text, workers int DEFAULT NULL, dedup boolean DEFAULT false) returns bigint

Loads a text file into a columnar table with several background workers at once, as one atomic operation. Returns the number of rows loaded. The caller needs membership in the pg_read_server_files role, which superusers hold, and INSERT on the target. The file uses COPY text format. Each worker runs core COPY over a byte range of the file, so parse and write behavior match COPY FROM exactly. filename is a local file. An object-storage URL is refused with 0A000; see Object storage. The function checks INSERT on target first, so a caller without INSERT gets 42501 for a URL too.

The target may be one of two kinds:

• A single columnar table. The workers write the one table together. Any record-aligned split of the file is correct, so the file needs no ordering.
• A RANGE-partitioned table whose partitions are columnar. Each worker loads a distinct set of partitions. The file must be sorted ascending by the partition key. The key column may sit anywhere in the row, and its type must be numeric or a date/time type. The function reports an error when the file is not sorted.

The load is atomic. Each worker prepares its transaction rather than committing, and a coordinator commits them together only if every worker succeeded. A bad row, a full disk, or a constraint failure in any range rolls the whole load back. The target keeps its earlier contents. The load runs in background workers, so it commits on its own. It is not part of the calling transaction, and a caller ROLLBACK does not undo it. Set max_prepared_transactions above the worker count, because the load prepares one transaction per worker.

When workers is omitted the function derives a value from the target. For a partitioned target it lowers workers to the partition count when the count is smaller.

Refusing a load this table has already taken

A load that commits, whose acknowledgement the client never receives, is retried, and the rows go in twice. Pass dedup => true to refuse the repeat.

With dedup, the function records the SHA-256 of the file after a successful load, in pgcolumnar.load_fingerprint. A later load of a file with the same contents into the same table stores nothing, returns 0, and raises a NOTICE saying why. It does not fail. A file whose contents changed is a different load and is stored, even at the same path.

dedup is off by default. Discarding rows a caller asked to store is not ordinary INSERT behavior, so it happens only when asked for, and only on this function.

Three limits apply.

The fingerprint is recorded after the data commits. A crash between the two leaves the data stored and unrecorded, so a later retry stores it again. That is the behavior without dedup and is the safe direction.

Two identical loads running at the same time both store their rows. Each checks the record before either writes it. dedup refuses a load that follows a completed one; it does not serialize concurrent loads.

A refused load still does the work. The rows are read, parsed and written, and then discarded without being made visible, because the check happens after every worker has prepared. The alternative costs every load a serial pass over the file before any worker starts. Measured on a 264 MiB file, that pass takes 42% of the load, while the fingerprint as implemented has no measurable cost.

-- refuse a repeat of a load this table has already taken
SELECT pgcolumnar.parallel_copy('events', '/data/events.txt', 8, true);

-- single columnar table, any row order
CREATE TABLE events (id bigint, ts timestamptz, val double precision) USING pgcolumnar;
SELECT pgcolumnar.parallel_copy('events', '/data/events.txt', 8);   -- returns row count

-- RANGE-partitioned target, file sorted ascending by the partition key
SELECT pgcolumnar.parallel_copy('events_by_day', '/data/events_sorted.txt', 8);

pgcolumnar.parallel_export_parquet(target regclass, path text, workers int DEFAULT NULL) returns bigint

Writes a columnar table to a directory of Parquet files with several read-only background workers at once. Returns the number of rows written. The caller needs membership in the pg_write_server_files role, which superusers hold, and SELECT on the target. The output directory must be empty. It is created if it does not exist. Each worker writes its own part-NNNN.parquet file, and pgcolumnar.read_parquet reads the whole directory back as one relation.

The path may also be an s3:// prefix. Each worker writes one part-NNNN.parquet object under it, exactly as for a local directory. No other remote scheme is accepted. The export lists its prefix, as the next sentences explain, and only an s3:// prefix can be listed. So a gs://, http:// or https:// prefix is refused with SQLSTATE 0A000 before any worker starts, and nothing is uploaded. See Object storage for the endpoint and credential rules, which are the same as for export_parquet. One difference applies to a remote prefix. The local path is created and checked for emptiness before the export. A remote prefix is not checked, so a re-run may write into a used prefix. Before it writes the _SUCCESS marker, the export lists the prefix to remove stale parts a larger prior run left. Its credentials therefore need list permission (s3:ListBucket on AWS) as well as write and delete. If the listing fails, those stale parts can remain, as described below under "A failed listing can leave a larger earlier run's parts".

The target may be one of two kinds:

• A single columnar table. The workers split it by row-group ranges, so each worker writes a distinct part of the table.
• A partitioned table whose partitions are columnar. Each worker takes a distinct set of partitions and writes one file per partition.

The export is read-only and consistent. The dispatcher exports one snapshot and every worker imports it. The files together are the committed image of the table at call time. This holds even when the call runs inside a transaction with uncommitted rows. There is no coordinator and no shared write state. If any worker fails, or the statement is cancelled, the dispatcher removes the files it wrote. It also removes them when every worker has finished but a later step fails: removing stale parts or writing the _SUCCESS marker. Over an s3:// prefix the dispatcher deletes the same objects by key. On a cancel or a dispatcher error, it first waits up to 5 seconds for the stopped workers to exit. It waits for a local directory and an s3:// prefix alike. A stopped worker that was committing its part waits for the store's answer. The store can apply that commit after a DELETE sent meanwhile. Waiting puts the DELETE after the commit. A local worker that was renaming its finished part has renamed it before it exits. A worker that is still running after 5 seconds, such as one waiting on a store that does not answer, can still leave its part. A worker terminated mid-upload can leave one incomplete multipart upload that the dispatcher cannot address. Set a bucket lifecycle rule that expires incomplete multipart uploads to reclaim it, as Exporting to object storage recommends.

A failed listing can leave a larger earlier run's parts. The cleanup above removes a failed run's own parts. So a partial directory is never left for read_parquet to union, with one exception: a failed listing of a remote prefix. A key that may write but not list fails the run, and the dispatcher removes the parts this run wrote. Without the listing, though, the export cannot find the higher-numbered parts that a larger earlier run left in the prefix, so they remain. A run of 4 workers followed by a failed run of 2 leaves part-0002 and part-0003, with no _SUCCESS marker. A later directory or prefix read would read those stale objects back as if they were a whole table. So check for _SUCCESS before you read a prefix, and read only a prefix that carries it.

On success the function writes an empty _SUCCESS marker at the destination, the Hadoop and Spark convention. Its presence means a complete run's output is there; a failed or cancelled run, whose part files the dispatcher removes, leaves none. The marker is written last, after every worker has finished, so a directory or prefix that carries it is a whole export. A remote prefix allows a re-run to overwrite a used prefix. A smaller re-run there first removes the stale higher-numbered parts a larger prior run left, so the marker certifies exactly the parts this run wrote. read_parquet and the foreign-data wrapper skip it when they read the directory or prefix. They skip any name below it that begins with an underscore.

When workers is omitted the function derives a value from the target.

-- single columnar table, split across 8 workers
SELECT pgcolumnar.parallel_export_parquet('events', '/data/events_out', 8);
-- read the whole directory back as one relation
SELECT count(*) FROM pgcolumnar.read_parquet('/data/events_out')
  AS t(id bigint, ts timestamptz, val double precision);

Reading external Parquet

These read a server-side Parquet file in place, without importing it. They require the pg_read_server_files role, which superusers hold, and operate on little-endian hosts. In each function, path can be one of three things. It can be a single file. It can be a directory, and then the function reads all the *.parquet files below it at any depth as one relation, in sorted order. It can also be a glob pattern. A local path can be any of these. An object-storage URL can be an exact key, and an s3:// URL can also be a prefix or a pattern. A remote prefix ends in /. Every remote read, of a single key as well as a prefix or pattern, needs the object-store module. See Object storage.

The readers return each value as the declared column type, including its type modifier. They apply it as PostgreSQL's input functions do. A numeric(p,s) column rounds each value to scale s. A value with more than p - s digits before the point is an error, SQLSTATE 22003 (numeric field overflow). A varchar(n) column refuses a longer value with SQLSTATE 22001 (value too long), unless the extra characters are spaces; those it drops. A timestamp(p) or time(p) column rounds to p fractional digits. This holds for read_parquet, import_parquet, iceberg_scan and both foreign-data wrappers, and for array elements and composite fields. So CREATE TABLE AS or INSERT ... SELECT from a reader never stores a value that breaks the declared type. A row that an Iceberg delete removes is not checked.

Glob patterns too large to match

The readers refuse a glob pattern that is too large for glibc to match safely, with error 54000 (program_limit_exceeded), before they read anything (#1968, #1972):

• a pattern of 4096 bytes (PATH_MAX) or more, measured for a local pattern in its folded form (see below);
• a local pattern with more than 128 / after its first *, ? or [, counted in its folded form;
• an s3:// pattern with more than 128 *. The reader refuses it before it lists the prefix.

The reader counts and expands a local pattern in a folded form. In that form, each run of / becomes one /, and a . name followed by / is dropped. The kernel opens the same file by either spelling, so the files read do not change. So '/w/s[p]' || repeat('/.', 200) || '/a.parquet' has 201 levels as written, but 1 in the folded form, and the reader reads /w/sp/a.parquet. With repeat('/.', 5000) the pattern is over 4096 bytes as written but not in the folded form, and the reader reads it too. Two names are kept as written:

• a . at the end, or a . that only / follow, because it makes the pattern match only directories. Without it, glob(3) matches a pattern that ends in / against a file, so '/w/s[p]/a.parquet/./' would read /w/sp/a.parquet (#1977 review);
• a name followed by .., because when that name is a symbolic link, the kernel goes up from the directory the link leads to.

A backslash before a / keeps the / from being folded with what follows it, and a name such as \. is not dropped. So tier_partition, which folds these as well as a name followed by .., can find fewer levels than the reader does.

glob(3) and fnmatch use stack space for each such level, and a larger pattern could crash the server. The limits are those of path_glob_matches. The reader checks the pattern before it looks for an object of that exact name. So an s3:// key that holds more than 128 * cannot be read by name.

The reader also bounds the walk of a local pattern on the filesystem (#1984). Each real directory lists . and .., and a name such as .* matches both. So each level of .*/ in a local pattern multiplies the walk. The reader counts each directory it opens, each name it reads and each stat as one step. It stops after 1,000,000 steps with error 54000, the error of path_glob_matches, and reads no file. The count covers every name the walk reads in every directory the pattern reaches, not only the names that match. So a glob fails when the directories it walks hold more than about 1,000,000 names in all. That includes a partitioned tree such as <tree>/*/*.parquet whose directories are each small: 400 directories of 2,600 names each are refused. Read such a tree as a directory instead. A directory read finds every *.parquet in its subdirectories and does not count steps. We measured on PostgreSQL 18 with glibc 2.43. A pattern of 18 levels of .*/ is refused after about 0.25 s. A glob that matches 2000 files among 12,000 names reads every match.

A cancel, a statement_timeout, a transaction_timeout (PostgreSQL 17 and later) or pg_terminate_backend stops the walk. Before, the walk did not stop for any of them: with 15 levels of .*/ it ran for minutes, and only SIGKILL stopped it. An interrupt that raises no error, such as the one client_connection_check_interval sets, does not stop the walk; the reader handles it once the walk ends.

If a cancel arrives while interrupts are held off, the reader cannot process it, and the walk stops again at once on every attempt. After 100 such attempts the read fails with error 57014, could not expand pattern "...": interrupted 100 times.

pgcolumnar.read_parquet(path text) returns setof record

Returns the rows of a Parquet file. You must supply a column definition list. It gives the names of the output columns and their types. The reader connects the list to the leaf columns of the file by position. It uses the same rules for type compatibility as the import functions.

The list must contain each leaf column in the file. A list with fewer columns is an error and not a projection. The read stops and gives a message. The message contains the number of leaf columns in the file and the number that the target expands to. Its DETAIL names the file, so a read of a directory, a prefix or a glob says which of its files does not match. Every other error about a file that does not match the list has the same DETAIL. The same rule applies to a foreign table's column definitions. Projection pushdown selects the declared columns that the reader decodes. This is a separate question from the number of columns that you must declare. Use parquet_schema to generate the full list: declare one column for each row it reports. A composite column covers all the leaves of its struct.

A string cell read into a text or varchar column must be valid in the server encoding (#1821). A cell with an invalid byte sequence or an embedded NUL byte stops the read with SQLSTATE 22021, as COPY does. The message names the Parquet column and the first bad bytes. This applies to the foreign table, the import functions and iceberg_scan too, because they share the reader.

SELECT * FROM pgcolumnar.read_parquet('/data/events.parquet')
  AS t(id int, ts timestamp, amount numeric(12,2));

-- read a whole directory
SELECT count(*) FROM pgcolumnar.read_parquet('/data/events/')
  AS t(id int, ts timestamp, amount numeric(12,2));

pgcolumnar.read_parquet(path text, field_ids integer[]) returns setof record

Returns the rows of a Parquet file, binding output columns to file columns by Parquet field id rather than by position. Output column i is bound to the file column whose field id equals field_ids[i]. The reader decodes only those columns, in the order given, whatever their order in the file. The array length must equal the column definition list length. This is the projection form Apache Iceberg uses (#388). A data file written before a column rename still carries the old name, and columns are selected by id.

The file must carry field ids. A file written without them is an error that directs you to the positional form above. Each requested id must match exactly one scalar column of a compatible type. A compatible type includes Iceberg's two exact widenings: an integer INT32 column reads as bigint, and a FLOAT column as double precision. The positional form above does not widen. An absent id is an error. So is an id that matches more than one column, or an array or composite output column. An id that belongs to a struct, list or map is refused as nested, whatever the output column's type (#1788). None is a silent wrong column. Read the ids a file carries with parquet_schema. It does not list the ids of a struct's fields; the parquet_schema section below says how to find them.

-- the file has columns alpha (id 7), beta (id 3), gamma (id 12);
-- read gamma then alpha, by id, in that order
SELECT * FROM pgcolumnar.read_parquet('/data/events.parquet', ARRAY[12, 7])
  AS t(g int, a int);

pgcolumnar.parquet_schema(path text) returns table(column_name text, data_type text, nullable bool, field_id int)

Reports the top-level columns of a Parquet file and the PostgreSQL type each maps to, without reading the data. Useful for writing the column definition list for read_parquet or a foreign table. For a directory or glob it describes the first file. The type it reports holds every value the column can store. So an unsigned UINT_32 column reports bigint, and a UINT_64 column reports numeric(20,0).

A millisecond or microsecond timestamp column that holds instants (isAdjustedToUTC=true, or the older TIMESTAMP_MILLIS or TIMESTAMP_MICROS type) is reported as timestamp with time zone. A column of wall times (isAdjustedToUTC=false) is reported as timestamp without time zone. Each advised type reads the value unchanged. See Timestamps and time zones.

A legacy INT96 timestamp column, which Spark, Hive and Impala write, is reported as timestamp with time zone. The reader takes an INT96 value as an instant in UTC, as Spark and Hive write it. Impala writes wall time instead, so its values read shifted; see Limitations. A read keeps whole microseconds and drops the nanosecond digits by rounding down. The column also reads into timestamp, which gets the wall time in the session time zone. A value whose nanoseconds fall outside the day, or whose Julian day is outside the timestamp range, is refused with SQLSTATE 22008. The DETAIL names both the Julian day and the nanoseconds the file stores.

The rows are the column definition list that read_parquet needs: declare one column per row, with the reported type. A nested column that read_parquet reads as one column gets one row under its own name:

• A list of a scalar reports that type's array type, for example bigint[].
• A struct of scalar fields reports its field list, for example composite (a bigint, b text). Create a composite type with those fields, CREATE TYPE s_t AS (a bigint, b text), and declare the column as s_t. A field name that needs quotes comes quoted, so the list works as written.

read_parquet reads any other nesting leaf by leaf, so parquet_schema reports one row for each leaf. A map, a list of structs, a struct of a struct and a struct that holds a list are examples. Each row is named by the leaf's path in the file, joined with dots, for example m.key_value.key. Its type is an array type when the leaf repeats. So a map with string keys and bigint values reports m.key_value.key as text[] and m.key_value.value as bigint[]. A dotted name must be quoted in a column definition list, or you can use any other name: the reader matches columns by position. Two rows can share a name, for example when the file also has a top-level column named s.t.list.element. Give one of them another name in your list.

data_type is NULL for a leaf that no column type reads correctly. A leaf under two repeated levels, such as the elements of a list of lists, is one example. read_parquet cannot read a file that holds such a leaf. Declared as an array, the leaf fails with SQLSTATE 42804, because a one-dimensional array would lose the inner lists. Declared as a scalar, it fails as unexpectedly repeated. read_parquet binds every column by position, so you cannot leave it out either. import_parquet and the pgcolumnar_parquet foreign table refuse the file the same way, even for a query that does not use that column (#1917).

nullable for a nested column says whether the column itself can be NULL. A NULL list element or struct field does not make it true. On a leaf row it is true when an OPTIONAL element sits on the leaf's path above its first repeated element.

field_id for a list or struct row is the column's own id. A leaf row carries the leaf's own id. The ids of a struct's fields do not appear when the struct has its own row. read_parquet(path, ids) still reads a struct field by its id, as a scalar column. Read those ids with another Parquet tool, for example pyarrow's ParquetFile(path).schema.

When a reader refuses a declared column type, the message names the function or foreign-data wrapper you used, for example column "c" has type money, which pgcolumnar.read_parquet does not support.

field_id is the Parquet schema field id the column carries. Formats such as Apache Iceberg use it to select columns by id rather than by name. It is NULL when the writer emitted no field id. A field id of 0 is a real value, so a NULL and a 0 mean different things.

SELECT * FROM pgcolumnar.parquet_schema('/data/events.parquet');

pgcolumnar.read_avro_manifest(path text) returns table(status int, content int, file_path text, file_format text, record_count bigint, file_size_in_bytes bigint, partition text, sequence_number bigint)

Decodes an Apache Iceberg Avro manifest file and reports its data-file entries. Each row is one entry: the file path, format, row count, byte size, partition, and the entry's data sequence number. The partition is rendered as name=value (see How a partition value is shown). The sequence number is NULL when the entry inherits it from the manifest. That is the usual case for a freshly written manifest. It is the ordering key for reading tables with deletes. A position delete applies to data files with a lower or equal sequence number, the same commit or earlier. An equality delete applies to a strictly lower one. The caller needs the pg_read_server_files role, which superusers hold. This is the first step of Iceberg support (#388). It is a standalone Avro object-container reader, decoded against the schema embedded in the file, so a v3 manifest reads structurally. It reads a local file. An object-storage URL is refused with 0A000; see Object storage. A caller without pg_read_server_files gets 42501 for a URL too. It does not resolve a table's snapshot or apply delete files. To read a whole table, use iceberg_scan.

SELECT file_path, record_count, partition
  FROM pgcolumnar.read_avro_manifest('/data/warehouse/db/events/metadata/abc.avro');

pgcolumnar.read_manifest_list(path text) returns table(manifest_path text, manifest_length bigint, content int, partition_spec_id int, added_files_count int, existing_files_count int, deleted_files_count int, added_rows_count bigint, existing_rows_count bigint, deleted_rows_count bigint, sequence_number bigint, min_sequence_number bigint, added_snapshot_id bigint)

Decodes an Apache Iceberg snapshot manifest-list Avro file and reports its manifest_file entries. Each row names one manifest the snapshot points at. It carries the manifest's length, content type (0 data, 1 deletes), and partition spec. It also carries the added, existing, and deleted file and row counts the writer recorded. The caller needs the pg_read_server_files role, which superusers hold. This is step two of Iceberg support (#388). It reads the same Avro object-container format as read_avro_manifest, decoded against the schema embedded in the file. A v3 manifest list therefore reads structurally. It reads a local file. An object-storage URL is refused with 0A000; see Object storage. A caller without pg_read_server_files gets 42501 for a URL too. It does not resolve a table's current snapshot or open the manifests it names. To read a whole table, use iceberg_scan.

SELECT manifest_path, added_files_count, added_rows_count
  FROM pgcolumnar.read_manifest_list('/data/warehouse/db/events/metadata/snap-123.avro');

pgcolumnar.iceberg_current_snapshot(metadata_path text) returns table(snapshot_id bigint, parent_snapshot_id bigint, sequence_number bigint, timestamp_ms bigint, operation text, manifest_list text, schema_id int)

Reads an Apache Iceberg table metadata.json and reports the current snapshot the table declares. The file may be plain or gzip-compressed (*.gz.metadata.json or *.metadata.json.gz). The current snapshot is the one whose id equals the file's current-snapshot-id. The row carries its sequence number, commit timestamp, and operation (append, overwrite, delete, replace). It also carries the manifest-list file the snapshot points at and its schema id. It returns one row, or no rows when the table has no current snapshot. The caller needs the pg_read_server_files role, which superusers hold. The metadata path can be a local file or an s3://, http:// or https:// URL, as for iceberg_scan. A remote path is read with the server process environment's credentials. The function reports the manifest_list path as the file records it, without opening it. Decode that file with read_manifest_list, or read the table with iceberg_scan.

SELECT snapshot_id, operation, manifest_list
  FROM pgcolumnar.iceberg_current_snapshot('/data/warehouse/db/events/metadata/v3.metadata.json');

pgcolumnar.iceberg_data_files(metadata_path text) returns table(file_path text, file_format text, record_count bigint, partition text)

Lists the live data files of an Apache Iceberg table at its current snapshot. It resolves the current snapshot from metadata.json, reads that snapshot's manifest list, then each manifest. It returns one row per data-file entry: the file path, format, row count, and partition rendered as name=value (see How a partition value is shown). The caller needs the pg_read_server_files role, which superusers hold. It returns no rows when the table has no current snapshot.

The absolute paths recorded in the table are rebased onto the table's actual location. That location is taken from where metadata.json sits. A table copied to a new directory therefore still reads. A recorded path that points outside the table location is refused, not read.

A data file that the current snapshot removed is not live, so it is skipped. A copy-on-write delete or a filtered overwrite records the file it replaced this way, as a manifest entry with status 2 (DELETED).

Delete files are refused, not ignored. A snapshot that carries a live delete file raises an error here. It does not return rows the table says are gone. To read a table that uses deletes, use iceberg_scan, which applies position, equality, and deletion-vector deletes. A delete file that the current snapshot removed is not live, so it is skipped too. A rewrite or compaction that removes every old delete file leaves a delete manifest that holds only such entries (#1829); that table lists normally. A delete manifest that holds a live data-file entry is corrupt, and is refused with SQLSTATE XX001.

How a partition value is shown

iceberg_data_files and read_avro_manifest show the partition as text. Each field is name=value, and fields are joined by commas. The text is always valid in the database encoding, and each value can be read back exactly:

Partition value	Shown as	Example
integer, long, and the date, time, timestamp, bucket, truncate and temporal transform results	the stored integer (days, hours or microseconds for the temporal ones)	id=7, ts_day=19783
boolean	true or false	flag=true
decimal	the exact value, with the column's scale	price=-0.50
float, double	the shortest text that reads back as the same value	x=0.1
binary, fixed, uuid	\x and lowercase hex, as bytea prints it	b=\xfffe
string	the value, with %, ,, =, control characters and DEL written as % and two hex digits	s=a%2Cb%3Dc
NULL	the field name alone, with no =	s

An empty string is s=, so it differs from NULL. In a database whose encoding is not UTF8 or SQL_ASCII, a non-ASCII string is also percent-encoded, byte by byte of its UTF-8 form (city=caf%C3%A9). So is a string that is not valid UTF-8. Field names follow the string rule.

SELECT file_path, record_count, partition
  FROM pgcolumnar.iceberg_data_files('/data/warehouse/db/events/metadata/v3.metadata.json');

pgcolumnar.iceberg_scan(metadata_path text) returns setof record

Reads an Apache Iceberg table at its current snapshot. You supply a column definition list. Each output column name is resolved to a field id through the table's current schema. Every live data file is then read, projected by those ids. Iceberg selects columns by field id. A data file written before a column was renamed still reads. The name in the file need not match, only the id. The caller needs the pg_read_server_files role, which superusers hold.

The output column names must be fields of the table's current schema; a name that is not is an error. Matching is case sensitive against the schema, so quote a mixed-case name in the column definition list to preserve its case. Only Parquet data files are read.

The table may live in object storage. A metadata path of s3://, http://, or https:// reads the metadata, manifests, data files, and delete files from the endpoint. The endpoint must be listed in pgcolumnar.objstore_allowed_endpoints, and credentials come from the process environment, the same way read_parquet reads a remote file. The results are identical to reading the same table from a local path.

A data file written outside Iceberg carries no field ids. Such a file is read through the table's schema.name-mapping.default property, which maps each field id to the column names an id-less file may use. Each id-less column is bound to the field id whose mapping lists its name. A file that carries field ids ignores the mapping. An id-less file with no such property is refused, because the specification defines no positional fallback, and the error names the property. The mapping is read for top-level scalar columns.

Only top-level primitive columns can be read. An output column that names a struct, list or map field is an error (SQLSTATE 0A000). This holds whatever type the column definition list gives it. The error names the column and its kind (#1788). The same holds for iceberg_rest_scan and the pgcolumnar_iceberg foreign-data wrapper.

A projected column that the file does not carry reads as null. This covers a column added to the schema after the file was written. It also covers a column an id-less file has but the mapping does not bind. The file's other columns return their real values.

A column whose type was promoted reads as its current type. Iceberg allows three promotions: int to long, float to double, and a decimal to a larger precision at the same scale. A data file written before the promotion still stores the old type, and its values are widened as they are read. Declare such a column with the current schema's type: bigint, double precision or numeric. Files from before and after the promotion read together. An equality delete on a promoted column applies to both, whichever type its own file stores. A declared type narrower than a file's stored type is refused. So a column declared with its pre-promotion type errors once a file written after the promotion is live.

Row-level deletes are applied, each kind under its own Iceberg sequence rule. A position delete drops the row ordinals it lists from the data file it names. It applies when that data file's data sequence number is at or below the delete's. A position delete can affect data written in the same commit, so the two may share a sequence number. An equality delete drops every data row that equals a delete row on all of the delete file's equality_ids columns. It applies only when the data file's sequence number is strictly below the delete's. An equality delete never affects data from its own commit. A null delete value matches a null data value, and only a null. Columns in the delete file beyond equality_ids do not take part in the match. A delete that is not strictly newer than any data file has no effect and is skipped.

Format version 3 encodes position deletes as deletion vectors. A deletion vector is a compressed bitmap of row ordinals, stored as a blob in a Puffin file and scoped to one data file. It applies under the same sequence rule as a position delete file. When a deletion vector applies to a data file, position delete files for that file are ignored. The Iceberg specification requires the writer to fold their deletes into the vector. A snapshot may carry at most one deletion vector per data file; a second one is refused. The blob's checksum, its offsets against the Puffin footer, and its recorded cardinality are all verified, and a mismatch is refused. Deletion vectors in a table below format version 3 are refused, as is a compressed Puffin footer.

An equality delete written under a partitioned spec is applied within its partition. Its stored partition values are compared against each data file's, and it removes rows only from data files in the same partition. A delete for a partition that holds no data removes nothing. Some equality-delete forms are refused rather than ignored. A table using them errors instead of returning rows it should have removed. A delete column is refused when its type has no supported mapping; the supported types are int, long, string, boolean, and date. A delete column that is no longer in the table's current schema is refused. A delete column missing from an older data file is also an error. A partition value the reader cannot compare exactly, such as a floating-point value, is refused. The recorded file paths are rebased onto the table's actual location and resolved against a path boundary. A relocated table reads, and a path pointing outside the table is refused. Applying equality deletes reads each affected data file's delete columns twice. A probe pass computes the row ordinals to drop. Each applicable equality-delete file is read once per scan into an in-memory hash table keyed on its equality_ids columns. The probe hashes each data row once per delete file and walks only that hash's bucket. There it compares the data row only with delete rows that have the same hash. The cost therefore grows with the number of data rows times the number of applicable delete files, plus the number of delete rows. It no longer grows with data rows times delete rows. All delete rows stay in memory until the scan ends.

A malformed manifest is refused, not read as far as it parses. A manifest entry that records no data-file path is an error, as is a manifest whose embedded Avro schema is not well formed. An enum value outside the Iceberg spec is refused with XX001 and the value in the message. A data file content or an entry status must be 0, 1 or 2. A manifest list content must be 0 or 1.

SELECT id, region, sum(amount)
  FROM pgcolumnar.iceberg_scan('/data/warehouse/db/events/metadata/v3.metadata.json')
    AS t(id bigint, region text, amount int)
  GROUP BY id, region;

pgcolumnar.iceberg_rest_table_location(catalog_uri text, namespace text, table_name text) returns text

Also iceberg_rest_table_location(catalog_uri text, namespace text[], table_name text). See Name a namespace.

Resolves a table named by an Iceberg REST catalog to the URI of its current metadata file. The function calls the catalog over HTTP or HTTPS. It reads the catalog configuration, loads the table, and returns the reported metadata location. That location is an ordinary metadata path, so it is read with iceberg_scan and the other functions above.

The catalog endpoint is subject to pgcolumnar.objstore_allowed_endpoints, the allow-list that governs every other remote access. The module refuses a host that resolves to a link-local range or a known instance-metadata address, whether or not it is listed. See the refused ranges. The request is carried by the pgcolumnar_objstore module, so no additional TLS library is loaded into the server process. HTTPS requires the module to be built with OpenSSL, as for object storage.

The first argument is either a catalog URI or the name of a foreign server. A value beginning with http:// or https:// is a URI. Any other value names a server. A server name can never look like a URI, so the two forms never collide.

When the first argument is a URI, the token comes from the environment. It is read from the PGCOLUMNAR_ICEBERG_REST_TOKEN variable in the server process. It is never a function argument, so it does not appear in the statement log or in pg_stat_activity. A catalog that needs no token is queried without one.

When the first argument names a server, the catalog URI comes from the server and the token comes from the current role's user mapping. The role needs USAGE on the server, as dblink requires for a named server. Without it the call is refused with SQLSTATE 42501 before any mapping is read or any request is made, even through a PUBLIC mapping. So REVOKE USAGE ON FOREIGN SERVER takes the server away from a role. The token lives in pg_user_mapping, which is not world-readable, so one role's token is not visible to another. A role with no mapping and no token is refused. A superuser, or a mapping that sets credentials_required 'false', uses the environment token instead.

A user mapping may carry OAuth2 client credentials rather than a static token. When it sets oauth_client_id and oauth_client_secret, the catalog is asked to mint a bearer by the client-credentials grant. The request goes to oauth_token_uri when set, otherwise to {catalog_uri}/v1/oauth/tokens, with an optional oauth_scope. The client secret travels in the request body, never a URL or a log line. A mapping that sets only one of the pair is refused before any request is made, even when it also sets a static token. A mapping that sets a token and the complete pair uses the token and mints no bearer.

The function requires the pg_read_server_files role, like the other Iceberg functions. The server form also requires USAGE on the server.

Name a namespace

Every REST function that takes a namespace accepts it in two forms:

• text, with levels separated by dots, such as 'analytics.daily'. The function splits the text on every dot. So this form cannot name a level that itself contains a dot.
• text[], one element per level, such as ARRAY['analytics', 'daily']. Each element is one level, exactly as given, so a level may contain a dot: ARRAY['analytics.v2'] is the single level analytics.v2 (#1865).

A bare string literal, such as 'analytics', selects the text form. The text[] array must have one dimension, at least one element, and no NULL element. Otherwise the call fails with SQLSTATE 22023 (22004 for a NULL level).

A level may be empty. The Iceberg REST catalog accepts a namespace with an empty level, so pgcolumnar sends it as given. A text[] element '' is an empty level. In the text form, a leading, trailing or doubled dot makes one, as in '.db', 'db.' or 'db..x', and so does ''. A stray dot often makes one by mistake. So when the catalog answers 404 (not found), the error carries a hint that names the empty level, such as Level 2 of 3 of the namespace is empty (#1891). Both 404 errors carry it: "no such namespace" (SQLSTATE 3F000) and "no such table" (SQLSTATE 42P01).

A namespace whose only level is empty, ARRAY[''] or '', is refused with SQLSTATE 22023 (#1922). The refusal comes before any request, including a server's OAuth token request, so it does not depend on whether the catalog can be reached. Its request path would hold an empty segment, such as /v1/namespaces//tables. The Apache Iceberg REST catalog answers that path with HTTP 400, and a proxy may collapse // to /. The REST spec has no other way to write it. The error's hint reads Level 1 of 1 of the namespace is empty.

iceberg_rest_table_location and iceberg_rest_scan refuse an empty table name, '', with SQLSTATE 22023 (#1931). The error reads iceberg: the table name is empty. The refusal comes before any request, including a server's OAuth token request. The request path would end in an empty segment, as in /v1/namespaces/db/tables/. A proxy that strips the trailing slash would send it to the table list instead.

A table named . or .. is refused with SQLSTATE 22023 too (#1948). So is a namespace whose path segment would be . or .., such as ARRAY['..']. Such a segment is a dot segment. A server or proxy that normalises the path removes it, so /v1/namespaces/db/tables/.. would reach /v1/namespaces/db instead. The refusal comes before any request, including a server's OAuth token request. On a catalog whose separator is ., the text forms '.' and '..' are refused as well. Their levels join to %2E and %2E%2E, which a normaliser may decode to dots. That check follows the request for /v1/config, which names the separator. A name that only holds dots, such as a.b, ... or .hidden, is not a dot segment and is sent as given. So is ARRAY['db','..'], whose path segment is not only dots.

A catalog cannot be asked about a level that contains its own namespace separator, because the catalog would split that level in two. The function then fails with SQLSTATE 22023 before it sends any request that names the namespace. The Apache Iceberg REST catalog uses . as its separator. On it, no form can name a level that contains a dot, and pgcolumnar refuses ARRAY['a.b']. The check applies to the text form too. For example, on a catalog with the default separator, a level that holds the byte 0x1F is refused.

The error message names the level. It shows a control byte as \xNN, such as db\x1Fx.

Every REST function first reads the catalog configuration (GET /v1/config) and follows it:

• The prefix goes after /v1/ in every later request. A prefix may contain /, such as catalogs/main; each part is sent as its own path segment.
• The namespace-separator joins the levels of a multi-level namespace in a request. The default is the unit separator, sent as %1F. The Apache Iceberg REST catalog advertises %2E.
• A key can be set in more than one place. The value in overrides wins. Next comes a top-level key of the same name, and last the value in defaults.

-- URI form: the token, if any, comes from the server environment, not the query
SELECT pgcolumnar.iceberg_rest_table_location(
         'https://catalog.example.com', 'analytics', 'events');
--> s3://warehouse/analytics/events/metadata/00042-....metadata.json

-- a namespace level that contains a dot: give the levels as text[]
SELECT pgcolumnar.iceberg_rest_table_location(
         'https://catalog.example.com', ARRAY['analytics.v2'], 'events');

-- server form: the token is per-role and stays in pg_user_mapping
CREATE SERVER cat FOREIGN DATA WRAPPER pgcolumnar_iceberg_catalog
  OPTIONS (catalog_uri 'https://catalog.example.com');
GRANT USAGE ON FOREIGN SERVER cat TO analyst;
CREATE USER MAPPING FOR analyst SERVER cat OPTIONS (token 's3cr3t');

SELECT count(*) FROM pgcolumnar.iceberg_scan(
  pgcolumnar.iceberg_rest_table_location('cat', 'analytics', 'events'))
  AS t(id bigint, region text, amount int);

The pgcolumnar_iceberg_catalog wrapper has a validator but no handler, so its servers cannot be selected from as tables. It accepts catalog_uri and an optional warehouse on a server. The warehouse selects a warehouse on a multi-warehouse catalog and is sent as the ?warehouse= parameter on the GET /v1/config request. On a user mapping it accepts token, the OAuth2 options (oauth_client_id, oauth_client_secret, oauth_scope, oauth_token_uri), and credentials_required. A secret on a server, where options are world-readable, is rejected. Setting credentials_required 'false' is restricted to a superuser.

When the catalog answers with an error status, the call fails with one of these SQLSTATEs:

Catalog reply	SQLSTATE
HTTP 401 or 403	28000
HTTP 404 of type NoSuchNamespaceException	3F000
any other HTTP 404	42P01
any other status but 200	08P01

When the reply carries the catalog's own error message, the error detail repeats it as The catalog said: <message>. The message is cut to 512 bytes.

pgcolumnar.iceberg_rest_scan(catalog_uri text, namespace text, table_name text) returns setof record

Also iceberg_rest_scan(catalog_uri text, namespace text[], table_name text). See Name a namespace.

Reads a table named by an Iceberg REST catalog at its current snapshot. It takes a column definition list exactly like iceberg_scan. The catalog resolves the table to its metadata location. That location is read through the same path, so field-id projection and every delete rule apply unchanged. The catalog and authentication rules are those of iceberg_rest_table_location above, including the allow-list, the link-local refusal, and the environment bearer token.

A catalog can vend storage credentials in its loadTable reply. The data, metadata, and delete files are then read with those credentials, not the server environment. Each file gets its own credential. For each file, the reader uses the storage-credentials entry whose prefix is the longest match for the file's location. A file that no prefix covers uses the flat config keys. If the flat config vends no keys either, the read fails with SQLSTATE 28000 and names the file. The server's own AWS_* credentials are never used for a table whose catalog vends credentials. An entry, or a flat config, counts as a credential only when it has both s3.access-key-id and s3.secret-access-key. Vended credentials do not bypass the allow-list. The endpoint is still checked. A table that vends no credentials is read with the ambient environment, as before.

SELECT region, sum(amount)
  FROM pgcolumnar.iceberg_rest_scan('https://catalog.example.com',
                                    'analytics', 'events')
    AS t(id bigint, region text, amount int)
  GROUP BY region;

pgcolumnar.iceberg_rest_namespaces(catalog_uri text) returns setof text

Lists the namespaces of a catalog, one per row, at every level. A multi-level namespace is returned dot-joined, for example analytics.daily. Same catalog and authentication rules as above.

The catalog lists only the direct children of a namespace. So the function asks for the top level, then asks for the children of each namespace it finds (?parent=). That is one request for the top level, plus one for each namespace. A namespace can be up to 64 levels deep. If the catalog has a namespace 65 levels deep, the function raises an error.

The function needs every one of these requests to succeed. If the catalog answers an error to any of them, the function raises that error and returns no rows. This includes a catalog that does not accept ?parent=. On such a catalog, list the tables of a namespace you know with iceberg_rest_tables.

Some catalogs also list nested namespaces at the top level. The function returns each namespace once.

The dot-joined form is ambiguous: the single level a.b and the two levels a, b both come out as a.b. To tell them apart, use iceberg_rest_namespace_levels. An empty level keeps its dot: the levels "", x come out as .x (#1922).

pgcolumnar.iceberg_rest_namespace_levels(catalog_uri text) returns setof text[]

Lists the same namespaces as iceberg_rest_namespaces, each as a text[] of its levels (#1865). The single level a.b comes out as {a.b}, and the two levels a, b as {a,b}. You can pass a row back unchanged as the namespace argument of the other REST functions, with two exceptions, described next. A row with an empty level, such as {db,""}, passes back too (#1891). The function also lists the children of such a namespace, such as {db,"",x}, and their children, such as {db,"",x,y} (#1939).

A level may contain the catalog's separator. No request can name such a namespace, so the function returns it but does not ask for its children. Passing that row back fails with SQLSTATE 22023.

A catalog may also hold the namespace whose only level is empty. The function returns it as {""}. Passing that row back fails with SQLSTATE 22023 too (#1922). The function still asks for its children, with an empty ?parent=. The Apache Iceberg REST catalog reads an empty ?parent= as {""}, so a child such as {"",x} is listed, and passes back. A catalog that reads an empty ?parent= as the top level gives no children of {""}.

The rows {"."} and {".."} are listed too, and passing either back fails with SQLSTATE 22023 (#1948). The function asks for their children with ?parent=. or ?parent=... A query value is not a path segment, so a normaliser leaves it alone.

SELECT ns, t
  FROM pgcolumnar.iceberg_rest_namespace_levels('https://catalog.example.com') AS ns,
       LATERAL pgcolumnar.iceberg_rest_tables('https://catalog.example.com', ns) AS t;

Caution: One level that holds the catalog's namespace separator makes this whole query fail with SQLSTATE 22023. The default separator is the byte 0x1F. Some catalogs use ., such as the Apache Iceberg REST catalog. Skip those rows first. On a catalog whose separator is .:

SELECT ns, t
  FROM pgcolumnar.iceberg_rest_namespace_levels('https://catalog.example.com') AS ns,
       LATERAL pgcolumnar.iceberg_rest_tables('https://catalog.example.com', ns) AS t
 WHERE NOT EXISTS (SELECT FROM unnest(ns) AS level WHERE level LIKE '%.%');

On a catalog with the default separator, replace level LIKE '%.%' with strpos(level, chr(31)) > 0.

The row {""} also makes the whole query fail with SQLSTATE 22023 (#1922). To skip it, add AND ns <> ARRAY[''] to the WHERE clause. The rows {"."} and {".."} do too (#1948). Add AND ns NOT IN (ARRAY['.'], ARRAY['..']) to skip them.

pgcolumnar.iceberg_rest_tables(catalog_uri text, namespace text) returns setof text

Also iceberg_rest_tables(catalog_uri text, namespace text[]). See Name a namespace.

Lists the table names in a namespace, one per row.

Both list functions follow the catalog's next-page-token, so a catalog that splits a list into pages is read to the end.

SELECT * FROM pgcolumnar.iceberg_rest_namespaces('https://catalog.example.com');
SELECT * FROM pgcolumnar.iceberg_rest_tables('https://catalog.example.com', 'analytics');

The pgcolumnar_parquet foreign-data wrapper

Exposes a Parquet file, directory, or glob as a foreign table. The scan streams one row group at a time. It holds a single row group rather than the whole file. A LIMIT the plan satisfies early leaves the rest of the file undecoded. It pushes work down: row groups whose min/max statistics exclude the query's predicate are skipped, and only referenced columns are decoded. Skipping requires a column op constant clause over an integer or floating-point column with a constant of the same type; limitations.md lists the conditions. A scan that skips nothing still returns correct rows.

The wrapper skips no row group on a column whose type modifier can change a value, because the statistics hold the stored values. Examples are numeric(18,1) over a file whose DECIMAL scale is 2, which reads a stored 99.99 as 100.0, and timestamp(3), which rounds microseconds. A timestamp(6) column, or a numeric(p,s) column with the file's own scale and at least its precision, still skips.

The path option can name a local file, directory, or glob, or an object-storage URL. A remote URL is an exact object, a prefix ending in a slash, or a pattern, expanded the same way as a local path. For a remote server the endpoint and credentials come from the server and user-mapping options described in Object storage.

Table options: path, and partition_columns for a Hive-style layout. The latter names the columns whose values come from col=value directory components rather than from the files. You declare these columns. pgColumnar does not infer them, because an incorrect value would change the rows that a query returns, with no message. A predicate on a partition column removes complete files before the reader opens them. The plan shows this as Files Pruned.

CREATE SERVER pq FOREIGN DATA WRAPPER pgcolumnar_parquet;
CREATE FOREIGN TABLE events (id int, ts timestamp, amount numeric(12,2))
  SERVER pq OPTIONS (path '/data/events/');

-- events/dt=2026-01-01/region=eu/part-0.parquet
CREATE FOREIGN TABLE events_p (id int, amount numeric(12,2), dt date, region text)
  SERVER pq OPTIONS (path '/data/events', partition_columns 'dt,region');

SELECT sum(amount) FROM events WHERE ts >= '2026-01-01';

-- EXPLAIN ANALYZE reports Row Groups, Row Groups Skipped, Row Groups Decoded,
-- Columns Read, Columns Total, and Files.
EXPLAIN (ANALYZE, COSTS OFF) SELECT id FROM events WHERE ts >= '2026-01-01';

The pgcolumnar_iceberg foreign-data wrapper

Exposes an Apache Iceberg table as a foreign table. Unlike iceberg_scan, which is a set-returning function that receives no predicate, the foreign table gets the query's quals and prunes data files two ways. A predicate on an identity-partitioned column removes whole files by their partition value. An equality predicate on a bucket[N]-partitioned column removes files whose stored bucket differs from the constant's. A predicate on an integer or boolean column removes whole files whose stored minimum and maximum exclude it. An unpartitioned column can prune this way. All read from the manifest, so a file is skipped without a read. Pruning is only an optimization. A file that is not pruned is read normally, so a predicate the wrapper cannot decide never changes the rows returned. Field-id projection and every delete rule are those of iceberg_scan. A foreign table may declare a nested Iceberg column (a struct, list or map) as a scalar. Such a table refuses every query, even one that does not read that column (#1788).

The one table option is metadata_path, the table's current metadata.json (a local path or an object-storage URL). The wrapper takes no server or user-mapping options. It reads a remote table with the server process environment's credentials, as iceberg_scan does (see Endpoints and credentials). The wrapper requires the pg_read_server_files role. EXPLAIN (ANALYZE) reports Files Pruned.

Partition pruning covers the identity, bucket[N], truncate[W], and temporal transforms. Identity partitioning prunes on any predicate. bucket[N] prunes on an equality predicate on the source column under a deterministic collation. Iceberg buckets a string by its UTF-8 bytes, and under a nondeterministic collation two equal strings can differ in bytes. So such an equality reads every file. truncate[W] prunes on a predicate on an integer source column. day() on a date source column prunes on a predicate on that column.

The year(), month(), day(), and hour() transforms also prune on a range or equality predicate. They apply to a timestamp or timestamp with time zone source column, and year() and month() apply to a date column as well. day() on a date keeps the exact path above. Each of these buckets spans a range of source values. So a file whose bucket equals the predicate constant's bucket is read, not skipped, and the row filter runs on it. A timestamp with time zone value is compared as its UTC instant, matching the catalog. Metrics pruning covers integer and boolean columns; other column types are read in full.

A column whose type modifier can change a value is never used to prune. Examples are varchar(2), which reads a stored 'eu ' as 'eu', and timestamp(0), which reads 00:59:59.7 as 01:00:00. The manifest holds the stored value, so it does not describe the value the scan returns.

CREATE SERVER ice FOREIGN DATA WRAPPER pgcolumnar_iceberg;
CREATE FOREIGN TABLE events (id bigint, region text, amount int)
  SERVER ice OPTIONS (metadata_path '/data/warehouse/db/events/metadata/v3.metadata.json');

-- reads only the region=eu data file; EXPLAIN ANALYZE shows "Files Pruned: 1"
SELECT sum(amount) FROM events WHERE region = 'eu';

Object storage

The Parquet read and export functions, export_arrow and the Iceberg reader accept an object-storage URL wherever they accept a local path. So do the pgcolumnar_parquet and pgcolumnar_iceberg foreign-data wrappers. The third wrapper, pgcolumnar_iceberg_catalog, takes no path. import_arrow, file_split_offsets, parallel_copy, read_avro_manifest and read_manifest_list read local files only. Each of them refuses a URL in any of the schemes below with SQLSTATE 0A000 before it reads anything, for example columnar: import_arrow does not read "s3://bucket/m.arrow". These URL schemes are recognized:

Scheme	Meaning
s3://bucket/key	An S3 or S3-compatible object. The request is signed with AWS Signature Version 4.
s3a://bucket/key, s3n://bucket/key	The Hadoop names for S3, matched in any case. The readers read them as s3://bucket/key: the Parquet functions, the pgcolumnar_parquet wrapper and the Iceberg reader. The exports refuse them with SQLSTATE 0A000, for example columnar: export_parquet does not write "s3a://bucket/t.parquet", with a hint that names the s3:// URL. The local-only functions above refuse them too.
gs://bucket/key	A Google Cloud Storage object, read and written through the interoperable XML API. It signs with the same Signature Version 4 as s3://, so a GCS HMAC key is given as AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY. The endpoint defaults to https://storage.googleapis.com and the region to auto; set AWS_ENDPOINT_URL to override.
http://host[:port]/path	A plain-HTTP object. The request carries no credential, but the data crosses the network in clear, so use it on a trusted network only.
https://host[:port]/path	An HTTPS object. Available when the object-store module was built with OpenSSL. The server certificate is verified.
az://container/key	Reserved for Azure Blob Storage, which no module reads yet. pgcolumnar treats the URL as remote, not as a local path, and refuses it with SQLSTATE 0A000: reading "az://..." is not supported, or, without the object-store module, reading "az://..." requires the object-store module.

A reader rewrites an s3a:// or s3n:// URL to s3:// before it lists, signs or opens anything. So the request, the endpoint and the credentials are those of the s3:// URL. Error messages name the s3:// URL too, because that is the URL that was requested. Only the start of the URL counts: xs3a:// or s3ax:// is a local path.

Requests to an http:// or https:// URL are never signed and carry no credential, for reads and for exports alike. An export sends one PUT, or, for a large object, the multipart POST and PUT requests and the DELETE that aborts a failed upload. None of them has an Authorization header. Use these schemes for a server that accepts unsigned requests, and s3:// or gs:// for a store that needs credentials. If such a server refuses a request with HTTP 403, the error hint says the request was unsigned.

Credentials go over plain HTTP only when an s3:// or gs:// URL has an http:// AWS_ENDPOINT_URL. The request is then signed, and the signature, the access key ID and any AWS_SESSION_TOKEN cross the network in clear. The secret access key is never sent.

S3 requests use path-style addressing (endpoint/bucket/key) by default. Set pgcolumnar.objstore_s3_addressing to virtual for virtual-host addressing (bucket.endpoint/key), which is what AWS now prefers; the endpoint allow-list still authorizes the endpoint, not the per-bucket hostname.

Object-storage support lives in a separate module, pgcolumnar_objstore, which loads on the first use of a remote URL and never before. A build or an install without it reads and writes local files as before, and a remote URL reports that the module is required.

A remote path is read as an exact key, a prefix, or a pattern. An s3:// URL that names an object reads that one object, with no listing. A key that holds *, ? or [ is the exception described below. The read starts with one HEAD, then fetches the footer and the column data with ranged GETs. When the HEAD returns a strong ETag of at most 79 characters, every GET sends that ETag in an If-Match header. The 79 includes the ETag's two quote marks. If another client then overwrites the object during the read, the server answers 412. The query then fails with changed while it was being read, instead of returning rows from two versions. Run the statement again to read the new version. When the HEAD also returns x-amz-version-id, as a versioned S3 bucket does, every GET asks for that version with versionId=. An overwrite during the read then leaves the read on the version it started on, and the query succeeds. A GET that names a version needs the s3:GetObjectVersion permission. If the store answers such a GET with 403, the reader asks again without the version, for the rest of that read, and keeps If-Match. The reader also compares the total length in each 206 Content-Range with the length from the HEAD. When they differ, the query fails with changed while it was being read, whatever the server sends as ETag. The query fails the same way when a GET for bytes inside the HEAD's length gets 416: the object has shrunk. Without a strong ETag of at most 79 characters and without a version id, the read is not pinned. A server that sends no ETag, a weak one, or a longer one pins the read only by version id (see limitations). Set pgcolumnar.objstore_pin_reads to off for a server whose ETag changes from one request to the next: the reader then sends no If-Match and no versionId. Two reads of one object in a statement, such as a self-join, read one version (#1894). The second read asks for the version the first one opened. When the store names no version, the second read fails with changed while it was being read if the ETag or the length changed in between. With no strong ETag, only a change of length is caught. The pin lasts one top-level statement (see limitations). An s3://bucket/prefix/ URL that ends in a slash lists every object under the prefix, at any depth, like a local directory. An s3://bucket/prefix/*.parquet URL lists the literal prefix and matches the pattern segment by segment, like a local glob. Listing is a paged ListObjectsV2 call. Below a prefix, _SUCCESS, _temporary, and other names that begin with _ or . are skipped, as they are below a local directory. The prefix you name may itself begin with _ or ., such as s3://bucket/_landing/. A pattern matches names as a local glob does. It matches a name that begins with _. It matches a name that begins with . only when the pattern spells the dot, such as .*.parquet. The scheme is compared without case, so S3:// reads as s3:// does. Hive partition_columns work over a remote prefix. The object-store module issues the listing, as it issues every remote read.

Only the key of an s3:// URL, the part after the bucket, can be a pattern. The scheme and the bucket never are. An http:// or https:// URL is always one object, so a ? in it starts a query string, which is sent to the server. A key may itself contain *, ? or [, because S3 allows them. When an s3:// URL holds one of these characters, the reader lists the prefix and first looks for an object with exactly that key. If one exists, the reader reads that object alone, whatever its suffix. Only when none exists is the key matched as a pattern. A backslash before a character makes the pattern match that character literally, so s3://bucket/a/q\?.parquet matches only a/q?.parquet. A read from a host written as an IPv6 literal in brackets, such as http://[::1]:9000/, is refused with SQLSTATE 0A000. When an http(s):// URL has a query string, the reader and the writer add their own parameters to it with &. Examples are versionId= and uploadId=.

On write, parallel_export_parquet treats an s3:// URL as a prefix, and refuses every other remote URL before any worker starts. It writes one part-NNNN.parquet object under the prefix per worker, and an empty _SUCCESS marker beside them on completion. Trailing slashes on the prefix make no difference: s3://bucket/out/, s3://bucket/out// and s3://bucket/out all write out/part-0000.parquet and out/_SUCCESS. The scheme may be written in any case, so S3://bucket/out writes the same keys. Before it writes the marker, the export deletes any part-NNNN.parquet directly under out/ that a larger earlier run left behind. It looks only directly under out/, so objects under outer/, out2/ or a deeper prefix such as out/sub/ are never touched. The prefix must name something under the bucket: s3://bucket/ and s3://bucket are refused before any worker starts, with SQLSTATE 22023. The workers use the calling session's values of pgcolumnar.objstore_allowed_endpoints, pgcolumnar.objstore_s3_addressing and pgcolumnar.objstore_part_size, including any set with SET. The endpoint is checked against the session's allow-list before any worker starts, so a refused endpoint fails before anything is uploaded. Every other write function writes a single object at the exact key.

Remote paths carry the same privilege as local ones. A read needs pg_read_server_files and a write needs pg_write_server_files, over and above any table privilege.

Endpoints and credentials

The endpoint and the credentials come from the server process environment, from the catalog, or from both.

The function API (read_parquet, export_parquet, export_arrow, import_parquet, parquet_schema) has no server object. Its endpoint and credentials come from the environment of the server process:

Variable	Meaning
AWS_ENDPOINT_URL	The object-storage endpoint, http://... or https://.... Required for an s3:// URL; optional for gs://, which defaults to https://storage.googleapis.com.
AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY	The access key pair.
AWS_SESSION_TOKEN	A session token, when the credentials are temporary. Optional.
AWS_REGION or AWS_DEFAULT_REGION	The signing region.

Two calls take a server instead: export_parquet(rel, path, server), and tier_partition, which writes through it. They resolve the endpoint and the credentials as the foreign-data wrapper does, as described next.

For the pgcolumnar_parquet foreign-data wrapper the endpoint and credentials come from the catalog, which keeps them out of a world-readable place. The non-secret settings go on the server and the secret ones go on a user mapping:

CREATE SERVER s3 FOREIGN DATA WRAPPER pgcolumnar_parquet
  OPTIONS (endpoint 'https://s3.example.com', region 'us-east-1');

CREATE USER MAPPING FOR analyst SERVER s3
  OPTIONS (access_key_id '...', secret_access_key '...');

CREATE FOREIGN TABLE events (id int, ts timestamp, amount numeric(12,2))
  SERVER s3 OPTIONS (path 's3://reports/events.parquet');

The wrapper accepts endpoint and region only on the server, and access_key_id, secret_access_key, session_token, and credentials_required only on a user mapping. Any other placement is an error at CREATE or ALTER time. pg_user_mapping is not world-readable, so a secret placed there is not exposed the way a server option is.

A server with no endpoint or region option takes that value from AWS_ENDPOINT_URL or AWS_REGION (or AWS_DEFAULT_REGION) in the environment. This applies to every caller, because an endpoint and a region are not secrets.

A read resolves the scanning user's mapping first, then a PUBLIC mapping. When a mapping supplies no credentials, the server process environment is used only in two cases. The caller is a superuser. Or a superuser has marked the mapping credentials_required 'false'. Ambient credentials are a privilege, not a default. An ordinary role with no mapping is refused rather than given the server process identity.

The pgcolumnar_iceberg wrapper works differently. It takes no server or user-mapping options, so it has no per-role credentials and no credentials_required. It reads with the server process environment, as iceberg_scan does, for every role that may read the foreign table. Its gate is the pg_read_server_files role.

The endpoint allow-list

pgcolumnar.objstore_allowed_endpoints lists the endpoints the module may connect to. It is empty by default, which refuses every remote endpoint. A role that can read server files therefore cannot use the extension to reach an arbitrary host. A superuser sets it to the endpoints the deployment uses:

ALTER SYSTEM SET pgcolumnar.objstore_allowed_endpoints = 's3.example.com';
SELECT pg_reload_conf();

Each entry is a host or a host:port. A request whose endpoint matches no entry is refused before any connection is made. The link-local ranges and the known cloud instance-metadata addresses are refused unconditionally, whether or not they appear in the list. These include 169.254.169.254 and Alibaba Cloud's 100.100.100.200. For every refused range, see Run object storage safely. The setting is superuser-only, so a role cannot widen its own reach.

Exporting to object storage

export_parquet and export_arrow write to an s3:// URL as well as a local path. A small object goes in one request. A large one is written as a multipart upload. It becomes visible at its final name only when the upload completes, so a reader never sees a partial object. An export that fails before its last request removes what it wrote, including an incomplete multipart upload. The last request is different, as the next paragraphs explain.

A request whose body is over 1 MiB, a single request or a part, sends its headers first with Expect: 100-continue. It waits up to one second for the store to answer them before it sends the body. A store can refuse the request from its headers alone, with a 403 for example. The export then reports that status and its hint, over https:// as well as http://. A store that ignores Expect costs that second once per object written. After a wait runs out, the later parts of that object go without Expect. A store that answers 417 gets the request again without Expect. A 2xx answer to the headers alone still gets the body, and stands as the answer once the body is sent.

This cleanup holds when you cancel or terminate the export while it waits for the store to start the upload. The export then has no id for that upload. It lists the open uploads for its own key and aborts each one, within 10 seconds. Uploads at other keys stay open. An upload that another writer started at the same key is aborted too. This works for s3:// and gs:// URLs.

A multipart upload holds at most 10000 parts of pgcolumnar.objstore_part_size bytes each, 8 MiB by default. An export that needs more parts fails with SQLSTATE 54000 before it sends part 10001, and aborts the upload. Raise the part size for an object over about 78 GiB. AWS S3, Google Cloud Storage and Cloudflare R2 refuse parts under 5 MiB, except the last. To those stores, an export with a smaller part size fails with SQLSTATE 22023 when it starts a multipart upload, before it sends a part. A small object that fits in one part still goes in one request. Other stores are not checked.

The export reads each reply from the store by the HTTP/1.1 framing rules. A reply with neither a Content-Length nor chunked framing ends when the store closes the connection; a 204 reply has no body. Some stores send such a reply to a write and keep the connection open. The export then waits 60 seconds and fails with "timeout reading". A chunked or close-delimited write reply over 1 MiB fails the export with "response exceeded". This holds even when the store has already written the object.

The last request commits the object. It is the single request of a small object, or the request that completes a multipart upload. Once the store has that whole request, a cancel or terminate waits for its answer. The wait ends when the answer arrives, or when the store sends nothing for 60 seconds. A store that keeps sending slowly can make it wait longer. If the store accepted the request, the export issues a warning that it wrote the object. The statement can still end with the cancel error. The new object is then at the key and has replaced any earlier object there. A store cannot take a committed object back.

If the connection closes during that wait, the export does not send the request again. It fails with an error that says the store may have written the object. Check the key to see whether it holds the new object.

A backend killed with SIGKILL, or a lost connection to the store, can still leave an incomplete multipart upload. To reclaim one, set a bucket lifecycle rule that expires incomplete multipart uploads after a few days.

Export to object storage is not transactional. An export whose transaction later rolls back has already written the object. This is true of a local export as well, and is more visible when the artifact is remote and shared.

Visibility map inspection

These report the state of the columnar visibility-map fork that serves index-only scans. They are for diagnostics.

pgcolumnar.vm_is_visible(rel regclass, blk int)

Tells you if the block (chunk group) has the all-visible mark.

pgcolumnar.vm_selftest(rel regclass, blk int)

Runs a set and clear self-test against the visibility map for one block.