Multiple inserts via pgsql function cause error

Question

I have a pgsql function which looks up a value (unique constraint) in a table and returns the id. If the value does not exist, it creates the entry and returns the id. The id is used to create rows in another table. I use the function with node-pg to bulk-insert. Most of the time, it works good, but sometimes returns

ERROR duplicate key value violates unique constraint "collections_slug_key" Key (slug)=(heroes-follow-heroes) already exists.","where":"SQL statement "INSERT INTO collections (slug) VALUES (slug_) RETURNING id" PL/pgSQL function myfunction(slug, key) line 11

I don't understand it, since I also tried it in a transaction (and I thought pgsql functions are in a transaction anyway).

The sql to create the table looks like this:

BEGIN TRANSACTION;

CREATE TABLE collections (
    id BIGSERIAL PRIMARY KEY,
    slug TEXT NOT NULL,
    UNIQUE(slug)
);

CREATE OR REPLACE FUNCTION myFunction(
    slug_ TEXT,
    key_ TEXT)
RETURNS VOID AS $$
BEGIN
    DECLARE
        collection_id_ BIGINT;

    BEGIN
        SELECT INTO collection_id_ id
        FROM collections AS c
        WHERE c.slug = slug_;
        IF NOT FOUND THEN
            INSERT INTO collections (slug)
            VALUES (slug_) RETURNING id INTO collection_id_;
        END IF;
    END;
END;
$$
LANGUAGE plpgsql;

COMMIT;

The insert-code looks like this:

SELECT myFunction(
    b.slug,
    b.key)
FROM json_to_recordset($1)
AS b(
    slug TEXT,
    key TEXT
)

I used a basic node-pg query:

await client.query({
    "name": "INSERT_MY",
    "text": await FILE_CONTENT_QUERY,
    "values": [JSON.stringify(data)],
});

I also tried node-pg transactions with manual BEGIN, COMMIT, ROLLBACK, but it had the same issues. Any ideas?

Multiple inserts via pgsql function cause error

0 Answers