Thread: updated patch for selecting large results sets in psql using cursors
Hi there, here comes the latest version (version 7) of the patch to handle large result sets with psql. As previously discussed, a cursor is used for SELECT queries when \set FETCH_COUNT some_value > 0 (defaults to 100 if FETCH_COUNT is set with no value). Comparing to the previous version, the patch actually got smaller and is less invasive, because I doesn't have to deal with a new command and can share some more code with SendQuery() in common.c. Bye, Chris. -- Chris Mair http://www.1006.org
Attachment
<chrisnospam@1006.org> writes:
> here comes the latest version (version 7) of the patch to handle large
> result sets with psql. As previously discussed, a cursor is used
> for SELECT queries when \set FETCH_COUNT some_value > 0
Wait a minute. What I thought we had agreed to was a patch to make
commands sent with \g use a cursor. This patch changes SendQuery
so that *every* command executed via psql is treated this way.
That's a whole lot bigger behavioral change than I think is warranted.
regards, tom lane
Re: updated patch for selecting large results sets in psql using cursors
From
Peter Eisentraut
Date:
Tom Lane wrote: > Wait a minute. What I thought we had agreed to was a patch to make > commands sent with \g use a cursor. This patch changes SendQuery > so that *every* command executed via psql is treated this way. That's what I remembered. I don't think we want to introduce a difference between ; and \g. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Tom Lane wrote: > > Wait a minute. What I thought we had agreed to was a patch to make > > commands sent with \g use a cursor. This patch changes SendQuery > > so that *every* command executed via psql is treated this way. > > That's what I remembered. I don't think we want to introduce a > difference between ; and \g. I am confused. I assume \g and ; should be affected, like Peter says. Tom, what *every* command are you talking about? You mean \d? -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Peter Eisentraut wrote:
>> Tom Lane wrote:
>>> Wait a minute. What I thought we had agreed to was a patch to make
>>> commands sent with \g use a cursor.
> I am confused. I assume \g and ; should be affected, like Peter says.
> Tom, what *every* command are you talking about? You mean \d?
Like I said, I thought we were intending to modify \g's behavior only;
that was certainly the implication of the discussion of "\gc".
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Peter Eisentraut wrote: > >> Tom Lane wrote: > >>> Wait a minute. What I thought we had agreed to was a patch to make > >>> commands sent with \g use a cursor. > > > I am confused. I assume \g and ; should be affected, like Peter says. > > Tom, what *every* command are you talking about? You mean \d? > > Like I said, I thought we were intending to modify \g's behavior only; > that was certainly the implication of the discussion of "\gc". OK, got it. I just don't see the value to doing \g and not ;. I think the \gc case was a hack when he didn't have \set. Now that we have \set, we should be consistent. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> OK, got it. I just don't see the value to doing \g and not ;. I think
> the \gc case was a hack when he didn't have \set. Now that we have
> \set, we should be consistent.
I'm willing to accept this if we can make sure we aren't adding any
overhead --- see my proposal elsewhere in the thread for fixing that.
regards, tom lane
> > > I am confused. I assume \g and ; should be affected, like Peter says. > > > Tom, what *every* command are you talking about? You mean \d? > > > > Like I said, I thought we were intending to modify \g's behavior only; > > that was certainly the implication of the discussion of "\gc". At some point you OKed the "\g and ;" proposal. I admit I was quick adding the "and ;" part, but it seemed so natural once we agreed on using a variable. > OK, got it. I just don't see the value to doing \g and not ;. I think > the \gc case was a hack when he didn't have \set. Now that we have > \set, we should be consistent. I agree with this statement. If we have a variable that switches just between two versions of \g, we could have gone with using \u (or whatever) in the first place. In the mean time I have been converted by the variable camp, and I think the variable should change "\g" and ";" together, consistently. If we find we can't live with the performance overhead of that if(FETCH_COUNT), it is still not clear why we would be better off moving it into the \g code path only. Is it because presumably \g is used less often in existing psql scripts? Bye, Chris. -- Chris Mair http://www.1006.org
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > OK, got it. I just don't see the value to doing \g and not ;. I think > > the \gc case was a hack when he didn't have \set. Now that we have > > \set, we should be consistent. > > I'm willing to accept this if we can make sure we aren't adding any > overhead --- see my proposal elsewhere in the thread for fixing that. Right, if \g has overhead, I don't want people to start using ; because it is faster. That is the kind of behavior that makes us look sloppy. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
<chrisnospam@1006.org> writes:
> here comes the latest version (version 7) of the patch to handle large
> result sets with psql. As previously discussed, a cursor is used
> for SELECT queries when \set FETCH_COUNT some_value > 0
Applied with revisions ... I didn't like the fact that the code was
restricted to handle only unaligned output format, so I fixed print.c
to be able to deal with emitting output in sections. This is not
ideal for aligned output mode, because we compute column widths
separately for each FETCH group, but all the other output modes work
nicely. I also did a little hacking to make \timing and pager output
work as expected.
regards, tom lane
On Tue, 2006-08-29 at 18:31 -0400, Tom Lane wrote: > <chrisnospam@1006.org> writes: > > here comes the latest version (version 7) of the patch to handle large > > result sets with psql. As previously discussed, a cursor is used > > for SELECT queries when \set FETCH_COUNT some_value > 0 > > Applied with revisions ... I didn't like the fact that the code was > restricted to handle only unaligned output format, so I fixed print.c > to be able to deal with emitting output in sections. This is not > ideal for aligned output mode, because we compute column widths > separately for each FETCH group, but all the other output modes work > nicely. I also did a little hacking to make \timing and pager output > work as expected. > > regards, tom lane Cool! I specially like that as a side effect of your work for applying this, psql is faster now. Thanks to all people that helped with this (lots...:) Bye, Chris. -- Chris Mair http://www.1006.org