Обсуждение: Picking up strange queries for "pg_catalog"

Hi all,

While investigating some performance issues with a database, I have
been analysing the database queries, and have discovered that I am
getting thousands and thousands of queries that look like the ones
below.

Asking on the pgsql-general list, I was pointed in the direction of
the jdbc driver, which in theory is making these queries, but not
caching them.

Does this look familiar to anybody?

In addition to this, I am using the atomikos XA transaction manager,
could this be the source of these queries?

Is there anything I can do to stop these queries being made?

2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '4'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '4'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '5'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '5'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '8'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '8'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '9'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '9'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '7'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '7'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '10'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '10'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT attnotnull
FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '6'
2010-07-03 23:49:31 SAST LOG:  execute <unnamed>: SELECT def.adsrc
FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON
(a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and
a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
2010-07-03 23:49:31 SAST DETAIL:  parameters: $1 = '29427', $2 = '6'

Regards,
Graham
--

Graham Leggett wrote on 04.07.2010 13:13:
> Hi all,
>
> While investigating some performance issues with a database, I have been
> analysing the database queries, and have discovered that I am getting
> thousands and thousands of queries that look like the ones below.
>
> Asking on the pgsql-general list, I was pointed in the direction of the
> jdbc driver, which in theory is making these queries, but not caching them.
>
> Does this look familiar to anybody?
>
> In addition to this, I am using the atomikos XA transaction manager,
> could this be the source of these queries?
>

I don't think it's the driver "making" these calls. Some of them look like they are a result of calling severl JDBC
metadataAPI functions (e.g DatabaseMetaData.getColumns() or something similar) which in turn are not initiated by the
driver,but by the client software. So I would suspect the transaction manager or your ORM library to make those calls
(andthus generating the queries) 

Thomas

On 04 Jul 2010, at 1:25 PM, Thomas Kellerer wrote:

> I don't think it's the driver "making" these calls. Some of them
> look like they are a result of calling severl JDBC metadata API
> functions (e.g DatabaseMetaData.getColumns() or something similar)
> which in turn are not initiated by the driver, but by the client
> software. So I would suspect the transaction manager or your ORM
> library to make those calls (and thus generating the queries)

I have managed to isolate at least one of the strange queries to a
call to the "getColumnTypeName()" method on the following object:

org.postgresql.jdbc3.Jdbc3ResultSetMetaData

The debugger catches what I think are internal fields inside the above
object called "schemaNameCache" and "tableNameCache", both of which
are null.

In theory it looks like the Jdbc3ResultSetMetaData object is firing
off database queries on calls to metadata methods over and over again.

Am I on the right track, and if so, is there a way to get the JDBC
driver's org.postgresql.jdbc3.Jdbc3ResultSetMetaData object to cache
these calls so that they aren't as expensive?

Regards,
Graham
--