Expand on resolutions to transaction is too large to complete; try splitting into pieces error

[awoods187]

Andy Woods (awoods187) commented:

Describe the problem

A customer recently received the error transaction is too large to complete; try splitting into pieces when attempting to use transactions to delete information.

We only reference this error in Build a Python App (https://www.cockroachlabs.com/docs/v2.1/build-a-python-app-with-cockroachdb-sqlalchemy.html#break-up-large-transactions-into-smaller-units-of-work). In this case, the error wasn't resolved with the advice contained due to the specific customer steps.

For large deletes, the customer uses two transactions: first fetches session id's from select and it is passed in the delete in the 2nd.

We resolved this problem by adding FROM add AS OF SYSTEM TIME '-1h' in the Select ( https://www.cockroachlabs.com/docs/stable/as-of-system-time.html)

This allows users to reduce contention since the deletes do not need to occur in realtime.

We should expand out our documentation on this error and this solution.

Also worth noting that Follower Reads is a superior solution to the AOST.

Jira Issue: DOC-246

[jseldess]

Thanks, @awoods187! Sounds like we need to at least both expand the sql alchemy guidance and add this error to https://www.cockroachlabs.com/docs/stable/common-errors.html.

[ericharmeling]

FWIW, we've improved our DELETE docs to cover large deletes with an AOST query: https://www.cockroachlabs.com/docs/stable/delete.html#batch-delete-on-a-non-indexed-column.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB docs!