Re: [HACKERS] Proposal to add --single-row to psql
Fabrízio and Tom, I know that you can use --variable=FETCH_COUNT=1 from the psql command line, but internally that uses a CURSOR to batch the rows and [Redshift doesn't support CURSOR]( https://forums.aws.amazon.com/thread.jspa?threadID=122664tstart=0) so it's not an option when using psql to download data from Redshift. Pavel's idea of having a single row mode option to replace FETCH_COUNT is interesting, does anyone have any problems with that or alternative ideas? Regards, Christopher On Wed, Apr 24, 2013 at 7:04 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Tue, Apr 23, 2013 at 1:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Isn't there already a way to set FETCH_COUNT from the command line? (ie, I think there's a generic variable-assignment facility that could do this) Christopher, Tom is all right... from psql [1] command line we can do that: $ bin/psql --variable=FETCH_COUNT=100 psql (9.3devel) Type help for help. fabrizio=# \echo :FETCH_COUNT 100 Regards, [1] http://www.postgresql.org/docs/current/interactive/app-psql.html#AEN84903 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
[HACKERS] Proposal to add --single-row to psql
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
