Thread: Edit grid crashes adding new rows to table with autoincrement primary key.

Dear friends,

I'm seeing a problem consistently when using the edit grid to add new rows
to a table whose primary key has data type "bigserial."  After completion of
editing the columns excluding that of the primary key, when I exit the
editing widget (e.g. by pressing the Enter key, or clicking the "Save the
changed row" button), the program ends immediately with a message from the
operating system that says, "pgAdmin3.exe has generated errors and will be
closed by Windows.  You will need to restart the program.  An error log is
being created."

Restarting pgAdmin and viewing the same table in the grid shows that the row
actually did get inserted prior to the crash.  I get the same result if I
enter unique data in the primary key column.

Editing a table whose primary key is just plain old int2 does not manifest
the crash.

I've been able to repeat the error as described above many times.

My system data is shown below.  Many thanks for any suggestions,

Dave

OS: Win2K with SP4
PostgreSQL version 8.1.3
pgAdmin III versions 1.4.1 and 1.4.2 (error is reproduced in both versions)

Table definition:

CREATE TABLE "Heritages"
( heritage varchar(30) NOT NULL, heritages_id int8 NOT NULL DEFAULT
nextval('"Heritages_heritages_id_seq"'::regclass), CONSTRAINT "Heritages_pkey" PRIMARY KEY (heritages_id)
)
WITHOUT OIDS;
ALTER TABLE "Heritages" OWNER TO postgres;



--
David A. Nash, Ph.D.

OneSAF Architecture and Integration (SAIC)
(321) 235-7735 (Voice)
(321) 235-1606 (Fax)
david.a.nash@saic.com

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 09 March 2006 14:39
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Edit grid crashes adding new rows
> to table with autoincrement primary key.
>
> Dear friends,
>
> I'm seeing a problem consistently when using the edit grid to
> add new rows
> to a table whose primary key has data type "bigserial."
> After completion of
> editing the columns excluding that of the primary key, when I exit the
> editing widget (e.g. by pressing the Enter key, or clicking
> the "Save the
> changed row" button), the program ends immediately with a
> message from the
> operating system that says, "pgAdmin3.exe has generated
> errors and will be
> closed by Windows.  You will need to restart the program.  An
> error log is
> being created."

Hi,

I'm currently unable to recreate this on a 1.5 dev build, so a couple of
questions I'm afraid:

Are you editting the table as user postgres?

Is the table you've shown one that you can recreate the error with, or a
cut-down and untested version of the real table definition?


Regards, Dave.

>> Are you editting the table as user postgres? 

Yes ... I am editing the table as the user postgres.  I also created the
table with that identity.

>> Is the table you've shown one that you can recreate the error with, or a
>> cut-down and untested version of the real table definition?

The table definition that I included was the actual, unmodified version.
Editing the table produced by this definition gives rise to the error.  I
have several other tables in the same database for which the error occurs or
does not, according to whether or not they have primary keys with an
autoincrement data type.

Many thanks!

Dave N.

-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Thursday, March 09, 2006 11:57 AM
To: dnash@ideorlando.org; pgadmin-support@postgresql.org
Subject: RE: [pgadmin-support] Edit grid crashes adding new rows to
table with autoincrement primary key.



> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org 
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 09 March 2006 14:39
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Edit grid crashes adding new rows 
> to table with autoincrement primary key.
> 
> Dear friends,
> 
> I'm seeing a problem consistently when using the edit grid to 
> add new rows
> to a table whose primary key has data type "bigserial."  
> After completion of
> editing the columns excluding that of the primary key, when I exit the
> editing widget (e.g. by pressing the Enter key, or clicking 
> the "Save the
> changed row" button), the program ends immediately with a 
> message from the
> operating system that says, "pgAdmin3.exe has generated 
> errors and will be
> closed by Windows.  You will need to restart the program.  An 
> error log is
> being created."

Hi,

I'm currently unable to recreate this on a 1.5 dev build, so a couple of
questions I'm afraid:

Are you editting the table as user postgres? 

Is the table you've shown one that you can recreate the error with, or a
cut-down and untested version of the real table definition?


Regards, Dave.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 09 March 2006 17:09
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Edit grid crashes adding new
> rows to table with autoincrement primary key.
>
> >> Are you editting the table as user postgres?
>
> Yes ... I am editing the table as the user postgres.  I also
> created the table with that identity.
>
> >> Is the table you've shown one that you can recreate the
> error with, or a
> >> cut-down and untested version of the real table definition?
>
> The table definition that I included was the actual,
> unmodified version.  Editing the table produced by this
> definition gives rise to the error.  I have several other
> tables in the same database for which the error occurs or
> does not, according to whether or not they have primary keys
> with an autoincrement data type.

Hmm, OK. If you recreate that table in a scratch database do you then
get the same error when editting, or is the problem confined to the
original tables?

Another thing to try - please switch the log level to debug under File
-> Options, and capture the error in the most minimal test case possible
(to avoid generating a 40GB logfile!). I'd be a little surprised if I'm
honest, but there might just be a clue in it. It might also be worth
checking the server logs.

Regards, Dave.

Dave, your suggestion worked ... creating a new database, and a new table
within it made it possible to edit the table without error.  For
completeness, the new table definition appears below, but it's pretty clear
that there's no meaningful difference between it and the one that didn't
work:

CREATE TABLE "TestTable"
( "keyColumn" int8 NOT NULL DEFAULT
nextval('"TestTable_keyColumn_seq"'::regclass), "myDataColumn" varchar(30), CONSTRAINT "TestTable_pkey" PRIMARY KEY
("keyColumn")
) 
WITHOUT OIDS;
ALTER TABLE "TestTable" OWNER TO postgres;

Based on this result I went back to the original database, created a new
table with an identical definition, and tried to edit the new table.  It
worked just fine.

One pattern that suggests itself is that the troublesome tables were ones
for which I had changed the schema, by deleting the primary key column and
adding a new one.  The tables were empty when I did this, so I didn't think
that it would be a problem.

I guess that the old idea in database engineering of doing the work to get
the schema right the first time around really applies in this case :) .

Thanks much,

Dave Nash

>> Hmm, OK. If you recreate that table in a scratch database do you then
>> get the same error when editting, or is the problem confined to the
>> original tables?
>> ...
>> Regards, Dave.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 10 March 2006 13:18
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Edit grid crashes adding new
> rows to table with autoincrement primary key.
>
> Dave, your suggestion worked ... creating a new database, and
> a new table within it made it possible to edit the table
> without error.  For completeness, the new table definition
> appears below, but it's pretty clear that there's no
> meaningful difference between it and the one that didn't work:
>
> CREATE TABLE "TestTable"
> (
>   "keyColumn" int8 NOT NULL DEFAULT
> nextval('"TestTable_keyColumn_seq"'::regclass),
>   "myDataColumn" varchar(30),
>   CONSTRAINT "TestTable_pkey" PRIMARY KEY ("keyColumn")
> )
> WITHOUT OIDS;
> ALTER TABLE "TestTable" OWNER TO postgres;
>
> Based on this result I went back to the original database,
> created a new table with an identical definition, and tried
> to edit the new table.  It worked just fine.
>
> One pattern that suggests itself is that the troublesome
> tables were ones for which I had changed the schema, by
> deleting the primary key column and adding a new one.  The
> tables were empty when I did this, so I didn't think that it
> would be a problem.

Interesting. Out of interest (if you still have it), what does pg_dump
think the schema looks like? It sounds like pgAdmin is misreading the
schema somehow, causing the crash - most likely when it tries to detect
the primary key. One thought - in the edit grid, did the int8 column
have [PK] in the column header?

> I guess that the old idea in database engineering of doing
> the work to get the schema right the first time around really
> applies in this case :) .

:-)

Regards, Dave

>> Interesting. Out of interest (if you still have it), what does pg_dump
>> think the schema looks like? It sounds like pgAdmin is misreading the
>> schema somehow, causing the crash - most likely when it tries to detect
>> the primary key. One thought - in the edit grid, did the int8 column
>> have [PK] in the column header?

In fact, the edit grid does show [PK] in the column header -- it reads:

heritages_id[PK] int8

... and here's the output from pg_dump -s -t Heritages :

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: Heritages; Type: TABLE; Schema: public; Owner: postgres;
Tablespace:
--

CREATE TABLE "Heritages" (   heritage character varying(30) NOT NULL,   heritages_id bigserial NOT NULL
);


ALTER TABLE public."Heritages" OWNER TO postgres;

--
-- Name: Heritages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres;
Tabl
espace:
--

ALTER TABLE ONLY "Heritages"   ADD CONSTRAINT "Heritages_pkey" PRIMARY KEY (heritages_id);


--
-- PostgreSQL database dump complete
--

All the best,
Dave N.

-- 
David A. Nash, Ph.D.

OneSAF Architecture and Integration (SAIC)
(321) 235-7735 (Voice)
(321) 235-1606 (Fax)
david.a.nash@saic.com


-----Original Message-----
From: Dave Page [mailto:dpage@vale-housing.co.uk]
Sent: Friday, March 10, 2006 8:44 AM
To: dnash@ideorlando.org; pgadmin-support@postgresql.org
Subject: RE: [pgadmin-support] Edit grid crashes adding new rows to
table with autoincrement primary key.



> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org 
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 10 March 2006 13:18
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Edit grid crashes adding new 
> rows to table with autoincrement primary key.
> 
> Dave, your suggestion worked ... creating a new database, and 
> a new table within it made it possible to edit the table 
> without error.  For completeness, the new table definition 
> appears below, but it's pretty clear that there's no 
> meaningful difference between it and the one that didn't work:
> 
> CREATE TABLE "TestTable"
> (
>   "keyColumn" int8 NOT NULL DEFAULT
nextval('"TestTable_keyColumn_seq"'::regclass),
>   "myDataColumn" varchar(30),
>   CONSTRAINT "TestTable_pkey" PRIMARY KEY ("keyColumn")
> ) 
> WITHOUT OIDS;
> ALTER TABLE "TestTable" OWNER TO postgres;
> 
> Based on this result I went back to the original database, 
> created a new table with an identical definition, and tried 
> to edit the new table.  It worked just fine.
> 
> One pattern that suggests itself is that the troublesome 
> tables were ones for which I had changed the schema, by 
> deleting the primary key column and adding a new one.  The 
> tables were empty when I did this, so I didn't think that it 
> would be a problem.

Interesting. Out of interest (if you still have it), what does pg_dump
think the schema looks like? It sounds like pgAdmin is misreading the
schema somehow, causing the crash - most likely when it tries to detect
the primary key. One thought - in the edit grid, did the int8 column
have [PK] in the column header?

> I guess that the old idea in database engineering of doing 
> the work to get the schema right the first time around really 
> applies in this case :) .

:-) 

Regards, Dave

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of David Nash
> Sent: 10 March 2006 14:02
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Edit grid crashes adding new
> rows to table with autoincrement primary key.
>
> >> Interesting. Out of interest (if you still have it), what
> does pg_dump
> >> think the schema looks like? It sounds like pgAdmin is
> misreading the
> >> schema somehow, causing the crash - most likely when it
> tries to detect
> >> the primary key. One thought - in the edit grid, did the
> int8 column
> >> have [PK] in the column header?
>
> In fact, the edit grid does show [PK] in the column header --
> it reads:
>
> heritages_id
>  [PK] int8
>
> ... and here's the output from pg_dump -s -t Heritages :

Seems it gets that right then - it would also be interesting to see what
query pgAdmin used to do the update if you still have the log around.

Thanks, Dave