Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

szmate1618
Dear list members,

I have the following problem, with which I'd like to request your aid:

Currently, at version 3.25.2, SQLite only has a limited support for alter
table. E.g. you cannot change the datatype (type affinity) of a column, or
drop a column.

The usual workaround is to create a new table with the desired schema, fill
it with data from the original table, drop the original table, and rename
the new one. But what if the original table is a parent table in a foreign
key relationship?

The official solution
<https://www.sqlite.org/lang_altertable.html#otheralter> is turning foreign
keys off, making the changes you want, then turning foreign keys on. But
I'm slightly annoyed this cannot be done in a transaction (because these PRAGMA
foreign_keys =s don't take effect inside of transactions, so they need to
be issued before and after).

I'd like to use deferred foreign keys instead. I have 3 queries, one of
them seems to work, the two others do not. My questions are the following:

   - Does the seemingly working query work by design? Or it's just a
   fortunate(?) constellation of multiple factors, and depending on other
   tables or new data in the database it might break in the future? Somewhat
   like undefined behavior in C++?
   - Why do the other ones not work? How are they different from the first
   one?

Setup

PRAGMA foreign_keys = OFF;
DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
VALUES('whatever');

PRAGMA foreign_keys = ON;

Query1 - seems to be working as intended

BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
FROM Temp;DROP TABLE Temp;COMMIT;

Query2 - create [...] as select [...] fails

BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
from Query1DROP TABLE Temp;COMMIT;

Result:

sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
UNIQUE INDEX ParentIndex on Parent(A);
sqlite> SELECT * FROM Parent;
whatever

Query3 - insert into [...] fails

BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;

Result:

sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
sqlite> SELECT * FROM Parent;
whatever

Note that PRAGMA foreign_key_check does not report any problem in any of
the cases.


I posted an identical question on StackOverflow, but no one was able to
provide any information so far. Thanks in advance!

Máté Szabó
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

Dan Kennedy-4
On 11/23/2018 09:54 PM, szmate1618 wrote:

> Dear list members,
>
> I have the following problem, with which I'd like to request your aid:
>
> Currently, at version 3.25.2, SQLite only has a limited support for alter
> table. E.g. you cannot change the datatype (type affinity) of a column, or
> drop a column.
>
> The usual workaround is to create a new table with the desired schema, fill
> it with data from the original table, drop the original table, and rename
> the new one. But what if the original table is a parent table in a foreign
> key relationship?
>
> The official solution
> <https://www.sqlite.org/lang_altertable.html#otheralter> is turning foreign
> keys off, making the changes you want, then turning foreign keys on. But
> I'm slightly annoyed this cannot be done in a transaction (because these PRAGMA
> foreign_keys =s don't take effect inside of transactions, so they need to
> be issued before and after).


"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?

Dan.



>
> I'd like to use deferred foreign keys instead. I have 3 queries, one of
> them seems to work, the two others do not. My questions are the following:
>
>    - Does the seemingly working query work by design? Or it's just a
>    fortunate(?) constellation of multiple factors, and depending on other
>    tables or new data in the database it might break in the future? Somewhat
>    like undefined behavior in C++?
>    - Why do the other ones not work? How are they different from the first
>    one?
>
> Setup
>
> PRAGMA foreign_keys = OFF;
> DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
> COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
> DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
> Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
> VALUES('whatever');
>
> PRAGMA foreign_keys = ON;
>
> Query1 - seems to be working as intended
>
> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
> FROM Temp;DROP TABLE Temp;COMMIT;
>
> Query2 - create [...] as select [...] fails
>
> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
> from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
> from Query1DROP TABLE Temp;COMMIT;
>
> Result:
>
> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
> UNIQUE INDEX ParentIndex on Parent(A);
> sqlite> SELECT * FROM Parent;
> whatever
>
> Query3 - insert into [...] fails
>
> BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
> from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
> Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
> INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
>
> Result:
>
> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
> sqlite> SELECT * FROM Parent;
> whatever
>
> Note that PRAGMA foreign_key_check does not report any problem in any of
> the cases.
>
>
> I posted an identical question on StackOverflow, but no one was able to
> provide any information so far. Thanks in advance!
>
> Máté Szabó
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

Thomas Kurz
To what I've learned so far, SQlite stores all data "as is" into any column regardless of the column declaration. The affinity only matters upon reading, am I correct? If so, would it be a big deal implementing ALTER TABLE ALTER COLUMN?


----- Original Message -----
From: Dan Kennedy <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Friday, November 23, 2018, 16:30:12
Subject: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

On 11/23/2018 09:54 PM, szmate1618 wrote:
> Dear list members,

> I have the following problem, with which I'd like to request your aid:

> Currently, at version 3.25.2, SQLite only has a limited support for alter
> table. E.g. you cannot change the datatype (type affinity) of a column, or
> drop a column.

> The usual workaround is to create a new table with the desired schema, fill
> it with data from the original table, drop the original table, and rename
> the new one. But what if the original table is a parent table in a foreign
> key relationship?

> The official solution
> <https://www.sqlite.org/lang_altertable.html#otheralter> is turning foreign
> keys off, making the changes you want, then turning foreign keys on. But
> I'm slightly annoyed this cannot be done in a transaction (because these PRAGMA
> foreign_keys =s don't take effect inside of transactions, so they need to
> be issued before and after).


"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?

Dan.




> I'd like to use deferred foreign keys instead. I have 3 queries, one of
> them seems to work, the two others do not. My questions are the following:

>    - Does the seemingly working query work by design? Or it's just a
>    fortunate(?) constellation of multiple factors, and depending on other
>    tables or new data in the database it might break in the future? Somewhat
>    like undefined behavior in C++?
>    - Why do the other ones not work? How are they different from the first
>    one?

> Setup

> PRAGMA foreign_keys = OFF;
> DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
> COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
> DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
> Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
> VALUES('whatever');

> PRAGMA foreign_keys = ON;

> Query1 - seems to be working as intended

> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
> FROM Temp;DROP TABLE Temp;COMMIT;

> Query2 - create [...] as select [...] fails

> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
> from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
> from Query1DROP TABLE Temp;COMMIT;

> Result:

> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
> UNIQUE INDEX ParentIndex on Parent(A);
> sqlite> SELECT * FROM Parent;
> whatever

> Query3 - insert into [...] fails

> BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
> from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
> Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
> INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;

> Result:

> sqlite> PRAGMA foreign_key_check;
> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
> sqlite> SELECT * FROM Parent;
> whatever

> Note that PRAGMA foreign_key_check does not report any problem in any of
> the cases.


> I posted an identical question on StackOverflow, but no one was able to
> provide any information so far. Thanks in advance!

> Máté Szabó
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

Dan Kennedy-4
On 11/23/2018 10:47 PM, Thomas Kurz wrote:
> To what I've learned so far, SQlite stores all data "as is" into any column regardless of the column declaration. The affinity only matters upon reading, am I correct? If so, would it be a big deal implementing ALTER TABLE ALTER COLUMN?

Affinity changes are applied before data is written to the database. As
you say though, implementing ALTER TABLE to change the type would be
easier if they were not.

Dan.



>
>
> ----- Original Message -----
> From: Dan Kennedy <[hidden email]>
> To: [hidden email] <[hidden email]>
> Sent: Friday, November 23, 2018, 16:30:12
> Subject: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship
>
> On 11/23/2018 09:54 PM, szmate1618 wrote:
>> Dear list members,
>
>> I have the following problem, with which I'd like to request your aid:
>
>> Currently, at version 3.25.2, SQLite only has a limited support for alter
>> table. E.g. you cannot change the datatype (type affinity) of a column, or
>> drop a column.
>
>> The usual workaround is to create a new table with the desired schema, fill
>> it with data from the original table, drop the original table, and rename
>> the new one. But what if the original table is a parent table in a foreign
>> key relationship?
>
>> The official solution
>> <https://www.sqlite.org/lang_altertable.html#otheralter> is turning foreign
>> keys off, making the changes you want, then turning foreign keys on. But
>> I'm slightly annoyed this cannot be done in a transaction (because these PRAGMA
>> foreign_keys =s don't take effect inside of transactions, so they need to
>> be issued before and after).
>
>
> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
> database file. So what advantage would there be in including the PRAGMA
> statements in the body of a transaction?
>
> Dan.
>
>
>
>
>> I'd like to use deferred foreign keys instead. I have 3 queries, one of
>> them seems to work, the two others do not. My questions are the following:
>
>>    - Does the seemingly working query work by design? Or it's just a
>>    fortunate(?) constellation of multiple factors, and depending on other
>>    tables or new data in the database it might break in the future? Somewhat
>>    like undefined behavior in C++?
>>    - Why do the other ones not work? How are they different from the first
>>    one?
>
>> Setup
>
>> PRAGMA foreign_keys = OFF;
>> DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
>> COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
>> DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
>> Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
>> VALUES('whatever');
>
>> PRAGMA foreign_keys = ON;
>
>> Query1 - seems to be working as intended
>
>> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
>> FROM Temp;DROP TABLE Temp;COMMIT;
>
>> Query2 - create [...] as select [...] fails
>
>> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
>> from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
>> from Query1DROP TABLE Temp;COMMIT;
>
>> Result:
>
>> sqlite> PRAGMA foreign_key_check;
>> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
>> UNIQUE INDEX ParentIndex on Parent(A);
>> sqlite> SELECT * FROM Parent;
>> whatever
>
>> Query3 - insert into [...] fails
>
>> BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
>> from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
>> Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
>> INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
>
>> Result:
>
>> sqlite> PRAGMA foreign_key_check;
>> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
>> sqlite> SELECT * FROM Parent;
>> whatever
>
>> Note that PRAGMA foreign_key_check does not report any problem in any of
>> the cases.
>
>
>> I posted an identical question on StackOverflow, but no one was able to
>> provide any information so far. Thanks in advance!
>
>> Máté Szabó
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

szmate1618
In reply to this post by Dan Kennedy-4
>"PRAGMA foreign_keys = ?" is a property of the connection only, not the
>database file. So what advantage would there be in including the PRAGMA
>statements in the body of a transaction?

Not much, if I do everything right, I guess. But what if I break the
foreign key integrity?
If it's inside a transaction, I can rollback easily, given that I realize I
broke it.
But if I don't, because foreign key checks are turned off, and I commit
everything before noticing that
something's wrong, that's a lot harder to fix.

I can either take extra care not to break anything, or just do a backup of
the database file before
'risky' transactions, but it would be much easier if I could just use
deferred foreign keys that don't
let me commit if the database is in an inconsistent state.

Máté

Dan Kennedy <[hidden email]> ezt írta (időpont: 2018. nov. 23., P,
16:30):

> On 11/23/2018 09:54 PM, szmate1618 wrote:
> > Dear list members,
> >
> > I have the following problem, with which I'd like to request your aid:
> >
> > Currently, at version 3.25.2, SQLite only has a limited support for alter
> > table. E.g. you cannot change the datatype (type affinity) of a column,
> or
> > drop a column.
> >
> > The usual workaround is to create a new table with the desired schema,
> fill
> > it with data from the original table, drop the original table, and rename
> > the new one. But what if the original table is a parent table in a
> foreign
> > key relationship?
> >
> > The official solution
> > <https://www.sqlite.org/lang_altertable.html#otheralter> is turning
> foreign
> > keys off, making the changes you want, then turning foreign keys on. But
> > I'm slightly annoyed this cannot be done in a transaction (because these
> PRAGMA
> > foreign_keys =s don't take effect inside of transactions, so they need to
> > be issued before and after).
>
>
> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
> database file. So what advantage would there be in including the PRAGMA
> statements in the body of a transaction?
>
> Dan.
>
>
>
> >
> > I'd like to use deferred foreign keys instead. I have 3 queries, one of
> > them seems to work, the two others do not. My questions are the
> following:
> >
> >    - Does the seemingly working query work by design? Or it's just a
> >    fortunate(?) constellation of multiple factors, and depending on other
> >    tables or new data in the database it might break in the future?
> Somewhat
> >    like undefined behavior in C++?
> >    - Why do the other ones not work? How are they different from the
> first
> >    one?
> >
> > Setup
> >
> > PRAGMA foreign_keys = OFF;
> > DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
> > COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
> > DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
> > Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
> > VALUES('whatever');
> >
> > PRAGMA foreign_keys = ON;
> >
> > Query1 - seems to be working as intended
> >
> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> > Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
> > FROM Temp;DROP TABLE Temp;COMMIT;
> >
> > Query2 - create [...] as select [...] fails
> >
> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
> > Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
> > from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
> > from Query1DROP TABLE Temp;COMMIT;
> >
> > Result:
> >
> > sqlite> PRAGMA foreign_key_check;
> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
> > UNIQUE INDEX ParentIndex on Parent(A);
> > sqlite> SELECT * FROM Parent;
> > whatever
> >
> > Query3 - insert into [...] fails
> >
> > BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
> > from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
> > Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
> > INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
> >
> > Result:
> >
> > sqlite> PRAGMA foreign_key_check;
> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
> > sqlite> SELECT * FROM Parent;
> > whatever
> >
> > Note that PRAGMA foreign_key_check does not report any problem in any of
> > the cases.
> >
> >
> > I posted an identical question on StackOverflow, but no one was able to
> > provide any information so far. Thanks in advance!
> >
> > Máté Szabó
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

szmate1618
I'm afraid I still didn't make any progress on this.

It looks to me that PRAGMA foreign_key_check; and the deferred foreign key
mechanism sometimes contradict each other,
consequently at least one of these features must have a bug, but I don't
know for sure which one,
so I can't risk using any of them in production until the situation is
resolved.

Do you have any suggestion how I should go about it? Can I escalate this to
someone?

Thanks in advance,
Máté

szmate1618 <[hidden email]> ezt írta (időpont: 2018. nov. 23., P,
17:21):

> >"PRAGMA foreign_keys = ?" is a property of the connection only, not the
> >database file. So what advantage would there be in including the PRAGMA
> >statements in the body of a transaction?
>
> Not much, if I do everything right, I guess. But what if I break the
> foreign key integrity?
> If it's inside a transaction, I can rollback easily, given that I realize
> I broke it.
> But if I don't, because foreign key checks are turned off, and I commit
> everything before noticing that
> something's wrong, that's a lot harder to fix.
>
> I can either take extra care not to break anything, or just do a backup of
> the database file before
> 'risky' transactions, but it would be much easier if I could just use
> deferred foreign keys that don't
> let me commit if the database is in an inconsistent state.
>
> Máté
>
> Dan Kennedy <[hidden email]> ezt írta (időpont: 2018. nov. 23., P,
> 16:30):
>
>> On 11/23/2018 09:54 PM, szmate1618 wrote:
>> > Dear list members,
>> >
>> > I have the following problem, with which I'd like to request your aid:
>> >
>> > Currently, at version 3.25.2, SQLite only has a limited support for
>> alter
>> > table. E.g. you cannot change the datatype (type affinity) of a column,
>> or
>> > drop a column.
>> >
>> > The usual workaround is to create a new table with the desired schema,
>> fill
>> > it with data from the original table, drop the original table, and
>> rename
>> > the new one. But what if the original table is a parent table in a
>> foreign
>> > key relationship?
>> >
>> > The official solution
>> > <https://www.sqlite.org/lang_altertable.html#otheralter> is turning
>> foreign
>> > keys off, making the changes you want, then turning foreign keys on. But
>> > I'm slightly annoyed this cannot be done in a transaction (because
>> these PRAGMA
>> > foreign_keys =s don't take effect inside of transactions, so they need
>> to
>> > be issued before and after).
>>
>>
>> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
>> database file. So what advantage would there be in including the PRAGMA
>> statements in the body of a transaction?
>>
>> Dan.
>>
>>
>>
>> >
>> > I'd like to use deferred foreign keys instead. I have 3 queries, one of
>> > them seems to work, the two others do not. My questions are the
>> following:
>> >
>> >    - Does the seemingly working query work by design? Or it's just a
>> >    fortunate(?) constellation of multiple factors, and depending on
>> other
>> >    tables or new data in the database it might break in the future?
>> Somewhat
>> >    like undefined behavior in C++?
>> >    - Why do the other ones not work? How are they different from the
>> first
>> >    one?
>> >
>> > Setup
>> >
>> > PRAGMA foreign_keys = OFF;
>> > DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
>> > COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
>> > DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
>> > Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
>> > VALUES('whatever');
>> >
>> > PRAGMA foreign_keys = ON;
>> >
>> > Query1 - seems to be working as intended
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> > Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
>> > FROM Temp;DROP TABLE Temp;COMMIT;
>> >
>> > Query2 - create [...] as select [...] fails
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> > Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
>> > from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
>> > from Query1DROP TABLE Temp;COMMIT;
>> >
>> > Result:
>> >
>> > sqlite> PRAGMA foreign_key_check;
>> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
>> > UNIQUE INDEX ParentIndex on Parent(A);
>> > sqlite> SELECT * FROM Parent;
>> > whatever
>> >
>> > Query3 - insert into [...] fails
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
>> > from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
>> > Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
>> > INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
>> >
>> > Result:
>> >
>> > sqlite> PRAGMA foreign_key_check;
>> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
>> > sqlite> SELECT * FROM Parent;
>> > whatever
>> >
>> > Note that PRAGMA foreign_key_check does not report any problem in any of
>> > the cases.
>> >
>> >
>> > I posted an identical question on StackOverflow, but no one was able to
>> > provide any information so far. Thanks in advance!
>> >
>> > Máté Szabó
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

Dan Kennedy-4
On 11/29/2018 07:10 PM, szmate1618 wrote:

> I'm afraid I still didn't make any progress on this.
>
> It looks to me that PRAGMA foreign_key_check; and the deferred foreign key
> mechanism sometimes contradict each other,
> consequently at least one of these features must have a bug, but I don't
> know for sure which one,
> so I can't risk using any of them in production until the situation is
> resolved.
>
> Do you have any suggestion how I should go about it? Can I escalate this to
> someone?

If there is a bug, please do. I haven't actually understood the
explanation of it yet though.

Or what is wrong with:

   PRAGMA foreign_keys = 0;
   BEGIN;
     <do lots of stuff>
   if( [PRAGMA foreign_key_check]=="ok" ) COMMIT else ROLLBACK;
   PRAGMA foreign_keys = 1;


Dan.



>
> Thanks in advance,
> Máté
>
> szmate1618 <[hidden email]> ezt írta (időpont: 2018. nov. 23., P,
> 17:21):
>
>>> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
>>> database file. So what advantage would there be in including the PRAGMA
>>> statements in the body of a transaction?
>>
>> Not much, if I do everything right, I guess. But what if I break the
>> foreign key integrity?
>> If it's inside a transaction, I can rollback easily, given that I realize
>> I broke it.
>> But if I don't, because foreign key checks are turned off, and I commit
>> everything before noticing that
>> something's wrong, that's a lot harder to fix.
>>
>> I can either take extra care not to break anything, or just do a backup of
>> the database file before
>> 'risky' transactions, but it would be much easier if I could just use
>> deferred foreign keys that don't
>> let me commit if the database is in an inconsistent state.
>>
>> Máté
>>
>> Dan Kennedy <[hidden email]> ezt írta (időpont: 2018. nov. 23., P,
>> 16:30):
>>
>>> On 11/23/2018 09:54 PM, szmate1618 wrote:
>>>> Dear list members,
>>>>
>>>> I have the following problem, with which I'd like to request your aid:
>>>>
>>>> Currently, at version 3.25.2, SQLite only has a limited support for
>>> alter
>>>> table. E.g. you cannot change the datatype (type affinity) of a column,
>>> or
>>>> drop a column.
>>>>
>>>> The usual workaround is to create a new table with the desired schema,
>>> fill
>>>> it with data from the original table, drop the original table, and
>>> rename
>>>> the new one. But what if the original table is a parent table in a
>>> foreign
>>>> key relationship?
>>>>
>>>> The official solution
>>>> <https://www.sqlite.org/lang_altertable.html#otheralter> is turning
>>> foreign
>>>> keys off, making the changes you want, then turning foreign keys on. But
>>>> I'm slightly annoyed this cannot be done in a transaction (because
>>> these PRAGMA
>>>> foreign_keys =s don't take effect inside of transactions, so they need
>>> to
>>>> be issued before and after).
>>>
>>>
>>> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
>>> database file. So what advantage would there be in including the PRAGMA
>>> statements in the body of a transaction?
>>>
>>> Dan.
>>>
>>>
>>>
>>>>
>>>> I'd like to use deferred foreign keys instead. I have 3 queries, one of
>>>> them seems to work, the two others do not. My questions are the
>>> following:
>>>>
>>>>    - Does the seemingly working query work by design? Or it's just a
>>>>    fortunate(?) constellation of multiple factors, and depending on
>>> other
>>>>    tables or new data in the database it might break in the future?
>>> Somewhat
>>>>    like undefined behavior in C++?
>>>>    - Why do the other ones not work? How are they different from the
>>> first
>>>>    one?
>>>>
>>>> Setup
>>>>
>>>> PRAGMA foreign_keys = OFF;
>>>> DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
>>>> COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
>>>> DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
>>>> Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
>>>> VALUES('whatever');
>>>>
>>>> PRAGMA foreign_keys = ON;
>>>>
>>>> Query1 - seems to be working as intended
>>>>
>>>> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>>>> Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
>>>> FROM Temp;DROP TABLE Temp;COMMIT;
>>>>
>>>> Query2 - create [...] as select [...] fails
>>>>
>>>> BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>>>> Parent;CREATE TABLE Parent AS SELECT * FROM Temp;     -- different
>>>> from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
>>>> from Query1DROP TABLE Temp;COMMIT;
>>>>
>>>> Result:
>>>>
>>>> sqlite> PRAGMA foreign_key_check;
>>>> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>>>> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
>>>> UNIQUE INDEX ParentIndex on Parent(A);
>>>> sqlite> SELECT * FROM Parent;
>>>> whatever
>>>>
>>>> Query3 - insert into [...] fails
>>>>
>>>> BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);      -- different
>>>> from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
>>>> Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
>>>> INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
>>>>
>>>> Result:
>>>>
>>>> sqlite> PRAGMA foreign_key_check;
>>>> sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>>>> DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
>>>> sqlite> SELECT * FROM Parent;
>>>> whatever
>>>>
>>>> Note that PRAGMA foreign_key_check does not report any problem in any of
>>>> the cases.
>>>>
>>>>
>>>> I posted an identical question on StackOverflow, but no one was able to
>>>> provide any information so far. Thanks in advance!
>>>>
>>>> Máté Szabó
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users