Re: psql connection option: statement_timeout

Craig Boyd <craig@xxxxxxxxxxxxxxx> · Sun, 3 Jul 2016 20:16:37 -0500



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

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

            
                      On Sun, Jul 3, 2016 at
                        6:54 PM, Craig Boyd <craig@xxxxxxxxxxxxxxx>
                        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.