Bulk Upsert (match-or-create)

Development correction (#1320): the constraint checks and entity-complete graph-chunk boundaries described below are being qualified after0.21.0. Released0.21.0 bulk resolvers can bypass schema constraints; do not assume that a successful bulk request proves constraint validity on that revision.

Where Bulk Loading is about getting a lot of data in quickly, upsert is about reconciling data: for each input record, find an existing node (or edge) by a match key and UPDATE it, or CREATE it if it isn’t there. It is a bulk, index-accelerated MERGE — served online, over REST, by the leader.

By default, every upsert runs the versioned path — through the engram log, bracketed by a pre-load marker — so the whole load, however large, reverts as one unit (see Time Travel). With the default conflict policy, an ambiguous match rejects atomic resolution before graph changes. Chunked loads and lost responses have different recovery boundaries below.

There are three flavours, all admin- and leader-only REST endpoints:

All three accept a chunk_rows field: each chunk is one replicated Raft entry, so a very large load doesn’t ship as one giant entry (see Chunking a very large load). For /mgmt/upsert-graph, chunk_rows is a target number of resolved graph operations rather than input rows. A boundary extends to keep all changes to one entity together, including creation and its required properties. The entry can therefore exceed that target; the server’s entry-size limit still applies.

An ambiguous relationship endpoint fails explicitly. Node-file imports also offer the explicit on_conflict policies described below; none silently picks one arbitrary matching node.

Bulk resolution checks the final touched entities with the same uniqueness, required-property and property-type rules as Cypher writes, including secondary labels and relationship constraints. Validation uses disposable state, not partially applied live writes. Each committed chunk must also be valid on its own; rejecting a later chunk does not undo earlier committed chunks.

Node upsert — POST /mgmt/upsert

Match-or-create nodes on a per-batch match key. The file path is confined to the server’s filesystem via the same --allow-csv-dir sandbox LOAD CSV uses (see Bulk Loading → Server file-I/O sandbox).

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert \
  -H 'content-type: application/json' \
  -d '{
    "db": "default",
    "file": "/srv/import/people.csv",
    "label": "Person",
    "match": ["email"],
    "merge": "add",
    "on_conflict": "error",
    "strategy": "seek"
  }'

{"created": 2, "updated": 1, "skipped": 0, "conflicts": 0,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_pre_1699…",
 "revert_hint": "restore_to('6f2f…')"}

Per row, an existing :Person is found by exact equality on the match key (one attribute, or a composite ["first","last","dob"]). Found → UPDATE per merge; not found → CREATE. Re-running the same file is idempotent (it updates in place, never duplicates).

• merge: "add" (default) — add/overwrite the row’s attributes, keep the rest.
• merge: "replace" — the node keeps ONLY the row’s attributes (drops others).
• merge: "add-missing" — set only attributes the node does not already have.
• on_conflict: "error" (default) — a match key that hits more than one node fails loud. "update-all" updates every match; "skip" leaves them, counts them.
• strategy: "seek" (default) or "sort-merge" — two byte-identical lookup strategies; sort-merge reads the index in one sequential pass and is faster when the input is a large fraction of the label.
• chunk_rows — for a load too large to hold in RAM as one changeset (see below).

Concurrency safety. The resolve+propose runs through the same optimistic conflict-check pipeline REST autocommit writes use, so two concurrent upserts of the same value don’t both create a duplicate — the loser’s retry re-resolves against fresh HEAD and MATCHes the winner’s just-created node. This detection needs the match key to be a UNIQUE or NODE KEY constraint (see Constraints); an unconstrained match key is not conflict-detected online.

Chunking a very large load

By default an upsert is applied as one changeset (one engram), so the whole resolved batch is held in RAM. That is fine up to millions of rows, but a truly huge load (tens of millions of nodes/edges) can exhaust memory.

Pass chunk_rows to process the load in chunks of N rows, each applied as its own changeset — so peak memory stays bounded by the chunk size, not the total row count, and an arbitrarily large file loads:

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert \
  -H 'content-type: application/json' \
  -d '{"db":"default","file":"/srv/import/huge.parquet","label":"Person","match":["email"],"chunk_rows":100000}'

Rows whose keys an earlier chunk already created are matched, not duplicated. Every committed chunk must independently satisfy the schema constraints. Consequently, a whole-batch change such as exchanging two unique values may be valid atomically but invalid at an intermediate chunk boundary. The load remains revertable as one unit via its pre-load tag; chunking does not promise that every input accepted atomically will also succeed chunkwise.

Two things to know about the trade-off:

• Chunking is somewhat slower (each chunk pays a small per-changeset overhead) — a larger chunk_rows means less overhead but more memory.
• A chunked load is not all-or-nothing: if a chunk fails partway through (e.g. a non-unique match key), the earlier chunks stay applied. Recover using the pre-load HEAD/tag or reconcile the committed prefix. An error response need not contain the successful response’s revert hint. Without chunk_rows, a validation rejection before proposal leaves the graph untouched; use the default when the whole load must be atomic and fits in memory.

Constraint validation may briefly wait for a complete native read snapshot while another publication finishes. This wait consumes the request’s existing deadline; it does not extend the timeout, repeat committed chunks, or skip constraints. Embedded callers without a deadline receive the publication refusal immediately rather than waiting indefinitely.

A timeout or dropped connection is not evidence of rollback, even for an atomic request. The server may have committed the batch without its response reaching the caller. Persist the intended keys/values, check authoritative committed state with the appropriate causal barrier, and reconcile before retrying. Do not treat an initially absent write as proof that an in-flight request can no longer commit. Upserts do not automatically provide an unbounded exactly-once guarantee for arbitrary retry sequences.

/mgmt/upsert-edges takes the same chunk_rows field with the same semantics. /mgmt/upsert-graph applies all the subgraph’s nodes in chunks first (so an edge always finds its endpoints), then the edges in chunks. Note the JSON document itself is still held in memory as one coupled payload — chunking bounds the per-changeset memory, not the size of the document you can post in one call.

Edge upsert — POST /mgmt/upsert-edges

Match-or-create relationships between existing nodes. Each row’s start and end endpoints are resolved by the same match machinery as node upsert, then the edge is matched on its canonical (start, end, type) identity.

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert-edges \
  -H 'content-type: application/json' \
  -d '{
    "db": "default",
    "file": "/srv/import/works_at.csv",
    "start_label": "Person", "start_match": ["email"],
    "end_label": "Company", "end_match": ["name"],
    "rel": "WORKS_AT"
  }'

Every column named in start_match/end_match is consumed as an endpoint match value and is never an edge property; every other column is an edge property. Because patinaDB edges are id-less (at most one edge per (start,end,type)), there is no conflict policy for the edge itself — only on_missing governs a row whose endpoint match finds no node:

• "error" (default) — fail the whole load loud, touching nothing.
• "skip" — leave the row uncreated, count it, keep going.
• "create" — CREATE the missing endpoint node too, instead of failing or skipping. The created node’s properties are set to exactly its match-key attribute(s) — nothing else. Two rows in the same batch that reference the SAME missing endpoint (same label + same match value) resolve to the SAME newly-created node, so a file with many edges into one new node never creates duplicates.

{"created": 2, "updated": 0, "skipped": 0, "missing_endpoints": 1,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_edges_pre_1699…"}

Subgraph upsert — POST /mgmt/upsert-graph

The most powerful form: match-or-create a whole node+edge subgraph described in ONE coupled JSON document, posted as the request body, applied as one revertable engram.

Edges reference nodes by a fake id — a payload-local handle (unique within the document) used ONLY to wire edges[].from/.to to nodes[].id. A fake id is never stored; the server replaces each with the real DB UUID the node resolved to. This is what makes a homogeneous self-relationship (e.g. Person KNOWS Person) trivial — something a value-only edge match can’t express.

{
  "identity": { "Person": ["email"], "Company": ["name"] },
  "merge": "add",
  "nodes": [
    { "id": "p1", "label": "Person",  "props": { "email": "a@x", "name": "Alice", "age": 30 } },
    { "id": "c1", "label": "Company", "props": { "name": "Acme", "industry": "tech" } }
  ],
  "edges": [
    { "from": "p1", "to": "c1", "type": "WORKS_AT", "props": { "since": 2020 } }
  ]
}

curl -s -u neo4j:secret -X POST localhost:21001/mgmt/upsert-graph \
  -H 'content-type: application/json' --data-binary @graph.json

{"nodes_created": 2, "nodes_updated": 0, "edges_created": 1, "edges_updated": 0,
 "pre_load": "6f2f…", "post_load": "a1e3…",
 "pre_load_tag": "_upsert_graph_pre_1699…"}

Any number of node and relationship types

A subgraph document can mix as many node labels and relationship types as you like — each label is matched-or-created by its own identity, and the edges wire fake ids together across type boundaries. One document, one revertable engram:

{
  "nodes": [
    { "id": "p1", "label": "Person",  "props": { "email": "alice@x", "name": "Alice" } },
    { "id": "c1", "label": "Company", "props": { "name": "Acme" } },
    { "id": "s1", "label": "Skill",   "props": { "name": "Rust" } },
    { "id": "s2", "label": "Skill",   "props": { "name": "Graphs" } }
  ],
  "edges": [
    { "from": "p1", "to": "c1", "type": "WORKS_AT",  "props": { "since": 2020 } },
    { "from": "p1", "to": "s1", "type": "HAS_SKILL", "props": { "level": 5 } },
    { "from": "p1", "to": "s2", "type": "HAS_SKILL" }
  ]
}

Post this with an identity block naming Person=email, Company=name, and Skill=name — Alice is now connected to a Company and two Skills in one shot: three node types, two relationship types, resolved and applied together.

Where the match key (identity) comes from

Which properties make a node unique is resolved per label:

1. a persisted UNIQUE or NODE KEY constraint on the label — then you can omit the identity block entirely; else
2. the document’s identity block.

If both a constraint and a differing identity block exist, or neither exists, the load fails loud — patinaDB never guesses how to match your nodes.

An edge whose from/to names a fake id that isn’t in the document fails loud (the payload must be self-contained). merge (default "add") applies to node and edge properties, exactly as for node upsert.

The homogeneous case (Person KNOWS Person)

{
  "identity": { "Person": ["email"] },
  "nodes": [
    { "id": "p1", "label": "Person", "props": { "email": "a@x" } },
    { "id": "p2", "label": "Person", "props": { "email": "b@x" } },
    { "id": "p3", "label": "Person", "props": { "email": "c@x" } }
  ],
  "edges": [
    { "from": "p1", "to": "p2", "type": "KNOWS" },
    { "from": "p2", "to": "p3", "type": "KNOWS" }
  ]
}

The KNOWS edges land on exactly a→b and b→c (and NOT a→c) — the fake ids disambiguate which Person is which, even though every node has the same label.

Edge cardinality — 1-to-many vs 1-to-1

By default an edge is 1-to-many: many :WORKS_AT edges can leave one Person. Set an edge’s "identity" field to make it 1-to-1 — useful when a relationship is exclusive (one employer, one current owner, …).

1-to-many (default) — targets coexist. Load a-[:WORKS_AT]->Acme, then a-[:WORKS_AT]->Globex, and Alice works at both:

{ "from": "p1", "to": "c1", "type": "WORKS_AT" }

1-to-1 ("from") — the new target replaces the old. With a-[:WORKS_AT]->Acme already present, upserting:

{ "from": "p1", "to": "c2", "type": "WORKS_AT", "identity": "from" }

deletes the a→Acme edge and creates a→Globex — Alice now works at exactly one company. Re-upserting the same target just updates that edge’s properties.

Extra edge-property uniqueness — "match"

Because patinaDB edges are id-less, there is at most one physical edge per (from,to,type). An optional per-edge "match": ["attr",…] names edge properties that are part of the edge’s logical identity and acts as a guard: if a committed edge already occupies the triple but its match-property values differ from what you’re upserting, the load fails loud rather than silently overwriting a semantically-different edge. It composes with any cardinality mode.

Reverting a load

Every upsert is a single revertable changeset. The returned pre_load HEAD (and its replicated tag) is the exact state before the load. On the server:

USE default AS OF TAG '_upsert_pre_1699…'
MATCH (n) RETURN count(n)

Or inspect the whole load as one diff — CALL patinadb.diffRange('<pre_load>', '<post_load>') (see Diffs and Time Travel). The pre-load tag is replicated, so USE <db> AS OF TAG '<tag>' reads the graph as it was before the load on any node.

Treat pre_load_tag as an opaque returned identifier, not a timestamp to parse or construct. The #1323 fix (development, not yet released) derives new marker names from the exact pre-load Engram; older builds used seconds and could reuse a tag for different loads in the same second. Existing tags are not rewritten. On an older build, retain the explicit pre_load ID rather than trusting a shared generated tag to keep its earlier binding. The fix also covers offline CLI node, relationship and graph upsert markers. Marker prefixes remain intact; the suffix is not an API for extracting wall-clock time.

Rollback retention is an operational policy

Pre-load tags pin history snapshots. Frequent tiny management upserts therefore accumulate rollback artifacts; the snapshot cache setting is not a total budget for tagged history. Prefer ordinary Bolt writes for individual ticket updates and group nightly synchronization into a managed load (chunked when necessary). Retain each returned marker until the load is reconciled and its rollback window expires. Then an administrator can DROP TAG <returned_tag> if no other workflow still needs it; never blindly delete snapshot files. Dropping the last tag referencing a snapshot unpins it, but is not a promise of immediate disk reclamation. Monitor allocated disk and preserve restore/maintenance headroom.

The #1261 two-hour 4,096-ticket management-load drill retained 755 snapshot files occupying 1,163,825,152 allocated bytes on one source node, versus 10,862,592 allocated bytes for its live graph file. This is a fixture-specific warning, not an extrapolation to larger databases; see the backup qualification report.

Snapshot admission between graph chunks

Before proposing each resolved graph chunk, the server validates its constraints against a native snapshot. If a concurrent publication temporarily prevents a consistent snapshot, this read-only validation can wait within the load’s existing query deadline. Each attempt uses that same deadline; it is not reset for each chunk. With no configured deadline, snapshot refusal returns immediately. Constraint violations and write/commit errors are not retried by this handling.

This wait never repeats an already submitted chunk and does not make the whole load atomic. A terminal failure can still leave earlier chunks applied. It does not carry the non-chunked write_outcome: "not_submitted" receipt, and a client must not blindly replay the whole request. No new environment setting or timeout increase is introduced (#1483).