Postgres: general setting 'show all databases' doesn't work, only the default database is shown #20602

dbeaverforpostgres · 2023-07-19T12:34:58Z

Description

In the preferences under Connections/Drivers/PostgreSQL 'show all databases' and 'show databases not available for connection' is ticked.
When creating a new connection you would assume that automatically all databases would be shown but that is not the case.
Nevertheless, if I check the connection settings of a connection I just created, under the PostgreSQL tab indeed 'show all databases' is ticked. Why doesn't it become visible?
So I went to have a look at data-sources.json and saw that the driver properties part is missing.
The strange thing is when I edit the connection , save and reconnect suddenly the driver properties part appears:
e.g.
"provider-properties": {
"@dbeaver-show-non-default-db@": "true",
"@dbeaver-show-template-db@": "false",
"@dbeaver-show-unavailable-db@": "false",
"show-database-statistics": "true",
"@dbeaver-read-all-data-types-db@": "false",
"read-keys-with-columns": "false",
"@dbeaver-use-prepared-statements-db@": "false",
"postgresql.dd.plain.string": "false",
"postgresql.dd.tag.string": "false"
}
Why do I need to edit the connection to get the provider properties part in the data-sources.json?
So as a workaround I figured, I could adapt the drivers.xml and add parameter lines in the driver id part. But I don't know if it would help and if so what the syntax would be?
I also checked the debug logging and there it states that it will start up the default database, no errors

DBeaver Version

Community edition 23.0.2

Operating System

windows

Database and driver

postgresql-42.6.0.jar

Steps to reproduce

In the preferences under Connections/Drivers/PostgreSQL 'show all databases' and 'show databases not available for connection' is ticked.
When creating a new connection you would assume that automatically all databases would be shown but that is not the case.
Nevertheless, if I check the connection settings of a connection I just created, under the PostgreSQL tab indeed 'show all databases' is ticked. Why doesn't it become visible?
So I went to have a look at data-sources.json and saw that the driver properties part is missing.
The strange thing is when I edit the connection , save and reconnect suddenly the driver properties part appears:
e.g.
"provider-properties": {
"@dbeaver-show-non-default-db@": "true",
"@dbeaver-show-template-db@": "false",
"@dbeaver-show-unavailable-db@": "false",
"show-database-statistics": "true",
"@dbeaver-read-all-data-types-db@": "false",
"read-keys-with-columns": "false",
"@dbeaver-use-prepared-statements-db@": "false",
"postgresql.dd.plain.string": "false",
"postgresql.dd.tag.string": "false"
}
Why do I need to edit the connection to get the provider properties part in the data-sources.json?
So as a workaround I figured, I might need to adapt the drivers.xml and add parameter lines in the driver id part. I don't know if it would help and if so what the syntax would be?

Additional context

No response

LonwoLonwo · 2023-07-20T06:58:21Z

Hello @dbeaverforpostgres

Do you have a connection with manual or URL type?

Additionally, I can't reproduce the issue. If "Show all databases" is ticked in the general preferences -> then "Show all databases" is also ticked for new connections.

dbeaverforpostgres · 2023-07-20T11:36:43Z

Indeed, we connect exactly as indicated above. Some additional information about the drivers. Since I don't have direct access to the open internet I don't use the maven default but put the driver postgresql-42.6.0.jar locally and refer to it in the drivers.xml file

dbeaverforpostgres · 2023-07-20T11:52:01Z

Do you know in which file the general options information like "show all databases" are stored so I can see if there's anything missing?

LonwoLonwo · 2023-07-20T14:03:43Z

Hello @dbeaverforpostgres

You can take a look on the workspace directory under the following path: .metadata/.plugins/org.eclipse.core.runtime/.settings

I have this setting in the org.jkiss.dbeaver.core.prefs file

dbeaverforpostgres · 2023-07-26T07:26:31Z

Thank you a lot for your response. I had a look but apparantly the 'show non default db' setting is set. Nevertheless I need to alter a connection before all databases come visible. Here is the list of all the settings in org.jkiss.dbeaver.core.prefs

@dbeaver-read-all-data-types-db@=false
@dbeaver-show-non-default-db@=true
@dbeaver-show-template-db@=false
@dbeaver-show-unavailable-db@=true
database.editor.separate.connection=NEVER
database.meta.separate.connection=NEVER
driver.selector.orderBy=score
eclipse.preferences.version=1
org.jkiss.dbeaver.core.confirm.filter_resultset=always
read-keys-with-columns=false
show-database-statistics=true
swt.client.browser=EDGE
tipOfTheDayInitializer.notFirstRun=true
transaction.auto.close.enabled=true
transaction.auto.close.ttl=10800
ui.auto.update.check=false
ui.auto.update.check.time=1681208255161
ui.drivers.home=C:\Users\u45368\AppData\Roaming\DBeaverData\drivers
ui.render.boolean.style.checked.align=CENTER
ui.render.boolean.style.checked.color=default
ui.render.boolean.style.checked.font=NORMAL
ui.render.boolean.style.checked.text=[v]
ui.render.boolean.style.mode=TEXT
ui.render.boolean.style.null.align=CENTER
ui.render.boolean.style.null.color=default
ui.render.boolean.style.null.font=NORMAL
ui.render.boolean.style.null.text=[NULL]
ui.render.boolean.style.unchecked.align=CENTER
ui.render.boolean.style.unchecked.color=default
ui.render.boolean.style.unchecked.font=NORMAL
ui.render.boolean.style.unchecked.text=[\u2000]
ui.show.tip.of.the.day.on.startup=false

dbeaverforpostgres · 2023-07-27T08:03:52Z

I thought I got it working when I used an old file instead of the one above but in fact only when editing the connection I get to view all the databases. Same as above

@dbeaver-read-all-data-types-db@=false
@dbeaver-show-non-default-db@=true
@dbeaver-show-template-db@=false
@dbeaver-show-unavailable-db@=false
database.editor.separate.connection=false
database.meta.separate.connection=false
driver.selector.orderBy=score
eclipse.preferences.version=1
org.jkiss.dbeaver.core.confirm.close_result_tabs=prompt
org.jkiss.dbeaver.core.confirm.close_running_query=prompt
org.jkiss.dbeaver.core.confirm.dangerous_sql=prompt
org.jkiss.dbeaver.core.confirm.disconnect_txn=prompt
org.jkiss.dbeaver.core.confirm.driver_download=prompt
org.jkiss.dbeaver.core.confirm.entity_delete=prompt
org.jkiss.dbeaver.core.confirm.entity_reject=prompt
org.jkiss.dbeaver.core.confirm.entity_revert=prompt
org.jkiss.dbeaver.core.confirm.exit=prompt
org.jkiss.dbeaver.core.confirm.fetch_all_rows=prompt
org.jkiss.dbeaver.core.confirm.keep_statement_open=prompt
org.jkiss.dbeaver.core.confirm.mass_parallel_sql=prompt
org.jkiss.dbeaver.core.confirm.order_resultset=prompt
org.jkiss.dbeaver.core.confirm.version_check=prompt
resultset.presentation.active=plaintext
show-database-statistics=true
tipOfTheDayInitializer.notFirstRun=true
transaction.auto.close.enabled=true
transaction.auto.close.ttl=10800
ui.auto.update.check=false
ui.drivers.home=C:\Users\u45368\AppData\Roaming\DBeaverData\drivers
ui.render.boolean.style.checked.align=CENTER
ui.render.boolean.style.checked.color=default
ui.render.boolean.style.checked.font=NORMAL
ui.render.boolean.style.checked.text=[v]
ui.render.boolean.style.mode=TEXT
ui.render.boolean.style.null.align=CENTER
ui.render.boolean.style.null.color=default
ui.render.boolean.style.null.font=NORMAL
ui.render.boolean.style.null.text=[NULL]
ui.render.boolean.style.unchecked.align=CENTER
ui.render.boolean.style.unchecked.color=default
ui.render.boolean.style.unchecked.font=NORMAL
ui.render.boolean.style.unchecked.text=[\u2000]
ui.show.tip.of.the.day.on.startup=false

E1izabeth · 2023-07-28T10:08:50Z

I reproduced the issue this way:

Check Show all databases in preferences, if it's not
Create new connection, check that Show all databases checked, but don't delete default database postgres in Database field in Connection settings
Connect and see only specified database - postgres

However, if I clean Database field, it works - several databases shown in navigator.

E1izabeth · 2023-07-28T10:14:09Z

When I tried to expand database postgres in navigator, I caught several the same errors:

java.lang.IllegalStateException: No databases found on the server
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDataSource.getDefaultInstance(PostgreDataSource.java:641)
	at org.jkiss.dbeaver.ext.postgresql.model.PostgreDataSource.getDefaultInstance(PostgreDataSource.java:1)
	at org.jkiss.dbeaver.model.DBUtils.getObjectOwnerInstance(DBUtils.java:2133)
	at org.jkiss.dbeaver.model.DBUtils.getDefaultContext(DBUtils.java:2140)
	at org.jkiss.dbeaver.model.navigator.DBNUtils.isDefaultElement(DBNUtils.java:164)
	at org.jkiss.dbeaver.ui.navigator.database.DatabaseNavigatorLabelProvider.getFont(DatabaseNavigatorLabelProvider.java:152)
	at org.eclipse.jface.viewers.ColumnLabelProvider.update(ColumnLabelProvider.java:45)
	at org.eclipse.jface.viewers.ViewerColumn.refresh(ViewerColumn.java:144)
	at org.eclipse.jface.viewers.AbstractTreeViewer.doUpdateItem(AbstractTreeViewer.java:970)
	at org.eclipse.jface.viewers.AbstractTreeViewer$UpdateItemSafeRunnable.run(AbstractTreeViewer.java:126)
	at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:45)

dbeaverforpostgres · 2023-09-06T06:59:03Z

The above workaround might work for an existing connection but not a new connection because it takes the username as databasename which doesn't work either. I'd rather wait till this issue is solved before upgrading to the new version.
It would be nice to have an update here when there's a new version available tackling this bug.

LonwoLonwo · 2023-09-06T15:06:12Z

It would be nice to have an update here when there's a new version available tackling this bug.

Hello @dbeaverforpostgres

This info is unavailable. We do not know also.

If something changes, you will see it in the Milestone section of this ticket.

muhmudrik · 2024-01-10T08:56:34Z

Hello @dbeaverforpostgres

Do you have a connection with manual or URL type?

Additionally, I can't reproduce the issue. If "Show all databases" is ticked in the general preferences -> then "Show all databases" is also ticked for new connections.

Hi, i would like to provide some extra information about this. I can reproduce the issue either when creating new connection or editing existing connection. The issue also happens when i use the Connect by URL without filling the database name.

It can be seen below that it only shows the postgres database even though i already checked the Show all databases option.

But if i change to Connect by Host an emptying the database field, it will shows all the database.

Hope this helps

E1izabeth · 2024-01-10T08:58:22Z

@muhmudrik thank you

LonwoLonwo · 2024-01-10T11:19:44Z

Hello @muhmudrik

This is expected behavior (for now).

dbeaverforpostgres · 2024-01-16T13:29:40Z

Hello everyone, first of all, thanks a lot for your input. I haven't had time to play around for a while but I gave it another try now. I'll add some screenshots to make it clearer. Some strange things happen which can hardly be seen as expected behaviour.
We compare 2 situations: both have the general settings configured

then I configured 2 connections. The only difference between the two is that I opened the PostgreSQL tab (I haven't even changed any option)

Here's how I set up the connection for both of them

The strange thing is that for the connection postgres with opening the PostgreSQL tab is showing all the connections and the other one only the default.

I would expect by ticking show all databases in preferences that it is sufficient to see all databases and that I don't need to open the postgresqltab. This is a bug. It would work if you don't set the general preferences which means you have to set them for each connection individually since you need to open the PostgreSQL tab then to tick the box 'show all databases' but this is not something you would expect and is very confusing.

doruiulian · 2024-01-16T15:24:08Z

I can confirm this behaviour . Even if in the General preferences "Show all databases" is ticked, Connections won't show all the databases in an instance unless we first open the Connection Settings - PostgreSQL tab for new or existing connections.
In the case of the first connection from the picture(postgres) i went to the PostgreSQL tab then was able to see all the other databases ; for the 2nd connection(postgres2) i didn't do it and left it by default again with the "Show all databases" in preferences ticked.

Both DBeaver Community edition and Enterprise edition present the same behaviour.

* dbeaver/pro#2236 add an ability to send all messages to AI * #20602 remove excessive changes --------- Co-authored-by: Matvey16 <82543000+Matvey16@users.noreply.github.com>

dbeaverforpostgres · 2024-01-29T14:34:45Z

Could you give me an idea of the next steps and what the timeline is for putting this bugfix in production?

arhayka · 2024-02-27T16:38:59Z

@dbeaverforpostgres We hope to fix this issue in one of the next sprints. But we can't guarantee that it will be fixed.

dbeaverforpostgres · 2024-04-22T13:59:54Z

@arhayka, I see label "blocked" was added but unfortunately there hasn't been any response yet since Mar 4. Any idea how we can get this issue going again?

dbeaverforpostgres added bug wait for review labels Jul 19, 2023

LonwoLonwo added the x:postgresql label Jul 20, 2023

LonwoLonwo added wait for response and removed wait for review labels Jul 20, 2023

LonwoLonwo added wait for review and removed wait for response labels Jul 26, 2023

iraidaa assigned E1izabeth Jul 27, 2023

E1izabeth removed their assignment Jul 28, 2023

LonwoLonwo added xp:normal xf:metadata and removed wait for review labels Jul 28, 2023

arhayka added this to the 23.3.4 milestone Dec 14, 2023

arhayka added the sp:2 label Jan 22, 2024

arhayka assigned Destrolaric Jan 22, 2024

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 read from global properties

23a3dc7

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 fix reading global properties

8433959

Destrolaric mentioned this issue Jan 23, 2024

dbeaver/pro#2365 read global properties if new connection #22561

Closed

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 apply global properties from the main page instead

baa1e88

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 only on new connection wizard

ce5036f

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 remove excessive changes

be630c6

Destrolaric added a commit that referenced this issue Jan 23, 2024

#20602 fix imports

cb2c84e

Destrolaric added a commit that referenced this issue Jan 24, 2024

#20602 remove excessive changes

5e44cb8

Destrolaric linked a pull request Jan 29, 2024 that will close this issue

dbeaver/dbeaver#20602 load controls for extra pages #22616

Open

Destrolaric added a commit that referenced this issue Jan 29, 2024

#20602 load controls for extra pages

782a9e9

Destrolaric added a commit that referenced this issue Jan 31, 2024

Merge branch 'devel' into #20602-load-controls-for-extra-pages

9bf851f

arhayka modified the milestones: 23.3.4, 23.3.5 Feb 5, 2024

arhayka removed this from the 23.3.5 milestone Feb 19, 2024

arhayka added this to the 24.0.1 milestone Feb 27, 2024

arhayka added the blocked label Mar 4, 2024

arhayka modified the milestones: 24.0.1, 24.0.2 Mar 4, 2024

arhayka removed this from the 24.0.2 milestone Mar 24, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Postgres: general setting 'show all databases' doesn't work, only the default database is shown #20602

Postgres: general setting 'show all databases' doesn't work, only the default database is shown #20602

dbeaverforpostgres commented Jul 19, 2023

LonwoLonwo commented Jul 20, 2023

dbeaverforpostgres commented Jul 20, 2023

dbeaverforpostgres commented Jul 20, 2023

LonwoLonwo commented Jul 20, 2023

dbeaverforpostgres commented Jul 26, 2023

dbeaverforpostgres commented Jul 27, 2023 •

edited

E1izabeth commented Jul 28, 2023

E1izabeth commented Jul 28, 2023 •

edited

dbeaverforpostgres commented Sep 6, 2023

LonwoLonwo commented Sep 6, 2023

muhmudrik commented Jan 10, 2024 •

edited

E1izabeth commented Jan 10, 2024

LonwoLonwo commented Jan 10, 2024

dbeaverforpostgres commented Jan 16, 2024

doruiulian commented Jan 16, 2024 •

edited

dbeaverforpostgres commented Jan 29, 2024

arhayka commented Feb 27, 2024

dbeaverforpostgres commented Apr 22, 2024

Postgres: general setting 'show all databases' doesn't work, only the default database is shown #20602

Postgres: general setting 'show all databases' doesn't work, only the default database is shown #20602

Comments

dbeaverforpostgres commented Jul 19, 2023

Description

DBeaver Version

Operating System

Database and driver

Steps to reproduce

Additional context

LonwoLonwo commented Jul 20, 2023

dbeaverforpostgres commented Jul 20, 2023

dbeaverforpostgres commented Jul 20, 2023

LonwoLonwo commented Jul 20, 2023

dbeaverforpostgres commented Jul 26, 2023

dbeaverforpostgres commented Jul 27, 2023 • edited

E1izabeth commented Jul 28, 2023

E1izabeth commented Jul 28, 2023 • edited

dbeaverforpostgres commented Sep 6, 2023

LonwoLonwo commented Sep 6, 2023

muhmudrik commented Jan 10, 2024 • edited

E1izabeth commented Jan 10, 2024

LonwoLonwo commented Jan 10, 2024

dbeaverforpostgres commented Jan 16, 2024

doruiulian commented Jan 16, 2024 • edited

dbeaverforpostgres commented Jan 29, 2024

arhayka commented Feb 27, 2024

dbeaverforpostgres commented Apr 22, 2024

dbeaverforpostgres commented Jul 27, 2023 •

edited

E1izabeth commented Jul 28, 2023 •

edited

muhmudrik commented Jan 10, 2024 •

edited

doruiulian commented Jan 16, 2024 •

edited