How to set a field in a row to number of some rows in the same table without concurrency problems in postgresql?

Assume I have an schema like this:

CREATE TABLE "test2" (
    "id" SERIAL NOT NULL,
    "text" CHAR(10),

    PRIMARY KEY ("id")
);
CREATE TABLE "test1" (
    "id" SERIAL NOT NULL,
    "fkey" INTEGER NOT NULL,
    "order" INTEGER NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY ("id")
);

ALTER TABLE "test1" ADD FOREIGN KEY ("fkey") REFERENCES "test2"("id") ON DELETE CASCADE ON UPDATE CASCADE;

for some performance optimizations i want to store in the "order" field, number of rows in "test1" table referencing same row in "test2". So i try this on inserts:

insert INTO "test1"
        ("fkey","order")
        select '3', count(*) from "test1" where "fkey"='3';

Can this have concurrency problems? (e.g. When two threads insert to test1 with same fkey at the same time can the values of order become incorrect? How to solve this issue?

Solution

Yes, you'll need to account for concurrency issues when multiple inserts are done at the same time.

There are several solutions. Off the top of my head I can see:

Acquire an exclusive lock in the database. Probably a row in the main table will do.
Manage a lock or semaphore outside the database.
Insert the rows, but update the order in a deferred way, probably using a queue (most likely a priority queue), or single separate process.
Use optimistick locking while inserting.

Each one of these have some drawbacks, so it'll depend on the specific requirements you have.

I would guess that you don't want to update all siblings each time you insert a new child; that could be overkill in terms of performance. If this sounds reasonable, maybe you can use a priority queue, run a process every 10 minutes (or so), group all children of the same parent and update them at once.