Re: [HACKERS] Automatic free space map filling
I thought we had sufficiently destroyed that reuse a tuple meme yesterday. You can't do that: there are too many aspects of the system design that are predicated on the assumption that dead tuples do not come back to life. You have to do the full vacuuming bit (index entry removal, super-exclusive page locking, etc) before you can remove a dead tuple. One more idea I would like to throw in. Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead tuple by reducing the tuple to it's header info. (If you still wanted to be able to locate index entries fast, you would need to keep indexed columns, but I think we agreed that there is no real use) I think that would be achievable at reasonable cost (since you can avoid one page IO) on the page of the currently active tuple (the first page that is considered). On this page: if freespace available -- use it elsif freespace available after reducing all dead rows -- use the freespace with a new slot else Of course this only works when we still have free slots, but I think that might not really be an issue. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Automatic free space map filling
Ühel kenal päeval, N, 2006-03-02 kell 09:53, kirjutas Zeugswetter Andreas DCP SD: I thought we had sufficiently destroyed that reuse a tuple meme yesterday. You can't do that: there are too many aspects of the system design that are predicated on the assumption that dead tuples do not come back to life. You have to do the full vacuuming bit (index entry removal, super-exclusive page locking, etc) before you can remove a dead tuple. One more idea I would like to throw in. Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead tuple by reducing the tuple to it's header info. (If you still wanted to be able to locate index entries fast, you would need to keep indexed columns, but I think we agreed that there is no real use) I don't even think you need the header, just truncate the slot to be 0-size (the next pointer is the same as this one or make the pointer point to unaligned byte or smth) and detect this condition when accessing tuples. this would add on compare to all accesse to the tuple, but I suspect that mostly it is a noop performance-wise as all data needed is already available in level1 cache. This would decouple declaring a tuple to be dead/reuse data space and final cleanup/free index space. Hannu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Automatic free space map filling
[sorry to everyone if that mail arrives multiple times, but i had some odd problems with my mail gateway yesterday...] On Wed, Mar 01, 2006 at 12:41:01PM -0500, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: How does an optimistic FSM entry avoid the need to run vacuum? It ensures that all freed tuples are already in the FSM. That has nothing to do with it, because the space isn't actually free for re-use until vacuum deletes the tuple. But couldn't such an opportunistic approach be used for another lightweight VACUUM mode in such a way, that VACUUM could look at a special Hot Spot queue, which represents potential candidates for freeing? Let's call it a 2-phase VACUUMthis would avoid a constant long running VACUUM run on big tables, e.g. when tuples gets updated (or deleted) frequently. Just an idea... Bernd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
Peter, I'd need an invitation to get a visa. Is't possible ? Oleg On Wed, 1 Mar 2006, Peter Eisentraut wrote: PostgreSQL Anniversary Summit = Call for Contributions -- The PostgreSQL Anniversary Summit will take place on July 8 and 9, 2006, in Toronto, Canada. We are planning for a gathering of about 50 hackers, contributors, and other friends of the PostgreSQL project to celebrate the project's 10th anniversary, reflect on the work accomplished, establish new contacts, and plan for the future. The summit will feature speaker sessions, workshops, discussion groups, and social events. We are now looking for content proposals. Topics can include: - Development, how to and how not to - Features for the future (or of the past) - PostgreSQL-related research projects - Issues relating to the project's organization - PostgreSQL-related projects - Legal issues - Non-profit organizations - Advocacy, marketing - How to make PostgreSQL more appealing to $X - Business aspects - Other interesting event proposals such as discussions, contests, awards, question sessions, etc. will also be considered if you are prepared to organize them. There is considerable freedom in developing the program. Anything that is important to you, of interest to others, and of value to the project can be reasonable. But remember that this is a conference of PostgreSQL contributors, so user-level talks should normally not be submitted. Submissions and the actual sessions should be in English. Contributions should generally use time slots of 45 minutes, but feel free to specify otherwise if you have special requirements. We are also welcoming lightning talks of about 5 minutes. Send submissions to [EMAIL PROTECTED] in free form, but include the following information: - your name - your e-mail address - title of your contribution - type of your contribution (talk, discussion, etc.) - abstract of up to 100 words (for publishing in the program) - extended description (for review by the organizers, not published) The deadline for submissions is March 31st. Speakers and other supporters of the conference program (exception: lightning talks) will be offered free registration. They will also be first in line to receive financial assistance, but we cannot guarantee any such thing at the moment, so be prepared to pay for your travel and accomodation. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Automatic free space map filling
On Wed, Mar 01, 2006 at 12:41:01PM -0500, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: How does an optimistic FSM entry avoid the need to run vacuum? It ensures that all freed tuples are already in the FSM. That has nothing to do with it, because the space isn't actually free for re-use until vacuum deletes the tuple. Hmm, but couldn't such an opportunistic approach be used for another leightweight VACUUM mode in such a way, that VACUUM could look at a special Hot Spot queue, which represents potential candidates for freeing? Let's call it a 2-phase VACUUMthis would avoid a long running VACUUM run on big tables, e.g. when tuples gets updated (or deleted) frequently. Just an idea... Bernd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] ACCESS EXCLUSIVE LOCK
This may be a newbie question, but according to the 7.4 docs, an ACCESS EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. However, when viewing pg_locks during the execution of a stored procedure that does not perform any of the above commands, I see that the table it is working on is locked by ACCESS EXCLUSIVE. I have also tested that I can not perform a simple SELECT on the locked table while the SP is executing. Can anyone provide some insight? Thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Automatic free space map filling
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Tom Lane) would write: I thought we had sufficiently destroyed that reuse a tuple meme yesterday. You can't do that: there are too many aspects of the system design that are predicated on the assumption that dead tuples do not come back to life. This discussion needs to come up again in October when the zombie movies come out :-). That's the other problem: it's not apparent why pushing work from vacuum back into foreground processing is a good idea. Especially not why retail vacuuming of individual tuples will be better than wholesale. What is unclear to me in the discussion is whether or not this is invalidating the item on the TODO list... --- Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. One complexity is that index entries still have to be vacuumed, and doing this without an index scan (by using the heap values to find the index entry) might be slow and unreliable, especially for user-defined index functions. --- It strikes me as a non-starter to draw vacuum work directly into the foreground; there is a *clear* loss in that the death of the tuple can't actually take place at that point, due to MVCC and the fact that it is likely that other transactions will be present, keeping the tuple from being destroyed. But it would *seem* attractive to do what is in the TODO, above. Alas, the user defined index functions make cleanout of indexes much more troublesome :-(. But what's in the TODO is still wholesale, albeit involving more targetted selling than the usual Kirby VACUUM :-). -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/rdbms.html Rules of the Evil Overlord #140. I will instruct my guards when checking a cell that appears empty to look for the chamber pot. If the chamber pot is still there, then the prisoner has escaped and they may enter and search for clues. If the chamber pot is not there, then either the prisoner is perched above the lintel waiting to strike them with it or else he decided to take it as a souvenir (in which case he is obviously deeply disturbed and poses no threat). Either way, there's no point in entering. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ACCESS EXCLUSIVE LOCK
[EMAIL PROTECTED] wrote: This may be a newbie question, but according to the 7.4 docs, an ACCESS EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. However, when viewing pg_locks during the execution of a stored procedure that does not perform any of the above commands, I see that the table it is working on is locked by ACCESS EXCLUSIVE. Is the SP executing a LOCK TABLE perchance? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ACCESS EXCLUSIVE LOCK
On 2006-03-02, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: No. Here is the offending SP: CREATE OR REPLACE FUNCTION update_my_cache() RETURNS void AS ' BEGIN TRUNCATE TABLE my_cache_table; TRUNCATE is another command that takes an access exclusive lock. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Initdb on Windows 2003
All I have determined what is causing the failure. It appears that the stdout stderr redirection to nul produces the Access is Denied. message. This is happening even if I type dir nul at the command prompt! I assume that this re-direction in PostgreSQL is done when starting postgres.exe as to allow the communction between the two processes? I know this is not the correct place to ask my next question but maybe there is a Windows expert out there :o) Does anyone have any idea how Windows controls access to devices such as nul,comN, lpt1, etc? Or what security setting governs this? Thanks all for your help. Regards James -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hughes, James Sent: 02 March 2006 07:51 To: [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Initdb on Windows 2003 Thanks for the info, I shall indeed try this. One thing to mention is that I don't think the error occurs within initdb, it seams to be postgres.exe that dbinit starts. Regards James -Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 01 March 2006 16:26 To: Hughes, James Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Initdb on Windows 2003 (private email says leaving off --user=foo doesn't fix it ;-( ) Since you have apparently compiled your own, could you please try with the latest stable initdb.c code for your release? That is version 1.99.2.2 for release 8.1 or 1.73.4.3 for release 8.0. They are downloadable here: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/initdb/initdb.c Maybe there's something odd about your setup - we have a WS2k3 machine happily building and running on buildfarm: see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=REL8_1_ST ABLE If it still doesn't work, you might get some useful info from initdb --debug Thanks andrew [EMAIL PROTECTED] wrote: Hi, 1. The command line passed is -D c:\data --user=McAfeePostgresUser 2. McAfeePostgresUser which is a local machine user with limited privileges. Though this problem occurs with any user account you create with non-administrator privileges. Thanks James -Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 01 March 2006 13:27 To: Hughes, James Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Initdb on Windows 2003 1. please show the EXACT initdb command line used. 2. Which Windows user was actually running initdb? cheers andrew Hello all, Below is an email thread regarding a possible bug in PostgreSQL on Windows 2003. Any help or advice anyone can give on this would be much appreciated. All the best, James Hughes --- EMAIL THREAD FOLLOWS --- [EMAIL PROTECTED] wrote: Hi Bruce, We are now seeing this issue on three machines, all of which are running Windows 2003. After some looking at the code and putting some extra debug output (very little) all we have determined is that initdb.exe opens postgres.exe via pipes, and at some point within postgres.exe this error is generated. If we knock out the check for the user being not being admin and run initdb.exe as an administrator all works as expected. Adding the user to the 'Power Users' group also causes the Access Denined error! I don't believe that the access denined is anything to do with file access or memory (shared) access as I ran some tools to see what postgres.exe is trying to access. Is there an easy way on Windows to step-through the PostgreSQL code as it runs? Is there any other information I can provide which will help you with this defect? Any help getting to the root of this problem is much appericated. All the best James Hughes -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 24 February 2006 18:54 To: Hughes, James Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #2268: initdb fails to initialize database cluster with error Access is denied Strange. It isn't a typical error we see, and the fact you see it on two machines is even stranger. My guess is that somehow the configuration on those two machines is the same and is causing the failure. - - -- --- James Hughes wrote: The following bug has been logged online: Bug reference: 2268 Logged by: James Hughes Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: Windows 2003 (Server) Description:initdb fails to initialize database cluster with error Access is denied Details: When we try and initialize a database cluster using initdb.exe we are getting the following output. -- OUTPUT from dbinit.exe -- The files belonging to this database system will be owned by user user1. This user must also own the server process. The database cluster will be
Re: [HACKERS] Automatic free space map filling
On Thu, Mar 02, 2006 at 08:33:46AM -0500, Christopher Browne wrote: What is unclear to me in the discussion is whether or not this is invalidating the item on the TODO list... --- Create a bitmap of pages that need vacuuming snip I think this is doable, and not invalidated by anything said so far. All this is changeing is whether to scan the whole table or just the bits changed. Unfortunatly I don't think you can avoid scanning the indexes :(. Note, for this purpose you don't need to keep a bit per page. The OS I/O system will load 64k+ (8+ pages) in one go so one bit per 8 pages would be sufficient. The inverse is keep a list of pages where we know all tuples are visible to everyone. I'm not sure if this can be done race condition free. ISTM it would be possible to get the new Bitmap Index Scans to avoid checking visiblity straight away but wait until it has been AND/OR'd with other bitmaps and only at the end checking visibility. But maybe that already happens... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] ACCESS EXCLUSIVE LOCK
On 2006-03-02, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: TRUNCATE is another command that takes an access exclusive lock. The whole SP takes about 10 seconds to run total. The TRUNCATE command only takes less than a second. However, the access exclusive lock is held throughout the entire SP, not just during the execution of the TRUNCATE command. Shouldn't TRUNCATE be releasing the lock as soon as it finishes? No, locks are always held until the end of the transaction. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: Tom Lane wrote: The problem is that you can't determine what answer justify_days would give without using the assumption 1 month == 30 days, which is an assumption that justify_hours must not depend on. Ahhh. So the fact that justify_days already makes the 1 month == 30 days assumption is ok in that function but can't be propagated to justify_hours. Right. I don't want us to define things so that none of this functionality is available in situations where the 30-day assumption is untenable. justify_hours can still do something useful (ie, trim oversize hours fields) without that. justify_interval will probably be the new normal way to do things when you are prepared to make both assumptions. I'm not entirely sure about the use-case for justify_days, but seems we ought to keep it for reasons of backwards compatibility. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ACCESS EXCLUSIVE LOCK
On 2006-03-02, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: First of all, thank you very much. I changed TRUNCATE to DELETE FROM and my problem as been fixed. Is there any way to override that behavior? I know you can explicitly lock tables, can you explicitly unlock tables? No. Just to be clear, once I run a TRUNCATE command inside an SP, that table that it acts upon will have an access exclusive lock on it until the SP is finished? Until the transaction is finished, and since you can't commit from inside a function, that means the lock will be held _at least_ until the end of the SP. This is necessary in order for other concurrent transactions not to get incorrect results. (The difference between TRUNCATE and DELETE in this case is that TRUNCATE gives the table a new, empty, heap and indexes, deleting the old ones on commit; that means that it can't allow concurrent access to the table since it is going to delete old tuples that might otherwise still be visible to other transactions. DELETE on the other hand simply marks the old tuples as dead; remember to vacuum as needed to clean up.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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] Automatic free space map filling
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, N, 2006-03-02 kell 09:53, kirjutas Zeugswetter Andreas DCP SD: Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead tuple by reducing the tuple to it's header info. I don't even think you need the header, just truncate the slot to be 0-size I think you must keep the header because the tuple might be part of an update chain (cf vacuuming bugs we repaired just a few months ago). t_ctid is potentially interesting data even in a certainly-dead tuple. Andreas' idea is possibly doable but I am not sure that I see the point. It does not reduce the need for vacuum nor the I/O load imposed by vacuum. What it does do is bias the system in the direction of allocating an unreasonably large number of tuple line pointers on a page (ie, more than are useful when the page is fully packed with normal tuples). Since we never reclaim such pointers, over time all the pages in a table would tend to develop line-pointer-bloat. I don't know what the net overhead would be, but it'd definitely impose some aggregate inefficiency. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Automatic free space map filling
Bernd Helmle [EMAIL PROTECTED] writes: But couldn't such an opportunistic approach be used for another lightweight VACUUM mode in such a way, that VACUUM could look at a special Hot Spot queue, which represents potential candidates for freeing? The proposed dirty-page bit map seems a superior solution to that. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 Feature Freeze Rough Estimate
Cool... I'm looking forward to 8.2... Ever since 8.x, it seems like we're progressing rather quickly on performance enhancements, fixes, and new additions. On 3/2/06, Tom Lane [EMAIL PROTECTED] wrote: Robert Treat [EMAIL PROTECTED] writes: I'm trying to plan out a few postgresql related projects and could use some intuition on when folks think the feature freeze for 8.2 is going to occur.After some discussion among core, we've agreed to set August 1 as thetentative feature freeze date for 8.2.This is a year from the freezefor 8.1, and there doesn't seem to be any good reason to let the development cycle run longer than that.Of course, we'll change thedate if some good reason emerges, but for now let's plan around Aug 1.regards, tom lane---(end of broadcast)---TIP 6: explain analyze is your friend-- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation732.331.1324
Re: [HACKERS] Automatic free space map filling
Christopher Browne [EMAIL PROTECTED] writes: What is unclear to me in the discussion is whether or not this is invalidating the item on the TODO list... No, I don't think any of this is an argument against the dirty-page-bitmap idea. The amount of foreground effort needed to set a dirty-page bit is minimal (maybe even zero, if we can make the bgwriter do it, though I'm pretty suspicious of that idea because I think it needs to be done immediately when the page is dirtied). I don't see the dirty-page bitmap as changing the way that VACUUM works in any fundamental respect --- it will just allow the vacuum process to skip reading pages that certainly don't need to change. One point that does need to be considered though is what about anti-wraparound processing (ie, replacing old XIDs with FrozenXID before they wrap around)? VACUUM currently is a safe way to handle that, but if its normal mode of operation stops looking at every tuple then we're going to have an issue there. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Automatic free space map filling
Tom Lane wrote: Christopher Browne [EMAIL PROTECTED] writes: What is unclear to me in the discussion is whether or not this is invalidating the item on the TODO list... No, I don't think any of this is an argument against the dirty-page-bitmap idea. The amount of foreground effort needed to set a dirty-page bit is minimal (maybe even zero, if we can make the bgwriter do it, though I'm pretty suspicious of that idea because I think it needs to be done immediately when the page is dirtied). I don't see the dirty-page bitmap as changing the way that VACUUM works in any fundamental respect --- it will just allow the vacuum process to skip reading pages that certainly don't need to change. See the email I just posted. I am questioning how big a win it is to skip heap pages if we have to sequentially scan all indexes. One point that does need to be considered though is what about anti-wraparound processing (ie, replacing old XIDs with FrozenXID before they wrap around)? VACUUM currently is a safe way to handle that, but if its normal mode of operation stops looking at every tuple then we're going to have an issue there. We would need to do sequential scan occasionally and somehow track that. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.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] [SQL] Interval subtracting
On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote: Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: But it isn't '-2 months, -1 day'. I think what you are saying is what I am saying, that we should make the signs consistent. Pretty much. It just seems wrong to have different signs in what is essentially a single unit. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. But we do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. But the military says 1515 or 1545 or 0315 or 0345, because if they get the time wrong they shell the wrong place and kill their own soldiers. I.e. getting it right is important to them. So they use exact language. I prefer the more exact way. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Wisconsin Court Systems software
On Wed, Mar 1, 2006 at 11:02 am, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2006- 03- 01 at 10:22 - 0600, Kevin Grittner wrote: On Tue, Feb 28, 2006 at 7:22 am, in message [EMAIL PROTECTED], Simon Riggs [EMAIL PROTECTED] wrote: We use serializable transactions heavily; our whole middle tier architecture depends on having that transaction isolation level for all requests which modify data. (You probably don't want to hear the details.) *I* would, but others may not. ;- ) An executive overview of our environment, with enough detail to constitute more than vague hand waving, would probably be at least 4K of text. If you're interested, I could write something up and post it somewhere, but this list doesn't seem to be the appropriate place. Where would be? The general hand waving overview: We've got about 100 databases with a lot of fancy portability features which allow real time data replication in a heterogeneous environment. 3,000 directly connected users, dozens of queue-based interfaces (in both directions) with business partner agencies, and a web site with about 2 million hits per day which query from the databases (when you count both SOAP and browser traffic). So far four of our databases are on PostgreSQL, with another four being converted over the next few days. The servers spread around the state will be converted more gradually, over the course of the next year or so. Software is almost entirely Java, and mostly home-grown. All database access is done through JDBC from a middle tier database service which treats each client request as one (and only one) database transaction so that serialization errors can be handled automatically. Queries are written in an approved subset of standard ANSI SQL, with our query tool parsing that and turning it into Java classes which use lowest common denominator SQL code, with Java code to handle all procedural steps. Since stored procedures and triggers are implemented in our Java code, we can see all data flow, allowing us to copy the transactions to audit databases and replicate the data from multiple sources to multiple targets. It is easy to show a number of ways that we will have data integrity problems if the JDBC requests from the trigger code aren't in a SERIALIZABLE transaction with the triggering data modification. Now, aren't you sorry you asked? ;-) -Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Automatic free space map filling
Christopher Browne wrote: What is unclear to me in the discussion is whether or not this is invalidating the item on the TODO list... --- Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. One complexity is that index entries still have to be vacuumed, and doing this without an index scan (by using the heap values to find the index entry) might be slow and unreliable, especially for user-defined index functions. --- It strikes me as a non-starter to draw vacuum work directly into the foreground; there is a *clear* loss in that the death of the tuple can't actually take place at that point, due to MVCC and the fact that it is likely that other transactions will be present, keeping the tuple from being destroyed. But it would *seem* attractive to do what is in the TODO, above. Alas, the user defined index functions make cleanout of indexes much more troublesome :-(. But what's in the TODO is still wholesale, albeit involving more targetted selling than the usual Kirby VACUUM :-). What bothers me about the TODO item is that if we have to sequentially scan indexes, are we really gaining much by not having to sequentially scan the heap? If the heap is large enough to gain from a bitmap, the index is going to be large too. Is disabling per-index cleanout for expression indexes the answer? The entire expression index problem is outlined in this thread: http://archives.postgresql.org/pgsql-hackers/2006-02/msg01127.php I don't think it is a show-stopper because if we fail to find the index that matches the heap, we know we have a problem and can report it and fall back to an index scan. Anyway, as I remember, if you have a 20gig table, a vacuum / sequential scan is painful, but if we have to sequential scan the all indexes, that is probably just as painful. If we can't make headway there and we can't cleanout indexes without an sequential index scan, I think we should just remove the TODO item and give up on improving vacuum performance. For the bitmaps, index-only scans require a bit that says all page tuples are visible while vacuum wants some tuples are expired. DELETE would clear both bits, while INSERT would clear just the first, and update is a mix of INSERT and UPDATE, though perhaps on different pages. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Automatic free space map filling
What bothers me about the TODO item is that if we have to sequentially scan indexes, are we really gaining much by not having to sequentially scan the heap? If the heap is large enough to gain from a bitmap, the index is going to be large too. Is disabling per-index cleanout for expression indexes the answer? I guess you're saying that full index scan should only be done when the index is a functional one, and use index lookup for safe indexes ? That would be a huge win for most of my vacuum-problematic tables, as I don't have any functional indexes. But I guess full index scan would still be faster if the percentage of pages changed is more than some threshold. On the other hand it would allow very frequent vacuuming even for huge tables so that situation should not occur. Autovacuum thresholds could be lowered drastically in that case... Anyway, as I remember, if you have a 20gig table, a vacuum / sequential scan is painful, but if we have to sequential scan the all indexes, that is probably just as painful. If we can't make headway there and we can't cleanout indexes without an sequential index scan, I think we should just remove the TODO item and give up on improving vacuum performance. From my POV, there must be a way to speed up vacuums on huge tables and small percentage of to-be-vacuumed tuples... a 200 million rows table with frequent updates of the _same_ record is causing me some pain right now. I would like to have that table vacuumed as often as possible, but right now it only works to do it once per week due to load problems on long-running transactions preventing vacuuming other tables. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] INS/UPD/DEL Returning Patch
All,
This is only the current patch updated to apply cleanly on cvs... it's
not ready for -patches yet as I still haven't spent much time looking
through it and testing it. This is just for anyone to play with
and find issues.
My focus for the next couple days is on getting INS/UPD/DEL RETURNING
ready for 8.2... all comments, suggestions, issues would be appreciated.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
diff -cr pgsql/src/backend/access/common/printtup.c pgsql-iudret/src/backend/access/common/printtup.c
*** pgsql/src/backend/access/common/printtup.c 2005-11-03 12:11:30.0 -0500
--- pgsql-iudret/src/backend/access/common/printtup.c 2006-03-02 12:07:43.0 -0500
***
*** 19,24
--- 19,25
#include access/printtup.h
#include libpq/libpq.h
#include libpq/pqformat.h
+ #include executor/executor.h
#include tcop/pquery.h
#include utils/lsyscache.h
#include utils/portal.h
***
*** 112,117
--- 113,120
{
DR_printtup *myState = (DR_printtup *) self;
Portal portal = myState-portal;
+ List *returning = ((Query *) linitial(portal-parseTrees))-returning;
+ bool withReturning = (returning != NIL);
if (PG_PROTOCOL_MAJOR(FrontendProtocol) 3)
{
***
*** 136,142
SendRowDescriptionMessage(typeinfo,
FetchPortalTargetList(portal),
portal-formats);
!
/*
* We could set up the derived attr info at this time, but we postpone it
* until the first call of printtup, for 2 reasons:
--- 139,149
SendRowDescriptionMessage(typeinfo,
FetchPortalTargetList(portal),
portal-formats);
! else if (withReturning)
! SendRowDescriptionMessage(ExecTypeFromTL(returning, false),
! returning,
! portal-formats);
!
/*
* We could set up the derived attr info at this time, but we postpone it
* until the first call of printtup, for 2 reasons:
***
*** 305,311
/*
* send the attributes of this tuple
*/
! for (i = 0; i natts; ++i)
{
PrinttupAttrInfo *thisState = myState-myinfo + i;
Datum origattr = slot-tts_values[i],
--- 312,318
/*
* send the attributes of this tuple
*/
! for (i = 0; i natts; i++)
{
PrinttupAttrInfo *thisState = myState-myinfo + i;
Datum origattr = slot-tts_values[i],
diff -cr pgsql/src/backend/executor/execMain.c pgsql-iudret/src/backend/executor/execMain.c
*** pgsql/src/backend/executor/execMain.c 2006-02-27 23:10:27.0 -0500
--- pgsql-iudret/src/backend/executor/execMain.c 2006-03-02 12:07:43.0 -0500
***
*** 77,88
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest,
EState *estate);
! static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
! static void ExecDelete(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
! static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
static TupleTableSlot *EvalPlanQualNext(EState *estate);
static void EndEvalPlanQual(EState *estate);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
--- 77,88
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest,
EState *estate);
! static void ExecInsert(TupleTableSlot *slot, DestReceiver *dest,
!ItemPointer tupleid, EState *estate);
! static void ExecDelete(TupleTableSlot *slot, DestReceiver *dest,
! ItemPointer tupleid, EState *estate);
! static void ExecUpdate(TupleTableSlot *slot, DestReceiver *dest,
! ItemPointer tupleid, EState *estate);
static TupleTableSlot *EvalPlanQualNext(EState *estate);
static void EndEvalPlanQual(EState *estate);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
***
*** 151,156
--- 151,159
estate-es_snapshot = queryDesc-snapshot;
estate-es_crosscheck_snapshot = queryDesc-crosscheck_snapshot;
estate-es_instrument = queryDesc-doInstrument;
+ estate-es_returning =
+ ExecTransformReturning(queryDesc-parsetree-returning,
+ estate);
/*
* Initialize the plan state tree
***
*** 1299,1315
break;
case CMD_INSERT:
! ExecInsert(slot, tupleid, estate);
result = NULL;
break;
case CMD_DELETE:
! ExecDelete(slot, tupleid, estate);
result = NULL;
break;
case CMD_UPDATE:
! ExecUpdate(slot, tupleid, estate);
result = NULL;
break;
--- 1302,1318
break;
case CMD_INSERT:
! ExecInsert(slot, dest, tupleid, estate);
result = NULL;
break;
case CMD_DELETE:
! ExecDelete(slot, dest, tupleid, estate);
result = NULL;
break;
case CMD_UPDATE:
! ExecUpdate(slot, dest, tupleid, estate);
result = NULL;
break;
***
*** 1408,1413
--- 1411,1417
*/
Re: [HACKERS] [SQL] Interval subtracting
Ühel kenal päeval, N, 2006-03-02 kell 10:13, kirjutas Scott Marlowe: On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote: Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: .. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. But we do say both quarter past three (3 hours 15 min) and quarter to four (4 hours -15 min) when talking about time. But the military says 1515 or 1545 or 0315 or 0345, because if they get the time wrong they shell the wrong place and kill their own soldiers. do they also speak so of intervals ? hannu=# select now(), now() - '-1 hour ago'::interval; now | ?column? ---+--- 2006-03-02 19:47:38.042408+02 | 2006-03-02 18:47:38.042408+02 (1 row) You see what I mean - perfectly precise and unconfusable :) I.e. getting it right is important to them. So they use exact language. I prefer the more exact way. And in the current global era they must also speak in GMT all the time, to avoid any confusion :P -- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatic free space map filling
Csaba Nagy wrote: What bothers me about the TODO item is that if we have to sequentially scan indexes, are we really gaining much by not having to sequentially scan the heap? If the heap is large enough to gain from a bitmap, the index is going to be large too. Is disabling per-index cleanout for expression indexes the answer? I guess you're saying that full index scan should only be done when the index is a functional one, and use index lookup for safe indexes ? That would be a huge win for most of my vacuum-problematic tables, as I don't have any functional indexes. But I guess full index scan would still be faster if the percentage of pages changed is more than some threshold. On the other hand it would allow very frequent vacuuming even for huge tables so that situation should not occur. Autovacuum thresholds could be lowered drastically in that case... Right. Another idea would be to remove the heap space held by expired rows, but to keep the tid slot in place because it is pointed to by an index. The index entry could be recycled by a later vacuum index scan, or if an index lookup finds such an entry. Because of multiple indexes, I don't think the tid slot can be removed except by sequential index scans of all indexes. There is also the concern that updating the single-page bitmap will cause contention by multiple sessions modifing a table. I am thinking as long as we have to sequential-scan every index, we aren't going to improve vacuum performance dramatically. If the bitmap adds contention, and it is only a marginal improvement, it might not be a win. The bitmap can be a win, but I think we have to think more boldly to ensure it is a win. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.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] qsort again (was Re: [PERFORM] Strange Create Index behaviour)
Added to TODO: * Improve port/qsort() to handle sorts with 50% unique and 50% duplicate value [qsort] This involves choosing better pivot points for the quicksort. --- Dann Corbit wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, February 15, 2006 5:22 PM To: Ron Cc: pgsql-performance@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index behaviour) Ron [EMAIL PROTECTED] writes: How are we choosing our pivots? See qsort.c: it looks like median of nine equally spaced inputs (ie, the 1/8th points of the initial input array, plus the end points), implemented as two rounds of median-of-three choices. With half of the data inputs zero, it's not too improbable for two out of the three samples to be zeroes in which case I think the med3 result will be zero --- so choosing a pivot of zero is much more probable than one would like, and doing so in many levels of recursion causes the problem. Adding some randomness to the selection of the pivot is a known technique to fix the oddball partitions problem. However, Bentley and Sedgewick proved that every quick sort algorithm has some input set that makes it go quadratic (hence the recent popularity of introspective sort, which switches to heapsort if quadratic behavior is detected. The C++ template I submitted was an example of introspective sort, but PostgreSQL does not use C++ so it was not helpful). I think. I'm not too sure if the code isn't just being sloppy about the case where many data values are equal to the pivot --- there's a special case there to switch to insertion sort, and maybe that's getting invoked too soon. Here are some cases known to make qsort go quadratic: 1. Data already sorted 2. Data reverse sorted 3. Data organ-pipe sorted or ramp 4. Almost all data of the same value There are probably other cases. Randomizing the pivot helps some, as does check for in-order or reverse order partitions. Imagine if 1/3 of the partitions fall into a category that causes quadratic behavior (have one of the above formats and have more than CUTOFF elements in them). It is doubtful that the switch to insertion sort is causing any sort of problems. It is only going to be invoked on tiny sets, for which it has a fixed cost that is probably less that qsort() function calls on sets of the same size. It'd be useful to get a line-level profile of the behavior of this code in the slow cases... I guess that my in-order or presorted tests [which often arise when there are very few distinct values] may solve the bad partition problems. Don't forget that the algorithm is called recursively. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
Chris Browne wrote: oleg@sai.msu.su (Oleg Bartunov) writes: I'd need an invitation to get a visa. Is't possible ? Certainty is difficult to promise, but there is a reasonable population of relevant people here such that invitations can be arranged. I suggest that everyone who needs invitations or other documentation, be it for arranging a visa or getting a day off work or whatever, write to [EMAIL PROTECTED] and we'll work it out. -- 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] [SQL] Interval subtracting
On Thu, Mar 02, 2006 at 10:06:26AM -0800, Mark Dilger wrote: I used grep -R to find all locations where interval_justify_time is mentioned, and for each one added an analogous entry for my new function interval_justify. But I get lost where OID=1175 is associated with interval_justify_time. I can't really just grep for 1175 and add analogous entries of my new OID, because I don't know what all the tables mean. (BTW, I grabbed the next slot in the table and used OID=2711, but I don't know if that is the proper strategy for adding new OIDs.) Is there an automated way of handling this task, such as a developer tool that I didn't find? In the include/catalog directory there are two script, unused_oids and duplicate_oids. It tells you whats available. Have a ncie day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Automatic free space map filling
I think you must keep the header because the tuple might be part of an update chain (cf vacuuming bugs we repaired just a few months ago). t_ctid is potentially interesting data even in a certainly-dead tuple. yes, I'd still want to keep the full header. Andreas' idea is possibly doable but I am not sure that I see the point. It does not reduce the need for vacuum nor the I/O load imposed by vacuum. What it does do is bias the system in the direction of allocating an unreasonably large number of tuple line pointers on a page (ie, more than are useful when the page is fully packed with normal tuples). Since we never reclaim such pointers, over time all the pages in a table would tend to develop line-pointer-bloat. I don't know what the net overhead would be, but it'd definitely impose some aggregate inefficiency. Ok, for vacuum the slot would look like any other dead row and thus be target for removal. Why do we not truncate the line pointer array ? Is it, that vacuum (not the full version) does not move rows to other pages or slots ? Of course vacuum full could do it, but I see your point. Maybe we could impose an upper limit on the number of slots to allow, after which the optimization is turned off. But this starts to sound not so good :-( Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
Mark Dilger [EMAIL PROTECTED] writes: I've written the interval_justify() function but the parser does not know about it yet. The pg_proc change is the only source change you need for that, but afterwards you need to update the postgres.bki file (handled by make and make install in src/backend) and then initdb. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index behaviour)
My introsort is almost complete and its the fastest variant of quicksort I can find, I'll submit it to -patches in the next couple days as-well.On 3/2/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Added to TODO:* Improve port/qsort() to handle sorts with 50% unique and 50% duplicatevalue [qsort]This involves choosing better pivot points for the quicksort. ---Dann Corbit wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED]] On Behalf Of Tom Lane Sent: Wednesday, February 15, 2006 5:22 PM To: Ron Cc: pgsql-performance@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index behaviour) Ron [EMAIL PROTECTED] writes: How are we choosing our pivots? See qsort.c: it looks like median of nine equally spaced inputs (ie, the 1/8th points of the initial input array, plus the end points), implemented as two rounds of median-of-three choices.With half of the data inputs zero, it's not too improbable for two out of the three samples to be zeroes in which case I think the med3 result will be zero --- so choosing a pivot of zero is much more probable than one would like, and doing so in many levels of recursion causes the problem. Adding some randomness to the selection of the pivot is a known technique to fix the oddball partitions problem.However, Bentley and Sedgewick proved that every quick sort algorithm has some input set that makes it go quadratic (hence the recent popularity of introspective sort, which switches to heapsort if quadratic behavior is detected.The C++ template I submitted was an example of introspective sort, but PostgreSQL does not use C++ so it was not helpful). I think.I'm not too sure if the code isn't just being sloppy about the case where many data values are equal to the pivot --- there's a special case there to switch to insertion sort, and maybe that's getting invoked too soon. Here are some cases known to make qsort go quadratic: 1. Data already sorted 2. Data reverse sorted 3. Data organ-pipe sorted or ramp 4. Almost all data of the same value There are probably other cases.Randomizing the pivot helps some, as does check for in-order or reverse order partitions. Imagine if 1/3 of the partitions fall into a category that causes quadratic behavior (have one of the above formats and have more than CUTOFF elements in them). It is doubtful that the switch to insertion sort is causing any sort of problems.It is only going to be invoked on tiny sets, for which it has a fixed cost that is probably less that qsort() function calls on sets of the same size. It'd be useful to get a line-level profile of the behavior of this code in the slow cases... I guess that my in-order or presorted tests [which often arise when there are very few distinct values] may solve the bad partition problems.Don't forget that the algorithm is called recursively. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster --Bruce Momjian http://candle.pha.pa.usSRA OSS, Inc. http://www.sraoss.com+ If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)---TIP 6: explain analyze is your friend-- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation732.331.1324
Re: [HACKERS] Automatic free space map filling
Zeugswetter Andreas DCP SD [EMAIL PROTECTED] writes: Why do we not truncate the line pointer array ? Is it, that vacuum (not the full version) does not move rows to other pages or slots ? Of course vacuum full could do it, but I see your point. We can't reassign tuple TIDs safely except in vacuum full. It's possible that a plain vacuum could safely truncate off unused line pointers at the end of the array, but in the absence of a forcing function to make those pointers become unused, I'm not sure it'd help much. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
Just curious, but what is involved in these invitations? For instance, is there a limit on # of invitations any one person(?) or company can issue? Are there any legal implications of issuing such an invitation? I could imagine some pretty hot water if pre 9/11 someone were to invite bin Laden to a conference, and had the twin towers go down while he was here, for instance ... On Thu, 2 Mar 2006, Peter Eisentraut wrote: Chris Browne wrote: oleg@sai.msu.su (Oleg Bartunov) writes: I'd need an invitation to get a visa. Is't possible ? Certainty is difficult to promise, but there is a reasonable population of relevant people here such that invitations can be arranged. I suggest that everyone who needs invitations or other documentation, be it for arranging a visa or getting a day off work or whatever, write to [EMAIL PROTECTED] and we'll work it out. -- 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 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
Ühel kenal päeval, N, 2006-03-02 kell 15:35, kirjutas Marc G. Fournier: Just curious, but what is involved in these invitations? For instance, is there a limit on # of invitations any one person(?) or company can issue? Are there any legal implications of issuing such an invitation? Sure. The one who dares to invite anybody is called to an aeroport and strip-searched as well, legal or not. --- Hannu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [DOCS] [HACKERS] Online backup vs Continuous backup
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: This addresses the technical workings of the various backup systems. The fact that it is or isn't on-line, hot, continuous, fast, or flexible is secondary. To me, the continuous activity is the significant feature of that backup method. I chose Continuous Backup because it is the continual activity that is significant. Continuous Archiving? I like Peter's suggestion as-is; or WAL Archiving. I don't like applying adjectives that are not specific to the particular method. That will just create more confusion down the road if we add another feature that could also be called continuous archiving. I do like WAL Archiving but I am concerned that it references a specific internal implementation detail (WAL). Is that OK? Also, I am concerned that is specificially talks about archiving the wal files and is not general enough to include the file system backup that is also necessary. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
On Wed, 2006-03-01 at 11:51 +0100, Peter Eisentraut wrote: The PostgreSQL Anniversary Summit will take place on July 8 and 9, 2006, in Toronto, Canada. We are planning for a gathering of about 50 hackers, contributors, and other friends of the PostgreSQL project to celebrate the project's 10th anniversary, reflect on the work accomplished, establish new contacts, and plan for the future. One thing I'd like to add: we're considering organizing a code sprint for the days immediately following the conference. This would be an opportunity for people interested in contributing to PostgreSQL to work together in the same (large) room. I'm hoping that some of the major contributors will be there, but anyone who's at the summit is welcome to join us. We'll have a bunch of planned projects to work on, but I'd encourage everyone to bring their own project ideas as well. You'll need your own laptop, or have someone you can pair program with. Before we go any farther organizing the sprint, I'd like to get an idea how much interest there is. If you're likely to attend the summit and would be interested in staying for the code sprint, please let me know. You should include you how many days you'd be interested in sprinting for (I'd like to do at least one day, and perhaps two). Thanks, Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
Yea, sure I would like to attend. --- Neil Conway wrote: On Wed, 2006-03-01 at 11:51 +0100, Peter Eisentraut wrote: The PostgreSQL Anniversary Summit will take place on July 8 and 9, 2006, in Toronto, Canada. We are planning for a gathering of about 50 hackers, contributors, and other friends of the PostgreSQL project to celebrate the project's 10th anniversary, reflect on the work accomplished, establish new contacts, and plan for the future. One thing I'd like to add: we're considering organizing a code sprint for the days immediately following the conference. This would be an opportunity for people interested in contributing to PostgreSQL to work together in the same (large) room. I'm hoping that some of the major contributors will be there, but anyone who's at the summit is welcome to join us. We'll have a bunch of planned projects to work on, but I'd encourage everyone to bring their own project ideas as well. You'll need your own laptop, or have someone you can pair program with. Before we go any farther organizing the sprint, I'd like to get an idea how much interest there is. If you're likely to attend the summit and would be interested in staying for the code sprint, please let me know. You should include you how many days you'd be interested in sprinting for (I'd like to do at least one day, and perhaps two). Thanks, Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Uninstall script errors
On Thu, Mar 02, 2006 at 02:49:13PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Would it make sense for DROP TYPE to have some kind of limited cascade so you could drop a type and its I/O functions at the same time, but still get an error if other objects depend on the type? Seems pretty ugly. Maybe the thing to do is have a command that somehow reverts a type to the shell state, whereupon the deletion sequence can be the exact logical inverse of the creation sequence: I thought the same thing after the recent commits involving shell types and got similarly stuck. Do people at least agree that a DROP TYPE that works without CASCADE would be desirable? The rationale is the same as for other DROP commands: drop the object if nothing depends on it, else raise an error. That's impossible now because of the circular dependency between a type and its I/O functions, which requires the use of CASCADE. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] PG Extensions: Must be statically linked?
I'm creating user-defined server extensions, written in C per the manual 31.9. C-Language Functions. Everything works well, but only if I fully link the .so such that there are *no* unresolved external references at all. Not even the stuff in libstdc++.a can be left out. I've tried setting LD_LIBRARY_PATH everywhere possible, with no luck. Here's the make(1) line I have to use to link: libmyfuncs.so.0.0: gcc -Wall -Wmissing-prototypes -Wpointer-arith \ -Wdeclaration-after-statement -Wold-style-definition -Wendif-labels \ -fno-strict-aliasing -fpic -shared -Wl,-soname,libmyfuncs.so.0 \ $(OBJS) \ $(MYLIB)/lib/libmylibs.a \ /usr/lib/gcc/i386-redhat-linux/3.4.2/libstdc++.a \ -o libmyfuncs.so.0.0 Is this correct? Do Postgres extension need to be fully statically linked? Or is there some configuration that will specify LD_LIBRARY_PATH (or perhaps a Postgres-specific equivalent). The manual's instructions are good regarding writing code, but don't say much about linking. Thanks, Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG Extensions: Must be statically linked?
Craig A. James wrote: I'm creating user-defined server extensions, written in C per the manual 31.9. C-Language Functions. Everything works well, but only if I fully link the .so such that there are *no* unresolved external references at all. What happens if you don't? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PG Extensions: Must be statically linked?
Craig A. James [EMAIL PROTECTED] writes: I'm creating user-defined server extensions, written in C per the manual 31.9. C-Language Functions. Everything works well, but only if I fully link the .so such that there are *no* unresolved external references at all. Not even the stuff in libstdc++.a can be left out. If you're using libstdc++.a, you are not writing C. There is no support for C++ in the backend, and I would strongly advise not trying to use it, as any of C++'s moderately interesting features like exceptions will not play nicely with the backend environment. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] INS/UPD/DEL Returning Patch
Here's my very first proof-of-concept patch to PL/pgSQL to use the
RETURNING syntax... INSERT/UPDATE seem to work fine but I think I've
found an error with DELETE RETURNING though, so it doesn't work
properly just yet. Give this a test if you get a chance.
CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;
On 3/2/06, Jonah H. Harris [EMAIL PROTECTED] wrote:
All,
This is only the current patch updated to apply cleanly on cvs... it's
not ready for -patches yet as I still haven't spent much time looking
through it and testing it. This is just for anyone to play with
and find issues.
My focus for the next couple days is on getting INS/UPD/DEL RETURNING
ready for 8.2... all comments, suggestions, issues would be appreciated.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
diff -cr pgsql/src/pl/plpgsql/src/gram.y pgsql-iudret/src/pl/plpgsql/src/gram.y
*** pgsql/src/pl/plpgsql/src/gram.y 2006-02-12 01:37:05.0 -0500
--- pgsql-iudret/src/pl/plpgsql/src/gram.y 2006-03-02 17:08:47.0 -0500
***
*** 50,55
--- 50,56
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
static PLpgSQL_stmt *make_select_stmt(void);
+ static PLpgSQL_stmt *make_returning_stmt(char *command);
static PLpgSQL_stmt *make_fetch_stmt(void);
static void check_assignable(PLpgSQL_datum *datum);
static PLpgSQL_row *read_into_scalar_list(const char *initial_name,
***
*** 141,146
--- 142,148
%type stmt stmt_for stmt_select stmt_perform
%type stmt stmt_dynexecute stmt_getdiag
%type stmt stmt_open stmt_fetch stmt_close stmt_null
+ %type stmt stmt_insert stmt_update stmt_delete
%type list proc_exceptions
%type exception_block exception_sect
***
*** 170,175
--- 172,178
%token K_DEBUG
%token K_DECLARE
%token K_DEFAULT
+ %token K_DELETE
%token K_DIAGNOSTICS
%token K_DOTDOT
%token K_ELSE
***
*** 185,190
--- 188,194
%token K_IF
%token K_IN
%token K_INFO
+ %token K_INSERT
%token K_INTO
%token K_IS
%token K_LOG
***
*** 201,212
--- 205,218
%token K_RENAME
%token K_RESULT_OID
%token K_RETURN
+ %token K_RETURNING
%token K_RETURN_NEXT
%token K_REVERSE
%token K_SELECT
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_UPDATE
%token K_WARNING
%token K_WHEN
%token K_WHILE
***
*** 605,610
--- 611,622
{ $$ = $1; }
| stmt_select
{ $$ = $1; }
+ | stmt_insert
+ { $$ = $1; }
+ | stmt_update
+ { $$ = $1; }
+ | stmt_delete
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***
*** 1119,1124
--- 1131,1157
}
;
+ stmt_insert : K_INSERT lno
+ {
+ $$ = make_returning_stmt(INSERT);
+ $$-lineno = $2;
+ }
+ ;
+
+ stmt_update : K_UPDATE lno
+ {
+ $$ = make_returning_stmt(UPDATE);
+ $$-lineno = $2;
+ }
+ ;
+
+ stmt_delete : K_DELETE lno
+ {
+ $$ = make_returning_stmt(DELETE);
+ $$-lineno = $2;
+ }
+ ;
+
stmt_exit : exit_type lno opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***
*** 2077,2087
--- 2110,2260
execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
execsql-cmd_type = PLPGSQL_STMT_EXECSQL;
execsql-sqlstmt = expr;
+ execsql-rec = rec;
+ execsql-row = row;
return (PLpgSQL_stmt *)execsql;
}
}
+ static PLpgSQL_stmt *
+ make_returning_stmt(char *command)
+ {
+ PLpgSQL_dstring ds;
+ int nparams = 0;
+ int params[1024];
+ charbuf[32];
+ PLpgSQL_expr *expr;
+ PLpgSQL_row *row = NULL;
+ PLpgSQL_rec *rec = NULL;
+ int tok;
+ boolhave_returning = false;
+ boolhave_into = false;
+ PLpgSQL_stmt_execsql *execsql;
+
+
Re: [HACKERS] INS/UPD/DEL Returning P.tch
Jonah H. Harris wrote: All, This is only the current patch updated to apply cleanly on cvs... it's not ready for -patches yet as I still haven't spent much time looking through it and testing it. This is just for anyone to play with and find issues. Somebody else already did this in the last few days -- post a patch to -hackers instead of -patches, claiming that since it's not ready, it's not suitable for the latter. I think this is a mistake; a patch is a patch, whether it's ready for application or not, so it should be posted to pgsql-patches. You can of course indicate that the patch is a work-in-progress and that it shouldn't be applied yet. Anyway thanks for the patch, -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] INS/UPD/DEL Returning P.tch
On Thu, 2006-03-02 at 17:23 -0500, Jonah H. Harris wrote: If this is the consensus, then I'm fine with posting to -patches Yeah, -patches is the right place. I just want to make sure people are aware of it so it can get tested. I wouldn't expect a whole lot of testing. The usual process is that the person submitting the patch does all the coding and testing, then submits it to -patches. When the patch is reviewed, you might need to resubmit it a few times to satisfy the reviewers' comments. Then a committer will apply the patch (often with some additional changes, at least in my case). -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PG Extensions: Must be statically linked?
Tom Lane wrote: I'm creating user-defined server extensions, written in C per the manual 31.9. C-Language Functions. Everything works well, but only if I fully link the .so such that there are *no* unresolved external references at all. Not even the stuff in libstdc++.a can be left out. If you're using libstdc++.a, you are not writing C. There is no support for C++ in the backend, and I would strongly advise not trying to use it, as any of C++'s moderately interesting features like exceptions will not play nicely with the backend environment. Unfortunately, we're also using a second library (OpenBabel) that is written in C++. A good portion of the code I've written is a wrapper layer that hides the C++ objects and presents a simple C wrapper that works for Postgres. Craig ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Installation problem
I got an installation problem.There was an old copy of postgres installed butI want to install the modified postgres. I get problems related to locating "/usr/local/pgsql/data".I tried:[EMAIL PROTECTED] ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/dataThe files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with locale en_US.UTF-8.The default database encoding has accordingly been set to UNICODE.initdb: directory "/usr/local/pgsql/data" exists but is not emptyIf you want to create a new database system, either remove or emptythe directory "/usr/local/pgsql/data" or run initdbwith an argument other than "/usr/local/pgsql/data".Then it does not run correctly after I start the server:pg_ctl start -w -D /usr/local/pgsql/data[EMAIL PROTECTED] pgsql]$ psql -U postgres uiucWelcome to psql 8.0.3, 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 quituiuc=# \dserver closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.! \q[EMAIL PROTECTED] pgsql]$I realized that it might because I used the old */usr/local/pgsql/data/postgresql.conf* and it does not work for the new one. Then I tried to build a new directory for data, I used following comments to build a new data directory but it end up in a segmentation faulty. Do you have any suggestion on it? Thanks a lot.[EMAIL PROTECTED] ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/dbdataThe files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with locale en_US.UTF-8.The default database encoding has accordingly been set to UNICODE.fixing permissions on existing directory /usr/local/pgsql/dbdata .. okcreating directory /usr/local/pgsql/dbdata/global ... okcreating directory /usr/local/pgsql/dbdata/pg_xlog ... okcreating directory /usr/local/pgsql/dbdata/pg_xlog/archive_status ... okcreating directory /usr/local/pgsql/dbdata/pg_clog ... okcreating directory /usr/local/pgsql/dbdata/pg_subtrans ... okcreating directory /usr/local/pgsql/dbdata/base ... okcreating directory /usr/local/pgsql/dbdata/base/1 ... okcreating directory /usr/local/pgsql/dbdata/pg_tblspc ... okselecting default max_connections .. 100selecting default shared_buffers ... 1000creating configuration files ... okcreating template1 database in /usr/local/pgsql/dbdata/base/1 .. okinitializing pg_shadow ... okenabling unlimited row size for system tables ... okinitializing pg_depend ... sh: line 1: 24405 Segmentation fault "/usr/local/pgsql/bin/postgres" -F -O -c search_path=pg_catalog -c exit_on_error=true template1 /dev/nullchild process exited with exit code 139initdb: removing contents of data directory "/usr/local/pgsql/dbdata" Anyone can give me an idea? Thanks a lot. Best wishes,Xiaoqian
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
FOlks, One thing I'd like to add: we're considering organizing a code sprint for the days immediately following the conference. To add further. There will probably be a code sprint AT the conference as well. Then Monday and Tuesday for an extended code sprint. We're still discussing it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Installation problem
John [EMAIL PROTECTED] writes: initializing pg_depend ... sh: line 1: 24405 Segmentation fault=20 /usr/local/pgsql/bin/postgres -F -O -c search_path=3Dpg_catalog -c=20 exit_on_error=3Dtrue template1 /dev/null Seems like you've got a broken postgres executable there. Where did you get it from? (BTW, PG 8.0.3 is a bit behind the times: as long as you are doing a fresh install, there is no good reason not to be using 8.0.7. Or maybe even 8.1.something.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [NOVICE] pg_config --pgxs
George Weaver [EMAIL PROTECTED] writes: pg_config --version now shows: $ pg_config --version PostgreSQL 8.1.0 However, when I try pg_config --pgxs it returns nothing: $ pg_config --pgxs $ If you're on Windows this probably means that GetShortPathName() is failing. I'm not sure what conditions cause that, exactly. It might be a good idea if we fixed pg_config to print out the error code rather than just silently failing. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG Extensions: Must be statically linked?
On Thu, 02 Mar 2006 13:43:35 -0800, Craig A. James wrote: I'm creating user-defined server extensions, written in C per the manual [snip] Is this correct? Do Postgres extension need to be fully statically linked? Or is there some configuration that will specify LD_LIBRARY_PATH (or perhaps a Postgres-specific equivalent). Not generally, unless your platform requires it because of restrictive dynamic loader behaviour. contrib/xml2 uses two external libraries (libxml and libxslt) - they are dynamically referenced by the resulting pgxml.so. Have a look at the Makefile for contrib/xml2 for hints on options? The manual's instructions are good regarding writing code, but don't say much about linking. True - but I think the assumption has been that most people wouldn't have any involved linking requirements. Your case doesn't come into this category, but there are useful cases for linking to external function libraries and exposing them to PG. Regards John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Automatic free space map filling
Csaba Nagy wrote From my POV, there must be a way to speed up vacuums on huge tables and small percentage of to-be-vacuumed tuples... a 200 million rows table with frequent updates of the _same_ record is causing me some pain right now. I would like to have that table vacuumed as often as possible, but right now it only works to do it once per week due to load problems on long-running transactions preventing vacuuming other tables. Are you running 8.1? If so, you can use autovacuum and set per table thresholds (read vacuum aggressivly) and per table cost delay settings so that the performance impact is minimal. If you have tried 8.1 autovacuum and found it unhelpful, I would be curious to find out why. Matt ---(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] Foreign keys for non-default datatypes
[ returning to a week-old thread... ] Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 23 Feb 2006, Tom Lane wrote: Any thoughts about details? My feeling is that we should tie RI semantics to btree opclasses, same as we have done for ORDER BY and some other SQL constructs, but I don't have a concrete proposal right offhand. The btree idea may not cover cross-type FKs anyway. ISTM that the btree opclass is too restrictive right now since I'm guessing we'd want to allow say int4 - numeric which I don't think is in either btree opclass, but I don't know if they're not there because it wasn't worth putting in or if there's a more fundamental reason. I thought a bit more about this and realized that there's really a fairly natural way to associate an FK constraint with a btree index opclass. To wit, we already identify a unique index that the FK constraint depends on to enforce uniqueness of the PK column --- and in the current system, only btree indexes can be unique. So we can just use the opclass(es) of that index. (If we ever add uniqueness support to GiST, it would not be unreasonable to expect that the opclasses that support uniqueness identify exactly which operator they think defines equality, so we could still make it work for that.) To handle the cross-type cases, I propose that we make two checks: 1. If the index opclass contains an exact operator for the case PKtype = FKtype, use that operator. 2. Otherwise, if there's an implicit coercion from the FK column type to the PK column type, apply that coercion and use the opclass's native equality operator. If neither of these work, I think it'd be OK to fail (ie, reject creation of the FK constraint). This would have the interesting property that the current warning condition FK constraint will require costly sequential scans would become an error case. I don't really have a problem with that --- if there are any practically-useful cases where people still get that warning, it means there are gaps we ought to fill in the btree cross-type indexing support, not that we ought to go out of our way to continue supporting a very inefficient mode of FK operation. In any case, the current code is willing to try to enforce FKs that use an equality operator that we have no hard evidence works like equality at all, and that surely isn't a good idea. Comments? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Foreign keys for non-default datatypes
On Thu, Mar 02, 2006 at 08:05:59PM -0500, Tom Lane wrote: [ returning to a week-old thread... ] Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 23 Feb 2006, Tom Lane wrote: Any thoughts about details? My feeling is that we should tie RI semantics to btree opclasses, same as we have done for ORDER BY and some other SQL constructs, but I don't have a concrete proposal right offhand. The btree idea may not cover cross-type FKs anyway. ISTM that the btree opclass is too restrictive right now since I'm guessing we'd want to allow say int4 - numeric which I don't think is in either btree opclass, but I don't know if they're not there because it wasn't worth putting in or if there's a more fundamental reason. I thought a bit more about this and realized that there's really a fairly natural way to associate an FK constraint with a btree index opclass. To wit, we already identify a unique index that the FK constraint depends on to enforce uniqueness of the PK column --- and in the current system, only btree indexes can be unique. So we can just use the opclass(es) of that index. (If we ever add uniqueness support to GiST, it would not be unreasonable to expect that the opclasses that support uniqueness identify exactly which operator they think defines equality, so we could still make it work for that.) To handle the cross-type cases, I propose that we make two checks: 1. If the index opclass contains an exact operator for the case PKtype = FKtype, use that operator. 2. Otherwise, if there's an implicit coercion from the FK column type to the PK column type, apply that coercion and use the opclass's native equality operator. If neither of these work, I think it'd be OK to fail (ie, reject creation of the FK constraint). This would have the interesting property that the current warning condition FK constraint will require costly sequential scans would become an error case. I don't really have a problem with that --- if there are any practically-useful cases where people still get that warning, it means there are gaps we ought to fill in the btree cross-type indexing support, not that we ought to go out of our way to continue supporting a very inefficient mode of FK operation. In any case, the current code is willing to try to enforce FKs that use an equality operator that we have no hard evidence works like equality at all, and that surely isn't a good idea. Comments? regards, tom lane Somewhat related to this issue is that you cannot declare a primary key on creation of a table on a column which should use a separate operator class. The case in point is a subtype (domain) with a BTREE operator class. I can of course create a separate unique index, however, if I use the PRIMARY KEY syntax the op class of the data type is not recognized. I'm still thinking about the proper way this should be handled. If FKs will be able to recognize the opclass of the datatype, then the primary key should also using the same code. But if you can only create PK constraints on default BTREE opclasses then you can only create FKs on default BTREE opclasses. What I'm saying is that the opclass needs to be an option to PRIMARY KEY and FOREIGN KEY--whether implicitly in the code or explicitly in the grammar. But as I said I'm thinking about it. Perhaps there are other alternatives. --elein [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Foreign keys for non-default datatypes
elein [EMAIL PROTECTED] writes: ... What I'm saying is that the opclass needs to be an option to PRIMARY KEY and FOREIGN KEY-- PRIMARY KEY and UNIQUE, you mean. This was brought up before, but I remain less than excited about it. You can get essentially the same functionality by doing a CREATE UNIQUE INDEX command, so allowing it as part of the PK/UNIQUE syntax is little more than syntactic sugar. I'm concerned that wedging opclass names into that syntax could come back to haunt us some day --- eg, if SQL2009 decides to put their own kind of option into the same syntactic spot. The case in point is a subtype (domain) with a BTREE operator class. I can of course create a separate unique index, however, if I use the PRIMARY KEY syntax the op class of the data type is not recognized. Hm, does CREATE INDEX work without explicitly specifying the opclass? I suspect your complaint really stems from overeager getBaseType() calls in the index definition code, which is maybe fixable without having to get into syntactic extensions. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] column order in GROUP BY
The query optimizer currently does not consider reordering a query's
grouping columns. While the order in which ORDER BY columns are
specified affects the semantics of the query, AFAICS GROUP BY's column
order does not. Reordering a query's grouping columns would allow the
optimizer to avoid some unnecessary sorts; for example, given an index
on (a, b), we should be able to avoid a sort in this query:
SELECT a, b, max(c) FROM t1 GROUP BY b, a;
which the optimizer is currently incapable of doing.
I think fixing this properly would require teaching the planner that
certain PathKeys are unordered, so the planner can pick whichever order
is best. That looks like a fairly invasive change: the assumption that
PathKeyItems are ordered looks pretty widespread.
A simple hack might help with a subset of this problem, though. For
queries with both ORDER BY and GROUP BY clauses, we can sort the
grouping columns according to their position in the ORDER BY list. So,
given a query like:
SELECT a, b, max(c) FROM t1 GROUP BY a, b ORDER BY b;
We can avoid the redundant sort for the ORDER BY by grouping by (b, a)
instead. Attached is a proof-of-concept patch that implements this,
although it's an enormous kludge.
Thoughts?
-Neil
#
# old_revision [7c6bab196365c3c324210ded9cea01038fd07207]
#
# patch src/backend/optimizer/path/pathkeys.c
# from [e6be522f2cec66b12a3cc2f3c5f4f51b52b6ab57]
#to [c52e4a6a4408f81933273acecb7e0e2f59948585]
#
# patch src/backend/optimizer/plan/planmain.c
# from [379d9feab5bc2737dec63d52b3d75e1e7eb5bf30]
#to [c884241d073161bc4eaeded7dda01a6ecd3639b7]
#
# patch src/backend/optimizer/plan/planner.c
# from [5a40208a0611916228cc08497bca0ae593555b3a]
#to [94cab4ec5e862d671e80aee41f34864b415a5f5e]
#
# patch src/include/optimizer/paths.h
# from [076eea394b0c5bfb9a7fd159d39be0ace8481d32]
#to [9615bb8f8006cde7ce48dfdb2ccab54feb344c17]
#
*** src/backend/optimizer/path/pathkeys.c e6be522f2cec66b12a3cc2f3c5f4f51b52b6ab57
--- src/backend/optimizer/path/pathkeys.c c52e4a6a4408f81933273acecb7e0e2f59948585
***
*** 722,728
--- 722,787
return new_pathkeys;
}
+ void
+ reorder_group_pathkeys(PlannerInfo *root)
+ {
+ ListCell *group_pos;
+ ListCell *lc;
+ if (root-sort_pathkeys == NIL || root-group_pathkeys == NIL)
+ return;
+
+ /* If the grouping list contains just a single column, we can't reorder */
+ if (list_length(root-group_pathkeys) == 1)
+ return;
+
+ group_pos = NULL;
+ foreach (lc, root-sort_pathkeys)
+ {
+ List *order_by_key = lfirst(lc);
+ ListCell *prev;
+ ListCell *match;
+
+ /* Look for the current order_by_key in the grouping list */
+ prev = NULL;
+ if (group_pos)
+ match = lnext(group_pos);
+ else
+ match = list_head(root-group_pathkeys);
+
+ while (match)
+ {
+ List *key = (List *) lfirst(match);
+
+ if (key == order_by_key)
+ {
+ root-group_pathkeys = list_delete_cell(root-group_pathkeys,
+ match, prev);
+
+ if (group_pos == NULL)
+ {
+ root-group_pathkeys = lcons(key, root-group_pathkeys);
+ group_pos = list_head(root-group_pathkeys);
+ }
+ else
+ {
+ group_pos = lappend_cell(root-group_pathkeys,
+ group_pos, key);
+ }
+
+ break;
+ }
+
+ prev = match;
+ match = lnext(match);
+ }
+
+ if (!match)
+ return;
+ }
+ }
+
+
/*
* count_canonical_peers
* Given a PathKeyItem, find the equi_key_list subset it is a member of,
*** src/backend/optimizer/plan/planmain.c 379d9feab5bc2737dec63d52b3d75e1e7eb5bf30
--- src/backend/optimizer/plan/planmain.c c884241d073161bc4eaeded7dda01a6ecd3639b7
***
*** 171,181
* Also canonicalize the groupClause and sortClause pathkeys for use
* later.
*/
- root-query_pathkeys = canonicalize_pathkeys(root, root-query_pathkeys);
root-group_pathkeys = canonicalize_pathkeys(root, root-group_pathkeys);
root-sort_pathkeys = canonicalize_pathkeys(root, root-sort_pathkeys);
/*
* Ready to do the primary planning.
*/
final_rel = make_one_rel(root, joinlist);
--- 171,198
* Also canonicalize the groupClause and sortClause pathkeys for use
* later.
*/
root-group_pathkeys = canonicalize_pathkeys(root, root-group_pathkeys);
root-sort_pathkeys = canonicalize_pathkeys(root, root-sort_pathkeys);
/*
+ * The order in which the GROUP BY columns are specified does not
+ * affect the semantics of the query. Currently the planner does
+ * not realize this: in general, it assumes the ordering of
+ * PathKeys is important. As a quick hack, we reorder the grouping
+ * columns to match their position in the ORDER BY list. This
+ * allows us to avoid a redundant sort for queries like GROUP BY
+ * a, b ORDER BY b.
+ */
+ reorder_group_pathkeys(root);
+
+ if
Re: [HACKERS] to_char and i18n
Added to TODO: * Add missing parameter handling in to_char() http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php I added a URL in TODO because it is a single message of detail I need to reference. --- Gavin Sherry wrote: On Wed, 21 Dec 2005, Tom Lane wrote: Manuel Sugawara masm@fciencias.unam.mx writes: (Some time ago I proposed an--incomplete--patch and it was rejectd by Karel arguing that to_char functions should behave *exactly* the same way that they do in Oracle.) That is the accepted plan for to_char ... of course, if Oracle changes to_char every so often, it'll get more interesting to decide what to do. There's some functionality in 10g which PostgreSQL does not have: TZD - returns the short timezone string with daylight saving information, eg: PDT TZM - timezone offset minutes part TZH - timezone offset hours part TZR - timezone region (US/Pacific, for example) RR/ - accept 'rounded' years, eg 99-1-1 = 1999-1-1 (kind of pointless) FF - specify how many digits to the right of the decimal place to display, when looking at factions of seconds. Eg: HH:MM:SS.FF3 would produce 15:56:22.123 X - the local radix character. Eg: HH:MM:SSXFF would produce 15:56:22.123 E - Era name (like, Japanese Imperial) (kind of pointless) EE - Full era name DS - Locale formatted short date. For example, DD/MM/ for the Brits, MM/DD/ for the Yanks DL - Locale formatted long date. Eg: fmDay, dd. Month in Germany SCC - Like 'CC', but will carry a - (minus) for BC dates (I'm not sure if this implies that Oracle wants BC dates to be marked 'BC'. I don't have an Oracle system around at the moment to check though :-() TS - Locale formatted short time. YEAR - Year in words SYEAR - Year in words, prefixed by minus sign for BC dates S - , prefixed by minus sign for BC dates Gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.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] PostgreSQL Anniversary Summit, Call for Contributions
The world rejoiced as [EMAIL PROTECTED] (Marc G. Fournier) wrote: Just curious, but what is involved in these invitations? For instance, is there a limit on # of invitations any one person(?) or company can issue? Are there any legal implications of issuing such an invitation? I could imagine some pretty hot water if pre 9/11 someone were to invite bin Laden to a conference, and had the twin towers go down while he was here, for instance ... Here should be the authoritative information: http://www.cic.gc.ca/english/visit/visas.html Countries/Territories Requiring Visas http://www.cic.gc.ca/english/visit/letter.html Letter of Invitation for Countries Whose Citizens Require a Temporary Resident Visa to Enter Canada If an .se address implies Swedish citizenship, there's good news; Swedes don't need a visa to come to Canada. Ditto for pretty well all of Western Europe, all of North America (including Mexico), and Japan. I expect that most of those likely to need visas (and letters) will hearken from Eastern Europe or Asia. It's worth noting that whomever is providing that letter of invitation has to be prepared to send, to our foreign friends, a photocopy of our own Canadian birth certificate or some equivalent thereof. Not to say that this is *spectacularly* intimate information, but I daresay people would Not Be Pleased if such material got misused. There is some fairness there; the requirements are nicely laid out, and the intimacies go in both directions. The other pointy bit is that the letter of invitation needs to indicate the inviter's relationship to the person being invited. I expect that would need to be a tad more specific than merely he's some guy from Sweden that I heard about on the Internet... What this all implies is that these Letters of Invitation do indeed impose a certain degree of legal burden (whether highly formalized or not) such that I'm sure NOT going to be heading to the printers so I can send them out by the gross... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://linuxfinances.info/info/languages.html Once you accept that the world is a giant computer run by white mice, all other movies fade into insignificance. -- Mutsumi Takahashi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving free space usage (was: Reducing relation locking overhead)
Added to TODO: * Allow FSM to return free space toward the beginning of the heap file, in hopes that empty pages at the end can be truncated by VACUUM --- Jim C. Nasby wrote: On Fri, Dec 09, 2005 at 12:00:14AM +0200, Hannu Krosing wrote: Along those lines, I've wondered if it makes sense to add more flexibility in how free space is reclaimed in a table. One obvious possibility is to have a strategy where new tuples will always look to the FSM for space (instead of going into the current page if possible), and the FSM will always hand out the earliest page in the table it has. This mode would have the effect of moving tuples towards the front of the table, allowing for space reclamation. A variation might be that this mode will not effect tuples that are generated as part of an UPDATE and are in the first x% of the table, since it doesn't make sense to move a tuple from page 2 to page 1 in a 1000 page table. This % could be depending on some fill factor of the table, aiming not to move tuples, that would end up in the final volume of a balance table, which, in case of heavily updated table, would probably be 2 to 3 times the volume of densely populated table. Another possibility is to always go to the FSM and to have the FSM hand back the page that is closest to the new tuple according to a certain index. This would allow for ALTER TABLE CLUSTER to be much more self-maintaining. The downside is that you'd have to do a lookup on that index, but presumably if the index is important enough to cluster on then it should be well-cached. There's probably some other tweaks that could be done as well to make this more performant. Yes, I agree on all your points about better placement of new tuples, all they would be useful indeed. Sounds like a TODO, barring objections... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Vertical Partitioning with TOAST
Is there still interst in this idea for TODO?
---
Jim C. Nasby wrote:
On Thu, Dec 08, 2005 at 10:03:43AM -0500, Bruce Momjian wrote:
Jim C. Nasby wrote:
On Thu, Dec 08, 2005 at 12:59:47AM -0500, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
This seems like a useful feature to add, allowing for easy built-in
verticle partitioning. Are there issues with the patch as-is?
Other than the ones mentioned by the poster?
It seemed to me more like a not-too-successful experiment than something
ready for application. If you take the viewpoint that this is just
another TOAST storage strategy, I think it's pretty useless. A large
field value is going to get toasted anyway with the regular strategy,
and if your column happens to contain some values that are not large,
forcing them out-of-line anyway is simply silly. (You could make a case
for making the threshold size user-controllable, but I don't see the
case for setting the threshold to zero, which is what this amounts to.)
Valid point. I do think there's a lot of benefit to being able to set
the limit much lower than what it currently defaults to today. We have a
client that has a queue-type table that is updated very frequently. One
of the fields is text, that is not updated as frequently. Keeping this
table vacuumed well enough has proven to be problematic, because any
delay to vacuuming quickly results in a very large amount of bloat.
Moving that text field into a seperate table would most likely be a win.
Presumably this would need to be settable on at least a per-table basis.
Would adding such a variable be a good beginner TODO, or is it too
invasive?
Well, we have now:
ALTER TABLE ALTER [ COLUMN ] column
SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
What else is needed?
As Tom suggested, I think it would be best to be able to change the size
at which a field gets stored externally. I think it also makes sense to
have this reverse the normal order of compress first, then if it still
doesn't fit store it externally. I forsee this typically being useful
when you have fields that are between ~100 and 1000 bytes in size, and
I'm doubtful that compression would do much good there. But I wouldn't
rule out this being useful on fields that can also sometimes contain
much larger amounts of data, so I don't think it makes sense to disable
compression completely. So, I think this leaves two new options:
SET STORAGE EXTERNAL [THRESHOLD x]
If a field is over x in size, it's stored externally.
SET STORAGE EXTENDED [THRESHOLD x]
If a field is over x in size, it's stored externally. If it's over
BLCKSZ/4 it will also be compressed (I think that's how things work
now).
Actually, that's rather ugly. I think it would be better to just break
external storage and compression out into their own attributes:
SET STORAGE [ ALLOW EXTERNAL [THRESHOLD x] ] [ ALLOW COMPRESSION [THRESHOLD
x] ]
ALLOW EXTERNAL: if a field is larger than x bytes (BLCKSZ/4 by default)
then it will be stored externally. May be specified along with ALLOW
COMPRESSION.
ALLOW COMPRESSION: if a field is larger than x bytes (BLCKSZ/4 by default)
then it will be COMPRESSED. May be specified along with ALLOW EXTERNAL.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.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] PostgreSQL Anniversary Summit, Call for Contributions
Christopher Browne [EMAIL PROTECTED] writes: Letter of Invitation for Countries Whose Citizens Require a Temporary Resident Visa to Enter Canada I missed that this was happening up here in Canada. How exclusive is the guest list for this? Like, are you only expecting 50 top contributors by invitation only or is anyone who can make it welcome? What kind of costs are anticipated? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Inherited Constraints
Where are we on this patch? My testing shows it is still shows we have a problem: test= CREATE TABLE x(y INT CHECK(y 0)); CREATE TABLE test= CREATE TABLE z(a INT) inherits (x); CREATE TABLE test= ALTER TABLE z DROP CONSTRAINT x_y_check; ALTER TABLE test= ALTER TABLE x DROP CONSTRAINT x_y_check; ALTER TABLE Deleting the parent constraint first does not require CASCADE, as it should, I think: test= CREATE TABLE x(y INT CHECK(y 0)); CREATE TABLE test= CREATE TABLE z(a INT) inherits (x); CREATE TABLE test= ALTER TABLE x DROP CONSTRAINT x_y_check; ALTER TABLE test= ALTER TABLE z DROP CONSTRAINT x_y_check; ERROR: CONSTRAINT x_y_check does NOT exist --- Simon Riggs wrote: On Thu, 2005-12-08 at 11:10 +, Simon Riggs wrote: On Wed, 2005-12-07 at 21:24 +, Simon Riggs wrote: Following patch implements record of whether a constraint is inherited or not, and prevents dropping of inherited constraints. Patch posted to -patches list. What it doesn't do: It doesn't yet prevent dropping the parent constraint, which is wrong, clearly, but what to do about it? 1. make dropping a constraint drop all constraints dependent upon it (without any explicit cascade) 2. add a new clause to ALTER TABLE DROP CONSTRAINT CASCADE I prefer (1), since it is SQL Standard compliant, easier to remember and automatic de-inheritance is the natural opposite of the automatic inheritance process. Comments, please -hackers? Late night hacking again ALTER TABLE DROP CONSTRAINT CASCADE does of course already exist, so the following should cause dependency violation ERRORs: - omitting the CASCADE when attempting to delete parent constraint - attempting to drop the child constraint Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] column order in GROUP BY
Neil Conway [EMAIL PROTECTED] writes: A simple hack might help with a subset of this problem, though. For queries with both ORDER BY and GROUP BY clauses, we can sort the grouping columns according to their position in the ORDER BY list. So, given a query like: SELECT a, b, max(c) FROM t1 GROUP BY a, b ORDER BY b; We can avoid the redundant sort for the ORDER BY by grouping by (b, a) instead. Attached is a proof-of-concept patch that implements this, although it's an enormous kludge. I think that's the wrong place. transformGroupClause is the right place. It already does some hacking to try to make the GROUP BY semantics match ORDER BY, but it doesn't think to try reordering the GROUP BY items. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bug in 7.3.2
On 2/28/06, Suvarna [EMAIL PROTECTED] wrote: we are using postgresql 7.3.2 version. As somebody pointed out, that's not a bug... but i think you must upgrade at least to 7.3.14 even if you really found a bug nobody will fix it for 7.3.2 -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Foreign keys for non-default datatypes
On Thu, 2 Mar 2006, Tom Lane wrote: [ returning to a week-old thread... ] Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 23 Feb 2006, Tom Lane wrote: Any thoughts about details? My feeling is that we should tie RI semantics to btree opclasses, same as we have done for ORDER BY and some other SQL constructs, but I don't have a concrete proposal right offhand. The btree idea may not cover cross-type FKs anyway. ISTM that the btree opclass is too restrictive right now since I'm guessing we'd want to allow say int4 - numeric which I don't think is in either btree opclass, but I don't know if they're not there because it wasn't worth putting in or if there's a more fundamental reason. I thought a bit more about this and realized that there's really a fairly natural way to associate an FK constraint with a btree index opclass. To wit, we already identify a unique index that the FK constraint depends on to enforce uniqueness of the PK column --- and in the current system, only btree indexes can be unique. So we can just use the opclass(es) of that index. (If we ever add uniqueness support to GiST, it would not be unreasonable to expect that the opclasses that support uniqueness identify exactly which operator they think defines equality, so we could still make it work for that.) To handle the cross-type cases, I propose that we make two checks: 1. If the index opclass contains an exact operator for the case PKtype = FKtype, use that operator. Is this rule to read explicitly naming '=' or just the item in that position in the opclass? 2. Otherwise, if there's an implicit coercion from the FK column type to the PK column type, apply that coercion and use the opclass's native equality operator. If neither of these work, I think it'd be OK to fail (ie, reject creation of the FK constraint). This would have the interesting property that the current warning condition FK constraint will require costly sequential scans would become an error case. I don't really have a problem with that --- if there are any practically-useful cases where people still get that warning, it means there are gaps we ought to fill in the btree cross-type indexing support, not that we ought to go out of our way to continue supporting a very inefficient mode of FK operation. In any case, the current code is willing to try to enforce FKs that use an equality operator that we have no hard evidence works like equality at all, and that surely isn't a good idea. I think it's an acceptable idea to fail if we're going to extend the cross-type indexing support, but AFAICS we have to at the very least allow all of the standard numeric types in all combinations to work to meet the spec, and I don't think the above rules and current opclasses will give that to us (and I don't honestly understand some of the bits of this to know if there's a problem with extending the opclasses to allow that). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign keys for non-default datatypes
Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 2 Mar 2006, Tom Lane wrote: 1. If the index opclass contains an exact operator for the case PKtype = FKtype, use that operator. Is this rule to read explicitly naming '=' or just the item in that position in the opclass? The operator occupying the equality position in the opclass. I think it's an acceptable idea to fail if we're going to extend the cross-type indexing support, but AFAICS we have to at the very least allow all of the standard numeric types in all combinations to work to meet the spec, and I don't think the above rules and current opclasses will give that to us (and I don't honestly understand some of the bits of this to know if there's a problem with extending the opclasses to allow that). The cases that are likely to be problematic are things like a FLOAT8 column referencing a NUMERIC primary key. However, that sort of mishmash is fraught with all kinds of risks anyway (think roundoff error) so the fact that the spec nominally allows it doesn't tell me that we ought to encourage it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
[EMAIL PROTECTED] (Greg Stark) writes: Christopher Browne [EMAIL PROTECTED] writes: Letter of Invitation for Countries Whose Citizens Require a Temporary Resident Visa to Enter Canada I missed that this was happening up here in Canada. How exclusive is the guest list for this? Like, are you only expecting 50 top contributors by invitation only or is anyone who can make it welcome? What kind of costs are anticipated? It's not intended to be punitively high priced, so as to keep it exclusive, but the more expensive you find it to travel to Toronto, the more you'll find it costs, naturally... I'll probably grouse about parking costs a bit, at some point, but I won't have a thousand dollar plane ticket to pay for, to be sure... ;-) I think there is some desire to have some amount of funding provided for travel/accomodations based on what can be raised thru SPI; that's certainly still a matter in flux. The answers aren't clear yet... -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://www3.sympatico.ca/cbbrowne/ Rules of the Evil Overlord #31. All naive, busty tavern wenches in my realm will be replaced with surly, world-weary waitresses who will provide no unexpected reinforcement and/or romantic subplot for the hero or his sidekick. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
btw, how expensive is to go to the Niagara waterfall from Toronto ? I'd like to take an opportunity to see it. Oleg On Fri, 3 Mar 2006, Chris Browne wrote: [EMAIL PROTECTED] (Greg Stark) writes: Christopher Browne [EMAIL PROTECTED] writes: Letter of Invitation for Countries Whose Citizens Require a Temporary Resident Visa to Enter Canada I missed that this was happening up here in Canada. How exclusive is the guest list for this? Like, are you only expecting 50 top contributors by invitation only or is anyone who can make it welcome? What kind of costs are anticipated? It's not intended to be punitively high priced, so as to keep it exclusive, but the more expensive you find it to travel to Toronto, the more you'll find it costs, naturally... I'll probably grouse about parking costs a bit, at some point, but I won't have a thousand dollar plane ticket to pay for, to be sure... ;-) I think there is some desire to have some amount of funding provided for travel/accomodations based on what can be raised thru SPI; that's certainly still a matter in flux. The answers aren't clear yet... Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char and i18n
Gavin Sherry wrote: On Wed, 21 Dec 2005, Tom Lane wrote: Manuel Sugawara masm@fciencias.unam.mx writes: (Some time ago I proposed an--incomplete--patch and it was rejectd by Karel arguing that to_char functions should behave *exactly* the same way that they do in Oracle.) That is the accepted plan for to_char ... of course, if Oracle changes to_char every so often, it'll get more interesting to decide what to do. There's some functionality in 10g which PostgreSQL does not have: TZD - returns the short timezone string with daylight saving information, eg: PDT This is the same as TZ and it is easy to implement. TZM - timezone offset minutes part Trivial TZH - timezone offset hours part Trivial TZR - timezone region (US/Pacific, for example) We don't currently have an offset - region name lookup table but it should be easy enough to implement... RR/ - accept 'rounded' years, eg 99-1-1 = 1999-1-1 (kind of pointless) FF - specify how many digits to the right of the decimal place to display, when looking at factions of seconds. Eg: HH:MM:SS.FF3 would produce 15:56:22.123 Trivial X - the local radix character. Eg: HH:MM:SSXFF would produce 15:56:22.123 I don't know how to get this character... is it included in the locale data some where (and where, specifically) E - Era name (like, Japanese Imperial) (kind of pointless) EE - Full era name No idea where to get this data. DS - Locale formatted short date. For example, DD/MM/ for the Brits, MM/DD/ for the Yanks Is this desirable? It may lead to confusion with datestyle. DL - Locale formatted long date. Eg: fmDay, dd. Month in Germany Should be straight forward - if the underlying library will honour locale. SCC - Like 'CC', but will carry a - (minus) for BC dates (I'm not sure if this implies that Oracle wants BC dates to be marked 'BC'. I don't have an Oracle system around at the moment to check though :-() Thoughts? TS - Locale formatted short time. Should be straight forward - if the underlying library will honour locale. YEAR - Year in words Hmmm. This would be hard to do if we want to support local language settings. SYEAR - Year in words, prefixed by minus sign for BC dates As above. S - , prefixed by minus sign for BC dates Should be straight forward. Any comments on the above? Gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char and i18n
E - Era name (like, Japanese Imperial) (kind of pointless) EE - Full era name Some stuff here: http://java.sun.com/javase/6/docs/guide/intl/calendar.doc.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
On Fri, 3 Mar 2006 09:07:06 +0300 (MSK) Oleg Bartunov oleg@sai.msu.su wrote: how expensive is to go to the Niagara waterfall from Toronto ? I'd like to take an opportunity to see it. If you are driving, Niagara Falls is about one hour from Toronto. Cost is a tank of gas and parking. Looking at the falls is free. There are special tours like the Maid of the Mist (a boat that goes to the base of the falls) and a tour through the tunnels behind the falls which have some cost. Not a particularly expensive side trip. Those of us who live here should think about some entertainment possibilities. -- D'Arcy J.M. Cain darcy@druid.net | 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] to_char and i18n
Added to TODO:
* Allow to_date() and to_timestamp() accept localized month names
Comment added to the C code to show where it has to happen.
---
Karel Zak wrote:
On Sun, 2005-12-25 at 17:56 -0300, Euler Taveira de Oliveira wrote:
--- Euler Taveira de Oliveira [EMAIL PROTECTED] escreveu:
I have a patch like this. But this was for 7.4.x. I have to take a
look
at it.
The patch is attached. It implements day and month i18n. I fixed a few
misspelling comments. Docs is attached too.
template1=# select to_char(now(), 'Day, DD Month ');
to_char
--
Sunday , 25 December 2005
(1 registro)
template1=# select to_char(now(), 'TMDay, DD TMMonth ');
to_char
---
Domingo, 25 Dezembro 2005
(1 registro)
template1=#
Comments?
I think it looks like a good patch. There's small problem that the
current to_char() output is possible use as argument for to_timestamp()
or to_date() function. It means you should implement vice-versa
conversion from string with TMMonth/TMDay to timestamp.
to_timestamp('Domingo, 25 Dezembro 2005', 'TMDay, DD TMMonth ')
Or.. at least describe in the docs that this way is unsupported
for 'TM' prefix.
Karel
--
Karel Zak [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.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
