Things affected globally in a postgresql transaction

Question

Correct me if I am using the correct term: "affected globally" I mean, if I increase the sequence value, it will be increased for all other running transactions without the need for commit....

I know that sequences have this behavior, but my doubt is there is a list with some other roles that are affected globally, if i update it inside a transaction?

For example, if I set alter table mytable enable trigger all; this will affect the trigger behavior only in the local transaction or in other transactions, note that this is only a example, i am thinking about a lot of other things that i am need to be carefully about it.

Answer 1

alter table mytable enable trigger all; cannot affect concurrent transactions directly because the command takes an exclusive lock on the table before the change is applied. And the lock stays until the end of the transaction. (Of course, the lock itself can have indirect effects on concurrent transactions.)

Some effects that are visible to concurrent transactions before COMMIT (or even after ROLLBACK):

• Locks, obviously (this is how other conflicts are avoided).
• Sequences (like you already mentioned).
• Entries in unique indexes (conflict with dupes even when still uncommitted).
• Data written to log files (or anything else written to files).
• Effects of dblink calls (emulating autonomous transactions, effectively).
• Changes to prepared statements (only matters in case of a ROLLBACK, since prepared statements are only visible within the same session).
• Long running transactions also can keep VACUUM from doing it's job, since rows still potentially visible in the snapshot cannot be removed. Can even lead to transaction ID wraparound in very extreme cases. (Well, other security measures kick in before that actually happens.)

I am sure there is more ...

Some commands cannot be executed inside a transaction at all. Like CREATE DATABASE, CREATE INDEX CONCURRENTLY or VACUUM.

Related:

• Do stored procedures run in database transaction in Postgres?
• How to use RETURNING with ON CONFLICT in PostgreSQL?
• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?