Thread: COPY vs. INSERT
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.
darcy@druid.net (D'Arcy J.M. Cain) writes: > 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. Obviously it isn't. Care to show us the code? regards, tom lane
Thus spake Tom Lane > darcy@druid.net (D'Arcy J.M. Cain) writes: > > 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. > > Obviously it isn't. Care to show us the code? Sure. ftp://ftp.vex.net/pub/glaccount. By "right thing" I mean that when it gets a comparison it returns -1, 0 or +1 depending on the comparison. The problem appears to be that the functions just don't get called. That's why I suspect the SQL that sets up the indexing instead. And then there is the other 7.1.2 system that it works on. -- 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.
darcy@druid.net (D'Arcy J.M. Cain) writes:
>> Obviously it isn't. Care to show us the code?
> Sure. ftp://ftp.vex.net/pub/glaccount.
PG_FUNCTION_INFO_V1(glaccount_cmp);
Datum
glaccount_cmp(PG_FUNCTION_ARGS)
{ glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0); glaccount *a2 = (glaccount *)
PG_GETARG_POINTER(1);
PG_RETURN_BOOL(do_cmp(a1, a2));
}
The btree comparison function needs to return 1/0/-1, not boolean.
Try PG_RETURN_INT32().
PG_FUNCTION_INFO_V1(glaccount_eq);
Datum
glaccount_eq(PG_FUNCTION_ARGS)
{ glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0); glaccount *a2 = (glaccount *)
PG_GETARG_POINTER(1);
PG_RETURN_BOOL (!do_cmp(a1, a2));
}
PG_FUNCTION_INFO_V1(glaccount_ne);
Datum
glaccount_ne(PG_FUNCTION_ARGS)
{ glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0); glaccount *a2 = (glaccount *)
PG_GETARG_POINTER(1);
PG_RETURN_BOOL (!!do_cmp(a1, a2));
}
While these two are not actually wrong, that sort of coding always
makes me itch. Seems like
PG_RETURN_BOOL (do_cmp(a1, a2) == 0);
PG_RETURN_BOOL (do_cmp(a1, a2) != 0);
respectively would be cleaner, more readable, and more like the other
comparison functions. I've always thought that C's lack of distinction
between booleans and integers was a bad design decision; indeed, your
cmp bug kinda proves the point, no?
regards, tom lane
Thus spake Tom Lane
> darcy@druid.net (D'Arcy J.M. Cain) writes:
> >> Obviously it isn't. Care to show us the code?
>
> > Sure. ftp://ftp.vex.net/pub/glaccount.
>
> PG_FUNCTION_INFO_V1(glaccount_cmp);
> Datum
> glaccount_cmp(PG_FUNCTION_ARGS)
> {
> glaccount *a1 = (glaccount *) PG_GETARG_POINTER(0);
> glaccount *a2 = (glaccount *) PG_GETARG_POINTER(1);
>
> PG_RETURN_BOOL(do_cmp(a1, a2));
> }
>
>
> The btree comparison function needs to return 1/0/-1, not boolean.
> Try PG_RETURN_INT32().
Doh! I converted all the ints to booleans and got carried away. Now
I just have to figure out why this worked on another system.
> PG_RETURN_BOOL (!!do_cmp(a1, a2));
>
> While these two are not actually wrong, that sort of coding always
> makes me itch. Seems like
>
> PG_RETURN_BOOL (do_cmp(a1, a2) == 0);
>
> PG_RETURN_BOOL (do_cmp(a1, a2) != 0);
>
> respectively would be cleaner, more readable, and more like the other
> comparison functions. I've always thought that C's lack of distinction
> between booleans and integers was a bad design decision; indeed, your
> cmp bug kinda proves the point, no?
I agree with you about the lack of a true boolean type and it certainly
was the root of my error but I don't think that the other follows. I
don't think that using the paradigms is wrong. Kernighan gives a nice
talk on that subject. He argues that you don't have to write for people
that don't know the language. Rather you should strive for clarity but
use what programmers (in the same language) are used to. For example;
for (i = 1; i <= count; i++)
is correct but the equivalent (assuming i is a counter and is not used
in the loop itself)
for (i = 0; i < count; i++)
is the right way to code not because it is more correct but because it
is what a future maintainer who is comfortable with C would understand
faster.
In this case there might be an argument for one or the other as I have
seen both styles used about equally.
OK, I guess I can work on the docs for the indexing too as there are some
differences with the new methods.
--
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.