Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
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.geoghega...@gmail.com On 29 October 2010 21:52, Rob Richardson rob.richard...@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.
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
On 30 October 2010 11:26, Dmitriy Igrishin dmit...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
On Sat, Oct 30, 2010 at 2:45 PM, Peter Geoghegan peter.geoghega...@gmail.com wrote: On 30 October 2010 11:26, Dmitriy Igrishin dmit...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
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.
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
On Sat, Oct 30, 2010 at 4:14 PM, Dmitriy Igrishin dmit...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
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 dp...@pgadmin.org On Sat, Oct 30, 2010 at 4:14 PM, Dmitriy Igrishin dmit...@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.
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unhandled exception in PGAdmin when opening 16-million-record table
On 29 October 2010 21:52, Rob Richardson rob.richard...@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
