Apache OpenOffice (AOO) Bugzilla – Issue 116801
Postgresql SDBC Driver Heuristics for detecting primary key don't work
Last modified: 2013-02-07 22:34:27 UTC
When using the PostGresql SDBC Driver, the driver does not recognise the primary key, and then fails when using an updateable resultset. This is despite:- (1) There being a primary key on the table. (2) The primary key being included in the select statement. (3) The driver (seeming) to recognise that there is a primary key. Please see the logs of the application:- **** Fri Feb 4 11:19:58 2011 [ERROR]: can't support updateable resultset for table public.FILEDOCUMENTS, because resultset does not contain a part of the primary key ( column FILEDOCUMENTID is missing ) Fri Feb 4 11:19:58 2011 [SQL]: executed query 'SELECT "FILESTEPSID", "Filename", "Description","FileDate","FileFeeEarner","UnitsOfTime","BillingCategory","IsOpenOffice?", "FILEDOCUMENTID" FROM "FILEDOCUMENTS" WHERE "FILEDOCUMENTID"=1072' sucessfully, duration=1ms, returnedRows=1. **** Look at the error message, and then the next line - which actually contains the "select" statement - the last column selected before the 'WHERE' is the primary key column. The JDBC driver does not have this problem, but I would prefer not to use it as it runs around 50 - 75% slower on my 32 bit machines, and around 500% slower on 64bit machine! Thanks.
@ domcoop What linux? You use OOo from OOo site or use linux distro version? If yes, report to your linux distro this problem, the issue not solvable here. What version of SDBC driver you use? What version of postgresql you connect? The heuristics some times fails, for this in OOo 3.3 was introduced new settings under Advanced settings: http://wiki.services.openoffice.org/wiki/Base/New_features_in_3_3#Allow_to_overrule_Base.27s_heuristics_for_primary_key_support Please try to use other settings, what you have now.
1) Using stock OOO not distro - 3.0 RC10 * I am going to test on the final release version and see if that fixes it; but previous releases had the same problem (I just never got around to reporting it, as I tried to make the JDBC connector work faster) 2) Using Fedora 14 distribution if that is relevant - same issue on Fedora 12 3) Postgresql SDBC Version 0.7.6b 4) Postgresql server connected via ip 5) Server version 9.0 - same bug appears on 8.4 (I upgraded to see if it would make a difference). 6) The dba module KNOWS that the database supports primary keys - that is not the problem. It seems to be parsing the SELECT statement and thinking (for some reason) that the primary key has not been SELECTed when it has. In any event this is a native SDBC driver - not an external API (and therefore this option is not available or needed)
The SDBC driver successfully queries for the structure of the database - here is the application log **** Fri Feb 4 10:53:37 2011 [SQL]: executed query 'SELECT DISTINCT ON (pg_namespace .nspname, relname ) pg_namespace.nspname, relname, relkind, pg_description.descr iption FROM pg_namespace, pg_class LEFT JOIN pg_description ON pg_class.oid = pg _description.objoid WHERE relnamespace = pg_namespace.oid AND ( relkind = 'r' OR relkind = 'v') AND pg_namespace.nspname LIKE '%' AND relname LIKE 'FILEDOCUMENT S' ' sucessfully, duration=4ms, returnedRows=1. Fri Feb 4 10:53:37 2011 [SQL]: executed query 'SELECT DISTINCT ON (pg_namespace .nspname, relname ) pg_namespace.nspname, relname, relkind, pg_description.descr iption FROM pg_namespace, pg_class LEFT JOIN pg_description ON pg_class.oid = pg _description.objoid WHERE relnamespace = pg_namespace.oid AND ( relkind = 'r' OR relkind = 'v') AND pg_namespace.nspname LIKE '%' AND relname LIKE 'FILEDOCUMENT S' ' sucessfully, duration=6ms, returnedRows=1. Fri Feb 4 10:53:37 2011 [SQL]: executed query 'SELECT attname,attnum FROM pg_at tribute INNER JOIN pg_class ON attrelid = pg_class.oid INNER JOIN pg_namespace O N pg_class.relnamespace = pg_namespace.oid WHERE relname='FILEDOCUMENTS' AND nsp name='public'' sucessfully, duration=4ms, returnedRows=16. Fri Feb 4 10:53:37 2011 [SQL]: executed query 'SELECT conname, contype, confup dtype, confdeltype, class2.relname, nmsp2.nspname, conkey,confkey FROM pg_constr aint INNER JOIN pg_class ON conrelid = pg_class.oid INNER JOIN pg_namespace ON p g_class.relnamespace = pg_namespace.oid LEFT JOIN pg_class AS class2 ON confreli d = class2.oid LEFT JOIN pg_namespace AS nmsp2 ON class2.relnamespace=nmsp2.oid WHERE pg_class.relname = 'FILEDOCUMENTS' AND pg_namespace.nspname = 'public'' su cessfully, duration=10ms, returnedRows=1. Fri Feb 4 10:53:37 2011 [SQL]: executed query 'SELECT pg_namespace.nspname, pg_ class.relname, pg_attribute.attname, pg_type.typname, pg_attribute.atttypmod, pg _attribute.attnotnull, pg_type.typdefault, pg_attribute.attnum, pg_type.typtype, pg_attrdef.adsrc, pg_description.description, pg_type.typbasetype FROM pg_class, pg_attribute LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum LEFT JOIN pg_description ON pg_attribute.attrelid = pg_description.objoid AND pg_attribute.attnum=pg_description.objsubid, pg_type, pg_namespace WHERE pg_attribute.attrelid = pg_class.oid AND pg_attribute.atttypid = pg_type.oid AND pg_class.relnamespace = pg_namespace.oid AND pg_namespace.nspname LIKE 'public' AND pg_class.relname LIKE 'FILEDOCUMENTS' AND pg_attribute.attname LIKE '%' ORDER BY pg_namespace.nspname || pg_class.relname || pg_attribute.attnum' sucessfully, duration=15ms, returnedRows=16. ****
Now tried using final release - Milestone 20 build 9567 - same issue.
Created attachment 76780 [details] minimal patch This is fixed in a newer version of the PostgreSQL-SDBC driver, soon to be released. In the meantime, here's a minimal patch.
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.