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 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

Hey all,

Why not to use MVC approach by implementing a model, which uses, e.g.
scrollable cursors? I believe that wxWidgets supports MVC.

2010/10/30 Peter Geoghegan <peter.geoghegan86@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

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

Hey Peter, Dave

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.

Why not MOVE cursor to the end, then get number of ROWS
(PQcmdTuples) affected by the MOVE command, and finally MOVE the first
tuple to determine the number of returned rows?

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.

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 ?

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

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

--
// Dmitriy.

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

Ahh, yes. In this case it is possibly only with holdable cursors by
declaring
them and committing before reading any rows from it to force materialization
of entire result set to the temporary storage.
Although, this approach can be used to avoid std::bad_alloc in case of
millions tuples in the result set :-)

2010/10/30 Dave Page <dpage@pgadmin.org>

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

--
// Dmitriy.

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