Re: Strange delete behaviour

> On Mon, Aug 01, 2005 at 01:57:32PM +0200, Renzo Kottmann wrote:
>> If I try a
>>
>> delete
>> from t_node
>> where node_doc_id = XX;
>>
>> from inside a plpgsql function
>> ...
>> The deletion does not finish after several minutes and the CPU is
>> running at 100% all the time unless I stop postmaster. A select works
>> normal and gives me around 2500 rows. Does anybody has an idea why this
>> happens?
>
> What happens if you execute the delete by itself, i.e., not from
> inside a function?

The same! Before I did "delete from t_node where node_doc_id = XX;"

I did

1. "delete from t_as_annotation where asann_ann_id in (select
ann_global_id from t_annotation where ann_doc_id = XX);"

2. "delete from t_annotation where ann_doc_id = XX;"

3. "delete from t_annot_set where as_doc_id = XX;"

These are the same statements in the same order like in the function.

> What output do you get if you connect to the
> database with psql and execute "EXPLAIN ANALYZE DELETE ..."?

It also hangs up with 100% CPU load.

> Do other tables have foreign key references to t_node? If so, are
> there indexes on those tables' foreign key columns? How many records
> are in t_node and any tables that reference it? Do you keep the
> tables vacuumed and analyzed?
>

Yes. I vacuumed and analyezed. There are several references (t_annotation
has two references to t_node): Here is the dicription of the tables.

Table "public.t_node"
Column | Type | Modifiers
----------------+---------+--------------------------------------------
node_global_id | integer | not null default nextval('seq_node'::text)
node_doc_id | integer | not null
node_local_id | integer | not null
node_offset | integer | not null
Indexes:
"t_node_pkey" PRIMARY KEY, btree (node_global_id)
"xt_node_01" UNIQUE, btree (node_doc_id, node_local_id)
Foreign-key constraints:
"t_node_node_doc_id_fkey" FOREIGN KEY (node_doc_id) REFERENCES
t_document(doc_id) MATCH FULL

Table "public.t_document"
Column | Type | Modifiers
---------------------+---------+------------------------------------------------
doc_id | integer | not null default
nextval('seq_document'::text)
doc_content_id | integer |
doc_lr_id | integer | not null
doc_url | text |
doc_start | integer |
doc_end | integer |
doc_is_markup_aware | boolean | not null
Indexes:
"t_document_pkey" PRIMARY KEY, btree (doc_id)
"xt_document_01" UNIQUE, btree (doc_lr_id)
Foreign-key constraints:
"t_document_doc_content_id_fkey" FOREIGN KEY (doc_content_id)
REFERENCES t_doc_content(dc_id) MATCH FULL
"t_document_doc_lr_id_fkey" FOREIGN KEY (doc_lr_id) REFERENCES
t_lang_resource(lr_id) MATCH FULL

Table "public.t_annotation"
Column | Type | Modifiers
------------------+---------+--------------------------------------------------
ann_global_id | integer | not null default
nextval('seq_annotation'::text)
ann_doc_id | integer |
ann_local_id | integer | not null
ann_at_id | integer | not null
ann_startnode_id | integer | not null
ann_endnode_id | integer | not null
Indexes:
"t_annotation_pkey" PRIMARY KEY, btree (ann_global_id)
"xt_annotation_01" UNIQUE, btree (ann_doc_id, ann_local_id)
Foreign-key constraints:
"t_annotation_ann_doc_id_fkey" FOREIGN KEY (ann_doc_id) REFERENCES
t_document(doc_id) MATCH FULL
"t_annotation_ann_at_id_fkey" FOREIGN KEY (ann_at_id) REFERENCES
t_annotation_type(at_id) MATCH FULL
"t_annotation_ann_startnode_id_fkey" FOREIGN KEY (ann_startnode_id)
REFERENCES t_node(node_global_id) MATCH FULL
"t_annotation_ann_endnode_id_fkey" FOREIGN KEY (ann_endnode_id)
REFERENCES t_node(node_global_id) MATCH FULL

Table "public.t_annot_set"
Column | Type | Modifiers
-----------+------------------------+-------------------------------------------------
as_id | integer | not null default
nextval('seq_annot_set'::text)
as_name | character varying(128) |
as_doc_id | integer | not null
Indexes:
"t_annot_set_pkey" PRIMARY KEY, btree (as_id)
"xt_annot_set_01" UNIQUE, btree (as_doc_id, as_name)
Foreign-key constraints:
"t_annot_set_as_doc_id_fkey" FOREIGN KEY (as_doc_id) REFERENCES
t_document(doc_id) MATCH FULL

Table "public.t_as_annotation"
Column | Type | Modifiers
--------------+---------+-----------------------------------------------------
asann_id | integer | not null default nextval('seq_as_annotation'::text)
asann_ann_id | integer | not null
asann_as_id | integer | not null
Indexes:
"t_as_annotation_pkey" PRIMARY KEY, btree (asann_id)
"xt_as_annotation_01" btree (asann_as_id)
"xt_as_annotation_02" btree (asann_ann_id)
Foreign-key constraints:
"t_as_annotation_asann_ann_id_fkey" FOREIGN KEY (asann_ann_id)
REFERENCES t_annotation(ann_global_id) MATCH FULL
"t_as_annotation_asann_as_id_fkey" FOREIGN KEY (asann_as_id)
REFERENCES t_annot_set(as_id) MATCH FULL

The row count of these tables:

count: t_node 605911
count: t_document 165
count: t_annotation 618218
count: t_ annot_set 531
count: t_as_annotation 620104

thank you
renzo