Redundant SQL commands - Mailing list pgadmin-support

From Erwin Brandstetter
Subject Redundant SQL commands
Date
Msg-id 46180B05.1080807@falter.at
Whole thread Raw
Responses Re: Redundant SQL commands
List 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


pgadmin-support by date:

Previous
From: "Sidnei Vladisauskis"
Date:
Subject: RES: RES: pgAgent - where is the node jobs?
Next
From: "Aaron Bono"
Date:
Subject: Motivations for PostgreSQL