Open an ODB file connected to a Postgresql server
Open any table with a primary key.
Can not add or edit data
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.
Works for me on my own libreoffice-3-5 compile tree, on Debian GNU/LInux amd64: can edit data.
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
Column | Type | Modifiers
id | integer | not null default nextval('names_id_seq'::regclass)
name | text |
college | integer |
"names_college_fkey" FOREIGN KEY (college) REFERENCES college(id) MATCH FULL
test=# select * from names;
id | name | college
9 | Mickey | 11
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 :-)]
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.
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.
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?
(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
That is correct - purged everything remotely related to lodev/libreoffice, then dpkg -i the .deb packages from the link you quote.
(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.
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;
test=# \c lotest
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"
lotest=# insert into foo (name) values ('Mickey');
INSERT 0 1
lotest=# select * from foo;
id | name
1 | Mickey
Purge all lodev* libreoffice* packages
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?
Created attachment 56335 [details]
lotest.odb with apparently read-only form pointing at psql db
(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.)
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 :-)
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(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.
Just upgraded postgresql to
lotest=# select 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
(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()?
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.
(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.
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
(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
fixed in libreoffice-3-5: