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