psql: flush output in cursor-fetch mode

Started by Neil Conwayabout 19 years ago4 messagespatches
Jump to latest
#1Neil Conway
neilc@samurai.com

psql's "FETCH_COUNT" feature is useful for incrementally displaying the
results of a long-running query. However, psql fails to flush its output
stream as new rows from the partial result set are produced, which means
that partial query results may not be visible to the client until the
stdio buffer is eventually flushed or the query produces its complete
result set.

Example:

$ cat ~/test.sql
-- a contrived function to get a query that slowly produces
-- more rows
create function slow_func() returns boolean as
$$
begin
perform pg_sleep(2);
return true;
end;
$$ language plpgsql;

neilc=# \i ~/test.sql
CREATE FUNCTION
neilc=# create table t1 (a int, b int);
CREATE TABLE
neilc=# insert into t1 values (5, 10), (10, 15), (20, 25), (30, 35);
INSERT 0 4
neilc=# \set FETCH_COUNT 1
neilc=# select * from t1 where slow_func() is true;

With CVS HEAD, no output is visible until the complete result set has
been produced, at which point all 4 rows are printed. This is
undesirable: since the client has gone to the trouble of FETCH'ing the
rows one-at-a-time, it should display the partial result set before
issuing another FETCH.

Attached is a patch that fixes this, by calling fflush() on the psql
output stream after each call to printQuery() in ExecQueryUsingCursor().

-Neil

Attachments:

psql_fetch_count_fflush-1.patchtext/x-patch; charset=UTF-8; name=psql_fetch_count_fflush-1.patchDownload+6-0
#2Neil Conway
neilc@samurai.com
In reply to: Neil Conway (#1)
Re: psql: flush output in cursor-fetch mode

On Wed, 2007-06-20 at 15:51 -0700, Neil Conway wrote:

Attached is a patch that fixes this, by calling fflush() on the psql
output stream after each call to printQuery() in ExecQueryUsingCursor().

Applied to HEAD.

-Neil

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Neil Conway (#2)
Re: psql: flush output in cursor-fetch mode

Neil Conway <neilc@samurai.com> writes:

On Wed, 2007-06-20 at 15:51 -0700, Neil Conway wrote:

Attached is a patch that fixes this, by calling fflush() on the psql
output stream after each call to printQuery() in ExecQueryUsingCursor().

Applied to HEAD.

Seems reasonable to back-patch into 8.2 too...

regards, tom lane

#4Neil Conway
neilc@samurai.com
In reply to: Tom Lane (#3)
Re: psql: flush output in cursor-fetch mode

On Thu, 2007-21-06 at 22:09 -0400, Tom Lane wrote:

Seems reasonable to back-patch into 8.2 too...

Okay, done.

-Neil