[pgadmin-support] pgAdmin Query Tool hangs when working with GIS data

[Michael Willekes]

Hello,

I've run into a the same issue described here:

"Query Tool history makes the Query Tool unusable after BIG query had been
executed<http://archives.postgresql.org/pgadmin-support/2010-09/msg00006.php>
"

Hardware:
- Windows 7 64 bit, 2 GB Ram, 2.8 Ghz

Tools:
- PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 32-bit
- pgAdmin 1.12.1 (Oct 3, 2010, rev:REL-1_12_1)
- PostGIS 1.5.2

I used shp2pgsql from the PostGIS toolkit to generate an SQL Query from a
shapefile(*). The generated .SQL file is not too big (~13MB) but is a
little odd in that some of the generated geometry columns are excessively
wide (>250K characters on a single line). The query executes fine (data is
correctly loaded into the database), but subsequent attemps to load the
pgAdmin query tool cause pgAdmin to hang (has to be killed from Task
Manager).

Deleting 'pgadmin_histoqueries.xml' resolves the issue. I
uninstalled/reinstalled pgAdmin several times, but didn't know about the
existance of this history file (which was stored in my Windows AppData Dir -
and 'survived' an uninstall/reinstall - which of course meant that each
install exhibited the same problem).

It's probably not a common scenario, but it took me a while to find the
issue, so I thought I'd mention it here...

Regards,
Mike

(*) Shapefile:
http://www.naturalearthdata.com/downloads/10m-physical-vectors/10m-coastline/

[Guillaume Lelarge]

Post by Michael Willekes
Hello,
"Query Tool history makes the Query Tool unusable after BIG query had been
executed<http://archives.postgresql.org/pgadmin-support/2010-09/msg00006.php>
"
- Windows 7 64 bit, 2 GB Ram, 2.8 Ghz
- PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 32-bit
- pgAdmin 1.12.1 (Oct 3, 2010, rev:REL-1_12_1)
- PostGIS 1.5.2
I used shp2pgsql from the PostGIS toolkit to generate an SQL Query from a
shapefile(*). The generated .SQL file is not too big (~13MB) but is a
little odd in that some of the generated geometry columns are excessively
wide (>250K characters on a single line). The query executes fine (data is
correctly loaded into the database), but subsequent attemps to load the
pgAdmin query tool cause pgAdmin to hang (has to be killed from Task
Manager).
Deleting 'pgadmin_histoqueries.xml' resolves the issue. I
uninstalled/reinstalled pgAdmin several times, but didn't know about the
existance of this history file (which was stored in my Windows AppData Dir -
and 'survived' an uninstall/reinstall - which of course meant that each
install exhibited the same problem).

This file is used by the query tool since 1.12. Do you know which size
it was?

[Michael Willekes]

The history file is only 14 MB (4219 lines long) but the PostGIS geometry
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.



This file is used by the query tool since 1.12. Do you know which size

Post by Guillaume Lelarge
it was?
--
Guillaume
http://www.postgresql.fr
http://dalibo.com

[Guillaume Lelarge]

Post by Michael Willekes
The history file is only 14 MB (4219 lines long) but the PostGIS geometry
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.

Is it possible to get the SQL query? by private mail. My idea is to try
on my laptop and see if I found any issue. Thanks.

[Mohammadk485 Mohammadk485]

stop my subscription message

Post by Guillaume Lelarge

Post by Michael Willekes
The history file is only 14 MB (4219 lines long) but the PostGIS geometry
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.

Is it possible to get the SQL query? by private mail. My idea is to try
on my laptop and see if I found any issue. Thanks.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
http://www.postgresql.org/mailpref/pgadmin-support

[Guillaume Lelarge]

Post by Guillaume Lelarge

Post by Michael Willekes
The history file is only 14 MB (4219 lines long) but the PostGIS geometry
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.

Is it possible to get the SQL query? by private mail. My idea is to try
on my laptop and see if I found any issue. Thanks.

I tried your SQL file, but don't have your issue. It just works for me.
Damn slow, but works.

[Michael Willekes]

Thanks for investigating. It was simple to workaround once I realized what
the problem was. My only concern was that I had inadvertently triggered the
issue, and it was not obvious how to resolve/workaround it.

If someone runs into a similar issue, hopefully a search will turn-up this
thread.

Mike

On Tue, Dec 21, 2010 at 4:58 PM, Guillaume Lelarge

Post by Michael Willekes

Post by Guillaume Lelarge

Post by Michael Willekes
The history file is only 14 MB (4219 lines long) but the PostGIS

geometry

Post by Guillaume Lelarge

Post by Michael Willekes
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.

Is it possible to get the SQL query? by private mail. My idea is to try
on my laptop and see if I found any issue. Thanks.

I tried your SQL file, but don't have your issue. It just works for me.
Damn slow, but works.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com

[Dave Page]

Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

On Tue, Dec 21, 2010 at 11:15 PM, Michael Willekes

Post by Michael Willekes
Thanks for investigating. It was simple to workaround once I realized what
the problem was.  My only concern was that I had inadvertently triggered the
issue, and it was not obvious how to resolve/workaround it.
If someone runs into a similar issue, hopefully a search will turn-up this
thread.
Mike

Post by Guillaume Lelarge

Post by Guillaume Lelarge

Post by Michael Willekes
The history file is only 14 MB (4219 lines long) but the PostGIS geometry
columns (auto-generated from a .SHP file) result in individual INSERT
statements that are as much as 500000 characters long.

Is it possible to get the SQL query? by private mail. My idea is to try
on my laptop and see if I found any issue. Thanks.

I tried your SQL file, but don't have your issue. It just works for me.
Damn slow, but works.
--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Guillaume Lelarge]

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

[Dave Page]

On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

Oops.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Guillaume Lelarge]

Post by Dave Page
On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

Oops.

I looked a bit more at this issue. We use the option. The flow of
operations is:
* put the query in the combobox and the file
* execute the query
* if the query fails, delete it from the combobox and the file
* if it's bigger than the max size, delete it from combobox and file

The reasoning behind all this is to still be able to look at your query
if it crashes pgAdmin. So you don't have to type it again. You just need
to select it in the combobox and fix it.

It has its downside, for example when pgAdmin freezes and the query is
bigger than the max size, at restart, pgAdmin will try to put that huge
line in the combobox. Which probably doesn't please wxWidgets (and for
good reasons).

One way to fix the issue is to make sure we don't try to put a 14MB line
in the combobox.

Comments?

[Dave Page]

On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

Oops.

I looked a bit more at this issue. We use the option. The flow of
 * put the query in the combobox and the file
 * execute the query
 * if the query fails, delete it from the combobox and the file
 * if it's bigger than the max size, delete it from combobox and file
The reasoning behind all this is to still be able to look at your query
if it crashes pgAdmin. So you don't have to type it again. You just need
to select it in the combobox and fix it.

We can still limit the size of query written to the file. If you want
crash protection, use a temp file or something, rather than trying to
overload that on the history mechanism. If the file exists when the
query tool is reloaded, load the file (like word or openoffice do).

Those really are two different functions...
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Guillaume Lelarge]

Post by Dave Page
On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

Oops.

I looked a bit more at this issue. We use the option. The flow of
* put the query in the combobox and the file
* execute the query
* if the query fails, delete it from the combobox and the file
* if it's bigger than the max size, delete it from combobox and file
The reasoning behind all this is to still be able to look at your query
if it crashes pgAdmin. So you don't have to type it again. You just need
to select it in the combobox and fix it.

We can still limit the size of query written to the file. If you want
crash protection, use a temp file or something, rather than trying to
overload that on the history mechanism. If the file exists when the
query tool is reloaded, load the file (like word or openoffice do).
Those really are two different functions...

I agree. Don't know why I mixed the two. So I have a new patch
(attached). It changes the current behaviour.

Current behaviour:

When query is launched
Save query
When query is done
If unsuccessul
Delete query
If successful
If it's bigger than max size
Delete query
Else if already present
Delete the old one

New behaviour

When query is done
If successful
If it's less than max size
Add query
If already present
Delete the old one

This really changes the behaviour. I wrote the patch for the 1.12
branch, but I'm not sure we really want it there. So, only master, or
master and 1.12?

[Dave Page]

Seems like a bugfix, even if thd behaviour changes.

Post by Guillaume Lelarge

Post by Dave Page
On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge

Post by Guillaume Lelarge

Post by Dave Page
Guillaume; do we have a limit on the size of data we'll store in the
history file? We've seen 3 or 4 reports like this.

The Options dialog says yes (1024 characters). But, AFAICT from reading
the source code, we don't use it :-/ I was sure we did. Will fix it today.

Oops.

I looked a bit more at this issue. We use the option. The flow of
* put the query in the combobox and the file
* execute the query
* if the query fails, delete it from the combobox and the file
* if it's bigger than the max size, delete it from combobox and file
The reasoning behind all this is to still be able to look at your query
if it crashes pgAdmin. So you don't have to type it again. You just need
to select it in the combobox and fix it.

We can still limit the size of query written to the file. If you want
crash protection, use a temp file or something, rather than trying to
overload that on the history mechanism. If the file exists when the
query tool is reloaded, load the file (like word or openoffice do).
Those really are two different functions...

I agree. Don't know why I mixed the two. So I have a new patch
(attached). It changes the current behaviour.
When query is launched
Save query
When query is done
If unsuccessul
Delete query
If successful
If it's bigger than max size
Delete query
Else if already present
Delete the old one
New behaviour
When query is done
If successful
If it's less than max size
Add query
If already present
Delete the old one
This really changes the behaviour. I wrote the patch for the 1.12
branch, but I'm not sure we really want it there. So, only master, or
master and 1.12?
--
Guillaume
http://www.postgresql.fr
http://dalibo.com

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Guillaume Lelarge]

Post by Dave Page
Seems like a bugfix, even if thd behaviour changes.

OK, then commited to both.