Deferred foreign key constraint bug?

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

Deferred foreign key constraint bug?

Steve Campbell-6
A lingering statement handle can prevent a deferred foreign key constraint from being enforced.  Here is an example:

sqlite3_open( ":memory:", &pDatabase );
sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED, d);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );

// Does not violate constraint; will return SQLITE_OK
sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )

sqlite3_stmt* pStatement1;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1, &pStatement1, NULL);

// Does violate constraint
// Will return SQLITE_CONSTRAINT
sqlite3_step( pStatement1 );

sqlite3_stmt* pStatement2;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1, &pStatement2, NULL);

// Note that pStatement1 has not been finalized yet
// Does violate constraint
// Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; this is the malfunction
sqlite3_step( pStatement2 );

sqlite3_finalize( pStatement2 );
sqlite3_finalize( pStatement1 );
sqlite3_close( pDatabase );

The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead.

If you change the code to finalize statement1 first, then the last call to sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign key constraint to be immediate instead of deferred, the last call to sqlite3_step will return SQLITE_CONSTRAINT.

To summarize, we can prevent foreign keys from being properly enforced if we declare the constraints to be deferred and we leave lingering statement handles open.

Is this a bug?

Much appreciated,
Steve Campbell

---------------------------------------------------------------------
This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Deferred foreign key constraint bug?

jan-118
What happens with a transaction around your statements?

Am 21.01.2011 19:38, schrieb Steve Campbell:

> A lingering statement handle can prevent a deferred foreign key constraint from being enforced.  Here is an example:
>
> sqlite3_open( ":memory:",&pDatabase );
> sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED, d);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );
>
> // Does not violate constraint; will return SQLITE_OK
> sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )
>
> sqlite3_stmt* pStatement1;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1,&pStatement1, NULL);
>
> // Does violate constraint
> // Will return SQLITE_CONSTRAINT
> sqlite3_step( pStatement1 );
>
> sqlite3_stmt* pStatement2;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1,&pStatement2, NULL);
>
> // Note that pStatement1 has not been finalized yet
> // Does violate constraint
> // Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; this is the malfunction
> sqlite3_step( pStatement2 );
>
> sqlite3_finalize( pStatement2 );
> sqlite3_finalize( pStatement1 );
> sqlite3_close( pDatabase );
>
> The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead.
>
> If you change the code to finalize statement1 first, then the last call to sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign key constraint to be immediate instead of deferred, the last call to sqlite3_step will return SQLITE_CONSTRAINT.
>
> To summarize, we can prevent foreign keys from being properly enforced if we declare the constraints to be deferred and we leave lingering statement handles open.
>
> Is this a bug?
>
> Much appreciated,
> Steve Campbell
>
> ---------------------------------------------------------------------
> This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Deferred foreign key constraint bug?

Steve Campbell-6
In that case, we get the desired behavior.  Both calls to sqlite3_step return SQLITE_DONE because it is a deferred constraint.  When you execute the "COMMIT;", it then returns SQLITE_CONSTRAINT because of the constraint violation.

To summarize, to reproduce this problem, the statements must not be enclosed in a BEGIN-COMMIT block.

To be very explicit, here is the code that I used to test this:

sqlite3* pDatabase;
sqlite3_open( ":memory:", &pDatabase );

sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED, d);", NULL, NULL, NULL );
sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );

// Does not violate constraint
sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL );

sqlite3_exec( pDatabase, "BEGIN;", NULL, NULL, NULL );

sqlite3_stmt* pStatement1;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1, &pStatement1, NULL );

// Returns SQLITE_DONE because constraint is deferred
sqlite3_step( pStatement1 );

sqlite3_stmt* pStatement2;
sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1, &pStatement2, NULL);

// Returns SQLITE_DONE because constraint is deferred
sqlite3_step( pStatement2 );

// Returns SQLITE_CONSTRAINT because both statements violate the constraint
sqlite3_exec( pDatabase, "COMMIT;", NULL, NULL, NULL );

sqlite3_finalize( pStatement2 );
sqlite3_finalize( pStatement1 );
sqlite3_close( pDatabase );

Steve


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Jan
Sent: Friday, January 21, 2011 2:39 PM
To: [hidden email]
Subject: Re: [sqlite] Deferred foreign key constraint bug?

What happens with a transaction around your statements?

Am 21.01.2011 19:38, schrieb Steve Campbell:

> A lingering statement handle can prevent a deferred foreign key constraint from being enforced.  Here is an example:
>
> sqlite3_open( ":memory:",&pDatabase );
> sqlite3_exec( pDatabase, "PRAGMA foreign_keys =on;", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t1(a PRIMARY KEY, b);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "CREATE TABLE t2(c REFERENCES t1(a) DEFERRABLE INITIALLY DEFERRED, d);", NULL, NULL, NULL );
> sqlite3_exec( pDatabase, "INSERT INTO t1 VALUES(1, 2);", NULL, NULL, NULL );
>
> // Does not violate constraint; will return SQLITE_OK
> sqlite3_exec( pDatabase, "INSERT INTO t2 VALUES(1, 3);", NULL, NULL, NULL )
>
> sqlite3_stmt* pStatement1;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(2, 4);", -1,&pStatement1, NULL);
>
> // Does violate constraint
> // Will return SQLITE_CONSTRAINT
> sqlite3_step( pStatement1 );
>
> sqlite3_stmt* pStatement2;
> sqlite3_prepare_v2( pDatabase, "INSERT INTO t2 VALUES(3, 6);", -1,&pStatement2, NULL);
>
> // Note that pStatement1 has not been finalized yet
> // Does violate constraint
> // Should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead; this is the malfunction
> sqlite3_step( pStatement2 );
>
> sqlite3_finalize( pStatement2 );
> sqlite3_finalize( pStatement1 );
> sqlite3_close( pDatabase );
>
> The last call to sqlite3_step should return SQLITE_CONSTRAINT, but it will return SQLITE_OK instead.
>
> If you change the code to finalize statement1 first, then the last call to sqlite3_step will return SQLITE_CONSTRAINT.  Also, if you declare the foreign key constraint to be immediate instead of deferred, the last call to sqlite3_step will return SQLITE_CONSTRAINT.
>
> To summarize, we can prevent foreign keys from being properly enforced if we declare the constraints to be deferred and we leave lingering statement handles open.
>
> Is this a bug?
>
> Much appreciated,
> Steve Campbell
>
> ---------------------------------------------------------------------
> This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

---------------------------------------------------------------------
This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Deferred foreign key constraint bug?

jan-118
Am 21.01.2011 21:36, schrieb Steve Campbell:
> To summarize, to reproduce this problem, the statements must not be enclosed in a BEGIN-COMMIT block.

But DEFERRABLE is all about transactions, isn't it? As far as I
understand this feature...

I use it a lot, because then I have no headache importing data (order
does not matter any more).

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

Re: Deferred foreign key constraint bug?

Steve Campbell-6
Yes, that's right.  Deferrable is all about transactions.

A lone-statement that is not explicitly enclosed in a BEGIN-COMMIT block is itself an implicit transaction.  Deferred foreign key constraints get enforced at the COMMIT and the COMMIT is implicitly executed with a lone-statement, so the constraints should be enforced.  This is what happens unless you reproduce the specific circumstances that I described.

Steve

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Jan
Sent: Saturday, January 22, 2011 9:48 AM
To: [hidden email]
Subject: Re: [sqlite] Deferred foreign key constraint bug?

Am 21.01.2011 21:36, schrieb Steve Campbell:
> To summarize, to reproduce this problem, the statements must not be enclosed in a BEGIN-COMMIT block.

But DEFERRABLE is all about transactions, isn't it? As far as I
understand this feature...

I use it a lot, because then I have no headache importing data (order
does not matter any more).

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

---------------------------------------------------------------------
This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Deferred foreign key constraint bug?

Richard Hipp-3
In reply to this post by Steve Campbell-6
On Fri, Jan 21, 2011 at 1:38 PM, Steve Campbell <[hidden email]> wrote:

>
> To summarize, we can prevent foreign keys from being properly enforced if
> we declare the constraints to be deferred and we leave lingering statement
> handles open.
>
> Is this a bug?
>
>
We cannot check FK constraints until all statement handles that might
potentially change those constraints have finished running.   Otherwise, the
FK checks might get the wrong answer because they are not taking into
account changes that haven't yet been made.

I think you are surprised here because of confusion about when a statement
"has finished running".  Sometimes a statement "finishes running" (for the
purpose of FK constraint enforcement) on the final sqlite3_step() call that
returns SQLITE_DONE.  Other times, it finishes when sqlite3_finalize() or
sqlite3_reset() is called.  SQLite makes no guarantees about when the
statement will finish other than that it will be finished by the time
sqlite3_finalize() or sqlite3_reset() returns.  It might finish before then,
or it might not.

Perhaps it would be less surprising to developers if a statement always
finished on the sqlite3_step() call that returned SQLITE_DONE.  That is a
reasonable request and we will look into enhancing SQLite to do that.  For
version 3.7.6.  The thing is, changing this involves making subtle changes
to the core that carry a high risk of breaking other things.  So we are not
willing to undertake such a change so late in the release cycle.

If you are diligent to always finalize or reset your statements when you
finish with them, you should not have any trouble.  Meanwhile, we will work
to make SQLite robust and consistent in the face of less pedantic programs
in the future.

I consider this an enhancement request, not a bug.

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

Re: Deferred foreign key constraint bug?

Steve Campbell-6
That makes sense.  Thanks for explaining.

Steve

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Monday, January 24, 2011 9:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Deferred foreign key constraint bug?

On Fri, Jan 21, 2011 at 1:38 PM, Steve Campbell <[hidden email]> wrote:

>
> To summarize, we can prevent foreign keys from being properly enforced if
> we declare the constraints to be deferred and we leave lingering statement
> handles open.
>
> Is this a bug?
>
>
We cannot check FK constraints until all statement handles that might
potentially change those constraints have finished running.   Otherwise, the
FK checks might get the wrong answer because they are not taking into
account changes that haven't yet been made.

I think you are surprised here because of confusion about when a statement
"has finished running".  Sometimes a statement "finishes running" (for the
purpose of FK constraint enforcement) on the final sqlite3_step() call that
returns SQLITE_DONE.  Other times, it finishes when sqlite3_finalize() or
sqlite3_reset() is called.  SQLite makes no guarantees about when the
statement will finish other than that it will be finished by the time
sqlite3_finalize() or sqlite3_reset() returns.  It might finish before then,
or it might not.

Perhaps it would be less surprising to developers if a statement always
finished on the sqlite3_step() call that returned SQLITE_DONE.  That is a
reasonable request and we will look into enhancing SQLite to do that.  For
version 3.7.6.  The thing is, changing this involves making subtle changes
to the core that carry a high risk of breaking other things.  So we are not
willing to undertake such a change so late in the release cycle.

If you are diligent to always finalize or reset your statements when you
finish with them, you should not have any trouble.  Meanwhile, we will work
to make SQLite robust and consistent in the face of less pedantic programs
in the future.

I consider this an enhancement request, not a bug.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

---------------------------------------------------------------------
This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users