Sequences, UUIDs, modifying CTEs, indexes and materialized views

These SQL additions address #101, #102, #103, #104, and #105. They preserve the driver's autocommit model. A complete dbt materialization run has not been validated by these SQL-level tests. Upgrade every driver client that accesses these objects: older versions do not enforce the new identity or materialized-view write protections.

Generated identifiers

CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    event_id UUID DEFAULT gen_random_uuid(),
    label TEXT
);
INSERT INTO events(label) VALUES ('first') RETURNING id, event_id;

SMALLSERIAL, SERIAL, BIGSERIAL (and SERIAL2/4/8) in CREATE TABLE create integer, non-null columns with owned sequences. Identity columns support GENERATED ALWAYS and GENERATED BY DEFAULT, plus INSERT ... OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE. ALWAYS rejects explicit values; USER ignores supplied identity values and allocates new ones. Arrow append ingestion generates values for omitted columns. Explicit values for an ALWAYS column are rejected during ingestion. Identity does not enforce uniqueness; the existing primary-key enforcement limitations still apply.

CREATE SEQUENCE, ALTER SEQUENCE and DROP SEQUENCE [IF EXISTS] expose AS SMALLINT/INTEGER/BIGINT, INCREMENT [BY], [NO] MINVALUE, [NO] MAXVALUE, START [WITH], CACHE, [NO] CYCLE and OWNED BY table.column / NONE. ALTER SEQUENCE ... RESTART [WITH n] resets allocation. Sequence values use exact signed 64-bit arithmetic and atomic metadata-slot updates. Cached blocks belong to a connection; abandoning them can leave gaps. Failed inserts can consume values. currval() and lastval() are connection-local; nextval() and setval() change the stored allocator. setval(name,n,false) makes the next allocation return n without setting that connection's currval.

ALTER TABLE ... ALTER COLUMN ... ADD GENERATED ... AS IDENTITY, SET GENERATED ALWAYS / BY DEFAULT, SET sequence_option, RESTART, and DROP IDENTITY [IF EXISTS] manage existing integer, non-null columns. Issue identity changes separately from other ALTER actions. Adding a new SERIAL or IDENTITY column through ADD COLUMN is explicitly unsupported; create the integer column first and add its identity after populating it and setting NOT NULL. Sequence rename is explicitly unsupported.

TRUNCATE ... RESTART IDENTITY publishes row replacement and owned sequence restart definitions in the same atomic catalog change. Volatile sequence allocations remain nontransactional and can leave gaps after failed statements. Dropping a table removes its owned sequences. pg_get_serial_sequence, information_schema.sequences, identity fields of information_schema.columns, pg_sequence and pg_sequences expose allocation metadata.

UUID representation

UUID accepts hexadecimal text with uppercase/lowercase digits, optional braces, and optional hyphens after groups of four digits. It stores and returns the canonical lowercase 8-4-4-4-12 spelling. Malformed input raises 22P02. Casts to/from text and UUID '...' literals are supported. gen_random_uuid() generates independent random version-4 values for each call.

Driver-managed UUID columns use exact TAG fields. Adopted UUID text uses driver-side filtering because application-owned HASHes may use noncanonical spellings. Equality filters, joins and grouping use canonical text; canonical lexical order matches UUID byte order. Arrow output is utf8 with ARROW:FLIGHT:SQL:TYPE_NAME=UUID field metadata, rather than an Arrow extension array. This is distinct from the opt-in input extension storage normalization policy.

WITH and data modification

WITH staged AS (SELECT 'second' AS label)
INSERT INTO events(label) SELECT label FROM staged;

WITH moved AS (DELETE FROM events WHERE id = 1 RETURNING label)
INSERT INTO events(label) SELECT label FROM moved;

Top-level WITH can precede INSERT. INSERT, UPDATE, DELETE and the driver's MERGE RETURNING form can be CTE bodies. Modifying bodies execute exactly once, including unused bodies and bodies under an outer LIMIT 0. Referencing one more than once reuses its RETURNING relation. A body without RETURNING may be unused, but cannot be read as a relation. Recursive modifying CTEs and nested modifying WITH are rejected.

Before writes, the executor captures the physical tables referenced by the statement and its ordinary views into memory. Each body and the outer statement read that image, while CTE references read RETURNING values. This can require memory proportional to all referenced source rows. Capture reads one pinned committed generation. All modifying bodies and the outer write publish together; a later error leaves the original managed rows unchanged. Avoid modifying the same physical row in more than one body.

Named indexes

CREATE INDEX IF NOT EXISTS events_label_idx ON events USING btree(label);
DROP INDEX IF EXISTS events_label_idx;

CREATE INDEX adds missing indexable fields through FT.ALTER and records the SQL name and columns. Existing fields need no new physical index. btree and hash are accepted as compatibility hints; the implementation remains Redis Search. CONCURRENTLY is accepted, and INCLUDE / WITH options are ignored. Expression, partial and UNIQUE indexes are explicitly unsupported.

Exact quoted column names are supported on ordinary tables and materialized views, including mixed case, spaces, embedded quotes and punctuation:

CREATE INDEX customer_id_idx ON customers ("Customer ID");

For physical HASH fields unsafe in Search expressions, named-index creation records a private, collision-checked Search alias and uses field AS alias in the native index schema. SQL metadata retains the exact logical column name; renames, materialized-view refreshes and generation rebuilds retain the binding. SQL expression pushdown still requires safe physical field names and otherwise uses exact HASH values. This DDL support does not add alias-based predicate, sort or aggregate optimizations. The existing type restrictions still apply: numeric-like and string columns are indexable; binary, array and other unsupported column types are rejected with SQLSTATE 0A000. Default indexing and NOINDEX policies are unchanged.

DROP INDEX removes the SQL name and retains the physical Search fields. Other SQL indexes and default indexing can share them. pg_indexes and pg_index list the named indexes. Table/column renames retain field bindings; dropping a column removes indexes that refer to it. Application-owned indexes cannot be altered through this DDL.

Materialized views

CREATE MATERIALIZED VIEW event_totals AS SELECT count(*) AS n FROM events;
REFRESH MATERIALIZED VIEW event_totals;
REFRESH MATERIALIZED VIEW CONCURRENTLY event_totals;
REFRESH MATERIALIZED VIEW event_totals WITH NO DATA;
DROP MATERIALIZED VIEW event_totals;

A materialized view stores its query and a physical table generation. Refresh builds a private generation before one atomic catalog publication. A failed build preserves the previously published rows. Old reader pins retain their original generation; normal reference-aware reclamation retires abandoned outputs and unreferenced generations. CONCURRENTLY does not add unique-index validation. Ordinary reads begun after publication use the new generation.

CREATE / REFRESH accept WITH [NO] DATA. An unpopulated view raises 55000 when read. CONCURRENTLY requires populated input and WITH DATA. Writes and ingestion are refused with 42809, including modifications that match no rows. ALTER MATERIALIZED VIEW ... RENAME TO, DROP, pg_matviews, and ADBC GetObjects support these objects. A failed initial population can leave an unpopulated view; fix its source query/data and refresh it. TEMP and OR REPLACE materialized views, parameters, and dependency-aware cascade are unsupported.