Connection Timeouts or "canceling statement due to user request"

[Abe Gillespie]

Good news! Turns out we've supported setting the timeout value since
version 1.0. In fact, we support everything connection parameter as
listed here:

http://npgsql.projects.postgresql.org/docs/manual/UserManual.html
(scroll down to the "Connection String Parameters" section)

Whatever appears in your .zig file will be sent to PostgreSQL. So, to
use the example.zig file, yours can now look like:

--8<-----------------------
[connection]
; Your PostgreSQL server.
server = localhost

; Your PostgreSQL server's port.
port = 5432

; Your PostGIS database.
database = yourdb

; Your PostGIS user.
user = psqluser

; Your PostGIS password.
password = psqluserpassword

; NEW - add the connection timeout here - the amount of time allowed
to make an initial connection to the PostgreSQL server (time in
seconds).
timeout = 30

; NEW - add the command timeout here - the amount of time allowed for
a query to execute (time in seconds).
commandtimeout = 60
----------------------->8--

So what does this mean for people experiencing the dreaded "canceling
statement due to user request" while the "Add PostGIS Data" form is
loading? Simply adjust the commandtimeout parameter to allow more
time for queries to return the metadata for you PostGIS tables.

Please let us know your experiences.

Good luck!
-Abe

[Obe, Regina]

Abe,

I tried this on one of my larger databases with kind of big datasets and
that seems to resolve my issue with timeout on this particular database.

It takes a while to load up though. In my case I have lots of schemas
-- and a lot of them I really don't even need to ever query. They are
usually their for other purposes. I also have a staging schema that's
really their just for loading in data before I process it.

So I'm thinking how hard would it be to as a preload -- show the users
the available schemas -- just query information_schema.schemata (that
would make the SQL Server case a bit easier too since both SQL Server
and PostgreSQL support the ANSI information_schema)

For that -- you'll want to filter out the system schemas such as things
that start with pg_ and information_schema. SQL Server does that
already pretty much, but PostgreSQL doesn't.

anyway so once you show the schema options -- then show a + sign or
allow the user to check which schemas they want to make available for
querying.

It might also be worthwhile to have a checkbox that says -- "Just read
from geometry_columns". I think QuantumGIS (or is it OpenJump) can't
remember gives that option. In my case its getting mostly slowed down
by a hierarchical tables I have of mixed SRIDS that's about 2,000,000
records in size which I really don't want queried directly anyway.

All this will be much easier when the new typmod model comes into play
in PostGIS 2.0, since the SRID, geometry type and all that other
information will be available via the PostgreSQL system views and as
part of the geometry field declaration. No more need for
AddGeometryColumn or forgetting to do that.

Thanks,
Regina

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

[Abe Gillespie]

Regina,

Thank you so much for your feedback. I'm glad it was able to do the
trick albeit slowly. And we greatly appreciate your ideas on how to
better handle the initial metadata grab. We've gathered feedback from
a few people and all will be used to form a better "add data"
experience in 3.0. Unfortunately zigGIS 2.0 is reaching it's
end-of-life so we will not enhance that codebase but only maintain it
with necessary bug fixes. All new development work will be focused on
3.0.

Thanks again.
-Abe