Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 To reiterate my opinion, I think the behavior should be the same for interactive and non-interactive sessions. Not only will it prevent nasty surprises, but unless we make a third 'setting', there will be no way to enable this in non-interactive scripts, which is something that I would want to be able to do. I don't buy the but what if I set it in .psqlrc and forget argument. That could be applied to a lot of things you could put in there. This setting defaults to off and must be explicitly enabled. I'd be okay with a smart mode that explicitly enables the interactive/non-interactive split. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200504260737 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCbilxvJuQZxSWSsgRAgf8AJ9/NcsU/5A0V9isGvQy4sjba/aukgCgoFbp otSb0vVLfnL7mIt99rA4Piw= =1vVP -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: To reiterate my opinion, I think the behavior should be the same for interactive and non-interactive sessions. Not only will it prevent nasty surprises, but unless we make a third 'setting', there will be no way to enable this in non-interactive scripts, which is something that I would want to be able to do. I don't buy the but what if I set it in .psqlrc and forget argument. That could be applied to a lot of things you could put in there. This setting defaults to off and must be explicitly enabled. I'd be okay with a smart mode that explicitly enables the interactive/non-interactive split. But people (like me for example) will want to enable this behaviour by default. So they (me too) will put the option in .psqlrc. It is then enabled by default. But then many of my scripts will destroy data instead of just erroring out. I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? If you really want this for scripts, there must be two options: * one to put savely into .psqlrc (what some people will want, I have \set AUTOCOMMIT off in my .psqlrc file, too, and I know I am not the only one) * another one that will also work in scripts I hope you understand and accept the issue here. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Michael Paesold wrote: But people (like me for example) will want to enable this behaviour by default. So they (me too) will put the option in .psqlrc. It is then enabled by default. But then many of my scripts will destroy data instead of just erroring out. I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? DROP TABLE foo; CREATE TABLE foo... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane [EMAIL PROTECTED] writes: To reiterate my opinion, I think the behavior should be the same for interactive and non-interactive sessions. Not only will it prevent nasty surprises, but unless we make a third 'setting', there will be no way to enable this in non-interactive scripts, which is something that I would want to be able to do. I'm finding it hard to visualize a non-interactive script making any good use of such a setting. Without a way to test whether you got an error or not, it would amount to an ignore errors within transactions mode, which seems a pretty bad idea. Can you show a plausible use-case for such a thing? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Richard Huxton wrote: Michael Paesold wrote: But people (like me for example) will want to enable this behaviour by default. So they (me too) will put the option in .psqlrc. It is then enabled by default. But then many of my scripts will destroy data instead of just erroring out. I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? DROP TABLE foo; CREATE TABLE foo... This would be: \set AUTOCOMMIT off DROP TABLE foo; -- error, rolled back CREATE TABLE foo ... COMMIT; You could as well do: \set AUTOCOMMIT on -- default DROP TABLE foo; -- print error message CREATE TABLE foo ... There is not much difference, except for locking, ok. I see your point, but I don't think this makes enabling it by default (even in .psqlrc) any safer. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Richard Huxton dev@archonet.com writes: Michael Paesold wrote: I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? DROP TABLE foo; CREATE TABLE foo... Unconvincing. What if the drop fails for permission reasons, rather than because the table's not there? Then the CREATE will fail too ... but now the script bulls ahead regardless, with who knows what bad consequences. I would far rather see people code explicit markers around statements whose failure can be ignored. That is, a script that needs this behavior ought to look like BEGIN; \begin_ignore_error DROP TABLE foo; \end_ignore_error CREATE ... ... COMMIT; where I'm supposing that we invent psql backslash commands to cue the sending of SAVEPOINT and RELEASE-or-ROLLBACK commands. (Anyone got a better idea for the names than that?) Once you've got such an infrastructure, it makes sense to allow an interactive mode that automatically puts such things around each statement. But I can't really see the argument for using such a behavior in a script. Scripts are too stupid. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
I would far rather see people code explicit markers around statements whose failure can be ignored. That is, a script that needs this behavior ought to look like BEGIN; \begin_ignore_error DROP TABLE foo; \end_ignore_error CREATE ... ... COMMIT; That seems awful noisy. Why not just: BEGIN: DROP TABLE foo; ERROR: table foo does not exist; CONTINUE; etc Sincerely, Joshua D. Drake Command Prompt, Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in
At 12:28 AM 27/04/2005, Tom Lane wrote: Can you show a plausible use-case for such a thing? A not-uncommon case in other DBs is to handle insert/update code where insert is the most likely result. Not sure if this is relevant to scripts: Begin; ...do stuff... insert into trap duplicate index error and do update instead update... ...more stuff... commit; Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm told ;-). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Philip Warner [EMAIL PROTECTED] writes: Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm told ;-). Sure, but pg_dump scripts don't try to execute as a single transaction. None of this discussion applies to the behavior outside an explicit transaction block. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Tom Lane wrote: Richard Huxton dev@archonet.com writes: Michael Paesold wrote: I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? DROP TABLE foo; CREATE TABLE foo... Unconvincing. What if the drop fails for permission reasons, rather than because the table's not there? Then the CREATE will fail too ... but now the script bulls ahead regardless, with who knows what bad consequences. I would far rather see people code explicit markers around statements whose failure can be ignored. That is, a script that needs this behavior ought to look like BEGIN; \begin_ignore_error DROP TABLE foo; \end_ignore_error CREATE ... ... COMMIT; That's a lot of work. In this particular case I would actually like to see us provide DROP IF EXISTS ... or some such. My instinct on this facility is that distinguishing between interactive and noninteractive use is likely to be highly confusing. So I would favor behaviour that is consistent and defaults to off. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I would far rather see people code explicit markers around statements whose failure can be ignored. That is, a script that needs this behavior ought to look like BEGIN; \begin_ignore_error DROP TABLE foo; \end_ignore_error CREATE ... ... COMMIT; That's a lot of work. How so? It's a minuscule extension to the psql patch already coded: just provide backslash commands to invoke the bits of code already written. In this particular case I would actually like to see us provide DROP IF EXISTS ... or some such. That's substantially more work, with substantially less scope of applicability: it would only solve the issue for DROP. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Cleaning up unreferenced table files
On Mon, 25 Apr 2005, Bruce Momjian wrote: Tom Lane wrote: ... I think though that we ought to first consider the question of whether we *want* this functionality. On reflection I'm fairly nervous about the idea of actually deleting anything during startup --- seems like a good recipe for turning small failures into large failures. But if we're not going to delete anything then it's questionable whether we need to code it like this at all. It'd certainly be easier and safer to examine these tables after the system is up and running normally. Let's discuss this. The patch as submitted checks for unreferenced files on bootup and reports them in the log file, but does not delete them. That seems like the proper behavior. I think we delete from pgsql_tmp on bootup, but we _know_ those aren't referenced. What other user interface would trigger this if we did it after startup? Wouldn't we have to lock pg_class against VACUUM while we scan the file system, and are we sure we do things in pg_class or the file system first consistently? It seems much more prone to error doing it while the system is running. I agree. Also, you can only have stale files after a backend crash, since they are normally cleaned up at the end of transaction. If it was a separate program or command, the administrator would have to be aware of the issue. Otherwise, he wouldn't know he needs to run it after a crash. I feel that crashes that leaves behind stale files are rare. You would need an application that creates/drops tables as part of normal operation. Some kind of a large batch load might do that: BEGIN, CREATE TABLE foo, COPY 1 GB of data, COMMIT. The nasty thing right now is, you might end up with 1 GB of wasted disk space, and never even know it. I guess I am happy with just reporting during startup like the patch does now. Ok. I'll fix the design issues Tom addressed earlier, add documentation, and resubmit. We can come back to this after a release or two, when we have more confidence in the feature. Maybe we'll also get some feedback on how often those stale files occur in practice. - Heikki ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I would far rather see people code explicit markers around statements whose failure can be ignored. That is, a script that needs this behavior ought to look like BEGIN; \begin_ignore_error DROP TABLE foo; \end_ignore_error CREATE ... ... COMMIT; That's a lot of work. How so? It's a minuscule extension to the psql patch already coded: just provide backslash commands to invoke the bits of code already written. I meant it's a lot to type ;-) In this particular case I would actually like to see us provide DROP IF EXISTS ... or some such. That's substantially more work, with substantially less scope of applicability: it would only solve the issue for DROP. True. I wasn't suggesting it as an alternative in the general case. I still think it's worth doing, though - I have often seen it requested and can't think of a compelling reason not to provide it. But maybe that's off topic ;-) cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Andrew Dunstan [EMAIL PROTECTED] writes: \begin_ignore_error DROP TABLE foo; \end_ignore_error I meant it's a lot to type ;-) Well, that's just a matter of choosing good (ie short) names for the backslash commands. I was trying to be clear rather than proposing names I would actually want to use ;-). Any suggestions? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Cleaning up unreferenced table files
On Tue, 26 Apr 2005, Heikki Linnakangas wrote: On Mon, 25 Apr 2005, Bruce Momjian wrote: ... I guess I am happy with just reporting during startup like the patch does now. Ok. I'll fix the design issues Tom addressed earlier, add documentation, and resubmit. Here you go. The new functionality is now separated in a new file backend/utils/init/cleanup.c. There was code in many places that constructs the path to a tablespace directory. I refactored that into a new function called GetTablespacePath and put it next to GetDatabasePath in catalog.c. I added a section under the Routine Database Maintenance Tasks that basically gives a heads up that these notifications can appear in the log after a crash. - Heikki ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
On Apr 26, 2005, at 10:35 AM, Tom Lane wrote: Once you've got such an infrastructure, it makes sense to allow an interactive mode that automatically puts such things around each statement. But I can't really see the argument for using such a behavior in a script. Scripts are too stupid. Would it be possible to have a command line switch and/or a psql variable to control interactive? If I recall correctly, the setting depends on tty and there are possible interactive uses of psql outside of a terminal session. With so many things depending on this, it would be nice to be able to override the default. Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Cleaning up unreferenced table files
Heikki Linnakangas [EMAIL PROTECTED] writes: I feel that crashes that leaves behind stale files are rare. Indeed, and getting more so all the time ... which makes me question the value of doing anything about this at all. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
