Issue 103672

Summary: [Postgres] database form list box inserts incorrect primary key value
Product: Base Reporter: prlw1 <prlw1>
Component: codeAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: drewjensen.inbox, helge.kraak, issues, mechtilde
Version: OOO310m19Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description prlw1 2009-07-20 17:51:21 UTC
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 to link to 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,, college.shortname
   from names, college
  where =;

 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
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))
Comment 1 drewjensen.inbox 2009-07-23 01:44:34 UTC
@prlw1 - Could you tell me what you have for the setting under:
Edit>Database>Advanced Settings ( Generated values page )

Comment 2 prlw1 2009-07-23 16:56:51 UTC
Generated values, settings, retrieve generated values is off <- is this wrong?

Special settings, options,

- use outer join syntax
- ignore the privileges from the database driver
- replace named parameters with ?
- use catolg name in select statements
- use schema name in select statements
- create index with asc or desc statement
- form data input checks for required fields

- use sql92 naming constraints
- append the table alias name on select statements
- display version columns (when available)
- end text lines with cr+lf

Comparison of Boolean values: default
Comment 3 prlw1 2009-07-23 20:24:08 UTC
Actually - why would it matter that id is auto_incremented? All the list_box
does is read id,shortname, display shortname, and insert id into another table
when shortname is selected.
Comment 4 drewjensen.inbox 2009-07-23 20:42:05 UTC
@prlw1...hmmm, yes I suppose your right..oops

Will get this checked tonight. (btw, the postgres folks released an update to
the ODBC driver day before yesterday I think..will try to check with this newer
one also)
Comment 5 peolonet 2009-08-21 07:57:28 UTC
I have the same problem. I've tried to change the odbc driver with the latest
(july 20th) but nothing changed. I have also tried to use ODBCng driver from
commandprompt inc but it is not in the list of drivers available (in the
registry it has a new entry, not the same of others ODBC drivers and the key 
has no settings).
Comment 6 peolonet 2009-08-21 08:22:05 UTC
Oppssss!!! I forgot to write that I have the same problem with OOo 3.1, windows
vista business, postgres 8.4. thx
Comment 7 prlw1 2009-09-07 18:09:55 UTC
Just checked with the 3.1.1 source code, i.e., OOO310m19, and the behaviour
hasn't changed.
Comment 8 Mechtilde 2010-01-02 08:42:02 UTC
@ atjensen
If you can confirm the problem plase set the issue to NEW

@ prlw1

Did you also try the nativ psql connector from
Comment 9 prlw1 2010-01-05 16:30:23 UTC
Just so you know someone is listening - no news yet:
- on windows I can only connect to database read-only, so can't insert a new
record with the form.
- on ubuntu jaunty 9.04, I can't get native connector to install - no errors,
just no postgresql in drop down on connect. (Did aptitude install
so did have database part of OO installed when installing native connector)
- NetBSD side I'll have try to compile connector from source - it would be
easier if said source was in the standard tarball...
... so still trying to get a native connector setup to reproduce the bug.
Comment 10 prlw1 2010-01-06 12:03:01 UTC
Success with the native connector (under windows after reinstalling postgresql -
odd as the odbc connection did allow read/write, so hard to say how the
permissions on the tables could be wrong)
Comment 11 prlw1 2010-01-06 18:26:40 UTC
Success also on ubuntu using
sudo aptitude install
rather than Tools/Package Manager

(With Tools/Package Manager, I could see it insalled in, but
postgresql didn't appear listed in oobase)