Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
"Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
On Tue, Jun 06, 2006 at 09:48:43AM -0400, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Hannu Krosing <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Ühel kenal päeval, E, 2006-06-05 kell 14:10, kirjutas Tom Lane: > Neil Conway <[EMAIL PROTECTED]> 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 1 -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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Andrew Dunstan írta: Mark Woodward wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
> Mark Woodward wrote: >>> "Jim C. Nasby" <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Hi! Tom Lane írta: "Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
Mark Woodward wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much
> "Jim C. Nasby" <[EMAIL PROTECTED]> 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? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
"Jim C. Nasby" <[EMAIL PROTECTED]> 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... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> 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? Nope. > 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... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
"Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] psql -A (unaligned format) eats too much memory
Moving to -hackers On Mon, Jun 05, 2006 at 12:32:38AM +0200, Zoltan Boszormenyi wrote: > >I just noticed that psql's unformatted output uses too much > >memory. Is it normal? It seems that psql draws all records > >of a query off the server before it displays or writes the output. > >I would expect this only with formatted output. > > > >Problem is, I have an export that produces 500'000+ records > >which changes frequently. Several (20+) sites run this query > >nightly with different parameters and download it. The SELECTs > >that run in psql -A -t -c '...' may overlap and the query that runs > >in less than 1.5 minutes if it's the only one at the time may take > >3+ hours if ten such queries overlap. The time is mostly spent > >in swapping, all psql processes take up 300+ MB, so the 1GB > >server is brought to its knees quickly, peek swap usage is 1.8 GB. > >I watched the progress in top and the postmaster processes finished > >their work in about half an hour (that would still be acceptable) > >then the psql processes started eating up memory as they read > >the records. > > > >PostgreSQL 8.1.4 was used on RHEL3. > > > >Is there a way to convince psql to use less memory in unformatted > >mode? I know COPY will be able to use arbitrary SELECTs > >but until then I am still stuck with redirecting psql's output. > > The answer it to use SELECT INTO TEMP and then COPY. > Psql will use much less memory that way. But still... 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? ISTM this is a bug... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
