[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Simon Slavin]

On 1 Dec 2015, at 12:55am, Yuri  wrote:

> On 11/23/2015 07:55, James K. Lowden wrote:
>> As a matter of fact, violation of UNIQUE & PK constraints is enforced
>> rather too strictly in SQLite.  Any UPDATE statement that modifie more
>> than one row of a UNIQUE column can fail because two rows may
>> *transiently*  have the same value.  SQLite will fail the statement
>> even though the completed transactation leaves the constraint
>> unviolated.
> 
> It seems like it performs the checking on the level of individual field 
> write, not SQL statements. I suggested earlier to have "CONSTRAINT LEVEL 
> [WRITE|STATEMENT|TRANSACTION];"
> It seems they currently work on the level of field writes and transactions, 
> and not on SQL statement level.

These (largely overlapping) pages




seem to describe UNIQUE, NOT NULL, and CHECK constraints being checked at the 
statement level.  Which is the behaviour you're seeing.

However this is not the behaviour I've seen described for SQLite in the past.  
I'm sure I've read somewhere that consistency checks are checked at COMMIT 
time.  Maybe that's just for the default FOREIGN KEY checks mentioned upthread:



So SQLite is not consistent.  It checks some things at the statement level and 
others at the transaction level.  Which is probably not good.

Simon.

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

On 11/23/2015 07:55, James K. Lowden wrote:
> As a matter of fact, violation of UNIQUE & PK constraints is enforced
> rather too strictly in SQLite.  Any UPDATE statement that modifie more
> than one row of a UNIQUE column can fail because two rows may
> *transiently*  have the same value.  SQLite will fail the statement
> even though the completed transactation leaves the constraint
> unviolated.

It seems like it performs the checking on the level of individual field 
write, not SQL statements. I suggested earlier to have "CONSTRAINT LEVEL 
[WRITE|STATEMENT|TRANSACTION];"
It seems they currently work on the level of field writes and 
transactions, and not on SQL statement level.

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[James K. Lowden]

On Tue, 17 Nov 2015 22:29:10 -0500
Richard Hipp  wrote:

> With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the
> constraint has failed right away.  And for those constraints, SQLite
> does provide more detail about exactly which constraint failed.  But
> for FK constraints, you never know if a constraint that is failing
> right now might be resolved before the end of the transaction.

As a matter of fact, violation of UNIQUE & PK constraints is enforced
rather too strictly in SQLite.  Any UPDATE statement that modifie more
than one row of a UNIQUE column can fail because two rows may
*transiently* have the same value.  SQLite will fail the statement
even though the completed transactation leaves the constraint
unviolated.  

sqlite> create table T(t int not null primary key);
sqlite> insert into T values (1), (2);
sqlite> update T set t = t+1;
Error: UNIQUE constraint failed: T.t

--jkl

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[R Smith]

On 2015/11/18 7:23 PM, Duquette, William H (393K) wrote:
> On 11/17/15, 3:35 PM, "sqlite-users-bounces at mailinglists.sqlite.org on
> behalf of Rolf Ade"  behalf of rolf at pointsman.de> wrote:
>
>
>> Richard Hipp  writes:
>>> On 11/17/15, Yuri  wrote:
 This message always leaves the user wondering: "Which constraint?"

 How hard is it to add this information to the message? Is this a matter
 of memorizing the ID of the constraint, and then printing its name in
 the message?

>>> ...
>>> To provide information about which constraint(s) failed, it would be
>>> necessary to have a bag (a list or hash table or an associative array)
>>> of all the constraints that have been violated and then remove
>>> elements from the bag as constraints are resolved.
> What if it saved just the first constraint to fail?  That would allow the
> problem to be fixed one constraint at a time.  Or is that still hard to do
> given the current implementation?

There is no "first" constraint that can fail. There is a procession of 
constraints either within a statement or within a transaction (both can 
have many constraints) and as they are required, they are counted up, 
and as they become resolved they are counted down. At the end the result 
is simply either that "some constraint failed" or "All resolved eventually".

When we end up with a remainder in this checks-count (indicating that 
one or more did not resolve), there is no way to know which one of the 
initial requirements did not come back to indicate itself as resolved - 
we only know one or more of them didn't. To know which one it was, new 
code has to be added to keep track of all the constraints that might be 
required to resolve and which resolved so we can eliminate them from the 
list as they resolve.

It is this tracking list requirement that is no longer "Lite" in both 
code and execution cost / resources.

I hope that makes some sense!
Ryan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

On 11/18/2015 15:33, Scott Hess wrote:
> What do you plan to use the error message for?  There is generally no
> interface contract with error messages, so you can't generally depend on
> them being readable by code.  They are helpful to the developer, but
> usually they just save you a few minutes figuring it out yourself.  I'm not
> hating on your few minutes, but saving a few milliseconds or bytes at
> runtime on a few million devices is worth a periodic few minutes of my
> time.  You can use SQL to generate pretty complicated schema and queries,
> so in the limit this kind of problem can be pretty deep to resolve!

I have a process that imports some data with quite complex structure. 
Every once in a while the constraint violation occurs (usually due to 
the data irregularities). Even this simple matter can be complicated 
when you are busy with 100s other things, and this DB schema isn't on 
top of your mind. My concern is mostly development time and to minimize 
the number of things to remember. So if the message can be more 
informative, I always vote for this.

> Maybe it would make sense to have some sort of
> SQLITE_WITH_EXTENDED_FK_ERRORS type define so that you can get a better
> edit/compile/run loop going in development, without being expensive in
> production.

Yes, such option can very nicely become a package option (on BSD).

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Duquette, William H (393K)]

On 11/17/15, 3:35 PM, "sqlite-users-bounces at mailinglists.sqlite.org on
behalf of Rolf Ade"  wrote:


>
>Richard Hipp  writes:
>> On 11/17/15, Yuri  wrote:
>>> This message always leaves the user wondering: "Which constraint?"
>>>
>>> How hard is it to add this information to the message? Is this a matter
>>> of memorizing the ID of the constraint, and then printing its name in
>>> the message?
>>>
>>
>>...
>> To provide information about which constraint(s) failed, it would be
>> necessary to have a bag (a list or hash table or an associative array)
>> of all the constraints that have been violated and then remove
>> elements from the bag as constraints are resolved.

What if it saved just the first constraint to fail?  That would allow the
problem to be fixed one constraint at a time.  Or is that still hard to do
given the current implementation?

Will

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Scott Robison]

On Wed, Nov 18, 2015 at 3:20 AM, Keith Medcalf  wrote:

> A little off topic, this reminds me of a feature of the PL/1 F compiler.
> The PL/1 compiler was a huge monstrous beast that had both syntactical and
> semantic analyzer paths so it could "correct" a number of common
> programming errors, faulty assumptions, and misspelling of keywords if the
> programmer were too lazy to formulate the problem and the source code
> correctly.
>
> Because code was painstaking (slowly) entered onto decks of 80-column
> hollerith punch cards that were submitted for execution overnight (or, in
> many cases over-fortnight), this "feature" made it possible for many
> "sloppy" errors to be automatically corrected thus avoiding the fortnightly
> correct-submit-result-edit-repeat process.
>

I realize you're saying "feature" and "sloppy" with quotes, which probably
means that you don't actually believe that, but I would like to chime in.
Languages like FORTRAN had implicit variable definition rules, and they
were a real feature of the language. When you are keypunching a stack of
cards, every card cost money. Implicitly defining the type of variables
that start with a given letter saves you the expense of the card that
declared one or more variables. It reduces the time it takes to read the
stack of cards into the computer. Given that the machines of the time were
very memory constrained by modern standards, this was all very valuable. As
for PL/1 F, from Wikipedia:

PL/I was first implemented by IBM, at its Hursley Laboratories
 in the United Kingdom,
as part of the development of System/360
. The first production PL/I
compiler  was the PL/I F compiler
for the OS/360  Operating System,
built by John Nash's team at Hursley in the UK: the runtime library team
was managed by I.M. (Nobby) Clarke. The PL/I F compiler was written
entirely in System/360 assembly language.[18]
 Release 1 shipped
in 1966. *OS/360 was a real-memory environment and the compiler was
designed for systems with as little as 64 kilobytes of real storage ? F
being 64 kB in S/360 parlance. To fit a large compiler into the 44
kilobytes of memory available on a 64-kilobyte machine, the compiler
consisted of a control phase and a large number of compiler phases
(approaching 100). The phases were brought into memory from disk, and
released, one at a time to handle particular language features and aspects
of compilation.*

So every card saved reduced the price of consumables for the program,
reduced the time it took to read a stack of cards into memory, probably
reduced the amount of data written out to temporary magnetic storage
between phases, which of course reduced the amount of data read back in
from temporary magnetic storage. Given that the cost of computer time by
some estimates was about $10,000/hour (almost $2.78/second!) this was all a
very real savings to the owner / operator of the computer.

Forgetting for a moment about the "lazy programmer" who took a short cut to
save some keypunching, 23 years ago Steve McConnell wrote Code Complete and
claimed an average of 15 to 50 bugs per KLOC. Regardless of how many bugs
there are on average, there is some non-zero number that is the real
average probability of a bug in a line of code, so reducing the number of
lines in theory reduces the number of bugs, and reduces the probability of
wasting a computer session and waiting a fortnight for another shot at it!

I never had to work with punched cards. Missed it by a few years, even in
college. I consider myself fortunate to work in a world with lots of RAM
and huge sophisticated compilers that can do lots of things quickly in a
"single pass" of the compiler. I like being able to do "RAD" style
programming, though to me that means I can spend my time typing text into a
computer and let the compiler tell me I made a typo. :)

Which brings us back around to SQLite: SQLite isn't always running in that
heavy duty environment with gigs of ram. It works on memory constrained
devices with perhaps less than a megabyte of ram.

Anyway, I agree it would be nice to have more detailed error messages
available when FK conflicts arise, but I can appreciate why the overhead is
not desirable, and that past architectural decisions have made it a
difficult feature to add at this time.


> For example, in "C" you may get an error that some variable is not
> declared and the compilation halts.  In PL/1, the compiler assumes that you
> are simply a forgetful and sloppy programmer and "creates" the necessary
> declaration for you.  This allows the compilation and execution to
> continue.  If the assumption the compiler made turns out to be correct,
> this saved you two weeks of time.  If the compiler was in error, it cost
> you nothing -- you still have to 

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Scott Hess]

On Wed, Nov 18, 2015 at 3:22 PM, Yuri  wrote:

> On 11/18/2015 09:55, R Smith wrote:
>
>> There is no "first" constraint that can fail. There is a procession of
>> constraints either within a statement or within a transaction (both can
>> have many constraints) and as they are required, they are counted up, and
>> as they become resolved they are counted down. At the end the result is
>> simply either that "some constraint failed" or "All resolved eventually".
>>
>
> Why not have two variants of the error message: one for immediate failure
> with the foreign key name, and one like now, for the complicated case of
> delayed constraints?


What do you plan to use the error message for?  There is generally no
interface contract with error messages, so you can't generally depend on
them being readable by code.  They are helpful to the developer, but
usually they just save you a few minutes figuring it out yourself.  I'm not
hating on your few minutes, but saving a few milliseconds or bytes at
runtime on a few million devices is worth a periodic few minutes of my
time.  You can use SQL to generate pretty complicated schema and queries,
so in the limit this kind of problem can be pretty deep to resolve!

Maybe it would make sense to have some sort of
SQLITE_WITH_EXTENDED_FK_ERRORS type define so that you can get a better
edit/compile/run loop going in development, without being expensive in
production.

-scott

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

On 11/18/2015 09:55, R Smith wrote:
> There is no "first" constraint that can fail. There is a procession of 
> constraints either within a statement or within a transaction (both 
> can have many constraints) and as they are required, they are counted 
> up, and as they become resolved they are counted down. At the end the 
> result is simply either that "some constraint failed" or "All resolved 
> eventually".

Why not have two variants of the error message: one for immediate 
failure with the foreign key name, and one like now, for the complicated 
case of delayed constraints?

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Dominique Devienne]

On Wed, Nov 18, 2015 at 10:58 AM, Darren Duncan 
wrote:

> On 2015-11-18 1:27 AM, Yuri wrote:
>
>> On 11/18/2015 01:17, Darren Duncan wrote:
>>>
>>> Deferred constraints are definitely a benefit.
>>>
>> I agree they can be beneficial, but not in all cases. Depends on what you
>> do. It
>> would have been great if it was an option, ex. "CONSTRAINT LEVEL
>> [STATEMENT|TRANSACTION];".
>>
>
> You can declare that behavior individually per foreign key constraint, and
> you can also change it at runtime with
> https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys if that's
> what you were looking for. -- Darren Duncan


[DD] Oh, I didn't realize that pragma existed! Thanks for that. Didn't even
realize SQLite supported deferred per FK, I thought it was always for all
FKs in general. Given this pragma, then showing the FK name on immediate
mode is possible, no? --DD

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Dominique Devienne]

On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan 
wrote:

> On 2015-11-18 12:50 AM, Yuri wrote:
>
>> On 11/18/2015 00:45, Dominique Devienne wrote:
>>
>>> True. But that's in the case where FK constraints validation is deferred
>>> to
>>> the transaction end.
>>>
>>
>> Why does SQLite defer constraint violation errors? Is there a benefit?
>> This only complicates things, I would rather see the statement fail
>> immediately.
>>
>
> Deferred constraints are definitely a benefit.
>

[DD] In some cases, definitely. But in all cases, that's hardly true IMHO.


> They allow you to express constraints otherwise not possible, for example
> that a record may exist in table X if and only if a counterpart exists in
> table Y, such as balancing records in a double-entry accounting system.
> Granted all you really need for this is the ability to change multiple
> tables as a single atomic operation, but failing that ability, deferred
> constraints are the way SQL provides to do it.
>

[DD] Once again, those cases are more the exception than the norm. Deferred
constraints are only "required" when faced with circular FKs, since the
work-around of using an appropriate order for your statements works for all
other cases.


> Also, having to enter records in a specific order, eg parent/child, is a
> contrivance given you're dealing with what should be a set-oriented
> database, and a contrivance that can make using the database more
> difficult.  Ideally you just insert all the records in a change set at
> once, and only the total effect of the change set is what is important for
> enforcing constraints.  SQL immediate constraints break this ease of use.


[DD] That's ease of use you pay dearly for in terms of usability though.
And that order-agnostic benefit you claim is IMHO "artificial" and more a
"mathematical" concept than a physical reality. As an analogy, it's a bit
like asking to create a file first in a non-existent directory, and later
create that missing directly. The real world often requires to do things in
a specific order, and schemas do model the real world most times, so being
order dependent seems entirely "natural" to me. My $0.02. --DD

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Simon Slavin]

On 18 Nov 2015, at 10:05am, Dominique Devienne  wrote:

> Given this pragma, then showing the FK name on immediate
> mode is possible, no?

Unfortunately not.  [following explanation is, I suspect, simplified]

When the table schema is read from the file the table's constraints are 
'compiled' into a set of rules.  A single rule might express the requirements 
of many different constraints.  Rules are more compact, faster and simpler to 
apply than analyzing and applying each constraint for each value changed.  
Unfortunately because the structure of the rules doesn't map 1-to-1 into the 
constraints they came from it's not possible to tell, just from 'rule fails', 
which of the constraints that made up the rule was broken.

Applying the original constraints one by one would make SQLite far slower even 
for operations which did not violate any constraints, and the majority of 
users, who don't violate constraints, would get no benefit from it.

An earlier poster came up with a good solution: the existing 'fast check' is 
done first and then, if and only if a rule fails, a more complicated slower 
check has to be done to figure out which constraint to report as causing the 
error.  This would complicate SQLite's code, and as before most users wouldn't 
benefit from it.

As mentioned above SQLite4 is written differently and it's possible that 
reporting the violated constraint might be 'cheaper' to add to that.

Simon.

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Dominique Devienne]

On Wed, Nov 18, 2015 at 4:29 AM, Richard Hipp  wrote:

> On 11/17/15, Yuri  wrote:
> > Don't keep the bag, keep only one integer ID of the first failed
> > constraint.
>
> Therein lays the rub: there is no way to tell which (if any) FK
> constraint has failed until you have run the operation to the end and
> checked them all.  Remember, an FK constraint is allowed to be
> violated during a transaction, as long as the violation is resolved
> before the end of the transaction.
>

True. But that's in the case where FK constraints validation is deferred to
the transaction end.

In Oracle for example, all FKs are validated immediately at the statement
level by default,
and *can* be deferred, typically to deal with circular references. It does
force you to insert/update
in parent-child order, and delete in child-parent order (unless using ON
DELETE CASCADE),
but that's a good practice anyway IMHO, and does make troubleshooting on
errors so much easier.

Of course SQLite can't change its default behavior to "immediate"
validation, but for clients
willing to order their DMLs appropriately, a new pragma to fail on the
first validation error using
Yuri's idea, with thus the FK name (and even which parent/child
tables/columns involved perhaps)
would be a welcome addition, and hopefully be both easy enough to code, and
cheap enough
to not incur any slowdown. "Le beurre et l'argent du beurre" [1] quoi. --DD

[1] https://en.wiktionary.org/wiki/le_beurre_et_l%27argent_du_beurre

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Keith Medcalf]

Examining the VDBE code generated from some simulated operations explains how 
it works with stark clarity.

There are two constraint violation counters.  One has a "statement" scope (for 
immediate constraints), and the other a "transaction" scope (for deferred 
constraints).

Basically when the statement is executed, the appropriate violation counter is 
incremented for each violation -- even if there is a program in place that 
would "fix" the error (such as an ON ... CASCADE, etc) or even a user-defined 
trigger.  Then when the program/triggers is actually run successfully the 
appropriate violation counter is decremented.  Clearly you have to do it this 
way because this program could in turn fire a trigger (for example -- since it 
may update other tables) which itself might end up having an unresolvable 
constraint violation or otherwise fail to "resolve" the violation.

If the value of the immediate violation counter is non-zero at the end of the 
statement, then a constraint violation error is thrown.  There is no way to 
tell which of the constraints caused the error (it could be a constraint on a 
table not mentioned in the query that was updated as the result of a ON ... 
CASCADE  or a user-provided trigger or a program or trigger on any of them 
recursively).

For deferred constraints the processing is the same, just using a different 
counter that persists across statements within a transaction.  Statements which 
"clear" a pre-existing deferred constraint violation, decrement the deferred 
violation count.  At commit, a non-zero deferred violation count throws the 
constraint violation error.

Constraints such as CHECK, NOT NULL and UNIQUE cannot be "fixed" after they 
happen by a triggered update of any kind within the same statement -- nor can 
they be deferred -- and therefore they can be reported and attributed as soon 
as the violation is detected.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Wednesday, 18 November, 2015 03:44
> To: SQLite mailing list
> Subject: Re: [sqlite] How hard is it to add the constraint name to the
> 'FOREIGN KEY constraint failed' message?
> 
> 
> On 18 Nov 2015, at 10:05am, Dominique Devienne 
> wrote:
> 
> > Given this pragma, then showing the FK name on immediate
> > mode is possible, no?
> 
> Unfortunately not.  [following explanation is, I suspect, simplified]
> 
> When the table schema is read from the file the table's constraints are
> 'compiled' into a set of rules.  A single rule might express the
> requirements of many different constraints.  Rules are more compact,
> faster and simpler to apply than analyzing and applying each constraint
> for each value changed.  Unfortunately because the structure of the rules
> doesn't map 1-to-1 into the constraints they came from it's not possible
> to tell, just from 'rule fails', which of the constraints that made up the
> rule was broken.
> 
> Applying the original constraints one by one would make SQLite far slower
> even for operations which did not violate any constraints, and the
> majority of users, who don't violate constraints, would get no benefit
> from it.
> 
> An earlier poster came up with a good solution: the existing 'fast check'
> is done first and then, if and only if a rule fails, a more complicated
> slower check has to be done to figure out which constraint to report as
> causing the error.  This would complicate SQLite's code, and as before
> most users wouldn't benefit from it.
> 
> As mentioned above SQLite4 is written differently and it's possible that
> reporting the violated constraint might be 'cheaper' to add to that.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Keith Medcalf]

On Tuesday, 17 November, 2015 20:29, Richard Hipp said:
> On 11/17/15, Yuri  wrote:

> > Don't keep the bag, keep only one integer ID of the first failed
> > constraint.

> Therein lays the rub: there is no way to tell which (if any) FK
> constraint has failed until you have run the operation to the end and
> checked them all.  Remember, an FK constraint is allowed to be
> violated during a transaction, as long as the violation is resolved
> before the end of the transaction.

Richard, I don't think that is entirely true.

When a constraint is set to IMMEDIATE (the default) then each interim operation 
(statement) must leave the database in a consistent state at the end of the 
statement, even within a transaction.  Only a DEFERRABLE INITIALLY DEFERRED 
constraint can leave the database in an inconsistent state between operations 
(statements) until commit time (or if pragma defer_foreign_keys=on which turns 
all foreign key constraints into deferred constraints).

In the case of deferred constraints producing an actionable constraint 
violation message that is meaningful would be difficult.  Even if one were to 
produce a message that constraint x was violated, you would have to know which 
one of the myriad of transformations (statements) in the transaction led to the 
condition.  This would probably require the use of an embedded database engine 
of some type to track.

> With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the
> constraint has failed right away.  And for those constraints, SQLite
> does provide more detail about exactly which constraint failed.  But
> for FK constraints, you never know if a constraint that is failing
> right now might be resolved before the end of the transaction.

However, for immediate constraints which must be satisfied at the end of each 
statement within a transaction (before commit), would it not be possible to pop 
out which constraint failed since it must (obviously) be the one that is being 
checked at the moment, for that exact statement, much the same as for CHECK, 
UNIQUE and NOT NULL, which also must be satisfied for each statement?

If there is an unsatisfied deferred constraint at commit time, then the 
designer of the database schema ought to be able to analyze the issue since 
this is why they chose to use deferred constraints in the first place.  That 
they are using deferred constraints ineffectually is an error in programmer 
analysis, design, and understanding.  While it is the job of the database to 
prevent such inconsistent states from being committed to the database, doing 
"heavy" analysis of what is (obviously) inherently ill-conceived design of the 
schema and the transaction processing is not within the scope of a "lite" 
database.  

However, indicating an immediate constraint violation with attribution for a 
non-deferred constraint is much smaller problem that can only exist within the 
VDBE code execution for the single statement.

Perhaps something like an attributed immediate constraint violation message for 
immediate constraints, but for constraint violation in deferred constraints a 
simple "deferred constraint violated" should be sufficient (although it should 
be obvious that a constraint violation detected at COMMIT time must only be a 
deferred constraint and cannot arise from an immediate constraint).

A little off topic, this reminds me of a feature of the PL/1 F compiler.  The 
PL/1 compiler was a huge monstrous beast that had both syntactical and semantic 
analyzer paths so it could "correct" a number of common programming errors, 
faulty assumptions, and misspelling of keywords if the programmer were too lazy 
to formulate the problem and the source code correctly.  

Because code was painstaking (slowly) entered onto decks of 80-column hollerith 
punch cards that were submitted for execution overnight (or, in many cases 
over-fortnight), this "feature" made it possible for many "sloppy" errors to be 
automatically corrected thus avoiding the fortnightly 
correct-submit-result-edit-repeat process.

For example, in "C" you may get an error that some variable is not declared and 
the compilation halts.  In PL/1, the compiler assumes that you are simply a 
forgetful and sloppy programmer and "creates" the necessary declaration for 
you.  This allows the compilation and execution to continue.  If the assumption 
the compiler made turns out to be correct, this saved you two weeks of time.  
If the compiler was in error, it cost you nothing -- you still have to correct 
the program source and do another fortnight turn-around.  Of course, the 
compiler also had an option to "punch" a new card deck of the "corrected" and 
"properly formatted" source.

The long and the short of this is that it encouraged programmers to write 
sloppy ill-formatted and error-filled code and let the compiler fix it up.  If 
it did so correctly, then you ended up with a working program in a much shorter 
period of time and a 

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

On 2015-11-18 2:05 AM, Dominique Devienne wrote:
> On Wed, Nov 18, 2015 at 10:58 AM, Darren Duncan 
> wrote:
>
>> On 2015-11-18 1:27 AM, Yuri wrote:
>>> I agree they can be beneficial, but not in all cases. Depends on what you
>>> do. It
>>> would have been great if it was an option, ex. "CONSTRAINT LEVEL
>>> [STATEMENT|TRANSACTION];".
>>
>> You can declare that behavior individually per foreign key constraint, and
>> you can also change it at runtime with
>> https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys if that's
>> what you were looking for. -- Darren Duncan
>
> [DD] Oh, I didn't realize that pragma existed! Thanks for that. Didn't even
> realize SQLite supported deferred per FK, I thought it was always for all
> FKs in general. Given this pragma, then showing the FK name on immediate
> mode is possible, no? --DD

Well what does https://www.sqlite.org/foreignkeys.html tell you? -- Darren 
Duncan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

Replying to myself...

Often there is a single command available where one can say create a file on 
this particular filesystem path, and the system will go and create any 
nonexistent directories it needs to on the way.  So from the user's point of 
view creating the multiple things is a single operation, which is my point.

-- Darren Duncan

On 2015-11-18 2:06 AM, Darren Duncan wrote:
> On 2015-11-18 1:58 AM, Dominique Devienne wrote:
>> On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan 
>> wrote:
>>
>>> Deferred constraints are definitely a benefit.
>>
>>> They allow you to express constraints otherwise not possible, for example
>>> that a record may exist in table X if and only if a counterpart exists in
>>> table Y, such as balancing records in a double-entry accounting system.
>>> Granted all you really need for this is the ability to change multiple
>>> tables as a single atomic operation, but failing that ability, deferred
>>> constraints are the way SQL provides to do it.
>>
>> [DD] Once again, those cases are more the exception than the norm. Deferred
>> constraints are only "required" when faced with circular FKs, since the
>> work-around of using an appropriate order for your statements works for all
>> other cases.
>
> I think its more considered an exception because it is typically hard to do so
> people don't try.  But if arbitrary database constraints were easy, people 
> would
> probably be used to them and make more use, so less exceptional.
>
>>> Also, having to enter records in a specific order, eg parent/child, is a
>>> contrivance given you're dealing with what should be a set-oriented
>>> database, and a contrivance that can make using the database more
>>> difficult.  Ideally you just insert all the records in a change set at
>>> once, and only the total effect of the change set is what is important for
>>> enforcing constraints.  SQL immediate constraints break this ease of use.
>>
>> [DD] That's ease of use you pay dearly for in terms of usability though.
>> And that order-agnostic benefit you claim is IMHO "artificial" and more a
>> "mathematical" concept than a physical reality. As an analogy, it's a bit
>> like asking to create a file first in a non-existent directory, and later
>> create that missing directly. The real world often requires to do things in
>> a specific order, and schemas do model the real world most times, so being
>> order dependent seems entirely "natural" to me. My $0.02. --DD
>
> While I agree that in the real world some things need to be in order, that 
> isn't
> as true in the computer.  Its all a matter of abstraction.
>
> In your analogy, one wants to create a file AND a directory; they should just 
> be
> able to tell the system they want to create those 2 items as a single change,
> and have it happen, without having to worry about order.
>
> But more importantly, a relational database is different than a file system, 
> and
> what I propose is more appropriate there.  I'm talking about putting records 
> in
> several tables at once using a single SQL statement, which is like creating a
> set of files only and not also directories, apples and oranges.
>
> That being said, your file/directory analogy is more like creating a table and
> populating it in one statement, which incidentally can be done with a CREATE
> TABLE AS SELECT statement, though combining actions on other tables into the
> same statement isn't provided by SQL.
>
> -- Darren Duncan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

On 2015-11-18 1:58 AM, Dominique Devienne wrote:
> On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan 
> wrote:
>
>> Deferred constraints are definitely a benefit.
>
>> They allow you to express constraints otherwise not possible, for example
>> that a record may exist in table X if and only if a counterpart exists in
>> table Y, such as balancing records in a double-entry accounting system.
>> Granted all you really need for this is the ability to change multiple
>> tables as a single atomic operation, but failing that ability, deferred
>> constraints are the way SQL provides to do it.
>
> [DD] Once again, those cases are more the exception than the norm. Deferred
> constraints are only "required" when faced with circular FKs, since the
> work-around of using an appropriate order for your statements works for all
> other cases.

I think its more considered an exception because it is typically hard to do so 
people don't try.  But if arbitrary database constraints were easy, people 
would 
probably be used to them and make more use, so less exceptional.

>> Also, having to enter records in a specific order, eg parent/child, is a
>> contrivance given you're dealing with what should be a set-oriented
>> database, and a contrivance that can make using the database more
>> difficult.  Ideally you just insert all the records in a change set at
>> once, and only the total effect of the change set is what is important for
>> enforcing constraints.  SQL immediate constraints break this ease of use.
>
> [DD] That's ease of use you pay dearly for in terms of usability though.
> And that order-agnostic benefit you claim is IMHO "artificial" and more a
> "mathematical" concept than a physical reality. As an analogy, it's a bit
> like asking to create a file first in a non-existent directory, and later
> create that missing directly. The real world often requires to do things in
> a specific order, and schemas do model the real world most times, so being
> order dependent seems entirely "natural" to me. My $0.02. --DD

While I agree that in the real world some things need to be in order, that 
isn't 
as true in the computer.  Its all a matter of abstraction.

In your analogy, one wants to create a file AND a directory; they should just 
be 
able to tell the system they want to create those 2 items as a single change, 
and have it happen, without having to worry about order.

But more importantly, a relational database is different than a file system, 
and 
what I propose is more appropriate there.  I'm talking about putting records in 
several tables at once using a single SQL statement, which is like creating a 
set of files only and not also directories, apples and oranges.

That being said, your file/directory analogy is more like creating a table and 
populating it in one statement, which incidentally can be done with a CREATE 
TABLE AS SELECT statement, though combining actions on other tables into the 
same statement isn't provided by SQL.

-- Darren Duncan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Simon Slavin]

On 17 Nov 2015, at 11:35pm, Rolf Ade  wrote:

> Although, the plea stays. Obviously, the name of the constraint would
> add value to the message. 

Since SQLite4 works differently to SQLite3 it's possible that adding this 
feature to SQLite4 would be 'cheaper' than adding it to SQLite3.  We'll have to 
wait and see.

Simon.

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

On 2015-11-18 1:27 AM, Yuri wrote:
> On 11/18/2015 01:17, Darren Duncan wrote:
>>
>> Deferred constraints are definitely a benefit.
>>
>> They allow you to express constraints otherwise not possible, for example 
>> that
>> a record may exist in table X if and only if a counterpart exists in table Y,
>> such as balancing records in a double-entry accounting system.  Granted all
>> you really need for this is the ability to change multiple tables as a single
>> atomic operation, but failing that ability, deferred constraints are the way
>> SQL provides to do it.
>
> I agree they can be beneficial, but not in all cases. Depends on what you do. 
> It
> would have been great if it was an option, ex. "CONSTRAINT LEVEL
> [STATEMENT|TRANSACTION];".

You can declare that behavior individually per foreign key constraint, and you 
can also change it at runtime with 
https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys if that's what you 
were looking for. -- Darren Duncan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

On 11/18/2015 01:17, Darren Duncan wrote:
>
> Deferred constraints are definitely a benefit.
>
> They allow you to express constraints otherwise not possible, for 
> example that a record may exist in table X if and only if a 
> counterpart exists in table Y, such as balancing records in a 
> double-entry accounting system.  Granted all you really need for this 
> is the ability to change multiple tables as a single atomic operation, 
> but failing that ability, deferred constraints are the way SQL 
> provides to do it. 

I agree they can be beneficial, but not in all cases. Depends on what 
you do. It would have been great if it was an option, ex. "CONSTRAINT 
LEVEL [STATEMENT|TRANSACTION];".

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

On 2015-11-18 12:50 AM, Yuri wrote:
> On 11/18/2015 00:45, Dominique Devienne wrote:
>> True. But that's in the case where FK constraints validation is deferred to
>> the transaction end.
>
> Why does SQLite defer constraint violation errors? Is there a benefit?
> This only complicates things, I would rather see the statement fail 
> immediately.

Deferred constraints are definitely a benefit.

They allow you to express constraints otherwise not possible, for example that 
a 
record may exist in table X if and only if a counterpart exists in table Y, 
such 
as balancing records in a double-entry accounting system.  Granted all you 
really need for this is the ability to change multiple tables as a single 
atomic 
operation, but failing that ability, deferred constraints are the way SQL 
provides to do it.

Also, having to enter records in a specific order, eg parent/child, is a 
contrivance given you're dealing with what should be a set-oriented database, 
and a contrivance that can make using the database more difficult.  Ideally you 
just insert all the records in a change set at once, and only the total effect 
of the change set is what is important for enforcing constraints.  SQL 
immediate 
constraints break this ease of use.

-- Darren Duncan

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Rolf Ade]

Am 11/17/2015 11:32 PM, Richard Hipp wrote:
> On 11/17/15, Yuri  wrote:
>> This message always leaves the user wondering: "Which constraint?"
>>
>> How hard is it to add this information to the message? Is this a matter
>> of memorizing the ID of the constraint, and then printing its name in
>> the message?
>>
> 
> It is a substantial change (basically a complete rewrite of the entire
> foreign key constraint mechanism) which would negatively impact both
> space and performance.
> 
> The current foreign key constraint mechanism uses a single counter.
> As constraints are violated, the counter increments, and as
> constraints are resolved the counter decrements.  At the end, if the
> counter is greater than zero then a "foreign key constraint" error is
> issued.
> 
> To provide information about which constraint(s) failed, it would be
> necessary to have a bag (a list or hash table or an associative array)
> of all the constraints that have been violated and then remove
> elements from the bag as constraints are resolved.
> 
> A bag takes more run-time memory than a single counter.  (Maybe a lot
> more, depending on how many elements it holds.)  Adding an element to
> a bag takes more time than incrementing a counter.  (In particular,
> adding an element to a bag probably involves one or more calls to
> malloc().) Removing an element from a bag takes more time than
> decrementing a counter.


A good explanation. It's faster and need lesser memory.

Although, the plea stays. Obviously, the name of the constraint would
add value to the message.

You made crystal clear, that we can't have both at once: fast / less
memory and helpful constraint failed message.

The way out may be ly in the words "at once". In case of a 'FOREIGN KEY
constraint failed' I'm temporarily fine with a not so fast and memory
efficent sqlite, as long, as it helps me, to understand the situation
more quickly.

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

On 11/18/2015 00:45, Dominique Devienne wrote:
> True. But that's in the case where FK constraints validation is deferred to
> the transaction end.

Why does SQLite defer constraint violation errors? Is there a benefit?
This only complicates things, I would rather see the statement fail 
immediately.

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Stephen Chrzanowski]

Why not a pragma option that toggles the option to the bag populated?  If
the pragma is not set, things run as expected.  If the pragma is set, keep
the bag.

Another option for those that link directly to the engine is at each
violation counter up, and another for each violation counter down, add
something to the API that would allow the program outside SQLite to link to
so it can decide what to do with the errors at finalization.  Maybe not run
anything internally, just pass the buck to the calling software and let it
deal with the problem?

On Tue, Nov 17, 2015 at 10:29 PM, Richard Hipp  wrote:

> On 11/17/15, Yuri  wrote:
> >
> > Don't keep the bag, keep only one integer ID of the first failed
> > constraint.
>
> Therein lays the rub: there is no way to tell which (if any) FK
> constraint has failed until you have run the operation to the end and
> checked them all.  Remember, an FK constraint is allowed to be
> violated during a transaction, as long as the violation is resolved
> before the end of the transaction.
>
> With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the
> constraint has failed right away.  And for those constraints, SQLite
> does provide more detail about exactly which constraint failed.  But
> for FK constraints, you never know if a constraint that is failing
> right now might be resolved before the end of the transaction.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Bernardo Sulzbach]

On Wed, Nov 18, 2015 at 12:00 AM, Simon Slavin  wrote:
>
>
> Since SQLite4 works differently to SQLite3 it's possible that adding this 
> feature to SQLite4 would be 'cheaper' than adding it to SQLite3.  We'll have 
> to wait and see.
>
> Simon.

Yes. Maybe it can be shipped into SQLite 4.

-- 
Bernardo Sulzbach

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Bernardo Sulzbach]

If there was a vote, I would also vote for better message. But I don't
think there is one. The impact may be significant for applications
that have already been written. Updating your dependency and seeing it
slower is not something any developer likes to do. I would understand
if this just ends up as a "wontfix".


-- 
Bernardo Sulzbach

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Richard Hipp]

On 11/17/15, Yuri  wrote:
>
> Don't keep the bag, keep only one integer ID of the first failed
> constraint.

Therein lays the rub: there is no way to tell which (if any) FK
constraint has failed until you have run the operation to the end and
checked them all.  Remember, an FK constraint is allowed to be
violated during a transaction, as long as the violation is resolved
before the end of the transaction.

With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the
constraint has failed right away.  And for those constraints, SQLite
does provide more detail about exactly which constraint failed.  But
for FK constraints, you never know if a constraint that is failing
right now might be resolved before the end of the transaction.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

> It is a substantial change (basically a complete rewrite of the entire
> foreign key constraint mechanism) which would negatively impact both
> space and performance.

I think the argument is fallacious.

Don't keep the bag, keep only one integer ID of the first failed 
constraint. That's all the users mostly care about.

// --- pseudo-code at the point of failure ---
if (!...check if constraint failed...) {
   // fast branch: normal stuff when constraint is satisfied, not 
impacted by the change at all
} else { // constraint failed
   // slow branch: failure is normally unexpected, this is executed very 
rarely
   cntFailed++;
   if (!savedFailedID) savedFailedID = currentFailedID; // the only 
added line is
}

You only need to modify the slow branch. This has practically zero 
performance impact, in any case it is exactly zero for the non-failure 
operation.

Yuri

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Darren Duncan]

I think there's a solution for this, which is, loosely, to run the constraint 
tests twice, conditionally.  That is, run it the current fast way as usual, and 
then only if there is a failure, run the tests again the slower way that keeps 
track of things so we know where the failure is.  Since we only get the 
slowdown 
in the failure case, when the code is aborting anyway, it should be ok.  The 
only negative then is that the code size increases somewhat, but it doesn't 
have 
to increase as much because the second run isn't to determine whether there 
will 
be a failure but to inform on a failure we already know happened.  This add-on 
could also be a compile-time option to exclude if desired. -- Darren Duncan

On 2015-11-17 2:32 PM, Richard Hipp wrote:
> On 11/17/15, Yuri  wrote:
>> This message always leaves the user wondering: "Which constraint?"
>>
>> How hard is it to add this information to the message? Is this a matter
>> of memorizing the ID of the constraint, and then printing its name in
>> the message?
>
> It is a substantial change (basically a complete rewrite of the entire
> foreign key constraint mechanism) which would negatively impact both
> space and performance.
>
> The current foreign key constraint mechanism uses a single counter.
> As constraints are violated, the counter increments, and as
> constraints are resolved the counter decrements.  At the end, if the
> counter is greater than zero then a "foreign key constraint" error is
> issued.
>
> To provide information about which constraint(s) failed, it would be
> necessary to have a bag (a list or hash table or an associative array)
> of all the constraints that have been violated and then remove
> elements from the bag as constraints are resolved.
>
> A bag takes more run-time memory than a single counter.  (Maybe a lot
> more, depending on how many elements it holds.)  Adding an element to
> a bag takes more time than incrementing a counter.  (In particular,
> adding an element to a bag probably involves one or more calls to
> malloc().) Removing an element from a bag takes more time than
> decrementing a counter.
>

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Richard Hipp]

On 11/17/15, Yuri  wrote:
> This message always leaves the user wondering: "Which constraint?"
>
> How hard is it to add this information to the message? Is this a matter
> of memorizing the ID of the constraint, and then printing its name in
> the message?
>

It is a substantial change (basically a complete rewrite of the entire
foreign key constraint mechanism) which would negatively impact both
space and performance.

The current foreign key constraint mechanism uses a single counter.
As constraints are violated, the counter increments, and as
constraints are resolved the counter decrements.  At the end, if the
counter is greater than zero then a "foreign key constraint" error is
issued.

To provide information about which constraint(s) failed, it would be
necessary to have a bag (a list or hash table or an associative array)
of all the constraints that have been violated and then remove
elements from the bag as constraints are resolved.

A bag takes more run-time memory than a single counter.  (Maybe a lot
more, depending on how many elements it holds.)  Adding an element to
a bag takes more time than incrementing a counter.  (In particular,
adding an element to a bag probably involves one or more calls to
malloc().) Removing an element from a bag takes more time than
decrementing a counter.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

[Yuri]

This message always leaves the user wondering: "Which constraint?"

How hard is it to add this information to the message? Is this a matter 
of memorizing the ID of the constraint, and then printing its name in 
the message?

Yuri