I can’t find something related to the transactions in the official documentation. The insert is rolledback well but the drop table is ignored.
Below is a simple example. After calling the transaction, the table is deleted forever.
CREATE PROCEDURE test_transaction() AS
drop table test;