No way, holding open the transaction if we have no intent to edit is
pathological. We should not be telling the data store "hey, I am
about to edit you" until we select an edit tool... that is, the user
signals their intent to edit the layer. Gary's case is not a corner
case, it is smack in the middle, it's our behavior that is completely
wrongheaded.
P
On 13-Oct-06, at 7:53 AM, Jesse Eichar wrote:
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
_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel
