error happens randomly under load

[necryin]

Hi, I use pgx v4.16.1 and I have a schema in postgres 12.9 (13 also tested)

CREATE TYPE promo_reason AS ENUM ('a');
CREATE TYPE promo_type AS ENUM ('b');

CREATE TABLE promos
(
    promo_id    UUID         NOT NULL PRIMARY KEY,
    user_id     UUID         NOT NULL,
    reason      promo_reason NOT NULL,
    type        promo_type   NOT NULL,
    created_at  TIMESTAMPTZ  NOT NULL,
    consumed_at TIMESTAMPTZ  NULL
);

CREATE UNIQUE INDEX promotions_user_id_first_transfer_key ON promos (user_id)
    WHERE type = 'b' AND reason = 'a';

And have query:

	const query = `
INSERT INTO promos
    (promo_id, user_id, reason, type, created_at, consumed_at)
VALUES
    ($1, $2, $3, $4, $5, NULL)
ON CONFLICT (user_id) WHERE type = $4 AND reason = $3
DO NOTHING
`

	_, err := pgxPool.Exec(ctx, query, p.ID, p.UserID, p.Reason, p.Type, p.CreatedAt)
	return err

I use small test exec this query 100 times in separate goroutines.

Under load sometimes error return ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)
If I scale maxConn error frequency lowers.
Also it is ok if I use lib/pg

Looks like some error inside pgxpool

[necryin]

Problem appears only if prefer_simple_protocol=false (default value)

[jackc]

Under load sometimes error return ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)

I have no idea how a bug in the calling code or in pgx could induce that bug randomly. The SQL should either work or not.

Maybe try the race detector? Maybe the SQL is getting garbled. Or are you concurrently doing DDL on that table?

[necryin]

Min code to reproduce:

package main

import (
	"context"
	"sync"
	"testing"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v4/pgxpool"
	"github.com/stretchr/testify/require"
)

const ddlUp = `
CREATE TYPE promo_reason AS ENUM ('promo_reason');
CREATE TYPE promo_type AS ENUM ('promo_type');

CREATE TABLE promos
(
promo_id    UUID         NOT NULL PRIMARY KEY,
user_id     UUID         NOT NULL,
reason      promo_reason NOT NULL,
type        promo_type   NOT NULL
);

CREATE UNIQUE INDEX promotions_user_id_first_transfer_key ON promos (user_id)
WHERE type = 'promo_type' AND reason = 'promo_reason';
`

const ddlDown = `
DROP TABLE promos;

DROP TYPE promo_reason;
DROP TYPE promo_type;
`

const insert = `
INSERT INTO promos
    (promo_id, user_id, reason, type)
VALUES
    ($1, $2, $3, $4)
ON CONFLICT (user_id) WHERE type = $4 AND reason = $3
DO NOTHING
`

type PromoReason string

const PromoReasonS PromoReason = "promo_reason"

type PromoType string

const PromoTypeS PromoType = "promo_type"

type Promo struct {
	PromoID     uuid.UUID
	UserID      uuid.UUID
	PromoReason PromoReason
	PromoType   PromoType
}

func Test_Upsert(t *testing.T) {
	ctx := context.Background()

	dsn := "host=127.0.0.1 port=7432 user=postgres password=password dbname=test sslmode=disable prefer_simple_protocol=false"
	pool, err := pgxpool.Connect(ctx, dsn)
	require.NoError(t, err)
	defer pool.Close()

	_, err = pool.Exec(ctx, ddlUp)
	require.NoError(t, err)
	defer func() {
		_, err = pool.Exec(ctx, ddlDown)
		require.NoError(t, err)
	}()

	p := Promo{
		UserID:      uuid.New(),
		PromoReason: PromoReasonS,
		PromoType:   PromoTypeS,
	}

	wg := sync.WaitGroup{}
	for i := 0; i < 200; i++ {
		wg.Add(1)
		promo := p

		go func(promo Promo) {
			promo.PromoID = uuid.New()
			_, insertErr := pool.Exec(ctx, insert, promo.PromoID, promo.UserID, promo.PromoReason, promo.PromoType)
			if insertErr != nil {
				t.Errorf("FAIL: %v", insertErr)
			} else {
				t.Log("SUCCESS")
			}
			wg.Done()
		}(promo)
	}
	wg.Wait()
}

no races detected, no other queries in postgres

[jackc]

I believe you have actually encountered a PostgreSQL bug. After examining your reproduction case I case able to duplicate the issue directly in psql. The error always occurs on the 6th execution. The PostgreSQL planner decides after 5 executions whether to use a generic plan or a custom plan.

Here is the reproduction case in psql:

CREATE TYPE promo_reason AS ENUM ('promo_reason');
CREATE TYPE promo_type AS ENUM ('promo_type');

CREATE TABLE promos
(
promo_id    UUID         NOT NULL PRIMARY KEY,
user_id     UUID         NOT NULL,
reason      promo_reason NOT NULL,
type        promo_type   NOT NULL
);

CREATE UNIQUE INDEX promotions_user_id_first_transfer_key ON promos (user_id)
WHERE type = 'promo_type' AND reason = 'promo_reason';

PREPARE s AS INSERT INTO promos
    (promo_id, user_id, reason, type)
VALUES
    ($1, $2, $3, $4)
ON CONFLICT (user_id) WHERE type = $4 AND reason = $3
DO NOTHING;

EXECUTE s ('00ebd890-f5ac-47c7-9365-4ce9875c04a1', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('8e3775d9-af90-472f-9720-d0341ff7bba7', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('f983db27-62d9-4ef1-bc67-e1e492eee48e', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('10532081-851c-4dc4-9d83-8750bd4cf78d', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('98e049e4-8762-4c95-be6c-31f4d8f9b04e', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('355972ac-20a4-4c17-b28d-70d8cb2dd5b8', '132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');

I just reported the bug on the PostgreSQL tracker. It was assigned bug reference 17532. The discussion should show up soon on the pgsql-bugs mailing list.