I've got some weird issue with Odoo and postgresql. It happens when I try to install the forum module on odoo 15. It's not that relevant and I'm not sure it's repeatable but if you can reproduce the bug that would be how.
Using the answers from this question: How to detect query which holds the lock in Postgres?
I can find that the SQL query acquiring a lock is the following:
SELECT * FROM ir_translation
WHERE lang='en_US' AND
type='model_terms' AND
name='ir.ui.view,arch_db' AND
res_id IN (2618)
The res_id (2618) is being created within the same transaction. But this query returns nothing because there is apparently no translation loaded yet.
Table "public.ir_translation"
Column | Type | Collation | Nullable | Default
----------+-------------------+-----------+----------+--------------------------------------------
id | integer | | not null | nextval('ir_translation_id_seq'::regclass)
name | character varying | | not null |
res_id | integer | | |
lang | character varying | | |
type | character varying | | |
src | text | | |
value | text | | |
module | character varying | | |
state | character varying | | |
comments | text | | |
Indexes:
"ir_translation_pkey" PRIMARY KEY, btree (id)
"ir_translation_code_unique" UNIQUE, btree (type, lang, md5(src)) WHERE type::text = 'code'::text
"ir_translation_comments_index" btree (comments)
"ir_translation_model_unique" UNIQUE, btree (type, lang, name, res_id) WHERE type::text = 'model'::text
"ir_translation_module_index" btree (module)
"ir_translation_res_id_index" btree (res_id)
"ir_translation_src_md5" btree (md5(src))
"ir_translation_type_index" btree (type)
"ir_translation_unique" UNIQUE, btree (type, name, lang, res_id, md5(src))
Foreign-key constraints:
"ir_translation_lang_fkey_res_lang" FOREIGN KEY (lang) REFERENCES res_lang(code)
That's the schema for ir_translation.
Then there are 3 queries working from querying fields from res_groups_users_rel, ir_model_data, ir_model_access, ir_model, ir_rule, rule_group_rel. None of those query gets blocked.
After that there is this query that get stuck on a lock on the query with ir_translation.
SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."google_maps_api_key" as "google_maps_api_key", "website"."user_id" as "user_id", "website"."cdn_activated" as "cdn_activated", "website"."cdn_url" as "cdn_url", "website"."cdn_filters" as "cdn_filters", "website"."homepage_id" as "homepage_id", "website"."custom_code_head" as "custom_code_head", "website"."custom_code_footer" as "custom_code_footer", "website"."robots_txt" as "robots_txt", "website"."theme_id" as "theme_id", "website"."specific_user_account" as "specific_user_account", "website"."auth_signup_uninvited" as "auth_signup_uninvited", "website"."create_uid" as "create_uid", "website"."create_date" as "create_date", "website"."write_uid" as "write_uid", "website"."write_date" as "write_date", "website"."channel_id" as "channel_id", "website"."karma_profile_min" as "karma_profile_min" FROM "website" WHERE "website".id IN (1)
I don't really understand why a query on a website table with no direct ling to ir_translation can get locked.
Here's the result from select * from lock_monitor; view created based on the linked question.
locked_item | waiting_duration | blocked_pid | blocked_query | blocked_mode | blocking_pid | blocking_query | blocking_mode
-------------+------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+-------------------------------------------------------------------------------------------------------------------+--------------------------
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| AccessShareLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| RowShareLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| RowExclusiveLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| ShareUpdateExclusiveLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| ShareRowExclusiveLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
website | 00:01:20.267783 | 21038 | SELECT "website"."id" as "id", "website"."name" as "name", "website"."sequence" as "sequence", "website"."domain" as "domain", "website"."company_id" as "company_id", "website"."default_lang_id" as "default_lang_id", "website"."auto_redirect_lang" as "auto_redirect_lang", "website"."cookies_bar" as "cookies_bar", "website"."configurator_done" as "configurator_done", "website"."social_twitter" as "social_twitter", "website"."social_facebook" as "social_facebook", "website"."social_github" as "social_github", "website"."social_linkedin" as "social_linkedin", "website"."social_youtube" as "social_youtube", "website"."social_instagram" as "social_instagram", "website"."has_social_default_image" as "has_social_default_image", "website"."google_analytics_key" as "google_analytics_key", "website"."google_management_client_id" as "google_management_client_id", "website"."google_management_client_secret" as "google_management_client_secret", "website"."google_search_console" as "google_search_console", "website"."goo | AccessShareLock | 21059 | SELECT * FROM ir_translation +| AccessExclusiveLock
| | | | | | WHERE lang='en_US' AND type='model_terms' AND name='ir.ui.view,arch_db' AND res_id IN (2619) |
(6 rows)
Technically everything is executed in the same transaction. So I'm not so certain why it is locking on a query it previously made. It would make sense if there were 2 concurrent transaction because the concurrency mode is set to serial but in this case I just don't see how 2 unrelated table can cause a lock.
But the PID seems to imply there are 2 processes and I can see two processes in top. Is it possible for postgres to create multiple process for 1 cursor?
