Update Table

The Airport extension allows tables that is manages to be updated. The Arrow Flight server is responsible for storing and persisting the updated data if it chooses to do so.

Example

A standard SQL UPDATE statement is used:

-- Attach an Airport database
ATTACH 'example' (TYPE AIRPORT, location 'grpc://localhost:50312/');

-- Assume that there is a `main` schema
-- already in the `example` database
CREATE TABLE example.main.employees (
  name varchar,
  id integer
);

-- Update a single row
UPDATE example.main.employees set id = 5 where id = 1;

Caution

For an Airport managed table to perform an UPDATE operation it must have a rowid pseudocolumn.

Tip

Airport-managed tables lack the transactional guarantees of native DuckDB tables. When using the INSERT statement, all rows are sent to the Arrow Flight server where they are presumed to be immediately commited to storage. This differs from a standard SQL transation that doesn’t commit data until COMMIT or ROLLBACK is issued.

The current philosophy of the Airport extension is to send the rows to the server. If the server fails to commit the rows during the RPC request, it will raise an Arrow Flight exception, causing the DuckDB transaction to abort.

This may change in the future.

Arrow Flight Server Implementation Notes

If the table does has a rowid pseudocolumn the update can be performed.

A DoExchange Arrow Flight RPC is made to update rows. This allows the server to return rows (needed for the RETURNING clause), the rows may differ from the original input supplied. On the write stream of the DoExchange call only the rowid pseudocolumn will be sent to the server along with all of the new column values.

Supplied gRPC Headers for DoExchange request.

Header Name	Description
airport-operation	Set to update to indicate the operation being performed
return-chunks	Set to 1 if a RETURNING clause is present; otherwise, 0

Final Metadata Message

When the DuckDB client finishes writing to the write stream of the DoExchange RPC, the server is expected to return a single metadata message on the read stream that is serialized using msgpackwith this structure.

struct AirportUpdateFinalMetadata
{
  uint64_t total_updated;
  MSGPACK_DEFINE_MAP(total_updated)
};

This message informs the client of the total number of rows successfully updated from the table.