Redundant SQL commands

From: Erwin Brandstetter <brandstetter(at)falter(dot)at>
To: pgadmin-support(at)postgresql(dot)org
Subject: Redundant SQL commands
Date: 2007-04-07 21:20:05
Message-ID: 46180B05.1080807@falter.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

Hi developers! Hi Dave!

Testing pgAdmin III v1.6.3 rev: 6112, client Win XP, host: Debian Sarge,
PG 8.1.8.

Not sure, wheter this is any important.
While checking on the SQL sent to the database for displaying sequence
properties (with log_statement = 'all' in postgresql.conf) I made the
following observations.

When I refresh the properties of a sequence in the pgAdmin object tree
(hitting F5) these statements are sent:
(full quote)
SELECT cl.oid, relname, pg_get_userbyid(relowner) AS seqowner, relacl,
description
FROM pg_class cl
LEFT OUTER JOIN pg_description des ON des.objoid=cl.oid
WHERE relkind = 'S' AND relnamespace = 2200::oid
AND cl.oid=1537768::oid
ORDER BY relname
SELECT blks_read, blks_hit FROM pg_statio_all_sequences WHERE relid =
1537768::oid
SELECT last_value, min_value, max_value, cache_value, is_cycled,
increment_by FROM termin_termin_id_seq
SELECT last_value, min_value, max_value, cache_value, is_cycled,
increment_by FROM termin_termin_id_seq
SELECT blks_read, blks_hit FROM pg_statio_all_sequences WHERE relid =
1537768::oid

The last two SELECTs are done twice. Seems redundant?
To get the bigger picture I checked on every object type in the tree and
found a couple of similar redundancies when refreshing.
Here is a summary, quoting only the redundant parts:

table
SELECT opcname FROM pg_opclass WHERE oid=1978
SELECT opcname FROM pg_opclass WHERE oid=1978

view
SELECT opcname FROM pg_opclass WHERE oid=1978
SELECT opcname FROM pg_opclass WHERE oid=1978

schema
SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
pg_type t, pg_namespace n WHERE t.typnamespace = n.oid
SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
pg_type t, pg_namespace n WHERE t.typnamespace = n.oid
SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
pg_type t, pg_namespace n WHERE t.typnamespace = n.oid

database
SELECT has_table_privilege('pg_authid', 'SELECT')
SELECT has_table_privilege('pg_authid', 'SELECT')

No redundancies when refreshing other objects:
domain, aggregate, fuction, procedure, type etc. (didn't have any custom
conversions, slony nodes, operators or operator classes to check on)

One more case when opening the properties dialogue for a view:
SELECT usename FROM pg_user ORDER BY usename
SELECT usename FROM pg_user ORDER BY usename

Does not happen with properties dialogue of any other object.
But I've seen that line a lot:
SELECT 1;
Seems redundant per se? (Or maybe to check whether the connection is
still alive?)

Is it appropriate to post that kind of stuff in -support or would you
rather have me post it in _hackers?
Oh, and let me know if this report is of use to you at all. I'd rather
not waste any of your precious time. (Neither mine ;) )

Regards
Erwin

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Jason Best 2007-04-10 18:06:55 Slony-I creation scripts not available
Previous Message Sidnei Vladisauskis 2007-04-07 17:30:26 RES: RES: pgAgent - where is the node jobs?