Re: [HACKERS] [PATCHES] COPY view
Hi, Robert Treat [EMAIL PROTECTED] writes: On Tuesday 22 August 2006 16:10, Tom Lane wrote: As I see it, we've effectively got a patch that was rejected once, and Bruce wants to apply it anyway because no replacement has been forthcoming. Well, unless someone is going to commit to doing it the other way, it seems the guy who actually codes something offers a better solution than handwaving... people have also had plenty of time to come up with a replacement if that's what they really wanted. The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. regards, tom lane I am willing to get it up to shape and support both COPY (select) TO and COPY view TO, the second is rewritten as SELECT * FROM view. In fact, I already started. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
--On Dienstag, August 22, 2006 23:12:21 -0400 Tom Lane [EMAIL PROTECTED] wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. What are these open issues for the updatable views patch you are seeing exactly? I'm currently trying to update this patch based on alvaros comments in the code and i see the INSERT...RETURNING stuff as the only big hurd at the moment (however, i haven't looked at this closer, but saw your and Jaime's comments on this...). It would be nice if we could summarize all open things so everybody who is able to work on this gets a complete overview. -- Thanks Bernd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] COPY view
On Tue, Aug 22, 2006 at 01:11:22PM -0400, Andrew Dunstan wrote: There's nothing hidden (unless it's also hidden from me ;-) ) I take it that when you talk about we did this you are referring to the patch from Karel Zak. Hans has been original author of COPY VIEW idea and I've wrote it for his customer (yes, it was sponsored work). Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] SQL/XML publishing function experimental patch II
On 8/22/06, Bruce Momjian [EMAIL PROTECTED] wrote:
I am still waiting for some documentation on what XML support we have,
and what we need. We can't decide on this patch until we have that.
Here is my thoughts:
http://nikolay.samokhvalov.com/2006/08/23/xml-and-relational/
(maybe too much words - sorry for it - but classification is very
simple and then I give some simple but important examples; if you them
too boring, read the two comments and the end).
As for that patch itself, I can say two things:
1. It's really useful but can be considered only as the first step
to XML type support; I personally would be very happy if it will be
included in 8.2 not by default, but with --with-xml configure option
(the patch changes the grammar... is it possible to provide the user
with capabilities to define during configuration process what parts of
gram.y will be turned on?)
2. It has some problems such as following:
a) SELECT XMLELEMENT(NAME A!--111, 222); produces invalid XML
(the substring -- cannot be used in element names, and even as text
value of XML elements). In other words, patch uses ColCabel to define
XML element names, which is not correct (needs additional narrowing);
b) subqueries inside some constructions are not possible, e.g.:
SELECT XMLCONCAT((SELECT XMLELEMENT(NAME aaa, 111)), XMLELEMENT(NAME
bbb, 222)); doesn't work (only straight list of xmlexpr can be
accepted). Another simple example of this issue is XMLCONCAT of NULLs
(NULL is valid XML value according to SQL/XML:200{3,6});
c) XMLPI should have NAME keyword before the first argument
(similar to XMLELEMENT), according to SQL/XML:2003.
Since I use this patch in my work on SoC's initial support of XML
type (I'll describe results and provide the code in the separate
messages later), I've improved some of issues as long as typos - such
as -- ... -- instead of !-- ... -- in XMLCOMMENT definition,
code style cleanup, etc. If you decide to include Pavel's patch to
8.2, I'll be glad to create the slightly improved version of his
patch.
I plan to continue discussing the theme of XML-enabled databases and
discuss abilities that Postgres has in this area.
Hope, this will help. Please, if you see that I am wrong somewhere -
let me know / let's discuss it.
--
Best regards,
Nikolay
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Leaving... (was: Re: [HACKERS] [PATCHES] COPY view)
Hi all, seriously... I don't have time to work on PostgreSQL. It's time to say that I'm leaving this project. So, if you found some my broken code or whatever in PostgreSQL you should go and fix it. It's community-driven project. It's about collaboration -- don't ask why should I help -- go and help! It was nice time and really big experience, but in the world is more projects and many of them need more help than already stable (do you remember PostgreSQL 6.5? :-) and very reliable PostgreSQL. Good bye! Karel On Tue, Aug 22, 2006 at 11:12:21PM -0400, Tom Lane wrote: The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Merge join performance
It seems that the page-at-a-time-index-scan patch applied in the spring
caused a slight performance regression to merge joins. The btree
mark/restore became much more expensive, as btmarkpos now has to copy
the array of item pointers retrieved from the current index page. That
adds up, because merge join calls markpos for every tuple.
We can make markpos fast, if we make the copy lazily in _bt_steppage,
see attached patch.
I did some micro-benchmarking of merge join performance, see attached
test. Test results, on my laptop:
8_1_STABLE: 1.77 s
HEAD, with patch: 1.65 s
HEAD, without patch: 2.46 s
The results are pretty stable, within 0.1 s.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
mergejointest.sh
Description: application/shellscript
Index: src/backend/access/nbtree/nbtree.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/nbtree/nbtree.c,v
retrieving revision 1.149
diff -c -r1.149 nbtree.c
*** src/backend/access/nbtree/nbtree.c 10 May 2006 23:18:39 - 1.149
--- src/backend/access/nbtree/nbtree.c 23 Aug 2006 11:54:09 -
***
*** 368,373
--- 368,374
{
so = (BTScanOpaque) palloc(sizeof(BTScanOpaqueData));
so-currPos.buf = so-markPos.buf = InvalidBuffer;
+ so-markItemIndex = -1;
if (scan-numberOfKeys 0)
so-keyData = (ScanKey) palloc(scan-numberOfKeys * sizeof(ScanKeyData));
else
***
*** 392,397
--- 393,399
ReleaseBuffer(so-markPos.buf);
so-markPos.buf = InvalidBuffer;
}
+ so-markItemIndex = -1;
/*
* Reset the scan keys. Note that keys ordering stuff moved to _bt_first.
***
*** 430,435
--- 432,438
ReleaseBuffer(so-markPos.buf);
so-markPos.buf = InvalidBuffer;
}
+ so-markItemIndex = -1;
if (so-killedItems != NULL)
pfree(so-killedItems);
***
*** 456,469
so-markPos.buf = InvalidBuffer;
}
! /* bump pin on current buffer for assignment to mark buffer */
if (BTScanPosIsValid(so-currPos))
{
! IncrBufferRefCount(so-currPos.buf);
! memcpy(so-markPos, so-currPos,
! offsetof(BTScanPosData, items[1]) +
! so-currPos.lastItem * sizeof(BTScanPosItem));
! }
PG_RETURN_VOID();
}
--- 459,472
so-markPos.buf = InvalidBuffer;
}
! /* Record the current itemIndex we're on. If we later step to next page
! * before releasing the marked position, _bt_steppage makes a full copy
! * of the currPos-struct. */
if (BTScanPosIsValid(so-currPos))
{
! so-markItemIndex = so-currPos.itemIndex;
! } else
! so-markItemIndex = -1;
PG_RETURN_VOID();
}
***
*** 477,500
IndexScanDesc scan = (IndexScanDesc) PG_GETARG_POINTER(0);
BTScanOpaque so = (BTScanOpaque) scan-opaque;
! /* we aren't holding any read locks, but gotta drop the pin */
! if (BTScanPosIsValid(so-currPos))
{
! /* Before leaving current page, deal with any killed items */
! if (so-numKilled 0
! so-currPos.buf != so-markPos.buf)
! _bt_killitems(scan, false);
! ReleaseBuffer(so-currPos.buf);
! so-currPos.buf = InvalidBuffer;
! }
!
! /* bump pin on marked buffer */
! if (BTScanPosIsValid(so-markPos))
{
! IncrBufferRefCount(so-markPos.buf);
! memcpy(so-currPos, so-markPos,
! offsetof(BTScanPosData, items[1]) +
! so-markPos.lastItem * sizeof(BTScanPosItem));
}
PG_RETURN_VOID();
--- 480,512
IndexScanDesc scan = (IndexScanDesc) PG_GETARG_POINTER(0);
BTScanOpaque so = (BTScanOpaque) scan-opaque;
! if (so-markItemIndex != -1)
{
! /* The restore position was on the same page.
! * Just restore the itemIndex */
! so-currPos.itemIndex = so-markItemIndex;
! }
! else
{
! /* we aren't holding any read locks, but gotta drop the pin */
! if (BTScanPosIsValid(so-currPos))
! {
! /* Before leaving current page, deal with any killed items */
! if (so-numKilled 0
! so-currPos.buf != so-markPos.buf)
! _bt_killitems(scan, false);
! ReleaseBuffer(so-currPos.buf);
! so-currPos.buf = InvalidBuffer;
! }
!
! if (BTScanPosIsValid(so-markPos))
! {
! /* bump pin on marked buffer for assignment to current buffer */
! IncrBufferRefCount(so-markPos.buf);
! memcpy(so-currPos, so-markPos,
! offsetof(BTScanPosData, items[1]) +
! so-markPos.lastItem * sizeof(BTScanPosItem));
! }
}
PG_RETURN_VOID();
Index: src/backend/access/nbtree/nbtsearch.c
===
RCS file: /home/hlinnaka/pgcvsrepository/pgsql/src/backend/access/nbtree/nbtsearch.c,v
retrieving revision 1.105
diff -c -r1.105 nbtsearch.c
*** src/backend/access/nbtree/nbtsearch.c 7 May 2006 01:21:30 - 1.105
--- src/backend/access/nbtree/nbtsearch.c 23 Aug 2006 11:11:45 -
***
*** 1055,1060
--- 1055,1074
rel = scan-indexRelation;
+ /* Before we
[PATCHES] plpgsql, return can contains any expression
Hello,
this patch allow any expression in return statement and it does simply
casting from non type record into typed records. Allows same casting as
assign statement.
Regards
Pavel Stehule
From: Bruce Momjian [EMAIL PROTECTED]
To: Pavel Stehule [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: near future plpgsql?
Date: Wed, 23 Aug 2006 08:25:18 -0400 (EDT)
Pavel Stehule wrote:
Hello,
I had talk with Neil and I knew so Neil forgot on my patch
http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php . I
want
to resend this patch again, but this patch is in conflict with cancelled
lookahead patch. Can I work on it? My patch was sent before feature
frease. But I prefere apply it after lookahead patch.
your recommendation?
Yes, please send an updated version. Sorry we forgot about it. I try
to keep patches around until someone does apply it, but didn't do that
in this case.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2006-08-23 14:29:07.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2006-08-23 14:31:43.0 +0200
***
*** 1586,1599
/para
para
- When returning a scalar type, any expression can be used. The
- expression's result will be automatically cast into the
- function's return type as described for assignments. To return a
- composite (row) value, you must write a record or row variable
- as the replaceableexpression/replaceable.
- /para
-
- para
If you declared the function with output parameters, write just
commandRETURN/command with no expression. The current values
of the output parameter variables will be returned.
--- 1586,1591
*** ./src/pl/plpgsql/src/gram.y.orig 2006-08-23 10:39:35.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2006-08-23 14:37:20.0 +0200
***
*** 2001,2006
--- 2001,2007
make_return_stmt(int lineno)
{
PLpgSQL_stmt_return *new;
+ int tok;
new = palloc0(sizeof(PLpgSQL_stmt_return));
new-cmd_type = PLPGSQL_STMT_RETURN;
***
*** 2026,2032
}
else if (plpgsql_curr_compile-fn_retistuple)
{
! switch (yylex())
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
--- 2027,2034
}
else if (plpgsql_curr_compile-fn_retistuple)
{
! tok = yylex();
! switch (tok)
{
case K_NULL:
/* we allow this to support RETURN NULL in triggers */
***
*** 2040,2051
new-retvarno = yylval.rec-recno;
break;
default:
! yyerror(RETURN must specify a record or row variable in function returning tuple);
break;
}
! if (yylex() != ';')
! yyerror(RETURN must specify a record or row variable in function returning tuple);
}
else
{
--- 2042,2059
new-retvarno = yylval.rec-recno;
break;
+ case T_WORD:
+ case '(':
+ plpgsql_push_back_token(tok);
+ new-expr = plpgsql_read_expression(';',;);
+ break;
+
default:
! yyerror(RETURN must specify a value in function returning tuple);
break;
}
! if (!new-expr yylex() != ';')
! yyerror(RETURN must specify a value in function returning tuple);
}
else
{
***
*** 2065,2070
--- 2073,2079
make_return_next_stmt(int lineno)
{
PLpgSQL_stmt_return_next *new;
+ int tok;
if (!plpgsql_curr_compile-fn_retset)
yyerror(cannot use RETURN NEXT in a non-SETOF function);
***
*** 2083,2089
}
else if (plpgsql_curr_compile-fn_retistuple)
{
! switch (yylex())
{
case T_ROW:
new-retvarno = yylval.row-rowno;
--- 2092,2099
}
else if (plpgsql_curr_compile-fn_retistuple)
{
! tok = yylex();
! switch (tok)
{
case T_ROW:
new-retvarno = yylval.row-rowno;
***
*** 2093,2104
new-retvarno = yylval.rec-recno;
break;
default:
! yyerror(RETURN NEXT must specify a record or row variable in function returning tuple);
break;
}
! if (yylex() != ';')
! yyerror(RETURN NEXT must specify a record or row variable in function returning tuple);
}
else
new-expr = plpgsql_read_expression(';', ;);
--- 2103,2120
new-retvarno = yylval.rec-recno;
break;
+ case T_WORD:
+ case '(':
+ plpgsql_push_back_token(tok);
+ new-expr = plpgsql_read_expression(';',;);
+ break;
+
default:
! yyerror(RETURN NEXT must a value in function returning tuple);
break;
}
! if (!new-expr yylex() != ';')
! yyerror(RETURN NEXT must specify a value in function returning tuple);
}
else
new-expr =
Re: [PATCHES] Allow commenting of variables in postgresql.conf to -
Peter, What is the status of this patch now? I read that two bugs has been fixed in this patch and now it is waiting for new review. Is there something what I can/must do? Zdenek Peter Eisentraut wrote: Zdenek Kotala wrote: OK. I split patch to two parts. Part one is refactoring of set_config_options function. Part two implements feature Allow commenting of variables in postgresql.conf to restore them to defaults. I'm having trouble wrapping my head around a code refactoring which actually makes the code significantly *longer*. The only interface change I could detect is the introduction of a function verify_config_option(), which should just be a small variation on set_config_option() as it currently exists. I'm also about a relive a personal trauma if I see error messages like this: errmsg(configuration file is invalid) I just had to deal with an unnamed product where this was all you got! Please, explain again what this refactoring is supposed to achieve. The second part of your patch actually looks pretty reasonable and does not appear to require the refactoring. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Allow commenting of variables in
Zdenek Kotala wrote: Peter, What is the status of this patch now? I read that two bugs has been fixed in this patch and now it is waiting for new review. Is there something what I can/must do? The patch was applied, fixed, and fixed again, then reverted. It will sit until just before beta, where it will be applied or a vote will be taken on whether to apply it. There is nothing more you have to do on it. I think the fixes just scared some folks so there is hope more review might happen before beta. --- Zdenek Peter Eisentraut wrote: Zdenek Kotala wrote: OK. I split patch to two parts. Part one is refactoring of set_config_options function. Part two implements feature Allow commenting of variables in postgresql.conf to restore them to defaults. I'm having trouble wrapping my head around a code refactoring which actually makes the code significantly *longer*. The only interface change I could detect is the introduction of a function verify_config_option(), which should just be a small variation on set_config_option() as it currently exists. I'm also about a relive a personal trauma if I see error messages like this: errmsg(configuration file is invalid) I just had to deal with an unnamed product where this was all you got! Please, explain again what this refactoring is supposed to achieve. The second part of your patch actually looks pretty reasonable and does not appear to require the refactoring. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Use of backslash in tsearch2
Patch isn't full, simple test (values are took from regression.diffs): and try dump table and restore: ERROR: syntax error CONTEXT: COPY tt, line 5, column tq: '1 ''2' Attached cumulative patch fixes problem, but I have some doubts, is it really needed? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ttt.gz Description: Unix tar archive ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] COPY view
Bernd Helmle [EMAIL PROTECTED] writes: What are these open issues for the updatable views patch you are seeing exactly? Didn't Alvaro list a bunch of issues when he put the patch back up for comment? I have not looked at it myself yet. i see the INSERT...RETURNING stuff as the only big hurd at the moment That's not the fault of the updatable-views patch, but it definitely is something we need to put some time into :-( regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] COPY view
--On Mittwoch, August 23, 2006 08:24:55 -0400 Tom Lane [EMAIL PROTECTED] wrote: What are these open issues for the updatable views patch you are seeing exactly? Didn't Alvaro list a bunch of issues when he put the patch back up for comment? I have not looked at it myself yet. Indeed he did and this helps a lot to clean up some parts of the code (oh, thanks to him for reviewing this, i think i forgot that :( ). I thought you were refering to some specific showstoppers i've missed. -- Thanks Bernd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. So when will you send in a revised patch? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. So when will you send in a revised patch? cheers andrew Soon. :-) Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
B?sz?rm?nyi Zolt?n wrote: B?sz?rm?nyi Zolt?n wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. I think Alvaro is saying we need it in a few days, not longer. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Allow commenting of variables in postgresql.conf to -
Am Mittwoch, 23. August 2006 14:44 schrieb Zdenek Kotala: What is the status of this patch now? I read that two bugs has been fixed in this patch and now it is waiting for new review. Is there something what I can/must do? As I said previously, if you are refactoring code to make it longer, then I don't believe in the merit of the patch. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Allow commenting of variables in
Peter Eisentraut wrote: Am Mittwoch, 23. August 2006 14:44 schrieb Zdenek Kotala: What is the status of this patch now? I read that two bugs has been fixed in this patch and now it is waiting for new review. Is there something what I can/must do? As I said previously, if you are refactoring code to make it longer, then I don't believe in the merit of the patch. The refactoring was to fix problems in incorrect parsing of the config file. I believe the example was: http://archives.postgresql.org/pgsql-committers/2006-08/msg00245.php -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: B?sz?rm?nyi Zolt?n wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. I think Alvaro is saying we need it in a few days, not longer. Of course. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: * The schema now uses foreign keys to more accurately reflect a finacial DDL Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. * The history table now has a primary key that uses a serial Ditto. Again, part of the point :) * The respective balance columns have been increased to int8 to deal with larger values Ditto. This was done because you can easily break pgbench without the increase in data type. pgbench -c 850 -t 1000 pgbench gave a stream of errors like this before ending: Client 18 aborted in state 8: ERROR: integer out of range Client 429 aborted in state 8: ERROR: integer out of range Client 168 aborted in state 8: ERROR: integer out of range PG error log showed: 2006-08-22 15:45:19 PDT-[local]STATEMENT: UPDATE branches SET bbalance = bbalance + 4209228 WHERE bid = 679; 2006-08-22 15:45:19 PDT-[local]ERROR: integer out of range * Initalization will be done in a new schema/namespace, pgbench will exit if this schema/namespace exists OK, maybe that doesn't matter. Yeah I did it just so we wouldn't stomp on somebody on accident. * The new DDL should allow both Mammoth Replicator and Slony to be tested using pgbench (at least basic replication) Erm ... exactly why couldn't you do that before? history was missing a primary key. It could be done before. I just removed a step in getting it to work. pgbench doesn't have all that many things to recommend it, but what it does have is that it's been a stable testbed across quite a few PG releases. Arbitrarily whacking around the tested functionality will destroy that continuity. Well to be fair, I wasn't doing it arbitrarily. I had a specific purpose which was to have it use a schema that would be closer to a production schema, without breaking existing behavior. This patch does that :) I fell into this trap before myself ... I have a local copy of pgbench that produces TPS numbers quite a lot better than the standard pgbench, against exactly the same server. What's wrong with that picture? Well I think we all agree that some of the behavior of pgbench has been weird. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] COPY view
Hi, Bruce Momjian írta: Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. BTW, my first name is Zoltán. Best regards, Zoltán Böszörményi pgsql-copyselect.patch.gz Description: Unix tar archive ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi írta: Hi, Bruce Momjian írta: Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I meant that UNION selects, subselects don't work yet. BTW, my first name is Zoltán. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi wrote: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I'm not sure I agree with the approach of creating a fake SELECT * FROM foo in analyze.c in the relation case and passing it back to the parser to create a Query node. That's not there in the original code and you shouldn't need it. Just let the case where COPY gets a relation continue to handle it as it does today, and add a separate case for the SELECT. That doesn't help you with the UNION stuff though. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Alvaro Herrera írta: Zoltan Boszormenyi wrote: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I'm not sure I agree with the approach of creating a fake SELECT * FROM foo in analyze.c in the relation case and passing it back to the parser to create a Query node. That's not there in the original code and you shouldn't need it. Just let the case where COPY gets a relation continue to handle it as it does today, and add a separate case for the SELECT. The exact same code was there, e.g. parse and rewrite SELECT * FROM view just not in analyze.c. I will try without it, though. That doesn't help you with the UNION stuff though. :-( ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [GENERAL] pgxml xpath_table
Patch applied. Thanks. --- John Gray wrote: On Mon, 2006-08-21 at 23:38 -0400, Bruce Momjian wrote: [snip] FYI, I have not seen a patch for this yet. Thanks for prodding me to submit it. Attached is the documentation patch, based on Phillipe's example. Regards John Content-Description: [ Attachment, skipping... ] -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Merge join performance
Heikki Linnakangas [EMAIL PROTECTED] writes: We can make markpos fast, if we make the copy lazily in _bt_steppage, Nice hack. Getting rid of the buffer refcount manipulations is probably even more useful than avoiding the memcpy. I did some micro-benchmarking of merge join performance, see attached test. Test results, on my laptop: 8_1_STABLE: 1.77 s HEAD, with patch: 1.65 s HEAD, without patch: 2.46 s Hm, I don't see a performance regression on my machine, but there is a measureable improvement with the patch: 8.1 branch: 10.6 s CVS HEAD: 10.1 s w/patch: 9.3 s Patch applied ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? I wouldn't object to providing that as a (non default) option. The int8 change should be unnecessary in view of Tatsuo's recent fix to make the random deltas symmetrical about zero. I would counsel against making the other changes either, as they seem to accomplish little except make the comparability of results more doubtful. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? I wouldn't object to providing that as a (non default) option. O.k. I will take a look at what that would take.. The int8 change should be unnecessary in view of Tatsuo's recent fix to make the random deltas symmetrical about zero. O.k. that may be the case, my testing was with the 8.1 version pgbench. I will verify with -HEAD before I change the int8. I would counsel against making the other changes either, as they seem to accomplish little except make the comparability of results more doubtful. I am not interested in really changing the overall internals. I just wanted the foreign key option. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Patch - Have psql show current values
Hi all,
This patch was discussed a few months ago.
I could not complete this patch at that time.
I hope that the current version of my patch is acceptable.
Patch details:
**
1. Assign a new field called 'Seq Value' for \ds command
2. All the sequence values are '1' initially
3. After executing the query, call AssignSeqValue()
4. This function assigns the respective sequence values back to the
resultset
Please review and comment on this patch.
Thanks
Dhanaraj
Tom Lane wrote:
Dhanaraj M [EMAIL PROTECTED] writes:
However, it was not possible to display the seq. value using this.
Hence, I made a small change in the currval() function, so that it
retrieves the last_value
even if the the value is not cached.
Breaking currval()'s semantics is not an acceptable solution for this.
The best, fully backward compatible solution is for psql to issue
SELECT last_value FROM seq queries to get the values. This might
be a bit tricky to wedge into the structure of describe.c, but I don't
see any fundamental reason why it can't be done.
regards, tom lane
*** src/bin/psql/describe.c.orig Mon Aug 21 11:21:56 2006
--- src/bin/psql/describe.c Thu Aug 24 10:54:59 2006
***
*** 14,19
--- 14,20
#include settings.h
#include print.h
#include variables.h
+ #include libpq-int.h
#include ctype.h
***
*** 38,43
--- 39,45
static bool add_tablespace_footer(char relkind, Oid tablespace, char **footers,
int *count, PQExpBufferData buf, bool newline);
+ static void AssignSeqValue(PGresult *resultset);
/*
* Handlers for various slash commands displaying some sort of list
***
*** 1499,1504
--- 1501,1507
bool showSeq = strchr(tabtypes, 's') != NULL;
bool showSystem = strchr(tabtypes, 'S') != NULL;
+ int64 initialSeqValue = 1;
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
***
*** 1521,1526
--- 1524,1533
_(table), _(view), _(index), _(sequence),
_(special), _(Type), _(Owner));
+ if ((showSeq) (!showTables))
+ appendPQExpBuffer(buf, ,\n INT64_FORMAT as \%s\,
+ initialSeqValue, _(Seq Value));
+
if (showIndexes)
appendPQExpBuffer(buf,
,\n c2.relname as \%s\,
***
*** 1587,1592
--- 1594,1602
myopt.nullPrint = NULL;
myopt.title = _(List of relations);
+ if ((showSeq) (!showTables))
+ AssignSeqValue(res);
+
printQuery(res, myopt, pset.queryFout, pset.logfile);
}
***
*** 1594,1599
--- 1604,1641
return true;
}
+
+ /*
+ * \ds
+ *
+ * Assign the respective sequence value.
+ */
+ static void AssignSeqValue(PGresult *resultset)
+ {
+ int i, rows, nfields;
+ PQExpBufferData buf;
+ PGresult *seqValue;
+
+ rows = PQntuples(resultset);
+ nfields = PQnfields(resultset);
+
+ /*
+ * Execute the select query to get the sequence value for each sequence separately,
+ * by using the retrieved sequence names from the second field of resultset.
+ * Re-assign the respective sequence values to the last field of resultset.
+ */
+ for(i=0; irows; i++)
+ {
+ initPQExpBuffer(buf);
+ printfPQExpBuffer(buf, select last_value from %s, PQgetvalue(resultset, i, 1));
+
+ seqValue = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ strcpy(resultset-tuples[i][nfields-1].value, seqValue-tuples[0][0].value);
+ PQclear(seqValue);
+ }
+ }
+
/*
* \dD
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
