Hi,
there has been a discussion in the public forums recently on the reliably of ROWID's (or the lack of) to relocate records when table partitioning is used. The discussions were mainly on how well ADM and ADM2 will cope with records being updated (by someone else) with the consequence that the record has moved to a different record and the risk, that the same ROWID is now used by a different record..
With ProDatasets typically unique keys values in the before table are used to refetch a record for update (done all nicely by SAVE-ROW-CHANGES). This should not be a problem, unless the unique key values (let's hope for GUID's) are not reused.
Unfortunately we have customers of our OERA framework that have (some) tables with no unique keys. The ProDataset allows the use of the ROWID as an emergency key. Which works great.
Am I right in assuming, that in 11.4 with table partitioning the use of ROWID's for the DATA-SOURCE KEYS definition has the same risk as what was discussed for ADM and ADM2 in the other thread?
A client holds a record and is updating it.
In the mean time, a different client updates the original record with the consequence, that it's moved to a different partition/storage area and a new record is created with the original ROWID value.
The first client, then submits a row for update with the stored ROWID in the before table and the SAVE-ROW-CHANGES is going to update the wrong record based on the ROWID key.
Or is there some magic built into the ProDataset to avoid this situation?
Mike
with partitioned tables, a row's rowid will change only if the row is moved. it can occur if you update the partition key value. don't do that. Oracle does not even allow it.
The ADM issue I mentioned has nothing to do with table partitioning or with changing rowid's. that problem was caused by the adm trying to read a table's /template record/ in a multitenant environment. that read fails because the template record is not directly readable by 4GL code executed by ordinary users. nothing at all to do with table partitioning.
the other problem i mentioned is relatively rare and also has nothing to do with table partitioning. it is caused by dirty reads and subsequent deletion of the data you read and then the rowid being reused. again, nothing to do with partitioning, but it can happen with partitioned tables too.
internally, the rowids used by the 4GL runtime are accompanied by the partition id and table number.
“internally, the rowids used by the 4GL runtime are accompanied by the partition id and table number.”
with partitioned tables, a row's rowid will change only if the row is moved. it can occur if you update the partition key value. don't do that. Oracle does not even allow it.
The ADM issue I mentioned has nothing to do with table partitioning or with changing rowid's. that problem was caused by the adm trying to read a table's /template record/ in a multitenant environment. that read fails because the template record is not directly readable by 4GL code executed by ordinary users. nothing at all to do with table partitioning.
the other problem i mentioned is relatively rare and also has nothing to do with table partitioning. it is caused by dirty reads and subsequent deletion of the data you read and then the rowid being reused. again, nothing to do with partitioning, but it can happen with partitioned tables too.
internally, the rowids used by the 4GL runtime are accompanied by the partition id and table number.
[collapse]On 06/03/2014 05:19 PM, gus wrote:
> with partitioned tables, a row's rowid will change only if the row is moved. it can occur if you update the partition key value. don't do that.
will it be painful? :)
> Oracle does not even allow it.
in fact it does, they do offer a 'row movement' for partitioned tables
so one can decide a record will not move to another partition even if
changes to the partition fields would otherwise lead to that... by
default an update can translate to a delete+insert (move to another
partition) which means different rowid in Oracle as well.
>
> The ADM issue I mentioned has nothing to do with table partitioning or with changing rowid's.
Think Mike is referring to Rick's thread on that subject -
https://community.progress.com/products/beta-eap/OE114-External/f/41/t/9225.aspx
Normally this will affect ProDatasets using rowid as identifier (if no
primary key exist for the table), a client sending changes to such a
dataset might fail to find the record if previous update lead to a
partition move... or might find the wrong record if rowid was reused.
> that problem was caused by the adm trying to read a table's /template record/ in a multitenant environment. that read fails because the template record is not directly readable by 4GL code executed by ordinary users. nothing at all to do with table partitioning.
> the other problem i mentioned is relatively rare and also has nothing to do with table partitioning. it is caused by dirty reads and subsequent deletion of the data you read and then the rowid being reused. again, nothing to do with partitioning, but it can happen with partitioned tables too.
> internally, the rowids used by the 4GL runtime are accompanied by the partition id and table number.
>
> View this message online or reply to this message (http://community.progress.com/products/beta-eap/OE114-External/f/41/p/10566/39561.aspx#39561).
>
>
--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.edu[/collapse]
I know pretty much nothing about the ADM so I'm not commenting on that. But in regards to your original question about ProDataSets and SAVE-ROW-CHANGES, I think that is possible. I don't believe there is any magic built into the ProDataset to avoid this. PLEASE someone correct me if I'm wrong.
I know pretty much nothing about the ADM so I'm not commenting on that. But in regards to your original question about ProDataSets and SAVE-ROW-CHANGES, I think that is possible. I don't believe there is any magic built into the ProDataset to avoid this. PLEASE someone correct me if I'm wrong.
So, if the lessons here are, don't design data bases with tables that have no unique key and, if you do, don't partition those tables, I for one don't find that onerous.
So, if the lessons here are, don't design data bases with tables that have no unique key and, if you do, don't partition those tables, I for one don't find that onerous.
Documentation is good ... if people actually read it. More often, I think, it provides a place that someone can point to afterward.
This case might be a little marginal since the use case that creates a problem is something I would consider bad database design.
Documentation is good ... if people actually read it. More often, I think, it provides a place that someone can point to afterward.
This case might be a little marginal since the use case that creates a problem is something I would consider bad database design.
Flag this post as spam/abuse.
You have more faith than I about people actually reading documentation, especially carefully.
You have more faith than I about people actually reading documentation, especially carefully.
Flag this post as spam/abuse.
If it's not in the docs, then the chance of them learning this from the docs is 0.
actually there are legit cases when no pk is required for a table
(log/audit tables for instance)... just in that case there should be no
individual record update/delete. while deleting/updating a set of
records using selection criteria can work (even with sequential access),
individual records can't really be found if there is no primary key.
imho, the usage of rowid as an `alternate` pk in prodatasets shouldn't
be allowed... have no pk on the table you can't save-changes.
the chance a record gets a new rowid because of updates on partitioning
key(s) and later save-changes might not found the record or find a
different (new) record (rowid reuse) isn't going to be more frequent
than an actual delete followed by an insert that will reuse the rowid
which can happen on any table... bref, if one uses rowid as key in a
prodataset now will be as safe (or unsafe) when table partitioning is
used as it is now.
or maybe I'm slow and didn't get the lessons here :)
On 06/04/2014 05:56 PM, Thomas Mercer-Hursh wrote:
> So, if the lessons here are, don't design data bases with tables that have no unique key and, if you do, don't partition those tables, I for one don't find that onerous.
>
> View this message online or reply to this message (http://community.progress.com/products/beta-eap/OE114-External/f/41/p/10566/39642.aspx#39642).
>
>
--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.edu
Well, I tend to think that even though update is unlikely, that doesn't mean it will never happen, so it is better to provide it with a key. There are any number of tasks other than update where that could be useful.
Reply by Marian Eduactually there are legit cases when no pk is required for a table
(log/audit tables for instance)... just in that case there should be no
individual record update/delete. while deleting/updating a set of
records using selection criteria can work (even with sequential access),
individual records can't really be found if there is no primary key.
imho, the usage of rowid as an `alternate` pk in prodatasets shouldn't
be allowed... have no pk on the table you can't save-changes.
the chance a record gets a new rowid because of updates on partitioning
key(s) and later save-changes might not found the record or find a
different (new) record (rowid reuse) isn't going to be more frequent
than an actual delete followed by an insert that will reuse the rowid
which can happen on any table... bref, if one uses rowid as key in a
prodataset now will be as safe (or unsafe) when table partitioning is
used as it is now.
or maybe I'm slow and didn't get the lessons here :)
On 06/04/2014 05:56 PM, Thomas Mercer-Hursh wrote:
> So, if the lessons here are, don't design data bases with tables that have no unique key and, if you do, don't partition those tables, I for one don't find that onerous.
>
> View this message online or reply to this message (http://community.progress.com/products/beta-eap/OE114-External/f/41/p/10566/39642.aspx#39642).
>
>
--
m.edu
keep it simple
http://www.ganimede.ro
http://ro.linkedin.com/in/marianedu
medu@ganimede.ro
mobile: +40 740 036 212
skype: marian.eduStop receiving emails on this subject.Flag this post as spam/abuse.