Insert

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

Support for `RETURNING`

The Airport extension supports the RETURNING clause in INSERT statements.

From the DuckDB documentation:

The RETURNING clause may be used to return the contents of the rows that were inserted. This can be useful if some columns are calculated upon insert. For example, if the table contains an automatically incrementing primary key, then the RETURNING clause will include the automatically created primary key. This is also useful in the case of generated columns.

Example

A standard SQL INSERT 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
);

-- Insert a single row.
INSERT INTO example.main.employees values ('Rusty', 1);

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

A DoExchange Arrow Flight RPC is made to insert rows. This allows the server to return rows (needed for the RETURNING clause), the rows may differ from the original input supplied.

Supplied gRPC Headers for `DoExchange` request.

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

Schema Handling

The schema sent by the client must match the schema of the table.
The schema returned by the server should also match the table’s schema.

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 AirportInsertFinalMetadata
{
  uint64_t total_inserted;
  MSGPACK_DEFINE_MAP(total_inserted)
};

This message informs the client of the total number of rows successfully inserted to the table.

Support for RETURNING