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.
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.
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.
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:
FOR EACH on the buffer does not show the newly created record.
Using a named buffer in this case doesn't work. The release statement works however.
> 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.
DEFINE TEMP-TABLE tttable no-undo
FIELD tablename AS CHARACTER.
DEFINE BUFFER btable FOR tttable.
EMPTY TEMP-TABLE tttable.
ASSIGN tttable.tablename = "customer" .
FOR each btable NO-LOCK :
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?
Man, one need to really review all code that uses temp-tables… there really seems to be (not so) funny issues there :)
Ok, this is because can-find is probably using a different (and hidden) buffer reference.
Thanks Piotr and George
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.
> 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.
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.
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.
cmon guys - you've both been around long enough to know that this is "expected behaviour**"
**"Expected behaviour" is something that is not expected, nor desired , but because it's documented it's ok ... :)
> 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.
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.
Learnt something new today, didn't know that FIND was releasing the current buffer. Now that you say it, it sounds OK :)
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 :)
> 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?
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?
Your temp-table has no defined index that is why it is working without the RELEASE statement.
[quote user="Kunal Berlia"]
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.