HTTP Endpoint: Multiple Statements in Transaction possible?

CrateDB
1

Hi all,

is it possible to execute multiple statements (say, an ALTER and an UPDATE statement) using the HTTP endpoint in a single transaction?

2

Hi David,
CrateDB does not have transactions as such, but you can send multiple statements and we also have ways to do many things in a single statement, like adding a column and populating it with a value (with dynamic column policy).
Happy to help if you want to elaborate on what you were trying to do.

3

Thanks for your response :slight_smile:

I want to achieve the following:

  1. Modify the table structure by adding a nullable column
  2. Update all existing rows (that now have a NULL value in the new column) with a static value. Note that this might take a few seconds because of the table size.

The challenge here is that this is a multithreaded scenario, where some clients might already try to insert data after step 1, while step 2 is still running. This data could potentially contain intended NULL values for the new column.

Ideally, the static value inserted in step 2 should only be applied to the pre-existing rows, not the new rows inserted after step two or - more importantly - concurrently while step 2 is executed.

In addition, it would be good to either have the ALTER and the UPDATE operation completed, or if either one fails, none of them (in other words, wrap the two operations in some kind of transaction).

I would be very interested to learn about the dynamic column policy you mentioned, or any other mechanisms to achieve this. Thank you in advance :folded_hands:

4

You may notice that in addition to ADD COLUMN CrateDB’s ALTER TABLE also supports RENAME COLUMN and DROP COLUMN. I am thinking that the easiest way to achieve what you want to do is to add the new column with a temporary name, then do the UPDATE and once that completes successfully we can rename the column to the intended name. If anything does not look right we can drop the column instead.

1 Like