-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Michael Glaesemann
Sent: Wednesday, August 22, 2007 5:17 PM
To: Ben Tilly
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] SQL feature requests
On Aug 22, 2007, at 18:45 ,
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Chuck McDevitt
Sent: Wednesday, August 22, 2007 11:53 PM
To: Michael Glaesemann; Ben Tilly
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] SQL feature requests
If your implementation accepts:
group by case when true then 'foo' end
I think he meant: group by case when true then foo end
What would that mean? Regardless of whether or not it's accepted, it
should have *some* meaning.
It's not equivalent to GROUP BY foo
Yea, but only
how much harder can it be to accept:
group by 'foo'
Presumably you meant group by foo.
Imho pg should accept group by foo. It could be part of a constant
removal, that also takes burden off the sort.
e.g. in select x, count(*) from bar where x=5 group by x, x could be
removed since it
Tom Lane wrote:
Hardwiring /usr/share/zoneinfo
into the binary is isomorphic to having it use a symlink defined that
way, as far as the objections to absolute symlinks go.
That just shows how silly the relative symlink requirement is in the
first place. If setting a symlink and hardcoding a
Zeugswetter Andreas ADI SD [EMAIL PROTECTED] writes:
how much harder can it be to accept:
group by 'foo'
Presumably you meant group by foo.
No that's the whole point. He meant the constant string 'foo' not the column
identifier foo which certainly should work now.
--
Gregory Stark
Tom Lane [EMAIL PROTECTED] writes:
In short, lots of downsides here, and not a whole lot of upside.
I highly doubt the spec would ever conflict with allowing the user to elide
the aliases given that Oracle (and others?) have always allowed this. Moreover
if it's been 15 years without them
Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
Why would --with-zoneinfo want to use a symlink though? Shouldn't it just
compile the binary to use the path specified directly? Symlinks are fine for a
sysadmin or a packager but if it's going to be supported by Postgres code
directly
Oleg Bartunov wrote:
On Wed, 22 Aug 2007, Heikki Linnakangas wrote:
Is the file format for the ispell dictionary documented somewhere?
There's apparently support for an old and a new format, but I can't
figure out what the formats are.
ispell, myspell and hunspell formats are supported
Tom Lane wrote:
[ catching up on today's email ]
Gregory Stark [EMAIL PROTECTED] writes:
Why would --with-zoneinfo want to use a symlink though? Shouldn't it just
compile the binary to use the path specified directly?
AFAICS that just moves the problem to a different place, one where an
Between 7.4 and 8.0, the owner's grant options were removed from the
aclitem display. This was as a side effect of the fixes of bug #1150,
but I can't readily tell anymore for what specific reason this
happened.
The information schema shows obviously inaccurate data now, and it might
be easy
Here's a snippet of the affix file around that line:
prefixes
flag *A:
. ALI # alivaltiosihteeri, alihankkija # line 83
I ALI\-
Just remove the rules with \-, tsearch allows only alpha character here.
ispell works just fine with it.
I found a
Peter Eisentraut [EMAIL PROTECTED] writes:
Between 7.4 and 8.0, the owner's grant options were removed from the
aclitem display.
Yeah, that was intentional, because the way we were doing it was wrong
and caused behavioral bugs. The owner's grant options have to be
treated as being granted to
Zdenek Kotala [EMAIL PROTECTED] writes:
Tom Lane wrote:
AFAICS that just moves the problem to a different place, one where an
admin *can't* fix it without recompiling ...
Is there real reason why admin should do it? Timezone files are stored in
defined location where all software expect it.
Gregory Stark escribió:
The upside is the convenience which after all is the same upside as most of
our spec grammar extensions. Many many programmers are accustomed to entering
ad-hoc queries of this form and forcing them to enter an alias for no purpose
is just silly pedanticism from their
Tom Lane wrote:
Zdenek Kotala [EMAIL PROTECTED] writes:
Tom Lane wrote:
AFAICS that just moves the problem to a different place, one where an
admin *can't* fix it without recompiling ...
Is there real reason why admin should do it? Timezone files are stored in
defined location where
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
If your implementation accepts:
group by case when true then 'foo' end
What would that mean? Regardless of whether or not it's accepted, it
should have *some* meaning.
To my eyes it
Ben Tilly [EMAIL PROTECTED] writes:
... But then I need this (possibly
empty) list to have a valid group by statement at the end. In Oracle
I used to just write it like this:
SELECT ...
GROUP BY $possible_term_1
$possible_term_2
$possible_term_3
'end of possible
Alvaro Herrera [EMAIL PROTECTED] writes:
Gregory Stark escribió:
The upside is the convenience which after all is the same upside as most of
our spec grammar extensions. Many many programmers are accustomed to entering
ad-hoc queries of this form and forcing them to enter an alias for no
On Aug 23, 2007, at 10:47 , Ben Tilly wrote:
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
*This* seems like a bug:
test=# select record_id
, count(observation_id) as bar
from observation
group by record_id
, case when true
Gregory Stark [EMAIL PROTECTED] writes:
I highly doubt the spec would ever conflict with allowing the user to elide
the aliases given that Oracle (and others?) have always allowed this. Moreover
if it's been 15 years without them adding it surely that argues we can be
pretty sure they won't
Gregory Stark escribió:
Alvaro Herrera [EMAIL PROTECTED] writes:
Gregory Stark escribió:
The upside is the convenience which after all is the same upside as most of
our spec grammar extensions. Many many programmers are accustomed to
entering
ad-hoc queries of this form and forcing
On 8/23/07, Tom Lane [EMAIL PROTECTED] wrote:
Ben Tilly [EMAIL PROTECTED] writes:
... But then I need this (possibly
empty) list to have a valid group by statement at the end. In Oracle
I used to just write it like this:
SELECT ...
GROUP BY $possible_term_1
$possible_term_2
This seems like a particularly petty case compared to a lot of other
extensions we do allow.
That's exactly the problem. Most of our other extensions are
justified
by some significant capability gain. This isn't --- it provides zero
new functionality, and the convenience factor
Alvaro Herrera [EMAIL PROTECTED] writes:
Tom Lane wrote:
[ shrug... ] This just reinforces my point: if every packager is going
to have his own opinion about how to do it, there's little point in
trying to provide a pre-fab solution.
Well, there's a point if every packager have his own
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
This seems like a particularly petty case compared to a lot of other
extensions we do allow.
That's exactly the problem. Most of our other extensions are justified
by some significant capability gain. This isn't
Chuck McDevitt wrote:
Sometimes supporting de-facto standards as well as official standards
makes sense.
On that basis we would support huge pieces of stuff that emulates MySQL
too. Or perhaps you'd like us to follow Oracle's treatment of NULL. Who
gets to choose what is the de facto
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 22, 2007, at 20:36 , Ben Tilly wrote:
[...]
I can well believe that the standard says that you must accept
subqueries with aliases. But does it say you must reject subqueries
without aliases? I strongly doubt that.
If I'm
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Chuck McDevitt wrote:
Tom, it isn't just a case of convenience. When we are trying to
convert users from another database (say Oracle for example) to
PostgeSQL, one of the big stumbling blocks that slows down the work is
all the little query
Chuck McDevitt [EMAIL PROTECTED] writes:
Tom, it isn't just a case of convenience. When we are trying to
convert users from another database (say Oracle for example) to
PostgeSQL, one of the big stumbling blocks that slows down the work is
all the little query changes that people have to make
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Tom Lane wrote:
[ shrug... ] This just reinforces my point: if every packager is going
to have his own opinion about how to do it, there's little point in
trying to provide a pre-fab solution.
Well, there's a point if every
Peter Eisentraut [EMAIL PROTECTED] writes:
Note, for example, that libc refers to /usr/share/zoneinfo by
absolute path.
After reflecting more about this, that's an awfully strong point.
Maybe we should go back to the original patch from Sun --- that is,
just have the configure option change
Gregory Stark escribió:
I get this about once a day because I frequently type this ad-hoc query and
always forget to put the alias in the first time:
postgres=# select count(*),n from (select count(*) as n from test group by i)
group by n;
ERROR: subquery in FROM must have an alias
Tom Lane wrote:
I think we've already found out that the opinions *aren't* equal.
So far the score seems to be:
Red Hat: will use relative symlink
Solaris: will use hardwired path in program
???: will use absolute symlink
Peter's patch supports only the third case,
Peter Eisentraut [EMAIL PROTECTED] writes:
FWIW, hardwiring plus an environment variable would seem to address all
currently known und unknown requirements, and is not so totally
different from solutions to previous, related problems.
I think we are converging on the recognition that Zdenek
On 8/23/07, Tom Lane [EMAIL PROTECTED] wrote:
Chuck McDevitt [EMAIL PROTECTED] writes:
Tom, it isn't just a case of convenience. When we are trying to
convert users from another database (say Oracle for example) to
PostgeSQL, one of the big stumbling blocks that slows down the work is
On 8/23/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 23, 2007, at 10:47 , Ben Tilly wrote:
[...]
Why does it seem like a bug to you?
Turn it around, and tell me in what way is its behaviour surprising to
someone who knows SQL. You asked to group on something that is the
same
Ben Tilly wrote:
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
If your implementation accepts:
group by case when true then 'foo' end
What would that mean? Regardless of whether or not it's accepted, it
should have *some* meaning.
On Thursday 23 August 2007 09:55, Alvaro Herrera wrote:
I think the compromise is to instead of generating aliases at all just
use an alias like *Anonymous Subquery* and add a boolean flag
indicating that that range table is anonymous and not a valid target for
references. I started doing
-Original Message-
From: Andrew Dunstan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 23, 2007 10:26 AM
To: Chuck McDevitt
Cc: Tom Lane; Gregory Stark; Michael Glaesemann; Ben Tilly; pgsql-
[EMAIL PROTECTED]
Subject: Re: [HACKERS] SQL feature requests
Chuck McDevitt
On Aug 23, 2007, at 12:25 , Ben Tilly wrote:
It is already extended in postgres. For pretty good reasons.
Yes, this particular point is moot. Whether or not the reasons are
good is another thing and a difference of opinion.
I'm not surprised that the window clause section is
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-hackers-
[EMAIL PROTECTED] On Behalf Of Florian G. Pflug
Sent: Thursday, August 23, 2007 11:04 AM
To: Ben Tilly
Cc: Michael Glaesemann; Gregory Stark; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] SQL feature requests
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
Note, for example, that libc refers to /usr/share/zoneinfo by
absolute path.
After reflecting more about this, that's an awfully strong point.
Maybe we should go back to the original patch from Sun --- that is,
just have the
Chuck McDevitt wrote:
PostgreSQL already has a huge amount of
non-standard syntax and semantics (perhaps extensions is a better
word?).
Everything from non-standard cast operator, non-standard substr,
non-standard trim, non standard group by semantics (allowing simple ints
to mean column
Tom Lane wrote:
I think we've already found out that the opinions *aren't* equal.
So far the score seems to be:
Red Hat: will use relative symlink
Solaris: will use hardwired path in program
Finally, Because my original patch has not been accepted, Solaris also
use relative
Alvaro Herrera wrote:
It would be even better to use --with-system-tzdata=/usr/share/zoneinfo
which enables lookup in the specified dir, hardwired at compile time in
the executable (I'm not aware if the patch already accepts a path
argument -- seems like the only sane choice). No symlink
Josh Berkus [EMAIL PROTECTED] writes:
I just don't see the ability to omit the alias in a query with only one
subquery (the only circumstances under which it would be safe to do so) as
any significant gain in fuctionality.
Why do you think it'd be restricted to only one subquery?
As long as
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
FWIW, hardwiring plus an environment variable would seem to address all
currently known und unknown requirements, and is not so totally
different from solutions to previous, related problems.
I think we are converging on the
On Aug 23, 2007, at 14:25 , Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
I just don't see the ability to omit the alias in a query with
only one
subquery (the only circumstances under which it would be safe to
do so) as
any significant gain in fuctionality.
Why do you think
Tom,
As long as you take care that the subquery's column names don't match
any other ones in the query, you don't *need* an alias for it ---
there'll be no need to qualify the column names. This extends just
fine to multiple subqueries.
Oh, hmmm. I was thinking this would somehow be more
Chuck McDevitt [EMAIL PROTECTED] writes:
Except group by 1 meaning group by column 1 is a PostgreSQL extension,
not a SQL standard feature, if I recall.
Correct. Part of the reason for being conservative about changing here
is that we've got a mix of standard and nonstandard behaviors with
Michael Glaesemann wrote:
On Aug 23, 2007, at 14:25 , Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
I just don't see the ability to omit the alias in a query with only one
subquery (the only circumstances under which it would be safe to do so)
as
any significant gain in
Peter Eisentraut wrote:
I'm having trouble with the hardcoded 60 second timeout in pg_ctl. pg_ctl
sometimes just times out and there is no way to make it wait a little longer.
I would like to add an option to be able to change that, say
pg_ctl -w --timeout=120. Comments?
+1
I played with
Josh Berkus [EMAIL PROTECTED] writes:
I still don't see it as terrifically useful functionality, given that it's
just saving you 4 keystrokes ...
Less than that, because the AS is optional. The only argument I've
heard that carries much weight with me is that it eases porting from
other
Tom,
so you don't get to point to them as one that we'd improve
compatibility with.
Derby DB2 are also strict.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
Alvaro Herrera [EMAIL PROTECTED] writes:
Michael Glaesemann wrote:
How about something like gensym?
It is dangerous to provide a synthetic name; if the standard ever gets
modified to support alias-less subqueries, they would likely choose a
different name-generating algorithm, and we would
Zdenek Kotala wrote:
Let me know if there something what should be adjusted on my patch. I
would like to do it tomorrow, because I will be offline for next two
weeks.
I can try to fit your patch into what's there now, if you'd rather just
start your holiday or whatever.
--
Peter Eisentraut
Peter Eisentraut wrote:
Zdenek Kotala wrote:
Let me know if there something what should be adjusted on my patch. I
would like to do it tomorrow, because I will be offline for next two
weeks.
I can try to fit your patch into what's there now, if you'd rather just
start your holiday or
On Thu, Aug 23, 2007 at 3:01 PM, in message [EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
The only argument I've
heard that carries much weight with me is that it eases porting from
other DBMS's that allow this. Are there any others besides Oracle?
select * from (select f1 from t)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Zdenek Kotala wrote:
Peter Eisentraut wrote:
I'm having trouble with the hardcoded 60 second timeout in pg_ctl.
pg_ctl sometimes just times out and there is no way to make it wait a
little longer. I would like to add an option to be able to
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Zdenek Kotala wrote:
Peter Eisentraut wrote:
I'm having trouble with the hardcoded 60 second timeout in pg_ctl.
pg_ctl sometimes just times out and there is no way to make it wait a
little longer. I would like to add an
Chuck McDevitt wrote:
Tom, it isn't just a case of convenience. When we are trying to
convert users from another database (say Oracle for example) to
PostgeSQL, one of the big stumbling blocks that slows down the work is
all the little query changes that people have to make (people who might
I have just noticed that we are still accepting data that is invalid for
the server encoding, and in consequence we can produce a dump which we
can't subsequently load. This is a major bug IMNSHO, and needs to be
fixed for 8.3.
Mark, have you made any progress on fixing this? The last
Kevin Grittner wrote:
On Thu, Aug 23, 2007 at 3:01 PM, in message [EMAIL PROTECTED],
Tom Lane [EMAIL PROTECTED] wrote:
The only argument I've
heard that carries much weight with me is that it eases porting from
other DBMS's that allow this. Are there any others besides Oracle?
select *
Hi,
a few days ago I registered on http://www.pgbuildfarm.org/register.html
to setup my machine in the PGSQL build farm.
I have not seen an e-mail yet.
I wonder if it is because the spam filter of Gmail ate the message or it
is just the summer holidays that requires more patience?
regards
On 8/23/07, Florian G. Pflug [EMAIL PROTECTED] wrote:
Ben Tilly wrote:
On 8/22/07, Michael Glaesemann [EMAIL PROTECTED] wrote:
On Aug 22, 2007, at 20:49 , Ben Tilly wrote:
If your implementation accepts:
group by case when true then 'foo' end
What would that mean? Regardless of
Gabor Szabo wrote:
Hi,
a few days ago I registered on http://www.pgbuildfarm.org/register.html
to setup my machine in the PGSQL build farm.
I have not seen an e-mail yet.
I wonder if it is because the spam filter of Gmail ate the message or it
is just the summer holidays that requires more
Florian G. Pflug wrote:
knowing *why* the standard committee disallows that syntax -
and why everybody except oracle chose to agree with it would be quite
interesting.
Consider that we rightfully disallow
SELECT * FROM tab1, tab1;
because there are two tables with the same name involved.
Hi,
I have a simple query that uses digest(data, 'sha1') and consistently
crashes the backend (tested in two different machines):
ciencias=# select version();
version
Florian G. Pflug [EMAIL PROTECTED] writes:
The really funny thing is that pgsql, mysql and at least sybase
*explicitly* dissallow the no-alias case.
I knew that pgsql does that, because I made sure it did ;-). But it is
pretty interesting that these other DBMSes also go out of their way to
Manuel Sugawara [EMAIL PROTECTED] writes:
Hi,
I have a simple query that uses digest(data, 'sha1') and consistently
crashes the backend
The problem is with null arguments:
ciencias=# select digest(null, 'sha1');
el servidor ha cerrado la conexión inesperadamente,
probablemente
Peter Eisentraut [EMAIL PROTECTED] writes:
Florian G. Pflug wrote:
knowing *why* the standard committee disallows that syntax -
and why everybody except oracle chose to agree with it would be quite
interesting.
...
If you believe that, then a table source without any name at all, such
as
In the interest of closing work on what's officially titled the Automatic
adjustment of bgwriter_lru_maxpages patch, I wanted to summarize where I
think this is at, what I'm working on right now, and see if feedback from
that changes how I submit my final attempt for a useful patch in this area
Zdenek Kotala [EMAIL PROTECTED] writes:
Yes, path is accepted (if I remember correctly :-). One thing should be
added and it is default path based on OS.
If the user doesn't know enough to know what the correct path is, he has
no business using the switch at all. So my opinion is no way are
Greg Smith [EMAIL PROTECTED] writes:
In the interest of closing work on what's officially titled the Automatic
adjustment of bgwriter_lru_maxpages patch, I wanted to summarize where I
think this is at ...
2) Having backends write their own buffers out does not significantly
degrade
On Thu, 23 Aug 2007, Tom Lane wrote:
It is doubtless true in a lightly loaded system, but once the kernel is
under any kind of memory pressure I think it's completely wrong.
The fact that so many tests I've done or seen get maximum throughput in
terms of straight TPS with the background
76 matches
Mail list logo