Re: [HACKERS] [GENERAL] Transactions within a function body
On Thu, Oct 2, 2008 at 6:46 PM, Bob Henkel [EMAIL PROTECTED] wrote: Have you looked at creating a function in perl and creating a new connection? Or using a dblink query which can create a new connection? These two methods work. I have used them to insert to a log table regardless of the parent transaction being commited or rolled back. A old example I posted of using pl/perl can be found here - http://www.postgresqlforums.com/forums/viewtopic.php?f=4t=647 The key is opening a new session which using dblink or pl/perl dbi connection will do. This is not ideal or efficient. It would be nice if you could just do autonomous transactions natively in pl/pgsql, but I find this method works for the cases where you need it(logging, huge batch processing tasks where it's not ideal to process everything in one transaction). The same can be done with plProxy which is quite efficient but yes opening connections is not. So if used extensively it would be clever to use pgBouncer to reuse connections. Thanks for interesting idea. Bob Hi all. Is there a way to have (sub)transactions within a function body? I'd like to execute some code (a transaction!) inside a function and later decide whether that transaction is to be committed or not. Thanks. On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Gurjeet Singh escribió: I have seen this feature being asked for, and this work-around suggested so many times. If plpgql does it internally, why not provide a clean interface for this? Is there some road-block, or that nobody has ever tried it? Initially we aimed at just exposing SAVEPOINT and ROLLBACK TO in functions, but ran into the problem that the SPI stack needs to be dealt with appropriately and you can't do it if the user is able to modify it arbitrarily by calling transaction-modifying commands. That's when the EXCEPTION idea came up. We never went back and studied whether we could have fixed the SPI limitation, but it's not trivial. -- Alvaro Herrera http://www.CommandPrompt.com/ http://www.commandprompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[HACKERS] Patchworks
I stumbled upon LWN article where Patchworks patch management system was discussed: http://lwn.net/Articles/298592/ It collects patches from mailing list, plus replies to it, and shows on a web page. Also keeps track of status. Sounds exactly like whats needed for Postgres? Patchworks own site: http://ozlabs.org/~jk/projects/patchwork/ And linux-ppc list that is already tracked using it: http://patchwork.ozlabs.org/ Comments? -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Allow pg_regress to be run outside the build tree.
On 02/10 17.29, Peter Eisentraut wrote: Tom Lane wrote: I think the right fix would be to convert those .sql files to input/*.source files and have pg_regress substitute the absolute directory names in them, like it is done for the backend. Ugh. I don't think it's acceptable to make contrib modules have to do that. Even if we fix all the ones in core, what of other people relying on the pgxs infrastructure? Yeah, true. Maybe copy the data directory over, but let pg_regress do it? For the record: when I integrated the pg_regress test suite (8.3.3) into OpenSolaris recently, I worked around various problems by making a wrapper script pg_regress.sh which copies everything into a temporary directory structure and then runs pg_regress from there. This involved creating three levels of dummy directories due to some hardcoded ../../../ in paths. A bit ugly, but then this package isn't to be included in the standard distribution to end users, but will be used by Solaris QA. -- Bjorn Munch Sun Microsystems Trondheim, Norway http://sun.com/postgresql/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patchworks
Marko Kreen wrote: I stumbled upon LWN article where Patchworks patch management system was discussed: http://lwn.net/Articles/298592/ It collects patches from mailing list, plus replies to it, and shows on a web page. Also keeps track of status. Sounds exactly like whats needed for Postgres? Patchworks own site: http://ozlabs.org/~jk/projects/patchwork/ And linux-ppc list that is already tracked using it: http://patchwork.ozlabs.org/ Comments? Awesome. I want it. :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] numeric_big test
The numeric_big regression test was added many years ago for the NUMERIC implementation but was not put in the default test set because it was too slow. Now my tests show, it is really not slower than some of the other slow tests (e.g., stats, tablespace), so perhaps time has caught up with us and we can now test it by default. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel pg_restore - WIP patch
Andrew Dunstan wrote: Stefan Kaltenbrunner wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Um, FKs could conflict with each other too, so that by itself isn't gonna fix anything. Good point. Looks like we'll need to make a list of can't run in parallel with items as well as strict dependencies. Yeah, I was just thinking about that. The current archive format doesn't really carry enough information for this. I think there are two basic solutions we could adopt: * Extend the archive format to provide some indication that restoring this object requires exclusive access to these dependencies. * Hardwire knowledge into pg_restore that certain types of objects require exclusive access to their dependencies. The former seems more flexible, as well as more in tune with the basic design assumption that pg_restore shouldn't have a lot of knowledge about individual archive object types. But it would mean that you couldn't use parallel restore with any pre-8.4 dumps. In the long run that's no big deal, but in the short run it's annoying. hmm not sure how much of a problem that really is - we usually recommend to use the pg_dump version of the target database anyway. We don't really need a huge amount of hardwiring as it turns out. Here is a version of the patch that tries to do what's needed in this area. this one is much better - however I still seem to be able to create deadlock scenarios with strange FK relations - ie FKs going in both directions between two tables. for those interested these are the timings on my 8 core testbox for my test database: single process restore: 169min -m2: 101min -m6: 64min -m8: 63min -m16: 56min Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Add relation fork support to pg_relation_size() function.
Hm, I wonder if we should have made the forks use a fork name instead of a number. It sure would be nicer to have files name 12345.fsm instead of another opaque number. The other reason I thought of this is that if EDB or anyone else uses forks for a private purpose then it would avoid the whole issue of conflicts. The best option right now would be to set aside a range of values for private purposes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Add relation fork support to pg_relation_size() function.
On 10/3/08, Gregory Stark [EMAIL PROTECTED] wrote: Hm, I wonder if we should have made the forks use a fork name instead of a number. It sure would be nicer to have files name 12345.fsm instead of another opaque number. The other reason I thought of this is that if EDB or anyone else uses forks for a private purpose then it would avoid the whole issue of conflicts. The best option right now would be to set aside a range of values for private purposes. Good idea. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] numeric_big test
Peter Eisentraut [EMAIL PROTECTED] writes: The numeric_big regression test was added many years ago for the NUMERIC implementation but was not put in the default test set because it was too slow. Now my tests show, it is really not slower than some of the other slow tests (e.g., stats, tablespace), so perhaps time has caught up with us and we can now test it by default. The other side of the coin is what would it possibly tell us that is worth any extra cycles at all? We do run it (at least I do) when touching the numeric datatype. Given the lack of machine dependence in that code, it seems unlikely that running numeric_big at other times would turn up anything. I can't see that it's worth slowing down everyone's regression tests for. (As somebody who frequently runs the regression tests dozens of times a day, I grudge any unnecessarily expensive testing...) 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] pgsql: Add relation fork support to pg_relation_size() function.
Marko Kreen [EMAIL PROTECTED] writes: On 10/3/08, Gregory Stark [EMAIL PROTECTED] wrote: The other reason I thought of this is that if EDB or anyone else uses forks for a private purpose then it would avoid the whole issue of conflicts. The best option right now would be to set aside a range of values for private purposes. Good idea. No, it isn't, because the patch assumes that the set of possible fork numbers is pretty compact (grep for uses of MAX_FORKNUM). I don't believe for a moment that EDB, or anyone else competent enough to put in a private fork definition, can't manage to add it to enum ForkNumber. They'd probably be well advised to operate with a private setting of catversion anyway, which would ensure that installations using this private fork wouldn't interoperate with backends not knowing about it. Once you've done that there's no need to worry about conflicts. I have no particular objection to the .fsm idea though --- that could be implemented fairly simply with a lookup table while forming the file name. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
OK, I have a stupid question- torn pages are a problem, but only during recovery. Recovery is (I assume) a fairly rare condition- if data corruption is going to happen, it's most likely to happen during normal operation. So why not just turn off CRC checksumming during recovery, or at least treat it as a much less critical error? During recovery, if the CRC checksum matches, we can assume the page is good- not only not corrupt, but not torn either. If the CRC checksum doesn't match, we don't panic, but maybe we do more careful analysis of the page to make sure that only the hint bits are wrong. Or maybe not. It's only during normal operation that a CRC checksum failure would be considered critical. Feel free to explain to me why I'm an idiot. Brian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
On Fri, Oct 3, 2008 at 3:36 PM, Brian Hurt [EMAIL PROTECTED] wrote: OK, I have a stupid question- torn pages are a problem, but only during recovery. Recovery is (I assume) a fairly rare condition- if data corruption is going to happen, it's most likely to happen during normal operation. So why not just turn off CRC checksumming during recovery, or at least treat it as a much less critical error? During recovery, if the CRC checksum matches, we can assume the page is good- not only not corrupt, but not torn either. If the CRC checksum doesn't match, we don't panic, but maybe we do more careful analysis of the page to make sure that only the hint bits are wrong. Or maybe not. It's only during normal operation that a CRC checksum failure would be considered critical. Well: 1. database half-writes the page X to disk, and there is power outage. 2. we regain the power 2. during recovery database replay all WAL-logged pages. The X page was not WAL-logged, thus it is not replayed. 3. when replaying is finished, everything looks OK at this point 4. user runs a SELECT which hits page X. Oops, we have a checksum mismatch. Best regards, Dawid Kuroczko -- ..``The essence of real creativity is a certain : *Dawid Kuroczko* : playfulness, a flitting from idea to idea : [EMAIL PROTECTED] : without getting bogged down by fixated demands.'' `..' Sherkaner Underhill, A Deepness in the Sky, V. Vinge -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
Brian Hurt wrote: OK, I have a stupid question- torn pages are a problem, but only during recovery. Recovery is (I assume) a fairly rare condition- if data corruption is going to happen, it's most likely to happen during normal operation. So why not just turn off CRC checksumming during recovery, or at least treat it as a much less critical error? During recovery, if the CRC checksum matches, we can assume the page is good- not only not corrupt, but not torn either. If the CRC checksum doesn't match, we don't panic, but maybe we do more careful analysis of the page to make sure that only the hint bits are wrong. Or maybe not. It's only during normal operation that a CRC checksum failure would be considered critical. Interesting question. The problem is that we don't read all pages in during recovery. One idea would be to WAL log the page numbers that might be torn and recompute the checksums on those pages during recovery. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Decibel! [EMAIL PROTECTED] [081002 19:18]: Well, it would be useful if whenever we magically decided it was time to write out a page that had only hint-bit updates we generated WAL, right? Even if it was just a no-op WAL record to ensure we had the page image in the WAL. Well, I'm by no means an expert in the code, but from my looking around bufmgr and transam yesterady, it really looks like it would be a modularity nightmare... But I think that would have the same total IO affect as nop WAL record being generated at the the page being dirtied, which would seem to fit the code a bit better... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] parallel restore test results
Andrew Dunstan wrote: Overall result: baseline: 4h32m parallel: 0h 54m. Awesome!! -- 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] patch: Add columns via CREATE OR REPLACE VIEW
I'm just getting back around to this now. I guess I'm wondering if someone could advise me on the best way of getting closer to a committable patch. The original patch just allows additional columns to be appended to the previous column list (while disallowing all other sorts of changes, including reordering/renaming/dropping columns). Although I understand that reordering, renaming, and dropping columns are potential sources of ambiguity, I don't think there is any possible ambiguity in adding columns. If so, perhaps this patch or a similar one might committable. But if not, then I'd like some suggestions on what could be done to pave the way for a future patch that would allow a more powerful version of CREATE OR REPLACE VIEW. Do I need to implement the three-tiered structure that Tom first proposed here? http://archives.postgresql.org/pgsql-hackers/2006-12/msg00977.php The major problem I have with this idea is that it seems to require us to do everything to do anything. Separating storage location from permanent ID is a performance enhancement which will probably require quite a bit of testing and discussion to figure out how it ought to work and how much control users ought to have over the behavior, questions which were extensively discussed here: http://archives.postgresql.org/pgsql-hackers/2007-02/msg01164.php On the other hand, separating permanent ID from view position requires working out the semantics of operations that rely on the column ordering, such as SELECT *, COPY, INSERT without column list, CREATE OR REPLACE VIEW view_name (column_alias, ...), and SELECT ... FROM table (column_alias, ...) as well as figuring out what sorts of DDL commands should be added to manipulate the view position. Trying to implement both of these things as a single patch sounds like it might be trying to do too much, but I'm open to suggestions. I think there would be a lot of benefit in being able to make changes to views without needing to drop and recreate all the dependent objects - it would certainly simplify things for me, and I imagine for others as well. I need some guidance though on where to go with it. I'd also be interested in knowing if anyone else is working on anything along these lines. Thanks, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
