Thread: duplicated Index entries
pgAdmin III version 1.0.2
On certain tables, under the Indexes tree view I am getting duplicated index entries but not on all entries.
For example I have a table that has 19 indexes yet the tree view shows 25 - hence it has duplicated 6 entries.
To prove that they were in fact duplicated:
a) each duplicated index has exactly the same details including the oid.
b) I deleted one of the duplicated entries and then did a refresh - the second entry was also removed.
Regards
Donald Fraser.
Some more information to help solve this sneaky little bug!
The duplicated indexes are only on indexes that use a function!
For example:
CREATE INDEX tbl_cmpy_notumbrella_key
ON tbl_cmpy
USING btree
(get_cmpyname(s_umbname, s_name))
WHERE b_isumbrella IS NOT TRUE AND d_deceased IS NULL;
ON tbl_cmpy
USING btree
(get_cmpyname(s_umbname, s_name))
WHERE b_isumbrella IS NOT TRUE AND d_deceased IS NULL;
----- Original Message -----From: Donald FraserTo: [pgADMIN]Sent: Thursday, April 29, 2004 5:30 PMSubject: [pgadmin-support] duplicated Index entriespgAdmin III version 1.0.2On certain tables, under the Indexes tree view I am getting duplicated index entries but not on all entries.For example I have a table that has 19 indexes yet the tree view shows 25 - hence it has duplicated 6 entries.To prove that they were in fact duplicated:a) each duplicated index has exactly the same details including the oid.b) I deleted one of the duplicated entries and then did a refresh - the second entry was also removed.RegardsDonald Fraser.
Hi Donald,
Sorry for the delay in replying.
I've created the following objects using the latest CVS version of pgAdmin and see no duplicates - would you mind trying the same on your system please?
CREATE TABLE foo
(
id serial NOT NULL,
data varchar(32)
) WITHOUT OIDS;
(
id serial NOT NULL,
data varchar(32)
) WITHOUT OIDS;
CREATE INDEX foo_idx
ON foo
USING btree
(ascii(data::text));
ON foo
USING btree
(ascii(data::text));
CREATE INDEX foo_idx2
ON foo
USING btree
(ascii(data::text))
WHERE id > 1;
ON foo
USING btree
(ascii(data::text))
WHERE id > 1;
Regards, Dave.
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 29 April 2004 17:42
To: [pgADMIN]
Subject: Re: [pgadmin-support] duplicated Index entriesSome more information to help solve this sneaky little bug!The duplicated indexes are only on indexes that use a function!For example:CREATE INDEX tbl_cmpy_notumbrella_key
ON tbl_cmpy
USING btree
(get_cmpyname(s_umbname, s_name))
WHERE b_isumbrella IS NOT TRUE AND d_deceased IS NULL;----- Original Message -----From: Donald FraserTo: [pgADMIN]Sent: Thursday, April 29, 2004 5:30 PMSubject: [pgadmin-support] duplicated Index entriespgAdmin III version 1.0.2On certain tables, under the Indexes tree view I am getting duplicated index entries but not on all entries.For example I have a table that has 19 indexes yet the tree view shows 25 - hence it has duplicated 6 entries.To prove that they were in fact duplicated:a) each duplicated index has exactly the same details including the oid.b) I deleted one of the duplicated entries and then did a refresh - the second entry was also removed.RegardsDonald Fraser.
Hi Dave,
I have tried the example you gave below and it does not show the problem.
Further investigation has shown that the duplicate entries are only occurring on functions that return a data type named citext.
Data type citext is my own installed data type... You might remember this data type from previous bugs that I reported in the early stages of development of pgAdmin III - it turned out that the problems were with PostgreSQL 7.3.x and not pgAdmin III.
Currently I am using PostgreSQL 7.4.2 so this shouldn't be a related bug - but then I'm not really qualified to say that for certain...
Not sure what else I can do to pin point things any further...
Regards
Donald Fraser
----- Original Message -----From: Dave PageTo: Donald Fraser ; [pgADMIN]Sent: Tuesday, May 04, 2004 9:33 AMSubject: RE: [pgadmin-support] duplicated Index entriesHi Donald,Sorry for the delay in replying.I've created the following objects using the latest CVS version of pgAdmin and see no duplicates - would you mind trying the same on your system please?CREATE TABLE foo
(
id serial NOT NULL,
data varchar(32)
) WITHOUT OIDS;CREATE INDEX foo_idx
ON foo
USING btree
(ascii(data::text));CREATE INDEX foo_idx2
ON foo
USING btree
(ascii(data::text))
WHERE id > 1;Regards, Dave.
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 29 April 2004 17:42
To: [pgADMIN]
Subject: Re: [pgadmin-support] duplicated Index entriesSome more information to help solve this sneaky little bug!The duplicated indexes are only on indexes that use a function!For example:CREATE INDEX tbl_cmpy_notumbrella_key
ON tbl_cmpy
USING btree
(get_cmpyname(s_umbname, s_name))
WHERE b_isumbrella IS NOT TRUE AND d_deceased IS NULL;----- Original Message -----From: Donald FraserTo: [pgADMIN]Sent: Thursday, April 29, 2004 5:30 PMSubject: [pgadmin-support] duplicated Index entriespgAdmin III version 1.0.2On certain tables, under the Indexes tree view I am getting duplicated index entries but not on all entries.For example I have a table that has 19 indexes yet the tree view shows 25 - hence it has duplicated 6 entries.To prove that they were in fact duplicated:a) each duplicated index has exactly the same details including the oid.b) I deleted one of the duplicated entries and then did a refresh - the second entry was also removed.RegardsDonald Fraser.
Hi Donald,
Yes, I do vaguely remember it! How deep is the dependency tree? Is is possible to send a copy of the definitions of the objects involved for testing?
Regards, Dave.
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 04 May 2004 10:41
To: [pgADMIN]
Subject: Re: [pgadmin-support] duplicated Index entriesHi Dave,I have tried the example you gave below and it does not show the problem.Further investigation has shown that the duplicate entries are only occurring on functions that return a data type named citext.Data type citext is my own installed data type... You might remember this data type from previous bugs that I reported in the early stages of development of pgAdmin III - it turned out that the problems were with PostgreSQL 7.3.x and not pgAdmin III.Currently I am using PostgreSQL 7.4.2 so this shouldn't be a related bug - but then I'm not really qualified to say that for certain...Not sure what else I can do to pin point things any further...RegardsDonald Fraser----- Original Message -----From: Dave PageTo: Donald Fraser ; [pgADMIN]Sent: Tuesday, May 04, 2004 9:33 AMSubject: RE: [pgadmin-support] duplicated Index entriesHi Donald,Sorry for the delay in replying.I've created the following objects using the latest CVS version of pgAdmin and see no duplicates - would you mind trying the same on your system please?CREATE TABLE foo
(
id serial NOT NULL,
data varchar(32)
) WITHOUT OIDS;CREATE INDEX foo_idx
ON foo
USING btree
(ascii(data::text));CREATE INDEX foo_idx2
ON foo
USING btree
(ascii(data::text))
WHERE id > 1;Regards, Dave.
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 29 April 2004 17:42
To: [pgADMIN]
Subject: Re: [pgadmin-support] duplicated Index entriesSome more information to help solve this sneaky little bug!The duplicated indexes are only on indexes that use a function!For example:CREATE INDEX tbl_cmpy_notumbrella_key
ON tbl_cmpy
USING btree
(get_cmpyname(s_umbname, s_name))
WHERE b_isumbrella IS NOT TRUE AND d_deceased IS NULL;----- Original Message -----From: Donald FraserTo: [pgADMIN]Sent: Thursday, April 29, 2004 5:30 PMSubject: [pgadmin-support] duplicated Index entriespgAdmin III version 1.0.2On certain tables, under the Indexes tree view I am getting duplicated index entries but not on all entries.For example I have a table that has 19 indexes yet the tree view shows 25 - hence it has duplicated 6 entries.To prove that they were in fact duplicated:a) each duplicated index has exactly the same details including the oid.b) I deleted one of the duplicated entries and then did a refresh - the second entry was also removed.RegardsDonald Fraser.