Recent

Author Topic: PQConnection.Params.Add  (Read 4292 times)

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
PQConnection.Params.Add
« on: September 18, 2017, 08:53:31 pm »
Hello, where can I find explanation of PQConnection.Params.Add?
What parameters can be added? Is there any example?

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: PQConnection.Params.Add
« Reply #1 on: September 19, 2017, 02:15:59 pm »
Code: Pascal  [Select][+][-]
  1. PQConn.Params.Add('port=5432');
  2. PQConn.Open;
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
« Last Edit: September 19, 2017, 02:31:23 pm by goodname »

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: PQConnection.Params.Add
« Reply #2 on: September 27, 2017, 07:41:22 am »

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: PQConnection.Params.Add
« Reply #3 on: September 27, 2017, 12:28:14 pm »

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: PQConnection.Params.Add
« Reply #4 on: October 22, 2017, 10:54:00 pm »
What exactly happens if I set parameter "keepalives" to zero (keepalives=0)?
1. Does it mean that the Postgresql server will never terminate idle sessions?
2. Does it mean that Postgresql server will never terminate Postgresql sessions from closed Lazarus applications?
3. Does it mean that the Postgresql server will never terminate Postgresql sessions from client Lazarus application if network connection is broken?

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: PQConnection.Params.Add
« Reply #5 on: October 24, 2017, 02:30:47 pm »
It is likely that nobody on this forum has used the "keepalives" parameter. A quick look through the PostgreSQL documentation didn't really clarify what would happen. Your either going to have to try it and see what happens, do a more in depth web search, or ask on the PostgreSQL mailing list.  https://www.postgresql.org/list/pgsql-general/

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: PQConnection.Params.Add
« Reply #6 on: June 29, 2023, 10:02:20 am »
I put into the PQConnection's Params property following string:
Code: Pascal  [Select][+][-]
  1. connect_timeout=180
  2. keepalives=1
  3. keepalives_idle=86400
  4. keepalives_interval=10
  5. keepalives_count=100
  6. options=-c statement_timeout=2147483647 -c lock_timeout=2147483647
  7.  
The last row for statement_timeout and lock_timeout raises an error.
I tried then with:
Code: Pascal  [Select][+][-]
  1. connect_timeout=180
  2. keepalives=1
  3. keepalives_idle=86400
  4. keepalives_interval=10
  5. keepalives_count=100
  6. statement_timeout=2147483647
  7. lock_timeout=2147483647
  8.  
But again, these two parameters (statement_timeout and lock_timeout) are not recognized.
Do you know how to add them properly?

I know that I can set it through code in this way:
Code: Pascal  [Select][+][-]
  1. // Set statement timeout
  2. pqconnection1.ExecuteDirect('SET statement_timeout = 2147483647');
  3. // Set lock timeout
  4. pqconnection1.ExecuteDirect('SET lock_timeout = 2147483647');        
  5.  
But I would like to set it through GUI...
« Last Edit: June 29, 2023, 10:44:59 am by tatamata »

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: PQConnection.Params.Add
« Reply #7 on: June 29, 2023, 11:29:24 am »
and if you've actually read the link: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
you would have seen, that both "Parameters" are not connection-Params, but Server-Parameter/System-Variables inside the pg.conf (or whatever it's called)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: PQConnection.Params.Add
« Reply #8 on: June 29, 2023, 11:38:30 am »
What exactly happens if I set parameter "keepalives" to zero (keepalives=0)?
1. Does it mean that the Postgresql server will never terminate idle sessions?
2. Does it mean that Postgresql server will never terminate Postgresql sessions from closed Lazarus applications?
3. Does it mean that the Postgresql server will never terminate Postgresql sessions from client Lazarus application if network connection is broken?

Code: Pascal  [Select][+][-]
  1. keepalives=1
  2. keepalives_idle=86400
  3. keepalives_interval=10
  4. keepalives_count=100

Line 1: activate "keepalives" (is also defaultvalue) on the CLIENT
Line 2: after 86400 seconds (24 hours) of "idle" on that TCP-CLIENT-Connection, send out a "Hey, i'm alive" to the Server
Line 3: if a "I'm alive"-Message has NOT been acknowledged by the Server back to the CLIENT, resend that message after 10 seconds
Line 4: if Line 3 fails a 100 times, that CLIENT-connection to the Server is considered dead

Have a guess on which side of the Client-server equation we are with that.....
« Last Edit: June 29, 2023, 11:44:55 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: PQConnection.Params.Add
« Reply #9 on: June 29, 2023, 01:23:32 pm »
and if you've actually read the link: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
you would have seen, that both "Parameters" are not connection-Params, but Server-Parameter/System-Variables inside the pg.conf (or whatever it's called)
Zvoni, nevrtheless, if I set it this way, through Lazarus client program:
Code: Pascal  [Select][+][-]
  1. // Set statement timeout
  2. pqconnection1.ExecuteDirect('SET statement_timeout = 2147483647');
  3. // Set lock timeout
  4. pqconnection1.ExecuteDirect('SET lock_timeout = 2147483647');
  5.  
what is the scope of validity of this settings? Would it last until the pqconnection is closed? Will it be valid for all queries being executed via this connection?

tatamata

  • Hero Member
  • *****
  • Posts: 787
    • ZMSQL - SQL enhanced in-memory database
Re: PQConnection.Params.Add
« Reply #10 on: June 29, 2023, 01:25:00 pm »
What exactly happens if I set parameter "keepalives" to zero (keepalives=0)?
1. Does it mean that the Postgresql server will never terminate idle sessions?
2. Does it mean that Postgresql server will never terminate Postgresql sessions from closed Lazarus applications?
3. Does it mean that the Postgresql server will never terminate Postgresql sessions from client Lazarus application if network connection is broken?

Code: Pascal  [Select][+][-]
  1. keepalives=1
  2. keepalives_idle=86400
  3. keepalives_interval=10
  4. keepalives_count=100

Line 1: activate "keepalives" (is also defaultvalue) on the CLIENT
Line 2: after 86400 seconds (24 hours) of "idle" on that TCP-CLIENT-Connection, send out a "Hey, i'm alive" to the Server
Line 3: if a "I'm alive"-Message has NOT been acknowledged by the Server back to the CLIENT, resend that message after 10 seconds
Line 4: if Line 3 fails a 100 times, that CLIENT-connection to the Server is considered dead

Have a guess on which side of the Client-server equation we are with that.....
Thanks, this was nice explanation.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: PQConnection.Params.Add
« Reply #11 on: June 29, 2023, 01:36:19 pm »
and if you've actually read the link: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
you would have seen, that both "Parameters" are not connection-Params, but Server-Parameter/System-Variables inside the pg.conf (or whatever it's called)
Zvoni, nevrtheless, if I set it this way, through Lazarus client program:
Code: Pascal  [Select][+][-]
  1. // Set statement timeout
  2. pqconnection1.ExecuteDirect('SET statement_timeout = 2147483647');
  3. // Set lock timeout
  4. pqconnection1.ExecuteDirect('SET lock_timeout = 2147483647');
  5.  
what is the scope of validity of this settings? Would it last until the pqconnection is closed? Will it be valid for all queries being executed via this connection?
As far as i understand it: They would remain that way at least until the next Server-Reboot, since those are System/Server-Variables.
Worst case: They overwrite the values in the pg.conf with those values, and all clients to that server have then to "suffer" those values from then on.
But: I'm not a PG-Expert, but i do know my way around server-based DBMS.

User-Rights/Grants not withstanding of course!
« Last Edit: June 29, 2023, 01:38:09 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018