Hi Sandro,
I have only one detail to add: the MAX+1 policy that is used when AUTOINCREMENT
is not declared can lead to re-use of previously used ROWIDs long before
reaching the max possible value for an INTEGER 64bit. User just needs to delete
the record with the highest ROWID. I trust that triggers in SpatiaLite behave
right but I just want to emphasize for other readers that re-using ROWIDs is
not really "an highly unrealistic condition" and it must be considered if ROWID
is used as a foreign key. Re-use happens always and inevidently for example if
table is truncated. Test with this SQL and table foo2 will have ROWID=1
(calculated as 0+1=1) while table foo3 has ROWID=2 because that value is taken
from a sequence by autoincrement.
create table foo2 (id INTEGER PRIMARY KEY,bar INTEGER);
create table foo3 (id INTEGER PRIMARY KEY AUTOINCREMENT,bar INTEGER);
insert into foo2 (bar) values (1);
insert into foo3 (bar) values (1);
delete from foo2;
delete from foo3;
insert into foo2 (bar) values (2);
insert into foo3 (bar) values (2);
-Jukka-
________________________________________
Lähettäjä: a.furi...@lqt.it <a.furi...@lqt.it>
Lähetetty: 7. maaliskuuta 2017 18:01
Vastaanottaja: Rahkonen Jukka (MML)
Kopio: Even Rouault; gdal-dev@lists.osgeo.org
Aihe: Re: [gdal-dev] sqlite -> spatialite (rowid)
On Tue, 7 Mar 2017 15:07:04 +0000, Rahkonen Jukka (MML) wrote:
> Hi,
>
> I have a slight feeling that there may be some trouble if attribute
> "ROWID" is INTEGER PRIMARY KEY but it does not AUTOINCREMENT and
> users
> do lot of deletes and inserts. Maybe not if the triggers in r-tree
> tables are clever enough. Just a slight feeling but perhaps you
> should
> test also cases where ROWID differs from OID and __ROWID__?
>
Hi Jukka,
we can safely exclude any possible dangerous side-effect.
in SQLite any INTEGER PRIMARY KEY definition will always
ensure that an appropriate value will be automatically
set if not explicitly provided.
what exactly does the AUTOINCREMENT keyword is just
slightly changing the mechanism adopted to automatically
assign PK values.
1. not declaring AUTOINCREMENT
SQLite will search for the biggest PK value, and
will adopt this value increased by 1 as the new
PK value.
if the max possible value for an INTEGER 64bit
has already been used (an highly unrealistic
condition) then SQLite will search if there
is some free "hole" in the PK sequence, and
will reuse the first unused value it can find
note: in this exceptional case it's not
exactly true that ROWIDs will be generated
in a regularly ascending order; sometimes
lower values could be possibly returned.
2. declaring AUTOINCREMENT
in this case the automatically generated PK
values can never return back; they can simply
growth until the maximum value supported
by an INTEGER 64bit is reached.
and at this point the auto-sequence will
definitely stop working.
bye Sandro
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
