HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Are semicolon-delimited Postgres commands run serial or parallel?

Submitted by: @import:stackexchange-dba··
0
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.

Context

StackExchange Database Administrators Q#117292, answer score: 2

Revisions (0)

No revisions yet.