Re: [HACKERS] Feature Freeze date for 8.4
On 10/25/07, Gregory Stark [EMAIL PROTECTED] wrote: Huh, I hadn't heard of that either. The Debian package patchutils says it was downloaded from: http://cyberelk.net/tim/data/patchutils What's really cool is that patchutils also appears to have the utility I've been looking for for a while: a tool to view the differences between two diffs! I wouldn't trust that too much. I've played with it quite a bit and there are quite a few corner cases where it gives the wrong answer (or at least a different diff than I get when I manually apply them and then take the diff) -- Kris Shannon [EMAIL PROTECTED] ---(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
[HACKERS] order by in cursor declaration does not allow update
If a cursor is declared using Order by then it gives following error during updation of the cursor: ERROR: cursor c is not a simply updatable scan of table test Ex: DROP TABLE IF EXISTS test; create table test (num int,num2 int ); insert into test values(1,100); insert into test values(2,200); insert into test values(3,300); insert into test values(4,400); insert into test values(5,500); BEGIN; DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num; FETCH 2 FROM c; UPDATE test SET num = 500 WHERE CURRENT OF c; ERROR: cursor c is not a simply updatable scan of table test SELECT * FROM test; FETCH 2 FROM c; COMMIT; SELECT * FROM test; FETCH 2 FROM c; COMMIT; Comments for this...?? Regards, Dharmendra www.enterprisedb.com
Re: [HACKERS] postgres under linux can't start because of postmaster.pid
I just consider this may happens and pg can't recover correctly: if postgres crashed last time and left a postmaster.pid file, and last postgres id is reused by another process which is not postgres now. Tom Lane [EMAIL PROTECTED] дÈëÏûÏ¢ÐÂÎÅ:[EMAIL PROTECTED] Richard Wang [EMAIL PROTECTED] writes: I construct a postmaster.pid file and then set the pid to be one of existing process id(not postgres, e.g vim), then I run postgres. This may happen if postgres crashed last time and left a postmaster.pid file, and last postgres id is reused by another process which is not postgres now. Don't do that. The postmaster is perfectly capable of recovering on its own, why would you want to mess with the postmaster.pid file? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(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] pgadmin debug on windows
Roberto Icardi wrote: Also, please set pgAdmin to 'Debug' log level (under File-Options), create a new log of you recreating the crash (using direct debugging, not a global breakpoint) and then send me the logfile. Done Doesn't shed any light though unfortunately. Do you have a firewall on that box? If so, can you try disabling it temporarily? Regards, Dave. Only the windows firewall, if you can call it a firewall g Anyway, I've disabled, but without luck. Only thing I can add is that on that pc there where a previous release of PostgreSQL (8.2), uninstalled before 8.3 beta installation. Now I'll try with a Win2000 virtual machine. On this machine is installed 8.2 also, that I'm uninstalling right now. Shouldn't matter - I have all manner of installs on my dev boxes - of various release and dev versions. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3 GSS Issues
On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: I know I haven't been very active for a while here, but I just got to testing the October 3 version a bit prior to getting back to the Java GSS client stuff I promised. There seem to be some funny things there. Apologies for not responding to this one sooner. BTW, what's the status on the Java stuff? Will it be around by the time 8.3 is released? The only serious issue is that the server doesn't require the realm name to match. I haven't looked at how that broke yet, but I know I was careful of that point in my original patches because it's always been wrong in the Kerberos 5 auth method. I honestly don't remember exactly how that became. I think I stripped it out in order to make it work like the krb5 method. What you're asking for is basically a krb_match_realm parameter, or do I understand you wrong? It appears that you can just put a complete (realm-included) name into postgres, so that's obviously the way to support gssapi connections from non-default realms. In short this is a security hole. IMO it should be fixed prior to release. Can't you also configure the kerberos libraries on your machine not to accept other realms than your own? IIRC, that was something considered at the time, but I can't find a reference to such a discussion. - I notice there are hba options for gss and sspi both. Why? Is there some windows-only functionality it enables? Shouldn't we be using Microsoft's advertised GSSAPI/SSPI compatibility? If you build on Windows then I'm sure you want to link the SSPI libraries rather than require installation of a separate package, but that shouldn't change the functionality or the wire protocol AFAIK. In other words I would expect this to be a build-time option. There was discussion about this, and we were presented with clear cases where you'd want to be able to do either one. Making it a build option doesn't help the 99.9% of Windows users that use a pre-packaged binary distribution. - At the risk of diluting my message: I still think it's a mistake to call it gss instead of something like gss-noprot. I believe this will cause misunderstandings in the future when we get the security layer of gssapi implemented. Well, I don't agree with this, but if others want it changed, it can certainly be changed. And it can only be changed *now*, and not once we release. But we have host and hostssl, not hostnossl and host. So the way we are donig it now is IMO more consistent with what we have in other parts of pg. - There's no way to specify the gssapi library to use. I have three on my main development Sun: MIT, Sun, and Heimdal. I might have more than one version of one of those three at some times. Of course there's no way to specify which kerberos 5 library or openssl library you want either, so consider this a feature request for future development. Yeah, that's something that can be done for 8.4, certainly not something we can put in now. But I'll be happy to see a patch once we open the tree for 8.4 :-) //Magnus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] PostGreSQL and zlib
Hi everybody, I'm looking for using zlib function directly in the database : Example : a function zipit that would take a bytea as argument and returns a compressed bytea and an unzipit that would take a compressed bytea as argument and returns a uncompressed bytea I'm sure it's very easy to implement as a C function that would call zlib Is there already an implementation of such things? Thanks! Hubert FONGARNAND ___ Ce message et les éventuels documents joints peuvent contenir des informations confidentielles. Au cas où il ne vous serait pas destiné, nous vous remercions de bien vouloir le supprimer et en aviser immédiatement l'expéditeur. Toute utilisation de ce message non conforme à sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'étant pas sécurisées, l'intégrité de ce message n'est pas assurée et la société émettrice ne peut être tenue pour responsable de son contenu.
Re: [HACKERS] second DML operation fails with updatable cursor
Tom Lane wrote: SQL92 has this under Leveling Rules: 1) The following restrictions apply for Intermediate SQL: a) A declare cursor shall not specify INSENSITIVE. b) If an updatability clause of FOR UPDATE with or without a column name list is specified, then neither SCROLL nor ORDER BY shall be specified. So SCROLL with FOR UPDATE is a Full-SQL-only feature. (In SQL99 it's broken out as Feature F831-01, but that doesn't tell you much about how hard it is or whether most implementations have it.) Oh, ok then. I don't feel particularly bad about not supporting every such feature. I think Simon's recommendation is definitely the way to go for 8.3 --- if anyone is motivated to relax the restriction in the future, they can figure out how to resolve the corner cases then. Ok. Looking at what you committed, I completely misunderstood what you were saying earlier. Yeah, let's leave it like that for now. A nice not supported error message is perfectly fine, as long as we can avoid the unexpected behavior. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] MaxOffsetNumber versus MaxHeapTuplesPerPage
Is there any reason to have both these macros? By my opinion MaxHeapTuplesPerPage is more accurate and it should replace all MaxOffsetNumber occurrence. Any comments? Zdenek http://doxygen.postgresql.org/htup_8h.html#c8829334a53a69e12b070bf09b7b7ab7 http://doxygen.postgresql.org/off_8h.html#fc094b61f53a883c7a24bc152382cd31 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostGreSQL and zlib
Hubert FONGARNAND [EMAIL PROTECTED] writes: I'm sure it's very easy to implement as a C function that would call zlib Is there already an implementation of such things? Not that I'm aware though you might look at pgcrypto. Good crypto has to compress first so there may be a possibility of just doing the compression. Postgres also will automatically try to compress data like byteas if the record is larger than 2kB (1/4 of the block size if you've changed the block size). So you may not have to do anything if you're just looking to save space on disk. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MaxOffsetNumber versus MaxHeapTuplesPerPage
Zdenek Kotala wrote: Is there any reason to have both these macros? By my opinion MaxHeapTuplesPerPage is more accurate and it should replace all MaxOffsetNumber occurrence. We use MaxOffsetNumber with index pages as well. At quick glance, the only places I can see where we could replace MaxOffsetNumber with MaxHeapTuplesPerPage, are in vacuum.c and vacuumlazy.c, where we allocate arrays big enough to hold potentially a full page's worth of tuples. We could change those, but it's hardly worth the trouble. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] MaxOffsetNumber versus MaxHeapTuplesPerPage
Heikki Linnakangas wrote: Zdenek Kotala wrote: Is there any reason to have both these macros? By my opinion MaxHeapTuplesPerPage is more accurate and it should replace all MaxOffsetNumber occurrence. We use MaxOffsetNumber with index pages as well. I forgot to indexes, but there is MaxIndexTuplesPerPage which is also better estimation for indexes. At quick glance, the only places I can see where we could replace MaxOffsetNumber with MaxHeapTuplesPerPage, are in vacuum.c and vacuumlazy.c, where we allocate arrays big enough to hold potentially a full page's worth of tuples. We could change those, but it's hardly worth the trouble. Yes, it is a cleanup (maybe reduce some memory requirements), but I think is better to reduce different macros to avoid future problem, when somebody forget changes all of these macros. Zdenek ---(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] order by in cursor declaration does not allow update
On Thu, 2007-10-25 at 12:28 +0530, Dharmendra Goyal wrote: If a cursor is declared using Order by then it gives following error during updation of the cursor: ERROR: cursor c is not a simply updatable scan of table test Ex: DROP TABLE IF EXISTS test; create table test (num int,num2 int ); insert into test values(1,100); insert into test values(2,200); insert into test values(3,300); insert into test values(4,400); insert into test values(5,500); BEGIN; DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num; FETCH 2 FROM c; UPDATE test SET num = 500 WHERE CURRENT OF c; ERROR: cursor c is not a simply updatable scan of table test Comments for this...?? You haven't specified FOR UPDATE on the query in the DECLARE clause. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] order by in cursor declaration does not allow update
According to SQL specifications: If READ ONLY is not specified in cursor declaration then for update is implicit. Anyway, even if i specify for update in the declare clause, behaviour is same. DROP TABLE IF EXISTS test; create table test (num int,num2 int ); insert into test values(1,100); insert into test values(2,200); insert into test values(3,300); insert into test values(4,400); insert into test values(5,500); BEGIN; DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num FOR UPDATE; FETCH 2 FROM c; UPDATE test SET num = 500 WHERE CURRENT OF c; ERROR: cursor c is not a simply updatable scan of table test SELECT * FROM test; FETCH 2 FROM c; COMMIT; SELECT * FROM test; FETCH 2 FROM c; COMMIT; Regards, Dharmendra www.enterprisedb.com On 10/25/07, Simon Riggs [EMAIL PROTECTED] wrote: On Thu, 2007-10-25 at 12:28 +0530, Dharmendra Goyal wrote: If a cursor is declared using Order by then it gives following error during updation of the cursor: ERROR: cursor c is not a simply updatable scan of table test Ex: DROP TABLE IF EXISTS test; create table test (num int,num2 int ); insert into test values(1,100); insert into test values(2,200); insert into test values(3,300); insert into test values(4,400); insert into test values(5,500); BEGIN; DECLARE c CURSOR FOR SELECT * FROM test ORDER BY num; FETCH 2 FROM c; UPDATE test SET num = 500 WHERE CURRENT OF c; ERROR: cursor c is not a simply updatable scan of table test Comments for this...?? You haven't specified FOR UPDATE on the query in the DECLARE clause. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Re: [HACKERS] order by in cursor declaration does not allow update
On Thu, 2007-10-25 at 16:53 +0530, Dharmendra Goyal wrote: According to SQL specifications: If READ ONLY is not specified in cursor declaration then for update is implicit. Though that isn't what the PostgreSQL docs say. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostGreSQL and zlib
The problem is that for TOAST columns : The compression technique used is a fairly simple and very fast member of the LZ family of compression techniques. See src/backend/utils/adt/pg_lzcompress.c for the details. With such function you could choose you compression algorithm and compression level... Another question : does this TOAST code works for all type of data, like TEXT (in our case) Le jeudi 25 octobre 2007 à 11:03 +0100, Gregory Stark a écrit : Hubert FONGARNAND [EMAIL PROTECTED] writes: I'm sure it's very easy to implement as a C function that would call zlib Is there already an implementation of such things? Not that I'm aware though you might look at pgcrypto. Good crypto has to compress first so there may be a possibility of just doing the compression. Postgres also will automatically try to compress data like byteas if the record is larger than 2kB (1/4 of the block size if you've changed the block size). So you may not have to do anything if you're just looking to save space on disk. ___ Ce message et les éventuels documents joints peuvent contenir des informations confidentielles. Au cas où il ne vous serait pas destiné, nous vous remercions de bien vouloir le supprimer et en aviser immédiatement l'expéditeur. Toute utilisation de ce message non conforme à sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'étant pas sécurisées, l'intégrité de ce message n'est pas assurée et la société émettrice ne peut être tenue pour responsable de son contenu.
Re: [HACKERS] 8.3 GSS Issues
Magnus Hagander wrote: On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: At the risk of diluting my message: I still think it's a mistake to call it gss instead of something like gss-noprot. I believe this will cause misunderstandings in the future when we get the security layer of gssapi implemented. Well, I don't agree with this, but if others want it changed, it can certainly be changed. And it can only be changed *now*, and not once we release. But we have host and hostssl, not hostnossl and host. So the way we are donig it now is IMO more consistent with what we have in other parts of pg. Actually we have hostssl, hostnossl and host. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.3 GSS Issues
On Thu, Oct 25, 2007 at 09:26:47AM -0300, Alvaro Herrera wrote: Magnus Hagander wrote: On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: At the risk of diluting my message: I still think it's a mistake to call it gss instead of something like gss-noprot. I believe this will cause misunderstandings in the future when we get the security layer of gssapi implemented. Well, I don't agree with this, but if others want it changed, it can certainly be changed. And it can only be changed *now*, and not once we release. But we have host and hostssl, not hostnossl and host. So the way we are donig it now is IMO more consistent with what we have in other parts of pg. Actually we have hostssl, hostnossl and host. Good point. But the line that corresponds to what is currently called gss is host :) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] text search and filenames
Hi,
I noticed that the default parser does not recognize Windows-style
filenames:
alvherre=# SELECT alias, description, token FROM ts_debug(e'c:\\archivos');
alias | description | token
---+-+--
asciiword | Word, all ASCII | c
blank | Space symbols | :\
asciiword | Word, all ASCII | archivos
(3 lignes)
I played with it a bit (see attached patch -- basically I added \ in all
places where a / was being parsed, in the file-path states) and managed
to have it parse some naive versions, like
alvherre=# SELECT alias, description, token FROM ts_debug(e'c:\\archivos\\foo');
alias |description| token
---+---+-
file | File or path name | c:\archivos\foo
(1 ligne)
However it fails as soon as you have a space, which is quite common on
Windows, for example
alvherre=# SELECT alias, description, token FROM ts_debug(e'c:\\Program
Files\\');
alias |description| token
---+---+
file | File or path name | c:\Program
blank | Space symbols |
asciiword | Word, all ASCII | Files
blank | Space symbols | \
(4 lignes)
It also fails to recognize network file names, like
alvherre=# SELECT alias, description, token FROM
ts_debug(e'server\\archivos\\foo');
alias | description | token
---+-+--
blank | Space symbols | \\
asciiword | Word, all ASCII | server
blank | Space symbols | \
asciiword | Word, all ASCII | archivos
blank | Space symbols | \
asciiword | Word, all ASCII | foo
(6 lignes)
Is this something worth worrying about?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/tsearch/wparser_def.c
===
RCS file: /cvsroot/pgsql/src/backend/tsearch/wparser_def.c,v
retrieving revision 1.4
diff -c -p -r1.4 wparser_def.c
*** src/backend/tsearch/wparser_def.c 23 Oct 2007 20:46:12 - 1.4
--- src/backend/tsearch/wparser_def.c 25 Oct 2007 13:45:36 -
*** static TParserStateActionItem actionTPS_
*** 1053,1058
--- 1053,1059
{p_isdigit, 0, A_NEXT, TPS_InFile, 0, NULL},
{p_iseqC, '_', A_NEXT, TPS_InFile, 0, NULL},
{p_iseqC, '/', A_NEXT, TPS_InFileFirst, 0, NULL},
+ {p_iseqC, '\\', A_NEXT, TPS_InFileFirst, 0, NULL},
{NULL, 0, A_POP, TPS_Null, 0, NULL}
};
*** static TParserStateActionItem actionTPS_
*** 1063,1068
--- 1064,1070
{p_iseqC, '_', A_NEXT, TPS_InFile, 0, NULL},
{p_iseqC, '.', A_NEXT, TPS_InPathSecond, 0, NULL},
{p_iseqC, '/', A_NEXT, TPS_InFileFirst, 0, NULL},
+ {p_iseqC, '\\', A_NEXT, TPS_InFileFirst, 0, NULL},
{NULL, 0, A_POP, TPS_Null, 0, NULL}
};
*** static TParserStateActionItem actionTPS_
*** 1070,1082
--- 1072,1087
{p_isEOF, 0, A_POP, TPS_Null, 0, NULL},
{p_iseqC, '.', A_NEXT, TPS_InPathSecond, 0, NULL},
{p_iseqC, '/', A_NEXT, TPS_InFileFirst, 0, NULL},
+ {p_iseqC, '\\', A_NEXT, TPS_InFileFirst, 0, NULL},
{NULL, 0, A_POP, TPS_Null, 0, NULL}
};
static TParserStateActionItem actionTPS_InPathSecond[] = {
{p_isEOF, 0, A_BINGO | A_CLEAR, TPS_Base, FILEPATH, NULL},
{p_iseqC, '/', A_NEXT | A_PUSH, TPS_InFileFirst, 0, NULL},
+ {p_iseqC, '\\', A_NEXT | A_PUSH, TPS_InFileFirst, 0, NULL},
{p_iseqC, '/', A_BINGO | A_CLEAR, TPS_Base, FILEPATH, NULL},
+ {p_iseqC, '\\', A_BINGO | A_CLEAR, TPS_Base, FILEPATH, NULL},
{p_isspace, 0, A_BINGO | A_CLEAR, TPS_Base, FILEPATH, NULL},
{NULL, 0, A_POP, TPS_Null, 0, NULL}
};
*** static TParserStateActionItem actionTPS_
*** 1089,1094
--- 1094,1100
{p_iseqC, '_', A_NEXT, TPS_InFile, 0, NULL},
{p_iseqC, '-', A_NEXT, TPS_InFile, 0, NULL},
{p_iseqC, '/', A_PUSH, TPS_InFileFirst, 0, NULL},
+ {p_iseqC, '\\', A_PUSH, TPS_InFileFirst, 0, NULL},
{p_iseqC, '?', A_PUSH, TPS_InURIFirst, 0, NULL},
{NULL, 0, A_BINGO, TPS_Base, FILEPATH, NULL}
};
*** static TParserStateActionItem actionTPS_
*** 1130,1135
--- 1136,1142
static TParserStateActionItem actionTPS_InProtocolFirst[] = {
{p_isEOF, 0, A_POP, TPS_Null, 0, NULL},
{p_iseqC, '/', A_NEXT, TPS_InProtocolSecond, 0, NULL},
+ {p_iseqC, '\\', A_NEXT, TPS_InFile, 0, NULL},
{NULL, 0, A_POP, TPS_Null, 0, NULL}
};
---(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
[HACKERS] Datum should be defined outside postgres.h
I'm trying to solve one TODO item mentioned in src/backend/utils/mmgr/mcxt.c. /* * MemoryContextSwitchTo * Returns the current context; installs the given context. * * This is inlined when using GCC. * * TODO: investigate supporting inlining for some non-GCC compilers. */ Everything works fine with Sun Studio instead of zic and ecpg compilation. The problem there is that palloc.h defines CurrentMemoryContext which is declared in mcxt.c. And palloc.h is included by postgres.h. Unfortunately zic and ecpg break the rule which is mentioned on many places and they include postgres.h. Linker is looking for CurrentMemoryContext because inlined function requires it. This problem disappears when -xO3 is enabled and SS optimizes a code. But it cannot be use in general. I fixed it for zic, but problem with ecpg is that it includes nodes/primnodes.h and it requires Datum type definition which is defined in postgres.h. :( By my opinion Datum should be defined in separate file and all headers which use this type should include it. (this is problem on many places with another types). Another question is why ecpg needs it? Any comments how to fix ecpg? Zdenek ---(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
[HACKERS] text search parser, uri
I observed that the parser calls the part after the host in a URL, uri. This is quite incorrect; a URI is supposed to be a generalization of a URL, so it makes no sense to call that a URI. RFC 1738, section 3.1 calls that url-path. Are there objections to changing it at this time? The contrib module used to call it uri as well, but we've already changed some of the names so this is the best time to do it. My suggestion would be url_path, with an underscore instead of an hyphen to be consistent with the hword_* names. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] order by in cursor declaration does not allow update
Dharmendra Goyal [EMAIL PROTECTED] writes: If a cursor is declared using Order by then it gives following error during updation of the cursor: ERROR: cursor c is not a simply updatable scan of table test This is not a bug. (See also quote from SQL92 in the other thread.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] postgres under linux can't start because of postmaster.pid
Richard Wang [EMAIL PROTECTED] writes: I just consider this may happens and pg can't recover correctly: if postgres crashed last time and left a postmaster.pid file, and last postgres id is reused by another process which is not postgres now. Postgres defends itself against that just fine, at least in any reasonably recent release. There are some corner cases where it could have problems, but scripted hacking on the .pid file is just about guaranteed to make things worse not better. regards, tom lane ---(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] MaxOffsetNumber versus MaxHeapTuplesPerPage
Heikki Linnakangas [EMAIL PROTECTED] writes: Zdenek Kotala wrote: Is there any reason to have both these macros? By my opinion MaxHeapTuplesPerPage is more accurate and it should replace all MaxOffsetNumber occurrence. We use MaxOffsetNumber with index pages as well. At quick glance, the only places I can see where we could replace MaxOffsetNumber with MaxHeapTuplesPerPage, are in vacuum.c and vacuumlazy.c, where we allocate arrays big enough to hold potentially a full page's worth of tuples. We could change those, but it's hardly worth the trouble. There is also a difference in intent: MaxOffsetNumber is selected so that it's physically impossible to have more than that many offsets on a page, and so it's safe to use an array sized that way without any overflow checks. MaxHeapTuplesPerPage is the most that *should* be there but one can think of corner cases where there could be more (eg, limit on number of redirect pointers hasn't been enforced correctly, not to mention flat-out corrupt page). If there is any code using MaxHeapTuplesPerPage as an array size and not backstopping it with an explicit overflow check, that would be a bug IMHO. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Datum should be defined outside postgres.h
Am Donnerstag, 25. Oktober 2007 schrieb Zdenek Kotala: I fixed it for zic, but problem with ecpg is that it includes nodes/primnodes.h and it requires Datum type definition which is defined in postgres.h. :( I don't find an inclusion of primnodes.h in ecpg. Which file are you looking at? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] PostGreSQL and zlib
On Thu, Oct 25, 2007 at 01:49:57PM +0200, Hubert FONGARNAND wrote: Another question : does this TOAST code works for all type of data, like TEXT (in our case) Yes. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Datum should be defined outside postgres.h
Peter Eisentraut wrote: Am Donnerstag, 25. Oktober 2007 schrieb Zdenek Kotala: I fixed it for zic, but problem with ecpg is that it includes nodes/primnodes.h and it requires Datum type definition which is defined in postgres.h. :( I don't find an inclusion of primnodes.h in ecpg. Which file are you looking at? It is indirectly included in parser.c from parser/gramparse.h. Now I probably find a solution. I created fake gramparse.h and parser.h into ecpg directory (same way as parse.h is faked). It looks fine. Now I'm testing it. Zdenek ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] text search and filenames
Alvaro Herrera [EMAIL PROTECTED] writes: I noticed that the default parser does not recognize Windows-style filenames: Is this something worth worrying about? I'm not too excited about it. The fact that there's a filename category at all seems a bit of a wart to me, particularly since simple examples like 'example.txt' don't get parsed that way. I definitely don't see any good way to allow spaces in Windows filenames... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Datum should be defined outside postgres.h
Zdenek Kotala [EMAIL PROTECTED] writes: I fixed it for zic, but problem with ecpg is that it includes nodes/primnodes.h and it requires Datum type definition which is defined in postgres.h. :( Why in the world is ecpg including either primnodes.h or postgres.h? By my opinion Datum should be defined in separate file and all headers which use this type should include it. (this is problem on many places with another types). Another question is why ecpg needs it? Datum is a type that no frontend code has any business dealing in; and the same goes for everything in primnodes.h. I'd suggest trying to fix ecpg to not depend on backend-only include files... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Opportunity for a Radical Changes in Database Software
Hi In looking at current developments in computers, it seems we're nearing a point where a fundamental change may be possible in databases... Namely in-memory databases which could lead to huge performance improvements. A good starting point is to look at memcached, since it provides proof that it's possible to interconnect hundreds of machines into a huge memory cluster with, albeit, some issues on reliability. For more info on memcached, try: http://www.socialtext.net/memcached/index.cgi?faq The sites that use it see incredible performance increases, but often at the cost of not being able to provide versioned results that are guaranteed to be accurate. The big questions are then, how would you create a distributed in-memory database? Another idea that may be workable Everyone knows the main problem with a standard cluster is that every machine has to perform every write, which leads to diminishing returns as the writes consume more and more of every machine's resources. Would it be possible to create a clustered environment where the master is the only machine that writes the data to disk, while the others just use cached data? Or, perhaps it would work better if the master or master log entry moves from machine to machine with a commit coinciding with a disk write on each machine? Any other ideas? It seems to be a problem worth pondering since in-memory databases are possible. Thanks Dan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Datum should be defined outside postgres.h
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: I fixed it for zic, but problem with ecpg is that it includes nodes/primnodes.h and it requires Datum type definition which is defined in postgres.h. :( Why in the world is ecpg including either primnodes.h or postgres.h? The problem is that ecpg shares parser.c source code and this code includes postgres.h. By my opinion Datum should be defined in separate file and all headers which use this type should include it. (this is problem on many places with another types). Another question is why ecpg needs it? Datum is a type that no frontend code has any business dealing in; and the same goes for everything in primnodes.h. I'd suggest trying to fix ecpg to not depend on backend-only include files... Yes, agree. I'm now testing my fix. I removed postgres.h from parser.c + performed some other changes around. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Opportunity for a Radical Changes in Database Software
I'd suggest looking at the source code to several of the in-memory databases which already exist. On 10/25/07, Dan [EMAIL PROTECTED] wrote: Hi In looking at current developments in computers, it seems we're nearing a point where a fundamental change may be possible in databases... Namely in-memory databases which could lead to huge performance improvements. A good starting point is to look at memcached, since it provides proof that it's possible to interconnect hundreds of machines into a huge memory cluster with, albeit, some issues on reliability. For more info on memcached, try: http://www.socialtext.net/memcached/index.cgi?faq The sites that use it see incredible performance increases, but often at the cost of not being able to provide versioned results that are guaranteed to be accurate. The big questions are then, how would you create a distributed in-memory database? Another idea that may be workable Everyone knows the main problem with a standard cluster is that every machine has to perform every write, which leads to diminishing returns as the writes consume more and more of every machine's resources. Would it be possible to create a clustered environment where the master is the only machine that writes the data to disk, while the others just use cached data? Or, perhaps it would work better if the master or master log entry moves from machine to machine with a commit coinciding with a disk write on each machine? Any other ideas? It seems to be a problem worth pondering since in-memory databases are possible. Thanks Dan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Datum should be defined outside postgres.h
Zdenek Kotala wrote: Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: By my opinion Datum should be defined in separate file and all headers which use this type should include it. (this is problem on many places with another types). Another question is why ecpg needs it? Datum is a type that no frontend code has any business dealing in; and the same goes for everything in primnodes.h. I'd suggest trying to fix ecpg to not depend on backend-only include files... OK the problem now is pg_dump.c. It includes postgres.h :( and it is described there why. It needs it for catalog header files. Any suggestion how to fix it? One solution should be put sugar words into separate header and include them directly from catalog/*.h files. Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Datum should be defined outside postgres.h
Zdenek Kotala [EMAIL PROTECTED] writes: Tom Lane wrote: Why in the world is ecpg including either primnodes.h or postgres.h? The problem is that ecpg shares parser.c source code and this code includes postgres.h. ecpg cannot do that. It would fail if parser.c happened to use anything that won't compile in frontend, eg elog() or palloc(). It's mere luck that it's worked for him so far. Considering that ecpg has its own copy of all of gram.y and scan.l, sharing parser.c isn't much of a savings anyway. regards, tom lane ---(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] Datum should be defined outside postgres.h
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: Tom Lane wrote: Why in the world is ecpg including either primnodes.h or postgres.h? The problem is that ecpg shares parser.c source code and this code includes postgres.h. ecpg cannot do that. It would fail if parser.c happened to use anything that won't compile in frontend, eg elog() or palloc(). It's mere luck that it's worked for him so far. Considering that ecpg has its own copy of all of gram.y and scan.l, sharing parser.c isn't much of a savings anyway. OK. I will create separate infrastructure fix for ecpg. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Datum should be defined outside postgres.h
Zdenek Kotala [EMAIL PROTECTED] writes: One solution should be put sugar words into separate header and include them directly from catalog/*.h files. Yeah, that would probably be a good idea. It's unlikely that we'll get away anytime soon from frontend code wanting to include catalog/pg_type.h, in particular (to get the macros for type OIDs). [ looks at code... ] Another problem with #including those headers without postgres.h is going to be the function declarations --- eg. GenerateTypeDependencies() needs Node *. I've always thought that the function declarations lurking at the bottom of the catalog headers were pretty out-of-place anyway. What say we pull all the function declarations out of the catalog/pg_xxx.h files? Not quite sure where to put them instead, though. We could smash them all into one new header, but if you want to keep a separate header per module then we'll need some new naming convention to select the filenames to use. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] text search parser, uri
Alvaro Herrera [EMAIL PROTECTED] writes: I observed that the parser calls the part after the host in a URL, uri. This is quite incorrect; a URI is supposed to be a generalization of a URL, so it makes no sense to call that a URI. I was wondering about that, but failed to go check :-( RFC 1738, section 3.1 calls that url-path. My suggestion would be url_path, with an underscore instead of an hyphen to be consistent with the hword_* names. Works for me. Please change it. 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] Datum should be defined outside postgres.h
Zdenek Kotala wrote: Zdenek Kotala wrote: Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: By my opinion Datum should be defined in separate file and all headers which use this type should include it. (this is problem on many places with another types). Another question is why ecpg needs it? Datum is a type that no frontend code has any business dealing in; and the same goes for everything in primnodes.h. I'd suggest trying to fix ecpg to not depend on backend-only include files... OK the problem now is pg_dump.c. It includes postgres.h :( and it is described there why. It needs it for catalog header files. Any suggestion how to fix it? One solution should be put sugar words into separate header and include them directly from catalog/*.h files. Another idea is to test the FRONTEND macro in the include file to prevent inclusion of things you don't need. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Opportunity for a Radical Changes in Database Software
On Thu, Oct 25, 2007 at 08:05:24AM -0700, Dan wrote: In looking at current developments in computers, it seems we're nearing a point where a fundamental change may be possible in databases... Namely in-memory databases which could lead to huge performance improvements. I think there are a number of challenges in this area. Higher end machines are tending towards a NUMA architecture, where postgresql's single buffer pool becomes a liability. In some situations you might want a smaller per processor pool and an explicit copy to grab buffers from processes on other CPUs. I think relibility becomes the real issue though, you can always produce the wrong answer instantly, the trick is to get the right one... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] module archive
From time to time people have raised the idea of a CPAN-like mechanism for downloading, building and installing extensions and the like (types, functions, sample dbs, anything not requiring Postgres itself to be rebuilt), and I have been thinking on this for the last few days. What sort of requirements would people have of such a mechanism? How do people envision it working? 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
[HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 14:45 +, Alvaro Herrera wrote: Log Message: --- Extract catalog info for error reporting before an error actually happens. Also, remove redundant reset of for-wraparound PGPROC flag. Just noticed you've made these changes. I was working on them, but hadn't fully tested the patch because of all the different touch points. Sorry for the delay. Would you like me to refresh my earlier patch against the newly committed state (or did you commit that aspect already as well?). I am doing just that. I'll post it soon. FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ La principal característica humana es la tontería (Augusto Monterroso) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 14:45 +, Alvaro Herrera wrote: Log Message: --- Extract catalog info for error reporting before an error actually happens. Also, remove redundant reset of for-wraparound PGPROC flag. Just noticed you've made these changes. I was working on them, but hadn't fully tested the patch because of all the different touch points. Sorry for the delay. Would you like me to refresh my earlier patch against the newly committed state (or did you commit that aspect already as well?). I am doing just that. I'll post it soon. OK thanks. FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. Remember the complaint we already had on hackers just after beta1: auto *vacuum* blocked a schema change, and of course the user complained. Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.3 GSS Issues
Magnus Hagander [EMAIL PROTECTED] writes: On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: There's no way to specify the gssapi library to use. I have three on my main development Sun: MIT, Sun, and Heimdal. I might have more than one version of one of those three at some times. Of course there's no way to specify which kerberos 5 library or openssl library you want either, so consider this a feature request for future development. Yeah, that's something that can be done for 8.4, certainly not something we can put in now. But I'll be happy to see a patch once we open the tree for 8.4 :-) Isn't this something you do by specifying include and link search paths to configure? The above argument could be made for *any* library we use, and I surely do not want to put a single-purpose switch for each library into configure. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Michael Paesold wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, and in the end you will only vacuum the tables when you run out of time for Xid wraparound. Remember the complaint we already had on hackers just after beta1: auto *vacuum* blocked a schema change, and of course the user complained. Actually I can't remember it, but I think we should decree that this is a known shortcoming; that we will fix it when we have cancellable vacuum; and that the user is free to cancel the vacuuming on his own if he so decides. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ The ability to monopolize a planet is insignificant next to the power of the source ---(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] Opportunity for a Radical Changes in Database Software
On Oct 25, 2007, at 8:05 AM, Dan wrote: In looking at current developments in computers, it seems we're nearing a point where a fundamental change may be possible in databases... Namely in-memory databases which could lead to huge performance improvements. ... The sites that use it see incredible performance increases, but often at the cost of not being able to provide versioned results that are guaranteed to be accurate. The big questions are then, how would you create a distributed in- memory database? Everything you are looking for is here: http://web.mit.edu/dna/www/vldb07hstore.pdf It is the latest Stonebraker et al on massively distributed in-memory OLTP architectures. J. Andrew Rogers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] suitable text search configuration
Have we got consensus that initdb should just look at the first component of the locale name to choose a text search configuration (at least for 8.3)? If so, who's going to make the change? I can do it but don't want to duplicate effort if someone else was already on it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Alvaro Herrera wrote: Michael Paesold wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:41 -0300, Alvaro Herrera wrote: ... FWIW I disagree with cancelling just any autovac work automatically; in my patch I'm only cancelling if it's analyze, on the grounds that if you have really bad luck you can potentially lose a lot of work that vacuum did. We can relax this restriction when we have cancellable vacuum. That was requested by others, not myself, but I did agree with the conclusions. The other bad luck might be that you don't complete some critical piece of work in the available time window because an automated job kicked in. Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, and in the end you will only vacuum the tables when you run out of time for Xid wraparound. Well, only if you do a lot of schema changes. In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Best Regards Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Alvaro Herrera [EMAIL PROTECTED] writes: Michael Paesold wrote: Yeah, I thought we had agreed that we must cancel all auto vacuum/analyzes, on the ground that foreground operations are usually more important than maintenance tasks. What this means is that autovacuum will be starved a lot of the time, Not really, because DDL changes aren't *that* common (at least not for non-temp tables). I think the consensus was that we should cancel autovac in these cases unless it is an anti-wraparound vacuum. Isn't that why you were putting in the flag to show it is for wraparound? regards, tom lane ---(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] 8.3 GSS Issues
On Oct 25, 2007, at 10:22 AM, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: There's no way to specify the gssapi library to use. I have three on my main development Sun: MIT, Sun, and Heimdal. I might have more than one version of one of those three at some times. Of course there's no way to specify which kerberos 5 library or openssl library you want either, so consider this a feature request for future development. Yeah, that's something that can be done for 8.4, certainly not something we can put in now. But I'll be happy to see a patch once we open the tree for 8.4 :-) Isn't this something you do by specifying include and link search paths to configure? The above argument could be made for *any* library we use, and I surely do not want to put a single-purpose switch for each library into configure. All the other OS packages I've looked at seem to support a per- support-option specification of the relevant installation to use for that support. I expect that's a pain to implement, but it's what I (and presumably other people) expect. As I said this is a request for the future. The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(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] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. Agreed. If auto-vacuum is blocking activity, it isn't very 'auto' to me. If vacuum is blocking something, by definition it is a bad time for it to be vacuuming and it should abort and try again later. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.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] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Simon Riggs wrote: On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. Perhaps I misunderstood, or have been mistunderstood :-) - I am actually agreeing that autovac should not block DDL. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 8.3beta1 testing on Solaris
Update on my testing 8.3beta1 on Solaris.
* CLOG reads
* Asynchronous Commit benefit
* Hot CPU Utilization
Regards,
Jignesh
__Background_:_
We were using PostgreSQL 8.3beta1 testing on our latest Sun SPARC
Enterprise T5220 Server using Solaris 10 8/07 and Sun Fire X4200 using
Solaris 10 8/07. Generally for performance benefits in Solaris we put
file systems on forcedirectio we bypass the filesystem cache and go
direct to disks.
__Problem_:_
What we were observing that there were lots of reads happening about
4MB/sec on the file system holding $PGDATA and the database tables
during an OLTP Benchmark run. Initially we thought that our bufferpools
were not big enough. But thanks to 64-bit builds we could use bigger
bufferpools. However even with extraordinary bufferpool sizes we still
saw lots of reads going to the disks.
__DTrace to the Rescue_:_
I modified iosnoop.d to just snoop on reads. The modified rsnoop.d is as
follows:
$ cat rsnoop.d
#!/usr/sbin/dtrace -s
syscall::read:entry
/execname==postgres/
{
printf(pid %d reading %s\n, pid, fds[arg0].fi_pathname);
}
Based on it I found that most postgresql processes were doing lots of
reads from pg_clog directory.
CLOG or commit logs keep track of transactions in flight. Writes of CLOG
comes from recording of transaction commits( or when it aborts) or when
an XLOG is generated. However though I am not clear on reads yet, it
seems every process constantly reads it to get some status. CLOG data is
not cached in any PostgreSQL shared memory segments and hence becomes
the bottleneck as it has to constantly go to the filesystem to get the
read data.
# ./rsnoop.d
dtrace: script './rsnoop.d' matched 1 probe
CPU IDFUNCTION:NAME
0 49222 read:entry pid 8739 reading
/export/home0/igen/pgdata/pg_clog/000C
0 49222 read:entry pid 9607 reading
/export/home0/igen/pgdata/pg_clog/000C
0 49222 read:entry pid 9423 reading
/export/home0/igen/pgdata/pg_clog/000C
0 49222 read:entry pid 8731 reading
/export/home0/igen/pgdata/pg_clog/000C
0 49222 read:entry pid 8719 reading
/export/home0/igen/pgdata/pg_clog/000C
0 49222 read:entry pid 9019 reading
/export/home0/igen/pgdata/pg_clog/000C
1 49222 read:entry pid 9255 reading
/export/home0/igen/pgdata/pg_clog/000C
1 49222 read:entry pid 8867 reading
/export/home0/igen/pgdata/pg_clog/000C
Later on during another run I added ustack() after the printf in the
above script to get the function name also:
# ./rsnoop.d
dtrace: script './rsnoop.d' matched 1 probe
CPU IDFUNCTION:NAME
0 49222 read:entry pid 10956 reading
/export/home0/igen/pgdata/pg_clog/0011
libc.so.1`_read+0xa
postgres`SimpleLruReadPage+0x3e6
postgres`SimpleLruReadPage_ReadOnly+0x9b
postgres`TransactionIdGetStatus+0x1f
postgres`TransactionIdDidCommit+0x42
postgres`HeapTupleSatisfiesVacuum+0x21a
postgres`heap_prune_chain+0x14b
postgres`heap_page_prune_opt+0x1e6
postgres`index_getnext+0x144
postgres`IndexNext+0xe1
postgres`ExecScan+0x189
postgres`ExecIndexScan+0x43
postgres`ExecProcNode+0x183
postgres`ExecutePlan+0x9e
postgres`ExecutorRun+0xab
postgres`PortalRunSelect+0x47a
postgres`PortalRun+0x262
postgres`exec_execute_message+0x565
postgres`PostgresMain+0xf45
postgres`BackendRun+0x3f9
0 49222 read:entry pid 10414 reading
/export/home0/igen/pgdata/pg_clog/0011
libc.so.1`_read+0xa
postgres`SimpleLruReadPage+0x3e6
postgres`SimpleLruReadPage_ReadOnly+0x9b
postgres`TransactionIdGetStatus+0x1f
postgres`TransactionIdDidCommit+0x42
postgres`HeapTupleSatisfiesVacuum+0x21a
postgres`heap_prune_chain+0x14b
postgres`heap_page_prune_opt+0x1e6
postgres`index_getnext+0x144
postgres`IndexNext+0xe1
postgres`ExecScan+0x189
^C libc.so.1`_read+0xa
postgres`SimpleLruReadPage+0x3e6
postgres`SimpleLruReadPage_ReadOnly+0x9b
postgres`TransactionIdGetStatus+0x1f
postgres`TransactionIdDidCommit+0x42
postgres`HeapTupleSatisfiesMVCC+0x34f
postgres`index_getnext+0x29e
postgres`IndexNext+0xe1
postgres`ExecScan+0x189
postgres`ExecIndexScan+0x43
postgres`ExecProcNode+0x183
postgres`ExecutePlan+0x9e
postgres`ExecutorRun+0xab
postgres`PortalRunSelect+0x47a
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Michael Paesold wrote: Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. Well, there's going to be pain *somewhere* here, and we already know that users will find the current 8.3 behavior unacceptable. I'd rather have autovacuum not make progress than have users turn it off because it gets in their way too much. Which I think is exactly what will happen if we ship it with the current behavior. exactly - 8.3 will be the first release with autovac enabled by default (and concurrent autovacuuming) and we really need to make sure that people wont get surprised by it in the way I (and others) did when playing with Beta1. So my vote would be on cancelling always except in the case of a wraparound vacuum. Stefan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] 8.3beta1 testing on Solaris
Jignesh K. Shah [EMAIL PROTECTED] writes: CLOG data is not cached in any PostgreSQL shared memory segments The above statement is utterly false, so your trace seems to indicate something broken. Are you sure these were the only reads of pg_clog files? Can you extend the tracing to determine which page of the file got read? I am wondering if your (unspecified) test load was managing to touch more pages of the clog than there is room for in shared memory. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [COMMITTERS] pgsql: Extract catalog info for error reporting before an error actually
On Thu, Oct 25, 2007 at 03:54:28PM -0400, Andrew Dunstan wrote: Simon Riggs wrote: On Thu, 2007-10-25 at 13:51 -0400, Andrew Dunstan wrote: Michael Paesold wrote: In the previous discussion, Simon and me agreed that schema changes should not happen on a regular basis on production systems. Shouldn't we rather support the regular usage pattern instead of the uncommon one? Users doing a lot of schema changes are the ones who should have to work around issues, not those using a DBMS sanely. No? Unfortunately, doing lots of schema changes is a very common phenomenon. It makes me uncomfortable too, but saying that those who do it have to work around issues isn't acceptable IMNSHO - it's far too widely done. We didn't agree that DDL was uncommon, we agreed that running DDL was more important than running an auto VACUUM. DDL runs very quickly, unless blocked, though holds up everybody else. So you must run it at pre-planned windows. VACUUMs can run at any time, so a autoVACUUM shouldn't be allowed to prevent DDL from running. The queuing DDL makes other requests queue behind it, even ones that would normally have been able to execute at same time as the VACUUM. Anyway, we covered all this before. I started off saying we shouldn't do this and Heikki and Michael came up with convincing arguments, for me, so now I think we should allow autovacuums to be cancelled. Perhaps I misunderstood, or have been mistunderstood :-) - I am actually agreeing that autovac should not block DDL. +1 here for having autovacuum not block DDL :) Cheers, David (for what it's worth) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.3 GSS Issues
On Oct 25, 2007, at 1:47 AM, Magnus Hagander wrote: On Fri, Oct 19, 2007 at 04:51:04PM -0700, Henry B. Hotz wrote: I know I haven't been very active for a while here, but I just got to testing the October 3 version a bit prior to getting back to the Java GSS client stuff I promised. There seem to be some funny things there. Apologies for not responding to this one sooner. BTW, what's the status on the Java stuff? Will it be around by the time 8.3 is released? Touche. ;-) I hope to get to that in the next couple of weeks. The only serious issue is that the server doesn't require the realm name to match. I haven't looked at how that broke yet, but I know I was careful of that point in my original patches because it's always been wrong in the Kerberos 5 auth method. I honestly don't remember exactly how that became. I think I stripped it out in order to make it work like the krb5 method. What the krb5 method does is IMO a documented bug. The realm name is part of the name. As I explained at some length you cannot assume the username (first component of the principal) has any meaning by itself, except in small deployments with no external trust agreements. Kerberos (and AD) are designed to support larger infrastructures with multiple organizations. What you're asking for is basically a krb_match_realm parameter, or do I understand you wrong? I'm asking for name matching to be done i.a.w. the gssapi recommendations. That's all I want, but it's actually necessary for this feature to be at all usable in my environment. If we don't then I suggest we pull this feature until it can be done correctly. If you want to add a non-default ignore_realm option I have no objection, but the code may not be robust to varying gssapi implementations. Guaranteed it won't work with a non-kerberos mechanism like SPKM (which isn't widely deployed). For a proper discussion of this topic I recommend the section starting on page 64 of Sun's Security for Developers Guide, document 816-4863. Note that there is a discussion of how to do compares efficiently. IIRC my patch did things the easy way described on page 67. In the long run it's possible we'd want to do it the fast way described on page 69, but that's merely an optimization and might not be needed. It appears that you can just put a complete (realm-included) name into postgres, so that's obviously the way to support gssapi connections from non-default realms. In short this is a security hole. IMO it should be fixed prior to release. Can't you also configure the kerberos libraries on your machine not to accept other realms than your own? IIRC, that was something considered at the time, but I can't find a reference to such a discussion. Kerberos is about authenticating (identifying) users, not determining what they're authorized (allowed) to do. At the basic protocol level I can only refuse to exchange cross-realm keys with anybody. There's a NASA interpretation of a Presidential directive that would likely prohibit me from using this option, even if I wanted to. If I have even one user @NASA.GOV that I want to allow in to even one service @JPL.NASA.GOV then I can't use this option. The three major Kerberos implementations, Microsoft, MIT (including Apple and Sun), and Heimdal, all have different degrees of support for authorization control. MIT and Heimdal (and Microsoft, I assume) will let you trust STANFORD.EDU and *.NASA.GOV without (for example) trusting STANFORD.EDU to identify [EMAIL PROTECTED] Microsoft bundles their Kerberos with an LDAP server so they can do fine-grained authorization control from the same place. Every individual user in any trusted realm needs to have an entry in the local LDAP in order to get access to a Windows service. MIT supports an auth_to_local translation service to relate Kerberos principals to local workstation usernames. By default, the local realm is translated to the username by stripping the realm name. Other realms do not translate. The facility is poorly documented, and not standard, so I cannot recommend it. - I notice there are hba options for gss and sspi both. Why? Is there some windows-only functionality it enables? Shouldn't we be using Microsoft's advertised GSSAPI/SSPI compatibility? If you build on Windows then I'm sure you want to link the SSPI libraries rather than require installation of a separate package, but that shouldn't change the functionality or the wire protocol AFAIK. In other words I would expect this to be a build-time option. There was discussion about this, and we were presented with clear cases where you'd want to be able to do either one. Making it a build option doesn't help the 99.9% of Windows users that use a pre-packaged binary distribution. Doesn't exactly answer my question, but I guess there exists a Windows use case where linking
Re: [HACKERS] 8.3 GSS Issues
* Henry B. Hotz ([EMAIL PROTECTED]) wrote: What the krb5 method does is IMO a documented bug. The realm name is part of the name. As I explained at some length you cannot assume the username (first component of the principal) has any meaning by itself, except in small deployments with no external trust agreements. Kerberos (and AD) are designed to support larger infrastructures with multiple organizations. This isn't unexpected for PG as the current krb5 support does this. I'm not a big fan of it but at the same time I don't feel it's justification to drop it from 8.3. Having it only allow the default realm would be an option which could work in 8.3, imv. Longer term (since it's likely too late to be accepted now), as I think has been discussed in the past, PG could really use a .k5login-esque, either admin-only (ala pg_hba.conf / ident map) or per-user (some sort of ALTER ROLE that a user could do on himself?), mapping functionality. It doesn't strike me as terribly complex or hard to do but it certainly goes beyond the what is currently implemented for GSS in 8.3, and what exists currently for krb5. It's also something which could, technically, be added later. I do think it would be better done now though, if possible, since otherwise we would have to default to the current sub-par behaviour for quite some time (if not forever). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] 8.3beta1 testing on Solaris
Jignesh K. Shah [EMAIL PROTECTED] writes: CLOG data is not cached in any PostgreSQL shared memory segments and hence becomes the bottleneck as it has to constantly go to the filesystem to get the read data. This is the same bottleneck you discussed earlier. CLOG reads are cached in the Postgres shared memory segment but only NUM_CLOG_BUFFERS are which defaults to 8 buffers of 8kb each. With 1,000 clients and the transaction rate you're running you needed a larger number of buffers. Using the filesystem buffer cache is also an entirely reasonable solution though. That's surely part of the logic behind not trying to keep more of the clog in shared memory. Do you have any measurements of how much time is being spent just doing the logical I/O to the buffer cache for the clog pages? 4MB/s seems like it's not insignificant but your machine is big enough that perhaps I'm thinking at the wrong scale. I'm really curious whether you see any benefit from the vxid read-only transactions. I'm not sure how to get an apples to apples comparison though. Ideally just comparing it to CVS HEAD from immediately prior to the vxid patch going in. Perhaps calling some function which forces an xid to be allocated and seeing how much it slows down the benchmark would be a good substitute. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.3beta1 testing on Solaris
Tom Lane [EMAIL PROTECTED] writes: Jignesh K. Shah [EMAIL PROTECTED] writes: CLOG data is not cached in any PostgreSQL shared memory segments The above statement is utterly false, so your trace seems to indicate something broken. Are you sure these were the only reads of pg_clog files? Can you extend the tracing to determine which page of the file got read? I am wondering if your (unspecified) test load was managing to touch more pages of the clog than there is room for in shared memory. Didn't we already go through this? He and Simon were pushing to bump up NUM_CLOG_BUFFERS and you were arguing that the test wasn't representative and some other clog.c would have to be reengineered to scale well to larger values. Also it seemed there were only modest improvements from raising the value and there would always be a ceiling to bump into so just raising the number of buffers isn't particularly interesting unless there's some magic numbers we're trying to hit. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] suitable text search configuration
Tom Lane wrote: Have we got consensus that initdb should just look at the first component of the locale name to choose a text search configuration (at least for 8.3)? If so, who's going to make the change? I can do it but don't want to duplicate effort if someone else was already on it. Thanks, it works wonderfully for me now. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Ni aun el genio muy grande llegaría muy lejos si tuviera que sacarlo todo de su propio interior (Goethe) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.3 GSS Issues
On Oct 25, 2007, at 3:27 PM, Stephen Frost wrote: * Henry B. Hotz ([EMAIL PROTECTED]) wrote: What the krb5 method does is IMO a documented bug. The realm name is part of the name. As I explained at some length you cannot assume the username (first component of the principal) has any meaning by itself, except in small deployments with no external trust agreements. Kerberos (and AD) are designed to support larger infrastructures with multiple organizations. This isn't unexpected for PG as the current krb5 support does this. I'm not a big fan of it but at the same time I don't feel it's justification to drop it from 8.3. Having it only allow the default realm would be an option which could work in 8.3, imv. I don't think the fact that the existing krb5 code does the wrong thing (and can't be used in an environment with cross-realm agreements) is justification for doing the wrong thing in a new capability. The code in my original patch would do the latter (default realm only). More precisely: if you do a gss_import_name() on smith and [EMAIL PROTECTED] you get the same internal representation, and gss_compare_name() will tell you they're the same. Also gss_compare_name() will tell you [EMAIL PROTECTED] is different from either of the first two. If we don't use gss_compare_name(), or some similar mechanism, to compare connection names to PG usernames, then I don't think GSSAPI support should be included in 8.3. Longer term (since it's likely too late to be accepted now), as I think has been discussed in the past, PG could really use a .k5login-esque, either admin-only (ala pg_hba.conf / ident map) or per-user (some sort of ALTER ROLE that a user could do on himself?), mapping functionality. There has been discussion of a general mapping layer between authentication names and authorization/role names. I think that's the way to go. I haven't thought about who or where the administration of the mapping ought to be. See note about authentication vs authorization in last email. It doesn't strike me as terribly complex or hard to do but it certainly goes beyond the what is currently implemented for GSS in 8.3, and what exists currently for krb5. It's also something which could, technically, be added later. I do think it would be better done now though, if possible, since otherwise we would have to default to the current sub-par behaviour for quite some time (if not forever). Thanks, Stephen The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [HACKERS] 8.3beta1 testing on Solaris
Gregory Stark [EMAIL PROTECTED] writes: Didn't we already go through this? He and Simon were pushing to bump up NUM_CLOG_BUFFERS and you were arguing that the test wasn't representative and some other clog.c would have to be reengineered to scale well to larger values. AFAIR we never did get any clear explanation of what the test case is. I guess it must be write-mostly, else lazy XID assignment would have helped this by reducing the rate of XID consumption. It's still true that I'm leery of a large increase in the number of buffers without reengineering slru.c. That code was written on the assumption that there were few enough buffers that a linear search would be fine. I'd hold still for 16, or maybe even 32, but I dunno how much impact that will have for such a test case. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] 8.3beta1 testing on Solaris
Tom, It's still true that I'm leery of a large increase in the number of buffers without reengineering slru.c. That code was written on the assumption that there were few enough buffers that a linear search would be fine. I'd hold still for 16, or maybe even 32, but I dunno how much impact that will have for such a test case. Actually, 32 made a significant difference as I recall ... do you still have the figures for that, Jignesh? The test case is a workload called iGen which is a fixed TPCC-like workload. I've been trying to talk Sun into open-sourcing it, but no dice so far. It is heavy on writes, and (like TPCC) consists mostly of one-line transactions. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(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: [PERFORM] [HACKERS] 8.3beta1 testing on Solaris
Josh Berkus [EMAIL PROTECTED] writes: Actually, 32 made a significant difference as I recall ... do you still have the figures for that, Jignesh? I'd want to see a new set of test runs backing up any call for a change in NUM_CLOG_BUFFERS --- we've changed enough stuff around this area that benchmarks using code from a few months back shouldn't carry a lot of weight. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] PANIC caused by open_sync on Linux
I encountered PANICs on CentOS 5.0 when I ran write-mostly workload. It occurs only if wal_sync_method is set to open_sync; there were no problem in fdatasync. It occurred on both Postgres 8.2.5 and 8.3dev. PANIC: could not write to log file 0, segment 212 at offset 3399680, length 737280: Input/output error STATEMENT: COMMIT; My nearby Linux guy says mixed usage of bufferd I/O and direct I/O could cause errors (EIO) on many version of Linux kernels. If we use buffered I/O before direct I/O, Linux could fail to discard kernel buffer cache of the region and report EIO -- yes, it's a bug in Linux. We use bufferd I/O on WAL segements even if wal_sync_method is open_sync. We initialized segements with zero using buffered I/O, and after that, we re-open them with specified sync options. The behaviors in the bug are different on RHEL 4 and 5. RHEL 4 - No error reports even though the kernel cache is incosistenet. RHEL 5 - write() failes with EIO (Input/output error) PANIC occurs only on RHEL 5, but RHEL 4 also has a problem. If a wal archiver reads the inconsistent cache of wal segments, it could archive wrong contents and PITR might fail at the corrupted archived file. I'll recommend not to use open_sync for users on Linux until the bug is fiexed. However, are there any idea to avoid the bug and to use direct i/o? Mixed usage of bufferd and direct i/o is legal, but enforces complexity to kernels. If we simplify it, things would be more relaxed. For example, dropping zero-filling and only use direct i/o. Is it possible? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PANIC caused by open_sync on Linux
On Fri, 26 Oct 2007, ITAGAKI Takahiro wrote: My nearby Linux guy says mixed usage of buffered I/O and direct I/O could cause errors (EIO) on many version of Linux kernels. I'd be curious to get some more information about this--specifically which versions have the problems. I'd heard about some weird bugs in the sync write code in versions between RHEL 4 (2.6.9) and 5 (2.6.18), but I wasn't aware of anything wrong with those two stable ones in this area. I have a RHEL 5 system here, will see if I can replicate this EIO error. Mixed usage of buffered and direct i/o is legal, but enforces complexity to kernels. If we simplify it, things would be more relaxed. For example, dropping zero-filling and only use direct i/o. Is it possible? It's possible, but performance suffers considerably. I played around with this at one point when looking into doing all database writes as sync writes. Having to wait until the entire 16MB WAL segment made its way to disk before more WAL could be written can cause a nasty pause in activity, even with direct I/O sync writes. Even the current buffered zero-filled write of that size can be a bit of a drag on performance for the clients that get caught behind it, making it any sort of sync write will be far worse. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
