Thank you. This is exactly the type of feedback I was looking for. To answer your question, for now it is a guide for one particular client, however, if I get enough feedback and contributions, I will revise it and submit to the PostgreSQL community.
On Mon, Aug 24, 2015 at 2:34 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
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.