pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBig query problem
2+ messages / 2 participants
[nested] [flat]
* Big query problem
@ 2002-11-27 12:46 Tomas Berndtsson <tomas@nocrew.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tomas Berndtsson @ 2002-11-27 12:46 UTC (permalink / raw)
To: pgsql-sql
I'm using 7.2.1, trying to run a query like this:
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.
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?
Greetings,
Tomas
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Big query problem
@ 2002-11-29 09:55 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2002-11-29 09:55 UTC (permalink / raw)
To: tomas@nocrew.org; +Cc: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-29 09:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-27 12:46 Big query problem Tomas Berndtsson <tomas@nocrew.org>
2002-11-29 09:55 Re: Big query problem Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox