Re: Discarding DISCARD ALL

On 12/23/20 3:47 PM, Vladimir Sitnikov wrote:
> Simon>It seems strange to me that we put this work onto the pooler, forcing
> Simon>poolers to repeatedly issue the same command
>
> What if poolers learn to manage connections and prepared statements better?
> Then poolers won't have to reset the session every time, and everyone wins.

While that is be possible to implement since some client libraries
implement this in their pools (e.g. Sequel for Ruby) this patch would
help connection poolers which are not aware of prepared statements, for
example PgBouncer, so it is worthwhile as long as there are connection
poolers out there which are not aware of prepared statements. And even
the connection poolers which are aware might want to automatically drop
temporary tables and reset GUCs. So I do not think that this feature
would become pointless even if people write a patch for PgBouncer.

> Simon>This has an additional side benefit: if we know we will clean up at
> Simon>the end of the transaction, then all temp tables become effectively ON
> Simon>COMMIT DROP
>
> The ability to use the temporary tables sounds cool, however,
> server-prepared statements
> allow improve performance significantly (both at frontend and backend),
> so I would not treat
> "server_reset_query=discard all" as a best practice.
> That is why transaction_cleanup=on (discard everything at transaction
> finish) seems to be a workaround
> rather than a best practice for the future.

While I know how to add support for prepared statements to a connection
pooler it is unclear to me how one would add support for temporary
tables which are not at least ON COMMIT DELETE ROWS since rows inserted
on one connection will only be visible at the connection.

Andreas