pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [PERFORM] psql -A (unaligned format) eats too much
12+ messages / 7 participants
[nested] [flat]
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 16:40 Andrew Dunstan <andrew@dunslane.net>
parent: Mark Woodward <pgsql@mohawksoft.com>
0 siblings, 2 replies; 12+ messages in thread
From: Andrew Dunstan @ 2006-06-05 16:40 UTC (permalink / raw)
To: Mark Woodward <pgsql@mohawksoft.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
Mark Woodward wrote:
>> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>>
>>> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
>>>
>>>> I'm reading this as just another uninformed complaint about libpq's
>>>> habit of buffering the whole query result. It's possible that there's
>>>> a memory leak in the -A path specifically, but nothing said so far
>>>> provided any evidence for that.
>>>>
>>> Certainly seems like it. It seems like it would be good to allow for
>>> libpq not to buffer, since there's cases where it's not needed...
>>>
>> See past discussions. The problem is that libpq's API says that when it
>> hands you back the completed query result, the command is complete and
>> guaranteed not to fail later. A streaming interface could not make that
>> guarantee, so it's not a transparent substitution.
>>
>> I wouldn't have any strong objection to providing a separate API that
>> operates in a streaming fashion, but defining it is something no one's
>> bothered to do yet. In practice, if you have to code to a variant API,
>> it's not that much more trouble to use a cursor...
>>
>>
>
> Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve
> this for free?
>
>
>
It won't solve it in the general case for clients that expect a result
set. ISTM that "use a cursor" is a perfectly reasonable answer, though.
cheers
andrew
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 16:45 Zoltan Boszormenyi <zboszor@dunaweb.hu>
1 sibling, 0 replies; 12+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-05 16:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
Hi!
Tom Lane írta:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>
>> I've been able to verify this on 8.1.4; psql -A -t -c 'SELECT * FROM
>> largetable' > /dev/null results in psql consuming vast quantities of
>> memory. Why is this?
>>
>
> Is it different without the -A?
>
> I'm reading this as just another uninformed complaint about libpq's
> habit of buffering the whole query result. It's possible that there's
> a memory leak in the -A path specifically, but nothing said so far
> provided any evidence for that.
>
> regards, tom lane
>
So, is libpq always buffering the result? Thanks.
I thought psql buffers only because in its formatted output mode
it has to know the widest value for all the columns.
Then the SELECT INTO TEMP ; COPY TO STDOUT solution
I found is _the_ solution.
I guess then the libpq-based ODBC driver suffers
from the same problem? It certainly explains the
performance problems I observed: the server
finishes the query, the ODBC driver (or libpq underneath)
fetches all the records and the application receives
the first record after all these. Nice.
Best regards,
Zoltán Böszörményi
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 16:48 Mark Woodward <pgsql@mohawksoft.com>
1 sibling, 1 reply; 12+ messages in thread
From: Mark Woodward @ 2006-06-05 16:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
>>> I'm reading this as just another uninformed complaint about libpq's
>>> habit of buffering the whole query result. It's possible that there's
>>> a memory leak in the -A path specifically, but nothing said so far
>>> provided any evidence for that.
>
>> Certainly seems like it. It seems like it would be good to allow for
>> libpq not to buffer, since there's cases where it's not needed...
>
> See past discussions. The problem is that libpq's API says that when it
> hands you back the completed query result, the command is complete and
> guaranteed not to fail later. A streaming interface could not make that
> guarantee, so it's not a transparent substitution.
>
> I wouldn't have any strong objection to providing a separate API that
> operates in a streaming fashion, but defining it is something no one's
> bothered to do yet. In practice, if you have to code to a variant API,
> it's not that much more trouble to use a cursor...
>
Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve
this for free?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 17:01 Mark Woodward <pgsql@mohawksoft.com>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 12+ messages in thread
From: Mark Woodward @ 2006-06-05 17:01 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
> Mark Woodward wrote:
>>> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>>>
>>>> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
>>>>
>>>>> I'm reading this as just another uninformed complaint about libpq's
>>>>> habit of buffering the whole query result. It's possible that
>>>>> there's
>>>>> a memory leak in the -A path specifically, but nothing said so far
>>>>> provided any evidence for that.
>>>>>
>>>> Certainly seems like it. It seems like it would be good to allow for
>>>> libpq not to buffer, since there's cases where it's not needed...
>>>>
>>> See past discussions. The problem is that libpq's API says that when
>>> it
>>> hands you back the completed query result, the command is complete and
>>> guaranteed not to fail later. A streaming interface could not make
>>> that
>>> guarantee, so it's not a transparent substitution.
>>>
>>> I wouldn't have any strong objection to providing a separate API that
>>> operates in a streaming fashion, but defining it is something no one's
>>> bothered to do yet. In practice, if you have to code to a variant API,
>>> it's not that much more trouble to use a cursor...
>>>
>>>
>>
>> Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve
>> this for free?
>>
>>
>>
>
> It won't solve it in the general case for clients that expect a result
> set. ISTM that "use a cursor" is a perfectly reasonable answer, though.
I'm not sure I agree -- surprise!
psql is often used as a command line tool and using a cursor is not
acceptable.
Granted, with an unaligned output, perhaps psql should not buffer the
WHOLE result at once, but without rewriting that behavior, a COPY from
query may be close enough.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 17:03 Andrew Dunstan <andrew@dunslane.net>
parent: Mark Woodward <pgsql@mohawksoft.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andrew Dunstan @ 2006-06-05 17:03 UTC (permalink / raw)
To: Mark Woodward <pgsql@mohawksoft.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; pgsql-hackers@postgresql.org
Mark Woodward wrote:
>>>>
>>>>
>>>>
>>> Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve
>>> this for free?
>>>
>>>
>>>
>>>
>> It won't solve it in the general case for clients that expect a result
>> set. ISTM that "use a cursor" is a perfectly reasonable answer, though.
>>
>
> I'm not sure I agree -- surprise!
>
> psql is often used as a command line tool and using a cursor is not
> acceptable.
>
> Granted, with an unaligned output, perhaps psql should not buffer the
> WHOLE result at once, but without rewriting that behavior, a COPY from
> query may be close enough.
>
>
You have missed my point. Surprise!
I didn't say it wasn't OK in the psql case, I said it wasn't helpful in
the case of *other* libpq clients.
Expecting clients generally to split and interpret COPY output is not
reasonable, but if they want large result sets they should use a cursor.
cheers
andrew
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 17:17 Zoltan Boszormenyi <zboszor@dunaweb.hu>
parent: Andrew Dunstan <andrew@dunslane.net>
1 sibling, 1 reply; 12+ messages in thread
From: Zoltan Boszormenyi @ 2006-06-05 17:17 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: Mark Woodward <pgsql@mohawksoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
Andrew Dunstan írta:
> Mark Woodward wrote:
>>> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>>>
>>>> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote:
>>>>
>>>>> I'm reading this as just another uninformed complaint about libpq's
>>>>> habit of buffering the whole query result. It's possible that
>>>>> there's
>>>>> a memory leak in the -A path specifically, but nothing said so far
>>>>> provided any evidence for that.
>>>>>
>>>> Certainly seems like it. It seems like it would be good to allow for
>>>> libpq not to buffer, since there's cases where it's not needed...
>>>>
>>> See past discussions. The problem is that libpq's API says that
>>> when it
>>> hands you back the completed query result, the command is complete and
>>> guaranteed not to fail later. A streaming interface could not make
>>> that
>>> guarantee, so it's not a transparent substitution.
>>>
>>> I wouldn't have any strong objection to providing a separate API that
>>> operates in a streaming fashion, but defining it is something no one's
>>> bothered to do yet. In practice, if you have to code to a variant API,
>>> it's not that much more trouble to use a cursor...
>>>
>>>
>>
>> Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve
>> this for free?
Yes, it would for me.
> It won't solve it in the general case for clients that expect a result
> set. ISTM that "use a cursor" is a perfectly reasonable answer, though.
The general case cannot be applied for all particular cases.
E.g. you cannot use cursors from shell scripts and just for
producing an "export file" it's not too reasonable either.
Redirecting psql's output or COPY is enough.
Best regards,
Zoltán Böszörényi
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 17:52 Neil Conway <neilc@samurai.com>
parent: Zoltan Boszormenyi <zboszor@dunaweb.hu>
0 siblings, 1 reply; 12+ messages in thread
From: Neil Conway @ 2006-06-05 17:52 UTC (permalink / raw)
To: Zoltan Boszormenyi <zboszor@dunaweb.hu>; +Cc: Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
On Mon, 2006-06-05 at 19:17 +0200, Zoltan Boszormenyi wrote:
> The general case cannot be applied for all particular cases.
> E.g. you cannot use cursors from shell scripts
This could be fixed by adding an option to psql to transparently produce
SELECT result sets via a cursor.
-Neil
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-05 18:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2006-06-05 18:10 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Zoltan Boszormenyi <zboszor@dunaweb.hu>; Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
Neil Conway <neilc@samurai.com> writes:
> On Mon, 2006-06-05 at 19:17 +0200, Zoltan Boszormenyi wrote:
>> The general case cannot be applied for all particular cases.
>> E.g. you cannot use cursors from shell scripts
> This could be fixed by adding an option to psql to transparently produce
> SELECT result sets via a cursor.
Note of course that such a thing would push the incomplete-result
problem further upstream. For instance in (hypothetical --cursor
switch)
psql --cursor -c "select ..." | myprogram
there would be no very good way for myprogram to find out that it'd
been sent an incomplete result due to error partway through the SELECT.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-06 12:30 Hannu Krosing <hannu@skype.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Hannu Krosing @ 2006-06-06 12:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Neil Conway <neilc@samurai.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
Ühel kenal päeval, E, 2006-06-05 kell 14:10, kirjutas Tom Lane:
> Neil Conway <neilc@samurai.com> writes:
> > On Mon, 2006-06-05 at 19:17 +0200, Zoltan Boszormenyi wrote:
> >> The general case cannot be applied for all particular cases.
> >> E.g. you cannot use cursors from shell scripts
>
> > This could be fixed by adding an option to psql to transparently produce
> > SELECT result sets via a cursor.
I think this is an excellent idea.
psql --cursor --fetchby 10000 -c "select ..." | myprogram
> Note of course that such a thing would push the incomplete-result
> problem further upstream. For instance in (hypothetical --cursor
> switch)
> psql --cursor -c "select ..." | myprogram
> there would be no very good way for myprogram to find out that it'd
> been sent an incomplete result due to error partway through the SELECT.
would it not learn about it at the point of error ?
even without --cursor there is still no very good way to find out when
something else goes wrong, like the result inside libpq taking up all
memory and so psql runs out of memory on formatting some longer lines.
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-06 13:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hannu Krosing <hannu@skype.net>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2006-06-06 13:48 UTC (permalink / raw)
To: Hannu Krosing <hannu@skype.net>; +Cc: Neil Conway <neilc@samurai.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; Jim C. Nasby <jnasby@pervasive.com>; pgsql-hackers@postgresql.org
Hannu Krosing <hannu@skype.net> writes:
> Ühel kenal päeval, E, 2006-06-05 kell 14:10, kirjutas Tom Lane:
>> Note of course that such a thing would push the incomplete-result
>> problem further upstream. For instance in (hypothetical --cursor
>> switch)
>> psql --cursor -c "select ..." | myprogram
>> there would be no very good way for myprogram to find out that it'd
>> been sent an incomplete result due to error partway through the SELECT.
> would it not learn about it at the point of error ?
No, it would merely see EOF after some number of result rows. (I'm
assuming you're also using -A -t so that the output is unadorned.)
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-06 14:39 Jim C. Nasby <jnasby@pervasive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Jim C. Nasby @ 2006-06-06 14:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@skype.net>; Neil Conway <neilc@samurai.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; pgsql-hackers@postgresql.org
On Tue, Jun 06, 2006 at 09:48:43AM -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > Ãhel kenal päeval, E, 2006-06-05 kell 14:10, kirjutas Tom Lane:
> >> Note of course that such a thing would push the incomplete-result
> >> problem further upstream. For instance in (hypothetical --cursor
> >> switch)
> >> psql --cursor -c "select ..." | myprogram
> >> there would be no very good way for myprogram to find out that it'd
> >> been sent an incomplete result due to error partway through the SELECT.
>
> > would it not learn about it at the point of error ?
>
> No, it would merely see EOF after some number of result rows. (I'm
> assuming you're also using -A -t so that the output is unadorned.)
So if an error occurs partway through reading a cursor, no error message
is generated? That certainly sounds like a bug to me...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [PERFORM] psql -A (unaligned format) eats too much
@ 2006-06-06 14:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2006-06-06 14:47 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Hannu Krosing <hannu@skype.net>; Neil Conway <neilc@samurai.com>; Zoltan Boszormenyi <zboszor@dunaweb.hu>; Andrew Dunstan <andrew@dunslane.net>; Mark Woodward <pgsql@mohawksoft.com>; pgsql-hackers@postgresql.org
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Tue, Jun 06, 2006 at 09:48:43AM -0400, Tom Lane wrote:
>>> psql --cursor -c "select ..." | myprogram
>>> there would be no very good way for myprogram to find out that it'd
>>> been sent an incomplete result due to error partway through the SELECT.
> So if an error occurs partway through reading a cursor, no error message
> is generated? That certainly sounds like a bug to me...
Sure an error is generated. But it goes to stderr. The guy at the
downstream end of the stdout pipe cannot see either the error message,
or the nonzero status that psql will (hopefully) exit with.
You can theoretically deal with this by having the shell script calling
this combination check psql exit status and discard the results of
myprogram on failure, but it's not easy or simple.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2006-06-06 14:47 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-05 16:45 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-05 16:48 ` Mark Woodward <pgsql@mohawksoft.com>
2006-06-05 16:40 ` Andrew Dunstan <andrew@dunslane.net>
2006-06-05 17:01 ` Mark Woodward <pgsql@mohawksoft.com>
2006-06-05 17:03 ` Andrew Dunstan <andrew@dunslane.net>
2006-06-05 17:17 ` Zoltan Boszormenyi <zboszor@dunaweb.hu>
2006-06-05 17:52 ` Neil Conway <neilc@samurai.com>
2006-06-05 18:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-06 12:30 ` Hannu Krosing <hannu@skype.net>
2006-06-06 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-06 14:39 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-06 14:47 ` Tom Lane <tgl@sss.pgh.pa.us>
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