Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Rollback on constraint failure #1071

Merged
merged 2 commits into from Sep 2, 2022

Conversation

joshbuddy
Copy link
Contributor

@joshbuddy joshbuddy commented Aug 5, 2022

This fixes an issue where deferred constraints can leave the transaction in an inconsistent state. In the event of a failed deferred constraint, we need to issue a rollback in order to make a transaction startable later.

@joshbuddy joshbuddy marked this pull request as draft August 5, 2022 15:11
@joshbuddy joshbuddy force-pushed the rollback-on-constraint-failure branch from 6b59a48 to 7b40448 Compare August 8, 2022 13:41
@joshbuddy joshbuddy marked this pull request as ready for review August 8, 2022 13:42
@mattn
Copy link
Owner

mattn commented Aug 18, 2022

@rittneje Do you have an opinion?

@rittneje
Copy link
Collaborator

Given that database/sql will not allow a transaction to be reused once you call Commit() (even if it fails), should we just issue a ROLLBACK on error, regardless of which specific error it was? The SQLite docs say this:

If the transaction has already been rolled back automatically by the error response, then the ROLLBACK command will fail with an error, but no harm is caused by this.

@joshbuddy
Copy link
Contributor Author

Sounds good to me @rittneje. I've updated the PR and also updated the comment.

sqlite3.go Outdated
// However, database/sql considers the transaction complete once we
// return from Commit() - we must clean up to honour its semantics.
// We don't know if the ROLLBACK is strictly necessary, but according
// to sqlite's docs, there is no hard in calling ROLLBACK unnecessarily.
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Suggested change
// to sqlite's docs, there is no hard in calling ROLLBACK unnecessarily.
// to sqlite's docs, there is no harm in calling ROLLBACK unnecessarily.

@rittneje
Copy link
Collaborator

@joshbuddy LGTM, just one typo.

@joshbuddy joshbuddy force-pushed the rollback-on-constraint-failure branch from 75abef4 to d07c0fc Compare August 28, 2022 20:54
@joshbuddy
Copy link
Contributor Author

@rittneje fixed, thank you

@joshbuddy
Copy link
Contributor Author

Looks like some test flakiness?

@rittneje
Copy link
Collaborator

@joshbuddy I think there may be something wrong with TestQueryer, and due to an oversight in how it got written, it is not reporting the real issue. Can you update that unit test like so?

for rows.Next() {
    ...
}
if err := rows.Err(); err != nil {
    t.Fatal(err)
}
if n != 3 {
    ...
}

@joshbuddy
Copy link
Contributor Author

Hey @rittneje, I fixed the underlying test. If you want me to split it off into a separate PR, let me know.

@joshbuddy joshbuddy force-pushed the rollback-on-constraint-failure branch 2 times, most recently from e2bf557 to 15d5547 Compare August 29, 2022 15:50
@joshbuddy
Copy link
Contributor Author

I've moved the test fixes to #1079

@joshbuddy joshbuddy force-pushed the rollback-on-constraint-failure branch from 15d5547 to 0f9d82e Compare September 1, 2022 15:28
@codecov-commenter
Copy link

Codecov Report

Merging #1071 (0f9d82e) into master (f92b6bb) will not change coverage.
The diff coverage is 100.00%.

@@           Coverage Diff           @@
##           master    #1071   +/-   ##
=======================================
  Coverage   45.96%   45.96%           
=======================================
  Files          11       11           
  Lines        1499     1499           
=======================================
  Hits          689      689           
  Misses        670      670           
  Partials      140      140           
Impacted Files Coverage Δ
sqlite3.go 52.64% <100.00%> (ø)

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

@rittneje rittneje merged commit 4ef63c9 into mattn:master Sep 2, 2022
@joshbuddy joshbuddy deleted the rollback-on-constraint-failure branch September 2, 2022 06:12
semrekkers pushed a commit to semrekkers/go-sqlite3 that referenced this pull request Sep 9, 2022
Always rollback on a commit error
patrickdevivo added a commit to mergestat/mergestat that referenced this pull request Oct 26, 2022
[![Mend
Renovate](https://app.renovatebot.com/images/banner.svg)](https://renovatebot.com)

This PR contains the following updates:

| Package | Type | Update | Change |
|---|---|---|---|
| [github.com/mattn/go-sqlite3](https://togithub.com/mattn/go-sqlite3) |
require | patch | `v1.14.15` -> `v1.14.16` |

---

### Release Notes

<details>
<summary>mattn/go-sqlite3</summary>

###
[`v1.14.16`](https://togithub.com/mattn/go-sqlite3/releases/tag/v1.14.16)

[Compare
Source](https://togithub.com/mattn/go-sqlite3/compare/v1.14.15...v1.14.16)

#### What's Changed

- Add build tag to enable OSTRACE() logging by
[@&#8203;benbjohnson](https://togithub.com/benbjohnson) in
[mattn/go-sqlite3#1067
- TestQueryer: actually check Rows returned by
[@&#8203;ohwgiles](https://togithub.com/ohwgiles) in
[mattn/go-sqlite3#1062
- Update README to fix reference URL by
[@&#8203;shibukawa](https://togithub.com/shibukawa) in
[mattn/go-sqlite3#1082
- Fix test queryer test by
[@&#8203;joshbuddy](https://togithub.com/joshbuddy) in
[mattn/go-sqlite3#1079
- Rollback on constraint failure by
[@&#8203;joshbuddy](https://togithub.com/joshbuddy) in
[mattn/go-sqlite3#1071
- Fix "ennviroment" by
[@&#8203;RewardedIvan](https://togithub.com/RewardedIvan) in
[mattn/go-sqlite3#1077
- こんにちわ is wrong Japanse, Correct word is こんにちは by
[@&#8203;KiYugadgeter](https://togithub.com/KiYugadgeter) in
[mattn/go-sqlite3#1085
- Add test for sqlite_math_functions tag by
[@&#8203;lggruspe](https://togithub.com/lggruspe) in
[mattn/go-sqlite3#1059
- remove unuseful ldflags for windows platform. by
[@&#8203;kkqy](https://togithub.com/kkqy) in
[mattn/go-sqlite3#1084
- Cross Compiling for Mac OS via `musl-cross` by
[@&#8203;jodosha](https://togithub.com/jodosha) in
[mattn/go-sqlite3#1090
- Update README.md to include vtable feature by
[@&#8203;dvas0004](https://togithub.com/dvas0004) in
[mattn/go-sqlite3#1100
- Updating vtable example, "BestIndex" method by
[@&#8203;dvas0004](https://togithub.com/dvas0004) in
[mattn/go-sqlite3#1099
- Update amalgamation code by
[@&#8203;mattn](https://togithub.com/mattn) in
[mattn/go-sqlite3#1104

#### New Contributors

- [@&#8203;ohwgiles](https://togithub.com/ohwgiles) made their first
contribution in
[mattn/go-sqlite3#1062
- [@&#8203;shibukawa](https://togithub.com/shibukawa) made their first
contribution in
[mattn/go-sqlite3#1082
- [@&#8203;joshbuddy](https://togithub.com/joshbuddy) made their first
contribution in
[mattn/go-sqlite3#1079
- [@&#8203;RewardedIvan](https://togithub.com/RewardedIvan) made their
first contribution in
[mattn/go-sqlite3#1077
- [@&#8203;KiYugadgeter](https://togithub.com/KiYugadgeter) made their
first contribution in
[mattn/go-sqlite3#1085
- [@&#8203;lggruspe](https://togithub.com/lggruspe) made their first
contribution in
[mattn/go-sqlite3#1059
- [@&#8203;kkqy](https://togithub.com/kkqy) made their first
contribution in
[mattn/go-sqlite3#1084
- [@&#8203;jodosha](https://togithub.com/jodosha) made their first
contribution in
[mattn/go-sqlite3#1090
- [@&#8203;dvas0004](https://togithub.com/dvas0004) made their first
contribution in
[mattn/go-sqlite3#1100

**Full Changelog**:
mattn/go-sqlite3@v1.14.15...v1.14.16

</details>

---

### Configuration

📅 **Schedule**: Branch creation - At any time (no schedule defined),
Automerge - At any time (no schedule defined).

🚦 **Automerge**: Disabled by config. Please merge this manually once you
are satisfied.

♻ **Rebasing**: Whenever PR becomes conflicted, or you tick the
rebase/retry checkbox.

🔕 **Ignore**: Close this PR and you won't be reminded about this update
again.

---

- [ ] <!-- rebase-check -->If you want to rebase/retry this PR, click
this checkbox.

---

This PR has been generated by [Mend
Renovate](https://www.mend.io/free-developer-tools/renovate/). View
repository job log
[here](https://app.renovatebot.com/dashboard#github/mergestat/mergestat).

<!--renovate-debug:eyJjcmVhdGVkSW5WZXIiOiIzNC4xLjUiLCJ1cGRhdGVkSW5WZXIiOiIzNC4xLjUifQ==-->
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

None yet

4 participants