Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Art,

This is SUCH a revelation to me! It won't help for my situation because our legacy application is rife with zillions of explicit references to rowid itself.  Hence, we have no choice but to use the pseudo rowid. But it is great to know.

Stepping off on a tangent here: For your example,  I must presume that your example was done on release 15.x. Why assume so?  Because below release 15, the rowid part of the first row in a table would be 257 == 0x101; page 1, slot 1.  In your example, the rowid part of the first row was the insanely negative -9223372036854710271. In hex, that is 0x8000 0000 0001 0001.  Still page 1, slot 1 but the slot number is a 16-bit number rather than 8 bit.  In this case 65,537

So here's my segue question: Why is the high-order bit of that rowid set to 1?  (I'm sure that is explained on one of myriad help pages.  But which one?)

Again, thanks for that eye-opener.

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Art may be already drafting an answer here but I'll jump in anyway. Internally we pass rowids around in the code everywhere. Parameters to routines, return values from routines. We have zillions of variables that have to contain them. Rather than attempt to have separate 4-byte integer variables/parameters to store legacy rowids along with 8-byte integer variables/parameters to store large rowids, we standardized all rowid containers as 8-byte integers. This made the code much, much simpler and easier to maintain.

But given an 8-byte integer rowid value we need a way to distinguish between legacy and large rowids. That's the purpose of setting the sign bit for large rowids. Capacity is already so huge that we aren't sacrificing much by reserving that highest-order bit.

Art,

This is SUCH a revelation to me! It won't help for my situation because our legacy application is rife with zillions of explicit references to rowid itself.  Hence, we have no choice but to use the pseudo rowid. But it is great to know.

Stepping off on a tangent here: For your example,  I must presume that your example was done on release 15.x. Why assume so?  Because below release 15, the rowid part of the first row in a table would be 257 == 0x101; page 1, slot 1.  In your example, the rowid part of the first row was the insanely negative -9223372036854710271. In hex, that is 0x8000 0000 0001 0001.  Still page 1, slot 1 but the slot number is a 16-bit number rather than 8 bit.  In this case 65,537

So here's my segue question: Why is the high-order bit of that rowid set to 1?  (I'm sure that is explained on one of myriad help pages.  But which one?)

Again, thanks for that eye-opener.

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Art may be already drafting an answer here but I'll jump in anyway. Internally we pass rowids around in the code everywhere. Parameters to routines, return values from routines. We have zillions of variables that have to contain them. Rather than attempt to have separate 4-byte integer variables/parameters to store legacy rowids along with 8-byte integer variables/parameters to store large rowids, we standardized all rowid containers as 8-byte integers. This made the code much, much simpler and easier to maintain.

But given an 8-byte integer rowid value we need a way to distinguish between legacy and large rowids. That's the purpose of setting the sign bit for large rowids. Capacity is already so huge that we aren't sacrificing much by reserving that highest-order bit.

Art,

This is SUCH a revelation to me! It won't help for my situation because our legacy application is rife with zillions of explicit references to rowid itself.  Hence, we have no choice but to use the pseudo rowid. But it is great to know.

Stepping off on a tangent here: For your example,  I must presume that your example was done on release 15.x. Why assume so?  Because below release 15, the rowid part of the first row in a table would be 257 == 0x101; page 1, slot 1.  In your example, the rowid part of the first row was the insanely negative -9223372036854710271. In hex, that is 0x8000 0000 0001 0001.  Still page 1, slot 1 but the slot number is a 16-bit number rather than 8 bit.  In this case 65,537

So here's my segue question: Why is the high-order bit of that rowid set to 1?  (I'm sure that is explained on one of myriad help pages.  But which one?)

Again, thanks for that eye-opener.

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!

Art,

This is SUCH a revelation to me! It won't help for my situation because our legacy application is rife with zillions of explicit references to rowid itself.  Hence, we have no choice but to use the pseudo rowid. But it is great to know.

Stepping off on a tangent here: For your example,  I must presume that your example was done on release 15.x. Why assume so?  Because below release 15, the rowid part of the first row in a table would be 257 == 0x101; page 1, slot 1.  In your example, the rowid part of the first row was the insanely negative -9223372036854710271. In hex, that is 0x8000 0000 0001 0001.  Still page 1, slot 1 but the slot number is a 16-bit number rather than 8 bit.  In this case 65,537

So here's my segue question: Why is the high-order bit of that rowid set to 1?  (I'm sure that is explained on one of myriad help pages.  But which one?)

Again, thanks for that eye-opener.

Jacob:

I know that this does not fix a problem with 3rd party applications that use ROWID specifically, but for customer developed software and for ad-hoc queries, there is the pseudo-column ifx_row_id which exists for all tables, even partitioned tables, without adding any hidden column like the WITH ROWIDS feature:

The ifx_row_id was introduced a LONG time ago (so long I can't find anyone who remembers what version it appears in first). As long as you are using v14.10.FC6 or later, SELECT  FROM table WHERE ifx_row_id = '1049283:769'; is at least as faster as SELECT  * FROM table WHERE ROWID = 769; (and can be faster) because, like the real ROWID pseudo-column, it represents the physical address of the row on disk (or more accurately the physical location of the row's slot table entry on disk). Unlike ROWID the pseudo-column which does not contain the partition number, and the WITH ROWID hidden column which is just an indexed SERIAL column, ifx_row_id exists and works just fine even for partitioned tables:

Hi again today.

It is known that when I create a table "with rowid" (or alter fragment .. with rowid) the server create a shadow serial column named rowid and and index named "system-rowid" to go with it.  We have experienced serial values overflowing the 2^31 threshold, resulting in a negative serial value. (Why, oh why, wasn't serial designated as unsigned integer?)  My concern is that, if a table gets gazzilion inserts, the pseudo-rowid could also go negative.  I know some legacy application packages - that get extra speed by using rowid - get cranky with faced with a negative rowid.

Well, if serial can overflow into the negatives, what's to stop the pseudo rowid from doing the same, evoking the same cranky behavior as when the true rowid looks negative. (Like when the logical page number is over 2^23.)  At least for serial we now have the bigserial (64-bit) data type to forestall the negativity. But what's available for pseudo-rowid?

Ideas, anyone?

Thanks!