By Rouz


2018-05-22 09:20:58 8 Comments

I am by no means a database expert so I might be asking something dumb.

So I am using PostgreSQL and I want to do the insert to one table, keep the ID (ID is incrementing automatically) and insert a tuple to another table (tuple has that ID in it).

Obviously I want to keep things atomic.

Query:

WITH inserted AS (
  INSERT INTO
    "public"."user" (email, first_name, last_name, username)
  VALUES
    (?, ?, ?, ?)
  RETURNING id
)
INSERT INTO 
  "public"."security" (user_id, security)
VALUES((
  SELECT id FROM inserted
), ?);

Question is, can this even work? What will happen if the second insert fails? Will the first remain persisted?

The obvious answer is that I might put this into transaction and commit/rollback but will this be sufficient?

1 comments

@a_horse_with_no_name 2018-05-22 12:20:26

A single statement is always run atomically.

Quote from the manual

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block

A set of common table expressions defined under the same WITH clause, however many of them (including writeable CTEs), is still part of a single statement.

So the statement either fails completely or succeeds completely.

You only need (explicit) transactions if you want to run atomically more than one statement.

The handling of generated sequence values is no different than in any other situation: a nextval operation is never rolled back

Related Questions

Sponsored Content

1 Answered Questions

MySQL Partial Transactions

1 Answered Questions

[SOLVED] Key-range RangeI-N lock compatibility in SQL Server

1 Answered Questions

[SOLVED] What will happen with MySQL transaction if one of the queries fails?

2 Answered Questions

[SOLVED] What happens to a transaction if the network connection fails?

1 Answered Questions

[SOLVED] What happens as a database grows to the HDD's lmit?

2 Answered Questions

[SOLVED] What happens in PostgreSQL checkpoint?

  • 2014-03-26 17:17:19
  • Konrad Garus
  • 15215 View
  • 22 Score
  • 2 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] How does MS SQL Server authenticate MSDTC connections when using SSPI?

1 Answered Questions

[SOLVED] SQL Server: affect other transactions?

1 Answered Questions

[SOLVED] What happens when checkpoint_segments reached?

Sponsored Content