psql connection option: statement_timeout

On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd <craig@mysoftforge.com> wrote:

Hello All,

I am something of a newbie and I am trying to understand how to pass
connection options using the psql client. My understanding is that it is
possible to do this as part of the psql connection event.
I am on Mint and my PostgreSQL Server version = 9.3.13.

I am trying to connect to an instance on a different machine (also 9.3.13).
The following works:
psql -U username -h 192.x.x.x <enter>

But when I try to set the statement like this it fails:
psql -U username -h 192.x.x.x statement_timeout=1000

I get the following "invalid connection option" I am less concerned with
actually setting this parameter than I am learning how to pass or set
connection options when I log in. If it is a case where this particular
option cannot be set as part of the connection string that is fine. But
that leads me to ask what options can I set as part of the connection
string?
I have looked here:
https://www.postgresql.org/docs/9.3/static/app-psql.html
and here:
https://www.postgresql.org/docs/9.3/static/runtime-config-client.html

I suspect I am close, but I can't seem to figure out where I am going awry.
Any thoughts?

Thanks in advance.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I suspect what you want is connect_timeout

ie: psql -U username -h 192.x.x.x connect_timeout=1000
see examples at https://www.postgresql.org/docs/9.3/static/app-psql.html

However, if you truly want to set statement_timeout, that cannot be set at
the command line. You must execute that AFTER you connect.
ie: # SET statement_timeout = 1000;

You can also place multiple commands inside a file and then execute that
after you connect.
eg: # \i your_filename.sql
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 07/03/2016 06:15 PM, Melvin Davidson wrote:

On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd <craig@mysoftforge.com
<mailto:craig@mysoftforge.com>> wrote:

Hello All,

I am something of a newbie and I am trying to understand how to
pass connection options using the psql client. My understanding
is that it is possible to do this as part of the psql connection
event.
I am on Mint and my PostgreSQL Server version = 9.3.13.

I am trying to connect to an instance on a different machine (also
9.3.13).
The following works:
psql -U username -h 192.x.x.x <enter>

But when I try to set the statement like this it fails:
psql -U username -h 192.x.x.x statement_timeout=1000

I get the following "invalid connection option" I am less
concerned with actually setting this parameter than I am learning
how to pass or set connection options when I log in. If it is a
case where this particular option cannot be set as part of the
connection string that is fine. But that leads me to ask what
options can I set as part of the connection string?
I have looked here:
https://www.postgresql.org/docs/9.3/static/app-psql.html
and here:
https://www.postgresql.org/docs/9.3/static/runtime-config-client.html

I suspect I am close, but I can't seem to figure out where I am
going awry.
Any thoughts?

Thanks in advance.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I suspect what you want is connect_timeout

ie: psql -U username -h 192.x.x.x connect_timeout=1000
see examples at https://www.postgresql.org/docs/9.3/static/app-psql.html

However, if you truly want to set statement_timeout, that cannot be
set at the command line. You must execute that AFTER you connect.
ie: # SET statement_timeout = 1000;

You can also place multiple commands inside a file and then execute
that after you connect.
eg: # \i your_filename.sql
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Hi Melvin,

Thanks for the quick response.
That worked so thanks for that! :)

Part of what I am trying to do is understand the delineation between
those options I have at connect time as part of the connection string
versus those that should be scripted as you suggest.
The documentation, from what I can tell, kind of leaves it up to us to
figure out when something works during the connection event or not. Or
am I missing something?
So to put it another way: is there a list that shows what options are
available during the connection event or as part of the connection string?

Thanks.

On Sun, Jul 3, 2016 at 5:15 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd <craig@mysoftforge.com> wrote:

Hello All,

I am something of a newbie and I am trying to understand how to pass
connection options using the psql client. My understanding is that it is
possible to do this as part of the psql connection event.
I am on Mint and my PostgreSQL Server version = 9.3.13.

I am trying to connect to an instance on a different machine (also
9.3.13).
The following works:
psql -U username -h 192.x.x.x <enter>

But when I try to set the statement like this it fails:
psql -U username -h 192.x.x.x statement_timeout=1000

I get the following "invalid connection option" I am less concerned with
actually setting this parameter than I am learning how to pass or set
connection options when I log in. If it is a case where this particular
option cannot be set as part of the connection string that is fine. But
that leads me to ask what options can I set as part of the connection
string?
I have looked here:
https://www.postgresql.org/docs/9.3/static/app-psql.html
and here:
https://www.postgresql.org/docs/9.3/static/runtime-config-client.html

I suspect I am close, but I can't seem to figure out where I am going
awry.
Any thoughts?

Thanks in advance.I suspect what you want is connect_timeout

ie: psql -U username -h 192.x.x.x connect_timeout=1000
see examples at https://www.postgresql.org/docs/9.3/static/app-psql.html

However, if you truly want to set statement_timeout, that cannot be set at
the command line. You must execute that AFTER you connect.
ie: # SET statement_timeout = 1000;

You can also place multiple commands inside a file and then execute that
after you connect.
eg: # \i your_filename.sql

Also you can set such things as statement_timeout in the postgresql.conf,
or set them by database or by user / role.

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

Note that the connect_timeout is a connection time setting, for how long to
attempt a connection to be made, not for how long to hold it while idle.

correction:

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

should read

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to run a statement.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

correction:

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

should read

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to run a statement.

Part of what I am trying to do is understand the delineation between those

options I have at connect time as part of the connection string versus
those that should be scripted...

It is fairly simple. The options you have at connect time are documented
here:
https://www.postgresql.org/docs/9.3/static/app-psql.html

All other commands/statements must be executed after connect, specified
with the -c option or included in a file with -f .
Note the exceptions that can be specified per user as Scott mentioned.

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

On 07/03/2016 06:51 PM, Scott Marlowe wrote:

On Sun, Jul 3, 2016 at 5:15 PM, Melvin Davidson <melvin6925@gmail.com
<mailto:melvin6925@gmail.com>> wrote:

On Sun, Jul 3, 2016 at 6:54 PM, Craig Boyd <craig@mysoftforge.com
<mailto:craig@mysoftforge.com>> wrote:

Hello All,

I am something of a newbie and I am trying to understand how
to pass connection options using the psql client. My
understanding is that it is possible to do this as part of the
psql connection event.
I am on Mint and my PostgreSQL Server version = 9.3.13.

I am trying to connect to an instance on a different machine
(also 9.3.13).
The following works:
psql -U username -h 192.x.x.x <enter>

But when I try to set the statement like this it fails:
psql -U username -h 192.x.x.x statement_timeout=1000

I get the following "invalid connection option" I am less
concerned with actually setting this parameter than I am
learning how to pass or set connection options when I log in.
If it is a case where this particular option cannot be set as
part of the connection string that is fine. But that leads me
to ask what options can I set as part of the connection string?
I have looked here:
https://www.postgresql.org/docs/9.3/static/app-psql.html
and here:
https://www.postgresql.org/docs/9.3/static/runtime-config-client.html

I suspect I am close, but I can't seem to figure out where I
am going awry.
Any thoughts?

Thanks in advance.I suspect what you want is connect_timeout

ie: psql -U username -h 192.x.x.x connect_timeout=1000
see examples at
https://www.postgresql.org/docs/9.3/static/app-psql.html

However, if you truly want to set statement_timeout, that cannot
be set at the command line. You must execute that AFTER you connect.
ie: # SET statement_timeout = 1000;

You can also place multiple commands inside a file and then
execute that after you connect.
eg: # \i your_filename.sql

Also you can set such things as statement_timeout in the
postgresql.conf, or set them by database or by user / role.

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

Note that the connect_timeout is a connection time setting, for how
long to attempt a connection to be made, not for how long to hold it
while idle.

Thanks Scott.

On 07/03/2016 07:00 PM, Melvin Davidson wrote:

On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe <scott.marlowe@gmail.com
<mailto:scott.marlowe@gmail.com>> wrote:

correction:

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

should read

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to run a statement.

Part of what I am trying to do is understand the delineation between

those options I have at connect time as part of the connection string
versus those that should be scripted...

It is fairly simple. The options you have at connect time are
documented here:
https://www.postgresql.org/docs/9.3/static/app-psql.html

All other commands/statements must be executed after connect,
specified with the -c option or included in a file with -f .
Note the exceptions that can be specified per user as Scott mentioned.

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

Hi Melvin,

OK...I can see that for some of the web page that you linked to, but not
all. Which is fine.
Part of my confusion stems from the name of this page:
https://www.postgresql.org/docs/current/static/runtime-config-client.html ~
18.11. Client Connection Defaults
This leads me to believe that the options specified on this page can be
altered at connect time. Based on what you are telling me and my own
failures to connect using options on this page then I have to assume
that these are options that can be changed post login.
Thanks for everyone's help. This makes a bit more sense. :)

Sincerely,

Craig

Craig Boyd wrote:

So to put it another way: is there a list that shows what options are
available during the connection event or as part of the connection string?

Yes, but it belongs to the chapter on libpq. The psql docpage merely points
to it:

<quote>
-d dbname
--dbname=dbname

Specifies the name of the database to connect to. This is
equivalent to specifying dbname as the first non-option argument
on the command line.

If this parameter contains an = sign or starts with a valid URI
prefix (postgresql:// or postgres://), it is treated as a
conninfo string. See Section 31.1.1 for more information.
</quote>

In the HTML-formatted doc, this "Section 31.1.1" links to:
https://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING
which has the list you want.

Besides connection options such as "connect_timout", it happens
that server config options, such as "statement_timeout", can also
be incorporated into a connection string, through the
"options" keyword and -c switch (possibly used multiple times)

For example:

$ psql -d "dbname=test connect_timeout=10 options='-c statement_timeout=1000
-c geqo=off'"

psql (9.3.13)
Type "help" for help.

test=> show statement_timeout ;
statement_timeout
-------------------
1s
(1 row)

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/04/2016 11:01 AM, Daniel Verite wrote:

Craig Boyd wrote:

So to put it another way: is there a list that shows what options are
available during the connection event or as part of the connection string?

Yes, but it belongs to the chapter on libpq. The psql docpage merely points
to it:

<quote>
-d dbname
--dbname=dbname

Specifies the name of the database to connect to. This is
equivalent to specifying dbname as the first non-option argument
on the command line.

If this parameter contains an = sign or starts with a valid URI
prefix (postgresql:// or postgres://), it is treated as a
conninfo string. See Section 31.1.1 for more information.
</quote>

In the HTML-formatted doc, this "Section 31.1.1" links to:
https://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING
which has the list you want.

Besides connection options such as "connect_timout", it happens
that server config options, such as "statement_timeout", can also
be incorporated into a connection string, through the
"options" keyword and -c switch (possibly used multiple times)

For example:

$ psql -d "dbname=test connect_timeout=10 options='-c statement_timeout=1000
-c geqo=off'"

psql (9.3.13)
Type "help" for help.

test=> show statement_timeout ;
statement_timeout
-------------------
1s
(1 row)

Best regards,

Hi Daniel,

Thank you VERY much for helping to clear this up for me. This makes a
lot more sense now and is exactly what I was looking for.
I was able to test you sample statement and it was nearly spot on. I
just had to add the "hostaddr" and "user" options and it worked like a
champ.

Sincerely,

Craig Boyd

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson <melvin6925@gmail.com> writes:

On Sun, Jul 3, 2016 at 7:52 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

correction:

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to connect.

should read

alter user reporting set statement_timemout=60 is handy for users that
should never take a long time to run a statement.

Part of what I am trying to do is understand the delineation between those options I have at connect time as part of the connection string versus those that should be

scripted...
 
It is fairly simple. The options you have at connect time are documented here:
https://www.postgresql.org/docs/9.3/static/app-psql.html

All other commands/statements must be executed after connect, specified with the -c option or included in a file with -f .
Note the exceptions that can be specified per user as Scott mentioned.

No one has mentioned the PGOPTIONS variable here yet ?...

FWIW

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

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general