Reputation: 11910
I'm trying to run the following query in PSQL -
DO $$
BEGIN TRANSACTION
LOCK TABLE tags IN EXCLUSIVE MODE;
IF (SELECT COUNT(*)
FROM tags
WHERE user_id = 1) > 3
THEN
INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
VALUES (1, 4, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313')
RETURNING "id";
INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
VALUES (4, 1, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313')
RETURNING "id";
ELSE
ROLLBACK;
END IF;
COMMIT;
$$;
Unfortunately it keeps erroring out with -
ERROR: syntax error at or near "TRANSACTION"
LINE 2: BEGIN TRANSACTION
^
and I can't seem to figure out why it doesn't like BEGIN TRANSACTION
. I tried playing around with adding a ;
and removing the keyword TRANSACTION
as well.
Upvotes: 3
Views: 3878
Reputation: 32326
In an anonymous code block BEGIN
signals the beginning of the code, not the SQL command BEGIN
. Note that a function, including an anonymous code block, runs in its own transaction so you usually do not need an explicit additional transaction.
In your code a transaction is not needed at all since you are only SELECT
ing data prior to the ROLLBACK
. This should work just fine:
DO $$
DECLARE
cnt integer;
BEGIN
SELECT count(*) INTO cnt
FROM tags
WHERE user_id = 1;
IF cnt > 3 THEN
INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
VALUES (1, 4, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313');
INSERT INTO "tags" ("user_id", "friend_id", "status", "explanation", "score", "created_at", "updated_at")
VALUES (4, 1, 'pending', '', 0.0, '2016-02-29 00:43:58.969313', '2016-02-29 00:43:58.969313');
END IF;
END;
$$ LANGUAGE plpgsql;
You are typically better off leaving concurrency resolution to the DBMS. Using an EXCLUSIVE LOCK
on a table that is often accessed will slow the whole system down. If you are using PG 9.5, have a look at INSERT ... ON CONFLICT DO
. In all versions you can also use less drastic locking strategies such as SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
(which is already very strict without locking the entire table) or advisory locks (much less invasive). Also note that concurrency conflicts do not occur when inserting records with keys that are guaranteed to be unique, such as those generated by a sequence.
Upvotes: 4