Use of ROWID in update statements
Florian wanted to replace ESQL "UPDATE ... WHERE CURRENT OF cursor" with ROWID-based updates and asked whether an int4 holds a ROWID and when ROWIDs change. Art Kagel explained: non-fragmented tables have a 32-bit physical ROWID (stable, with forward pointers if a row outgrows its page), fragmented tables need WITH ROWID (a hidden serial), otherwise no ROWID exists; int4 suffices, but never store ROWIDs persistently (unload/load changes them). Andreas Legner and Fernando Nunes added that pending in-place alters (and repack) can move rows without forward pointers, even causing a row to be read twice in one scan. Art supplied a sysmaster query to find tables with outstanding in-place alters, and the fix is to run task('table update_ipa parallel', ...), with Florian finding the correct argument form (table, database, owner).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I am currently working on replacing some of our ESQL code with a 3rd party library for easier consistency with Oracle. In the process I noticed that this library doesn't really let me handle cursor the way I want, so something like: UPDATE ... SET ... WHERE CURRENT OF <cursor_name> is somewhat out of the question. I was thinking of switching the code to fetch and use ROWID like we do in Oracle. Now I have two questions: * sqlca.sqlerrd in the ESQL sources seems to be an int4, is this enough? * When does the ROWID change? Will it be stable inside a transaction or when modifying rows which have been selected with "SELECT FOR UPDATE" -- I'd assume yes, since it is a physical row id, but who knows ;) Thanks & best regards, Florian
Florian: There are two different ROWIDs in Informix and a third ROWID condition that has to be handled: 1. ROWID for a non-fragmented table This is the physical address of the row, it is relative page in the table and shifted 8 bits plus the slot number on the page where the row resides. This does not change. If a variable length row is updated and so it outgrows the space available on its "home page" it is moved to another page and leaves behind in its slot on the original home page a pointer to its new location. So, its ROWID for all purposes, including indexing, id permanent. This rowid is always 32 bits (the 8bit shift is why a single partition table cannot exceed 2^24 pages). 2. ROWID for a fragmented table WITH ROWID This is not really a ROWID but just a hidden SERIAL type column. This is also a permanent value that never changes. 3. Fragmented tables without the WITH ROWID clause. These rows have no ROWID at all. They do have a physical address that is used in indexing, but there is no way that you can access that value. So, yes, INT4 is sufficient to hold a ROWID. Never use a ROWID for reference purposes in permanent structures because there are ways to change the physical location of a row of data and therefore its ROWID and that would break such references. Example: export/import. 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 Tue, Feb 14, 2017 at 3:41 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi, > > I am currently working on replacing some of our ESQL code with a 3rd party > library for easier consistency with Oracle. In the process I noticed that > this > library doesn't really let me handle cursor the way I want, so something > like: > > UPDATE ... SET ... WHERE CURRENT OF <cursor_name> > > is somewhat out of the question. I was thinking of switching the code to > fetch > and use ROWID like we do in Oracle. Now I have two questions: > > * sqlca.sqlerrd in the ESQL sources seems to be an int4, is this enough? > * When does the ROWID change? Will it be stable inside a transaction or > when > modifying rows which have been selected with "SELECT FOR UPDATE" -- I'd > assume > yes, since it is a physical row id, but who knows ;) > > Thanks & best regards, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c0d44c2a5be200548840311
There's one more case where a row's rowid can change, even within a=20 transaction and in a way that the row might even be found a second time by = same statement: if an old version (in-place alter) page gets update to=20 latest version and some of its rows need to move out of this page - they'd = do this in their entirety, without leaving a forward pointer on the old=20 page. So when relying on rowid =3D physical location for addressing a row, make=20 sure you don't have outstanding in-place alters. Regards, Andreas From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 14.02.2017 22:12 Subject: Re: Use of ROWID in update statements [38632] Sent by: ids-bounces@iiug.org Florian:=20 There are two different ROWIDs in Informix and a third ROWID condition=20 that=20 has to be handled:=20 1. ROWID for a non-fragmented table=20 This is the physical address of the row, it is relative page in the=20 table and shifted 8 bits plus the slot number on the page where the row=20 resides. This does not change. If a variable length row is updated and so=20 it outgrows the space available on its "home page" it is moved to another=20 page and leaves behind in its slot on the original home page a pointer to=20 its new location. So, its ROWID for all purposes, including indexing, id=20 permanent. This rowid is always 32 bits (the 8bit shift is why a single=20 partition table cannot exceed 2^24 pages).=20 2. ROWID for a fragmented table WITH ROWID=20 This is not really a ROWID but just a hidden SERIAL type column. This is=20 also a permanent value that never changes.=20 3. Fragmented tables without the WITH ROWID clause.=20 These rows have no ROWID at all. They do have a physical address that is=20 used in indexing, but there is no way that you can access that value.=20 So, yes, INT4 is sufficient to hold a ROWID. Never use a ROWID for=20 reference purposes in permanent structures because there are ways to=20 change=20 the physical location of a row of data and therefore its ROWID and that=20 would break such references. Example: export/import.=20 Art=20 Art S. Kagel, President and Principal Consultant=20 ASK Database Management=20 www.askdbmgt.com=20 Blog: http://informix-myview.blogspot.com/=20 Disclaimer: Please keep in mind that my own opinions are my own opinions=20 and do not reflect on the IIUG, nor any other organization with which I am = associated either explicitly, implicitly, or by inference. Neither do=20 those opinions reflect those of other individuals affiliated with any=20 entity with which I am affiliated nor those of the entities themselves.=20 On Tue, Feb 14, 2017 at 3:41 PM, FLORIAN APOLLONER=20 <florian.apolloner@bap.at=20 > wrote:=20 > Hi,=20 >=20 > I am currently working on replacing some of our ESQL code with a 3rd=20 party=20 > library for easier consistency with Oracle. In the process I noticed=20 that=20 > this=20 > library doesn't really let me handle cursor the way I want, so something = > like:=20 >=20 > UPDATE ... SET ... WHERE CURRENT OF <cursor=5Fname>=20 >=20 > is somewhat out of the question. I was thinking of switching the code to = > fetch=20 > and use ROWID like we do in Oracle. Now I have two questions:=20 >=20 > * sqlca.sqlerrd in the ESQL sources seems to be an int4, is this enough? = > * When does the ROWID change? Will it be stable inside a transaction or=20 > when=20 > modifying rows which have been selected with "SELECT FOR UPDATE" -- I'd=20 > assume=20 > yes, since it is a physical row id, but who knows ;)=20 >=20 > Thanks & best regards,=20 > Florian=20 >=20 >=20 > ************************************************************=20 > *******************=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20 >=20 >=20 --94eb2c0d44c2a5be200548840311=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
YES! That's the one I couldn't dredge up! Thanks Andreas. 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 Tue, Feb 14, 2017 at 4:26 PM, Andreas Legner <andreas.legner@de.ibm.com> wrote: > There's one more case where a row's rowid can change, even within a=20 > transaction and in a way that the row might even be found a second time by > = > > same statement: if an old version (in-place alter) page gets update to=20 > latest version and some of its rows need to move out of this page - they'd > = > > do this in their entirety, without leaving a forward pointer on the old=20 > page. > So when relying on rowid =3D physical location for addressing a row, > make=20 > sure you don't have outstanding in-place alters. > > Regards, > Andreas > > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org > Date: 14.02.2017 22:12 > Subject: Re: Use of ROWID in update statements [38632] > Sent by: ids-bounces@iiug.org > > Florian:=20 > > There are two different ROWIDs in Informix and a third ROWID condition=20 > that=20 > has to be handled:=20 > > 1. ROWID for a non-fragmented table=20 > > This is the physical address of the row, it is relative page in the=20 > > table and shifted 8 bits plus the slot number on the page where the row=20 > > resides. This does not change. If a variable length row is updated and > so=20 > > it outgrows the space available on its "home page" it is moved to > another=20 > > page and leaves behind in its slot on the original home page a pointer > to=20 > > its new location. So, its ROWID for all purposes, including indexing, id=20 > > permanent. This rowid is always 32 bits (the 8bit shift is why a single=20 > > partition table cannot exceed 2^24 pages).=20 > > 2. ROWID for a fragmented table WITH ROWID=20 > > This is not really a ROWID but just a hidden SERIAL type column. This is=20 > > also a permanent value that never changes.=20 > > 3. Fragmented tables without the WITH ROWID clause.=20 > > These rows have no ROWID at all. They do have a physical address that is=20 > > used in indexing, but there is no way that you can access that value.=20 > > So, yes, INT4 is sufficient to hold a ROWID. Never use a ROWID for=20 > reference purposes in permanent structures because there are ways to=20 > change=20 > the physical location of a row of data and therefore its ROWID and that=20 > would break such references. Example: export/import.=20 > > Art=20 > > Art S. Kagel, President and Principal Consultant=20 > ASK Database Management=20 > www.askdbmgt.com=20 > > Blog: http://informix-myview.blogspot.com/=20 > > Disclaimer: Please keep in mind that my own opinions are my own opinions=20 > and do not reflect on the IIUG, nor any other organization with which I am > = > > associated either explicitly, implicitly, or by inference. Neither do=20 > those opinions reflect those of other individuals affiliated with any=20 > entity with which I am affiliated nor those of the entities themselves.=20 > > On Tue, Feb 14, 2017 at 3:41 PM, FLORIAN APOLLONER=20 > <florian.apolloner@bap.at=20 > > wrote:=20 > > > Hi,=20 > >=20 > > I am currently working on replacing some of our ESQL code with a 3rd=20 > party=20 > > library for easier consistency with Oracle. In the process I noticed=20 > that=20 > > this=20 > > library doesn't really let me handle cursor the way I want, so something > = > > > like:=20 > >=20 > > UPDATE ... SET ... WHERE CURRENT OF <cursor=5Fname>=20 > >=20 > > is somewhat out of the question. I was thinking of switching the code to > = > > > fetch=20 > > and use ROWID like we do in Oracle. Now I have two questions:=20 > >=20 > > * sqlca.sqlerrd in the ESQL sources seems to be an int4, is this enough? > = > > > * When does the ROWID change? Will it be stable inside a transaction > or=20 > > when=20 > > modifying rows which have been selected with "SELECT FOR UPDATE" -- > I'd=20 > > assume=20 > > yes, since it is a physical row id, but who knows ;)=20 > >=20 > > Thanks & best regards,=20 > > Florian=20 > >=20 > >=20 > > ************************************************************=20 > > *******************=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > >=20 > >=20 > > --94eb2c0d44c2a5be200548840311=20 > > ************************************************************ > ***************= > ****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b327ad5f2150548843f11
Hi Art & Andreas, first off, thanks for the great answers, much appreciated! A small follow up though: > There's one more case where a row's rowid can change, even within a > transaction and in a way that the row might even be found a second time by > same statement: if an old version (in-place alter) page gets update to > latest version and some of its rows need to move out of this page I think I have seen this before (not with ROWID though), in essence: I did run alter on a table (it resulted in a fast/inplace alter), then ran a select over the whole table and updated a few rows while going through. This resulted in duplicated rows (ie rows that got updated where fetched by the outer select). Is this what you ment by that? Here is a gist of the example output and script (test.py, it is python but I think it should be fairly readable): https://gist.github.com/apollo13/cb1ca136b7469b238a93 Cheers, Florian
Oh, and how can I ensure that I have no outstanding table alters? Sounds like something my database migration script should take care of :)
find_ipas.sql
---------------------------------------------
set environment optcompind '0';
set isolation dirty read;
{ Identify partnums that have been altered. }
select (p.pg_partnum + p.pg_pagenum - 1) as partn, a.name as dbspace
from sysmaster:syspaghdr p, sysmaster:sysdbspaces a
where p.pg_partnum = 1048576 * a.dbsnum + 1
-- Tblspace tblspace partnums have format 0xnnn00001
and a.is_temp = 0 and a.is_blobspace = 0 and a.is_sbspace = 0
-- exclude tempspaces, blobspaces and smart blob spaces
and p.pg_next != 0
-- non zero = altered
into temp altpts with no log ;
{ Get all table partnums & save in a temp table. }
select b.dbsname database, b.tabname table,
a.dbspace dbspace, hex(a.partn) partnum,
i.ti_nrows nrows, i.ti_npdata datapages,
-1 as minver, 999 as maxver
from altpts a, outer sysmaster:systabnames b,
outer sysmaster:systabinfo i
where a.partn = b.partnum and a.partn = i.ti_partnum
into temp tabvers with no log ;
{ Set the minimum page version for each table saved. }
update tabvers set minver = (select (min(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1 -- data page
and sysmaster:bitval(pg_flags, '0x8') <> 1 -- not remainder page
and pg_frcnt < ((pg_pagesize) - 4*pg_nslots) -- with some data on
it.
);
{ Set the maximum version for each table saved. }
update tabvers set maxver = (select (max(pg_next)) / 16777216
from sysmaster:syspaghdr p, altpts a
where p.pg_partnum = a.partn
and tabvers.partnum = a.partn
and sysmaster:bitval(pg_flags, '0x1') = 1
and sysmaster:bitval(pg_flags, '0x8') <> 1
and pg_frcnt < ((pg_pagesize) - 4*pg_nslots)
);
{
If the min version < max version definitely has incomplete alters.
If the min version = max version it MIGHT have incomplete alters.
}
select database, table, dbspace, nrows, datapages, minver, maxver,
decode(maxver - minver, 0, "Maybe Needs Update", "Definitely Needs Update")
from tabvers
order by 1, 2;
drop table tabvers;
drop table altpts;--------------------------------------------------------------------------------
------------------------------------------
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 Tue, Feb 14, 2017 at 5:07 PM, FLORIAN APOLLONER <florian.apolloner@bap.at
> wrote:
> Oh, and how can I ensure that I have no outstanding table alters? Sounds
> like
> something my database migration script should take care of :)
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f403045d5b10db5487054884eda1
Yes, basically the row with key '1' was moved to another page when it was updated from the older schema to the newest schema and so your SELECT found it again when it scanned that last page. 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 Tue, Feb 14, 2017 at 5:03 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi Art & Andreas, > > first off, thanks for the great answers, much appreciated! > > A small follow up though: > > > There's one more case where a row's rowid can change, even within a > > transaction and in a way that the row might even be found a second time > by > > same statement: if an old version (in-place alter) page gets update to > > latest version and some of its rows need to move out of this page > > I think I have seen this before (not with ROWID though), in essence: I did > run > alter on a table (it resulted in a fast/inplace alter), then ran a select > over > the whole table and updated a few rows while going through. This resulted > in > duplicated rows (ie rows that got updated where fetched by the outer > select). > Is this what you ment by that? > > Here is a gist of the example output and script (test.py, it is python but > I > think it should be fairly readable): > https://gist.github.com/apollo13/cb1ca136b7469b238a93 > > Cheers, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f403045f4fae9415bc054884f535
I don't really know what to say, that is just amazing -- thanks!
New features like repack can also change the rowid (although it shouldn't happen for a record locked). Regards. On Tue, Feb 14, 2017 at 10:07 PM, FLORIAN APOLLONER < florian.apolloner@bap.at> wrote: > Oh, and how can I ensure that I have no outstanding table alters? Sounds > like > something my database migration script should take care of :) > > > ************************************************************ > ******************* > 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... --001a11448bb4ae6880054885262f
From the docs it looks like that those are mainly maintenance operations and executed by the DBA -- ie should not interfere with normal operations, right? Just out of curiosity, is an "UPDATE ... WHERE CURRENT OF ..." safe in regard with row moving, or does it exhibit the same problems as ROWID? Thanks, Florian
Mhm, I did try
> EXECUTE FUNCTION task('table update_ipa parallel','table_name');
on one of the tables reported by the script. Doing that in the db in question
with a normal user resulted in a message about "task cannot be resolved".
I am on 12.10FC5 (yes I should update that ;)) and reran as informix against
the sysadmin database, which could resolve the task but resulted in:
> (expression) FAILED: table sysadmin:informix.table_name
The docs just mention an unqualified table name. How do I properly reference
the table in question?
Thanks,
Florian
Ah, found it:
> EXECUTE FUNCTION task('table update_ipa parallel','table_name', 'db_name','owner_name');
IB it should be:
execute function task( 'table update_ipa parallel', 'database:tablename' );
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 Tue, Feb 14, 2017 at 5:46 PM, FLORIAN APOLLONER <florian.apolloner@bap.at
> wrote:
> Mhm, I did try
>
> > EXECUTE FUNCTION task('table update_ipa parallel','table_name');>
> on one of the tables reported by the script. Doing that in the db in
> question
> with a normal user resulted in a message about "task cannot be resolved".
>
> I am on 12.10FC5 (yes I should update that ;)) and reran as informix
> against
> the sysadmin database, which could resolve the task but resulted in:
>
> > (expression) FAILED: table sysadmin:informix.table_name
>
> The docs just mention an unqualified table name. How do I properly
> reference
> the table in question?
>
> Thanks,
> Florian
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c0d44c2ce1c4505488563ba