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

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