Thread: Unhandled exception in PGAdmin when opening 16-million-record table

This one aught to be good!

The tool is after all called "pgAdmin" rather that say "pgBrowser".

I think you have a "teaching opportunity" here. There is a feature for
getting the first N rows that might help (a lot).  There is
query-by-example as well.

I can't really imagine the value of being able to look at all 16M
records in one list.

Not saying this excuses the crash necessarily or more importantly the
poor error message.  One might find a stack trace in the system error log?

On 10/29/2010 02:52 PM, Rob Richardson wrote:
> A customer was reviewing the database that supports the application we
> have provided.  One of the tables is very simple, but has over 16
> million records.  Here is the table's definition:
>
> CREATE TABLE feedback
> (
>   charge integer,
>   elapsed_time integer, -- number of elapsed minutes since data began
> recording
>   tag_type character varying(24), -- Description of tag being recorded
>   tag_value real, -- value of tag being recorded
>   status smallint, -- PLC Status, recorded with Control PV only
>   stack integer, -- Not used
>   heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling
>   cooling smallint DEFAULT 0 -- not used
> )
>
> As you see, there is no primary key.  There is a single index, as follows:
>
> CREATE INDEX feedback_charge_idx
>   ON feedback
>   USING btree
>   (charge);
> In PGAdmin, the customer selected this table and clicked the grid on the
> toolbar, asking for all of the records in the table.  After twenty
> minutes, a message box appeared saying that an unhandled exception had
> happened.  There was no explanation of what the exception was.  The
> database log does not contain any information about it.  The PGAdmin
> display did show a number of records, leading me to believe that the
> error happened in PGAdmin rather than anywhere in PostGres.
>
> Can anyone explain what is happening?
>
> The customer is using PostgreSQL 8.4.5 (we just updated them within the
> last few days) and PGAdmin 1.10.5 on a Windows Server 2003 box.
>
> I see PGAdmin is now up to 1.12.1.  I suppose the first thing I should
> do is update their PGAdmin.
>
> Thanks for your help!
>
> RobR
>

On Fri, 2010-10-29 at 15:02 -0600, Rob Sargent wrote:

> Not saying this excuses the crash necessarily or more importantly the
> poor error message.  One might find a stack trace in the system error log?
>

Think probably ran out of memory. 16M records? Really?

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Le 29/10/2010 13:52, Rob Richardson a écrit :
> A customer was reviewing the database that supports the application we
> have provided.  One of the tables is very simple, but has over 16
> million records.  Here is the table's definition:
>
> CREATE TABLE feedback
> (
>   charge integer,
>   elapsed_time integer, -- number of elapsed minutes since data began
> recording
>   tag_type character varying(24), -- Description of tag being recorded
>   tag_value real, -- value of tag being recorded
>   status smallint, -- PLC Status, recorded with Control PV only
>   stack integer, -- Not used
>   heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling
>   cooling smallint DEFAULT 0 -- not used
> )
>
> As you see, there is no primary key.  There is a single index, as
> follows:
>
> CREATE INDEX feedback_charge_idx
>   ON feedback
>   USING btree
>   (charge);
>
> In PGAdmin, the customer selected this table and clicked the grid on the
> toolbar, asking for all of the records in the table.  After twenty
> minutes, a message box appeared saying that an unhandled exception had
> happened.  There was no explanation of what the exception was.  The
> database log does not contain any information about it.  The PGAdmin
> display did show a number of records, leading me to believe that the
> error happened in PGAdmin rather than anywhere in PostGres.
>
> Can anyone explain what is happening?
>

Definitely not an error in PostgreSQL. More related to pgAdmin.

> The customer is using PostgreSQL 8.4.5 (we just updated them within the
> last few days) and PGAdmin 1.10.5 on a Windows Server 2003 box.
>
> I see PGAdmin is now up to 1.12.1.  I suppose the first thing I should
> do is update their PGAdmin.
>

Won't do anything if your customer still wants to look at 16 million rows.

The only thing we could probably do on the coding side is looking at the
estimated number of rows and displays a warning message telling: "Hey
dude, you're trying to look at around 16 million rows. That can't work.
You would be very well advised to cancel.", but still allows the user to
bypass this check (if the estimated number of rows is wrong).


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

On 29 October 2010 21:52, Rob Richardson <Rob.Richardson@rad-con.com> wrote:
> A customer was reviewing the database that supports the application we have
> provided.  One of the tables is very simple, but has over 16 million
> records.  Here is the table's definition:
>
> CREATE TABLE feedback
> (
>   charge integer,
>   elapsed_time integer, -- number of elapsed minutes since data began
> recording
>   tag_type character varying(24), -- Description of tag being recorded
>   tag_value real, -- value of tag being recorded
>   status smallint, -- PLC Status, recorded with Control PV only
>   stack integer, -- Not used
>   heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling
>   cooling smallint DEFAULT 0 -- not used
> )
>
> As you see, there is no primary key.  There is a single index, as follows:
>
> CREATE INDEX feedback_charge_idx
>   ON feedback
>   USING btree
>   (charge);
> In PGAdmin, the customer selected this table and clicked the grid on the
> toolbar, asking for all of the records in the table.  After twenty minutes,
> a message box appeared saying that an unhandled exception had happened.
> There was no explanation of what the exception was.  The database log does
> not contain any information about it.  The PGAdmin display did show a number
> of records, leading me to believe that the error happened in PGAdmin rather
> than anywhere in PostGres.
>
> Can anyone explain what is happening?

Does WxWidgets/PgAdmin provide an overload of global operator new()
that follows the pre-standard C++ behaviour of returning a null ptr,
ala malloc()? C++ application frameworks that eschew exceptions often
do. This sounds like an unhandled std::bad_alloc exception.


Why don't we have some hard limit on the number of rows viewable in a
table? Would that really be so terrible?


--
Regards,
Peter Geoghegan

On 30 October 2010 11:26, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Hey all,
>
> Why not to use MVC approach by implementing a model, which uses, e.g.
> scrollable cursors? I believe that wxWidgets supports MVC.

I've seen that behaviour before in similar applications, but it had a
major downside: the number of rows returned was not known in advance
of scrolling down to the last one. So you couldn't visualise the size
of the record set based on the size and relative position of the
scrollbar.

--
Regards,
Peter Geoghegan

On Sat, Oct 30, 2010 at 2:45 PM, Peter Geoghegan
<peter.geoghegan86@gmail.com> wrote:
> On 30 October 2010 11:26, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>> Hey all,
>>
>> Why not to use MVC approach by implementing a model, which uses, e.g.
>> scrollable cursors? I believe that wxWidgets supports MVC.
>
> I've seen that behaviour before in similar applications, but it had a
> major downside: the number of rows returned was not known in advance
> of scrolling down to the last one. So you couldn't visualise the size
> of the record set based on the size and relative position of the
> scrollbar.

That is basically how it works (MVC), albeit without using cursors;
for both the reason you state and because part of the point of the
tool is to tune queries and using cursors to do that completely messes
up any timings we might get.


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

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

On Sat, Oct 30, 2010 at 4:14 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Do you mean that cursors (regular, not holdable) live only inside a
> transactions?
> But it is possible to check transaction status from another part of pgAdmin
> or
> even make the window with result set modal ?

No, I mean that the tool was developed to help tune application
queries, in which the data transfer time can be just as important as
the query execution time. With cursors, you lose that information.

Of course, patches to make optional use of cursors would be interesting to us.

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

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

Le 29/10/2010 14:46, Guillaume Lelarge a écrit :
> Le 29/10/2010 13:52, Rob Richardson a écrit :
>> A customer was reviewing the database that supports the application we
>> have provided.  One of the tables is very simple, but has over 16
>> million records.  Here is the table's definition:
>>
>> CREATE TABLE feedback
>> (
>>   charge integer,
>>   elapsed_time integer, -- number of elapsed minutes since data began
>> recording
>>   tag_type character varying(24), -- Description of tag being recorded
>>   tag_value real, -- value of tag being recorded
>>   status smallint, -- PLC Status, recorded with Control PV only
>>   stack integer, -- Not used
>>   heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling
>>   cooling smallint DEFAULT 0 -- not used
>> )
>>
>> As you see, there is no primary key.  There is a single index, as
>> follows:
>>
>> CREATE INDEX feedback_charge_idx
>>   ON feedback
>>   USING btree
>>   (charge);
>>
>> In PGAdmin, the customer selected this table and clicked the grid on the
>> toolbar, asking for all of the records in the table.  After twenty
>> minutes, a message box appeared saying that an unhandled exception had
>> happened.  There was no explanation of what the exception was.  The
>> database log does not contain any information about it.  The PGAdmin
>> display did show a number of records, leading me to believe that the
>> error happened in PGAdmin rather than anywhere in PostGres.
>>
>> Can anyone explain what is happening?
>>
>
> Definitely not an error in PostgreSQL. More related to pgAdmin.
>
>> The customer is using PostgreSQL 8.4.5 (we just updated them within the
>> last few days) and PGAdmin 1.10.5 on a Windows Server 2003 box.
>>
>> I see PGAdmin is now up to 1.12.1.  I suppose the first thing I should
>> do is update their PGAdmin.
>>
>
> Won't do anything if your customer still wants to look at 16 million rows.
>
> The only thing we could probably do on the coding side is looking at the
> estimated number of rows and displays a warning message telling: "Hey
> dude, you're trying to look at around 16 million rows. That can't work.
> You would be very well advised to cancel.", but still allows the user to
> bypass this check (if the estimated number of rows is wrong).
>

I added a ticket on this (http://code.pgadmin.org/trac/ticket/273) so
that we can work on it at a later time.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com