David Fetter wrote:
> Second, if we put this feature as-is in psql, we're stuck supporting
> it in psql until the end of time, even if (when, I believe) we have a
> fuller and likely not perfectly compatible feature on the back-end.
To me, doing \pivot in psql vs PIVOT in the backend is a
David Fetter wrote:
> That said, a thing in psql that could slice serialized output into
> columns would be handy as a broad, general part of reporting in
> psql
To avoid any confusion with server-side PIVOT, I suggest that the
currently proposed command in psql should have a different na
Hi,
When tab-completing after GRANT EXECUTE, currently psql injects
"PROCEDURE", rather than the expected "ON".
The code for completing with "ON" is there, but it's not reached due to
falling earlier into another branch, one that handles CREATE TRIGGER.
A trivial patch is attached. It adds the
Robert Haas wrote:
> > A trivial patch is attached. It adds the condition that if EXECUTE is
> > preceded by GRANT itself preceded by nothing, then that completion
> > with PROCEDURE is skipped.
>
> Thanks, I committed this. I don't think we usually back-patch tab
> completion fixes, but
Hi,
This is a reboot of my previous proposal for pivoting results in psql,
with a new patch that generalizes the idea further through a command
now named \rotate, and some examples.
So the concept is: having an existing query in the query buffer,
the user can specify two column numbers C1 and C2
I wrote:
> What I like in that representation is that it looks good enough
> to be pasted directly into a document in a word processor.
And ironically, the nice unicode borders came out all garbled
in the mail, thanks to a glitch in my setup that mis-reformatted them
before sending.
Sorry abou
Pavel Stehule wrote:
> the name "rotate" is not correct - maybe "\cross" ?
I'm not dead set on \rotate and suggested other names
previously in [1], but none of them seems decisively
superior.
The rationale behind rotate is that, it's a synonym of pivot
as a verb, and it's not already us
Victor Wagner wrote:
> It would just take a bit more time for client and a bit more load for
> server - to make sure that this connection is read-write by
> issuing
>
>show transaction_read_only
>
> statement before considering connection useful.
If the purpose of the feature is to
Pavel Stehule wrote:
> rotate ~ sounds like transpose matrix, what is not true in this case.
The various definitions that I can see, such as
http://dictionary.reference.com/browse/rotate
make no mention of matrices. It applies to anything that
moves around a pivot or axis.
OTOH, the esta
Hi,
This is the 2nd iteration of this patch, for comments and review.
Changes:
- the arguments can be column names (rather than only numbers).
- the horizontal headers are sorted server-side according to their original
type. DESC order is possible by prefixing the column arg with a minus si
Pavel Stehule wrote:
> in the help inside your last patch, you are using "crosstab". Cannto be
> crosstab the name for this feature?
If it wasn't taken already by contrib/tablefunc, that would be a first
choice. But now, when searching for crosstab+postgresql, pages of
results come out co
Pavel Stehule wrote:
> my comments:
>
> 1. I don't understand why you are use two methods for sorting columns
> (qsort, and query with ORDER BY)
qsort (with strcmp as the comparator) is only used to determine the
set of distinct values for the vertical and horizontal headers.
In fact th
Pavel Stehule wrote:
> 2. Data column are not well aligned - numbers are aligned as text
Thanks for spotting that, it's fixed in the attached new iteration of
the patch.
> 3. When data are multiattribute - then merging together with space separator
> is not practical
>
> * important i
Pavel Stehule wrote:
> > So not using \crosstab is deliberate; it's to prevent confusion with
> > the server-side function.
>
> I don't afraid about this - crosstab is a function in extension. Psql
> backslash commands living in different worlds.
Sure, but the confusion would be assumin
Marko Tiikkaja wrote:
> Since the default t/f output for booleans is not very user friendly,
> attached is a patch which enables you to do for example the following:
Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?
test=> \pset true 1
Pavel Stehule wrote:
> I am looking on this last patch. I talked about the name of this command
> with more people, and the name "rotate" is unhappy. The correct name for
> this visualization technique is "crosstab" (see google "crosstab"). The
> conflict with our extension is unhappy, but
Joe Conway wrote:
> but if you don't want to conflict with the name
> crosstab, perhaps "pivot" would be better?
Initially I had chosen \pivot without much thought about it,
but the objection was raised that a PIVOT/UNPIVOT SQL feature
would likely exist in core in a next release independ
Matthijs van der Vleuten wrote:
> -1 for changing boolout(). It will break anything that receives
> boolean values from the server.
Not if the default output is still 't' or 'f' like now.
Nobody seems to suggest a gratuitous compatibility break.
> How a client is going to display value
Pavel Stehule wrote:
> [ \rotate being a wrong name ]
Here's an updated patch.
First it renames the command to \crosstabview, which hopefully may
be more consensual, at least it's semantically much closer to crosstab .
> The important question is sorting output. The vertical header is
>
Michael Paquier wrote:
> So, looking at this thread, here is the current status:
> - Tom Lane: -1
> - Michael Paquier: -1
> - Peter Geoghegan: +1?
> - Peter Eisentraut: +1
> - the author: surely +1.
> Any other opinions? Feel free to correct this list if needed, and then
> let's try to mov
Jim Nasby wrote:
> I was more thinking it would be nice to be able to temporarily
> over-ride/wrap what an output function is doing. AFAIK that would allow
> this to work everywhere (row(), copy, etc). I don't know of any remotely
> practical way to do that, though.
Yes. Something like
Hi,
Following last week's commit 344cdff :
Author: Tom Lane
Date: Thu Dec 3 14:28:58 2015 -0500
Clean up some psql issues around handling of the query output file.
Formerly, if "psql -o foo" failed to open the output file "foo", it would
print an error message but then carry on
Pavel Stehule wrote:
> postgres=# \crosstabview 4 +month label
>
> Maybe using optional int order column instead label is better - then you can
> do sort on client side
>
> so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol]
In the meantime I've followed a different idea:
Pavel Stehule wrote:
> here is patch - supported syntax: \crosstabview VCol [+/-]HCol [HOrderCol]
>
> Order column should to contains any numeric value. Values are sorted on
> client side
If I understand correctly, I see a problem with HOrderCol.
If the vertical header consists of, for
Pavel Stehule wrote:
> The symbol 'X' in two column mode should be centred - now it is aligned to
> left, what is not nice
Currently print.c does not support centered alignment, only left and right.
Should we add it, it would have to work for all output formats
(except obviously for "unal
Hi,
Here's an updated patch that replaces sorted arrays by AVL binary trees
when gathering distinct values for the columns involved in the pivot.
The change is essential for large resultsets. For instance,
it allows to process a query like this (10 million rows x 10 columns):
select x,(random(
Alvaro Herrera wrote:
> With that, pushed and I hope this is closed for good.
Great!
I understand the fix couldn't be backpatched because
we are not allowed to change the StringInfo struct in
existing releases. But it occurs to me that the new "long_ok"
flag might not be necessary after a
Peter Eisentraut wrote:
> This could probably be sorted out somehow, but I don't want
> to be too lax now and cause problems for later features. There is a
> similar case, namely changing the return type of a function, which we
> also prohibit.
Consider the case of a table with a SERIAL
Michael Paquier wrote:
> Hm. Is symmetry an important properly for sequences? It seems to me
> that if we map with the data types we had better use the MIN values
> instead for consistency. So the concept of this patch is rather weird
> and would introduce an exception with the rest of the
Peter Eisentraut wrote:
> So in order to correctly answer the question, is the sequence about to
> run out, you need to look not only at
> the sequence but also any columns it is associated with. check_postgres
> figures this out, but it's complicated and slow, and not easy to do
> manual
Tom Lane wrote:
> "Daniel Verite" writes:
> > [ psql-var-hooks-v6.patch ]
>
> I took a quick look through this. It seems to be going in generally
> the right direction, but here's a couple of thoughts:
Thanks for looking into this!
> I'm incl
Ashutosh Sharma wrote:
> postgres=# \echo :ENCODING
> UTF8
> postgres=# \set ENCODING xyz
> postgres=# \echo :ENCODING
> xyz
>
> I think currently we are not even showing what are the different valid
> encoding names to the end users like we show it for other built-in
> variables
> VERBOS
Tom Lane wrote:
> However, it only really works if psql never overwrites the values
> after startup, whereas I believe all of these are overwritten by
> a \c command.
Yes, there are reset to reflect the properties of the new connection.
> So maybe it's more user-friendly to make these va
Tom Lane wrote:
> I took a quick look through this. It seems to be going in generally
> the right direction, but here's a couple of thoughts:
Here's an update with these changes:
per Tom's suggestions upthread:
- change ParseVariableBool() signature to return validity as bool.
- remov
I just wrote:
> - add enum-style suggestions on invalid input for \pset x, \pset pager,
> and \set of ECHO, ECHO_HIDDEN, ON_ERROR_ROLLBACK, COMP_KEYWORD_CASE,
> HISTCONTROL, VERBOSITY, SHOW_CONTEXT, \x, \pager
There's no such thing as \pager, I meant to write:
\pset x, \pset pager,
and \
Corey Huinker wrote:
> >
> > 1: unrecognized value "whatever" for "\if"; assuming "on"
> >
> > I do not think that the script should continue with such an assumption.
> >
>
> I agree, and this means we can't use ParseVariableBool() as-is
The patch at https://commitfest.postgresql.org/12/
Corey Huinker wrote:
> Revised patch
A comment about control flow and variables:
in branches that are not taken, variables are expanded
nonetheless, in a way that can be surprising.
Case in point:
\set var 'ab''cd'
-- select :var;
\if false
select :var ;
\else
select 1;
\endif
The
Christoph Berg wrote:
> A workaround is to submit queries using "\x\g\x", but that's ugly,
> clutters the output with toggle messages, and will forget that "\x
> auto" was set.
>
> Mysql's CLI client is using \G for this purpose, and adding the very
> same functionality to psql fits nicel
Christoph Berg wrote:
> But do we really want to choose
> something different just because MySQL is using it?
That's not what I meant. If mysql wasn't using \G
I'd still suggest the name \gx because:
- it means the functionality of \g combined with \x so
semantically it makes sense.
- t
Corey Huinker wrote:
> > \if ERROR
> > \echo X
> > \else
> > \echo Y
> > \endif
> >
> > having both X & Y printed and error reported on else and endif. I think
> > that an expression error should just put the stuff in ignore state.
> >
>
> Not just false, but ignore the wh
Tom Lane wrote:
> Also, if you want to argue that allowing it to change intra-
> transaction is too confusing, why would we only forbid this direction
> of change and not both directions?
The thread "Surprising behaviour of \set AUTOCOMMIT ON" at:
https://www.postgresql.org/message-id/CAH
Tom Lane wrote:
> Evidently, this test script is relying on the preceding behavior that
> setting a bool variable to an empty string was equivalent to setting
> it to "true". If it's just that script I would be okay with saying
> "well, it's a bug in that script" ... but I'm a bit worried
Tom Lane wrote:
> Moreover, the committed patch is inconsistent in that it forbids
> only one of the above. Why is it okay to treat unset as "off",
> but not okay to treat the default empty-string value as "on"?
Treating unset (NULL in the value) as "off" comes from the fact
that except
Stephen Frost wrote:
> That's not how '\dx' works, as I pointed out, so I don't see having the
> second character being 'x' to imply "\x mode" makes sense.
\gx means "like \g but output with expanded display"
It turns out that it's semantically close to "\g with \x" so
I refered to it lik
I wrote:
> This would allow the hook to distinguish between initialization and
> unsetting, which in turn will allow it to deny the \unset in the
> cases when it doesn't make any sense conceptually (like AUTOCOMMIT).
I notice that in the commited patch, you added the ability
for DeleteVar
Tom Lane wrote:
> I updated the documentation as well. I think this is committable if
> there are not objections.
That works for me. I tested and read it and did not find anything
unexpected or worth objecting.
"\unset var" acting as "\set var off" makes sense considering
that its opposi
Peter Eisentraut wrote:
> You really have (at least) three options here:
>
> 1. Rename nothing
> 2. Rename directory only
> 3. Rename everything
I vote for 1) as I believe the renaming will create more confusion
than it's worth, not even considering the renaming of functions
and views.
Corey Huinker wrote:
> I meant in the specific psql-context, does it do anything other
> than (attempt to) terminate sent-but-not-received SQL queries?
It cancels the current edit in the query buffer, including the
case when it spans multiple lines.
If we add the functionality that Ctrl+C
Corey Huinker wrote:
[about Ctrl-C]
> That does seem to be the consensus desired behavior. I'm just not sure
> where to handle that. The var "cancel_pressed" shows up in a lot of places.
> Advice?
Probably you don't need to care about cancel_pressed, and
the /if stack could be unwound at
I wrote:
> So I went through the psql commands which don't fail on parse errors
> for booleans
> [...]
Here's a v5 patch implementing the suggestions mentioned upthread:
all meta-commands calling ParseVariableBool() now fail
when the boolean argument can't be parsed successfully.
Also include
Alvaro Herrera wrote:
> I propose to rename allow_long to huge_ok. "Huge" is the terminology
> used by palloc anyway. I'd keep makeLongStringInfo() and
> initLongStringInfo() though as interface, because using Huge instead of
> Long there looks strange. Not wedded to that, though (parti
Alvaro Herrera wrote:
> But I realized that doing it this way is simple enough;
> and furthermore, in any platforms where int is 8 bytes (ILP64), this
> would automatically allow for 63-bit-sized stringinfos
On such platforms there's the next problem that we can't
send COPY rows through t
Fabien COELHO wrote:
> - if not, are possible corner case backward incompatibilities introduced
>by such feature ok?
In psql, if backslash followed by [CR]LF is interpreted as a
continuation symbol, commands like these seem problematic
on Windows since backslash is the directory sepa
Alvaro Herrera wrote:
> I have now pushed this to 9.5, 9.6 and master. It could be backpatched
> to 9.4 with ease (just a small change in heap_form_tuple); anything
> further back would require much more effort.
>
> I used a 32-bit limit using sizeof(int32). I tested and all the
> menti
Tom Lane wrote:
> BTW, I realized while testing this that there's still one gap in our
> understanding of what went wrong for you: cases like "SELECT 'hello'"
> should not have tried to use the pager, because that would've produced
> less than a screenful of data
At some point emacs was m
Rahila Syed wrote:
> Kindly consider following comments,
Sorry for taking so long to post an update.
> There should not be an option to the caller to not follow the behaviour of
> setting valid to either true/false.
OK, fixed.
> In following examples, incorrect error message is begin d
Hi,
When testing the patch at https://commitfest.postgresql.org/12/768/
("sequence data type" by Peter E.), I notice that there's a preexisting
oddity in the fact that sequences created with a negative increment
in current releases initialize the minval to -(2^63)+1 instead of -2^63,
the actual l
Andrew Dunstan wrote:
> If someone can make a good case that this is going to be of
> general use I'll happily go along, but I haven't seen one so far.
About COPY FROM with a raw format, for instance just yesterday
there was this user question on stackoverflow:
http://stackoverflow.com/qu
Andrew Dunstan wrote:
> Inserting the whole contents of a text file unchanged is insanely easy
> in psql.
>
> \set file `cat /path/to/file`
> insert into mytable(contents) values(:'file');
That's assuming psql but the asker of that question never mentioned
using psql. The COPY
Tom Lane wrote:
> >> Code that uses PQexecParams() binary "resultFormat", or the
> >> binary format of copy doesn't have that problem, but most
> >> client-side drivers don't do that.
>
> > And maybe they just can't realistically, because getting result
> > format in binary is exposed a
Alvaro Herrera wrote:
Thanks for looking into that patch!
> regression=# select * from pg_class \crosstabview relnatts
> \crosstabview: missing second argument
> regression-#
Fixed. This was modelled after the behavior of:
select 1 \badcommand
but I've changed to mimic what happens w
Alvaro Herrera wrote:
> I messed with that code some more, as it looked unnecessarily
> complicated; please see attached and verify that it still behaves
> sanely. This needs those regression tests you promised. I tested a few
> cases and it seems good to me.
I've fixed a couple things
Alvaro Herrera wrote:
> I wonder if the business of appending values of multiple columns
> separated with spaces is doing us any good. Why not require that
> there's a single column in the cell? If the user wants to put things
> together, they can use format() or just || the fields toget
Alvaro Herrera wrote:
> I pushed it.
That's awesome, thanks! Also thanks to Pavel who reviewed and helped
continuously when iterating on this feature, and all others who
participed in this thread.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twit
Tom Lane wrote:
> I noticed that the \crosstabview documentation asserts that column name
> arguments are handled per standard SQL semantics. In point of fact,
> though, the patch expends a couple hundred lines to implement what is
> NOT standard SQL semantics: matching unquoted names cas
David G. Johnston wrote:
> > So I guess:
>
> "
> crosstabview with only 2 output columns
> "
>
> https://wiki.postgresql.org/wiki/Crosstabview
> (last section on that page)
>
> never got implemented
It was implemented but eventually removed.
I will update shortly this wiki
Tom Lane wrote:
> > That would be OK with me; it's certainly less of a hack than what's
> > there now. (I went back and forth about how much effort to put into
> > dealing with the colon syntax; I think the version I have in my patch
> > would be all right, but it's not perfect.)
>
> Her
Christoph Berg wrote:
> > I don't quite see how to work around that, short of simply
> > removing the possibility of addressing columns by their
> > numbers. [...]
> That would be bad news, given that \crosstabview is meant for
> interactive use where these number shortcuts are much more
Christoph Berg wrote:
> If there's no way out, what about changing it the other way, i.e.
> breaking the case where the column is named by a number? That seems
> much less of a problem in practice.
I don't think it would be acceptable.
But there's still the option of keeping the dedicated
Tom Lane wrote:
> I wrote:
> > My feeling is that what we should do is undo the change to use OT_SQLID,
> > and in indexOfColumn() perform a downcasing/dequoting conversion that
> > duplicates what OT_SQLID does in psqlscanslash.l.
>
> Here's an updated patch that does it that way, and al
Robert Haas wrote:
> Of course, we could make this value 1-based rather than 0-based, as
> Peter Geoghegan suggested a while back. But as I think I said at the
> time, I think that's more misleading than helpful. The leader
> participates in the parallel plan, but typically does far less
Tom Lane wrote:
> > "Daniel Verite" writes:
> >> To avoid the confusion between "2:4" and "2":"4" or 2:4,
> >> and the ambiguity with a possibly existing "2:4" column,
> >> maybe we should abandon this sy
Tom Lane wrote:
> Pushed, thanks.
> BTW, I see we've been spelling your name with an insufficient number
> of accents in the commit logs and release notes. Can't do much about
> the logs, but will fix the release notes.
I use myself the nonaccented version of my name in "From" headers, a
Magnus Hagander wrote:
> > I don't understand why you want to change the default. Is it for
> > performance? Has it been measured?
> >
> >
> Yes. I've run into it multiple times, but I haven't specifically measured
> it. But I've had more than one situation where turning it off has
> com
101 - 174 of 174 matches
Mail list logo