Re: [HACKERS] [PATCHES] Solve a problem of LC_TIME of windows.
Hiroshi Saito [EMAIL PROTECTED] wrote: I think that MinGW does not have a direct relation. #define_UNICODE is required for wcsftime. Probably, ITAGAKI-san has only forgotten it.:-) No, definition of _UNICODE is independent from wcsftime (and other wcs functions). It affects only _tcs functions (_tcsftime). Wednesday in japanese should be the following sequences in unicode: wcs = 6c34 66dc 65e5 -- sui yo bi I rebuild my test on VC++2005 SP1, but it has the same bug. So, we cannot use wcsftime in Windows unless we build binaries in VC++2008 at least (and the bug is fixed there). Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Solve a problem of LC_TIME of windows.
ITAGAKI Takahiro wrote: Hiroshi Saito [EMAIL PROTECTED] wrote: I think that MinGW does not have a direct relation. #define_UNICODE is required for wcsftime. Probably, ITAGAKI-san has only forgotten it.:-) No, definition of _UNICODE is independent from wcsftime (and other wcs functions). It affects only _tcs functions (_tcsftime). Wednesday in japanese should be the following sequences in unicode: wcs = 6c34 66dc 65e5 -- sui yo bi I rebuild my test on VC++2005 SP1, but it has the same bug. So, we cannot use wcsftime in Windows unless we build binaries in VC++2008 at least (and the bug is fixed there). Please call setlocale(LC_CTYPE/LC_ALL, ) first. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Solve a problem of LC_TIME of windows.
Hiroshi Inoue [EMAIL PROTECTED] wrote: Please call setlocale(LC_CTYPE/LC_ALL, ) first. Ah, it works! But setlocale(*, ) means that we always use platform locale (Japanese and SJIS in Japan). It could be different from server encoding and locale in postgres. Is it acceptable? I think we need to set LC_CTYPE and other LC_* independently... Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
David Rowley wrote: I've created a query that uses the table in your regression test. max_salary1 gives incorrect results. If you remove the max_salary2 column it gives the correct results. Thanks. I saw this myself yesterday, while hacking on the patch. I thought it was a bug I had introduced, but apparently it was there all along. Anyway, fixed in the latest version I will send shortly. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert, big thanks for your review. I think #1 is still partially valid, because it contains general cleanups, but part of it is not necessary now. #2, #3 and #4 you can move to return with feedback section. Thanks Zdenek Robert Haas napsal(a): Zdenek - I am a bit murky on where we stand with upgrade-in-place in terms of reviewing. Initially, you had submitted four patches for this commitfest: 1. htup and bufpage API clean up 2. HeapTuple version extension + code cleanup 3. In-place online upgrade 4. Extending pg_class info + more flexible TOAST chunk size I think that it was decided that replacing the heap tuple access macros with function calls was not acceptable, so I have moved patches #1 and #2 to the Returned with feedback section. I thought that perhaps the third patch could be salvaged, but the consensus seemed to be to go in a new direction, so I'm thinking that one should probably be moved to Returned with feedback as well. However, I'm not clear on whether you will be submitting something else instead and whether that thing should be considered material for this commitfest. Can you let me know how you are thinking about this? With respect to #4, I know that Alvaro submitted a draft patch, but I'm not clear on whether that needs to be reviewed, because: - I'm not sure whether it's close enough to being finished for a review to be a good use of time. - I'm not sure how much you and Heikki have already reviewed it. - I'm not sure whether this patch buys us anything by itself. Thoughts? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enhancement to pg_dump
Rob Kirkbride wrote: I've introduced a --delete-not-drop option which simply does a DELETE FROM % rather than 'DROP and then CREATE'. Beware foreign-keys slowing you - TRUNCATE all relevant tables should be the fastest method if possible. I hope this sounds sensible and I haven't missed something - I'm still learning! Have you considered restoring to a completely different database (report1/report2) and just switching between them? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Solve a problem of LC_TIME of windows.
ITAGAKI Takahiro wrote: Hiroshi Inoue [EMAIL PROTECTED] wrote: Please call setlocale(LC_CTYPE/LC_ALL, ) first. Ah, it works! But setlocale(*, ) means that we always use platform locale (Japanese and SJIS in Japan). Maybe you can call setlocale(LC_CTYPE, .20932) instead and you would get CP20932 encoding. The encoding of LC_TIME or LC_MESSAGES has little meaning. It could be different from server encoding and locale in postgres. Is it acceptable? I think we need to set LC_CTYPE and other LC_* independently... Seems LC_CTYPE and LC_TIME should be convertible even though we use wcsftime (which internally calls strftime?). As for gettext(LC_MESSAGES) on Windows we can set LC_CTYPE independently because it is unrelated to the output encoding. In addition we can call bind_textdomain_codeset to change the output encoding. I'm providing a patch to adjust the output encoding of Windows gettext. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Automatic view update rules
--On Dienstag, November 25, 2008 23:43:02 -0500 Robert Haas [EMAIL PROTECTED] wrote: Do you intend to submit an updated version of this patch for this commitfest? I'll do asap, i've updated the status to 'waiting on author'. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/11/26 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/26 David Rowley [EMAIL PROTECTED]: I'm at a bit of a loss to what to do now. Should I wait for your work Heikki? Or continue validating this patch? The best thing I can think to do right now is continue and any problems I find you can add regression tests for, then if we keep your regression tests for Heikki's changes then we can validate those changes more quickly. Any thoughts? Better ideas? Thanks to your great tests, we now know much more about specification and where to fail easily, so continuing makes sense but it may be good time to take a rest and wait for Heikki's patch completing. Here's another updated patch, including all your bug fixes. There's two known issues: - ranking functions still don't treat peer rows correctly. - I commented out the this function requires ORDER BY clause in the window test in rank_up, because a window function shouldn't be poking into the WindowState struct like that. I wonder if it's really needed? In section 7.11, the SQL2008 spec says if WD has no window ordering clause, then the window ordering is implementation-dependent, and *all rows are peers*. The regression test now fails because of this, but the current behavior actually seems correct to me. Yes, I was wrong. The reason I put the error in rank() without ORDER BY is nothing but I didn't find it. It is actually a reasonable specification, isn't it. This is tiny thing, but negative transition function can be called inverse transition function? I feel the latter is more readable. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Brittleness in regression test setup
Tom Lane wrote: AFAICS the only way you'd end up with a zombie postmaster is if pg_ctl stop fails, but I'm failing to understand why that's likely to happen. No, the zombies appear if the postmaster dies (briefly) after launch. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for better PQExpBuffer out-of-memory behavior
On Tue, Nov 25, 2008 at 10:33:05AM -0500, Tom Lane wrote: I've been chewing on the problem described here: http://archives.postgresql.org/pgsql-general/2008-11/msg01220.php It's not particularly easy to fix without making annoyingly large changes to the API for PQExpBuffer. Yup, I've just realized that my very naive suggestion have having a matching function to return something useful wouldn't be good as almost all the functions return void and would introduce the most enormous duplicity. The best idea I have come up with so far goes like this: * Upon failure to malloc or realloc the buffer for a PQExpBuffer, the pqexpbuffer.c code should release whatever buffer it might have had and set data = pointer to empty, statically allocated string len = 0 maxlen = 0 This is distinguishable from the normal non-error case because maxlen can never be zero in non-error cases. * All subsequent operations except resetPQExpBuffer will do nothing to such a PQExpBuffer. resetPQExpBuffer will attempt to restore the string to normal empty status by allocating a new default-size buffer. Sounds like a reasonable compromise. Would it be better to have this string be something like ## out of memory in enlargePQExpBuffer ##? That way, if something doesn't check correctly we've got some way to determine where things went wrong rather than just ending up with an empty string? Or are strings the only special case and most other types will bomb out upon receiving an empty literal. The only alternative that I can think of that avoids the latter disadvantage is to allow the pqexpbuffer routines to abort on out-of-memory (ie, printf(stderr) and exit(1)). This seems pretty unpleasant though for functions that are part of libpq's infrastructure. If there's no way to avoid the abort then this sounds nasty! In particular, although we could allow the calling application to override such behavior via some sort of callback hook function, it's far from clear what it could do instead without risking bizarre misbehavior by libpq. It doesn't seem obvious to me how these callback functions could do anything useful. They would still need some way of returning an error to the outside world which would imply some sort of mechanism, like the one above, to allow this to happen. I'd be happy writing a patch for this if you want. There appear to be a couple of thousand references to the PQExpBuffer code, but I can't imagine needing to touch all of them. Sam -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/11/26 Heikki Linnakangas [EMAIL PROTECTED]: Hitoshi Harada wrote: I read more, and your spooling approach seems flexible for both now and the furture. Looking at only current release, the frame with ORDER BY is done by detecting peers in WinFrameGetArg() and add row number of peers to winobj-currentpos. Actually if we have capability to spool all rows we need on demand, the frame would be only a boundary problem. Yeah, we could do that. I'm afraid it would be pretty slow, though, if there's a lot of peers. That could probably be alleviated with some sort of caching, though. It seems to me that eval_windowaggregate() also should use frame APIs. Only things we have to care is the shrinking frame, which is not supported in this release. So I'd suggest winobj-aggregatedupto to be removed. Is there objection? Actually, I took a different approach in the latest patch. Window aggregates now use a separate read pointer into the tuplestore. The current row is also read using a separate read pointer in ExecWindow. The aggregate and current row read pointers don't need random access, which has the nice effect that if you only use window aggregates, not window functions, the tuplestore doesn't need random access, and doesn't need to spill to disk as long as the window frame fits in work_mem. We should still figure out how to make it possible to trim the tuplestore, when window functions that don't need random access are used. Like ROW_NUMBER and RANK. Earlier, I thought we should add function to the window object API to explicitly tell that tuples before row X are no longer needed. But I'm now starting to wonder if we should design the window object API more like the tuplestore API, with a read pointer that you can advance forward or backward, and rewind. That would probably map more naturally to the underlying tuplestore, and it seems like it would be just as easy to use in all the existing window functions. Complete solution, at least for the current release. I now figure out exactly what the tuplestore_trim does. So currentrow pointer doesn't need go backward, neither does extending frame's aggregate pointer, row_number, rank, etc. Cutting off frame's aggregate need random row, so does lead, lag, etc. Even there were random access, it's very flexible in triming and saving memory. Little concern is some operations like WinRowIsPeer() doesn't know if the required row is trimmed already, which isn't big problem in the existing functions. Now you might think about sharing aggregate code like initialize/advance/finalize. If you want I can refactor in nodeAgg.c to be able sharing with nodeWindow.c, which wouldn't conflict with your work. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visibility map, partial vacuums
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: The visibility map won't be inquired unless you vacuum. This is a bit tricky. In vacuum, we only know whether we can set a bit or not, after we've acquired a cleanup lock on the page, and scanned all the tuples. While we're holding a cleanup lock, we don't want to do I/O, which could potentially block out other processes for a long time. So it's too late to extend the visibility map at that point. This is no good; I think you've made the wrong tradeoffs. In particular, even though only vacuum *currently* uses the map, you want to extend it to be used by indexscans. So it's going to uselessly spring into being even without vacuums. I'm not convinced that I/O while holding cleanup lock is so bad that we should break other aspects of the system to avoid it. However, if you want to stick to that, how about * vacuum page, possibly set its header bit * release page lock (but not pin) * if we need to set the bit, fetch the corresponding map page (I/O might happen here) * get share lock on heap page, then recheck its header bit; if still set, set the map bit Yeah, could do that. There is another problem, though, if the map is frequently probed for pages that don't exist in the map, or the map doesn't exist at all. Currently, the size of the map file is kept in relcache, in the rd_vm_nblocks_cache variable. Whenever a page is accessed that's rd_vm_nblocks_cache, smgrnblocks is called to see if the page exists, and rd_vm_nblocks_cache is updated. That means that every probe to a non-existing page causes an lseek(), which isn't free. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enhancement to pg_dump
Rob Kirkbride [EMAIL PROTECTED] writes: Richard, Yes, I've changed it use TRUNCATE rather than DELETE and it's working well for us now. I'm a bit surprised actually as it sounded like you were aiming to avoid the table lock. A TRUNCATE does require an exclusive lock on the table. It still has advantages over DROP in that there is no window when the table does not exist and any existing references to the table from views or functions will continue to function. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enhancement to pg_dump
I must admit I've not read up on the various locks that are set so that's a good point. Is there a good reference for me to read and understand these? I'm guessing though that a delete from and then an insert never requires an exclusive lock, what about adding/deleting constraints? Rob 2008/11/26 Gregory Stark [EMAIL PROTECTED] Rob Kirkbride [EMAIL PROTECTED] writes: Richard, Yes, I've changed it use TRUNCATE rather than DELETE and it's working well for us now. I'm a bit surprised actually as it sounded like you were aiming to avoid the table lock. A TRUNCATE does require an exclusive lock on the table. It still has advantages over DROP in that there is no window when the table does not exist and any existing references to the table from views or functions will continue to function. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
Re: [HACKERS] Visibility map, partial vacuums
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: There is another problem, though, if the map is frequently probed for pages that don't exist in the map, or the map doesn't exist at all. Currently, the size of the map file is kept in relcache, in the rd_vm_nblocks_cache variable. Whenever a page is accessed that's rd_vm_nblocks_cache, smgrnblocks is called to see if the page exists, and rd_vm_nblocks_cache is updated. That means that every probe to a non-existing page causes an lseek(), which isn't free. Well, considering how seldom new pages will be added to the visibility map, it seems to me we could afford to send out a relcache inval event when that happens. Then rd_vm_nblocks_cache could be treated as trustworthy. Maybe it'd be worth doing that for the FSM too. The frequency of invals would be higher, but then again the reference frequency is probably higher too? A relcache invalidation sounds awfully heavy-weight. Perhaps a light-weight invalidation event that doesn't flush the entry altogether, but just resets the cached sizes? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Joshua D. Drake [EMAIL PROTECTED] writes: On Tue, 2008-11-25 at 20:33 -0500, Tom Lane wrote: So we really don't have any methodically-gathered evidence about the effects of different stats settings. It wouldn't take a lot to convince us to switch to a different default, I think, but it would be nice to have more than none. I don't this is not empirical but really, 150 is very reasonable. Let's just set it to that by default and be done with it. What happened to the more than zero evidence part of the discussion? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Tom Lane [EMAIL PROTECTED] writes: Dann Corbit [EMAIL PROTECTED] writes: I also do not believe that there is any value that will be the right answer. But a table of data might be useful both for people who want to toy with altering the values and also for those who want to set the defaults. I guess that at one time such a table was generated to produce the initial estimates for default values. Sir, you credit us too much :-(. The actual story is that the current default of 10 was put in when we first implemented stats histograms, replacing code that kept track of only a *single* most common value (and not very well, at that). So it was already a factor of 10 more stats than we had experience with keeping, and accordingly conservatism suggested not boosting the default much past that. I think that's actually too little credit. The sample size is chosen quite carefully based on solid mathematics to provide a specific confidence interval estimate for queries covering ranges the size of a whole bucket. The actual number of buckets more of an arbitrary choice. It depends entirely on how your data is distributed and how large a range your queries are covering. A uniformly distributed data set should only need a single bucket to generate good estimates. Less evenly distributed data sets need more. I wonder actually if there are algorithms for estimating the number of buckets needed for a histogram to achieve some measurable goal. That would close the loop. It would be much more reassuring to base the size of the sample on solid statistics than on hunches. So we really don't have any methodically-gathered evidence about the effects of different stats settings. It wouldn't take a lot to convince us to switch to a different default, I think, but it would be nice to have more than none. I think the difficulty (aside from testing being laborious at the best of times) is that it's heavily dependent on data sets which are hard to generate good examples for. Offhand I would think the census data might make a good starting point -- it should have columns which range from perfectly uniform to highly skewed. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visibility map, partial vacuums
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Well, considering how seldom new pages will be added to the visibility map, it seems to me we could afford to send out a relcache inval event when that happens. Then rd_vm_nblocks_cache could be treated as trustworthy. A relcache invalidation sounds awfully heavy-weight. It really isn't. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Hot standby
On Wed, Nov 26, 2008 at 3:52 PM, Pavan Deolasee [EMAIL PROTECTED]wrote: I think whats happening is that ResolveRecoveryConflictWithVirtualXIDs() is failing to abort the open transaction Btw, ISTM that SIGINT works only for statement cancellation. So if the transaction is in idle state, SIGINT has nothing to cancel and hence also fails to abort the transaction. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Brittleness in regression test setup
Tom Lane wrote: I'd vote for keeping the --temp-port option but not having the Makefile use it. Seems like it'd still be potentially useful for hand use of pg_regress. Sorry, I didn't document this fully. The --temp-port option appears to be redundant with the --port option, so I figured we could drop the former and just use the latter for both the temp install and existing install cases. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Brittleness in regression test setup
Peter Eisentraut [EMAIL PROTECTED] writes: Then again, a simple way to avoid the issue altogether on platforms supporting Unix-domain sockets would be to run the test over Unix-domain sockets (which we do anyway) placed in a private directory. How about that? Then the brittleness is still there on Windows, only we'd probably get confused and think it was a platform-specific bug. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
Hitoshi Harada wrote: I read more, and your spooling approach seems flexible for both now and the furture. Looking at only current release, the frame with ORDER BY is done by detecting peers in WinFrameGetArg() and add row number of peers to winobj-currentpos. Actually if we have capability to spool all rows we need on demand, the frame would be only a boundary problem. Yeah, we could do that. I'm afraid it would be pretty slow, though, if there's a lot of peers. That could probably be alleviated with some sort of caching, though. It seems to me that eval_windowaggregate() also should use frame APIs. Only things we have to care is the shrinking frame, which is not supported in this release. So I'd suggest winobj-aggregatedupto to be removed. Is there objection? Actually, I took a different approach in the latest patch. Window aggregates now use a separate read pointer into the tuplestore. The current row is also read using a separate read pointer in ExecWindow. The aggregate and current row read pointers don't need random access, which has the nice effect that if you only use window aggregates, not window functions, the tuplestore doesn't need random access, and doesn't need to spill to disk as long as the window frame fits in work_mem. We should still figure out how to make it possible to trim the tuplestore, when window functions that don't need random access are used. Like ROW_NUMBER and RANK. Earlier, I thought we should add function to the window object API to explicitly tell that tuples before row X are no longer needed. But I'm now starting to wonder if we should design the window object API more like the tuplestore API, with a read pointer that you can advance forward or backward, and rewind. That would probably map more naturally to the underlying tuplestore, and it seems like it would be just as easy to use in all the existing window functions. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Hot standby
ISTM that the redo conflict resolution is not working as intended. I did the following test and it throws some surprises. On standby: postgres=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ; BEGIN postgres=# SELECT * from test; a | b -+--- 102 | 103 | (2 rows) On primary: postgres=# SELECT * from test; a | b -+--- 102 | 103 | (2 rows) postgres=# postgres=# UPDATE test SET a = a + 100; UPDATE 2 postgres=# VACUUM test; VACUUM postgres=# SELECT pg_switch_xlog(); pg_switch_xlog 0/2D000288 (1 row) On standby (server log): LOG: restored log file 0001002D from archive LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 LOG: recovery cancels activity of virtual transaction 2/2 pid 10593 because it blocks exclusive locks (current delay now 5 secs) CONTEXT: xlog redo exclusive relation lock: slot 99 db 11517 rel 24576 same message repeated The open transaction (see above) on the standby is not still not aborted and if I query the table in the same transaction, I get: (Note: the transaction is still open) postgres=# postgres=# SELECT * from test; a | b ---+--- (0 rows) I think whats happening is that ResolveRecoveryConflictWithVirtualXIDs() is failing to abort the open transaction and it keeps trying for that, everytime doubling the sleep time, so the LOG messages come less frequently later, but they are never ending. Soon the sleep becomes exponentially large. Even though the standby has a open transaction, its obvious that the cleanup_redo has also failed to abort the transaction. Thats why the tuples have disappeared from the standby (most likely because they are cleaned up by VACUUM). Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Column reordering in pg_dump
Robert Haas [EMAIL PROTECTED] writes: Imagine for example: CREATE TABLE foo (c1 integer, c2 text, c3 boolean, c4 date, c5 timestamp, c6 numeric, c7 varchar); CREATE OR REPLACE VIEW tricky AS SELECT * FROM foo AS bar (a, b, c); ALTER TABLE foo ALTER COLUMN c2 POSITION LAST; After some thought, it seems pretty clear, at least to me, that the third (hypothetical) command should not change the result of SELECT * FROM tricky (the contrary conclusion gives rise to a lot of problems, especially if there are other views depending on it). But what will pg_dump -t tricky output at this point? I don't think it's as bad as you fear, because you can always insert additional aliases that aren't changing the column names. Furthermore, per spec the column ordering of tricky doesn't change when foo's does. So immediately after the CREATE VIEW tricky ought to look like SELECT a, b, c, c4, c5, c6, c7 FROM foo AS bar (a, b, c); which we could also represent as SELECT a, b, c, c4, c5, c6, c7 FROM foo AS bar (a, b, c, c4, c5, c6, c7); and the column position change would morph this into SELECT a, b, c, c4, c5, c6, c7 FROM foo AS bar (a, c, c4, c5, c6, c7, b); Now admittedly the current internal representation of alias-lists doesn't cope with that (unless maybe you consider that list position corresponds to column identity), but that representation isn't set in stone. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Updated interval patches - ECPG [was, intervalstyle....]
On Thu, Nov 20, 2008 at 05:07:40PM -0800, Ron Mayer wrote: Got it. Patch attached. Looks reasonable to me. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Updated interval patches - ECPG [was, intervalstyle....]
Michael Meskes [EMAIL PROTECTED] writes: On Thu, Nov 20, 2008 at 05:07:40PM -0800, Ron Mayer wrote: Patch attached. Looks reasonable to me. Michael, since that's ecpg code, please take charge of committing it if you want it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What's going on with pgfoundry?
Today I noticed I cannot login to cvs.pgfoundry.org anymore since the IP address has been changed am asked password which seems to be changed. So I cannot use CVS any more. Does anybody why this happens and how to fix it? -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
1. htup and bufpage API clean up 2. HeapTuple version extension + code cleanup 3. In-place online upgrade 4. Extending pg_class info + more flexible TOAST chunk size big thanks for your review. I think #1 is still partially valid, because it contains general cleanups, but part of it is not necessary now. #2, #3 and #4 you can move to return with feedback section. OK, when can you submit a new version of #1 with the parts that are still valid, updated to CVS HEAD, etc? Thanks, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, Nov 26, 2008 at 2:43 PM, Tatsuo Ishii [EMAIL PROTECTED] wrote: Today I noticed I cannot login to cvs.pgfoundry.org anymore since the IP address has been changed am asked password which seems to be changed. So I cannot use CVS any more. Does anybody why this happens and how to fix it? It's the same IP address - but try port 35 for ssh. Marc changed it (temporarily) due to a vast number of malicious connection attempts. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas escribió: With respect to #4, I know that Alvaro submitted a draft patch, but I'm not clear on whether that needs to be reviewed, because: - I'm not sure whether it's close enough to being finished for a review to be a good use of time. - I'm not sure how much you and Heikki have already reviewed it. - I'm not sure whether this patch buys us anything by itself. I finished that patch, but I didn't submit it because in later discussion it turned out (at least as I read it) that it's considered to be unnecessary. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Brittleness in regression test setup
Alvaro Herrera wrote: Is it possible to make it retry in case the chosen port is busy? I guess a simple check should suffice, ignoring the obvious race condition that someone uses the port after you checked it was OK. Well, the whole point of this exercise was to avoid that. If we had a way to do a simple check, we might as well stick to the hardcoded port and count up from that or something. The problem with doing the checking is that you have to emulate the complete postmaster logic for port numbers, listen addresses, Unix domain socket directories, etc. That can become quite involved. Then again, a simple way to avoid the issue altogether on platforms supporting Unix-domain sockets would be to run the test over Unix-domain sockets (which we do anyway) placed in a private directory. How about that? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visibility map, partial vacuums
Heikki Linnakangas [EMAIL PROTECTED] writes: There is another problem, though, if the map is frequently probed for pages that don't exist in the map, or the map doesn't exist at all. Currently, the size of the map file is kept in relcache, in the rd_vm_nblocks_cache variable. Whenever a page is accessed that's rd_vm_nblocks_cache, smgrnblocks is called to see if the page exists, and rd_vm_nblocks_cache is updated. That means that every probe to a non-existing page causes an lseek(), which isn't free. Well, considering how seldom new pages will be added to the visibility map, it seems to me we could afford to send out a relcache inval event when that happens. Then rd_vm_nblocks_cache could be treated as trustworthy. Maybe it'd be worth doing that for the FSM too. The frequency of invals would be higher, but then again the reference frequency is probably higher too? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enhancement to pg_dump
Richard, Yes, I've changed it use TRUNCATE rather than DELETE and it's working well for us now. The switching of the database is a good idea - thanks. Unfortunately, we've not got enough disk space currently to do that, but if we get problems in the future that will definitely be something we'll consider. Rob Richard Huxton wrote: Rob Kirkbride wrote: I've introduced a --delete-not-drop option which simply does a DELETE FROM % rather than 'DROP and then CREATE'. Beware foreign-keys slowing you - TRUNCATE all relevant tables should be the fastest method if possible. I hope this sounds sensible and I haven't missed something - I'm still learning! Have you considered restoring to a completely different database (report1/report2) and just switching between them? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] what is necessary for filling SysCache?
Hello
I added two new columns to pg_proc. I have a problem because access
via SearchSysCache doesn't work
/* Search syscache by name only */
catlist = SearchSysCacheList(PROCNAMEARGSNSP, 1,
CStringGetDatum(funcname),
0, 0, 0);
for (i = 0; i catlist-n_members; i++)
{
HeapTuple proctup = catlist-members[i]-tuple;
Form_pg_proc procform = (Form_pg_proc) GETSTRUCT(proctup);
...
elog(NOTICE, %d, SysCacheGetAttr(PROCOID, proctup,
Anum_pg_proc_prondefargs, isnull));
elog(NOTICE, %d %d, procform-prondefargs, procform-pronargs);
result:
postgres=# select fo(10,20);
NOTICE: 1
NOTICE: 0 1
NOTICE: 2
NOTICE: 0 2
NOTICE: 2
NOTICE: 0 3
fo
10
(1 row)
what is mechanism, that select between directly accessed fields and
fields that are accessed via SysCacheGetAttr?
Thank you
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Alvaro Herrera napsal(a): Robert Haas escribió: With respect to #4, I know that Alvaro submitted a draft patch, but I'm not clear on whether that needs to be reviewed, because: - I'm not sure whether it's close enough to being finished for a review to be a good use of time. - I'm not sure how much you and Heikki have already reviewed it. - I'm not sure whether this patch buys us anything by itself. I finished that patch, but I didn't submit it because in later discussion it turned out (at least as I read it) that it's considered to be unnecessary. From pg_upgrade perspective, it is something what we will need do anyway. Because TOAST_MAX_CHUNK_SIZE will be different in 8.5 (if you commit CRC). Then we will need the patch for 8.5. It is not necessary for 8.3-8.4 upgrade because TOAST_MAX_CHUNK_SIZE is same. And make this change into toast table now will add unnecessary complexity. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what is necessary for filling SysCache?
Pavel Stehule [EMAIL PROTECTED] writes: I added two new columns to pg_proc. I have a problem because access via SearchSysCache doesn't work Well, you blew the catalog modifications somewhere, but since you haven't shown us what you did it's hard to guess where. You might want to pull the diffs for some past pg_proc addition from CVS and go over the changes. This one is a good minimal example: http://archives.postgresql.org/pgsql-committers/2005-03/msg00433.php Also, not sure if this is relevant, but all the fixed-width columns have to come first. Anything that's past a var-width column has to be fetched via SysCacheGetAttr --- you can't fetch it as a struct member. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what is necessary for filling SysCache?
some more info:
CATALOG(pg_proc,1255) BKI_BOOTSTRAP
{
NameDataproname;/* procedure name */
Oid pronamespace; /* OID of namespace
containing this proc */
Oid proowner; /* procedure owner */
Oid prolang;/* OID of
pg_language entry */
float4 procost;/* estimated execution cost */
float4 prorows;/* estimated # of rows
out (if proretset) */
Oid provariadic;/* element type of
variadic array, or 0 */
boolproisagg; /* is it an aggregate? */
boolprosecdef; /* security definer */
boolproisstrict;/* strict with respect to NULLs? */
boolproretset; /* returns a set? */
charprovolatile;/* see PROVOLATILE_ categories below */
int2pronargs; /* number of arguments */
Oid prorettype; /* OID of result type */
/* VARIABLE LENGTH FIELDS: */
oidvector proargtypes;/* parameter types (excludes
OUT params) */
Oid proallargtypes[1]; /* all
param types (NULL if IN o
charproargmodes[1]; /* parameter modes (NULL if IN only) */
textproargnames[1]; /* parameter names (NULL if no names) */
int2prondefargs;/* number of
default arguments */ -- new
textprodefargs; /* default
arguments */ -- new
textprosrc; /* procedure source text */
bytea probin; /* secondary procedure
info (can be NULL) */
textproconfig[1]; /* procedure-local GUC settings */
aclitem proacl[1]; /* access permissions */
} FormData_pg_proc;
#define Schema_pg_proc \
{ 1255, {proname},19, -1, NAMEDATALEN, 1, 0,
-1, -1, false, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {pronamespace}, 26, -1, 4, 2, 0, -1, -1,
true, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {proowner}, 26, -1, 4, 3, 0, -1, -1,
true, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {prolang},26, -1, 4, 4, 0, -1, -1,
true, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {procost}, 700, -1, 4, 5, 0, -1, -1,
FLOAT4PASSBYVAL, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {prorows}, 700, -1, 4, 6, 0, -1, -1,
FLOAT4PASSBYVAL, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {provariadic},26, -1, 4, 7, 0, -1, -1,
true, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {proisagg}, 16, -1, 1, 8, 0, -1, -1,
true, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {prosecdef}, 16, -1, 1, 9, 0, -1, -1,
true, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {proisstrict},16, -1, 1, 10, 0, -1, -1,
true, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {proretset}, 16, -1, 1, 11, 0, -1, -1,
true, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {provolatile},18, -1, 1, 12, 0, -1, -1,
true, 'p', 'c', true, false, false, true, 0 }, \
{ 1255, {pronargs}, 21, -1, 2, 13, 0, -1, -1,
true, 'p', 's', true, false, false, true, 0 }, \
{ 1255, {prorettype}, 26, -1, 4, 14, 0, -1, -1,
true, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {proargtypes},30, -1, -1, 15, 1, -1, -1,
false, 'p', 'i', true, false, false, true, 0 }, \
{ 1255, {proallargtypes}, 1028, -1, -1, 16, 1, -1, -1, false, 'x',
'i', false, false, false, true, 0 }, \
{ 1255, {proargmodes}, 1002, -1, -1, 17, 1, -1, -1, false,
'x', 'i', false, false, false, true, 0 }, \
{ 1255, {proargnames}, 1009, -1, -1, 18, 1, -1, -1, false,
'x', 'i', false, false, false, true, 0 }, \
{ 1255, {prondefargs},21, -1, 2, 19, 0, -1,
-1, true, 'p', 's', true, false, false, true, 0 }, \
{ 1255, {prodefargs}, 25, -1, -1, 20, 0, -1,
-1, false, 'x', 'i', false, false, false, true, 0 }, \
{ 1255, {prosrc}, 25, -1, -1, 21, 0, -1,
-1, false, 'x', 'i', false, false, false, true, 0 }, \
{ 1255, {probin}, 17, -1, -1, 22, 0, -1,
-1, false, 'x', 'i', false, false, false, true, 0 }, \
{ 1255, {proconfig},1009, -1, -1, 23, 1, -1, -1, false,
'x', 'i', false, false, false, true, 0 }, \
{ 1255, {proacl}, 1034, -1, -1, 24, 1, -1, -1,
false, 'x', 'i', false, false, false, true, 0 }
This is only one problem - after my changes regression test are passed
Pavel
2008/11/26 Tom
Re: [HACKERS] what is necessary for filling SysCache?
Hi, Le mercredi 26 novembre 2008, Tom Lane a écrit : You might want to pull the diffs for some past pg_proc addition from CVS and go over the changes. This one is a good minimal example: http://archives.postgresql.org/pgsql-committers/2005-03/msg00433.php The following link should help the lazy clic browse amongst us: http://git.postgresql.org/?p=postgresql.git;a=commitdiff;h=578ce39692571e39fd0e677c079b05fad52d Hope this helps ;) -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Visibility map, partial vacuums
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Well, considering how seldom new pages will be added to the visibility map, it seems to me we could afford to send out a relcache inval event when that happens. Then rd_vm_nblocks_cache could be treated as trustworthy. A relcache invalidation sounds awfully heavy-weight. It really isn't. Okay, then. I'll use relcache invalidation for both the FSM and visibility map. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what is necessary for filling SysCache?
Pavel Stehule [EMAIL PROTECTED] writes: some more info: /* VARIABLE LENGTH FIELDS: */ oidvector proargtypes;/* parameter types (excludes OUT params) */ Oid proallargtypes[1]; /* all param types (NULL if IN o charproargmodes[1]; /* parameter modes (NULL if IN only) */ textproargnames[1]; /* parameter names (NULL if no names) */ int2prondefargs;/* number of default arguments */ -- new Well, you ignored the rule about fixed-width before variable-width columns. That's why there's a big VARIABLE LENGTH FIELDS marker comment there ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] what is necessary for filling SysCache?
2008/11/26 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: some more info: /* VARIABLE LENGTH FIELDS: */ oidvector proargtypes;/* parameter types (excludes OUT params) */ Oid proallargtypes[1]; /* all param types (NULL if IN o charproargmodes[1]; /* parameter modes (NULL if IN only) */ textproargnames[1]; /* parameter names (NULL if no names) */ int2prondefargs;/* number of default arguments */ -- new Well, you ignored the rule about fixed-width before variable-width columns. That's why there's a big VARIABLE LENGTH FIELDS marker comment there ... I though it, but I believed so there is some smart mechanism :) thank you Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
On 11/26/08, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: On Mon, Nov 24, 2008 at 10:25 AM, Marko Kreen [EMAIL PROTECTED] wrote: IOW, DISCARD ALL should be functionally equivalent to backend exit. Having done a lot of work with advisory locks, I support this change. Advisory locks are essentially session scoped objects like prepared statements or notifies. It's only natural to clean them up in the same way. That said, I don't think this should be backpatched to 8.3. Done but not back-patched. I think this should be back-patched as well: - The fact that disconnect will clean up used resources has been always true, thus most clients assume at some level. - DISCARD ALL was new feature in 8.3. It is highly doubtful some adv-locks using project has managed to hard-code dependency on buggy behaviour of DISCARD. - The bug was reported by regular user who encountered deadlocks on 8.3 because of it. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Updated interval patches - ECPG [was, intervalstyle....]
On Wed, Nov 26, 2008 at 09:31:48AM -0500, Tom Lane wrote: Michael, since that's ecpg code, please take charge of committing it if you want it. Okay, done. I wasn't sure whether this was related to a backend patch that was still under review. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard -- Statistics idea...
On Tue, Nov 25, 2008 at 06:59:25PM -0800, Dann Corbit wrote: I do have a statistics idea/suggestion (possibly useful with some future PostgreSQL 9.x or something): It is a simple matter to calculate lots of interesting univarate summary statistics with a single pass over the data (perhaps during a vacuum full). For instance with numerical columns, you can calculate mean, min, max, standard deviation, skew, kurtosis and things like that with a single pass over the data. Calculating interesting univariate summary statistics and having something useful to do with them are two different things entirely. Note also that whereas this is simple for numeric columns, it's a very different story for non-numeric data types, that don't come from a metric space. That said, the idea of a probability metric space is well explored in the literature, and may have valuable application. The current histogram implementation is effectively a description of the probability metric space the column data live in. Now, if you store a few numbers calculated in this way, it can be used to augment your histogram data when you want to estimate the volume of a request. So (for instance) if someone asks for a scalar that is value you can look to see what percentage of the tail will hang out in that neck of the woods using standard deviation and the mean. Only if you know that the data follow a distribution that can be described accurately with a standard deviation and a mean. If your data don't follow a Gaussian distribution, this will give you bad estimates. - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] Re: Updated interval patches - ECPG [was, intervalstyle....]
Michael Meskes [EMAIL PROTECTED] writes: On Wed, Nov 26, 2008 at 09:31:48AM -0500, Tom Lane wrote: Michael, since that's ecpg code, please take charge of committing it if you want it. Okay, done. I wasn't sure whether this was related to a backend patch that was still under review. No, the backend part went in some time ago. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, 26 Nov 2008, Dave Page wrote: It's the same IP address - but try port 35 for ssh. Marc changed it (temporarily) due to a vast number of malicious connection attempts. Why wasn't this change communicated to anyone, not even gforge-admins? How temporary is temporary? Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
Heikki Linnakangas [EMAIL PROTECTED] writes: Here's another updated patch, including all your bug fixes. I did a very fast pass through this and have a few comments. * Don't bother manually updating keywords.sgml. As stated therein, that table is automatically generated. All you'll accomplish is to cause merge problems. (The effort would be a lot better spent on the non-boilerplate parts of the docs anyway.) * I assume there's going to be some way to create user-defined window functions? * It seems fairly unlikely that you can get away with not supporting any qual expression on a Window plan node. What will you do with HAVING qualifiers? * The find_aggref code added to planagg.c (where it doesn't belong anyway) doesn't seem to be used anywhere. * In the same vein, I'm unimpressed with moving GetAggInitVal into execGrouping.c, which it isn't at all related to. I'd just leave it alone and duplicate the code in nodeWindow.c --- it's not exactly large. If you did insist on sharing this code it would be appropriate to change the name and comments to reflect the fact that it's being used for more than just aggregates, anyhow. * And in the same vein. var.c is hardly the place to put a search-for-wfuncs routine. * It seems like a coin was flipped to determine whether struct and field names would use window, win, or just w (I find WFunc to be particularly unhelpful to a reader who doesn't already know what it is). Please try to reduce the surprise factor. I'd suggest consistently using window in type names, though win is an OK prefix for field names within window-related structs. * This is a bad idea: /* + * OrderClause - + * representation of ORDER BY in Window + */ + typedef SortGroupClause OrderClause; + + + /* + * PartitionClause - + * representaition of PATITION BY in Window + */ + typedef SortGroupClause PartitionClause; If they're just SortGroupClauses, call them that, don't invent an alias. (Yes, I know SortClause and GroupClause used to be aliases. That was a bad idea: it confused matters and required lots of useless duplicated code, except for the places where we didn't duplicate code because we were willing to assume struct equivalence. There's basically just nothing that wins about that approach.) In any case, order and partition are really bad names to be using here given the number of possible other meanings for those terms in a DBMS context. If you actually need separate struct types then names like WindowPartitionClause would be appropriate. * The API changes chosen for func_get_detail seem pretty bizarre. Why didn't you just add a new return code FUNCDETAIL_WINDOW? * The node support needs to be gone over more closely. I noticed just in random checking that WFunc is missing parse-location support in nodeFuncs.c and the Query.hasWindow field got added to copyfuncs, outfuncs, readfuncs, but not equalfuncs. * Please heed the comment at the top of parallel_schedule about the max number of tests per parallel group. * I don't find the test added to opr_sanity.sql to be particularly sane. We *will* have the ability to add window functions. But it might be helpful to check that proisagg and proiswfunc aren't both set. * errcodes.h is not the only place that has to be touched to add a new error code --- see also sgml/errcodes.sgml, plpgsql/src/plerrcodes.h. And what is your precedent for using 42813? I don't see that in the standard. If it's coming from DB2 it's okay, otherwise we should be using a private 'P' code. * Please try to eliminate random whitespace changes from the patch ... *particularly* in files that otherwise wouldn't be touched at all (there are at least two cases of that in this patch) That's all I have time for right now ... more to come no doubt. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
On Wed, Nov 26, 2008 at 11:06 AM, Marko Kreen [EMAIL PROTECTED] wrote: I think this should be back-patched as well: - The fact that disconnect will clean up used resources has been always true, thus most clients assume at some level. - DISCARD ALL was new feature in 8.3. It is highly doubtful some adv-locks using project has managed to hard-code dependency on buggy behaviour of DISCARD. - The bug was reported by regular user who encountered deadlocks on 8.3 because of it. I see your point but there's a pretty high standard for changing existing behavior in bugfix releases. It's just as likely to introduce an application bug as to fix one...suppose the application is using both 'discard all' for prepared statements and advisory locks for other purposes. You could break that application. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Kris Jurka wrote: On Wed, 26 Nov 2008, Dave Page wrote: It's the same IP address - but try port 35 for ssh. Marc changed it (temporarily) due to a vast number of malicious connection attempts. Why wasn't this change communicated to anyone, not even gforge-admins? How temporary is temporary? Kris Jurka I can't speak to the administrative and communications aspects, but based on my experience, I can recommend communicating to the appropriate users and making the change permanent. I have changed the external ssh port on all machines I administer. The result is the complete elimination of the previous hundreds to thousands of daily script-kiddie brute-force attempts I used to see. Obscurity should not be your *only* line of defense, but camouflage helps as well. And even if it didn't, it still reduces server-load, bandwidth and heaps of logfile cruft. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, 26 Nov 2008, Steve Crawford wrote: Obscurity should not be your *only* line of defense, but camouflage helps as well. And even if it didn't, it still reduces server-load, bandwidth and heaps of logfile cruft. In order case, thankfully, there was minimal banwidth impact, but the server load on some of the machines was to the point of unusability ... again, thankfully, that didn't manifest it self on any of the postgresql servers, but we didn't want to take any chances of it bleeding over ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments to Synchronous replication patch v3
On Tue, Nov 25, 2008 at 12:23:45PM -0300, Alvaro Herrera wrote: Fujii Masao escribió: On Tue, Nov 25, 2008 at 10:57 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Dickson S. Guedes escribió: Fujii Masao escreveu: (...) Even if we need to have the database in real, I'd like to use another name for it. The name 'walsender' seems to be an internal module name but it should be a feature name (ex. 'replication'). Agreed. The name 'replication' is more suitable, I also think. Any other ideas? 'walsender' should be a schema in the 'replication' database. Other modules, in replication feature, could be placed there too. Hmm, what is this database there for? It's for authentication for replication. This was discussed before. Please see the following thread and feel free to comment. http://archives.postgresql.org/pgsql-hackers/2008-11/msg00187.php Hmm ... I think this means that the suggestion by Dickson does not make much sense, right? It sounds to me like this should use SQL/MED connections, if it's holding auth information :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enhancement to pg_dump
Rob Kirkbride [EMAIL PROTECTED] writes: I must admit I've not read up on the various locks that are set so that's a good point. Is there a good reference for me to read and understand these? I'm guessing though that a delete from and then an insert never requires an exclusive lock, what about adding/deleting constraints? There is documentation http://www.postgresql.org/docs/8.3/static/explicit-locking.html However I found it very confusing when I was first learning. It's not really the documentation's fault either, there are just a lot of different lock levels with a lot of different combinations possible. All DML, even selects, take a table-level shared lock on the tables involved which blocks the tables from being dropped or truncated while the query is running. DELETE and UPDATE (and SELECT FOR UPDATE) take exclusive row-level locks. A SELECT can read the old version of the record but another UPDATE will block until your transaction finishes so it can update the most recent version. But an update which doesn't need to look at that record won't be affected at all. TRUNCATE and DROP take exclusive table-level locks which blocks anyone else from even selecting from the table. It also means they can't proceed until all queries which have already started reading the table finish. DROP is still a lot heavier than TRUNCATE because it also has to drop (or search for and throw an error) anything else dependent on the table. triggers, views, etc. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
I see your point but there's a pretty high standard for changing existing behavior in bugfix releases. It's just as likely to introduce an application bug as to fix one...suppose the application is using both 'discard all' for prepared statements and advisory locks for other purposes. You could break that application. I would expect someone to use DEALLOCATE ALL for that purpose, but it is true that people don't always do what you expect... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
Merlin Moncure [EMAIL PROTECTED] writes: On Wed, Nov 26, 2008 at 11:06 AM, Marko Kreen [EMAIL PROTECTED] wrote: I think this should be back-patched as well: - The fact that disconnect will clean up used resources has been always true, thus most clients assume at some level. - DISCARD ALL was new feature in 8.3. It is highly doubtful some adv-locks using project has managed to hard-code dependency on buggy behaviour of DISCARD. - The bug was reported by regular user who encountered deadlocks on 8.3 because of it. I see your point but there's a pretty high standard for changing existing behavior in bugfix releases. It's just as likely to introduce an application bug as to fix one...suppose the application is using both 'discard all' for prepared statements and advisory locks for other purposes. You could break that application. DISCARD ALL was specifically added in 8.3 for the purpose of connection poolers to be a big hammer that exactly emulates a new session. I'm somewhat skeptical that there are any applications using it directly at all, and doubly so that they would be using it and expecting advisory locks to persist. I think the second and third points are pretty convincing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments to Synchronous replication patch v3
David Fetter wrote: It sounds to me like this should use SQL/MED connections, if it's holding auth information :) No, the SQL/MED stuff holds authentication information to authenticate to other data sources. This is about authentication of *incoming* connections. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logging auto_explain outputs to another log file
On Sunday 23 November 2008 15:50:09 Andrew Dunstan wrote: Tom Lane wrote: Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= [EMAIL PROTECTED] writes: Is $SUBJECT possible? If not, do you think it would be worth implementing this? No, and no. The feature isn't even in core; it can hardly qualify as something that should drive a massive overhaul of the elog infrastructure. Which is what this would take. Well, it might be possible to build some sort of splitting facility (regex based?) into the logging collector without having to change the rest of the logging infrastructure. But there are already good log splitting tools for some varieties of syslog, and like Tom I suspect using any effort in this direction on our part is probably not worth it. How would you folks feel about adding a dtrace probe to look for this? I haven't exactly worked out where/how this would be put, but it would allow for easily tracking these via dtrace if we had one. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard -- Statistics idea...
On Nov 25, 2008, at 8:59 PM, Dann Corbit wrote: It is a simple matter to calculate lots of interesting univarate summary statistics with a single pass over the data (perhaps during a vacuum full). I don't think that the problem we have is how to collect statistics (well, except for cross-field stuff); the problem is what to actually do with them. What we need people to look at is how we can improve query plan estimates across the board. Row count estimates, page access estimates, the cost estimates for accessing those pages, etc. This isn't a coding problem, it's an algorithm problem. It needs someone with an advanced (if not expert) grasp of statistics who can come up with better ways of estimating these things. So, if you have a statistics hammer to wield, I think you'll find a lot of nails sticking up in the planner code. Hammer on those before worrying about additional stats to collect. :) -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote: The thought occurs to me that we're looking at this from the wrong side of the coin. I've never, ever seen query plan time pose a problem with Postgres, even without using prepared statements. I certainly have seen plan times be a problem. I wonder if you have too and just didn't realize it. With a default_stats_target of 1000 you'll have hundreds of kilobytes of data to slog through to plan a moderately complex query with a few text columns. Forget about prepared queries, I've seen plan times be unusable for ad-hoc interactive queries before. Can you provide any examples? And no, I've never seen a system where a few milliseconds of plan time difference would pose a problem. I'm not saying they don't exist, only that I haven't seen them (including 2 years working as a consultant). I'll also make the argument that anyone with a system that does have those kind of requirements will have also needed to actually tune their config, and tune it well. I can't see them being bothered by having to set one more parameter. There are a lot of systems that are being impacted by our ultra-low stats target, and a lot of those don't necessarily need a lot of hand tuning beyond the stats target. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
On Wed, Nov 26, 2008 at 8:13 PM, Gregory Stark [EMAIL PROTECTED] wrote: DISCARD ALL was specifically added in 8.3 for the purpose of connection poolers to be a big hammer that exactly emulates a new session. I'm somewhat skeptical that there are any applications using it directly at all, and doubly so that they would be using it and expecting advisory locks to persist. I think the second and third points are pretty convincing. I kinda agree with you. The only problem IMHO is that we described in the doc exactly what it does and not simply as the big hammer it was supposed to be. See http://www.postgresql.org/docs/8.3/interactive/sql-discard.html . You can't guarantee that nobody checked the doc to see if it discards advisory locks even if it's highly unlikely. That said, I'm more for the backpatching too. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Column reordering in pg_dump
On Nov 25, 2008, at 9:41 PM, Robert Haas wrote: Changing physical positioning is purely an internal matter. A first-cut implementation should probably just make it identical to logical positioning, until the latter is changed by the user (after which, physical positioning continues to reflect the original ordering). Only after this work has been done and gotten battle-tested, we can get into niceties like having the server automatically rearrange physical positioning to improve performance. Yeah. The problem with that is that, as Tom pointed out in a previous iteration of this discussion, you will likely have lurking bugs. The bugs are going to come from confusing physical vs. logical vs. column identity, and if some of those are always-equal, it's gonna be pretty hard to know if you have bugs that confuse the two. Now, if you could run the regression tests with a special option that would randomly permute the two orderings with respect to one another, that would give you at least some degree of confidence... Random is good, but I suspect there are some boundary cases that could be tested too. As for the complexity, it might make sense to only tackle part of this at a time. There would be value in only allowing logical order to differ from literal order, or only allowing physical order to differ. That means you could tackle just one of those for the first go-round and still get a benefit from it. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Decibel! [EMAIL PROTECTED] writes: On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote: The thought occurs to me that we're looking at this from the wrong side of the coin. I've never, ever seen query plan time pose a problem with Postgres, even without using prepared statements. I certainly have seen plan times be a problem. I wonder if you have too and just didn't realize it. With a default_stats_target of 1000 you'll have hundreds of kilobytes of data to slog through to plan a moderately complex query with a few text columns. Forget about prepared queries, I've seen plan times be unusable for ad-hoc interactive queries before. Can you provide any examples? At the time I couldn't understand what the problem was. In retrospect I'm certain this was the problem. I had a situation where just running EXPLAIN took 5-10 seconds. I suspect I had some very large toasted arrays which were having to be detoasted each time. IIRC I actually reloaded the database with pg_dump and the problem went away. And no, I've never seen a system where a few milliseconds of plan time difference would pose a problem. I'm not saying they don't exist, only that I haven't seen them (including 2 years working as a consultant). How many milliseconds does it take to read a few hundred kilobytes of toasted, compressed data? These can easily be more data than the actual query is going to read. Now ideally this will all be cached but the larger the data set the less likely it will be. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Steve Crawford wrote: I have changed the external ssh port on all machines I administer. The result is the complete elimination of the previous hundreds to thousands of daily script-kiddie brute-force attempts I used to see. +1 We have not used port 22 in our production network for years; for all the same reasons. Although its only obfuscation, it works. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments to Synchronous replication patch v3
On Wed, Nov 26, 2008 at 09:15:49PM +0200, Heikki Linnakangas wrote: David Fetter wrote: It sounds to me like this should use SQL/MED connections, if it's holding auth information :) No, the SQL/MED stuff holds authentication information to authenticate to other data sources. This is about authentication of *incoming* connections. Thanks for clearing that up :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, Nov 26, 2008 at 10:51:23AM -0800, Steve Crawford wrote: Kris Jurka wrote: On Wed, 26 Nov 2008, Dave Page wrote: It's the same IP address - but try port 35 for ssh. Marc changed it (temporarily) due to a vast number of malicious connection attempts. Why wasn't this change communicated to anyone, not even gforge-admins? How temporary is temporary? Kris Jurka I can't speak to the administrative and communications aspects, but based on my experience, I can recommend communicating to the appropriate users and making the change permanent. We should move to a port-knocking http://dotancohen.com/howto/portknocking.html or other modern strategy if we're going to move at all. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
Gregory Stark [EMAIL PROTECTED] writes: Merlin Moncure [EMAIL PROTECTED] writes: I see your point but there's a pretty high standard for changing existing behavior in bugfix releases. DISCARD ALL was specifically added in 8.3 for the purpose of connection poolers to be a big hammer that exactly emulates a new session. I'm somewhat skeptical that there are any applications using it directly at all, and doubly so that they would be using it and expecting advisory locks to persist. The fact that it is new in 8.3 definitely weakens the backwards- compatibility argument. I tend to agree that it's unlikely anyone is really depending on this behavior yet. You could make a case that if we don't backpatch now, we'd actually be *more* likely to create a problem, because the longer that 8.3 is out with the current behavior, the more likely that someone might actually come to depend on it. On balance I'm for back-patching, but wanted to see what others thought. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
On Wed, Nov 26, 2008 at 3:42 PM, Tom Lane [EMAIL PROTECTED] wrote: Gregory Stark [EMAIL PROTECTED] writes: Merlin Moncure [EMAIL PROTECTED] writes: I see your point but there's a pretty high standard for changing existing behavior in bugfix releases. DISCARD ALL was specifically added in 8.3 for the purpose of connection poolers to be a big hammer that exactly emulates a new session. I'm somewhat skeptical that there are any applications using it directly at all, and doubly so that they would be using it and expecting advisory locks to persist. The fact that it is new in 8.3 definitely weakens the backwards- compatibility argument. I tend to agree that it's unlikely anyone is really depending on this behavior yet. You could make a case that if we don't backpatch now, we'd actually be *more* likely to create a problem, because the longer that 8.3 is out with the current behavior, the more likely that someone might actually come to depend on it. On balance I'm for back-patching, but wanted to see what others thought. ok...i give :-) merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
Guillaume Smet [EMAIL PROTECTED] writes: I kinda agree with you. The only problem IMHO is that we described in the doc exactly what it does and not simply as the big hammer it was supposed to be. See http://www.postgresql.org/docs/8.3/interactive/sql-discard.html . Well, the *first* sentence there says it resets the session to its initial state, so it seems to me the intent is clear. But maybe we should alter the second sentence to read, say, This _currently_ has the same effect as ..., thereby making it clear that this is implementation detail and not the controlling definition. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [bugfix] DISCARD ALL does not release advisory locks
On Wed, Nov 26, 2008 at 9:45 PM, Tom Lane [EMAIL PROTECTED] wrote: Well, the *first* sentence there says it resets the session to its initial state, so it seems to me the intent is clear. But maybe we should alter the second sentence to read, say, This _currently_ has the same effect as ..., thereby making it clear that this is implementation detail and not the controlling definition. +1. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
On Wed, Nov 5, 2008 at 11:47 PM, Jaime Casanova [EMAIL PROTECTED] wrote: On Fri, Oct 31, 2008 at 7:42 PM, Emmanuel Cecchet [EMAIL PROTECTED] wrote: Hi Nikhil, i'm looking at this one: http://archives.postgresql.org/message-id/[EMAIL PROTECTED] 'cause the great interest this one has (i'm being ironic, just in case ;) can we safely say this was returned with feedback and remove it from the list of pending patches? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] where is the last hot standby patch?
i get lost with this one... i thought there were two patches that get merged into one, but i don't find nor the merged version nor the actualized version of any of one... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Decibel! [EMAIL PROTECTED] wrote: On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote: The thought occurs to me that we're looking at this from the wrong side of the coin. I've never, ever seen query plan time pose a problem with Postgres, even without using prepared statements. I certainly have seen plan times be a problem. I wonder if you have too and just didn't realize it. With a default_stats_target of 1000 you'll have hundreds of kilobytes of data to slog through to plan a moderately complex query with a few text columns. Forget about prepared queries, I've seen plan times be unusable for ad-hoc interactive queries before. Can you provide any examples? And no, I've never seen a system where a few milliseconds of plan time difference would pose a problem. When we first brought the statewide circuit court data onto PostgreSQL, on some early version of 8.1, we tried boosting the statistics targets for a few dozen important columns, and had to back off because of plan times up in the 20 to 30 second range. I hadn't tried it lately, so I just gave it a go with switching from a default statistics target of 10 with no overrides to 1000. The plan time for a fairly complex query which is run over 300,000 times per day went from 55 ms to 315 ms; however, with the particular search criteria I used (which I knew to be challenging) the run time went from something which exceeded my patience tolerance for the test (over two minutes) to two seconds, so a better plan was definitely found. I'm not sure what this suggests in terms of a good default value, but just to put some numbers out there from a real-world application -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
'cause the great interest this one has (i'm being ironic, just in case ;) can we safely say this was returned with feedback and remove it from the list of pending patches? Um... are you referring to lack of interest from the patch author, or from the community? If the patch author is no longer interested in the patch, of course it should be withdrawn. But as for the community, the patch is on the commitfest wiki[1] and you are listed as the reviewer, so I wouldn't necessarily expect anyone else to comment at this point - although, in fact, Emmanuel Cecchet wrote in as well, so I would say you have exactly the opposite of a lack of interest. If you think the patch needs further review from another reviewer, say so. I'm sure someone else can be assigned to do an additional review. If you think the patch is ready to commit, say so, and update the wiki accordingly. ...Robert [1] http://wiki.postgresql.org/wiki/CommitFest_2008-11 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Kevin Grittner [EMAIL PROTECTED] wrote: I hadn't tried it lately, so I just gave it a go with switching from a default statistics target of 10 with no overrides to 1000. Oh, this was on 8.2.7, Linux, pretty beefy machine. Do you want the whole set of config info and the hardware specs, or would that just be clutter? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
David Fetter wrote: We should move to a port-knocking http://dotancohen.com/howto/portknocking.html or other modern strategy if we're going to move at all. Yeah, but telling my firewall to move port 22 inside to port outside took less time than writing this email. Inside the firewall plain old ssh continues to work fine and I don't have to deal with issues of forwarding additional ports through the firewall, mucking with iptables rules, etc. For my servers, moving outside access to a non-standard port has proven 100% effective for over a year so additional complexity hasn't been warranted. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, 2008-11-26 at 13:57 -0800, Steve Crawford wrote: David Fetter wrote: We should move to a port-knocking http://dotancohen.com/howto/portknocking.html or other modern strategy if we're going to move at all. Yeah, but telling my firewall to move port 22 inside to port outside took less time than writing this email. Inside the firewall plain old ssh continues to work fine and I don't have to deal with issues of forwarding additional ports through the firewall, mucking with iptables rules, etc. For my servers, moving outside access to a non-standard port has proven 100% effective for over a year so additional complexity hasn't been warranted. Since were chatting :P. My vote would be to move everything back to port 22 and force key based auth only. Joshua D. Drake Cheers, Steve -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Wednesday, November 26, 2008 14:00:59 -0800 Joshua D. Drake [EMAIL PROTECTED] wrote: Since were chatting :P. My vote would be to move everything back to port 22 and force key based auth only. How does that work? Does that kill the script kiddies in their tracks? I'm guessing so, but had never thought to try it ... How would someone upload their key if they don't have access? Some sort of web interface? One wouldn't want to throw extra admin overhead if it can be avoided ... - -- Marc G. FournierHub.Org Hosting Solutions S.A. (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (FreeBSD) iEYEARECAAYFAkktyHIACgkQ4QvfyHIvDvPUFwCfbV3QhjxF3kA7szsTeZp5ZIm8 AfUAn3NiwLA9r0hhs3camv4GstIpcJil =I4+l -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Marc G. Fournier wrote: How would someone upload their key if they don't have access? Some sort of web interface? One wouldn't want to throw extra admin overhead if it can be avoided ... pgfoundry already has a web interface for uploading SSH keys. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Marc G. Fournier wrote: Since were chatting :P. My vote would be to move everything back to port 22 and force key based auth only. How does that work? Does that kill the script kiddies in their tracks? I'm guessing so, but had never thought to try it ... Depends on where the problem is. AFAIK, it will still go through the initial cryptographic key exchange before it even starts talking about auth methods. However, if the problem is that they are trying many different passwords *over the same connection*, it should fix the problem. I suggested this long ago for our servers in general (for other reasons), but was voted down at the time. Can't remember why though :-) This was around the same time I proposed we should not allow remote root logins... How would someone upload their key if they don't have access? Some sort of web interface? One wouldn't want to throw extra admin overhead if it can be avoided ... IIRC, you can already upload your key using the gforge web interface if you want to - it's just not mandatory. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
On Wed, 2008-11-26 at 18:06 -0400, Marc G. Fournier wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Since were chatting :P. My vote would be to move everything back to port 22 and force key based auth only. How does that work? Does that kill the script kiddies in their tracks? I'm guessing so, but had never thought to try it ... Well they can still talk to the port of course but its irrelevant because unless they have an ssh key, they aren't getting in. Period. How would someone upload their key if they don't have access? Some sort of web interface? One wouldn't want to throw extra admin overhead if it can be avoided ... See other comment on this. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Joshua D. Drake wrote: On Wed, 2008-11-26 at 18:06 -0400, Marc G. Fournier wrote: Since were chatting :P. My vote would be to move everything back to port 22 and force key based auth only. How does that work? Does that kill the script kiddies in their tracks? I'm guessing so, but had never thought to try it ... Well they can still talk to the port of course but its irrelevant... Not really. My servers don't allow remote root ssh access at all. But all the failed script-kiddie attempts really hose the log files to say nothing about wasting my bandwidth. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Wednesday, November 26, 2008 14:12:42 -0800 Joshua D. Drake [EMAIL PROTECTED] wrote: Well they can still talk to the port of course but its irrelevant because unless they have an ssh key, they aren't getting in. Period. Well, they weren't getting in before ... i twas the massive flood of attempts that was hurting :) - -- Marc G. FournierHub.Org Hosting Solutions S.A. (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (FreeBSD) iEYEARECAAYFAkktzlcACgkQ4QvfyHIvDvMTVwCeJeEMXlp1IUQwl6yFejsabAJc BlkAn1BYToJyJ0i3wMxpQm9SNeW9LAu2 =EmfE -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Marc G. Fournier [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Well they can still talk to the port of course but its irrelevant because unless they have an ssh key, they aren't getting in. Period. Well, they weren't getting in before ... i twas the massive flood of attempts that was hurting :) Yeah. So having a more secure login API won't help that a bit. I don't have a problem with moving the ssh support to a nonstandard port, but I do have a problem with the lack of notification about it. Even core found out the hard way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
Marc G. Fournier wrote: - --On Wednesday, November 26, 2008 14:12:42 -0800 Joshua D. Drake [EMAIL PROTECTED] wrote: Well they can still talk to the port of course but its irrelevant because unless they have an ssh key, they aren't getting in. Period. Well, they weren't getting in before ... i twas the massive flood of attempts that was hurting :) It should be easy to block the IPs that cause too many failures, like fail2ban does in Linux using iptables. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What's going on with pgfoundry?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Wednesday, November 26, 2008 17:42:12 -0500 Tom Lane [EMAIL PROTECTED] wrote: Marc G. Fournier [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Well they can still talk to the port of course but its irrelevant because unless they have an ssh key, they aren't getting in. Period. Well, they weren't getting in before ... i twas the massive flood of attempts that was hurting :) Yeah. So having a more secure login API won't help that a bit. I don't have a problem with moving the ssh support to a nonstandard port, but I do have a problem with the lack of notification about it. Even core found out the hard way. I just moved pgfoundry back to port 22, sinc eout of all of them, I believe that one had the largest impact ... I would still like to move it back to 35 ... Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (FreeBSD) iEYEARECAAYFAkkt1b4ACgkQ4QvfyHIvDvPV1QCgyJBxAAPznvT8CK5Hx6Dj20Jy BqoAoLAqPZfE6L7uANeHNrpavXZ7L0bt =o3iw -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Memory mess introduced by recent funcapi.c patch
This patch: http://archives.postgresql.org/pgsql-committers/2008-02/msg00356.php which was alleged to prevent memory leaks, seems to have introduced some. The problem is that shutdown_MultiFuncCall() now performs a MemoryContextSwitchTo(flinfo-fn_mcxt); which means that an exiting SRF will now leave CurrentMemoryContext pointing at a long-lived context, not the short-lived context in which the function was called. Subsequent operations that think they are running in a short-lived context (and hence can be cavalier about whether to free allocations) now result in memory leaks. Did you have a specific reason for adding that? There was no trace of any such switch before, and a quick check with the regression tests shows no harm from taking it out. Plus that makes the memory leak I'm seeing go away ... For the record, the test case that led me to this was select unnest((ts_debug(title)).lexemes) as lex from publications_test; where publications_test.title is a long column of article titles, and I wanted to extract a single column of lexemes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
On 26/11/2008, Hitoshi Harada [EMAIL PROTECTED] wrote: 2008/11/26 David Rowley [EMAIL PROTECTED]: Hitoshi Harada wrote: 2008/11/20 David Rowley [EMAIL PROTECTED]: -- The following query gives incorrect results on the -- maxhighbid column SELECT auctionid, category, description, highestbid, reserve, MAX(highestbid) OVER (ORDER BY auctionid) AS maxhighbid, MAX(reserve) OVER() AS highest_reserve FROM auction_items; If you remove the highest_reserve column you get the correct results. The bug also affects MIN, AVG, COUNT, STDDEV but not SUM. Good report! I fixed this bug, which was by a trival missuse of list_concat() in the planner. This was occurred when the first aggregate trans func is strict and the second aggregate argument may be null. Yep, the argument of the second was implicitly passed to the first wrongly. That's why it didn't occur if you delete the second MAX(). I added a test case with the existing data emulating yours (named strict aggs) but if it is wrong, let me know. It's not quite right yet. I'm also getting regression tests failing on window. Let me know if you want the diffs. I've created a query that uses the table in your regression test. max_salary1 gives incorrect results. If you remove the max_salary2 column it gives the correct results. Please excuse the lack of sanity with the query. I had to do it this way to get 2 columns with NULLs. SELECT depname, empno, salary, salary1, salary2, MAX(salary1) OVER (ORDER BY empno) AS max_salary1, MAX(salary2) OVER() AS max_salary2 FROM (SELECT depname, empno, salary, (CASE WHEN salary 5000 THEN NULL ELSE salary END) AS salary1, (CASE WHEN salary = 5000 THEN NULL ELSE salary END) AS salary2 FROM empsalary ) empsalary; Actual results: depname | empno | salary | salary1 | salary2 | max_salary1 | max_salary2 ---+---++-+-+-+- sales | 1 | 5000 |5000 | | |4800 personnel | 2 | 3900 | |3900 | |4800 sales | 3 | 4800 | |4800 | |4800 sales | 4 | 4800 | |4800 | |4800 personnel | 5 | 3500 | |3500 | |4800 develop | 7 | 4200 | |4200 | |4800 develop | 8 | 6000 |6000 | | |4800 develop | 9 | 4500 | |4500 | |4800 develop |10 | 5200 |5200 | | |4800 develop |11 | 5200 |5200 | | |4800 Correct results: depname | empno | salary | salary1 | salary2 | max_salary1 | max_salary2 ---+---++-+-+-+- sales | 1 | 5000 |5000 | |5000 |4800 personnel | 2 | 3900 | |3900 |5000 |4800 sales | 3 | 4800 | |4800 |5000 |4800 sales | 4 | 4800 | |4800 |5000 |4800 personnel | 5 | 3500 | |3500 |5000 |4800 develop | 7 | 4200 | |4200 |5000 |4800 develop | 8 | 6000 |6000 | |6000 |4800 develop | 9 | 4500 | |4500 |6000 |4800 develop |10 | 5200 |5200 | |6000 |4800 develop |11 | 5200 |5200 | |6000 |4800 This might be a good regression test once it's fixed. Hmm, did you apply the latest patch correctly? My build can produce right results, so I don't see why it isn't fixed. Make sure the lines around 2420-2430 in planner.c like: /* * must copyObject() to avoid args concatenating with each other. */ pulled_exprs = list_concat(pulled_exprs, copyObject(wfunc-args)); where copyObject() is added. I'm sitting here away from home with a funny feeling I forgot to make install. I think my home adsl has dropped out so can't confirm that. If it works for you and not for me last night then I probably did forget. I'll let you know. I'm not sure if this is related, another bug is found: *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *** *** 2246,2251 equal(void *a, void *b) --- 2246,2252 break; case T_Aggref: retval = _equalAggref(a, b); +break;
Re: [HACKERS] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
On Wed, Nov 26, 2008 at 10:52 PM, Robert Haas [EMAIL PROTECTED] wrote: 'cause the great interest this one has (i'm being ironic, just in case ;) can we safely say this was returned with feedback and remove it from the list of pending patches? the patch is on the commitfest wiki[1] and you are listed as the reviewer, so I wouldn't necessarily expect anyone else to comment at this point - although, in fact, Emmanuel Cecchet wrote in as well, so I would say you have exactly the opposite of a lack of interest. i review it on nov 6, and there were open questions by me and by Emmanuel none of those has been answered: http://archives.postgresql.org/pgsql-hackers/2008-11/msg00362.php -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/11/27 Tom Lane [EMAIL PROTECTED]: Heikki Linnakangas [EMAIL PROTECTED] writes: Here's another updated patch, including all your bug fixes. I did a very fast pass through this and have a few comments. Thanks for your comments. The executor part is now being refactored by Heikki, but remaining are still on me. Note that some of those are because of my earlier poor understanding. * Don't bother manually updating keywords.sgml. As stated therein, that table is automatically generated. All you'll accomplish is to cause merge problems. (The effort would be a lot better spent on the non-boilerplate parts of the docs anyway.) OK, I intend nothing here but didn't know the rule. will remove it. * I assume there's going to be some way to create user-defined window functions? Yes, but for 8.4 no. The window functions will need specific window function APIs rather than existing PG_XXX APIs and the design of them affects how to design the Window executor node. So we are currently desgining the APIs. If it completes in the future users can create their own functions. * It seems fairly unlikely that you can get away with not supporting any qual expression on a Window plan node. What will you do with HAVING qualifiers? Window nodes are executed after any of WHERE, GROUP BY, HAVING, and before ORDER BY. Window nodes don't have qual and HAVING doesn't give any effect to Window operations. * The find_aggref code added to planagg.c (where it doesn't belong anyway) doesn't seem to be used anywhere. It was needed to extract Aggref node in planner once, but not needed anymore. will remove it. * In the same vein, I'm unimpressed with moving GetAggInitVal into execGrouping.c, which it isn't at all related to. I'd just leave it alone and duplicate the code in nodeWindow.c --- it's not exactly large. If you did insist on sharing this code it would be appropriate to change the name and comments to reflect the fact that it's being used for more than just aggregates, anyhow. It is now in the discussion. Since nodeWindow has much duplicated code in initialize/advance/finalize so we wonder if those codes should be shared among the two nodes. If so, GetAggInitVal seems to be shared as well as other aggregate specific code. If we decide to separate them, your suggestion that GetAggInitVal should be duplicated will be sane. * And in the same vein. var.c is hardly the place to put a search-for-wfuncs routine. Agreed, but where to go? clause.c may be, or under parser/ ? * It seems like a coin was flipped to determine whether struct and field names would use window, win, or just w (I find WFunc to be particularly unhelpful to a reader who doesn't already know what it is). Please try to reduce the surprise factor. I'd suggest consistently using window in type names, though win is an OK prefix for field names within window-related structs. I named WFunc as WinFunc once, but sounds too long for such heavily used node. I liked it like Agg, but Win is not appropriate neither is Func. And also, its name is consistent with the added pg_proc column named proiswfunc. I wonder it would be proiswinfunc if we rename WFunc as WinFunc. * This is a bad idea: /* + * OrderClause - + * representation of ORDER BY in Window + */ + typedef SortGroupClause OrderClause; + + + /* + * PartitionClause - + * representaition of PATITION BY in Window + */ + typedef SortGroupClause PartitionClause; If they're just SortGroupClauses, call them that, don't invent an alias. (Yes, I know SortClause and GroupClause used to be aliases. That was a bad idea: it confused matters and required lots of useless duplicated code, except for the places where we didn't duplicate code because we were willing to assume struct equivalence. There's basically just nothing that wins about that approach.) In any case, order and partition are really bad names to be using here given the number of possible other meanings for those terms in a DBMS context. If you actually need separate struct types then names like WindowPartitionClause would be appropriate. This is because I didn't know quite well about windowed table specification earlier (and when I was started the Group and the Sort was separated as you point). And now I can tell the two nodes can be named SortGroupClause, nothing special. * The API changes chosen for func_get_detail seem pretty bizarre. Why didn't you just add a new return code FUNCDETAIL_WINDOW? An aggregate that is existing currently can be used as a window function. But we need to treat it as specialized case. A normal aggregate without OVER clause is GROUP BY aggregate and with OVER clause it's window aggregate. For func_get_detail to determine which aggregate windef variable must be passed. Is it better? And also, block starting with Oops. Time to die comment in ParseFuncOrColumn can be shared among two types. So I thought the two are similar and func_get_detail
Re: [HACKERS] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
i review it on nov 6, and there were open questions by me and by Emmanuel none of those has been answered: http://archives.postgresql.org/pgsql-hackers/2008-11/msg00362.php Hmm, there's only one actual question in that email, which is a request for ideas about PL/pgsql vs. C. I suspect you didn't get any responses because the rest of the email seems to indicate that the patch is not very mature at this point: for example, being able to handle updates that move rows between partitions would seem to me to be an essential feature for a project of this type, even though there are many practical scenarios were it's unimportant. Likewise, being able to repartition sounds important. With respect to the specific question about PL/pgsql vs C, I suspect it's very unlikely that any patch of this type that relies on PL/pgsql being loaded would be accepted into core. However, it's possible that a useful contrib module or pgfoundry project could be spawned on that basis, and that might be a good place to start. I think having a useful toolkit, or a core language feature, that supports table partitioning would be awesome and would find very broad application... but it sounds like there is quite a bit of work left to be done to get there. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] where is the last hot standby patch?
On Wed, Nov 26, 2008 at 4:38 PM, Jaime Casanova [EMAIL PROTECTED] wrote: i get lost with this one... i thought there were two patches that get merged into one, but i don't find nor the merged version nor the actualized version of any of one... Simon said he would split the infrastructure changes back out as a separate patch in an hour or so but that was the last word I saw on this. http://archives.postgresql.org/pgsql-hackers/2008-11/msg01333.php Simon, did you by any chance send something that got eaten by the message size limit? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enable pl/python to return records based on multiple OUT params
Though it is a somewhat separate problem from current patch I'd like to do something about it before having it all committed, as the fix must touch the very same places than this patch. I think it takes two-tree days to figure out proper way to fix it. I'd like it to just accept ANY* and do the right thing but I may end up just rejecting ANY* on both IN and OUT args. The text above makes it sound like you're still working on this, but that was more than three weeks ago. What is the status of this now? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A bug with ALTER TABLE SET WITHOUT OIDS in CVS HEAD
Heikki Linnakangas wrote:
This patch:
commit 35ad25ad66fa3999bbc0bb59ca13cef3d750fb07
Author: Tom Lane [EMAIL PROTECTED]
Date: Sat Jul 26 19:15:35 2008 +
As noted by Andrew Gierth, there's really no need any more to force a
junk
filter to be used when INSERT or SELECT INTO has a plan that returns raw
disk tuples. The virtual-tuple-slot optimizations that were put in place
awhile ago mean that ExecInsert has to do ExecMaterializeSlot, and that
already copies the tuple if it's raw (and does so more efficiently than
a junk filter, too). So get rid of that logic. This in turn means that
we can throw away ExecMayReturnRawTuples, which wasn't used for any other
purpose, and was always a kluge anyway.
In passing, move a couple of SELECT-INTO-specific fields out of
EState
and into the private state of the SELECT INTO DestReceiver, as was
foreseen
in an old comment there. Also make intorel_receive use
ExecMaterializeSlot
not ExecCopySlotTuple, for consistency with ExecInsert and to possibly
save
a tuple copy step in some cases.
made this test case crash:
CREATE TABLE xtable (padding char(2000)) WITH OIDS;
INSERT INTO xtable VALUES('1');
ALTER TABLE xtable SET WITHOUT OIDS;
INSERT INTO xtable (SELECT * FROM xtable);
with assertion failure:
TRAP: FailedAssertion(!(!(tup-t_data-t_infomask 0x0008)), File:
heapam.c, Line: 1782)
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items
In addition, it can show us another unexpected behavior.
* Before patch applied:
postgres=# CREATE TABLE t1 (a int, b text) WITH OIDS;
CREATE TABLE
postgres=# INSERT INTO t1 VALUES (1,'aaa'), (2,'bbb'), (3,'ccc');
INSERT 0 3
postgres=# SELECT oid,* FROM t1;
oid | a | b
---+---+-
16405 | 1 | aaa
16406 | 2 | bbb
16407 | 3 | ccc
(3 rows)
postgres=# INSERT INTO t1 (SELECT * FROM t1);
INSERT 0 3
postgres=# SELECT oid,* FROM t1;
oid | a | b
---+---+-
16405 | 1 | aaa
16406 | 2 | bbb
16407 | 3 | ccc
16405 | 1 | aaa
16406 | 2 | bbb
16407 | 3 | ccc
(6 rows)
The newly insered three tuples preserves its object identifier because
the fetched tuples has its valid object identifier which means it does
not need to assign a new one.
The matter comes from that we cannot guess ahead whether the fetched
tuple has object identifier field, or not. Thus, it is necessary to
enforce to translate fetched tuples into the current proper rowtype
on INSERT, UPDATE or SELECT INTO.
If my understanding is correct, the following patch can fix the matters.
-(cut here)-
*** src/backend/executor/execScan.c (revision 1244)
--- src/backend/executor/execScan.c (working copy)
***
*** 243,250
* If the plan context requires a particular hasoid setting, then that
has
* to match, too.
*/
! if (ExecContextForcesOids(ps, hasoid)
! hasoid != tupdesc-tdhasoid)
return false;
return true;
--- 243,249
* If the plan context requires a particular hasoid setting, then that
has
* to match, too.
*/
! if (ExecContextForcesOids(ps, hasoid))
return false;
return true;
-(cut here)-
* After the patch applied:
postgres=# CREATE TABLE t1 (a int, b text) WITH OIDS;
CREATE TABLE
postgres=# INSERT INTO t1 VALUES (1,'aaa'), (2,'bbb'), (3,'ccc');
INSERT 0 3
postgres=# SELECT oid,* FROM t1;
oid | a | b
---+---+-
16435 | 1 | aaa
16436 | 2 | bbb
16437 | 3 | ccc
(3 rows)
postgres=# ALTER TABLE t1 SET WITHOUT OIDS;
ALTER TABLE
postgres=# INSERT INTO t1 (SELECT * FROM t1);
INSERT 0 3
postgres=# SELECT * FROM t1;
a | b
---+-
1 | aaa
2 | bbb
3 | ccc
1 | aaa
2 | bbb
3 | ccc
(6 rows)
* After patch applied:
postgres=# CREATE TABLE t1 (a int, b text) WITH OIDS;
CREATE TABLE
postgres=# INSERT INTO t1 VALUES (1,'aaa'), (2,'bbb'), (3,'ccc');
INSERT 0 3
postgres=# SELECT oid,* FROM t1;
oid | a | b
---+---+-
16420 | 1 | aaa
16421 | 2 | bbb
16422 | 3 | ccc
(3 rows)
postgres=# INSERT INTO t1 (SELECT * FROM t1);
INSERT 0 3
postgres=# SELECT oid,* FROM t1;
oid | a | b
---+---+-
16420 | 1 | aaa
16421 | 2 | bbb
16422 | 3 | ccc
16423 | 1 | aaa
16424 | 2 | bbb
16425 | 3 | ccc
(6 rows)
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei [EMAIL PROTECTED]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Thread safety
Can someone remind me why we have --enable-thread-safety? As opposed to it being default and having --disable-thread-safety. /Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
