Re: get/setReadOnly broken if default_transaction_read_only on

On 06/08/2012 04:19 PM, Johann 'Myrkraverk' Oskarsson wrote:
> Craig Ringer<ringerc(at)ringerc(dot)id(dot)au> writes:
>
>> The JDBC driver really needs a way to grab everything it needs to know
>> efficiently during initial connection setup, with some extensibility
>> so connection parameters can specify other things to request and cache
>> when the connection is first established.
>
> Something like
>
> SELECT current_setting( 'shared_buffers' )
> UNION
> SELECT current_setting( 'data_directory' );
>
> for some randomly chosen settings? Of course the query will need to be
> structured with key/values to be useful.

I was thinking more of running something like:

regress=#
SELECT name, setting
FROM pg_settings
WHERE name IN
('client_encoding','server_encoding','bytea_output','standard_conforming_strings',
'xmlbinary','DateStyle','TimeZone','array_nulls','backslash_quote','IntervalStyle',
'quote_all_identifiers', 'default_transaction_read_only');

name | setting
-------------------------------+----------------
array_nulls | on
backslash_quote | safe_encoding
bytea_output | hex
client_encoding | UTF8
default_transaction_read_only | off
DateStyle | ISO, MDY
IntervalStyle | postgres
quote_all_identifiers | off
server_encoding | UTF8
standard_conforming_strings | on
TimeZone | Australia/West
xmlbinary | base64
(11 rows)

... preferably combined with a mechanism for the server to notify (or
NOTIFY) the JDBC client when a pg_ctl reload or a SET causes settings to
change.

While the JDBC driver could try to track SET commands sent by its own
client, that would be trivially foiled by a SET wrapped in a function,
so really the server needs to tell the JDBC driver.

The main hurdles with using LISTEN/NOTIFY for that are:

- No triggers allowed on system tables so server code changes are
required; and

- If using LISTEN/NOTIFY, a way would be needed to make sure the
application never saw NOTIFY messages destined for PgJDBC (not hard with
payload notifies) and that a PgJDBC driver LISTEN name never clashed
with an application one.

- The JDBC driver won't find out about a settings change until the
end of a long-running query. Dunno of that matters, really.

The reason I'm interested in this is to allow the JDBC driver to do the
right thing for situations where server settings change how it should
interpret or send data. It'd also allow the JDBC driver to entirely cut
out server round trips and log spew on the backend when query
generators, ORMs, etc query settings. For example, one tool I use will
issue a `SHOW TRANSACTION ISOLATION;` painfully frequently; being able
to have PgJDBC answer that without a round trip by examining its cached
`transaction_isolation` would be great.

--
Craig Ringer