Re: [HACKERS] Latest ecpg patch broke MSVC build
In hindsight, all these ecpg changes should have been made between beta1 and beta2 when we have time to deal with the fallout, not right before beta1. Or considered new features and held back for 8.4. Not picking on Michael, but the resemblance to the /contrib discussion is striking. Ecpg is another part of core PostgreSQL that lives by slightly different rules. /Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended trans action ID module to contrib as
Right. My thought is still that if it isn't good enough for core, it shouldn't be in contrib. If it *is* good enough, and we want it, we should accept that it came in long after freeze and put it in core anyway. If it *isn't*, then it should be on pgfoundry and be moved into core when it's ready - for 8.4 or so. The long and the short of it was that the patch wasn't ready. So if the patch wasn' ready, why did it get accepted for /contrib? To put it in core for 8.3, we'd have either had to delay the beta yet more, or force initdb post-beta1, neither of which would have flown. So it should've been saved for 8.4. The whole contrib thing confuses a lot of users. To me, contrib exists mostly as a forcing function to ensure that we keep the extension-module system working. Ok. But if that's what it's mainly for then we *really* shouldn't put things that we expect our users to rely heavily on. And if this thing will go deep into replication systems, that's exactly what it is. Contrib also has a role to play as a repository of code examples that people can crib from when developing new extension modules. I would not want to claim that it's all best practice code --- a lot of it definitely isn't --- but it stands a lot better chance of representing current good practice if it's maintained with the core code than if it's out on pgfoundry. On pgfoundry, it won't get included in the global- search-and-replace patches that we do so many of, and it'll most likely accumulate a lot of cruft from trying to be compatible with multiple core releases. Same comment applies here. And it's certainly far from best practice if it breaks the rules... /Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended trans action ID module to contrib as
(1) we've always been laxer about contrib than the core code, While that appears to be true, I think (a) there is no technical reason allowing us to do that, and (b) most people don't seem to like it. I will even go so far as to say there are technical reasons not to do it. I beleive that contrib is currently included in most if not all our packages. It certainly is on win32 and I think I've seen the RPMs. It may not be loaded by default but it's there. And that will have users expecting the same code quality as for the rest of the PGDG code. If we can't (or won't) do that, well,that's why we have pgfoundry. /Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended transaction ID module to contrib as
On Mon, 2007-10-08 at 17:38 -0400, Robert Treat wrote: On Monday 08 October 2007 16:29, Magnus Hagander wrote: The whole contrib thing confuses a lot of users. Is it included, or isn't it? IMHO, that distinction need to be clear, and I thought we were working (if not actively then at least passively) to retire contrib, moving things either to core or to pgFoundry. Adding yet another important feature that's just in contrib is making things worse, not better. IMHO, of course ;-) +1. I felt the same way about pg_standby, which would have been far more accessible for 8.2 users had it lived on pg_foundry. I think we should move a version of pg_standby to pg_foundry anyway, specifically to support 8.2 users. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including Snapshot Info with Indexes
On 10/8/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: I am always slightly late in understanding things. Let me try to understand the use of DSM. It is a bitmap index on whether all the tuples in a particular block is visible to all the backends, whether a particular block contains tuples which are invisible to everyone. But i think this will get subjected to the same limitations of Bitmap index. Even Oracle suggests the use of Bitmap index for only data warehousing tables, where the Bitmap indexes will be dropped and recreated after every bulk load. This is not a viable alternative for OLTP transactions. Well, it's not quite the same as a bitmap index, though both use a bitmap. You didn't quite get into details on what the limitations are and why it wouldn't be suitable for OLTP, but I don't see any significant problems. But i think i am late in the game as i haven't participated in those discussions Better late than never :). One Bitmap index block usually maps to lot of blocks in the heap. So locking of one page to update the DSM for update/delete/insert would hit the concurrency. But again all these are my observation w.r.t oracle bitmap indexes. May be i am missing something in DSM. Yeah, the DSM page could become a contention bottleneck. My current thinking is that we'd have a flag in the heap page header, that would be set together with the bit in the DSM. When the flag in the page header is set, you don't need to lock and update the DSM because you know the bit is already set. Vacuum would have to clear both the DSM bit and the flag. It matters to us, where the index scan will goto. If the Index Scan is going to touch DSM for understanding visibility(This might degrade the performance of some of the index scans, if they have to wait to acquire the share lock, and learn that they have to goto the heap to understand their visibility requirements.) In the mean while, if the vacuum, inserts/updates/deletes are holding the BUFFER_EXCLUSIVE lock on that, this would hurt the Select transactions. Since there is only one bit per block in the DSM(best case), there might be one DSM block per 8000 table blocks. All the transactions which are accessing the 8000 blocks will be waiting on this one DSM block. If we are going to update the Heap page header and asking the Indexscan to refer to that, then there is no reduction in random I/Os. Can't we say that if the snapshot info is embedded with index, we can avoid all these difficulties? Most importantly it won't affect the performance of current postgres in any way. Let's take up Retail Vacuuming again. The User defined function which would return different values at different time can be classified as non-deterministic functions. We can say that this index cannot be created on a non-deterministic function. This is the way it is implemented in Oracle. What they have done is they have classified certain built-in operators and functions as deterministic. Similarly they have classified a few as non-deterministic operators and functions. Can we follow a similar approach? We already do. A function must be marked as IMMUTABLE in order to use it in an index expression. But we can't enforce that the user defined function really behaves like an immutable function should. If someone creates a user-defined function in C that calls the C random() function, we can't stop it. A function is said to be deterministic, if it returns the same value, irrespective of how many times, it is invoked. I think this definition clearly puts the random function under the non-deterministic category. If we have such a classification, do you think we can resolve this issue? As I said earlier, using an index like that will of course lead to bogus results. But it won't currently cause any server crashes or more serious corruption. One more final word on unique indexes. Whenever we are doing an update, there will be insertions into the unique indexes which will trigger table lookups. Ofcourse there is more probability, that the table block would be in memory(un-pinned). Still contention for a shared resource is avoided, if the snapshot info is stored with the indexes. Let me get one more clarification, what would be type of performance results with this implementation, that would encourage the hackers community to accept the extra maintenance overhead. Thanks, Gokul.
Re: [HACKERS] Including Snapshot Info with Indexes
On 10/8/07, Florian G. Pflug [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: Hi Heikki, I am always slightly late in understanding things. Let me try to understand the use of DSM. It is a bitmap index on whether all the tuples in a particular block is visible to all the backends, whether a particular block contains tuples which are invisible to everyone. But i think this will get subjected to the same limitations of Bitmap index. Even Oracle suggests the use of Bitmap index for only data warehousing tables, where the Bitmap indexes will be dropped and recreated after every bulk load. This is not a viable alternative for OLTP transactions. But i think i am late in the game as i haven't participated in those discussions While the DSM might be similar in spirit to a bitmap index, the actual implementation has a lot more freedome I'd say, since you can tailor it exactly to the need of tracking some summarized visibility info. So not all shortcomings of bitmap indices must necessarily apply to the DSM also. But of course thats mostly handwavering... One Bitmap index block usually maps to lot of blocks in the heap. So locking of one page to update the DSM for update/delete/insert would hit the concurrency. But again all these are my observation w.r.t oracle bitmap indexes. May be i am missing something in DSM. A simple DSM would probably contain a bit per page that says all xmin GlobalXmin, and all xmax unset or aborted. That bit would only get SET during VACUUM, and only unset during INSERT/UPDATE/DELETE. If setting it is protected by a VACUUM-grade lock on the page, we might get away with no locking during the unset, making the locking overhead pretty small. Let me try to understand. Do you mean to say some kind of Test and Set implementation for Insert/Update/Delete? So that would mean that there won't be any lock during the change of bit flags. Why do we need lock to set it then? It looks like a great idea. I couldn't get that piece of discussion in the archive, which discusses the design of Retail Vacuum. So please advise me again here. Let's take up Retail Vacuuming again. The User defined function which would return different values at different time can be classified as non-deterministic functions. We can say that this index cannot be created on a non-deterministic function. This is the way it is implemented in Oracle. What they have done is they have classified certain built-in operators and functions as deterministic. Similarly they have classified a few as non-deterministic operators and functions. Can we follow a similar approach? Postgres already distinguishes VOLATILE,STABLE and IMMUTABLE functions. It doesn't, however, risk physical data corruption, even if you get that classification wrong. The worst that happens AFAIK are wrong query results - but fixing your function, followed by a REINDEX always corrects the problme. If you start poking holes into that safety net, there'll be a lot of pushback I believe - and IMHO rightly so, because people do, and always will, get such classifications wrong. A deterministic function is classified as one, which returns the same results, irrespective of how many times, it is invoked. So if we form a classification like that, do you think we will resolve the issue of Retail Vaccum? In the case of User-Defined functions, the user should be defining it as Deterministic. Can we frame a set of guidelines, or may be some test procedure, which can declare a certain function as deterministic? I am just saying from the top of my mind. Even otherwise, if we can even restrict this indexing to only Built-in deterministic functions., don't you think it would help the cause of a majority? I have just made the proposal to create the index with snapshot a optional one. Thanks, Gokul.
Re: [HACKERS] PG on NFS may be just a bad idea
On Mon, 2007-10-01 at 19:25 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: http://blogs.netapp.com/dave/2007/08/oracle-optimize.html Not a whole lot of technical content there, but pretty interesting nonetheless. I *think* that the issues we're seeing are largely in the NFS client-side kernel code, so bypassing that stack as Oracle is doing might eliminate the problem. Of course, there's a sizable amount of code to be written to do that ... Yeh, that would take a while. I thought of another reason to do that also. If you put a tablespace on an NFS mount and the remote server crashes, it sounds like there could be a window of potential data loss. We could guard against that by recovering the tablespace, but we don't do that unless the local server crashes. So having your own NFS client would allow you to tell that the link had dropped and needed to be recovered. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including Snapshot Info with Indexes
On 10/9/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: On 10/8/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: I am always slightly late in understanding things. Let me try to understand the use of DSM. It is a bitmap index on whether all the tuples in a particular block is visible to all the backends, whether a particular block contains tuples which are invisible to everyone. But i think this will get subjected to the same limitations of Bitmap index. Even Oracle suggests the use of Bitmap index for only data warehousing tables, where the Bitmap indexes will be dropped and recreated after every bulk load. This is not a viable alternative for OLTP transactions. Well, it's not quite the same as a bitmap index, though both use a bitmap. You didn't quite get into details on what the limitations are and why it wouldn't be suitable for OLTP, but I don't see any significant problems. But i think i am late in the game as i haven't participated in those discussions Better late than never :). One Bitmap index block usually maps to lot of blocks in the heap. So locking of one page to update the DSM for update/delete/insert would hit the concurrency. But again all these are my observation w.r.t oracle bitmap indexes. May be i am missing something in DSM. Yeah, the DSM page could become a contention bottleneck. My current thinking is that we'd have a flag in the heap page header, that would be set together with the bit in the DSM. When the flag in the page header is set, you don't need to lock and update the DSM because you know the bit is already set. Vacuum would have to clear both the DSM bit and the flag. It matters to us, where the index scan will goto. If the Index Scan is going to touch DSM for understanding visibility(This might degrade the performance of some of the index scans, if they have to wait to acquire the share lock, and learn that they have to goto the heap to understand their visibility requirements.) In the mean while, if the vacuum, inserts/updates/deletes are holding the BUFFER_EXCLUSIVE lock on that, this would hurt the Select transactions. Since there is only one bit per block in the DSM(best case), there might be one DSM block per 8000 table blocks. All the transactions which are accessing the 8000 blocks will be waiting on this one DSM block. If we are going to update the Heap page header and asking the Indexscan to refer to that, then there is no reduction in random I/Os. Can't we say that if the snapshot info is embedded with index, we can avoid all these difficulties? Most importantly it won't affect the performance of current postgres in any way. Let's take up Retail Vacuuming again. The User defined function which would return different values at different time can be classified as non-deterministic functions. We can say that this index cannot be created on a non-deterministic function. This is the way it is implemented in Oracle. What they have done is they have classified certain built-in operators and functions as deterministic. Similarly they have classified a few as non-deterministic operators and functions. Can we follow a similar approach? We already do. A function must be marked as IMMUTABLE in order to use it in an index expression. But we can't enforce that the user defined function really behaves like an immutable function should. If someone creates a user-defined function in C that calls the C random() function, we can't stop it. A function is said to be deterministic, if it returns the same value, irrespective of how many times, it is invoked. I think this definition clearly puts the random function under the non-deterministic category. If we have such a classification, do you think we can resolve this issue? If we frame a set of guidelines/test procedure, do you think it might solve the issue? Even, if we don't allow this type of indexing to anything other than built-in deterministic functions, i feel it would serve most of the indexing requirements. As I said earlier, using an index like that will of course lead to bogus results. But it won't currently cause any server crashes or more serious corruption. One more final word on unique indexes. Whenever we are doing an update, there will be insertions into the unique indexes which will trigger table lookups. Ofcourse there is more probability, that the table block would be in memory(un-pinned). Still contention for a shared resource is avoided, if the snapshot info is stored with the indexes. Let me get one more clarification, what would be type of performance results with this implementation, that would encourage the hackers community to accept the extra maintenance overhead. Thanks, Gokul.
Re: [HACKERS] Latest ecpg patch broke MSVC build
On Tue, Oct 09, 2007 at 08:15:35AM +0200, Magnus Hagander wrote: In hindsight, all these ecpg changes should have been made between beta1 and beta2 when we have time to deal with the fallout, not right before beta1. This one I totally agree with. Or considered new features and held back for 8.4. Not picking on Michael, but the resemblance to the /contrib discussion is striking. Ecpg is another part of core PostgreSQL that lives by slightly different rules. But this one I don't. At least not the new features part. Had I considered the patch a new feature I wouldn't have committed it. To me it looked like a bug fix and I still see it as such. Yes, we could have documented the bug instead, but still I don't see how we could argue that getting multithreading to work on Windows is a feature when it's already working on all other platforms a and is also compilable, but not working in some/most cases, on Windows. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including Snapshot Info with Indexes
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: On 10/9/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: A function is said to be deterministic, if it returns the same value, irrespective of how many times, it is invoked. I think this definition clearly puts the random function under the non-deterministic category. If we have such a classification, do you think we can resolve this issue? If we frame a set of guidelines/test procedure, do you think it might solve the issue? Even, if we don't allow this type of indexing to anything other than built-in deterministic functions, i feel it would serve most of the indexing requirements. We already do this. c.f. IMMUTABLE at http://www.postgresql.org/docs/8.2/interactive/xfunc-volatility.html and http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html -- Gregory Stark EnterpriseDB http://www.enterprisedb.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] Latest ecpg patch broke MSVC build
On Tue, Oct 09, 2007 at 10:00:51AM +0200, Michael Meskes wrote: On Tue, Oct 09, 2007 at 08:15:35AM +0200, Magnus Hagander wrote: In hindsight, all these ecpg changes should have been made between beta1 and beta2 when we have time to deal with the fallout, not right before beta1. This one I totally agree with. Or considered new features and held back for 8.4. Not picking on Michael, but the resemblance to the /contrib discussion is striking. Ecpg is another part of core PostgreSQL that lives by slightly different rules. But this one I don't. At least not the new features part. Had I considered the patch a new feature I wouldn't have committed it. To me it looked like a bug fix and I still see it as such. Yes, we could have documented the bug instead, but still I don't see how we could argue that getting multithreading to work on Windows is a feature when it's already working on all other platforms a and is also compilable, but not working in some/most cases, on Windows. We'retalking abuot different patches I think ;-) Things like: http://archives.postgresql.org/pgsql-committers/2007-09/msg00465.php http://archives.postgresql.org/pgsql-committers/2007-09/msg00408.php aren't win32 fixes. They're making parts of ecpg thread-safe that weren't before. And they're the ones that *caused* the win32 specific patches to be needed. That said, I'm sure one could argue they were bug-fixes, but I'm fairliy certain they would *not* be accepted as bug fixes if it were backend code. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including Snapshot Info with Indexes
[snip] In the case of User-Defined functions, the user should be defining it as Deterministic. The user CAN already define his functions as Deterministic=IMMUTABLE... the problem is that many of us will define functions as immutable, when in fact they are not. And do that by mistake... and there's nothing postgres can do about that. Can we frame a set of guidelines, or may be some test procedure, which can declare a certain function as deterministic? You mean postgres should check your function if it is really immutable ? I can't imagine any way to do it correctly in reasonable time :-) Imagine a function of 10 parameters which returns the sum of the parameters all the time except for parameters all 1 it will randomly return a value _once in a thousand executions_... please find a generic algorithm which spots this function as not immutable in reasonable execution time ;-) So this example is a bit extreme, but don't underestimate the user ;-) I am just saying from the top of my mind. Even otherwise, if we can even restrict this indexing to only Built-in deterministic functions., don't you think it would help the cause of a majority? I have just made the proposal to create the index with snapshot a optional one. Restrictions like this are always confusing for the end user (i.e. why can I use built-ins here and not my own ?). I leave to the actual coders to say anything about code maintenance concerns... Cheers, Csaba. ---(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] Timezone database changes
On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. As a general rule, when you're doing planning or calendar type applications where times need to be treated in local time, you never store them in any other form (such as UTC). If you need to work with multiple zones, you also store the timezone and do explicit conversions on demand. In database terms, that means using timestamp without time zone and some other column for the zone. Put another way, when the authoritative reference is local time and not absolute time, you don't use absolute time :) I'm sure this trips up a lot of people, but it's S.O.P. for any environment. OS services have the same caveats, and I've seen desktop apps make this mistake and have to correct it later. (PostgreSQL actually provides better support for time zones than some environments. I've seen some use the current offset for conversions of all times, which utterly breaks in the face of DST; others take DST into account, but using the current year's DST rules only.) It might be worth trying to document for PostgreSQL-using people to find, but I don't see any need for behavior changes. Or anything practical that could be done, for that matter. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended transaction ID module to contrib as
On Tue, Oct 09, 2007 at 08:20:45AM +0100, Simon Riggs wrote: On Mon, 2007-10-08 at 17:38 -0400, Robert Treat wrote: On Monday 08 October 2007 16:29, Magnus Hagander wrote: The whole contrib thing confuses a lot of users. Is it included, or isn't it? IMHO, that distinction need to be clear, and I thought we were working (if not actively then at least passively) to retire contrib, moving things either to core or to pgFoundry. Adding yet another important feature that's just in contrib is making things worse, not better. IMHO, of course ;-) +1. I felt the same way about pg_standby, which would have been far more accessible for 8.2 users had it lived on pg_foundry. I think we should move a version of pg_standby to pg_foundry anyway, specifically to support 8.2 users. Are you saying you want two versions of pg_standby, one in contrbi and one on pgfoundry, or are you saying we should take the one in contrib away? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended transaction ID module to contrib as
Did it? I see nothing for txid in relesase.sgml. Right. release.sgml will be updated in batches as we near final release. We don't update for individual commits. Ok. I will explain about txid for local users myself. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [COMMITTERS] pgsql: Added the Skytools extended transaction ID module to contrib as
On Tue, 2007-10-09 at 10:58 +0200, Magnus Hagander wrote: On Tue, Oct 09, 2007 at 08:20:45AM +0100, Simon Riggs wrote: On Mon, 2007-10-08 at 17:38 -0400, Robert Treat wrote: On Monday 08 October 2007 16:29, Magnus Hagander wrote: The whole contrib thing confuses a lot of users. Is it included, or isn't it? IMHO, that distinction need to be clear, and I thought we were working (if not actively then at least passively) to retire contrib, moving things either to core or to pgFoundry. Adding yet another important feature that's just in contrib is making things worse, not better. IMHO, of course ;-) +1. I felt the same way about pg_standby, which would have been far more accessible for 8.2 users had it lived on pg_foundry. I think we should move a version of pg_standby to pg_foundry anyway, specifically to support 8.2 users. Are you saying you want two versions of pg_standby, one in contrbi and one on pgfoundry, or are you saying we should take the one in contrib away? I would prefer that we backported pg_standby into 8.2 contrib, so the solution is where people need it to be. If not... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ECPG regression tests
On Sat, Oct 06, 2007 at 11:50:39PM -0400, Andrew Dunstan wrote: Andrew Dunstan wrote: Magnus Hagander wrote: Bingo. With that, all the ECPG regression tests now pass on MSVC builds. Andrew - please enable it for the buildfarm :-) Yes, when I have had a chance to test it. Might be a day or so. I finally managed to get this working after much wailing and gnashing of teeth and rending of hair. (Hint: if you don't put the PlatformSDK directories first in the INCLUDE and LIB lists bad and inexplicable things can happen.) Pick up the latest version of run_build.pl in CVS if you want to run this in your buildfarm animal now. A release will be forthcoming very soon. I put it in, but it doesn't work. It works when running ecpg tests manual, but from run_build I get: http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=skylarkdt=2007-10-09%20090814stg=ecpg-check which seems similar to what you had before. How did you fix that one? Is that the one requiring a reorder? //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] the best format of FAQ for you
Hello I would to manage czech FAQ with mediawiky http://www.pgsql.cz/index.php/Frequently_Asked_Questions and automaticly transform FAQ to any format. what is good format for you? I prefere plain html or DocBook? Current form of F.A.Q. is little bit obsolette. Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] mal advice in FAQ 4.1.
Hello I found lot of slow queries in some databases which I checked based on advice 4.1. from FAQ, To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; It's robust and slow on bigger tables. Can we add some better solutions? Regards Pavel Stehule ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Timezone database changes
I wrote: On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. As a general rule, when you're doing planning or calendar type applications where times need to be treated in local time, you never store them in any other form (such as UTC). If you need to work with multiple zones, you also store the timezone and do explicit conversions on demand. In database terms, that means using timestamp without time zone and some other column for the zone. Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that timestamp with time zone actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) If that's true, then the current behavior is a bug^H^H^Hdocumented limitation. I still don't know of anything practical that could be done now, but... ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] IndexTuple Structure
Hi, When i saw the IndexTuple structure, i saw that 13 bits are allocated to store the size of the Values List of the IndexTuple Structure. Also the IID(ItemId Identifier) in the Page header stores the complete size of the IndexTuple. Can't we derive the size of the IndexTuple from the IID-len? I think we will be able to save 1 byte per IndexTuple, if we can implement this from t_info. But will this really be a benefit, if we reduce this, considering the effect of MAX_ALIGN? I did a small analysis, Currently IndexTuple has a ItemId (6 Bytes) + t_info(2 Bytes) . So usually it is 8 Bytes. In this case it doesn't matter whether the t_info occupies 1 byte/2 bytes. But when Null Bitmap is included and if it is less than one byte(which it is in most of the cases), then IndexInfoFindDataOffset will return 12 bytes now (9 bytes + 3 bytes for maxalign). If we make this improvement, it will continue to be 8 bytes(saving of 4 bytes per IndexTuple) Please put forth your comments.. Thanks, Gokul.
Re: [HACKERS] Including Snapshot Info with Indexes
Csaba Nagy wrote: Can we frame a set of guidelines, or may be some test procedure, which can declare a certain function as deterministic? You mean postgres should check your function if it is really immutable ? I can't imagine any way to do it correctly in reasonable time :-) Imagine a function of 10 parameters which returns the sum of the parameters all the time except for parameters all 1 it will randomly return a value _once in a thousand executions_... please find a generic algorithm which spots this function as not immutable in reasonable execution time ;-) So this example is a bit extreme, but don't underestimate the user ;-) I think you're overly pessimistic here ;-) This classification can be done quite efficiently as long as your language is static enough. The trick is not to execute the function, but to scan the code to find all other functions and SQL statements a given function may possibly call. If your function calls no SQL statements, and only other functions already marked IMMUTABLE, then it must be IMMUTABLE itself. It does seem that only pl/pgsql is static enough for this to work, though, making this idea rather unappealing. I am just saying from the top of my mind. Even otherwise, if we can even restrict this indexing to only Built-in deterministic functions., don't you think it would help the cause of a majority? I have just made the proposal to create the index with snapshot a optional one. Restrictions like this are always confusing for the end user (i.e. why can I use built-ins here and not my own ?). I leave to the actual coders to say anything about code maintenance concerns... Yes, and some built-ins have gotten that classification wrong too in the past IIRC. Which probably is a good reason not to trust our users to get it right ;-) greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Uninformative messages from pg_ctl
These messages from pg_ctl are not useful $ pg_ctl -D nonexistent stop pg_ctl: PID file nonexistent/postmaster.pid does not exist Is server running? The message should say pg_ctl: Data Directory nonexistent does not exist $ pg_ctl -D nonexistent start postgres cannot access the server configuration file /usr/local/pgsql/nonexistent/postgresql.conf: No such file or directory server starting The message should say pg_ctl: Data Directory nonexistent does not exist and should not say server starting at all. Any objections to changing them? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] IndexTuple Structure
On 10/9/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: Hi, When i saw the IndexTuple structure, i saw that 13 bits are allocated to store the size of the Values List of the IndexTuple Structure. Also the IID(ItemId Identifier) in the Page header stores the complete size of the IndexTuple. Can't we derive the size of the IndexTuple from the IID-len? I think we will be able to save 1 byte per IndexTuple, if we can implement this from t_info. But will this really be a benefit, if we reduce this, considering the effect of MAX_ALIGN? I did a small analysis, Currently IndexTuple has a ItemId (6 Bytes) + t_info(2 Bytes) . So usually it is 8 Bytes. In this case it doesn't matter whether the t_info occupies 1 byte/2 bytes. But when Null Bitmap is included and if it is less than one byte(which it is in most of the cases), then IndexInfoFindDataOffset will return 12 bytes now (9 bytes + 3 bytes for maxalign). If we make this improvement, it will continue to be 8 bytes(saving of 4 bytes per IndexTuple) No the calculation went wrong over here. It occupies 12 bytes, if the null bitmap exists, since the null bitmap is always 4 bytes. Please put forth your comments.. Thanks, Gokul.
Re: [HACKERS] Including Snapshot Info with Indexes
I think you're overly pessimistic here ;-) This classification can be done quite efficiently as long as your language is static enough. The trick is not to execute the function, but to scan the code to find all other functions and SQL statements a given function may possibly call. If your function calls no SQL statements, and only other functions already marked IMMUTABLE, then it must be IMMUTABLE itself. OK, I have a black-box mindset right now due to the problem I'm currently working on, so I didn't even think about checking the source code of the function (which is the right thing to do if you have the source code)... in which case you're right, I was overly pessimistic :-) 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] Uninformative messages from pg_ctl
Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: These messages from pg_ctl are not useful $ pg_ctl -D nonexistent stop pg_ctl: PID file nonexistent/postmaster.pid does not exist Is server running? The message should say pg_ctl: Data Directory nonexistent does not exist Well, this objection could apply to any place where a file is being opened. I'm curious how you plan to sort out the difference, considering that open() simply returns ENOENT in both cases. -- 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] Uninformative messages from pg_ctl
On Tue, Oct 09, 2007 at 01:09:19PM +0200, Peter Eisentraut wrote: Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: These messages from pg_ctl are not useful $ pg_ctl -D nonexistent stop pg_ctl: PID file nonexistent/postmaster.pid does not exist Is server running? The message should say pg_ctl: Data Directory nonexistent does not exist Well, this objection could apply to any place where a file is being opened. I'm curious how you plan to sort out the difference, considering that open() simply returns ENOENT in both cases. You'd do opendir() on the directory part fisrt, I assume. A question I had about it is, where are we wrt translations? When do we plan string freeze? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Uninformative messages from pg_ctl
On Tue, 2007-10-09 at 13:20 +0200, Magnus Hagander wrote: On Tue, Oct 09, 2007 at 01:09:19PM +0200, Peter Eisentraut wrote: Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: These messages from pg_ctl are not useful $ pg_ctl -D nonexistent stop pg_ctl: PID file nonexistent/postmaster.pid does not exist Is server running? The message should say pg_ctl: Data Directory nonexistent does not exist Well, this objection could apply to any place where a file is being opened. I'm curious how you plan to sort out the difference, considering that open() simply returns ENOENT in both cases. You'd do opendir() on the directory part fisrt, I assume. Yes, so we catch the real error. A question I had about it is, where are we wrt translations? When do we plan string freeze? Not one day after Beta1, I presume. We would keep the pid does not exist error because it still might be true that we have a data directory, but no pid file. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Timezone database changes
Trevor Talbot wrote: Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that timestamp with time zone actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) SQL itself doesn't say anything how the data element should be stored, only how it should be operated upon. It do, however,say that a datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003, §4.3). All operations on the element are defined as if it's an instance in time (in UTC). Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character value, it should be converted with the _original_ time zone value (SQL 2003, §5.8) _unless_ you specify AT LOCAL. In the database, it makes sense to store the time instance in UTC (for efficiency) and only apply the offset for presentation. --Magne ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Uninformative messages from pg_ctl
Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: On Tue, 2007-10-09 at 13:20 +0200, Magnus Hagander wrote: On Tue, Oct 09, 2007 at 01:09:19PM +0200, Peter Eisentraut wrote: Well, this objection could apply to any place where a file is being opened. I'm curious how you plan to sort out the difference, considering that open() simply returns ENOENT in both cases. You'd do opendir() on the directory part fisrt, I assume. Yes, so we catch the real error. Note that opendir() requires different permissions than reading or writing a file in that directory. So you might in fact be catching the wrong error. stat() might work better, but I'm not sure. You would also have to cope with the directory structure changing as you traverse it. Also consider the effort required to slice apart directory names in a portable way and iterate and catch all these problems. This could at best be used in a limited number of places where pilot errors are common. I believe, however, that this approach is wrong. The real error, as you put it, is the one reported by the kernel -- by definition. Everything else is at best a hint. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] some points for FAQ
4.1)
To SELECT a random row, use:
SELECT col
FROM tab
ORDER BY random()
LIMIT 1;
+ On bigger tables this solution is slow. Please, find smarter
solution on network.
4.6)
ILIKE is slow, specially on multibyte encodings. If is possible use
FULLTEXT. LIKE '%some%' is slow always .. thing about FULLTEXT.
4.11.2)
+ Alternatively (on PostgreSQL 8.2.0 and all later releases) you could
RETURNING clause for retrieving used SERIAL value, e.g.,
new_id = execute(SELECT INSERT INTO person(name) VALUES('Blaise
Pascal') RETURNING id);
4.19)
+ most of problems with invalid OIDs in cache are solved in PostgreSQL
8.3. Please remeber, so every replanning of SQL statements needs time.
Write your application, they can exist without cache invalidation.
I am sorry, I am not able create patch via my minimalistic english knowleage.
Regards
Pavel Stehule
p.s. can we create some general F.A.Q XML format and store FAQ there?
WIP Proposal:
faq name = . language =
entry number=1.1.1
query/query
ansver
...
we need some tags from html: pbraibullitable
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Timezone database changes
Am Dienstag, 9. Oktober 2007 schrieb Magne Mæhre: SQL itself doesn't say anything how the data element should be stored, only how it should be operated upon. It do, however,say that a datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003, §4.3). All operations on the element are defined as if it's an instance in time (in UTC). There is, generally, a significant mismatch between the time zone handling specified in SQL and practical requirements. More specifically, SQL only supports time zones with fixed offsets and does not support daylight-saving time rules at all. Independent of what any specification might say, however, the currently implemented behavior is clearly wrong in my mind and needs to be fixed. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] mal advice in FAQ 4.1.
Hubert recently posted his thoughts on this topic: http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ I've encountered with this problem several times in web development and every time found out that the best (in terms of performance) solution is to use some pseudo random approach (such as = random() limit 1 or limit 1 offset random()*N or even pre-caching rows on app side). On 10/9/07, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found lot of slow queries in some databases which I checked based on advice 4.1. from FAQ, To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; It's robust and slow on bigger tables. Can we add some better solutions? -- Best regards, Nikolay
Re: [HACKERS] mal advice in FAQ 4.1.
2007/10/9, Nikolay Samokhvalov [EMAIL PROTECTED]: Hubert recently posted his thoughts on this topic: http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ I've encountered with this problem several times in web development and every time found out that the best (in terms of performance) solution is to use some pseudo random approach (such as = random() limit 1 or limit 1 offset random()*N or even pre-caching rows on app side). I know this article, but you cannot link from faq to private (unstable) blog. it would article on techdoc.postgresql.org Pavel On 10/9/07, Pavel Stehule [EMAIL PROTECTED] wrote: Hello I found lot of slow queries in some databases which I checked based on advice 4.1. from FAQ, To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; It's robust and slow on bigger tables. Can we add some better solutions? -- Best regards, Nikolay ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] permission denied for tablespace pg_global?
Hi. I'm looking at the strange phenomenon -- C:\Program Files\PostgreSQL\8.3-beta1\binpsql postgres postgres Password for user postgres: Welcome to psql 8.3beta1, 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 postgres=# select *,oid from pg_catalog.pg_tablespace; spcname | spcowner | spclocation | spcacl | oid +--+-++-- pg_default | 10 | || 1663 pg_global | 10 | || 1664 (2 rows) postgres=# SELECT pg_size_pretty(pg_tablespace_size(1663)); pg_size_pretty 13 MB (1 row) postgres=# SELECT pg_size_pretty(pg_tablespace_size(1664)); ERROR: permission denied for tablespace pg_global postgres=# /END Have I overlooked the present specification change? or can someone suggest? Regards, Hiroshi Saito ---(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] the best format of FAQ for you
On Tuesday 09 October 2007 05:55, Pavel Stehule wrote:
Hello
I would to manage czech FAQ with mediawiky
http://www.pgsql.cz/index.php/Frequently_Asked_Questions
and automaticly transform FAQ to any format.
what is good format for you? I prefere plain html or DocBook?
Current form of F.A.Q. is little bit obsolette.
The FAQ's *are* managed in html, though we also keep a spare copy as plain
text for historical reasons. See doc/src/FAQ/
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Uninformative messages from pg_ctl
On Tue, 2007-10-09 at 14:25 +0200, Peter Eisentraut wrote: Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: On Tue, 2007-10-09 at 13:20 +0200, Magnus Hagander wrote: On Tue, Oct 09, 2007 at 01:09:19PM +0200, Peter Eisentraut wrote: Well, this objection could apply to any place where a file is being opened. I'm curious how you plan to sort out the difference, considering that open() simply returns ENOENT in both cases. You'd do opendir() on the directory part fisrt, I assume. Yes, so we catch the real error. Note that opendir() requires different permissions than reading or writing a file in that directory. So you might in fact be catching the wrong error. stat() might work better, but I'm not sure. Yeh, I presumed he was speaking Windows You would also have to cope with the directory structure changing as you traverse it. No directory. pid file is in data directory root We'll still fail with the old error if anything changes Also consider the effort required to slice apart directory names in a portable way and iterate and catch all these problems. This could at best be used in a limited number of places where pilot errors are common. Nothing to do. The -D option supplies the data dir name, we add the pid file name on top, so no munging required. I believe, however, that this approach is wrong. The real error, as you put it, is the one reported by the kernel -- by definition. Everything else is at best a hint. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] some points for FAQ
Pavel Stehule escribió: p.s. can we create some general F.A.Q XML format and store FAQ there? WIP Proposal: faq name = . language = entry number=1.1.1 query/query ansver ... we need some tags from html: pbraibullitable There is a DocBook spec for FAQ lists. Actually a friend of mine was working on converting our FAQ into that kind of XML. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 God is real, unless declared as int ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] some points for FAQ
2007/10/9, Alvaro Herrera [EMAIL PROTECTED]: Pavel Stehule escribió: p.s. can we create some general F.A.Q XML format and store FAQ there? WIP Proposal: faq name = . language = entry number=1.1.1 query/query ansver ... we need some tags from html: pbraibullitable There is a DocBook spec for FAQ lists. Actually a friend of mine was working on converting our FAQ into that kind of XML. I'll look on it Pavel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Timezone database changes
On 10/9/07, Peter Eisentraut [EMAIL PROTECTED] wrote: Independent of what any specification might say, however, the currently implemented behavior is clearly wrong in my mind and needs to be fixed. I don't think it's wrong, just a particular choice. As an example, consider an interval scheduling system that handles everything in absolute time (UTC), but uses local time as a convenience. Perhaps it presents a timestamp a few months from now to the user, and accepts any stamp back in the user's timezone. When the DST rules suddenly change a couple weeks before that timestamp occurs, you don't want the database changing its interpretation of what was entered months ago; the absolute time is already the correct time. That's simply a specific version of the general case of wanting the database to operate in absolute time, and present local time as a user convenience. Conveniently, PostgreSQL does exactly that now. If that behavior changes, making the above work anyway is easy: explicitly convert to UTC on input. But that's just a counterpoint to what I mentioned earlier in the thread, explicit conversion of local times. Either way, someone has to do some work to adapt to their specific usage, so which method the database naturally uses is just an arbitrary choice. FWIW, I am in favor of having it [behave as if it does] store the literal time and its associated zone. To me that seems smart, consistent, and more likely to fit what people need. I don't see it as fixing wrong behavior, though. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Timezone database changes
On Mon, Oct 8, 2007 at 10:48 PM, in message [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. It depends. It's what you want if you are looking to point your telescope to the right part of the sky or to be on an international conference call which isn't going to be rescheduled because of New Zealand's daylight saving time rules; but, as you point out, not usually what you want for a local appointment. We use TIMESTAMP WITH TIME ZONE to capture a moment in the natural stream of time, and separate DATE and TIME WITHOUT TIME ZONE to capture local appointments. I believe this gives the desired behavior both with ANSI/ISO standard behavior and with the PostgreSQL implementation. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Timezone database changes
On Tue, Oct 9, 2007 at 6:49 AM, in message [EMAIL PROTECTED], Magne Mæhre [EMAIL PROTECTED] wrote: Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character value, it should be converted with the _original_ time zone value (SQL 2003, *5.8) _unless_ you specify AT LOCAL. A lot of the ANSI/ISO behavior is broken if TIMESTAMP WITH TIME ZONE does not include the time zone. One of the least standards compliant areas of PostgreSQL is the date/time arithmetic; but any attempt to implement the standard date math will fail until the time zone is part of the WITH TIME ZONE data types. -Kevin ---(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] Timezone database changes
Am Dienstag, 9. Oktober 2007 schrieb Trevor Talbot: I don't think it's wrong, just a particular choice. As an example, consider an interval scheduling system that handles everything in absolute time (UTC), but uses local time as a convenience. We are not considering an interval scheduling system, we are considering a database system. Such a system should have the basic property that if you store A, it will read out as A. The money type is similarly buggy: if you change the locale, the semantic value of the data changes. With money type, the problem is obvious and easy to recognize. With the timestamp with time zone type, however, the problem is much more subtle and will likely go unnoticed by many who will then be unpunctual for their appointments. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Uninformative messages from pg_ctl
On Tue, 2007-10-09 at 14:25 +0200, Peter Eisentraut wrote: I believe, however, that this approach is wrong. The real error, as you put it, is the one reported by the kernel -- by definition. Everything else is at best a hint. Are you objecting to making the wording of the hint/error clearer? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Including Snapshot Info with Indexes
Csaba Nagy wrote: You mean postgres should check your function if it is really immutable ? I can't imagine any way to do it correctly in reasonable time :-) I would say that in the general case it's analogous to the halting problem, not solvable at all let alone in any reasonable time. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] type money causes unrestorable dump
On Mon, 8 Oct 2007 20:02:56 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: The money data type has been deprecated for years. It is completely non standard and essentially duplicative of numeric/decimal. What is the point? It may be deprecated (maybe not) and it may have drawbacks but it is not a duplication of numeric or decimal. While numeric/decimal may be faster for I/O, money is faster for doing large sums. Depending on your needs it does have an advantage over numeric. That said, I wonder if there is another answer to this question. Perhaps the functions in cash.c can be pulled out and made into external functions that can be fed an int (long) and output the desired format. That way we could use the existing int or long type but convert manually on I/O. Let people choose whether they want the simplification of the money type or the standardization allowed by just using the functions. Just a thought. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ECPG regression tests
Magnus Hagander wrote: (Hint: if you don't put the PlatformSDK directories first in the INCLUDE and LIB lists bad and inexplicable things can happen.) Pick up the latest version of run_build.pl in CVS if you want to run this in your buildfarm animal now. A release will be forthcoming very soon. I put it in, but it doesn't work. It works when running ecpg tests manual, but from run_build I get: http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=skylarkdt=2007-10-09%20090814stg=ecpg-check which seems similar to what you had before. How did you fix that one? Is that the one requiring a reorder? Yes. compare its build_env INCLUDE and LIB and possibly PATH values with those of red_bat: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=red_batdt=2007-10-09%2004:30:04 BTW, following some advice I found on the net those PlatformSDK directories were copied manually from the SDK install, back when I installed MSVC. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including Snapshot Info with Indexes
Florian G. Pflug wrote: I think you're overly pessimistic here ;-) This classification can be done quite efficiently as long as your language is static enough. The trick is not to execute the function, but to scan the code to find all other functions and SQL statements a given function may possibly call. If your function calls no SQL statements, and only other functions already marked IMMUTABLE, then it must be IMMUTABLE itself. It does seem that only pl/pgsql is static enough for this to work, though, making this idea rather unappealing. How would you propose to analyse C functions, for which you might not have the C code? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Including Snapshot Info with Indexes
On Tue, 2007-10-09 at 11:22 -0400, Andrew Dunstan wrote: Csaba Nagy wrote: You mean postgres should check your function if it is really immutable ? I can't imagine any way to do it correctly in reasonable time :-) I would say that in the general case it's analogous to the halting problem, not solvable at all let alone in any reasonable time. In the light of Florian's mail, I would say that in the context of a language which can check each of it's constructs if it is immutable or not, a procedure using only immutable constructs should be itself immutable... the halting problem is avoided in that you don't really need to know if/how the procedure works, you only need to know that it will always work the same ;-) The problem is that in the general case the languages don't have available checks for this kind of thing, so either you restrict the immutability check to simple languages (static enough as Florian would say) or you must allow the user to decide if the function is immutable or not. In the general case I assume the users will want the power to decide (and potentially be wrong), and will expect that if they do mistake, the result won't be catastrophic. I guess this is the same conclusion as in previous threads about the subject... Cheers, Csaba. ---(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] Timezone database changes
On Tue, Oct 09, 2007 at 05:04:39PM +0200, Peter Eisentraut wrote: We are not considering an interval scheduling system, we are considering a database system. Such a system should have the basic property that if you store A, it will read out as A. The money type is similarly buggy: if you change the locale, the semantic value of the data changes. With money type, the problem is obvious and easy to recognize. With the timestamp with time zone type, however, the problem is much more subtle and will likely go unnoticed by many who will then be unpunctual for their appointments. For both money and timestamps the taggedtypes module provides exactly what you want. It stores the timezone/currency as entered and displays that when output. Sometimes that's what you want, sometimes it's not. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Including Snapshot Info with Indexes
Andrew Dunstan wrote:
Florian G. Pflug wrote:
I think you're overly pessimistic here ;-) This classification can be done
quite efficiently as long as your language is static enough. The trick is
not to execute the function, but to scan the code to find all other
functions and SQL statements a given function may possibly call. If your
function calls no SQL statements, and only other functions already marked
IMMUTABLE, then it must be IMMUTABLE itself.
It does seem that only pl/pgsql is static enough for this to work,
though, making this idea rather unappealing.
How would you propose to analyse C functions, for which you might not have
the C code?
Scanning the binary, together with symbol annotations for immutability of course
;-))
No, seriously. I do *not* advocate that we actually autoclassify functions, for
a lot of reasons. I just wanted to refute the statement that doing so is
generally impossible - it's not. It's trivial for some languages (In haskhell
for example all functions that don't use monads are immutable, and their
signature tell if they do use monads or or), realistic for others (pl/pgsql,
where we do have the sourcecode), and utterly impossible for others
(pl/{ruby,python,perl,...}, pl/c, ...).
Besides - AFAICS *anything* that makes VACUUM depend on IMMUTABLE to be correct
would instantly break tsearch, no? At least as long as we allow changing
stopwords and the like of dictionaries used by an index - which we'd better
allow, unless we want the DBAs to come with pitchforks after us...
regards, Florian Pflug, who shudders when imagining DBAs with pitchforks...
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] type money causes unrestorable dump
On Tue, 9 Oct 2007 11:26:16 -0400 D'Arcy J.M. Cain [EMAIL PROTECTED] wrote: On Mon, 8 Oct 2007 20:02:56 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: The money data type has been deprecated for years. It is completely non standard and essentially duplicative of numeric/decimal. What is the point? It may be deprecated (maybe not) and it may have drawbacks but it is not a duplication of numeric or decimal. While numeric/decimal may be faster for I/O, money is faster for doing large sums. Depending on your needs it does have an advantage over numeric. Fair enough.. However, keep in mind that I really don't care if Money is deprecated or not. I do care that the docs say it is, and it may not be. :) Joshua D. Drake That said, I wonder if there is another answer to this question. Perhaps the functions in cash.c can be pulled out and made into external functions that can be fed an int (long) and output the desired format. That way we could use the existing int or long type but convert manually on I/O. Let people choose whether they want the simplification of the money type or the standardization allowed by just using the functions. Just a thought. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [HACKERS] mal advice in FAQ 4.1.
Nikolay Samokhvalov [EMAIL PROTECTED] writes: Hubert recently posted his thoughts on this topic: http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ I've encountered with this problem several times in web development and every time found out that the best (in terms of performance) solution is to use some pseudo random approach (such as = random() limit 1 or limit 1 offset random()*N or even pre-caching rows on app side). ORDER BY random() LIMIT 1 should be faster in 8.3 due to the bounded-sort optimization. It should be basically the same as the two options above as far as how many comparisons are done and how much memory is used. It does have to call random() for every record whereas the solutions above only call random() once. But I think all of these are basically the same to a first degree approximation. They all have to do a scan of all the records being considered. If you want something faster you need a solution which can use an index to scan only the target record. There are ways of doing that but they require some application knowledge. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Uninformative messages from pg_ctl
Am Dienstag, 9. Oktober 2007 schrieb Simon Riggs: On Tue, 2007-10-09 at 14:25 +0200, Peter Eisentraut wrote: I believe, however, that this approach is wrong. The real error, as you put it, is the one reported by the kernel -- by definition. Everything else is at best a hint. Are you objecting to making the wording of the hint/error clearer? Not at all. I'm just trying to point out that it's not quite as obvious as it may seem. Let's see a patch. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
OS: Windows XP Pro SP2 CPU: AMD Athlon 64 3500+ RAM: 2GB DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400 I've come to the conclusion that it seems like a deadlock occurs when dropping a column in a table the same moment that table is autovacuumed. Example: ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records) deadlocks with VACUUM ANALYZE public.bondetail; (user=postgres) If you wait a very long time, it goes on, the quick method is to cancel the VACUUM command. If you need some more info, let me know. Greetings Deblauwe Gino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] some points for FAQ
[EMAIL PROTECTED] (Alvaro Herrera) writes: Pavel Stehule escribió: p.s. can we create some general F.A.Q XML format and store FAQ there? WIP Proposal: faq name = . language = entry number=1.1.1 query/query ansver ... we need some tags from html: pbraibullitable There is a DocBook spec for FAQ lists. Actually a friend of mine was working on converting our FAQ into that kind of XML. Yup, the structure is known as a qandaset http://www.docbook.org/tdg/en/html/qandaset.html There is an example of this in the Slony-I docs - the admin guide has a FAQ defined using qandaset and its children. -- cbbrowne,@,acm.org http://www3.sympatico.ca/cbbrowne/faq.html All extremists should be taken out and shot. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] type money causes unrestorable dump
[Note: Cc list trimmed as everyone is probably on the list anyway] On Tue, 9 Oct 2007 09:02:09 -0700 Joshua D. Drake [EMAIL PROTECTED] wrote: However, keep in mind that I really don't care if Money is deprecated or not. I do care that the docs say it is, and it may not be. :) Understood. Personally I would like to see that comment dropped but it isn't my decision. I did fix the biggest complaints in the recent change to 64 bit storage so perhaps someone can see their way clear to dropping that comment. Here is a suggested change. Index: datatype.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v retrieving revision 1.209 diff -u -p -u -r1.209 datatype.sgml --- datatype.sgml 31 Aug 2007 04:52:29 - 1.209 +++ datatype.sgml 9 Oct 2007 16:30:13 - @@ -828,14 +828,6 @@ ALTER SEQUENCE replaceable class=param sect1 id=datatype-money titleMonetary Types/title - note -para - The typemoney/type type is deprecated. Use - typenumeric/type or typedecimal/type instead, in - combination with the functionto_char/function function. -/para - /note - para The typemoney/type type stores a currency amount with a fixed fractional precision; see xref @@ -846,6 +838,15 @@ ALTER SEQUENCE replaceable class=param Output is generally in the latter form but depends on the locale. /para + para +Due to locale changes this type may have problems with dump and +restore and care should be taken. Also, I/O is slower than using +NUMERIC or DECIMAL but internal calculations such as SUM will be +faster due to its internal storage format. Be sure to weigh all +these considerations as well as convenience when choosing between +this type and others. + /para + table id=datatype-money-table titleMonetary Types/title tgroup cols=4 -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] mal advice in FAQ 4.1.
2007/10/9, Gregory Stark [EMAIL PROTECTED]: Nikolay Samokhvalov [EMAIL PROTECTED] writes: Hubert recently posted his thoughts on this topic: http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/ I've encountered with this problem several times in web development and every time found out that the best (in terms of performance) solution is to use some pseudo random approach (such as = random() limit 1 or limit 1 offset random()*N or even pre-caching rows on app side). ORDER BY random() LIMIT 1 should be faster in 8.3 due to the bounded-sort optimization. It should be basically the same as the two options above as far as how many comparisons are done and how much memory is used. It does have to call random() for every record whereas the solutions above only call random() once. But I think all of these are basically the same to a first degree approximation. They all have to do a scan of all the records being considered. If you want something faster you need a solution which can use an index to scan only the target record. There are ways of doing that but they require some application knowledge. It needs always seq scan :(, and take space on buffer cache. Solution based on random generated PK are much faster. I collaborate with one my customer. He shows random products from 10K products on every page of one eShop. And he cannot understand, so ORDER random() LIMIT is bad trick, because this trick is on PostgreSQL FAQ. Pavel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
Deblauwe Gino wrote: OS: Windows XP Pro SP2 CPU: AMD Athlon 64 3500+ RAM: 2GB DB: PostgreSQL 8.3beta1, compiled by Visual C++ build 1400 I've come to the conclusion that it seems like a deadlock occurs when dropping a column in a table the same moment that table is autovacuumed. Example: ALTER TABLE bondetail DROP COLUMN btw; (user=gino, 16252 records) deadlocks with VACUUM ANALYZE public.bondetail; (user=postgres) Does it really deadlock, or is it just locked waiting for the vacuum to finish? If it deadlocks you should get a message about it and a transaction rollback. Otherwise you should be able to see the ungranted lock in pg_locks. Also it's not clear if autovacuum is involved, or you invoked the VACUUM ANALYZE manually. Can you clarify? -- 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] type money causes unrestorable dump
Am Dienstag, 9. Oktober 2007 schrieb D'Arcy J.M. Cain: + Due to locale changes this type may have problems with dump and + restore and care should be taken. With respect, this kind of advice is useless. What are the problems, when do they occur, and what should be done about them? We do know the answers to all of these questions. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone database changes
Trevor Talbot wrote: I wrote: On 10/8/07, Bruce Momjian [EMAIL PROTECTED] wrote: I had a thought a week ago. If we update the time zone database for future dates, and you have a future date/time stored, doesn't the time change when the time zone database changes. For example if I schedule an appointment in New Zealand for 10:00a and we change the time zone database so that date is now daylight savings, doesn't the time change to display as 9 or 11am? That seems pretty bad. As a general rule, when you're doing planning or calendar type applications where times need to be treated in local time, you never store them in any other form (such as UTC). If you need to work with multiple zones, you also store the timezone and do explicit conversions on demand. In database terms, that means using timestamp without time zone and some other column for the zone. Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that timestamp with time zone actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) If that's true, then the current behavior is a bug^H^H^Hdocumented limitation. I still don't know of anything practical that could be done now, but... Do we need additional documention about this? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] type money causes unrestorable dump
On Tue, 9 Oct 2007 19:02:38 +0200 Peter Eisentraut [EMAIL PROTECTED] wrote: Am Dienstag, 9. Oktober 2007 schrieb D'Arcy J.M. Cain: + Due to locale changes this type may have problems with dump and + restore and care should be taken. With respect, this kind of advice is useless. What are the problems, when do they occur, and what should be done about them? We do know the answers to all of these questions. Right. How about this: ... restore and care should be taken when dumping and reloading from different locales. To avoid problems always explicitely set your locale before both a dump and reload and make sure that they are identical. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. No it's not. Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Timezone database changes
On Tue, Oct 9, 2007 at 12:11 PM, in message [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote: Trevor Talbot wrote: Actually, I'm used to knowing how PostgreSQL does it, but looking at things again I remember some confusion I had when first encountering the timestamp types. I don't know what the SQL Standard says; is the implication that timestamp with time zone actually stores the literal time and the zone it is associated with? (Would make more sense, given the name.) I don't see how the ANSI functionality can work without it. If that's true, then the current behavior is a bug^H^H^Hdocumented limitation. I still don't know of anything practical that could be done now, but... Do we need additional documention about this? Probably, but we need a lot more than that to conform to the standard and to avoid surprising behavior. The first of the two statements below is valid ANSI syntax to add one day to the current moment. It is accepted and generates the wrong value. The second is the PostgreSQL way. It is one of many anomalies. bigbird= select current_timestamp, current_timestamp + interval '1' day; now | ?column? ---+--- 2007-10-09 12:47:18.876498-05 | 2007-10-09 12:47:18.876498-05 (1 row) bigbird= select current_timestamp, current_timestamp + interval '1 day'; now | ?column? ---+--- 2007-10-09 12:47:20.190999-05 | 2007-10-10 12:47:20.190999-05 (1 row) -Kevin ---(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] type money causes unrestorable dump
D'Arcy J.M. Cain [EMAIL PROTECTED] writes: That said, I wonder if there is another answer to this question. Perhaps the functions in cash.c can be pulled out and made into external functions that can be fed an int (long) and output the desired format. That way we could use the existing int or long type but convert manually on I/O. Let people choose whether they want the simplification of the money type or the standardization allowed by just using the functions. Long term I liked the idea from a few years ago of having a default format which would be attached to a column just like a default collation can be attached. Then you can declare your currency columns as regular integers but mark them as being formatted as currency by default. pg_dump would presumably explicitly override the default and format the integers as plain integers and restore the default format string as part of its DDL. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
Andrew Dunstan wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. No it's not. Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? Yeah, I'd like to know that as well. And specifically what kind of stuff it is that's ok... If nothing else, that'll be good to know for packagers. Due to the addition of this module we'll have to make code changes that aren't just bugfixes to the win32 installer for example, which is also in feature freeze... //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] mal advice in FAQ 4.1.
On Tue, Oct 09, 2007 at 06:40:23PM +0200, Pavel Stehule wrote: It needs always seq scan :(, and take space on buffer cache. Solution based on random generated PK are much faster. I collaborate with one my customer. He shows random products from 10K products on every page of one eShop. And he cannot understand, so ORDER random() LIMIT is bad trick, because this trick is on PostgreSQL FAQ. It's the only trick that works in all situations though. There are ofcourse faster methods, but they require information about the distribution of the values, the type, PKs, indexes etc... The standard does have stuff relating to extracting samples from tables, but they're not implemented. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] mal advice in FAQ 4.1.
2007/10/9, Martijn van Oosterhout [EMAIL PROTECTED]: On Tue, Oct 09, 2007 at 06:40:23PM +0200, Pavel Stehule wrote: It needs always seq scan :(, and take space on buffer cache. Solution based on random generated PK are much faster. I collaborate with one my customer. He shows random products from 10K products on every page of one eShop. And he cannot understand, so ORDER random() LIMIT is bad trick, because this trick is on PostgreSQL FAQ. It's the only trick that works in all situations though. There are ofcourse faster methods, but they require information about the distribution of the values, the type, PKs, indexes etc... The standard does have stuff relating to extracting samples from tables, but they're not implemented. I agree. I don't wont to remove it from FAQ. I would to add note, so sometimes is necessary to find other trick. Regards Pavel ---(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] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 17:33 -0400, Alvaro Herrera wrote: Simon Riggs escribió: Seems like we don't need to mess with the deadlock checker itself. We can rely on the process at the head of the lock wait queue to sort this out for us. So all we need do is look at the isAutovacuum flag on the process that is holding the lock we're waiting on. If it isn't an autoANALYZE we can carry on with the main deadlock check. We just need a new kind of deadlock state to handle this, then let ProcSleep send SIGINT to the autoANALYZE and then go back to sleep, waiting to be reawoken when the auotANALYZE aborts. Ok, I think this makes sense. I can offer the following patch -- it makes it possible to determine whether an autovacuum process is doing analyze or not, by comparing the PGPROC of the running WorkerInfo list (the list has at most max_autovacuum_workers entries, so this is better than trolling ProcGlobal). OK, I've got this working now. It successfully handles this test case, which trips up on an auto ANALYZE every time I run it. -- drop table a; create table a as select generate_series(1,100)::integer as col1; alter table a alter column col1 type bigint; alter table a alter column col1 type bigint; alter table a alter column col1 type bigint; alter table a alter column col1 type bigint; alter table a alter column col1 type bigint; -- I think there may be a cleaner implementation, so I'll clean it up and post tomorrow. Few thoughts: Why do we run all of the ANALYZEs in a single big transaction? That seems like it could be the cause of many problems. ANALYZE specifically holds locks until EOXact, so I'd recommend we start a new transaction for each one. What do you think? I notice when we cancel an AV worker it always says cancelling autovacuum of table, even when its just an ANALYZE. Wasn't important before but now looks a little strange. If you want to commit this patch, I'll layer mine over the top. Any other input anyone? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] some points for FAQ
Pavel Stehule wrote:
4.1)
To SELECT a random row, use:
SELECT col
FROM tab
ORDER BY random()
LIMIT 1;
+ On bigger tables this solution is slow. Please, find smarter
solution on network.
Well, give me a better example that works.
4.6)
ILIKE is slow, specially on multibyte encodings. If is possible use
FULLTEXT. LIKE '%some%' is slow always .. thing about FULLTEXT.
I added a mention of full text indexing for word searches.
4.11.2)
+ Alternatively (on PostgreSQL 8.2.0 and all later releases) you could
RETURNING clause for retrieving used SERIAL value, e.g.,
new_id = execute(SELECT INSERT INTO person(name) VALUES('Blaise
Pascal') RETURNING id);
Agreed. I have updated the text to suggest RETURNING be used and
reduced the other examples. The web site should have the updated
content shortly but CVS will have FAQ.html as well soon.
4.19)
+ most of problems with invalid OIDs in cache are solved in PostgreSQL
8.3. Please remeber, so every replanning of SQL statements needs time.
Write your application, they can exist without cache invalidation.
Agreed. Item removed.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 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] Timezone database changes
Kevin Grittner wrote: Probably, but we need a lot more than that to conform to the standard and to avoid surprising behavior. The first of the two statements below is valid ANSI syntax to add one day to the current moment. That's the lack of standard interval support, which is an entirely separate issue. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Skytools committed without hackers discussion/review
Andrew Dunstan wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. No it's not. Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Skytools committed without hackers discussion/review
Jan Wieck wrote: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Doing it the way you did with no warning, right before beta, and then leaving is the worse of all times. I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. This is not the first time you have had trouble with patches. There was an issue with your patch of February, 2007: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00385.php That email might contain the keyword COMMIT, but it doesn't have to do with anything I committed to CVS. The trigger changes you are referring to have been discussed and a patch for discussion was presented here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00146.php Right, but at the time you didn't want to give a good explaination and I had to ask for it. That should not have been necessary. (In summary, you had to be coaxed to explain your patch to the community.) Basically, I am not sure you understand the process that has to be followed, or feel you are somehow immune from following it. I don't see how you leap from the above example to that conclusion. You have had only a few commits in 2007, and there have been two problems. That ratio seems too high to me, hence my questions above. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Locale + encoding combinations
I'm working on some code for pgInstaller that will check the locale and encoding selected by the user are a valid combination. The changes recently added to initdb (which highlighted the UTF-8 issue on Windows that Tom posted about) appear to only allow the default encoding for the locale to be selected. For example, for me that would be: English_United Kingdom.1252 However, setlocale() will also accept other valid combinations on Windows, which initdb will not, for example: English_United Kingdom.28591 (Latin1) Is there any reason not to accept other combinations that setlocale() is happy with? Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Andrew Dunstan wrote: Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. I think we should be looking long and hard at that. I can't see any justification for it at all. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. No it's not. Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. the postgresql ecosystem is growing and there is a lot of people like packagers that will be a quite irritated if we keep randomly adding completely new code and modules during BETA. Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. Why? When people were complaining about not being able to use TSearch because their ISPs wouldn't install contrib modules we couldn't understand why they would think that way. If we are going to be less stringent about /contrib, maybe they were right to cautious. /D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Skytools committed without hackers discussion/review
Dave Page wrote: Bruce Momjian wrote: Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. Why? When people were complaining about not being able to use TSearch because their ISPs wouldn't install contrib modules we couldn't understand why they would think that way. If we are going to be less stringent about /contrib, maybe they were right to cautious. The idea is /contrib isn't installed by default and it isn't tied into the core code, and can be tested easier because it is stand-alone. We can rethink that logic but that has been the guide in the past. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Skytools committed without hackers discussion/review
Magnus Hagander wrote: Andrew Dunstan wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Uh, it *was* after beta1. Oh, so it didn't hold up beta1 --- that's good. No it's not. Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? Yeah, I'd like to know that as well. And specifically what kind of stuff it is that's ok... I hate to say this - but this adding completely new steff after or immediatly before beta business really scares the hell out of me and somewhat starts to resemble the mysql way of adding new features at will and even during stable release trains ... There is no point in having any kind of feature freeze or even a PatchStatus Page if we keep adding stuff (as useful as it might be) that late in the cycle ... Stefan ---(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] Skytools committed without hackers discussion/review
On 10/9/2007 1:06 AM, Bruce Momjian wrote: Jan Wieck wrote: On 10/8/2007 1:34 PM, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Marko Kreen wrote: Because of the bad timing it would have been -core call anyway whether it gets in or not so Jan asked -core directly. That's my explanation about what happened, obviously Jan and Tom have their own opinion. Right. I can see your point, but it's my understanding that -hackers is really the ones supposed to decide on this. It would ultimately have been core's decision, but the discussion should have happened on -hackers. There was no reason for it to be private. That blame certainly belongs to me and I apologize for jumping that and adding it to contrib without any -hackers discussion. It is definitely a timing issue since I write this very email from JFK, boarding a flight to Hong Kong in less than an hour and will be mostly offline for the rest of the week. I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Doing it the way you did with no warning, right before beta, and then leaving is the worse of all times. I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. This is not the first time you have had trouble with patches. There was an issue with your patch of February, 2007: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00385.php That email might contain the keyword COMMIT, but it doesn't have to do with anything I committed to CVS. The trigger changes you are referring to have been discussed and a patch for discussion was presented here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00146.php (In summary, you had to be coaxed to explain your patch to the community.) Basically, I am not sure you understand the process that has to be followed, or feel you are somehow immune from following it. I don't see how you leap from the above example to that conclusion. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Dave Page wrote: Bruce Momjian wrote: Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. Why? When people were complaining about not being able to use TSearch because their ISPs wouldn't install contrib modules we couldn't understand why they would think that way. If we are going to be less stringent about /contrib, maybe they were right to cautious. The idea is /contrib isn't installed by default and it isn't tied into the core code, and can be tested easier because it is stand-alone. We can rethink that logic but that has been the guide in the past. I think you just outlined a whole lot of arguments for pgfoundry, and not for contrib. //Magnus ---(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] some points for FAQ
2007/10/9, Bruce Momjian [EMAIL PROTECTED]: Pavel Stehule wrote: 4.1) To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; + On bigger tables this solution is slow. Please, find smarter solution on network. Well, give me a better example that works. Better universal solution doesn't exist. Exists only unelegant solutions - but mutch faster. SELECT id, ... FROM data WHERE id = ANY(ARRAY( SELECT (random()*:max_id)::int FROM generate_series(1,20))) LIMIT 1; max_id is host variable ~ real max id + some -- fast solution if id is PK of data 4.19) + most of problems with invalid OIDs in cache are solved in PostgreSQL 8.3. Please remeber, so every replanning of SQL statements needs time. Write your application, they can exist without cache invalidation. Agreed. Item removed. Cache invalidation isn't 100% protection before this error message. With specific using of EXECUTE statement, you can get this message too. But all temp tables related problems are solved. Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] some points for FAQ
Pavel Stehule wrote: 2007/10/9, Bruce Momjian [EMAIL PROTECTED]: Pavel Stehule wrote: 4.1) To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; + On bigger tables this solution is slow. Please, find smarter solution on network. Well, give me a better example that works. Better universal solution doesn't exist. Exists only unelegant solutions - but mutch faster. SELECT id, ... FROM data WHERE id = ANY(ARRAY( SELECT (random()*:max_id)::int FROM generate_series(1,20))) LIMIT 1; max_id is host variable ~ real max id + some -- fast solution if id is PK of data Right. We really only want general solutions in the FAQ. 4.19) + most of problems with invalid OIDs in cache are solved in PostgreSQL 8.3. Please remeber, so every replanning of SQL statements needs time. Write your application, they can exist without cache invalidation. Agreed. Item removed. Cache invalidation isn't 100% protection before this error message. With specific using of EXECUTE statement, you can get this message too. But all temp tables related problems are solved. OK, let's see how many bug reports we get and we can always re-add it. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
Bruce Momjian wrote: Dave Page wrote: Bruce Momjian wrote: Can somebody please explain to me what beta means if you can commit new stuff after it has been declared? We allow /contrib to be more lax about beta changes. Why? When people were complaining about not being able to use TSearch because their ISPs wouldn't install contrib modules we couldn't understand why they would think that way. If we are going to be less stringent about /contrib, maybe they were right to cautious. The idea is /contrib isn't installed by default and it isn't tied into the core code, and can be tested easier because it is stand-alone. We can rethink that logic but that has been the guide in the past. Yes, I think we should if this is the result. It's one thing keeping modules seperate for ease of testing, but it's another to become less rigourous about how that code is maintained, developed and tested compared to the core code. /D ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
On 10/9/2007 4:22 PM, Bruce Momjian wrote: Jan Wieck wrote: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Doing it the way you did with no warning, right before beta, and then leaving is the worse of all times. I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. This is not the first time you have had trouble with patches. There was an issue with your patch of February, 2007: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00385.php That email might contain the keyword COMMIT, but it doesn't have to do with anything I committed to CVS. The trigger changes you are referring to have been discussed and a patch for discussion was presented here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00146.php Right, but at the time you didn't want to give a good explaination and I had to ask for it. That should not have been necessary. (In summary, you had to be coaxed to explain your patch to the community.) Basically, I am not sure you understand the process that has to be followed, or feel you are somehow immune from following it. I don't see how you leap from the above example to that conclusion. You have had only a few commits in 2007, and there have been two problems. That ratio seems too high to me, hence my questions above. You are misrepresenting the situation. The discussion about the commit timestamp, where you asked for a complete functional specification of a multimaster replication system based on it before anything should be done feature wise at all, was not about any CVS activity that happened. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] some points for FAQ
2007/10/9, Bruce Momjian [EMAIL PROTECTED]: Pavel Stehule wrote: 2007/10/9, Bruce Momjian [EMAIL PROTECTED]: Pavel Stehule wrote: 4.1) To SELECT a random row, use: SELECT col FROM tab ORDER BY random() LIMIT 1; + On bigger tables this solution is slow. Please, find smarter solution on network. Well, give me a better example that works. Better universal solution doesn't exist. Exists only unelegant solutions - but mutch faster. SELECT id, ... FROM data WHERE id = ANY(ARRAY( SELECT (random()*:max_id)::int FROM generate_series(1,20))) LIMIT 1; max_id is host variable ~ real max id + some -- fast solution if id is PK of data Right. We really only want general solutions in the FAQ. ok. I accept it. Can be some note there? Not this strange select. 4.19) + most of problems with invalid OIDs in cache are solved in PostgreSQL 8.3. Please remeber, so every replanning of SQL statements needs time. Write your application, they can exist without cache invalidation. Agreed. Item removed. Cache invalidation isn't 100% protection before this error message. With specific using of EXECUTE statement, you can get this message too. But all temp tables related problems are solved. OK, let's see how many bug reports we get and we can always re-add it. It's true :). You have to try really wild things inside plpgsql procedures. Pavel ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] some points for FAQ
Pavel Stehule wrote: Better universal solution doesn't exist. Exists only unelegant solutions - but mutch faster. SELECT id, ... FROM data WHERE id = ANY(ARRAY( SELECT (random()*:max_id)::int FROM generate_series(1,20))) LIMIT 1; max_id is host variable ~ real max id + some -- fast solution if id is PK of data Right. We really only want general solutions in the FAQ. ok. I accept it. Can be some note there? Not this strange select. Well, with 8.3 having this be faster I am thinking we should wait to see if the hacks are needed. Cache invalidation isn't 100% protection before this error message. With specific using of EXECUTE statement, you can get this message too. But all temp tables related problems are solved. OK, let's see how many bug reports we get and we can always re-add it. It's true :). You have to try really wild things inside plpgsql procedures. Good. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Skytools committed without hackers discussion/review
Jan Wieck wrote: On 10/9/2007 4:22 PM, Bruce Momjian wrote: Jan Wieck wrote: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Doing it the way you did with no warning, right before beta, and then leaving is the worse of all times. I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. This is not the first time you have had trouble with patches. There was an issue with your patch of February, 2007: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00385.php You have had only a few commits in 2007, and there have been two problems. That ratio seems too high to me, hence my questions above. You are misrepresenting the situation. The discussion about the commit timestamp, where you asked for a complete functional specification of a multimaster replication system based on it before anything should be done feature wise at all, was not about any CVS activity that happened. Here is a quote of exactly what I had to ask for, which I shouldn't have had to ask for: What I did want to hear is a layout of how the system would work, and an exchange of ideas until almost everyone was happy. Also, I saw the trigger patch with no explaination of why it was important or who would use it --- that also isn't going to fly well. So, to add something, the community needs to hear how it is going to help users, because every code addition has cost, and we don't want to add things unless it has general utility. If someone can't explain the utility of an addition, I question whether the person has fully thought through were they are going. Not sure where you got the complete functional specification of a multimaster replication system. I go back to my original question, do you understand the process that has to be followed for patch submission/application, and that it applies to all of us, including you? A simple yes is all I need to hear. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 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] Skytools committed without hackers discussion/review
On 10/9/2007 5:13 PM, Bruce Momjian wrote: Jan Wieck wrote: On 10/9/2007 4:22 PM, Bruce Momjian wrote: Jan Wieck wrote: I don't see how timing has anything to do with this. You could have added it between beta1 and beta2 after sufficient hackers discussion. Doing it the way you did with no warning, right before beta, and then leaving is the worse of all times. I am surprised we are not backing out the patch and requiring that the patch go through the formal review process. This is not the first time you have had trouble with patches. There was an issue with your patch of February, 2007: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00385.php You have had only a few commits in 2007, and there have been two problems. That ratio seems too high to me, hence my questions above. You are misrepresenting the situation. The discussion about the commit timestamp, where you asked for a complete functional specification of a multimaster replication system based on it before anything should be done feature wise at all, was not about any CVS activity that happened. Here is a quote of exactly what I had to ask for, which I shouldn't have had to ask for: What I did want to hear is a layout of how the system would work, and an exchange of ideas until almost everyone was happy. Also, I saw the trigger patch with no explaination of why it was important or who would use it --- that also isn't going to fly well. So, to add something, the community needs to hear how it is going to help users, because every code addition has cost, and we don't want to add things unless it has general utility. If someone can't explain the utility of an addition, I question whether the person has fully thought through were they are going. Not sure where you got the complete functional specification of a multimaster replication system. I go back to my original question, do you understand the process that has to be followed for patch submission/application, and that it applies to all of us, including you? A simple yes is all I need to hear. Yes, Sir. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Locale + encoding combinations
Dave Page wrote: Is there any reason not to accept other combinations that setlocale() is happy with? setlocale() sets the locale. How does it accept a combination? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] some points for FAQ
ok. I accept it. Can be some note there? Not this strange select. Well, with 8.3 having this be faster I am thinking we should wait to see if the hacks are needed. difference, on 10K lines (on small think table) postgres=# select * from test where i = any(array(select (random()*1)::int from generate_series(1,20))) limit 1; i | v -+- 869 | 113 (1 row) Time: 3,984 ms postgres=# select * from test order by random() limit 1; i | v --+- 3687 | 293 (1 row) Time: 21,978 ms 8.2 postgres=# select * from test order by random() limit 1; i | v --+- 4821 | 608 (1 row) Time: 51,299 ms postgres=# select * from test where i = any(array(select (random()*1)::int from generate_series(1,20))) limit 1; i | v -+- 762 | 254 (1 row) Time: 4,530 ms Results: 8.3 fast solution' is 6x faster 8.2 'fast solution' is 11x faster .. it's minimum. Pavel for me, it's one from typical beginers mistakes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Locale + encoding combinations
Peter Eisentraut wrote: Dave Page wrote: Is there any reason not to accept other combinations that setlocale() is happy with? setlocale() sets the locale. How does it accept a combination? setlocale(LC_CTYPE, English_United Kingdom.65001) will return null (and not change anything) because it doesn't like the combination of the locale and that encoding (UTF-8). setlocale(LC_CTYPE, English_United Kingdom.1252) will return English_United Kingdom.1252 and set the locale accordingly because WIN1252 is a valid encoding for that locale. Similarly, LATIN1 and numerous other encodings are accepted in combination with that locale. Should initdb allow any combination that setlocale() accepts, or should it *only* accept the default encoding for the specified locale? /D ---(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] ECPG regression tests
Andrew Dunstan wrote: Magnus Hagander wrote: (Hint: if you don't put the PlatformSDK directories first in the INCLUDE and LIB lists bad and inexplicable things can happen.) Pick up the latest version of run_build.pl in CVS if you want to run this in your buildfarm animal now. A release will be forthcoming very soon. I put it in, but it doesn't work. It works when running ecpg tests manual, but from run_build I get: http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=skylarkdt=2007-10-09%20090814stg=ecpg-check which seems similar to what you had before. How did you fix that one? Is that the one requiring a reorder? Yes. compare its build_env INCLUDE and LIB and possibly PATH values with those of red_bat: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=red_batdt=2007-10-09%2004:30:04 BTW, following some advice I found on the net those PlatformSDK directories were copied manually from the SDK install, back when I installed MSVC. You also appear to have simply a configuration bug - there is a missing semicolon or two in: LIB' = 'D:\\Program Files (x86)\\Microsoft Visual Studio 8\\VC\\ATLMFC\\LIB;D:\\Program Files (x86)\\Microsoft Visual Studio 8\\VC\\LIBD:\\Program Files (x86)\\Microsoft Visual Studio 8\\VC\\PlatformSDK\\lib;D:\\Program Files (x86)\\Microsoft Visual Studio 8\\SDK\\v2.0\\libD:\\Program Files (x86)\\Microsoft Visual Studio .NET 2003\\SDK\\v1.1\\Lib\\;C:\\Program Files\\SQLXML 4.0\\bin\\', cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Skytools committed without hackers discussion/review
On Tue, 2007-10-09 at 17:13 -0400, Bruce Momjian wrote: I go back to my original question, do you understand the process that has to be followed for patch submission/application, and that it applies to all of us, including you? I think you're braking a little hard here. Nothing bad has happened has it? My understanding of committing stuff was about taking responsibility for what's in there. Jan definitely has the knowledge and track record to be trusted, plus we know he has time to fix any mistakes. That close to release, only Core members should be doing that and Jan is Core. Personally, I want to see Jan contribute more, not less. The link with Slony and related replication technology is critically important to Postgres, which is why Jan has spent so long on it. Generally we should be encouraging everybody to contribute; the project must have an orientation towards action and growth if we are to survive. If Jan had not done this, would there have been a storm of protest? Anyway, its a good release and its out now. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.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] some points for FAQ
Pavel Stehule wrote: ok. I accept it. Can be some note there? Not this strange select. Well, with 8.3 having this be faster I am thinking we should wait to see if the hacks are needed. difference, on 10K lines (on small think table) postgres=# select * from test where i = any(array(select (random()*1)::int from generate_series(1,20))) limit 1; i | v -+- 869 | 113 (1 row) Time: 3,984 ms postgres=# select * from test order by random() limit 1; i | v --+- 3687 | 293 (1 row) Time: 21,978 ms 8.2 postgres=# select * from test order by random() limit 1; i | v --+- 4821 | 608 (1 row) Time: 51,299 ms postgres=# select * from test where i = any(array(select (random()*1)::int from generate_series(1,20))) limit 1; i | v -+- 762 | 254 (1 row) Time: 4,530 ms Results: 8.3 fast solution' is 6x faster 8.2 'fast solution' is 11x faster .. it's minimum. OK, how do we even explain this idea in the FAQ. It pulls 20 random values from 1 to 1? That seems pretty hard to code to me. Where do you get the 1 number from? How do you know you will hit a match in 20 tries? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Skytools committed without hackers discussion/review
Simon Riggs wrote: That close to release, only Core members should be doing that and Jan is Core. My understanding (not being a member :-) ) is that Core is an administrative group, not a group of committers. Some members of Core are committers, some not, some committers are in Core, some not. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Skytools committed without hackers discussion/review
On Tue, 2007-10-09 at 17:55 -0400, Andrew Dunstan wrote: Simon Riggs wrote: That close to release, only Core members should be doing that and Jan is Core. My understanding (not being a member :-) ) is that Core is an administrative group, not a group of committers. Some members of Core are committers, some not, some committers are in Core, some not. By observation, all committers are not equal. If they are equal then we must censure others also, as well as Jan, but I see no need personally. If they are not equal, then Jan deserves extra leeway, IMHO. Either way, we should not focus just upon Jan, especially when so many will thank him for his actions. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Skytools committed without hackers discussion/review
On Tue, 09 Oct 2007 17:55:48 -0400 Andrew Dunstan [EMAIL PROTECTED] wrote: Simon Riggs wrote: That close to release, only Core members should be doing that and Jan is Core. My understanding (not being a member :-) ) is that Core is an administrative group, not a group of committers. Some members of Core are committers, some not, some committers are in Core, some not. That is my understanding as well and has been substantiated buy other members of core. Sincerely, Joshua D. Drake cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [HACKERS] Skytools committed without hackers discussion/review
Andrew Dunstan wrote: Simon Riggs wrote: That close to release, only Core members should be doing that and Jan is Core. My understanding (not being a member :-) ) is that Core is an administrative group, not a group of committers. Some members of Core are committers, some not, some committers are in Core, some not. Correct. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
