Re: [HACKERS] NOFIXADE / NOPRINTADE
On Fri, 2004-09-24 at 12:30, Neil Conway wrote: Barring any objections, I intend to apply the attached patch to HEAD later today. Applied to HEAD. -Neil ---(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] SQL-Invoked Procedures for 8.1
Joe Conway wrote: Gavin Sherry wrote: Do you have any idea about databases returning result sets from SQL procedures (ie, not functions). As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). It works like: And these databases also return a result status/value from the stored procedure. IIRC this result is limited to an int value. Maarten ---(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] SQL-Invoked Procedures for 8.1
Tom Lane wrote: One interesting point is whether it's possible for one procedure to call another, and if so what that means for the semantics. Is the inner procedure allowed to commit a transaction started by the outer one? Usually yes a procedure can call another, and it's extremely useful to do so. I'm not so sure about the semantics with transactions, but personally I wouldn't like to see a procedure be able to commit the transaction of it's caller. Peter ---(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] SQL-Invoked Procedures for 8.1
Maarten Boekhold wrote: Joe Conway wrote: Gavin Sherry wrote: Do you have any idea about databases returning result sets from SQL procedures (ie, not functions). As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). It works like: And these databases also return a result status/value from the stored procedure. IIRC this result is limited to an int value. Maarten Yes, MS SQL returns an int as far as I know (all the procs I use return an int), but in theory it can be any type. From my useage, the return parameter (if requested) is returned as the first out parameter. Ie, from JDBC, my CallableStateme is of the form: ? = Call dbo.MyProc( ?, ?, ? ) so I can simply use cs.getInt( 1 ); to get at that value. If I don't ask for the return code, then I don't receive it. Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Fri, 2004-09-24 at 04:12, Josh Berkus wrote: My comments are based on having professionally written several hundred thousand lines of procedural code for PostgreSQL, SQL Server, and Oracle. I haven't used stored procedures as implemented elsewhere, so I appreciate your comments. If we go down the route of saying that procedures are a type of function, we have the option of allowing users access to OUT and INOUT in functions. This would make procedures simply a subset of functions. What do people think? Well, to be frank, my first thought is, why bother? If you're just going to implement some syntatic sugar on top of the current Function feature, why bother at all? As far as possible, I would like to extend the PG concept of functions to offer what people expect from stored procedures, and then implement syntax sugar so that people can use the standard's stored procedure syntax. I think the system is cleaner if we keep the number of distinct concepts users need to understand to a minimum. That means not making arbitrary distinctions between stored procedures and functions. It may turn out, for example, that implementing the kind of transactional behavior people want for procedures won't be possible within the existing function infrastructure -- if that's the case, so be it: we'll need to distinguish procedures and functions. But I'd prefer to unify the concepts as far as possible. Given the opportunity, I would far prefer to set us on a road that would allow us to replicate -- and exceed -- the functionality of Oracle's PL/SQL. That's a priority for me, as well. Well, see my thoughts above on differentiating SPs from Functions.I certainly don't think we should be using the same table. Using a different system catalog strikes me as total overkill, and a surefire way to duplicate a lot of code. SET VARIABLE varname = val SHOW VARIABLE varname The problem with this is name collisions with the GUC -- especially as it now allows add-ins to create their own GUC variables. However intuitive SET and SHOW are, they will lead to problems. I don't see how it will: SET VARIABLE would not share SET's namespace, so collisions would not be possible. The other option is that we do it at the protocol level and modify libpq (and psql) to add support. [...] This may be a better approach. I've personally never been comfortable with the use of variables outside of SPs and packages; it seems orthagonal to the declaritive nature of SQL. Whether we support protocol-level variables or SQL-level variables has nothing to do with how those variables can be referenced in queries, so I'm not sure what you're getting at. The more practical consideration is, where will OUT and INOUT parameters be used? Do we want them returned to the SQL session or directly to the calling client? I think what you're asking is after a query like: CALL foo_proc(:some_out_param); does the client need to explicitly fetch the modified variable, or is it returned to the client via some means automatically. Requiring the client to issue a fetch involves an additional roundtrip (and is an annoyance), so I'm leaning toward returning modified variables automatically. Perhaps we should allow clients to register interest in variables -- when the value of that variable changes, they would receive a protocol message with its new value. I don't see a clean way to do this without modifying the protocol, though. (We might have clients register for interest in variables they create by default.) The only other question (that I can think of now) with respect to variables is how they are affected by transactions. My gut feel is that they should have transactional semantics. [...] I agree strongly with this, especially since we'll be using Savepoints inside the SPs. Having one's data mods roll back but not the variable values would be confusing and lead to a *lot* of debugging. Agreed. BTW, I should note that I'm not taken with the idea of storing variables in temporary tables -- I don't think it will take too much work to implement transaction semantics for variables by hand, since there is no need to worry about concurrency. -Neil (I need to mull over your points on overloading and transactions -- I'll get back to you on that...) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Fri, 2004-09-24 at 01:56, Joe Conway wrote: As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). From looking at the docs, it appears this isn't supported by Oracle or DB2 (correct me if I'm wrong). I can see how it would be useful, but I don't think it needs to be part of the first shot at doing stored procedures. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote: I don't think we can do that in a standard function, at least not without a lot of work. Can you elaborate on why this would be so difficult? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Fri, 2004-09-24 at 02:40, Tom Lane wrote: I concur with Grant Finnemore's objection as well: people expect procedures to be able to return resultsets, ie SETOF something, not only scalar values. IMHO most products (and the standard) define stored procedures as not returning _anything_, whether they be scalar values or not. The only counter-example I've seen pointed out is MS SQL. Or are you talking about non-scalar OUT params? -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Neil Conway wrote: On Fri, 2004-09-24 at 01:56, Joe Conway wrote: As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). From looking at the docs, it appears this isn't supported by Oracle or DB2 (correct me if I'm wrong). I can see how it would be useful, but I don't think it needs to be part of the first shot at doing stored procedures. For Oracle you would return refcursors... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Peter Mount [EMAIL PROTECTED] writes: Tom Lane wrote: One interesting point is whether it's possible for one procedure to call another, and if so what that means for the semantics. Is the inner procedure allowed to commit a transaction started by the outer one? Usually yes a procedure can call another, and it's extremely useful to do so. I'm not so sure about the semantics with transactions, but personally I wouldn't like to see a procedure be able to commit the transaction of it's caller. From the quote from the spec referred to earlier it seems the spec anticipates that by default it wouldn't be able to. At least not able to commit refer to savepoints from its caller. Presumably that extends to transactions. However it did provide a way to declare a procedure that could refer to savepoints from its caller. Conceivably that extends to the overall transaction as well. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] planner target lists
Hi, I am confused about an internal point of the planner. Consider a select query and the output target list at the root of the tree. This target lists points to some Vars. Each of which has as relation either INNER/OUTER. Does this INNER/OUTER refer to the inner/outer relations of the top-most node in the tree or to the bottom-most one. In other words, in the following tree, a variable in B that shows in Op1's target list, does it have its relation as INNER (which is B) or OUTER (which is Op2) Op1 / \ / \ / \ Op2 Op3 / \ / \ / \ / \ A B C D Regards, --h ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Neil Conway [EMAIL PROTECTED] writes: Or are you talking about non-scalar OUT params? Exactly. I agree that a procedure has no return value per se, but we need to be able to support OUT params that are rowsets. 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] Use of zlib
Thomas Hallgren wrote: Problem: PL/Java use a JVM. On some platforms and with some JVM's (Sun's in particular) a libzip.so is bundled that contains a 1.1.3 version of functions also provided in zlib (why they do this is beyond me, but they do so I'll have to live with it). PostgreSQL is linked with zlib by default. This resuls in a conflict which resuls in a JVM crash. I can circumvein this crash by using LD_PRELOAD to force a load of the JVM bundled libzip.so but I suspect that might result in a crash as soon as PostgreSQL calls on zlib to do something. It's of course possible to configure postgresql with --without-zlib also provided you have accesst o the source. Question: From what I can understand from the documentation, the only utility in PostgreSQL that actually uses zlib is pg_dump? If so, why is the postgres process linked with -lz? I did a small experiment by installing binutils 2.15 and adding -Wl,--as-needed to the LDFLAGS, as Tom had suggested might be useful. This seemed to work quite well and trimmed back the needed libs quite a bit. However, when you configure --with-openssl, libz is again linked in. Not sure where that leaves us. cheers andrew ---(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] SQL-Invoked Procedures for 8.1
Neil Conway [EMAIL PROTECTED] writes: On Fri, 2004-09-24 at 04:12, Josh Berkus wrote: Well, see my thoughts above on differentiating SPs from Functions.I certainly don't think we should be using the same table. Using a different system catalog strikes me as total overkill, and a surefire way to duplicate a lot of code. I think that choice will be driven by one thing and one thing only: do procedures and functions have the same primary key? Which boils down to whether they have the same semantics about overloaded function names and resolution of ambiguous parameter types. Personally I think I'd prefer that they did, but plenty of people have indicated they'd rather have other features (like defaultable parameters). regards, tom lane ---(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
[HACKERS] PostgreSQL 8.0 beta3 on Monday
Its been almost a month now, since Beta2, and commit activity has quite busy, so we're aiming for Monday, September 28th, for Beta3. Starting with Beta3, Bruce is also going to change the format for the OpenItems list a little bit, but including a list of 'Changes since last Beta' at the bottom, instead of just removing completed items. If anyone has something they are sitting on right now, that they feel should be in Beta3, please let us know before packaging starts on Sunday. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] planner target lists
Hicham G. Elmongui [EMAIL PROTECTED] writes: In other words, in the following tree, a variable in B that shows in Op1's target list, does it have its relation as INNER (which is B) or OUTER (which is Op2) Op1 / \ / \ / \ Op2 Op3 / \ / \ / \ / \ A B C D IIRC, up to the point where setrefs.c runs, all Vars have varnos that refer to their parent relation in the rangetable list --- so B's vars look the same no matter where they are in the tree. setrefs.c changes Vars that are in JOIN plan nodes to have varno INNER or OUTER, indicating whether the value is coming from the inner or outer (right or left) input of *that particular plan node*. IIRC it also relabels varattno to be the column number of that value in the tuples emitted by that input. So after this happens, the same Var might look completely different at each tree level it appears in. The executor is never particularly interested in rangetable positions --- all Vars it deals with can be resolved by looking in either the current table's scanned tuple (at the bottom scan level) or one of the input tuples to the current upper-level plan node. So basically setrefs is transforming the Var from a planner-friendly representation to an executor-friendly one. regards, tom lane ---(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] SQL-Invoked Procedures for 8.1
Neil Conway wrote: On Fri, 2004-09-24 at 01:56, Joe Conway wrote: As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). From looking at the docs, it appears this isn't supported by Oracle or DB2 (correct me if I'm wrong). I can see how it would be useful, but I don't think it needs to be part of the first shot at doing stored procedures. I believe you are correct for Oracle at least. But for people porting over from MSSQL it is a *huge* deal, and given the native windows port of Postgres with 8.0.0, I predict *many* requests for this in upcoming months. Joe ---(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] Use of zlib
Andrew Dunstan wrote: Thomas Hallgren wrote: Problem: PL/Java use a JVM. On some platforms and with some JVM's (Sun's in particular) a libzip.so is bundled that contains a 1.1.3 version of functions also provided in zlib (why they do this is beyond me, but they do so I'll have to live with it). PostgreSQL is linked with zlib by default. This resuls in a conflict which resuls in a JVM crash. I can circumvein this crash by using LD_PRELOAD to force a load of the JVM bundled libzip.so but I suspect that might result in a crash as soon as PostgreSQL calls on zlib to do something. It's of course possible to configure postgresql with --without-zlib also provided you have accesst o the source. Question: From what I can understand from the documentation, the only utility in PostgreSQL that actually uses zlib is pg_dump? If so, why is the postgres process linked with -lz? I did a small experiment by installing binutils 2.15 and adding -Wl,--as-needed to the LDFLAGS, as Tom had suggested might be useful. This seemed to work quite well and trimmed back the needed libs quite a bit. However, when you configure --with-openssl, libz is again linked in. From PL/Javas standpoint, I think it would be great if this change could be incorporated into the 8.0 release. The openssl issue is something I'll have to investigate. Perhaps it works using the libzip from the JRE if I use LD_PRELOAD. I'm happy to see that Sun has an open bug on the subject in their Bug Database (bug 4953050 if anyone is interested). Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
I believe you are correct for Oracle at least. But for people porting over from MSSQL it is a *huge* deal, and given the native windows port of Postgres with 8.0.0, I predict *many* requests for this in upcoming months. Speaking from a commercial perspective. I have had, in the last 60 days over a dozen inquiries of how PostgreSQL 8.0 on WINDOWS compares to MSSQL. The specific question topics have been: 1. Reliability 2. Performance 3. High Availability 4. Features Anything that we can do, within reason to help the migration from MSSQL to PostgreSQL is a good thing (tm). Sincerely, Joshua D. Drake Joe ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(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] PostgreSQL 8.0 beta3 on Monday
I hope to get PL/Python builds on win32 in. I would like to get the SSL stuff in, but I haven't had the time to look at it lately, so unless someone else steps up to that one (I know at least Dave is also working on it) I don't think it'll make it. //Magnus -Original Message- From: Marc G. Fournier [mailto:[EMAIL PROTECTED] Sent: Friday, September 24, 2004 4:38 PM To: [EMAIL PROTECTED] Subject: [HACKERS] PostgreSQL 8.0 beta3 on Monday Its been almost a month now, since Beta2, and commit activity has quite busy, so we're aiming for Monday, September 28th, for Beta3. Starting with Beta3, Bruce is also going to change the format for the OpenItems list a little bit, but including a list of 'Changes since last Beta' at the bottom, instead of just removing completed items. If anyone has something they are sitting on right now, that they feel should be in Beta3, please let us know before packaging starts on Sunday. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.0 beta3 on Monday
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Hagander Sent: 24 September 2004 16:27 To: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] PostgreSQL 8.0 beta3 on Monday I would like to get the SSL stuff in, but I haven't had the time to look at it lately, so unless someone else steps up to that one (I know at least Dave is also working on it) I don't think it'll make it. I'm working on it, but in 5 minute bursts at the moment unfortunately :-( Regards, Dave. ---(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] pg_autovacuum
pg_autovacuum just writes to standard out unless you specify a log file on the command line. See pg_autovacuum -h for details. Matthew On Wed, 2004-09-22 at 03:29, Iulia Pacurar wrote: Hi! I run pg_autovacuum: ./pg_autovacuum -D but then I cannot find pg_autovacuum.log file. Where shoud I look for it? Thank you. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] CRITICAL HELP NEEDED! DEAD DB!
Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG: database system was interrupted while in recovery at 2004-09-24 10:21:41 MST Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG: checkpoint record is at 9A/C2022368 Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG: redo record is at 9A/C2022368; undo record is at 0/0; shutdown FALSE Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG: next transaction ID: 197841225; next OID: 715436086 Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG: redo starts at 9A/C20223B0 Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC: btree_insert_redo: failed to add item Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG: startup process (PID 18306) was terminated by signal 6 Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG: aborting startup due to startup process failure Any suggestions to recover?! I'm dead in the water! Please!!! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] CRITICAL HELP NEEDED! DEAD DB!
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cott Lang Sent: Friday, September 24, 2004 10:21 AM To: [EMAIL PROTECTED] Subject: [HACKERS] CRITICAL HELP NEEDED! DEAD DB! Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG: database system was interrupted while in recovery at 2004-09-24 10:21:41 MST Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG: checkpoint record is at 9A/C2022368 Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG: redo record is at 9A/C2022368; undo record is at 0/0; shutdown FALSE Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG: next transaction ID: 197841225; next OID: 715436086 Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG: redo starts at 9A/C20223B0 Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC: btree_insert_redo: failed to add item Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG: startup process (PID 18306) was terminated by signal 6 Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG: aborting startup due to startup process failure Any suggestions to recover?! I'm dead in the water! Please!!! When did you do your last backup? This message is a clue: HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. If you do a restore from your last backup, you will lose the data between that time and the time of the problem. Any other solution will be fraught with peril, I think. Otherwise, maybe something here will help: http://svana.org/kleptog/pgsql/pgfsck.html ---(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] CRITICAL HELP NEEDED! DEAD DB!
Cott Lang [EMAIL PROTECTED] writes: Sep 24 10:22:37 snafu postgres[18306]: [2-1] LOG: database system was interrupted while in recovery at 2004-09-24 10:21:41 MST Sep 24 10:22:37 snafu postgres[18306]: [2-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Sep 24 10:22:37 snafu postgres[18306]: [3-1] LOG: checkpoint record is at 9A/C2022368 Sep 24 10:22:37 snafu postgres[18306]: [4-1] LOG: redo record is at 9A/C2022368; undo record is at 0/0; shutdown FALSE Sep 24 10:22:37 snafu postgres[18306]: [5-1] LOG: next transaction ID: 197841225; next OID: 715436086 Sep 24 10:22:37 snafu postgres[18306]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Sep 24 10:22:37 snafu postgres[18306]: [7-1] LOG: redo starts at 9A/C20223B0 Sep 24 10:22:37 snafu postgres[18306]: [8-1] PANIC: btree_insert_redo: failed to add item Sep 24 10:22:37 snafu postgres[18299]: [2-1] LOG: startup process (PID 18306) was terminated by signal 6 Sep 24 10:22:37 snafu postgres[18299]: [3-1] LOG: aborting startup due to startup process failure Any suggestions to recover?! I'm dead in the water! Please!!! I think your only chance is pg_resetxlog. Be aware that you won't necessarily have a consistent database afterwards --- in particular, whichever index that failure is about is certainly broken. I'd recommend a dump and reload, plus as much manual verification of data consistency as you can manage. How did you get into this state, anyway? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] CRITICAL HELP NEEDED! DEAD DB!
On Fri, 2004-09-24 at 11:43, Tom Lane wrote: I think your only chance is pg_resetxlog. Be aware that you won't necessarily have a consistent database afterwards --- in particular, whichever index that failure is about is certainly broken. I'd recommend a dump and reload, plus as much manual verification of data consistency as you can manage. That's what I've done, so far so good, although we are still checking consistency against the last backup. Thanks for the info. Luckily this was one of our smaller databases ... How did you get into this state, anyway? I wish I knew - this is what appeared to start it: Sep 24 10:19:41 snafu postgres[18176]: [464-1] ERROR: could not open segment 1 of relation idx_ordl_id (target block 1719234412): No such file or Sep 24 10:19:41 snafu postgres[18176]: [464-2] directory I can't figure out what the exact problem is; there were no I/O errors or any other relative messages at the time, the box was empty, and nothing remarkable was going on. :( thanks, Cott PS: No, I don't think it's a PG problem. :) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CRITICAL HELP NEEDED! DEAD DB!
Does pgfsck work on 7.4.x? Otherwise, maybe something here will help: http://svana.org/kleptog/pgsql/pgfsck.html ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.4.5 losing committed transactions
The attached archive contains a script that I used to reproduce the error multiple times. Setup: * create database crashtest * start 6 instances of testload.tcl as ./testload.tcl tN dbname=crashtest where N = 1..6 * frequently kill a backend to cause a postmaster restart. The testload script creates a table and starts inserting rows, 2 to 6 per transaction. If the transaction succeeds, it increments the internal ID counter by the number of rows inserted. If the transaction fails with a dupkey error, it increments the ID counter by 1. On any failure, it reconnects to the database. The duplicate key errors can happen because the transaction is committed, but the response doesn't reach the client before the crash. So that is a normal and expected behaviour. But occasionally there will appear a gap in the data. With the given logic only to increment the counter on a dupkey or after a positive COMMIT response by the backend, IMHO there can only be one if we lose transactions after commit on a crash restart. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # lose_committed.tgz Description: application/compressed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Fri, Sep 24, 2004 at 10:03:33AM -0400, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Or are you talking about non-scalar OUT params? Exactly. I agree that a procedure has no return value per se, but we need to be able to support OUT params that are rowsets. FWIW, Sybase, MSSQL, and DB2 return recordsets via an 'open' SELECT or OPEN CURSOR statement. IE: you execute a SELECT or an OPEN CURSOR, but don't fetch it into anything. Oracle takes the track of fetching into a refcursor or cursor variable, which you return as an OUT or INOUT parameter. The advantage to MSSQL et all is it's less work/code. The advantage to Oracle is there's no ambiguity. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CRITICAL HELP NEEDED! DEAD DB!
Cott Lang wrote: I wish I knew - this is what appeared to start it: Sep 24 10:19:41 snafu postgres[18176]: [464-1] ERROR: could not open segment 1 of relation idx_ordl_id (target block 1719234412): No such file or Sep 24 10:19:41 snafu postgres[18176]: [464-2] directory I can't figure out what the exact problem is; there were no I/O errors or any other relative messages at the time, the box was empty, and nothing remarkable was going on. :( I saw that exact error message, with no logged I/O system errors, when using SAN attached storage a month or so ago. It turned out to be the SAN silently corrupting files. We did eventually start to see scsi errors, but not at the beginning. Joe ---(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
[HACKERS] PG Build Farm Status
Shown below is a HOWTO for PostgreSQL build farm clients for the system
I'm working on. The HOWTO is also available at
http://pgfoundry.org/docman/view.php/140/4/PGBuildFarm-HOWTO.txt
The code is running successfully on several machines, and uploading
results to my test server.
A production server for collecting the results from the distributed
clients is going to be provided by CommandPrompt (thanks, Joshua!), and
should be available in a couple of weeks. Meanwhile, as shown below,
people who want to try before they buy can test out the client-side
without any uploading.
Comments are welcome, either to me directly or preferably on the
trackers and forums at http://pgfoundry.org/projects/pgbuildfarm/
cheers
andrew
=
This HOWTO is for PostgreSQL Build Farm clients.
0. PostgreSQL build Farm is a distributed build system designed to detect
build failures on a large collection of platforms and configurations.
This software is written in Perl. If you're not comfortable with Perl
then you possibly don't want to run this, even though the only adjustment
you should ever need is to the config file (which is also Perl).
1. Get the Software, from:
http://pgfoundry.org/download.php/66/build-farm-1_0.tgz
Unpack it and put it somewhere. You can put the config file in a different
place from the run_build.pl script if you want to (see later), but the
simplest thing is to put it in the same place.
2. Create a directory where builds will run. This should be dedicated to
the use of the build farm. Make sure there's plenty of space - on my
machine each branch can use up to about 700Mb during a build.
3. Edit the config file and put the location of the directory you just
created in the config variable build_root. Adjust the config variables
make, config_opts and (if you don't use ccache) config_env
to suit your environment, and to choose which optional postgres modules
you want to build. You should not need to adjust any other variables.
Check that you didn't screw things up by running perl -cw build-farm.conf.
4. If the path to your perl installation isn't /usr/bin/perl, edit
the #! line in run_build.pl so it is correct. This is the ONLY line in that
file you should ever need to edit.
5. run perl -cw run_build.pl. If you get errors about missing perl modules
you will need to install them. Specifically, you will need these modules:
LWP
HTTP::Request::Common
MIME::Base64
Digest::SHA1
Fcntl
Getopt::Long
File::Find
Many of these you should have. They are all standard CPAN modules. When you
don't get an error any more you are ready to start testing.
6. With a PATH that matches what you will have when running from cron, run
the script in no-send, no-status, verbose mode. Something like this:
PATH=/usr/bin:/bin ./run_build.pl --nosend --nostatus --verbose
and watch the fun begin. If this results in failures because it can't
find some executables (especially gmake and cvs), you might need to change
the config file again, this time changing the build_env with another
setting something like:
PATH = /usr/local/bin:$ENV{PATH},
Also, if you put the config file somewhere else, you will need to use
the --config=/path/to/build-farm.conf option.
7. When you have that running, it's time to try with cron. Put a line in your
crontab that looks something like this:
43 * * * * cd /location/of/run_build.pl/ ./run_build.pl --nosend --verbose
Again, add the --config option if needed. Notice that this time we didn't
specify nostatus. That means that (after the first run) the script won't
do any build work unless the CVS repo has changed. Check that your cron
job runs (it should email you the results, unless you tell it to send them
elsewhere).
8. By default run_build.pl builds the HEAD branch from CVS. If you want to
build other branches, you can do so by specifying the name on the commandline,
e.g.
run_build.pl REL7_4_STABLE
so, once you have HEAD working, remove the --verbose flag from your crontab,
and add extra cron lines for each branch you want to build regularly.
My crontab (well, one of them) looks something like this:
6 * * * * cd /home/andrew/buildfarm ./run_build.pl --nosend
30 4 * * * cd /home/andrew/buildfarm ./run_build.pl --nosend REL7_4_STABLE
9. Once this is all running happily, you can register to upload your
results to the central server. After that you will edit 3 lines in your
config file, remove the --nosend flags, and you are done. We'll cover
registration in detail when the central server is set up.
10. Resource use. Using the 'update' cvs method (see the config file) results
in significantly lower bandwidth use on both your server and the main
postgresql cvs server than using method 'export'. The price is that
occasionally cvs update is less reliable, and you have a slightly higher
disk usage (about 70Mb more for HEAD branch). Eventually I'd like to
migrate the load entirely off the postgresql cvs
Re: [HACKERS] 7.4.5 losing committed transactions
Jan Wieck [EMAIL PROTECTED] writes: But occasionally there will appear a gap in the data. With the given logic only to increment the counter on a dupkey or after a positive COMMIT response by the backend, IMHO there can only be one if we lose transactions after commit on a crash restart. Hmm. I was able to reproduce this in 7.4.5 but not (so far) in 8.0. What I see in the 7.4.5 postmortem is that the missing rows are present in the table, as you'd expect, and are marked as inserted by a transaction number that *definitely did not commit* according to the WAL log --- there are WAL entries showing it inserting the missing rows, and their btree index entries, but no commit. The post-crash database state seems exactly consistent with what is in WAL. This means either that the server sent a commit message before it had xlog'd the commit, or that Pgtcl mistakenly reported the command as successful when it was not. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4.5 losing committed transactions
On 9/24/2004 5:12 PM, Tom Lane wrote: This means either that the server sent a commit message before it had xlog'd the commit, or that Pgtcl mistakenly reported the command as successful when it was not. Any thoughts? Is it somehow possible that the commit record was still sitting in the shared WAL buffers (unwritten) when the response got sent to the client? That would be the only possibility I can see right now, because Pgtcl used as in that script sits on top of libpq in blocking mode and that pretty much outrules early reporting of COMMAND_OK. Fsync/disk-flush issues can't be the case either because it was only a PM restart without any OS crash involved, and I don't like the idea of whatever*nix forgetting a write(). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4.5 losing committed transactions
Jan Wieck [EMAIL PROTECTED] writes: Is it somehow possible that the commit record was still sitting in the shared WAL buffers (unwritten) when the response got sent to the client? I don't think so. What I see in the two cases I have now are: (1) The backend that was doing the lost transaction is *not* the one I kill -9'd. I know this in both cases because I know which table has the missing entries, and I can see that that instance of the script got a WARNING: terminating connection because of crash of another server process message rather than just a connection closure. (2) There's a pretty fair distance in the WAL log between the entries made by the lost transaction and the checkpoint made by recovery --- a dozen or so other transactions were made and committed in between. It seems unlikely that this transaction would have been the only one to lose a WAL record if something like that had happened. What I'm currently speculating about is that there might be some weirdness associated with the very act of sending out the WARNING. quickdie() isn't doing anything to ensure that the system is in a good state before it calls ereport --- which is probably not so cool considering it is a signal handler. It might be wise to reset at least the elog.c state before doing this. Can you still reproduce the problem if you take out the ereport call in quickdie()? BTW, what led you to develop this test setup ... had you already seen something that made you suspect a data loss problem? regards, tom lane ---(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
[HACKERS] implosion follow up, 7.4.5
The new thread on 7.4.5 losing committed transactions popped up just as I discovered something that was at least unexpected to me. In doing the cleanup from my pg_resetxlogs from today's earlier fun, I found some missing rows and some duplicate row versions showing up in my restore. All of this was within a 90 second period, which makes sense to me. What doesn't make sense to me is that I'm missing 19 records in one table that were committed 3 hours before my crash. There were no errors before the crash, there were no errors in the dump after the pg_resetxlog. I have application logs that confirm these records were present; not only do I have logs showing they were saved, but logs from later processes manipulating these records. I'm running 7.4.5 on RHAS 3 x86-64 on 4x244 32GB system. It's NFS attached. Derogatory remarks about NFS welcome, but you're preaching to the choir. :) The only thing unusual thing I noticed today was abominable performance for several hours before the crash (Load=30, iowait=95%). This machine has been running for weeks with excellent performance - generally 4 times faster than my dual Xeon 2.4Ghz, 12GB RAM, 6x36GB U320 RAID 1+0 systems. Typically in my benchmarking sessions and application runs, I rarely saw any read activity - it appeared that everything was pulled straight out of the disk buffer cache. Today, NFS was choked with reads, despite having 10GB of RAM free (!). Nothing has changed on this machine in at least 4 weeks. Any ideas are appreciated. While I'm sure the crash is hardware/config related, the missing 19 records from something committed 3 hours earlier is confusing. :) As always, any insight is appreciated. We are very committed to PostgreSQL after booting a large Oracle installation out 16 months ago. thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4.5 losing committed transactions
This means either that the server sent a commit message before it had xlog'd the commit, or that Pgtcl mistakenly reported the command as successful when it was not. Any thoughts? Oh, fooey. exec_simple_query calls EndCommand before it calls finish_xact_command, and of course the latter is where the COMMIT really happens. But EndCommand puts the 'C' message for the COMMIT into the outbound message queue. Under normal circumstances this is no problem because we don't pq_flush the data to the client until after the commit is done. But suppose quickdie() is called in between. It will call ereport, which will emit the WARNING message *and pqflush it*. libpq gets the 'C' message and therefore reports that the COMMIT is complete. More generally, any sort of warning message occuring during transaction commit would do the wrong thing. (Errors chance not to, because those will make libpq replace the pending COMMAND_OK result with an error result. I'm not sure what the internal logic in the JDBC driver is, but I guess it acts similarly, or else we'd have heard about this before from JDBC users.) Not sure what the cleanest solution is... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4.5 losing committed transactions
On 9/24/2004 6:37 PM, Tom Lane wrote: Can you still reproduce the problem if you take out the ereport call in quickdie()? Will check ... BTW, what led you to develop this test setup ... had you already seen something that made you suspect a data loss problem? Good guess ... what actually happenend was that after a couple of these getaddrinfo() SIGSEGV's on AIX, one of our Slony-replica was out of sync. Long story short, it all didn't make sense. Now the scary thing is that not only did this crash rollback a committed transaction. Another session had enough time in between to receive a NOTIFY and select the data that got rolled back later. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Parse message problem, maybe a bug?
Hi all, Martijn, a user of Npgsql, sent me a query which is giving problems with postgresql using extended query mode. The problem I'm having is in the Parse message. The parse message I'm sending is as follow: select * from table where $1 in (select some_field from table) Postgresql returns this error: could not determine data type of parameter $1 Severity: ERROR Code: 42P18 But I already have many working samples in the form for example: insert into table(field1, field2) values ($1, $2) The only way I could have it working was by adding the explicit type of parameter so the parse text is, for example for parameter of type text: select * from table where $1::text in (select some_field from table) Is this a bug in Postgresql or is this by design? Thanks in advance. Regards, Francisco Figueiredo Jr. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Possible Optimization?
It would appear that region_id = parent_id is not internally converted to region_id = 1129, despite parent_id being enforced to 1129 at the top level. In this case, it makes a difference in performance of about 4 (2 minutes vs 30 second). The reason I didn't do this myself upfront, is that parent_id is calculated by a function which I didn't want to call twice. I've split the query into 2 parts as a result. Plans attached from PostgreSQL 7.4.5. QUERY: SELECT region_id, region_title FROM bric_extension.region_in_region WHERE parent_id = 1129 AND class = (SELECT region_class FROM region_classes WHERE order (SELECT order FROM region JOIN region_classes ON (region_class = class) WHERE region_id = parent_id) -- 1129 ORDER BY order LIMIT 1); QUERY PLAN Subquery Scan region_in_region (cost=1455.18..1455.21 rows=1 width=45) (actual time=27966.381..27966.400 rows=1 loops=1) InitPlan - Limit (cost=1.65..1.65 rows=1 width=36) (actual time=1.449..1.453 rows=1 loops=1) InitPlan - Hash Join (cost=1.06..1.32 rows=1 width=4) (actual time=0.598..0.931 rows=1 loops=1) Hash Cond: (outer.region_class = (inner.class)::text) - Subquery Scan region_classes (cost=0.00..0.21 rows=7 width=36) (actual time=0.121..0.549 rows=7 loops=1) - Append (cost=0.00..0.14 rows=7 width=0) (actual time=0.103..0.452 rows=7 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.092..0.106 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.072..0.076 rows=1 loops=1) - Subquery Scan *SELECT* 2 (cost=0.00..0.02 rows=1 width=0) (actual time=0.027..0.041 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=1) - Subquery Scan *SELECT* 3 (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.041 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.015 rows=1 loops=1) - Subquery Scan *SELECT* 4 (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.039 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.016 rows=1 loops=1) - Subquery Scan *SELECT* 5 (cost=0.00..0.02 rows=1 width=0) (actual time=0.027..0.041 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=1) - Subquery Scan *SELECT* 6 (cost=0.00..0.02 rows=1 width=0) (actual time=0.026..0.040 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.017 rows=1 loops=1) - Subquery Scan *SELECT* 7 (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..0.039 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.016 rows=1 loops=1) - Hash (cost=1.06..1.06 rows=1 width=14) (actual time=0.125..0.125 rows=0 loops=1) - Seq Scan on region (cost=0.00..1.06 rows=1 width=14) (actual time=0.065..0.097 rows=1 loops=1) Filter: (region_id = 1129::numeric) - Sort (cost=0.33..0.34 rows=7 width=36) (actual time=1.434..1.434 rows=1 loops=1) Sort Key: order - Subquery Scan region_classes (cost=0.00..0.23 rows=7 width=36) (actual time=1.102..1.364 rows=4 loops=1) - Append (cost=0.00..0.16 rows=7 width=0) (actual time=1.083..1.300 rows=4 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.976..0.976 rows=0 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.963..0.963 rows=0 loops=1) One-Time Filter: (10 $0) - Subquery Scan *SELECT* 2 (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)
Re: [HACKERS] 7.4.5 losing committed transactions
Jan Wieck [EMAIL PROTECTED] writes: Now the scary thing is that not only did this crash rollback a committed transaction. Another session had enough time in between to receive a NOTIFY and select the data that got rolled back later. Different session, or same session? NOTIFY is one of the cases that would cause the backend to emit messages within the trouble window between EndCommand and actual commit. I don't believe that that path will do a deliberate pq_flush, but it would be possible that the NOTIFY message fills the output buffer and causes the 'C' message to go out prematurely. If you can actually prove that a *different session* was able to see as committed data that was not safely committed, then we have another problem to look for. I am hoping we have only one nasty bug today ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4.5 losing committed transactions
On 9/24/2004 10:24 PM, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: Now the scary thing is that not only did this crash rollback a committed transaction. Another session had enough time in between to receive a NOTIFY and select the data that got rolled back later. Different session, or same session? NOTIFY is one of the cases that would cause the backend to emit messages within the trouble window between EndCommand and actual commit. I don't believe that that path will do a deliberate pq_flush, but it would be possible that the NOTIFY message fills the output buffer and causes the 'C' message to go out prematurely. If you can actually prove that a *different session* was able to see as committed data that was not safely committed, then we have another problem to look for. I am hoping we have only one nasty bug today ;-) I do mean *different session*. My current theory about how the subscriber got out of sync is this: In Slony the chunks of serializable replication data are applied in one transaction, together with the SYNC event and the events CONFIRM record plus a notify on the confirm relation. The data provider (master or cascading node) does listen on the subscribers (slave) confirm relation. So immediately after the subscriber commits, the provider will pick up the confirm record and knows now that the data has propagated and could be deleted. If now the crash whipes out the committed transaction, the entire SYNC has to be redone. A problem that will be fixed in 1.0.3 can cause the replication engine not to restart immediately, and that probably gave the data providers cleanup procedure enough time to purge the replication data. That way it was possible, that a direct subscriber was still in sync, but a cascaded subscriber behind it wasn't. That constellation automatically ruled out that the update wasn't captured on the master. And since the log forwarding is stored within the same transaction too, the direct subscriber who had the correct data, must at that time have had the correct replication log as well. I guess nobody ever relied that heavily on data to be persistent at the microsecond the NOTIFY arrives ... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4.5 losing committed transactions
I said: Oh, fooey. exec_simple_query calls EndCommand before it calls finish_xact_command, Fooey again --- that theory is all wrong. Back to the drawing board. I have managed to reproduce the bug on CVS tip, btw. But it's very painful to make it happen. Have you got any tips for making it more probable? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4.5 losing committed transactions
Jan Wieck [EMAIL PROTECTED] writes: I guess nobody ever relied that heavily on data to be persistent at the microsecond the NOTIFY arrives ... Sure they have. In theory you cannot see a NOTIFY before the sending transaction commits, because the sender is holding a lock on pg_notify and you can't even find out that you've been notified until he releases it. Your idea that the COMMIT WAL record is getting dropped would fit the facts, but I really am having a hard time believing it. Why would the COMMIT record be more prone to loss than any other record? All the cases I have seen so far have the right number of non-COMMIT records in the log, so the bogus transaction is not getting lost altogether. 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])
