M3: SQL and the point path

The fourth milestone of rupg, specified in spec/23-milestones.md section 23.6. M3 runs the SQL that applications run on rows: the full analyzer, INSERT, UPDATE, DELETE, MERGE and RETURNING, MVCC at READ COMMITTED and REPEATABLE READ, prepared statements and the plan cache. It also delivers the point path, the first optimizer, and version 1 of the vectorized executor on the hot store. Gate G4 closes here.

Why

The point path is at M3, not at M8. Gate G4 measures the CPU per operation. A slow protocol layer is hard to fix late, because its cost is spread over every message. The point path sets the cost floor while the code is still small.

M3 also completes the first SQL path that M2 shipped for the catalog queries of G1. The regression suite starts to count here, as a tracked pass count with a ratchet.

The work

Taken from spec/23 §23.6. The reference in brackets names the spec section that owns the detail. Change the list in the spec and copy it again. Do not edit it here.

Exit criterion

G4 (spec/02 §2.10): YCSB workloads A, B, C and F at 3x less server CPU per operation than PostgreSQL, without pipelining. spec/20 §20.13 runs it on the oltp machine with 16 clients against PostgreSQL 19, and the ratio must hold on every workload.
The report publishes the pass count of the 239 regression tests, at or above its ratchet.
sql_parse runs 48 CPU hours with no open find.
1 million more crash files with DML and DDL run with no lost commit.
The close report answers each line of the checklist in spec/23 §23.18 that applies from M3.

Open questions

These questions in spec/24 close at M3.

Q12 (spec/24 §24.3): max_parallel_workers_per_gather, an effective default that differs from the reported value or a gate run that sets it. Q12: What does max_parallel_workers_per_gather report? #25 Closed by Close Q12, Q13, Q21 and Q25 with the M3 evidence #1068: rupg reports 2, and a gate run sets the value and prints it.
Q13 (spec/24 §24.4): float aggregates in a different order, an exclusion for the last bits or PostgreSQL's serial order. Q13: Do float aggregates in a different order match the oracle? #26 Closed by Close Q12, Q13, Q21 and Q25 with the M3 evidence #1068: document 05 item 17 accepts the last bits only for a parallel or segment sum.
Q18 (spec/24 §24.4): the collation order of ICU4X against ICU4C, decided by a corpus test. Q18: Does ICU4X order strings as ICU4C does? #31
Q20 (spec/24 §24.4): math functions on other platforms, and correctly rounded functions in Rust. Q20: Do math functions give the same answers on other platforms? #33 Closed by Call cbrt and tgamma of the C library #1067: rupg calls the C math library of the platform, as PostgreSQL does.
Q21 (spec/24 §24.4): hash values that users can see, with two sets of hashes in rupg-kernels. Q21: Do hash values that users can see match PostgreSQL? #34 Closed by Close Q12, Q13, Q21 and Q25 with the M3 evidence #1068: the hashes that users see match PostgreSQL 19.
Q25 (spec/24 §24.4): relpages, bytes divided by 8,192 or a count that keeps external cost formulas as on PostgreSQL. Q25: What does relpages report? #38 Closed by Close Q12, Q13, Q21 and Q25 with the M3 evidence #1068: the page count of PostgreSQL for the same rows.
Q72 (spec/24 §24.9): facts not verified in two cited sources. Q72: Which facts are not verified? #85 Closed by Close Q72 with the release of self-join elimination #1060: PostgreSQL 18 added self-join elimination.

Harness work

Compatibility: M3: L3 tracked and the generated corpus rupg-compat#3
Benchmarks: M3: YCSB and the point path rupg-bench#3

Depends on

#3.

Spec

#391 is merged. It is the first step of the item for INSERT, UPDATE, DELETE, MERGE and RETURNING, and of the item for MVCC.

Tables hold rows now. INSERT with VALUES, with a query and with DEFAULT VALUES stores them, and SELECT reads them.
The defaults of the columns and of the domains, the not-null and check constraints, and the errors of transformInsertStmt match PostgreSQL 19 in connection/insert.test.
The rows are in memory with transaction IDs and snapshots. READ COMMITTED takes a snapshot for each statement, and REPEATABLE READ keeps one for the transaction. A rollback removes the rows.

The two items stay open. The next steps are the unique checks, UPDATE, DELETE, RETURNING, and the move of the rows to rupg-txn and the hot store.

#392 is merged. It adds UPDATE and DELETE on the heap tables. Each row version now has xmin, cmin, xmax and cmax. A change of a row that a running transaction changed waits with the wait event Lock/transactionid, and it has the deadlock check and lock_timeout. After a commit of the other transaction, read committed runs the statement again and repeatable read gives 40001. The output of a single-session script and of a two-session script is the same as the output of PostgreSQL 19, with the exception of the transaction IDs.

Known differences: the second run uses a new snapshot for the full statement and not EvalPlanQual. A table with a unique index, a foreign key, a trigger or a rule, RETURNING and WITH still give 0A000.

Next: the unique and primary key checks on the heap, then the foreign key checks and RETURNING.

#395 is merged. INSERT and UPDATE now check the unique indexes and the primary keys. A duplicate key gives 23505 with the detail Key (...)=(...) already exists., and a key that a transaction in progress holds makes the write wait, with the context while inserting index tuple. The heap keeps a hash map for each unique key, so a run of single-row inserts does not scan the full table. The output of a script of unique keys and of a two-session script is the same as the output of PostgreSQL 19, with the exception of the transaction IDs.

Known differences: a unique index on an expression, a partial unique index and a deferrable unique constraint still give 0A000.

Release 0.1.42 is out with INSERT, UPDATE and DELETE (#391 and #392).

Next: the foreign key checks, then RETURNING.

#396 adds RETURNING to INSERT, UPDATE and DELETE. The list can use *, expressions, subqueries, and the columns of FROM and USING. It also has the names old and new of PostgreSQL 18 and the option RETURNING WITH (OLD AS o, NEW AS n). The rows go to the client in the simple protocol, the extended protocol and EXECUTE, and Describe gives RowDescription for a statement with RETURNING. The test script was recorded from PostgreSQL 19 and gives the same output.

The checklist item for DML stays open until MERGE and ON CONFLICT are done.

Next: the foreign key checks, then ON CONFLICT.

#397 is merged. INSERT, UPDATE and DELETE now check the foreign keys after the statement, as the internal triggers of ri_triggers.c do. The checks support MATCH SIMPLE, MATCH FULL, NO ACTION and RESTRICT, a table that references itself, and keys of two types. The errors and their details are the same as in PostgreSQL. The session reads the other table one time for each foreign key and keeps its keys in a hash map.

The actions CASCADE, SET NULL and SET DEFAULT and the constraints that are INITIALLY DEFERRED still give 0A000. The row locks of FOR KEY SHARE are not made yet.

The checklist item for DML stays open. Next: the actions of the foreign keys, then ON CONFLICT.

The CI on main failed at cargo xtask style after #1221. This fixes the SAFETY comment in os::StopSignals. (#1225)

#1229 is merged. The planner now makes a Gather Merge over a Sort of a parallel scan for the order of the query, as generate_useful_gather_paths does. With LIMIT, the Sort below the Gather Merge reads only the first rows, as create_ordered_paths does. connection/explain_gather_merge.test has the plans and the output is the same as PostgreSQL 19.

The full schedule timed out after 179 of 239 tests because of the load on server2. In these tests no file got worse. select_parallel goes from 553 to 487 diff lines, incremental_sort from 373 to 353, subselect from 558 to 540 and groupingsets from 189 to 183 with #1218. explain goes from 28 to 19.

Next in the parallel plans: create_partial_distinct_paths, so that SELECT DISTINCT over a parallel scan has a HashAggregate or a Unique in each process below the Gather.

The catalog_statistics test failed since #1128 and stopped the release of 0.4.15 and 0.4.16. This fixes it. (#1231)

Release 0.4.17 (#1234), with #1225 to #1232. The tag v0.4.16 did not build because of cargo xtask style and catalog_statistics, which #1225 and #1231 fix, so 0.4.17 has the changes of 0.4.16 too.

Commits to a frozen table now go to the commit log instead of a full checkpoint. (#1242)

#1248 is merged. The planner now makes DISTINCT in two parts over a parallel scan, as create_partial_distinct_paths does: a HashAggregate or a Unique over a Sort in each process below a Gather or a Gather Merge, and the final DISTINCT above it. The plans and the costs in connection/explain_partial_distinct.test are the same as PostgreSQL 19.

The PR also fixes the order of the rows when a partial hash table is below a Gather Merge. hash_tables no longer goes below a gather, so a Unique or a Finalize GroupAggregate over a Gather Merge gives its rows in the order of the keys.

The full schedule timed out after 225 of 239 tests because of the load on server2, and no file got worse in these tests. select_distinct goes from 48 to 20 diff lines and incremental_sort from 353 to 342.

Next in the parallel plans: a parallel index scan and index only scan, the partial Limit for a DISTINCT key that is a constant, and Parallel Append.

A logged commit now reads the versions of each changed table once instead of five times. (#1251)

#1253 is merged. When a condition makes each key of DISTINCT a constant, each process of a parallel scan now has a Limit of one row below the Gather, as create_partial_distinct_paths makes it. The plan of SELECT DISTINCT four FROM tenk1 WHERE four = 10 in select_distinct is now the same as PostgreSQL 19. The next work is the parallel index scans and index only scans, with the random_page_cost setting that select_parallel uses.

PR #1258 is merged. It adds parallel scans of a btree index and Parallel Append for select_parallel.

A btree index gets a Parallel Index Scan or a Parallel Index Only Scan. The number of workers comes from the pages of the heap and of the index, as compute_parallel_worker.
A table with children of INHERITS or a partitioned table gets a Parallel Append, also with children that have parallel_workers at 0. With enable_parallel_append off, the plan is an Append of the partial scans.
random_page_cost is now a setting.
The Gather of a base relation has the rows of the relation, as in PostgreSQL 19.

The two new tests have the output of PostgreSQL 19. All 900 tests of the rupg-session library pass.

Next in select_parallel: Parallel Bitmap Heap Scan, partial aggregates for a GROUP BY of an expression, and the hashed SubPlan filter in a parallel scan.

PR #1265 is merged. It adds the Parallel Bitmap Heap Scan for select_parallel.

The best bitmap of a table gives a partial bitmap heap scan. The workers come from the pages of the heap that the scan reads.
The planner now has parallel_safe in add_path. When a plain plan and a Gather cost about the same, the plain plan wins, as in PostgreSQL 19.
The plan of select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand = 0 with enable_seqscan off is now the same as in PostgreSQL 19: a nested loop of two Gather nodes, with a Parallel Bitmap Heap Scan on the inner side.

The new test has the output of PostgreSQL 19. All 904 tests of the rupg-session library pass.

Merged #1269. A parallel plan can now read an InitPlan and an uncorrelated SubPlan with no Gather. A correlated SubPlan keeps the scan out of the parallel plan. The One-Time Filter of EXISTS goes below the Partial Aggregate, as in PostgreSQL 19. EXPLAIN shows a scalar subquery after ANY with no cast.

The new test is explain_parallel_sublink. The session tests pass (905 tests).

Next in select_parallel: partial aggregates for a GROUP BY expression, and partial joins below a Gather.

Merged #1271. GROUP BY with no aggregate call now has a plan in two parts, a Partial HashAggregate below a Gather or a Group in each process below a Gather Merge. The partial nodes have the cost of the key expressions for each group, so the choice between the plain plan and the plan in two parts is the same as in PostgreSQL 19, with the same costs.

The new test is explain_parallel_group. The session tests pass (908 tests).

Asynchronous commit with a WAL writer, log chunks of zeros, and a commit log that reads only the changed versions (#1273)

Less work before a simple statement: settings index, known parameters, search path and type caches, relation maps (#1274)

Merged #1277. A join of a partial outer path now has a partial path, so a Gather can be above a nested loop, a merge join or a hash join, and a Parallel Hash Join can read a Parallel Hash that the workers fill together. The inner path follows the rules of PostgreSQL 19: a partial nested loop takes the cheapest total inner path only when a worker can run it, and a merge join or a hash join takes the cheapest inner path that a worker can run. The partial aggregate can read a partial join.

The new test is explain_parallel_join. The session tests pass (910 tests).

Navigation Menu

Repository navigation

M3: SQL and the point path #4

Why

The work

Exit criterion

Open questions

Harness work

Depends on

Spec

682 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

M3: SQL and the point path #4

Description

Why

The work

Exit criterion

Open questions

Harness work

Depends on

Spec

Activity

tamnd commented on Oct 17, 2026

tamnd commented on Oct 18, 2026

tamnd commented on Oct 18, 2026

tamnd commented on Oct 18, 2026

tamnd commented on Oct 19, 2026

682 remaining items

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Oct 31, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

tamnd commented on Nov 1, 2026

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions