Skip site navigation (1) Skip section navigation (2)

Re: Strange bug...

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Donald Fraser" <demolish(at)cwgsy(dot)net>,<pgadmin-support(at)postgresql(dot)org>
Subject: Re: Strange bug...
Date: 2003-01-21 13:28:07
Message-ID: 03AF4E498C591348A42FC93DEA9661B88582@mail.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgadmin-support
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(at)cwgsy(dot)net] 
	Sent: 21 January 2003 12:59
	To: pgadmin-support(at)postgresql(dot)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 <mailto:dpage(at)vale-housing(dot)co(dot)uk>  
	To: Donald Fraser <mailto:demolish(at)cwgsy(dot)net>  ;
pgadmin-support(at)postgresql(dot)org 
	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(at)cwgsy(dot)net] 
		Sent: 17 January 2003 20:05
		To: pgadmin-support(at)postgresql(dot)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.

pgadmin-support by date

Next:From: Dave PageDate: 2003-01-21 13:38:57
Subject: Re: Anomolous schemas
Previous:From: Donald FraserDate: 2003-01-21 12:58:37
Subject: Re: Strange bug...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group