Hi Gary,
Currently as long as udig is running it is holding a transaction.
What can be done here is to enable transactions only when Editing is
taking place. Each layer has the concept of "editable" which can be
set by the user or by the system. By default it is on, so that users
can leap straight into editing. There are two options. One we can
make a plugin for you that will make all the layer non-editable by
default rather than editable.
I think this is a good idea and will implement this change today. It
is not a large change so I should be able to complete it in time for
the next release.
I will send you the plugin for your application so that you can have
the editing off by default when we have made the release..
I appreciate the complete analysis you have given this issue,
Jesse
On 13-Oct-06, at 7:27 AM, Gary Lucas wrote:
uDig/GeoTools/JDBC clobbers Postgres vacuums
I was wondering if anyone had any experience with the following
problem. We have encountered a situation where uDig 1.1.RC4
interferes with Postgres ability to perform maintenance on tables,
eventually causing the database management system to fail. I'm not
sure whether the problem is in uDig, GeoTools, or the JDBC itself.
If it lies in uDig or GeoTools, then I suspect that the problem can
be fixed easily.
Background:
We run a high performance, real-time application that tracks
aircraft movements on a 7-by-24 basis. The system uses Postgres as
its database engine. We are currently working on integrating the
system with GeoServer and uDig and have recently installed PostGIS.
A key feature of the database is that it features a nearly 100
percent turnover of its data every 24 hours and must run for weeks
at a time without being shutdown for maintenance. We stress the
heck out of Postgres and it runs like a champ.
Recently, I have seen occasions where database performance fell
through the floor. Maintenance procedures that normally ran in two
minutes were taking over 11000 seconds. After some investigation,
and many false leads, I determined that these problems occurred
when I left uDig running unattended. To make the problem happen, I
opened a PostGIS Data Source, plotted, performed one info request,
and went home.
The cause is that somewhere in uDig something happens to interfere
with the vacuum operation. In Postgres, when you delete a record,
the disk space it formerly occupied is not made available for re-
use until you or the system performs a maintenance procedure known
as a vacuum. Thus, if this maintenance procedure does not occur,
the database accumulates a lot of dead space in its files if an
application performs a lot of deletions (or SQL updates... which
are essentially copy the old record, delete it, and rewrite it in
its updated form). As dead space accumulates, data becomes scatted
over many pages in the database file system and badly fragmented,
until all queries fairly crawl). Over the last couple of years, the
Postgres folks introduced a feature to perform this vacuum
operation automatically, but we don't use it because it does add
some overhead and slow performance (at least when we tested the
feature a couple of releases ago). Fortunately, because we were
performing the Vacuum manually, we had the means to verify that it
was indeed uDig (or one of its components) that was interfering
with the Vacuum.
The Cause:
There are certain database operations that can stall a Postgres
vacuum process. For example, if a table is locked, or if an
application is in the middle of performing a SQL transaction
(“Begin” and “Commit”), the Vacuum operation waits until the lock
is released or the transaction is committed before performing any
further operations on the table. When we run a Vacuum manually, we
can put it in “verbose” mode and watch its progress. Last night, I
started uDig, plotted a layer, and started the Vacuum. It moved
along merrily until it reached the table that I had plotted in
uDig, at which point it stalled. When I terminated uDig several
hours later, the Vacuum resumed almost immediately.
I believe that uDig is either locking the table of interest or
holding a transaction open. And I’m not sure whether this is “a bug
or a feature”. From a certain perspective, this is not an
unreasonable thing for it to do since one can view uDig as a data-
editing tool… and if a user has exclusive authorship of a
database, then he might very well want a transaction open for long
periods of time. However, in a case where uDig is being used to
inspect a database (or a user just walks away and leaves his
editing session open), it can interfere with other operations. So,
if it is a feature rather than a bug, it may be a feature that
needs to be re-thought.
Suggestion:
I don’t know enough about uDig and GeoTools to make a suggestion
with any kind of authority, but I wonder if the quickest fix might
be to allow uDig to open a PostGIS Data Source on a read-only basis
and ensure that when it does so, no transactions or locks are left
in place between rendering operations. A more sophisticated
approach would allow multiple users to operate on the database, but
may be harder to implement.
Qualification:
Even as I write this, I must admit that I do not understand enough
about the workings of uDig (the source code of which I find quite
challenging to follow), or even Postgres, to claim that my analysis
is completely correct. But I did conduct the experiment as
described above multiple times and have no doubt that the problems
clear up almost immediately when uDig terminates. I hope that my
observations can contribute to the continuing process of
improvement of the uDig application.
Thank you for your attention in this matter.
Gary
---
Gary W. Lucas, Senior Software Engineer
Sonalysts, Inc
215 Parkway North
Waterford, CT 06320
(860) 326-3682
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
