I am trying to debug an error in list box handling in forms.

The problem happens under ubuntu 8.04.3 which only has openoffice 2.4, so I
have now compiled from 3.1.0 source on NetBSD-current/i386 (pkgsrc) and
see the same problem.

So, openoffice 3.1.0, unixODBC, psqlODBC, PostgreSQL is the datasource setup.

Two toy tables, one references the other which will be the source for the
list box:

create table college (
  id         serial primary key,
  shortname  text
);
  
create table names (
  id      serial primary key,
  name    text,
  college integer references college(id)
);

insert into college (shortname) values ('Gonville and Caius');
insert into college (shortname) values ('Newnham');
insert into college (shortname) values ('Trinity Hall');
insert into names (college,name) values (1,'John');

Now create a form in design view, names is the source for the form, college
is the source for the list box, and you want names.college to link to
college.id. Add a text box for id and name to check all the data.

When leaving design view, the data looks correct and it appears that you see
the equivalent of

 select names.id, names.name, college.shortname
   from names, college
  where names.college = college.id;

 id | name |     shortname      
----+------+--------------------
  1 | John | Gonville and Caius

But now try entering a record. From an earlier run (more colleges hence '9'):

LOG:  statement: BEGIN;update "public"."names" set "name" = E'Minnie', "college"
= -1309508768 where ctid = '(0, 9)' returning ctid
ERROR:  insert or update on table "names" violates foreign key constraint
"names_college_fkey"                  
DETAIL:  Key (college)=(-1309508768) is not present in table "college".

-1309508768 doesn't look like the id from the listbox...


Some oddities looking at the logs:

  SELECT * FROM "cuca"."public"."names" WHERE 0 = 1

is called often - why? It can't possibly return any data, and I think you
already found the field names from the system tables (pg_catalog schema).

  PREPARE "_KEYSET_0xb41f90e0"(tid,tid) as SELECT * , "ctid" FROM
"cuca"."public"."names" where ctid in ($1,$2)

That query is prepared, but I don't think it is used:

test=# select ctid,id from names;
 ctid  | id 
-------+----
 (0,1) |  1
(1 row)

test=# select *,ctid from names where ctid in (0,1);
ERROR:  operator does not exist: tid = integer
LINE 1: select *,ctid from names where ctid in (0,1);
                                            ^
HINT:  No operator matches the given name and argument type(s). You might need
to add explicit type casts.


So, what should I do now? Do you know which bit of OO code I could start
looking at? (Why the complication of ctids instead of using the primary keys -
is it to take care of tables without primary keys? (PKCOLUMN_NAME))