Unique constraint violation for unique index on LOWER(col)

Question

I encountered a very strange behaviour and am hoping the experts here can help me explain why the phenomenon occurs.

I have the following table and function definitions in PostgreSQL:

CREATE TABLE test_table (
    "Id" text PRIMARY KEY,
    "Counter" int NOT NULL
);

CREATE UNIQUE INDEX idx_test_table_id ON test_table("Id");

CREATE OR REPLACE FUNCTION public.test_func(id text)
RETURNS int
AS $$
DECLARE counter int;
BEGIN
    INSERT INTO public.test_table
    VALUES (id, 2)
    ON CONFLICT ("Id")
    DO UPDATE SET "Counter" = public.test_table."Counter" + 1
    RETURNING "Counter" - 1
    INTO counter;
        
    RETURN counter;
END
$$
LANGUAGE plpgsql;

I have a test client that calls the function in a loop, asynchronously, and using the same ID.

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace Sandbox
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public async Task TestMethod1()
        {
            int id = new Random().Next();

            IList<Task> tasks = new List<Task>();

            for (int i = 0; i < 80; i++)
            {
                tasks.Add(ExecutePgFunctionAsync(id.ToString()));
            }

            await Task.WhenAll(tasks.ToArray());
        }

        private async Task ExecutePgFunctionAsync(string id)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Database=sandbox;Host=localhost;Password=runsmarter;Pooling=True;Port=12000;Timeout=15;Username=postgres;Include Error Detail=True");
            await conn.OpenAsync();

            using (NpgsqlCommand command = new NpgsqlCommand("test_func", conn))
            {
                try
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("id", id);
                    await command.ExecuteNonQueryAsync();
                }
                finally
                {
                    await command.Connection.CloseAsync();
                }
            }
        }
    }
}

Using the above definitions, everything is fine. However, if I change the unique index to:

CREATE UNIQUE INDEX idx_test_table_id ON test_table(LOWER("Id"));

I will begin to get the following error sporadically:

Npgsql.PostgresException: 23505: duplicate key value violates unique constraint "idx_test_table_id"

If I further add LOWER() to the conflict condition, i.e.:

ON CONFLICT (LOWER("Id"))

The error changes to:

Npgsql.PostgresException: 23505: duplicate key value violates unique constraint "test_table_pkey"

Why do these errors occur?

Addendum

I re-ran the original code almost as-is on a fresh installation of Visual Studio and PostgreSQL. I added a catch clause to ExecutePgFunctionAsync() in the hope of providing more diagnostic data.

With:

I get the following exception:

And the table is in the following state at the time of exception (please note the value of Counter will vary across runs):

With:

I get the following exception:

And the table is in the following state at the time of exception:

Answer 1

Not really an answer, just some ideas that are too long to fit in a comment:

1. I set up a test case locally and could not get a duplicate error even when running up to 10,000 inserts. Granted I was not using Npgsql, just an anonymous function in psql. My guess is that this is maybe the reason I succeeded, no async. More on that below.

2. From here On CONFLICT:

INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.

3. Also from the above link:

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.

4. My suspicion is that the async nature of the inserts is tripping 2) and/or 3). Especially since there are two UNIQUE indexes on "Id". Though that does not explain why LOWER("Id") seems to be a problem.

5. I am still not sure what the the purpose is of using text field for an integer value? Nor why if the values are all going to be integers you would use LOWER("Id")?

6. To have a better chance at getting a definitive answer I would suggest that you post to the pgsql-general mailing list. Though be prepared for questions on the points in 5).