Re: Too many SET TimeZone and Application_name queries

Hi Adrian,

Thanks a lot for the right pointer. Setting -Duser.timezone=UTC has solved
the problem. Now, we don't see any more queries related to *SET TimeZone.*
Thanks again for your time and valuable suggestion.

@Jeff : These queries were sent by the JDBC Driver latest changes, nothing
to do with the Tomcat server. On test server, We were seeing around 45 K +
queries with very minimal load.

Regards, Amarendra

On Mon, Oct 14, 2019 at 7:45 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)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(at)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(at)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>