KB Entry P133222

Posted by Riverside Software on 23-Feb-2018 04:18

Hello,

Does anybody know how to reproduce the problem explained in KB entry P133222 ? I don't understand in which case it could happen, and how using a named buffer or a release statement could solve anything.

Thanks

All Replies

Posted by George Potemkin on 23-Feb-2018 04:30

DEFINE TEMP-TABLE tt NO-UNDO
  FIELD i AS INTEGER INITIAL 1
  INDEX i i.

CREATE tt.
ASSIGN tt.i = 1.

MESSAGE CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

Posted by Piotr Ryszkiewicz on 23-Feb-2018 04:31

Hi,

I am pretty sure, this is the result of the question I had to tech support :)

Look at the code below:

DEFINE TEMP-TABLE w_paruj NO-UNDO
FIELD payments_id AS INT
FIELD turn_id AS INT
FIELD rekon_id AS INT
INDEX i-turn turn_id
INDEX i-rekon rekon_id turn_id payments_id
.

DEF BUFFER b_paruj FOR w_paruj.

CREATE w_paruj.
ASSIGN
w_paruj.turn_id = 1
/*w_paruj.rekon_id = 0
w_paruj.payments_id = 0*/.

/*FIND FIRST b_paruj.*/

FOR EACH b_paruj WHERE
b_paruj.turn_id GT 0
AND b_paruj.rekon_id EQ 0:
DISP b_paruj.
END.

FOR EACH on the buffer does not show the newly created record.

Regards,

Piotr

Posted by Riverside Software on 23-Feb-2018 04:32

Using a named buffer in this case doesn't work. The release statement works however.

Posted by George Potemkin on 23-Feb-2018 04:40

> Using a named buffer in this case doesn't work

I guess KB P133222 means:

DEFINE TEMP-TABLE tt NO-UNDO
  FIELD i AS INTEGER INITIAL 1
  INDEX i i.

PROCEDURE CreateTT:
  DEFINE BUFFER bf FOR tt.
  CREATE bf.
  ASSIGN bf.i = 1.
END PROCEDURE.

RUN CreateTT.

MESSAGE CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

Posted by Kunal Berlia on 23-Feb-2018 04:42

DEFINE TEMP-TABLE tttable no-undo

FIELD tablename AS CHARACTER.

DEFINE BUFFER btable FOR tttable.

EMPTY TEMP-TABLE tttable.

PROCEDURE ip_a:

CREATE tttable.

ASSIGN tttable.tablename = "customer"    .

//RELEASE tttable.

RUN ip_b.

END.

PROCEDURE ip_b:

   FOR each btable NO-LOCK :

      MESSAGE btable.TABLENAME

      VIEW-AS ALERT-BOX.

  END.  

END.

RUN ip_a.

Using this, it is allowing me to show the buffer of for each Without release statement. Does this is considering the whole procedure as transaction?

Posted by Kunal Berlia on 23-Feb-2018 04:42

DEFINE TEMP-TABLE tttable no-undo
FIELD tablename AS CHARACTER.
 
DEFINE BUFFER btable FOR tttable.
 
EMPTY TEMP-TABLE tttable.
 
PROCEDURE ip_a:
 
CREATE tttable.
ASSIGN tttable.tablename = "customer"    .
//RELEASE tttable.
RUN ip_b.
 
 
END.
PROCEDURE ip_b:
    FOR each btable NO-LOCK :
       MESSAGE btable.TABLENAME
       VIEW-AS ALERT-BOX.
   END.  
END.
 
RUN ip_a.
 
Using this, it is allowing me to show the buffer of for each Without release statement. Does this is considering the whole procedure as transaction?
 

Posted by marian.edu on 23-Feb-2018 04:46

Man, one need to really review all code that uses temp-tables… there really seems to be (not so) funny issues there :)


Anyway, I find it odd for TS to advise on using RELEASE while I think that should just be deprecated because it it’s totally misleading when it comes to transaction scope… if it must use VALIDATE.

Back to the sample, there is probably something wrong with the way temp-table indexes are implemented… if you take out the second index (the multi fields one) the record is found, if you leave the index but then remove the check on b_paruj.rekon_id EQ 0 the record is again found and much to one surprise the value of that field is indeed 0. The explanation would be the index record was not created because no value was set for one of the index fields, then the for loop uses (rightful) the multi-field index but there is no record created in that index :(


Marian Edu

Acorn IT 
+40 740 036 212

Posted by Riverside Software on 23-Feb-2018 04:52

Ok, this is because can-find is probably using a different (and hidden) buffer reference.

Thanks Piotr and George

Posted by marian.edu on 23-Feb-2018 04:54

It really has nothing to do with internal entries (procedure, functions or even methods) nor with defined buffers, it’s all about some kind of ‘optimisation’ on index creation… no idea if it’s only for temp-tables or same holds true for database tables.


DEF TEMP-TABLE tt NO-UNDO
    FIELD a AS CHAR
    FIELD b AS CHAR
    INDEX pk a
    INDEX idx a b.

DEFINE BUFFER btt FOR tt.

CREATE tt.
tt.a = 'test'.

FIND FIRST tt WHERE tt.b > '' NO-ERROR.

MESSAGE 'tt' SKIP AVAILABLE(tt) VIEW-AS ALERT-BOX.

FIND FIRST btt WHERE btt.b > '' NO-ERROR.

MESSAGE ‘btt' SKIP AVAILABLE(btt) VIEW-AS ALERT-BOX.


Marian Edu

Acorn IT 
+40 740 036 212

Posted by George Potemkin on 23-Feb-2018 04:55

> Does this is considering the whole procedure as transaction?

Kunal, in your example, there are no blocks that starts a transaction.

> there is probably something wrong with the way temp-table indexes are implemented

Marian, the temp-tables work as the normal db tables. The only exception: temp-tables can be created /outside/ transaction. I always define a transaction block to create the temp-table records. If thransactin blocks are not used then we should explicitely use the operators (like VALIDATE) that do the same as Progress does by default at the end of transaction block.

Posted by marian.edu on 23-Feb-2018 05:02

oh man, how the heck are those 4gl apps working? :)

if you use a find statement the record is there, even the can-find sees it… if you remove the find then can-find goes blind :(

DEFINE TEMP-TABLE tt NO-UNDO
  FIELD i AS INTEGER INITIAL 1
  INDEX i i.

PROCEDURE CreateTT:

  CREATE tt.
  ASSIGN tt.i = 1.
END PROCEDURE.

RUN CreateTT.

FIND FIRST tt NO-ERROR. /* somehow find is better than can-find */

MESSAGE available(tt) SKIP CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

the for statement can also see the record wether or not a named buffer is used.

RUN CreateTT.

FOR EACH tt:
    DISPLAY tt.
END.




Posted by Riverside Software on 23-Feb-2018 05:15

Marian, ended up doing exactly the same thing and find that quite confusing...

DEF TEMP-TABLE tt NO-UNDO

   FIELD a AS INT INITIAL 1

   INDEX pk a.

CREATE tt.

tt.a = 1.

MESSAGE 'First can-find' SKIP CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

FIND FIRST tt.

MESSAGE 'Second can-find' SKIP CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

Posted by jmls on 23-Feb-2018 05:22

cmon guys - you've both been around long enough to know that this is "expected behaviour**"

knowledgebase.progress.com/.../P29935

**"Expected behaviour" is something that is not expected, nor desired , but because it's documented it's ok ... :)

Posted by George Potemkin on 23-Feb-2018 05:24

> how the heck are those 4gl apps working? :)

in fact: FIND = RELEASE + FIND.

As a first step the FIND statement always releases the current buffer.

Posted by George Potemkin on 23-Feb-2018 05:29

Where is the tollerance? ;-) Respect the temp-tables as normal db tables and you will not have the problems!

DEFINE TEMP-TABLE tt NO-UNDO
  FIELD i AS INTEGER INITIAL 1
  INDEX i i.

DO TRANSACTION:
  CREATE tt.
  ASSIGN tt.i = 1.
  MESSAGE "Inside transaction:" CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.
END.

MESSAGE "Outside transaction:" CAN-FIND(FIRST tt) VIEW-AS ALERT-BOX.

Posted by Riverside Software on 23-Feb-2018 05:32

Learnt something new today, didn't know that FIND was releasing the current buffer. Now that you say it, it sounds OK :)

Posted by marian.edu on 23-Feb-2018 05:34

George, can we please stop mentioning that RELEASE statement, the buffer release action is something I can digest when used in an explanation but anyway, Julian is right… all that is expected behaviour and more important will never change because there might be someone that rely on this behaviour :)


  
Marian Edu

Acorn IT 
+40 740 036 212

Posted by ske on 23-Feb-2018 06:55

marian.edu wrote:

>  it’s all about some kind of ‘optimisation’ on index creation…

> no idea if it’s only for temp-tables or same holds true for database tables.

I have since a long time had the impression that each index is individually written to storage when all the fields of that index have been set, or the buffer is released, even for regular database tables. And only those indexes that have been written to storage can be found by other users or procedures, from that point in time. (And you need to use NO-LOCK to test this on regular tables.)

Usually, when you assign all fields in a single statement, or release it shortly after, you don't notice the difference, but if you do it one field at a time and don't release, it becomes more apparent.

(I don't remember if there was any difference between the primary index and other indexes.)

Maybe some-one from Progress can confirm or deny whether this impression is correct?

Posted by gus bjorklund on 24-Feb-2018 04:44

marian, you are correct.

the database writes are postponed as long as possible so that multiple updates to the same record can be coalesced.

there is another wrinkle: /creates/ are delayed as long as possible too.

however, the compiler ought to be smart enough to handle the can-find. that is not as simple as it might seem. what if there is a unique key violation and the (delayed) index entry can’t be created?

Posted by jquerijero on 19-Mar-2018 14:56

Your temp-table has no defined index that is why it is working without the RELEASE statement.

[quote user="Kunal Berlia"]

DEFINE TEMP-TABLE tttable no-undo
FIELD tablename AS CHARACTER.
 
DEFINE BUFFER btable FOR tttable.
 
EMPTY TEMP-TABLE tttable.
 
PROCEDURE ip_a:
 
CREATE tttable.
ASSIGN tttable.tablename = "customer"    .
//RELEASE tttable.
RUN ip_b.
 
 
END.
PROCEDURE ip_b:
    FOR each btable NO-LOCK :
       MESSAGE btable.TABLENAME
       VIEW-AS ALERT-BOX.
   END.  
END.
 
RUN ip_a.
 
Using this, it is allowing me to show the buffer of for each Without release statement. Does this is considering the whole procedure as transaction?
 
 

[/quote]

Posted by jquerijero on 19-Mar-2018 15:04

I think this is true also for database table, and can also happen even when all fields that are part of the index are filled in. 

[quote user="marian.edu"]

The explanation would be the index record was not created because no value was set for one of the index fields, then the for loop uses (rightful) the multi-field index but there is no record created in that index :(

[/quote]

This thread is closed