Bug 45254 - EDITING PostgreSQL-SDBC can not edit data with PostgreSQL server 8.4
Summary: EDITING PostgreSQL-SDBC can not edit data with PostgreSQL server 8.4
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.0 RC2
Hardware: x86-64 (AMD64) Linux (All)
: low major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:3.5.1 target:3.6.0
Keywords:
Depends on:
Blocks: mab3.5
  Show dependency treegraph
 
Reported: 2012-01-25 22:46 UTC by Drew Jensen
Modified: 2012-02-08 09:24 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
lotest.odb with apparently read-only form pointing at psql db (8.92 KB, application/vnd.oasis.opendocument.database)
2012-01-30 09:05 UTC, Patrick Welche
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2012-01-25 22:46:52 UTC
Open an ODB file connected to a Postgresql server

Open any table with a primary key.
Can not add or edit data
Comment 1 Petr Mladek 2012-01-26 05:56:09 UTC
Lionel could you please have a look?

We definitely need to fix soon. On the other hand, the postgreSQL connector is used only by limited number of users => it should not block the .0 release => lovering the severity a bit.
Comment 2 Lionel Elie Mamane 2012-01-26 08:39:43 UTC
Works for me on my own libreoffice-3-5 compile tree, on Debian GNU/LInux amd64: can edit data.
Comment 3 Patrick Welche 2012-01-29 07:53:08 UTC
I just tried 3.5RC2 on ubuntu 10.04 which comes with postgresql 8.4 and can't edit data.

The simple test was:


test=# \d names
                          Table "public.names"
 Column  |  Type   |                     Modifiers                      
---------+---------+----------------------------------------------------
 id      | integer | not null default nextval('names_id_seq'::regclass)
 name    | text    | 
 college | integer | 
Foreign-key constraints:
    "names_college_fkey" FOREIGN KEY (college) REFERENCES college(id) MATCH FULL

test=# select * from names;
 id |  name  | college 
----+--------+---------
  9 | Mickey |      11
(1 row)

Create a database form with the wizard (obviously not saying read-only nor dissallow modification). Click in the text box containing "Mickey" and attempt to change anything. (You can't add a record either.)

[Just noticed the above doesn't have a primary key, so tried on the college table which does - no change other than there are more records to choose from. Lionel, tell us your magic :-)]
Comment 4 Lionel Elie Mamane 2012-01-29 08:05:50 UTC
Please check that you don't have an older PostgreSQL-SDBC installed as an extension, either per-user or shared. My theory is that this older PostgreSQL-SDBC overrides the bundled/internal one.

You can exclude per-user extensions by using a fresh profile. Extensions are also listed in menu tools/extension manager.
Comment 5 Drew Jensen 2012-01-29 20:23:10 UTC
re-checked after ensuring no hint of an old LibO on this machine, 
reinstalled 3.5 RC2 and it came up with no previous setting recognized.

Can not edit anything using the Base GUI (dataviews and controls)

- insert and update statements via the SQL window work just fine

I can edit records using other tools running on the same desktop, same db, same db user account..just not with Base GUI.
Comment 6 Lionel Elie Mamane 2012-01-30 03:08:57 UTC
So, in menu Tools / Extension Manager, you don't have "PostgreSQL-SDBC" listed, right? If you do, then remove it and restart LibreOffice.

Else, please give me more detailed reproduction instructions, because it works for me, cannot reproduce. The only difference I can see is that I use a 9.0 server. I can edit data both in forms (both in datasheet and other arrangement) and in tables.

 - pg_dump of affected database (--format=plain)

 - which table / query / ... do you try to edit data from, how (form? double-click on table?)

 - attach the .odb file you use to access the database

 - how exactly did you install LibreOffice? .deb packages from http://www.libreoffice.org/download/pre-releases/? Ubuntu-provided packages? I ask because different builds have different postgresql options, it might make a difference (should not, but you never know). If you installed RC1 or earlier in the past, did you uninstall the old libobasis3.5-extension-postgresql-sdbc package and replace it with libobasis3.5-postgresql-sdbc_3.5.0-12_*.deb?
Comment 7 Patrick Welche 2012-01-30 05:28:36 UTC
(Quick reply to comment #6 - more later when I have time!)
> So, in menu Tools / Extension Manager, you don't have "PostgreSQL-SDBC" listed,
> right? If you do, then remove it and restart LibreOffice.

Correct (not listed)

> Else, please give me more detailed reproduction instructions, because it works
> for me, cannot reproduce. The only difference I can see is that I use a 9.0
> server. I can edit data both in forms (both in datasheet and other arrangement)
> and in tables.
> 
>  - pg_dump of affected database (--format=plain)
> 
>  - which table / query / ... do you try to edit data from, how (form?
> double-click on table?)

Hopefully those 2 can be deduced from comment 3

>  - attach the .odb file you use to access the database

Will do later....

>  - how exactly did you install LibreOffice? .deb packages from
> http://www.libreoffice.org/download/pre-releases/?

That is correct - purged everything remotely related to lodev/libreoffice, then dpkg -i the .deb packages from the link you quote.
Comment 8 Lionel Elie Mamane 2012-01-30 06:54:14 UTC
(In reply to comment #7)
> (Quick reply to comment #6 - more later when I have time!)

>>  - pg_dump of affected database (--format=plain)

>>  - which table / query / ... do you try to edit data from, how (form? double-click on table?)

> Hopefully those 2 can be deduced from comment 3

No, not completely. You show me only the structure of the "names" table, which has no primary key, not even a unique index. Then at the end of comment 3 you write "I tried with college table, too. It has primary key, but still does not work". I don't see / know the structure of the "college" table which is the interesting one, since there it is *supposed* to work. ("No primary key => cannot edit" is known and not PostgreSQL-specific.)

You also do not say whether you took all default options in the form creation wizard or if you put a subform or what. As it works for me, I'm trying to find *anything* you do different from me to try to reproduce the issue.
Comment 9 Patrick Welche 2012-01-30 09:03:37 UTC
Just lost one writeup thanks to my browser :-/ Trying again...

Standard postgresql 8.4 package on ubuntu 10.04.3 x86_64

test=# create database lotest;
CREATE DATABASE
test=# \c lotest
psql (8.4.10)
You are now connected to database "lotest".
lotest=# create table foo (id serial primary key, name text);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
lotest=# insert into foo (name) values ('Mickey');
INSERT 0 1
lotest=# select * from foo;
 id |  name  
----+--------
  1 | Mickey
(1 row)

Purge all lodev* libreoffice* packages
Install http://download.documentfoundation.org/libreoffice/testing/3.5.0-rc2/deb/x86_64/LibO_3.5.0rc2_Linux_x86-64_install-deb_en-US.tar.gz
Create new database with url (default on ubuntu)
dbname=lotest host=/var/run/postgresql port=5433
test connection with no userid -> established OK
save as /tmp/lotest.odb to be attached here, open for editing
create form with wizard, table = public.foo, >> for all fields
Get table. Clicking on Mickey doesn't get a prompt, new record is greyed out - AFAICT I can't change anything. Maybe I'm missing something?
Comment 10 Patrick Welche 2012-01-30 09:05:14 UTC
Created attachment 56335 [details]
lotest.odb with apparently read-only form pointing at psql db
Comment 11 Patrick Welche 2012-01-30 09:09:57 UTC
(also checked Tools / extension manager, and there is no mention of postgresql. There is an old copy of openoffice 3.2 on the system, and it has postgresql listed.)
Comment 12 Patrick Welche 2012-01-30 09:58:26 UTC
I think I have it: postgresql 8.4 doesn't seem to have aclexplode():

lotest=# select c.relname, c.relnamespace, c.relowner, aclexplode(c.relacl) from pg_catalog.pg_class c;
ERROR:  function aclexplode(aclitem[]) does not exist
LINE 1: select c.relname, c.relnamespace, c.relowner, aclexplode(c.r...
                                                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


Hopefully all will be well with a postgresql 9 :-)
Comment 13 Drew Jensen 2012-01-30 10:15:48 UTC
Great - 

You need to double check this, but I believe the real problem is that prior to 9.0 the code should be using acldefault and 9.0 on use aclexplode. 

Peter Eisentraut wrote:
> I figured the best and most flexible way to address this is to export
> acldefault() as an SQL function and replace
> 
>    aclexplode(proacl)
> 
> with
> 
>    aclexplode(coalesce(proacl, acldefault('f', proowner)))

That above patch was to fix regressions with (some)triggers no longer working after upgrade.

Anyway - if no one minds that the 3.5 only works with pg 9.x and greater and libo3.4 or older only works with pg 8.4 or older I suppose it is a wrap.
Comment 14 Patrick Welche 2012-01-30 11:53:09 UTC
Just upgraded postgresql to
lotest=# select version();
                                                      version                   
                                   
--------------------------------------------------------------------------------
-----------------------------------
 PostgreSQL 9.0.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 2
0080704 (Red Hat 4.1.2-46), 64-bit
(1 row)

(openscg package), which does have aclexplode() and now the form is writeable :-)

I don't have a building libreoffice setup (yet) - do you want to patch pg_databasemetadata.cxx:1716 to add the coalesce()?
Comment 15 Drew Jensen 2012-01-31 08:39:24 UTC
Just to follow up - set up a pg 9.1.2 server last night running on OpenSUSE 12.1 , the LibreOfice 3.5 client still running on Ubuntu 11.04, re-ran the whole shibang again - dataview - single table & query based forms a simple Master/Sub form and a couple of more complicated multi-master/multi sub-form forms - edits work like a charm all the way through.
Comment 16 Lionel Elie Mamane 2012-02-01 03:03:10 UTC
(In reply to comment #13)

> You need to double check this, but I believe the real problem is that prior to
> 9.0 the code should be using acldefault and 9.0 on use aclexplode. 

No, acldefault is available (in SQL, as opposed to in C code) in the current development version (9.2) only.

The story is: PostgreSQL-SDBC does not use information_schema to get privileges information, but looks directly in pg_catalog. The patch you allude to (introducing acldefault) is to fix the bug that an object without any ACL was listed as "nobody has any privilege on it" in information_schema. But that is wrong, since then the default ACL applies. In that case, PostgreSQL-SDBC actually added "all rights to owner", which is the factory-installed state of the default ACL. I was not aware that the default ACL can be changed, too.

So I don't want to just plainly use information_schema, because then with 9.1 and 9.0, LibreOffice will not let you edit data on tables you are owner of if you have never granted any rights to anybody else or removed rights from yourself (i.e. the default ACL applies). But on 9.2, I can plainly use information_schema. My current plan is:

 - do basic privilege interrogation from information_schema
 - if server version < 9.2, add "owner can do anything" on objects with no ACL. This is wrong if the default ACL has been changed, but it is the best we can do.

I'll prepare that for 3.5.1. That should even restore compatibility with PostgreSQL 8.4, but this point needs actual testing.
Comment 17 Drew Jensen 2012-02-01 06:45:26 UTC
Well, hope I didn't send you on too long a wild goose chase then.

As for your plan going forward - sounds good - I'll keep the 8.4 server around for testing against the daily builds as soon as I see your code drop then.

Thanks very much Lionel
Comment 18 Lionel Elie Mamane 2012-02-01 10:06:50 UTC
(In reply to comment #17)

> As for your plan going forward - sounds good - I'll keep the 8.4 server around
> for testing against the daily builds as soon as I see your code drop then.

That change is now in master (3.6), and will thus be in any future daily build (i.e. tomorrow). Please test, and if it works with 8.4, I'll apply it to 3.5.1, too. Thanks.

master commit: 8c922d832792402554cc3dfd174d6d4da50847f4