Bug 137442 - Postgresql schema missing in Table Filter in Ubuntu LibreOffice 6.4.6-0ubuntu0.20.04.1
Summary: Postgresql schema missing in Table Filter in Ubuntu LibreOffice 6.4.6-0ubuntu...
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.4.6.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-13 09:22 UTC by mshiner
Modified: 2021-07-27 12:20 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Base showing missing Schema from Ubuntu (164.41 KB, image/png)
2020-11-02 14:48 UTC, mshiner
Details
Screenshot Windows10 (52.81 KB, image/png)
2020-11-02 14:51 UTC, mshiner
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mshiner 2020-10-13 09:22:10 UTC
Description:
Using postgreSQL froma base database.

Previously the table filter listed all schemas in the db. However the backend has a 'reports' schema which does not show in the table filer dialog.

Looks like a problem with the filter dialog as the reports schema is accessible if no filter is applied.



Steps to Reproduce:
1. New base database
2. Connect to an existing postgresql database that has a schema called reports 
3. Select table filter from menu

Actual Results:
No reports schema listed in table filter dialog but all other schemas show. This is a change from previous behaviour.

Expected Results:
All schemas listed for selection/deselection including a schema named reports


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 6.4.6.2
Build ID: 1:6.4.6-0ubuntu0.20.04.1
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-US
Calc: threaded
Comment 1 Robert Großkopf 2020-10-28 18:44:30 UTC
Which connection to PostgreSQL do you use? Direct, JDBC or ODBC?

Do I have only to run
CREATE SCHEMA IF NOT EXISTS reports 
to see the behavior?
Comment 2 mshiner 2020-10-29 07:34:28 UTC
(In reply to Robert Großkopf from comment #1)
> Which connection to PostgreSQL do you use? Direct, JDBC or ODBC?
> 
> Do I have only to run
> CREATE SCHEMA IF NOT EXISTS reports 
> to see the behavior?

Robert 

I am connecting to the open source uzERP ERP system - I think you may be able to get a demo database here:

https://github.com/uzerpllp

Direct connection in the form 

host=[uzerphost] dbname=[uzerp] 

The postgreSQL backend db in question has the following

archive
information_schema
pg_catalog
public
reports
user_tables
validate

when I open the 'base' file they all appear but if I try to user table filter the reports schema then doesn't show.

M
Comment 3 Alex Thurgood 2020-10-29 09:06:23 UTC
@mshiner : my understanding of 

https://doc.uzerp.com/faq/power-user/

is that the reports schema is for server-side views.

I could be wrong, but I don't think the table filter dialog takes into account views as tables with the postgresql driver.

You might find a reading of bug 67649 helpful (or not) regarding how SDBC functionality considers server side views.
Comment 4 Alex Thurgood 2020-10-29 09:13:10 UTC
Another thought I had was whether the table filter code in some way relied on a keyword constraint for table names and thus considered the word "reports" a reserved name and not a possible table schema - I would hope not !
Comment 5 mshiner 2020-10-29 09:34:03 UTC
(In reply to Alex Thurgood from comment #3)
> @mshiner : my understanding of 
> 
> https://doc.uzerp.com/faq/power-user/
> 
> is that the reports schema is for server-side views.
> 
> I could be wrong, but I don't think the table filter dialog takes into
> account views as tables with the postgresql driver.
> 
> You might find a reading of bug 67649 helpful (or not) regarding how SDBC
> functionality considers server side views.

Alex

The reports schema does contain server side views, as does the public schema..... this has never been a problem in the past with this database.

I tripped this over as I added a new server side view within the reports schema and it didn't show in calc. So I went to the base file to amend the table filter (which had been previously set to only expose certain views from public and reports) and the reports schema disappeared.
Comment 6 mshiner 2020-10-29 09:35:53 UTC
(In reply to Alex Thurgood from comment #4)
> Another thought I had was whether the table filter code in some way relied
> on a keyword constraint for table names and thus considered the word
> "reports" a reserved name and not a possible table schema - I would hope not
> !

Your conclusion was where I got to as all other schema are OK.

M
Comment 7 Lionel Elie Mamane 2020-10-29 11:55:48 UTC
You report that this worked correctly in a previous version, i.e. this is a regression. Can you please specify the latest version where you have seen this worked correctly?

Can you reproduce the problem on a freshly created database? Can you isolate what condition makes the schema not shown in LibreOffice? Is it the name? Is it the fact that it has only views and no table?

As far as I remember, the pgsql-sdbc code actually doesn't differentiate between views and tables, it considers them all as tables.
Comment 8 Robert Großkopf 2020-10-29 18:47:39 UTC
I have created some other schemas for my test-database in PostgreSQL. All schemas are shown when I have added a table to the schema. Also the schema "reports" I created. Schema aren't shown if there isn't created a table inside the schema. But you could use this schema in table editor of the GUI.

Tested with LO 7.0.3.1 and direct connection and JDBC, also with LO 6.3.5.2 and 6.4.6.2 with the same connections. Could see all schemas with content. So I can't reproduce this bug under OpenSUSE 15.1 64 bit rpm Linux.

Please try to test the same behavior with packages of LO directly. You are using the packages of Ubuntu, which could be different to the packages of LO.
Comment 9 Alex Thurgood 2020-10-30 11:53:09 UTC
@mshiner : I'm setting this to NEEDINFO pending an answer from you with regard to Lionel's questions in comment 7
Comment 10 Alex Thurgood 2020-10-30 11:56:39 UTC
Also, as Robert has mentioned, he couldn't reproduce this on his Linux (OpenSuse) distribution, so the problem may indeed lie with the Ubuntu packets (wouldn't be the first time). 

Please consider testing with the latest production release versions of TDF provided LibreOffice installation packets.
Comment 11 mshiner 2020-10-30 13:29:29 UTC
(In reply to Lionel Elie Mamane from comment #7)
> You report that this worked correctly in a previous version, i.e. this is a
> regression. Can you please specify the latest version where you have seen
> this worked correctly?
> 
> Can you reproduce the problem on a freshly created database? Can you isolate
> what condition makes the schema not shown in LibreOffice? Is it the name? Is
> it the fact that it has only views and no table?
> 
> As far as I remember, the pgsql-sdbc code actually doesn't differentiate
> between views and tables, it considers them all as tables.

Yes all tables and views were OK in the past.

Can't say when I last updated a view so I wouldn't be able to pinpoint when it last worked.

Sorry.

M
Comment 12 mshiner 2020-10-30 13:31:07 UTC
Alex, Robert, Lionel

I'll try and pull the official rather than ubuntu version and give it another test next week.

Thanks for all your help.

M
Comment 13 QA Administrators 2020-10-31 04:36:23 UTC Comment hidden (obsolete)
Comment 14 mshiner 2020-11-02 14:48:27 UTC
Created attachment 166939 [details]
Base showing missing Schema from Ubuntu

Here is a screenshot of the table filter from Version: 6.4.6.2
Build ID: 1:6.4.6-0ubuntu0.20.04.1
Comment 15 mshiner 2020-11-02 14:51:09 UTC
Created attachment 166940 [details]
Screenshot Windows10

Here is the table filter under 7.0.3.1 (x64) from a Windows 10 virtual machine.

As you can see the reports schema is showing in the tables list to the left but not in the table filter dialog.

Martyn
Comment 16 mshiner 2020-11-02 15:22:10 UTC
OK

This is now officially weird.

I did as you asked and set up a fresh, test database on my localhost. I added a new schema called reports plus a table in the public schema and a view based on this table in reports.

All are visible in Base in the and the table filter works OK.

Obviously there must be something about the pgsql database that is causing this - if you leave this open I will report back with anything I have in case it is useful to someone else.

Martyn
Comment 17 mshiner 2020-11-02 15:48:21 UTC
Even more weird....

I took a copy of a live-in-production-use uzERP db and put it on my local pgsql server....

#1 The reports schema does not show in the table filter but is in the tables list as before.

#2 I then changed the name of the schema to myreports and 'ta-da' it appears in the main list AND the table filter list - so I figure it can't be access permissions or some other problem related to the role I'm using because I didn't change any of that only the name.

#3 I then change the schema name back to reports to see the effect (thinking that maybe the act of changing the name fixes something) and - WHAT! - it disappears.

#4 So I then change the name of the ORIGINAL reports schema again to something else (as in #2 in appears in the table filter) and create a NEW reports schema which does not appear in the table filter.

Beats me what is going on here.

M
Comment 18 Buovjaga 2021-07-27 12:20:36 UTC
I'm closing as notourbug as there were no further updates