Skip site navigation (1) Skip section navigation (2)

Re: get/setReadOnly broken if default_transaction_read_only on

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Johann 'Myrkraverk' Oskarsson <johann(at)2ndquadrant(dot)com>
Cc: PostgreSQL JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: get/setReadOnly broken if default_transaction_read_only on
Date: 2012-06-08 13:13:35
Message-ID: 4FD1FA7F.4050400@ringerc.id.au (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2012-06-08 13:43:34
Subject: Re: Connection.isValid(int timeout) implementation
Previous:From: Vlad ArkhipovDate: 2012-06-08 11:32:14
Subject: Connection.isValid(int timeout) implementation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group