* * *

Author Topic: Remote Postgresql session breaks down  (Read 415 times)

tatamata

  • Hero Member
  • *****
  • Posts: 633
    • ZMSQL - SQL enhanced in-memory database
Remote Postgresql session breaks down
« on: February 27, 2017, 04:15:12 pm »
I have a remote Postgresql 9.6 database server.
Connection is successfully established but the session terminates during long running transactions. After a while I get pqconnection null pointer error.

On postgresql side, Connection limit = -1, on Lazarus side KeepConnection=True...I cannot figure out why connection breaks down on long running transactions...

Any idea?

francisco1844

  • New member
  • *
  • Posts: 15
Re: Remote Postgresql session breaks down
« Reply #1 on: February 27, 2017, 04:34:09 pm »
If I recall Postgresql 9.6 established an option to kill "Idle In Transaction" connections after a certain amount of time.

You may want to check what is the state of your connection(s) while they are working see if it/they somehow end up as Idle In Transaction.

As the postgres superuser try:
select * from pg_stat_activity;

And look for your query to see what is it' state.

May also what to check what happens to the connection after your Pascal program gets the error. See if the connection is still visible in postgres.

May also be good to check the logs in postgres to see if there are any clues in there.

tatamata

  • Hero Member
  • *****
  • Posts: 633
    • ZMSQL - SQL enhanced in-memory database
Re: Remote Postgresql session breaks down
« Reply #2 on: February 27, 2017, 04:57:47 pm »
If I recall Postgresql 9.6 established an option to kill "Idle In Transaction" connections after a certain amount of time.
In postgresql.conf:
Code: [Select]
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabledThis should mean that the timeout is disabled...

May also what to check what happens to the connection after your Pascal program gets the error. See if the connection is still visible in postgres.
The session is closed in Postgresql too, even much before Lazarus actually shows the Pqconnection error.

zeljko

  • Hero Member
  • *****
  • Posts: 888
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Remote Postgresql session breaks down
« Reply #3 on: February 27, 2017, 05:06:00 pm »
There's also another timeout setting (cannot remember exact name atm), look if it's default (0) or have some value.

francisco1844

  • New member
  • *
  • Posts: 15
Re: Remote Postgresql session breaks down
« Reply #4 on: February 27, 2017, 05:20:45 pm »
The session is closed in Postgresql too, even much before Lazarus actually shows the Pqconnection error.

Anything in the postgres logs?
I am thinking of two potential scenarios:
* Postgres closed the connection(s).
* Somehow FPC closed the connection.

If it was postgres, something should show in the postgres logs.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus