Re: [HACKERS] Summary and Plan for Hot Standby
On Mon, 2009-11-16 at 13:23 +0900, Tatsuo Ishii wrote: Just a question: - Does Hot Standby allow to use prepared query (not prepared transaction) in standby? I mean: Parse message from frontend can be accepted by standby? Yes, no problem with any of those kind of facilities - Can we create tempory tables in standby? No, but this is for two reasons * CREATE TEMPORARY TABLE actually writes to catalog tables. It doesn't need to do that, so allowing this would require some medium-heavy lifting of the way temp tables work. A preliminary design was agreed in July 2008. I believe it would be a popular feature, since about 40-50% of people ask for this. * CREATE TEMP TABLE is currently considered to be disallowed during read only transactions. That might be able to change if the underlying physical operation were write-free. -- Simon Riggs www.2ndQuadrant.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] Patch committers
On Mon, Nov 16, 2009 at 02:08, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) yes, easily. Just pass it the commit id. And unlike cvs, there is one diff for the patch, not one for every file ;) For example: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d3359020ef7e0fba02ac552d88ede0c3ce5128cc turning it into context-diff style will require patching gitweb though, it doesn't do that by default. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Patch committers
On Mon, Nov 16, 2009 at 02:32, Robert Haas robertmh...@gmail.com wrote: On Sun, Nov 15, 2009 at 8:08 PM, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) I dunno about the automated comment generation thing. Seems like it could generate a lot of comment spam inside the app. Also, I'm not sure if we really want to move away from the mailing list as the primary way of sharing patches. One nice thing about having them on the mailing list is that it is a permanent archive. Another is that it it is a push mechanism - you don't have to go to the CommitFest app and notice, hey, there are new patches here, or new versions of existing patches. You just read your email and there they are. Yeah, I agree. I'm not averse to adding fields for repo and branch; that seems pretty uncontroversial, since they'll be optional and those who don't want to use them needn't. But I think the rest of this needs a bit more thought. Just MHO, of course. Just to be clear, I wasn't suggesting auto-adding comments. I was just suggesting adding a flag that'll put some kind of marker on the line saying there's something newer in the git repo. Obviously only if the git repo has been specified, and not mandatory. The idea being to encourage people to use git, not force them to :-) And exploiting the extra value we get from git for patches that do change often. It's not a replacement - the current policy of posting patches to the list is still the way they should be submitted. It's just an extra tool. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] [COMMITTERS] pgsql: /home/peter/commit-msg
Magnus Hagander wrote: On Mon, Nov 16, 2009 at 08:29, David Fetter da...@fetter.org wrote: On Mon, Nov 16, 2009 at 06:56:54AM +0200, Peter Eisentraut wrote: Yeah, sorry guys. I fixed the CVS log message now. Strangely, the git repo still shows the old message. For the record, there's the new one: I don't find that strange at all. In git, the commit message is part of the commit, and thus the SHA1. If it changes, it would be a different commit. Which would change history and break the repositories of anybody pulling from it. So it's not only not strange, I'm very happy it didn't pull those changes and broke my repository :-) Yeah, I'm glad it didn't do anything funny with the mirror. Now, if we want to correct that the way to do it is to rebuild the git mirror from scratch and have everybody start over, I think :-) While you're not supposed to change history in any RCS, git makes it a lot harder than cvs to do it... Nah, you'd only have to back out to the commit before the one that was broken. And actually git provides quite simple commands to do that, see git-reset for example. I forget how exactly the mirroring software works, but I presume it would then see that commit as a new one, as well as anything on top of it, and mirror them. Rewriting git history like that would mean that anyone who has pulled from the mirror since that commit happened would get an error the next time they try to pull/fetch again. But you can easily get over that by doing git fetch --force. So we could rewrite the git history too, and I think it would be quite nice to have the right commit message there as well. But I don't care enough to volunteer to do the legwork. If we are going to do it, we should do it as soon as possible, while we're only a couple of commits ahead of that point. It's going to be more painful later on. -- 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] proposal: using PQexecParams in psql (using variables as real params)
2009/11/16 Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp: Pavel Stehule pavel.steh...@gmail.com wrote: I propose to add possibility to use psql variables as real query parameters. The goal of this proposal is simplification of creating psql based commands. postgres=# \pexec Separately passing parameters is on. postgres=# select upper(:message); There might be good if we can use both old and new behaviors at once instead of \pexec switch. Something like: SELECT * FROM :tablename WHERE id = $value BTW, this proposal is a partial solution for scripting in psql. But we will want other goodies soon - assignment, if, loop... If there is a total solution, it would be better than a partial one. I've heard pgAdmin uses pgScript as a client-side language. Should we also take the language (or client-side plpgsql) in psql? I don't would to reimplement pgScript. Personally I prefer some scripting possibilities in psql too, but when pgScript exists, then we could to use it. What more - we have a DO statement. So there are tools. DO is great, now I missing some like USING clause: DO (tablename varchar, value varchar) $$ EXECUTE 'SELECT * FROM '|| tablename || 'WHERE id = $1' USING value; $$ USING :tablename, :value; I don't would to create new syntax for identifiers - see parallel thread. Proposed feature is maximally simple and transparent. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: /home/peter/commit-msg
On Mon, Nov 16, 2009 at 09:05, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 08:29, David Fetter da...@fetter.org wrote: On Mon, Nov 16, 2009 at 06:56:54AM +0200, Peter Eisentraut wrote: Yeah, sorry guys. I fixed the CVS log message now. Strangely, the git repo still shows the old message. For the record, there's the new one: I don't find that strange at all. In git, the commit message is part of the commit, and thus the SHA1. If it changes, it would be a different commit. Which would change history and break the repositories of anybody pulling from it. So it's not only not strange, I'm very happy it didn't pull those changes and broke my repository :-) Yeah, I'm glad it didn't do anything funny with the mirror. Now, if we want to correct that the way to do it is to rebuild the git mirror from scratch and have everybody start over, I think :-) While you're not supposed to change history in any RCS, git makes it a lot harder than cvs to do it... Nah, you'd only have to back out to the commit before the one that was broken. And actually git provides quite simple commands to do that, see git-reset for example. I forget how exactly the mirroring software works, but I presume it would then see that commit as a new one, as well as anything on top of it, and mirror them. I would assume that yes, but I haven't tested it. Rewriting git history like that would mean that anyone who has pulled from the mirror since that commit happened would get an error the next time they try to pull/fetch again. But you can easily get over that by doing git fetch --force. So we could rewrite the git history too, and I think it would be quite nice to have the right commit message there as well. But I don't care enough to volunteer to do the legwork. If we are going to do it, we should do it as soon as possible, while we're only a couple of commits ahead of that point. It's going to be more painful later on. Yeah. Right now, that commit is actually the top, so it would be just one. I was about to do it right now, then I realized that it hits backbranches as well so it's not quite so easy. So I won't do it now - I have to leave for JPUG pretty soon, and I don't want to risk leaving us with an inconsistent git mirror if things go wrong. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] [COMMITTERS] pgsql: /home/peter/commit-msg
Heikki Linnakangas wrote: So we could rewrite the git history too, and I think it would be quite nice to have the right commit message there as well. But I don't care enough to volunteer to do the legwork. If we are going to do it, we should do it as soon as possible, while we're only a couple of commits ahead of that point. It's going to be more painful later on. We had a little chat with Magnus, and decided to stop the cron job that updates the git mirror. The commit with wrong commit message is currently the latest commit, so it'll be quite painless to back it out now before more commits are mirrored. However, Magnus is just getting on a plane, so he doesn't want to back out the commit right now because he wouldn't have time to fix it if something goes wrong. Stopping the mirror buys us time to do it later and test it properly, and if we decide to leave it as it is in the end, we can just re-enable the cron job. In any case, we'll have the mirroring re-enabled in a couple of days. -- 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] Patch committers
Magnus Hagander wrote: On Mon, Nov 16, 2009 at 02:08, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) yes, easily. Just pass it the commit id. And unlike cvs, there is one diff for the patch, not one for every file ;) For example: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d3359020ef7e0fba02ac552d88ede0c3ce5128cc turning it into context-diff style will require patching gitweb though, it doesn't do that by default. Any idea how the get the equivalent of git diff branch A branch B through the web interface? -- 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] New VACUUM FULL
Here is an updated patch of rewriting vacuum based on vacuum options patch. Documentations and vacuumdb modification (-i, --inplace) are added. Jeff Davis pg...@j-davis.com wrote: 1. Do we want to introduce syntax for INPLACE at all, if we are eventually going to remove the current mechanism? My opinion is that if we really still need the current in-place mechanism, then VACUUM (FULL) should use the current in-place mechanism; and VACUUM (FULL REWRITE) should use your new rewrite mechanism. AFAIK, VACUUM FULL should behave as REWRITE in the past discussion. Since we don't want users to use in-place FULL vacuum, so we will change the default behavior of VACUUM FULL. There are some choices: REWRITE versionin-place version 1. VACUUM (FULL REPLACE) vs. VACUUM (FULL INPLACE) 2. VACUUM (FULL) vs. VACUUM (FULL INPLACE) 3. VACUUM (REWRITE) vs. VACUUM (FULL) 4. VACUUM (FULL REWRITE) vs. VACUUM (FULL) 5. Don't use SQL and use a GUC instead. (bool inplace_vacuum_full ?) I choose a hybrid syntax of 1 + 2 in the patch, but I'm not particular about it. What is the best? 2. Why do all of the following exist: VACOPT_FULL, VACOPT_REPLACE, and VACOPT_INPLACE? Shouldn't VACOPT_FULL be equivalent to one of the other two? This is essentially what Simon was getting at, I think. * FULL [REPLACE] := VACOPT_FULL * FULL INPLACE := VACOPT_FULL + VACOPT_INPLACE 3. Some options are being set in vacuum() itself. It looks like the options should already be set in gram.y, so should that be an Assert instead? I think it's cleaner to set all of the options properly early on, rather than waiting until vacuum() to interpret the combinations. I moved all of the logic into gram.y. vacuum() has only assert tests. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center vacuum-full_20091116.patch 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] Patch committers
On Mon, Nov 16, 2009 at 10:20, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 02:08, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) yes, easily. Just pass it the commit id. And unlike cvs, there is one diff for the patch, not one for every file ;) For example: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d3359020ef7e0fba02ac552d88ede0c3ce5128cc turning it into context-diff style will require patching gitweb though, it doesn't do that by default. Any idea how the get the equivalent of git diff branch A branch B through the web interface? I don't think you can - but it's probably not a huge thing to implement it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] patch - per-tablespace random_page_cost/seq_page_cost
--On 14. November 2009 20:22:42 -0500 Robert Haas robertmh...@gmail.com wrote: I will take another crack at it. ...Robert I take this that you are going to provide a new patch version? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
- Does Hot Standby allow to use prepared query (not prepared transaction) in standby? I mean: Parse message from frontend can be accepted by standby? Yes, no problem with any of those kind of facilities Please correct me if I'm wrong. Parse will result in obtaining RowExclusiveLock on the target table if it is parsing INSERT/UPDATE/DELETE. If so, is this ok in the standby? - Can we create tempory tables in standby? No, but this is for two reasons * CREATE TEMPORARY TABLE actually writes to catalog tables. It doesn't need to do that, so allowing this would require some medium-heavy lifting of the way temp tables work. A preliminary design was agreed in July 2008. I believe it would be a popular feature, since about 40-50% of people ask for this. * CREATE TEMP TABLE is currently considered to be disallowed during read only transactions. That might be able to change if the underlying physical operation were write-free. Thanks for explanation. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRIGGER with WHEN clause
KaiGai Kohei wrote: I'm uncertain how Oracle handles the condition on the statement triggers. But it seems to me WHEN clause on the statement triggers are nonsense. SQL CREATE TRIGGER dummy BEFORE DELETE ON employees WHEN (1 = 1) 2 BEGIN 3 END; 4 / CREATE TRIGGER dummy BEFORE DELETE ON employees WHEN (1 = 1) * ERROR at line 1: ORA-04077: WHEN clause cannot be used with table level triggers Yours, Laurenz Albe -- 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] TRIGGER with WHEN clause
Albe Laurenz wrote: SQL CREATE TRIGGER dummy BEFORE DELETE ON employees WHEN (1 = 1) 2 BEGIN 3 END; 4 / CREATE TRIGGER dummy BEFORE DELETE ON employees WHEN (1 = 1) * ERROR at line 1: ORA-04077: WHEN clause cannot be used with table level triggers Thanks for your information. I am also not sure about Oracle, but I think there are usage of statement trigger with WHEN cluase something like: =# CREATE TRIGGER log_trig BEFORE UPDATE ON tbl WHEN (is_superuser()) EXECUTE PROCEDURE log_current_stmt(); Itagaki-san, I also think your example usage is enough valueable. However, Oracle does not have the feature apparently, although the purpose of this patch is to provide a compatible feature, IIRC. I don't have any preference on either of them. If you make a decision, I'll review the patch according to your decision. So, I like to ask you which is your preference again. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] named parameters in SQL functions
On sön, 2009-11-15 at 12:37 -0500, Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? Yes, search the SQL standard for SQL parameter reference. -- 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] ORDER BY vs. volatile functions
Andrew Gierth wrote: This query: select random() from generate_series(1,10) order by random(); produces sorted output. Should it? I recall a workaround from a different thread[1] if specifically were looking for random ordering of random numbers is: select random() from foo order by random()+1; The thread has more odd corner cases with multiple calls to random() and sorts as well. [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Academic research on programmers' motivation
Dear PostgreSQL developers, We are researching the motivation factors of Open Source software programmers and would like to ask your cooperation in our large-scale research. The research is performed for the Amsterdam Business School of the University of Amsterdam. Your participation would consist of completing an online survey to which we have linked below. Your participation is completely anonymous and the research publication will be freely available, including the results of the survey. The goal of the research is to better understand the motivations software programmers have for joining and contributing to an Open Source project. The insights gained from this research would help the development of theory for Information Management and could help practitioners better understand Open Source projects. Next to this, the publication of this research will increase the exposure of the development of Open Source software within the academic environment. Please follow the link to the online survey. Our test audience has shown that completing the survey will take about 15 minutes. Your time is highly appreciated by us. http://bit.ly/Survey_Developers_Motivation Kind regards, Dr. Thomas Adelaar Mark Pith PS If you reply to this message, please include mark.p...@student.uva.nl as CC for a faster response. -This independent research is not related to the PostgreSQL project.- -- 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] Listen / Notify - what to do when the queue is full
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We still need to decide what to do with queue full situations in the proposed listen/notify implementation. I have a new version of the patch to allow for a variable payload size. However, the whole notification must fit into one page so the payload needs to be less than 8K. That sounds fine to me, FWIW. I have also added the XID, so that we can write to the queue before committing to clog which allows for rollback if we encounter write errors (disk full for example). Especially the implications of this change make the patch a lot more complicated. Can you elaborate on the use case for this? so it won't update its pointer for some time. With the current space we can acommodate at least 2147483647 notifications or more, depending on the payload length. That's a whole lot of notifications. I doubt any program out there is using anywhere near that number at the moment. In my applications, having a few hundred notifications active at one time is a lot in my book. :) These are the solutions that I currently see: 1) drop new notifications if the queue is full (silently or with rollback) I like this one best, but not with silence of course. While it's not the most polite thing to do, this is for a super extreme edge case. I'd rather just throw an exception if the queue is full rather than start messing with the readers. It's a possible denial of service attack too, but so is the current implementation in a way - at least I don't think apps would perform very optimally with 2147483647 entries in the pg_listener table :) If you need some real-world use cases involving payloads, let me know, I've been waiting for this feature for some time and have it all mapped out. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 200911160902 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksBXC0ACgkQvJuQZxSWSsh5XQCg2qPh+MovjPAdbxTmlOGu51HF 6OYAn0f+tt6lXJhVKoAAmh1QlWfRC4kl =Izb1 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify rewrite
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 You misunderstand the requirements. LISTEN notifications are *not* meant to survive a database crash, and never have been. However, so long as both client and server stay up, they must be reliable. If the client has to poll database state because it might have missed a notification, the feature is just a waste of time. Why would it be so important for messages to be reliable if the database is up, yet its OK to lose messages if it crashes? The application must still allow for the case that messages are lost. Well, there are many use cases. For example, Bucardo uses notifications to let it know that a table has changed. If the database crashes, Bucardo is going to restart - as part of its startup routine, it checks all tables manually for changes, eliminating the need for the NOTIFYs to survive the crash. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200911160910 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksBXkAACgkQvJuQZxSWSsjEWACePcT+65HQ0dvx52PjjTkdMzVS ELMAnAhR3Ll016/EwPdizzS5BcsuXaw9 =jds6 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Mon, Nov 16, 2009 at 9:05 AM, Greg Sabino Mullane We still need to decide what to do with queue full situations in the proposed listen/notify implementation. I have a new version of the patch to allow for a variable payload size. However, the whole notification must fit into one page so the payload needs to be less than 8K. That sounds fine to me, FWIW. +1! I think this should satisfy everyone. I have also added the XID, so that we can write to the queue before committing to clog which allows for rollback if we encounter write errors (disk full for example). Especially the implications of this change make the patch a lot more complicated. Can you elaborate on the use case for this? Tom specifically asked for it: The old implementation was acid so the new one should be to so it won't update its pointer for some time. With the current space we can acommodate at least 2147483647 notifications or more, depending on the payload length. That's a whole lot of notifications. I doubt any program out there is using anywhere near that number at the moment. In my applications, having a few hundred notifications active at one time is a lot in my book. :) These are the solutions that I currently see: 1) drop new notifications if the queue is full (silently or with rollback) I like this one best, but not with silence of course. While it's not the most polite thing to do, this is for a super extreme edge case. I'd rather just throw an exception if the queue is full rather than start messing with the readers. It's a possible denial of service attack too, but so is the current implementation in a way - at least I don't think apps would perform very optimally with 2147483647 entries in the pg_listener table :) If you need some real-world use cases involving payloads, let me know, I've been waiting for this feature for some time and have it all mapped out. me too. Joachim: when I benchmarked the original patch, I was seeing a few log messages that suggested there might be something going inside. In any event, the performance was fantastic. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - per-tablespace random_page_cost/seq_page_cost
On Mon, Nov 16, 2009 at 4:37 AM, Bernd Helmle maili...@oopsware.de wrote: --On 14. November 2009 20:22:42 -0500 Robert Haas robertmh...@gmail.com wrote: I will take another crack at it. ...Robert I take this that you are going to provide a new patch version? Yes. I'm not sure whether or not it will be in time for this CF, however. ...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] Patch committers
On Mon, Nov 16, 2009 at 4:30 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Nov 16, 2009 at 10:20, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 02:08, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Sat, Nov 14, 2009 at 13:35, Robert Haas robertmh...@gmail.com wrote: On Sat, Nov 14, 2009 at 4:11 AM, Magnus Hagander mag...@hagander.net wrote: How about we add specific feature(s) about tihs to the commitfest management tool? Like the possibility to directly link a git repo/branch with the patch? So two fields, one for the repo URL and one for the branch name? Yeah, I think that's it. It might actually be interesting to pull the latest version date and make a note in the cf management stuff automagically in case there the git repo has a more updated version than the one that was submitted. I think that could be quite useful - shouldn't be too hard to do, I think. Probably just a cron job that updates a third col in the db? Can you get git to dynamically generate a tree diff via a URL? That would be nice. Extra points for a context diff. ;-) yes, easily. Just pass it the commit id. And unlike cvs, there is one diff for the patch, not one for every file ;) For example: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d3359020ef7e0fba02ac552d88ede0c3ce5128cc turning it into context-diff style will require patching gitweb though, it doesn't do that by default. Any idea how the get the equivalent of git diff branch A branch B through the web interface? I don't think you can - but it's probably not a huge thing to implement it. I think git log branch A...branch B would also be really useful. If you update your patches by merging rather than rebasing, the existing gitweb view is nearly useless. I'm astonished this hasn't bothered any of the kernel developers enough for them to fix it. But then maybe they use the same workaround I do: the command-line. ...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] Listen / Notify - what to do when the queue is full
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 We still need to decide what to do with queue full situations in the proposed listen/notify implementation. I have a new version of the patch to allow for a variable payload size. However, the whole notification must fit into one page so the payload needs to be less than 8K. That sounds fine to me, FWIW. Agreed. Thank you for all your work. 1) drop new notifications if the queue is full (silently or with rollback) I like this one best, but not with silence of course. While it's not the most polite thing to do, this is for a super extreme edge case. I'd rather just throw an exception if the queue is full rather than start messing with the +1 -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] different result between 8.3 and 8.5 (to_timestamp function)
Hello
our customer reports different result of to_timestamp function between
8.3 and 8.4
It looks like to_timestamp returns some strange timezone value
postgres=# select to_timestamp('00:00:00','HH24:MI:SS');
to_timestamp
─
0001-01-01 00:00:00+00:57:44 BC
(1 row)
postgres=# select to_timestamp('1.2.2009 20:10:10','DD.MM. HH24:MI:SS');
to_timestamp
2009-02-01 20:10:10+01
(1 row)
8.3 returns +01
it
we missing 2 minutes, 15 second
any explanation?
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ORDER BY vs. volatile functions
Andrew Gierth and...@tao11.riddles.org.uk writes: For bonus weirdness: select distinct random(),random() from generate_series(1,10); set enable_hashagg=off; select distinct random(),random() from generate_series(1,10); I think _that_ one is a bug. Hmm. I think the first one is a bug --- the two invocations of random() in the tlist shouldn't be folded together. 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] [COMMITTERS] pgsql: /home/peter/commit-msg
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 08:29, David Fetter da...@fetter.org wrote: On Mon, Nov 16, 2009 at 06:56:54AM +0200, Peter Eisentraut wrote: Yeah, sorry guys. I fixed the CVS log message now. So it's not only not strange, I'm very happy it didn't pull those changes and broke my repository :-) Yeah, I'm glad it didn't do anything funny with the mirror. I think we should have a policy of NO manual changes to the CVS repository files. At least not without careful discussion beforehand. The lack of a commit message for this one small patch was absolutely not worth taking any risks to fix. 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] different result between 8.3 and 8.5 (to_timestamp function)
Pavel Stehule pavel.steh...@gmail.com writes: It looks like to_timestamp returns some strange timezone value What timezone setting are you using? I'd bet a great deal that +00:57:44 is what the Olsen database shows as the LMT offset for your zone before standardized timezones were adopted. 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] different result between 8.3 and 8.5 (to_timestamp function)
2009/11/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: It looks like to_timestamp returns some strange timezone value What timezone setting are you using? I'd bet a great deal that +00:57:44 is what the Olsen database shows as the LMT offset for your zone before standardized timezones were adopted. postgres=# select current_timestamp; now ─── 2009-11-16 16:32:33.225416+01 (1 row) I am using CET. Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] different result between 8.3 and 8.5 (to_timestamp function)
Pavel Stehule pavel.steh...@gmail.com writes: 2009/11/16 Tom Lane t...@sss.pgh.pa.us: What timezone setting are you using? Â I'd bet a great deal that +00:57:44 is what the Olsen database shows as the LMT offset for your zone before standardized timezones were adopted. I am using CET. CET covers a multitude of sins, but I suspect it's specifically Europe/Prague: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Prague 0:57:44 - LMT 1850 0:57:44 - PMT 1891 Oct # Prague Mean Time 1:00C-Eur CE%sT 1944 Sep 17 2:00s 1:00Czech CE%sT 1979 1:00EU CE%sT So midnight local time corresponds to 00:57:44 GMT before 1891. If you think this is wrong, use another zone, or take it up with the zic folk. 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] different result between 8.3 and 8.5 (to_timestamp function)
2009/11/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2009/11/16 Tom Lane t...@sss.pgh.pa.us: What timezone setting are you using? I'd bet a great deal that +00:57:44 is what the Olsen database shows as the LMT offset for your zone before standardized timezones were adopted. I am using CET. CET covers a multitude of sins, but I suspect it's specifically Europe/Prague: # Zone NAME GMTOFF RULES FORMAT [UNTIL] Zone Europe/Prague 0:57:44 - LMT 1850 0:57:44 - PMT 1891 Oct # Prague Mean Time 1:00 C-Eur CE%sT 1944 Sep 17 2:00s 1:00 Czech CE%sT 1979 1:00 EU CE%sT So midnight local time corresponds to 00:57:44 GMT before 1891. If you think this is wrong, use another zone, or take it up with the zic folk. ok - I understand. Thank you Pavel regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Summary and Plan for Hot Standby
Tom Lane wrote: I agree with Heikki that it would be better not to commit as long as any clear showstoppers remain unresolved. I agree that it would be better not to commit as long as any of the following are true: (1) There are any known issues which would break things for clusters *not using* hot standby. (2) There isn't an easy way for to disable configuration of hot standby. (3) There is significant doubt that the vast majority of the patch will be useful in the eventually-enabled final solution. If none of these are true, I'm not sure what the down side of a commit is. -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] Summary and Plan for Hot Standby
On Mon, Nov 16, 2009 at 11:07 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane wrote: I agree with Heikki that it would be better not to commit as long as any clear showstoppers remain unresolved. I agree that it would be better not to commit as long as any of the following are true: (1) There are any known issues which would break things for clusters *not using* hot standby. (2) There isn't an easy way for to disable configuration of hot standby. (3) There is significant doubt that the vast majority of the patch will be useful in the eventually-enabled final solution. If none of these are true, I'm not sure what the down side of a commit is. Well, I think you wouldn't want to commit something that enabled Hot Standby but caused Hot Standby queries to give wrong answers, or didn't even allow some/all queries to be executed. That's fairly pointless, and might mislead users into thinking we had a feature when we really didn't. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] What is the correct way to extract values from an int8 array in SPI?
Boszormenyi Zoltan wrote:
Hi,
I am using this code on 8.4/8.5, which works on 64-bit,
but segfaults on 32-bit Linux:
I'm not sure but perhaps this patch could help you. It may be a bit outdated.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: doc/src/sgml/spi.sgml
===
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.65
diff -c -p -r1.65 spi.sgml
*** doc/src/sgml/spi.sgml 5 Aug 2009 19:31:50 - 1.65
--- doc/src/sgml/spi.sgml 9 Oct 2009 20:16:58 -
*** char * SPI_getnspname(Relation paramete
*** 2969,2974
--- 2969,2975
allocations made by functionpalloc/function,
functionrepalloc/function, or SPI utility functions (except for
functionSPI_copytuple/function,
+functionSPI_copydatum/function,
functionSPI_returntuple/function,
functionSPI_modifytuple/function, and
functionSPI_palloc/function) are made in this context. When a
*** HeapTuple SPI_copytuple(HeapTuple param
*** 3221,3226
--- 3222,3301
!-- *** --
+ refentry id=spi-spi-copydatum
+ refmeta
+ refentrytitleSPI_copydatum/refentrytitle
+ manvolnum3/manvolnum
+ /refmeta
+
+ refnamediv
+ refnameSPI_copydatum/refname
+ refpurposemake a copy of a datum in the upper executor context/refpurpose
+ /refnamediv
+
+ indextermprimarySPI_copydatum/primary/indexterm
+
+ refsynopsisdiv
+ synopsis
+ Datum SPI_copydatum(Datum parametervalue/parameter, bool parametertypByVal/parameter, int parametertypLen/parameter)
+ /synopsis
+ /refsynopsisdiv
+
+ refsect1
+ titleDescription/title
+
+ para
+functionSPI_copydatum/function makes a copy of a datum in the
+upper executor context.
+ /para
+ /refsect1
+
+ refsect1
+ titleArguments/title
+
+ variablelist
+varlistentry
+ termliteralDatum parametervalue/parameter/literal/term
+ listitem
+ para
+ datum to be copied
+ /para
+ /listitem
+/varlistentry
+
+varlistentry
+ termliteralbool parametertypByVal/parameter/literal/term
+ listitem
+ para
+ whether the type of the datum is passed by value
+ /para
+ /listitem
+/varlistentry
+
+varlistentry
+ termliteralint parametertypLen/parameter/literal/term
+ listitem
+ para
+ length of the type
+ /para
+ /listitem
+/varlistentry
+
+ /variablelist
+ /refsect1
+
+ refsect1
+ titleReturn Value/title
+
+ para
+the copied datum; symbolNULL/symbol only if
+parametervalue/parameter is symbolNULL/symbol
+ /para
+ /refsect1
+ /refentry
+
+ !-- *** --
+
refentry id=spi-spi-returntuple
refmeta
refentrytitleSPI_returntuple/refentrytitle
Index: src/backend/executor/spi.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/executor/spi.c,v
retrieving revision 1.209
diff -c -p -r1.209 spi.c
*** src/backend/executor/spi.c 2 Oct 2009 17:57:30 - 1.209
--- src/backend/executor/spi.c 9 Oct 2009 20:35:03 -
*** SPI_copytuple(HeapTuple tuple)
*** 615,620
--- 615,635
return ctuple;
}
+ Datum
+ SPI_copydatum(Datum value, bool typByVal, int typLen)
+ {
+ Size len;
+ void *tmp;
+ Datum retval;
+
+ len = datumGetSize(value, typByVal, typLen);
+ tmp = SPI_palloc(len);
+ memcpy(tmp, DatumGetPointer(value), len);
+ retval = PointerGetDatum(tmp);
+
+ return retval;
+ }
+
HeapTupleHeader
SPI_returntuple(HeapTuple tuple, TupleDesc tupdesc)
{
Index: src/include/executor/spi.h
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/include/executor/spi.h,v
retrieving revision 1.72
diff -c -p -r1.72 spi.h
*** src/include/executor/spi.h 11 Jun 2009 14:49:11 - 1.72
--- src/include/executor/spi.h 9 Oct 2009 20:00:19 -
*** extern bool SPI_plan_is_valid(SPIPlanPtr
*** 98,103
--- 98,104
extern const char *SPI_result_code_string(int code);
extern HeapTuple SPI_copytuple(HeapTuple tuple);
+ extern Datum SPI_copydatum(Datum value, bool typByVal, int typLen);
extern HeapTupleHeader SPI_returntuple(HeapTuple tuple, TupleDesc tupdesc);
extern HeapTuple SPI_modifytuple(Relation rel, HeapTuple tuple, int natts,
int *attnum, Datum *Values, const char *Nulls);
Index: src/pl/plpgsql/src/pl_exec.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.248
diff -c -p -r1.248 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 6 Aug 2009 20:44:31 - 1.248
--- src/pl/plpgsql/src/pl_exec.c 9 Oct 2009 20:36:01 -
***
Re: [HACKERS] next CommitFest
and...@dunslane.net (Andrew Dunstan) writes: Robert Haas wrote: I am personally quite tired of reviewing patches for people who don't in turn review mine (or someone's). It makes me feel like not working on this project. If we can solve that problem without implementing a policy of this type, that is good. I would much prefer to run by the honor system rather than having to threaten to drop patches, but only if the honor system actually works. Organizing contributors on a project like this is like herding cats. Threats and penalties are unlikely to be effective. This is essentially a charity where people give in ways that work for them, and you take whatever they have to give. I'm extremely uncomfortable with the idea of a prescriptive system. I've proposed them myself in the past, but I have since come to the realization that it will simply drive people away. Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. -- I really only meant to point out how nice InterOp was for someone who doesn't have the weight of the Pentagon behind him. I really don't imagine that the Air Force will ever be able to operate like a small, competitive enterprise like GM or IBM. -- Kent England -- 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] [COMMITTERS] pgsql: /home/peter/commit-msg
On mån, 2009-11-16 at 10:05 -0500, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Magnus Hagander wrote: On Mon, Nov 16, 2009 at 08:29, David Fetter da...@fetter.org wrote: On Mon, Nov 16, 2009 at 06:56:54AM +0200, Peter Eisentraut wrote: Yeah, sorry guys. I fixed the CVS log message now. So it's not only not strange, I'm very happy it didn't pull those changes and broke my repository :-) Yeah, I'm glad it didn't do anything funny with the mirror. I think we should have a policy of NO manual changes to the CVS repository files. At least not without careful discussion beforehand. I used cvs admin. -- 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] Summary and Plan for Hot Standby
On Mon, 2009-11-16 at 19:06 +0900, Tatsuo Ishii wrote: - Does Hot Standby allow to use prepared query (not prepared transaction) in standby? I mean: Parse message from frontend can be accepted by standby? Yes, no problem with any of those kind of facilities Please correct me if I'm wrong. Parse will result in obtaining RowExclusiveLock on the target table if it is parsing INSERT/UPDATE/DELETE. If so, is this ok in the standby? Any attempt to take RowExclusiveLock will fail. Any attempt to execute INSERT/UPDATE/DELETE will fail. This behaviour should be identical to read only transaction mode. If it is not documented as an exception, please report as a bug. -- Simon Riggs www.2ndQuadrant.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] next CommitFest
On Mon, 2009-11-16 at 11:31 -0500, Chris Browne wrote: Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. It is important to remember we are all volunteers here. Any increase to the barrier of contribution is a bad one. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] next CommitFest
On Mon, Nov 16, 2009 at 12:17 PM, Joshua D. Drake j...@commandprompt.com wrote: On Mon, 2009-11-16 at 11:31 -0500, Chris Browne wrote: Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. It is important to remember we are all volunteers here. Any increase to the barrier of contribution is a bad one. True. But not enough reviewers to review all the patches we get is also a barrier to contribution. ...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] ORDER BY vs. volatile functions
Tom == Tom Lane t...@sss.pgh.pa.us writes: For bonus weirdness: select distinct random(),random() from generate_series(1,10); set enable_hashagg=off; select distinct random(),random() from generate_series(1,10); I think _that_ one is a bug. Tom Hmm. I think the first one is a bug --- the two invocations of Tom random() in the tlist shouldn't be folded together. That's what I meant. If you try it using nextval(), you'll notice that the function does in fact get called twice per row, but one of the results is thrown away and replaced with the other one. -- Andrew. -- 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] ORDER BY vs. volatile functions
Andrew Gierth and...@tao11.riddles.org.uk writes: If you try it using nextval(), you'll notice that the function does in fact get called twice per row, but one of the results is thrown away and replaced with the other one. Yeah. The problem is that setrefs.c is generating a tlist for the hashagg node in which both output expressions point to the first output of the underlying scan node, because it's just relying on equal() to match up the expressions. I'm testing a fix now ... 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] ALTER TABLE...ALTER COLUMN vs inheritance
On Thu, Nov 12, 2009 at 11:56, Bernd Helmle maili...@oopsware.de wrote: I've just started looking into this and wonder how this should look like. IIRC another motivation for moving them into pg_constraint was we could then give them names as required by the spec (unless I got mixed up with defaults). Looking at the 2003 spec I don't see any grammar for that, so either I cant find it (likely) or its not there. Either way I see something like the below options: ALTER TABLE ALTER COLUMN ADD CONSTRAINT my_not_null NOT NULL; [ we dont currently support add constraint on ALTER COLUMN AFAICT... but it might be nice? ] -or- ALTER TABLE ADD CONSTRAINT my_not_null NOT NULL (column); -or- ALTER TABLE ALTER COLUMN column SET NOT NULL 'name'; Comments? Anyway Bernd if you are working on this great! If not lemme know, Ill plan on having something for the next commit feast. Though I still may never get around to it :(. FYI defaults have the same problem. Would it be awkward would it be to use pg_constraint for the book keeping as well? [ and by that I really mean ALTER TABLE ADD CONSTRAINT my_default DEFAULT so you can give them a name ] -- 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] next CommitFest
j...@commandprompt.com (Joshua D. Drake) writes: On Mon, 2009-11-16 at 11:31 -0500, Chris Browne wrote: Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. It is important to remember we are all volunteers here. Any increase to the barrier of contribution is a bad one. But this *isn't* a barrier to contribution, at least not notably more than the already existant issue that a paucity of reviewers is a barrier to contribution. It represents a policy for triaging review efforts with a bias in favor of those that *are* contributing to the reviewers' list. I don't think it's unjust for those that contribute to the review process to get more favorable scheduling of reviews to their patches. If we get so many reviewers that such triaging becomes unnecessary, then it may automatically *not* be a problem. -- (format nil ~...@~s cbbrowne acm.org) http://linuxfinances.info/info/slony.html Bother, said Pooh, as he deleted his root directory. -- 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] next CommitFest
On Mon, 2009-11-16 at 12:42 -0500, Robert Haas wrote: On Mon, Nov 16, 2009 at 12:17 PM, Joshua D. Drake j...@commandprompt.com wrote: On Mon, 2009-11-16 at 11:31 -0500, Chris Browne wrote: Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. It is important to remember we are all volunteers here. Any increase to the barrier of contribution is a bad one. True. But not enough reviewers to review all the patches we get is also a barrier to contribution. No. It is a barrier of contribution not to contribution. The types of current structure that are being considered are punitive regardless of the softness of wording. This is certainly not an easy problem to solve and I am not saying I have a better solution (although something more personal and direct such as the way Selena helps user groups seems more appropriate). Sincerely, Joshua D. Drake ...Robert -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] write ahead logging in standby (streaming replication)
Hi, Greg Stark wrote: I think my definition would be that a query against the replica will produce the same result as a query against the master -- and that that will be the case even after a system failure. That might not necessarily mean that the log entry is fsynced on the replica, only that it's fsynced in a location where the replica will have access to it when it runs recovery. I tend to agree with that definition of synchrony for replicated databases. However, let me point to an earlier thread around the same topic: http://archives.postgresql.org/message-id/4942ecf7.5040...@bluegap.ch You will definitely find different definitions and requirements of what synchronous replication means there. It convinced me that synchronous is more of a marketing term in this area and is better avoided in technical documents and discussions, or needs explanation. As far as marketing goes, there are the customers who absolutely want synchronous replication for its consistency and then there are the others who absolutely don't want it due to its unusably high latency. Regards Markus Wanner -- 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] next CommitFest
On Mon, Nov 16, 2009 at 1:08 PM, Joshua D. Drake j...@commandprompt.com wrote: True. But not enough reviewers to review all the patches we get is also a barrier to contribution. No. It is a barrier of contribution not to contribution. I am not sure exactly what that means, but I agree that it isn't quite the same. Backing up a minute, AIUI, the CommitFest process was created to solve the problem that patches weren't getting reviewed in a timely fashion. To address that problem, dedicated times were created in each release cycle for people to stop working on their own patches and review patches from other contributors. I haven't been around long enough to be able to compare from personal experience, but I think generally what I've heard is that the new process is a big improvement. But, there are some problems, and speaking from experience, one of those problems is that reviewing patches and running CommitFests is long, hard, and difficult when not enough people volunteer to review, or not enough committers volunteer to commit. I guess I agree with your statement that the structures that are being proposed are punitive, although perhaps I might choose the word coercive instead. Clearly, the preferable solution is for people to volunteer. But if they don't, we haven't got a lot of options. Perhaps by encouraging them to volunteer and recognizing their contributions when they do volunteer, we can get the number of volunteers back up to an adequate level. If after doing those things we still don't have enough volunteers, we're not going to be able to review all the patches. Should that occur, we'll have to decide which ones to review and which ones to skip. Maybe we'll just let people volunteer and any patches for which nobody volunteers will fall on the floor or be forever postponed to the next CommitFest. Maybe we'll try to assign reviewers preferentially to first-time contributors and those who are themselves reviewing, as I'm suggesting. Or maybe we'll handle it some other way. I don't know. It seems we don't have to decide yet. ...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] ORDER BY vs. volatile functions
Ron Mayer rm...@cheapcomplexdevices.com writes: [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php FWIW, the behavior has changed from the time of that discussion --- we now track sort ordering using EquivalenceClasses, which are able to distinguish different instances of textually equal() volatile expressions. The particular cases of select random() from foo order by 1; select random() from foo order by random(); still behave the same, but that's intentional for backwards compatibility (and because SQL99 forbids the first, which would mean there's no way to get the behavior except via deprecated syntax). Beyond the case of order by/group by items being matched to tlist items, I'd generally expect that the system should act as though different textual instances of random() are evaluated separately. 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] ALTER TABLE...ALTER COLUMN vs inheritance
Alex Hunsaker bada...@gmail.com writes: FYI defaults have the same problem. Would it be awkward would it be to use pg_constraint for the book keeping as well? [ and by that I really mean ALTER TABLE ADD CONSTRAINT my_default DEFAULT so you can give them a name ] That sounds moderately insane to me. Why would you need a name? What would it mean to have more than one default attached to a column? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Update on Insert
Hi Everyone, Given a table Items with a PK item1 and Qty - a numeric column I'd like to define a way in Postgres to insert when item11 doesn't exist already in Items and update the Qty by adding the new quantity to the existent when the item11 exists. What is a good approach and where should I be looking for details. Thanks. Sebastian -- 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] Unpredictable shark slowdown after migrating to 8.4
On Thu, Nov 12, 2009 at 4:42 AM, Robert Haas robertmh...@gmail.com wrote:
On Wed, Nov 11, 2009 at 12:50 PM, Sergey Konoplev gray...@gmail.com wrote:
Was this situation mentioned before and is there a solution or
workaround? (I didn't find any) If not please give me a glue where to
dig or what information should I provide?
I think you should use log_min_duration_statement or auto_explain to
figure out which queries are giving you grief. I don't believe that
8.4 is in general slower than 8.3, so there must be something about
how you are using it that is making it slower for you. But without
more information it's hard to speculate what it might be. It's also
not entirely clear that your 8.4 setup is really the same as your 8.3
setup. You might have different configuration, differences in your
tables or table data, differences in workload, etc. Without
controlling for all those factors it's hard to draw any conclusions.
Well I turned on log_min_duration_statement, set up auto_explain,
pg_stat_statements, pgrowlocks, pg_buffercache, wrote some monitoring
queries and started to wait when the situation repeats.
Today it happens! Situation was absolutely the same as I described in
my previous letter. One more thing I noticed about CPU user-time this
time is that after connections count gets close to pgbouncer threshold
it decreased from ~800 to ~10 very fast.
Log monitoring shows me that query plans were the same as usual (thanx
auto_explain).
I reset pg_stat_statements and few minutes later did select from it. I
noticed that slowest queries was on tables with high number of updates
(but isn't it usual?).
I tried to get locks with this queries
SELECT
t.tablename,
(SELECT count(*) FROM pgrowlocks(schemaname || '.' || t.tablename)) AS locks
FROM pg_tables t
WHERE schemaname = 'public'
ORDER BY 2 DESC
LIMIT 10;
SELECT * FROM pgrowlocks('public.person_online');
but nothing was returned.
Here is portions of vmstat and iostat results http://pastie.org/701326
This time situation was saved by PG restart to. Obviously all I
provided tells almost nothing and I'm very confused with it. So please
tell me what else could I do to get more info?
Also, I don't believe this is an appropriate topic for pgsql-hackers.
If you have EXPLAIN ANALYZE results for the affected queries, try
pgsql-performance.
I do have but this results are good and the same as when nothing has
happened when everything is allright.
--
Regards,
Sergey Konoplev
--
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] Unpredictable shark slowdown after migrating to 8.4
Sergey Konoplev escribió: I tried to get locks with this queries Did you try pg_locks? -- 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] Unpredictable shark slowdown after migrating to 8.4
On Mon, Nov 16, 2009 at 1:53 PM, Sergey Konoplev gray...@gmail.com wrote:
On Thu, Nov 12, 2009 at 4:42 AM, Robert Haas robertmh...@gmail.com wrote:
On Wed, Nov 11, 2009 at 12:50 PM, Sergey Konoplev gray...@gmail.com wrote:
Was this situation mentioned before and is there a solution or
workaround? (I didn't find any) If not please give me a glue where to
dig or what information should I provide?
I think you should use log_min_duration_statement or auto_explain to
figure out which queries are giving you grief. I don't believe that
8.4 is in general slower than 8.3, so there must be something about
how you are using it that is making it slower for you. But without
more information it's hard to speculate what it might be. It's also
not entirely clear that your 8.4 setup is really the same as your 8.3
setup. You might have different configuration, differences in your
tables or table data, differences in workload, etc. Without
controlling for all those factors it's hard to draw any conclusions.
Well I turned on log_min_duration_statement, set up auto_explain,
pg_stat_statements, pgrowlocks, pg_buffercache, wrote some monitoring
queries and started to wait when the situation repeats.
Today it happens! Situation was absolutely the same as I described in
my previous letter. One more thing I noticed about CPU user-time this
time is that after connections count gets close to pgbouncer threshold
it decreased from ~800 to ~10 very fast.
Log monitoring shows me that query plans were the same as usual (thanx
auto_explain).
I reset pg_stat_statements and few minutes later did select from it. I
noticed that slowest queries was on tables with high number of updates
(but isn't it usual?).
I tried to get locks with this queries
SELECT
t.tablename,
(SELECT count(*) FROM pgrowlocks(schemaname || '.' || t.tablename)) AS
locks
FROM pg_tables t
WHERE schemaname = 'public'
ORDER BY 2 DESC
LIMIT 10;
SELECT * FROM pgrowlocks('public.person_online');
but nothing was returned.
Here is portions of vmstat and iostat results http://pastie.org/701326
This time situation was saved by PG restart to. Obviously all I
provided tells almost nothing and I'm very confused with it. So please
tell me what else could I do to get more info?
Also, I don't believe this is an appropriate topic for pgsql-hackers.
If you have EXPLAIN ANALYZE results for the affected queries, try
pgsql-performance.
I do have but this results are good and the same as when nothing has
happened when everything is allright.
Can you show us the non-commented settings from your postgresql.conf?
Can you show us what the vmstat output looks like when everything is
running normally? It looks like the blocks out are pretty high, but I
don't know how that compares to normal for you.
...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] Update on Insert
On Mon, Nov 16, 2009 at 1:31 PM, SebiF sfe...@gmail.com wrote: Hi Everyone, Given a table Items with a PK item1 and Qty - a numeric column I'd like to define a way in Postgres to insert when item11 doesn't exist already in Items and update the Qty by adding the new quantity to the existent when the item11 exists. What is a good approach and where should I be looking for details. Thanks. Sebastian This is not a -hackers question; please use pgsql-general instead. Also see example 38-2 here: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html ...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] ORDER BY vs. volatile functions
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Ron Mayer rm...@cheapcomplexdevices.com writes: [1] http://archives.postgresql.org/pgsql-general/2006-11/msg01544.php Tom FWIW, the behavior has changed from the time of that discussion --- Tom we now track sort ordering using EquivalenceClasses, which are able Tom to distinguish different instances of textually equal() volatile Tom expressions. The particular cases of Tom select random() from foo order by 1; Tom select random() from foo order by random(); Tom still behave the same, but that's intentional for backwards Tom compatibility (and because SQL99 forbids the first, which would mean Tom there's no way to get the behavior except via deprecated syntax). SQL99 doesn't forbid: select random() as r from foo order by r; or select r from (select random() as r from foo) as s order by r; -- Andrew (irc:RhodiumToad) -- 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] Unpredictable shark slowdown after migrating to 8.4
On Wednesday 11 November 2009 18:50:46 Sergey Konoplev wrote: Hello community, Second time after migration 8.3.7 -- 8.4.1 I was caught by this problem. Migration was 8 days ago. (note, I never seen such situation on 8.3) Is 8.4 configured similarly to 8.3? Andres -- 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] UTF8 with BOM support in psql
On ons, 2009-10-21 at 13:11 +0900, Itagaki Takahiro wrote: Sure. Client encoding is declared in body of a file, but BOM is in head of the file. So, we should always ignore BOM sequence at the file head no matter what client encoding is used. The attached patch replace BOM with while spaces, but it does not change client encoding automatically. I think we can always ignore client encoding at the replacement because SQL command cannot start with BOM sequence. If we don't ignore the sequence, execution of the script must fail with syntax error. OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. -- 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] write ahead logging in standby (streaming replication)
Markus Wanner wrote: You will definitely find different definitions and requirements of what synchronous replication means there. To quote from the Wikipedia entry on Database Replication that Simon pointed to during the earlier discussion, http://en.wikipedia.org/wiki/Database_replication Synchronous replication - guarantees zero data loss by the means of atomic write operation, i.e. write either completes on both sides or not at all. Write is not considered complete until acknowledgement by both local and remote storage. That last part is the critical one: acknowledgement by both local and remote storage is required before you can label something truly synchronous replication. In implementation terms, that means you must have both local and slave fsync calls finish to be considered truly synchronous. That part is not ambiguous at all. There's a definition of the weaker form in there too, which is where the ambiguity is at: Semi-synchronous replication - this usually means that a write is considered complete as soon as local storage acknowledges it and a remote server acknowledges that it has received the write either into memory or to a dedicated log file. I don't consider that really synchronous replication anymore, but as you say it's been strengthened by marketing enough to be a valid industry term at this point. Since it's already gained traction we might use it, as long as it's defined properly and its trade-offs vs. a true synchronous implementation are documented. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Update on Insert
SebiF sfe...@gmail.com wrote: Hi Everyone, Given a table Items with a PK item1 and Qty - a numeric column I'd like to define a way in Postgres to insert when item11 doesn't exist already in Items and update the Qty by adding the new quantity to the existent when the item11 exists. What is a good approach and where should I be looking for details. Thanks. 21:53 akretschmer ??upsert 21:53 rtfm_please For information about upsert 21:53 rtfm_please see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Read that. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net writes: I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. So wouldn't it be better to remove the three bytes, rather than replace with spaces? The latter will certainly confuse clients that think that column 1 means what they think is the first character. A syntax error in the first line of the file should be sufficient to demonstrate the issue. 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] ALTER TABLE...ALTER COLUMN vs inheritance
On Mon, Nov 16, 2009 at 11:45, Tom Lane t...@sss.pgh.pa.us wrote: Alex Hunsaker bada...@gmail.com writes: FYI defaults have the same problem. Would it be awkward would it be to use pg_constraint for the book keeping as well? [ and by that I really mean ALTER TABLE ADD CONSTRAINT my_default DEFAULT so you can give them a name ] That sounds moderately insane to me. Why would you need a name? I don't care strongly enough to argue for them. I just thought if it was something the spec said or someone wanted it would be easy to add while in the area :) Sorry for the insane hand waving. We already have pg_attrdef, all we really need is the inhcount and islocal columns on that. No reason to bring pg_constraint into it all at. What would it mean to have more than one default attached to a column? It would be like so far out dude Ok so my hippie impression needs work... -- 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] New VACUUM FULL
Jeff Davis pg...@j-davis.com writes: On Mon, 2009-11-16 at 13:37 +0900, Itagaki Takahiro wrote: [ new options syntax for VACUUM ] Great, I am marking this part ready for committer. Applied with very minor editorialization. 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] Listen / Notify rewrite
On Sat, Nov 14, 2009 at 11:06 PM, Merlin Moncure mmonc...@gmail.com wrote: The old method (measured on a 4 core high performance server) has severe scaling issues due to table bloat (we knew that): ./pgbench -c 10 -t 1000 -n -b listen.sql -f notify.sql run #1 tps = 1364.948079 (including connections establishing) new method on my dual core workstation (max payload 128): ./pgbench -c 10 -t 1 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 16343.012373 (including connections establishing) That looks fine and is similar to my tests where I also see a performance increase of about 10x, and unlike pg_listener it is constant. getting sporadic 'LOG: could not send data to client: Broken pipe' throughout the test. This looks like the server is trying to send a notification down to the client but the client has already terminated the connection... Joachim -- 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] Listen / Notify rewrite
On Mon, Nov 16, 2009 at 4:41 PM, Joachim Wieland j...@mcknight.de wrote: On Sat, Nov 14, 2009 at 11:06 PM, Merlin Moncure mmonc...@gmail.com wrote: The old method (measured on a 4 core high performance server) has severe scaling issues due to table bloat (we knew that): ./pgbench -c 10 -t 1000 -n -b listen.sql -f notify.sql run #1 tps = 1364.948079 (including connections establishing) new method on my dual core workstation (max payload 128): ./pgbench -c 10 -t 1 -n -b listen.sql -f notify.sql -hlocalhost postgres tps = 16343.012373 (including connections establishing) That looks fine and is similar to my tests where I also see a performance increase of about 10x, and unlike pg_listener it is constant. old method scaled (badly) on volume of notifications and your stuff seems to scale based on # of client's sending simultaneous notifications. Well, you're better all day long, but it shows that your fears regarding locking were not completely unfounded. Do the Burcardo people have any insights on the #of simultaneous notifies are generated from different backends? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] using separate parameters in psql query execution
Hello
now - complete patch
ToDo:
* enhance a documentation (any volunteer?)
* check name for backslash command
Regards
Pavel Stehule
*** ./doc/src/sgml/ref/psql-ref.sgml.orig 2009-10-13 23:04:01.0 +0200
--- ./doc/src/sgml/ref/psql-ref.sgml 2009-11-16 22:44:50.530277356 +0100
***
*** 307,312
--- 307,325
/varlistentry
varlistentry
+ termoption-r//term
+ termoption--separate-params//term
+ listitem
+ para
+ Separately passing parameters. Any used applicationpsql/application
+ variables are passing with query separately (as parameters). This is
+ different than default behave, where variables are substituted by value.
+ /para
+ /listitem
+ /varlistentry
+
+
+ varlistentry
termoption-R replaceable class=parameterseparator/replaceable//term
termoption--record-separator replaceable class=parameterseparator/replaceable//term
listitem
***
*** 1659,1664
--- 1672,1687
/varlistentry
varlistentry
+ termliteral\pexec [ replaceable class=parameteron/replaceable | replaceable class=parameteroff/replaceable ]/literal/term
+ listitem
+ para
+ Changes mode of using applicationpsql/application variables.
+ /para
+ /listitem
+ /varlistentry
+
+
+ varlistentry
termliteral\prompt [ replaceable class=parametertext/replaceable ] replaceable class=parametername/replaceable/literal/term
listitem
para
*** ./src/bin/psql/command.c.orig 2009-11-16 21:39:08.143281729 +0100
--- ./src/bin/psql/command.c 2009-11-16 21:40:12.770279298 +0100
***
*** 1127,1132
--- 1127,1152
free(pattern);
}
+ /* \pexec -- pass parameters separately */
+ else if (strcmp(cmd, pexec) == 0)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (opt)
+ pset.use_parameters = ParseVariableBool(opt);
+ else
+ pset.use_parameters = !pset.use_parameters;
+ if (!pset.quiet)
+ {
+ if (pset.use_parameters)
+ puts(_(Separately passing parameters is on.));
+ else
+ puts(_(Separately passing parameters is off.));
+ }
+ free(opt);
+ }
+
/* \! -- shell escape */
else if (strcmp(cmd, !) == 0)
{
*** ./src/bin/psql/common.c.orig 2009-11-16 21:39:08.146280462 +0100
--- ./src/bin/psql/common.c 2009-11-16 21:40:12.771276920 +0100
***
*** 70,75
--- 70,89
}
void *
+ pg_realloc(void *ptr, size_t size)
+ {
+ void *tmp;
+
+ tmp = realloc(ptr, size);
+ if (!tmp)
+ {
+ psql_error(out of memory\n);
+ exit(EXIT_FAILURE);
+ }
+ return tmp;
+ }
+
+ void *
pg_malloc_zero(size_t size)
{
void *tmp;
***
*** 852,858
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
--- 866,891
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
! if (!pset.use_parameters)
! results = PQexec(pset.db, query);
! else
! {
! /* use PQexecParams function instead */
! results = PQexecParams(pset.db, query,
! pset.nparameters,
! NULL,
! pset.parameters,
! NULL,
! NULL,
! 0);
! if (pset.nparameters)
! {
! pset.nparameters = 0;
! pset.maxparameters = 0;
! free(pset.parameters);
! pset.parameters = NULL;
! }
! }
/* these operations are included in the timing result: */
ResetCancelConn();
***
*** 1005,1011
appendPQExpBuffer(buf, DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s,
query);
! results = PQexec(pset.db, buf.data);
OK = AcceptResult(results)
(PQresultStatus(results) == PGRES_COMMAND_OK);
PQclear(results);
--- 1038,1064
appendPQExpBuffer(buf, DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s,
query);
! if (!pset.use_parameters)
! results = PQexec(pset.db, buf.data);
! else
! {
! /* use PQexecParams function instead */
! results = PQexecParams(pset.db, buf.data,
! pset.nparameters,
! NULL,
! pset.parameters,
! NULL,
! NULL,
! 0);
! if (pset.nparameters)
! {
! pset.nparameters = 0;
! pset.maxparameters = 0;
! free(pset.parameters);
! pset.parameters = NULL;
! }
! }
!
OK = AcceptResult(results)
(PQresultStatus(results) == PGRES_COMMAND_OK);
PQclear(results);
*** ./src/bin/psql/common.h.orig 2009-11-16 21:39:08.148278501 +0100
--- ./src/bin/psql/common.h 2009-11-16 21:40:12.772278454 +0100
***
*** 30,35
--- 30,36
extern void *pg_malloc(size_t size);
extern void *pg_malloc_zero(size_t size);
extern void *pg_calloc(size_t nmemb, size_t size);
+ extern void *pg_realloc(void *ptr, size_t size);
Re: [HACKERS] ALTER TABLE...ALTER COLUMN vs inheritance
--On 16. November 2009 11:00:33 -0700 Alex Hunsaker bada...@gmail.com wrote: Anyway Bernd if you are working on this great! If not lemme know, Ill plan on having something for the next commit feast. Though I still may never get around to it :(. I'm just working on it. The current patch assigns tablename_col_not_null (by using ChooseConstraintName()) as the constraint name to NOT NULL, i record the attnum this NOT NULL belongs to in conkey. So far so good, creating the constraints already works, i'm going to adjust the utility commands now. One thing i just stumpled across: I guess we want the same behavior for dropping NOT NULL constraints recursively like we already do for CHECK constraints. I thought i can reuse some of the infrastructure of ATExecDropConstraint(), but this seems somekind awful, since it requires a constraint name and we already did the scanning of pg_constraint up to this point. Since i don't like duplicating too much code i'm thinking about splitting ATExecDropConstraint() in an additional function ATExecDropConstraintInternal(), which does the real work for a given constraint OID. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl and inline functions -- first draft
On Sun, Nov 15, 2009 at 12:10:33PM +1100, Brendan Jurd wrote: I noticed that there was a fairly large amount of bogus/inconsistent whitespace in the patch, particularly in the body of plperl_inline_handler(). Some of the lines were indented with tabs, others with spaces. You should stick with tabs. There were also a lot of lines with a whole lot of trailing whitespace at the end. Thanks -- I tend to forget whitespace :) In the documentation you refer to this feature as inline functions. I think this might be mixing up the terminology ... although the code refers to inline handlers internally, the word inline doesn't appear in the user-facing documentation for the DO command. Instead they are referred to as anonymous code blocks. I think it would improve consistency if the PL/Perl mention used the same term. I can accept that argument. The attached patch modifies the documentation, and fixes another inconsistency I found. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml index 49631f2..ebcb608 100644 *** a/doc/src/sgml/plperl.sgml --- b/doc/src/sgml/plperl.sgml *** CREATE FUNCTION replaceablefuncname/r *** 59,69 # PL/Perl function body $$ LANGUAGE plperl; /programlisting The body of the function is ordinary Perl code. In fact, the PL/Perl !glue code wraps it inside a Perl subroutine. A PL/Perl function must !always return a scalar value. You can return more complex structures !(arrays, records, and sets) by returning a reference, as discussed below. !Never return a list. /para note --- 59,81 # PL/Perl function body $$ LANGUAGE plperl; /programlisting + +PL/Perl also supports anonymous code blocks called with the +xref linkend=sql-do endterm=sql-do-title +statement: + + programlisting + DO $$ + # PL/Perl function body + $$ LANGUAGE plperl; + /programlisting + The body of the function is ordinary Perl code. In fact, the PL/Perl !glue code wraps it inside a Perl subroutine. Anonymous code blocks cannot !return a value; PL/Perl functions created with CREATE FUNCTION must always !return a scalar value. You can return more complex structures (arrays, !records, and sets) by returning a reference, as discussed below. Never !return a list. /para note diff --git a/src/include/catalog/pg_pltemplate.h b/src/include/catalog/pg_pltemplate.h index 5ef97df..8cdedb4 100644 *** a/src/include/catalog/pg_pltemplate.h --- b/src/include/catalog/pg_pltemplate.h *** typedef FormData_pg_pltemplate *Form_pg_ *** 70,77 DATA(insert ( plpgsql t t plpgsql_call_handler plpgsql_inline_handler plpgsql_validator $libdir/plpgsql _null_ )); DATA(insert ( pltcl t t pltcl_call_handler _null_ _null_ $libdir/pltcl _null_ )); DATA(insert ( pltclu f f pltclu_call_handler _null_ _null_ $libdir/pltcl _null_ )); ! DATA(insert ( plperl t t plperl_call_handler _null_ plperl_validator $libdir/plperl _null_ )); ! DATA(insert ( plperlu f f plperl_call_handler _null_ plperl_validator $libdir/plperl _null_ )); DATA(insert ( plpythonu f f plpython_call_handler _null_ _null_ $libdir/plpython _null_ )); #endif /* PG_PLTEMPLATE_H */ --- 70,77 DATA(insert ( plpgsql t t plpgsql_call_handler plpgsql_inline_handler plpgsql_validator $libdir/plpgsql _null_ )); DATA(insert ( pltcl t t pltcl_call_handler _null_ _null_ $libdir/pltcl _null_ )); DATA(insert ( pltclu f f pltclu_call_handler _null_ _null_ $libdir/pltcl _null_ )); ! DATA(insert ( plperl t t plperl_call_handler plperl_inline_handler plperl_validator $libdir/plperl _null_ )); ! DATA(insert ( plperlu f f plperl_call_handler plperl_inline_handler plperl_validator $libdir/plperl _null_ )); DATA(insert ( plpythonu f f plpython_call_handler _null_ _null_ $libdir/plpython _null_ )); #endif /* PG_PLTEMPLATE_H */ diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile index a3c3495..2c32850 100644 *** a/src/pl/plperl/GNUmakefile --- b/src/pl/plperl/GNUmakefile *** OBJS = plperl.o spi_internal.o SPI.o *** 38,45 SHLIB_LINK = $(perl_embed_ldflags) ! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl ! REGRESS = plperl plperl_trigger plperl_shared plperl_elog # where to find psql for running the tests PSQLDIR = $(bindir) --- 38,45 SHLIB_LINK = $(perl_embed_ldflags) ! REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl --load-language=plperlu ! REGRESS = plperl plperl_trigger plperl_shared plperl_elog plperl_do # where to find psql for running the tests PSQLDIR = $(bindir) diff --git a/src/pl/plperl/expected/plperl_do.out b/src/pl/plperl/expected/plperl_do.out index ...a955581 . *** a/src/pl/plperl/expected/plperl_do.out --- b/src/pl/plperl/expected/plperl_do.out *** *** 0 --- 1,7 + DO $$ + $a = 'This is a test'; +
Re: [HACKERS] next CommitFest
On Mon, Nov 16, 2009 at 12:41:02PM -0500, Chris Browne wrote: j...@commandprompt.com (Joshua D. Drake) writes: On Mon, 2009-11-16 at 11:31 -0500, Chris Browne wrote: Ah, but the thing is, what was proposed wasn't totally evilly draconian. There's a difference between: You haven't reviewed any patches - we'll ignore you forever! and Since you haven't reviewed any patches, we are compelled to defer your patches until the next CommitFest. It's enough pain to make people think, but it's not *totally* punitive. It is important to remember we are all volunteers here. Any increase to the barrier of contribution is a bad one. But this *isn't* a barrier to contribution, at least not notably more than the already existant issue that a paucity of reviewers is a barrier to contribution. It represents a policy for triaging review efforts with a bias in favor of those that *are* contributing to the reviewers' list. I don't think it's unjust for those that contribute to the review process to get more favorable scheduling of reviews to their patches. If we get so many reviewers that such triaging becomes unnecessary, then it may automatically *not* be a problem. In the PostgreSQL Weekly News, I track patches, and apparently at least one person reads that section. Would it be helpful to track reviews somehow during commitfests with the reviewers' names prominently attached? It's a more positive approach, and like many others, I really prefer those types of approaches, even if I grump occasionally. :) Cheers, 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics 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] next CommitFest
2009/11/17 David Fetter da...@fetter.org: In the PostgreSQL Weekly News, I track patches, and apparently at least one person reads that section. Would it be helpful to track reviews somehow during commitfests with the reviewers' names prominently attached? Yes. See also my suggestion [1] that we do a Reviewer Honour Roll or Hall of Fame at the end of the CF, also published in the PWN. One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. I think creating incentives to review is going to be more potent and more enjoyable for everyone involved than punitive measures. Cheers, BJ [1] http://archives.postgresql.org/message-id/37ed240d0911130932i3b48849csb8cbae061abf1...@mail.gmail.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] next CommitFest
Brendan Jurd dire...@gmail.com writes: One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. Well, that could be fixed: instead of blah blah blah Joe Coder we could write blah blah blah Joe Coder, reviewed by X and Y Although keeping track of just who to credit might be a bit tricky. I'd be happy to commit to crediting whoever is listed in the CF entry for the patch, but sometimes other people have chimed in as much or more as the nominal reviewer. 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] next CommitFest
Brendan Jurd wrote: One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. I think creating incentives to review is going to be more potent and more enjoyable for everyone involved than punitive measures. Indeed. I once suggested only half jokingly that we should have a Coder of the month award. Maybe a Reviewer of the month award would also be good. Seriously, the major benefit most people get from contributing (apart from good karma and a warm inner glow) is kudos, and we should possibly lay it on a bit thicker. cheers andrew -- 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] Summary and Plan for Hot Standby
Please correct me if I'm wrong. Parse will result in obtaining RowExclusiveLock on the target table if it is parsing INSERT/UPDATE/DELETE. If so, is this ok in the standby? Any attempt to take RowExclusiveLock will fail. Any attempt to execute INSERT/UPDATE/DELETE will fail. This behaviour should be identical to read only transaction mode. If it is not documented as an exception, please report as a bug. Is it? It seems read only transaction mode is perfectly happy with RowExclusiveLock: test=# begin; BEGIN test=# set transaction read only; SET test=# prepare a(int) as insert into t1 values($1); PREPARE test=# \x Expanded display is on. test=# select * from pg_locks; -[ RECORD 1 ]--+- locktype | relation database | 1297143 relation | 10969 page | tuple | virtualxid | transactionid | classid| objid | objsubid | virtualtransaction | 1/101699 pid| 28020 mode | AccessShareLock granted| t -[ RECORD 2 ]--+- locktype | virtualxid database | relation | page | tuple | virtualxid | 1/101699 transactionid | classid| objid | objsubid | virtualtransaction | 1/101699 pid| 28020 mode | ExclusiveLock granted| t -[ RECORD 3 ]--+- locktype | relation database | 1297143 relation | 1574918 page | tuple | virtualxid | transactionid | classid| objid | objsubid | virtualtransaction | 1/101699 pid| 28020 mode | RowExclusiveLock granted| t test=# select relname from pg_class where oid = 1574918; -[ RECORD 1 ] relname | t1 -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net wrote: OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that Are they AND condition? If so, this patch will be useless. Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, like: Defining Python Source Code Encodings: http://www.python.org/dev/peps/pep-0263/ I'm not sure if replacing a BOM by three spaces is a good way to implement eating, because it might throw off a column indicator somewhere, say, but I couldn't reproduce a problem. Note that the U +FEFF character is defined as *zero-width* non-breaking space. I assumed psql discards whitespaces automatically, but I see it is more robust to remove BOM bytes explitly. I'll fix it. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning option for COPY
Hi, I'll hopefully look at the next version of the patch tommorrow. Emmanuel Cecchet wrote: o test1.sql always segfaults for me, poking around with gdb suggests it's a case of an uninitialised cache list (another reason to use the builtin one). I was never able to reproduce that problem. I don't know where this comes from. I have integrated your tests in the regression test suite and I was never able to reproduce the segfault you mentioned. What platform are you using? In the meantime I tried the test1.sql file again and it still segfaulted for me. I'm using 32bit Linux, PG compiled with: $ ./configure CFLAGS=-O0 --enable-cassert --enable-debug --without-perl --without-python --without-openssl --without-tcl and then I start postmaster, fire up psql, attach gdb to the backend, do \i test1.sql and get: Program received signal SIGSEGV, Segmentation fault. 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 1821child_relation_id = child_oid_cell-oid_value; (gdb) bt #0 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 #1 0x081950e3 in CopyFrom (cstate=0x871e0dc) at copy.c:2480 #2 0x08192532 in DoCopy (stmt=0x86fb144, queryString=0x86fa73c copy parent from stdin with (partitioning);) at copy.c:1227 (gdb) p child_oid_cell $1 = (OidCell *) 0x7f7f7f7f (gdb) p child_oid_cell-oid_value Cannot access memory at address 0x7f7f7f7f That 0x7f7f7f7f looks like clobbered memory, the memory management funcs do that when cassert is enabled, IIRC. Cheers, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin -- 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] UTF8 with BOM support in psql
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, As has been stated multiple times, that will not get accepted, because it will *break* files in other encodings that chance to match the BOM pattern. 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] UTF8 with BOM support in psql
Itagaki Takahiro wrote: Peter Eisentraut pete...@gmx.net wrote: OK, I think the consensus here is: - Eat BOM at beginning of file (as you implemented) - Only when client encoding is UTF-8 -- please fix that Are they AND condition? If so, this patch will be useless. Please remember \encoding or SET client_encoding appear *after* BOM at beginning of file. I'll agree if the condition is Eat BOM at beginning of file and set client encoding to UTF-8, like: Defining Python Source Code Encodings: http://www.python.org/dev/peps/pep-0263/ As previously discussed we should not be automagically setting the client encoding, nor inferring it from the presence of a BOM. As for when it can be set, unless I'm mistaken you should be able to set it before any file is opened, if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. Of course, if the server encoding is utf8 then, in the absence of it being set using those methods, the client encoding will start as utf8 also. cheers andrew -- 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] UTF8 with BOM support in psql
Andrew Dunstan and...@dunslane.net writes: As for when it can be set, unless I'm mistaken you should be able to set it before any file is opened, if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. Of course, if the server encoding is utf8 then, in the absence of it being set using those methods, the client encoding will start as utf8 also. It could also be set in ~/.psqlrc, which would probably be the most convenient method for regular users of UTF8 files who need to talk to non-UTF8 databases. 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] next CommitFest
On Mon, 2009-11-16 at 19:15 -0500, Andrew Dunstan wrote: Brendan Jurd wrote: One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. I think creating incentives to review is going to be more potent and more enjoyable for everyone involved than punitive measures. Indeed. I once suggested only half jokingly that we should have a Coder of the month award. Maybe a Reviewer of the month award would also be good. Seriously, the major benefit most people get from contributing (apart from good karma and a warm inner glow) is kudos, and we should possibly lay it on a bit thicker. In the old days (I can't believe I said that), it was not uncommon for a developer to just want a thank you, some pizza and beer. I don't know that it is much different now. It is amazing what people are willing to do if they feel a little appreciated. Joshua D. Drake cheers andrew -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] Partitioning option for COPY
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: Program received signal SIGSEGV, Segmentation fault. 0x0819368b in route_tuple_to_child (parent_relation=0xb5d93040, tuple=0x873b08c, hi_options=0, parentResultRelInfo=0x871e204) at copy.c:1821 1821child_relation_id = child_oid_cell-oid_value; (gdb) p child_oid_cell $1 = (OidCell *) 0x7f7f7f7f This looks like the patch is trying to create a data structure in a memory context that's not sufficiently long-lived for the use of the structure. If you do this in a non-cassert build, it will seem to work, some of the time, if the memory in question happens to not get reallocated to something else. A good rule of thumb is to never do code development in a non-cassert build. You're just setting yourself up for failure. 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] next CommitFest
On Nov 16, 2009, at 8:47 PM, Joshua D. Drake j...@commandprompt.com wrote: On Mon, 2009-11-16 at 19:15 -0500, Andrew Dunstan wrote: Brendan Jurd wrote: One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. I think creating incentives to review is going to be more potent and more enjoyable for everyone involved than punitive measures. Indeed. I once suggested only half jokingly that we should have a Coder of the month award. Maybe a Reviewer of the month award would also be good. Seriously, the major benefit most people get from contributing (apart from good karma and a warm inner glow) is kudos, and we should possibly lay it on a bit thicker. In the old days (I can't believe I said that), it was not uncommon for a developer to just want a thank you, some pizza and beer. I don't know that it is much different now. It is amazing what people are willing to do if they feel a little appreciated. +1. ...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] next CommitFest
On Mon, Nov 16, 2009 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Brendan Jurd dire...@gmail.com writes: One of the rewards for getting a patch into the tree is having your name immortalised in the commit log. There's no such compensation for reviewing patches. Well, that could be fixed: instead of blah blah blah Joe Coder we could write blah blah blah Joe Coder, reviewed by X and Y Although keeping track of just who to credit might be a bit tricky. I'd be happy to commit to crediting whoever is listed in the CF entry for the patch, but sometimes other people have chimed in as much or more as the nominal reviewer. If looking at the CF entries, it's important to note that sometimes one person posts a review and someone else (historically, me) adds a link to it, and of course we want to post the reviewer, not the person who dropped in the link. I try to always make the comment something like review from so-and-so when I do this, but I (or someone) might forget that on occasion, so clicking through to the underlying message is probably a good idea. As for other people chiming in, I think half a loaf is better than none. We should try to credit the people who deserve credit; and if someone who chimes in is particularly concerned about getting credit, then they can post a link to their chiming-in on the CF app. Otherwise, it's best effort, just like anything else. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Raising the geqo_threshold default
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is there any chance we can raise the default geqo_threshold from its current default of 12? This seems too low, as a modern Postgres on modern hardware has no problem with 12 table joins. However, I have seen geqo causing trouble for clients when they hit 12 and get random (and crappy) query plans. Is the value of 12 based on any recent measurements? Thanks. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200911162121 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksCCN8ACgkQvJuQZxSWSsihOwCgyRldD/QS63rQzcBO6ZoyI/zH NPUAoNmkgf3Txr/V6p2oZJ/tNY8gx/mt =u1ut -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning option for COPY
Tom Lane wrote: A good rule of thumb is to never do code development in a non-cassert build. And the same rule goes for review, too; I'll update the review guidelines to spell that out more clearly. Basically, if you're doing any work on new code, you should have cassert turned on, *except* if you're doing performance testing. The asserts slow things down enough (particularly with large shared_buffers values) to skew performance tests, but in all other coding situations you should have them enabled. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Listen / Notify rewrite
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 old method scaled (badly) on volume of notifications and your stuff seems to scale based on # of client's sending simultaneous notifications. Well, you're better all day long, but it shows that your fears regarding locking were not completely unfounded. Do the Burcardo people have any insights on the #of simultaneous notifies are generated from different backends? Very low. On a busy system I know of there are about 90 entries in pg_listener, and I would guess that the maximum rate of simulataneous notifies is not more than 3 per second, tops. If someone knows an easy way to measure such a thing and is really curious, I can see about getting better numbers. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200911162127 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAksCCjsACgkQvJuQZxSWSsgTogCfS5Xg8N2JhsUpi2r96IbxX+Tm pMsAnAktBVkEblzx6Ux/netXkP9u4AVG =SO/k -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 with BOM support in psql
Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: if you need to, using PGOPTIONS or psql dbname=mydb options='-c client_encoding=utf8'. It could also be set in ~/.psqlrc, which would probably be the most convenient method for regular users of UTF8 files who need to talk to non-UTF8 databases. It's nonsense. Users often use scripts written in difference encodings at once. Encoding information should be packed in script file itself. We should not force users to open script files and check its encoding before they execute the files. BTW, I have an idea to improve handling of per-file encoding. We continue to use the encoding settings specified in included file at \i command. But should the setting be reverted at the end of file? ie. =# \encoding SJIS =# \i script-in-utf8.sql =# -- encoding should be SJIS here. If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRIGGER with WHEN clause
KaiGai Kohei kai...@kaigai.gr.jp wrote: Itagaki-san, I also think your example usage is enough valueable. However, Oracle does not have the feature apparently, although the purpose of this patch is to provide a compatible feature, IIRC. I don't have any preference on either of them. If you make a decision, I'll review the patch according to your decision. So, I like to ask you which is your preference again. I'd like to add support statement triggers with WHEN clause. Of cource Oracle is a good example, but it doesn't prevent us from developing a better copy :) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sgml and empty closing tags
While looking over the writable cte patch I noticed queries.sgml has lots of things in the form literalFROM/. I tried various googles to see if / is some kind of sgml/xml shorthand for close the last opened tag. But alas, nothing found. Bad google foo? Should we change those to be the right closing tag? aka /literal -- 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] sgml and empty closing tags
Alex Hunsaker bada...@gmail.com writes: While looking over the writable cte patch I noticed queries.sgml has lots of things in the form literalFROM/. I tried various googles to see if / is some kind of sgml/xml shorthand for close the last opened tag. But alas, nothing found. Bad google foo? Apparently --- it's perfectly legal in SGML. (I think not in XML.) Should we change those to be the right closing tag? aka /literal You'd be wasting your time. I don't think it's good style to use / when the opening tag is far away or there are other tags between. But for examples like the one you cite, it's perfectly reasonable. 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] Raising the geqo_threshold default
Greg Sabino Mullane g...@turnstep.com writes: Is there any chance we can raise the default geqo_threshold from its current default of 12? We were over that just a few months ago. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sgml and empty closing tags
Tom Lane wrote: Alex Hunsaker bada...@gmail.com writes: While looking over the writable cte patch I noticed queries.sgml has lots of things in the form literalFROM/. I tried various googles to see if / is some kind of sgml/xml shorthand for close the last opened tag. But alas, nothing found. Bad google foo? Apparently --- it's perfectly legal in SGML. (I think not in XML.) Correct on both counts. cheers andrew -- 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] UTF8 with BOM support in psql
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. This isn't going to happen, so please stop wasting our time arguing about 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] sgml and empty closing tags
On Mon, Nov 16, 2009 at 20:41, Tom Lane t...@sss.pgh.pa.us wrote: Apparently --- it's perfectly legal in SGML. (I think not in XML.) Cool. Thanks! BTW anyone know how to escape and for google? I tried searching for it-- but ran into a chick and egg situation. So the I tried various forms of google search left angle bracket, quotes, backslashes and +. no luck -- 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] Raising the geqo_threshold default
On Mon, Nov 16, 2009 at 10:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Sabino Mullane g...@turnstep.com writes: Is there any chance we can raise the default geqo_threshold from its current default of 12? We were over that just a few months ago. Yeah. I think we need to see if we can do something about the ridiculous amount of memory that the standard planner consumes for large join problems. I would like to look into this problem, too, which might be related, but have not had time: http://archives.postgresql.org/pgsql-hackers/2009-11/msg00328.php ...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] sgml and empty closing tags
On Mon, Nov 16, 2009 at 10:54 PM, Alex Hunsaker bada...@gmail.com wrote: On Mon, Nov 16, 2009 at 20:41, Tom Lane t...@sss.pgh.pa.us wrote: Apparently --- it's perfectly legal in SGML. (I think not in XML.) Cool. Thanks! BTW anyone know how to escape and for google? I tried searching for it-- but ran into a chick and egg situation. So the I tried various forms of google search left angle bracket, quotes, backslashes and +. no luck I don't think you can. I gather that the Google text search algorithm is word-based. It seems like you can't search for things that it doesn't consider to be words. It has a pretty expansive notion of what a word is (like 2a43 is a word, for example) but any non-word characters are ignored (so, for example, 2a43$ returns the same hits as 2a43). ...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] UTF8 with BOM support in psql
Tom Lane t...@sss.pgh.pa.us wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: If encoding setting is reverted, Eat BOM at beginning of file and set client encoding to UTF-8 will be much safer. This isn't going to happen, so please stop wasting our time arguing about it. Ok, sorry. But I still cannot accept this restriction. - Only when client encoding is UTF-8 -- please fix that The attachd patch is a new proposal of the feature. When we found BOM at beginning of file, set expected_encoding to UTF8. Before every execusion of query, if pset.encoding is not UTF8, we check the query string not to contain any non-ASCII characters and throw an error if found. Encoding declarations are typically written only in ascii characters, so we can postpone encoding checking until non-ascii characters appear. Since the default value of expected_encoding is SQL_ASCII, that pass through all characters, so the patch does nothing to scripts without BOM. (There are no codes to set expected_encoding except BOM.) If client encoding is UTF8, it skips BOM and no effect to the script body. BOMs are skipped even if client encoding is not set to UTF8, but can throw an error if there are no explicit encoding declaration. AFAIC, the patch can solve the almost problems in the discussions developmentally. Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center psql-utf8bom_20091117.patch 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] patch - Report the schema along table name in a referential failure error message
On Sun, Nov 15, 2009 at 1:43 PM, Andrew Dunstan and...@dunslane.net wrote: George Gensure wrote: This begs a bigger question: what's *really* easy or low barrier to entry for very light contributors like myself? - I've got time, I like the product, I need to know what's going to get you a win, I may not be gunning particularly for the feature myself. The TODO list at http://wiki.postgresql.org/wiki/Todo doesn't seem to have a huge number or [E] items. Maybe we need a bit of a brainstorm to come up with a few more. The one I just started talking about (using param names in SQL functions) might not be terribly hard, depending on your coding skills, since it would be making use of the new parser hooks feature that Tom has just done the heavy lifting on. cheers andrew There's some tricky stuff in here to say the least. Doesn't look like param names are kept anywhere past the parser - gonna have to have it follow through a bunch of functions to reach parse_(fixed|variable)_parameters. The p_post_columnref_hook you alluded to will help once I have the names though, so thanks :) -George -- 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] Writeable CTE patch
On Sun, Nov 15, 2009 at 14:27, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
I wrote:
Attached is the latest version of this patch.
Find attached a incremental diff with the following changes:
-get rid of operation argument to InitResultRelInfo, its unused now
-add some asserts to make sure places we use subplanstate now that it
can be null
(*note* AFAICT its a cant happen, but it made me nervous hence the Asserts)
-remove unneeded plannodes.h includes
-minor whitespace fix
Other comments:
You have an XXX we should probably update the snapshot a bit
differently. Any plans on that?
Thats quite a bit of new code in ExecutePlan, worth splitting into its
own function?
Also, after reading through the previous threads; it was not
immediately obvious that you dealt with
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00566.php by
only allowing selects or values at the top level of with.
Find below the standard review boilerplate from
http://wiki.postgresql.org/wiki/Reviewing_a_Patch
Summary: looks ready for a commiter to me after above comments are addressed.
Submission review:
*Is the patch in context diff format?
Yes
* Does it apply cleanly to the current CVS HEAD?
Yes, with fuzz
* Does it include reasonable tests, necessary doc patches, etc?
Yes
Usability review:
Read what the patch is supposed to do, and consider:
* Does the patch actually implement that?
Yes
* Do we want that?
Yes
* Do we already have it?
No
* Does it follow SQL spec, or the community-agreed behavior?
Yes
* Does it include pg_dump support (if applicable)?
N/A
* Are there dangers?
No
* Have all the bases been covered?
All the ones I can see
Feature test:
Apply the patch, compile it and test:
* Does the feature work as advertised?
Yes
* Are there corner cases the author has failed to consider?
Not that I could trigger
* Are there any assertion failures or crashes?
No
o Review should be done with the configure options --enable-cassert
and --enable-debug turned on;
Yes
Performance review:
*Does the patch slow down simple tests:
No
*If it claims to improve performance, does it?
N/A
*Does it slow down other things
No
Coding review:
Read the changes to the code in detail and consider:
* Does it follow the project coding guidelines?
Yes
* Are there portability issues?
No
* Will it work on Windows/BSD etc?
Yes
* Are the comments sufficient and accurate?
Yes
* Does it do what it says, correctly?
Yes
* Does it produce compiler warnings?
No
* Can you make it crash?
No
Architecture review:
Consider the changes to the code in the context of the project as a whole:
* Is everything done in a way that fits together coherently with
other features/modules?
I think so.
* Are there interdependencies than can cause problems?
No
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***
*** 925,931 ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE, /* don't need any index info */
false);
resultRelInfo++;
}
--- 925,930
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***
*** 665,671 InitPlan(QueryDesc *queryDesc, int eflags)
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- operation,
estate-es_instrument);
resultRelInfo++;
}
--- 665,670
***
*** 857,863 void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
bool doInstrument)
{
/*
--- 856,861
***
*** 987,993 ExecGetTriggerResultRel(EState *estate, Oid relid)
InitResultRelInfo(rInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE,
estate-es_instrument);
estate-es_trig_target_relations =
lappend(estate-es_trig_target_relations, rInfo);
--- 985,990
*** a/src/backend/executor/nodeSubplan.c
--- b/src/backend/executor/nodeSubplan.c
***
*** 667,672 ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
--- 667,673
/* Link the SubPlanState to already-initialized subplan */
sstate-planstate = (PlanState *) list_nth(estate-es_subplanstates,
subplan-plan_id - 1);
+ Assert(sstate-planstate != NULL);
/* Initialize subexpressions */
sstate-testexpr = ExecInitExpr((Expr *) subplan-testexpr, parent);
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
***
*** 18,24
#include nodes/nodeFuncs.h
#include parser/analyze.h
#include parser/parse_cte.h
- #include nodes/plannodes.h
#include utils/builtins.h
--- 18,23
*** a/src/backend/parser/parse_relation.c
--- b/src/backend/parser/parse_relation.c
***
*** 24,30
Re: [HACKERS] sgml and empty closing tags
On mån, 2009-11-16 at 20:30 -0700, Alex Hunsaker wrote: While looking over the writable cte patch I noticed queries.sgml has lots of things in the form literalFROM/. I tried various googles to see if / is some kind of sgml/xml shorthand for close the last opened tag. But alas, nothing found. Bad google foo? If you have DocBook installed locally, you should have a file called docbook.dcl, which contains the SGML declaration of DocBook, and somewhere down contains this: FEATURES MINIMIZE DATATAG NO OMITTAG NO RANK NO SHORTTAG YES So if you google for something like markup minimization shorttag, you can find more information. For amusement, contrast this with the SGML declaration of HTML: FEATURES MINIMIZE DATATAG NO OMITTAG YES -- This is why you can omit body, for example. RANK NO SHORTTAG YES -- 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] UTF8 with BOM support in psql
On tis, 2009-11-17 at 14:19 +0900, Itagaki Takahiro wrote: The attachd patch is a new proposal of the feature. When we found BOM at beginning of file, set expected_encoding to UTF8. Before every execusion of query, if pset.encoding is not UTF8, we check the query string not to contain any non-ASCII characters and throw an error if found. Encoding declarations are typically written only in ascii characters, so we can postpone encoding checking until non-ascii characters appear. Since the default value of expected_encoding is SQL_ASCII, that pass through all characters, so the patch does nothing to scripts without BOM. (There are no codes to set expected_encoding except BOM.) If client encoding is UTF8, it skips BOM and no effect to the script body. BOMs are skipped even if client encoding is not set to UTF8, but can throw an error if there are no explicit encoding declaration. I think I could support using the presence of the BOM as a fall-back indicator of encoding in absence of any other declaration. It seems to me, however, that the description above ignores the existence of encodings other than SQL_ASCII and UTF8. Also, when the proposed patch to set the encoding from the locale appears, we need to make this logic more precise. Something like: 1. set client_encoding or \encoding, otherwise 2. if BOM found, then UTF8, otherwise 3. by locale environment, otherwise 4. SQL_ASCII (= server encoding, effectively) Also, I'm not sure if we need this logic only when we send a query. It might be better to do this in the lexer when we find a non-ASCII character and we don't have a client encoding != SQL_ASCII set yet. -- 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] TRIGGER with WHEN clause
Itagaki-san, I checked the latest patch. It seems to me the patch getting improved from the prior version. We are next to the commiter review phase. But I could find a few matters. :-( Please see the following comments, and fix it before sending it to commiters. I fixed the bug and two other bugs: * Crash in AFTER TRIGGER + WHEN clause. * Incorrect behavior when multiple tuples are modified. Also regression tests for it are added. It looks for me fine. I'd like to add support statement triggers with WHEN clause. Of cource Oracle is a good example, but it doesn't prevent us from developing a better copy :) OK, it is your decision. * the documentation seems to me misleading. It saids, NEW and OLD are only available and ... o INSERT can refer NEW o UPDATE can refer NEW, OLD o DELETE can refer OLD But, it may actually incorrect, if user gives several events on a certain trigger. For example, when a new trigger is invoked for each row on INSERT or UPDATE statement, the function cannot refer the OLD. They are bitwise-AND flags. INSERT-OR-DELETE trigger cannot refer to both OLD and NEW tuples. It is possible to use a dummy tuple (filled with NULLs?) in the cases, but I want to just throw an error as of now. I'll fix documentation to reflect the code. Ideas for better descriptions welcome. | Note that if a trigger has multiple events, it can refer only tuples | that can be referred in all of the events. For example, | INSERT OR DELETE trigger cannot refer neither NEW nor OLD tuples. At least, it seems to me meaningful. Is there any comments from native English users? varlistentry + termreplaceable class=parametercondition/replaceable/term + listitem + para + Any acronymSQL/acronym conditional expression (returning + typeboolean/type). Only literalFOR EACH ROW/literal triggers + can refer literalNEW/ and literalOLD/ tuples. + literalINSERT/literal trigger can refer literalNEW/, + literalDELETE/literal trigger can refer literalOLD/, + and literalUPDATE/literal trigger can refer both of them. + Note that if a trigger has multiple events, it can refer only tuples + that can be referred in all of the events. For example, + literalINSERT/ literalOR/ literalDELETE/ trigger cannot + refer neither literalNEW/ nor literalOLD/ tuples. + /para + /listitem +/varlistentry In addition, I could find a few matters. * TOAST may be necessary for pg_trigger? If we give very looong condition on the WHEN clause, the pg_trigger.tgqual can over the limitation of block size. postgres=# CREATE TRIGGER hoge before insert on t1 for row when (a [... very long condition ...]) execute procedure trig_func(); ERROR: row is too big: size 12940, maximum size 8164 But it is a quite corner case in my opinion. It depends on your preference. * ROW INSERT TRIGGER on COPY FROM statement --- The Assert() in TriggerEnabled (commands/trigger.c:2410) was mistaken bombing. In the code path from copy.c, NULL can be set on the estate-es_trig_tuple_slot. postgres=# CREATE TRIGGER tg_ins_row BEFORE INSERT ON t1 FOR ROW WHEN (true) EXECUTE PROCEDURE trig_func(); CREATE TRIGGER postgres=# COPY t1 FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. 2bbb server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. (gdb) bt #0 0x00cd4416 in __kernel_vsyscall () #1 0x009beba1 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64 #2 0x009c046a in abort () at abort.c:92 #3 0x08330e7e in ExceptionalCondition (conditionName=value optimized out, errorType=value optimized out, fileName=value optimized out, lineNumber=value optimized out) at assert.c:57 #4 0x081956d9 in TriggerEnabled (trigger=value optimized out, event=value optimized out, modifiedCols=value optimized out, estate=value optimized out, tupdesc=value optimized out, oldtup=value optimized out, newtup=value optimized out) at trigger.c:2410 #5 0x08196410 in ExecBRInsertTriggers (estate=value optimized out, relinfo=value optimized out, trigtuple=value optimized out) at trigger.c:1835 #6 0x08162e0e in CopyFrom (cstate=value optimized out) at copy.c:2137 #7 DoCopy (cstate=value optimized out) at copy.c:1189 #8 0x0827c653 in ProcessUtility (parsetree=value optimized out, queryString=value optimized out, params=value optimized out, isTopLevel=value optimized out, dest=value optimized out, completionTag=value optimized out) at utility.c:581 #9 0x0827931d in PortalRunUtility (portal=0x94a0e4c,
Re: [HACKERS] write ahead logging in standby (streaming replication)
Hi, Quoting Greg Smith g...@2ndquadrant.com: Synchronous replication - guarantees zero data loss by the means of atomic write operation, i.e. write either completes on both sides or not at all. Write is not considered complete until acknowledgement by both local and remote storage. Note that a storage acknowledge (hopefully) guarantees durability, but it does not necessarily mean that the transactional changes are immediately visible on a remote node. Which is what you had in your definition. My point is that there are at least three things that can run synchronously or not, WRT to distributed databases: 1. conflict detection and handling (for consistency) 2. storage acknowledgement (for durability) 3. effective application of changes (for visibility across nodes) That last part is the critical one: acknowledgement by both local and remote storage is required before you can label something truly synchronous replication. In implementation terms, that means you must have both local and slave fsync calls finish to be considered truly synchronous. That part is not ambiguous at all. I personally agree 100%. (Given it implies a congruent conflict handling *before* the disk write. Having conflicting transactional changes on the disk wouldn't help much at recovery time). (And yes, this means I think the effective application of changes can be deferred. IMO the load balancer and/or the application should take care not to send transactions from the same session to different nodes). Semi-synchronous replication ..is plain non-sense to my ears. Either something is synchronous or it is not. No half, no semi, no virtual synchrony. To have any technical relevance, one needs to add *what* is synchronous and what not. In that spirit I have to admit that the term 'eager' that I'm currently using to describe Postgres-R may not be any more helpful. I take it to mean synchrony of 1. and 2., but not 3. Regards Markus Wanner -- 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] UTF8 with BOM support in psql
Peter Eisentraut pete...@gmx.net wrote: I think I could support using the presence of the BOM as a fall-back indicator of encoding in absence of any other declaration. What is the difference the fall-back and set client encoding to UTF-8 if BOM found ? I read this discussion that we cannot accept any automatic encoding detections (properly speaking, detection is ok, but automatic assignment is not). We should not have any fall-back mechanism, no? Also, when the proposed patch to set the encoding from the locale appears, we need to make this logic more precise. Encoding-from-locale feature will be useful, but the patch does *not* set any encodings. The reason is same as above. Also, I'm not sure if we need this logic only when we send a query. It might be better to do this in the lexer when we find a non-ASCII character and we don't have a client encoding != SQL_ASCII set yet. Absolutely, but is it an indepedent issue from BOM? Multi-byte scripts without encoding are always dangerous whether BOM is present or not. I'd say we can always throw an error when we find queries that contain multi-byte characters if no prior encoding declaration. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
