Re: [HACKERS] Postgres as Historian

2010-08-03 Thread Hardik Belani 
Thanks for all your responses and my apologies for putting the question in
the wrong list.

I think OLAP is the answer for my requirements.

Regards,
Hardik



On Wed, Aug 4, 2010 at 5:40 AM, Greg Smith  wrote:

> Hardik Belani wrote:
>
>> For this i can create a table with number and time (may be time offset
>> instead of timestamp) as columns. But still it will require me to store huge
>> number of rows in the order of few millions. Data is read only and only
>> inserts can happen. But I need to perform all kinds of aggregation to get
>> various statistics. for example: daily avg, monthly avg etc..
>>
>>
>
>
> You've unfortunately asked on the wrong list about this.  pgsql-hackers is
> intended mainly for discussion related to the source code of PostgreSQL, so
> this is off-topic for it.  The people who like to argue about the best way
> to implement aggregates and the like are on the pgsql-performance list.
>  You'd be more likely to get detailed responses if you asked this question
> there.  That group loves to talk about how to design things for other
> people.
>
>
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us 
>
>

Re: [HACKERS] merge command - GSoC progress

2010-08-03 Thread Boxuan Zhai 
2010/8/4 Greg Smith 

> Boxuan Zhai wrote:
>
>> I think there are no redundant lines in this time's patch file.
>>
>
> It is much better.  There are still more blank likes around the new code
> you've added than are needed in many places, but that doesn't interfere with
> reading the patch.
>
> Sorry, it is my personal habit of leaving blanks around codes. I will chage
this if it doesn't follow the pgsql coding style.


> The main code formatting issue left you'll need to address eventually are
> all the really long comments in there.
>
> I will correct the long comments in the next patch.

>
>
> And, I have tested the running of MERGE command with different situations.
>> I am sorry that I didn't create regression test files, because I am not sure
>> how to add new files in the git package.
>>
>
> git add  ?
>
> The tests you've put in there are the right general sort of things to try
> out.  The one example you gave does show an UPSERT being emulated by MERGE,
> which is the #1 thing people are looking for initially.
>
> In fact, I have created a merge.sql with simple merge example. I put it in
the folder of /src/test/regress/sql/ and modified the serial_schedule file
to add a line of : test: merge
Is this correct?

But, I don't know how to run regress to test this sql file. My "make check"
fails when install the db. I think this is because I do it under a MinGW
environment and some parameters are not matched with the default setting of
postgres.
I can configure, make install , initdb and do sql query in psql successfully
in my machine. So the source code itself should be correct.

I put my merge.sql in attachment, in case anyone want to have a look.




>
> --
> Greg Smith  2ndQuadrant US  Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com   www.2ndQuadrant.us 
>
>


merge.sql
Description: Binary data

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

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Pavel Stehule 
2010/8/3 Joshua Tolley :
> On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote:
>> On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
>> > In case anyone's interested, I've taken the CTE-based grouping sets
>> > patch from [1] and made it apply to 9.1, attached. I haven't yet
>> > done things like checked it for whitespace consistency, style
>> > conformity, or anything else, but (tuits permitting) hope to figure
>> > out how it works and get it closer to commitability in some upcoming
>> > commitfest.
>> >
>> > I mention it here so that if someone else is working on it, we can
>> > avoid duplicated effort, and to see if a CTE-based grouping sets
>> > implementation is really the way we think we want to go.
>> >
>> > [1]
>> > http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php
>>
>> I've added back one file in the patch enclosed here.  I'm still
>> getting compile fails from
>>
>> CC="ccache gcc" ./configure     --prefix=$PG_PREFIX     
>> --with-pgport=$PGPORT     --with-perl     --with-libxml     --enable-debug   
>>   --enable-cassert
>> make
>>
>> Log from that also enclosed.
>>
>
> Yeah, I seem to have done a poor job of producing the patch based on the
> repository I was working from. That said, it seems Pavel's working actively on
> a patch anyway, so perhaps my updating the old one isn't all that worthwhile.
> Pavel, is your code somewhere that we can get to it?
>

not now. please wait a week.

Regards

Pavel

> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkxYeiQACgkQRiRfCGf1UMPlEQCff+I4sCGtR+lzUs6Wb5JKi7Uu
> 3qYAnjLHzHzyMSHHX55QsphkaBbEJ0Zf
> =uRqV
> -END PGP SIGNATURE-
>
>

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

Re: [HACKERS] Postgres as Historian

2010-08-03 Thread Greg Smith 

Hardik Belani wrote:
For this i can create a table with number and time (may be time offset 
instead of timestamp) as columns. But still it will require me to 
store huge number of rows in the order of few millions. Data is read 
only and only inserts can happen. But I need to perform all kinds of 
aggregation to get various statistics. for example: daily avg, monthly 
avg etc..
 



You've unfortunately asked on the wrong list about this.  pgsql-hackers 
is intended mainly for discussion related to the source code of 
PostgreSQL, so this is off-topic for it.  The people who like to argue 
about the best way to implement aggregates and the like are on the 
pgsql-performance list.  You'd be more likely to get detailed responses 
if you asked this question there.  That group loves to talk about how to 
design things for other people.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [HACKERS] reducing NUMERIC size for 9.1

2010-08-03 Thread Robert Haas 
On Fri, Jul 30, 2010 at 9:55 PM, Robert Haas  wrote:
> On Fri, Jul 30, 2010 at 2:08 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>>   Maybe something like this,
>>> obviously with a suitable comment which I haven't written yet:
>>
>>>     numeric_digits = (precision + 6) / 4;
>>>     return (numeric_digits * sizeof(int16)) + NUMERIC_HDRSZ;
>>
>> This is OK for the base-10K case, but there's still code in there
>> for the base-10 and base-100 cases.  Can you express this logic in
>> terms of DEC_DIGITS and sizeof(NumericDigit) ?  I think you might
>> find it was actually clearer that way, cf Polya.
>
> It appears to work out to:
>
>    numeric_digits = (precision + 2 * (DEC_DIGITS - 1)) / DEC_DIGITS
>    return (numeric_digits * sizeof(NumericDigits)) + NUMERIC_HDRSZ;
>
> The smallest value for precision which requires 2 numeric_digits is
> always 2; and the required number of numeric_digits increases by 1
> each time the number of base-10 digits increases by DEC_DIGITS.

And here is a patch implementing that.

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


numeric_maximum_size.patch
Description: Binary data

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

Re: [HACKERS] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.

2010-08-03 Thread Robert Haas 
On Fri, Jul 9, 2010 at 10:26 AM, Robert Haas  wrote:
> On Fri, Jul 9, 2010 at 12:54 AM, Tom Lane  wrote:
>> Robert Haas  writes:
> How long should I wait before I start breaking things?

 Did you have any particular breakage in mind?
>>
>>> Well, you can see for yourself what I've submitted for the next CF.
>>
>> You might want to hold off on the get_whatever_oid patches for a bit,
>> but the other stuff I see there looks pretty localized.  No objection
>> to pressing forward with CF work otherwise.
>
> I can hold off on those for a bit - I don't think there will be enough
> drift to matter very much, but if it makes you more comfortable, it's
> not a big deal.

I checked on these patches today and there was only one, quite trivial
conflict (and the relevant patch was not even something that was
back-patched).  So I think there is not much reason to hold off any
longer on committing these.

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

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

Re: [HACKERS] merge command - GSoC progress

2010-08-03 Thread Greg Smith 

Boxuan Zhai wrote:

I think there are no redundant lines in this time's patch file.


It is much better.  There are still more blank likes around the new code 
you've added than are needed in many places, but that doesn't interfere 
with reading the patch.


The main code formatting issue left you'll need to address eventually 
are all the really long comments in there.



And, I have tested the running of MERGE command with different 
situations. I am sorry that I didn't create regression test files, 
because I am not sure how to add new files in the git package.


git add  ?

The tests you've put in there are the right general sort of things to 
try out.  The one example you gave does show an UPSERT being emulated by 
MERGE, which is the #1 thing people are looking for initially.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [HACKERS] reducing NUMERIC size for 9.1, take two

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 6:03 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Here's a second version of the main patch, in which I have attempted
>> to respond to Tom's concerns/suggestions.
>
> This version looks fine to me.

Excellent.  Committed.

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

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

Re: [HACKERS] merge command - GSoC progress

2010-08-03 Thread David Fetter 
On Tue, Aug 03, 2010 at 03:14:02PM -0700, Josh Berkus wrote:
> 
> > And, I have tested the running of MERGE command with different
> > situations. I am sorry that I didn't create regression test files,
> > because I am not sure how to add new files in the git package.
> > But, I have written web pages in Postgres Wiki. I explain the
> > details of my implementation and a set of testing examples.
> 
> Can someone help Boxuan with how to write regression tests?

Happy to.  I'll start this evening PDT or tomorrow :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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

Re: [HACKERS] merge command - GSoC progress

2010-08-03 Thread Josh Berkus 

> https://wiki.postgresql.org/wiki/Implementation_detalis
> https://wiki.postgresql.org/wiki/Test_examples

These pages were confusingly named, so I just moved them:

https://wiki.postgresql.org/wiki/MergeTestExamples
https://wiki.postgresql.org/wiki/MergeImplementationDetails

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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

Re: [HACKERS] merge command - GSoC progress

2010-08-03 Thread Josh Berkus 

> And, I have tested the running of MERGE command with different
> situations. I am sorry that I didn't create regression test files,
> because I am not sure how to add new files in the git package. But, I
> have written web pages in Postgres Wiki. I explain the details of my
> implementation and a set of testing examples.

Can someone help Boxuan with how to write regression tests?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.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

Re: [HACKERS] reducing NUMERIC size for 9.1, take two

2010-08-03 Thread Tom Lane 
Robert Haas  writes:
> Here's a second version of the main patch, in which I have attempted
> to respond to Tom's concerns/suggestions.

This version looks fine to me.

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

Re: [HACKERS] Review: Patch for phypot - Pygmy Hippotause

2010-08-03 Thread Tom Lane 
"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> I think the patch is good in principle
 
> Since everyone seems to agree this is a good patch which needed
> minor tweaks, and we haven't heard from the author, I just went
> ahead and made the changes.

Applied with a bit of further editing of the comments.

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

Re: [HACKERS] Compiling CVS HEAD with clang under OSX

2010-08-03 Thread Martijn van Oosterhout 
On Mon, Aug 02, 2010 at 05:34:57PM +0100, Greg Stark wrote:
> Tom: Because that code is much more complex and prone to errors
> especially when you start getting into multiplication and other
> operations and it's also much slower than the code which allows
> overflow to happen and then checks that the result makes sense.

> I'm not entirely sure I agree. At least I haven't actually gone
> through all the arithmetic operations and I'm not sure how much more
> complex they get. If they were all at that level of complexity I think
> I would say we should go ahead and bite the performance bullet and do
> it the ultra-safe way.

FWIW, here's a site with some gcc magic which will allow you to detect
overflows during addition. Ofcourse, the fact that it's gcc specific
makes it a lot less useful.

http://www.fefe.de/intof.html

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first. 
>   - Charles de Gaulle


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Joshua Tolley 
On Tue, Aug 03, 2010 at 12:58:03PM -0700, David Fetter wrote:
> On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
> > In case anyone's interested, I've taken the CTE-based grouping sets
> > patch from [1] and made it apply to 9.1, attached. I haven't yet
> > done things like checked it for whitespace consistency, style
> > conformity, or anything else, but (tuits permitting) hope to figure
> > out how it works and get it closer to commitability in some upcoming
> > commitfest.
> > 
> > I mention it here so that if someone else is working on it, we can
> > avoid duplicated effort, and to see if a CTE-based grouping sets
> > implementation is really the way we think we want to go.
> > 
> > [1]
> > http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php
> 
> I've added back one file in the patch enclosed here.  I'm still
> getting compile fails from
> 
> CC="ccache gcc" ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT 
> --with-perl --with-libxml --enable-debug --enable-cassert
> make
> 
> Log from that also enclosed.
> 

Yeah, I seem to have done a poor job of producing the patch based on the
repository I was working from. That said, it seems Pavel's working actively on
a patch anyway, so perhaps my updating the old one isn't all that worthwhile.
Pavel, is your code somewhere that we can get to it?

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread David Fetter 
On Mon, Aug 02, 2010 at 11:50:00PM -0600, Josh Tolley wrote:
> In case anyone's interested, I've taken the CTE-based grouping sets
> patch from [1] and made it apply to 9.1, attached. I haven't yet
> done things like checked it for whitespace consistency, style
> conformity, or anything else, but (tuits permitting) hope to figure
> out how it works and get it closer to commitability in some upcoming
> commitfest.
> 
> I mention it here so that if someone else is working on it, we can
> avoid duplicated effort, and to see if a CTE-based grouping sets
> implementation is really the way we think we want to go.
> 
> [1]
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00700.php

I've added back one file in the patch enclosed here.  I'm still
getting compile fails from

CC="ccache gcc" ./configure --prefix=$PG_PREFIX --with-pgport=$PGPORT   
  --with-perl --with-libxml --enable-debug --enable-cassert
make

Log from that also enclosed.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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
diff --git a/src/backend/parser/Makefile b/src/backend/parser/Makefile
index a8f4c07..fb248a6 100644
--- a/src/backend/parser/Makefile
+++ b/src/backend/parser/Makefile
@@ -15,7 +15,7 @@ override CPPFLAGS := -I. -I$(srcdir) $(CPPFLAGS)
 OBJS= analyze.o gram.o keywords.o kwlookup.o parser.o \
   parse_agg.o parse_clause.o parse_coerce.o parse_cte.o parse_expr.o \
   parse_func.o parse_node.o parse_oper.o parse_param.o parse_relation.o \
-  parse_target.o parse_type.o parse_utilcmd.o scansup.o
+  parse_target.o parse_type.o parse_utilcmd.o scansup.o parse_gsets.o
 
 FLEXFLAGS = -CF
 
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6b99a10..1b579a8 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -34,6 +34,7 @@
 #include "parser/parse_clause.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_cte.h"
+#include "parser/parse_gsets.h"
 #include "parser/parse_oper.h"
 #include "parser/parse_param.h"
 #include "parser/parse_relation.h"
@@ -150,6 +151,163 @@ parse_sub_analyze(Node *parseTree, ParseState 
*parentParseState,
 }
 
 /*
+ * process GROUPING SETS
+ */
+static SelectStmt *
+makeSelectStmt(List *targetList, List *fromClause)
+{
+   SelectStmt *n = makeNode(SelectStmt);
+   n->distinctClause = NULL;
+   n->intoClause = NULL;
+   n->targetList = targetList;
+   n->fromClause = fromClause;
+   n->whereClause = NULL;
+   n->groupClause = NULL;
+   n->havingClause = NULL;
+   n->windowClause = NIL;
+   n->withClause = NULL;
+   n->valuesLists = NIL;
+   n->sortClause = NIL;
+   n->limitOffset = NULL;
+   n->limitCount = NULL;
+   n->lockingClause = NIL;
+   n->op = SETOP_NONE;
+   n->all = false;
+   n->larg = NULL;
+   n->rarg = NULL;
+   return n;
+}
+
+static List *
+makeStarTargetList(void)
+{
+   ResTarget *rt = makeNode(ResTarget);
+   
+   rt->name = NULL;
+   rt->indirection = NIL;
+   rt->val = (Node *) makeNode(ColumnRef);
+   ((ColumnRef *) rt->val)->fields = list_make1(makeNode(A_Star));
+   rt->location = -1;
+
+   return list_make1(rt);
+}
+ 
+static SelectStmt *
+transformGroupingSets(ParseState *pstate, SelectStmt *stmt)
+{
+   if (stmt->groupClause && IsA(stmt->groupClause, GroupByClause))
+   {
+   GroupingSetsSpec *gss = (GroupingSetsSpec *) 
expandGroupingSets(pstate, 
+   (List *)((GroupByClause 
*)stmt->groupClause)->fields);
+   
+   if (pstate->p_hasGroupingSets)
+   {
+   CommonTableExpr *cte = makeNode(CommonTableExpr);
+   SelectStmt  *cteedstmt;
+   int ngroupingsets = list_length(gss->set_list) + 
(gss->has_empty_set ? 1 : 0);
+   boolall = ((GroupByClause *) 
stmt->groupClause)->all;
+   
+   cteedstmt = makeSelectStmt(NIL, NIL);
+   cteedstmt->intoClause = stmt->intoClause;
+   cteedstmt->sortClause = stmt->sortClause;
+   cteedstmt->limitOffset = stmt->limitOffset;
+   cteedstmt->limitCount = stmt->limitCount;
+   cteedstmt->lockingClause = stmt->lockingClause;
+   
+   cte->ctename = "**g**";
+   cte->ctequery = (Node *) stmt;
+   cte->location = -1;
+   
+   cteedstmt->withClause = makeNode(WithClause);
+   cteedstmt->withClause->ctes = list_make1(cte);
+   cteedstmt->w

Re: [HACKERS] (9.1) btree_gist support for searching on "not equals"

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 3:52 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis  wrote:
>>> Sure. I attached two tests.
>
>> Committed.
>
> I see no sign of a commit from here ...

Sigh.  Forgot to exit my editor.

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

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

Re: [HACKERS] (9.1) btree_gist support for searching on "not equals"

2010-08-03 Thread Tom Lane 
Robert Haas  writes:
> On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis  wrote:
>> Sure. I attached two tests.

> Committed.

I see no sign of a commit from here ...

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

Re: [HACKERS] tracking inherited columns (was: patch for check constraints using multiple inheritance)

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 3:05 PM, Yeb Havinga  wrote:
> Yeb Havinga wrote:
>> The underlying cause is the failure of the code to recognize that if
>> relation C inherits from both A and B, where A and B both have column x,
>> that A.x 'is the same as' B.x, where the 'is the same as' relation is the
>> same that holds for (A.x, C.x) and (B.x, C.x), which the code does a lot of
>> trouble for to recognize. This means that if some definition is altered on
>> A.x, only C.x is updated and B.x not touched. IMO this is wrong and either a
>> multiple inheritance structure like this should be prohibited, since the
>> user did not explicitly declare that A.x and B.x 'are the same' (by e.g.
>> defining a relation D.x and have A and B inherit from that), or the code
>> should update parents of relations when the childs are updated.
>
> Thinking about this a bit more, the name 'is the same as' is a bit
> confusing, since that relation might not be commutative. C.x 'inherits
> properties from' A.x, or C.x 'is defined by' A.x are perhaps better names,
> that reflect that the converse might not hold. OTOH, what does C.x 'inherits
> (all) properties from' A.x mean? If it means that for all properties P,
> P(C.x) iff P(A.x), then C.x =  A.x commutatively and by similar reasoning
> A.x = B.x.
>
>> ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column;
>
> When looking for previous discussions that was referred to upthread, the
> first thing I found was this recent thread about the exactly the same
> problem  http://archives.postgresql.org/pgsql-hackers/2010-01/msg03117.php
>
> Sorry for the double post, however the previous discussion postponed work to
> .. now, so maybe there is some value in first trying to specify exactly what
> 'inherits' means, and derive consequences for code behaviour from that.

Yeah, I was thinking about that thread, too, on my drive home from
Metuchen.  I wouldn't get too bogged down in formal logic; it seems
there are a couple of distinct cases here:

1. If you're changing properties of a column, you need to verify for
each relation in the inheritance tree that the "expected attinhcount"
and the actual attinhcount match.  If, for any relation in the
inheritance tree rooted at the named table, they don't, then they are
doubly inherited there, from some other table outside the hierarchy
rooted at the named table, and the operation must fail.  We'd need
similar logic for constraints, if we had support for renaming or
otherwise modifying them, but right now we don't.

2. If you're adding a column, you need to propagate the new column to
relations that don't have it yet, but if you find one that already has
it than you adjust attinhcount and don't recurse to its chidlren.

3. If you're dropping a column, you essentially decrement the
attinhcount of all your children; then you recurse into any that reach
attincount = 0 and not attislocal and drop the column there as well.

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

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

Re: [HACKERS] Patch to show individual statement latencies in pgbench output

2010-08-03 Thread Greg Smith 

Florian Pflug wrote:

I created the patch to tune the wal_writer for the synchronous_commit=off case 
- the idea being that the COMMIT should be virtually instantaneous if the 
wal_writer writes old wal buffers out fast enough.
  


As I was saying, being able to see the COMMIT times for purposes such as 
that is something I consider valuable about using this instrumentation 
that's not really targeted by pg_stat_statements, the other way built-in 
way people might try to get it.



I haven't yet used pgbench's log output feature, so I can't judge how useful 
the additional of per-statement data to that log is, and what the format should 
be. However, if you think it's useful and can come up with a sensible format, 
I'd be happy to add that feature to the patch.
  


Let's worry about that in the future.  Maybe it's a good add-on, but 
it's more than I have time to get into during this CF personally.



That was a leftover of the trimming and comment skipping logic, which my patch 
moves to process_command.
  


I think there's still a trimming error here--line 195 of the new patch 
is now removing the declaration of "i" just before it sets it to zero?


On the coding standard side, I noticed all your for loops are missing a 
space between the for and the (; that should get fixed.


Finally, now that the rest of the patch is looking in good shape and is 
something I think is worth considering to commit, it's time to work on 
the documentation SGML.


Also:  when generating multiple versions of a patch like this, standard 
practice is to add something like "-vX" to the naming, so those of us 
trying to review can keep them straight. So next one would be 
"pgbench_statementlatency_v3.patch" or something like that.  It's a good 
habit to get into from first version of a patch you submit.  Presuming 
that's going to be the only version is optimistic for all but the 
smallest of patches, and sometimes not even them...


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [HACKERS] (9.1) btree_gist support for searching on "not equals"

2010-08-03 Thread Robert Haas 
On Mon, Aug 2, 2010 at 11:16 PM, Jeff Davis  wrote:
> On Mon, 2010-08-02 at 12:27 -0400, Robert Haas wrote:
>> I was also wondering if it would be worth adding some additional
>> regression testing to contrib/btree_gist exercising this new
>> functionality.  Thoughts?
>
> Sure. I attached two tests.

Committed.  I renamed the test to "not_equals" rather than "mixed" and
added an "EXPLAIN (COSTS OFF)" in there to verify that the index is
actually being used.  (I might have to remove that if it turns out not
to be stable between an index scan and a bitmap index scan, but let's
see what the buildfarm says.)

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

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

Re: [HACKERS] patch for check constraints using multiple inheritance

2010-08-03 Thread Yeb Havinga 

Yeb Havinga wrote:
The underlying cause is the failure of the code to recognize that if 
relation C inherits from both A and B, where A and B both have column 
x, that A.x 'is the same as' B.x, where the 'is the same as' relation 
is the same that holds for (A.x, C.x) and (B.x, C.x), which the code 
does a lot of trouble for to recognize. This means that if some 
definition is altered on A.x, only C.x is updated and B.x not touched. 
IMO this is wrong and either a multiple inheritance structure like 
this should be prohibited, since the user did not explicitly declare 
that A.x and B.x 'are the same' (by e.g. defining a relation D.x and 
have A and B inherit from that), or the code should update parents of 
relations when the childs are updated.
Thinking about this a bit more, the name 'is the same as' is a bit 
confusing, since that relation might not be commutative. C.x 'inherits 
properties from' A.x, or C.x 'is defined by' A.x are perhaps better 
names, that reflect that the converse might not hold. OTOH, what does 
C.x 'inherits (all) properties from' A.x mean? If it means that for all 
properties P, P(C.x) iff P(A.x), then C.x =  A.x commutatively and by 
similar reasoning A.x = B.x.



ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column;
When looking for previous discussions that was referred to upthread, the 
first thing I found was this recent thread about the exactly the same 
problem  http://archives.postgresql.org/pgsql-hackers/2010-01/msg03117.php


Sorry for the double post, however the previous discussion postponed 
work to .. now, so maybe there is some value in first trying to specify 
exactly what 'inherits' means, and derive consequences for code 
behaviour from that.


regards,
Yeb Havinga


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

[HACKERS] Re: [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 1:46 PM, Tom Lane  wrote:
> 1. This way would match the argument ordering for \connect,
> which in case you've forgotten is dbname user host port.

I noticed that discrepancy when reviewing, but felt it was a problem
for another day.  Today is fine, though.  :-)

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

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

Re: [HACKERS] [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.

2010-08-03 Thread Tom Lane 
"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> You are connected to database "regression" as user "postgres"
>> via local socket in "/tmp" at port "5432".
 
> +1

Looking at the code, I notice another problem, which is that it's a
rather egregious violation of the rule against assembling messages
out of small phrases that would have to be translated separately.
I realize that this is a pre-existing problem in \connect and the
\conninfo patch just copied it, but that doesn't make it OK.

The problem in \connect is that somebody made an arbitrary decision to
print only the parameters that changed, which they weren't even too
consistent about since the database name is always printed.  Maintaining
that behavior exactly would require quite a large number of variant
messages.  What I suggest we do in \connect is always print the database
and user names, plus print all of the addressing info if any of it
changed.  This would mean three translatable messages there:

You are now connected to database "%s" as user "%s".
You are now connected to database "%s" as user "%s" on host "%s" at port "%s".
You are now connected to database "%s" as user "%s" via local socket in "%s" at 
port "%s".

while \conninfo would have two translatable messages corresponding
to the last two cases.

BTW, the word "local" seems to be useless extra verbiage; any
objections to making it just read "via socket in"?

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

Re: [HACKERS] review: xml_is_well_formed

2010-08-03 Thread Pavel Stehule 
Hello

2010/8/3 Peter Eisentraut :
> On lör, 2010-07-31 at 13:40 -0400, Robert Haas wrote:
>> > Well-formedness should probably only allow XML documents.
>>
>> I think the point of this function is to determine whether a cast to
>> xml will throw an error.  The behavior should probably match exactly
>> whatever test would be applied there.
>
> Maybe there should be
>
> xml_is_well_formed()
> xml_is_well_formed_document()
> xml_is_well_formed_content()
>
> I agree that consistency with SQL/XML is desirable, but for someone
> coming from the outside, the unqualified claim that 'foo' is well-formed
> XML might sound suspicious.
>

yes, it is little bit curious - but it can be just documented. Now, I
don't think, so we need more functions.

Regards

Pavel

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

Re: [HACKERS] Status report on writeable CTEs

2010-08-03 Thread Marko Tiikkaja 

On 8/3/2010 7:30 PM, Hitoshi Harada wrote:

As hackers say, the first to try should be Marko's first design that
use the list of tuplestore and DTScanNode. So if he has solid image to
reach there, we can wait for him to complete his first compilable
version. Then let's take it back and forth. Is it possible?


I am currently working on a version that treats all WITH queries like 
wCTEs.  My progress can be seen in my git repo [1], branch "wcte".  In 
its current form, the patch compiles and passes all applicable 
regression tests but it's still very messy.  I'm going to send a cleaner 
WIP patch to the list the minute I have one, but anyone's free to look 
at the git repo (and even work on it if they want!).



And I concern we might not have concrete consensus about list of
features in document form. Does it accept Recursive query? What if x
refers to y that refers to x cyclicly? An external design sometimes
fix the internal design, and it helps people to review the
implementation. If I missed something please point me to the link.


A recursive query should be fine as long as 1) it's SELECT-only and 2) 
it doesn't loop forever.  A wCTE can of course refer to the result of 
the recursive SELECT query with INSERT .. SELECT, UPDATE .. FROM or 
DELETE .. USING.  Cyclic dependencies are out of the scope of this 
patch; I'm not planning on adding any new features to regular CTEs.



[1] http://git.postgresql.org/gitweb?p=users/johto/postgres.git;a=summary


Regards,
Marko Tiikkaja

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

Re: [HACKERS] [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.

2010-08-03 Thread Kevin Grittner 
Tom Lane  wrote:
 
> You are connected to database "regression" as user "postgres"
> via local socket in "/tmp" at port "5432".
 
+1
 
-Kevin

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

Re: [HACKERS] [COMMITTERS] pgsql: Add \conninfo command to psql, to show current connection info.

2010-08-03 Thread Tom Lane 
rh...@postgresql.org (Robert Haas) writes:
> Add \conninfo command to psql, to show current connection info.
> David Christensen. Reviewed by Steve Singer.  Some further changes by me.

Looking at the output from this command:

regression=# \conninfo 
You are connected to database "regression" via local socket in "/tmp" at port 
"5432" as user "postgres".

This seems a bit awkwardly phrased.  I think it would read better if the
ordering was changed to

You are connected to database "regression" as user "postgres" via local socket 
in "/tmp" at port "5432".

I can't put my finger on the reason why the current ordering seems like
awkward English, but I can give a couple of concrete arguments for
changing it:

1. This way would match the argument ordering for \connect,
which in case you've forgotten is dbname user host port.

2. At least to me, this seems closer to the relative importance of the
items.

Comments, objections?

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

Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname

2010-08-03 Thread Boszormenyi Zoltan 
Kevin Grittner írta:
> Boszormenyi Zoltan  wrote:
>  
>   
>> attached is a patch that adds the missing feature
>> 
>  
>   
>> I certainly feel that this should be applied to 9.0 as a bugfix.
>> 
>  
> Those two statements seem to contradict one another.

PostgreSQL 8.3 or so added WHERE CURRENT OF  at the SQL level.

9.0 added ECPG's dynamic cursorname (a char variable carries the actual
cursor name)
but the WHERE CURRENT OF part was not converted, which was definitely an
oversight. Whether this is a "missing feature" or a "bugfix", it's only
a difference in
points of view. I think this patch belongs to 9.0.

>   Is there some
> bug manifestation beyond an unimplemented feature this fixes? 
> Without this, is there some regression going from 8.4 to 9.0?
>   

I haven't looked at 8.4's regression tests but pgtypeslib/numeric.c in
8.4.4 has
the same problem, so the second patch (at least the numeric.c part) can be
applied there as well, maybe in even older branches.

Best regards,
Zoltán Böszörményi


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

Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname

2010-08-03 Thread Kevin Grittner 
Boszormenyi Zoltan  wrote:
 
> attached is a patch that adds the missing feature
 
> I certainly feel that this should be applied to 9.0 as a bugfix.
 
Those two statements seem to contradict one another.  Is there some
bug manifestation beyond an unimplemented feature this fixes? 
Without this, is there some regression going from 8.4 to 9.0?
 
-Kevin


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

[HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname

2010-08-03 Thread Boszormenyi Zoltan 
Hi,

attached is a patch that adds the missing feature to use
"WHERE CURRENT OF :curname" in UPDATE and
DELETE statements via ECPG. I used the current CVS MAIN
but also applies almost cleanly to 9.0beta4. I certainly feel that
this should be applied to 9.0 as a bugfix.

The execute.c changes were required because

1. The statement

UPDATE table SET fld1 = :input1
WHERE CURRENT OF :curname
RETURNING id + :input2;

is transformed into

UPDATE table SET fld1 = $1
WHERE CURRENT OF $0
RETURNING id + $2;

and the $0 is past $1. The current code cannot deal with such
a messed up order, and scanning the original query twice is
needed, once for $0 substitution, once for mapping $1, etc. to
the other input variables.

2. With such a statement and auto-prepare turned on, I always got

SQL error: there is no parameter $0 on line X

It turned out that the statement was prepared by the auto-prepare
machinery
before the $0 substitution. PostgreSQL allows
PREPARE mystmt AS UPDATE ... WHERE CURRENT OF mycur
even if "mycur" is currently unknown to the system. It's resolved upon
executing the prepared query, so we should allow it in ECPG even with
dynamic cursorname.

The code survives "make check" and I also went through all the regression
tests manually to check them with valgrind to see that there's no leak.
As a result, another patch is attached that fixes two memory leaks in
PGTYPESnumeric_from_asc() and PGTYPESnumeric_to_long() and
quite some leaks in the regression tests themselves.

Best regards,
Zoltán Böszörményi

diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql-wherecurrentof/src/backend/parser/gram.y
*** pgsql.orig/src/backend/parser/gram.y	2010-07-26 10:05:46.0 +0200
--- pgsql-wherecurrentof/src/backend/parser/gram.y	2010-08-03 10:45:41.0 +0200
*** where_clause:
*** 8201,8207 
  /* variant for UPDATE and DELETE */
  where_or_current_clause:
  			WHERE a_expr			{ $$ = $2; }
! 			| WHERE CURRENT_P OF name
  {
  	CurrentOfExpr *n = makeNode(CurrentOfExpr);
  	/* cvarno is filled in by parse analysis */
--- 8201,8207 
  /* variant for UPDATE and DELETE */
  where_or_current_clause:
  			WHERE a_expr			{ $$ = $2; }
! 			| WHERE CURRENT_P OF cursor_name
  {
  	CurrentOfExpr *n = makeNode(CurrentOfExpr);
  	/* cvarno is filled in by parse analysis */
diff -dcrpN pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c pgsql-wherecurrentof/src/interfaces/ecpg/ecpglib/execute.c
*** pgsql.orig/src/interfaces/ecpg/ecpglib/execute.c	2010-07-11 11:15:00.0 +0200
--- pgsql-wherecurrentof/src/interfaces/ecpg/ecpglib/execute.c	2010-08-03 16:50:43.0 +0200
*** ecpg_store_input(const int lineno, const
*** 1082,1099 
  	return true;
  }
  
! static void
! free_params(const char **paramValues, int nParams, bool print, int lineno)
  {
  	int			n;
  
! 	for (n = 0; n < nParams; n++)
  	{
  		if (print)
! 			ecpg_log("free_params on line %d: parameter %d = %s\n", lineno, n + 1, paramValues[n] ? paramValues[n] : "null");
! 		ecpg_free((void *) (paramValues[n]));
  	}
! 	ecpg_free(paramValues);
  }
  
  
--- 1082,1109 
  	return true;
  }
  
! void
! ecpg_free_params(struct statement *stmt, bool print, int lineno)
  {
  	int			n;
  
! 	for (n = 0; n < stmt->nparams; n++)
  	{
  		if (print)
! 			ecpg_log("free_params on line %d: parameter %d = %s\n", lineno, n + 1, stmt->param_values[n] ? stmt->param_values[n] : "null");
! 		ecpg_free((void *) (stmt->param_values[n]));
  	}
! 	ecpg_free(stmt->param_values);
! 
! 	stmt->nparams = 0;
! 	stmt->param_values = NULL;
! 
! 	for (n = 0; n < stmt->ndollarzero; n++)
! 		ecpg_free((void *) (stmt->dollarzero[n]));
! 	ecpg_free(stmt->dollarzero);
! 
! 	stmt->ndollarzero = 0;
! 	stmt->dollarzero = NULL;
  }
  
  
*** insert_tobeinserted(int position, int ph
*** 1130,1135 
--- 1140,1203 
  }
  
  static bool
+ ecpg_replace_inline_params(struct statement * stmt)
+ {
+ 	struct variable *var;
+ 	int			position = 0;
+ 
+ 	/*
+ 	 * We have to check the $0 inline parameters first, they can appear
+ 	 * after $1, e.g. in this example:
+ 	 * EXEC SQL UPDATE table SET f1 = :in1 WHERE CURRENT OF :curname RETURNING id + :in2;
+ 	 * transformed statement is:
+ 	 * "update table set f1 = $1 WHERE CURRENT OF $0 RETURNING id + $2"
+ 	 */
+ 	var = stmt->inlist;
+ 	while (var)
+ 	{
+ 		char	   *tobeinserted;
+ 
+ 		tobeinserted = NULL;
+ 
+ 		if ((position = next_insert(stmt->command, position, stmt->questionmarks) + 1) == 0)
+ 			break;
+ 
+ 		/*
+ 		 * if the placeholder is '$0' we have to replace it on the client side
+ 		 * this is for places we want to support variables at that are not
+ 		 * supported in the backend
+ 		 */
+ 		if (stmt->command[position] == '0')
+ 		{
+ 			const char **dollarzero;
+ 
+ 			if (!ecpg_store_input(stmt->lineno, stmt->force_indicator, var, &tobeinserted, false))
+ return false;
+ 
+

Re: [HACKERS] reducing NUMERIC size for 9.1, take two

2010-08-03 Thread Brendan Jurd 
On 31 July 2010 07:58, Robert Haas  wrote:
> Here's a second version of the main patch, in which I have attempted
> to respond to Tom's concerns/suggestions.
>
> (There is still a small, side issue with numeric_maximum_size() which
> I plan to fix, but this patch is the good stuff.)
>

Applies fine, compiles fine, passes regression tests, and demonstrates
the same space reduction seen with the previous version of the patch.

Marking Ready for Committer.

Cheers,
BJ

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

Re: [HACKERS] Two problems when using Postgresql8.3.7, Please help me!

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 12:34 PM, Richard  wrote:
1.To add live HA  to PG, I transfer WAL of a database instance(Primary
node) to another database instance (standby node) at real time, and
keep startup alive in standby node to recovery WAL online,so that
standby node can be a hot standby.
> But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereportERROR
> when mdread function enter ERROR branch, and I got message like this "could not read block X of
> relation X/X/X: read only Xof X bytes". I spent two days to figure out what happened, but it is too hard.Please help me.
>
> 2. When restore data from a LIVE backup , I got message like "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time of backup dump". It seems  the WAL was
> corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong?

Please see http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

I recently troubleshot a problem much like the one you describe in (2)
and I believe in that case it was a bad disk subsystem that started
failing writes and didn't tell PG.  But it could also be caused by
misconfiguration.  Since you haven't given any details about your
configuration or what steps you've tried to take to resolve the
problem, it's pretty difficult to guess what's going on in your case.

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

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

Re: [HACKERS] Two problems when using Postgresql8.3.7, Please help me!

2010-08-03 Thread Joshua D. Drake 
On Wed, 2010-08-04 at 00:34 +0800, Richard wrote:
> 1.To add live HA  to PG, I transfer WAL of a database instance(Primary node) 
> to another database instance (standby node) at real time, and keep startup 
> alive in standby node to recovery WAL online,so that standby node can be a 
> hot standby.
> But I got some trouble. When standby node switch to primary mode to accept 
> connections, of course after startup initilize the WAL and exit, the postgres 
> process ereportERROR 
> when mdread function enter ERROR branch, and I got message like this
> "could not read block X of  relation X/X/X: read only Xof X bytes". I
> spent two days to figure out what happened, but it is too hard.Please
> help me.   

What are you using to transfer the WAL?

Did you read this chapter?

http://www.postgresql.org/docs/8.3/static/continuous-archiving.html

Sincerely,

Joshua D. Drake

> 

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


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

[HACKERS] Two problems when using Postgresql8.3.7, Please help me!

2010-08-03 Thread Richard 
1.To add live HA  to PG, I transfer WAL of a database instance(Primary node) to 
another database instance (standby node) at real time, and keep startup alive 
in standby node to recovery WAL online,so that standby node can be a hot 
standby.
But I got some trouble. When standby node switch to primary mode to accept 
connections, of course after startup initilize the WAL and exit, the postgres 
process ereportERROR 
when mdread function enter ERROR branch, and I got message like this "could not 
read block X of  relation X/X/X: read only Xof X bytes". I spent two days to 
figure out what happened, but it is too hard.Please help me.   

2. When restore data from a LIVE backup , I got message like "unexpected 
pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time 
of backup dump". It seems  the WAL was 
corrupted. I found the LSN where the error occured contained the wrong 
pageaddr, the pageaddr was 8K before it's real address.What was wrong? 

2010-08-04 



Richard

Re: [HACKERS] Per-column collation, proof of concept

2010-08-03 Thread Peter Eisentraut 
On mån, 2010-08-02 at 01:43 -0500, Jaime Casanova wrote:
> nowadays, CREATE DATABASE has a lc_collate clause. is the new collate
> clause similar as the lc_collate?
> i mean, is lc_collate what we will use as a default?

Yes, if you do not specify anything per column, the database default is
used.

How to integrate the per-database or per-cluster configuration with the
new system is something to figure out in the future.

> if yes, then probably we need to use pg_collation there too because
> lc_collate and the new collate clause use different collation names.
> """
> postgres=# create database test with lc_collate 'en_US.UTF-8';
> CREATE DATABASE
> test=# create table t1 (col1 text collate "en_US.UTF-8");
> ERROR:  collation "en_US.UTF-8" does not exist
> test=# create table t1 (col1 text collate "en_US.utf8");
> CREATE TABLE
> """

This is something that libc does for you.  The locale as listed by
locale -a is called "en_US.utf8", but apparently libc takes
"en_US.UTF-8" as well.

> also i got errors from regression tests when MULTIBYTE=UTF8
> (attached). it seems i was trying to create locales that weren't
> defined on locales.txt (from were was fed that file?). i added a line
> to that file (for es_EC.utf8) then i create a table with a column
> using that collate and execute "select * from t2 where col1 > 'n'; "
> and i got this error: "ERROR:  could not create locale "es_EC.utf8""
> (of course, that last part was me messing the things up, but it show
> we shouldn't be using a file locales.txt, i think)

It might be that you don't have those locales installed in your system.
locales.txt is created by using locale -a.  Check what that gives you.

> i can attach a collate to a domain but i can't see where are we
> storing that info (actually it says it's not collatable):

Domain support is not done yet.



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

Re: [HACKERS] Status report on writeable CTEs

2010-08-03 Thread Hitoshi Harada 
2010/7/21 David Fetter :
> On Sat, Jul 17, 2010 at 01:15:22AM +0900, Hitoshi Harada wrote:
>> 2010/7/17 Marko Tiikkaja :
>> > On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:
>> >> 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
>> >> is exsiting one that work with single tuplestore, it might be sane to
>> >> modify this so that it accepts tuplestore from Query instead of its
>> >> child node.
>> >
>> > I thought about this, but I don't necessarily like the idea of overloading
>> > executor nodes.
>>
>> Neither do I have good shape for this solution. Maybe it's not good
>> idea. But my concern is adding DtScanNode, which looks similar to
>> MaterialNode. Of course each purpose is different, but quite big part
>> will overlap each other, I think.
>
> Any ideas as to how to factor this out?  Handiest ideas would be in
> the form of a patch ;)

Yeah, that would be handiest, but I think I must wait for his first
compilable patch to modify to try the idea. Current version looks
quite messy and can't build.

>> >> 2. Use temp table instead of tuplestore list. Since we agreed we need
>> >> to execute each plan one by one starting and shutting down executor,
>> >> it now looks very simple strategy.
>> >
>> > I didn't look at this because I thought using a "tuplestore receiver" in 
>> > the
>> > portal logic was simple enough.  Any thoughts on how this would work?
>>
>> It's just deconstructing queries like:
>>
>> WITH t AS (INSERT INTO x ... RETURING *)
>> SELECT * FROM t;
>>
>> to
>>
>> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
>> SELECT * FROM t;
>>
>> While the second statement is not implemented yet, it will be so simpler.
>
> So CTAS would get expanded into CTA[row-returning query] ?
> Interesting.  How much work would that part be?

FWIW, this is getting interesting to me these days, and I think this
can be separated from wCTE

As hackers say, the first to try should be Marko's first design that
use the list of tuplestore and DTScanNode. So if he has solid image to
reach there, we can wait for him to complete his first compilable
version. Then let's take it back and forth. Is it possible?

And I concern we might not have concrete consensus about list of
features in document form. Does it accept Recursive query? What if x
refers to y that refers to x cyclicly? An external design sometimes
fix the internal design, and it helps people to review the
implementation. If I missed something please point me to the link.


Regards,


-- 
Hitoshi Harada

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

Re: [HACKERS] Develop item from TODO list

2010-08-03 Thread Joshua D. Drake 
On Tue, 2010-08-03 at 11:10 -0400, Robert Haas wrote:
> On Tue, Aug 3, 2010 at 9:01 AM, Thom Brown  wrote:
> > I can't answer the main question, but you can try Anjuta as a C
> > editor: http://www.anjuta.org/
> 
> Or vi.

cough.

The hint here would be:

>As we have few experience with open-source programs, can someone 
>recommend a good C-editor for Ubuntu?

I doubt they are unix/linux people at all. Putting them into Vi land is
a bit torturous.

There are quite a few editors out there but here are some that are going
to be a bit more user friendly (in terms of learning curve):

Bluefish
Anjuta
Kate

If you want a full environment with projects, SCM integration etc... I
would suggest Eclipse.

That said, if you can get a handle on VI/VIM or (joe :P) you will
probably be pleased with the efficiency.

Sincerely,

Joshua D. Drake



-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


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

[HACKERS] Too problems when using Postgresql8.3.7,Please help!

2010-08-03 Thread Richard 
1.To add live HA  to PG, I transfer WAL of a database instance(Primary node) to 
another database instance (standby node) at real time, and keep startup alive 
in standby node to recovery WAL online,so that standby node can be a hot 
standby.But I got some trouble. When standby node switch to primary mode to 
accept connections, of course after startup initilize the WAL and exit, the 
postgres process ereport(ERROR when mdread function enter ERROR branch, and I 
got message like this "could not read block X of   relation X/X/X: read only %d 
of %d bytes". I spent two days to figure out what happened, but it is too 
hard.Please help me.   

2. When restore data from a LIVE backup , I got message like "unexpected 
pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time 
of backup dump". It seems  the WAL was 
corrupted. I found the LSN where the error occured contained the wrong 
pageaddr, the pageaddr was 8K before it's real address.What was wrong? 
--
Richard
2010-08-03


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

Re: [HACKERS] patch for check constraints using multiple inheritance

2010-08-03 Thread Yeb Havinga 

Robert Haas wrote:

On Mon, Aug 2, 2010 at 2:56 PM, Yeb Havinga  wrote:

Hence the ATOneLevelRecursion routing is usable in its
current form for all callers during the prep stage, and not only
ATPrepAddColumn.


Well, only if they happen to want the "visit each table only once"
behavior, which might not be true for every command type.
It is actually "visit each table only once for each distinct parent". 
Looking at the command types for ALTER TABLE, I see none where this 
behaviour would be incorrect.


That put aside, the top1/top2 example is interesting in the sense that 
it reveals other problems besides the wrong attinhcount at the basement. 
For an example see the script below. The underlying cause is the failure 
of the code to recognize that if relation C inherits from both A and B, 
where A and B both have column x, that A.x 'is the same as' B.x, where 
the 'is the same as' relation is the same that holds for (A.x, C.x) and 
(B.x, C.x), which the code does a lot of trouble for to recognize. This 
means that if some definition is altered on A.x, only C.x is updated and 
B.x not touched. IMO this is wrong and either a multiple inheritance 
structure like this should be prohibited, since the user did not 
explicitly declare that A.x and B.x 'are the same' (by e.g. defining a 
relation D.x and have A and B inherit from that), or the code should 
update parents of relations when the childs are updated.


The difficulty is in exactly specifying the 'is the same' as relation, 
i.e. under what conditions are columns A.x and B.x allowed to be merged 
to C.x. In the regression test there's only a small amount of tests, but 
one of them shows that the 'is the same' as relation does not mean 
everything is the same, as it shows that default values may differ.


regards,
Yeb Havinga


DROP SCHEMA IF EXISTS test_inheritance CASCADE;
CREATE SCHEMA test_inheritance;
SET search_path TO test_inheritance;

CREATE TABLE top1 (i int);
CREATE TABLE top2 (i int);
CREATE TABLE bottom () INHERITS (top1, top2);
CREATE TABLE basement () INHERITS (bottom);

ALTER TABLE top1 ADD COLUMN a_table_column INTEGER;
ALTER TABLE top2 ADD COLUMN a_table_column INTEGER;

SELECT t.oid, t.relname, a.attinhcount, a.attname
FROM pg_class t
JOIN pg_attribute a ON (a.attrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%'
ORDER BY oid;

ALTER TABLE top1 RENAME COLUMN a_table_column TO another_table_column;

SELECT t.oid, t.relname, a.attinhcount, a.attname
FROM pg_class t
JOIN pg_attribute a ON (a.attrelid = t.oid)
JOIN pg_namespace n ON (t.relnamespace = n.oid)
WHERE n.nspname = 'test_inheritance' AND a.attname LIKE '%table_column%'
ORDER BY oid;

ALTER TABLE top2 RENAME COLUMN a_table_column TO another_table_column;


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

Re: [HACKERS] review: xml_is_well_formed

2010-08-03 Thread Peter Eisentraut 
On lör, 2010-07-31 at 13:40 -0400, Robert Haas wrote:
> > Well-formedness should probably only allow XML documents.
> 
> I think the point of this function is to determine whether a cast to
> xml will throw an error.  The behavior should probably match exactly
> whatever test would be applied there.

Maybe there should be

xml_is_well_formed()
xml_is_well_formed_document()
xml_is_well_formed_content()

I agree that consistency with SQL/XML is desirable, but for someone
coming from the outside, the unqualified claim that 'foo' is well-formed
XML might sound suspicious.


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

Re: [HACKERS] Develop item from TODO list

2010-08-03 Thread Robert Haas 
On Tue, Aug 3, 2010 at 9:01 AM, Thom Brown  wrote:
> I can't answer the main question, but you can try Anjuta as a C
> editor: http://www.anjuta.org/

Or vi.

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

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

Re: [HACKERS] Develop item from TODO list

2010-08-03 Thread Tom Lane 
Viktor Valy  writes:
> We are 2 Students from the Technical University of Vienna. At our internship
> we would like to develop the item of the TODO list: "Allow SET CONSTRAINTS
> to be qualified by schema/table name".
> Is anyone working on it?

Uh, it was done years ago, AFAICS, unless the Todo entry means something
non-obvious.

regression=# create schema foo;
CREATE SCHEMA
regression=# create table foo.bar (f1 int unique deferrable);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "bar_f1_key" for 
table "bar"
CREATE TABLE
regression=# set constraints foo.bar_f1_key deferred;
SET CONSTRAINTS
regression=# set constraints foo.bar_f1_key immediate;
SET CONSTRAINTS
regression=# 

Bruce, do you remember what that entry was really about?

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

Re: [HACKERS] Develop item from TODO list

2010-08-03 Thread Thom Brown 
On 3 August 2010 13:57, Viktor Valy  wrote:
> Hello!
> We are 2 Students from the Technical University of Vienna. At our internship
> we would like to develop the item of the TODO list: "Allow SET CONSTRAINTS
> to be qualified by schema/table name".
> Is anyone working on it?
> Our research at the SET CONTRAINTS function showed the following Error:
> ERROR: cross-database references are not implemented:
> "schema.table.constraint"
> SQL state: 0A000
> Is this the problem we could implement?
> Approximately where is the point to implement?
> As we have few experience with open-source programs, can someone recommend a
> good C-editor for Ubuntu?
> What do others use?
> Thanks in advance,
> Chris & Viktor

I can't answer the main question, but you can try Anjuta as a C
editor: http://www.anjuta.org/

-- 
Thom Brown
Registered Linux user: #516935

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

[HACKERS] Develop item from TODO list

2010-08-03 Thread Viktor Valy 
Hello!

We are 2 Students from the Technical University of Vienna. At our internship
we would like to develop the item of the TODO list: "Allow SET CONSTRAINTS
to be qualified by schema/table name".
Is anyone working on it?

Our research at the SET CONTRAINTS function showed the following Error:
*ERROR: cross-database references are not implemented:
"schema.table.constraint"*
*SQL state: 0A000*

Is this the problem we could implement?
Approximately where is the point to implement?

As we have few experience with open-source programs, can someone recommend a
good C-editor for Ubuntu?
What do others use?

Thanks in advance,

Chris & Viktor

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Pavel Stehule 
2010/8/3 Hitoshi Harada :
> 2010/8/3 Pavel Stehule :
>> Hello
>>
>> 2010/8/3 Joshua Tolley :
>>> In case anyone's interested, I've taken the CTE-based grouping sets patch 
>>> from
>>> [1] and made it apply to 9.1, attached. I haven't yet done things like 
>>> checked
>>> it for whitespace consistency, style conformity, or anything else, but 
>>> (tuits
>>> permitting) hope to figure out how it works and get it closer to 
>>> commitability
>>> in some upcoming commitfest.
>>>
>>> I mention it here so that if someone else is working on it, we can avoid
>>> duplicated effort, and to see if a CTE-based grouping sets implementation is
>>> really the way we think we want to go.
>>>
>>
>> I am plaing with it now :). I have a plan to replace CTE with similar
>> but explicit executor node. The main issue of existing patch was using
>> just transformation to CTE. I agree, so it isn't too much extensiable
>> in future. Now I am cleaning identifiers little bit. Any colaboration
>> is welcome.
>>
>> My plan:
>> 1) clean CTE patch
>> 2) replace CTE with explicit executor node, but still based on tuplestore
>> 3) when will be possible parallel processing based on hash agg - then
>> we don't need to use tuplestore
>
> Couldn't you explain what exactly "explicit executor node"? I hope we
> can share your image to develop it further than only transformation to
> CTE.

I have a one reason

Implementation based on CTE doesn't create space for possible
optimalisations (I think now, maybe it isn't true). It is good for
initial or referencial implementation - but it can be too complex,
when we will try to append some optimalizations - like parallel hash
agg processing, direct data reading without tuplestore. If you are, as
CTE author, thinking so these features are possible in non recursive
CTE too, I am not agains. I hope so this week I'll have a CTE based
patch - and we can talk about next direction. I see as possible
performance issue using a tuplestore - there are lot of cases where
repeating of source query can be faster.

If I remember well, Tom has a objection, so transformation to CTE is
too early - in parser. So It will be first change. Executor node can
be CTE.

regards

Pavel

p.s. I am sure, so there are lot of task, that can be solved together
with non recursive CTE.

>
>
> Regards,
>
> --
> Hitoshi Harada
>

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

Re: [HACKERS] GROUPING SETS revisited

2010-08-03 Thread Hitoshi Harada 
2010/8/3 Pavel Stehule :
> Hello
>
> 2010/8/3 Joshua Tolley :
>> In case anyone's interested, I've taken the CTE-based grouping sets patch 
>> from
>> [1] and made it apply to 9.1, attached. I haven't yet done things like 
>> checked
>> it for whitespace consistency, style conformity, or anything else, but (tuits
>> permitting) hope to figure out how it works and get it closer to 
>> commitability
>> in some upcoming commitfest.
>>
>> I mention it here so that if someone else is working on it, we can avoid
>> duplicated effort, and to see if a CTE-based grouping sets implementation is
>> really the way we think we want to go.
>>
>
> I am plaing with it now :). I have a plan to replace CTE with similar
> but explicit executor node. The main issue of existing patch was using
> just transformation to CTE. I agree, so it isn't too much extensiable
> in future. Now I am cleaning identifiers little bit. Any colaboration
> is welcome.
>
> My plan:
> 1) clean CTE patch
> 2) replace CTE with explicit executor node, but still based on tuplestore
> 3) when will be possible parallel processing based on hash agg - then
> we don't need to use tuplestore

Couldn't you explain what exactly "explicit executor node"? I hope we
can share your image to develop it further than only transformation to
CTE.


Regards,

-- 
Hitoshi Harada

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

Re: [HACKERS] review: psql: edit function, show function commands patch

2010-08-03 Thread Pavel Stehule 
Hello

I hope so I found and fixed last issue - the longer functions was
showed directly - without a pager.

Regards

Pavel

2010/8/3 Pavel Stehule :
> Hello
>
> 2010/8/3 Pavel Stehule :
>> attached updated patch
>>
>> * don't use a default option for navigation in editor - user have to
>> set this option explicitly
>> * name for this psql variable is EDITOR_LINENUMBER_SWITCH -
>> * updated comments, doc and some issues described by Robert
>>
>> Regards
>>
>> Pavel Stehule
>
> I found a small bug - the last patch better handle parsing lineno
> after function descriptor
>
> Regards
>
> Pavel
>
>>
>> 2010/8/3 Robert Haas :
>>> On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas  wrote:
> b) more robust algorithm for header rows identification

 Have not gotten to this one yet.
>>>
>>> I notIce that on WIN32 the default editor is notepad.exe and the
>>> default editor navigation option is "/".  Does "notepad.exe /lineno
>>> filename" actually work on Windows?  A quick Google search suggests
>>> that the answer is "no", which seems somewhat unfortunate: it means
>>> we'd be shipping "broken on Win32 out of the box".
>>>
>>> http://www.robvanderwoude.com/commandlineswitches.php#Notepad
>>>
>>> This is actually my biggest concern about this patch - that it may be
>>> just too much of a hassle to actually make it work for people.  I just
>>> tried setting $EDITOR to MacOS's TextEdit program, and it turns out
>>> that TextEdit doesn't understand +.  I'm afraid that we're going to
>>> end up with a situation where it only works for people using emacs or
>>> vi, and everyone else ends up with a broken install (and, possibly, no
>>> clear idea how to fix it).  Perhaps it would be better to accept \sf
>>> and reject \sf+ and \ef  .
>>>
>>> Assuming we get past that threshold issue, I'm also wondering whether
>>> the "navigation option" terminology is best; e.g. set
>>> PSQL_EDITOR_NAVIGATION_OPTION to configure it.  It doesn't seem
>>> terrible, but it doesn't seem great, either.  Anyone have a better
>>> idea?
>>>
>>> The docs are a little rough; they could benefit from some editing by a
>>> fluent English speaker.  If anyone has time to work on this, it would
>>> be much appreciated.
>>>
>>> Instead of "line number is unacceptable", I think you should write
>>> "invalid line number".
>>>
>>> "dollar" should not be spelled "dolar".  "function" should not be
>>> spelled "finction".
>>>
>>> This change looks suspiciously like magic.  If it's actually safe, it
>>> needs a comment explaining why:
>>>
>>> -       sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1);
>>> +       sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1);
>>>
>>> Attempting to compile with this patch applied emits a warning on my
>>> machine; whether or not the warning is valid, you need to make it go
>>> away:
>>>
>>> command.c: In function ‘HandleSlashCmds’:
>>> command.c:1055: warning: ‘bsep’ may be used uninitialized in this function
>>> command.c:1055: note: ‘bsep’ was declared here
>>>
>>> Why does the \sf output have a trailing blank line?  This seems weird,
>>> especially because \ef puts no such trailing blank line in the editor.
>>>
>>> Instead of:
>>>
>>> +       /* skip useles whitespaces */
>>> +       while (c >= func)
>>> +               if (isblank(*c))
>>> +                       c--;
>>> +               else
>>> +                       break;
>>>
>>> ...wouldn't it be just as good to write:
>>>
>>> while (c >= func && isblank(*c))
>>>    c--;
>>>
>>> (and similarly elsewhere)
>>>
>>> In extract_separator, if you invert the sense of the first if-test,
>>> then you can avoid having to indent the entire function contents.
>>>
>>> --
>>> Robert Haas
>>> EnterpriseDB: http://www.enterprisedb.com
>>> The Enterprise Postgres Company
>>>
>>
>
*** ./doc/src/sgml/ref/psql-ref.sgml.orig	2010-08-03 09:00:48.384710383 +0200
--- ./doc/src/sgml/ref/psql-ref.sgml	2010-08-03 10:44:57.312835131 +0200
***
*** 1339,1345 
  
  

! \edit (or \e)  filename 
  
  
  
--- 1339,1345 
  
  

! \edit (or \e)  filename   linenumber 
  
  
  
***
*** 1369,1380 
  systems, notepad.exe on Windows systems.
  
  
  

  
  

! \ef  function_description 
  
  
  
--- 1369,1387 
  systems, notepad.exe on Windows systems.
  
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor (The psql's variable EDITOR_LINENUMBER_SWITCH
+ have to be filled).
+ 
  

  
  

! \ef  function_description   linenumber  
  
  
  
***
*** 1397,1402 
--- 1404,1417 
   If no function is specified, a blank CREATE FUNCTION
   template is presented for editing.
  
+ 
+ 
+ If li

Re: [HACKERS] review: psql: edit function, show function commands patch

2010-08-03 Thread Pavel Stehule 
Hello

2010/8/3 Pavel Stehule :
> attached updated patch
>
> * don't use a default option for navigation in editor - user have to
> set this option explicitly
> * name for this psql variable is EDITOR_LINENUMBER_SWITCH -
> * updated comments, doc and some issues described by Robert
>
> Regards
>
> Pavel Stehule

I found a small bug - the last patch better handle parsing lineno
after function descriptor

Regards

Pavel

>
> 2010/8/3 Robert Haas :
>> On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas  wrote:
 b) more robust algorithm for header rows identification
>>>
>>> Have not gotten to this one yet.
>>
>> I notIce that on WIN32 the default editor is notepad.exe and the
>> default editor navigation option is "/".  Does "notepad.exe /lineno
>> filename" actually work on Windows?  A quick Google search suggests
>> that the answer is "no", which seems somewhat unfortunate: it means
>> we'd be shipping "broken on Win32 out of the box".
>>
>> http://www.robvanderwoude.com/commandlineswitches.php#Notepad
>>
>> This is actually my biggest concern about this patch - that it may be
>> just too much of a hassle to actually make it work for people.  I just
>> tried setting $EDITOR to MacOS's TextEdit program, and it turns out
>> that TextEdit doesn't understand +.  I'm afraid that we're going to
>> end up with a situation where it only works for people using emacs or
>> vi, and everyone else ends up with a broken install (and, possibly, no
>> clear idea how to fix it).  Perhaps it would be better to accept \sf
>> and reject \sf+ and \ef  .
>>
>> Assuming we get past that threshold issue, I'm also wondering whether
>> the "navigation option" terminology is best; e.g. set
>> PSQL_EDITOR_NAVIGATION_OPTION to configure it.  It doesn't seem
>> terrible, but it doesn't seem great, either.  Anyone have a better
>> idea?
>>
>> The docs are a little rough; they could benefit from some editing by a
>> fluent English speaker.  If anyone has time to work on this, it would
>> be much appreciated.
>>
>> Instead of "line number is unacceptable", I think you should write
>> "invalid line number".
>>
>> "dollar" should not be spelled "dolar".  "function" should not be
>> spelled "finction".
>>
>> This change looks suspiciously like magic.  If it's actually safe, it
>> needs a comment explaining why:
>>
>> -       sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1);
>> +       sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1);
>>
>> Attempting to compile with this patch applied emits a warning on my
>> machine; whether or not the warning is valid, you need to make it go
>> away:
>>
>> command.c: In function ‘HandleSlashCmds’:
>> command.c:1055: warning: ‘bsep’ may be used uninitialized in this function
>> command.c:1055: note: ‘bsep’ was declared here
>>
>> Why does the \sf output have a trailing blank line?  This seems weird,
>> especially because \ef puts no such trailing blank line in the editor.
>>
>> Instead of:
>>
>> +       /* skip useles whitespaces */
>> +       while (c >= func)
>> +               if (isblank(*c))
>> +                       c--;
>> +               else
>> +                       break;
>>
>> ...wouldn't it be just as good to write:
>>
>> while (c >= func && isblank(*c))
>>    c--;
>>
>> (and similarly elsewhere)
>>
>> In extract_separator, if you invert the sense of the first if-test,
>> then you can avoid having to indent the entire function contents.
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise Postgres Company
>>
>
*** ./doc/src/sgml/ref/psql-ref.sgml.orig	2010-08-03 09:00:48.384710383 +0200
--- ./doc/src/sgml/ref/psql-ref.sgml	2010-08-03 10:44:57.312835131 +0200
***
*** 1339,1345 
  
  

! \edit (or \e)  filename 
  
  
  
--- 1339,1345 
  
  

! \edit (or \e)  filename   linenumber 
  
  
  
***
*** 1369,1380 
  systems, notepad.exe on Windows systems.
  
  
  

  
  

! \ef  function_description 
  
  
  
--- 1369,1387 
  systems, notepad.exe on Windows systems.
  
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor (The psql's variable EDITOR_LINENUMBER_SWITCH
+ have to be filled).
+ 
  

  
  

! \ef  function_description   linenumber  
  
  
  
***
*** 1397,1402 
--- 1404,1417 
   If no function is specified, a blank CREATE FUNCTION
   template is presented for editing.
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor. It count lines from start of function body, not from
+ start of text (The psql's variable EDITOR_LINENUMBER_SWITCH
+ have to be filled).
+

Re: [HACKERS] review: psql: edit function, show function commands patch

2010-08-03 Thread Pavel Stehule 
attached updated patch

* don't use a default option for navigation in editor - user have to
set this option explicitly
* name for this psql variable is EDITOR_LINENUMBER_SWITCH -
* updated comments, doc and some issues described by Robert

Regards

Pavel Stehule

2010/8/3 Robert Haas :
> On Sun, Aug 1, 2010 at 11:48 PM, Robert Haas  wrote:
>>> b) more robust algorithm for header rows identification
>>
>> Have not gotten to this one yet.
>
> I notIce that on WIN32 the default editor is notepad.exe and the
> default editor navigation option is "/".  Does "notepad.exe /lineno
> filename" actually work on Windows?  A quick Google search suggests
> that the answer is "no", which seems somewhat unfortunate: it means
> we'd be shipping "broken on Win32 out of the box".
>
> http://www.robvanderwoude.com/commandlineswitches.php#Notepad
>
> This is actually my biggest concern about this patch - that it may be
> just too much of a hassle to actually make it work for people.  I just
> tried setting $EDITOR to MacOS's TextEdit program, and it turns out
> that TextEdit doesn't understand +.  I'm afraid that we're going to
> end up with a situation where it only works for people using emacs or
> vi, and everyone else ends up with a broken install (and, possibly, no
> clear idea how to fix it).  Perhaps it would be better to accept \sf
> and reject \sf+ and \ef  .
>
> Assuming we get past that threshold issue, I'm also wondering whether
> the "navigation option" terminology is best; e.g. set
> PSQL_EDITOR_NAVIGATION_OPTION to configure it.  It doesn't seem
> terrible, but it doesn't seem great, either.  Anyone have a better
> idea?
>
> The docs are a little rough; they could benefit from some editing by a
> fluent English speaker.  If anyone has time to work on this, it would
> be much appreciated.
>
> Instead of "line number is unacceptable", I think you should write
> "invalid line number".
>
> "dollar" should not be spelled "dolar".  "function" should not be
> spelled "finction".
>
> This change looks suspiciously like magic.  If it's actually safe, it
> needs a comment explaining why:
>
> -       sys = pg_malloc(strlen(editorName) + strlen(fname) + 10 + 1);
> +       sys = pg_malloc(strlen(editorName) + strlen(fname) + 20 + 1);
>
> Attempting to compile with this patch applied emits a warning on my
> machine; whether or not the warning is valid, you need to make it go
> away:
>
> command.c: In function ‘HandleSlashCmds’:
> command.c:1055: warning: ‘bsep’ may be used uninitialized in this function
> command.c:1055: note: ‘bsep’ was declared here
>
> Why does the \sf output have a trailing blank line?  This seems weird,
> especially because \ef puts no such trailing blank line in the editor.
>
> Instead of:
>
> +       /* skip useles whitespaces */
> +       while (c >= func)
> +               if (isblank(*c))
> +                       c--;
> +               else
> +                       break;
>
> ...wouldn't it be just as good to write:
>
> while (c >= func && isblank(*c))
>    c--;
>
> (and similarly elsewhere)
>
> In extract_separator, if you invert the sense of the first if-test,
> then you can avoid having to indent the entire function contents.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
*** ./doc/src/sgml/ref/psql-ref.sgml.orig	2010-08-03 09:00:48.384710383 +0200
--- ./doc/src/sgml/ref/psql-ref.sgml	2010-08-03 10:44:57.312835131 +0200
***
*** 1339,1345 
  
  

! \edit (or \e)  filename 
  
  
  
--- 1339,1345 
  
  

! \edit (or \e)  filename   linenumber 
  
  
  
***
*** 1369,1380 
  systems, notepad.exe on Windows systems.
  
  
  

  
  

! \ef  function_description 
  
  
  
--- 1369,1387 
  systems, notepad.exe on Windows systems.
  
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor (The psql's variable EDITOR_LINENUMBER_SWITCH
+ have to be filled).
+ 
  

  
  

! \ef  function_description   linenumber  
  
  
  
***
*** 1397,1402 
--- 1404,1417 
   If no function is specified, a blank CREATE FUNCTION
   template is presented for editing.
  
+ 
+ 
+ If linenumber is
+ specified, then cursor is moved on this line after start of 
+ editor. It count lines from start of function body, not from
+ start of text (The psql's variable EDITOR_LINENUMBER_SWITCH
+ have to be filled).
+ 
  

  
***
*** 2116,2121 
--- 2131,2148 
  
  

+ \sf[+] function_description  linenumber  
+ 
+ 
+ 
+  This command fetches and shows the definition of the named