Postgres Pro
Downloads
Home   >   mailing lists

Thread: Strange bug...

Strange bug...

From
"Donald Fraser"
Date:
OS W2K SP2
pgAdmin II 1.4.12
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
 
Another strange bug...
 
I create the following table using an SQL statement:
 
CREATE TABLE "tbl_usertype" (
"id" int4 NOT NULL,
"s_desc" varchar(60) NOT NULL,
CONSTRAINT "tbl_usertype_s_desc_key" UNIQUE ("s_desc"),
CONSTRAINT "tbl_usertype_pkey" PRIMARY KEY ("id")
) WITHOUT OIDS;
GRANT SELECT ON "tbl_usertype" TO PUBLIC;
 
pgAdmin then reports the following as the SQL statements:
 
-- Table: public.tbl_usertype
CREATE TABLE public.tbl_usertype (
id int4 NOT NULL,
s_desc varchar(60) NOT NULL,
CONSTRAINT tbl_usertype_s_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_usertype_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
GRANT SELECT ON TABLE public.tbl_usertype TO PUBLIC;
GRANT ALL ON TABLE public.tbl_usertype TO postgres;
 
Now one would expect to see under pgAdmin one Index named "tbl_usertype_s_desc_key".
pgAdmin reports zero Indexes....?
Again I checked the output from pg_dumpall and it definitely exists.
pg_dump displays the following lines.
-- Name: tbl_usertype_s_desc_key; Type: INDEX; Schema: public; Owner: postgres
CREATE UNIQUE INDEX tbl_usertype_s_desc_key ON tbl_usertype USING btree (s_desc);
 
Regards
Donald Fraser.

Re: Strange bug...

From
"Dave Page"
Date:
Hi Donald,
 
This is a feature, not a bug :-)
 
Seriously, pgAdmin figures out that the index is part of a constraint and classes it as a system object, therefore hiding it. If you switch on View System Objects on the View menu, you will see both indexes under the table. My guess is that pg_dump is not quite so clever and misses the UNIQUE constraint from the table definition, adding the index manually instead.
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 17 January 2003 20:05
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Strange bug...

OS W2K SP2
pgAdmin II 1.4.12
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
 
Another strange bug...
 
I create the following table using an SQL statement:
 
CREATE TABLE "tbl_usertype" (
"id" int4 NOT NULL,
"s_desc" varchar(60) NOT NULL,
CONSTRAINT "tbl_usertype_s_desc_key" UNIQUE ("s_desc"),
CONSTRAINT "tbl_usertype_pkey" PRIMARY KEY ("id")
) WITHOUT OIDS;
GRANT SELECT ON "tbl_usertype" TO PUBLIC;
 
pgAdmin then reports the following as the SQL statements:
 
-- Table: public.tbl_usertype
CREATE TABLE public.tbl_usertype (
id int4 NOT NULL,
s_desc varchar(60) NOT NULL,
CONSTRAINT tbl_usertype_s_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_usertype_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
GRANT SELECT ON TABLE public.tbl_usertype TO PUBLIC;
GRANT ALL ON TABLE public.tbl_usertype TO postgres;
 
Now one would expect to see under pgAdmin one Index named "tbl_usertype_s_desc_key".
pgAdmin reports zero Indexes....?
Again I checked the output from pg_dumpall and it definitely exists.
pg_dump displays the following lines.
-- Name: tbl_usertype_s_desc_key; Type: INDEX; Schema: public; Owner: postgres
CREATE UNIQUE INDEX tbl_usertype_s_desc_key ON tbl_usertype USING btree (s_desc);
 
Regards
Donald Fraser.

Re: Strange bug...

From
"Donald Fraser"
Date:
Thanks for that Dave,
I don't mind it not displaying the constraint as an index but the one thing I would ask for is consistency...
 
For example if I create the following table via SQL:
 
CREATE TABLE public.tbl_cmpycat (
id int4 NOT NULL,
s_desc text NOT NULL,
id_editedby int4,
dt_edited timestamp,
CONSTRAINT tbl_cmpycat_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_cmpycat_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
 
When I view it under pgAdminII it lists one index named tbl_cmpycat_desc_key...?
 
This then leads on to another bug...
When I try to drop the above table (not the index) pgAdminII attempts to drop the index first and then I get the following message as recorded in the log.
21-01-2003 12:52:17 - SQL (Bugs): DROP INDEX public.tbl_cmpycat_desc_key
21-01-2003 12:52:17 - Error in pgAdmin II:basActions.Drop: -2147467259 - ERROR:  Cannot drop index tbl_cmpycat_desc_key because constraint tbl_cmpycat_desc_key on table tbl_cmpycat requires it  You may drop constraint tbl_cmpycat_desc_key on table tbl_cmpycat instead
 
Regards
Donald Fraser
 
----- Original Message -----
From: Dave Page
Sent: Tuesday, January 21, 2003 12:28 PM
Subject: RE: [pgadmin-support] Strange bug...

Hi Donald,
 
This is a feature, not a bug :-)
 
Seriously, pgAdmin figures out that the index is part of a constraint and classes it as a system object, therefore hiding it. If you switch on View System Objects on the View menu, you will see both indexes under the table. My guess is that pg_dump is not quite so clever and misses the UNIQUE constraint from the table definition, adding the index manually instead.
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 17 January 2003 20:05
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Strange bug...

OS W2K SP2
pgAdmin II 1.4.12
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
 
Another strange bug...
 
I create the following table using an SQL statement:
 
CREATE TABLE "tbl_usertype" (
"id" int4 NOT NULL,
"s_desc" varchar(60) NOT NULL,
CONSTRAINT "tbl_usertype_s_desc_key" UNIQUE ("s_desc"),
CONSTRAINT "tbl_usertype_pkey" PRIMARY KEY ("id")
) WITHOUT OIDS;
GRANT SELECT ON "tbl_usertype" TO PUBLIC;
 
pgAdmin then reports the following as the SQL statements:
 
-- Table: public.tbl_usertype
CREATE TABLE public.tbl_usertype (
id int4 NOT NULL,
s_desc varchar(60) NOT NULL,
CONSTRAINT tbl_usertype_s_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_usertype_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
GRANT SELECT ON TABLE public.tbl_usertype TO PUBLIC;
GRANT ALL ON TABLE public.tbl_usertype TO postgres;
 
Now one would expect to see under pgAdmin one Index named "tbl_usertype_s_desc_key".
pgAdmin reports zero Indexes....?
Again I checked the output from pg_dumpall and it definitely exists.
pg_dump displays the following lines.
-- Name: tbl_usertype_s_desc_key; Type: INDEX; Schema: public; Owner: postgres
CREATE UNIQUE INDEX tbl_usertype_s_desc_key ON tbl_usertype USING btree (s_desc);
 
Regards
Donald Fraser.

Re: Strange bug...

From
"Dave Page"
Date:
It's a bit bizarre I know, but it comes from some old code that had to figure out if an index is system generated. The rules are:
 
1) Unique = True AND Name = TableName_FirstColName_key (i.e. created as a unique constraint)
2) Primary = True (i.e. created as a primary key on a table).
 
In the second example you sent, you missed the s_ prefix from the name, so rule 1 doesn't catch it as a system index. It's not a perfect soilution, but until PostgreSQL 7.3 came along, it was the best I could come up with. The changes to support 7.3 using a new better method are extensive, so I will only be implementing them in pgAdmin III.
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 21 January 2003 12:59
To: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Strange bug...

Thanks for that Dave,
I don't mind it not displaying the constraint as an index but the one thing I would ask for is consistency...
 
For example if I create the following table via SQL:
 
CREATE TABLE public.tbl_cmpycat (
id int4 NOT NULL,
s_desc text NOT NULL,
id_editedby int4,
dt_edited timestamp,
CONSTRAINT tbl_cmpycat_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_cmpycat_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
 
When I view it under pgAdminII it lists one index named tbl_cmpycat_desc_key...?
 
This then leads on to another bug...
When I try to drop the above table (not the index) pgAdminII attempts to drop the index first and then I get the following message as recorded in the log.
21-01-2003 12:52:17 - SQL (Bugs): DROP INDEX public.tbl_cmpycat_desc_key
21-01-2003 12:52:17 - Error in pgAdmin II:basActions.Drop: -2147467259 - ERROR:  Cannot drop index tbl_cmpycat_desc_key because constraint tbl_cmpycat_desc_key on table tbl_cmpycat requires it  You may drop constraint tbl_cmpycat_desc_key on table tbl_cmpycat instead
 
Regards
Donald Fraser
 
----- Original Message -----
From: Dave Page
Sent: Tuesday, January 21, 2003 12:28 PM
Subject: RE: [pgadmin-support] Strange bug...

Hi Donald,
 
This is a feature, not a bug :-)
 
Seriously, pgAdmin figures out that the index is part of a constraint and classes it as a system object, therefore hiding it. If you switch on View System Objects on the View menu, you will see both indexes under the table. My guess is that pg_dump is not quite so clever and misses the UNIQUE constraint from the table definition, adding the index manually instead.
 
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 17 January 2003 20:05
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Strange bug...

OS W2K SP2
pgAdmin II 1.4.12
PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC 2.96
 
Another strange bug...
 
I create the following table using an SQL statement:
 
CREATE TABLE "tbl_usertype" (
"id" int4 NOT NULL,
"s_desc" varchar(60) NOT NULL,
CONSTRAINT "tbl_usertype_s_desc_key" UNIQUE ("s_desc"),
CONSTRAINT "tbl_usertype_pkey" PRIMARY KEY ("id")
) WITHOUT OIDS;
GRANT SELECT ON "tbl_usertype" TO PUBLIC;
 
pgAdmin then reports the following as the SQL statements:
 
-- Table: public.tbl_usertype
CREATE TABLE public.tbl_usertype (
id int4 NOT NULL,
s_desc varchar(60) NOT NULL,
CONSTRAINT tbl_usertype_s_desc_key UNIQUE (s_desc),
CONSTRAINT tbl_usertype_pkey PRIMARY KEY (id)
) WITHOUT OIDS;
GRANT SELECT ON TABLE public.tbl_usertype TO PUBLIC;
GRANT ALL ON TABLE public.tbl_usertype TO postgres;
 
Now one would expect to see under pgAdmin one Index named "tbl_usertype_s_desc_key".
pgAdmin reports zero Indexes....?
Again I checked the output from pg_dumpall and it definitely exists.
pg_dump displays the following lines.
-- Name: tbl_usertype_s_desc_key; Type: INDEX; Schema: public; Owner: postgres
CREATE UNIQUE INDEX tbl_usertype_s_desc_key ON tbl_usertype USING btree (s_desc);
 
Regards
Donald Fraser.