Thread: psql connection option: statement_timeout

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.

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.

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.

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.

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.

Thanks Scott.

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.

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

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

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
theconnection 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