Proposal to add --single-row to psql

Hello

It is redundant to current FETCH_COUNT implementation, so I don't see sense
to use it together. Maybe we can drop FETCH_COUNT and replace it by
--single-row mode and probably it can simplify code.

Regards

Pavel

2013/4/23 Christopher Manning <c@christophermanning.org>

Christopher Manning <c@christophermanning.org> writes:

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.

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)

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Apr 23, 2013 at 1:05 PM, Tom Lane <tgl@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]http://www.postgresql.org/docs/current/interactive/app-psql.html#AEN84903 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
http://www.postgresql.org/docs/current/interactive/app-psql.html#AEN84903

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Fabrízio and Tom,

I know that you can use --variable="FETCH_COUNT=10000" 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=122664&amp;tstart=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 <
fabriziomello@gmail.com> wrote:

Christopher Manning <c@christophermanning.org> writes:

Fabr�zio and Tom,
I know that you can use --variable="FETCH_COUNT=10000" 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=122664&amp;tstart=0) so it's
not an option when using psql to download data from Redshift.

I don't know what redshift is, but I have very little patience with the
idea of burdening psql with yet another random feature in order to work
around deficiencies in somebody else's software. Considering that the
earliest any such thing could reach the field would be 9.4, it seems not
unlikely that the need for it would be gone by next year anyway.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/25/2013 10:42 AM, Tom Lane wrote:

Christopher Manning <c@christophermanning.org> writes:

Fabrízio and Tom,
I know that you can use --variable="FETCH_COUNT=10000" 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=122664&amp;tstart=0) so it's
not an option when using psql to download data from Redshift.

I don't know what redshift is,

It's an Amazon product based on release 8.0, but with many many features
removed (e.g. Indexes!)

but I have very little patience with the
idea of burdening psql with yet another random feature in order to work
around deficiencies in somebody else's software. Considering that the
earliest any such thing could reach the field would be 9.4, it seems not
unlikely that the need for it would be gone by next year anyway.

Plus there is the fact that we have no way to test it against redshift
anyway.

It should be up to Amazon to produce a useful psql program that works
with redshift, not us. We have enough to do to support our own product.

If this is to be justified at all it needs to be without reference to
redshift.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/25/2013 07:42 AM, Tom Lane wrote:

Christopher Manning <c@christophermanning.org> writes:

Fabr�zio and Tom,
I know that you can use --variable="FETCH_COUNT=10000" 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=122664&amp;tstart=0) so it's
not an option when using psql to download data from Redshift.

I don't know what redshift is,

It is a PostgreSQL fork based on ancient source code. From Amazon:

Amazon Redshift is based on PostgreSQL 8.0.2. Amazon Redshift and
PostgreSQL have a number of very important differences that you must be
aware of as you design and develop your data warehouse applications.

but I have very little patience with the
idea of burdening psql with yet another random feature in order to work
around deficiencies in somebody else's software. Considering that the
earliest any such thing could reach the field would be 9.4, it seems not
unlikely that the need for it would be gone by next year anyway.

+1 this is really an amazon problem not a postgresql problem.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/25/2013 10:55 PM, Andrew Dunstan wrote:

It's an Amazon product based on release 8.0, but with many many
features removed (e.g. Indexes!)

More specifically, it's a hacked-up column-store-ized Pg for OLAP and
analytics work. As I understand it Amazon didn't develop it themselves;
they bought/licensed Paraccel ( http://www.paraccel.com/) and customised
to integrated into Amazon's IAM authentication/authorization, usage
accounting, dynamic provisioning, etc.

http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html
http://en.wikipedia.org/wiki/ParAccel

I find it frustrating that I've never seen an @paraccel email address
here and that few of the other vendors of highly customised Pg offshoots
are contributing back. It's almost enough to make me like the GPL.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 4/28/13 7:50 AM, Craig Ringer wrote:

I find it frustrating that I've never seen an @paraccel email address here and that few of the other vendors of highly customised Pg offshoots are contributing back. It's almost enough to make me like the GPL.

FWIW, I think there's a pretty large barrier to these folks contributing back. Would the community really want to add a bunch of hooks to support something like Redshift? Or Greenplum? Or etc, etc.? Most of these guys have to change significant amounts of PG code, so much so that it's actually hard for them to stay current (which is why most of them just fork).

I do think this is a shame, but I'm not sure of any good way to fix it.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Apr 29, 2013 at 4:33 PM, Jim Nasby <jim@nasby.net> wrote:

On 4/28/13 7:50 AM, Craig Ringer wrote:

I find it frustrating that I've never seen an @paraccel email address here
and that few of the other vendors of highly customised Pg offshoots are
contributing back. It's almost enough to make me like the GPL.

FWIW, I think there's a pretty large barrier to these folks contributing
back. Would the community really want to add a bunch of hooks to support
something like Redshift? Or Greenplum? Or etc, etc.? Most of these guys have
to change significant amounts of PG code, so much so that it's actually hard
for them to stay current (which is why most of them just fork).

I do think this is a shame, but I'm not sure of any good way to fix it.

Yep. There are plenty of things that we do at EDB for good and valid
business reasons that I can't imagine the community accepting under
any circumstances. For example, Oracle compatibility is not something
the community values as highly as EnterpriseDB (and our customers) do.
I'm sure that many of those vendors are in similar situations - they
write code that only runs on specialized hardware, or (rather
commonly, I suspect) they remove parts of the functionality in order
to make certain things very fast. Those are not trade-offs that make
sense for PostgreSQL, but I find it hard to understand what we'd gain
from preventing other people from making them. There are in fact a
pretty large number of companies - EnterpriseDB, obviously, but there
are many, many others - that are choosing to build businesses around
PostgreSQL precisely because it *isn't* GPL. Personally, I think
that's a good thing for our community in terms of mindshare even when
companies choose not to contribute back - and it's even better when
they do.

I was at the MySQL show a couple of years back and they had a vendor
area. And I got talking to one the vendors there who had implemented
some new kind of database - I forget the details exactly - and he told
me that it spoke the PostgreSQL wire protocol. I was of course a bit
surprised as PostgreSQL was not exactly what people at this show were
excited about. So I asked him why not the MySQL wire protocol, and
he, basically, that they were afraid of being accused of a GPL
violation, because MySQL AB had a very expansive interpretation of
what the GPL did and did not allow. We could perhaps argue about
whether he was right to be worried about that... but he was. I can't
think of all the names right now, but I've talked with a bunch of
other companies over the last few years who also chose PostgreSQL for
licensing reasons. I'm pretty happy about that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I find it frustrating that I've never seen an @paraccel email address
here and that few of the other vendors of highly customised Pg offshoots
are contributing back. It's almost enough to make me like the GPL.

Well, Paraccel never ended up contributing any code, but in years back
(2006-2008) they did contribute money to PostgreSQL (event sponsorships,
SPI). I actually talked to them about contributing code, but somehow it
never happened.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 05/02/2013 08:36 AM, Robert Haas wrote:

I've talked with a bunch of other companies over the last few years
who also chose PostgreSQL for licensing reasons. I'm pretty happy
about that.

I think it's a pretty good thing too, personally ... but I do wish
they'd contribute a bit more to the client-side drivers like psqlODBC
and PgJDBC.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

... There are in fact a
pretty large number of companies - EnterpriseDB, obviously, but there
are many, many others - that are choosing to build businesses around
PostgreSQL precisely because it *isn't* GPL. Personally, I think
that's a good thing for our community in terms of mindshare even when
companies choose not to contribute back - and it's even better when
they do.

Yeah, exactly. People forking PG may not contribute much directly,
but they still add to mindshare and general familiarity with the PG
code base. In the long run, that cannot help but add to our goal of
world domination ;-)

We had the GPL-vs-BSD-license discussion in excruciating length years
ago, mid-2000 if memory serves, so you can find a whole lot more about
this topic in the mailing list archives from back then. But personally
I wouldn't consider changing even if it were somehow practical. The
license choice has served us well and will continue to do so.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 5/1/13 7:36 PM, Robert Haas wrote:

On Mon, Apr 29, 2013 at 4:33 PM, Jim Nasby<jim@nasby.net> wrote:

On 4/28/13 7:50 AM, Craig Ringer wrote:

I find it frustrating that I've never seen an @paraccel email address here
and that few of the other vendors of highly customised Pg offshoots are
contributing back. It's almost enough to make me like the GPL.

FWIW, I think there's a pretty large barrier to these folks contributing
back. Would the community really want to add a bunch of hooks to support
something like Redshift? Or Greenplum? Or etc, etc.? Most of these guys have
to change significant amounts of PG code, so much so that it's actually hard
for them to stay current (which is why most of them just fork).

I do think this is a shame, but I'm not sure of any good way to fix it.

Yep. There are plenty of things that we do at EDB for good and valid
business reasons that I can't imagine the community accepting under
any circumstances. For example, Oracle compatibility is not something
the community values as highly as EnterpriseDB (and our customers) do.
I'm sure that many of those vendors are in similar situations - they
write code that only runs on specialized hardware, or (rather
commonly, I suspect) they remove parts of the functionality in order
to make certain things very fast. Those are not trade-offs that make
sense for PostgreSQL, but I find it hard to understand what we'd gain
from preventing other people from making them. There are in fact a
pretty large number of companies - EnterpriseDB, obviously, but there
are many, many others - that are choosing to build businesses around
PostgreSQL precisely because it*isn't* GPL. Personally, I think
that's a good thing for our community in terms of mindshare even when
companies choose not to contribute back - and it's even better when
they do.

FWIW, one point I was trying to make that was overlooked is that it seems to be exceptionally difficult for companies to fork Postgres and then stay current (AFAIK EnterpriseDB and Mammoth are the only products that have pulled that feat off). I believe that makes it significantly harder for them to actually contribute code back that doesn't give them a business advantage, as well as making it harder to justify hacking on the community codebase because they'll just face a very large hurdle when it comes to pulling that code back into their proprietary product.

I don't know of any good way to solve that problem. Maybe it's not worth solving... but I do suspect there's some useful stuff that the community has lost out on because of this.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, May 08, 2013 at 06:08:28PM -0500, Jim Nasby wrote:

On 5/1/13 7:36 PM, Robert Haas wrote:

On Mon, Apr 29, 2013 at 4:33 PM, Jim Nasby<jim@nasby.net> wrote:

On 4/28/13 7:50 AM, Craig Ringer wrote:

I find it frustrating that I've never seen an @paraccel email address here
and that few of the other vendors of highly customised Pg offshoots are
contributing back. It's almost enough to make me like the GPL.

FWIW, I think there's a pretty large barrier to these folks contributing
back. Would the community really want to add a bunch of hooks to support
something like Redshift? Or Greenplum? Or etc, etc.? Most of these guys have
to change significant amounts of PG code, so much so that it's actually hard
for them to stay current (which is why most of them just fork).

I do think this is a shame, but I'm not sure of any good way to fix it.

Yep. There are plenty of things that we do at EDB for good and valid
business reasons that I can't imagine the community accepting under
any circumstances. For example, Oracle compatibility is not something
the community values as highly as EnterpriseDB (and our customers) do.
I'm sure that many of those vendors are in similar situations - they
write code that only runs on specialized hardware, or (rather
commonly, I suspect) they remove parts of the functionality in order
to make certain things very fast. Those are not trade-offs that make
sense for PostgreSQL, but I find it hard to understand what we'd gain
from preventing other people from making them. There are in fact a
pretty large number of companies - EnterpriseDB, obviously, but there
are many, many others - that are choosing to build businesses around
PostgreSQL precisely because it*isn't* GPL. Personally, I think
that's a good thing for our community in terms of mindshare even when
companies choose not to contribute back - and it's even better when
they do.

FWIW, one point I was trying to make that was overlooked is that it
seems to be exceptionally difficult for companies to fork Postgres
and then stay current (AFAIK EnterpriseDB and Mammoth are the only
products that have pulled that feat off).

VMware and CitusDB are doing pretty well so far, but it's early days.

I believe that makes it significantly harder for them to actually
contribute code back that doesn't give them a business advantage, as
well as making it harder to justify hacking on the community
codebase because they'll just face a very large hurdle when it comes
to pulling that code back into their proprietary product.

I don't know of any good way to solve that problem. Maybe it's not
worth solving... but I do suspect there's some useful stuff that the
community has lost out on because of this.

Some of this is getting solved by making PostgreSQL more pluggable in
ways that isolate the proprietary stuff, i.e. make people not have to
touch the PostgreSQL core code much, if at all, in order to provide
whatever special features they provide. Hooks and FDWs are two such
pluggable components.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, May 9, 2013 at 9:12 AM, David Fetter <david@fetter.org> wrote:

On Wed, May 08, 2013 at 06:08:28PM -0500, Jim Nasby wrote:

I believe that makes it significantly harder for them to actually
contribute code back that doesn't give them a business advantage, as
well as making it harder to justify hacking on the community
codebase because they'll just face a very large hurdle when it comes
to pulling that code back into their proprietary product.

I don't know of any good way to solve that problem. Maybe it's not
worth solving... but I do suspect there's some useful stuff that the
community has lost out on because of this.

Some of this is getting solved by making PostgreSQL more pluggable in
ways that isolate the proprietary stuff, i.e. make people not have to
touch the PostgreSQL core code much, if at all, in order to provide
whatever special features they provide. Hooks and FDWs are two such
pluggable components.

Extensions and the lovely background workers as well.
--
Michael

On Thu, May 9, 2013 at 7:36 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:

Some of this is getting solved by making PostgreSQL more pluggable in
ways that isolate the proprietary stuff, i.e. make people not have to
touch the PostgreSQL core code much, if at all, in order to provide
whatever special features they provide. Hooks and FDWs are two such
pluggable components.

Extensions and the lovely background workers as well.

Some kind of extendable parser would be awesome. It would need to tie
into the rewriter also.

No, I don't have a clue what the design looks like.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, May 11, 2013 at 11:17:03AM -0400, Robert Haas wrote:

On Thu, May 9, 2013 at 7:36 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:

Some of this is getting solved by making PostgreSQL more pluggable in
ways that isolate the proprietary stuff, i.e. make people not have to
touch the PostgreSQL core code much, if at all, in order to provide
whatever special features they provide. Hooks and FDWs are two such
pluggable components.

Extensions and the lovely background workers as well.

Some kind of extendable parser would be awesome. It would need to tie
into the rewriter also.

No, I don't have a clue what the design looks like.

That's a direction several of the proprietary RDBMS vendors have
proposed. I think it'd be great :)

Pre-coffee (yeah, I know. Bad idea.) sketch of an idea: create an API
to pass expression trees in and out. This could have other benefits
as to clustering space, shortening the planning cycle, etc., but let's
not go there for now. My knowledge is very, very sketchy, but when I
squint, the expression trees we use look a lot like JSON. Are they
isomorphic?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David Fetter <david@fetter.org> writes:

On Sat, May 11, 2013 at 11:17:03AM -0400, Robert Haas wrote:

Some kind of extendable parser would be awesome. It would need to tie
into the rewriter also.

No, I don't have a clue what the design looks like.

That's a direction several of the proprietary RDBMS vendors have
proposed. I think it'd be great :)

Pre-coffee (yeah, I know. Bad idea.) sketch of an idea: create an API
to pass expression trees in and out. This could have other benefits
as to clustering space, shortening the planning cycle, etc., but let's
not go there for now. My knowledge is very, very sketchy, but when I
squint, the expression trees we use look a lot like JSON. Are they
isomorphic?

By the time you've got an expression tree, the problem is mostly solved,
at least so far as parser extension is concerned.

More years ago than I care to admit, I worked on systems that had
run-time-extensible parsers at Hewlett-Packard, so technology for this
does exist. But my (vague) memory of those systems is that the parser's
language capabilities were more limited than bison's, perhaps only
LL(1). Parsing spec-compatible SQL that way might be a challenge.

A larger issue is that if you don't have the whole grammar available
to check, it's difficult to be sure there are no parsing conflicts.
I seem to remember that we hit some conflicts between different
extension ROMs back at HP :-(

Another point is that extensions that are actually interesting require
a lot more than new syntax. Robert mentioned the rewriter, but you'd
typically need planner and executor additions as well. It's possible to
see how whole new plan node types might be added by a plugin so far as
the executor is concerned, but I haven't a clue how we'd get the planner
to emit them ...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers