Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
On 03/09/10 21:50, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: On 03/09/10 21:16, Tom Lane wrote: It's probably not too unreasonable to assume that pid_t assignment is atomic. But I'm still thinking that we have bigger problems than that if there are really cases where SetLatch can execute at approximately the same time as a latch owner is coming or going. I don't see how to avoid it. A walsender, or any process really, can exit at any time. It can make the latch inaccessible to others before it exits to minimize the window, but it's always going to be possible that another process is just about to call SetLatch when you exit. Well, in that case what we need to do is presume that the latch object has a continuing existence but the owner/receiver can come and go. I would suggest that InitLatch needs to initialize the object into a valid but unowned state; there is *no* deinitialize operation; and there are AcquireLatch and ReleaseLatch operations to become owner or stop being owner. I think we have just a terminology issue. What you're describing is exactly how it works now, if you just s/InitLatch/AcquireLatch. At the moment there's no need for an initialization function other than the InitLatch/AcquireLatch that associates the latch with the current process. I can add one for the sake of future-proofing, and to have better-defined behavior for setting a latch that has not been owned by anyone yet, but it's not strictly necessary. We also need to define the semantics of SetLatch on an unowned latch --- does this set a signal condition that will be available to the next owner? At the moment, no. Perhaps that would be useful, separating the Init and Acquire operations is needed to make that sane. -- 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] string function - format function proposal
2010/9/6 Itagaki Takahiro itagaki.takah...@gmail.com: On Wed, Sep 1, 2010 at 1:29 PM, Pavel Stehule pavel.steh...@gmail.com wrote: * %v also doesn't quote boolean values, but t and f are not valid. You should use true and false (or 't' and 'f') for the cases. you have a true - it should be fixed I found quote_literal() prints boolean values as 'true' or 'false'. It uses casting to text type rather than calling output function. OTOH, format functions (and concat funcs) use output functions. Which should we use for such purposes? Consistent behavior is obviously preferred. Boolean type might be the only type that is converted to different representation in typoutput or cast-to-test, but we should consider to have boolean-specific hardwired code, or cast all types to text instead of output functions. Personally I prefer casting to text - it allows some later customizations. And it's more consistent with || operator. So the functions concat* should to be fixed. Regards Pavel -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
OT: OFF TOPIC: [HACKERS] returning multiple result sets from a stored procedure
OT: OFF TOPIC: I honestly do not mean any offence, just out of curiosity. If you guys care about money and time why would you spend the best years of your life basically copying commercial products for free? Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details. I just don't follow/understand your thinking. Maybe I am naïve. I do not have experience with open source and I kind of thought open source guys do not need or care about money and time. John From: Josh Berkus j...@agliodbs.com To: John Adams john_adams_m...@yahoo.com Cc: PostgreSQL-development pgsql-hackers@postgreSQL.org Sent: Fri, September 3, 2010 1:07:03 PM Subject: Re: [HACKERS] returning multiple result sets from a stored procedure I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com
Re: [HACKERS] The other major HS TODO: standby promotion
On Sat, Sep 4, 2010 at 5:02 AM, Josh Berkus j...@agliodbs.com wrote: (a) seems easily enough solved by giving two steps: giving the DBA a way to check where in the replication stream each standby is (I think we already have this) Yep, pg_last_xlog_receive_location would help. The second method would be by giving standbys a way to subscribe to a new timeline. This seems like the better approach, as it would logically be part of the re-mastering command. What changes would be required to do this? Wait for new master to archive the timeline history file, set recovery_target_timeline to 'latest' in unpromoted standbys and restart them. Which would make them restore WAL files with previous timeline from the archive and read WAL files with current one. (c) can actually already be dealt with by setting an archive_command on each standby. Beyond that, I don't think that we really need to do anything; DBAs can have a choice between archiving logs to allow for remastering of all standbys, or saving space and bandwidth, and forcing some standbys to be re-cloned if you run out of time. It would be nice, eventually, to have a way to tell PostgreSQL to retain more or less WAL segments without restarting the server, but I don't see this as critical. Or the register/unregister of standbys facility is required? http://archives.postgresql.org/pgsql-hackers/2010-08/msg01984.php And we would need to change primary_conninfo in all the unpromoted standbys before restarting them. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION 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: OT: OFF TOPIC: [HACKERS] returning multiple result sets from a stored procedure
2010/9/3 John Adams john_adams_m...@yahoo.com: OT: OFF TOPIC: I honestly do not mean any offence, just out of curiosity. If you guys care about money and time why would you spend the best years of your life basically copying commercial products for free? Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details. I just don't follow/understand your thinking. Maybe I am naïve. I do not have experience with open source and I kind of thought open source guys do not need or care about money and time. The work on PostgreSQL is adventure, and very good experience, very good school for me. It's job only for people who like programming, who like hacking, it isn't job for people, who go to office on 8 hours. Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a perfect knowledge, perfect contacts to developers, and I can work together with best programmers on planet. and I can create some good things. Probably if I work on commercial projects I can have a better money - but life is only one, and money is important, but not on top for me - life have to be adventure! Regards Pavel Stehule John From: Josh Berkus j...@agliodbs.com To: John Adams john_adams_m...@yahoo.com Cc: PostgreSQL-development pgsql-hackers@postgreSQL.org Sent: Fri, September 3, 2010 1:07:03 PM Subject: Re: [HACKERS] returning multiple result sets from a stored procedure I noticed in postgres you cannot return multiple result sets from a stored procedure (surprisingly as it looks like a very good dbms). That feature has been on the TODO list for years. However, nobody has stepped forward to either write it, or to fund working on it. If your company has programmers or money to build this feature, it could probably get done fairly quickly (as in, next version). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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] git: uh-oh
On Sun, Sep 5, 2010 at 10:43, Max Bowsher m...@f2s.com wrote: On 05/09/10 03:55, Robert Haas wrote: On Sat, Sep 4, 2010 at 9:17 AM, Max Bowsher m...@f2s.com wrote: Can you post the repo you ended up with somewhere? Well, it's a Bazaar repository at the moment :-) But, I'll re-run it targetting git, and push it somewhere. github? anywhere better? No, that's fine. I think we should start a git repository somewhere containing the precise conversion recipe - i.e.: * cvs2git options file * cvs2git invocation command line * all scripts that massage the CVS repository before conversion, or the Git repository afterwards Yeah, that would be great. For both, see http://github.com/maxb As I've previously posted, the stuff I've done is all on http://github.com/mhagander/pg_githooks/tree/master/migration/ But I have to confess I haven't put up the latest versions of the scripts I've been using yet - I wanted to narrow down the problems first.. -- 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] git: uh-oh
On Sun, Sep 5, 2010 at 04:55, Robert Haas robertmh...@gmail.com wrote: On Sat, Sep 4, 2010 at 9:17 AM, Max Bowsher m...@f2s.com wrote: and the result is that things are looking pretty clean :-) Hey, that's great. But I wonder why Magnus got a different result. This is the first time I've posted these incantations for excising the unwanted history, so he would not have been using them. Well, he did something fairly similar. Not sure if it was exactly the same. No. I used cvs admin, not rcs. The specific commands I ran were the following (and I'm pretty darn sure I posted this before): cd pgsql/src/backend/parser cvs admin -o 2.90.2.1:2.90.2.2 gram.c cvs admin -o 2.89: gram.c cd ../../interfaces/ecpg/preproc cvs admin -o 1.5.2.1:1.5.2.2 pgc.c cvs admin -o 1.3: pgc.c cvs admin -o 1.11.2.1:1.11.2.2 preproc.c cvs admin -o 1.7: preproc.c I would assume cvs just runs rcs commands behind the scenes, but I confess knowing way too little about that stuff to be sure ;) I'll be happy to re-run with the rcs commands instead. Can you post the repo you ended up with somewhere? Well, it's a Bazaar repository at the moment :-) But, I'll re-run it targetting git, and push it somewhere. github? anywhere better? No, that's fine. Yes, it's fine - just please be sure to remove the repository once we're done with the master conversion, so people don't end up accidentally cloning an incorrect one :D -- 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] git: uh-oh
On Mon, Sep 6, 2010 at 06:09, Tom Lane t...@sss.pgh.pa.us wrote: Michael Haggerty mhag...@alum.mit.edu writes: Tom Lane wrote: I suspect what it's doing is attributing the branch creation to the user who makes the first commit on the branch for that file. In general I'd expect that to give a reasonable result --- better than choosing a guaranteed-to-be-wrong constant value anyway ;-) On the contrary, I prefer an obvious indication of I don't know to a value that might appear to be authoritative but is really just a guess. It could be that one user copied the file verbatim to the branch and a second user changed the file as part of an unrelated change. Hm, I see. The default default value for these commits is cvs2svn (in your case cvs2git would probably be more appropriate), which I like because it makes it clearer than pgsql that the commit was generated as part of a conversion. If we can set it to a value different from any actual committer name, that would be a good thing to do. I intentionally picked the pgsql user because AFAIK that's what we've been previously using for commits that aren't commits. I figured the repository would be cleaner with just one such pseudo-user rather than two. But it's a trivial change - it just needs a name and an email address (which doesn't have to actually work, of course) -- 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] Windows Tools
On Sat, Sep 4, 2010 at 00:26, Peter Geoghegan peter.geoghega...@gmail.com wrote: Hi David I seem to recall the last time that I built Postgres on windows using VC++, there was a whole load of compiler warnings, because MS have taken it upon themselves to deprecate various C std lib functions (in particular, string.h functions). You're supposed to use their non-standard variants that specify bounds. They can safely be ignored. That must've been a *long* time ago. IIRC, we got rid of those before making the MSVC build official in a release... I wonder if you're mixing it up with the time when we only supported building libpq with it - we didn't, i think, remove those warnings back then... -- 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] Synchronization levels in SR
Boszormenyi Zoltan z...@cybertec.at writes: Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? I don't understand why it would be the case (sync holding back async transactions) — it's been proposed that walsender could periodically feed back to the master the current WAL position received, synced and applied. So you can register your sync transaction to wait (and block) until walsender sees a synced WAL position after your own (including it) and another transaction can wait until walsender sees a received WAL position after its own, for example. Of course, meanwhile, any async transaction would just commit without caring about slaves. Not implementing it nor thinking about how to implement it, it seems simple enough :) Regards, -- dim -- 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] Synchronous replication - patch status inquiry
Disclaimer : I have understood things in a way that allows me to answer here, I don't know at all if that's the way it's meant to be understood. Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: (scratches head..) What's the point of differentiating received/fsynced/replayed, if the master receives the ack for all of them at the same time? It wouldn't the way I understand Simon's proposal. What's happening is that the feedback channel is periodically sending an array of 3 LSN, the currently last received, fsync()ed and applied ones. Now what you're saying is that we should feed back this information after each recovery step forward, what Simon is saying is that we could have a looser coupling between the slave activity and the feedback channel to the master. That means the master will not see all the slave's restoring activity, but as the LSN are a monotonic sequence that's not a problem, we can use = rather than = in the wait-and-wakeup loop on the master. Let's try this with an example: In the master, I do stuff and commit a transaction. I want to know when the transaction is fsynced in the standby. The WAL is sent to the standby, up to the commit record. [...] So, when does standby send the single message back to the master? The standby is sending a stream of messages to the master with current LSN positions at the time the message is sent. Given a synchronous transaction, the master would wait until the feedback stream reports that the current transaction is in the past compared to the streamed last known synced one (or the same). Hope this helps, regards, -- dim -- 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] Synchronous replication - patch status inquiry
On 06/09/10 16:03, Dimitri Fontaine wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: (scratches head..) What's the point of differentiating received/fsynced/replayed, if the master receives the ack for all of them at the same time? It wouldn't the way I understand Simon's proposal. What's happening is that the feedback channel is periodically sending an array of 3 LSN, the currently last received, fsync()ed and applied ones. Periodically is a performance problem. The bottleneck in synchronous replication is typically the extra round-trip between master and standby, as the master needs to wait for the acknowledgment. Any delays in sending that acknowledgment lead directly to a decrease in performance. That's also why we need to eliminate the polling loops in walsender and walreceiver, and make them react immediately when there's work to do. Let's try this with an example: In the master, I do stuff and commit a transaction. I want to know when the transaction is fsynced in the standby. The WAL is sent to the standby, up to the commit record. [...] So, when does standby send the single message back to the master? The standby is sending a stream of messages to the master with current LSN positions at the time the message is sent. Given a synchronous transaction, the master would wait until the feedback stream reports that the current transaction is in the past compared to the streamed last known synced one (or the same). That doesn't really answer the question: *when* does standby send back the acknowledgment? -- 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] Windows Tools
Hello, David. We are still using MinGW. Why? How? And other questions are answered in my post: http://pgolub.wordpress.com/2008/12/15/building-postgresql-client-library-using-mingw-under-winxp-sp3/ You wrote: DF Hello, DF At work, I've been tasked with providing some Windows connection DF libraries, etc. Are people still using MinGW, or should I just DF recommend using a proprietary toolkit to do the builds? If so, which DF one(s)? DF Cheers, DF David (Windows n00b) DF -- DF David Fetter da...@fetter.org http://fetter.org/ DF Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter DF Skype: davidfetter XMPP: david.fet...@gmail.com DF iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics DF Remember to vote! DF Consider donating to Postgres: http://www.postgresql.org/about/donate -- With best wishes, Pavel mailto:pa...@gf.microolap.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] git: uh-oh
Magnus Hagander mag...@hagander.net writes: On Mon, Sep 6, 2010 at 06:09, Tom Lane t...@sss.pgh.pa.us wrote: If we can set it to a value different from any actual committer name, that would be a good thing to do. I intentionally picked the pgsql user because AFAIK that's what we've been previously using for commits that aren't commits. Uh, no, not so. Marc used to use that ID for commits related to pushing new versions. It's been retired, but there's nothing un-real about the commits under that ID. Please pick something else. I thought the suggestion of cvs2git was a good one. 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] string function - format function proposal
Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/6 Itagaki Takahiro itagaki.takah...@gmail.com: Which should we use for such purposes? Consistent behavior is obviously preferred. Boolean type might be the only type that is converted to different representation in typoutput or cast-to-test, but we should consider to have boolean-specific hardwired code, or cast all types to text instead of output functions. Personally I prefer casting to text - No, you need to use the I/O functions. Not every type is guaranteed to have a cast to text. iit allows some later customizations. And it's more consistent with || operator. I don't buy either of those arguments. 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] git: uh-oh
On Mon, Sep 6, 2010 at 15:37, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Mon, Sep 6, 2010 at 06:09, Tom Lane t...@sss.pgh.pa.us wrote: If we can set it to a value different from any actual committer name, that would be a good thing to do. I intentionally picked the pgsql user because AFAIK that's what we've been previously using for commits that aren't commits. Uh, no, not so. Marc used to use that ID for commits related to pushing new versions. It's been retired, but there's nothing un-real about the commits under that ID. Please pick something else. I thought the suggestion of cvs2git was a good one. Ok, I'll switch to that - no problem. Should the name really be PostgreSQL Daemon then? (Because that's what it's called on the cvs box, but that's probably just a coincidence) -- 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] git: uh-oh
Magnus Hagander mag...@hagander.net writes: On Mon, Sep 6, 2010 at 15:37, Tom Lane t...@sss.pgh.pa.us wrote: Uh, no, not so. Marc used to use that ID for commits related to pushing new versions. It's been retired, but there's nothing un-real about the commits under that ID. Please pick something else. I thought the suggestion of cvs2git was a good one. Ok, I'll switch to that - no problem. Should the name really be PostgreSQL Daemon then? (Because that's what it's called on the cvs box, but that's probably just a coincidence) That seems to be the name that shows up in the pgsql-committers archives, so I'd say we should stick with it. We're not in the business of redefining history here. 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] string function - format function proposal
On Mon, Sep 6, 2010 at 10:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, you need to use the I/O functions. Not every type is guaranteed to have a cast to text. One issue is that Pavel want to generate valid SQL statement using %v format. Boolean values are printed as t or f, so the unquoted values are not valid syntax. If we only use output functions, boolean values should be written as 't' or 'f' (single-quoted), Only numeric values can be unquoted on %v. -- Itagaki Takahiro -- 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] string function - format function proposal
2010/9/6 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2010/9/6 Itagaki Takahiro itagaki.takah...@gmail.com: Which should we use for such purposes? Consistent behavior is obviously preferred. Boolean type might be the only type that is converted to different representation in typoutput or cast-to-test, but we should consider to have boolean-specific hardwired code, or cast all types to text instead of output functions. Personally I prefer casting to text - No, you need to use the I/O functions. Not every type is guaranteed to have a cast to text. iit allows some later customizations. And it's more consistent with || operator. I don't buy either of those arguments. can we use a both? like plpgsql? First check cast to text, and second use a IO functions? Why I think so this is useful - sometimes people asked some GUC for formatting date, boolean and other. If these functions try to use a cast to text first, then there is some space for customization via custom cast functions. Regards Pavel Stehule 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] string function - format function proposal
2010/9/6 Itagaki Takahiro itagaki.takah...@gmail.com: On Mon, Sep 6, 2010 at 10:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, you need to use the I/O functions. Not every type is guaranteed to have a cast to text. One issue is that Pavel want to generate valid SQL statement using %v format. Boolean values are printed as t or f, so the unquoted values are not valid syntax. we can format some types directly - but I like idea of casting to text because there is space for users. Pavel If we only use output functions, boolean values should be written as 't' or 'f' (single-quoted), Only numeric values can be unquoted on %v. -- Itagaki Takahiro -- 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] Streaming a base backup from master
On Sun, Sep 5, 2010 at 4:51 PM, Martijn van Oosterhout klep...@svana.org wrote: If you're working from a known good version of the database at some point, yes you are right you have more interesting options. If you don't you want something that will fix it. Sure, in that case you want to restore from backup. Whatever you use to do that is the same net result. I'm not sure rsync is actually going to be much faster though since it still has to read all of the existing database which a normal restore doesn't have to. If the database has changed significantly that's a lot of extra I/O and you're probably on a local network with a lot of bandwidth available. What I'm talking about is how you *take* backups. Currently you have to take a full backup which if you have a large data warehouse could be a big job. If only a small percentage of the database is changing then you could use rsync to reduce the network bandwidth to transfer your backup but you still have to read the entire database and write out the entire backup. Incremental backups mean being able to read just the data blocks that have been modified and write out a backup file with just those blocks. When it comes time to restore then you restore the last full backup, then any incremental backups since then, then replay any logs needed to bring it to a consistent state. I think that description pretty much settles the question in my mind. The implementation choice of scanning the WAL to find all the changed blocks is more relevant to the use cases where incremental backups are useful. If you still have to read the entire database then there's not all that much to be gained except storage space. If you scan the WAL then you can avoid reading most of your large data warehouse to generate the incremental and only read the busy portion. In the use case where the database is extremely busy but writing and rewriting the same small number of blocks over and over even scanning the WAL might not be ideal. For that use case it might be more useful to generate a kind of wal-summary which lists all the blocks touched since the last checkpoint every checkpoint. But that could be a later optimization. -- greg -- 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] Synchronous replication - patch status inquiry
On Mon, 2010-09-06 at 16:14 +0300, Heikki Linnakangas wrote: The standby is sending a stream of messages to the master with current LSN positions at the time the message is sent. Given a synchronous transaction, the master would wait until the feedback stream reports that the current transaction is in the past compared to the streamed last known synced one (or the same). That doesn't really answer the question: *when* does standby send back the acknowledgment? I think you should explain when you think this happens in your proposal. Are you saying that you think the standby should send back one message for every transaction? That you do not think we should buffer the return messages? You seem to be proposing a design for responsiveness to a single transaction, not for overall throughput. That's certainly a design choice, but it wouldn't be my recommendation that we did that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 03/09/10 21:50, Tom Lane wrote: Well, in that case what we need to do is presume that the latch object has a continuing existence but the owner/receiver can come and go. I would suggest that InitLatch needs to initialize the object into a valid but unowned state; there is *no* deinitialize operation; and there are AcquireLatch and ReleaseLatch operations to become owner or stop being owner. I think we have just a terminology issue. What you're describing is exactly how it works now, if you just s/InitLatch/AcquireLatch. No, it isn't. What I'm suggesting requires breaking InitLatch into two operations. We also need to define the semantics of SetLatch on an unowned latch --- does this set a signal condition that will be available to the next owner? At the moment, no. Perhaps that would be useful, separating the Init and Acquire operations is needed to make that sane. Exactly. I'm not totally sure either if it would be useful, but the current design makes it impossible to allow that. BTW, on reflection the AcquireLatch/ReleaseLatch terminology seems a bit ill chosen: ReleaseLatch sounds way too much like something that would just unlock or clear the latch. Perhaps OwnLatch/DisownLatch, or something along that line. 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] string function - format function proposal
Itagaki Takahiro itagaki.takah...@gmail.com writes: On Mon, Sep 6, 2010 at 10:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, you need to use the I/O functions. Â Not every type is guaranteed to have a cast to text. One issue is that Pavel want to generate valid SQL statement using %v format. Boolean values are printed as t or f, so the unquoted values are not valid syntax. So? You'd need to quote the values anyway, in general. If you want something that will be valid SQL you'd better include the functionality of quote_literal() in it. If we only use output functions, boolean values should be written as 't' or 'f' (single-quoted), Only numeric values can be unquoted on %v. I'm not sure that it's a good idea to have any type-specific special cases. Failing to quote numeric values will bring in the whole set of issues about how the parser initially types numeric constants, as in the other thread over the weekend. 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] string function - format function proposal
Pavel Stehule pavel.steh...@gmail.com writes: Why I think so this is useful - sometimes people asked some GUC for formatting date, boolean and other. If these functions try to use a cast to text first, then there is some space for customization via custom cast functions. This is basically nonsense. If you don't control a type's output function, you don't control its cast to text either. Nor do I think it's a good idea to encourage people to make their casts to text operate differently from their output functions. We have that one wart in boolean casting because the SQL standard specifies the result of cast to text and it's different from our historical practice in the bool output function --- but it is a wart, not something we should encourage people to emulate. 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] string function - format function proposal
On Mon, Sep 6, 2010 at 11:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: So? You'd need to quote the values anyway, in general. If you want something that will be valid SQL you'd better include the functionality of quote_literal() in it. I'm not sure that it's a good idea to have any type-specific special cases. As I remember, the original motivation of %v formatter is some DBMSes don't like quoted numeric literals. However, Postgres accepts quoted numerics, and we're developing Postgres. So, our consensus would be %v formatter should be removed completely from the format function. -- Itagaki Takahiro -- 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] git: uh-oh
I wrote: Michael Haggerty mhag...@alum.mit.edu writes: On the contrary, I prefer an obvious indication of I don't know to a value that might appear to be authoritative but is really just a guess. It could be that one user copied the file verbatim to the branch and a second user changed the file as part of an unrelated change. Hm, I see. Actually, no I don't see. That sort of history might be possible in some SCMs, but how is it possible in CVS? The only way to get a file into a back branch is cvs add then cvs commit, and the commit is recorded, even if the file exactly matches what was in HEAD. There is an example in contrib/xml2/sql/xml2.sql. It was added to HEAD on 2010-02-28, and then the exact same file was back-patched into 8.4 on 2010-03-01, and the back-patch is visible as a separate action according to http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/xml2/sql/xml2.sql So I don't see why cvs2git has to produce a manufactured commit here. It's also a bit distressing that the manufactured commit bogusly includes a totally unrelated file: commit b36518cb880bb236496ec3e505ede4001ce56157 Author: PostgreSQL Daemon webmas...@postgresql.org Date: Sun Feb 28 21:32:02 2010 + This commit was manufactured by cvs2svn to create branch 'REL8_4_STABLE'. Cherrypick from master 2010-02-28 21:31:57 UTC Tom Lane t...@sss.pgh.pa.us 'Fix up memory management problems in contrib/xml2.': contrib/xml2/expected/xml2.out contrib/xml2/sql/xml2.sql src/bin/pg_dump/po/it.po (This is from the REL8_4_STABLE history in Max's repository.) The cherrypicked commit certainly did not include anything in pg_dump/po/it.po, so what happened here? 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] string function - format function proposal
2010/9/6 Itagaki Takahiro itagaki.takah...@gmail.com: On Mon, Sep 6, 2010 at 11:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: So? You'd need to quote the values anyway, in general. If you want something that will be valid SQL you'd better include the functionality of quote_literal() in it. I'm not sure that it's a good idea to have any type-specific special cases. As I remember, the original motivation of %v formatter is some DBMSes don't like quoted numeric literals. However, Postgres accepts quoted numerics, and we're developing Postgres. So, our consensus would be %v formatter should be removed completely from the format function. I think so tag that quotes all without numbers can be very useful, but it isn't too much important for me. I can live without them. Regards Pavel -- Itagaki Takahiro -- 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] git: uh-oh
Tom Lane wrote: I wrote: Michael Haggerty mhag...@alum.mit.edu writes: On the contrary, I prefer an obvious indication of I don't know to a value that might appear to be authoritative but is really just a guess. It could be that one user copied the file verbatim to the branch and a second user changed the file as part of an unrelated change. Hm, I see. Actually, no I don't see. That sort of history might be possible in some SCMs, but how is it possible in CVS? The only way to get a file into a back branch is cvs add then cvs commit, and the commit is recorded, even if the file exactly matches what was in HEAD. No, it is also possible to use cvs tag -b REL8_4_STABLE filename. In this case the file as it appears on the current branch is added to the specified branch, but CVS records no commit, author, or timestamp. It's also a bit distressing that the manufactured commit bogusly includes a totally unrelated file: commit b36518cb880bb236496ec3e505ede4001ce56157 Author: PostgreSQL Daemon webmas...@postgresql.org Date: Sun Feb 28 21:32:02 2010 + This commit was manufactured by cvs2svn to create branch 'REL8_4_STABLE'. Cherrypick from master 2010-02-28 21:31:57 UTC Tom Lane t...@sss.pgh.pa.us 'Fix up memory management problems in contrib/xml2.': contrib/xml2/expected/xml2.out contrib/xml2/sql/xml2.sql src/bin/pg_dump/po/it.po (This is from the REL8_4_STABLE history in Max's repository.) The cherrypicked commit certainly did not include anything in pg_dump/po/it.po, so what happened here? Given that adding a branch tag to a file leaves behind so little metainformation, cvs2svn has almost no information on which to base its decision of what file branchings to group together. So it groups as many as possible together consistent with the timestamps of the commits preceding and following the branching. Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
On 06/09/10 17:18, Tom Lane wrote:
Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes:
I think we have just a terminology issue. What you're describing is
exactly how it works now, if you just s/InitLatch/AcquireLatch.
No, it isn't. What I'm suggesting requires breaking InitLatch into two
operations.
We also need to define the semantics of SetLatch
on an unowned latch --- does this set a signal condition that will be
available to the next owner?
At the moment, no. Perhaps that would be useful, separating the Init and
Acquire operations is needed to make that sane.
Exactly. I'm not totally sure either if it would be useful, but the
current design makes it impossible to allow that.
Ok, I've split the Init and Acquire steps into two.
BTW, on reflection the AcquireLatch/ReleaseLatch terminology seems a bit
ill chosen: ReleaseLatch sounds way too much like something that would
just unlock or clear the latch. Perhaps OwnLatch/DisownLatch, or
something along that line.
Yeah, I see what you mean. Although, maybe it's just me but Own/Disown
looks ugly. Anyone have a better suggestion?
Here's an updated patch, with all the issues reported this far fixed,
except for that naming issue, and Fujii's suggestion to use poll()
instead of select() where available. I've also polished it quite a bit,
improving comments etc. Magnus, can you take a look at the Windows
implementation to check that it's sane? At least it seems to work.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/configure b/configure
index bd9b347..432cd58 100755
--- a/configure
+++ b/configure
@@ -27773,6 +27773,13 @@ _ACEOF
SHMEM_IMPLEMENTATION=src/backend/port/win32_shmem.c
fi
+# Select latch implementation type.
+if test $PORTNAME != win32; then
+ LATCH_IMPLEMENTATION=src/backend/port/unix_latch.c
+else
+ LATCH_IMPLEMENTATION=src/backend/port/win32_latch.c
+fi
+
# If not set in template file, set bytes to use libc memset()
if test x$MEMSET_LOOP_LIMIT = x ; then
MEMSET_LOOP_LIMIT=1024
@@ -29098,7 +29105,7 @@ fi
ac_config_files=$ac_config_files GNUmakefile src/Makefile.global
-ac_config_links=$ac_config_links src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} src/include/dynloader.h:src/backend/port/dynloader/${template}.h src/include/pg_config_os.h:src/include/port/${template}.h src/Makefile.port:src/makefiles/Makefile.${template}
+ac_config_links=$ac_config_links src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION} src/include/dynloader.h:src/backend/port/dynloader/${template}.h src/include/pg_config_os.h:src/include/port/${template}.h src/Makefile.port:src/makefiles/Makefile.${template}
if test $PORTNAME = win32; then
@@ -29722,6 +29729,7 @@ do
src/backend/port/dynloader.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c ;;
src/backend/port/pg_sema.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION} ;;
src/backend/port/pg_shmem.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION} ;;
+src/backend/port/pg_latch.c) CONFIG_LINKS=$CONFIG_LINKS src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION} ;;
src/include/dynloader.h) CONFIG_LINKS=$CONFIG_LINKS src/include/dynloader.h:src/backend/port/dynloader/${template}.h ;;
src/include/pg_config_os.h) CONFIG_LINKS=$CONFIG_LINKS src/include/pg_config_os.h:src/include/port/${template}.h ;;
src/Makefile.port) CONFIG_LINKS=$CONFIG_LINKS src/Makefile.port:src/makefiles/Makefile.${template} ;;
diff --git a/configure.in b/configure.in
index 7b09986..7f84cea 100644
--- a/configure.in
+++ b/configure.in
@@ -1700,6 +1700,13 @@ else
SHMEM_IMPLEMENTATION=src/backend/port/win32_shmem.c
fi
+# Select latch implementation type.
+if test $PORTNAME != win32; then
+ LATCH_IMPLEMENTATION=src/backend/port/unix_latch.c
+else
+ LATCH_IMPLEMENTATION=src/backend/port/win32_latch.c
+fi
+
# If not set in template file, set bytes to use libc memset()
if test x$MEMSET_LOOP_LIMIT = x ; then
MEMSET_LOOP_LIMIT=1024
@@ -1841,6 +1848,7 @@ AC_CONFIG_LINKS([
src/backend/port/dynloader.c:src/backend/port/dynloader/${template}.c
src/backend/port/pg_sema.c:${SEMA_IMPLEMENTATION}
src/backend/port/pg_shmem.c:${SHMEM_IMPLEMENTATION}
+ src/backend/port/pg_latch.c:${LATCH_IMPLEMENTATION}
src/include/dynloader.h:src/backend/port/dynloader/${template}.h
src/include/pg_config_os.h:src/include/port/${template}.h
src/Makefile.port:src/makefiles/Makefile.${template}
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 615a7fa..094d0c9 100644
---
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Hi, On 09/06/2010 06:27 PM, Heikki Linnakangas wrote: Here's an updated patch, with all the issues reported this far fixed, except for that naming issue, and Fujii's suggestion to use poll() instead of select() where available. I've also polished it quite a bit, improving comments etc. Magnus, can you take a look at the Windows implementation to check that it's sane? At least it seems to work. Is pselect() really as unportable as stated in the patch? What platforms have problems with pselect()? Using the self-pipe trick, don't we risk running into the open file handles limitation? Or is it just two handles per process? Do I understand correctly that the purpose of this patch is to work around the brokenness of select() on very few platforms? Or is there any additional feature that plain signals don't give us? + * It's important to reset the latch*before* checking if there's work to + * do. Otherwise, if someone sets the latch between the check and the + * ResetLatch call, you will miss it and Wait will block. Why doesn't WaitLatch() clear it? What's the use case for waiting for a latch and *not* wanting to reset it? Regards Markus -- 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] 9.1alpha1 bundled -- please verify
On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? -- 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] git: uh-oh
Michael Haggerty mhag...@alum.mit.edu writes: Tom Lane wrote: Actually, no I don't see. That sort of history might be possible in some SCMs, but how is it possible in CVS? The only way to get a file into a back branch is cvs add then cvs commit, and the commit is recorded, even if the file exactly matches what was in HEAD. No, it is also possible to use cvs tag -b REL8_4_STABLE filename. In this case the file as it appears on the current branch is added to the specified branch, but CVS records no commit, author, or timestamp. So, if we're prepared to assert that we've never done that, could we have an option to cvs2git that is willing to use the first commit on a branch to represent the act of adding the file to the branch? 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: OT: OFF TOPIC: [HACKERS] returning multiple result sets from a stored procedure
On Sep 6, 2010, at 12:07 AM, Pavel Stehule wrote: The work on PostgreSQL is adventure, and very good experience, very good school for me. It's job only for people who like programming, who like hacking, it isn't job for people, who go to office on 8 hours. Next I use PostgreSQL for my job - and hacking on PostgreSQL put me a perfect knowledge, perfect contacts to developers, and I can work together with best programmers on planet. and I can create some good things. Probably if I work on commercial projects I can have a better money - but life is only one, and money is important, but not on top for me - life have to be adventure! Could not have said it better myself. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.1alpha1 bundled -- please verify
On Mon, Sep 6, 2010 at 6:54 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? They're built now, and should be on the CDN website tomorrow. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] 9.1alpha1 bundled -- please verify
On mån, 2010-09-06 at 19:38 +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 6:54 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? They're built now, and should be on the CDN website tomorrow. OK, I will send out the announcement tomorrow when I see the installer on the mirrors. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Markus Wanner mar...@bluegap.ch writes: Is pselect() really as unportable as stated in the patch? What platforms have problems with pselect()? Well, it's not defined in the Single Unix Spec, which is our customary reference for portability. Also, it's alleged that some platforms have it but in a form that's not actually any safer than select(). For example, I read in the Darwin man page for it IMPLEMENTATION NOTES The pselect() function is implemented in the C library as a wrapper around select(). and that man page appears to be borrowed verbatim from FreeBSD. Using the self-pipe trick, don't we risk running into the open file handles limitation? Or is it just two handles per process? It's just two handles per process. 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] 9.1alpha1 bundled -- please verify
On Mon, Sep 6, 2010 at 7:41 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2010-09-06 at 19:38 +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 6:54 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? They're built now, and should be on the CDN website tomorrow. OK, I will send out the announcement tomorrow when I see the installer on the mirrors. They'll be here as with previous builds: http://www.enterprisedb.com/products/pgdevdownload.do -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronization levels in SR
Dimitri Fontaine írta: Boszormenyi Zoltan z...@cybertec.at writes: Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? I don't understand why it would be the case (sync holding back async transactions) — it's been proposed that walsender could periodically feed back to the master the current WAL position received, synced and applied. So you can register your sync transaction to wait (and block) until walsender sees a synced WAL position after your own (including it) and another transaction can wait until walsender sees a received WAL position after its own, for example. Of course, meanwhile, any async transaction would just commit without caring about slaves. The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? Not implementing it nor thinking about how to implement it, it seems simple enough :) Regards, -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Synchronization levels in SR
On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: Dimitri Fontaine írta: Boszormenyi Zoltan z...@cybertec.at writes: Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? I don't understand why it would be the case (sync holding back async transactions) — it's been proposed that walsender could periodically feed back to the master the current WAL position received, synced and applied. So you can register your sync transaction to wait (and block) until walsender sees a synced WAL position after your own (including it) and another transaction can wait until walsender sees a received WAL position after its own, for example. Of course, meanwhile, any async transaction would just commit without caring about slaves. The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? It seems your glass in half-empty. Mine is half-full. My perspective would be that if there is contention between async and sync transactions then we will get better throughout than if all transactions were sync. Though perhaps the main issue in that case would be application lock contention, not the speed of synchronous replication. The highest level issue is that the system only has so much physical resources. If we are unable to focus our resources onto the things that matter most then we end up wasting resources. Mixing async and sync transactions on the same server allows a single application to carefully balance performance and durability. Exactly as we do with synchronous_commit. By now, people are beginning to see that synchronous replication is important but has poor performance. Fine grained control is essential to using it effectively in areas that matter most. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
On 09/06/2010 08:46 PM, Tom Lane wrote: Well, it's not defined in the Single Unix Spec, which is our customary reference for portability. FWIW, I bet the self-pipe trick isn't mentioned there, either... any good precedence that it actually works as expected on all of the target platforms? Existing users of the self-pipe trick? (You are certainly aware that pselect() is defined in newer versions of POSIX). Also, it's alleged that some platforms have it but in a form that's not actually any safer than select(). For example, I read in the Darwin man page for it IMPLEMENTATION NOTES The pselect() function is implemented in the C library as a wrapper around select(). Ouch. Indeed, quick googling reveals the following source code for Darwin: http://www.opensource.apple.com/source/Libc/Libc-391.5.22/gen/FreeBSD/pselect.c Now that you are mentioning it, I seem to recall that even glibc had a user-space implementation of pselect. Fortunately, that's quite some years ago. and that man page appears to be borrowed verbatim from FreeBSD. At least FreeBSD seems to have fixed this about 8 months ago: http://svn.freebsd.org/viewvc/base?view=revisionrevision=200725 Maybe Darwin catches up eventually? AFAICT the custom select() implementation we are using for Windows could easily be changed to mimic pselect() instead. Thus most reasonably up-to-date Linux distributions plus Windows certainly provide a workable pselect() syscall. Would it be worth using pselect() for those (and maybe others that support pselect() appropriately)? It's just two handles per process. Good. How about syscall overhead? One more write operation to the self-pipe per signal from within the signal handler and one read to actually clear the 'ready' state of the pipe from the waiter portion of the code, right? Do we plan to replace all (or most) existing internal signals with these latches to circumvent the interruption problem? Or just the ones we need to wait for using pg_usleep()? For Postgres-R, I'd probably have to extend it to call select() not only on the self-pipe, but on at least one other socket as well (to talk to the GCS). As long as that's possible, it looks like a more portable replacement for the pselect() variant that's currently in place there. Regards Markus -- 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] 9.1alpha1 bundled -- please verify
On Mon, Sep 06, 2010 at 08:07:33PM +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 7:41 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2010-09-06 at 19:38 +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 6:54 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? They're built now, and should be on the CDN website tomorrow. OK, I will send out the announcement tomorrow when I see the installer on the mirrors. They'll be here as with previous builds: http://www.enterprisedb.com/products/pgdevdownload.do I hate to mention this at this late date, but it just occurred to me that the ordering is Alpha, then Beta, then Release Candidates, then Release. Did I get that wrong? 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] Synchronization levels in SR
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? It seems your glass in half-empty. Mine is half-full. Simon, you really are failing to advance the conversation. You claim that we can have sync plus async transactions without a performance hit, but you have failed to explain how, at least in any way that anyone else understands. Pontificating about how that will be so much better than not having it doesn't address the problem that others are having with seeing how to implement 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] git: uh-oh
On Mon, Sep 06, 2010 at 05:41:06AM +0200, Michael Haggerty wrote: Tom Lane wrote: Michael Haggerty mhag...@alum.mit.edu writes: CVS does not record when a branch was created or by whom. If a git commit has to be created for such events, cvs2git attributes them to a configurable username, which Max has set to be pgsql. It chooses the latest possible timestamp that is consistent with other (timestamped) changesets that depend on it. Does cvs2cl do something better? If so, how? I suspect what it's doing is attributing the branch creation to the user who makes the first commit on the branch for that file. In general I'd expect that to give a reasonable result --- better than choosing a guaranteed-to-be-wrong constant value anyway ;-) On the contrary, I prefer an obvious indication of I don't know Surely you jest! Databases have no possible way of recording ignorance (other than NULL, that is ;) 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] 9.1alpha1 bundled -- please verify
On Mon, Sep 06, 2010 at 01:11:03PM -0700, David Fetter wrote: On Mon, Sep 06, 2010 at 08:07:33PM +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 7:41 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2010-09-06 at 19:38 +0100, Dave Page wrote: On Mon, Sep 6, 2010 at 6:54 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-09-04 at 18:35 +0100, Dave Page wrote: Announcing on Monday doesn't give us any time to build installers. When are the installers ready? They're built now, and should be on the CDN website tomorrow. OK, I will send out the announcement tomorrow when I see the installer on the mirrors. They'll be here as with previous builds: http://www.enterprisedb.com/products/pgdevdownload.do I hate to mention this at this late date, but it just occurred to me that the ordering is Alpha, then Beta, then Release Candidates, then Release. Did I get that wrong? Oops. Sorry about the noise. I missed the 9.1 part :P Thanks to Erik Rijkers on IRC for pointing this out. 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] Synchronization levels in SR
Simon Riggs írta: On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: Dimitri Fontaine írta: Boszormenyi Zoltan z...@cybertec.at writes: Sorry for answering such an old mail, but what is the purpose of a transaction level synchronous behaviour if async transactions can be held back by a sync transaction? I don't understand why it would be the case (sync holding back async transactions) — it's been proposed that walsender could periodically feed back to the master the current WAL position received, synced and applied. So you can register your sync transaction to wait (and block) until walsender sees a synced WAL position after your own (including it) and another transaction can wait until walsender sees a received WAL position after its own, for example. Of course, meanwhile, any async transaction would just commit without caring about slaves. The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? It seems your glass in half-empty. Mine is half-full. This is good, we can meet halfway. :-) My perspective would be that if there is contention between async and sync transactions then we will get better throughout than if all transactions were sync. Though perhaps the main issue in that case would be application lock contention, not the speed of synchronous replication. The difference we are talking about is: xact1xact2 begin begin lock something lock same (in commit) write wal record wait for sync ack release locks/etc xact2 can proceed from here vs. xact1xact2 begin begin lock something lock same (in commit) write wal record release locks/etc xact2 can proceed from here wait for sync ack In the first case, the contention is obviously increased. With this, we are creating more idle time in the server instead of letting other transactions do their jobs as soon as possible. The second method was implemented in my patch. Are there any drawbacks with this? The highest level issue is that the system only has so much physical resources. If we are unable to focus our resources onto the things that matter most then we end up wasting resources. Mixing async and sync transactions on the same server allows a single application to carefully balance performance and durability. Exactly as we do with synchronous_commit. I don't think this is the same situation. With synchronous_commit, you have an auxiliary process that's handed the job of doing the syncing. But there's nowhere to hand out the waiting for sync ack from the standby. By now, people are beginning to see that synchronous replication is important but has poor performance. Fine grained control is essential to using it effectively in areas that matter most. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Synchronization levels in SR
On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote: (in commit) write wal record release locks/etc xact2 can proceed from here wait for sync ack In the first case, the contention is obviously increased. With this, we are creating more idle time in the server instead of letting other transactions do their jobs as soon as possible. The second method was implemented in my patch. Are there any drawbacks with this? Then I respectfully suggest that you're releasing locks too early. Your proposal would allow a 2nd user to see the results of the 1st user's transaction before the 1st user knew about whether it had committed or not. I know why you want that, but I don't think its right. This has very little, if anything, to do with mixing async/sync connections. You make it sound like all transactions always wait for other transactions, which they definitely don't, especially in reasonably well designed applications. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)
Markus Wanner mar...@bluegap.ch writes: AFAICT the custom select() implementation we are using for Windows could easily be changed to mimic pselect() instead. Thus most reasonably up-to-date Linux distributions plus Windows certainly provide a workable pselect() syscall. Would it be worth using pselect() for those (and maybe others that support pselect() appropriately)? I don't entirely see the point of opening ourselves up to the risk of using a pselect that's not safe under the hood. In any case, on most modern platforms poll() is preferable to any variant of select(). 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] Bug / shortcoming in has_*_privilege
Excerpts from Jim Nasby's message of jue jun 10 17:54:43 -0400 2010: test...@workbook=# select has_table_privilege( 'public', 'test', 'SELECT' ); ERROR: role public does not exist Here's a patch implementing this idea. I'm not too sure about the wording in the doc changes. If somebody wants to propose something better, I'm all ears. To facilitate bikeshedding, here's a relevant extract: has_table_privilege checks whether a user can access a table in a particular way. The user can be specified by name; as public, to indicate the PUBLIC pseudo-role; by OID (pg_authid.oid), or, if the argument is omitted, current_user is assumed. (the first appearance of public is literalpublic/. I had first made it quote but that didn't feel right.) Another thing that could raise eyebrows is that I chose to remove the missing_ok argument from get_role_oid_or_public, so it's not a perfect mirror of it. None of the current callers need it, but perhaps people would like these functions to be consistent. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support public-has-privileges-3.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] Bug / shortcoming in has_*_privilege
Alvaro Herrera alvhe...@commandprompt.com writes: Another thing that could raise eyebrows is that I chose to remove the missing_ok argument from get_role_oid_or_public, so it's not a perfect mirror of it. None of the current callers need it, but perhaps people would like these functions to be consistent. Well, it can't be really consistent anyway: if you did have a missing_ok argument then you'd need an unusual return convention so you could distinguish missing from public. As long as this is a static function I don't see a strong need for it to mimic the API of the general get_whatever_oid functions. 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: OT: OFF TOPIC: [HACKERS] returning multiple result sets from a stored procedure
On Fri, Sep 03, 2010 at 01:40:56PM -0700, John Adams wrote: OT: OFF TOPIC: I honestly do not mean any offence, just out of curiosity. If you guys care about money and time why would you spend the best years of your life basically copying commercial products for free? Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details. It's difficult to answer a question when there are so many different wrong assumptions that underlie it. I'll take pieces of the questions, explicitly state the assumptions that underlie them, and explain what I mean by wrong. If you guys care about money Here you're assuming that open source code development on large projects like PostgreSQL is done in people's spare time. In reality, 80-95% of such development is done by people who are paid by their workplace to do so. In the case of PostgreSQL developers, this pay is at least comfortable, so your assumption that this is done uncompensated, in terms of money, is simply wrong. For those who do development and are not directly compensated by their employer for doing so, there are other monetary rewards, such as being able to put such projects on résumés/CVs, which in turn results in better job prospects, consulting fees for specialized knowledge, etc., etc. and time why would you spend the best years of your life That time's compensated, in many different ways, as illustrated above. Perhaps your life is in such desperate straits that you can devote time to nothing but acquiring money. If this is true, I feel very sorry for you. I feel even sorrier for you if you are not in such desperate straits, but you are nevertheless devoting every waking hour to the pursuit of money. It's a sad and lonely way to waste your precious days of life. basically copying In a technological sense, FLOSS often leads the way and products catch up later if at all. FLOSS technologies are frequently so much better than their proprietary counterparts that they kill existing markets (C compilers, e.g.), and cause markets in other technologies (dynamic languages, e.g.) never to form. commercial products for free? There's a lot of confusion about this word. Commercial means of or pertaining to commerce. It has nothing to do with whether the license is permissive like PostgreSQL's or extremely restrictive as it is with, say the Windows EULA. In future, if you wish to contrast licenses, it's free vs. proprietary, and if you wish to contrast usage, it's hobby vs. commerce vs. science, roughly speaking. Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details. The difference between imagining something and actually accomplishing it is precisely those million stupid details. The truly rewarding thing isn't dreaming up some wonderful dream. That's easy. The truly rewarding thing is in bringing that dream from a lonely and ethereal state to one that's shared and concrete, where it can in turn help spawn new dreams, which people then realize and share, and on and on and on. I just don't follow/understand your thinking. Maybe I am naïve. You're that, clearly, along with being misinformed, young, and arrogant. Fortunately, all of these things but youth are fixable if you decide to do the work to fix them, and by the time you've done that work, your youth will also be waning ;) 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: OT: OFF TOPIC: [HACKERS] returning multiple result sets from a stored procedure
On Fri, Sep 3, 2010 at 4:40 PM, John Adams john_adams_m...@yahoo.com wrote: If you guys care about money and time why would you spend the best years of your life basically copying commercial products for free? I don't work for free. :-) There was a point at which this was just a hobby for me, but as it has since turned into a job, it's hard for me to say that the time I spent on it had no economic value. But it is also true that it was a great hobby. Working on PostgreSQL gave me an opportunity to work with some absolutely brilliant programmers, which is not something I've frequently gotten a chance to do in the course of my previous employment. And it's also fun to feel like you're contributing something back to a project that you've gotten so much out of. With respect to copying commerical products, we may be doing that to some extent, but it's not because we're sitting around going oh, so what has Oracle done lately?. We tend to think about what PostgreSQL needs and work on that. Sometimes there's overlap, other times not. Because for a person with higher than average IQ far less than one percent of any program is creative and needs some thinking and the bulk of it is just a million stupid details. I haven't written a program that matched this expectation since I was in high school. And I think that was only because I wasn't as good a programmer then as I thought I was. My experience is that most programming requires a lot of careful thought and good design, and that doing this well is not easy. This is doubly true for a large, complex, and mature project like PostgreSQL, where changes need to be exceedingly carefully thought out. I just don't follow/understand your thinking. Maybe I am naïve. I do not have experience with open source and I kind of thought open source guys do not need or care about money and time. I try not to make money the center of my life, but I like to get paid as much as the next guy. Many of the regulars here derive a substantial portion of their income from PostgreSQL-related work of one kind or another. Even when my PostgreSQL development was a hobby, a big part of my job revolved around developing FOR PostgreSQL. Filing down some of the rough edges I encountered during that development was one of the things that drew me to the project (the other being the aforementioned really smart people). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronization levels in SR
On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs si...@2ndquadrant.com wrote: Then I respectfully suggest that you're releasing locks too early. Your proposal would allow a 2nd user to see the results of the 1st user's transaction before the 1st user knew about whether it had committed or not. I know why you want that, but I don't think its right. Well that's always possible. The 1st user might just not wake up before the 2nd user gets the response back. The question is what happens if the server crashes and is failed over to the slave. The 2nd user with the async transaction might have seen data commited by the 1st user with his sync transaction but was subsequently lost. Is the user expecting that making his transaction synchronously replicated guarantees that *nobody* can see this data unless the transaction is guaranteed to have been replicated or is he only expecting it to guarantee that *he* can't see the commit until it can be trusted to be replicated? For that matter I'm not entirely clear I understand how the timing here works at all. If transactions can't be considered to be committed before they're acknowledged by the replica what happens if the master crashes after the WAL is written and then comes back without a failover. Then the transaction would be immediately visible even if it still hasn't been replicated yet. I think there's no way with our current infrastructure to guarantee that other transactions can't see your data before it's been replicated, So making any promise otherwise for some cases is only going to be a lie. To guarantee synchronous replication doesn't show data until it's been replicated we would have to some kind of 2-phase commit where we send the commit record to the slave and wait until the slave has received it and confirmed it has written it (but it doesn't replay it unless there's a failover) then write the master's commit record and send the message to the slave that it's safe to replay those records. -- greg -- 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] 9.1alpha1 bundled -- please verify
On Mon, Sep 6, 2010 at 4:29 PM, David Fetter da...@fetter.org wrote: Oops. Sorry about the noise. I missed the 9.1 part :P I was wondering what you were trying to figure out... :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Streaming a base backup from master
On Mon, Sep 6, 2010 at 10:07 AM, Greg Stark gsst...@mit.edu wrote: I think that description pretty much settles the question in my mind. The implementation choice of scanning the WAL to find all the changed blocks is more relevant to the use cases where incremental backups are useful. If you still have to read the entire database then there's not all that much to be gained except storage space. If you scan the WAL then you can avoid reading most of your large data warehouse to generate the incremental and only read the busy portion. If you can scan the WAL, why wouldn't you just replay it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronization levels in SR
On Mon, 2010-09-06 at 23:07 +0100, Greg Stark wrote: On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs si...@2ndquadrant.com wrote: Then I respectfully suggest that you're releasing locks too early. Your proposal would allow a 2nd user to see the results of the 1st user's transaction before the 1st user knew about whether it had committed or not. I know why you want that, but I don't think its right. Well that's always possible. The 1st user might just not wake up before the 2nd user gets the response back. The question is what happens if the server crashes and is failed over to the slave. The 2nd user with the async transaction might have seen data commited by the 1st user with his sync transaction but was subsequently lost. Is the user expecting that making his transaction synchronously replicated guarantees that *nobody* can see this data unless the transaction is guaranteed to have been replicated or is he only expecting it to guarantee that *he* can't see the commit until it can be trusted to be replicated? For that matter I'm not entirely clear I understand how the timing here works at all. If transactions can't be considered to be committed before they're acknowledged by the replica what happens if the master crashes after the WAL is written and then comes back without a failover. Then the transaction would be immediately visible even if it still hasn't been replicated yet. I think there's no way with our current infrastructure to guarantee that other transactions can't see your data before it's been replicated, So making any promise otherwise for some cases is only going to be a lie. To guarantee synchronous replication doesn't show data until it's been replicated we would have to some kind of 2-phase commit where we send the commit record to the slave and wait until the slave has received it and confirmed it has written it (but it doesn't replay it unless there's a failover) then write the master's commit record and send the message to the slave that it's safe to replay those records. Just to add that this part of the discussion has nothing at all to do with my proposal for master controlled replication. Zoltan is simply discussing when the wait should occur with sync replication. I have no proposal to vary that myself, wherever we eventually decide the wait should occur. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Mon, 2010-09-06 at 16:11 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote: The locks held by a transaction are released after RecordTransactionCommit(), and waiting for the sync ack happens in this function. Now what happens when a sync transaction hold a lock that an async one is waiting for? It seems your glass in half-empty. Mine is half-full. Simon, you really are failing to advance the conversation. You claim that we can have sync plus async transactions without a performance hit, but you have failed to explain how, at least in any way that anyone else understands. Pontificating about how that will be so much better than not having it doesn't address the problem that others are having with seeing how to implement it. A performance hit from mixing sync and async is unlikely. The overhead of deciding whether to wait after commit is trivial. At worst, the async transactions would go at the same speed as the sync transactions, especially if the application contends with itself, which is by no means a certainty. If acting independently, the async transactions would clearly go much faster. So the right question for discussion is how much will we gain by mixing async/sync?. Since we had exactly this situation for synchronous_commit and a similar discussion, I expect a similar eventual outcome. The discussion would go better if we had clear performance results published from existing work and we did not dissuade people from objective testing. Then you'd probably understand why I think this is so important to me. I've explained more than once how my proposal can work and Dimitri at least appears to have understood with zero off-list conversation. So far the discussion has been mostly negative and the reasons given haven't scored high on logic, I'm sorry to say. I will present a code-based proposal rather than just huge piles of words, to make this a more concrete discussion. -- 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] Rewrite, normal execution vs. EXPLAIN ANALYZE
On 2010-08-31 12:07 AM +0300, I wrote:
The patch needs a bit more comments and some cleaning up ..
Here's a cleaned up version with a bit more comments.
This patch still silently breaks pg_parse_and_rewrite(). We only use it
in our source code for SQL-language functions, so I think we should
deprecate it in favor of a function which would do the right thing for
SQL functions. Thoughts?
Regards,
Marko Tiikkaja
*** a/src/backend/catalog/pg_proc.c
--- b/src/backend/catalog/pg_proc.c
***
*** 832,838 fmgr_sql_validator(PG_FUNCTION_ARGS)
proc-proargtypes.values,
proc-pronargs);
(void) check_sql_fn_retval(funcoid, proc-prorettype,
!
querytree_list,
NULL, NULL);
}
else
--- 832,838
proc-proargtypes.values,
proc-pronargs);
(void) check_sql_fn_retval(funcoid, proc-prorettype,
!
llast(querytree_list),
NULL, NULL);
}
else
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
***
*** 90,107 typedef struct
ParamListInfo paramLI; /* Param list representing current args
*/
Tuplestorestate *tstore;/* where we accumulate result tuples */
JunkFilter *junkFilter; /* will be NULL if function returns
VOID */
! /* head of linked list of execution_state records */
! execution_state *func_state;
} SQLFunctionCache;
typedef SQLFunctionCache *SQLFunctionCachePtr;
/* non-export function prototypes */
! static execution_state *init_execution_state(List *queryTree_list,
SQLFunctionCachePtr fcache,
bool lazyEvalOK);
static void init_sql_fcache(FmgrInfo *finfo, bool lazyEvalOK);
--- 90,107
ParamListInfo paramLI; /* Param list representing current args
*/
Tuplestorestate *tstore;/* where we accumulate result tuples */
+ Snapshotsnapshot;
JunkFilter *junkFilter; /* will be NULL if function returns
VOID */
! List *func_state;
} SQLFunctionCache;
typedef SQLFunctionCache *SQLFunctionCachePtr;
/* non-export function prototypes */
! static List *init_execution_state(List *queryTree_list,
SQLFunctionCachePtr fcache,
bool lazyEvalOK);
static void init_sql_fcache(FmgrInfo *finfo, bool lazyEvalOK);
***
*** 123,183 static void sqlfunction_destroy(DestReceiver *self);
/* Set up the list of per-query execution_state records for a SQL function */
! static execution_state *
init_execution_state(List *queryTree_list,
SQLFunctionCachePtr fcache,
bool lazyEvalOK)
{
! execution_state *firstes = NULL;
! execution_state *preves = NULL;
execution_state *lasttages = NULL;
! ListCell *qtl_item;
! foreach(qtl_item, queryTree_list)
{
! Query *queryTree = (Query *) lfirst(qtl_item);
! Node *stmt;
! execution_state *newes;
! Assert(IsA(queryTree, Query));
! if (queryTree-commandType == CMD_UTILITY)
! stmt = queryTree-utilityStmt;
! else
! stmt = (Node *) pg_plan_query(queryTree, 0, NULL);
! /* Precheck all commands for validity in a function */
! if (IsA(stmt, TransactionStmt))
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! /* translator: %s is a SQL statement name */
!errmsg(%s is not allowed in a SQL
function,
!
CreateCommandTag(stmt;
! if (fcache-readonly_func !CommandIsReadOnly(stmt))
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! /* translator: %s is a SQL statement name */
!
Re: [HACKERS] Synchronous replication - patch status inquiry
On Mon, Sep 6, 2010 at 10:14 AM, Simon Riggs si...@2ndquadrant.com wrote: That doesn't really answer the question: *when* does standby send back the acknowledgment? I think you should explain when you think this happens in your proposal. Are you saying that you think the standby should send back one message for every transaction? That you do not think we should buffer the return messages? That's certainly what I was assuming - I can't speak for anyone else, of course. You seem to be proposing a design for responsiveness to a single transaction, not for overall throughput. That's certainly a design choice, but it wouldn't be my recommendation that we did that. Gee, I thought that if we tried to buffer the messages, you'd end up *reducing* overall throughput. Suppose we have a busy system. The number of simultaneous transactions in flight is limited by max_connections. So it seems to me that if each transaction takes X% longer to commit, then throughput will be reduced by X%. And as you've said, batching responses will make individual transactions less responsive. The corresponding advantage of batching the responses is that you reduce consumption of network bandwidth, but I don't think that's normally where the bottleneck will be. Of course, you might be able to opportunistically combine messages, if additional transactions become ready to acknowledge after the first one has become ready but before the acknowledgement has actually been sent. But waiting to try to increase the batch size doesn't seem right. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Synchronization levels in SR
On Mon, Sep 6, 2010 at 5:02 PM, Simon Riggs si...@2ndquadrant.com wrote: Then I respectfully suggest that you're releasing locks too early. Your proposal would allow a 2nd user to see the results of the 1st user's transaction before the 1st user knew about whether it had committed or not. Marking the transaction committed in CLOG will have that effect anyway. We are not doing strict two-phase locking. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] knngist - 0.8
2010/7/22 Teodor Sigaev teo...@sigaev.ru: http://www.sigaev.ru/misc/builtin_knngist_core-0.8.gz http://www.sigaev.ru/misc/builtin_knngist_itself-0.8.gz http://www.sigaev.ru/misc/builtin_knngist_proc-0.8.gz http://www.sigaev.ru/misc/builtin_knngist_contrib_pg_trgm-0.8.gz http://www.sigaev.ru/misc/builtin_knngist_contrib_btree_gist-0.8.gz Changes: * pg_amop has boolean column amoporder which points to clause's usage of operator * Syntax of CREATE OPERATOR CLASS CREATE OPERATOR CLASS ... [ORDER] OPERATOR ORDER OPERATOR is marked with pg_amop.amoporder = true * Bool-returning operator could not be used as ORDER OPERATOR, but type of returning value still should have a default Btree operator class. * Added flag SK_ORDER to SkanKey flag to indicate order operation, so access methods (only GiST right now) should check this flag (in previous versions of patch GiST checked returned value of operator's function) AFAICS, these patches include no documentation. That's pretty much a fatal flaw for a feature of this magnitude. At an absolute minimum, you need to update the system catalog documentation and the documentation on CREATE / ALTER OPERATOR CLASS. There might be some other places that need to be touched, also. + if (opform-oprresult == BOOLOID) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), +errmsg(index ordering operators must not return boolean))); My first thought was that this code was there to prevent people from doing the wrong thing by accident. But I have a niggling feeling that you're actually relying on this for the correctness of the system. I hope I'm wrong, because I don't think that would be a very good idea. The GIST code code use more comments; and perhaps the names of some of the functions and structures could be chosen to be more descriptive. I think that what used to be called GISTSearchStack has apparently been replaced with DataPointer; it's not obvious to me that it's good to change the name, but if it is I don't think DataPointer is a good choice. gistindex_keytest has been replaced (sort of) by processIndexTuple, which again seems more generic than what it replaced. Minor nit: the word shoould is mis-spelled. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Triggers on VIEWs
On Sep 5, 2010, at 3:09 AM, Dean Rasheed wrote: On 15 August 2010 18:38, Dean Rasheed dean.a.rash...@gmail.com wrote: Here is a WIP patch implementing triggers on VIEWs ... snip There are still a number of things left todo: - extend ALTER VIEW with enable/disable trigger commands - much more testing - documentation Attached is an updated patch with more tests and docs, and a few minor At least for me, there are some portions of the docs which could use some formatting changes in order to not be confusing grammatically; e.g., this was confusing to me on the first read: -trigger name. In the case of before triggers, the +trigger name. In the case of before and instead of triggers, the I realize this lack of formatting was inherited from the existing docs, but this would make more sense to me if this (and presumably the other related instances of before and after) were set apart with literal/ or similar. This is already in use in some places in this patch, so seems like the correct markup. Regards, David -- David Christensen End Point Corporation da...@endpoint.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] update on global temporary and unlogged tables
I haven't had a chance to do a great deal of work on this project, but I'm hoping to get back to it at some point and, in the meantime, thought that it might be useful to circulate a few thoughts I've had so far. 1. As common architecture for both features, I think that it might make sense to replace the existing relistemp (bool) field with a relpersistence (char) field. The current tests against rel-rd_istemp can be replaced with macros testing the exact property we really care about in that situation. I'm thinking of RelationNeedsWAL(), RelationUsesLocalBuffers(), and RelationUsesTempNamespace(). 2. With respect to global temporary tables, I've hit a fairly serious roadblock in the form of relfrozenxid. For a permanent table, VACUUM by any backend can advance relfrozenxid; for a backend-local temporary table, VACUUM by the owning backend can advance relfrozenxid. But for a global temporary table, the proper value for relfrozenxid is the earliest value for any backend that has stored tuples into the table. I'm not immediately sure what to do about this. Tom's previous suggestion of cloning the catalog entries for each backend that tries to access the table is one possible alternative, but I don't like that much for reasons previously discussed. Incidentally, per some previous discussion, I took a look at what Oracle does with respect to DDL on global temp tables, and I gather that they allow it if no session has bound (instantiated?) the table. Maybe some infrastructure along those lines would be useful in dealing with the VACUUM problem also; not sure. 3. With respect to unlogged tables, the major obstacle seems to be figuring out a way for these to get automatically truncated at startup time. As with temporary table cleanup in general, the problem here is that you can't do the obvious thing of iterating through pg_class and truncating each unlogged table you find without greatly complicating the startup sequence. However, I think there's a fairly easy way around this problem: truncating a table basically means removing all segments and relation forks other than the first segment of the main fork, and truncating that one to zero length. So we could do it the same way we clean up temporary files - namely, based on the file name - if we made the filenames for unlogged tables distinguishable from those for regular and temporary tables. What I'm thinking about is reserving a backend ID of -2 for this purpose via some suitable constant definition, just as -1 (InvalidBackendId) represents a permanent table in this context. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
