Обсуждение: [Fwd: Re: RHDB just sits and does nothing?]

Поиск
Список
Период
Сортировка

[Fwd: Re: RHDB just sits and does nothing?]

От
"Mauri Sahlberg"
Дата:
Hi,

What an earth I am doing wrong? I am trying to delete about 6500 rows and
I am the only user connected to the database and it just sits there and
does nothing?

Jun  9 10:02:44 pihsi postgres[31547]: [25] LOG:  query: begin;
Jun  9 10:02:44 pihsi postgres[31547]: [26] LOG:  duration: 0.000181 sec
Jun  9 10:02:51 pihsi postgres[31547]: [27] LOG:  query: set constraints
all deferred;
Jun  9 10:02:51 pihsi postgres[31547]: [28] LOG:  duration: 0.000205 sec
Jun  9 10:03:04 pihsi postgres[31547]: [29] LOG:  query: delete from
tilikartta
where yritys=18231413;

and after a long wait...
Jun  9 10:46:16 pihsi postgres[31547]: [30] LOG:  duration: 2592.534609
sec Jun  9 11:00:28 pihsi postgres[31547]: [31] LOG:  query: commit;
Jun  9 11:00:28 pihsi postgres[31547]: [32] LOG:  duration: 0.002915 sec

DELETE 6347
rahaks=# commit;
COMMIT

It just took about three quarters of a hour to delete 6500 rows!
Something is definitely broken and probably I am the one who broke it. Any
guesses what I have broken and how I could fix this?

Full log as an attachment.
Вложения

Re: [Fwd: Re: RHDB just sits and does nothing?]

От
"Scott Marlowe"
Дата:
On Wed, 2004-06-09 at 02:16, Mauri Sahlberg wrote:
> Hi,
>
> What an earth I am doing wrong? I am trying to delete about 6500 rows and
> I am the only user connected to the database and it just sits there and
> does nothing?
>
> Jun  9 10:02:44 pihsi postgres[31547]: [25] LOG:  query: begin;
> Jun  9 10:02:44 pihsi postgres[31547]: [26] LOG:  duration: 0.000181 sec
> Jun  9 10:02:51 pihsi postgres[31547]: [27] LOG:  query: set constraints
> all deferred;
> Jun  9 10:02:51 pihsi postgres[31547]: [28] LOG:  duration: 0.000205 sec
> Jun  9 10:03:04 pihsi postgres[31547]: [29] LOG:  query: delete from
> tilikartta
> where yritys=18231413;
>
> and after a long wait...
> Jun  9 10:46:16 pihsi postgres[31547]: [30] LOG:  duration: 2592.534609
> sec Jun  9 11:00:28 pihsi postgres[31547]: [31] LOG:  query: commit;
> Jun  9 11:00:28 pihsi postgres[31547]: [32] LOG:  duration: 0.002915 sec
>
> DELETE 6347
> rahaks=# commit;
> COMMIT
>
> It just took about three quarters of a hour to delete 6500 rows!
> Something is definitely broken and probably I am the one who broke it. Any
> guesses what I have broken and how I could fix this?

Do you have any foreign key references that are of mismatched types
involved?

Re: [Fwd: Re: RHDB just sits and does nothing?]

От
Tom Lane
Дата:
"Scott Marlowe" <smarlowe@qwest.net> writes:
> On Wed, 2004-06-09 at 02:16, Mauri Sahlberg wrote:
>> It just took about three quarters of a hour to delete 6500 rows!

> Do you have any foreign key references that are of mismatched types
> involved?

Specifically, what other tables reference this one?  Are their
referencing columns of identical types, and *do they have indexes*?

I'm betting on lack of index myself; that's the only FK mistake that
hurts you only at primary-key-delete time.

            regards, tom lane