Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Josh Berkus napsal(a): Zdenek, Hmmm ... we're not using the -fast option for the standard PostgreSQL packages. Where did you start using it? Yes, I know. The -fast option generates architecture depending code and it is not possible use in common packages. I found out this option when I analyzed BUG #2651. I tried regression test and it's fail. I found that same problem was described with Match Grun few month ago and the -fast option is mentioned in the FAQ.Solaris for performance tunning. That is all. regards Zdenek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Bad bug in fopen() wrapper code
What's bugging me is that 0 and O_EXCL give the same answer, and O_TRUNC and O_TRUNC | O_EXCL give the same answer, This is ok, as (iirc) O_EXCL only has effect in the presence of O_CREAT. snip more explanation Thanks, Claudio! After looking at the code some more, and actually reading up on the specs a bit more, it certainly does look like it's safe. So I don't think we need to do anything about that. Now, I still twist my head around the lines: if ((fd = _open_osfhandle((long) h, fileFlags O_APPEND)) 0 || (fileFlags (O_TEXT | O_BINARY) (_setmode(fd, fileFlags (O_TEXT | O_BINARY)) 0))) With the _setmode() call deep in the if statement... I would suggest we split that up into a couple of lines to make it more readable - I'm sure all compilers will easily optimise it into the same code anyway. Reasonable? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Block B-Tree concept
Jim C. Nasby wrote: Do I understand that to mean that you can no longer lazy vacuum a functional index? With the normal B-trees we have now, there's no problem vacuuming a functional index. But it would be a problem with the Block B-tree, because the proposed way of vacuuming a Block B-tree involves re-evaluating index expressions. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Block B-Tree concept
Jim C. Nasby wrote: Couldn't vacuum just eliminate tuples marked dead? Heck, don't we do that anyway right now? You mean _index_ tuples marked dead? Sure, no problem there. Granted, you'd want to periodically ensure that you scan the entire index, but that shouldn't be horribly hard to set up. Well, it seems to be. A vacuum can't evaluate index expressions because it's not in a real transaction. The DBA could set up a cron job to do SELECT * FROM foo WHERE bar 0 etc. with enable_seqscan=false? That would work, but we can't depend on an additional administrative task like. And we might as well just disable the optimization that's causing us problems. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Constant changes (Re-Build)
luis garcia wrote: Hi I'm a student from Valencia-Venezuela and I'm working with some other friends to make PostgreSQL allows the definition of Temporal Databases and their respective Selection, Insertion and some other functions needed to treat this paradigm (all based in TSQL2 Query Language). That's interesting. May I suggest that you take a look at a book called Temporal Data the Relational Model by C.J. Date, Hugh Darwin and Nikos Lorentzos (http://www.amazon.com/Temporal-Relational-Kaufmann-Management-Systems/dp/1558608559). It describes the best approach I've seen this far to dealing with temporal data. Right now we are working directly on the source code and making different changes during the day, so I'd like to ask you which is the better choice for re-building (I'm not sure if that is the right term) only the code files that I just have changed. I'm working on a Slow PC with not to many recourse, so every time I make (-configure/-make/-make-install/) i lose like 30 minutes of work, and I have been thinking in some other way to only re-configure the files I've recently changed. Well, you don't need to run configure every time you want to build. If you just run make, it will compile just the changes. I'd suggest running the configure with the --enable-depend option, so it picks up changes in header files better. Also take a look at ccache (http://ccache.samba.org/). And if you have more PCs to spare, you might want to set up distcc. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] jar in repository
Hi, Strk, strk wrote: Markus, I noticed we have a .jar file in the repository. Why is that ? Can't we build it from sources with free software tools ? It was a mistake when chekcing in the EJB3 code. It's just a copy of the normal postgis.jar that's needed to compile the EJB3 code, it was part of the archive file when I unpacked, and I forgot to ignore it when checking in. It's removed now, together with a clarification in the README. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Developer's Wiki
Dave Page wrote: I have now moved the wiki installation to: http://developer.postgresql.org/ BTW: I am wondering if there is an RSS feed of the changes? On my wiki I have an RSS feed for every page, subwiki (aka area) and the entire wiki people can subscribe to: http://oss.backendmedia.com/rss.php?area=PHPTODOpage=HomePage http://oss.backendmedia.com/rss.php?area=PHPTODO http://oss.backendmedia.com/rss.php regards, Lukas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Developer's Wiki
On Wed, 27 Sep 2006, Lukas Kahwe Smith wrote: Dave Page wrote: I have now moved the wiki installation to: http://developer.postgresql.org/ BTW: I am wondering if there is an RSS feed of the changes? On my wiki I have an RSS feed for every page, subwiki (aka area) and the entire wiki people can subscribe to: http://oss.backendmedia.com/rss.php?area=PHPTODOpage=HomePage http://oss.backendmedia.com/rss.php?area=PHPTODO http://oss.backendmedia.com/rss.php I only really know of the entire wiki one, but that's the only one I have ever wanted to do. I think it may be able to limit to namespaces, but I am not sure about that. http://developer.postgresql.org/index.php?title=Special:Recentchangesfeed=rss There are a bunch of knobs on the Special:Recentchanges page which could apply also to the rss version, but I have never tried it and they may not, I don't know. regards, Lukas -- Besides the device, the box should contain: * Eight little rectangular snippets of paper that say WARNING * A plastic packet containing four 5/17 inch pilfer grommets and two club-ended 6/93 inch boxcar prawns. YOU WILL NEED TO SUPPLY: a matrix wrench and 60,000 feet of tram cable. IF ANYTHING IS DAMAGED OR MISSING: You IMMEDIATELY should turn to your spouse and say: Margaret, you know why this country can't make a car that can get all the way through the drive-through at Burger King without a major transmission overhaul? Because nobody cares, that's why. WARNING: This is assuming your spouse's name is Margaret. -- Dave Barry, Read This First! ---(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] Developer's Wiki
I have now moved the wiki installation to: http://developer.postgresql.org/ BTW: I am wondering if there is an RSS feed of the changes? There is. http://developer.postgresql.org/index.php?title=Special:Recentchangesfe ed=rss //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Bad bug in fopen() wrapper code
Magnus Hagander writes: Now, I still twist my head around the lines: if ((fd = _open_osfhandle((long) h, fileFlags O_APPEND)) 0 || (fileFlags (O_TEXT | O_BINARY) (_setmode(fd, fileFlags (O_TEXT | O_BINARY)) 0))) With the _setmode() call deep in the if statement... I would suggest we split that up into a couple of lines to make it more readable - I'm sure all compilers will easily optimise it into the same code anyway. Reasonable? I agree it would be clearer if split up. Without having studied it closely, it might also highlight a bug on failure of the second clause -- if the _setmode fails, shouldn't _close be called instead of CloseHandle, and -1 returned? (CloseHandle would still be called on failure of the _open_osfhandle, obviously) Cheers, Claudio ---(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
Ignore that mail (was: [HACKERS] jar in repository)
Hi, Markus Schaber wrote: [something about a postgis.jar] Please ignore that mail, it got to the wrong list. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas wrote: Jim C. Nasby wrote: Couldn't vacuum just eliminate tuples marked dead? Heck, don't we do that anyway right now? You mean _index_ tuples marked dead? Sure, no problem there. Granted, you'd want to periodically ensure that you scan the entire index, but that shouldn't be horribly hard to set up. Well, it seems to be. A vacuum can't evaluate index expressions because it's not in a real transaction. The DBA could set up a cron job to do SELECT * FROM foo WHERE bar 0 etc. with enable_seqscan=false? That would work, but we can't depend on an additional administrative task like. And we might as well just disable the optimization that's causing us problems. Why can't the C code just do a full index scan that touches the heap, sets those expired bits, and then do a vacuum? My point is that the bits can be set outside the normal vacuum process, so you don't have to be doing heap lookups from the index inside vacuum. Assuming the heap is mostly in index order, the full index scan shouldn't take much longer than a heap scan, and if the heap doesn't match index order, a block index shouldn't be used anyway. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Block B-Tree concept
Bruce Momjian wrote: Heikki Linnakangas wrote: Jim C. Nasby wrote: Granted, you'd want to periodically ensure that you scan the entire index, but that shouldn't be horribly hard to set up. Well, it seems to be. A vacuum can't evaluate index expressions because it's not in a real transaction. The DBA could set up a cron job to do SELECT * FROM foo WHERE bar 0 etc. with enable_seqscan=false? That would work, but we can't depend on an additional administrative task like. And we might as well just disable the optimization that's causing us problems. Why can't the C code just do a full index scan that touches the heap, sets those expired bits, and then do a vacuum? My point is that the bits can be set outside the normal vacuum process, so you don't have to be doing heap lookups from the index inside vacuum. The point of the optimization that's causing problems was to reduce the effect of long-running vacuum transactions. If we're going to have another long running transaction instead, we're back to square one. AFAICS, we could disable the optimization and use a full-blown transaction when vacuuming a table with a functional block index. Granted, that's annoying, but not a show-stopper I think. Assuming the heap is mostly in index order, the full index scan shouldn't take much longer than a heap scan, and if the heap doesn't match index order, a block index shouldn't be used anyway. It introduces one more full heap scan for each block index on a table. That's expensive. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Cross-table statistics idea
On Tue, 2006-09-26 at 21:27 -0500, Jim C. Nasby wrote: Since I don't recall any ideas ever having been thrown out on how to do this... ISTM that we could gain additional insight on how many rows would likely result from a join One thing we can do is to use cross-column relationships to improve the estimation of Ndistinct. If we have a table Order_line (PK orderId, lineNum) If we look at lineNum and see it has on average 10 values we can then use this information to compute that Ndistinct should be -0.1, i.e. the number of values is proportional to the number of rows with a factor of 10. Right now if there are more than 10 lineNums per orderId on average then we never decide that orderId is a scalable statistic. I propose adding a final step to ANALYZE that applies a cross-column rule after all columns have been analysed. If all except one column of a PK have very low Ndistinct we can use that to calculate a minimum number of Ndistinct for the column with a high number of values. If that minimum number is less than the Ndistinct estimate in isolation, then we overlay the new value. This is desirable because the estimation of Ndistinct is very sensitive to the number of matching rows in the sample, so Ndistinct estimates are usually very poor for large Ndistinct. The estimates for low Ndistinct are much better, so we can use them with a lower standard error to correct the in-isolation estimate of other columns. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Faster StrNCpy
Tom, Let us know when you've added strlcpy () and we'll be happy to run some tests on the new code. David From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Tue 9/26/2006 7:25 PM To: josh@agliodbs.com Cc: pgsql-hackers@postgresql.org; Neil Conway; Martijn van Oosterhout Subject: Re: [HACKERS] Faster StrNCpy Josh Berkus josh@agliodbs.com writes: What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. Immediately? Presumably you mean for 8.3? No, I mean now. This is a performance bug and it's still open season on bugs. If we were close to having a release-candidate version, I'd hold off, but the above proposal seems sufficiently low-risk for the current stage of the cycle. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Please to technical check of upcoming release
http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. Hi :) I still see Theo Scholossenagle there. I realize the quote is not ready anyway, but why not fix the name in the mean time: it should be Theo Schlossnagle. Bye, Chris. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Faster StrNCpy
Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. Immediately? Presumably you mean for 8.3? No, I mean now. This is a performance bug and it's still open season on bugs. If we were close to having a release-candidate version, I'd hold off, but the above proposal seems sufficiently low-risk for the current stage of the cycle. What are the other hotspots? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Andrew Dunstan napsal(a): Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? That's a pretty good way to guarantee that you'll break the datetime code. ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours Doesn't this look odd regardless of what bad results come back from the FP library? The problem was generated, because -fast option was set only for the compiler and not for the linker. Linker takes wrong version of libraries. If -fast is set for both then horology test is OK, but question was if float optimalization should generate some problems. regards, 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
Re: [HACKERS] Faster StrNCpy
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. What are the other hotspots? The ones I can think of offhand are set_ps_display and use of strncpy as a HashCopyFunc. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Zdenek Kotala [EMAIL PROTECTED] writes: The problem was generated, because -fast option was set only for the compiler and not for the linker. Linker takes wrong version of libraries. If -fast is set for both then horology test is OK, but question was if float optimalization should generate some problems. So FAQ_Solaris needs to tell people to put -fast in both CFLAGS and LDFLAGS? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Buildfarm alarms
I wrote:
Tom Lane wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
It could certainly be done. In general, I have generally taken the view
that owners have the responsibility for monitoring their own machines.
Sure, but providing them tools to do that seems within buildfarm's
purview.
For some types of failure, the buildfarm script could make a local
notification without bothering the server --- but a timeout on the
server side would cover a wider variety of failures, including this
machine is dead and ought to be removed from the farm.
Nothing gets removed. If a machine does not report on a branch for 30 days
it drops off the dashboard, but apart from that it is a retained historic
aretfact. This buildup in history has been gradually slowing down the
dashboard, in fact, but Ian Barwick tells me that he has rewritten my
lousy SQL to make it fast again, so we'll soon get that working better.
Anyway, I think we can do something fairly simply for these alarms. We'll
just have a special stanza in the config file, and a cron job that checks,
say, once a day, to see if we have exceeded the alarm period on any
machine/branch combination.
OK, I have a gadget to do this in place.
It looks at the config of the last build registered on each branch for a
stanza called 'alerts' that would look like this:
alerts = {
HEAD = { alert_after = 24, alert_every = 48 },
REL8_1_STABLE = { alert_after = 168, alert_every = 48 },
}
The settings are in hours, so this says that if we haven't seen a HEAD
build in 1 day or a stable branch build in 1 week, alert the owner by
email, and keep repeating the alert in each case every 2 days.
If some intrepid buildfarm owner wants to test this out by using low
settings that would trigger an alert that would be good - the cron job
runs every hour.
cheers
andrew
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Faster StrNCpy
We sometimes see TupleDescInitEntry () taking high CPU times via OProfile. This does include, amongst a lot of other code, a call to namestrcpy () which in turn calls StrNCpy (). Perhaps this is not a good candidate right now as a name string is only 64 bytes. David From: [EMAIL PROTECTED] on behalf of Tom Lane Sent: Wed 9/27/2006 6:49 AM To: Andrew Dunstan Cc: josh@agliodbs.com; pgsql-hackers@postgresql.org; Neil Conway; Martijn van Oosterhout Subject: Re: [HACKERS] Faster StrNCpy Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. What are the other hotspots? The ones I can think of offhand are set_ps_display and use of strncpy as a HashCopyFunc. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas [EMAIL PROTECTED] writes: AFAICS, we could disable the optimization and use a full-blown transaction when vacuuming a table with a functional block index. No, we couldn't, or at least it's not merely a matter of reversing a recent optimization. The fundamental issue with all these proposals is the assumption that you can re-compute the index entries at all. VACUUM has never, ever, depended on the assumption that it can re-evaluate index entries and get the same answers as the original insertion did. Now obviously it should theoretically be able to do that, in a theoretical bug-free world, but given that we allow user-defined functions in index expressions that is a very hazardous assumption: you might get a different answer. Or an error. The current VACUUM procedure is able to clean up index entries correctly without any recalculation of the index values, just matching of TIDs. I think we'd be taking a serious robustness hit if we abandon that property. This is basically the same objection that I have to the occasional proposals for retail VACUUM. BTW, it's not merely a problem for functional indexes: the datatype-specific functions invoked while searching an index are also hazards. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Buildfarm alarms
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Andrew Dunstan
Sent: 27 September 2006 14:56
To: [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Buildfarm alarms
If some intrepid buildfarm owner wants to test this out by using low
settings that would trigger an alert that would be good - the
cron job
runs every hour.
Dunno about intrepid, but I've added the following to Snake:
alerts = {
HEAD = { alert_after = 1, alert_every = 2 },
REL8_1_STABLE = { alert_after = 168, alert_every = 48 },
REL8_0_STABLE = { alert_after = 168, alert_every = 48 },
}
Thanks for your work on this.
Regards, Dave
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: The problem was generated, because -fast option was set only for the compiler and not for the linker. Linker takes wrong version of libraries. If -fast is set for both then horology test is OK, but question was if float optimalization should generate some problems. So FAQ_Solaris needs to tell people to put -fast in both CFLAGS and LDFLAGS? Exactly, but I want to sure, that float optimalization is safe and should be applied for postgres, because -fast breaks IEE754 standard. If it is OK I will adjust FAQ_Solaris. Zdenek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] psql service parameter
I have just noticed that there does not seem to be a psql command line switch to specify a pg_service.conf name to connect to. I know we can use a PGSERVICE environment setting instead, but I think we should have a command line switch too. Small 8.3 TODO item? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Block B-Tree concept
Tom Lane [EMAIL PROTECTED] writes: Heikki Linnakangas [EMAIL PROTECTED] writes: Also, now that we have concurrent CREATE INDEX, we could implement concurrent REINDEX as well, I believe. That's probably more easily said than done --- in particular, I don't understand what the committed state after the first transaction would look like. CREATE INDEX can get away with it because nothing need be depending on the new index, but you can't say that for an existing index (esp. if it's UNIQUE). I think you build a whole new index named something like .temp-reindex and then as the last step of the second transaction delete the old idnex and rename the new index. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Block B-Tree concept
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: That's probably more easily said than done --- in particular, I don't understand what the committed state after the first transaction would look like. I think you build a whole new index named something like .temp-reindex and then as the last step of the second transaction delete the old idnex and rename the new index. That would require getting exclusive lock on the table. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql service parameter
Andrew Dunstan [EMAIL PROTECTED] writes: I have just noticed that there does not seem to be a psql command line switch to specify a pg_service.conf name to connect to. I know we can use a PGSERVICE environment setting instead, but I think we should have a command line switch too. Small 8.3 TODO item? More generally, it'd be nice to be able to specify a PQconnectdb string, instead of having to invent a new psql switch for every keyword we allow in those strings. 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] Block B-Tree concept
I think you build a whole new index named something like .temp-reindex and then as the last step of the second transaction delete the old idnex and rename the new index. That would require getting exclusive lock on the table. Just out of curiosity, creating a new index concurrently (or online, whatever you call it) doesn't require to set an exclusive lock on the table ? I thought it would, at least swiftly at the end of the operation, after all it's modifying the table... Cheers, Csaba. ---(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] psql service parameter
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I have just noticed that there does not seem to be a psql command line switch to specify a pg_service.conf name to connect to. I know we can use a PGSERVICE environment setting instead, but I think we should have a command line switch too. Small 8.3 TODO item? More generally, it'd be nice to be able to specify a PQconnectdb string, instead of having to invent a new psql switch for every keyword we allow in those strings. I think that both would be nice. Indeed, I wanted to suggest a switch for PGSERVICE myself. Ideas: -n or --name for the service name -C or --connect for the connect string So, +1 on the original suggestion! Yours, Laurenz Albe ---(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] psql service parameter
On Wed, Sep 27, 2006 at 04:54:35PM +0200, Albe Laurenz wrote: I think that both would be nice. Indeed, I wanted to suggest a switch for PGSERVICE myself. It some point I was wondering about using the @ symbol. If you have a service entry called testserver, you could do: psql @testserver But maybe that introduces too many quoting issues. Ofcourse, being able to provide a complete connect string would be good too... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] Block B-Tree concept
On Wed, Sep 27, 2006 at 05:38:38AM -0400, Bruce Momjian wrote: Heikki Linnakangas wrote: Jim C. Nasby wrote: Couldn't vacuum just eliminate tuples marked dead? Heck, don't we do that anyway right now? You mean _index_ tuples marked dead? Sure, no problem there. Granted, you'd want to periodically ensure that you scan the entire index, but that shouldn't be horribly hard to set up. Well, it seems to be. A vacuum can't evaluate index expressions because it's not in a real transaction. The DBA could set up a cron job to do SELECT * FROM foo WHERE bar 0 etc. with enable_seqscan=false? That would work, but we can't depend on an additional administrative task like. And we might as well just disable the optimization that's causing us problems. Why can't the C code just do a full index scan that touches the heap, sets those expired bits, and then do a vacuum? My point is that the bits can be set outside the normal vacuum process, so you don't have to be doing heap lookups from the index inside vacuum. Assuming the heap is mostly in index order, the full index scan shouldn't take much longer than a heap scan, and if the heap doesn't match index order, a block index shouldn't be used anyway. Well, my thought was to have a backend process that would periodically scan a small section of the index, so that you wouldn't have a long-running transaction. That could then be followed by a vacuum of that same section of the index, which would nuke the dead tuple entries. Though, maybe we wouldn't even need the vacuum step since 8.2 will now reclaim tuples marked as dead? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Buildfarm alarms
On Wed, 27 Sep 2006, Andrew Dunstan wrote: The settings are in hours, so this says that if we haven't seen a HEAD build in 1 day or a stable branch build in 1 week, alert the owner by email, and keep repeating the alert in each case every 2 days. How does this know if there wasn't a build because nothing in CVS changed over that time period? Especially on the back branches it is normal to go weeks without a build. Kris Jurka ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Cross-table statistics idea
On Wed, Sep 27, 2006 at 12:30:43PM +0100, Simon Riggs wrote: On Tue, 2006-09-26 at 21:27 -0500, Jim C. Nasby wrote: Since I don't recall any ideas ever having been thrown out on how to do this... ISTM that we could gain additional insight on how many rows would likely result from a join One thing we can do is to use cross-column relationships to improve the estimation of Ndistinct. If we have a table Order_line (PK orderId, lineNum) If we look at lineNum and see it has on average 10 values we can then use this information to compute that Ndistinct should be -0.1, i.e. the number of values is proportional to the number of rows with a factor of 10. Right now if there are more than 10 lineNums per orderId on average then we never decide that orderId is a scalable statistic. I propose adding a final step to ANALYZE that applies a cross-column rule after all columns have been analysed. If all except one column of a PK have very low Ndistinct we can use that to calculate a minimum number of Ndistinct for the column with a high number of values. If that minimum number is less than the Ndistinct estimate in isolation, then we overlay the new value. This is desirable because the estimation of Ndistinct is very sensitive to the number of matching rows in the sample, so Ndistinct estimates are usually very poor for large Ndistinct. The estimates for low Ndistinct are much better, so we can use them with a lower standard error to correct the in-isolation estimate of other columns. But wouldn't overlaying the value screw us if we wanted to look up something based on the unique field? (ie: if there was a line_id in order_line and we wanted to look something up based on line_id). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Buildfarm cvsignore files
I noticed that the build farm is only looking for the cvs-ignore'd files for a vpath build. Attached is a patch that will stop at the CVS stage if there are any cvs-ignore'd files in the clean repository. Its not triggered by a from-source build, only what should have been a clean check out. Thanks, -rocco -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: Sunday, September 03, 2006 11:45 AM To: Tom Lane Cc: Chris Browne; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path Andrew Dunstan wrote: Tom Lane wrote: The buildfarm script is supposed to complain about unexpected files in the repository --- I wonder if it is fooled by the .cvsignore entries for these files? Yes, we do. A patch made in July 2005 has this comment: ignore files listed in cvsignore files - this will stop inappropriate triggering of vpath builds. Perhaps I should only do that for vpath builds. Or perhaps I should even remove them at the end of a build, since we don't expect any of those files in a clean repo, do we? Also, in case anyone has not got the message yet: Don't ever build by hand in the buildfarm repo. Ever. I mean it. Use a copy. I have just committed a patch that removes the cvsignore trap. This should be safe as we now remove them at the end of a buildfarm vpath run. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings pgbf-cvsignore.patch Description: pgbf-cvsignore.patch ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql service parameter
On Wed, Sep 27, 2006 at 10:23:13AM -0400, Andrew Dunstan wrote: I have just noticed that there does not seem to be a psql command line switch to specify a pg_service.conf name to connect to. I know we can use a PGSERVICE environment setting instead, but I think we should have a command line switch too. Small 8.3 TODO item? The hack I've used so far is PGSERVICE=/path/to/pg_service.conf psql Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Buildfarm alarms
Kris Jurka wrote: On Wed, 27 Sep 2006, Andrew Dunstan wrote: The settings are in hours, so this says that if we haven't seen a HEAD build in 1 day or a stable branch build in 1 week, alert the owner by email, and keep repeating the alert in each case every 2 days. How does this know if there wasn't a build because nothing in CVS changed over that time period? Especially on the back branches it is normal to go weeks without a build. Kris Jurka Indeed. The short answer is it doesn't. But there is a buildfarm config option to allow you to force a build every so often even if there hasn't been a CVS change, and I'm thinking of providing an option for this to be branch specific. The you would make this setting shorter than your alarm period for any branch you had an alarm set for. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] large object regression tests
On Sun, 24 Sep 2006, Jeremy Drake wrote: On Thu, 21 Sep 2006, Tom Lane wrote: I suggest that instead of testing the server-side lo_import/lo_export functions, perhaps you could test the psql equivalents and write and read a file in psql's working directory. snip In the mean time, I will alter the test to also test the psql backslash commands based on how the copy equivalents are tested, since I had forgotten them and they need to be tested also. I just tried using the \lo_import command in a regression test, and I think I figured out why this will not work: $ make check ... largeobject ... FAILED ... $ cat regression.diffs *** ./expected/largeobject.out Sun Sep 24 19:55:25 2006 --- ./results/largeobject.out Sun Sep 24 19:55:58 2006 *** *** 188,194 (1 row) \lo_import 'results/lotest.txt' ! lo_import 31138 \set newloid :LASTOID -- This is a hack to test that export/import are reversible -- This uses knowledge about the inner workings of large object mechanism --- 188,194 (1 row) \lo_import 'results/lotest.txt' ! lo_import 31199 \set newloid :LASTOID -- This is a hack to test that export/import are reversible -- This uses knowledge about the inner workings of large object mechanism == Yes, that's the large object OID in the output there, and it is different each run (as I expect). If you look at src/bin/psql/large_obj.c line 192, you see: fprintf(pset.queryFout, lo_import %u\n, loid); Which is executed unconditionally whenever the lo_import is successful. While in a normal circumstance, it is quite necessary to know the loid, since it does change each call, in this case it serves to break the diffs, and so I guess it is impossible to use the \lo_import command in a regression test. -- The first time, it's a KLUDGE! The second, a trick. Later, it's a well-established technique! -- Mike Broido, Intermetrics ---(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] String Similarity
Hello! Would you like to give me a simple introduction of Levenshtein distence function? Thank you! On2006-05-1919:54,MartijnvanOosterhoutwrote: OnFri,May19,2006at04:00:48PM-0400,MarkWoodwardwrote: (3)IstherealsoadesireforaLevenshteindistencefunctionfortext andvarchars?Iexperimentedwithit,andwasforcedtowritethefunction initem#1. PostgresalreadyhasaLevenshteindistencefunction,seefuzzystrmatch incontrib.Whateveryoucomeupwithmightfitinwellthere... Haveaniceday, Fromeachaccordingtohisability.Toeachaccordingtohisabilitytolitigate.
Re: [PATCHES] [HACKERS] large object regression tests
On Mon, 25 Sep 2006, Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: I just tried using the \lo_import command in a regression test, and I think I figured out why this will not work: ... Yes, that's the large object OID in the output there, and it is different each run (as I expect). Right. I'd suggest temporarily setting ECHO off to hide the unpredictable part of the output. There are similar measures taken in many of the contrib tests. I tried this: jeremyd=# \set QUIET jeremyd=# \set ECHO off jeremyd=# BEGIN; jeremyd=# \lo_import results/lotest.txt lo_import 84951 jeremyd=# ROLLBACK; From what I could tell in the code, the message is printed regardless of setting. It looks like the large_obj.c output is missing much of the output settings handling which is in the PrintQueryStatus function in common.c, such as handling quiet mode, and html output. I will try to dig around and try to put together a patch to make it respect the settings like other commands... -- You are old, said the youth, and your programs don't run, And there isn't one language you like; Yet of useful suggestions for help you have none -- Have you thought about taking a hike? Since I never write programs, his father replied, Every language looks equally bad; Yet the people keep paying to read all my books And don't realize that they've been had. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Transaction is read-only in auto commit mode
Hi,My application based on Java servlets was running fine with version PostgreSQL 7.x, but started giving error: "transaction is read-only", in version 8.0 and 8.1. I am using Suse Linux 9.3/PostgreSQL 8.0 or Suse Linux 10.1/PostgreSQL 8.1. I am using JDBC 3 drivers and all connections are in auto-commit mode. Could you please tell me what's going wrong. Strangely, I looked through all the postings in all the forums but could not find a mention of this problem. Am I doing something exttremely stupid orhas something changed in version 8 onwards that's causing this problem? The error appears sporadically, not always but quite frequently. I am using the standard postgresql.conf, except that I had increased the shared buffers and working memory sizes.I'd really appreciate if anyone could suggest a pointer for further investigation, if not an outright solution.Thanks in advance..Asok
[HACKERS] PostgreSQL HA questions
Hello We're looking for HA PostgreSQL solution,so have a couple of questions: 1. Is it possible for multiply PostgreSQL instances (engines,cores) to use same DATA space? For example,to have two PostgreSQL processes which will use same data directory,same files,and same data ? So,if You update some data in some table over postmaster1,the process which uses connection to postmaster2 will 'see' the exact same data in the same table ? 2. Becouse of vaccuming issues,is it possible to create such a client process which will use two identical tables,and on receiving a signal,it will switch between those tables.For example,first a client application uses table1,after some time,send a signal to process,it will switch using table2,so You can freely vacuum table1 or whatsoever.After vacuuming done,table 1 will sinchronize with table2 and keep up-to-date until You send next signal to application,which will switch using table1,so You can vacuum table2. If any one has any ideas,thoughts ? Sincerely Dragan
Re: [HACKERS] DROP FUNCTION IF EXISTS
CN == Csaba Nagy [EMAIL PROTECTED] writes: CN The full story is that I typed 'ü' (u-umlaut if it won't render CN correctly) and backspace before the '1'. I guess the backspace CN will delete byte-wise and will so fail to delete properly CN multi-byte characters. Backspace deletes character-wise, as long as you have LANG set correctly. Check LANG and the LC_* environment variables. /Benny ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
On Wed, Sep 27, 2006 at 04:09:18PM +0200, Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: The problem was generated, because -fast option was set only for the compiler and not for the linker. Linker takes wrong version of libraries. If -fast is set for both then horology test is OK, but question was if float optimalization should generate some problems. So FAQ_Solaris needs to tell people to put -fast in both CFLAGS and LDFLAGS? Exactly, but I want to sure, that float optimalization is safe and should be applied for postgres, because -fast breaks IEE754 standard. If it is OK I will adjust FAQ_Solaris. Zdenek Unless the packager understands the floating point usage of every piece and module included and the effect that the -fast option will have on them, please do not recommend it for anything but extremely well tested dedicated use-cases. When it causes problems, it can be terrible if the problems are not detected immediately. Massive data corruption could occur. Given these caveats, in a well tested use-case the -fast option can squeeze a bit more from the CPU and could be used. I have had to debug the fallout from the -fast option in other software in the past. Let's just say, backups are a good thing. I would vote not to recommend it without very strong cautions similar to was Sun includes in the compiler manual pages. Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL HA questions
First, I'm moving this to -general, because this is way off topic for -hackers as near as I can tell. On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote: 1. Is it possible for multiply PostgreSQL instances (engines,cores) to use same DATA space? No. In fact, this is a very good way to cause corruption. What you _can_ do is set up a watchdog process that allows a different machine to take over the filesystem on a shared disk array, for instance, and come back up in recovery mode. So your outage is roughly as long as the time to notice your primary node failed, plus the time to recover from database crash. There are various software packages that will allow you to do this. NOT ALL OF THEM WORK WELL. Go back and read that sentence again. No, I am not saying this because of any painful experiences I have ever had ;-) 2. Becouse of vaccuming issues,is it possible to create such a client process which will use two identical tables,and on receiving a signal,it will switch between those tables.For example,first a client application uses table1,after some time,send a signal to process,it will switch using table2,so You can freely vacuum table1 or whatsoever.After vacuuming done,table 1 will sinchronize with table2 and keep up-to-date until You send next signal to application,which will switch using table1,so You can vacuum table2. It isn't clear to me why you think you need to do this: vacuum doesn't block your queries anyway. If the idea is that you have a table that you'd rather TRUNCATE and not have to vacuum, however, that makes sense. There are several strategies for this. My colleague Chris Browne seems really to like this kind of functionality, and has discussed it more than once on the -general list. I think you can find his detailed outlines of how to do this sort of thing by searching for rotor tables. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
This brings up something that I may have asked before, but I may also have just thought I should: Should/could we have the error message somehow reflect if the connection used pgpass.conf to pick up the password? //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Jeanna Geier Sent: Tuesday, September 26, 2006 7:51 PM To: Jeff Frost; Alvaro Herrera Cc: Tom Lane; pgsql-admin@postgresql.org; pgsql- [EMAIL PROTECTED] Subject: Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues Thank you, Thank you, Thank you!! :o) Jeff - Thanks in particular for your help on this, it is greatly appreciated! It was a hidden folder, but not anymore!! I found the file and re- set the password for the 'postgres' user and can now connect using my 'md5' hostssl connection: hostssl all all 127.0.0.1/32 md5 __ C:\msys\1.0\local\pgsql\binpsql -d apt -U postgres Password: Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. apt=# Again, thanks for everyone's time and effort on this! This mailing list is top-notch!! -Jeanna - Original Message - From: Jeff Frost [EMAIL PROTECTED] To: Alvaro Herrera [EMAIL PROTECTED] Cc: Jeanna Geier [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql- [EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 12:35 PM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues On Tue, 26 Sep 2006, Alvaro Herrera wrote: Jeanna Geier wrote: Searched again for 'pgpass' and for the 'Application Data' directory with no luck... The file is called pgpass.conf on Windows. As for the Application Data, it may be called differently if your Windows is localized -- try looking for %APPDATA%. (I think I'd do this by opening a terminal window and echo %APPDATA% or cd %APPDATA%). You can also just click start, run then type %appdata% and windows will open an explorer window in that directory. I guess it's also possible you need to turn on the view hidden and system directories in the explorer options to see/find in that directory, but I'm not sure. -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Buildfarm cvsignore files
Rocco Altier wrote: I noticed that the build farm is only looking for the cvs-ignore'd files for a vpath build. Attached is a patch that will stop at the CVS stage if there are any cvs-ignore'd files in the clean repository. Its not triggered by a from-source build, only what should have been a clean check out. I thought I had that taped. Anyway, it can be done more simply. I will fix it. BTW, -hackers isn't really the place for buildfarm patches (or bugs, usually) - that's what the buildfarm members list is for,. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)
[EMAIL PROTECTED] wrote: On Tue, Sep 19, 2006 at 11:21:51PM -0400, Alvaro Herrera wrote: [EMAIL PROTECTED] wrote: On Tue, Sep 19, 2006 at 08:20:13AM -0500, Jim C. Nasby wrote: On Mon, Sep 18, 2006 at 07:45:07PM -0400, [EMAIL PROTECTED] wrote: I would not use a 100% random number generator for a UUID value as was suggested. I prefer inserting the MAC address and the time, to at least allow me to control if a collision is possible. This is not easy to do using a few lines of C code. I'd rather have a UUID type in core with no generation routine, than no UUID type in core because the code is too complicated to maintain, or not portable enough. As others have mentioned, using MAC address doesn't remove the possibility of a collision. It does, as I control the MAC address. What happens if you have two postmaster running on the same machine? Could be bad things. :-) For the case of two postmaster processes, I assume you mean two different databases? If you never intend to merge the data between the two databases, the problem is irrelevant. There is a much greater chance that any UUID form is more unique, or can be guaranteed to be unique, within a single application instance, than across all application instances in existence. If you do intend to merge the data, you may have a problem. You may. But it's not very likely. Since a) there is a 13-bit random number in addition to the MAC address (the clock sequence) and b) the timestamp has a granularity of 100 nanosec. An implementation could be made to prevent clock-sequence collisions on the same machine and thereby avoid this altogether. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [Pgbuildfarm-members] [HACKERS] Buildfarm alarms
On Wed, Sep 27, 2006 at 01:55:21PM -0400, Andrew Dunstan wrote: Kris Jurka wrote: On Wed, 27 Sep 2006, Andrew Dunstan wrote: The settings are in hours, so this says that if we haven't seen a HEAD build in 1 day or a stable branch build in 1 week, alert the owner by email, and keep repeating the alert in each case every 2 days. How does this know if there wasn't a build because nothing in CVS changed over that time period? Especially on the back branches it is normal to go weeks without a build. Kris Jurka Indeed. The short answer is it doesn't. But there is a buildfarm config option to allow you to force a build every so often even if there hasn't been a CVS change, and I'm thinking of providing an option for this to be branch specific. The you would make this setting shorter than your alarm period for any branch you had an alarm set for. Another possibility is just having the client report no CVS changes detected to the server, as a form of a ping. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Restart after power outage: createdb
Jon Lapham [EMAIL PROTECTED] writes in pgsql-general: If I run... sleep 3; echo starting; createdb bar ...and power off the VM while the createdb bar is running. Upon restart, about 50% of the time I can reproduce the following error message: [EMAIL PROTECTED] ~]$ psql bar psql: FATAL: database bar does not exist [EMAIL PROTECTED] ~]$ createdb bar createdb: database creation failed: ERROR: could not create directory base/65536: File exists What apparently is happening here is that the same OID has been assigned to the new database both times. Even though the createdb didn't complete, the directory it started to build is there and so there's a filename collision. So, running createdb bar a second time works. Yeah, because the OID counter has been advanced, and so the second createdb uses a nonconflicting OID. In theory this scenario should not happen, because a crash-and-restart is supposed to guarantee that the OID counter comes up at or beyond where it was before the crash. After thinking about it for awhile, I believe the problem is that CREATE DATABASE is breaking the WAL rule: it's allowing a data change (specifically, creation of the new DB subdirectory) to hit disk without having guaranteed that associated WAL entries were flushed first. Specifically, if we generated an XLOG_NEXTOID WAL entry to record the consumption of an OID for the database, there isn't anything ensuring that record gets to disk before the mkdir occurs. (ie, the comment in XLogPutNextOid is correct as far as it goes, but it fails to account for outside-the-database effects such as creation of a directory named after the OID.) Hence after restart the OID counter might not get advanced as far as it should have been. We could fix this two different ways: 1. Put an XLogFlush into createdb() somewhere between making the pg_database entry and starting to create subdirectories. 2. Check for conflicting database directories while assigning the OID, comparable to what GetNewRelFileNode() does for table files. #2 has some appeal because it could deal with random junk in $PGDATA/base regardless of how the junk got there. However, to do that in a really bulletproof way we'd have to check all the tablespace directories too, and that's starting to get a tad tedious for something that shouldn't happen anyway. So I'm leaning to #1 as a suitably low-effort fix. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Pgbuildfarm-members] [HACKERS] Buildfarm alarms
Jim C. Nasby wrote: Another possibility is just having the client report no CVS changes detected to the server, as a form of a ping. I am not going to re-architect the buildfarm client and server for this. I think what I have done will be quite sufficient. I suspect most people will only want alarms on HEAD anyway. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Restart after power outage: createdb
On Wed, Sep 27, 2006 at 04:13:34PM -0400, Tom Lane wrote: Jon Lapham [EMAIL PROTECTED] writes in pgsql-general: If I run... sleep 3; echo starting; createdb bar ...and power off the VM while the createdb bar is running. Upon restart, about 50% of the time I can reproduce the following error message: [EMAIL PROTECTED] ~]$ psql bar psql: FATAL: database bar does not exist [EMAIL PROTECTED] ~]$ createdb bar createdb: database creation failed: ERROR: could not create directory base/65536: File exists What apparently is happening here is that the same OID has been assigned to the new database both times. Even though the createdb didn't complete, the directory it started to build is there and so there's a filename collision. So, running createdb bar a second time works. Yeah, because the OID counter has been advanced, and so the second createdb uses a nonconflicting OID. In theory this scenario should not happen, because a crash-and-restart is supposed to guarantee that the OID counter comes up at or beyond where it was before the crash. After thinking about it for awhile, I believe the problem is that CREATE DATABASE is breaking the WAL rule: it's allowing a data change (specifically, creation of the new DB subdirectory) to hit disk without having guaranteed that associated WAL entries were flushed first. Specifically, if we generated an XLOG_NEXTOID WAL entry to record the consumption of an OID for the database, there isn't anything ensuring that record gets to disk before the mkdir occurs. (ie, the comment in XLogPutNextOid is correct as far as it goes, but it fails to account for outside-the-database effects such as creation of a directory named after the OID.) Hence after restart the OID counter might not get advanced as far as it should have been. We could fix this two different ways: 1. Put an XLogFlush into createdb() somewhere between making the pg_database entry and starting to create subdirectories. 2. Check for conflicting database directories while assigning the OID, comparable to what GetNewRelFileNode() does for table files. #2 has some appeal because it could deal with random junk in $PGDATA/base regardless of how the junk got there. However, to do that in a really bulletproof way we'd have to check all the tablespace directories too, and that's starting to get a tad tedious for something that shouldn't happen anyway. So I'm leaning to #1 as a suitably low-effort fix. Thoughts? It'd be nice to clean things up, but I understand the reluctance to do so. Maybe a good compromise would be to warn about files that are present in $PGDATA but don't show up in any catalogs. Then again, if we're doing that, we could probably just nuke 'em... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] [GENERAL] Restart after power outage: createdb
Jim C. Nasby [EMAIL PROTECTED] writes: Then again, if we're doing that, we could probably just nuke 'em... This has been considered and rejected before, on the grounds that removing files you don't know the source of is a good way to lose data. Come to think of it, that argument bears on the immediate problem too. The way createdb() is coded, if it gets a failure (like File exists) trying to create the database's directories, it will attempt to apply remove_dbtablespaces() to clean up after itself. This would result in removing the pre-existing directory, which violates the principle of not removing unexpected files. So now I'm starting to think we do need a check-for-conflicting-files step in createdb. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL HA questions
[EMAIL PROTECTED] (Andrew Sullivan) writes: It isn't clear to me why you think you need to do this: vacuum doesn't block your queries anyway. If the idea is that you have a table that you'd rather TRUNCATE and not have to vacuum, however, that makes sense. There are several strategies for this. My colleague Chris Browne seems really to like this kind of functionality, and has discussed it more than once on the -general list. I think you can find his detailed outlines of how to do this sort of thing by searching for rotor tables. I'd suggest looking at the section in the documentation on Partitioning; the mechanisms there look like the Better Way these days. http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html There were some things that were Pretty Neat about rotor tables; as of 8.1, the benefits gotten from constraint propagation with partitioning seems to make that a much more attractive way to go about things. There are always going to be some caveats for whatever mechanism is used to partition data; it looks like 8.1's constraint propagation pushes preference towards using inheritance... -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://cbbrowne.com/info/linux.html It is easier to optimize correct code, than correct optimized code -- Yves Deville ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Restart after power outage: createdb
On Wed, Sep 27, 2006 at 04:52:51PM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Then again, if we're doing that, we could probably just nuke 'em... This has been considered and rejected before, on the grounds that removing files you don't know the source of is a good way to lose data. Come to think of it, that argument bears on the immediate problem too. The way createdb() is coded, if it gets a failure (like File exists) trying to create the database's directories, it will attempt to apply remove_dbtablespaces() to clean up after itself. This would result in removing the pre-existing directory, which violates the principle of not removing unexpected files. So now I'm starting to think we do need a check-for-conflicting-files step in createdb. I think it would be really useful to tell the DBA that there's a bunch of files in $PGDATA that are probably dead. If stuff had suddenly disappeared out of the catalog I'd certainly like to know it. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] -HEAD planner issue wrt hash_joins on dbt3 ?
Stefan Kaltenbrunner wrote: too bad - however any idea on one of the other troubling querys (q21) I mentioned in the mail I resent to the list (after the original one got lost)? http://archives.postgresql.org/pgsql-hackers/2006-09/msg02011.php What happens if you increase statistics for l_orderkey? Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Faster StrNCpy
On Wed, Sep 27, 2006 at 07:08:05AM -0700, Strong, David wrote: We sometimes see TupleDescInitEntry () taking high CPU times via OProfile. This does include, amongst a lot of other code, a call to namestrcpy () which in turn calls StrNCpy (). Perhaps this is not a good candidate right now as a name string is only 64 bytes. Just wondering - are any of these cases where a memcpy() would work just as well? Or are you not sure that the source string is at least 64 bytes in length? memcpy(target, source, sizeof(target)); target[sizeof(target)-1] = '\0'; I imagine any extra checking causes processor stalls, or at least for the branch prediction to fill up? Straight copies might allow for maximum parallelism? If it's only 64 bytes, on processors such as Pentium or Athlon, that's 2 or 4 cache lines, and writes are always performed as cache lines. I haven't seen the code that you and Tom are looking at to tell whether it is safe to do this or not. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Can i see server SQL commands ?
Hi all I wanna know what is going on while a DML command works. For example ; Which commands are executed by the core when we send an UPDATE tab SET col = val1... in case there is a foreing key or an unique constraint on table tab. How can i see that ? Best regards Adnan DURSUN ASRIN Bilişim Ltd. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] String Similarity
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, Sep 26, 2006 at 09:09:33AM +0800, Pang Zaihu wrote: Hello! Would you like to give me a simple introduction of Levenshtein distence function? Better than I could explain: http://en.wikipedia.org/wiki/Levenshtein_distance Thank you! Thank Wikipedia ;-) HTH - -- tomas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFFG1UIBcgs9XrR2kYRAr42AJ0TjRnUBqmogcKg12mXRVFl6oAjqQCeP/hw HmqRS+AANLP9eNbNIWp7jOM= =FHks -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
