Why we spent the last month eliminating Postgres substransactions (about.gitlab.com)

I read the article and have the following two comments:

1) The troubleshooting and graphs are very nice.

2) The savepoint logic is, and was, absolute trash.

The correct logic if you're doing an insert in SQL is to try the insert and trap the status, not whatever was shown in that post (SELECT before INSERT is a race condition.) It appears an application programmer is confusing application state transactions with simple SQL logic, or there's some obfuscation being caused by RoR.

I would never +1 code like that.

IOW, you 100% deserved the intermittent system degradation.

Source: DBA.

Excellently written article!

The motivating example for this seems to be completely broken:

    --- Start a transaction
    BEGIN
    SAVEPOINT active_record_1
    --- Look up the account
    SELECT * FROM credit_accounts WHERE customer_id = 1
    --- Insert the account; this may fail due to a duplicate constraint
    INSERT INTO credit_accounts (customer_id) VALUES (1)
    --- Abort this by rolling back
    ROLLBACK TO active_record_1
    --- Retry here: Start a new subtransaction
    SAVEPOINT active_record_2
    --- Find the newly-created account
    SELECT * FROM credit_accounts WHERE customer_id = 1
    --- Save the data
    RELEASE SAVEPOINT active_record_2
    COMMIT

The second SELECT will never see the newly added row because it's still operating against the same snapshot as the outer transaction, so the use of subtransactions is completely pointless?