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
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?
(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
@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.
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 !
(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.
(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
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.
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.
@mshiner : I'm setting this to NEEDINFO pending an answer from you with regard to Lionel's questions in comment 7
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.
(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
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
[Automated Action] NeedInfo-To-Unconfirmed
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
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
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
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
I'm closing as notourbug as there were no further updates