1

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.

2

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:

  • fior unique constraints there;s a lock created, not an immediate conflict (one of the transactions stalls) – Jasen Oct 24 '18 at 10:07
  • So how postgresql, knows that sequences needs to be affected globally or for all running transactions? – Luciano Andress Martini Oct 24 '18 at 12:25
  • @LucianoAndressMartini: nextval() and some other aspects of sequence-handling are not transactional. Effects are never rolled back. So always "global". – Erwin Brandstetter Oct 24 '18 at 12:29
  • +1 So the correct term was not global... but 'not transactional', this is very nice! thank you very much for this information, what more are not transactional in postgresql ? – Luciano Andress Martini Oct 24 '18 at 12:35
  • @LucianoAndressMartini: The term "not transactional" does not cover everything. There are various kinds of exceptions from usual transactional behavior. But those are rare exceptions. Most everything is transactional in Postgres, even DDL commands. I don't have an exhaustive list. Follow the links above. I added one more. – Erwin Brandstetter Oct 24 '18 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.