From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | tomas(at)nocrew(dot)org |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Big query problem |
Date: | 2002-11-29 09:55:21 |
Message-ID: | 3DE73988.F29C6A6E@rodos.fzk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>
> DELETE FROM table WHERE col1='something' AND col2 IN
> ('aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ... );
>
> In the parantheses I have 6400 names, each about 20 characters. I'm
> using libpq from C. This did not work very well, but the result was
> very unexpected.
>
The conditional operator IN is or at least was known to be slow.
Did you think of an alternative approach as
CREATE TABLE to_delete_col2 ( ref_col2 ... ) ;
populate it with 'aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ...
and then
DELETE FROM table WHERE col1='something' AND
EXISTS (SELECT ref_col2 FROM to_delete_col2 WHERE table.col2 =
to_delete_col2.ref_col2 );
> My application has several threads, each opening its own connection to
> the database. The above query was run in a transaction followed by a
> COMMIT. There was no error from running the above query, but instead,
> it seems that the query was never run at all. As a side effect, every
> other connection to the database always got:
>
> NOTICE: current transaction is aborted, queries ignored until end of
> transaction block
>
> when trying to run a query. I thought that the transactions in
> different connections didn't have anything to do with each other.
>
>
> If I limited the number of names in the failing query to 3200, it
> worked well and as expected.
>
>
> Is there a limit in libpq of the length of a query? And if this is
> exceeded, shouldn't PQexec() give an error?
I agree!
>
I think you should post this to the HACKERS list. Maybe you'll get
a reply from there.
Regards, Christoph
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2002-11-29 10:06:06 | master-detail relationship and count |
Previous Message | Rudi Starcevic | 2002-11-29 04:45:11 | Re: Analyze + Index |