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.kla...@aklaver.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.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
