MemberJunction / MemberJunction/MJ

Integration sync: spCreate re-selects by composite PK, so a NULL key component loses the row and fails the run

Open
#3,526 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.