Here is a session I had on a system.
xdb=# select * from chart where glaccount = '1100-0000';glaccount | gldesc | gllevel_id
-----------+--------+------------
(0 rows)
(Note: There is a matching row that this failed to find.)
xdb=# select * from chart where glaccount <= '1100-0000';glaccount | gldesc | gllevel_id
-----------+--------+------------1000-0000 | ASSETS | H1100-0000 | Bank | A
(2 rows)
(Note: See, there it is.)
xdb=# insert into chart values ('1100-0000', 'TEST', 'A');
INSERT 149240 1
(This should have failed because glaccount is the primary key.)
xdb=# select * from chart where glaccount = '1100-0000';glaccount | gldesc | gllevel_id
-----------+--------+------------1100-0000 | TEST | A
(1 row)
xdb=# drop index chart_pkey;
DROP
xdb=# select * from chart where glaccount = '1100-0000';glaccount | gldesc | gllevel_id
-----------+--------+------------1100-0000 | Bank | A1100-0000 | TEST | A
(2 rows)
(And magically the missing one appears.)
xdb=# create unique index chart_pkey on chart (glaccount);
ERROR: Cannot create unique index. Table contains non-unique values
(As expected.)
xdb=# delete from chart where gldesc = 'TEST';
DELETE 1
xdb=# create unique index chart_pkey on chart (glaccount);
CREATE
xdb=# select * from chart where glaccount = '1100-0000';glaccount | gldesc | gllevel_id
-----------+--------+------------
(0 rows)
(And there is is, gone.)
I followed the instructions on interfacing user defined types as per
http://www.ca.postgresql.org/devel-corner/docs/programmer/xindex.html.
In fact I helped write that page so I am pretty sure I got it right.
This code worked fine before. The only change I did was in the C code
to use PG_FUNCTION_INFO_V1() style functions. I put in a lot of debug
statements and I am positive that the code is doing the right thing.
I made no changes to the SQL which does what is described on that web
page. Is it possible that that page is now outdated and needs to be
rewritten for PG_FUNCTION_INFO_V1() style interfaces?
Oddly enough this seems to be working on another system with the same
version but it is in production and I can't play with it as much.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
