Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
Tom Lane [EMAIL PROTECTED] wrote Yeah. The LIKE index optimization depends on seeing a constant LIKE pattern at plan time --- otherwise the planner doesn't know what indexscan parameters to generate. So a bound-parameter query loses. AFAICS the problem is not restricted to LIKE, we can easily find a lot of similar problems caused by the actual parameters. For example, SeqScan vs. IndexScan vs. BitmapIndexScan for a range query. So an improvement is definitely needed. Ideas for improving this situation are welcome ... it's not an easy problem ... IMHO basically we have two ways to get better plan: one is to have a set of alternative plans for prepare queries. This will add some cost but PREPARE is supposed to do only once against a lot of EXECUTE. But still, the biggest problem is that number of plans is not controllable. Another way is to generate a plan on the fly. What we do is to let some REPLAN nodes sit on top of some critical plan node: at the execution, we will compare the actual numbers we get and the estimated number we have (mabye rows?), once we find that a re-plan efforts might be deserved, we will get a new plan on the fly. In this way, I think a not-too-big patch will do. I remember there is a paper talking about this somewhere but not remember clearly. -- This method can handle the range query problem above, but not for LIKE. So we may have to kludge some code to handle LIKE especially :-(. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
Added to TODO: o Add ALTER TABLE tab ADD/DROP INHERITS parent Sounds good, more inline with add/drop constraint. pg_attribute.attislocal has to be set to 'false' for ADD, and attislocal: If you set this to False, you wouldn't be able to set it back again. Just curious, why is that ? pg_attribute.attinhcount adjusted appropriately Do we have a plan on what to do with RI constraints on the parent when doing DROP INHERTITS ? Seems all FK tables would need to be checked for keys in the local table. (Not sure whether we have PK on tab*, but it seems you could do it when the partitioning column is part of the PK) Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, K, 2006-05-24 kell 09:56, kirjutas Zeugswetter Andreas DCP SD: Added to TODO: o Add ALTER TABLE tab ADD/DROP INHERITS parent Sounds good, more inline with add/drop constraint. pg_attribute.attislocal has to be set to 'false' for ADD, and attislocal: If you set this to False, you wouldn't be able to set it back again. Just curious, why is that ? pg_attribute.attinhcount adjusted appropriately Do we have a plan on what to do with RI constraints on the parent when doing DROP INHERTITS ? I think this is out of scope of this discussion, as we do not currently support RI over inherited tables. IOW RI is always on one table ONLY. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Further reduction of bufmgr lock contention
Tom Lane wrote: We're certainly not putting any such thing into 8.1.*. The proposed patch for 8.2 is stalled ATM because of the problem of not having a predictable size for the per-partition hash tables. Fixed-size shared memory is a harsh mistress :-( Fair enough :) Just wanted to ascertain that it was still a going concern - I have full confidence that you'll have a brainwave one morning as to the perfect solution =) gdh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, T, 2006-05-23 kell 18:48, kirjutas Simon Riggs: 2. attislocal is always set False when an appropriate ADD INHERITS is actioned. Not ever set back again. Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. attinhcount changes as appropriate - up for ADDs and down for DROPs. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] LIKE, leading percent, bind parameters and indexes
AFAICS the problem is not restricted to LIKE, we can easily find a lot of similar problems caused by the actual parameters. For example, SeqScan vs. IndexScan vs. BitmapIndexScan for a range query. So an improvement is definitely needed. Another way is to generate a plan on the fly. What we do is to let some REPLAN nodes sit on top of some critical plan node: at the execution, we will compare the actual numbers we get and the estimated number we have Since we are deciding this on histogram data, it seems we could store the ranges (and exception values) where this plan is not good, and replan in case the new value does not fit. This would also imply, that we postpone (part of the) planning until we get the first values, when the node cost largly depends on the supplied value. Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Hi I'm a new postgresql user. I wrote ACO (ant colony optimazition) and want to replace it with GEQO in postres/src/backend/optimizer but I don't know how to compile and run the source code :( I installed postgresql-8.1.3 and cygwin but I can not use them to compile the source code. I want tocompare GEQO and ACOoptimizers performance using a small database Can you help me??? Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
Re: [HACKERS] file-locking and postmaster.pid
On Tuesday 23 May 2006 19:36, Tom Lane wrote: Adis Nezirovic [EMAIL PROTECTED] writes: Well, maybe you could tweak postgres startup script, add check for post master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and delete pid file on negative results. This is exactly what you should NOT do. A start script that thinks it is smarter than the postmaster is almost certainly wrong. It is certainly dangerous, too, because auto-deleting that pidfile destroys the interlock against having two postmasters running in the same data directory (which WILL corrupt your data, quickly and irretrievably). All it takes to cause a problem is to use the start script to start a postmaster, forgetting that you already have one running ... My PG is not started with startup-scripts, but with this command: pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(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] file-locking and postmaster.pid
On Wednesday 24 May 2006 11:36, Andreas Joseph Krogh wrote: On Tuesday 23 May 2006 19:36, Tom Lane wrote: Adis Nezirovic [EMAIL PROTECTED] writes: Well, maybe you could tweak postgres startup script, add check for post master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and delete pid file on negative results. This is exactly what you should NOT do. A start script that thinks it is smarter than the postmaster is almost certainly wrong. It is certainly dangerous, too, because auto-deleting that pidfile destroys the interlock against having two postmasters running in the same data directory (which WILL corrupt your data, quickly and irretrievably). All it takes to cause a problem is to use the start script to start a postmaster, forgetting that you already have one running ... My PG is not started with startup-scripts, but with this command: pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start ... and manually after login, ie. not at boot-time. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(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] Updatable views/with check option parsing
I have spent some time figuring out how to resolve the parsing conflicts in Bernd Helmle's updatable views patch. The problem has now been reduced to specifically this situation: CREATE VIEW foo AS SELECT expr :: TIME . WITH (where expr is a_expr or b_expr and TIME could also be TIMESTAMP or TIME(x) or TIMESTAMP(x)). The continuation here could be WITH TIME ZONE (calling for a shift) or WITH CHECK OPTION (calling for a reduce). All the usual ideas about unfolding the rules or making keywords more reserved don't work (why should they). A one-token lookahead simply can't parse this. I have had some ideas about trying to play around with the precedence rules -- giving WITH TIME ZONE a higher precedence than WITH CHECK OPTION -- but I have no experience with that and I am apparently not doing it right, if that is supposed to work at all. If we can't get that to work, it seems that we are out of options unless we want to just accept the conflicts. How should we go about this, and what should Bernd do with his patch, which, as I understand it, has been held up for quite a while simply because he is concerned about this issue? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Updatable views/with check option parsing
Ühel kenal päeval, K, 2006-05-24 kell 13:13, kirjutas Peter Eisentraut: I have spent some time figuring out how to resolve the parsing conflicts in Bernd Helmle's updatable views patch. The problem has now been reduced to specifically this situation: CREATE VIEW foo AS SELECT expr :: TIME . WITH (where expr is a_expr or b_expr and TIME could also be TIMESTAMP or TIME(x) or TIMESTAMP(x)). The continuation here could be WITH TIME ZONE (calling for a shift) or WITH CHECK OPTION (calling for a reduce). All the usual ideas about unfolding the rules or making keywords more reserved don't work (why should they). A one-token lookahead simply can't parse this. Can't we teach tokenized a new token WITH TIME ZONE ? I have had some ideas about trying to play around with the precedence rules -- giving WITH TIME ZONE a higher precedence than WITH CHECK OPTION -- but I have no experience with that and I am apparently not doing it right, if that is supposed to work at all. If we can't get that to work, it seems that we are out of options unless we want to just accept the conflicts. How should we go about this, and what should Bernd do with his patch, which, as I understand it, has been held up for quite a while simply because he is concerned about this issue? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] problem with PQsendQuery/PQgetResult and COPY FROM statement
Thanks for all your replies, but I must clarify some things.
First, note that what I posted is just a small example that reproduces
behavior that appears incorrect. The real code is a C++ wrapper
around libpq that supports non-blocking queries and reuses open
connections.
Volkan and Martijn: I know about PQ{put,get}CopyData, but my example
never gets to that point. I must first determine whether the
(asynchronous) PQsendQuery() of COPY test FROM STDIN succeeded.
That's all that retrieve() tries to do in my example.
Tom: of course I should (and eventually do) use PQclear(), but I may
not want to right away, because I must return to the user a vector of
result objects (for example, all the result rows from a query).
I do not expect PQgetResult to return millions of non-null PGresult
objects after a PQsendQuery(COPY test FROM STDIN). I expect exactly
one non-null result, with a result status of PGRES_COPY_IN. Moreover,
the manual says:
If a COPY command is issued via PQexec in a string that could
contain
additional commands, the application must continue fetching results
via PQgetResult after completing the COPY sequence. Only when
PQgetResult returns NULL is it certain that the PQexec command
string
is done and it is safe to issue more commands.
I assumed this to be true for PQexec or one of the equivalent
functions mentioned in the manual, such as PQsendQuery. However, if
I add the following switch statement to my example:
while (!PQisBusy(conn)) {
PGresult *r = PQgetResult(conn);
if (r) {
switch (PQresultStatus(r)) {
case PGRES_COPY_IN:
break;
case PGRES_EMPTY_QUERY:
printf(PGRES_EMPTY_QUERY\n);
break;
case PGRES_COMMAND_OK:
printf(PGRES_COMMAND_OK\n);
break;
case PGRES_TUPLES_OK:
printf(PGRES_TUPLES_OK\n);
break;
case PGRES_COPY_OUT:
printf(PGRES_COPY_OUT\n);
break;
case PGRES_BAD_RESPONSE:
printf(PGRES_BAD_RESPONSE\n);
break;
case PGRES_NONFATAL_ERROR:
printf(PGRES_NONFATAL_ERROR\n);
break;
case PGRES_FATAL_ERROR:
printf(PGRES_FATAL_ERROR\n);
break;
}
res.push_back(r);
if (++i % 500 == 0) { printf(%d results\n, i); }
if (r == oldr) { printf(r==oldr (%p)\n, r); }
oldr = r;
} else {
printf(PQgetResult return 0 after %d results\n, i);
return;
}
}
the code still prints only:
500 results
1000 results
1500 results
2000 results
2500 results
PQgetResult return 0 after 25649299 results
In other words, there are 25M distinct non-null results, and all of
them have status code PGRES_COPY_IN, and none of them have errors.
So it appears that I should check whether the first PGresult object
has a status code of PGRES_COPY_IN, and ignore subsequent PGresults
even if they are not NULL. I don't object to this interface, but it
is not what I would conclude after RTFM.
max
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: Ühel kenal päeval, T, 2006-05-23 kell 18:48, kirjutas Simon Riggs: 2. attislocal is always set False when an appropriate ADD INHERITS is actioned. Not ever set back again. Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] error-free disabling of individual child partition
Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? The invariant is supposed to be that every attribute has attislocal true or attinhcount 0 (or both). Otherwise it has no justification to exist. I see in the regression database that someone has broken this invariant; it looks like LIKE inheritance is misimplemented. I'm going to insist on a fix for that ;-) I think that the correct behavior for add/drop is: * ADD INHERITS increments attinhcount for every column found to match a column of the parent. Nothing happens to attislocal. * DROP INHERITS decrements attinhcount for every column found to match a column of the parent. Set attislocal true if attinhcount thereby goes to zero. This makes ADD followed by DROP a certain no-op (if attinhcount was zero before the ADD, then attislocal must have been true already). However, DROP followed by ADD is not a no-op since we might wind up with attislocal true in a column that wasn't that way before. This seems like a relatively minor thing though. The alternative would be to delete the child column when it has no definition sources left; which would be self-consistent but I don't think it's the behavior we want for this. You could also imagine clearing attislocal during ADD, but that just changes which case isn't a complete no-op, so I don't see any great attraction to it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving ALTER TYPE support
Josh Berkus josh@agliodbs.com writes: I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: Why not go all the way and work out a way to define an SRF return type as a part of the function? e.g. Um, isn't that exactly what the OUT parameter support already gives you, ie, an anonymous record type? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updatable views/with check option parsing
Hannu Krosing wrote: Can't we teach tokenized a new token WITH TIME ZONE ? No, that's three tokens, not one. We surely don't want to start making white space significant. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] error-free disabling of individual child partition
Ühel kenal päeval, K, 2006-05-24 kell 09:40, kirjutas Tom Lane: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? The invariant is supposed to be that every attribute has attislocal true or attinhcount 0 (or both). In what case does it have both ? Or is it so that 1) islocal means that column is not inherited from any parent 2) attinhcount is the number of direct children who inherit this attribute In this case it should be possible to keep both right and make both ADD+DROP and DROP+ADD invariants. Otherwise it has no justification to exist. I see in the regression database that someone has broken this invariant; it looks like LIKE inheritance is misimplemented. I don't think that LIKE inheritance is inheritance at all, rather it is a create-time macro. I'm going to insist on a fix for that ;-) Agreed. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.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] error-free disabling of individual child partition
Hannu Krosing wrote: Ühel kenal päeval, K, 2006-05-24 kell 09:40, kirjutas Tom Lane: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? The invariant is supposed to be that every attribute has attislocal true or attinhcount 0 (or both). In what case does it have both ? create table parent (foo int); create table child (foo int) inherits (parent); In the child, the column is local but it's also inherited from parent. So if you drop the column from the parent, it should be kept in the child. Otherwise it has no justification to exist. I see in the regression database that someone has broken this invariant; it looks like LIKE inheritance is misimplemented. I don't think that LIKE inheritance is inheritance at all, rather it is a create-time macro. In that case the columns should be marked attislocal. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] error-free disabling of individual child partition
Hannu Krosing wrote: I don't think that LIKE inheritance is inheritance at all, rather it is a create-time macro. Right. It's actually quite useful. I'd like to see it made available in a couple of other contexts, such as CREATE TYPE and the type expression needed when calling a function that returns a RECORD or SETOF RECORD. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] error-free disabling of individual child partition
Alvaro Herrera [EMAIL PROTECTED] writes: Hannu Krosing wrote: I don't think that LIKE inheritance is inheritance at all, rather it is a create-time macro. In that case the columns should be marked attislocal. Right. 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] Updatable views/with check option parsing
Peter Eisentraut [EMAIL PROTECTED] writes: I have spent some time figuring out how to resolve the parsing conflicts in Bernd Helmle's updatable views patch. The problem has now been reduced to specifically this situation: Could we see the proposed patches for gram.y? If we can't get that to work, it seems that we are out of options unless we want to just accept the conflicts. Not acceptable, per prior discussions any time someone was too lazy to fix their grammar patch completely ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] file-locking and postmaster.pid
On 5/24/06, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: My PG is not started with startup-scripts, but with this command: pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start ... and manually after login, ie. not at boot-time. I'd suggest trying to fix your Linux-install instead of mucking about with Postgres, and this really a pgsql-novice question, not a -hackers thing. Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Further reduction of bufmgr lock contention
Tom, BTW, we're going to be testing this patch on Sun Niagara servers. What's the outstanding bug with it? I don't quite follow. I think I can get some of the Sun MDEs to take a stab at it if I can understand the issue. Links ok if maybe I've not found part of this thread in the archives. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Improving ALTER TYPE support
I guess I don't understand what one has to do with the other (SRF's returning records and OUT parameters). I always thought they were exclusive, could you elaborate?On 5/24/06, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus josh@agliodbs.com writes: I've been working on a function which returns a setof a composite type. Everytime I've changed the structure of the returning setof, I've had to change the type accordingly, which current means doing a drop type ... cascade down to the function. We should allow one of the following: Why not go all the way and work out a way to define an SRF return type as a part of the function?e.g.Um, isn't that exactly what the OUT parameter support already gives you,ie, an anonymous record type?regards, tom lane
Re: [HACKERS] problem with PQsendQuery/PQgetResult and COPY FROM statement
[EMAIL PROTECTED] writes: I do not expect PQgetResult to return millions of non-null PGresult objects after a PQsendQuery(COPY test FROM STDIN). I expect exactly one non-null result, with a result status of PGRES_COPY_IN. If you call it exactly once, it'll say that exactly once. If you keep calling it millions of times, it'll keep saying that. Moreover, the manual says: If a COPY command is issued via PQexec in a string that could contain additional commands, the application must continue fetching results via PQgetResult after completing the COPY sequence. Only when PQgetResult returns NULL is it certain that the PQexec command string is done and it is safe to issue more commands. Indeed. You forgot to complete the COPY sequence before returning to the PQgetResult loop. As long as the thing is in COPY mode, PQgetResult will return a result saying PGRES_COPY_IN. The point of this paragraph is that you might want to consider doing more PQgetResults *after* you've ended COPY mode. 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] Why is CVS server so slow?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: 24 May 2006 20:06 To: Tom Lane Cc: Michael Fuhr; Simon Riggs; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Why is CVS server so slow? Anyone noticing any network issues? I just looked over the server itself, swapping a *little* bit, but vmstat shows the disks aren't busy ... and nothing unusual taking up CPU on the server itself ... Err, have you read your emails from Magnus yet today? They might explain a thing or two. /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Why is CVS server so slow?
On Tue, May 23, 2006 at 08:30:36PM -0300, Marc G. Fournier wrote: I'm logged on daily interactively, and haven't noticed any issues ... Are you both referring to the same server? I've noticed that anoncvs.postgresql.org (66.98.251.159) has been slow for a couple of days -- it just took over five minutes to do a cvs update of HEAD where it usually takes thirty seconds or less. Yeah, I was about to say the same. The master CVS server has been OK (not great, but OK) for the last week or so; but I believe the folks complaining are using anoncvs. Anyone noticing any network issues? I just looked over the server itself, swapping a *little* bit, but vmstat shows the disks aren't busy ... and nothing unusual taking up CPU on the server itself ... anoncvs is on svr4, right? It's been dead slow due to spam getting trapped in a loop because of configuration errors (see other mails with details on what we need to do to fix that). //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Further reduction of bufmgr lock contention
Josh Berkus josh@agliodbs.com writes: BTW, we're going to be testing this patch on Sun Niagara servers. What's the outstanding bug with it? I don't quite follow. It's not acceptable as-is because of the risk of running out of shared memory for hashtable entries. In the existing code, there's a clear upper bound on the number of entries in the block-number-to-buffer hash table, ie, shared_buffers + 1 (the +1 because we acquire the new entry before releasing the old when reassigning a buffer). With multiple hashtables serving subsets of the buffers, the different tables might at different times need different numbers of entries, and that makes it a lot harder to be sure you won't run out of memory. I don't say it's insoluble, but the current patch wasn't even claimed to be safe by its author... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] file-locking and postmaster.pid
On Wednesday 24 May 2006 21:03, korry wrote: I'm sure there's a good reason for having it the way it is, having so many smart knowledgeable people working on this project. Could someone please explain the rationale of the current solution to me? We've ignored Andreas' original question. Why not use a lock to indicate that the postmaster is still running? At first blush, that seems more reliable than checking for a (possibly recycled) process ID. As Tom replied: Portability. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] file-locking and postmaster.pid
On Wednesday 24 May 2006 20:52, Andrej Ricnik-Bay wrote: On 5/24/06, Andreas Joseph Krogh [EMAIL PROTECTED] wrote: My PG is not started with startup-scripts, but with this command: pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start ... and manually after login, ie. not at boot-time. I'd suggest trying to fix your Linux-install instead of mucking about with Postgres, and this really a pgsql-novice question, not a -hackers thing. I'm sorry, can't resist, but this has to be *the* dumbest reply to these sort of questions. What makes you think it *only* happens when linux freezes(btw, I suspect my NVIDIA-driver to be the problem on my laptop, not Linux itself). Still - PG *should* handle that situation too, it's like a power outage. I've been using Linux exclusively since '96 and PG since 6.5, so I don't consider myself a novice in neither. Why PG doesn't use locking *is* definitely a -hackers thing. -- Andreas Joseph Krogh [EMAIL PROTECTED] Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] file-locking and postmaster.pid
I'm sure there's a good reason for having it the way it is, having so many smart knowledgeable people working on this project. Could someone please explain the rationale of the current solution to me? We've ignored Andreas' original question. Why not use a lock to indicate that the postmaster is still running? At first blush, that seems more reliable than checking for a (possibly recycled) process ID. -- Korry
Re: [HACKERS] file-locking and postmaster.pid
On Wednesday 24 May 2006 21:03, korry wrote: I'm sure there's a good reason for having it the way it is, having so many smart knowledgeable people working on this project. Could someone please explain the rationale of the current solution to me? We've ignored Andreas' original question. Why not use a lock to indicate that the postmaster is still running? At first blush, that seems more reliable than checking for a (possibly recycled) process ID. As Tom replied: Portability. Thanks - I missed that part of Tom's message. The only platform (although certainly not a minor issue) that I can think of that would have a portability issue would be Win32. You can't even read a locked byte in Win32. I usually solve that problem by locking a byte past the end of the file (which is portable). Is there some other portability issue that I'm missing? -- Korry
Re: [HACKERS] Improving ALTER TYPE support
John Jawed [EMAIL PROTECTED] writes: I guess I don't understand what one has to do with the other (SRF's returning records and OUT parameters). I always thought they were exclusive, could you elaborate? (BTW, please don't post uselessly HTML-ified mail.) If you write something like create function foo (in p1 int, out r1 int, out r2 text) returns setof record then you've effectively got a function returning an anonymous composite type (here, with one int and one text column). I don't see a case for inventing a separate facility that will pretty much just duplicate this functionality. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] error-free disabling of individual child partition
On Wed, 2006-05-24 at 09:40 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2006-05-24 at 11:17 +0300, Hannu Krosing wrote: Why never set back again ? I'd guess that it should be set back to true when it is not an inherited column anymore, that is when its attinhcount reaches zero. Because you have no record of whether it was created locally or inherited when originally created. And: do you care? Why? The invariant is supposed to be that every attribute has attislocal true or attinhcount 0 (or both). Otherwise it has no justification to exist. I see in the regression database that someone has broken this invariant; it looks like LIKE inheritance is misimplemented. I'm going to insist on a fix for that ;-) I think that the correct behavior for add/drop is: * ADD INHERITS increments attinhcount for every column found to match a column of the parent. Nothing happens to attislocal. * DROP INHERITS decrements attinhcount for every column found to match a column of the parent. Set attislocal true if attinhcount thereby goes to zero. This makes ADD followed by DROP a certain no-op (if attinhcount was zero before the ADD, then attislocal must have been true already). However, DROP followed by ADD is not a no-op since we might wind up with attislocal true in a column that wasn't that way before. This seems like a relatively minor thing though. The alternative would be to delete the child column when it has no definition sources left; which would be self-consistent but I don't think it's the behavior we want for this. Sounds good. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] file-locking and postmaster.pid
Certainly on all platforms there must be *some* locking primitive. We just need to figure out the appropiate parameters to fcntl() or flock() or lockf() on each. Right. The Win32 API for locking seems mighty strange to me. Linux/Unix byte locking is advisory (meaning that one lock can block another lock, but it can't block a read). Win32 locking is mandatory (at least in the most portable form) so a lock blocks a reader. To avoid that problem, youlock a byte that you never intend to read (that is, you lock a byte past the end of the file). Locking past the end-of-file is portable to all Unix/Linux systems that I've seen (that way, you can lock a region of a file before you grow the file). -- Korry
Re: [HACKERS] file-locking and postmaster.pid
Alvaro Herrera wrote: korry wrote: The only platform (although certainly not a minor issue) that I can think of that would have a portability issue would be Win32. You can't even read a locked byte in Win32. I usually solve that problem by locking a byte past the end of the file (which is portable). Certainly on all platforms there must be *some* locking primitive. We just need to figure out the appropiate parameters to fcntl() or flock() or lockf() on each. The Win32 API for locking seems mighty strange to me. We use file locking on Win32 (and on all other platforms) in the buildfarm ... it's done from perl so maybe perl does some magic under the hood. The call looks just the same, and works fine on W32, I believe. It is roughly: use Fcntl qw(:flock); open($lockfile,builder.LCK) || die opening lockfile; exit(0) unless flock($lockfile,LOCK_EX|LOCK_NB); 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] file-locking and postmaster.pid
korry wrote: The Win32 API for locking seems mighty strange to me. Linux/Unix byte locking is advisory (meaning that one lock can block another lock, but it can't block a read). No -- it is advisory meaning that a process that does not try to acquire the lock is not locked out. You can certainly block a file in exclusive mode, using the LOCK_EX flag. (And at least on my Linux system, there is mandatory locking too, using the fcntl() interface). I think the next question is -- how would the lock interface be used? We could acquire an exclusive lock on postmaster start (to make sure no backend is running), then reduce it to a shared lock. Every backend would inherit the shared lock. But the lock exchange is not guaranteed to be atomic so a new postmaster could start just after we acquire the lock and acquire the shared lock. It'd need to be complemented with another lock. Win32 locking is mandatory (at least in the most portable form) so a lock blocks a reader. There is also shared/exclusive locking of a file on Win32. My comment weas more directed at the fact that you have to create some sort of lock handle from a file handle and then lock the lock handle, or something like that. I don't recall the exact details but it was strange (as opposed to just open and then flock). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] file-locking and postmaster.pid
Alvaro Herrera [EMAIL PROTECTED] writes: Certainly on all platforms there must be *some* locking primitive. We just need to figure out the appropiate parameters to fcntl() or flock() or lockf() on each. Quite aside from the hassle factor of needing to deal with N variants of the syscalls, I'm not convinced that it's guaranteed to work. ISTR that for instance NFS file locking is pretty much Alice-in-Wonderland :-( Since the entire point here is to have a guaranteed bulletproof check, locks that work most of the time on most platforms/filesystems aren't gonna be an improvement. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving ALTER TYPE support
Tom, If you write something like create function foo (in p1 int, out r1 int, out r2 text) returns setof record D'oh! I feel like a dork now. I forgot we had this. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] file-locking and postmaster.pid
Andrew Dunstan wrote: We use file locking on Win32 (and on all other platforms) in the buildfarm ... it's done from perl so maybe perl does some magic under the hood. The call looks just the same, and works fine on W32, I believe. It is roughly: use Fcntl qw(:flock); open($lockfile,builder.LCK) || die opening lockfile; exit(0) unless flock($lockfile,LOCK_EX|LOCK_NB); flock on Perl is implemented using platform-dependent system calls. Per the docs, flock FILEHANDLE,OPERATION Calls flock(2), or an emulation of it, on FILEHANDLE. Returns true for success, false on failure. Produces a fatal error if used on a machine that doesn't implement flock(2), fcntl(2) locking, or lockf(3). flock is Perl's portable file locking interface, although it locks only entire files, not records. Note that it may fail! This seems to indicate that some platforms do not provide either locking mechanism. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] file-locking and postmaster.pid
Alvaro Herrera wrote: Note that it may fail! This seems to indicate that some platforms do not provide either locking mechanism. (Which means the whole discussion is a waste of time) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving ALTER TYPE support
Ok, this way works and the proposed way isn't necessary. On 5/24/06, Tom Lane [EMAIL PROTECTED] wrote: John Jawed [EMAIL PROTECTED] writes: I guess I don't understand what one has to do with the other (SRF's returning records and OUT parameters). I always thought they were exclusive, could you elaborate? (BTW, please don't post uselessly HTML-ified mail.) If you write something like create function foo (in p1 int, out r1 int, out r2 text) returns setof record then you've effectively got a function returning an anonymous composite type (here, with one int and one text column). I don't see a case for inventing a separate facility that will pretty much just duplicate this functionality. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] file-locking and postmaster.pid
Alvaro Herrera wrote: Alvaro Herrera wrote: Note that it may fail! This seems to indicate that some platforms do not provide either locking mechanism. (Which means the whole discussion is a waste of time) Umm, no, I don't think so. It will block instead of failing unless you request a non blocking call. Failure means someone else holds the lock. But what Tom says about NFS is probably true, and a good enough reason not to trust locking in general for this purpose, I think cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Compression and on-disk sorting
Jim C. Nasby wrote: Finally completed testing of a dataset that doesn't fit in memory with compression enabled. Results are at http://jim.nasby.net/misc/pgsqlcompression . Summary: work_memcompressed not compressed gain in-memory 2 400.1 797.7 49.8% in-memory 2000371.4 805.7 53.9% not in-memory 2 853717436 51.0% not in-memory 2000815217820 54.3% I find it very interesting that the gains are identical even when the tapes should fit in memory. My guess is that for some reason the OS is flushing those to disk anyway. In fact, watching gstat during a run, I do see write activity hitting the drives. So if there was some way to tune that behavior, the in-memory case would probably be much, much faster. Anyone know FreeBSD well enough to suggest how to change this? Anyone want to test on linux and see if the results are the same? This could indicate that it might be advantageous to attempt an in-memory sort with compressed data before spilling that compressed data to disk... I can test it on linux just let me know what you need. J As for CPU utilization, it was ~33% with compression and ~13% without. That tells me that CPU could become a factor if everything was truely in memory (including the table we were reading from), but if that's the case there's a good chance that we wouldn't even be switching to an on-disk sort. If everything isn't in memory then you're likely to be IO bound anyway... -- === 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] file-locking and postmaster.pid
On Wed, 2006-05-24 at 16:34 -0400, Alvaro Herrera wrote: korry wrote: The Win32 API for locking seems mighty strange to me. Linux/Unix byte locking is advisory (meaning that one lock can block another lock, but it can't block a read). No -- it is advisory meaning that a process that does not try to acquire the lock is not locked out. Right, that's why I said can block instead of will block. An advisory lock will only block another locker, not another reader (except in Win32). You can certainly block a file in exclusive mode, using the LOCK_EX flag. (And at least on my Linux system, there is mandatory locking too, using the fcntl() interface). My fault - I'm not really talking about file locking, I'm talking about byte-range locking (via lockf() and family). I don't believe that you can use byte-range locking to block read-access to a file, you can only use byte-range locking to block other locks. A simple exclusive lock on the first byte past the end of the file will do. I think the next question is -- how would the lock interface be used? We could acquire an exclusive lock on postmaster start (to make sure no backend is running), then reduce it to a shared lock. Every backend would inherit the shared lock. But the lock exchange is not guaranteed to be atomic so a new postmaster could start just after we acquire the lock and acquire the shared lock. It'd need to be complemented with another lock. You never need to reduce it to a shared lock. On postmaster startup, try to lock the sentinel byte (one byte past the end-of-file). If you can lock it, you know that no other postmaster has that byte locked. If you can't lock it, another postmaster is running. It is an atomic operation. However, Tom may be correct about NFS locking, but I guess I'm surprised that anyone would care :-) Win32 locking is mandatory (at least in the most portable form) so a lock blocks a reader. There is also shared/exclusive locking of a file on Win32. Yes, but Win32 shared locking only works on NTFS-type file systems. And you don't need shared locking anyway. -- Korry
Re: [HACKERS] file-locking and postmaster.pid
Alvaro Herrera [EMAIL PROTECTED] writes: Certainly on all platforms there must be *some* locking primitive. We just need to figure out the appropiate parameters to fcntl() or flock() or lockf() on each. I use lockf() (not fcntl() or flock()) on every platform other than Win32. Of course, I may not run on every system that PostgreSQL supports. Quite aside from the hassle factor of needing to deal with N variants of the syscalls, I'm not convinced that it's guaranteed to work. ISTR that for instance NFS file locking is pretty much Alice-in-Wonderland :-( Since the entire point here is to have a guaranteed bulletproof check, locks that work most of the time on most platforms/filesystems aren't gonna be an improvement. NFS file locking may certainly be problematic. I don't know about NFS byte-range locking. What we currently have in place is not bulletproof. I think holding a byte-range lock in addition to the is there some process with the right pid? check might be a little more bullet resistant :-) -- Korry
Re: [HACKERS] file-locking and postmaster.pid
Andrew Dunstan wrote: Alvaro Herrera wrote: Alvaro Herrera wrote: Note that it may fail! This seems to indicate that some platforms do not provide either locking mechanism. (Which means the whole discussion is a waste of time) Umm, no, I don't think so. It will block instead of failing unless you request a non blocking call. Failure means someone else holds the lock. I removed the part of the manual I had written which said that it will raise an error if the platform it's running doesn't have any locking primitive. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] file-locking and postmaster.pid
korry [EMAIL PROTECTED] writes: However, Tom may be correct about NFS locking, but I guess I'm surprised that anyone would care :-) Whether we think it's a real good idea or not, *plenty* of people run databases across NFS. We can't blow off that set of users. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] file-locking and postmaster.pid
korry wrote: I think the next question is -- how would the lock interface be used? We could acquire an exclusive lock on postmaster start (to make sure no backend is running), then reduce it to a shared lock. Every backend would inherit the shared lock. But the lock exchange is not guaranteed to be atomic so a new postmaster could start just after we acquire the lock and acquire the shared lock. It'd need to be complemented with another lock. You never need to reduce it to a shared lock. On postmaster startup, try to lock the sentinel byte (one byte past the end-of-file). If you can lock it, you know that no other postmaster has that byte locked. If you can't lock it, another postmaster is running. It is an atomic operation. This doesn't work if the postmaster dies but a backend continues to run, which is arguably the most important case we need to protect against. However, Tom may be correct about NFS locking, but I guess I'm surprised that anyone would care :-) Quite a lot of people run NFS-mounted data directories ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Compression and on-disk sorting
On Wed, May 24, 2006 at 02:20:43PM -0700, Joshua D. Drake wrote: Jim C. Nasby wrote: Finally completed testing of a dataset that doesn't fit in memory with compression enabled. Results are at http://jim.nasby.net/misc/pgsqlcompression . Summary: work_memcompressed not compressed gain in-memory 2 400.1 797.7 49.8% in-memory 2000371.4 805.7 53.9% not in-memory 2 853717436 51.0% not in-memory 2000815217820 54.3% I find it very interesting that the gains are identical even when the tapes should fit in memory. My guess is that for some reason the OS is flushing those to disk anyway. In fact, watching gstat during a run, I do see write activity hitting the drives. So if there was some way to tune that behavior, the in-memory case would probably be much, much faster. Anyone know FreeBSD well enough to suggest how to change this? Anyone want to test on linux and see if the results are the same? This could indicate that it might be advantageous to attempt an in-memory sort with compressed data before spilling that compressed data to disk... I can test it on linux just let me know what you need. Actually, after talking to Larry he mentioned that it'd be worth checking to see if we're doing something like opening the files in O_DIRECT, which I haven't had a chance to do. Might be worth looking at that before running more tests. Anyway, I've posted the patch now as well, and compress_sort.txt has the commands I was running. Those are just against a plain pgbench database that's been freshly initialized (ie: no dead tuples). I just created two install directories from a checkout of HEAD via --prefix=, one with the patch and one without. Both hit the same $PGDATA. I've posted the postgresql.conf as well. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
sibel karaasma schrieb: Hi I'm a new postgresql user. I wrote ACO (ant colony optimazition) and want to replace it with GEQO in postres/src/backend/optimizer but I don't know how to compile and run the source code :( I installed postgresql-8.1.3 and cygwin but I can not use them to compile the source code. I want to compare GEQO and ACO optimizers performance using a small database Can you help me??? download the src package via cygwin.com/setup.exe and check out the buildscript to see the used configure parameters and get all the dependencies right. -- Reini ---(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] compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
On Wed, May 24, 2006 at 01:46:19AM -0700, sibel karaasma wrote: Hi I'm a new postgresql user. I wrote ACO (ant colony optimazition) and want to replace it with GEQO in postres/src/backend/optimizer but I don't know how to compile and run the source code :( I installed postgresql-8.1.3 and cygwin but I can not use them to compile the source code. I want to compare GEQO and ACO optimizers performance using a small database mingw is now the approved build method for windows, and I think there's instructions on how to build PostgreSQL with it somewhere, though I can't seem to find them now. Though to be honest, you'd probably find it easier doing development on any kind of a unix box, including OS X. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] file-locking and postmaster.pid
You never need to reduce it to a shared lock. On postmaster startup, try to lock the sentinel byte (one byte past the end-of-file). If you can lock it, you know that no other postmaster has that byte locked. If you can't lock it, another postmaster is running. It is an atomic operation. This doesn't work if the postmaster dies but a backend continues to run, which is arguably the most important case we need to protect against. I may be confused here, but I don't see the problem - byte-range locks are not inherited across a fork. A backend would never hold the lock, a backend would never even look for the lock. However, Tom may be correct about NFS locking, but I guess I'm surprised that anyone would care :-) Quite a lot of people run NFS-mounted data directories ... I'm happy to take your word for that, and I agree that if NFS is important and locking is brain-dead on NFS, then relying solely on a lock is unacceptable. -- Korry
Re: [HACKERS] file-locking and postmaster.pid
korry [EMAIL PROTECTED] writes: Well, it fails in the safe direction: the postmaster may occasionally refuse to start when it should, but it won't ever start when it should not. It appears to me that anything relying on file locking will tend to fail in the other direction, and that's not acceptable IMHO. I was suggesting that we keep the current check in place too - if the lock exists, another postmaster must be running, if the lock doesn't exist, check the pid. But then you've not accomplished anything. The complaints about the pid-based mechanism are about false positives, not false negatives. Adding an independent check won't eliminate the false positives. How about a semaphore with a SEM_UNDO? That's guaranteed atomic (or it better be :-), the kernel automatically cleans up after a failure, if the mechanism fails, it fails in the safe direction (the kernel may not have cleaned up the semaphore before a new postmaster starts). And, I think it would be reasonably portable - I haven't carefully eyeballed the Win32 semaphore code so I don't know if it supports SEM_UNDO. We already have two platforms that don't use the SysV semaphore interface, and even on ones that have it, I wouldn't want to assume they all support SEM_UNDO. But aside from any portability issues, ISTM this would have its own failure modes. In particular you still have to rely on a pid-file (only now it's holding a semaphore ID not a PID), and there's still a bit of a leap of faith required to get from the observation that somebody is holding a lock on semaphore X to the conclusion that that somebody is a conflicting postmaster. It doesn't look to me like this is any better than the PID solution, really, as far as false positives go. As for false negatives: ipcrm. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] file-locking and postmaster.pid
korry wrote: You never need to reduce it to a shared lock. On postmaster startup, try to lock the sentinel byte (one byte past the end-of-file). If you can lock it, you know that no other postmaster has that byte locked. If you can't lock it, another postmaster is running. It is an atomic operation. This doesn't work if the postmaster dies but a backend continues to run, which is arguably the most important case we need to protect against. I may be confused here, but I don't see the problem - byte-range locks are not inherited across a fork. A backend would never hold the lock, a backend would never even look for the lock. Well, you are wrong here. We _want_ every backend to hold a shared lock. We need to stop a postmaster from starting if there is a backend running that was started by a no-longer-running postmaster. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] file-locking and postmaster.pid
We already have two platforms that don't use the SysV semaphore interface, and even on ones that have it, I wouldn't want to assume they all support SEM_UNDO. Which platforms, just out of curiousity? I assume that Win32 is one of them. But aside from any portability issues, ISTM this would have its own failure modes. In particular you still have to rely on a pid-file (only now it's holding a semaphore ID not a PID) You've lost me... why would you store the semid and not the pid? I was thinking that the semid might be a postgresql.conf thingie. and there's still a bit of a leap of faith required to get from the observation that somebody is holding a lock on semaphore X to the conclusion that that somebody is a conflicting postmaster. Isn't that sort of like saying that if a postmaster.pid file exists, it must have been written by a postmaster? Pick a semaphore id and dedicate it to postmaster exclusion. It doesn't look to me like this is any better than the PID solution, really, as far as false positives go. As long as the kernel cleans up SEM_UNDO semaphores, I guess I don't see have you would have a false positive. Oh, I guess I should say that is you use a SEM_UNDO semaphore, you don't need the pid check anymore. And, no worry about NFS. As for false negatives: ipcrm. Yes, that's a problem, but I think it's the same as rm postmaster.pid, isn't it?
Re: [HACKERS] file-locking and postmaster.pid
korry [EMAIL PROTECTED] writes: Isn't that sort of like saying that if a postmaster.pid file exists, it must have been written by a postmaster? Pick a semaphore id and dedicate it to postmaster exclusion. That's not workable, unless you want to assume that nothing on the system except Postgres uses SysV semaphores. Otherwise something else could randomly gobble up the semid you want to use. I don't care very much for requiring a distinct semid to be hand-specified for each postmaster on a machine, either. At least for my use, that would be a grade-A PITA: I normally have several postmasters of different vintages running on the same development machine, and having to configure each one with its own semid is an extra step I'd rather not deal with. As long as the kernel cleans up SEM_UNDO semaphores, I guess I don't see have you would have a false positive. My point was that you couldn't reliably tell a postmaster interested in a different data directory from a postmaster interested in your own data directory. Even with a configured semid, I don't see that that's real reliable. I know the first thing I'd do is fix my postmaster start scripts to specify semid on the command line rather than requiring it to be in the conf file, and as soon as I do that, the connection to the data directory is gone :-( --- now my security is utterly dependent on not screwing up by launching a postmaster with the wrong semid for the data directory it's pointed at. The only scenario where the PID-based solution is at serious risk of false positives is where there are multiple postmasters on the same machine, so unless you've got a bulletproof answer for this case, you haven't made an improvement over what we've got. Anyway the real problem here is that neither PIDs nor semids are strongly wired to a particular data directory, which is the thing you're really trying to protect. File locks would really be much nicer all around, if we could trust them, because they *would* be directly connected to a data directory. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
Richard [EMAIL PROTECTED] writes: LOG: 0: could not read symbolic link postmaster LOCATION: resolve_symlinks, exec.c:338 FATAL: XX000: /usr/local/pgsql/bin/postmaster: could not locate my own executable path LOCATION: PostmasterMain, postmaster.c:435 known? Nope, and we do have OS X machines in the buildfarm, so I think we'd have noticed if there were a generic problem. I don't recall any recent changes in that area of the code, either. Have you built PG successfully before on this same machine? If so, what was the last version that worked for you? Is there anything out of the ordinary about the filesystem or directory path you installed PG on? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] timezones to own config file
I'd like to know what exactly is to be done for this TODO-item o Allow customization of the known set of TZ names (generalize the present australian_timezones hack) The most recent mail in the archives about this subject seems to be: http://archives.postgresql.org/pgsql-general/2006-04/msg00966.php So we'd need a file specifying time zone abbreviations and their offsets, so for example a file containing lines like: XST -3.25 # comment here for Xtended Standard Time The default file would contain the now hardwired values from datetime.c. Postmaster would parse the file at least on startup or during SIGHUP as well and (after a few checks) merge the list with the remaining values from datetime.c (keeping them ordered alphabetically). I wondered especially if there was a need to associate the abbreviation in some way to the entries from the zic database but datetime.c seems to just add or subtract the offset and save the resulting time without further time zone information. Thanks, Joachim ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 hi tom, LOG: 0: could not read symbolic link postmaster LOCATION: resolve_symlinks, exec.c:338 FATAL: XX000: /usr/local/pgsql/bin/postmaster: could not locate my own executable path LOCATION: PostmasterMain, postmaster.c:435 known? Nope, and we do have OS X machines in the buildfarm, so I think we'd have noticed if there were a generic problem. I don't recall any recent changes in that area of the code, either. Have you built PG successfully before on this same machine? repeatedly. on this machine, and a variety of others similarly configured ... If so, what was the last version that worked for you? v8.1.3 on all boxes. i can currently successfully (re)build 813 on this box now, with no such problems ... Is there anything out of the ordinary about the filesystem or directory path you installed PG on? 'out of the ordinary' ... well, not from my perspective, or from that, apparently, of v813. cheers, richard - -- /\ \ / ASCII Ribbon Campaign X against HTML email, vCards / \ micro$oft attachments [GPG] OpenMacNews at gmail dot com fingerprint: 50C9 1C46 2F8F DE42 2EDB D460 95F7 DDBD 3671 08C6 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iEYEAREDAAYFAkR0+BQACgkQlffdvTZxCMad1gCfXvnR8nG8lZumMHK0HLB3b6w4 VXgAoIa/dCW06WyavJMTgjd376h51Wyr =fbTC -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
Richard wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 hi all, i've working built-from-source v813 installs on OSX 10.4.6. staring with a clean 814 src tree, a similarly-config'd build completes without error: % postmaster --version postmaster (PostgreSQL) 8.1.4 but, on executable launch, if fails w/ @ postgresql.log: LOG: 0: could not read symbolic link postmaster LOCATION: resolve_symlinks, exec.c:338 FATAL: XX000: /usr/local/pgsql/bin/postmaster: could not locate my own executable path LOCATION: PostmasterMain, postmaster.c:435 known? suggestion? Well it looks like the symlink to /usr/local/pgsql/bin/postmaster is broken. Joshua D. Drake cheers, richard - -- /\ \ / ASCII Ribbon Campaign X against HTML email, vCards / \ micro$oft attachments [GPG] OpenMacNews at gmail dot com fingerprint: 50C9 1C46 2F8F DE42 2EDB D460 95F7 DDBD 3671 08C6 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iEYEAREDAAYFAkR08fkACgkQlffdvTZxCMaukACcC29AL0JRtQwW1DjDlNVlWOko /zwAoKp96K9LSsYZSZmlWs+QIKWrH6PI =Wc17 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 tom, v8.1.3 on all boxes. i can currently successfully (re)build 813 on this box now, with no such problems ... Curious. yup. I just rebuilt/tested CVS HEAD successfully on my own 10.4.6 laptop ... good to know ... I'll try REL8_1_STABLE next, but I'll be *really* surprised if it fails. There's got to be something odd about your configuration. Look at PATH and so forth. checking 'by the numbers' ... the problem exists on four different boxes admittedly, they're all similarly configured ... fwiw, v813 is OK on all boxes. Look at the postmaster - postgres symlink; is it where it's supposed to be? seems to be: /usr/local/pgsql/bin ls -al postmaster lrwxrwx--- 1 root wheel 8 2006-05-24 07:48 postmaster - postgres i'll let you know if i find anything; please do the same ... richard - -- /\ \ / ASCII Ribbon Campaign X against HTML email, vCards / \ micro$oft attachments [GPG] OpenMacNews at gmail dot com fingerprint: 50C9 1C46 2F8F DE42 2EDB D460 95F7 DDBD 3671 08C6 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iEYEAREDAAYFAkR0/kUACgkQlffdvTZxCMaQ3gCfYnxf5D8dSmiulGFjy7UnrvjB HPwAn3pHIE0wg2bZkxk1g54R9b7Kp9Y/ =6nYL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
Richard [EMAIL PROTECTED] writes: Look at the postmaster - postgres symlink; is it where it's supposed to be? seems to be: /usr/local/pgsql/bin ls -al postmaster lrwxrwx--- 1 root wheel 8 2006-05-24 07:48 postmaster - postgres Hm, is it significant that that symlink is set for no access by world? I don't remember if Darwin enforces access bits on symlinks, but check to see if your 8.1.3 installation has identical privileges for the symlink. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] v814 + OSX10.4.6 builds OK, but fails launch ...
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 /usr/local/pgsql/bin ls -al postmaster lrwxrwx--- 1 root wheel 8 2006-05-24 07:48 postmaster - postgres Hm, is it significant that that symlink is set for no access by world? hmmm, too. I don't remember if Darwin enforces access bits on symlinks, but check to see if your 8.1.3 installation has identical privileges for the symlink. will do ... gonna rebuild clean. been monkeying around too much tracking this down. fwiw, make check == All 98 tests passed. == grumble ... - -- /\ \ / ASCII Ribbon Campaign X against HTML email, vCards / \ micro$oft attachments [GPG] OpenMacNews at gmail dot com fingerprint: 50C9 1C46 2F8F DE42 2EDB D460 95F7 DDBD 3671 08C6 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (Darwin) iEYEAREDAAYFAkR1AIAACgkQlffdvTZxCMa6QwCgsqUv4qTWncirjZ1JmJlcx2Eu IdEAn2R9jFMj4yD+PVegf4bCOuu2zcuJ =kRQS -END PGP SIGNATURE- ---(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] timezones to own config file
Joachim Wieland [EMAIL PROTECTED] writes:
I'd like to know what exactly is to be done for this TODO-item
o Allow customization of the known set of TZ names (generalize the
present australian_timezones hack)
Well, part of the TODO is to figure out exactly what to do ;-)
So we'd need a file specifying time zone abbreviations and their offsets, so
for example a file containing lines like:
XST -3.25 # comment here for Xtended Standard Time
That's the basic idea. It strikes me that there are going to be certain
groups of settings that go together, ie, folks in North America are
going to know what they want, the Aussies will know what they want but
it'll be different, etc. I was toying with the idea that what we should
have is a postgresql.conf setting that points at a particular file
containing TZ names. Then you could imagine that the standard distro
installs
{prefix}/share/tznames/americas
{prefix}/share/tznames/australia
{prefix}/share/tznames/fareast
and in postgresql.conf you put
tznames = australia
or if you live somewhere well and truly off the beaten track, you make
your own tznames file, put it in that directory, and specify it in
postgresql.conf. But 99% of users should not have to do that --- they
should be able to select an already-configured tzname set and have it
Do What They Want.
But that's just one idea. Feel free to propose something else.
I wondered especially if there was a need to associate the abbreviation in
some way to the entries from the zic database but datetime.c seems to just
add or subtract the offset and save the resulting time without further time
zone information.
The zic database doesn't seem to have a problem with using the same
abbreviations to mean many different things. We could look to it for
information, or maybe even use its classification of timezone groups,
but I don't think it can solve the problem for us.
I think you may be thinking of yet a separate TODO item, which is to
be able to use the zic timezone names in timestamptz input, viz
'2006-05-24 21:11 Americas/New_York'::timestamptz
But names like 'IST' or 'CDT' are not zic timezone names, they just
represent specific offsets from UTC.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Gborg and pgfoundry
I am asking again: what is the timetable for merging gborg and pgfoundry, and if not, can we set a date to shut down to force the move. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Hi Sibel, Here's the mail that I posted about 10 days ago about compiling and debugging postgres on windows. I have used msys/mingw toolkit and it is the recommended (by pg community) toolkit to compile postgres on windows. http://archives.postgresql.org/pgsql-hackers/2006-05/msg00396.php Hope it helps. Do let me know if you face any problems. Regards, Gurjeet. On 5/25/06, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, May 24, 2006 at 01:46:19AM -0700, sibel karaasma wrote: Hi I'm a new postgresql user. I wrote ACO (ant colony optimazition) and want to replace it with GEQO in postres/src/backend/optimizer but I don't know how to compile and run the source code :( I installed postgresql-8.1.3 and cygwin but I can not use them to compile the source code. I want to compare GEQO and ACO optimizers performance using a small database mingw is now the approved build method for windows, and I think there's instructions on how to build PostgreSQL with it somewhere, though I can't seem to find them now. Though to be honest, you'd probably find it easier doing development on any kind of a unix box, including OS X. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Also, I would recommend uninstalling cygwin before you install mingw, because if the mingw doesn't behave properly (gcc won't compile files etc...), you could be sure that there definitely is a conflict between the cygwin and mingw. If you don't wish to uninstall cygwin, at least rename the folder to someting else (cygwin.before.mingw) so that you don't accidentally use cygwin's binaries. Regards, Gurjeet. On 5/25/06, Gurjeet Singh [EMAIL PROTECTED] wrote: Hi Sibel, Here's the mail that I posted about 10 days ago about compiling and debugging postgres on windows. I have used msys/mingw toolkit and it is the recommended (by pg community) toolkit to compile postgres on windows. http://archives.postgresql.org/pgsql-hackers/2006-05/msg00396.php Hope it helps. Do let me know if you face any problems. Regards, Gurjeet. On 5/25/06, Jim C. Nasby [EMAIL PROTECTED] wrote: On Wed, May 24, 2006 at 01:46:19AM -0700, sibel karaasma wrote: Hi I'm a new postgresql user. I wrote ACO (ant colony optimazition) and want to replace it with GEQO in postres/src/backend/optimizer but I don't know how to compile and run the source code :( I installed postgresql-8.1.3 and cygwin but I can not use them to compile the source code. I want to compare GEQO and ACO optimizers performance using a small database mingw is now the approved build method for windows, and I think there's instructions on how to build PostgreSQL with it somewhere, though I can't seem to find them now. Though to be honest, you'd probably find it easier doing development on any kind of a unix box, including OS X. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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
