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

--
-- 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(dot)a(dot)nash(at)saic(dot)com

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

> -----Original Message-----
> From: pgadmin-support-owner(at)postgresql(dot)org
> [mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of David Nash
> Sent: 10 March 2006 13:18
> To: pgadmin-support(at)postgresql(dot)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