Transactions always re-query all types leading to huge delay

[phosmium]

Hey,

We're noticing big delays on certain transactions, we tracked it down to be the 'fetch types' functionality that queries select b.oid, b.typarray from pg_catalog.pg_type a left join pg_catalog.pg_type b on b.oid = a.typelem where a.typcategory = 'A' group by b.oid, b.typarray order by b.oid.

Each transaction re-fetches the types, disabling that feature however threw errors: PostgresError: malformed array literal: ""

Is there anything we can do against this delay? It delays them by 200ms+ on our side.

[porsager]

Types are only fetched at first connect. Are you running on some edge host? You can disable with fetch_types: false

[phosmium]

@porsager Hey, this is not running on an edge host. The problem is, disabling fetch_types throws some exceptions as it can't resolve a type from an array it seems. It seems to be re-fetching the types on each connection in the pool, so when I have a lot of transactions and it grabs different connections, it will indeed re-fetch the types.

When there are high amount of concurrent queries, postgres will open as many connections as needed up until max number of connections is reached. By default max is 10. This can be changed by setting max in the postgres() call. Example - postgres('connectionURL', { max: 20 }).

This means that we get a much simpler story for error handling and reconnections. Queries will be sent over the wire immediately on the next available connection in the pool. Connections are automatically taken out of the pool if you start a transaction using sql.begin(), and automatically returned to the pool once your transaction is done.

^ The README explains that behavior, except the part that it re-fetches all types. Is there any possible way to cache the types? We have a ton of concurrent queries as it's an gameserver, it not caching and re-fetching the types is quite time consuming as we have a big database. We noticed high delays on a big player count (stuff that requires knowing array types, as that plays a role) and when we had a lot of concurrent queries going on at startup which was eating some seconds of boot time.

[phosmium]

https://github.com/porsager/postgres/blob/master/src/connection.js#L743

This seems to be it, as needsTypes is scoped per connection and thus resulting in the re-fetch instead of re-using the already known types from a previous connection.