1

Does anyone know the reason, yugabyte-specific or otherwise, that I cannot alternate between truncating and inserting within the same transaction?

These steps:

  1. Truncate a table.
  2. Insert a row into that table.
  3. Truncate again.
  4. Insert another row into the table.

Result in this error on the final step:

ERROR: Operation failed. Try again.: Unknown transaction, could be recently aborted: e415ae05-0d46-42f5-b18d-f27b344b5642 (SQLSTATE 40001)

[Disclaimer]: This question was first asked on the YugabyteDB Community Slack channel.

MarkoR
  • 115
  • 8

1 Answers1

1

In YugabyteDB, currently 'truncate' is not transactional. The recommendation would be to avoid using:

a) truncate inside of a multi-step transaction

OR

b) running truncate concurrently with our read/write operations on the same table.

To my knowledge other distributed SQL databases also either:

a) do not support truncate (like Google Cloud Spanner). See Does Cloud Spanner support a TRUNCATE TABLE command?

OR,

b) they support truncate, but not in transactional manner.

We do plan to restrict this restriction in future. In the near term perhaps

delete from T;

can be used as a workaround; this is a bit heavier weight than using truncate, but will be transactional.

Kannan Muthukkaruppan
  • 560
  • 2
  • 6