PG 9.5
EG 3.7.0
We've found that if we reingest the Evergreen database (using pingest.pl) more than one record at a time, PostgreSQL will trhow errors.
Example:
./pingest.pl --user evergreen --host 127.0.0.1 --db evergreen --password password --port 5432 --max-child 4
PostgreSQL logs results with:
DBD::Pg::st execute failed: ERROR: deadlock detected
DETAIL: Process 48054 waits for ShareLock on transaction 11063061; blocked by process 48055.
Process 48055 waits for ShareLock on transaction 11063133; blocked by process 48054.
HINT: See server log for query details.
CONTEXT: while rechecking updated tuple (98465,12) in relation "symspell_dictionary"
PL/pgSQL function search.symspell_build_and_merge_entries(text,text,text,boolean) line 34 at RETURN QUERY
SQL statement "SELECT * FROM search.symspell_build_and_merge_entries(new_value, search_class, old_value)"
PL/pgSQL function search.symspell_maintain_entries() line 17 at PERFORM
SQL statement "DELETE FROM metabib.identifier_field_entry WHERE source = 10210"
PL/pgSQL function metabib.reingest_metabib_field_entries(bigint,boolean,boolean,boolean,boolean,integer[]) line 35 at EXECUTE at ./pingest.pl line 291.
metabib.reingest_metabib_field_entries failed for record 10210 at ./pingest.pl line 294.
It goes away when you do this:
ALTER TABLE metabib.title_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
ALTER TABLE metabib.author_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
ALTER TABLE metabib.subject_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
ALTER TABLE metabib.series_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
ALTER TABLE metabib.keyword_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
ALTER TABLE metabib.identifier_field_entry DISABLE TRIGGER maintain_symspell_entries_tgr;
I have confirmed this behavior with PostgreSQL 12 and Evergreen 3.5.3 with the did you mean/symspell code backported.
I have also verified that the behavior continues after applying the patch from bug 1931162.