Proposal to add --single-row to psql

From: Christopher Manning <c(at)christophermanning(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Proposal to add --single-row to psql
Date: 2013-04-23 15:30:59
Message-ID: CAEYkp92z2w3VBs4uxWPwub7k4hGW-vEPW_WNSui9R5T+cGpLYw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

psql currently collects the query result rows in memory before writing them
to a file and can cause out of memory problems for large results in low
memory environments like ec2. I can't use COPY TO STDOUT or FETCH_COUNT
since I'm using Redshift and it doesn't support [writing to STDOUT](
http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html) or [CURSOR](
https://forums.aws.amazon.com/thread.jspa?threadID=122664)

[Single Row Mode is available in Postgres 9.2](
http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html) but
[it doesn't look like](
http://www.postgresql.org/docs/9.2/static/app-psql.html) you can tell psql
to use single row mode when writing to a file (using --output).

I'm proposing to add a --single-row option to psql that would allow the
result rows of a query to be streamed to a file without collecting them in
memory first.

I'm new to the postgres source, but I was considering doing this by adding
an elseif at [this line in bin/psql/common.c](
https://github.com/postgres/postgres/blob/master/src/bin/psql/common.c#L955)
that
would call [PQsetSingleRowMode](
https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-exec.c#L1581)
and
ideally use something very similar to [ExecQueryUsingCursor](
https://github.com/postgres/postgres/blob/master/src/bin/psql/common.c#L1081
)

Please let me know if that would be an acceptable addition and if there's
anything in particular I should be aware of when adding the feature.

Thank you,
Christopher

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Steve Singer 2013-04-23 15:37:04 Re: Performance with the new security release?
Previous Message Simon Riggs 2013-04-23 15:28:33 Re: Enabling Checksums