Re: Too many SET TimeZone and Application_name queries

On 10/13/19 10:24 PM, Amarendra Konda wrote:
> Hi Adrian,
> 
> Thanks a lot for the response.
> 
> We are using JDBC Driver 42.2.8 along with the Tomcat Server on Java 8. 
> As part of application code, We are *_not_* setting timezone (or) 
> application names. One observation was, application was querying columns 
> of the datatype "timestamp without time zone" .

Well something is explicitly setting the TimeZone. Per this:

https://stackoverflow.com/questions/18447995/postgresql-9-2-jdbc-driver-uses-client-time-zone

I would start with the JDBC driver. You might also try the Postgres JDBC 
list:

https://www.postgresql.org/list/pgsql-jdbc/


Re: application_name. I do not see SET for this when I connect using 
application_name as part of connection string:

psql "host=localhost dbname=postgres user=postgres 
application_name=psql_client"

[unknown]-[unknown]-2019-10-14 07:06:35.508 PDT-0LOG:  connection 
received: host=::1 port=46246
[unknown]-postgres-2019-10-14 07:06:35.530 PDT-0LOG:  connection 
authorized: user=postgres database=postgres SSL enabled 
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, 
compression=off)

So I believe this is being explicitly SET by something. Since 
'PostgreSQL JDBC Driver' is the Postgres JDBC driver name I would start 
there.

> 
> Regards, Amarendra
> 
> 
> On Fri, Oct 11, 2019 at 7:33 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 10/11/19 4:49 AM, Amarendra Konda wrote:
>      > Hi,
>      >
>      > In our test environment, it was observed that there are too many
>     queries
>      > were getting fired to the database server, even though they are
>     not part
>      > of the SQL query execution.
>      >
>      > And the number of queries that were coming to server are very
>     high. Can
>      > you please suggest on how to avoid these queries to the database
>     server ?
> 
>     My guess is your application server/framework is setting the below.
>     What are you using for above?
> 
>      >
>      >
>      > 2019-10-10 13:37:25 UTC:172.31.77.194(36920):
>      > user1@new_unity_green1:[2549]:LOG:  duration: 0.081 ms
>       statement: *SET
>      > application_name='PostgreSQL JDBC Driver';*
>      > 2019-10-10 13:37:25 UTC:172.31.69.112(45682):
>      > user1@new_unity_green0:[3545]:LOG:  duration: 0.036 ms
>       statement: *SET
>      > TimeZone='UTC';*
>      > 2019-10-10 13:37:25
>      > UTC:172.31.77.194(36902):user1@new_unity_green1:[2112]:LOG:
>       duration:
>      > 0.177 ms  statement: *SET TimeZone='Etc/UTC';SET
>      > application_name='PostgreSQL JDBC Driver';*
>      >
>      >
>      > *_Environment_*
>      >
>      >   * PGBouncer 1.9
>      >   * JDBC Driver 42.2.8
>      >   * Java 1.8
>      >   * PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc
>     (GCC) 4.8.3
>      >     20140911 (Red Hat 4.8.3-9), 64-bit
>      >
>      >
>      > Application Server, pgBouncer and database server are all configured
>      > with UTC only.
>      >
>      > =>show timezone;
>      >   TimeZone
>      > ----------
>      >   UTC
>      >
>      > Thanks in advance,
>      >
>      > Regards, Amarendra
>      >
>      >
>      >
>      >
>      >
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com