Re: [HACKERS] Breakage in trigger.c
On Mon, 6 Sep 2004, Tom Lane wrote:
Surely the sense of this is backwards, and it should be
if (!(event-dte_event TRIGGER_DEFERRED_CANCELED)
!(event-dte_event TRIGGER_DEFERRED_DONE
TransactionIdIsValid(event-dte_done_xid)
!TransactionIdDidAbort(event-dte_done_xid)))
{
I'm also concerned about the fact that the per-item states have
dti_done_xid values distinct from the whole-event value. It's
not obvious that a rollback of the subxact that did one item implies
a rollback of the subxact that last marked the event as scanned.
Can anyone offer a proof that that's OK? If it is OK, is it really
necessary to have per-item dti_done_xid at all?
I don't think that case can occur.
The transaction marking the event will have seen one of the following
states I believe:
a) All items were marked by this transaction
b) Some items were already marked by a parent transaction
c) Some items were already marked by a previous committed subtransaction
In the first case, it's marked them so it's okay. In the second and third,
I think the only way for the the item marking subtransaction to abort
after marking the event would involve aborting a common parent transaction
which would abort both.
I think the per-item one is necessary for SET CONSTRAINTS (some of the
deferred actions on a particular event may have been done as per b or c
above).
Finally, surely the Mark the event done case should advance
prev_event? As-is the code is capable of messing up the list links.
As something for the future, it looks to me like subtransactions won't
delink items ever right now, where I think it'd be safe to do so for items
generated from the same subtransaction but I haven't looked to see if
we're keeping that info.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] Making AFTER triggers act properly in PL functions
Here's another thing that I think it would be good to fix in 8.0. We've had numerous complaints before about RI constraints not firing inside PL functions; a recent one is http://archives.postgresql.org/pgsql-bugs/2004-09/msg00020.php I think also that the current behavior violates the SQL spec. SQL99 4.17 says: The checking of a constraint depends on its constraint mode within the current SQL-transaction. If the constraint mode is immediate, then the constraint is effectively checked at the end of each SQL- statement. NOTE 13 - This includes SQL-statements that are executed as a direct result or an indirect result of executing a different SQL- statement. The NOTE is a bit opaque but I think it has to be taken as meaning that if a PL function contains DML commands then the results of those DML commands are checked when they complete, not when the outermost statement completes. Therefore, I think the behavior we want is that immediate-mode AFTER triggers fire at completion of the statement that queued them, no matter whether this was an interactive statement or one inside a function. Deferred triggers, as now, fire only at end of top-level transaction (or when changed to immediate mode by SET CONSTRAINTS). Changing this obviously risks breaking existing applications, but we'll have to do it eventually. 8.0 seems like the appropriate time. Implementation proposal: I was originally thinking we could just invoke DeferredTriggerEndQuery after each query is finished, but that doesn't work. Imagine the situation where a PL function is being called repeatedly by an UPDATE query (which is generating trigger events), and the PL function is doing queries that themselves fire triggers. We want completion of a query within the PL function to fire only those events queued by that query, not events already queued by the outer UPDATE. The outer UPDATE's events should be processed only when it finishes. To handle this, I think we need to introduce a DeferredTriggerBeginQuery function to match DeferredTriggerEndQuery, and make SPI and other callers of the executor essentially do DeferredTriggerBeginQuery() ExecutorStart() ExecutorRun() ExecutorEnd() DeferredTriggerEndQuery() trigger.c will have to be prepared to cope with nested begin/end query calls. I envision it working like this: * BeginQuery establishes a new, empty list of AFTER triggers for the new query. This is pushed onto a stack of active queries for the current (sub)transaction. * In the executor, any request to queue an AFTER trigger event queues it onto the topmost query's list. (Error if no active query, so you MUST have done DeferredTriggerBeginQuery.) * EndQuery processes and discards immediate-mode AFTER trigger events for the current query. Any remaining events (ie, DEFERRED triggers) are appended to the current (sub)transaction's list of pending deferred triggers. Note that even inside a subtransaction, we can discard immediate-mode events. * EndSubXact, in the commit case, expects the stack of open queries for the current subxact to be empty (error if not); in the abort case, pop and discard any open queries, along with any deferred triggers of the subxact. * EndXact and DeferredTriggerSetState continue to act the same as before. In particular, DeferredTriggerSetState need pay no attention to trigger events that are still in lists belonging to open queries; those events aren't ready to fire yet. Comments? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Making AFTER triggers act properly in PL functions
On Tue, 7 Sep 2004, Tom Lane wrote: * EndQuery processes and discards immediate-mode AFTER trigger events for the current query. Any remaining events (ie, DEFERRED triggers) are appended to the current (sub)transaction's list of pending deferred triggers. Note that even inside a subtransaction, we can discard immediate-mode events. * EndXact and DeferredTriggerSetState continue to act the same as before. In particular, DeferredTriggerSetState need pay no attention to trigger events that are still in lists belonging to open queries; those events aren't ready to fire yet. Comments? If I'm reading the above correctly, I think DeferredTriggerSetState may need to change a little if EndQuery works on a separate list of triggers because I believe set constraints immediate currently depends on EndQuery going over the entire list of saved deferred triggers. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Why does pg_stat_activity Truncate Queries?
Folks, I was just noticing that pg_stat_activity is truncating queries which are logged to it at about 150 chars. I can't think of any good reason for this. Is there one? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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] Making AFTER triggers act properly in PL functions
Stephan Szabo [EMAIL PROTECTED] writes: If I'm reading the above correctly, I think DeferredTriggerSetState may need to change a little if EndQuery works on a separate list of triggers because I believe set constraints immediate currently depends on EndQuery going over the entire list of saved deferred triggers. But it would. What I'm imagining is that the current list remains the same, but it only contains trigger events from already-completed statements. The per-query lists would be staging areas for gathering events from still-active statements. The only case where DeferredTriggerSetState would even see any nonempty per-query list is where you had SET CONSTRAINTS being executed inside a PL function that is called from an INSERT/UPDATE/DELETE command that has already generated some trigger events, but is not yet complete. It is not appropriate for those triggers to fire yet, because we haven't completed their generating statement. When we do complete it, we'll fire or defer the triggers according to the then-active SET CONSTRAINTS state. So AFAICS, DeferredTriggerSetState can and should ignore open per-query trigger lists. It will go over the whole list of events from prior statements, though, the same as it does now. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why does pg_stat_activity Truncate Queries?
Josh Berkus [EMAIL PROTECTED] writes: I was just noticing that pg_stat_activity is truncating queries which are logged to it at about 150 chars. 256, I would expect. I can't think of any good reason for this. There is good reason to limit the packet size, but to a kilobyte or so. Feel free to bump up PGSTAT_ACTIVITY_SIZE to 1000 or so in src/include/pgstat.h (and yes, this is already done for 8.0). 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] contrib/start-scripts/linux on Debian
So what is the thinking of HACKERS? Does it make sense to include nothing and rather allow each distribution to support its own? Or should each distribution known to support postgres be invited to submit the relevant script for inclusion? And, if so, should this be a HACKERS [or other appropriate group/individual]-led effort? I'm persisting just because no conclusive action was recommended, and I think this is a mildly important usability issue. I don't know whether altering this piece of contrib is something that would be relevant for 8.0... -tfo On Sep 5, 2004, at 8:01 AM, Ryan Kirkpatrick wrote: On Fri, 27 Aug 2004, Peter Eisentraut wrote: Thomas F.O'Connell wrote: I'd love to see a comment added to the linux start-script included in contrib that referenced update-rc.d. It's useful to note because Debian has different runlevels from Red Hat, and update-rc.d will intelligently do the same thing as chkconfig for Debian systems. Why not just include the Debian start script instead of asking to user to fix up the existing one? I agree. I wrote that start-script a long time ago when there was not a very good one at all for Linux. Since then, the package maintainers for the various distributions have filled that void, making my script obsolete. TTYL. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Making AFTER triggers act properly in PL functions
On Tue, 7 Sep 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: If I'm reading the above correctly, I think DeferredTriggerSetState may need to change a little if EndQuery works on a separate list of triggers because I believe set constraints immediate currently depends on EndQuery going over the entire list of saved deferred triggers. But it would. What I'm imagining is that the current list remains the same, but it only contains trigger events from already-completed statements. The per-query lists would be staging areas for gathering events from still-active statements. I misread then. I thought that you were proposing that EndQuery look only at the per-query list and then add the deferred items that weren't fired to the main list but never go over that list. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Making AFTER triggers act properly in PL functions
Stephan Szabo [EMAIL PROTECTED] writes: I misread then. I thought that you were proposing that EndQuery look only at the per-query list and then add the deferred items that weren't fired to the main list but never go over that list. It will have to re-examine the tail of the main list, as well as process the current per-query list. I haven't really done any coding yet, but I think this could be done pretty easily by appending the per-query list to the main list (an easy pointer swing) and then proceeding as before. Or it might be better to do it in two phases --- I'm not sure how hard it is to keep track of whether it's safe to recycle fully-fired events. Knowing that you are processing triggers generated by the current query might be a useful leg up on that task. Also, it's probably reasonable to assume that SET CONSTRAINTS doesn't queue any new triggers of its own, meaning that in any given EndQuery call only one of these tasks (rescan old triggers or scan new ones) can be needed. That might or might not be worth exploiting. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Why does pg_stat_activity Truncate Queries?
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: I was just noticing that pg_stat_activity is truncating queries which are logged to it at about 150 chars. 256, I would expect. I can't think of any good reason for this. There is good reason to limit the packet size, but to a kilobyte or so. Feel free to bump up PGSTAT_ACTIVITY_SIZE to 1000 or so in src/include/pgstat.h (and yes, this is already done for 8.0). I didn't see this changement in the 8.0 release note, don't you think shall be added ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Indexed views?
where productname is in the product table, and lotname in the lot table. I would be interested in creating an index such as CREATE INDEX ix_vw_lot ON vw_lot (lotname, productname); What purpose would this serve that indexes on the separate tables wouldn't serve? The index would be enough to cover 90% of the queries against lot the lot and inventory tables. This sounds to me like you are suffering from a common misconception. Postgres cannot answer queries from the contents of indexes alone. Yes, thanks Tom. This is precisely what I was missing. I searched the archives for the reason why this is so, but I found only one message mentioning the MVCC mechanism. Can you point me in the right direction? I would like to understand the issue. IMHO, a change in this area could deliver great performance improvements. -Tiago ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Making AFTER triggers act properly in PL functions
On Tue, 7 Sep 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: I misread then. I thought that you were proposing that EndQuery look only at the per-query list and then add the deferred items that weren't fired to the main list but never go over that list. It will have to re-examine the tail of the main list, as well as process the current per-query list. I haven't really done any coding yet, but I think this could be done pretty easily by appending the per-query list to the main list (an easy pointer swing) and then proceeding as before. Or it might be better to do it in two phases --- I'm not sure how hard it is to keep track of whether it's safe to recycle fully-fired events. Knowing that you are processing triggers generated by the current query might be a useful leg up on that task. Also, it's probably reasonable to assume that SET CONSTRAINTS doesn't queue any new triggers of its own, meaning that in any given EndQuery call only one of these tasks (rescan old triggers or scan new ones) can be needed. That might or might not be worth exploiting. Hmm, if our current state of deferred triggers look like (in order) Trigger A Trigger B Trigger C and triggers A and B are made immediate and scanning begins at the beginning of the queue again, during the execution of the Trigger A trigger function, if an update is done to a table with an immediate after trigger (D), does the firing order look like: Trigger A start Trigger D start Trigger D end Trigger A end Trigger B start Trigger B end or something else? What if trigger D calls set constraints to make Trigger C immediate? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Indexed views?
Tiago Wright [EMAIL PROTECTED] writes: Yes, thanks Tom. This is precisely what I was missing. I searched the archives for the reason why this is so, but I found only one message mentioning the MVCC mechanism. Can you point me in the right direction? I would like to understand the issue. Short answer: MVCC tuple visibility status isn't (and can't be) stored in the index. So the backend has to visit the actual tuple to see if it is visible to the transaction that is currently running. IMHO, a change in this area could deliver great performance improvements. Hard to say how it would work, but come up with a good design and quality patch and it'll probably go in. :) -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Indexed views?
On Tue, Sep 07, 2004 at 07:58:56PM -0400, Doug McNaught wrote: Tiago Wright [EMAIL PROTECTED] writes: Yes, thanks Tom. This is precisely what I was missing. I searched the archives for the reason why this is so, but I found only one message mentioning the MVCC mechanism. Can you point me in the right direction? I would like to understand the issue. IMHO, a change in this area could deliver great performance improvements. Hard to say how it would work, but come up with a good design and quality patch and it'll probably go in. :) Probably not. This has been discussed before; what's needed is that the visibility information is stored also in the index. This is hard and inefficient to do, because it requires updating the index at the same time that the heap is updated. Which is a bad proposition as soon as there is more than one index, and when there is a seqscan involved (i.e. no index), because it means a lot of extra I/O. A proposal that would be better received would be to implement bitmap scanning of indexes, which could mean retrieving heap pages in order, yielding much better performance. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) We take risks not to escape from life, but to prevent life escaping from us. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Making AFTER triggers act properly in PL functions
Stephan Szabo [EMAIL PROTECTED] writes: Hmm, if our current state of deferred triggers look like (in order) Trigger A Trigger B Trigger C and triggers A and B are made immediate and scanning begins at the beginning of the queue again, during the execution of the Trigger A trigger function, if an update is done to a table with an immediate after trigger (D), does the firing order look like: Trigger A start Trigger D start Trigger D end Trigger A end Trigger B start Trigger B end Yeah, I would think so. What if trigger D calls set constraints to make Trigger C immediate? That would be a query within D, so C would fire within D. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Indexed views?
Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Sep 07, 2004 at 07:58:56PM -0400, Doug McNaught wrote: Hard to say how it would work, but come up with a good design and quality patch and it'll probably go in. :) Probably not. This has been discussed before; what's needed is that the visibility information is stored also in the index. This is hard and inefficient to do, because it requires updating the index at the same time that the heap is updated. Which is a bad proposition as soon as there is more than one index, and when there is a seqscan involved (i.e. no index), because it means a lot of extra I/O. Yeah, hence the smiley. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(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] Indexed views?
Doug McNaught [EMAIL PROTECTED] writes: Short answer: MVCC tuple visibility status isn't (and can't be) stored in the index. Well, in principle it *could* be, but there are strong arguments why it shouldn't be: the costs of updating N index entries instead of just one tuple entry, the potential reliability hit (what happens when the index entries disagree with the master?), and the increase in index size (adding an extra dozen bytes to an index entry is a very nontrivial I/O hit). I don't say we wouldn't ever do it, but it will take a lot more than newbies opining that it might be a good idea to persuade us that it is a good 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] Indexed views?
Doug McNaught [EMAIL PROTECTED] writes: Short answer: MVCC tuple visibility status isn't (and can't be) stored in the index. Well the can't part is false or at least unproven. From prior discussion the only thing that would be technically challenging would be avoiding deadlocks. The main issue raised is that storing the visibility information in the index would incur other performance costs. It would increase the storage size of the index and it would dramatically increase the i/o needed to maintain indexes for updates and deletes. So it becomes a question of performance trade-offs, as many things do. It would be an experiment that would require a lot of work to even try, and people are skeptical that it would really have any benefits. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Making AFTER triggers act properly in PL functions
On Tue, 7 Sep 2004, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Hmm, if our current state of deferred triggers look like (in order) Trigger A Trigger B Trigger C and triggers A and B are made immediate and scanning begins at the beginning of the queue again, during the execution of the Trigger A trigger function, if an update is done to a table with an immediate after trigger (D), does the firing order look like: Trigger A start Trigger D start Trigger D end Trigger A end Trigger B start Trigger B end Yeah, I would think so. What if trigger D calls set constraints to make Trigger C immediate? That would be a query within D, so C would fire within D. Right, but if we search the entire trigger queue from the beginning looking for all triggers now immediate and fire them in the EndQuery of the set constraints statement contained in D, we'd potentially get an ordering like: Trigger A start Trigger D start Trigger B start Trigger B end Trigger C start Trigger C end Trigger D end Trigger A end rather than: Trigger A start Trigger D start Trigger C start Trigger C end Trigger D end Trigger A end Trigger B start Trigger B end where I'd gather the latter is the intended ordering. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] FYI: Fujitsu
I've accepted an offer from Fujitsu Australia Software Technologies to work on PostgreSQL full-time for them for the next twelve months in Sydney, Australia. I'll be working with Gavin Sherry and two other full-time developers from FAST. I'm grateful to Fujitsu for giving me the opportunity to do this, and I look forward to doing what I can to improve PostgreSQL over the next year. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
