Re: [PATCHES] Continue transactions after errors in psql
I've attached a revised patch which fixes the problem, however I'm sure there is a better way. Thanks to Neil for putting up with me on irc :-) How about calling the savepoint pg_psql_savepoint instead, that way it follows our 'don't begin things with pg_' philosophy. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Continue transactions after errors in psql
Robert Treat wrote: I've attached a revised patch which fixes the problem, however I'm sure there is a better way. Thanks to Neil for putting up with me on irc :-) In September 2004 I had already sent a patch to implement this behaviour, the patch, still in the archives, is here: http://archives.postgresql.org/pgsql-patches/2004-09/bin00040.bin (savepoints.patch) There are some issues it addressed: Assuming you put this option in your .psqlrc file, you will still probably not want this to be active when you execute commands from a file (non-interactive). So pset.notty must be checked. Again, when using \i, resetting errors seems dangerous. Using \i should also temporarily disable those savepoints. The real problem with my patch was, that it did not release the savepoints. Why? Look at this example (with the current patch reseterrors patch): template1=# \reseterror Reset error is on. template1=# BEGIN; BEGIN template1=# SAVEPOINT a; SAVEPOINT template1=# CREATE TABLE TEST ( a integer); CREATE TABLE template1=# ROLLBACK TO a; ERROR: no such savepoint So to get this right, you have to track savepoints created by the user and only release psql savepoints when there is no user savepoint sitting on top of your savepoint. Two ways come to my mind: 1) Parse SQL for savepoint and rollback to and create a stack of all savepoints. Then you can always release all savepoints as long as they are your own. 2) Implement a server-side function to get the savepoints from the server and query that before every release. What do you think? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] dbsize patch
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Hm, these are all implementable as SQL functions, do we need these hard coded too? e.g. create function aggregate_relation_size(oid) returns int8 as $CODE$ select sum(pg_relation_size(indexrelid)) from pg_index where indrelid=$1; $CODE$ language 'SQL' Your suggestion would be more compelling if the example were correct ;-). Consider more than one index on the same table. Hopefully SUM() will do the job. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Continue transactions after errors in psql
On Fri, 2005-01-28 at 04:46, Christopher Kings-Lynne wrote:
I've attached a revised patch which fixes the problem, however I'm sure
there
is a better way. Thanks to Neil for putting up with me on irc :-)
How about calling the savepoint pg_psql_savepoint instead, that way it
follows our 'don't begin things with pg_' philosophy.
I was actually thinking of calling it something like
pg_xact-start-time thinking that would be pretty unique within a
transaction, though having a specific documented name seemed ok too.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] more fixes for making contrib null safe
This adds the strict function attribute to the places in contrib that crash on null inputs. Kris JurkaIndex: contrib/chkpass/chkpass.sql.in === RCS file: /projects/cvsroot/pgsql/contrib/chkpass/chkpass.sql.in,v retrieving revision 1.5 diff -c -r1.5 chkpass.sql.in *** contrib/chkpass/chkpass.sql.in 29 Nov 2003 19:51:19 - 1.5 --- contrib/chkpass/chkpass.sql.in 29 Jan 2005 03:03:13 - *** *** 19,30 CREATE FUNCTION chkpass_in(cstring) RETURNS chkpass AS 'MODULE_PATHNAME' ! LANGUAGE 'c'; CREATE FUNCTION chkpass_out(chkpass) RETURNS cstring AS 'MODULE_PATHNAME' ! LANGUAGE 'c'; CREATE TYPE chkpass ( internallength = 16, --- 19,30 CREATE FUNCTION chkpass_in(cstring) RETURNS chkpass AS 'MODULE_PATHNAME' ! LANGUAGE C STRICT; CREATE FUNCTION chkpass_out(chkpass) RETURNS cstring AS 'MODULE_PATHNAME' ! LANGUAGE C STRICT; CREATE TYPE chkpass ( internallength = 16, *** *** 36,42 CREATE FUNCTION raw(chkpass) RETURNS text AS 'MODULE_PATHNAME', 'chkpass_rout' ! LANGUAGE 'c'; -- --The various boolean tests: --- 36,42 CREATE FUNCTION raw(chkpass) RETURNS text AS 'MODULE_PATHNAME', 'chkpass_rout' ! LANGUAGE C STRICT; -- --The various boolean tests: *** *** 45,56 CREATE FUNCTION eq(chkpass, text) RETURNS bool AS 'MODULE_PATHNAME', 'chkpass_eq' ! LANGUAGE 'c'; CREATE FUNCTION ne(chkpass, text) RETURNS bool AS 'MODULE_PATHNAME', 'chkpass_ne' ! LANGUAGE 'c'; -- --Now the operators. Note how some of the parameters to some --- 45,56 CREATE FUNCTION eq(chkpass, text) RETURNS bool AS 'MODULE_PATHNAME', 'chkpass_eq' ! LANGUAGE C STRICT; CREATE FUNCTION ne(chkpass, text) RETURNS bool AS 'MODULE_PATHNAME', 'chkpass_ne' ! LANGUAGE C STRICT; -- --Now the operators. Note how some of the parameters to some Index: contrib/isbn_issn/isbn_issn.sql.in === RCS file: /projects/cvsroot/pgsql/contrib/isbn_issn/isbn_issn.sql.in,v retrieving revision 1.10 diff -c -r1.10 isbn_issn.sql.in *** contrib/isbn_issn/isbn_issn.sql.in 29 Nov 2003 22:39:20 - 1.10 --- contrib/isbn_issn/isbn_issn.sql.in 29 Jan 2005 03:03:13 - *** *** 14,25 CREATE FUNCTION issn_in(cstring) RETURNS issn AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_out(issn) RETURNS cstring AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE TYPE issn ( INTERNALLENGTH = 16, --- 14,25 CREATE FUNCTION issn_in(cstring) RETURNS issn AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_out(issn) RETURNS cstring AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE TYPE issn ( INTERNALLENGTH = 16, *** *** 39,70 CREATE FUNCTION issn_lt(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_le(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_eq(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_ge(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_gt(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; CREATE FUNCTION issn_ne(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C'; -- --Now the operators. Note how some of the parameters to some --- 39,70 CREATE FUNCTION issn_lt(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_le(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_eq(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_ge(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_gt(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; CREATE FUNCTION issn_ne(issn, issn) RETURNS bool AS 'MODULE_PATHNAME' ! LANGUAGE 'C' STRICT; -- --Now the operators. Note how some of the parameters to some *** *** 120,126 CREATE FUNCTION issn_cmp(issn, issn) RETURNS integer AS '$libdir/isbn_issn' ! LANGUAGE c; -- Create default operator class for 'issn'-- -- Needed to create index or primary key -- --- 120,126 CREATE FUNCTION issn_cmp(issn, issn) RETURNS integer AS '$libdir/isbn_issn' ! LANGUAGE c STRICT; -- Create default operator class for 'issn'-- -- Needed to create index or primary key -- *** *** 147,158 CREATE FUNCTION isbn_in(cstring)
Re: [PATCHES] more fixes for making contrib null safe
On Fri, 28 Jan 2005, Kris Jurka wrote: This adds the strict function attribute to the places in contrib that crash on null inputs. Doesn't C need to be quoted? A few of those looked like this: LANGUAGE C STRICT but I thought it should be: LANGUAGE 'C' STRICT Is that right? Jon -- Jon Jensen End Point Corporation http://www.endpoint.com/ Software development with Interchange, Perl, PostgreSQL, Apache, Linux, ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] more fixes for making contrib null safe
On Fri, 28 Jan 2005, Kris Jurka wrote: Doesn't C need to be quoted? A few of those looked like this: LANGUAGE C STRICT This is perfectly legal: http://developer.postgresql.org/docs/postgres/sql-createfunction.html langname The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name may be enclosed by single quotes. Oh, ok. Good to know. Thanks, Jon -- Jon Jensen End Point Corporation http://www.endpoint.com/ Software development with Interchange, Perl, PostgreSQL, Apache, Linux, ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Continue transactions after errors in psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Paesold wrote: 2) Implement a server-side function to get the savepoints from the server and query that before every release. I could not find a way to do this. Is there any interface to the list? /aside I looked over the patch from Michael Paesold, and talked extensively with Robert Treat about this, and here is the solution Robert and I came up with: (thanks to both for their work) First, I'm not of the opinion that it should automatically be turned off when running non-interactively. That's too much assuming of what the user wants, when this is a settable flag. However, it should be settable via a script to a definite state. So \reseterror will take an optional argument, off or on, which sets it rather than toggles it. The patch Robert provided shold catch the problem of good command-commit. The other problem is not stepping on other people's savepoints. The best solution we came up with was to check for savepoint commands ourselves, similar to the way psql already checks for transaction affecting commands, and handle things appropriately. Specifically, if someone issues a savepoint while in \reseterror mode, it switches off automatically*. Since the implementation of reseterror is pretty much a lazy shortcut to issuing savepoints yourself, it should be safe to say that you do not want to mix manual and automatic ones, and we'll back off (with a message) if you issue your own. Plus there will be a warning in the docs to be careful about mixing savepoints and the \reseterror method. * We could also switch it back on after rollback or release, but this would entail a little more tracking. Comments? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200501282306 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFB+wzovJuQZxSWSsgRAt5eAJ9BVMtYZ9H+A76cNdUuhv4GpXeCwQCdFVsi +mgg6ZzMylgHgdfiVn4yI5o= =CpZQ -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: [PATCHES] more fixes for making contrib null safe
Kris Jurka [EMAIL PROTECTED] writes: This adds the strict function attribute to the places in contrib that crash on null inputs. Sounds good. Barring objections, I'll apply this to current and back branches before we make the upcoming releases. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
