workload: large number of DEALLOCATE statements after pgx upgrade

[rafiss]

After the pgx upgrade in #68608 various workloads seem to be running huge numbers of (hundreds to thousands) DEALLOCATE statements. These are commands that delete a prepared statement.

The behavior change started after August 17, which was when the upgrade PR was merged. The new pgx behavior is to use a stmtcache, which is an LRU cache that automatically prepares any statement executed by pgx. When an entry is evicted from the LRU cache, the statement is deallocated. See https://github.com/jackc/pgconn/blob/53f5fed36c570f0b5c98d6ec2415658c7b9bd11c/stmtcache/lru.go#L118

Previously, the workload would explicitly prepare the statements it needs to use (and these would never get deallocated), but that behavior was removed because pgx no longer allows you to prepare a statement at the connection pool level. See commit f446498

Perhaps we need to do something else to make sure the workload SQL statements don't get deallocated.

This issue might be causing the failures of #69086 but that investigation is happening separately, in parallel. (see #69317)

[rafiss]

Trying to bring back the old prepare workload behavior in #69313

[rafiss]

Ah I see, jackc/pgx@0c3e59b is new in pgx4 and enables this automatic prepared statement caching.