Re: PostgreSQL Developer Best Practices

David,

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(at)gmx(dot)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.
>
> Thomas
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.