Some more info:

My tables don't have oid's.

When I create a new table with oid's, one serial column used as key and another column with text newly created records are retrieved correctly.

Looking here http://www.openoffice.org/dba/drivers/postgresql/index.html I find the following remarks on the old pre-3.3 sdbc driver:

...
Support for serial (= auto increment ) datatype is difficult to implement, because it is not really a type in postgresql.
...
Additionally it is difficult to retrieve the generated value after an insertion in such a table. The driver follows two strategies here. When the table supports posgresql oids, it uses oid to find the just inserted row again. In case it does not, it queries for the structure of the primary key of the table and uses the curr_val() function to retrieve the last increment of the serial in this session.

This is a little slow (because these reflection queries are executed for every insertion). It might be sped up with cashing the reflection data when too many people complain.
o-o-o

Seems like our new driver does not use curr_val (which should actually be currval)?

Ferry

I had that *once* and now I cannot reproduce anymore. Maybe I mistakenly launched another LibO version the one time I got the problem, I'm not sure. My tables don't have oids either.

The "new" driver is continuation of "old" driver; I don't remember changing anything related to autoincrement values, so it should do the same as the old driver, including using currval().

The OpenOffice bug has been closed saying:

 with the latest version of the postgresql-driver

 http://dba.openoffice.org/drivers/postgresql/index.html ,

 you can create tables with serial columns (by checking the auto increment flag)
 and you can insert data in the beamer window as long as the table has oids.

So definitely this bug not fixed in OpenOffice in the past :)

In future please leave bugs in "UNCONFIRMED" state, not "NEW", until *another* user/tester also reproduced the bug.

Could you see if you get the same problem with LibO 3.5.0rc2 or later? Thanks. Make sure to use the bundled postgresql-sdbc (it is not an extension anymore, but an optionally installed component) and not an older extension.

With 3.4.4, which version of postgresql-sdbc extension from which source do you use?

Is the auto increment field shown as "<AutoField>" in insert row on the sheet view?


Please give me more detailed reproduction instructions:

 - postgresql server version

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

 - which table / query / ... do you try to add data to, 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/? Distro-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?

With the old driver it was something you needed to configure in the driver settings (you had a settings menu item, other things were configurable too).

Afaik the driver did not have this problem, as some of our tables have oids and other not and it was working well for a long time.

Following your link and searching for oid shows for > 0.7:
data can now be inserted into tables without oids and the addition gets correctly reflected in the UI. This also holds for tables with auto increment values in the primary key. In former versions, this was only the case for tables with oids. However the current solution requires some additional reflection queries, which makes it a little slow and resource consuming.

The version of LO I use if from Oneric (3.4.4-0ubuntu1) with sdbc driver 0.8.0+LibO3.4.4-0ubuntu1.

Right now I have a Table without oids where it works. 

Let me figure out the exact difference between the table with problems and the one where it works. I one case postgres is 9.0 and the other 8.4.
Also the working table has for the key field:
"ID" integer NOT NULL DEFAULT nextval(('public."test_seq"'::text)::regclass)


Don't know why the type casts are used here.

More info will follow.

Ferry

(In reply to comment #3)
> With the old driver it was something you needed to configure in the driver
> settings (you had a settings menu item, other things were configurable too).

I did not knowingly remove such a thing. Did you have this menu with 0.7.6b?

> Right now I have a Table without oids where it works. 

> Let me figure out the exact difference between the table with problems and the
> one where it works. I one case postgres is 9.0 and the other 8.4.
> Also the working table has for the key field:
> "ID" integer NOT NULL DEFAULT nextval(('public."test_seq"'::text)::regclass)

Yes, there might be something about PostgreSQL-SDBC choking on the DEFAULT value for the key field, and/or acting correctly only if the field is marked "NOT NULL" or some such. Let me know your findings.

> Don't know why the type casts are used here.

AFAIK, the effect of that is that the test_seq sequence is looked up at runtime instead of being statically bound by OID number.

So if you do "DROP SEQUENCE test_seq" and then "CREATE SEQUENCE test_seq", then the new sequence will be used, which would not be the case without the "::text" cast.

What I find until now is:
- I don't notice a difference between postgres 9.1 and 8.4
- on a new table with or without oid's it works fine
- there does not seem to be a fundamental difference between my old table that does not work and the new one.

I'm puzzled.

The old table has the ID field defined last, the new table has it first.

For some reason displaying the data in the new table using table view shows the data in the order entered, which is the same as the autofield order. However the old table starts by displaying the last half of the table and the rest more or less unordered.

I need to take a further look.

Ferry

It indeed seems to be related to the position of the ID field in the definition of the table. I copied my old table NietLeden to NietLeden2 by dragging in LO and moved the ID field first before clicking OK. Then added a sequence and constraint in PGdmin as the ID field had changed from SERIAL to INTEGER.

The new table works, the old one has a problem.

This problem may have existed in prior versions of the driver (if not elsewhere in Base code).

Here are the table definitions from pgadmin:
CREATE TABLE "NietLeden"
(
  "Achternaam" character varying(255),
  "Voorletters" character varying(10),
  "Roepnaam" character varying(20),
  "Adres" character varying(255),
  "Postcode" character varying(10),
  "Woonplaats" character varying(50),
  "Telefoonnummer1" character varying(15),
  "Telefoonnummer2" character varying(15),
  "E-mail" character varying(100),
  "E-mail2" character varying(100),
  "ID" serial NOT NULL,
  CONSTRAINT "pky_NietLeden" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "NietLeden" OWNER TO ferry;

CREATE TABLE "NietLeden2"
(
  "ID" integer NOT NULL DEFAULT nextval('"Temp_serial"'::regclass),
  "Achternaam" character varying(255),
  "Voorletters" character varying(10),
  "Roepnaam" character varying(20),
  "Adres" character varying(255),
  "Postcode" character varying(10),
  "Woonplaats" character varying(50),
  "Telefoonnummer1" character varying(15),
  "Telefoonnummer2" character varying(15),
  "E-mail" character varying(100),
  "E-mail2" character varying(100),
  CONSTRAINT tmp_idx PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "NietLeden2" OWNER TO ferry;

OK, thanks for the information. I'll try to fix this in time for 3.5.1

Lionel,

Were you able to reproduce it? Let me know if I can help!

Ferry

OK, reproduced with plain 3.5.0rc3

Strange bug.

Now I wish I had created my tables with the auto increment field in the first position at start.

I just tried to work around by creating a view in PGAdmin, but then I get a non editable table in LO. I thought we were able to add rows via simple queries?

Looks like I just need to wait patiently until this is fixed.

Ferry

I tested on Debian GNU/Linux amd64 with:
 3.4.4 (Debian package)
 3.5.0rc3 (from http://www.libreoffice.org/download/pre-releases/)
 self-compiled 3.5 development tree

The only way I can reproduce is on a table where, at the time the .odb file connected to the database and loaded the list of tables, the user did not have "SELECT" privilege on that table. Then (with psql) I grant the SELECT (and INSERT if not already there) privilege and then open the table in LibO. Then I get that bug. If I take menu "view / refresh table", then the problem disappears.

This is independent of the position of the ID column.

So, for example ("=>" is the psql prompt):

1) psql -U user1
2) => CREATE TABLE foo (...)
3) start LibO
4) connect to database with libO as user2 (after opening corresponding odb file, click on "Tables"; you see the list of tables)
5) => GRANT SELECT, INSERT ON foo TO user2
6) LibO: open table foo
7) LibO: insert data
8) Indeed, ID column shows 0
9) close table
10) view/refresh tables
11) open table foo
12) insert data
13) no problem


Also:

1) psql -U user1
2) => CREATE TABLE foo (...)
3) => GRANT SELECT, INSERT ON foo TO user2
4) start LibO
5) connect to database with libO as user2 (after opening corresponding odb file, click on "Tables"; you see the list of tables)
6) LibO: open table foo
7) LibO: insert data
8) no problem



Because this bug happens only in *very* specific circumstances and is easily worked around, I set it to low priority and minor severity.

Ferry, please double-check that you get the problem in other circumstances than described above. If you do get it in wider circumstances, I'll raise the severity/priority back. In this case, please give me more information to reproduce, such as:
 - complete pg_dump of database you get the problem on (as an attachment); I suggest you create a "toy" database with just enough (fake) data and tables to reproduce the problem
 - failing the above, at least the table structure, ACLs on the tables and ACLs on the columns
 - what user you connect as to get the problem

(In reply to comment #10)

> I just tried to work around by creating a view in PGAdmin, but then I get a non
> editable table in LO. I thought we were able to add rows via simple queries?

PostgreSQL does not support editable views. You can fake them with rewrite rules. See http://www.postgresql.org/docs/9.1/static/rules-views.html#RULES-VIEWS-UPDATE

Don't forget to give the user SELECT/INSERT privileges on the view (not on the underlying table).

Anyway, in my experience this does not depend on the order of the columns in the table.

Lionel,

I tried View/Refresh tables with no effect.

The Table I tried is 'NietLeden'. PG Admin shows it is defined as:
CREATE TABLE "NietLeden"
(
  "Achternaam" character varying(255),
  "Voorletters" character varying(10),
  "Roepnaam" character varying(20),
  "Adres" character varying(255),
  "Postcode" character varying(10),
  "Woonplaats" character varying(50),
  "Telefoonnummer1" character varying(15),
  "Telefoonnummer2" character varying(15),
  "E-mail" character varying(100),
  "E-mail2" character varying(100),
  "ID" serial NOT NULL,
  CONSTRAINT "pky_NietLeden" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "NietLeden" OWNER TO ferry;
GRANT ALL ON TABLE "NietLeden" TO ferry;
GRANT ALL ON TABLE "NietLeden" TO public;

I login from LO as user ferry.

I will try to extract this table to a new database, backup this database with pgadmin, create new odb and upload.

Ferry

Created attachment 56775 [details]
postgres compressed table backup

This binary contains a postgres back of a table in format compressed taken with PG Admin. Restore it into any database, schema public.

Created attachment 56776 [details]
odb to connect to the backupped database

After restoring the table use this odb. You need to change the database connection to yours. If you restored into schema public it should work.

Forgot one thing, I log in as user ferry, not sure if you need to create that user prior to restoring the table.

Just tested the restore procedure myself and the problem still happens.

Ferry

OK, could reproduce in 3.4 pre-merge PostgreSQL-SDBC extension.
Already fixed in 3.5.0.

In 3.4.6, the value shown in NULL and not 0, but the essence of this bug stays.