PostgreSQL Metadata Ingestion Lcoks View/Table which block DDL in Database.

[kenyyh]

trafficstars

Is your feature request related to a problem? Please describe.

When Postgres Metadata Ingestion Collecting information in Postgres.
It will claim lock on every user defined view and the related table.

PID 2533941 is Metadata Ingestion Session.
Which start tansaction at 2024-03-01 13:52:22 and won't commit until the jobs finish.

During the Ingestion Jobs running, we can't do any DDL / truncate on these table.
(The table doesn't have dependency with view will not be lock. )

When ingesting a large database with lots of table/view, it may cost a long time to do the ingestion job.
It will become a huge impact for some user that they can't do DDL and truncate for such a long time.
Is it necessary to claim the table/view lock for such a long time.
Considering that OpenMetadata is a platform to collect Metadata not the main user, it is not reasonable to block the table change when collecting Metadata.
Also, the Metadata Ingestion will not lock table when there is no view relate to it, i wonder is it necessary to lock view.

Describe the solution you'd like

1. Don't Lock Table/View when Collecting Table.
2. Don't hold the transaction for such a long time.

Describe alternatives you've considered N/A Additional context N/A

[TeddyCr]

~My hint here is the join we are doing on the pg_class table which should acquire an AccessShareLock on this table which will conflict with AccessExclusiveLock that this table will require when you run any kind of DDL statement.~

See below 👇

[TeddyCr]

During the Ingestion Jobs running, we can't do any DDL / truncate on these table.

@kenyyh can you share the type of error you are seeing from the postgres side when you are performing this kind of operation and the postgres version you are using?

Have you made any modifications to your locking mechanism as well? I see the metadata ingestion acquires a lock against 100K+ tables, which I can't reproduce. On my end, I cannot reproduce this behavior, regardless of the number of tables I ingest I will only acquire AccessSharedLock against 32 catalog tables -- which won't block any DDL statement.

In your scenario, it seems the ingestion acquires AccessSharedLock against all the table which will indeed be conflict with AccessExclusiveLock that ALTER.. will need to acquire https://www.postgresql.org/docs/current/explicit-locking.html

This query will allow you to check which tables are locked

select oid, relname, mode, locktype
from pg_catalog.pg_class a
inner join (select relation, mode, locktype from pg_locks where pid = 7534) b on a.oid=b.relation;

[pmbrull]

since we cannot reproduce it, not sure we have any direct action on our end to make here. We'll keep it open waiting for further user feedback. Last discussion happened in slack yesterday and moving the ingestion to lower loads it was processing fine

[TeddyCr]

@kenyyh do you have any extensions loaded in your postgres instance?

[TeddyCr]

Validation scenarios:

• [ ] Create artificial load on a relatively big schema and check the locks create when running ingestion
• [ ] Test older version of postgres
• [ ] (if extensions names shared) try to install extensions and run ingestion