CODEWITHSUNDEEP
postgresql
AmagicalFishy
AmagicalFishy

Reputation: 1389

Is there a way to SELECT and read from the table such that no COMMIT or ROLLBACK is necessary?

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)?

Upvotes: 0

Views: 547

Answers (1)

Bergi
Bergi

Reputation: 664650

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.

Upvotes: 1

Related Questions