Hello
I played with CTE and I have to say, it's great feature - great work.
One questions - can I enforce materialisation of query?
It would be usefull for some analytical queries like:
with tmp as (select a, sum(b) as b from test) select * from tmp union
all select 'all', sum(b) from tmp;
Andrew Dunstan wrote:
Kevin Grittner wrote:
Heikki Linnakangas [EMAIL PROTECTED] wrote:
We really need a more reliable way of detecting that a file has been
fully copied.
In our scripts we handle this by copying to a temp directory on the
same mount point as the archive
At David's request I've been looking through this patch.
Regarding documentation: if it would help, I can write some; I have
already made a start on writing down what is going on internally in
order to understand it myself.
I've found three more bugs so far:
1)
create view v2(id) as
Robert Haas napsal(a):
Here's a patch implementing the TODO item Add a separate TRUNCATE
permission. Hopefully I found all the bits that needed to be
modified to make this work.
Any feedback appreciated.
Added to the next commit fest patch list.
I replied to this post, yesterday. Yet I don't see my reply appear,
could it have been caught in a spamfilter or something?
--
Sincerely,
Stephen R. van den Berg.
Even if man could understand women, he still wouldn't believe it.
--
Sent via pgsql-hackers mailing list
On 25/07 12.56, Dann Corbit wrote:
Probably something from here:
http://www.freegis.org/database/?cat=1
The data predates this project by over 6 years. However, the data
looks quite similar to some of the data from the U.S. Census Bureau
for Alameda County, CA:
I happily announce that the first design of window function was
finished and the patch against HEAD is released online. See
http://umitanuki.net/pgsql/wfv03/design.html
The window functions such like row_number(), rank(), dense_rank(),
percent_rank() are experimentally defined within the patch as
Alvaro Herrera napsal(a):
Zdenek Kotala wrote:
I performed review and I prepared own patch which contains only probes
without any issue. I suggest commit this patch because the rest of patch
is independent and it can be committed next commit fest after rework.
I found following issues:
I
hi,
i have setup a postgres-r environment, including two nodes, and
1) the Ensemble daemon run with default configure (means no command options)
2) set the internal variable
repl_default_gcs = 'ensemble'
at postgresql.conf
3) all initial sql script initialized at node1 and node2 as follows:
create
Tom Lane napsal(a):
Zdenek Kotala [EMAIL PROTECTED] writes:
I performed review and I prepared own patch which contains only probes
without any issue. I suggest commit this patch because the rest of
patch is independent and it can be committed next commit fest after
rework.
I looked at this
Hi,
I know Commit Fest is in progress, as well as the holiday season. But
the Summer of Code ends in about three weeks, so I'd like to request a
bit of out-of-order processing :)
My previous mail sent to -hackers is here:
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]
I had
Andrew Dunstan wrote:
Martin Zaun wrote:
Hi,
trying to build postgres on Windows, I ran into a number of
problems not covered by the FAQs or the PostgreSQL 8.3.3
documentation:
http://www.postgresql.org/docs/8.3/interactive/install-win32-full.html
In short, it's not been an easy
On 27-Jul-08, at 3:00 PM, Stephen R. van den Berg wrote:
Stephen R. van den Berg wrote:
My Pike drivers now support multiple simultaneous portals and
automatic streaming by presending overlapping Execute statements with
a dynamically adapted fetchlimit calculated per select as the query
On 7/25/08, Hiroshi Saito [EMAIL PROTECTED] wrote:
I tackled with hope temporarily. It seems that some adjustment is still
required.
http://winpg.jp/~saito/pg_work/plproxy/
However, windows user desires to use. Of course, it is also me.
Regards,
Hiroshi Saito
Thanks, I applied the
On 7/28/08, Marko Kreen [EMAIL PROTECTED] wrote:
On 7/25/08, Hiroshi Saito [EMAIL PROTECTED] wrote:
I tackled with hope temporarily. It seems that some adjustment is still
required.
http://winpg.jp/~saito/pg_work/plproxy/
However, windows user desires to use. Of course, it is also
Heikki Linnakangas wrote:
Andrew Dunstan wrote:
- or maybe provide a .bat file or perl script that would work as na
archive_command on Windows.
We're not talking about archive_command. We're talking about the thing
that copies files to the directory that pg_standby polls.
Er, that's
Now we think that we were wrong. This type of query should run into
infinit recursion and it's user's responsibility that he does not make
such a query.
Another idea would be prohibiting *any* outer joins in the recursive
term (DB2 style), but this may be overkill.
Even if you were to do
Andrew Dunstan wrote:
Heikki Linnakangas wrote:
Andrew Dunstan wrote:
- or maybe provide a .bat file or perl script that would work as na
archive_command on Windows.
We're not talking about archive_command. We're talking about the thing
that copies files to the directory that pg_standby
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes:
This behaviour is clearly intentional, since the entire mechanism of
estate- es_disallow_tuplestore exists for no other reason, but it
seems to me to be clearly wrong. What is the justification for it?
Tatsuo Yes, this is due to prevent
Zdenek Kotala napsal(a):
Alvaro Herrera napsal(a):
Zdenek Kotala wrote:
I performed review and I prepared own patch which contains only
probes without any issue. I suggest commit this patch because the
rest of patch is independent and it can be committed next commit fest
after rework.
I
Zdenek Kotala [EMAIL PROTECTED] writes:
Whats about #include c.h? Does it work or does it have same issue?
Same problem --- postgres.h isn't actually including any of the
problematic files for itself.
What I suggest might be a reasonable compromise is to copy needed
typedefs directly into the
I spent some time reading the SQL spec over the weekend, and I believe
I've identified a fairly serious problem in the WITH patch. SQL99
7.12 query expression General Rule 1 is
1) If a non-recursive with clause is specified, then:
a) For every with list element WLE, let WQN
Hi hackers
Just my non hacker view on the pl/proxy matter.
From FAQ:
PL/Proxy is compact language for remote calls between PostgreSQL databases.
Why we submitted pl/proxy into core at all?
1. Current core distribution contains dblink which sucks both usability wise
and security wise but being
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes:
Tatsuo Included patches from Yoshiyuki should fix 1) and 2). I also
Tatsuo add your SQLs to the regression test. Thanks.
I think it needs this change in addition; without it, incorrect
results are returned when you reference a recursive view
Hi Marko-san.
Great thanks!!
Please correct one mistake of mine...sorry.
This patch solved problem of win32.:-)
Regards,
Hiroshi Saito
- Original Message -
From: Marko Kreen [EMAIL PROTECTED]
On 7/28/08, Marko Kreen [EMAIL PROTECTED] wrote:
On 7/25/08, Hiroshi Saito [EMAIL
On 7/28/08, Hiroshi Saito [EMAIL PROTECTED] wrote:
Please correct one mistake of mine...sorry.
This patch solved problem of win32.:-)
You mean the += -lws2_32 must be after PGXS? Ok, but moving
the PGXS line is not right as it should after all variables
are set. I moved the SHLIB_LINK +=
I notice that we now have four buildfarm members failing in the 8.1
branch, with symptoms indicating that they are running python 2.5,
which pre-8.2 plpython has known incompatibilities with. I think
it's high time we back-patched those compatibility fixes ... they've
been in the field long
Hi Marko-san.
Thanks! It is comfortable.:-)
Regards,
Hiroshi Saito
- Original Message -
From: Marko Kreen [EMAIL PROTECTED]
On 7/28/08, Hiroshi Saito [EMAIL PROTECTED] wrote:
Please correct one mistake of mine...sorry.
This patch solved problem of win32.:-)
You mean the +=
On Wed, 23 Jul 2008, Kevin Grittner wrote:
In our scripts we handle this by copying to a temp directory on the
same mount point as the archive directory and doing a mv to the
archive location when the copy is successfully completed. I think
that this even works on Windows. Could that just be
Asko Oja wrote:
About citext. Skype is using various hacks and workarounds because
there was no such type in PostgreSQL and i understand others also. To
me it seems to be choice between couple of developers doing it once
and for all and hundreds of developers inventing the wheel every day
Greg Smith wrote:
On Wed, 23 Jul 2008, Kevin Grittner wrote:
In our scripts we handle this by copying to a temp directory on the
same mount point as the archive directory and doing a mv to the
archive location when the copy is successfully completed. I think
that this even works on Windows.
In the PL/Proxy thread it was pointed out that plproxy + dblink
combination can create security hole if .pgpass is used for
storing the passwords for connections. Although plproxy allows
keeping passwords in several places - function body, data,
even reading from text file - keeping them in
On Mon, 2008-07-28 at 11:56 -0400, Tom Lane wrote:
I notice that we now have four buildfarm members failing in the 8.1
branch, with symptoms indicating that they are running python 2.5,
which pre-8.2 plpython has known incompatibilities with. I think
it's high time we back-patched those
Tom Lane [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
I think what this is saying is that the subquery defined by a WITH
clause is to be evaluated only once, even if it is referenced in
multiple places in the upper query. This is sensible because if there
is no such rule,
Gregory Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
This isn't going to be a particularly simple fix :-(. The basic
implementation clearly ought to be to dump the result of the subquery
into a tuplestore and then have the upper level read out from that.
However, we
Dave Cramer wrote:
On 27-Jul-08, at 3:00 PM, Stephen R. van den Berg wrote:
Stephen R. van den Berg wrote:
The driver beats libpq in speed by about 62%.
Anyone interested in taking a peek at the (GPL copyright) driver, I
temporarily put up a small package which contains the working driver
in Pike
On 7/28/08, Asko Oja [EMAIL PROTECTED] wrote:
Would capability to do remote procedure calls useful addition to PostgreSQL
feature set?
I agree with Tom/Simon on the topic of builtin remote calls - if there
is a plan to implement CREATE REMOTE TABLE/VIEW (builtin remote views)
then it should be
On Mon, 2008-07-28 at 18:45 +0200, Stephen R. van den Berg wrote:
Dave Cramer wrote:
On 27-Jul-08, at 3:00 PM, Stephen R. van den Berg wrote:
Stephen R. van den Berg wrote:
The driver beats libpq in speed by about 62%.
Anyone interested in taking a peek at the (GPL copyright) driver, I
Andrew Gierth [EMAIL PROTECTED] writes:
Tatsuo == Tatsuo Ishii [EMAIL PROTECTED] writes:
Tatsuo Included patches from Yoshiyuki should fix 1) and 2). I also
Tatsuo add your SQLs to the regression test. Thanks.
I think it needs this change in addition; without it, incorrect
results are
On Jul 28, 2008, at 09:01, Andrew Dunstan wrote:
Plenty of us who are hackers are also users.
ISTM that Tom's objection is really that citext is a hack, and that
it will actually make it harder for us to get to a collation-based
case insensitive comparison.
Well, that's why it's in
Tom == Tom Lane [EMAIL PROTECTED] writes:
I think it needs this change in addition; without it, incorrect
results are returned when you reference a recursive view from
within the recursive query, due to the RecursionScan nodes
becoming linked to the wrong tuplestores.
Tom That whole
On 28-Jul-08, at 12:45 PM, Stephen R. van den Berg wrote:
Dave Cramer wrote:
On 27-Jul-08, at 3:00 PM, Stephen R. van den Berg wrote:
Stephen R. van den Berg wrote:
The driver beats libpq in speed by about 62%.
Anyone interested in taking a peek at the (GPL copyright) driver, I
On Jul 28, 2008, at 1:54 PM, Dave Cramer wrote:
On 28-Jul-08, at 12:45 PM, Stephen R. van den Berg wrote:
Dave Cramer wrote:
On 27-Jul-08, at 3:00 PM, Stephen R. van den Berg wrote:
Stephen R. van den Berg wrote:
The driver beats libpq in speed by about 62%.
Anyone interested in taking
Dave Cramer wrote:
Since I wrote it, I can relicense it any which way I want.
What kind of license would you like to have?
As Joshua mentioned BSD is the preferred postgresql license. As I
understand it I can't even look at your code and subsequently use
anything in the JDBC driver
I'll
On 7/27/08, Stephen R. van den Berg [EMAIL PROTECTED] wrote:
Stephen R. van den Berg wrote:
My Pike drivers now support multiple simultaneous portals and
automatic streaming by presending overlapping Execute statements with
a dynamically adapted fetchlimit calculated per select as the query
Andrew Gierth [EMAIL PROTECTED] writes:
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom That whole business of using the EState to pass tuplestores back
Tom and forth looks fundamentally broken to me anyway; there's just
Tom no way it'll be certain to link the right nodes together in
Tom
Tom == Tom Lane [EMAIL PROTECTED] writes:
[snip spec]
Just out of curiosity, since I don't have a copy of the spec handy, how
does the language for WITH compare to that for views?
Tom I think this is a must fix because of the point about volatile
Tom functions --- changing it later will
Andrew Gierth [EMAIL PROTECTED] writes:
Recursive WITH queries that self-join the recursion result seem to be
rare in practice.
We are not in the business of getting spec-required semantics 80% right;
and as I took pains to point out to start with, there are good
functionality reasons to want
Andrew Dunstan [EMAIL PROTECTED] writes:
ISTM that Tom's objection is really that citext is a hack, and that it
will actually make it harder for us to get to a collation-based case
insensitive comparison.
Well, it won't make it harder to implement collations; but I worry that
people who have
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
ISTM that Tom's objection is really that citext is a hack, and that it
will actually make it harder for us to get to a collation-based case
insensitive comparison.
Well, it won't make it harder to implement collations; but I
Andrew Dunstan [EMAIL PROTECTED] writes:
Tom Lane wrote:
Well, it won't make it harder to implement collations; but I worry that
people who have been relying on the citext syntax will have a hard time
migrating to collations. Perhaps if someone did the legwork to
determine exactly what that
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom We are not in the business of getting spec-required semantics
Tom 80% right;
I guess that's why we still fold identifiers to lowercase, why our
timestamp implementation differs from the standard, why we used to
require AS for select-list aliases,
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
Tom Lane wrote:
Well, it won't make it harder to implement collations; but I worry that
people who have been relying on the citext syntax will have a hard time
migrating to collations. Perhaps if someone did the legwork to
On Mon, Jul 28, 2008 at 3:36 PM, Andrew Garner
[EMAIL PROTECTED] wrote:
On 7/27/08, Stephen R. van den Berg [EMAIL PROTECTED] wrote:
Stephen R. van den Berg wrote:
My Pike drivers now support multiple simultaneous portals and
automatic streaming by presending overlapping Execute statements
Andrew Gierth [EMAIL PROTECTED] writes:
Tom == Tom Lane [EMAIL PROTECTED] writes:
Tom This isn't going to be a particularly simple fix :-(. The basic
Tom implementation clearly ought to be to dump the result of the
Tom subquery into a tuplestore and then have the upper level read
Tom
On Mon, Jul 28, 2008 at 03:49:21PM -0400, Tom Lane wrote:
I kind of assumed we would do it by implementing the COLLATE clause of
the CREATE DOMAIN statement.
But to define such a domain, you'd have to commit to a case-insensitive
version of a specific collation, no? citext currently
A.M. wrote:
Anyway, what does The driver beats libpq in speed by about 62% mean?
It means that if I open up a connection, my lib only takes half the time
libpq uses to open up the connection, and it also means that
when I run the following query 10 times in a row:
SELECT * FROM
On Jul 25, 2008, at 11:44 AM, Alvaro Herrera wrote:
However, it would be neat if this behaved the same as
alvherre=# select '0.42' + 1;
ERROR: invalid input syntax for integer: 0.42
STATEMENT: select '0.42' + 1;
Do we really want to be making it easier for people to wrap numbers
in
Gregory == Gregory Stark [EMAIL PROTECTED] writes:
Gregory I just wonder where all these examples of real-world queries
Gregory were when I posted this patch and asked for such feedback
Gregory originally. sigh.
Gregory In any case I think we've already made this decision. If we
Gregory
On Thu, Jun 26, 2008 at 3:04 AM, Tom Lane [EMAIL PROTECTED] wrote:
Okay, it seems fairly close to RHEL-4. Doesn't
look like there are any RHEL4 or CentOS4 machines in the buildfarm,
which is why we didn't find out about it earlier.
FYI, I set up a CentOS 4 vserver on one of our buildfarm
On Mon, Jul 21, 2008 at 9:43 PM, Tom Lane [EMAIL PROTECTED] wrote:
Comments?
Tough question.
First PL/Proxy. One objection against PL/Proxy is that it might interfere
with SQL-MED implementation. I don't think its the case because both
solve slightly different problems. SQL-MED brings remote
On Mon, Jul 28, 2008 at 07:57:16PM +0100, Andrew Gierth wrote:
Which will be a serious pessimization in many common cases if you do
it all the time. Googling for examples of non-recursive WITH queries
shows that it is very widely used for clarity or convenience, in
contexts where you _don't_
Martijn van Oosterhout [EMAIL PROTECTED] writes:
That said, this is no more of a deal than that text also has a default
collation. You talk about the database's default collation but with
proper collation support that statement is meaningless.
Well, we had better still be able to support the
Decibel! [EMAIL PROTECTED] writes:
On Jul 25, 2008, at 11:44 AM, Alvaro Herrera wrote:
However, it would be neat if this behaved the same as
alvherre=# select '0.42' + 1;
ERROR: invalid input syntax for integer: 0.42
STATEMENT: select '0.42' + 1;
Do we really want to be making it
Martijn van Oosterhout [EMAIL PROTECTED] writes:
Since the problem is using the result of a WITH clause more than once,
would it be sufficient to simply detect that case and bail? You don't
want materialisation is most cases, there's just a few where it is
needed.
Really? I tried googling to
Tom Lane wrote:
My thought is that we could optimize away materialization in cases where
we can tell it's not needed (no volatile functions and/or no multiple
scans of the subquery). But not being able to do it means we've
implemented the feature incorrectly.
Gregory Stark [EMAIL PROTECTED] writes:
Decibel! [EMAIL PROTECTED] writes:
Do we really want to be making it easier for people to wrap numbers in
quotes?
Currently wrapping numbers in quotes is really the way Postgres expects to get
them.
Really?
regression=# select '2' + '2';
ERROR:
Zdenek Kotala wrote:
However what I suggested is commit probes without issue now and the
rest will be processed on the next commit fest after rework/discussion.
Agreed. I'll fix up the remaining issues with the patch you sent.
--
Robert Lor Sun Microsystems
Austin, USA
Andrew Dunstan [EMAIL PROTECTED] writes:
Tom Lane wrote:
My thought is that we could optimize away materialization in cases where
we can tell it's not needed (no volatile functions and/or no multiple
scans of the subquery). But not being able to do it means we've
implemented the feature
Zdenek Kotala wrote:
Alvaro Herrera napsal(a):
Zdenek Kotala wrote:
I performed review and I prepared own patch which contains only
probes without any issue. I suggest commit this patch because the
rest of patch is independent and it can be committed next commit
fest after rework.
I found
Tom Lane wrote:
* The probes that pass buffer tag elements are already broken by the
pending relation forks patch: there is soon going to be another field
in buffer tags. Perhaps it'd be feasible to pass the buffer tag as a
single probe argument to make that a bit more future-proof? I'm not
Robert Lor [EMAIL PROTECTED] writes:
Tom Lane wrote:
* The probes that pass buffer tag elements are already broken by the
pending relation forks patch: there is soon going to be another field
in buffer tags.
I'm not familiar with this pending patch, but why would it break when
another
Tom == Tom Lane [EMAIL PROTECTED] writes:
Martijn van Oosterhout [EMAIL PROTECTED] writes:
Since the problem is using the result of a WITH clause more than
once, would it be sufficient to simply detect that case and bail?
You don't want materialisation is most cases, there's just a few
On Jul 28, 2008, at 12:29, Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
ISTM that Tom's objection is really that citext is a hack, and that
it
will actually make it harder for us to get to a collation-based case
insensitive comparison.
Well, it won't make it harder to implement
On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote:
I happily announce that the first design of window function was
finished and the patch against HEAD is released online. See
http://umitanuki.net/pgsql/wfv03/design.html
I've put up a git repository at
David E. Wheeler [EMAIL PROTECTED] writes:
To upgrade from citext, I expect
that what one will have to do is to alter the column to change its
data type from citext to TEXT + collation.
What I'm wondering is how closely that will match the semantics of the
contrib module ...
Tom Lane wrote:
By break I meant fail to function usefully. Yes, it would still
compile, but if you don't have the fork number available then you won't
be able to tell what's really happening in the buffer pool. You might
as well not pass any of the buffer tag as pass only part of it.
Got
77 matches
Mail list logo
