Re: [HACKERS] Sync Rep: First Thoughts on Code
Mark Mielke wrote: Where does the expectation come from? I don't recall ever reading it in the documentation, and unless the session processes are contending over the integers (using some sort of synchronization primitive) in memory that represent the latest visible commit on every single select, I'm wondering how it is accomplished? The integers you're imagining are the ProcArray. Every backend has an entry there, and among other things it contains the current XID the backend is running. When a backend takes a new snapshot (on every single select in read committed mode), it locks the ProcArray, scans all the entries and collects all the XIDs listed there in the snapshot. Those are the set of transactions that were running when the snapshot was taken, and is used in the visibility checks. If they are contending over these integers, doesn't that represent a scaling limitation, in the sense that on a 32-core machine, they're going to be fighting with each other to get the latest version of these shared integers into the CPU for processing? Maybe it's such a small penalty that we don't care? :-) The ProcArrayLock is indeed quite busy on systems with a lot of CPUs. It's held for such short times that it's not a problem usually, but it can become a bottleneck with a machine like that with all backends running small transactions. -- 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] visibility map and reltuples
Heikki Linnakangas wrote: Ned T. Crigler wrote: It appears that the visibility map patch is causing pg_class.reltuples to be set improperly after a vacuum. For example, it is set to 0 if the map indicated that no pages in the heap needed to be scanned. Perhaps reltuples should not be updated unless every page was scanned during the vacuum? Yeah, vacuum shouldn't overwrite reltuples if it hasn't scanned all pages. Because we use reltuples divided by relpages in the planner, we probably shouldn't update relpages either if we don't update reltuples. Otherwise, if the table has grown a lot since we last updated reltuples, the reltuples / relpages ratio would be less, not more, accurate, if relpages is updated to a new higher value but reltuples is not. -- 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
[HACKERS] Function with defval returns error
Hi All, Following test returns error on 8.4 cvs head. it looks like an issue Testcase: (8.4 CVS head) CREATE OR REPLACE FUNCTION f007( a INTEGER, b INTEGER DEFAULT 10 ) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a INTEGER DEFAULT 10, b INTEGER DEFAULT 10, c INTEGER DEFAULT 10) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a TIMESTAMP DEFAULT to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ) RETURNS TIMESTAMP AS $$ select current_date::timestamp; $$ language sql; postgres=# SELECT f007( to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ); ERROR: functions with parameter defaults f007(integer, integer, integer) and f007(integer, integer) are ambiguous I think this should not return error as the input args here is timestamp... inputs? Thanks, Rushabh Lathia www.EnterpriseDB.com
Re: [HACKERS] Function with defval returns error
Rushabh Lathia wrote: Testcase: (8.4 CVS head) CREATE OR REPLACE FUNCTION f007( a INTEGER, b INTEGER DEFAULT 10 ) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a INTEGER DEFAULT 10, b INTEGER DEFAULT 10, c INTEGER DEFAULT 10) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a TIMESTAMP DEFAULT to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ) RETURNS TIMESTAMP AS $$ select current_date::timestamp; $$ language sql; postgres=# SELECT f007( to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ); ERROR: functions with parameter defaults f007(integer, integer, integer) and f007(integer, integer) are ambiguous I think this should not return error as the input args here is timestamp... inputs? In theory yes, but it's currently not that smart. When it considers a function based on available default values, it does not consider argument types. So if there is another function with the same number of arguments, it bails out. Feel free to propose improvements. -- 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] Function with defval returns error
Hello 2008/12/15 Rushabh Lathia rushabh.lat...@gmail.com: Hi All, Following test returns error on 8.4 cvs head. it looks like an issue Testcase: (8.4 CVS head) CREATE OR REPLACE FUNCTION f007( a INTEGER, b INTEGER DEFAULT 10 ) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a INTEGER DEFAULT 10, b INTEGER DEFAULT 10, c INTEGER DEFAULT 10) RETURNS INTEGER AS $$ select 10; $$ language sql; CREATE OR REPLACE FUNCTION f007( a TIMESTAMP DEFAULT to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ) RETURNS TIMESTAMP AS $$ select current_date::timestamp; $$ language sql; postgres=# SELECT f007( to_date('01-JUN-06 14:03:50', 'DD-MON-YY HH24:MI:SS') ); ERROR: functions with parameter defaults f007(integer, integer, integer) and f007(integer, integer) are ambiguous I think this should not return error as the input args here is timestamp... inputs? you are right - this is known problem - because postgresql identify ambigonous calling and choise the best function in two places - simply we identify ambigonous call to early (algoritm is more fast then smart) - so ugly efect is identifivation of stored ambiguous functions when you call other function with same name. Pavel Thanks, Rushabh Lathia 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: default values for function parameters
it's look well, but I still prefer some combination with = name: = '' name: = ''' :name = '' $name = .. $name = .. I wonder about name := ''. := is used in Pascal/Ada to assign a value. Or would that again be an allowed operator in pg ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] About debug means ---How to debug a function seperately in an easy way?
Hello, pgsql-hackers: I want to debug one function in postgre backend. for example, I want to debug the following function : array_set(ArrayType *array, int nSubscripts, int *indx, Datum dataValue, bool isNull, int arraytyplen, int elmlen, bool elmbyval, char elmalign) What I want is to set various inputs, and see the corresponding result. Meanwhile , I don't know how to design the test case from the regular process, from TCOP, parser, analyzer,... and finally to array_set() I just want to take it in an easy way, for example , I write some code somewhere in postgre backend source code, and make up many different inputs and see the result returned by this function array_set. Is there some convenient way to do it? Thanks a lot ^_^ yours sincerely bill...@163.com 2008-12-15 -- 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] pre-MED
David Fetter da...@fetter.org writes: Tom, I know you've been busy with lots of stuff, so here's a little reminder. I talked with a couple of people who know the back-end much better than I do. One said the above was way under-specified, and the other said he'd started work on it, but hasn't had much time. Since you're the one who brought this up, I think it's on you to flesh it out at least a little bit, or at least to describe it in enough detail that someone else could. I'm sorry, but I have far too much work in front of me reviewing patches that have a chance of getting into 8.4. I do not have time to do pre-implementation research for a patch that doesn'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] rules regression test failed on mingw
Jaime Casanova jcasa...@systemguards.com.ec writes: i'm seeing a fail in the rules regression, seems like it is not ordering the results right even when the regression has an explicit order by... What locale is this running in? 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] Sync Rep: First Thoughts on Code
* Robert Haas robertmh...@gmail.com [081215 07:32]: In fact, waiting for reply from standby server before acknowledging a commit to the client is a bit pointless otherwise. It puts you in a strange situation, where you're waiting for the commits in normal operation, but if there's a network glitch or the standby goes down, you're willing to go ahead without it. You get a high guarantee that your data is up-to-date in the standby, except when it isn't. Which isn't much of a guarantee. It protects you against a catastrophic loss of the primary, which is a non-trivial consideration. At the risk of being ghoulish, imagine that you are a large financial company headquartered in the world trade center. This was exacty my original point - I want the transaction durably on the slave before the commit is acknowledged (to build as much local redunancy as I can), but I certatily *don't* want to loose the ability to use WAL archiving, because I ship my WAL off-site too... The ability to have an extra local copy is good. But I'm certainly not going to want to give up my off-site backup/WAL for it... a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Sync Rep: First Thoughts on Code
So you'd want all commits to wait until the transaction is safely replicated in the standby. But if there's a network glitch, or the standby is restarted, you're happy to reply to the client that it's committed if it's only safely committed in the primary. Essentially, you wait for the reply as long the standby responds within X seconds, but if it takes more then Y seconds, you don't wait. I know that people do that, but it seems counterintuitive to me. In that case, when the primary acks the transaction as committed, you only know that it's safely committed in the primary; it doesn't give any hard guarantee about the state in the standby. I understand you're point, but I think there's still a use case. The idea is that declaring the secondary dead is a rare event, and there's some mechanism by which you're enabled to page your network staff, and they hightail it into the office to fix the problem. It might not be the way that you want to run your system, but I don't think it's unreasonable for someone else to want it. But when you consider the possibility to use the standby for queries, the synchronous mode makes sense too. I'm not opposed to providing all the options, but the synchronous mode where we can guarantee that if you query the standby, you will see the effects of all transactions committed in the primary, makes the synchronous mode much more interesting. If you don't need that property, you're most likely more happy with asynchronous mode anyway. I agree that asynchronous mode will be the right solution for a very large subset of our users. ...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] WIP: default values for function parameters
2008/12/15 Peter Eisentraut pete...@gmx.net: Pavel Stehule wrote: Because AS is signal for collecting column (or label) names. I thing so we should use AS as Tom's proposal, together with SQL/XML functionality. Yes, please implement that. I'll do it - it's better then nothing, It's only idea: default behave is using as for param name specification, seconf with flag maybe labeled allows using AS in SQL/XML behave But this syntax don't allow use this feature together (it is maybe enought).\ create function json(variadic labeled values int[]) ... I didn't get that ... :-( First I have to look to code, use AS means, so there will be one syntactic role with two meanings. Pavel -- 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] Sync Rep: First Thoughts on Code
On Sun, 2008-12-14 at 21:41 -0500, Robert Haas wrote: If this is right, #2, #3, #4, and #6 feel similar except that they're protecting against failures of different (but still all incomplete) subsets of the hardware on the slave, right? Right. Actually, the biggest difference with #6 has nothing to do with protecting against failures. It has rather to do with the ease of writing applications in the context of hot standby. You can close your connection, open a connection to a different server, and know that your transactions will be reflected there. On the other hand, I'd be surprised if it didn't come with a substantial performance penalty, so it may not be too practical in real life even if it sounds good on paper. #1 , #3, and #5 don't feel that useful to me. Yes, looks that way for me also. Good analysis Ron. I agree with Robert that #6 is there for other reasons. #2 corresponds to DRBD algorithm B #4 corresponds to DRBD algorithm C Fujii-san, please can we incorporate those two options, rather than just one choice synchronous_replication = on. They look like two commonly requested options. #6 is an additional synchronization step in Hot Standby. I would say that people won't want that when they see how it performs (they probably won't want #4 either for that same reason, but that is for robustness). Also, I would point out that the class of synch_rep is selected by the user on the primary and can vary from transaction to transaction. That is a very good thing, as far as I am concerned. We would need to enforce #6 for all transactions (if we implemented synchronisation in this way). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] rules regression test failed on mingw
On Mon, Dec 15, 2008 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: i'm seeing a fail in the rules regression, seems like it is not ordering the results right even when the regression has an explicit order by... What locale is this running in? Seems this is Spanish_Spain.1252 and the encoding WIN1252 -- 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] Block-level CRC checks
On Mon, Dec 15, 2008 at 7:24 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Here's an updated patch against head. Thanks. No problemo. NOTE, it appears that this (and the previous) patch PANIC with concurrent transaction log activity while database system is shutting down on shutdown if checksumming is enabled. This appears to be due to FlushBuffer (lines 1821-1828) during the checkpoint-at-shutdown. Yeah, I reported this issue several times. Hmm. Well, the easiest thing would be to add a !shutdown check for logging the hint bits during the shutdown checkpoint :) Of course, that would break the page for recovery, which was the whole point of putting that in place. I'd have to look at xlog and see whether that check can be deferred or changed. Or, did you already research this issue? Similarly, I ran a pgbench, performed a manual checkpoint, and corrupted the tellers table myself using hexedit but the system didn't pick up the corruption at all :( Heh :-) :( Alvaro, have you given up on the patch or are you just busy on something else at the moment? I've given up until we find a good way to handle hint bits. Various schemes have been proposed but they all have more or less fatal flaws. Agreed. Though, I don't want to see this patch get dropped from 8.4. ALL, Alvaro has tried a couple different methods, does anyone have any other ideas? -- Jonah H. Harris, Senior DBA myYearbook.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] Stats target increase vs compute_tsvector_stats()
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= j.urban...@students.mimuw.edu.pl writes: Tom Lane wrote: I came across this bit in ts_typanalyze.c: /* We want statistic_target * 100 lexemes in the MCELEM array */ num_mcelem = stats-attr-attstattarget * 100; I wonder whether the multiplier here should be changed? The origin of that bit is this post: http://archives.postgresql.org/pgsql-hackers/2008-07/msg00556.php and the following few downthread ones. If we bump the default statistics target 10 times, then changing the multiplier to 10 seems the right thing to do. OK, will do. Only thing that needs caution is the frequency of pruning we do in the Lossy Counting algorithm, that IIRC is correlated with the desired target length of the MCELEM array. Right below that we have /* * We set bucket width equal to the target number of result lexemes. * This is probably about right but perhaps might need to be scaled * up or down a bit? */ bucket_width = num_mcelem; so it should track automatically. AFAICS the argument in the above thread that this is an appropriate pruning distance holds good regardless of just how we obtain the target mcelem count. BTW: I've been occupied with other things and might have missed some discussions, but at some point it has been considered to use Lossy Counting to gather statistics from regular columns, not only tsvectors. Wouldn't this help the performance hit ANALYZE takes from upping default_stats_target? Perhaps, but it's not likely to get done for 8.4 ... 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] rules regression test failed on mingw
Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Dec 15, 2008 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: What locale is this running in? Seems this is Spanish_Spain.1252 and the encoding WIN1252 What it looks like is that the locale is intentionally sorting h after k (or more likely the rule is ch after ck). My Spanish is just about gone ... is that a sane behavior at all? 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] Block-level CRC checks
Jonah H. Harris wrote: Alvaro, have you given up on the patch or are you just busy on something else at the moment? I've given up until we find a good way to handle hint bits. Various schemes have been proposed but they all have more or less fatal flaws. Agreed. Though, I don't want to see this patch get dropped from 8.4. ALL, Alvaro has tried a couple different methods, does anyone have any other ideas? Feature freeze is not the time to be looking for new ideas. I suggest we save this for 8.5. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] rules regression test failed on mingw
On Mon, Dec 15, 2008 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: What it looks like is that the locale is intentionally sorting h after k (or more likely the rule is ch after ck). My Spanish is just about gone ... is that a sane behavior at all? not at all... where can i check those rules? -- 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] rules regression test failed on mingw
Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Dec 15, 2008 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: What it looks like is that the locale is intentionally sorting h after k (or more likely the rule is ch after ck). My Spanish is just about gone ... is that a sane behavior at all? not at all... where can i check those rules? Well, one thing you should try is select 'wieck'::text 'wiech'::text; select 'wieck'::text 'wiech'::text; just to confirm whether the comparisons are actually working that way or we've got some other issue. You could also try initdb'ing in other locales to see if the behavior changes. I have no idea how to poke into the internals of Windows' locale definitions. 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] WIP: default values for function parameters
David E. Wheeler wrote: Perhaps not, but I have to say, looking at Robert's JSON example: SELECT json(r.foo AS foo, r.bar AS bar, r.baz AS baz, r.bletch AS quux) FROM rel r; I would be pretty confused. It looks exactly like the proposed syntax for named parameters. So while syntactically they may never be used together, there's a semantic mismatch, IMHO. In my mind, you just have to think about it hard enough to come to realize that, when viewed from the right angle, the semantic conflict might not exist after all. It's a bit tricky, but I think it's possible. -- 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] rules regression test failed on mingw
Tom Lane wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Dec 15, 2008 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: What locale is this running in? Seems this is Spanish_Spain.1252 and the encoding WIN1252 What it looks like is that the locale is intentionally sorting h after k (or more likely the rule is ch after ck). My Spanish is just about gone ... is that a sane behavior at all? It was sane behavior a couple of decades ago -- dictionaries used to sort like this (ch was considered an independent letter, and sorted between c and d). I'm not sure if RAE did actually revoke this behavior, or it's just that us are now too used to the idea that it's obsolete. If the former, we should be complaining to the glibc developers. If the latter, we should complain to our school Spanish teachers ;-) -- 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] Sync Rep: First Thoughts on Code
In fact, waiting for reply from standby server before acknowledging a commit to the client is a bit pointless otherwise. It puts you in a strange situation, where you're waiting for the commits in normal operation, but if there's a network glitch or the standby goes down, you're willing to go ahead without it. You get a high guarantee that your data is up-to-date in the standby, except when it isn't. Which isn't much of a guarantee. It protects you against a catastrophic loss of the primary, which is a non-trivial consideration. At the risk of being ghoulish, imagine that you are a large financial company headquartered in the world trade center. ...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] Restore enforce_generic_type_consistency's breaks a farms
Zdenek Kotala zdenek.kot...@sun.com writes: Following commit: http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php breaks several farms. Is it know issue? Hmm. What I think is happening is that sometimes there's a pg_statistic entry for a table that another process is in the midst of dropping. The has_table_privilege() call that's embedded in the pg_stats view fails if the DROP has already committed, even though the catalog entries are still visible to the caller's snapshot. The easy workaround to this is to make the regression test reference pg_statistic directly instead of pg_stats (okay because the tests require superuser privilege anyway). But this type of problem has come up before. I wonder if we shouldn't do what was previously discussed: make has_table_privilege and related functions silently return FALSE, instead of throwing error, when given a nonexistent OID. I wouldn't suggest doing this in 8.3.x, so probably removing the new regression test from 8.3.x is the way to fix that branch. It seems like it might be a good thing to do in HEAD though. Comments? 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] visibility map and reltuples
I wonder if we should switch to keeping reltuplesperpage instead. Then a partial vacuum could update it by taking the average number of tuples per page forbthe pages it saw. Perhaps adjusting it to the weights average between the old value and the new value based on how many pages were seen. I suppose there's no reason we can't update reltuples using that same logic though it would be a big opaque. -- Greg On 15 Dec 2008, at 04:01, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Heikki Linnakangas wrote: Ned T. Crigler wrote: It appears that the visibility map patch is causing pg_class.reltuples to be set improperly after a vacuum. For example, it is set to 0 if the map indicated that no pages in the heap needed to be scanned. Perhaps reltuples should not be updated unless every page was scanned during the vacuum? Yeah, vacuum shouldn't overwrite reltuples if it hasn't scanned all pages. Because we use reltuples divided by relpages in the planner, we probably shouldn't update relpages either if we don't update reltuples. Otherwise, if the table has grown a lot since we last updated reltuples, the reltuples / relpages ratio would be less, not more, accurate, if relpages is updated to a new higher value but reltuples is not. -- 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 -- 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] rules regression test failed on mingw
On Mon, Dec 15, 2008 at 10:26 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Dec 15, 2008 at 8:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: What locale is this running in? Seems this is Spanish_Spain.1252 and the encoding WIN1252 What it looks like is that the locale is intentionally sorting h after k (or more likely the rule is ch after ck). My Spanish is just about gone ... is that a sane behavior at all? It was sane behavior a couple of decades ago -- dictionaries used to sort like this (ch was considered an independent letter, and sorted between c and d). while 'ch' and 'll' are independent letters they sort as they were 'c' and 'l'... that means that 'ch' should go before 'ck' http://www.rae.es/rae/gestores/gespub18.nsf/(voAnexos)/arch8100821B76809110C12571B80038BA4A/$File/CuestionesparaelFAQdeconsultas.htm#ap31 -- 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] Looking for someone with MinGW
On Sun, Dec 14, 2008 at 11:36:21AM -0500, Andrew Dunstan wrote: See below ... Thanks. The backtrace is kind of strange, but I might have found it. Could you please update from CVS and re-run? Thanks again. 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: mes...@jabber.org 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] visibility map and reltuples
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Greg Stark wrote: I wonder if we should switch to keeping reltuplesperpage instead. Then a partial vacuum could update it by taking the average number of tuples per page forbthe pages it saw. Perhaps adjusting it to the weights average between the old value and the new value based on how many pages were seen. The pages scanned by a partial vacuum isn't a random sample of pages in the table. That would bias the reltuplesperpage value towards those pages that are updated more. Yeah ... and it's highly likely that repeatedly-updated pages would have more dead space than never-updated ones, so there'd be a systematic creep towards underestimation of the total tuple count. I think your previous sketch is right: suppress update of reltuples (and relpages) from a partial vacuum scan, and ensure that the analyze phase is allowed to do it instead if it happens during VACUUM ANALYZE. 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] WIP: default values for function parameters
2008/12/15 Zeugswetter Andreas OSB sIT andreas.zeugswet...@s-itsolutions.at: it's look well, but I still prefer some combination with = name: = '' name: = ''' :name = '' $name = .. $name = .. I wonder about name := ''. := is used in Pascal/Ada to assign a value. Or would that again be an allowed operator in pg ? I like it too, but there is problem with possible collision with custom operator Pavel Andreas -- 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] rules regression test failed on mingw
Jaime Casanova wrote: On Mon, Dec 15, 2008 at 10:26 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: It was sane behavior a couple of decades ago -- dictionaries used to sort like this (ch was considered an independent letter, and sorted between c and d). while 'ch' and 'll' are independent letters they sort as they were 'c' and 'l'... that means that 'ch' should go before 'ck' http://www.rae.es/rae/gestores/gespub18.nsf/(voAnexos)/arch8100821B76809110C12571B80038BA4A/$File/CuestionesparaelFAQdeconsultas.htm#ap31 Interesting. So they are both wrong, glibc and teachers. We can file a bug with glibc but I'm not sure we can do a lot about the other bug. Thanks for the research. -- 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] rules regression test failed on mingw
Alvaro Herrera alvhe...@commandprompt.com writes: Jaime Casanova wrote: while 'ch' and 'll' are independent letters they sort as they were 'c' and 'l'... that means that 'ch' should go before 'ck' Interesting. So they are both wrong, glibc and teachers. We can file a bug with glibc but I'm not sure we can do a lot about the other bug. Thanks for the research. But I don't see this sorting behavior with glibc on Linux (Fedora 9 to be exact, testing LC_COLLATE=es_ES.utf8). Does the mingw build actually use glibc's strcoll() code, or is it somehow depending on Windows system functionality? I'm also wondering if the behavior is somehow affected by encoding ... 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] Block-level CRC checks
On Mon, Dec 15, 2008 at 10:13 AM, Bruce Momjian br...@momjian.us wrote: Jonah H. Harris wrote: Alvaro, have you given up on the patch or are you just busy on something else at the moment? I've given up until we find a good way to handle hint bits. Various schemes have been proposed but they all have more or less fatal flaws. Agreed. Though, I don't want to see this patch get dropped from 8.4. ALL, Alvaro has tried a couple different methods, does anyone have any other ideas? Feature freeze is not the time to be looking for new ideas. I suggest we save this for 8.5. Well, we may not need a new idea. Currently, the problem I see with the checkpoint-at-shutdown looks like it could possibly be easily solved. Though, there may be other issues I'm not familiar with. Has anyone reviewed this yet? -- Jonah H. Harris, Senior DBA myYearbook.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] Sync Rep: First Thoughts on Code
Fujii-san, Just repeating this in case you lost this comment: On Mon, 2008-12-15 at 09:40 +, Simon Riggs wrote: Fujii-san, please can we incorporate those two options, rather than just one choice synchronous_replication = on. They look like two commonly requested options. I see the comment in line 230+ of walreceiver.c, so understand that you have implemented option #3 from the following list. So from my previous list 1. We sent the message to standby (A) 2. We received the message on standby 3. We wrote the WAL to the WAL file (B) 4. We fsync'd the WAL file (C) 5. We CRC checked the WAL commit record 6. We applied the WAL commit record Please could you also add an option #4, i.e. add the *option* to fsync the WAL to disk at commit time also. That requires us to add a third option to synchronous_replication parameter. That then means we will have robustness options that map directly to DRBD algorithms A, B and C (shown in brackets in the above list). I believe these map also to Data Guard options Maximum Performance and Maximum Availability. AFAICS if we implement the additional items I've requested over the last few days, then the architecture is now at a good point for 8.4 and we can begin to look at low level implementation details. Or put another way, I'm not expecting to come up with more architecture changes. #6 is an additional synchronization step in Hot Standby. I would say that people won't want that when they see how it performs (they probably won't want #4 either for that same reason, but that is for robustness). We can jointly add option #6 once we have both sync rep and hot standby committed, or at a late stage of hot standby development. There's not much point looking at it before then. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Function with defval returns error
Peter Eisentraut pete...@gmx.net writes: Rushabh Lathia wrote: I think this should not return error as the input args here is timestamp... inputs? In theory yes, but it's currently not that smart. This is truly horrid. Was that patch *really* ready to commit? I noticed some comments added to polymorphism.sql that certainly look like there's still a lot of half-bakedness in 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
Re: [HACKERS] rules regression test failed on mingw
I wrote: But I don't see this sorting behavior with glibc on Linux (Fedora 9 to be exact, testing LC_COLLATE=es_ES.utf8). BTW, I *do* see wieck wiech in es_ES locale on HPUX 10.20, released ~1996. So I think we have correctly identified the core issue, and the only interesting question is why mingw isn't following a more up-to-date sorting rule. Is it worth installing a variant rules regression output file for this? I'd rather not, since that file tends to change often. 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] rules regression test failed on mingw
On Mon, Dec 15, 2008 at 10:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: Well, one thing you should try is select 'wieck'::text 'wiech'::text; select 'wieck'::text 'wiech'::text; administra...@casanova10 ~/pg.build/8.4dev $ bin/psql -a -f test.sql postgres select 'wieck'::text 'wiech'::text; ?column? -- t (1 row) select 'wiech'::text 'wieck'::text; ?column? -- f (1 row) just to confirm whether the comparisons are actually working that way or we've got some other issue. ok, confirmed... You could also try initdb'ing in other locales to see if the behavior changes. Actually, using Spanish_Ecuador.1252 (wich is the one a i should use from the beginning anyway ;) gives correct results, maybe the other behaviour is correct in spain... we have a lot of spanish languages ;) administra...@casanova10 ~/pg.build/8.4dev $ bin/psql -a -f test.sql postgres select 'wieck'::text 'wiech'::text; ?column? -- f (1 row) select 'wiech'::text 'wieck'::text; ?column? -- t (1 row) -- 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] Block-level CRC checks
Jonah H. Harris jonah.har...@gmail.com writes: On Mon, Dec 15, 2008 at 10:13 AM, Bruce Momjian br...@momjian.us wrote: Feature freeze is not the time to be looking for new ideas. I suggest we save this for 8.5. Well, we may not need a new idea. We don't really have an acceptable solution for the conflict with hint bit behavior. The shutdown issue is minor, agreed, but that's not the stumbling block. 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] Block-level CRC checks
Jonah H. Harris escribió: Well, we may not need a new idea. Currently, the problem I see with the checkpoint-at-shutdown looks like it could possibly be easily solved. Though, there may be other issues I'm not familiar with. Has anyone reviewed this yet? I didn't investigate the shutdown checkpoint issue a lot (I was aware of it), because the really hard problem are hint bits. -- 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] Restore enforce_generic_type_consistency's breaks a farms
I wrote: ... But this type of problem has come up before. I wonder if we shouldn't do what was previously discussed: make has_table_privilege and related functions silently return FALSE, instead of throwing error, when given a nonexistent OID. On checking the archives, it seems most of the previous complaints have been about cache lookup failed errors arising from pg_table_is_visible and its ilk; those are used in the queries generated by psql's \d commands. So my proposal is to change the OID-accepting variants of has_table_privilege and friends, as well as pg_table_is_visible and friends, to silently return FALSE instead of failing when given a bad OID. I had originally thought we might change the underlying functions (pg_class_aclcheck, RelationIsVisible, etc) but it looks like it'd be better to expend a SearchSysCacheExists call in the SQL-accessible functions. (This doesn't leave a race condition since there'd not be any check for syscache invalidation messages between the SearchSysCacheExists and the ensuing lookup.) 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] Block-level CRC checks
On Mon, Dec 15, 2008 at 11:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jonah H. Harris jonah.har...@gmail.com writes: On Mon, Dec 15, 2008 at 10:13 AM, Bruce Momjian br...@momjian.us wrote: Feature freeze is not the time to be looking for new ideas. I suggest we save this for 8.5. Well, we may not need a new idea. We don't really have an acceptable solution for the conflict with hint bit behavior. The shutdown issue is minor, agreed, but that's not the stumbling block. Agreed on the shutdown issue. But, didn't this patch address the hint bit setting as discussed? After performing a cursory look at the patch, it appears that hint-bit changes are detected and a WAL entry is written on buffer flush if hint bits had been changed. I don't see anything wrong with this in theory. Am I missing something? Now, in the case where hint bits have been updated and a WAL record is required because the buffer is being flushed, requiring the WAL to be flushed up to that point may be a killer on performance. Has anyone tested it? -- Jonah H. Harris, Senior DBA myYearbook.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] Sync Rep: First Thoughts on Code
On Sun, 2008-12-14 at 12:57 -0500, Mark Mielke wrote: I'm curious about your suggestion to direct queries that need the latest snapshot to the 'primary'. I might have misunderstood it - but it seems that the expectation from some is that *all* sessions see the latest snapshot, so would this not imply that all sessions would be redirect to the 'primary'? I don't think it is reasonable myself, but I might be misunderstanding something... I said a snapshot taken on the primary, but the query would run on the standby. Synchronising primary and standby so that they are identical from the perspective of a query requires some synchronisation delay. I'm pointing out that the synchronisation delay can occur * at the time we apply WAL - which will slow down commits (i.e. #6 on my previous list of options) * at the time we run a query that needs to see primary and standby synchronised So the same effect can be achieved in various ways. The first way would require *all* transactions to be applied on standby, i.e. option #6 for all transactions. That is a performance disaster and I would not force that onto everybody. The second way can be done by taking a snapshot on the primary, with an associated LSN, then using that snapshot on the standby. That is somewhat complex, but possible. I see the requirement for getting the same answer on multiple nodes as a further extension of transaction isolation mode and think that not all people will want this, so we should allow that as an option. I'm not going to worry about this at the moment. Hot standby will be useful without this and so I regard this as a secondary objective. Rome wasn't built in a single release, or something like that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Block-level CRC checks
Jonah H. Harris escribió: On Mon, Dec 15, 2008 at 11:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: We don't really have an acceptable solution for the conflict with hint bit behavior. The shutdown issue is minor, agreed, but that's not the stumbling block. Agreed on the shutdown issue. But, didn't this patch address the hint bit setting as discussed? After performing a cursory look at the patch, it appears that hint-bit changes are detected and a WAL entry is written on buffer flush if hint bits had been changed. I don't see anything wrong with this in theory. Am I missing something? That only does heap hint bits, but it does nothing about pd_flags, the btree flags (btpo_cycleid I think), and something else I don't recall at the moment. This was all solvable however. The big problem with it was that it was using a new bit in pd_flags in unsafe ways. To make it safe you'd have to grab a lock on the page, which is very probably problematic. Now, in the case where hint bits have been updated and a WAL record is required because the buffer is being flushed, requiring the WAL to be flushed up to that point may be a killer on performance. Has anyone tested it? I didn't measure it but I'm sure it'll be plenty slow. -- 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] WIP: default values for function parameters
On Dec 15, 2008, at 11:05 AM, Peter Eisentraut wrote: In my mind, you just have to think about it hard enough to come to realize that, when viewed from the right angle, the semantic conflict might not exist after all. It's a bit tricky, but I think it's possible. Better for users not to have to think about it, IMHO. Still, they will, in the future, more likely be familiar with passing parameters to functions than with the XML stuff, and so won't have to worry about it until they use the XML stuff. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DTrace probes patch
The attached patch contains a couple of fixes in the existing probes and includes a few new ones. - Fixed compilation errors on OS X for probes that use typedefs - Fixed a number of probes to pass ForkNumber per the relation forks patch - The new probes are those that were taken out from the previous submitted patch and required simple fixes. Will submit the other probes that may require more discussion in a separate patch. -Robert Index: src/backend/access/transam/xlog.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.322 diff -u -3 -p -r1.322 xlog.c --- src/backend/access/transam/xlog.c 9 Nov 2008 17:51:15 - 1.322 +++ src/backend/access/transam/xlog.c 15 Dec 2008 05:12:41 - @@ -48,6 +48,7 @@ #include utils/builtins.h #include utils/guc.h #include utils/ps_status.h +#include pg_trace.h /* File path names (all relative to $PGDATA) */ @@ -486,6 +487,8 @@ XLogInsert(RmgrId rmid, uint8 info, XLog if (info XLR_INFO_MASK) elog(PANIC, invalid xlog info mask %02X, info); + TRACE_POSTGRESQL_XLOG_INSERT(rmid, info); + /* * In bootstrap mode, we don't actually log anything but XLOG resources; * return a phony record pointer. @@ -914,6 +917,8 @@ begin:; XLogwrtRqst FlushRqst; XLogRecPtr OldSegEnd; + TRACE_POSTGRESQL_XLOG_SWITCH(); + LWLockAcquire(WALWriteLock, LW_EXCLUSIVE); /* @@ -1313,12 +1318,14 @@ AdvanceXLInsertBuffer(bool new_segment) * Have to write buffers while holding insert lock. This is * not good, so only write as much as we absolutely must. */ + TRACE_POSTGRESQL_WAL_BUFFER_WRITE_START(); WriteRqst.Write = OldPageRqstPtr; WriteRqst.Flush.xlogid = 0; WriteRqst.Flush.xrecoff = 0; XLogWrite(WriteRqst, false, false); LWLockRelease(WALWriteLock); Insert-LogwrtResult = LogwrtResult; + TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DONE(); } } } @@ -5904,6 +5911,8 @@ CreateCheckPoint(int flags) if (log_checkpoints) LogCheckpointStart(flags); + TRACE_POSTGRESQL_CHECKPOINT_START(flags); + /* * Before flushing data, we must wait for any transactions that are * currently in their commit critical sections. If an xact inserted its @@ -6069,6 +6078,11 @@ CreateCheckPoint(int flags) if (log_checkpoints) LogCheckpointEnd(); +TRACE_POSTGRESQL_CHECKPOINT_DONE(CheckpointStats.ckpt_bufs_written, +NBuffers, CheckpointStats.ckpt_segs_added, +CheckpointStats.ckpt_segs_removed, +CheckpointStats.ckpt_segs_recycled); + LWLockRelease(CheckpointLock); } Index: src/backend/storage/buffer/bufmgr.c === RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v retrieving revision 1.242 diff -u -3 -p -r1.242 bufmgr.c --- src/backend/storage/buffer/bufmgr.c 19 Nov 2008 10:34:52 - 1.242 +++ src/backend/storage/buffer/bufmgr.c 15 Dec 2008 05:12:45 - @@ -203,8 +203,7 @@ ReadBuffer_common(SMgrRelation smgr, boo if (isExtend) blockNum = smgrnblocks(smgr, forkNum); - TRACE_POSTGRESQL_BUFFER_READ_START(blockNum, smgr-smgr_rnode.spcNode, - smgr-smgr_rnode.dbNode, smgr-smgr_rnode.relNode, isLocalBuf); + TRACE_POSTGRESQL_BUFFER_READ_START(forkNum, blockNum, smgr-smgr_rnode.spcNode, smgr-smgr_rnode.dbNode, smgr-smgr_rnode.relNode, isLocalBuf); if (isLocalBuf) { @@ -253,7 +252,7 @@ ReadBuffer_common(SMgrRelation smgr, boo if (VacuumCostActive) VacuumCostBalance += VacuumCostPageHit; - TRACE_POSTGRESQL_BUFFER_READ_DONE(blockNum, + TRACE_POSTGRESQL_BUFFER_READ_DONE(forkNum, blockNum, smgr-smgr_rnode.spcNode, smgr-smgr_rnode.dbNode, smgr-smgr_rnode.relNode, isLocalBuf, found); @@ -380,9 +379,9 @@ ReadBuffer_common(SMgrRelation smgr, boo if (VacuumCostActive) VacuumCostBalance += VacuumCostPageMiss; - TRACE_POSTGRESQL_BUFFER_READ_DONE(blockNum, smgr-smgr_rnode.spcNode, - smgr-smgr_rnode.dbNode, smgr-smgr_rnode.relNode, - isLocalBuf, found); +
Re: [HACKERS] Sync Rep: First Thoughts on Code
Simon Riggs wrote: I am truly lost to understand why the *name* synchronous replication causes so much discussion, yet nobody has discussed what they would actually like the software to *do* It's the color of the bikeshed ... We can make the reply to a commit message when any of the following events have occurred 1. We sent the message to standby 2. We received the message on standby 3. We wrote the WAL to the WAL file 4. We fsync'd the WAL file 5. We CRC checked the WAL commit record 6. We applied the WAL commit record In DRBD tradition, I suggest you implement all of them, or at least factor the code so that each of them can be a one line change. (We can probably later drop one or two options.) -- 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] Sync Rep: First Thoughts on Code
Robert Haas wrote: In fact, waiting for reply from standby server before acknowledging a commit to the client is a bit pointless otherwise. It puts you in a strange situation, where you're waiting for the commits in normal operation, but if there's a network glitch or the standby goes down, you're willing to go ahead without it. You get a high guarantee that your data is up-to-date in the standby, except when it isn't. Which isn't much of a guarantee. It protects you against a catastrophic loss of the primary, which is a non-trivial consideration. At the risk of being ghoulish, imagine that you are a large financial company headquartered in the world trade center. So you'd want all commits to wait until the transaction is safely replicated in the standby. But if there's a network glitch, or the standby is restarted, you're happy to reply to the client that it's committed if it's only safely committed in the primary. Essentially, you wait for the reply as long the standby responds within X seconds, but if it takes more then Y seconds, you don't wait. I know that people do that, but it seems counterintuitive to me. In that case, when the primary acks the transaction as committed, you only know that it's safely committed in the primary; it doesn't give any hard guarantee about the state in the standby. But when you consider the possibility to use the standby for queries, the synchronous mode makes sense too. I'm not opposed to providing all the options, but the synchronous mode where we can guarantee that if you query the standby, you will see the effects of all transactions committed in the primary, makes the synchronous mode much more interesting. If you don't need that property, you're most likely more happy with asynchronous mode anyway. -- 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] Sync Rep: First Thoughts on Code
It's a real promise. The reason you're getting hand-wavy answers is because it's such a basic requirement that I'm trying to point out just how fundamental a requirement it is. If you want to see the actual code which guarantees this take a look around the code for procarray - in particular the code for taking a snapshot. There are comments there about what locks are needed when committing and when taking a snapshot and why. But it's quite technical. -- Greg On 15 Dec 2008, at 02:03, Mark Mielke m...@mark.mielke.cc wrote: Greg Stark wrote: When the database says the data is committed it has to mean the data is really committed. Imagine if you looked at a bank account balance after withdrawing all the money and saw a balance which didn't reflect the withdrawal and allowed you to withdraw more money again... Within the same session - sure. From different sessions? PostgeSQL MVCC let's you see an older snapshot, although it does prefer to have the latest snapshot with each command. For allowing to withdraw more money again, I would expect some sort of locking SELECT ... FOR UPDATE; to be used. This lock then forces the two transactions to become serialized and the second will either wait for the first to complete or fail. Any banking program that assumed that it could SELECT to confirm a balance and then UPDATE to withdraw the money as separate instructions would be a bad banking program. To exploit it, I would just have to start both operations at the same time - they both SELECT, they both see I have money, they both give me the money and UPDATE, and I get double the money (although my balance would show a big negative value - but I'm already gone...). Database 101. When I asked for does PostgreSQL guarantee this? I didn't mean hand waving examples or hand waving expectations. I meant a pointer into the code that has some comment that says we want to guarantee that a commit in one session will be immediately visible to other sessions, and that a later select issued in the other sessions will ALWAYS see the commit whether 1 nanosecond later or 200 seconds later Robert's expectation and yours seem like taking this guarantee for granted rather than being justified with design intent and proof thus far. :-) Given my experiment to try and force it to fail, I can see why this would be taken for granted. Is this a real promise, though? Or just a unlikely scenario that never seems to be hit? To me, the question is relevant in terms of the expectations of a multi-replica solution. We know people have the expectation. We know it can be convenient. Is the expectation valid in the first place? I've probably drawn this question out too long and should do my own research and report back... Sorry... :-) Cheers, mark -- Mark Mielke m...@mielke.cc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] rules regression test failed on mingw
Hi, i'm seeing a fail in the rules regression, seems like it is not ordering the results right even when the regression has an explicit order by... i'm in a mingw32 5.1 on xp sp2 using msys 1.0.10 and gcc 3.4.2 attached the regression.diffs please make me know if i can provide more info -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 regression.diffs Description: Binary data -- 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] Block-level CRC checks
Jonah H. Harris escribió: On Sun, Dec 14, 2008 at 4:51 PM, Josh Berkus j...@agliodbs.com wrote: v11 doesn't apply to cvs head anymore I'm not currently working on this patch, sorry. Should we pull it from 8.4, then? Here's an updated patch against head. Thanks. NOTE, it appears that this (and the previous) patch PANIC with concurrent transaction log activity while database system is shutting down on shutdown if checksumming is enabled. This appears to be due to FlushBuffer (lines 1821-1828) during the checkpoint-at-shutdown. Yeah, I reported this issue several times. Similarly, I ran a pgbench, performed a manual checkpoint, and corrupted the tellers table myself using hexedit but the system didn't pick up the corruption at all :( Heh :-) Alvaro, have you given up on the patch or are you just busy on something else at the moment? I've given up until we find a good way to handle hint bits. Various schemes have been proposed but they all have more or less fatal flaws. -- 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] visibility map and reltuples
Greg Stark wrote: I wonder if we should switch to keeping reltuplesperpage instead. Then a partial vacuum could update it by taking the average number of tuples per page forbthe pages it saw. Perhaps adjusting it to the weights average between the old value and the new value based on how many pages were seen. The pages scanned by a partial vacuum isn't a random sample of pages in the table. That would bias the reltuplesperpage value towards those pages that are updated more. -- 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: default values for function parameters
Pavel Stehule wrote: Because AS is signal for collecting column (or label) names. I thing so we should use AS as Tom's proposal, together with SQL/XML functionality. Yes, please implement that. It's only idea: default behave is using as for param name specification, seconf with flag maybe labeled allows using AS in SQL/XML behave But this syntax don't allow use this feature together (it is maybe enought).\ create function json(variadic labeled values int[]) ... I didn't get 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] Block-level CRC checks
On Mon, Dec 15, 2008 at 11:50 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: That only does heap hint bits, but it does nothing about pd_flags, the btree flags (btpo_cycleid I think), and something else I don't recall at the moment. This was all solvable however. The big problem with it was that it was using a new bit in pd_flags in unsafe ways. To make it safe you'd have to grab a lock on the page, which is very probably problematic. :( Now, in the case where hint bits have been updated and a WAL record is required because the buffer is being flushed, requiring the WAL to be flushed up to that point may be a killer on performance. Has anyone tested it? I didn't measure it but I'm sure it'll be plenty slow. Yeah. What really sucks is that it would be fairly unpredictable and could easily result in unexpected production performance issues. It is pretty late in the process to continue with this design-related discussion, but I really wanted to see it in 8.4. -- Jonah H. Harris, Senior DBA myYearbook.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] Restore enforce_generic_type_consistency's breaks a farms
On Mon, Dec 15, 2008 at 11:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: ... But this type of problem has come up before. I wonder if we shouldn't do what was previously discussed: make has_table_privilege and related functions silently return FALSE, instead of throwing error, when given a nonexistent OID. On checking the archives, it seems most of the previous complaints have been about cache lookup failed errors arising from pg_table_is_visible and its ilk; those are used in the queries generated by psql's \d commands. So my proposal is to change the OID-accepting variants of has_table_privilege and friends, as well as pg_table_is_visible and friends, to silently return FALSE instead of failing when given a bad OID. I had originally thought we might change the underlying Or maybe NULL? ...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] pre-MED
On Mon, Dec 15, 2008 at 08:50:25AM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: Tom, I know you've been busy with lots of stuff, so here's a little reminder. I talked with a couple of people who know the back-end much better than I do. One said the above was way under-specified, and the other said he'd started work on it, but hasn't had much time. Since you're the one who brought this up, I think it's on you to flesh it out at least a little bit, or at least to describe it in enough detail that someone else could. I'm sorry, but I have far too much work in front of me reviewing patches that have a chance of getting into 8.4. I do not have time to do pre-implementation research for a patch that doesn't. You took on the responsibility by rejecting the working patch and saying how it should be implemented. That you failed to respond when asked before is very strictly your problem to solve, and I don't mean by bouncing my useful feature. Regards, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] Block-level CRC checks
Jonah H. Harris escribió: It is pretty late in the process to continue with this design-related discussion, but I really wanted to see it in 8.4. Well, it's hard to blame anyone but me, because I started working on this barely two weeks before the final commitfest IIRC. -- 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] plperl: Documentation on BYTEA decoding is wrong
On Monday 28 January 2008 05:37:03 Florian Weimer wrote: * Robert Treat: Note we've been using Theo's plperl bytea patch on one of our production servers for some time; if anyone wants access to that lmk. I'm interested. Could you post a pointer to this code, please? I had to do some work on this machine last week, and while digging into it, I ran across this email which appears to have fallen through the cracks. Not sure that anyone is still interested (heck, i've forgotten what this thread was even about), but the following patch should apply cleanly to 8.2.11. Note that personally I think this is a bit of a hack; I'd rather see a solution in the upstream code, but aiui this approach is frowned upon. If I get time, I might redo this as a new pl language (plperlo or something) rather than maintaing the patch. In any case, if anyone is interested on hacking on this, please drop us a line. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com Index: plperl.c === RCS file: /projects/cvsroot/pgsql/src/pl/plperl/plperl.c,v retrieving revision 1.123.2.4 diff -c -r1.123.2.4 plperl.c *** plperl.c 22 Jan 2008 20:19:53 - 1.123.2.4 --- plperl.c 15 Dec 2008 16:20:21 - *** *** 53,58 --- 53,59 Oid result_typioparam; int nargs; FmgrInfo arg_out_func[FUNC_MAX_ARGS]; + Oid arg_out_oid[FUNC_MAX_ARGS]; bool arg_is_rowtype[FUNC_MAX_ARGS]; SV *reference; } plperl_proc_desc; *** *** 142,147 --- 143,149 static Datum plperl_trigger_handler(PG_FUNCTION_ARGS); static plperl_proc_desc *compile_plperl_function(Oid fn_oid, bool is_trigger); + static Datum plperl_convert_sv_to_datum(Oid rtypeid, FmgrInfo *in_func, Oid typioparam, SV *sv); static SV *plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc); static void plperl_init_shared_libs(pTHX); *** *** 561,566 --- 563,592 return res; } + /* Build a Datum form an SV handling the case of bytea */ + + static Datum + plperl_convert_sv_to_datum(Oid rtypeid, FmgrInfo *in_func, Oid typioparam, SV *sv) + { + char *val; + STRLEN len; + Datum retval; + + val = SvPV(sv, len); + if(rtypeid == BYTEAOID) + { + StringInfoData buf; + initStringInfo(buf); + appendBinaryStringInfo(buf, val, len); + retval = ReceiveFunctionCall(in_func, buf, + typioparam, -1); + } + else { + retval = InputFunctionCall(in_func, val, + typioparam, -1); + } + return retval; + } /* Build a tuple from a hash. */ *** *** 767,773 while ((val = hv_iternextsv(hvNew, key, klen))) { int attn = SPI_fnumber(tupdesc, key); - Oid typinput; Oid typioparam; int32 atttypmod; FmgrInfo finfo; --- 793,798 *** *** 778,793 errmsg(Perl hash contains nonexistent column \%s\, key))); /* XXX would be better to cache these lookups */ ! getTypeInputInfo(tupdesc-attrs[attn - 1]-atttypid, ! typinput, typioparam); ! fmgr_info(typinput, finfo); atttypmod = tupdesc-attrs[attn - 1]-atttypmod; if (SvOK(val)) { ! modvalues[slotsused] = InputFunctionCall(finfo, ! SvPV(val, PL_na), typioparam, atttypmod); modnulls[slotsused] = ' '; } else --- 803,845 errmsg(Perl hash contains nonexistent column \%s\, key))); /* XXX would be better to cache these lookups */ ! if(tupdesc-attrs[attn - 1]-atttypid == BYTEAOID) ! { ! Oid typreceive; ! getTypeBinaryInputInfo(tupdesc-attrs[attn - 1]-atttypid, ! typreceive, typioparam); ! fmgr_info(typreceive, finfo); ! } ! else ! { ! Oid typinput; ! getTypeInputInfo(tupdesc-attrs[attn - 1]-atttypid, ! typinput, typioparam); ! fmgr_info(typinput, finfo); ! } atttypmod = tupdesc-attrs[attn - 1]-atttypmod; if (SvOK(val)) { ! STRLEN len; ! char *str; ! str = SvPV(val, len); ! if(tupdesc-attrs[attn - 1]-atttypid == BYTEAOID) ! { ! StringInfoData buf; ! initStringInfo(buf); ! appendBinaryStringInfo(buf, str, len); ! modvalues[slotsused] = ReceiveFunctionCall(finfo, ! buf, typioparam, atttypmod); + } + else + { + modvalues[slotsused] = InputFunctionCall(finfo, + str, + typioparam, + atttypmod); + } modnulls[slotsused] = ' '; } else *** *** 1077,1089 } else { ! char *tmp; ! ! tmp = OutputFunctionCall((desc-arg_out_func[i]), ! fcinfo-arg[i]); ! sv = newSVstring(tmp); ! XPUSHs(sv_2mortal(sv)); ! pfree(tmp); } } PUTBACK; --- 1129,1152 } else { ! if(desc-arg_out_oid[i] == BYTEAOID) ! { ! bytea *tmpbytes; ! tmpbytes =
Re: [HACKERS] Block-level CRC checks
Alvaro Herrera alvhe...@commandprompt.com writes: Jonah H. Harris escribió: Now, in the case where hint bits have been updated and a WAL record is required because the buffer is being flushed, requiring the WAL to be flushed up to that point may be a killer on performance. Has anyone tested it? I didn't measure it but I'm sure it'll be plenty slow. How hard would it be to just take an exclusive lock on the page when setting all these hint bits? It might be a big performance hit but it would only affect running with CRC enabled and we can document that. And it wouldn't involve contorting the existing code much. -- 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] Block-level CRC checks
On Mon, 2008-12-15 at 10:13 -0500, Bruce Momjian wrote: Jonah H. Harris wrote: Alvaro, have you given up on the patch or are you just busy on something else at the moment? I've given up until we find a good way to handle hint bits. Various schemes have been proposed but they all have more or less fatal flaws. Agreed. Though, I don't want to see this patch get dropped from 8.4. ALL, Alvaro has tried a couple different methods, does anyone have any other ideas? Feature freeze is not the time to be looking for new ideas. I suggest we save this for 8.5. Agreed, shall we remove the replication and se postgres patches too :P. If we can't fix the issue, then yeah let's rip it out but as it sits we have a hurdle that needs to be overcome not a new feature that needs to be implemented. Sincerely, Joshua D. Drake -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] visibility map and reltuples
On Mon, 15 Dec 2008, Greg Stark wrote: I wonder if we should switch to keeping reltuplesperpage instead. It would be preferrable to not touch the user side of reltuples if possible, since it's the only instant way to get a good estimate of the number of rows in a table right now. That's been a regular application technique for at least two years now, since http://www.varlena.com/GeneralBits/120.php popularized it. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Restore enforce_generic_type_consistency's breaks a farms
Robert Haas robertmh...@gmail.com writes: On Mon, Dec 15, 2008 at 11:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: So my proposal is to change the OID-accepting variants of has_table_privilege and friends, as well as pg_table_is_visible and friends, to silently return FALSE instead of failing when given a bad OID. I had originally thought we might change the underlying Or maybe NULL? Hmm, yeah, that's not a bad idea. 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] Block-level CRC checks
Joshua D. Drake escribió: If we can't fix the issue, then yeah let's rip it out but as it sits we have a hurdle that needs to be overcome not a new feature that needs to be implemented. Ideas for solving the hurdle are welcome. Agreed, shall we remove the replication and se postgres patches too :P. There are plenty of ideas for those patches, and lively discussion. They look successful to me, which this patch does not. Please do not troll. -- 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] Block-level CRC checks
Gregory Stark escribió: Alvaro Herrera alvhe...@commandprompt.com writes: Jonah H. Harris escribió: Now, in the case where hint bits have been updated and a WAL record is required because the buffer is being flushed, requiring the WAL to be flushed up to that point may be a killer on performance. Has anyone tested it? I didn't measure it but I'm sure it'll be plenty slow. How hard would it be to just take an exclusive lock on the page when setting all these hint bits? I guess it will be intolerably slow then. If we were to say we have CRC now, but if you enable it you have 1% of the performance we will get laughed at. -- 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] Block-level CRC checks
On Mon, 2008-12-15 at 14:29 -0300, Alvaro Herrera wrote: Joshua D. Drake escribió: If we can't fix the issue, then yeah let's rip it out but as it sits we have a hurdle that needs to be overcome not a new feature that needs to be implemented. Ideas for solving the hurdle are welcome. Agreed, shall we remove the replication and se postgres patches too :P. There are plenty of ideas for those patches, and lively discussion. They look successful to me, which this patch does not. Please do not troll. I wasn't trolling. I was making a point. 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] Block-level CRC checks
On Mon, Dec 15, 2008 at 12:30 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: How hard would it be to just take an exclusive lock on the page when setting all these hint bits? I guess it will be intolerably slow then. If we were to say we have CRC now, but if you enable it you have 1% of the performance we will get laughed at. Well, Oracle does tell users that enabling full CRC checking will cost ~5% performance overhead, which is reasonable to me. I'm not pessimistic enough to think we'd be down to 1% the performance of a non-CRC enabled system, but the locking overhead would probably be fairly high. The problem is, at this point, we don't really know what the impact would be either way :( -- Jonah H. Harris, Senior DBA myYearbook.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] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Hackers, We don't yet seem to have a clear specification for this feature, and the Other Open Source DB has shown us how problematic it is to get auto-partitioning wrong. Should we defer auto-partitioning to 8.5? --Josh -- 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] pre-MED
On Mon, 2008-12-15 at 09:12 -0800, David Fetter wrote: On Mon, Dec 15, 2008 at 08:50:25AM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: Tom, Since you're the one who brought this up, I think it's on you to flesh it out at least a little bit, or at least to describe it in enough detail that someone else could. I'm sorry, but I have far too much work in front of me reviewing patches that have a chance of getting into 8.4. I do not have time to do pre-implementation research for a patch that doesn't. You took on the responsibility by rejecting the working patch and saying how it should be implemented. That you failed to respond when asked before is very strictly your problem to solve, and I don't mean by bouncing my useful feature. As much as I would like to see this feature I believe you are taking the wrong track here David. Tom certainly has no responsibility to you or your patch. This is all volunteer. You catch more flies with honey than with Vinegar. Sincerely, Joshua D. Drake Regards, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pre-MED
David Fetter da...@fetter.org writes: On Mon, Dec 15, 2008 at 08:50:25AM -0500, Tom Lane wrote: I'm sorry, but I have far too much work in front of me reviewing patches that have a chance of getting into 8.4. I do not have time to do pre-implementation research for a patch that doesn't. You took on the responsibility by rejecting the working patch and saying how it should be implemented. Working? What you submitted was a self-acknowledged crude hack, which was shortly shown to have several major problems; a quick look in the archives suggests * fails on subplans, and possibly other things that ruleutils.c doesn't support well * not at all clear what to do with aliased column names * requires custom support in each PL, only one of which was actually implemented and there are probably more (I didn't reread the whole thread). The fact that I suggested a possible avenue to fixing some of those problems doesn't make it my responsibility to fix them ... especially not if I don't particularly approve of the hack in the first place. Even with all this fixed it would be a dead-end feature, but we'd be stuck with supporting it forever. You should be happy that I was willing to hold still for accepting the patch if the problems got fixed. 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] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Josh Berkus j...@agliodbs.com writes: Hackers, We don't yet seem to have a clear specification for this feature, and the Other Open Source DB has shown us how problematic it is to get auto-partitioning wrong. Should we defer auto-partitioning to 8.5? If we're serious about having a next generation partitioning with a concept of partition keys then it seems to me to make more sense to do that first and then add on a feature like this. This is still very useful. I haven't looked at the actual patch, does it require core changes or can it be stashed in a pgfoundry or contrib module? -- 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] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
On Mon, Dec 15, 2008 at 1:46 PM, Gregory Stark st...@enterprisedb.com wrote: Josh Berkus j...@agliodbs.com writes: Hackers, We don't yet seem to have a clear specification for this feature, and the Other Open Source DB has shown us how problematic it is to get auto-partitioning wrong. Should we defer auto-partitioning to 8.5? If we're serious about having a next generation partitioning with a concept of partition keys then it seems to me to make more sense to do that first and then add on a feature like this. +1 This is still very useful. I haven't looked at the actual patch, does it require core changes or can it be stashed in a pgfoundry or contrib module? what i don't like about this one is that it creates partitions at create table time and to manually add all new partitions (inherit tables and modify the trigger)... and what i want to see is an automatic creation when it's needed... -- 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] Block-level CRC checks
Joshua D. Drake escribió: On Mon, 2008-12-15 at 14:29 -0300, Alvaro Herrera wrote: Joshua D. Drake escribió: If we can't fix the issue, then yeah let's rip it out but as it sits we have a hurdle that needs to be overcome not a new feature that needs to be implemented. Ideas for solving the hurdle are welcome. Agreed, shall we remove the replication and se postgres patches too :P. There are plenty of ideas for those patches, and lively discussion. They look successful to me, which this patch does not. Please do not troll. I wasn't trolling. I was making a point. Okay. Sorry. I was defeating your point. -- 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] Mostly Harmless: Welcoming our C++ friends
Kurt Harriman harri...@acm.org wrote: That's why I have instead offered some patches to enable C++ for new extensions and add-on development with minimal impact to the C core. I've been a bit confused by this thread. We wrote a couple PostgreSQL functions (pdftotext and pdfisok) which use libpoppler. It didn't seem very hard to do without any of this. Granted, it isn't likely to be portable to all environments, which is why we haven't bothered to try to put it out for the community; but there didn't seem to be any great barriers for our environment (SuSE/gcc). Is this intended to make such efforts more portable, so they can be shared with more environments? Did we just get lucky? -Kevin P.S. Our environment: PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070115 (SUSE Linux) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] planner issue with constraint exclusion
Hello, I ran into this problem recently: https://projects.commandprompt.com/public/replicator/pastebin?show=f1288d4d8%0D Of the functions the only one that will use constraint_exclusion is the one that explicitly passes the date value. I kind of get why except for the one that uses EXECUTE. As EXECUTE has to replan the query, shouldn't it be able to use constraint_exclusion? (text also below for those that don't want to fire up a browser) CREATE OR REPLACE FUNCTION test_search1() RETURNS integer AS $$ DECLARE temp date; tmp integer; BEGIN SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1; SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = date(timehit); RETURN tmp; END $$ LANGUAGE plpgsql STABLE; CREATE OR REPLACE FUNCTION test_search2() RETURNS integer AS $$ DECLARE temp date; tmp integer; BEGIN SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1; SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '2006-07-17'; RETURN tmp; END $$ LANGUAGE plpgsql STABLE; CREATE OR REPLACE FUNCTION test_search3() RETURNS integer AS $$ DECLARE temp date; tmp integer; BEGIN SELECT date(timehit) INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1; SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = temp; RETURN tmp; END $$ LANGUAGE plpgsql STABLE; CREATE OR REPLACE FUNCTION test_search4() RETURNS integer AS $$ use strict; use warnings; my $sql = SELECT date(timehit) AS timehit FROM foo51 WHERE unit_id = 1 LIMIT 1; my $rv = spi_exec_query($sql); return undef if( ! defined $rv-{rows}[0]-{'timehit'} ); my $date = $rv-{rows}[0]-{'timehit'}; $sql = SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '$date'; $rv = spi_exec_query($sql); return undef if( ! defined $rv-{rows}[0]-{'unit_id'} ); my $unit_id = $rv-{rows}[0]-{'unit_id'}; return $unit_id; $$ LANGUAGE 'plperlu' STABLE; CREATE OR REPLACE FUNCTION test_search5() RETURNS integer AS $$ DECLARE temp date; tmp integer; BEGIN SELECT timehit INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1; EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || ; RETURN 1; END $$ LANGUAGE plpgsql STABLE; -- 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] planner issue with constraint exclusion
Joshua D. Drake j...@commandprompt.com writes: Of the functions the only one that will use constraint_exclusion is the one that explicitly passes the date value. Since you haven't shown us the constraints you're talking about, or the resulting plans, it's difficult for anyone to guess what's going on. 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] Mostly Harmless: Welcoming our C++ friends
[ just realized that I set this message aside to reply to later, and then forgot about it --- apologies ] Kurt Harriman harri...@acm.org writes: Tom Lane wrote: There is no such option, and won't be. Yours is the first comment anyone has posted to the list regarding my proposed c++configure patch, and it sounds alarmingly definite. May I ask you to elaborate? Have you more to say on the subject? Well, as I understand it the proposal is to build main.c as C++ in the hope of (1) identifying C++ incompatibilities in our include headers, and (2) linking C++ instead of C library on platforms where they are different. As for #1, main.c doesn't (and shouldn't) include a large fraction of the headers that might be interesting to a C++ add-on --- I'm surprised that it hits as many as it does, because it surely has no direct use for most of them. Extra #includes could be added to main.c just for the purpose of getting them C++-syntax-checked. They'd disappear again the next time Bruce runs his unnecessary-#include elimination script. And anyway the vast majority of the inclusions you show here are accidental, indirect inclusions that might go away in any header refactoring. As for #2, thanks but no thanks: the very last thing I want is to have a switch that causes us to start running on a different basic C library. That would open all sorts of portability and testability concerns. AFAIK there are only a few obsolete platforms where a C++-specific libc is needed, and I'm perfectly happy to blow off the idea of supporting C++ add-ons on them. So I'm willing to support a project of making *all* our headers (or at least all the ones a C++ addon could possibly care about) C++-safe, if there's buildfarm support for making sure they stay that way. But I don't approve of changing the way main.c gets compiled. I am, btw, still waiting for an actually plausible use-case for this. AFAICS the setjmp-vs-exceptions thing puts a very serious crimp in what you could hope to accomplish by importing a pile of C++ code. 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] planner issue with constraint exclusion
On Mon, 2008-12-15 at 14:28 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: Of the functions the only one that will use constraint_exclusion is the one that explicitly passes the date value. Since you haven't shown us the constraints you're talking about, or the resulting plans, it's difficult for anyone to guess what's going on. Table public.foo_stats_day Column | Type | Modifiers --+-+--- a_serv_id| integer | not null day | date| not null num_leads| integer | num_subscribed | integer | num_unsubscribed | integer | num_unverified | integer | Indexes: foo_stats_day_pkey PRIMARY KEY, btree (a_serv_id, day) Triggers: partion_public_foo_stats_day_trigger BEFORE INSERT OR UPDATE ON lead_stats_day FOR EACH ROW EXECUTE PROCEDURE partion_public_foo_stats_day() app=# \d year_2007.foo_stats_day_q1 Table year_2007.foo_stats_day_q1 Column | Type | Modifiers --+-+--- a_serv_id| integer | not null day | date| not null num_leads| integer | num_subscribed | integer | num_unsubscribed | integer | num_unverified | integer | Indexes: foo_stats_day_pkey_q1 PRIMARY KEY, btree (a_serv_id, day), tablespace year_2007_indexes Check constraints: foo_stats_day_q1_day_check CHECK (day = '2007-01-01'::date AND day '2007-04-01'::date) Inherits: foo_stats_day Tablespace: year_2007 I am not sure which plans I should show you as they are function plans thereby not very useful. Sincerely, Joshua D. Drake regards, tom lane -- 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] Sync Rep: First Thoughts on Code
On Mon, 2008-12-15 at 09:19 -0500, Robert Haas wrote: I understand you're point, but I think there's still a use case. The idea is that declaring the secondary dead is a rare event, and there's some mechanism by which you're enabled to page your network staff, and they hightail it into the office to fix the problem. It might not be the way that you want to run your system, but I don't think it's unreasonable for someone else to want it. Agreed: there's an analogy to RAID here. When a disk goes out, it still allows writes, but moves to a degraded state. Hopefully your monitoring system notifies you, and you fix it. Also, let's say that the standby suffers catastrophic storage failure. Now you only have your data on one server anyway (the primary). Rejecting new transactions from committing doesn't save all the old transactions in the event of a subsequent storage failure on the primary. I'm not advocating this option in particular, other than saying that it seems like a reasonable option to me. Regards, Jeff Davis -- 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] SQL/MED compatible connection manager
On Fri, Dec 12, 2008 at 7:55 AM, Peter Eisentraut pete...@gmx.net wrote: Now I have a question about the FDW C interface. The way I understand it, an SQL/MED-enabled server and a FDW each have a specific API by which they communicate. Supposedly, each database vendor should be able to ship a binary library for its FDW and each SQL/MED-enabled server should be able to load and use it. (If you don't believe in binary compatibility, then I think there should at least be source-level interface compatibility.) Yes, all FDWs should be similar to ODBC drivers in that they are self-contained and interface with the database through a defined API. What happens inside them should be irrelevant to PG. -- Jonah H. Harris, Senior DBA myYearbook.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] Function with defval returns error
On Monday 15 December 2008 15:43:00 Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Rushabh Lathia wrote: I think this should not return error as the input args here is timestamp... inputs? In theory yes, but it's currently not that smart. This is truly horrid. Was that patch *really* ready to commit? I noticed some comments added to polymorphism.sql that certainly look like there's still a lot of half-bakedness in it. There is that one case where a call that could be allowed is overly-cautiously rejected. That only happens if you have a mix of overloading and default parameters. It's not really half-baked in the sense that it is not digestible; it's just not the greatest cake yet. It's improvement-compatible. -- 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] SQL/MED compatible connection manager
On Monday 15 December 2008 22:30:19 Jonah H. Harris wrote: On Fri, Dec 12, 2008 at 7:55 AM, Peter Eisentraut pete...@gmx.net wrote: Now I have a question about the FDW C interface. The way I understand it, an SQL/MED-enabled server and a FDW each have a specific API by which they communicate. Supposedly, each database vendor should be able to ship a binary library for its FDW and each SQL/MED-enabled server should be able to load and use it. (If you don't believe in binary compatibility, then I think there should at least be source-level interface compatibility.) Yes, all FDWs should be similar to ODBC drivers in that they are self-contained and interface with the database through a defined API. What happens inside them should be irrelevant to PG. What we are currently trying to figure out is the best method to introduce extensions to the API. -- 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] planner issue with constraint exclusion
* Joshua D. Drake (j...@commandprompt.com) wrote: On Mon, 2008-12-15 at 14:28 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: Of the functions the only one that will use constraint_exclusion is the one that explicitly passes the date value. Since you haven't shown us the constraints you're talking about, or the resulting plans, it's difficult for anyone to guess what's going on. I am not sure which plans I should show you as they are function plans thereby not very useful. Ideally, do a 'raise notice' with the exact string you're passing to EXECUTE, and then run an explain on that. Constraint Exclusion certainly does work when using explain and constants in general. Have you tried adding an explicit cast, ala: EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || '''::date'; Enjoy, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Sync Rep: First Thoughts on Code
Peter Eisentraut wrote: Simon Riggs wrote: I am truly lost to understand why the *name* synchronous replication causes so much discussion, yet nobody has discussed what they would actually like the software to *do* It's the color of the bikeshed ... Hmmm. I thought this was pretty clear. There's three levels of synch which are useful features: 1) synchronus standby which is really asynchronous, but only has a gap of 100ms. 2) Synchronous standby which guarentees that all committed transactions are on the failover node and that no data will be lost for failover, but the failover node is still in standby mode. 3) Synchronous replication where the standby node has identical transactions to the master node, and is queryable read-only. Any of these levels would be useful and allow a certain number of our users to deploy PostgreSQL in an environment where it wasn't used before. So if we can only do (2) for 8.4, that's still very useful for telecoms and banks. --Josh -- 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] odd output in restore mode
Martin Zaun wrote: 4. Issue: missing break in switch, silent override of '-l' argument? This behaviour has been in there before and is not addresses by the patch: The user-selected Win32 mklink command mode is never applied due to a missing 'break' in CustomizableInitialize(): switch (restoreCommandType) { case RESTORE_COMMAND_WIN32_MKLINK: SET_RESTORE_COMMAND(mklink, WALFilePath, xlogFilePath); case RESTORE_COMMAND_WIN32_COPY: SET_RESTORE_COMMAND(copy, WALFilePath, xlogFilePath); break; I have added the missing 'break' to CVS HEAD; thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Sync Rep: First Thoughts on Code
Josh Berkus wrote: Hmmm. I thought this was pretty clear. There's three levels of synch which are useful features: 1) synchronus standby which is really asynchronous, but only has a gap of 100ms. 2) Synchronous standby which guarentees that all committed transactions are on the failover node and that no data will be lost for failover, but the failover node is still in standby mode. 3) Synchronous replication where the standby node has identical transactions to the master node, and is queryable read-only. Any of these levels would be useful Isn't the queryable read-only feature totally orthogonal with how synchronous the replication is? For one reporting system I have, where new data is continually being added every second; I'd love to have a read-only-slave even if that system has the 100ms gap you mentioned in #1. Heck I don't care if the queries it runs even have a 100 *minute* gap; but I sure would like it to be synchronous in the sense that all the transactions to survive a failure of the primary. -- 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] Sync Rep: First Thoughts on Code
Isn't the queryable read-only feature totally orthogonal with how synchronous the replication is? Yes. However, it introduces specific difficult issues which an unreadable synchronous slave does not have. --Josh -- 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] Mostly Harmless: Welcoming our C++ friends
Tom Lane wrote: I am, btw, still waiting for an actually plausible use-case for this. AFAICS the setjmp-vs-exceptions thing puts a very serious crimp in what you could hope to accomplish by importing a pile of C++ code. The one use-case I can think of that imports a pile of C++ code is the GEOS library that PostGIS uses (used?): http://postgis.refractions.net/support/wiki/index.php?GEOS GEOS is a C++ port of the JTS Topology Suite. It is used by PostGIS to implement Topological functions. However it seems to work fine even without the C++-header project, so I must be missing something here... -- 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] Mostly Harmless: Welcoming our C++ friends
Ron Mayer wrote: Tom Lane wrote: I am, btw, still waiting for an actually plausible use-case for this. AFAICS the setjmp-vs-exceptions thing puts a very serious crimp in what you could hope to accomplish by importing a pile of C++ code. The one use-case I can think of that imports a pile of C++ code is the GEOS library that PostGIS uses (used?): There are also quite a number of OSS algorithms, useful for query optimization or otherwise, which are written in C++. For example, the fully OSS implementation of annealing (potentially useful as a replacement for GEQO) is in C++. However, the real reason to do this is to attract C++ hackers to hack on PostgreSQL and extend it. Most of what makes PostgreSQL cool now we got because PostgreSQL is so easy for C geeks to hack on. Who knows what the C++ crowd will contribute if given the opportunity? It's not the stuff we *know* we can get which is exciting, it's the stuff we don't know about. (and yes, I realize this would hold true of other programming languages as well. However, we can't support them as easily as C++) As the Guy Who Is Obsessed With Making Our Community Grow (GWIOWMOCG), I strongly support this goal. Although the other issues like breakage need fixing. --Josh -- 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] Sync Rep: First Thoughts on Code
On Mon, 2008-12-15 at 13:43 -0800, Josh Berkus wrote: Isn't the queryable read-only feature totally orthogonal with how synchronous the replication is? Yes. However, it introduces specific difficult issues which an unreadable synchronous slave does not have. Don't think it's hugely difficult, but there are multiple ways of doing this. But it is irrelevant until we have the basic ability to run queries. I've explained this twice now on different parts of this thread. Could I politely direct your attention to those posts? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Sync Rep: First Thoughts on Code
Simon, I've explained this twice now on different parts of this thread. Could I politely direct your attention to those posts? Chill. I was just explaining that the *goal* of sync standby was not complicated or really something to be argued about. It's pretty clear. --Josh -- 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] odd output in restore mode
Bruce Momjian wrote: Martin Zaun wrote: 4. Issue: missing break in switch, silent override of '-l' argument? This behaviour has been in there before and is not addresses by the patch: The user-selected Win32 mklink command mode is never applied due to a missing 'break' in CustomizableInitialize(): switch (restoreCommandType) { case RESTORE_COMMAND_WIN32_MKLINK: SET_RESTORE_COMMAND(mklink, WALFilePath, xlogFilePath); case RESTORE_COMMAND_WIN32_COPY: SET_RESTORE_COMMAND(copy, WALFilePath, xlogFilePath); break; I have added the missing 'break' to CVS HEAD; thanks. Why no backpatch to 8.3? Seems like a clear bugfix to me. //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][PATCHES] odd output in restore mode
Since this patch was rejected, I have added the attached documentation to pg_standby to mention the sleep() we do. --- Martin Zaun wrote: Below my comments on the CommitFest patch: pg_standby minor changes for Windows Simon, I'm sorry you got me, a Postgres newbie, signed up for reviewing your patch ;) To start with, I'm not quite sure of the status of this patch since Bruce's last comment on the -patches alias: Bruce Momjian wrote: OK, based on these observations I think we need to learn more about the issues before making any changes to our code. From easy to difficult: 1. Issues with applying the patch to CVS HEAD: The second file in the patch Index: doc/src/sgml/standby.sgml appears to be misnamed -- the existing file in HEAD is Index: doc/src/sgml/pgstandby.sgml However, still had issues after fixing the file name: m...@garu:~/pg/pgsql$ patch -c -p0 ../pg_standby.patch patching file contrib/pg_standby/pg_standby.c patching file doc/src/sgml/pgstandby.sgml Hunk #1 FAILED at 136. Hunk #2 FAILED at 168. Hunk #3 FAILED at 245. Hunk #4 FAILED at 255. 4 out of 4 hunks FAILED -- saving rejects to file doc/src/sgml/pgstandby.sgml.rej 2. Missing description for new command-line options in pgstandby.sgml Simon Riggs wrote: Patch implements * recommendation to use GnuWin32 cp on Windows Saw that in the changes to pgstandby.sgml, and looks ok to me, but: - no description of the proposed new command-line options -h and -p? 3. No coding style issues seen Just one comment: the logic that selects the actual restore command to be used has moved from CustomizableInitialize() to main() -- a matter of personal taste, perhaps. But in my view the: + the #ifdef WIN32/HAVE_WORKING_LINK logic has become easier to read 4. Issue: missing break in switch, silent override of '-l' argument? This behaviour has been in there before and is not addresses by the patch: The user-selected Win32 mklink command mode is never applied due to a missing 'break' in CustomizableInitialize(): switch (restoreCommandType) { case RESTORE_COMMAND_WIN32_MKLINK: SET_RESTORE_COMMAND(mklink, WALFilePath, xlogFilePath); case RESTORE_COMMAND_WIN32_COPY: SET_RESTORE_COMMAND(copy, WALFilePath, xlogFilePath); break; A similar behaviour on Non-Win32 platforms where the user-selected ln may be silently changed to cp in main(): #if HAVE_WORKING_LINK restoreCommandType = RESTORE_COMMAND_LN; #else restoreCommandType = RESTORE_COMMAND_CP; #endif If both Win32/Non-Win32 cases reflect the intended behaviour: - I'd prefer a code comment in the above case-fall-through, - suggest a message to the user about the ignored ln / mklink, - observe that the logic to override of the '-l' option is now in two places: CustomizableInitialize() and main(). 5. Minor wording issue in usage message on new '-p' option I was wondering if the always in the usage text fprintf(stderr, -p always uses GNU compatible 'cp' command on all platforms\n); is too strong, since multiple restore command options overwrite each other, e.g. -p -c applies Windows's copy instead of Gnu's cp. 6. Minor code comment suggestion Unrelated to this patch, I wonder if the code comments on all four time-related vars better read seconds instead of amount of time: int sleeptime = 5; /* amount of time to sleep between file checks */ int holdtime = 0; /* amount of time to wait once file appears full */ int waittime = -1; /* how long we have been waiting, -1 no wait * yet */ int maxwaittime = 0;/* how long are we prepared to wait for? */ 7. Question: benefits of separate holdtime option from sleeptime? Simon Riggs wrote: * provide holdtime delay, default 0 (on all platforms) Going back on the hackers+patches emails and parsing the code comments, I'm sorry if I missed that, but I'm not sure I've understood the exact tuning benefits that introducing the new holdtime option provides over using the existing sleeptime, as it's been the case (just on Win32 only). 8. Unresolved question of implementing now/later a cp replacement Simon Riggs wrote: On Tue, 2008-07-01 at 13:44 +0300, Heikki Linnakangas wrote: This seems pretty kludgey to me. I wouldn't want to install GnuWin32 utilities on a production system just for the cp command, and I don't know how I would tune holdtime properly for using copy. And it seems risky to have defaults that are known to not work reliably. How about implementing a replacement function for cp ourselves? It seems pretty trivial to do. We could use that on Unixes as well, which would keep the differences between
[HACKERS] 8.4 replication questions
Forgive me if this is clear to everyone else, but regarding the new replication options in 8.4: Will existing PITR backup techniques work without modification? Will existing techniques for warm standby with a custom script (not using pg_standby) work without modification? We will want to continue with an asynchronous approach involving WAL file shipping; however it might be a usable enhancement for us if there was a way to append to a WAL file copy as the production WAL file filled, especially if there's a way to fire a script when it is complete. Will that be possible? In particular, we need to copy WAL files from the database server to two different locations, at least one of which can't be running PostgreSQL software. -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] Sync Rep: First Thoughts on Code
On Mon, 2008-12-15 at 13:06 -0800, Josh Berkus wrote: Peter Eisentraut wrote: Simon Riggs wrote: I am truly lost to understand why the *name* synchronous replication causes so much discussion, yet nobody has discussed what they would actually like the software to *do* It's the color of the bikeshed ... Hmmm. I thought this was pretty clear. There's three levels of synch which are useful features: 1) synchronus standby which is really asynchronous, but only has a gap of 100ms. 2) Synchronous standby which guarentees that all committed transactions are on the failover node and that no data will be lost for failover, but the failover node is still in standby mode. 3) Synchronous replication where the standby node has identical transactions to the master node, and is queryable read-only. Any of these levels would be useful and allow a certain number of our users to deploy PostgreSQL in an environment where it wasn't used before. So if we can only do (2) for 8.4, that's still very useful for telecoms and banks. The (2) mentioned here could be any of sync points #2-5 referred to upthread. Different people have requested different levels of robustness. Looking at DRBD and Oracle, they both subdivide (2) into at least two further levels of option. So (2) is too broad a brush to paint with. I don't believe that (2) as stated is sufficient for banks, though is reasonable for many telco applications. But #4 or #5 would be suitable for banks, i.e. we must fsync to disk for very high value transactions. The extra code to do this is minor, which is why I've asked Fujii-san to include it now within the patch. All of this is controllable by the parameter synchronous_replication, which it is important to note can be set for each individual transaction rather than just fixed for the whole server. This is identical to the way we can mix synchronous commit and asynchronous commit transactions. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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][PATCHES] odd output in restore mode
Magnus Hagander wrote: Bruce Momjian wrote: Martin Zaun wrote: 4. Issue: missing break in switch, silent override of '-l' argument? This behaviour has been in there before and is not addresses by the patch: The user-selected Win32 mklink command mode is never applied due to a missing 'break' in CustomizableInitialize(): switch (restoreCommandType) { case RESTORE_COMMAND_WIN32_MKLINK: SET_RESTORE_COMMAND(mklink, WALFilePath, xlogFilePath); case RESTORE_COMMAND_WIN32_COPY: SET_RESTORE_COMMAND(copy, WALFilePath, xlogFilePath); break; I have added the missing 'break' to CVS HEAD; thanks. Why no backpatch to 8.3? Seems like a clear bugfix to me. I knew that was going to be asked. At this point I am pulling comments from rejected patches into CVS commits; these are not even submitted patches. I am not comfortable backpatching anything when using that system because obviously no one else even cared enough to submit a patch for it, let alone test it. If someone wants to batckpatch this or submit a patch to be backpatched, that is fine. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] odd output in restore mode
Tom Lane wrote: Heikki Linnakangas hei...@enterprisedb.com writes: Martin Zaun wrote: With these avenues to be explored, can the pg_standby patch on the CommitFest wiki be moved to the Returned with Feedback section? Yes, I think we can conclude that we don't want this patch as it is. Instead, we want a documentation patch that describes the problem, mentioning that GNU cp is safe, or you can use the copy+rename trick. Right, after which we remove the presently hacked-in delay. I've updated the commitfest page accordingly. I have documented the sleep() call and that GNU cp is safe, but did not remove the delay, nor mention copy+rename. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] upper()/lower() truncates the result under Japanese Windows
Tom Lane wrote: Hiroshi Inoue in...@tpf.co.jp writes: Upper(), lower() or initcap() function truncates the result under Japanese Windows with e.g. the server encoding=UTF-8 and the LC_CTYPE setting Japanese_japan.932 . Hmm, I guess that makes sense, since the LC_CTYPE implies an encoding other than UTF-8; MB_CUR_MAX should be set according to LC_CTYPE. The proposed patch seems pretty ugly though. Why don't we just stop using MB_CUR_MAX altogether? These three functions are the only references to it AFAICS. Although it looks ugly, it only follows what wchar2char() does. Though I don't like to use MB_CUR_MAX, it seems safe as long as wchar2char() calls wcstombs(). 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] odd output in restore mode
On Mon, 2008-12-15 at 17:10 -0500, Bruce Momjian wrote: Why no backpatch to 8.3? Seems like a clear bugfix to me. I knew that was going to be asked. 8.3 is really where this is needed. 8.4 has almost no need of this. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] [DOCS] [ADMIN] shared_buffers and shmmax
I have added this TODO item: Rationalize the discrepancy between settings that use values in bytes and SHOW that returns the object count * http://archives.postgresql.org/pgsql-docs/2008-07/msg7.php --- Greg Sabino Mullane wrote: [ There is text before PGP section. ] -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 NotDashEscaped: You need GnuPG to verify this message shared_buffers is in disk block size, typically 8K The table the OP is looking at (table 17.2 in the 8.3 docs) predates the ability to specify shared_buffers in KB or MB instead of number-of-buffers. I agree it's not entirely obvious that what it means is multiply your setting in KB/MB by 8400/8192. Anybody have an idea how to clarify things? Bite the bullet and start showing the buffer settings as a pure number of bytes everywhere, and get rid of the confusing '8kB' unit in pg_settings? Things like this don't help our cause: test=# show shared_buffers; shared_buffers 24MB (1 row) test=# set temp_buffers = '24MB'; SET test=# show temp_buffers; temp_buffers -- 3072 test=# select name, setting from pg_settings where name ~ 'buffers'; name | setting +- shared_buffers | 3072 temp_buffers | 3072 wal_buffers| 8 test=# show wal_buffers; wal_buffers - 64kB -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200807241351 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkiIwYYACgkQvJuQZxSWSsiY5wCfU/tca+1JakWaMCDDRHEHk/Uj 1rcAoMi1FNGSpJhyXWde1psygq6v3MlS =gCPg -END PGP SIGNATURE- -- Sent via pgsql-docs mailing list (pgsql-d...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] TODO item: Have psql show current values for a sequence
Sorry for the delay on this. What I did was to mark the simple TODO items as done and add an additional TODO item to list all sequence settings: D o Have psql show current values for a sequence o Have psql \ds show all sequences and their settings * http://archives.postgresql.org/pgsql-hackers/2008-07/msg00916.php --- Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Wow. I adjusted the patch slightly and applied it; the updated version is attached. We have been waiting for this to be done for quite some time. Thanks. Hmm ... I don't think that this patch actually addresses the TODO item. The TODO item seems to have originated here http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1220;r2=1.1221;f=h in response to this question on pgsql-novice: How can I list all the sequences in the database, with their attributes (such as last_value)? (I'm having a hard time guessing 'seq-name'; the 'A_id_seq' formula did not work.) http://archives.postgresql.org/pgsql-novice/2004-02/msg00148.php This applied-with-little-discussion patch only shows the sequence values if you do a \d on a specific sequence, or \d on a wildcard that happens to include some sequences (and probably a lot of other stuff too, causing the resulting display to be far too long to be useful). My interpretation of the TODO item has always been that we should improve \ds to include all the useful information in a format that requires only one line per sequence. The reason it has remained undone for four years is that that's hard given the existing catalog representation of sequences and the constraints of describe.c's implementation. (I recall at least one failed patch that tried to do this, though I can't find it in the archives right now.) I find the present patch to be pretty useless: it's not a material advance over doing select * from sequence-name. I think it should be reverted and the TODO item reinstated --- perhaps with more detail about what the item really is requesting. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Any reason not to return row_count in cursor of plpgsql?
laser wrote: hi all, I read the code that it seems easy for the cursor in plpgsql to return ROW_COUNT after MOVE LAST etc. The SPI_processed variable already there, but didn't put it into estate structure, any reason for that? [ Sorry for the delay.] Would some tests how Oracle behaves in this case? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Patch to eliminate duplicate b64 code from pgcrypto
Would someone who understand pgcrypto please review this? http://archives.postgresql.org/message-id/1216335149.11208.9.ca...@bloodnok.com --- Marc Munro wrote: -- Start of PGP signed section. I am attaching a patch to eliminate duplicate b64_encode and decode functions from pgcrypto, and to expose those functions for use by add-ins (I want to use them in Veil). The patch was made against CVS head today. It compiles and tests successfully. Though I was unable to run pgrypto regression tests, I did give the b64 encoding a sanity check. I also added a b64_char() function that is now used from pgcrypto. This allowed me to remove the duplicate _base64 character array. I hope this is the correct place to submit the patch. The wiki (http://wiki.postgresql.org/wiki/Submitting_a_Patch) refers to pgsql-patches but I believe that is now deprecated. __ Marc [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers