26.3. Snapshot-Based Isolation and Atomicity #
Postgres Pro AXE uses snapshots for isolating stored procedures executed on pgpro_metastore objects. Snapshots are monotonically increasing numbers that determine object visibility. Each object has begin_snapshot and end_snapshot fields in the corresponding metadata table. The begin_snapshot field is set when the object is created, and the end_snapshot field is set when the object is deleted.
When a stored procedure starts executing, it obtains the maximum snapshot number from the metadata catalog. This number determines which pgpro_metastore objects are visible to the procedure: an object is visible if its begin_snapshot field is less than or equal to this number, and its end_snapshot field is not set.
You can also read metadata using a specific snapshot number. To determine which pgpro_metastore objects are visible for a snapshot, ensure that the begin_snapshot field of the object is less than or equal to the snapshot number, and the end_snapshot field of the object is greater than the snapshot number or is not set.
Example 26.5. Retrieving Parquet Files Using a Specific Snapshot Number
SELECT data.path AS data_file_path
FROM axe_catalog.pga_data_file AS data
WHERE
data.table_id = 42 AND
15 >= data.begin_snapshot AND
(15 < data.end_snapshot OR data.end_snapshot IS NULL)
ORDER BY file_order;
Unlike Postgres Pro snapshots, Postgres Pro AXE snapshots are not limited by transactions, which allows stored procedures to continue executing across restarts of the server with the metadata catalog, provided that this server is separate from the Postgres Pro AXE server. Executing stored procedures in a Postgres Pro transaction block is not recommended because each stored procedure commits updates to the metadata catalog using a separate snapshot. If one procedure fails, the updates from previous procedures remain in the catalog and are not reverted.
Isolation guarantees differ depending on the type of operation:
Reading from analytical tables.
Standard Postgres Pro views are used for reading from analytical tables. Currently, views return the latest version of the data from Parquet files in the storage, regardless of the Postgres Pro transaction isolation level, including Repeatable Read and higher. Views are not limited to specific Postgres Pro AXE snapshots, and isolation is ensured by storage mechanisms.
Executing stored procedures.
Stored procedures commit metadata updates in a single serializable transaction, which resolves most conflicts when two procedures modify the same pgpro_metastore objects. If this is not enough, additional trigger logic that considers Postgres Pro AXE snapshots resolves the remaining conflicts.
Stored procedures also support the atomicity of metadata updates. If a procedure is canceled, Postgres Pro AXE deletes all intermediate data from the storage and does not commit metadata updates. To enable recovery after a server failure or restart, Postgres Pro AXE logs the intermediate data using service transactions. You can delete the intermediate data left by canceled procedures using the metastore.cleanup stored procedure.