[HACKERS] Can we simplify win32 threading code
Currently PG win32 port mainly does the following to simulate signals: (*) the process who kill the signal: - put the signal in a named pipe, then it is done; (*) the process who should receive the signal: - a non-stop thread pg_signal_thread will read the signal from the pipe, and start another thread pg_signal_dispatch_thread, which puts the signal in a local memory variable pg_signal_queue and SetEvent(local_memory_event_variable); - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; Could we simplify this process like this: (*) the process who kill the signal: - put the signal in a *shared memory variable pg_signal_queue* and SetEvent(*shared_memory_event_variable*), then it is done; (*) the process who should receive the signal: - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; -- there is no other threads of this process; Any show-stop reasons of not doing this? Regards, Qingqing ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] foreign keys and RI triggers
I spent a little while looking into a performance issue with a large UPDATE on a table with foreign keys. A few questions: (1) When a PK table is updated, we skip firing the per-row UPDATE RI triggers if none of the referenced columns in the PK table have been modified. However, AFAICS we do not apply a similar optimization for updates of foreign key tables: if a user does not modify the foreign key column, we needn't check for the presence of the FK column value in the primary key table. Is there a reason we don't implement this? (2) For per-row RI triggers of all kinds, we save the trigger under CurTransactionContext and invoke it at the end of the current query. There is not even overflow to disk (the report that prompted me to look into this was someone's database crashing because they kept running OOM when doing an UPDATE of a large table with FKs on a pretty lowend machine). While avoiding consuming a lot of memory for queued trigger execution is worth doing anyway, ISTM we needn't queue RI triggers in the first place. Is there a reason we can't just invoke after-row RI triggers immediately? (Hmm, I suppose we would need to defer firing the trigger until the command ID is incremented if the foreign key references its own table. But even so, this should not be an issue for non-self-referential foreign keys.) (3) This is minor, but AFAICS RI_FKey_check_upd() is not used -- RI_FKey_check_ins() is used to validate both inserts and updates on tables with foreign keys (see tablecmds.c circa 4423). Both functions are just wrappers over RI_FKey_check() anyway. This is rather confusing; would anyone object if I removed both functions and made RI_FKey_check() public? -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] soundex and metaphone
Jonah H. Harris [EMAIL PROTECTED] writes: Hey everyone, I've been working with a couple people who didn't know that soundex and metaphone were included in the distribution as contrib modules. While it's their fault that they didn't check contrib, soundex is pretty common among database systems and I was wondering if there was a reason it is not included as a core function? Because no one's taken ownership of the code and made a case for integrating it into the core backend. -Doug ---(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] soundex and metaphone
At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? Douglas McNaught wrote: Jonah H. Harris [EMAIL PROTECTED] writes: Hey everyone, I've been working with a couple people who didn't know that soundex and metaphone were included in the distribution as contrib modules. While it's their fault that they didn't check contrib, soundex is pretty common among database systems and I was wondering if there was a reason it is not included as a core function? Because no one's taken ownership of the code and made a case for integrating it into the core backend. -Doug ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] foreign keys and RI triggers
On Thu, 26 May 2005, Neil Conway wrote: I spent a little while looking into a performance issue with a large UPDATE on a table with foreign keys. A few questions: (1) When a PK table is updated, we skip firing the per-row UPDATE RI triggers if none of the referenced columns in the PK table have been modified. However, AFAICS we do not apply a similar optimization for updates of foreign key tables: if a user does not modify the foreign key column, we needn't check for the presence of the FK column value in the primary key table. Is there a reason we don't implement this? Are you sure? RI_FKey_Check seems to have a section on TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the old row wasn't part of this transaction. I'm not sure why it's doing the transaction id check, but it looks like it will do an equals check at least some of the time. (2) For per-row RI triggers of all kinds, we save the trigger under CurTransactionContext and invoke it at the end of the current query. There is not even overflow to disk (the report that prompted me to look into this was someone's database crashing because they kept running OOM when doing an UPDATE of a large table with FKs on a pretty lowend machine). While avoiding consuming a lot of memory for queued trigger execution is worth doing anyway, ISTM we needn't queue RI triggers in the first place. Is there a reason we can't just invoke after-row RI triggers immediately? If I'm understanding the question, there's two things. First is deferred constraints and the second is that constraints happen after the entire statement. In a case like: insert into pk values(2); insert into pk values(1); insert into fk values(2); update pk set key=key+1; In no action, that's not an error AFAIK because the constraint is satisfied at end of statement. If the order of updates happened such that the key=2 row were updated first we couldn't know whether or not the constraint would be satisfied by later updates to the same table. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] foreign keys and RI triggers
Stephan Szabo wrote: Are you sure? RI_FKey_Check seems to have a section on TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the old row wasn't part of this transaction. Well, regardless of how RI_FKey_Check() itself works, ISTM there is no need to enqueue the RI trigger in the first place. That's when the update-on-PK-table optimization is applied -- see trigger.c circa 3005. The specific case I was looking at resulted in the postgres backend allocating a few hundred MB just to store all the pending RI triggers, even though the UPDATE in question didn't change the foreign key field, so it didn't matter a great deal how quickly RI_FKey_Check() was able to bail out. If I'm understanding the question, there's two things. First is deferred constraints Right -- obviously we can't fire RI triggers for deferred constraints immediately. Immediate constraints are the common case, though. constraints happen after the entire statement. In a case like: insert into pk values(2); insert into pk values(1); insert into fk values(2); update pk set key=key+1; Hmm, good point. But ISTM there are still some circumstances in which we can safely check the RI trigger immediately, rather than at end of statement. For example, updating the FK table, inserting into the FK table, or deleting from the PK table. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] foreign keys and RI triggers
On Thu, 26 May 2005, Stephan Szabo wrote: On Thu, 26 May 2005, Neil Conway wrote: (2) For per-row RI triggers of all kinds, we save the trigger under CurTransactionContext and invoke it at the end of the current query. There is not even overflow to disk (the report that prompted me to look into this was someone's database crashing because they kept running OOM when doing an UPDATE of a large table with FKs on a pretty lowend machine). While avoiding consuming a lot of memory for queued trigger execution is worth doing anyway, ISTM we needn't queue RI triggers in the first place. Is there a reason we can't just invoke after-row RI triggers immediately? If I'm understanding the question, there's two things. First is deferred constraints and the second is that constraints happen after the entire statement. In a case like: insert into pk values(2); insert into pk values(1); insert into fk values(2); update pk set key=key+1; In no action, that's not an error AFAIK because the constraint is satisfied at end of statement. If the order of updates happened such that the key=2 row were updated first we couldn't know whether or not the constraint would be satisfied by later updates to the same table. Although, we could almost run referential actions that way. The almost comes from some behavior for set default cases to make sure the default still exists which I think would still have to happen at end of statement to be spec complient (I don't have easy access to my copy of SQL99 from here). I think we're still a little short of entirely complient on timing in any case because unless I'm misremembering constraint checks happen after user defined triggers and referential actions happen before which would be difficult if not impossible to do right now with the way we fire triggers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] soundex and metaphone
Jonah H. Harris wrote: At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? Soundex is really only useful for English names with English pronunciation. If we were to adapt a phonetic algorithm into the core, I'd like to see something more general. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Can we simplify win32 threading code
Currently PG win32 port mainly does the following to simulate signals: (*) the process who kill the signal: - put the signal in a named pipe, then it is done; (*) the process who should receive the signal: - a non-stop thread pg_signal_thread will read the signal from the pipe, and start another thread pg_signal_dispatch_thread, which puts the signal in a local memory variable pg_signal_queue and SetEvent(local_memory_event_variable); - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; Could we simplify this process like this: (*) the process who kill the signal: - put the signal in a *shared memory variable pg_signal_queue* and SetEvent(*shared_memory_event_variable*), then it is done; (*) the process who should receive the signal: - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; -- there is no other threads of this process; Any show-stop reasons of not doing this? Yeah, that should work. With one shared memory segment and one event for each process, of course. The event can be the same one as is used now, only it has to be named so it can be accessed externally. It would do away with the thread, certainly. But it's not quite as simple as you outline above - you'll need to replace the critical section locking (easy, lightweight) with a mutex or something like that (more complex, more heavy weight). But probably named pipes is more heavy, yes. You'll also need some way of delivering the feedback, I think - kill(0) is supposed to tell you if there is a live process in th eother end, so you can't just throw the signal out and hope for the best. I think the named pipe parts of things is a leftover from back when we were using APCs to interrupt the main thread - which required a separate thread. But since we can't do that, this sounds like a reasonable simplification. //Magnus ---(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] soundex and metaphone
Peter, I don't disagree with you that a more generalized function would also be good, just that soundex is common and would be helpful if it were built-in. Peter Eisentraut wrote: Jonah H. Harris wrote: At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? Soundex is really only useful for English names with English pronunciation. If we were to adapt a phonetic algorithm into the core, I'd like to see something more general. ---(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] Can we simplify win32 threading code
Magnus Hagander wrote: (*) the process who kill the signal: - put the signal in a *shared memory variable pg_signal_queue* and SetEvent(*shared_memory_event_variable*), then it is done; (*) the process who should receive the signal: - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; -- there is no other threads of this process; Any show-stop reasons of not doing this? Yeah, that should work. With one shared memory segment and one event for each process, of course. The event can be the same one as is used now, only it has to be named so it can be accessed externally. I assume that this will not break the use of pg_ctl to deliver pseudo-signals. That would be a show-stopper. cheers andrew ---(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] Can we simplify win32 threading code
(*) the process who kill the signal: - put the signal in a *shared memory variable pg_signal_queue* and SetEvent(*shared_memory_event_variable*), then it is done; (*) the process who should receive the signal: - the main thread of this process could be awakened by the event from waiting status(like semop()) or CHECK_FOR_INTERRUPTS() actively; -- there is no other threads of this process; Any show-stop reasons of not doing this? Yeah, that should work. With one shared memory segment and one event for each process, of course. The event can be the same one as is used now, only it has to be named so it can be accessed externally. I assume that this will not break the use of pg_ctl to deliver pseudo-signals. That would be a show-stopper. It shouldn't, but there is one concern: it has to be created in the global namespace. On older windows there is no different, but on modern windows with terminal services in it it does. It might require some permissions hackings - I don't know what the default permissinos are on these things. But I *think* it should work fine. //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] soundex and metaphone
Jonah H. Harris wrote: At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? I take it you mean apart from the fact that soundex is horribly limited and out of data and probably nobody should be using it? cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] foreign keys and RI triggers
Stephan Szabo [EMAIL PROTECTED] writes: Are you sure? RI_FKey_Check seems to have a section on TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the old row wasn't part of this transaction. I'm not sure why it's doing the transaction id check, but it looks like it will do an equals check at least some of the time. I think the reason for the xact check is that if we have deferred triggers and we do begin; insert new FK value; update new row; commit; then when the trigger for the INSERT event fires, it will do nothing because the tuple it's triggered on is now dead. So the trigger for the UPDATE event had better make the check. It's possible we could skip the UPDATE event if we could be certain the INSERT trigger had already fired, but I'm not sure how to be certain about that. While avoiding consuming a lot of memory for queued trigger execution is worth doing anyway, ISTM we needn't queue RI triggers in the first place. Is there a reason we can't just invoke after-row RI triggers immediately? If I'm understanding the question, there's two things. First is deferred constraints and the second is that constraints happen after the entire satement. Right. RI constraints are actually the only kind we do right in terms of enforcing the check when the SQL spec says we should. The thoughts I've had about special-casing RI events to save memory have to do with the idea of lossy storage. As you accumulate more per-row events, at some point it becomes more efficient to forget the individual rows and just reapply the original full-table check query when it's time to check the constraint. So if we could recognize RI events as being associated with the same constraint, and keep track of how many are pending for each constraint, we could make a decision to discard the queue and instead register one event to apply a full-table check. It's not clear how to do that efficiently though. 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])
[HACKERS] Regression failures: time, timetz, horology
I'm getting time, timetz, and horology regression failures in HEAD on Solaris 9 / gcc 3.4.2. So are other machines in the build farm, such as this one: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sharkdt=2005-05-26%2004:21:00 I'm getting the same regression failures shown in that link; here's an example: *** *** 34,45 SELECT f1 AS Five FROM TIME_TBL WHERE f1 '05:06:07'; Five ! - 11:59:00 12:00:00 12:01:00 23:59:00 ! 23:59:59.99 (5 rows) SELECT f1 AS None FROM TIME_TBL WHERE f1 '00:00'; --- 34,45 SELECT f1 AS Five FROM TIME_TBL WHERE f1 '05:06:07'; Five ! -- 11:59:00 12:00:00 12:01:00 23:59:00 ! 23:59:59.990 (5 rows) SELECT f1 AS None FROM TIME_TBL WHERE f1 '00:00'; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Rod Taylor
Hi Rod Taylor, ajs at crankycanuck.ca wrote On Thu, May 26, 2005 at 11:18:12AM +0630, Narasimha.Sridhar at cybernetsoft.com wrote: Hi Can anybody tell me whether Slony-I replication works with Postgres v7.2.1. I dont see any document specifying the minimum postgres version required by Slony-I. Slony-I requires 7.3 or later. Rod Taylor, on this list, has mentioned that he hacked it to work for his 7.2 - later version pgrade. He's aso told me that it wasn't exactly fun; but it's not impossible, for special cases. I don't think I'd want to run with it for any length of time. At the VERY LEAST, you'd best get off 7.2.1 to something later (i.e. the last 7.2 release). ISTR some nasty bug or other in 7.2.1. I heard for Andrew Sullivan that you managed to hack Slony-I to work for postgre 7.2. Can you tell me how you did as it would me helpful to me Thanks Regards Sridhar
Re: [HACKERS] soundex and metaphone
Jonah H. Harris [EMAIL PROTECTED] writes: At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? Is it really ready for prime time? For one thing, a quick look shows no evidence of being multibyte-ready. There's a fair amount of cleanup of random private coding conventions (META_MALLOC!?) to be done too. Doug's point is valid: there's some actual work needed here, not just arguing to shove the code from point A to point B. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] foreign keys and RI triggers
On Fri, 27 May 2005, Neil Conway wrote: Stephan Szabo wrote: Are you sure? RI_FKey_Check seems to have a section on TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the old row wasn't part of this transaction. Well, regardless of how RI_FKey_Check() itself works, ISTM there is no need to enqueue the RI trigger in the first place. That's when the update-on-PK-table optimization is applied -- see trigger.c circa 3005. The specific case I was looking at resulted in the postgres backend allocating a few hundred MB just to store all the pending RI triggers, even though the UPDATE in question didn't change the foreign key field, so it didn't matter a great deal how quickly RI_FKey_Check() was able to bail out. Okay, I can't think of cases even with triggers and the like where removing the check on equal valued rows would give appreciably different results, but I haven't thought too hard about it. If I'm understanding the question, there's two things. First is deferred constraints Right -- obviously we can't fire RI triggers for deferred constraints immediately. Immediate constraints are the common case, though. constraints happen after the entire statement. In a case like: insert into pk values(2); insert into pk values(1); insert into fk values(2); update pk set key=key+1; Hmm, good point. But ISTM there are still some circumstances in which we can safely check the RI trigger immediately, rather than at end of statement. For example, updating the FK table, inserting into the FK table, or deleting from the PK table. Unfortunately, I don't think so, if my assumption that user triggers are supposed to happen before constraint checks is true. In that case, we must wait until not only the action but all triggers fired by that action happen in order to run the constraint check because a trigger could make an otherwise invalid row valid. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] foreign keys and RI triggers
On Thu, 26 May 2005, Stephan Szabo wrote: On Fri, 27 May 2005, Neil Conway wrote: Stephan Szabo wrote: Are you sure? RI_FKey_Check seems to have a section on TRIGGER_FIRED_BY_UPDATE which seems to check if the keys are equal if the old row wasn't part of this transaction. Well, regardless of how RI_FKey_Check() itself works, ISTM there is no need to enqueue the RI trigger in the first place. That's when the update-on-PK-table optimization is applied -- see trigger.c circa 3005. The specific case I was looking at resulted in the postgres backend allocating a few hundred MB just to store all the pending RI triggers, even though the UPDATE in question didn't change the foreign key field, so it didn't matter a great deal how quickly RI_FKey_Check() was able to bail out. Okay, I can't think of cases even with triggers and the like where removing the check on equal valued rows would give appreciably different results, but I haven't thought too hard about it. Err, except the case that Tom mentions in his message. ---(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] soundex and metaphone
Tom Lane wrote: Jonah H. Harris [EMAIL PROTECTED] writes: At a minimum I think we should support soundex in the core. I'm willing to move soundex and metaphone into the backend. Does anyone see a reason not to do so? Is it really ready for prime time? For one thing, a quick look shows no evidence of being multibyte-ready. There's a fair amount of cleanup of random private coding conventions (META_MALLOC!?) to be done too. Doug's point is valid: there's some actual work needed here, not just arguing to shove the code from point A to point B. Well, META_MALLOC occurs in part of the code that he didn't ask for ... it was inherited from the perl module code that I adapted to do double metaphone. And a minimal wrapper suited my purposes at the time just fine. But the point is well taken nevertheless. cheers andrew ---(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] Regression failures: time, timetz, horology
Michael Fuhr [EMAIL PROTECTED] writes: I'm getting time, timetz, and horology regression failures in HEAD on Solaris 9 / gcc 3.4.2. So are other machines in the build farm, such as this one: I'll bet a nickel this broke it: 2005-05-25 23:48 momjian * src/: backend/utils/adt/datetime.c, interfaces/ecpg/pgtypeslib/interval.c: Display only 9 not 10 digits of precision for timestamp values when using non-integer timestamps. This prevents the display of rounding errors for common values like days 32. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] foreign keys and RI triggers
Stephan Szabo [EMAIL PROTECTED] writes: Okay, I can't think of cases even with triggers and the like where removing the check on equal valued rows would give appreciably different results, but I haven't thought too hard about it. Err, except the case that Tom mentions in his message. But the check could incorporate the same transaction ID test already in use. I think Neil is right that it'd be a win to apply the test before enqueueing the trigger instead of after. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Regression failures: time, timetz, horology
Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: I'm getting time, timetz, and horology regression failures in HEAD on Solaris 9 / gcc 3.4.2. So are other machines in the build farm, such as this one: I'll bet a nickel this broke it: 2005-05-25 23:48 momjian * src/: backend/utils/adt/datetime.c, interfaces/ecpg/pgtypeslib/interval.c: Display only 9 not 10 digits of precision for timestamp values when using non-integer timestamps. This prevents the display of rounding errors for common values like days 32. Yea, backing out. I thought I tested it, but I guess not. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] foreign keys and RI triggers
On Thu, 26 May 2005, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Okay, I can't think of cases even with triggers and the like where removing the check on equal valued rows would give appreciably different results, but I haven't thought too hard about it. Err, except the case that Tom mentions in his message. But the check could incorporate the same transaction ID test already in use. I think Neil is right that it'd be a win to apply the test before enqueueing the trigger instead of after. Good point. That would help in many cases anyway. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] foreign keys and RI triggers
On Thu, 26 May 2005, Tom Lane wrote: The thoughts I've had about special-casing RI events to save memory have to do with the idea of lossy storage. As you accumulate more per-row events, at some point it becomes more efficient to forget the individual rows and just reapply the original full-table check query when it's time to check the constraint. So if we could recognize One problem with that is that it works for the constraint check but not for referential actions, although if we instead fired the referential actions truly immediately rather than queued to statement end that'd prevent those from being an issue. The only thing there is that we'd have to also have a constraint check for at least set default. RI events as being associated with the same constraint, and keep track of how many are pending for each constraint, we could make a decision to discard the queue and instead register one event to apply a full-table check. It's not clear how to do that efficiently though. Yeah, I was thinking we could keep a separate structure for (foreign key trigger oid, action) where we could keep track of a current count and whether or not we've consolidated already and scan the queue when we do the consolidation removing items for that oid. That's still not very good though. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] foreign keys and RI triggers
Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 26 May 2005, Tom Lane wrote: The thoughts I've had about special-casing RI events to save memory have to do with the idea of lossy storage. One problem with that is that it works for the constraint check but not for referential actions, True. But even fixing it only for constraint checks would be a win. Sooner or later, we will have to implement spill-to-disk for the trigger event queue anyway ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] foreign keys and RI triggers
On Thu, 26 May 2005, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On Thu, 26 May 2005, Tom Lane wrote: The thoughts I've had about special-casing RI events to save memory have to do with the idea of lossy storage. One problem with that is that it works for the constraint check but not for referential actions, True. But even fixing it only for constraint checks would be a win. Yeah, I'm just wondering if going the extra step and forcing really really immediate referential actions (even if that sometimes means adding a no action trigger on the event as well) would be worth doing as that could remove the queued pk actions for cascade and set null as well and at least turn the queued pk action for set default into one that could be consolidated. ---(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] Rod Taylor
At the VERY LEAST, you'd best get off 7.2.1 to something later (i.e. the last 7.2 release). ISTR some nasty bug or other in 7.2.1. I heard for Andrew Sullivan that you managed to hack Slony-I to work for postgre 7.2. Can you tell me how you did as it would me helpful to me I no longer have the code, but (from memory) this is approximately what you need to do: * Take the 7.3 templates and copy them to 7.2 -- or otherwise hardcode the version your using to pick up the 7.3 templates * Remove all traces of schemas from the code and sql templates. I basically changed the . to an _. * Bunch of work related to the XID datatype and functions. For example, Slony creates CASTs for the xid to xxid and back -- but 7.2 cannot create new casts that way so you need to edit system tables by hand. I recall creating an Operator Class and editing several functions as well. * sl_log_1 will have severe performance problems with any kind of data volume. This required a number of index and query changes to optimize for 7.2. 7.3 and above are quite a bit smarter in terms of optimizations they can apply. * Don't bother trying to make sequences work. Do them by hand after the upgrade using pg_dump and grep. Of course, now that you have done all of the above, it's not compatible with standard Slony now. So you either need to implement 7.2 in a less hackish way, or you can also hack up slony to work without schemas on newer versions of PostgreSQL so they can talk to each other. Almost immediately after getting the DB upgraded from 7.2 to 7.4, we deinstalled the hacked up Slony (by hand for the most part), and started a migration from 7.4 to 7.4 on a different machine using the regular Slony. This was primarily to ensure we didn't keep our system catalogues which had been manually fiddled with. All that said, we upgraded a few hundred GB from 7.2 to 7.4 with about 30 minutes actual downtime (versus 48 hours for a dump / restore cycle) and no data loss. -- ---(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] adding a function to pg_proc.h
On 5/25/05, Jaime Casanova [EMAIL PROTECTED] wrote: Also, defining the return type as ANYELEMENT will definitely NOT work, since none of the input arguments are polymorphic. mmm... This is a problem, there is a way to make a function that can be used for returning different datatypes depending on the columns Ok, i have read in the manual about returning polymorphic types. why we can't cast the function to the appropiate type to avoid the extra parameter? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] postmaster.pid disappeared
Hi, I reinstall postgresql 7.4.6 instead of 7.4.8 (still on Solaris 10) and didn't include postgresql as services that is managed by SMF, and it works fine so far. Also, I should mentioned that I configured postgresql 7.4.6 with --enable-thread-safety option, don't know if this will have anything to do with this issue. Thanks for all the help, J On 5/24/05, Junaili Lie [EMAIL PROTECTED] wrote: Tom, I am not too sure how to determine the unlink call. Can you provide more information/instructions? In my case the pg_ctl reload -D /usr/local/pgsql deleted the postmaster.pid without creating a new one. I am not too sure if this is normal. J On 5/24/05, Tom Lane [EMAIL PROTECTED] wrote: Junaili Lie [EMAIL PROTECTED] writes: I am running postgresql 7.4.8 on solaris 10 (and I compile and installed slony). Everytime I am trying to reload the configuration using pg_ctl reload -D $PGDATA, it deleted the postmaster.pid and didn't create a new one. That's very strange. The pg_ctl script itself doesn't delete the postmaster.pid file under any circumstances (unless maybe you are using a locally modified version?), and the postmaster shouldn't delete it either unless exiting. Can you determine exactly where the unlink call is coming from? strace or local equivalent may help. 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] WAL replay failure after file truncation(?)
On Wed, 25 May 2005 18:19:19 -0400, Tom Lane [EMAIL PROTECTED] wrote: but it keeps a list (hash table, file, whatever) of those blocks. [...] Is it sufficient to remember just the relation and the block number or do we need the contents a well? We don't *have* the contents ... that's exactly why it's panicking ... I meant the contents of the WAL record, not the original block contents. Anyway, I think it's not needed. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] WAL replay failure after file truncation(?)
Manfred Koizar [EMAIL PROTECTED] writes: [...] Is it sufficient to remember just the relation and the block number or do we need the contents a well? I meant the contents of the WAL record, not the original block contents. Anyway, I think it's not needed. Oh, I see. Yes, it might be worth hanging onto for debugging purposes. If we did get a report of such a failure, I'm sure we'd wish to know what sort of WAL record triggered it. One trusts there won't be so many that storing 'em all is a problem ... 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] A 2 phase commit weirdness
Hackers, I'm seeing the following weirdness with the 2PC patch: alvherre=# begin; BEGIN alvherre=# create table a (a int); CREATE TABLE alvherre=# insert into a values (1); INSERT 0 1 alvherre=# prepare transaction 'foo'; PREPARE TRANSACTION alvherre=# select * from a; At this point, the backend freezes. However, if I connect in another session and issue the same select * from a query, it correctly returns no such relation. Now, because the backend cannot see the table (because it was created by a transaction that is not yet committed), the first backend shouldn't freeze but return the same no such relation. My guess is that the backend that created the prepared transaction has its relcache populated with the new table's entry. But given that we prepared the transaction, we should forget about the table, and only remember it when we receive the shared inval message that will get sent when the prepared transaction is committed. I'm wondering what should happen at prepare time so that my own cache is correct. Do I need to send the inval messages to me? Is this even possible? Maybe I need to read the messages from the prepare file and send it to me. Any ideas? -- Alvaro Herrera (alvherre[a]surnet.cl) Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] A 2 phase commit weirdness
Alvaro Herrera [EMAIL PROTECTED] writes: I'm wondering what should happen at prepare time so that my own cache is correct. Good point. As far as the local caches are concerned, we probably have to make it look like the transaction rolled back. I think Heikki already had code in there to send the right inval messages when the prepared transaction ultimately commits ... but we'll have to check that that sequence does the right things ... Do I need to send the inval messages to me? Is this even possible? inval.c is less than readable, isn't it :-( But yes, and yes. 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] Can we simplify win32 threading code
Magnus Hagander [EMAIL PROTECTED] writes Yeah, that should work. With one shared memory segment and one event for each process, of course. The event can be the same one as is used now, only it has to be named so it can be accessed externally. Yes, the shared memory segment size could be controlled by MaxBackends. It would do away with the thread, certainly. But it's not quite as simple as you outline above - you'll need to replace the critical section locking (easy, lightweight) with a mutex or something like that (more complex, more heavy weight). But probably named pipes is more heavy, yes. Yes, use mutex. You'll also need some way of delivering the feedback, I think - kill(0) is supposed to tell you if there is a live process in th eother end, so you can't just throw the signal out and hope for the best. To simulate kill(0) we can test the process handle, just like we handle waitpid(). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can we simplify win32 threading code
Magnus Hagander [EMAIL PROTECTED] writes I assume that this will not break the use of pg_ctl to deliver pseudo-signals. That would be a show-stopper. It shouldn't, but there is one concern: it has to be created in the global namespace. On older windows there is no different, but on modern windows with terminal services in it it does. It might require some permissions hackings - I don't know what the default permissinos are on these things. But I *think* it should work fine. Yes, the mutex/event stuff should be in the global namespace. What do you mean the permission hacks? Regards, Qingqing ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] unsafe use of hash_search(... HASH_ENTER ...)
-- First part --- In md.c/RememberFsyncRequest(): if (hash_search(pendingOpsTable, entry, HASH_ENTER, NULL) == NULL) ereport(FATAL, (errcode(ERRCODE_OUT_OF_MEMORY), errmsg(out of memory))); pendingOpsTable uses MdCxt to allocate memory. So if out of memory, we actually have no chance to raise the error level to FATAL. A quick fix is to use malloc() HASH_ALLOC method for pendingOpsTable. In general, code snippet like this: if (hash_search(..., HASH_ENTER, ...) == NULL) action_except_elog__ERROR__; are considered unsafe if: (1) the allocation method of the target hash table could elog(ERROR) themselves and (2) the reaction to the failure of hash_search() is not elog(ERROR). So shared memory hash table is safe because of condition (1). I scratched the server code and find the following places are like this: * RememberFsyncRequest() - solution as above; * XLogOpenRelation() - not a problem, since it is already in the critical section; * IndexNext() in 8.0.1; -- Second part --- Also, per discussion with Neil and Tom, it is possible to simplify code snippets like this: if (hash_search(local_hash, HASH_ENTER, ...) == NULL) elog(ERROR, out of memory); To hash_search(local_hash, HASH_ENTER, ...); Comments? Regards, Qingqing ---(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] unsafe use of hash_search(... HASH_ENTER ...)
Qingqing Zhou [EMAIL PROTECTED] writes: In md.c/RememberFsyncRequest(): if (hash_search(pendingOpsTable, entry, HASH_ENTER, NULL) == NULL) ereport(FATAL, (errcode(ERRCODE_OUT_OF_MEMORY), errmsg(out of memory))); pendingOpsTable uses MdCxt to allocate memory. So if out of memory, we actually have no chance to raise the error level to FATAL. A quick fix is to use malloc() HASH_ALLOC method for pendingOpsTable. Unsafe is a bit of an overstatement, when you evidently haven't analyzed the consequences of either choice of error level. That is, why is this a bug? 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] unsafe use of hash_search(... HASH_ENTER ...)
Tom Lane [EMAIL PROTECTED] writes Qingqing Zhou [EMAIL PROTECTED] writes: Unsafe is a bit of an overstatement, when you evidently haven't analyzed the consequences of either choice of error level. That is, why is this a bug? Consider the senario like this: Backends register some dirty segments in BgWriterShmem-requests; bgwrite will AbsorbFsyncRequests() asynchornously but failed to record some one in pendingOpsTable due to an out of memory error. All dirty segments remembered in requests after this one will not have chance be absorbed by bgwriter. Recall we have already removed those dirty segment by: BgWriterShmem-num_requests = 0; So we will have no chance to pick up it again. That is, we will never fsync some dirty segments (mdwrite() will not sync those files themselves either because ForwardFsyncRequest() is successfully done). Regards, Qingqing ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] foreign keys and RI triggers
Tom Lane wrote: But the check could incorporate the same transaction ID test already in use. I think Neil is right that it'd be a win to apply the test before enqueueing the trigger instead of after. Speaking of which, does anyone see a reason why RI_FKey_keyequal_upd() is implemented as a pseudo-trigger function -- e.g. taking a pointer to a TriggerData? It seems a kludge. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
