problem with MERGE command? - Mailing list pgsql-admin

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
noway put a row with nulls in most of the data because of the not null on >50% of columns. Then the MERGE statement
whichintermittently 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
itdoesn't make sense to partition the data in the db by that.) There are multiple processes at once updating the
differentslices for a given date, so there is a possibility of deadlock--although with the current pre-MERGE way of
doingthings, it is quite rare in practice. Finally, the delete restriction is incomplete--it is possible for one
chunk'sMERGE command to delete rows being updated or inserted by another. This is my mistake, and I'll correct it, but
thisseems like undesirable behavior here, I'd expect a more direct message about write conflict or deadlock. BTW, in
testingI turned on logging of all SQL, and included transaction IDs in the logs, and confirmed that with the db access
librarieswe'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@elevated-dev.com
https://www.linkedin.com/in/scottribe/