Re: [HACKERS] Serializable Isolation without blocking
Kevin Grittner wrote: I still haven't actually read the paper so I should probably bow out from the conversation until I do. I was apparently already under one misapprehension as Laurenz just claimed the paper does not show how to prevent phantoms (phantom reads I assume?). Perhaps it's not as ambitious as achieving true serializability after all? It does achieve true serializability in terms of the definitions I've read, although I've discovered at least one way in which its guarantees aren't as strong as traditional blocking techniques -- it doesn't guarantee that transactions at a level less strict than serializable will see a state which would exist between some serial execution of serializable transactions which modify the data, as the blocking schemes do. I still don't buy that this implementation guarantees serializability. All the authors show with regard to predicate handling is handwaving, and while you tried to come up with ideas how that could be improved that is not what the implementation described in the paper does. So this paper shows a performant implementation of something that is closer to serializability than snapshot isolation, but did not go all the way. As I said, I think it is promising, and it can only be hoped that the authors pursue the path they have taken and share their experiences with an implementation of full serializability with their technique. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
On Friday 08 May 2009 22:03:56 Tom Lane wrote: I hesitate though to suggest that we think about porting ourselves to NSS --- I'm not sure that there would be benefits to us within the context of Postgres alone. That could be attractive if we ripped out the OpenSSL code at the same time, as the NSS API is purportedly more abstract and presumably would reduce the amount and the complexity of the code. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
Peter Eisentraut wrote: On Friday 08 May 2009 22:03:56 Tom Lane wrote: I hesitate though to suggest that we think about porting ourselves to NSS --- I'm not sure that there would be benefits to us within the context of Postgres alone. That could be attractive if we ripped out the OpenSSL code at the same time, as the NSS API is purportedly more abstract and presumably would reduce the amount and the complexity of the code. Is NSS available on all the platforms that we are (and that has OpenSSL today)? Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... But no, that certainly wouldn't *reduce* the amount of code... //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cert chains patch
Andrew Gierth wrote: Magnus asked me for this, when the subject came up on IRC. This is a longstanding ignored issue, for example http://archives.postgresql.org/message-id/slrnemslp5.2rcr.andrew+non...@atlantis.supernews.net http://archives.postgresql.org/message-id/15d55918-fa9c-4e6a-ba15-bdc9142a6...@contegix.com Applied, thanks! -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
Seth Robertson wrote: In message 14727.1241816...@sss.pgh.pa.us, Tom Lane writes: It is of course possible to support both at the same time (at compile-time, if nowhere else). Yes, I suppose we'd not wish to just drop openssl completely. I wonder how much code duplication would ensue from a compile-time choice of which library to use ... My only datapoint for you is curl, which is an application I happen to have discovered that can use either NSS and OpenSSL. Lines Words Chars Filename 2508 7890 74682 ssluse.c 1331 3708 36411 nss.c IIRC, they also support gnutls. So we can probably get hints there about how to get this support if we want to :-) I imagine that you would more or less have to provide a different be-secure.c and fe-secure.c file for the two different libraries--whether as a separate file or via #ifdefs. It looks like there is a small amount of common code present (why *is* pg_block_sigpipe() in that file anyway?) Clearly this would be a good time to fix such abstraction errors if we decide to go ahead :-) -- Magnus Hagander Self: 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
[HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello everybody, i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. i would like to add this functionality to PostgreSQL 8.5. the oracle syntax is quite clear and easy to use here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2126016 informix should behave pretty much the same way. are there any arguments from hackers' side against this feature? many thanks, hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Greg On 11 May 2009, at 10:26, Hans-Juergen Schoenig postg...@cybertec.at wrote: hello everybody, i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. i would like to add this functionality to PostgreSQL 8.5. the oracle syntax is quite clear and easy to use here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2126016 informix should behave pretty much the same way. are there any arguments from hackers' side against this feature? many thanks, hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bytea vs. pg_dump
--On Mittwoch, Mai 06, 2009 19:04:21 -0400 Tom Lane t...@sss.pgh.pa.us wrote: So I'm now persuaded that a better textual representation for bytea should indeed make things noticeably better here. It would be useful though to cross-check this thought by profiling a case that dumps a comparable volume of text data that contains no backslashes... I'm going to try to create a profile with a converted text representation of the data. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello greg, the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; this practically means 3 commands. the killer argument, however, is that the lock might very well happen ways after the statement has started. imagine something like that (theoretical example): SELECT ... FROM WHERE x ( SELECT some_very_long_thing) FOR UPDATE ...; some operation could run for ages without ever taking a single, relevant lock here. so, you don't really get the same thing with statement_timeout. regards, hans Greg Stark wrote: Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Hans-Juergen Schoenig postg...@cybertec.at the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; Why not extend the SET instruction to allow configuration parameters to be set only in the duration of the transaction or the next n commands? -- Lucas Brito
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
--On 11. Mai 2009 06:38:44 -0300 Lucas Brito luca...@gmail.com wrote: Why not extend the SET instruction to allow configuration parameters to be set only in the duration of the transaction or the next n commands? It's already there: see SET LOCAL. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
-- Greg On 11 May 2009, at 11:18, Hans-Juergen Schoenig postg...@cybertec.at wrote: hello greg, the thing with statement_timeout is a little bit of an issue. you could do: SET statement_timeout TO ...; SELECT FOR UPDATE ... SET statement_timeout TO default; this practically means 3 commands. I tend to think there should be protocol level support for options like this but that would require buy-in from the interface writers. the killer argument, however, is that the lock might very well happen ways after the statement has started. Sure. But Isn't the statement_timeout behaviour what an application writer would actually want? Why would he care how long some sub-part of the statement took? Isn't an application -you used the example of a web app - really concerned with its response time? imagine something like that (theoretical example): SELECT ... FROM WHERE x ( SELECT some_very_long_thing) FOR UPDATE ...; some operation could run for ages without ever taking a single, relevant lock here. so, you don't really get the same thing with statement_timeout. regards, hans Greg Stark wrote: Can't you to this today with statement_timeout? Surely you do want to rollback the whole transaction or at least the subtransaction if you have error handling. -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
I tend to think there should be protocol level support for options like this but that would require buy-in from the interface writers. how would you do it? if you support it on the protocol level, you still need a way to allow the user to tell you how ... i would see WAIT for DELETE, UPDATE and SELECT FOR UPDATE. did you have more in mind? the killer argument, however, is that the lock might very well happen ways after the statement has started. Sure. But Isn't the statement_timeout behaviour what an application writer would actually want? Why would he care how long some sub-part of the statement took? Isn't an application -you used the example of a web app - really concerned with its response time? no, for a simple reason: in this case you would depend ways too much in other tasks. some other reads which just pump up the load or some nightly cronjobs would give you timeouts which are not necessarily related to locking. we really want to protect us against some LOCK TABLE IN ACCESS EXCLUSIVE MODE - i am not looking for a solution which kills queries after some time (we have that already). i want protect myself against locking issues. this feature is basically supported by most big vendor (informix, oracle, just to name a few). i am proposing this because i have needed it for a long time already and in this case it is also needed for a migration project. hans -- Cybertec Schönig Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator alpha 5 - truncates at 10 M rows
Tom Lane wrote: Erik Rijkers e...@xs4all.nl writes: On Sun, May 10, 2009 02:05, Alvaro Herrera wrote: I'm wondering that it could have forgotten to migrate the later table segments ... It seems al 'truncated' tables give pg_relation_size(oid) = 1073741824 Looks like Alvaro nailed it. Bruce, you reading? Yes, will research; thanks. I know we have multi-segment code, but obviously it is not working reliably. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgresql Developer
We are looking for an experienced Postgresql DBA willing to travel and provide short and tailored Postgresql training as well as develop an interface in VB or VB.NET for data capture and query postgresql database. The application shall be client server. Immediate, 10 days assignment all costs covered. Please contact Dunia Ramazani, Ph.D. at dunia.ramaz...@gmail.com -- Dunia Ramazani, Ph.D. 2093 de la Victoire, Laval (Quebec) Canada H7M 3E7 +14509758994 dunia.ramaz...@gmail.com
Re: [HACKERS] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at wrote: All the authors show with regard to predicate handling is handwaving, That is because predicate locking is a mature technology with many known implementations. The best technique for any database product will depend on that product, and their technique doesn't depend on which implementation is used. Assuming some form of predicate locking, do you have any other qualms about the the algorithm presented in the paper? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Mon, May 11, 2009 at 2:49 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Albe Laurenz laurenz.a...@wien.gv.at wrote: All the authors show with regard to predicate handling is handwaving, That is because predicate locking is a mature technology with many known implementations. The best technique for any database product will depend on that product, and their technique doesn't depend on which implementation is used. Assuming some form of predicate locking, do you have any other qualms about the the algorithm presented in the paper? I thought the big problem with providing true serializability was the predicate locking. If it doesn't address that need then does this get us any closer? Is this like saying walls are a well understood technology so these antilock brakes work great for stopping your car as long as you combine them with a wall? :) -- 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] Serializable Isolation without blocking
Kevin Grittner wrote: All the authors show with regard to predicate handling is handwaving, That is because predicate locking is a mature technology with many known implementations. The best technique for any database product will depend on that product, and their technique doesn't depend on which implementation is used. Assuming some form of predicate locking, do you have any other qualms about the the algorithm presented in the paper? No - given that the algorithm is correct (which the authors cite from another paper which I cannot easily access). In my first reply I wondered if the presence of concurrent read committed transactions would somehow affect the correctness of the algorithm, as the authors don't mention that. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Greg Stark st...@enterprisedb.com wrote: I thought the big problem with providing true serializability was the predicate locking. If it doesn't address that need then does this get us any closer? I thought the big problem was the perception that performance would suffer and that the level of blocking required would be unacceptable. This technique (based on available benchmarks from the prototype implementation) seems to give performance very close to snapshot isolation with no additional blocking beyond what snapshot isolation already has to support first committer wins update conflict detection. Benchmarks showed much better performance than traditional blocking techniques for achieving serializability. Since it can markedly increase serialization failure rollbacks, the software needs to be able to respond to those gracefully, but since our framework automatically re-submits transactions which are terminated with that SQLSTATE, this approach sound very useful for us. Is this like saying walls are a well understood technology so these antilock brakes work great for stopping your car as long as you combine them with a wall? :) I see it more like saying that walls are a well understood technology, and this is a proposal for a way to use them in putting up a particular useful building. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Mon, May 11, 2009 at 3:11 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Greg Stark st...@enterprisedb.com wrote: I thought the big problem with providing true serializability was the predicate locking. If it doesn't address that need then does this get us any closer? I thought the big problem was the perception that performance would suffer and that the level of blocking required would be unacceptable. This thread has really been one of those cases where everyone thought they were having a different kind of discussion. If predicate locking is so well understood and if someone who understands it and understands what kind of implementation would work well in Postgres steps forward with an implementation which doesn't cause major downsides then I suspect we might revisit our prejudices against it. But as it stands I think the assumption is that having to maintain locks on hypothetical records which don't exist would be an expensive cost to impose on every query which would unduly impact performance. I, for one, certainly assumed if we did anything like that it would work like our existing locks in that it wouldn't impose any additional blocking. If there was any question of that then it sounds like this paper might be a step forward in that you're on-side at least on that question now? -- 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] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at wrote: In my first reply I wondered if the presence of concurrent read committed transactions would somehow affect the correctness of the algorithm, as the authors don't mention that. Yeah, I was concerned about that, too. In thinking it through I've convinced myself that there is a choice in implementation, which seems to have a pretty obvious winner. (1) If READ COMMITTED and SNAPSHOT isolation levels don't change at all, there would be a behavioral difference between this technique and strict two phase locking (S2PL) implementations of serializable transactions. With S2PL, even READ COMMITTED transactions can only view the database in a state which is consistent with some serial application of SERIALIZABLE transactions. Under the algorithm from this paper, without changes to other isolation levels, if you want to view the database in a coherent state relative to SERIALIZABLE transactions, you must use a SERIALIZABLE transaction. (2) Promote everything to SERIALIZABLE by having all transactions, regardless of isolation level, take out SIREAD locks and check for unsafe access patterns. This would, strictly speaking, conform to the SQL standard, because an implementation is free to promote requests for any level of isolation to a more strict level; however, it hardly seems useful. So, I think the only sane thing to do in this regard would be to document that there is a difference from blocking implementations of SERIALIZABLE in the guarantees provided for non-serializable transactions. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cert chains patch
Magnus Hagander wrote: Andrew Gierth wrote: Magnus asked me for this, when the subject came up on IRC. This is a longstanding ignored issue, for example http://archives.postgresql.org/message-id/slrnemslp5.2rcr.andrew+non...@atlantis.supernews.net http://archives.postgresql.org/message-id/15d55918-fa9c-4e6a-ba15-bdc9142a6...@contegix.com Applied, thanks! Shouldn't this be backpatched? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
Magnus Hagander wrote: Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... If we were to support multiple libraries, would they be selected at run time or compile time? If only compile time, how would it work for the Windows installer with the SChannel thingy --- would they have to distribute two separate packages, for OpenSSL and SChannel? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. 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] SSL cert chains patch
Alvaro Herrera alvhe...@commandprompt.com writes: Magnus Hagander wrote: Applied, thanks! Shouldn't this be backpatched? It looks like a feature change to me ... 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] Serializable Isolation without blocking
Greg Stark st...@enterprisedb.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Greg Stark st...@enterprisedb.com wrote: I thought the big problem with providing true serializability was the predicate locking. If it doesn't address that need then does this get us any closer? I thought the big problem was the perception that performance would suffer and that the level of blocking required would be unacceptable. This thread has really been one of those cases where everyone thought they were having a different kind of discussion. Apparently so. If predicate locking is so well understood and if someone who understands it and understands what kind of implementation would work well in Postgres steps forward with an implementation which doesn't cause major downsides then I suspect we might revisit our prejudices against it. But as it stands I think the assumption is that having to maintain locks on hypothetical records which don't exist would be an expensive cost to impose on every query which would unduly impact performance. It would only impact transactions running at the full serializable isolation level, and I'm guessing that the performance would be reasonable if an implementation similar to that in DB2, Sybase, Microsoft SQL Server, etc. is used. Some here have derided that approach as crude and implied that only something more aesthetically pleasing would be considered, but that such implementations would be prohibitively slow (which, of course, is exactly why they are not used in these other products). I, for one, certainly assumed if we did anything like that it would work like our existing locks in that it wouldn't impose any additional blocking. Until this paper, implementation of serializable transactions, even in an MVCC database required S2PL techniques which caused a lot of blocking, including readers blocking on writes and vice versa. The advance of this paper isn't any novel implementation of predicate locking, but the reduction of the locks generated by reads to a new SIREAD level lock which would not introduce any blocking; but instead would assist in the detection of unsafe patterns of reads and writes to allow rollbacks to prevent serialization anomalies. If there was any question of that then it sounds like this paper might be a step forward in that you're on-side at least on that question now? I was never on the other side of that. I know that some apparently thought that my attempts to document PostgreSQL's deviation from current standards in this regard, and to provide more real-world examples of where people might run into trouble, were really sly attempts to persuade people to implement full support for serializable transactions. That really wasn't the case. We had been slightly burned by the differences in spite of my having read the current documentation, because the example given is so far-fetched and bizarre, that I rather naively thought Well, if that's how far out you have to go to hit a problem, the risk is quite low. I was trying to find something which gave people a clearer picture of the issue, so others didn't make the same mistake. I wasn't advocating for full serializable support at that point, and probably would have been reluctant to use it if available because of the performance issues (assuming a traditional implementation). In the course of discussing the issue, this paper, published by ACM earlier in the year, was brought to my attention. I see it as the best of both worlds -- MVCC performance with the protections of serializable transactions. Back when I first read the paper, though, it looked to be a struggle to get 8.4 to beta testing, so I sat on it until now. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL cert chains patch
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Magnus Hagander wrote: Applied, thanks! Shouldn't this be backpatched? It looks like a feature change to me ... Yup, I think so too. It changes the behavior if you have such a file. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
In message 4a07db89.2080...@hagander.net, Magnus Hagander writes: Is NSS available on all the platforms that we are (and that has OpenSSL today)? NSS stopped publishing their supported platform list for NSS for some strange reasons (older version have it). But I'd probably assume that the list includes AIX, Tru64, HPUX, Linux, Windows, Solaris, Mac OSX. I specifically don't see signs that they attempt to support the *BSD platforms, but the *BSD people have working ports. Generally from almost dozens of minutes of googling, I'd have to say that in terms of portability and declared actual ports: OpenSSL PostgreSQL Mozilla NSS GnuTLS GnuTLS doesn't seem to be as mature as either OpenSSL and Mozilla NSS, at least in my current hot-button issue of client certificate validation. Good luck with that Windows SChannel thing...I didn't find any opensource program which uses it. -Seth Robertson in-pgsql-hack...@baka.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
In message 20090511144317.gc8...@alvh.no-ip.org, Alvaro Herrera writes: Magnus Hagander wrote: Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... If we were to support multiple libraries, would they be selected at run time or compile time? If only compile time, how would it work for the Windows installer with the SChannel thingy --- would they have to distribute two separate packages, for OpenSSL and SChannel? While I have successfully performed runtime conditional dynamic loading inside programs (each shared library with its own list of dependent libraries) on one platform with one selected dynamic loading API, I cannot say I recommend it. This would aid neither portability, debug-ability, or performance (though compared to the overhead of SSL, the jump table is kinda irrelevant). -Seth Robertson in-pgsql-hack...@baka.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DROP TABLE vs inheritance
There was just another complaint about something we've heard about before, namely that dropping a child table doesn't interact nicely with queries concurrently accessing the parent table: http://archives.postgresql.org/pgsql-bugs/2009-05/msg00113.php As I responded there, this isn't fixable by the obvious method of making DROP TABLE try to lock the parent too. On reflection though it seems that there is a reasonably simple solution: we could make find_inheritance_children() and find_all_inheritors() acquire lock on each child table as they scan pg_inherits, and do try_relation_open() or equivalent to see if the child still exists. If not, assume the table just got dropped and ignore the pg_inherits entry. This would require an API change to let the callers tell them what type of lock they intend to acquire on each table, but overall it shouldn't result in any visible change in query behavior in normal cases --- we're just acquiring relation locks a bit earlier than we did before. The only arguable downside I can see is that if pg_inherits happens to contain a corrupt row with a bad child OID, you'd never hear about it. But that doesn't seem like a big problem. Since 8.4 already contains a number of changes designed to make concurrent-DROP scenarios work more safely than before, I'm strongly tempted to sneak this change into 8.4. Thoughts? 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] [PATCH] Automatic client certificate selection support for libpq v1
On Monday 11 May 2009 11:02:17 Magnus Hagander wrote: Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... But no, that certainly wouldn't *reduce* the amount of code... We'll call that Plan C: Making PostgreSQL the first piece of software in the world to support four different crypto libraries. ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Automatic client certificate selection support for libpq v1
Peter Eisentraut wrote: On Monday 11 May 2009 11:02:17 Magnus Hagander wrote: Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... But no, that certainly wouldn't *reduce* the amount of code... We'll call that Plan C: Making PostgreSQL the first piece of software in the world to support four different crypto libraries. ;-) I could've sworn curl did :-) But it turns out they do SChannel *through* OpenSSL. :-) So we can probably live with that ;) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan z...@cybertec.at writes: Would the lock_timeout work for all to be acquired locks individually, or all of them combined for the statement? The individual application of the timeout for every locks individually wouldn't be too nice. I think the way you're describing would be both harder to implement and full of its own strange traps. 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom Lane írta:
Boszormenyi Zoltan z...@cybertec.at writes:
Would the lock_timeout work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.
I think the way you're describing would be both harder to implement
and full of its own strange traps.
Why?
PGSemaphoreTimedLock(..., struct timespec *timeout)
{
...
gettimeofday(tv1, NULL);
semtimedop(... , timeout);
gettimeofday(tv2, NULL);
decrease *timeout with the difference of tv1 and tv2
}
Next call will use the decreased value.
Either all locks are acquired in the given time, or the next try will
timeout (error) or there are still locks and the timeout went down to
or below zero (error). Why is it hard?
regards, tom lane
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: I think the way you're describing would be both harder to implement and full of its own strange traps. Why? Well, for one thing: if I roll back a subtransaction, should the lock wait time it used now no longer count against the total? If not, once a timeout failure has occurred it'll no longer be possible for the total transaction to do anything, even if it rolls back a failed subtransaction. But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Hi, Tom Lane írta: Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. Would the lock_timeout work for all to be acquired locks individually, or all of them combined for the statement? The individual application of the timeout for every locks individually wouldn't be too nice. E.g. SELECT ... FOR ... WAIT N (N in seconds) behaviour in this scenario below is not what the application writed would expect: xact 1: SELECT ... FOR UPDATE (record 1) xact 2: SELECT ... FOR UPDATE (record 2) xact 3: SELECT ... FOR UPDATE WAIT 10 (record 1 and 2, waits for both records sequentially) xact 1: COMMIT/ROLLBACK almost 10 seconds later xact 3 acquires lock for record 1, wait for lock on record2 xact 2: COMMIT/ROLLBACK almost 10 seconds later xact 3 acquires lock for record 2 3rd transaction has to wait for almost 2 times the specified time. E.g. in Informix the SET LOCK MODE TO WAIT N works for all to-be acquired locks combined. If lock_timeout and/or ... FOR lockmode WAIT N ever gets implemented, it should behave that way. Best regards, Zoltán Böszörményi regards, tom lane -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom Lane írta: Boszormenyi Zoltan z...@cybertec.at writes: Tom Lane írta: I think the way you're describing would be both harder to implement and full of its own strange traps. Why? Well, for one thing: if I roll back a subtransaction, should the lock wait time it used now no longer count against the total? Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. If not, once a timeout failure has occurred it'll no longer be possible for the total transaction to do anything, even if it rolls back a failed subtransaction. But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. regards, tom lane -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Boszormenyi Zoltan z...@cybertec.at: Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. With respect I can't figure out what you're trying to say here. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. I think what you're saying is you think he only wanted to distinguish total time spent waiting for locks from total time spent executing including such things as i/o wait time. That's possible, Hans-Juergen wasn't very clear on what locking issues he was concerned about. I can think of a few categories of locking issues that might be problems though: 1) A web application wants to ensure that a slow batch job which locks records doesn't impact responsiveness. I think statement_timeout handles this better though. 2) A batch job might want to ensure it's still making progress even if slowly, but some other jobs might block indefinitely while holding locks (for example an email generating script might be stuck waiting for remote sites to respond). statement_timeout is better for ensuring overall execution speed but it won't fire until the entire time allotment is used up whereas something which detects being stuck on an individual lock would detect the problem much earlier (and perhaps the rest of the job could still be completed). 3) Applications which have hidden deadlocks because they block each other outside the database while holding locks in the database. This can be dealt with by using userlocks to represent the external resources but that depends on all of those external resources being identified correctly. A lock timeout would be an imprecise way to detect possible deadlocks even though it's always possible it just didn't wait long enough. Hans-Juergen, are any of these use cases good descriptions of your intended use? Or do you have a different case? -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello tom ... the reason for SELECT FOR UPDATE is very simple: this is the typical lock obtained by basically every business application if written properly (updating a product, whatever). the problem with NOWAIT basically is that if a small transaction holds a a lock for a subsecond, you will already lose your transaction because it does not wait at all (which is exactly what you want in some cases). however, in many cases you want to compromise on wait forever vs. die instantly. depending on the code path we could decide how long to wait for which operation. this makes sense as we would only fire 1 statement instead of 3 (set, run, set back). i agree that a GUC is definitely an option. however, i would say that adding an extension to SELECT FOR UPDATE, UPDATE and DELETE would make more sense form a usability point of view (just my 0.02 cents). if hackers' decides to go for a GUC, we are fine as well and we will add it to 8.5. many thanks, hans On May 11, 2009, at 4:46 PM, Tom Lane wrote: Hans-Juergen Schoenig postg...@cybertec.at writes: i would like to propose an extension to our SELECT FOR UPDATE mechanism. especially in web applications it can be extremely useful to have the chance to terminate a lock after a given timeframe. I guess my immediate reactions to this are: 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks? 2. That clear and easy to use oracle syntax sucks. You do not want to be embedding lock timeout constants in your application queries. When you move to a new server and the appropriate timeout changes, do you want to be trying to update your clients for that? What I think has been proposed previously is a GUC variable named something like lock_timeout, which would cause a wait for *any* heavyweight lock to abort after such-and-such an interval. This would address your point about not wanting to use an overall statement_timeout, and it would be more general than a feature that only works for SELECT FOR UPDATE row locks, and it would allow decoupling the exact length of the timeout from application query logic. 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 -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Greg Stark írta: 2009/5/11 Boszormenyi Zoltan z...@cybertec.at: Does statement_timeout counts against subtransactions as well? No. If a statement finishes before statement_timeout, does it also decrease the possible runtime for the next statement? No. I was talking about locks acquired during one statement. With respect I can't figure out what you're trying to say here. Sorry, bad rhetorics. Point correctly made is below. He argued about he wants a timeout *independent* from statement_timeout for locks only inside the same statement IIRC. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
2009/5/11 Hans-Jürgen Schönig postg...@cybertec.at: i agree that a GUC is definitely an option. however, i would say that adding an extension to SELECT FOR UPDATE, UPDATE and DELETE would make more sense form a usability point of view (just my 0.02 cents). I kinda agree with this. I believe Tom was arguing upthread that any change of this short should touch all of the places where NOWAIT is accepted now, and I agree with that. But having to issue SET as a separate statement and then maybe do another SET afterward to get the old value back doesn't seem like it provides any real advantage. GUCs are good for properties that you want to set and leave set, not so good for things that are associated with particular statements. It also seems to me that there's no reason for NOWAIT to be part of the syntax, but WAIT n to be a GUC. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. I agree with Tom here; I want to wait for a specific amount of time for a specific lock request. -- Josh Berkus PostgreSQL Experts Inc. 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Josh Berkus írta: But more generally, what you are proposing seems largely duplicative with statement_timeout. The only reason I can see for a lock-wait-specific timeout is that you have a need to control the length of a specific wait and *not* the overall time spent. Hans already argued upthread why he wants a feature that doesn't act like statement_timeout. I agree with Tom here; I want to wait for a specific amount of time for a specific lock request. Well, thinking about it a bit more, I think we can live with that. The use case would be mostly 1 record per SELECT FOR UPDATE WAIT N query, so for this the two semantics are equal. We would differ from Informix when one SELECT fetches more than one record obviously. We can have both GUC and the SQL extension for temporary setting. SET lock_timeout = N; -- 0 means infinite? or: SET lock_timeout = infinite; NOWAIT | WAIT (or no keyword as of now) for infinite waiting | WAIT DEFAULT | WAIT N (N seconds timeout) Comments? -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Robert Haas robertmh...@gmail.com writes: I kinda agree with this. I believe Tom was arguing upthread that any change of this short should touch all of the places where NOWAIT is accepted now, and I agree with that. But having to issue SET as a separate statement and then maybe do another SET afterward to get the old value back doesn't seem like it provides any real advantage. GUCs are good for properties that you want to set and leave set, not so good for things that are associated with particular statements. My point is that I don't believe the scenario where you say that you know exactly how long each different statement in your application should wait and they should all be different. What I do find credible is that you want to set a policy for all the lock timeouts. Now think about what happens when it's time to change the policy. A GUC is gonna be a lot easier to manage than timeouts that are embedded in all your individual queries. It also seems to me that there's no reason for NOWAIT to be part of the syntax, but WAIT n to be a GUC. I wasn't happy about NOWAIT in the syntax, either ;-) ... but at least that's a boolean and not a parameter whose specific value was plucked out of thin air, which is what it's pretty much always going to be. 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] Show method of index
Khee Chin escribió: Updated with an additional line in the comments for get_indexdef * if colno == -999, we only want the name of the variables that make up the index I don't think this hack is going to fly. I suggest you need to find some other way to implement this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Tom, My point is that I don't believe the scenario where you say that you know exactly how long each different statement in your application should wait and they should all be different. What I do find credible is that you want to set a policy for all the lock timeouts. Now think about what happens when it's time to change the policy. A GUC is gonna be a lot easier to manage than timeouts that are embedded in all your individual queries. For production applications, it's credible that you're going to desire three different behaviors for different locks: you'll want to not wait at all for some locks, wait a limited time for others, and for a few wait forever. I agree that the time for the 2nd case wouldn't vary per lock in any reasonable case. I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). So it would certainly be nice if we could somehow set total wait time instead of individual operation wait time. It's also completely and totally unworkable on the database layer for multiple reasons, so I'm not going to bother pushing any idea which implements this. So, I can see having a session-based lock_timeout GUC, and also a NOWAIT statement. It would mean that users would need to set lock_timeout=-1 if they didn't want the lock to timeout, but that's consistent with how other timeouts behave. -- Josh Berkus PostgreSQL Experts Inc. 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] Show method of index
Alvaro Herrera alvhe...@commandprompt.com writes: Khee Chin escribió: Updated with an additional line in the comments for get_indexdef * if colno == -999, we only want the name of the variables that make up the index I don't think this hack is going to fly. Yeah ... if it were local in describe.c that would be one thing, but putting such a kluge in a public function API is pretty icky. I think the proposed patch is doing pretty much the wrong thing anyhow. As I understood it, the request was *not* to add a column to \di (which would likely make it too wide to be readable, and would look rather silly in a mixed-indexes-and-tables listing too). The idea was to add a column to \d for an index, ie given something like CREATE INDEX fooi ON foo (f1, (f2+f3)) then \d fooi would give Index public.fooi Column | Type | Definition -+-+ f1 | integer | f1 pg_expression_2 | integer | (f2+f3) which you could do straight off with the existing behavior of pg_get_indexdef(). BTW, if we're going to have a different columnset for \d on indexes, it seems like it would be a good idea to include the opclass name too, at least in \d+. 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] Show method of index
On Tue, May 12, 2009 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Index public.fooi Column | Type | Definition -+-+ f1 | integer | f1 pg_expression_2 | integer | (f2+f3) Is there any reason to expose pg_expression_2 to the user at all? It's not like they can make use of it in any public interface. I would think we could just put the expression directly in the Column column. -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Josh Berkus j...@agliodbs.com writes: I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). And why is that only about lock wait time and not about total execution time? I still think statement_timeout covers the need, or at least is close enough that it isn't justified to make lock_timeout act like that (thus making it not serve the other class of requirement). 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] Show method of index
Greg Stark st...@enterprisedb.com writes: On Tue, May 12, 2009 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Index public.fooi Column | Type | Definition -+-+ f1 | integer | f1 pg_expression_2 | integer | (f2+f3) Is there any reason to expose pg_expression_2 to the user at all? Perhaps not, but if they did have a reason to access the individual index column then they'd need to know its name. I admit that there may not be any such reason at present, but do you want to find us having to change the definition back again sometime in the future? 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
On 5/11/09 4:25 PM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: I can see Zoltan's argument: for web applications, it's important to keep the *total* wait time under 50 seconds for most users (default browser timeout for most is 60 seconds). And why is that only about lock wait time and not about total execution time? I still think statement_timeout covers the need, or at least is close enough that it isn't justified to make lock_timeout act like that (thus making it not serve the other class of requirement). That was one of the reasons it's completely and totally unworkable, as I mentioned, if you read the next sentence. The only real answer to the response time issue is to measure total response time in the middleware. -- Josh Berkus PostgreSQL Experts Inc. 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] DROP TABLE vs inheritance
I wrote: it seems that there is a reasonably simple solution: we could make find_inheritance_children() and find_all_inheritors() acquire lock on each child table as they scan pg_inherits, and do try_relation_open() or equivalent to see if the child still exists. If not, assume the table just got dropped and ignore the pg_inherits entry. I've committed changes along this line, but there was one place that I thought needed further discussion to decide whether to change it. That is LockTableCommand(), which has historically attempted to determine whether the user has privilege on a table before it locks it. It's still working that way, which means it's at risk of the same type of child-disappeared problem that I just fixed elsewhere. I know we've gone back and forth on the question of how LOCK TABLE should behave, but at the moment I'm leaning towards changing it. The argument for the way it behaves now seems to be that a user who has no privileges on a table could cause a momentary denial of service to those who do by executing LOCK TABLE with an exclusive lock level. However, he can do that anyway via ALTER TABLE, which will happily take out AccessExclusiveLock before it checks any permissions. So I'm not seeing the point of risking unsafe behavior in LOCK TABLE. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
2009/5/11 Pavel Stehule pavel.steh...@gmail.com: I am thinking so Grouping Sets based on CTE should be more commitable code. It doesn't mean so your ideas are wrong, but these optimalization should to work on CTE too. select * from table group by rollup(a,b,c) have to have generate same plan as with q as (select * from table) select * from q group by a,b,c union all select * from q group by a,b union all select * from q group by a union all select * from q; and CTE is more general then Grouping Sets, so it is better do optimalization over CTE than Grouping Sets. If you need to buffer tuples from the outer plan and to rescan it multiple times, tuplestore seems more appropriate solution than using CTE node, from semantic point of view. During CTE and window functions development, tuplestore now has that kind of capability and CTE node is only a wrapper of tuplestore. Moreover, I guess you don't even need to buffer tuples to aggregate by different keys. What you have to do is only to prepare more than one hash tables (, or set up sort order if the plan detects hash table is too large to fit in the memory), and one time seq scan will do. The trans values are only to be stored in the memory, not the outer plan's results. It will win greately in performance. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Show method of index
On Tue, May 12, 2009 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Index public.fooi Column | Type | Definition -+-+ f1 | integer | f1 pg_expression_2 | integer | (f2+f3) Hi, I'd agree that the mucking around with rulesutil is unorthodox. Attached is a patch which does the above only modifying, describe . A prerequisite for column expressions to show is 8.4, as it makes use of array_agg, in pre 8.4-servers, it uses pg_get_indexdef(i.indexrelid,0,TRUE)), which I am still unsure whether we'd want as it stretches the output of \di extremely wide. - Modifies \di and \d output for indexes The output whilst connected to a 8.4 server and 8.3 server is as attached, psql (8.4beta1) Type help for help. postgres=# CREATE TABLE foo(a int, b text); CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# \div; List of relations Schema | Name| Type | Owner | Table | Method | Definition +---+---+---+---++--- public | idx_foo_bt_ab | index | rubik | foo | btree | a,b public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b)) public | idx_foo_hash | index | rubik | foo | hash | a public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition +---+---+---+---++ public | idx_foo_bt_ab | index | rubik | foo | btree | a,b (1 row) postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name | Type | Owner | Table | Method | Definition +-+---+---+---++--- public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) (1 row) postgres=# psql (8.4beta1, server 8.3.6) WARNING: psql version 8.4, server version 8.3. Some psql features might not work. Type help for help. postgres=# CREATE TABLE foo(a int, b text); CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); \div; \di idx_foo_bt_ab; \di idx_foo_bt_fooi; CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# \div; List of relations Schema | Name| Type | Owner | Table | Method | Definition +---+---+--+---++-- public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) public | idx_foo_bt_fooi | index | postgres | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b)) public | idx_foo_hash | index | postgres | foo | hash | CREATE INDEX idx_foo_hash ON foo USING hash (a) public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition +---+---+--+---++-- public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) (1 row) postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name
[HACKERS] COPY WITH CSV FORCE QUOTE *
Hi, FORCE QUOTE option of COPY WITH CSV requires an explicit column list, but '*' (all columns) would be also useful for typical usages. I searched the ML archive and found one request before: | COPY TO with FORCE QUOTE * | http://archives.postgresql.org/pgsql-sql/2008-08/msg00084.php The attached is a WIP patch add a support of '*' for FORCE QUOTE and FORCE NOT NULL options. I'd like to submit it for the next commit fest (8.5). Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center force_quote_all-20090512.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] Show method of index
My sincere apologies for flooding your mailboxes once again, as the patch attached in the previous post was incorrect. Also, I had failed to show test-cases of \d index in both 8.4 and 8.3 servers. Attached are the test cases for psql connecting to 8.4 and 8.3. psql (8.4beta1) Type help for help. postgres=# CREATE TABLE foo(a int, b text); CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# postgres=# \div List of relations Schema | Name| Type | Owner | Table | Method | Definition +---+---+---+---++--- public | idx_foo_bt_ab | index | rubik | foo | btree | a,b public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b)) public | idx_foo_hash | index | rubik | foo | hash | a public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition +---+---+---+---++ public | idx_foo_bt_ab | index | rubik | foo | btree | a,b (1 row) postgres=# \d idx_foo_bt_ab; Index public.idx_foo_bt_ab Column | Type | Definition +-+ a | integer | a b | text| b btree, for table public.foo postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name | Type | Owner | Table | Method | Definition +-+---+---+---++--- public | idx_foo_bt_fooi | index | rubik | foo | btree | md5((a)::text), md5((a || b)) (1 row) postgres=# \d idx_foo_bt_fooi; Index public.idx_foo_bt_fooi Column | Type | Definition -+--+-- pg_expression_1 | text | md5(a::text) pg_expression_2 | text | md5(a || b) btree, for table public.foo postgres=# psql (8.4beta1, server 8.3.6) WARNING: psql version 8.4, server version 8.3. Some psql features might not work. Type help for help. postgres=# CREATE TABLE foo(a int, b text); CREATE TABLE postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b)); CREATE INDEX postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a)); CREATE INDEX postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b)); CREATE INDEX postgres=# \div; List of relations Schema | Name| Type | Owner | Table | Method | Definition +---+---+--+---++-- public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) public | idx_foo_bt_fooi | index | postgres | foo | btree | md5((a)::text), md5((a || b)) public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b)) public | idx_foo_hash | index | postgres | foo | hash | CREATE INDEX idx_foo_hash ON foo USING hash (a) public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a)) (5 rows) postgres=# \di idx_foo_bt_ab; List of relations Schema | Name | Type | Owner | Table | Method | Definition +---+---+--+---++-- public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b) (1 row) postgres=# \d idx_foo_bt_ab; Index public.idx_foo_bt_ab Column | Type | Definition +-+ a | integer | a b | text| b btree, for table public.foo postgres=# \di idx_foo_bt_fooi; List of relations Schema | Name | Type | Owner | Table | Method | Definition +-+---+--+---++--- public | idx_foo_bt_fooi | index | postgres | foo | btree | md5((a)::text), md5((a || b)) (1 row) postgres=# \d idx_foo_bt_fooi; Index public.idx_foo_bt_fooi Column | Type |
