Suppose I have this application pseudo code (it's actually PHP and communicating with PG with the pg_* functions):
function handle_update_of_foo_table()
{
BEGIN;
TRUNCATE TABLE foo;
loop (thousands of times)
{
INSERT INTO foo blablabla...;
if (INSERT FAILED)
{
ROLLBACK;
return false;
}
}
COMMIT;
return true;
}
handle_update_of_foo_table();
UPDATE unrelated_table SET blablabla;
Do I understand things right if I say that, had I not actively remembered to do the "ROLLBACK" before returning, when an INSERT failed for whatever reason, the transaction would have "continued on" to perform the unrelated UPDATE query inside the transaction, and thus, when the script ends right afterwards, it would have auto-rolled back because there had been a BEGIN but no COMMIT, so as far as PG is aware, all of my queries, including the unrelated UPDATE in the end, are part of the same transaction? And thus, the unrelated_table will not be updated in the end, because the entire transaction was auto-rolled back due to not COMMIT?
With me forgetting the ROLLBACK (which I did not forget in the example code, but might very easily do in reality somewhere), it has no way to know what my intentions were since it just sees a series of queries inside an explicitly started "transaction"?
If I have understood this, I think I finally "get" transactions. It only took me twenty years...