WIBNIs re error messages and backup

This forum is to report technical problems with Rel.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

WIBNIs re error messages and backup

Post by HughDarwen »

I ran into a couple of problems recently, neither of which represents a bug exactly but I couldn't find a more suitable forum.

1. The statement "delete xyz;", where xyz is the name of a base relvar, gave rise to an error message telling me that an empty relation had been given for TUPLE FROM, and Dbrowser placed the cursor on "xyz;" in the input pane. It took me the best part of an hour to realise that I had (many moons ago) declared a constraint on xyz that relied on it being nonempty! Would that the error message had mentioned the constraint by name and Dbrowser had not bothered with the cursor positioning.

2. In connection with the above, at first I suspected a corrupted database, so I tried recreating my database from the latest backup. The proved to be quite difficult because the backup file had declarations of virtual relvars in front of the base relvars referenced in those declarations. I realise, of course, that a complete solution requires dependency tracking and support for multiple declarations in a single statement (a feature recently added to Tutorial D), but defining and populating all the base relvars first, then the virtuals, then the constraints, would cover most cases, I think.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

Indeed, item 1 isn't quite a bug, but it's stunningly unhelpful. I'd never run into this before, but it's trivial to recreate it. A constraint like

Code: Select all

CONSTRAINT blat   (x FROM TUPLE FROM myvar) = 2;
assuming an appropriately constituted myvar and an attempt to DELETE myvar; generates the following error:
ERROR: TUPLE FROM expects a relation with cardinality equal to one.

It doesn't mention the fact that the ERROR originates inside the constraint expression, not the "DELETE myvar;" statement.

I will fix that.

Item 2 is a surprise. All real relvar definitions, virtual relvar definitions, operator definitions, type definitions and constraint definitions have a CreationSequence attribute in the catalog. This numeric attribute identifies the order in which definitions have been created, and is used by the database backup generation script (DatabaseToScript.d, found in the Scripts directory) to sort the entries in a database backup into the order they were originally created -- except for database constraints, which are always emitted last.

What you experienced in trying to restore the database backup suggests that the CreationSequence somehow got out of sequence. The only way I can think that might occur is if the unique.id file, which is found in a Rel database directory, was deleted at some point. This would cause Rel to silently re-create it, but it would start the sequence numbering over at 1. Subsequent new definitions would be given sequence numbers that had already been used, so any subsequent database backup would have the definitions in the wrong order.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

Thank you, Dave. In connection with (b), I can confirm that the problem was not caused by a deletion of unique.id. I used the wrongly sequenced backup to restore my database, copying-and-pasting from the backup file piece by piece. Then I immediately ran backup again and inspected the file. Again it had virtuals defined before the reals they depended on.

However, the following might be of interest. Inspecting sys.Catalog, I notice that I have thirteen relvars altogether. The first nine have creation sequence numbers ranging from 102 to 151 (with a few gaps) and those look okay. But the tenth, with sequence number 152, is a virtual defined on the thirteenth, which has the surprising sequence number 7087 and the eleventh, with sequence number 153, is defined on the twelfth, which has the surprising sequence number 6984. Clearly I did not defined those last four in that order.

I'll send you a zip file for the database folder later.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

This suggests that there's a bug related to the sequencing of relvar definitions. Yes, a zip file of the database folder would be very helpful. Thanks!
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

On reflection, I think item 1 is a bug after all. I obtained the following output from the database I sent you but with the constraint ReadingsAscend replaced by the definition shown (note that I have only that one constraint apart from key constraints):

sys.Constraints
RELATION {Name CHARACTER, Definition CHARACTER, Owner CHARACTER, CreationSequence INTEGER} {
TUPLE {Name "ReadingsAscend", Definition "( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth where Day < max ( UsageThisMonth , Day ) , Day ) ) ) <= ( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth , Day ) ) )", Owner "User", CreationSequence 162}
}

delete UsageThisMonth, insert UsageThisMonth rel{tup{Day 1, Gb 0.13}};
ERROR: Result of MAX on no values is undefined.
Line 1, column 64 near '0.13'

UsageThisMonth := rel{tup{Day 1, Gb 0.13}};
ERROR: Result of MAX on no values is undefined.
Line 1, column 37 near '0.13'

At first I thought it was a multiple assignment bug, but then it occurred to me that using delete-then-insert was a bit silly in the first place! However, the simple assignment gives rise to the same error. Note that in both cases UsageThisMonth is nonempty at the semicolon.

Needless to say, the simpler definition of ReadingsAscend, without CASE test for empty, was my original attempt. I added the CASE test when I eventually stumbled on where the strange error message had come from.

Sorry that I originally reported the error message as arising from a TUPLE FROM invocation rather than MAX on the empty set.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

Just off the top of my head, this might be an effect of the fact that Rel's multiple assignment implementation is flawed (implementing it properly is surprisingly complex) so the result of "delete UsageThisMonth" is visible at the point where "insert UsageThisMonth ..." starts. However, it shouldn't have done any constraint checking in between the assignments! I'll have to do some testing.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

I think I must apologise. On close inspection, my constraint includes the expression "UsageThisMonth where Day = max ( UsageThisMonth where Day < max ( UsageThisMonth , Day ) , Day )".

The subexpression in bold yields an empty relation when UsageThisMonth has cardinality 1, as it does as a result of my update. As it is also the first operand of an invocation of MAX, that would account for the error.

However, what's puzzling me now is the fact that I was able to avoid the error simply by adding a test for emptiness:

CASE WHEN IS_EMPTY ( UsageThisMonth ) THEN TRUE ELSE ( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth where Day < max ( UsageThisMonth , Day ) , Day ) ) ) <= ( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth , Day ) ) ) END CASE

It seems to me that this should fail for exactly the same reason, when a relation of cardinality 1 is assigned to UsageThisMonth.

Sorry for all the confusion and for the over-complicated test case.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

No worries - thanks for the follow-up.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

It appears that I've been mistaken yet again. When my constraint is as I wrote on October 16:

CASE WHEN COUNT ( UsageThisMonth ) < 2 THEN TRUE ELSE ( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth where Day < max ( UsageThisMonth , Day ) , Day ) ) ) <= ( Gb from tuple from ( UsageThisMonth where Day = max ( UsageThisMonth , Day ) ) ) END CASE

then I do after all get an error message saying that MAX is undefined on an empty relation and I cannot now explain why I thought otherwise.

Also, the CreationSequence corruption problem seems to be repeatable after all. The attached files are (a) a backup file used to restore the database and (b) a copy of the output pane showing how CreationSequence values became corrupted as a result of a rather complicated database update. As an aside, the script shows how Rel gave the error message concerning MAX on the empty set at my first attempt to do that update (which takes several minutes, by the way), followed by my correction to the constraint definition and then the successful repetition of the multiple assignment. Notice the strange message "inserted 1 tuple" in response to a successfull DROP CONSTRAINT statement.
Attachments
LatestBackup.d
(29.57 KiB) Downloaded 519 times
CorruptedCreationSequence.txt
(9.38 KiB) Downloaded 533 times
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

Thanks for this second follow-up. The fact that the CreationSequence problem appears to be repeatable is good news -- that gives me an excellent chance of sorting it out fairly quickly.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

I can now report that on my "production" database the CreationSequence value for base relvar Usage is rising by a small amount after every execution of that multiple assignment. On test databases (copies) the symptoms have been slightly different, sometimes involving two base relvars and sequence numbers reaching into four or five figures.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

Thanks again - this additional information is helpful. It's giving me some ideas for places to look for a problem, but I haven't found the problem yet. I'll hopefully sort it out when I get a bit more time this weekend.
HughDarwen
Posts: 124
Joined: Sat May 24, 2008 4:49 pm

Re: WIBNIs re error messages and backup

Post by HughDarwen »

I have discovered a much easier way to recreate the problem with rising CreationSequence values.

Start DBrowser with a brand new database.

VAR ab base rel{a int, b int} key{a};
then:
(sys.Catalog where Owner = 'User'){Name, CreationSequence}
gives 106 as the CreationSequence value for ab.

Now:
ab := rel{tup{a 1, b 1}};
(sys.Catalog where Owner = 'User'){Name, CreationSequence}
gives 108 as the CreationSequence value for ab.

On each repetition of the above sequence, ab's CreationSequence value rises by 2 every time.

If ab is updated using insert, delete or update, CreationSequence remains unchanged. It seems that the bug lies in direct assignment.

Hugh
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

Thanks again - this is very helpful. It's the middle of term right now so I'm very busy with teaching, but I'm hoping to get some time to sort this out in the next few days and send out a new update.
Dave
Site Admin
Posts: 372
Joined: Sun Nov 27, 2005 7:19 pm

Re: WIBNIs re error messages and backup

Post by Dave »

I have found and fixed the bug. A new update containing this fix will be released shortly.
Post Reply