<p><a href="https://github.com/rporres" class="user-mention">@rporres</a> Just chiming in briefly to restate the problem, as doesn't seem to have been voiced in this discussion:</p>
<ul>
<li>By default SQLite provides autoincrement via an alias to ROWNUM - this is performant, guarantees uniqueness, and does <em>NOT</em> guarantee monotonic increase</li>
<li>Your change makes <em>all</em> sqlite autoincs proper ANSI-sql autoincs - slower, unique and monotonically increasing</li>
</ul><p>The problem: there is likely a vast amount of software both on CPAN and on Darkpan which has tests and business logic assumptions based on the ROWNUM-aliased semantics. Hence this is a very disruptive change that had to be quickly backed out.</p>
<p>The possible solutions all circle around these criteria: </p>
<ul>
<li>A silent change of behavior like this is not an option. Folks need to have at least a <em>year</em> of clear warning that the behavior is going to be changing</li>
<li>The behavior needs to be switchable - folks should be able to select the less correct but more performant behavior (it is even recommended by the SQLite documentation). I suggest some sort of general SQLite-producer specific producer_flag. Something like <code>autoinc_method => [ rownum | sequence ]</code>, with the default being 'rownum' and being changed in a year to 'sequence'</li>
<li>The breakage (behavior change) should be introduced a bit sooner on AUTOMATED_TESTING=1 so that cpantesters can highlight the downstream fallout</li>
</ul><p>I think this should make for a smooth transition towards an "everybody wins" scenario.</p>
<p>Cheers</p>
<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br>Reply to this email directly or <a href='https://github.com/dbsrgits/sql-translator/pull/26#issuecomment-27643998'>view it on GitHub</a>.<img src='https://github.com/notifications/beacon/4W3BWTo7EIi_7vx28Xzl_uOeUxBDjNYWumjU51m-gOiLBMgmzsWdFwoWA5qcdsWE.gif' height='1' width='1'></p>