Re: [HACKERS] Function to kill backend
> Tom Lane wrote: >>> it would definitely need to be a lot more constrained than >>> send-any-signal-to-any-postgres-process ... even for a superuser, >>> that's a mighty fat-gauge foot-gun. > >> What sort of constraints do you have in mind? > >I'd limit it to SIGINT (query cancel) and SIGTERM (fast shutdown), Actually, that is a restriction that's already there - just didn't get into those details. Since the functino as I wrote it so far just takes signal name as a string (can't rely on signal numbers being identical across platforms, right?), and then comparing it with a fixed set of signals. >and I'm not even real sure about SIGTERM. That facility is designed to >work in the case of shutting down all backends together --- >I'm not sure >I want to promise that it behaves pleasantly to SIGTERM one backend and >leave the rest going. Nor do I see a real good use-case for it. Really? Then what is the recommended way of shutting down a backend that you are not connected to, as an administrator? Even if you are logged in with shell access? I may have been doing things wrong for a long time, because I have certainly killed backends with TERM many times without problems. If that's not safe, there really ought to be a tip on the mailinglists to complement the "don't kill -9 the postmaster" with "and don't ever kill the backends, period"? I'm sure I'm not the only one who has done that... >Also, no killing processes that aren't regular backends (eg, the >bgwriter, the stats processes, and most especially the postmaster). That sounds like a reasonable limitation to add. Either by specifically excluding these processes, or by limiting it to only work on the backends currently listed in pg_stat_activity. >Another point is that killing by PID is not necessarily what >you want to >do --- kill by transaction ID might be a better API, especially for >query-cancel cases. Well, in my scenarios, killing by PID is what I need. But I guess transaction IDs might be added to the pg_stat_activity, which would give me the same functionality (I usually check that one first to see what a backend does, before I do anything) - and then some, because the transaction id carries other information as well. Question on that - how will it handle an idle backend (that has not explicitly opened a transaction, and is not executing a command in an implicit transaction)? > I think any such facility is inherently a security risk, since it means > that a remote attacker who's managed to break into your superuser > account can randomly zap other backends. Now admittedly there's plenty > of other mischief he can do with superuser privs, but that doesn't mean > we should hand him a pre-loaded, pre-sighted cannon. > Having to log into the database server locally to execute such > operations doesn't seem that bad to me. It does to me. I prefer being able to admin the server without having to do a separate login. I also much prefer being able to delegate the capability to terminate a backend, interrupt a long-running query, etc to someone who does not have to have shell access on the server. I guess it depends on the environment. > Bruce Momjian <[EMAIL PROTECTED]> writes: >> If they can read/write your data (as superuser), killing backends is the >> least worry. That's pretty much the assumption I was working under. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problems Vacuum'ing
Tom Lane wrote: It's the oldest xmin of any transaction that's local to your database, but those xmin values themselves were computed globally --- so what matters is the oldest transaction that was running when any local transaction started. In this case I expect it's the VACUUM's own transaction that's seeing the other guy as determining its xmin. We could fix this by making every transaction compute, and advertise in the PGPROC array, both local and global xmin values. In previous iterations of this discussion we concluded that the extra cycles (which would be spent in *every* transaction start) could not be justified by making VACUUM better able to reclaim space in the face of misbehaving clients. I don't suppose it is possible to find out to which database a transaction was local after it was committed? That conclusion might be wrong, but it's not instantly obvious that it is... Would it be possible to find out how long a transaction has been open already? It is quite simple to find the oldest uncommitted transaction using the pg_locks table, but from there we don't know yet how old it is. If it were possible to determine when it started the vacuum verbose output could perhaps include something like : DETAIL: 113590 dead row versions cannot be removed yet. Transaction 1234567 is has been in progress for 01:45:21, only dead row versions committed before that are removable. Nonremovable row versions range from 64 to 88 bytes long. Jochem PS Sorry about messing up the threading, I read the archives. -- I don't get it immigrants don't work and steal our jobs - Loesje ---(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] Inconsistent behavior on Array & Is Null?
Joe Conway <[EMAIL PROTECTED]> writes: > Greg Stark wrote: > > This really ought to work, it obviously shouldn't allow you to set a[5] and > > then surreptitiously move it to a[1]. But nor should it generate an error, > > since I may well have a specific meaning for a[5] and may be planning to fill > > in a[1]..a[4] later. > > The logical thing to do, I think, is pre-fill the entries a[1]..a[4] with > > null. This could be implemented by actually storing the NULLs or else storing > > some notation that's used to adjust the base of the index to save space. > > I agree. I had always envisioned something exactly like that once we supported > NULL elements. As far as the implementation goes, I think it would be very > similar to tuples -- a null bitmask that would exist if any elements are NULL. Well you might still want to store an internal "all indexes below this are null". That way update foo set a[1000]=1 doesn't require storing even a bitmap for the first 999 elements. Though might make maintaining the bitmap kind of a pain. Maintaining the bitmap might be kind of a pain anyways though because unlike tuples the array size isn't constant. > A related question is how to deal with non-existing array elements. Until now, > you could do: I would have to think about it some more, but my first reaction is that looking up [0] should generate an error if there can never be a valid entry at [0]. But looking up indexes above the highest index should return NULL. There are two broad use cases I see for arrays. Using them to represent tuples where a[i] means something specific for each i, and using them to represent sets where order doesn't matter. In the former case I might want to initialize my column to an empty array and set only the relevant columns as needed. In that case returning NULL for entries that haven't been set yet whether they're above the last entry set or below is most consistent. In the latter case you really don't want to be looking up anything past the end and don't want to be storing NULLs at all. So it doesn't really matter what the behaviour is for referencing elements past the end, but you might conceivably want to write code like "while (e = a[i++]) ...". Incidentally I'm using both of these models in my current project. I use text[] to represent localized strings, str[1] is always English and str[2] is always French. When I need to expand to more languages I'll add str[3] for Spanish or whatever else. It would be a problem if I stored something in str[2] and then found it in str[1] later. And it could be a bit awkward to have to prefill str[3] everywhere in the whole database when the time comes. Having it just silently return NULL would be more convenient. I also use arrays for sets in a cache table. In that case there would never be NULLs and the arrays are variable sized. Sometimes with thousands of entries. The purpose of the cache table is to speed things up so storing the arrays densely is important. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Better support for whole-row operations and composite
Tom Lane wrote: No, it's a HeapTupleHeader pointer. You need to reconstruct a HeapTuple on top of that to work with heap_getattr and most other core backend routines. Thanks. For triggers, I was previously building up the arguments thus: slot = TupleDescGetSlot(tupdesc); slot->val = trigdata->tg_trigtuple; arg[7] = PointerGetDatum(slot); I suppose now I should do this instead? arg[7] = PointerGetDatum(trigdata->tg_trigtuple->t_data); Also don't forget to ensure that you detoast the datum; this is not useful at the moment but will be important Real Soon Now. I added standard argument-fetch macros to fmgr.h to help with the detoasting bit. OK. This is the net result: #ifdef PG_VERSION_75_COMPAT Oid tupType; int32 tupTypmod; TupleDesc tupdesc; HeapTuple tuple = palloc(sizeof(HeapTupleData)); HeapTupleHeader tuple_hdr = DatumGetHeapTupleHeader(arg[i]); tupType = HeapTupleHeaderGetTypeId(tuple_hdr); tupTypmod = HeapTupleHeaderGetTypMod(tuple_hdr); tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod); tuple->t_len = HeapTupleHeaderGetDatumLength(tuple_hdr); ItemPointerSetInvalid(&(tuple->t_self)); tuple->t_tableOid = InvalidOid; tuple->t_data = tuple_hdr; PROTECT(el = pg_tuple_get_r_frame(1, &tuple, tupdesc)); pfree(tuple); #else TupleTableSlot *slot = (TupleTableSlot *) arg[i]; HeapTuple tuple = slot->val; TupleDesc tupdesc = slot->ttc_tupleDescriptor; PROTECT(el = pg_tuple_get_r_frame(1, &tuple, tupdesc)); #endif /* PG_VERSION_75_COMPAT */ Given the above changes, it's almost working now -- only problem left is with triggers: insert into foo values(11,'cat99',1.89); + ERROR: record type has not been registered + CONTEXT: In PL/R function rejectfoo delete from foo; + ERROR: cache lookup failed for type 0 + CONTEXT: In PL/R function rejectfoo (and a few other similar failures) Any ideas why the trigger tuple type isn't registered, or what I'm doing wrong? Thanks, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Inconsistent behavior on Array & Is Null?
Greg Stark wrote: Joe Conway <[EMAIL PROTECTED]> writes: I agree. I had always envisioned something exactly like that once we supported NULL elements. As far as the implementation goes, I think it would be very similar to tuples -- a null bitmask that would exist if any elements are NULL. Well you might still want to store an internal "all indexes below this are null". That way update foo set a[1000]=1 doesn't require storing even a bitmap for the first 999 elements. Though might make maintaining the bitmap kind of a pain. Maintaining the bitmap might be kind of a pain anyways though because unlike tuples the array size isn't constant. I don't think it will be worth the complication to do other than a straight bitmap -- at least not the first attempt. A related question is how to deal with non-existing array elements. Until now, you could do: I would have to think about it some more, but my first reaction is that looking up [0] should generate an error if there can never be a valid entry at [0]. But looking up indexes above the highest index should return NULL. There are two broad use cases I see for arrays. Using them to represent tuples where a[i] means something specific for each i, and using them to represent sets where order doesn't matter. In the former case I might want to initialize my column to an empty array and set only the relevant columns as needed. In that case returning NULL for entries that haven't been set yet whether they're above the last entry set or below is most consistent. Maybe, but you're still going to need to explicitly set the real upper bound element in order for the length/cardinality to be correct. In other words, if you really want an array with elements 1 to 1000, but 2 through 1000 are NULL, you'll need to explicitly set A[1000] = NULL; otherwise we'll have no way of knowing that you really want 1000 elements. Perhaps we'll want some kind of array_init function to create an array of a given size filled with all NULL elements (or even some arbitrary constant element). I'd think given the preceding, it would make more sense to throw an error whenever trying to access an element greater than the length. In the latter case you really don't want to be looking up anything past the end and don't want to be storing NULLs at all. So it doesn't really matter what the behaviour is for referencing elements past the end, but you might conceivably want to write code like "while (e = a[i++]) ...". See reasoning as above. And if you did somehow wind up with a "real" NULL element in this scenario, you'd never know about it. The looping could always be: while (i++ <= length) or for (i = 1; i <= length, i++) Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Better support for whole-row operations and composite
Joe Conway wrote: Given the above changes, it's almost working now -- only problem left is with triggers: insert into foo values(11,'cat99',1.89); + ERROR: record type has not been registered + CONTEXT: In PL/R function rejectfoo delete from foo; + ERROR: cache lookup failed for type 0 + CONTEXT: In PL/R function rejectfoo (and a few other similar failures) Any ideas why the trigger tuple type isn't registered, or what I'm doing wrong? A little more info on this. It appears that the tuple type is set to either 2249 (RECORDOID) or 0. In the case of RECORDOID this traces all the way back to here: /* * CreateTemplateTupleDesc * * This function allocates and zeros a tuple descriptor structure. * * Tuple type ID information is initially set for an anonymous record * type; caller can overwrite this if needed. * */ But the type id is never overwritten for a BEFORE INSERT trigger. It appears that somewhere it is explictly set to InvalidOid for both BEFORE DELETE and AFTER INSERT triggers (and possibly others). My take is that we now need to explicitly set the tuple type id for INSERT/UPDATE/DELETE statements -- not sure where the best place to do that is though. Does this sound correct? Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function to kill backend
Tom, > > Seems like useful functionality. Right now, how does an administrator > > kill another backend from psql? They can't. > > The question to ask is "should they be able to?" And the answer is, "Yes". This is a commonly requested feature by DBA's migrating from SQL Server and Oracle.In those databases, there is a GUI to monitor database requests, and potentially kill them to resolve deadlocks or runaway queries (though, in the case of SQL server, it does not work). Right now, it is very difficult for any of our GUI projects to construct such an interface due to the necessity of root shell access. > I think any such facility is inherently a security risk, since it means > that a remote attacker who's managed to break into your superuser > account can randomly zap other backends. Now admittedly there's plenty > of other mischief he can do with superuser privs, but that doesn't mean > we should hand him a pre-loaded, pre-sighted cannon. And requiring DBAs to use root shell access whenever they want to stop a runaway query is somehow a good security approach? If nothing else, it exposes lots of DBAs to the temptation to use SIGKILL instead off SIGINT or SIGTERM, making the database shut down. And I, personally, worry about the number of root shells I have to use, becuase every once in a while I forget and leave one open at the end of the day. Killing backends with runaway queries is a routine administrative task. It should be possible to accomplish it remotely, using tools provided by PostgreSQL instead of the command shell, because then it is possible for us to limit what those tools can do. Further, if an intruder has superuser access, having them kill random backends is the last thing I'm worried about. "DROP DATABASE" ranks a lot higher. In fact, it would be nice if they started killing random backends because then I'd know something was wrong. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function to kill backend
This is a commonly requested feature by DBA's migrating from SQL Server and Oracle.In those databases, there is a GUI to monitor database requests, and potentially kill them to resolve deadlocks or runaway queries (though, in the case of SQL server, it does not work). Right now, it is very difficult for any of our GUI projects to construct such an interface due to the necessity of root shell access. Yes, MySQL can do it too. http://www.mysql.com/doc/en/KILL.html I would love to have a KILL command in postgres. I don't know how you would restrict it to only being able to kill postgres backends though. Chris ---(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] Function to kill backend
On 2-Apr-04, at 5:16 PM, Andrew Dunstan wrote: If' we're going to have this shouldn't it be a proper command? Why? What benefit would this offer over implementing this feature as a function? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Better support for whole-row operations and composite types
Joe Conway <[EMAIL PROTECTED]> writes: > For triggers, I was previously building up the arguments thus: > slot = TupleDescGetSlot(tupdesc); > slot->val = trigdata->tg_trigtuple; > arg[7] = PointerGetDatum(slot); > I suppose now I should do this instead? > arg[7] = PointerGetDatum(trigdata->tg_trigtuple->t_data); Hm, no, that won't work because a tuple being passed to a trigger probably isn't going to contain valid type information. The API for calling triggers is different from calling ordinary functions, so I never thought about trying to make it look the same. At what point are you trying to do the above, anyway? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Better support for whole-row operations and composite
Joe Conway <[EMAIL PROTECTED]> writes: >> Any ideas why the trigger tuple type isn't registered, or what I'm doing >> wrong? > A little more info on this. It appears that the tuple type is set to > either 2249 (RECORDOID) or 0. After further thought, we could possibly make it work for BEFORE triggers, but there's just no way for AFTER triggers: in that case what you are getting is an image of what went to disk, which is going to contain transaction info not type info. If you really want the trigger API for PL/R to be indistinguishable from the function-call API, then I think you will need to copy the passed tuple and insert type information. This is more or less what ExecEvalVar does now in the whole-tuple case (the critical code is actually in heap_getsysattr though): HeapTupleHeaderdtup; dtup = (HeapTupleHeader) palloc(tup->t_len); memcpy((char *) dtup, (char *) tup->t_data, tup->t_len); HeapTupleHeaderSetDatumLength(dtup, tup->t_len); HeapTupleHeaderSetTypeId(dtup, tupleDesc->tdtypeid); HeapTupleHeaderSetTypMod(dtup, tupleDesc->tdtypmod); result = PointerGetDatum(dtup); regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Better support for whole-row operations and composite
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: For triggers, I was previously building up the arguments thus: slot = TupleDescGetSlot(tupdesc); slot->val = trigdata->tg_trigtuple; arg[7] = PointerGetDatum(slot); I suppose now I should do this instead? arg[7] = PointerGetDatum(trigdata->tg_trigtuple->t_data); Hm, no, that won't work because a tuple being passed to a trigger probably isn't going to contain valid type information. The API for calling triggers is different from calling ordinary functions, so I never thought about trying to make it look the same. At what point are you trying to do the above, anyway? That's a shame -- it used to work fine -- done this way so the same function could handle tuple arguments to regular functions, and old/new tuples to trigger functions. It is in plr_trigger_handler(); vaguely similar to pltcl_trigger_handler(). I'll have to figure out a workaround I guess. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Function to kill backend
Josh Berkus <[EMAIL PROTECTED]> writes: > Killing backends with runaway queries is a routine administrative > task. Cancelling runaway queries is a routine task. I'm less convinced that a remote kill (ie SIGTERM) facility is such a great idea. 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
Re: [HACKERS] Better support for whole-row operations and composite
Tom Lane wrote: If you really want the trigger API for PL/R to be indistinguishable from the function-call API, then I think you will need to copy the passed tuple and insert type information. This is more or less what ExecEvalVar does now in the whole-tuple case (the critical code is actually in heap_getsysattr though): That got me there. It may not be the best in terms of pure speed, but it is easier and simpler than refactoring, at least at the moment. And I don't think the reason people will choose PL/R for triggers is speed in any case ;-) Thanks! Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Function to kill backend
Tom Lane wrote: Josh Berkus <[EMAIL PROTECTED]> writes: Killing backends with runaway queries is a routine administrative task. Cancelling runaway queries is a routine task. I'm less convinced that a remote kill (ie SIGTERM) facility is such a great idea. Of course, cancelling runaway queries on Oracle is only a necessity if the DBA hasn't made use of resource limits - PROFILEs. ;-) Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function to kill backend
Neil Conway said: > On 2-Apr-04, at 5:16 PM, Andrew Dunstan wrote: >> If' we're going to have this shouldn't it be a proper command? > > Why? What benefit would this offer over implementing this feature as a > function? > psql help cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
