BUG: Client stuck on "loading foreign keys" step and flooding server with queries

[loic-simon]

Describe the bug

I encountered a major issue happening when (re)connecting to a database: the loading stucks a long time on "Loading foreign keys..." step.

It usually resolves in a dozens of seconds, but sometimes it keeps struck forever. Closing and re-opening the app fix it.

I initially thought it was a minor bug, just a little annoying, before we had to investigate with my team an issue of very high CPU usage on our development Postgres cluster... and we finally found out it was caused by my dbgate client flooding it with the following request:

select 
	fk.constraint_name as "constraint_name",
	fk.constraint_schema as "constraint_schema",
	base.table_name as "pure_name",
	base.table_schema as "schema_name",
	fk.update_rule as "update_action",
	fk.delete_rule as "delete_action",
	ref.table_name as "ref_table_name",
	ref.table_schema as "ref_schema_name",
	basecol.column_name as "column_name",
	refcol.column_name as "ref_column_name"
from information_schema.referential_constraints fk
inner join information_schema.table_constraints base on fk.constraint_name = base.constraint_name and fk.constraint_schema = base.constraint_schema
inner join information_schema.table_constraints ref on fk.unique_constraint_name = ref.constraint_name and fk.unique_constraint_schema = ref.constraint_schema 
inner join information_schema.key_column_usage basecol on base.table_name = basecol.table_name and base.constraint_name = basecol.constraint_name
inner join information_schema.key_column_usage refcol on ref.table_name = refcol.table_name and ref.constraint_name = refcol.constraint_name and basecol.ordinal_position = refcol.ordinal_position
where 
		base.table_schema <> 'information_schema' 
		and base.table_schema <> 'pg_catalog' 
		and base.table_schema !~ '^pg_toast' 
		and ('tables:' || base.table_schema || '.' || base.table_name)  = '0'
order by basecol.ordinal_position

See attached screenshots of CPU usages, corresponding to times DBeaver was running (and stuck), before I noticed it.

To Reproduce

It looks like it happens only after a long time with a database connexion, although I'm not really sure; it looks pretty random to me.

No need to say I consider this as a pretty critical bug that can have severe consequences on a system!

Expected behavior

Do not stuck on a loading step, and if even, do not flood server with queries

Screenshots

Version Information:

• OS: Mac
• App Version: 5.1.6
• Install source: Installer
• Type: Application
• Database engine: PostgreSQL

Thanks for the investigation; I love this soft nevertheless!

[janproch]

Thanks very much for reporting

This query always returns an empty result, so the simplest solution (but not solving the problem completely) will be not to execute it... It is invoked in some scenarios, when modification of DB structure is detected. Maybe the modification state is not cleared after new DB structure is loaded, so the new DB structure is loaded forever, probably there is some less usual construct in your DB structure

I will investigate this more deeply as soon as possible, I agree this is a critical issue

[janproch]

I released BETA version with the fix

This problem is caused by FK query optimization. As I have discovered more potential problems of this optimization than benefits, I removed it, so this query should never be invoked.

However, I am not sure, whether this will solve all related problems. If there is a repeated message in the shell console

DB modifications detected: ...

please let me know. I will be on vacation next week, so bigger changes must wait a bit

[loic-simon]

Thanks for the quick fix! I installed the beta version, looks good at first glance but I'll tell you if anything similar happens.