3

I have a sequence of insert, after insert trigger update, and query operations and my problem is that the data received from the query does not reflect the updates performed by the trigger (even though the data in the database is indeed updated).

I'm using PHP, PostgreSQL and Propel 1.6 ORM (although this problem might be database and ORM agnostic).

I have the following sequence:

  1. Use AJAX to insert a new row (a vote) in the "book_vote" table (with columns: 'book_id', 'book_score', 'voter_id').

  2. Have a PostgreSQL after insert trigger to update the corresponding book "vote_count" and "average_score" columns in the "book" table.

  3. Make a query to get the new "vote_count" and "average_score" data for the "book" and send that data back to the client with AJAX (to display updated values after the vote).

This all happens within the same PHP session, and my problem is that I do not get the updated "book" values in the AJAX response. It seems like the query is performed before the database trigger is performed. Is there any way to ensure the query happens after the database trigger?

halfer
  • 19,824
  • 17
  • 99
  • 186
RayOnAir
  • 2,038
  • 2
  • 22
  • 33
  • Are you sure that trigger works after insert? Do you have updated columns in book table after insert? – piotrekkr Oct 03 '12 at 14:23
  • @piotrekkr : Yes! the trigger works. The "vote_count" and "average_score" columns for the corresponding row in the "book" table are updated with the trigger. However, the query gets the data before it is updated... – RayOnAir Oct 03 '12 at 14:30
  • wrap the insert (and trigger) in a transaction, and do a commit before getting the next – JvdBerg Oct 03 '12 at 14:46
  • @JvdBerg : I'm already using a transaction for the insert. I'm using beginTransaction(), try (with commit()) and catch (with rollback()). It is only after the transaction that I query for the updated book object, but still no luck. – RayOnAir Oct 03 '12 at 15:09

2 Answers2

1

It seems to me that you are doing a save on a Propel object, and you have an external trigger that modifies that row further. You need to be able to tell Propel that the object needs refreshing immediately after the insert/update... and thankfully Propel supports that directly. In your table element in your schema, do this:

<table name="book_vote" reloadOnInsert="true" reloadOnUpdate="true">

You can use either or both of the reload statements, depending on requirements (in your case you'll probably just want the insert one). From there, just rebuild your model and it should work fine.More details here.

Addendum: as per discussion, the issue appears to be that you have already loaded the foreign row that you wish to reload, and as such it is being retrieved from the instance pool rather than the database. You've found that BookPeer::clearInstancePool() solved it for you - great! For bonus points, see if you can remove items from the pool individually - it is probably more efficient to allow the pool to run normally and to remove items one at a time, rather than to clear the pool for a whole table. Let us know if this is possible!

halfer
  • 19,824
  • 17
  • 99
  • 186
  • Thank you! I did not know about the table attributes you mentioned. However, would that work in my case where I have a "book" table (which has the aggregate columns that are updated by the trigger) and a "book_vote" table (where the vote is actually inserted or updated)? and if so, in which of the two tables should I include the attributes? I'll do some testing in the mean time. – RayOnAir Oct 09 '12 at 21:37
  • 1
    Just re-read your question, and I'm trying to think why you might not get a correct total - you should, afaict. The other thing you could consider is that you have already loaded a particular totals row, and the corresponding object has been cached in the instance pool. Try running `Propel::disableInstancePooling()` and then load the total row. – halfer Oct 09 '12 at 23:02
  • (Aside from the above, it might be a good idea to include some code in your question; it might make things clearer). – halfer Oct 09 '12 at 23:03
  • So, as mentioned in my previous comment, the table attributes `reloadOnInsert` and `reloadOnUpdate` do not work in my case since the table I'm updating with the trigger is not the table where I'm performing the insert or update with propel. However, your proposal to use `Propel::disableInstancePooling()` works! Although, I ended up using `clearInstancePool()` with the peer class like `BookPeer::clearInstancePool();` as indicated in the [an answer to another question](http://stackoverflow.com/a/11438540/1362009). If you can include those alternatives in your answer, I'll accept it! Thanks! – RayOnAir Oct 10 '12 at 14:54
  • Hi @RayOnAir - updated as requested. Glad it is now working for you! – halfer Oct 10 '12 at 21:51
0

See in ORM documentation how to refetch data after update. You may have option for column or you have to invoke some method.

Same problem is if you have default value for column and insert.

jordani
  • 1