SELECT while being UPDATEd?
An UPDATE trigger on table A called an SPL that SELECTed from the row being updated to insert into table B; sometimes the date looked "new" and the time "old", and the poster's boss blamed Informix. Respondents agreed the clean approach is to pass values via REFERENCING NEW/OLD (or a trigger routine) rather than re-SELECTing, though Fernando Nunes' test case showed the SELECT does return new values, so no product bug. The poster later found the real cause: an SPL variable declared DATETIME HOUR TO MINUTE instead of YEAR TO MINUTE, discarding the date. Resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
A colleague created an update trigger on a table A. This trigger invokes an SPL that INSERTs a new row in table B. Some of the values being INSERTed into B are being SELECTed from table A, from the very row being UPDATed (and that fired the trigger). My colleague is experiencing difficulty in that some of the data selected from A is "old" and some is "new". I offered the observation that one shouldn't try to read the exact row of a table that is currently being updated, while it is being updated. I further offered two alternatives: 1) Explicitly pass the required values FROM THE TRIGGER (using "NEW" or "OLD", as required). 2) Use a Trigger Routine to accomplish the same thing. Both were rejected because "Informix is flawed", and shouldn't work like that. So, another error is injected into the system while we "are moving on", as she stated. But, perhaps I am in error-- or misunderstanding (which is, essentially, the same thing). Maybe it is just fine to invoke an SPL, from a trigger, and said SPL SELECTs values (unspecified whether "old"/pre or "new"/post) from the row being UPDATEd (which caused the trigger to fire). I always thought that was bad practice and such values were in an inconsistent state. Set me straight if I am wrong, please. Can one legally read a row being changed, while in the process of being changed? DG P.S. The problem arose because the value actually being changed (and being read) is a datetime value. When changing the date, Informix acts as if "new" were specified. That is, the revised date is returned. When changing the time, Informix acts as if "old" were specified. That is, the previous time is returned.
David: You are correct. Your colleague is dead set on making Informix look bad. It is most definitely NOT broken since it is working exactly as it is designed and documented to work. As far as why some the select she uses in the trigger to fetch values to insert (the WRONG way to do it, as you noted) sometimes returns "old" values and sometimes "new" values, I cannot comment. I would want to see the DDL that creates the table and its triggers and the transaction that is updating the "date" and "time" columns (one wonders why it isn't just a DATETIME YEAR TO SECOND instead, but that's a different issue). The correct way to do this is to have the insert executed directly by the update trigger using the REFERENCING NEW AS <alias> or the REFERENCING OLD AS <alias> alias names as appropriate to the intent (or through a trigger procedure). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 25, 2016 at 6:19 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > A colleague created an update trigger on a table A. This trigger invokes an > SPL that INSERTs a new row in table B. Some of the values being INSERTed > into > B are being SELECTed from table A, from the very row being UPDATed (and > that > fired the trigger). > > My colleague is experiencing difficulty in that some of the data selected > from > A is "old" and some is "new". > > I offered the observation that one shouldn't try to read the exact row of a > table that is currently being updated, while it is being updated. > > I further offered two alternatives: > 1) Explicitly pass the required values FROM THE TRIGGER (using "NEW" or > "OLD", > as required). > 2) Use a Trigger Routine to accomplish the same thing. > > Both were rejected because "Informix is flawed", and shouldn't work like > that. > > So, another error is injected into the system while we "are moving on", as > she > stated. > > But, perhaps I am in error-- or misunderstanding (which is, essentially, > the > same thing). Maybe it is just fine to invoke an SPL, from a trigger, and > said > SPL SELECTs values (unspecified whether "old"/pre or "new"/post) from the > row > being UPDATEd (which caused the trigger to fire). > > I always thought that was bad practice and such values were in an > inconsistent > state. > > Set me straight if I am wrong, please. > > Can one legally read a row being changed, while in the process of being > changed? > > DG > > P.S. The problem arose because the value actually being changed (and being > read) is a datetime value. When changing the date, Informix acts as if > "new" > were specified. That is, the revised date is returned. When changing the > time, > Informix acts as if "old" were specified. That is, the previous time is > returned. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114452b869f015053aee505f
Art, Thank you, once again, for your (always) helpful response. The problem is exacerbated by the fact that the "colleague" to which I referred is actually my boss. I truly do not understand her absolute refusal to code properly. DG P.S. I think I was unclear in my example. A column in a table is being updated. This fires an update trigger, which invokes an SPL that reads that very same column that is being updated. The column is a datetime. If the time component of that (datetime) column is changed, the subsequent SELECT behaves as if the "post" value were specified. If the date component of the column is changed, the subsequent SELECT behaves as if the "pre" value were specified. [She asked me to troubleshoot the problem. I identified it as bad coding, and suggested alternatives. Rejected.] I don't want to get "wrapped around the axle" on this because my boss has already told me to "move on". So, I salute smartly and take the hill. But, just to bolster my own confidence, I sought to verify that I was "thinking right". We don't need to beat a dead horse, here. Thank you for your comments.
Hi, David. I would also suggest a good look at the trigger event definition. Please take a look at my last IIUG presentation, named "Don't make your Informix static - developers do's and don'ts tips" There is a good example, of a bad trigger definition that I had in one client, and my suggestion to its recoding. Unfortunately (or some may say fortunately), around 80% of my DBA working time is dedicated to application debugging. Sometimes is boring to get struggle with a client, that has a company which states "we are very qualified and experienced in Informix development", and you see that kind of things running slow... that's part of our work, I love it, just take care with your words, make a good technical explanation, and move on. Good luck, best regards. Atenciosamente, Alexandre Marini [http://mcsoftware.com.br/mc_conteudo/assinaturas/logoassinatura.png&retryCount= 3] [http://mcsoftware.com.br/mc_conteudo/assinaturas/arquiteturalogo.png&retryCount =3] ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de DAVID GROVE <david.grove@alaska.gov> Enviado: quinta-feira, 25 de agosto de 2016 21:35:37 Para: ids@iiug.org Assunto: Re: SELECT while being UPDATEd? [37664] Art, Thank you, once again, for your (always) helpful response. The problem is exacerbated by the fact that the "colleague" to which I referred is actually my boss. I truly do not understand her absolute refusal to code properly. DG P.S. I think I was unclear in my example. A column in a table is being updated. This fires an update trigger, which invokes an SPL that reads that very same column that is being updated. The column is a datetime. If the time component of that (datetime) column is changed, the subsequent SELECT behaves as if the "post" value were specified. If the date component of the column is changed, the subsequent SELECT behaves as if the "pre" value were specified. [She asked me to troubleshoot the problem. I identified it as bad coding, and suggested alternatives. Rejected.] I don't want to get "wrapped around the axle" on this because my boss has already told me to "move on". So, I salute smartly and take the hill. But, just to bolster my own confidence, I sought to verify that I was "thinking right". We don't need to beat a dead horse, here. Thank you for your comments. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Couldn't this be beaten with simple logic? Using explicite NEW and OLD references makes it clear which state of the=20 data is meant (and give you the choice at that), while not using it would=20 a) not state what is meant and b) leave it in the dark which state you get = and why (and c) refuse a choice). Andreas From: "DAVID GROVE" <david.grove@alaska.gov> To: ids@iiug.org Date: 26.08.2016 02:36 Subject: Re: SELECT while being UPDATEd? [37664] Sent by: ids-bounces@iiug.org Art,=20 Thank you, once again, for your (always) helpful response.=20 The problem is exacerbated by the fact that the "colleague" to which I=20 referred is actually my boss. I truly do not understand her absolute=20 refusal=20 to code properly.=20 DG=20 P.S. I think I was unclear in my example. A column in a table is being=20 updated. This fires an update trigger, which invokes an SPL that reads=20 that=20 very same column that is being updated. The column is a datetime. If the=20 time=20 component of that (datetime) column is changed, the subsequent SELECT=20 behaves=20 as if the "post" value were specified. If the date component of the column = is=20 changed, the subsequent SELECT behaves as if the "pre" value were=20 specified.=20 [She asked me to troubleshoot the problem. I identified it as bad coding,=20 and=20 suggested alternatives. Rejected.]=20 I don't want to get "wrapped around the axle" on this because my boss has=20 already told me to "move on". So, I salute smartly and take the hill. But, = just to bolster my own confidence, I sought to verify that I was "thinking = right".=20 We don't need to beat a dead horse, here.=20 Thank you for your comments.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
Not sure exactly what is expected from your post and the discussion that
followed.... comments inline
On Thu, Aug 25, 2016 at 11:19 PM, DAVID GROVE <david.grove@alaska.gov>
wrote:
> A colleague created an update trigger on a table A. This trigger invokes an
> SPL that INSERTs a new row in table B. Some of the values being INSERTed
> into
> B are being SELECTed from table A, from the very row being UPDATed (and
> that
> fired the trigger).
>
> My colleague is experiencing difficulty in that some of the data selected
> from
> A is "old" and some is "new".
>
> I offered the observation that one shouldn't try to read the exact row of a
> table that is currently being updated, while it is being updated.
>
If the idea is to retrieve the line that triggered the event, than it's a
clearly dumb idea to SELECT it, at it's lesse efficient.
>
> I further offered two alternatives:
> 1) Explicitly pass the required values FROM THE TRIGGER (using "NEW" or
> "OLD",
> as required).
> 2) Use a Trigger Routine to accomplish the same thing.
>
> Both were rejected because "Informix is flawed", and shouldn't work like
> that.
>
I can't discuss if it's flawed or not without a test case. I haven't seen
anything in the manual that states this is not allowed.
I created a test case and it worked as expected (always returns the new
value). If a customer thinks or has reasons to believe the product is not
functioning properly they should open a PMR. IBM is there to fix bugs. And
it does.
>
> So, another error is injected into the system while we "are moving on", as
> she
> stated.
>
> But, perhaps I am in error-- or misunderstanding (which is, essentially,
> the
> same thing). Maybe it is just fine to invoke an SPL, from a trigger, and
> said
> SPL SELECTs values (unspecified whether "old"/pre or "new"/post) from the
> row
> being UPDATEd (which caused the trigger to fire).
>
> I always thought that was bad practice and such values were in an
> inconsistent
> state.
>
That's not my understanding, and I haven't seen anything in the manual that
corroborates your statements. And my test case works.
>
> Set me straight if I am wrong, please.
>
> Can one legally read a row being changed, while in the process of being
> changed?
>
I'd say so, until proven wrong.... I must admit my search on the manual was
very brief.
>
> DG
>
> P.S. The problem arose because the value actually being changed (and being
> read) is a datetime value. When changing the date, Informix acts as if
> "new"
> were specified. That is, the revised date is returned. When changing the
> time,
> Informix acts as if "old" were specified. That is, the previous time is
> returned.
>
Test case taht works:
DROP TABLE IF EXISTS base_table;
DROP TABLE IF EXISTS target_table;
DROP PROCEDURE IF EXISTS test_proc;
CREATE TABLE base_table
(
col1 INTEGER,
col2 DATETIME YEAR TO SECOND
);
CREATE TABLE target_table
(
col1 INTEGER,
col2 DATETIME YEAR TO SECOND
);
CREATE PROCEDURE test_proc ()
DEFINE v DATETIME YEAR TO SECOND;
SELECT col2 INTO v
FROM base_table
WHERE col1 = 1;
DELETE FROM target_table;
INSERT INTO target_table VALUES(1, v);END PROCEDURE;
CREATE TRIGGER base_table_t_u UPDATE ON base_tableFOR EACH ROW ( EXECUTE PROCEDURE test_proc() );
INSERT INTO base_table VALUES (1, '2016-08-26 12:00:00');
UPDATE base_table SET col2 = '2016-08-26 15:00:00' WHERE col1 = 1;SELECT "Time changed: "||col2 FROM target_table WHERE col1 = 1;
UPDATE base_table SET col2 = '2016-08-27 15:00:00' WHERE col1 = 1;SELECT "Date changed: "||col2 FROM target_table WHERE col1 = 1;
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a1144981e7238b0053af58498
Thank you all for your helpful and informative comments. Since I left things with a sort of accusation (NOT from me) that there was an Informix bug, I want to set the record straight. My colleague came back to me, today, on this issue. This time I examined the SPL that the trigger actually executes (all our triggers are pretty much 'one-liners' with the actual work done in a separate SPL) more closely than before. I was particularly interested in the complaint that the time portion of a datetime variable was being INSERTed properly in the new table, but the date portion was not. Long story short, I found the problem to be that the relevant variable in the SPL was misdefined as "HOUR TO MINUTE", rather than "YEAR TO MINUTE". Hence the date was being ignored. NO PROBLEM WITH INFORMIX. I should have caught it when I was first asked to assist, but I was concentrating more on the coding logic, as opposed to variable declaration. Not a problem worthy of this forum. I apologize. DG
I hate bugs... they annoy customers and they annoy me ;) So I try to chase them as much as possible. The only thing worse than noticing a bug is to let it live :) In any case, and considering there is no bug, the point about doing a SELECT to get the data that could be passwd directly is relevant... Regards and thanks for the clarification On Sat, Aug 27, 2016 at 1:16 AM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you all for your helpful and informative comments. > > Since I left things with a sort of accusation (NOT from me) that there was > an > Informix bug, I want to set the record straight. > > My colleague came back to me, today, on this issue. This time I examined > the > SPL that the trigger actually executes (all our triggers are pretty much > 'one-liners' with the actual work done in a separate SPL) more closely than > before. I was particularly interested in the complaint that the time > portion > of a datetime variable was being INSERTed properly in the new table, but > the > date portion was not. > > Long story short, I found the problem to be that the relevant variable in > the > SPL was misdefined as "HOUR TO MINUTE", rather than "YEAR TO MINUTE". Hence > the date was being ignored. > > NO PROBLEM WITH INFORMIX. > > I should have caught it when I was first asked to assist, but I was > concentrating more on the coding logic, as opposed to variable declaration. > > Not a problem worthy of this forum. > > I apologize. > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --94eb2c05fb20939189053b03121e