Record creation order

B

bohrmann

Member
Hi,

I'm using Progress 9.1E database in Sun Solaris.

I encountered a situation which I cannot explain, why it happened. There is a program code looking like this (simplified somehow):

Code: 
ON "F10" OF query DO
:
IF table1.status = "B" THEN 
DO:
table1.status = "C".
  DO:
    CREATE t_table2.
    ASSIGN 
      table2.date = DATE
      table2.time = TIME
table2.text = "abcIR".
    RELEASE table2.
    RELEASE table1.
  END.
END.
ELSE IF table1.status = "A" THEN 
  DO:
table1.status = "B".
    DO:
      CREATE t_table2.
      ASSIGN 
        table2.date = DATE
        table2.time = TIME
table2.text = "abcRE".
      RELEASE table2.
      RELEASE table1.
    END.
  END.

END.

The problem occurs whenever I press F10 twice within one second. Table2 has a primary (but not unique) index including the date and time fields. In the first case table1.status is "A", so status will be set to "B", then pressing F10 second time will set the status to "C". The problem is with table2, because the two new records should be created in this order:

02/15/2012 11:45:02 abcRE
02/15/2012 11:45:02 abcIR

But I made the program select table content to a log file after it created table2 records, and the result is as follows:

select *
from table2
02/15/2012 11:45:02 abcRE
select *
from table2
02/15/2012 11:45:02 abcIR
02/15/2012 11:45:02 abcRE

So it seems in the first case table2 record was created (with "abcRE"), then in the second case the new table2 record (with "abcIR") got created as if it would have recorded earlier. Any idea how it can occur that the record created later is the first and not the second one in the table?

Regards,
Peter
 
You don't say anything about the indexes on the table which is very important. If you don't specify a where clause or a sort the records will be retrieved according to the primary index on the table. It the table has no primary index defined then Progress will create sort of a default index for you automatically and AFAIK it is based on the ROWID ( or RECID ) of the record.

Having said that, depending on the primary index on the table the order makes perfect sense.

Heavy Regards, RealHeavyDude.
 
I mentioned that there is a primary key, which is date + time. Because it's the same in both cases, I supposed the records will be stored on a chronological basis. Furthermore I took several tests, creating records with identical keys, and they were selected always in the correct order (how they were created chronologically).

Regards,
Peter
 
Sorry - I did not see that.

Anyways, if you have duplicate keys in the primary index then the order is not really predictable. I am not 100% positive, but that is maybe where the ROWID ( the physical address of the record ) comes into play. You can not predict in which database block at which position the database engine will store a newly created record. In that sense it might be likely that you retrieve the records in the order they get created but there is no guarantee.
If you need to have the records in a particular order then your only guarantee is a unique index.

Heavy Regards, RealHeavyDude.
 
To get them to sort correctly, you will need something more finegrained than one second.
 
You must log in or register to reply here.
Back
Top