Обсуждение: pgAdmin Query Tool hangs when working with GIS data
Hello,
I've run into a the same issue described here:
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
Le 17/12/2010 22:08, Michael Willekes a écrit : > 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). > This file is used by the query tool since 1.12. Do you know which size it was? -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
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 sizeit was?
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Le 20/12/2010 16:39, Michael Willekes a écrit : > 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. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
stop my subscription message On 12/20/10, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 20/12/2010 16:39, Michael Willekes a écrit : >> 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 > > -- > Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-support >
Le 20/12/2010 22:20, Guillaume Lelarge a écrit : > Le 20/12/2010 16:39, Michael Willekes a écrit : >> 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. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
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 <guillaume@lelarge.info> wrote:
Le 20/12/2010 22:20, Guillaume Lelarge a écrit :> Le 20/12/2010 16:39, Michael Willekes a écrit :I tried your SQL file, but don't have your issue. It just works for me.
>> 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.
>
Damn slow, but works.
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 <mikewillekes@gmail.com> wrote: > 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 <guillaume@lelarge.info> > wrote: >> >> Le 20/12/2010 22:20, Guillaume Lelarge a écrit : >> > Le 20/12/2010 16:39, Michael Willekes a écrit : >> >> 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
Le 22/12/2010 09:47, Dave Page a écrit : > 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. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 22/12/2010 09:47, Dave Page a écrit : >> 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
Le 22/12/2010 10:26, Dave Page a écrit : > On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >> Le 22/12/2010 09:47, Dave Page a écrit : >>> 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 comboboxand 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? -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 22/12/2010 10:26, Dave Page a écrit : >> On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge >> <guillaume@lelarge.info> wrote: >>> Le 22/12/2010 09:47, Dave Page a écrit : >>>> 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. 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
Le 23/12/2010 00:31, Dave Page a écrit :
> On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
>> Le 22/12/2010 10:26, Dave Page a écrit :
>>> On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge
>>> <guillaume@lelarge.info> wrote:
>>>> Le 22/12/2010 09:47, Dave Page a écrit :
>>>>> 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.
>
> 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?
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Вложения
Seems like a bugfix, even if thd behaviour changes. On 12/25/10, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 23/12/2010 00:31, Dave Page a écrit : >> On Thu, Dec 23, 2010 at 12:05 AM, Guillaume Lelarge >> <guillaume@lelarge.info> wrote: >>> Le 22/12/2010 10:26, Dave Page a écrit : >>>> On Wed, Dec 22, 2010 at 10:15 AM, Guillaume Lelarge >>>> <guillaume@lelarge.info> wrote: >>>>> Le 22/12/2010 09:47, Dave Page a écrit : >>>>>> 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. >> >> 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? > > > -- > 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
Le 26/12/2010 01:57, Dave Page a écrit : > Seems like a bugfix, even if thd behaviour changes. > OK, then commited to both. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com