patternsqlMinor
Are semicolon-delimited Postgres commands run serial or parallel?
Viewed 0 times
delimitedpostgresarecommandssemicolonserialparallelrun
Problem
INSERT INTO ...; UPDATE ...; are these commands run sequentially (to completion) left-to-right, or in parallel? Or "it's not that simple"?TL;DR I'm trying to circumvent 9.4.4's lacking
UPSERT by inserting first if missing, then updating both new and existing after:-- First bulk create any missing tags
INSERT INTO user_tags (user_id, tag_id, score)
SELECT :user_id, t.tag_id, 0
FROM (
SELECT tag_id
FROM post_tags
WHERE post_id=:post_id
EXCEPT
SELECT tag_id
FROM user_tags
WHERE user_id=:user_id
) t;
-- Then increment their score
UPDATE user_tags
SET score = score + 1
WHERE tag_id IN (
SELECT tag_id
FROM post_tags
WHERE post_id=:post_id
)
AND user_id=:user_id;Solution
All SQL commands in a single batch are executed linearly; that is the first statement is executed, then the second is executed after the first completes.
The only way to get multiple statements to run in parallel is to execute each statement on a separate connection.
So, to confirm, the two statements in your batch will always execute linearly, i.e. the insert will run first, then the update will run.
The only way to get multiple statements to run in parallel is to execute each statement on a separate connection.
So, to confirm, the two statements in your batch will always execute linearly, i.e. the insert will run first, then the update will run.
Context
StackExchange Database Administrators Q#117292, answer score: 2
Revisions (0)
No revisions yet.