0

In fact, the PostgreSQL documentation states that all interactions with the database are performed within a transaction.

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

Given this, even something like SELECT name FROM users will initiate a COMMIT. Is there a way to avoid this? In other words—is there a means of looking at the data in a table without issuing a COMMIT or a ROLLBACK? In the case of statements whose sole purpose is to fetch some data, it seems like superfluous overhead.

I read this and recognize that having SELECT statements be within a transaction is important; it allows one to take a snapshot of the data and thus remain consistent about what rows are where and what data they contain—but, then, is there a way to end a transaction without the overhead of COMMIT or ROLLBACK (in the case where neither is actually necessary)?

AmagicalFishy
  • 1,249
  • 1
  • 12
  • 36
  • 1
    Not if the database supports [ACID](https://en.wikipedia.org/wiki/ACID) as Postgres does. – Adrian Klaver Jan 18 '23 at 22:14
  • @AdrianKlaver Why not? I'm sure this question is born of ignorance—but why is looking at the DB (not changing it in any way) and *not* running `COMMIT` or `ROLLBACK` mutually exclusive w/ supporting ACID? – AmagicalFishy Jan 18 '23 at 22:21
  • Because `SELECT` can change the database if you do something like `select func_delete_items(where_expired_true)`. Also read the link for how transactions are used to maintain the `A` and `I`. – Adrian Klaver Jan 18 '23 at 22:28
  • @AdrianKlaver I understand that `SELECT` *can* change the database—but I'm talking, explicitly, about scenarios where it doesn't. Also, I read the link (I wouldn't have posted it otherwise!), but it isn't clear to me why we need a `COMMIT` or a `ROLLBACK` in any of the cases where a snapshot of the DB is required. Why does a transaction w/ only a `SELECT` query that *doesn't* change the DB necessitate a `COMMIT` or `ROLLBACK`? I don't understand why taking a snapshot of the DB requires a `COMMIT` to said DB at the end of the transaction. – AmagicalFishy Jan 18 '23 at 22:38
  • 1) To know whether a scenario 'needed it' or not would require the server to walk through the code and determine what it is doing. That rises to the AI level quickly. It is simpler and less overhead to just wrap it in a transaction. 2) Because the `I`(snapshot) is part and parcel of a transaction. See [Concurrency](https://www.postgresql.org/docs/current/mvcc.html) in particular [Transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html). Bottom line that is the way it is. – Adrian Klaver Jan 18 '23 at 22:44
  • @AdrianKlaver ... lol, it wouldn't require an AI. It'd just require the programmer to know whether or not the DB is being changed (e.g., `SELECT name FROM user`) and some method that explicitly omits the otherwise automatic `COMMIT` or `ROLLBACK` (which is what I'm asking about). I appreciate the links, but none of it is new information nor does it answer my question I'm afraid "that is the way it is" doesn't really give me much in terms of reasoning/an answer. Maybe it's true, but I have a hard time believing such a popular database standard would have superfluous `COMMIT`s and `ROLLBACK`s – AmagicalFishy Jan 18 '23 at 22:57
  • Again, maybe it *is* the case that PostgreSQL just has a bunch of commits that don't actually need to be there because "that's the way it is," but I'm skeptical of that; it seems like too blatant of an oversight to not have some reason. – AmagicalFishy Jan 18 '23 at 23:00
  • Most clients have `autocommit` so you don't have to explicitly issue a `BEGIN/COMMIT(ROLLBACK)`, but there is still a transaction happening for the reasons stated. ACID compliance needs to 'prepare for the worst' and not 'expect the best', in other words expect the programmer knows what they are doing. – Adrian Klaver Jan 18 '23 at 23:00

1 Answers1

1

I recognize that having SELECT statements be within a transaction is important; it allows one to take a snapshot of the data and thus remain consistent

Good.

but, then, is there a way to end a transaction without the overhead of COMMIT or ROLLBACK?

Committing a transaction that did only read data does not have any overhead. All you need to do is drop the transaction handle and the resources allocated for it.

The "implicit COMMIT" just means that the transaction is getting closed/exited/completed - with or without actually writing anything. You cannot have the transaction without ending it.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375