MemberJunction / MemberJunction/MJ
Integration sync: spCreate re-selects by composite PK, so a NULL key component loses the row and fails the run
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 308
Description
## Symptom
A sync ends `Failed` with per-record errors reading:
```
Error creating new record, no rows returned from SQL: WITH save_result AS (
SELECT * FROM ."spCreate"(...)
```
The run had already written thousands of rows successfully.
## Cause
The generated `spCreate` inserts the row and then re-selects it **by primary key** to return it.
When the entity's PK is composite and any component is NULL, `= NULL` matches nothing, so the
re-select returns zero rows, the engine treats the create as failed, and the record is dropped.
Because the engine counts that as a failed create, the whole run is marked `Failed`.
## Reproduction
Observed live on a PostgreSQL instance with the GA4 connector. `UtmPerformance`'s PK is
`(date, sessionCampaignName, sessionSource, sessionMedium)`. GA4 reports untagged traffic — direct
visits, links in an email signature — with an empty campaign and medium. Failing key:
```
2026-08-05||email_signature|
```
That single class of row failed a run that had already written 20,736 records.
## Why it is worth fixing centrally
Any connector whose PK components can be empty hits this, and the failure is asymmetric: the
individual rows are lost **and** the run is marked failed, so a healthy sync looks broken.
We worked around it connector-side (filling empty key components with GA4's own `(not set)`
literal — MemberJunction/Integrations#145), but that is a per-connector patch for a platform
behaviour. A composite-key insert that succeeds should not depend on every component being
non-NULL.
Suggested directions: return the inserted row via `RETURNING` instead of a post-insert re-select,
or make the re-select NULL-safe (`IS NOT DISTINCT FROM`).
## Environment
MJ 5.51.0, PostgreSQL.
Contributor guide
Research direction
Start by tracing the generated spCreate insert-and-reselect path described in the issue, focusing on PostgreSQL composite primary keys with NULL components. Compare the suggested RETURNING and NULL-safe re-select directions, then verify that a successful insert returns its row and that the sync remains successful for the reproduced GA4 case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100