Re: [ADMIN] ERROR: deadlock detected

2003-02-13 Thread Andreas Schmitz 
On Thursday 13 February 2003 06:08, Tom Lane wrote:
 Andreas Schmitz [EMAIL PROTECTED] 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

real340m52.760s
user0m0.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 [EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] ERROR: deadlock detected -referential integrity violation

2003-02-13 Thread Andreas Schmitz 

Hello Tom,

after running 2 at jobs at night (cluster on txtidx indexes and vacuum full) I 
tried again this morning 'cause the same job on a similar table structure 
went fine.

newsdb2=# update dpa_text set titleidx=txt2txtidx(volltext);
ERROR:  unnamed referential integrity violation - key referenced from 
dpa_text not found in dpa

any ideas ?

regards 

-andreas

-- 
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email [EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[ADMIN] ERROR: deadlock detected

2003-02-12 Thread Andreas Schmitz 

Hi *,

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 ?

regards 

-andreas


-- 
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email [EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org