Strange sql errors when lot of queries in concurrency

[acemtp]

When we have some load with lot of sql requests, sometimes (cannot reproduce), we get some extremely strange error messages in burst then, when the load gone, no more errors

All requests works 99% of the time.

Here is some error messages:

RangeError [ERR_OUT_OF_RANGE]: The value of "offset" is out of range. It must be >= 0 and <= 14. Received 18
TypeError: Cannot read properties of null (reading 'columns')
RangeError [ERR_OUT_OF_RANGE]: The value of "offset" is out of range. It must be >= 0 and <= 15. Received 19
PostgresError: insert or update on table "presences" violates foreign key constraint "presences_member_id_fkey"
RangeError [ERR_OUT_OF_RANGE]: The value of "offset" is out of range. It must be >= 0 and <= 2721. Received 2725

It happens on differents sql requests.

First Example

`message:"insert or update on table "presences" violates foreign key constraint "presences_member_id_fkey""``

The code is:

    const { memberUrn } = presence;

    const members = await sql`SELECT id from members WHERE urn = ${memberUrn}`;
    const memberId = members[0]?.id;
    if (!memberId) continue; // it's in a for() loop

    await sql`
      INSERT INTO presences ${sql({ memberId, week: presence.week, day: presence.day, hour: presence.hour })}
      ON CONFLICT (member_id, week, day, hour)
      DO NOTHING;
    `;

Since the INSERT is called, it means the memberId is found and in this case, it's 2788562.

But the INSERT failed and if we manually select the member with id 2788562, it returns nothing....

The error details are:

{
        "args": [
            {
                "first": {
                    "day": 3,
                    "hour": 17,
                    "memberId": 2788562,
                    "week": 2877
                },
                "rest": []
            }
        ],
        "code": "23503",
        "constraint_name": "presences_member_id_fkey",
        "detail": "Key (member_id)=(2788562) is not present in table \"members\".",
        "file": "ri_triggers.c",
        "line": "2599",
        "message": "insert or update on table \"presences\" violates foreign key constraint \"presences_member_id_fkey\"",
        "name": "PostgresError",
        "parameters": [
            "2788562",
            "2877",
            "3",
            "17"
        ],
        "query": "\n      INSERT INTO presences (\"member_id\",\"week\",\"day\",\"hour\")values($1,$2,$3,$4)\n      ON CONFLICT (member_id, week, day, hour)\n      DO NOTHING;\n    ",
        "routine": "ri_ReportViolation",
        "schema_name": "public",
        "severity": "ERROR",
        "severity_local": "ERROR",
        "table_name": "presences",
        "types": [
            23,
            21,
            21,
            21
        ]
}

So the question is... HOW is it possible that the SELECT returns a number that we cannot find...

We NEVER delete any members...

Second crazy example

Even crazier...

The query is:

    const users = await Users.find({}).fetch();
    const urns = users.map(u => u.urn);

    rows = await sql`
      WITH members_to_update AS (
        SELECT id, updated_at
        FROM members
        WHERE update_at < CURRENT_TIMESTAMP
        AND urn NOT IN ${sql(urns)}
        ORDER BY update_at DESC
        LIMIT ${extensionRequestsMembers}
        FOR UPDATE SKIP LOCKED
      )
      UPDATE members m
      SET update_at = CURRENT_TIMESTAMP + INTERVAL '1 day', 
          updated_at = CURRENT_TIMESTAMP
      FROM members_to_update mtu
      WHERE m.id = mtu.id
      RETURNING m.id, m.urn, mtu.updated_at as old_updated_at;
    `;

{
        "args": [
            {
                "first": [],
                "rest": []
            },
            1
        ],
        "code": "ERR_OUT_OF_RANGE",
        "message": "The value of \"offset\" is out of range. It must be >= 0 and <= 15. Received 19",
        "name": "RangeError",
        "parameters": [
            "1"
        ],
        "query": "\n      WITH members_to_update AS (\n        SELECT id, updated_at\n        FROM members\n        WHERE update_at < CURRENT_TIMESTAMP\n        AND urn NOT IN (null)\n        ORDER BY update_at DESC\n        LIMIT $1\n        FOR UPDATE SKIP LOCKED\n      )\n      UPDATE members m\n      SET update_at = CURRENT_TIMESTAMP + INTERVAL '1 day', \n          updated_at = CURRENT_TIMESTAMP\n      FROM members_to_update mtu\n      WHERE m.id = mtu.id\n      RETURNING m.id, m.urn, mtu.updated_at as old_updated_at;\n    ",
        "types": [
            20
        ]
}

What it this offset? we have a null and $1 is a number?

Conclusion

The only viable hypothesis is that when in high load / high concurrency, the parameters are mixed between requests or errors are completely not related to the requests.

We use:
postgres: ^3.4.5
node v20.17.0
postgresql 17

Completely lost by this