Hi pgadmin hackers,
I have been having trouble with the speed of the pgAdmin query tool on
remote links, and I'm sure I'm not the only one.
The delay is due to loading the data type information for every column:
> QUERY : Scalar query (localhost:50119): SELECT format_type(oid,-1)
as typname FROM pg_type WHERE oid = 1114
> QUERY : Scalar query (localhost:50119): SELECT CASE WHEN
typbasetype=0 THEN oid else typbasetype END AS basetype FROM pg_type
WHERE oid=1114
I have been playing around with a fix for quite a while, but I have
finally sat myself down and put it on github
(https://github.com/aiht/pgadmin3/commit/80a896e674bf68cc67aa3dc0dd425db9aa7372aa)
(patch attached, too).
I'm still trying to make sure it does exactly what I expect, so the
debug logging is quite heavy, but I wanted to ask a few questions to see
if I am on the right track.
If this code is useful to the project, I'll trim the logging down so it
isn't in the way.
Code conventions:
* Does the pg prefix for a class name have a specific meaning and if so,
am I using it wrongly?
* I have copied the i prefix on some methods, but I don't really
understand what it's for; I was thinking perhaps 'internal'?
Further suggestions:
* This code supports pre-caching all types in one go, but currently only
loads them one by one. Maybe this should be an option.
* When a pgConn is Duplicate()d, its cache is copied for the new
connection to use, but as far as I can see this is only called when
doing File->New Window in the query tool. If more pgConns are
constructed by Duplicate() then caching may be more efficient.
* Quite a few other parts of the code do their own type-info loading; at
least a few of them could share in this cache. However, the other code
does not load types one by one so they do not have the same latency
issues as the query tool.
Possible problems:
* Threading? As far as I have seen, the code doesn't tend to use
locking. The query threads just avoid accessing each others objects
while they are active. I have not added any locking, but each cache
object belongs to a single connection, and is only used from code that
was already using that connection, so I think it is okay.
* The cache is currently not flushed, because I was not sure when to
trigger it. The main object tree should probably flush its cache when
the view is refreshed, but I am not sure about what kind of changes the
server may make to existing types that require a flush.
* I have not tested this on Postgres variants (EDB etc.) or on old
versions. I have not tested the current version on Windows (although I
did test an old version of the same code long ago).
I have not yet patched the MSVS project file, but the only change
required should be to add the pgTypeCache.{h,cpp} files.
I hope it helps!
Harun
