Re: Edit grid crashes adding new rows to table with autoincrement primary key. - Mailing list pgadmin-support

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