Re: ERROR: deadlock detected - Mailing list pgsql-admin

On Thursday 13 February 2003 06:08, Tom Lane wrote:
> Andreas Schmitz <a.schmitz@cityweb.de> writes:
> > I am still working on the newspaper archive project. I started indexing
> > the articles with tsearch. Along the long text parts I get a "ERROR:
> > deadlock detected" when I try to build the index "update dpa_text set
> > titleidx=txt2txtidx(volltext);". The table has a count of 356437
> > datarows. I already increased the number of locks per transaction. Any
> > ideas on that ?
>
> This does not sound like an out-of-resources kind of problem.  Either
> you did something wrong or you found a bug.  But it's impossible to
> tell which.  Can you provide a SQL script that reproduces the behavior
> you're seeing?


Hello Tom,

there is not much to provide. It is just the update statement mentioned in the
text above. the table structure i a parent table with titles, authors and
other small infos. two subtables with pictures and text fields are referenced
to the parent table. the error occurs on the subtable with the text datatype.

newsdb2=# \d dpa_text
                               Table "public.dpa_text"
   Column   |     Type     |                        Modifiers
------------+--------------+---------------------------------------------------------
 textid     | integer      | not null default
nextval('"dpa_text_textid_seq"'::text)
 id         | integer      | not null
 volltext   | text         | not null
 update_tag | character(1) | default 't'
 titleidx   | txtidx       |
Indexes: dpa_text_pkey primary key btree (textid),
         d_te_id_idx btree (id),
         dpa_text_oid_id_idx btree (oid),
         dpa_txt_t_idx gist (titleidx)
Triggers: RI_ConstraintTrigger_9882551

I am running this on the tables to build the tsearch index:

bash-2.03$ less make_dpa_index.sql
update dpa set titleidx=txt2txtidx(dachzeile);
update dpa set titleidx=txt2txtidx(ueberschrift);
update dpa set titleidx=txt2txtidx(zwischentitel);
update dpa set titleidx=txt2txtidx(vorspann);
update dpa set titleidx=txt2txtidx(kurztext);
update dpa_text set titleidx=txt2txtidx(volltext);

this produces the output:

bash-2.03$ time psql newsdb2 <make_dpa_index.sql
UPDATE 110992
UPDATE 110992
UPDATE 110992
UPDATE 110992
UPDATE 110992
ERROR:  deadlock detected

real    340m52.760s
user    0m0.030s
sys     0m0.040s

regards,

-andreas
--
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email a.schmitz@cityweb.de