Bug 147497 - PostgreSQL direct connection: AutoValue wont be shown in table when created with "generated by default…"
Summary: PostgreSQL direct connection: AutoValue wont be shown in table when created w...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.3.0.3 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-02-17 14:55 UTC by Robert Großkopf
Modified: 2022-08-26 05:57 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2022-02-17 14:55:45 UTC 
Open a PostgreSQL-database with direct connection.
Connect to database through click on the Tables pane.

Start Tools → SQL.
Execute

CREATE TABLE public.distributors (
did
 integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
name
 varchar(40) NOT NULL
);

Close Tools → SQL

Refresh tables and you could see the table.
Open the table for input data.
No AutoValue appears.
Type something in column "name" and set to next row.
'0' appears as "did".
You could input a lot of content and always '0' appears.
You have to reload the table and the values would appear.

Direct connection couldn't handle autovalue created by "Generated by default as identity".
Direct connection doesn't save autovalue through GUI (another bug 60643).
Direct connection will only show autovalue when created as "serial". This is a second (special) way to create such a field in PostgreSQL.
Comment 1 Alex Thurgood 2022-03-23 11:18:49 UTC 
Confirming with

Version: 7.2.6.2 / LibreOffice Community
Build ID: b0ec3a565991f7569a5a7f5d24fed7f52653d754
CPU threads: 8; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded


Isn't this a regression though ? 

I thought that this used to work OK (but maybe I'm wrong), because it was one of the recommended workarounds to the limitations of bug 60643.
Comment 2 Robert Großkopf 2022-03-23 14:29:27 UTC 
(In reply to Alex Thurgood from comment #1)
> 
> Isn't this a regression though ? 
> 
> I thought that this used to work OK (but maybe I'm wrong), because it was
> one of the recommended workarounds to the limitations of bug 60643.

There are two methods to create autovalue fields. One is directly with datatype SERIAL, the other with PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY . The second expression has been added in PostgreSQL 10, so end of 2017. It had been added because it will be used in other database engines in the same way.

AutoValue created by SERIAL will be shown in direct connected PostgreSQL table.
Comment 3 Robert Großkopf 2022-08-26 05:57:34 UTC 
This buggy behavior also will appear when trying to get such an AutoValue by

stNID = oForm.getString(oForm.findColumn("ID"))

in a macro. So I have to switch macros to 

stNID = oForm.getString(oForm.findColumn("ID"))
IF stNID = 0 THEN
   oForm.reload()
   stNID = oForm.getString(oForm.findColumn("ID"))
END IF

This will work when form is sorted by 
"ID" DESC