null value in "col01" of relation "mytable_p2025_07_24" violates not-null constraint",
Failing row contains (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
So this is the base table:
Column | Type | Collation | Nullable | Default
---------------------------+--------------------------+-----------+----------+----------------------------------------------
col01 | text | | not null | 'foo'::text
col02 | double precision | | |
col03 | double precision | | |
col04 | double precision | | |
col05 | bigint | | not null |
col06 | text | | not null |
col07 | double precision | | |
col08 | double precision | | |
col09 | double precision | | |
col10 | date | | not null |
col11 | double precision | | not null |
col12 | double precision | | |
col13 | double precision | | not null |
col14 | uuid | | not null |
col15 | double precision | | |
col16 | text | | not null |
col17 | bigint | | not null |
col18 | bigint | | not null |
col19 | uuid | | not null | '00000000-0000-0000-0000-000000000000'::uuid
col20 | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Partition key: RANGE (col10)
I have a batch of data all on the same date, and am updating the partition for that date directly. I create a temp table, make inserts into it, then merge all in one transation. So first:
CREATE TEMP TABLE "mytable_p2025_07_16_1BFC84330FA88012"
(LIKE mytable_p2025_07_16 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING GENERATED INCLUDING IDENTITY INCLUDING INDEXES) ON COMMIT DROP;
Next come some INSERT statements--multiples because there's enough rows to exceed 64K value bindings. Note that there's no way put a row with nulls in most of the data because of the not null on >50% of columns. Then the MERGE statement which intermittently gives the error shown above:
MERGE INTO ONLY mytable_p2025_07_16 AS t1 USING "mytable_p2025_07_16_1BFC84330FA88012" AS t2
ON t1.col10 = t2.col10 AND t1.col17 = t2.col17
AND t1.col18 = t2.col18 AND t1.col05 = t2.col05
AND t1.col14 = t2.col14 AND t1.col16 = t2.col16
AND t1.col01 = t2.col01 AND t1.col06 = t2.col06
WHEN MATCHED AND (
t1.col02 IS DISTINCT FROM t2.col02
OR t1.col03 IS DISTINCT FROM t2.col03
OR t1.col04 IS DISTINCT FROM t2.col04
OR t1.col07 IS DISTINCT FROM t2.col07
OR t1.col08 IS DISTINCT FROM t2.col08
OR t1.col09 IS DISTINCT FROM t2.col09
OR t1.col11 IS DISTINCT FROM t2.col11
OR t1.col12 IS DISTINCT FROM t2.col12
OR t1.col13 IS DISTINCT FROM t2.col13
OR t1.col15 IS DISTINCT FROM t2.col15
) THEN UPDATE SET
col02 = t2.col02, col03 = t2.col03,
col04 = t2.col04, col07 = t2.col07,
col08 = t2.col08, col09 = t2.col09,
col11 = t2.col11, col12 = t2.col12,
col13 = t2.col13, col15 = t2.col15,
col19 = t2.col19
WHEN NOT MATCHED BY TARGET THEN
INSERT (
col01, col02, col03, col04, col05, col06,
col07, col08, col09, col10, col11,
col12, col13, col14, col15, col16, col17,
col18, col19, col20
) VALUES (
col01, col02, col03, col04, col05, col06,
col07, col08, col09, col10, col11,
col12, col13, col14, col15, col16, col17,
col18, col19, col20
)
WHEN NOT MATCHED BY SOURCE AND col17 = ANY($1) THEN DELETE
It's worth noting that the data comes in from multiple streams, with different but overlapping values for col17. (And it doesn't make sense to partition the data in the db by that.) There are multiple processes at once updating the different slices for a given date, so there is a possibility of deadlock--although with the current pre-MERGE way of doing things, it is quite rare in practice. Finally, the delete restriction is incomplete--it is possible for one chunk's MERGE command to delete rows being updated or inserted by another. This is my mistake, and I'll correct it, but this seems like undesirable behavior here, I'd expect a more direct message about write conflict or deadlock. BTW, in testing I turned on logging of all SQL, and included transaction IDs in the logs, and confirmed that with the db access libraries we're using, the CREATE TABLE/INSERT/MERGE commands were all actually executed in a single transaction.
PostgreSQL 17.5, running in an UBI 9 image.
--
Scott Ribe
scott_ribe@xxxxxxxxxxxxxxxx
https://www.linkedin.com/in/scottribe/
