pg_connect_pool retry on cancelled queries or dropped connection

[eqbalz]

I am using the pg_connect_pool option to direct stocklevel and orderstatus procs to a standby instance. The standby instance sometimes cancels a query on "recovery conflict" and sometimes restarts as well under certain conditions. I would like to be able to

1. Retry a cancelled query (some configurable number of times with possibly exponential backoff)
2. Reconnect in case connection is dropped.

Any suggestions on how to go about modifying hammerdb to accomplish this? I am rusty on tcl!
I have tried this for retrying on cancelled queries, but doing reconnect seems a very difficult task for me. Any suggestions welcome.

                ...
                set retry_count 10
                set cur_retry 1
                while {$cur_retry <= $retry_count } {
                    if { [catch { ostat $lda_os $w_id $RAISEERROR $ora_compatible $pg_storedprocs } qerror] } {
                        puts "caught query error: retrying cur retry count: $cur_retry out of $retry_count."
                        incr cur_retry
                        if { $cur_retry == $retry_count } {
                            puts "last retry before giving up"
                            ostat $lda_os $w_id $RAISEERROR $ora_compatible $pg_storedprocs
                            break
                        }
                    } else {
                        break
                    }
                    after [expr {$cur_retry * $cur_retry * 100}]
                }
                ...

[sm-shaw]

There is already the ability to re-run the entire script using the Virtual User Iterations option:

This can then be used to reconnect after a failure. An example is shown here using SQL Server, however the approach can be used for any database.

Firstly make sure that RAISEERROR is set to true so that errors caused the driver script to exit.

Then at the start of the script, if needed, add a catch to close any connections from the previous iteration, in this case we have added obdc close to the start of the script.

Note that also in a scenario where the system may be down for a period of time you may want to add a pause before trying to connect by also adding a line such as the following which will sleep for 60 seconds before trying to reconnect.

after 60000

Now run the workload as normal, in this example I have used the following SQL to query the session ids and then called kill to kill a particular session.

SELECT conn.session_id, host_name, program_name,
    nt_domain, login_name, connect_time, last_request_end_time 
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn
   ON sess.session_id = conn.session_id;

If you look at Virtual User 3 you can see that we killed the session 3 times, and it shows Iterations complete 3 times and then reconnected (because it it is re-running the script in its entirety)

Following this methodology for any database will allow you to test scenarios where a connection is dropped and you want to reconnect. Being all scripted gives you this flexibility to change test scenarios as you need.

[eqbalz]

I have tried this, but seem to be running into this crash: Tcl_RegisterChannel: duplicate channel names. any idea what could be wrong? I am using HammerDB-4.4.

[sm-shaw]

it is probably most likely that you have missed the bit about closing the previous connection, so you need to add the following under the EDITABLE OPTIONS:
catch {pg_disconnect $lda}

[eqbalz]

I have to do multiple disconnects as I am using pg_connect_pool. I have done that now, but I am running into same core dump still, although not as frequently and these kinds of errors as well Connection to database failed connection to server at xxx, port 5432 failed: could not receive data from server: Bad file descriptor SSL SYSCALL error: Bad file descriptor. After a few iterations postgres runs into connection limits. Could there be an issue with pgtcl library? where it's leaving orphaned connections on postgres due the above errors? BTW, I am using a scripted hammerdbcli.

[sm-shaw]

I wouldn't assume a problem with any of the HammerDB backend libraries unless there is clear evidence for it. (we have found a lot of bugs in databases over the years though).

Doing a general search for Bad file descriptor SSL SYSCALL error shows that this is often a connection timeout issue. By default we set just host port and sslmode.

connection {
 pg_host    = localhost
 pg_port    = 5432
 pg_sslmode = prefer
}

and use these like this:

 if {[catch {set lda [pg_connect -conninfo [list host = $host port = $port sslmode = $sslmode user = $user password = $password dbname = $dbname ]]} message]} {
        set lda "Failed" ; puts $message
....

However, there are a lot more options for you to explore that you can add to the HammerDB connection if you wish.

hammerdb>puts [ pg_conndefaults ]
{service Database-Service {} 20 {}} {user Database-User {} 20 postgres} {password Database-Password * 20 {}} {passfile Database-Password-File {} 64 {}} {channel_binding Channel-Binding {} 8 disable} {connect_timeout Connect-timeout {} 10 {}} {dbname Database-Name {} 20 {}} {host Database-Host {} 40 {}} {hostaddr Database-Host-IP-Address {} 45 {}} {port Database-Port {} 6 5432} {client_encoding Client-Encoding {} 10 {}} {options Backend-Options {} 40 {}} {application_name Application-Name {} 64 {}} {fallback_application_name Fallback-Application-Name {} 64 {}} {keepalives TCP-Keepalives {} 1 {}} {keepalives_idle TCP-Keepalives-Idle {} 10 {}} {keepalives_interval TCP-Keepalives-Interval {} 10 {}} {keepalives_count TCP-Keepalives-Count {} 10 {}} {tcp_user_timeout TCP-User-Timeout {} 10 {}} {sslmode SSL-Mode {} 12 disable} {sslcompression SSL-Compression {} 1 0} {sslcert SSL-Client-Cert {} 64 {}} {sslkey SSL-Client-Key {} 64 {}} {sslpassword SSL-Client-Key-Password * 20 {}} {sslrootcert SSL-Root-Certificate {} 64 {}} {sslcrl SSL-Revocation-List {} 64 {}} {sslcrldir SSL-Revocation-List-Dir {} 64 {}} {sslsni SSL-SNI {} 1 1} {requirepeer Require-Peer {} 10 {}} {ssl_min_protocol_version SSL-Minimum-Protocol-Version {} 8 TLSv1.2} {ssl_max_protocol_version SSL-Maximum-Protocol-Version {} 8 {}} {gssencmode GSSENC-Mode {} 8 disable} {krbsrvname Kerberos-service-name {} 20 postgres} {gsslib GSS-library {} 7 {}} {replication Replication D 5 {}} {target_session_attrs Target-Session-Attrs {} 15 any}

You can use the PostgreSQL manuals to determine what these are, however looking at timeouts and keepalives would be a good option. There is also this option to create your own connection loss handler routine https://pgtclng.sourceforge.net/pgtcldocs/pg-on-connection-loss.html