Re: PostgreSQL Developer Best Practices

Melvin Davidson schrieb am 22.08.2015 um 17:15:
> I've attached a file with a few starters that although are numbered,
> are in no special order.

> 2. End ALL queries with a semi-colon (;)
> EG: SELECT some_column FROM a_table;
>
> Although autocommit is on by default, it is always a good idea to signal the query processor that a statement is complete with the semicolon.
> Failure to do so could result in <IDLE IN TRANSACTION>, which will
> hold locks on the tables involved and prevent other queries from being processed.

Terminating a statement with ; has nothing to do with "<idle in transaction>" connections.
It is a mere syntax thing to make the SQL client (e.g. psql) recognize the end of the statement.
If you don't use it, your statement won't be executed in the first place - at least with psql
as it will wait indefinitely until you finish typing the statement. A GUI client might simply send
the wrong statement to the backend.

If you run with autocommit disabled, ending each statement with a semicolon, will not prevent your connection
from getting into that "<idle in transaction>" state. You have to end the _transaction_ using commit or
rollback to avoid that.

I do agree with the "end all queries with a semi-colon" rule, but the explanation is wrong.

You should have another rule that says:

End all transactions as soon as possible using commit or rollback.

Thomas