SQL statement logging: picking up strange queries from "pg_catalog"

Hi all,

I am currently trying to analyse some performance problems within my
postgresql v8.4.4 database, and after successfully turning sql
statement logging on, I discover thousands and thousands of queries
being made what looks like an internal postgresql set of tables in a
database called "pg_catalog".

These queries vastly outnumber the queries to the real database, which
are lost in the noise.

Does anyone recognise what these statements are for, whether they are
necessary, and if they aren't necessary, how to switch them off?

Here is a sample of the log:

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 <minfrin(at)sharp(dot)fm> writes:
> I am currently trying to analyse some performance problems within my
> postgresql v8.4.4 database, and after successfully turning sql
> statement logging on, I discover thousands and thousands of queries
> being made what looks like an internal postgresql set of tables in a
> database called "pg_catalog".

> These queries vastly outnumber the queries to the real database, which
> are lost in the noise.

> Does anyone recognise what these statements are for, whether they are
> necessary, and if they aren't necessary, how to switch them off?

I'd bet that you are using a client-side driver that feels a need to
fetch metadata about the tables it's working with. JDBC or ODBC maybe?

A reasonable driver would cache such information, so if there are enough
of these to present a performance issue, maybe the problem is you're
using too short-lived database connections.

regards, tom lane

On 04 Jul 2010, at 12:49 AM, Tom Lane wrote:

> I'd bet that you are using a client-side driver that feels a need to
> fetch metadata about the tables it's working with. JDBC or ODBC
> maybe?
>
> A reasonable driver would cache such information, so if there are
> enough
> of these to present a performance issue, maybe the problem is you're
> using too short-lived database connections.

I am using JDBC indeed, most specifically
postgresql-8.4-701.jdbc4.jar, along with the atomikos transaction
manager.

Does postgresql have an access log where the lifetime of database
connections are tracked? Specifically tracking when the client
connected, and how long the client stayed connected for.

Regards,
Graham
--

Graham Leggett <minfrin(at)sharp(dot)fm> writes:
> Does postgresql have an access log where the lifetime of database
> connections are tracked? Specifically tracking when the client
> connected, and how long the client stayed connected for.

See log_connections and log_disconnections. You probably want to
add PID to log_line_prefix, too, so that you can associate different
log entries for the same session.

regards, tom lane