Nicola

> On Thursday, 12 August 2021 at 18:52:19 UTC+10, Nicola wrote:
> > On 2021-08-11, Derek Ignatius Asirvadem wrote:
> >> > Given your detailed question, AND the example you have cited, at what
> >> > point in time, do you suggest that the COUNT() would be correct
> >> > ? I am not asking for a long answer here, just clarifying you query.
> >> > A few words would be enough.
> >
> >> If transaction T1 is scanning a table to count its rows, and
> >> concurrently transaction T2 removes one row and adds two, the only
> >> values T1 should be able to compute are N (the number of rows in the
> >> table before the T1 and T2 starts executing) and N+1. That is because
> >> there are two possible serial executions:
> >
> > False. We are not serialised.
> >
> > We are discussing READ COMMITTED,

> Ok, I have misunderstood your question.

Um, I did not have a question. The question was yours. I was answering it.. In your question, you raised some quaint notions, that we do not have [are not necessary in] the real world, that we have survived very nicely without, for forty years. I merely asked for clarification re one of those precious notions, which I needed, to qualify your question, in order to answer it..

> The above is a very informally
> stated definition of correctness of a schedule (i.e., serializability).

Yet another precious notion that exists only in fantasy land, not in reality, that we have done without, for forty years. And please note, I am not asking for a definition of that particular insanity, loose or tight, dressed up in a tuxedo or a clown suit.

> So, if you ask at what point in time a result of a query run at READ
> COMMITTED is correct, the answer is: in general, never.

Good (for you)

And therein the notion of “correct” and “point in time”, were yours, not mine. It is all about your question, which is so loose an floppy, and contains three stupid notions, that I was trying to answer, in your context, without destroying the silly notions. Don’t put it back on me. This item did not start with me asking you what is correct, it started with you saying that something in the real world is NOT “correct”, according to your fantasy of “correct”.

You are still not getting it. I have explained, in detail, how you are questioning reality, from an insane set of notions that exist only in fantasy (sure, collective fantasy, established and made “real” by hundreds of academic papers that engage in fantasy). Therefore very “real” to you. But to those of us who stand on the hard ground of reality, they remain the notions of drooling idiots in the asylum.

> Note that this
> answer doesn't rule out situations in which you can guarantee a correct
> result even at that level.

> >> 1. [S1] starts scanning the table, counting the rows, oblivious to
> >> other activity, by virtue that it declaratively runs at READ
> >> COMMITTED.
> >> 2. T2 executes and commits;
> >> 3. [S1] keeps scanning the table, without regard to other activity
> >
> > Which somehow is “incorrect” to you.
>
> Yes, according to the definition of "correctness" (which you affirm to
> reject) as equivalence to a serial schedule.

(I don’t understand what “affirm to reject” means, so my answer excludes that element. It sounds like each cancels the other.)

No, no, no. The “incorrect” is in quotation marks, meaning that it is incorrect according to your quaint notion of “correct”, AND by inference, that it is not incorrect otherwise.

So, [1][2][3] above is perfectly correct in the real world, where people are not in denial of the fact that the database is an online shared resource, that is constantly changing.

It is only “incorrect” where the person denies the real world, and concocts a hysterical notion of “correct:” where in he can observe the real world as “incorrect”.

> Sorry, what is the definition of "correctness" according to the
> Standards?

Again, the notion of “correct” is yours. We couldn’t care less about it.

Again, same as the academics that propose that the /Relational Model/ is somehow lacking because it does not define database design or how to tie your shoelaces, it is stupid to expect the Standard (which defines only the standard) to define anything but the standard. But academics do that all the time, as you do here. It is no less stupid. Perhaps you are unaware that we put men on the moon in 1969, and did so with mainframes and 8-bit minicomputers, that we have had real, fully ACID compliant DBMS since the early 1960’s, long before SQL. When SQL was released by IBM, we did not need the SQL Standard to define what correct operation in a database server is, or how to count rows. We did not find the SQL Standard lacking because it failed to define how to tie our shoelaces using an UPDATE command.

Again, as defined by the Mental Health Act, the insane cannot be responsible, so they impose the responsibility for their impotence, onto others.

It is not the business or the responsibility of the Standard to define what is correct in a particular database server, especially when they did not define what a database server is.

You are demonstrating SIX levels of INSANITY (stupid) here, which I destroy using Logic alone (science).
__ First, you concoct a stupid notion for “correct” that does not exist in reality (insane act).
__ Second, you apply it to reality and find reality “incorrect” (insane act). No one cares.
__ Third, when it fails to impress humans, you can’t give a definition (the definitions you do give fail, because it does not reference the reality that it is applied to).
__ Fourth, you can’t take responsibility for your own stupid notion (definition of insanity).
__ Fifth, you try to impose that responsibility for definition of your stupid undefined notion onto anyone who does not accept your stupid undefined notion (insanity squared).
__ Sixth, when that fails you say the Standard that does not define anything except the Standard, is somehow wrong for not defining something that is not in the Standard (ROTFLMAO insanity).

I do not use the terms such as /insanity/ lightly. Nor do I make declarations without evidence: you always provide evidence to support my declarations.

Hint: I can prove, with any good example of a reasonably complex query, and a stated set of circumstances, that the result obtained in Sybase is correct, AND that the result of the same query in Oracle is incorrect. But that gets into the operation of the server, and the expectation of the person writing the query. And the engagement itself changes that [prior understanding of correct] in the Oracle query writer’s min. If you are looking at my posts on TTM from eleven years ago, I did that three times, complete with examples and proofs, with people who were honestly trying to understand. (I did that dozens of times with Darwen and the slaves but due to their dishonesty, they abandoned the exercise so as to deprive me (science) of a resolution.)

Hint: Most people who have experience on both can categorically state that MySQL operates correctly, and that PoopDePooGres does not. They do not need /correct/ defined for them.

It is stupefying, idiotic, insane, to define “correct” as an abstraction without reference to reality, and then try to apply it to reality, but academics love abstraction with no relation to reality, it is the foundation for their fantasies. This is the core principle that caused the impotence of academics in this field for FIFTY years (no progress of the /Relational Model/, but hundreds of anti-Relational papers marketed as “Relational”). Such as your paper on Movie Titles, which was proved false in another thread.

Normal humans are quite satisfied with the definition of /correct/ from their favourite dictionary. We take any abstraction away from that as either criminal, or insane, or both.

It is the same for the lunatic notion of “serializsability” and “schedule” and their variants. In the real world, we are multi-threaded. “Serialisability” is only relevant for developers who are so stupid, they can only grasp single-threaded operation, and operation from a fantasy of a database “version” that was magically taken at a non-existent “point in time”, frozen, like their brains, that is offline; private; personal, that they can suck on, that does not move and shift to interfere with the desperate need for a long suck.

Yes, of course, we have Scheduling. For actual multi-threaded thread, dependent on the resources requested, and the entire current (real moment in time) workload and cache contents. Not the ham-fisted “scheduling” of requests or pages in a fixed “version” that never existed.

Those who are so intellectually crippled, insist that we who are not crippled, should be so crippled, so that OUR operation is “correct” according to cripples.

No, we do not have to be “correct” according to some lunatic notion of “correct”. No, we don’t have to be “serialised”, according to some knuckle-dragging notion of “operation”. No, we don’t need a crippled “schedule” to execute hundreds of threads concurrently based on the reality of each thread. No, we don’t need the safety and comfort of fantasy notions and the tiled cells that foster them. No, you can’t impose your insanity on the sane.
>>>>
Yes, you can and you have, which is why your fantasy such as “MVCC” is now a COLLECTIVE fantasy, the expectation of the unqualified “developers”.
<<<<
But not with guys like me.

You would have better luck if you get a prostitute to tell a virgin how she is not “correct”; how important and essential and glorious syphilis could be in her life; how she is missing out; how she is not really living. In case you do not understand that, hint: the virgin lives from her spirit and intellect; the prostitute lives from her body alone, in denial of her spirit and intellect. /Look, you could have oozing sores and scars that are permanent, that make you unique, even after you die you will be unique, your bones will be marked permanently./ Not impressive. The virgin knows (knowledge; logic; intellect; spirit) that she is unique because she is created as such, not by mere marks on her skin or bones.

You need to get some experience in the real world, such that you do not expect a Standard to define what “correct” is. Give yourself ten years or so, and your question will have evaporated.

OTOH, if you wish to remain an academic idiot, full of irrelevant fantasy notions, since you are pushing them, you will have to define them. Take note, if the definitions do not reference reality, they will not apply to reality, they will be dismissed.

----

Deadlocks. You say “deadlocks can’t happen in MVCC [MV-non-CC+2PL]” as if it is superior. It isn’t. Relative to the real world, it is inferior. The fact is, you can’t even define what a deadlock is. Fact is, if a deadlock did happen in reality, your schizophrenic “version” of the frozen database COULD not know about it. Fact is, the rough equivalent of deadlock in your primitive herd of programs does happen, it is called “serialization failure*, proving that you can’t even serialise what you say is serialised. You can’t make this stuff up.

PigPoopGres. Fact is, not only does MV-non-CC not work at all, until you add your primitive 2PL, but that it still does not work at all, until MANUAL LOCKING is implemented in the app code. You academics are so stupid, you do not realise that that (allowing app code to interfere with internal locks) (a) guarantees disaster , and (b) guarantees the possibility of deadlocks due to less-than-perfect app code. It is the same irresponsible principle as you are attempting here: you can’t provide the service that you declare you provide, so you impose that provision of service onto the developer.

In that issue, MySQL is superior. Yes, it is MV-non-CC+Lock Manager. Yes, it does not work because MV-non-CC does not work, and MV-non-CC+Lock Manager does not work. Yes, they have to have additional locking. But not being as insane as academics, they allow only external named locks, such that it does not interfere with the internal locks. So at least MySQL can guarantee that their herd of programs does not deadlock, and if a deadlock did happen, it is in the app code alone. Far superior to pig poop.

Fact is, we don’t serialise, so truly, we cannot have serialisation failures. Fact is, we don’t need a frozen “version” of the database, we operate in real time, on the single, online, active, version of the truth, so we don’t need an idiotic, primitive schedule, we truly cannot have schedule failures.

Understood properly, yes, deadlocks do happen in the real world, in every walk of life. Fact is, since you are divorced from reality, sucking on an offline frozen “version” of the database, the accurate statement is, in MV-non-CC, since the “versions” are artificially offline, the recognition of real world deadlocks is artificially prevented.

It is not so relevant that our real world database servers are superior (yes, they are), the proper understanding is, reality is relatively superior to the inferiority of perceptions of reality by the insane; the crippled; the non-functional; the impotent; the bat-poop crazy.

----

> >> Count() would return the correct result if and only if the returned
> >> value is among the values that some serial execution of the same set of
> >> committed transactions would have returned.
>
> > I reject that as a definition, Sybase; DB2; MS rejects that as well.
>
> Then, again, please provide a definition of "correctness". Because you
> want to be confident that your queries produce "correct" results, don't
> you?

No.

The moon has not fallen into the river, and I don’t need your help to fish it out. And I am certainly not about to give you a definition of what a correct moon in the river looks like.

Yes, I have confidence, but that confidence is based in reality, not in abstraction.

My confidence that a certain query is, or is not, correct [no quotation marks] is NOT based on some hysterical abstraction, which does not reference the real world elements that it supposedly evaluates, it is based on the real world circumstances that the query operates in. If you want your idiotic abstraction to apply to reality, it has to be an abstraction *OF* reality. It is because your abstraction is a result of circle-jerking over fantasy that is proudly detached from reality, that it does not relate to, it is not applicable to, reality.

----

In the real world, we are not so crippled as to require “serial execution” in the multi-threaded server (multi-threaded at all levels).. Only academics, who consistently demonstrate their abject disability, their sub-human intellect, their inability to grasp technical subjects, their divorcement from reality, that can only write simple single-threaded programs, that need a “serial execution”.

Here, let me help you get out of your own mess, I don’t know if it will work, because I don’t have the necessary qualifications. You still have the hysterical notion of MV-non-CC, as the basis; the foundation, of your idiotic beliefs. That is a snapshot of the entire database as a “version”, taken at an abstract “moment in time”, that never existed (NOT like taking a hardcopy), for single-user use, the only thing you can understand, because you cannot handle the reality of an online database that is in the constant state of change. Great for simpletons. But in the real world, reality, there is no such thing as a snapshot of the database, no such thing as that /moment in time/. We do not freeze the database and take a photo, only cripples do.

No problem, I accept that you are intellectually crippled.

But then you try and impose that sub-human disability on the real world, wherein databases are not a snapshots taken at a mythical moment in time. I have a problem with that.

----

We get “developers” who have such hysterical notions all the time, they have false confidence because they say the academics said so. We have to train them by introducing reality to them. Those who are not totally crippled love it, and become useful over time. The others leave quietly, or throw a tantrum, or commit suicide later.

----

> Since anything can happen concurrently during the execution of a query
> at READ COMMITTED, how can you tell apart the result of a query
> returning an integer from
>
> select cast(rand() * 1000000) as int)
>
> ?

I understand the SQL, but I have read that question several times, but sorry, I do not understand your question.

----

> > Wherein COUNT at READ COMMITTED works perfectly.
>
> Ok if you are referring to other ways of counting.

No, since I referenced “COUNT”, I am referring to reading all the rows in a large table, per the SQL requirement, NOT other ways of obtaining a /count/ of the rows, which would be more efficient.

> But if you mean that
> counting by scanning the table at READ COMMITTED works perfectly,

Yes.

> please
> elaborate on what "works perfectly" means.

Why ?
For what purpose ?
You yourself just referenced “anything can happen concurrently during the execution of a query”: if you understand what you wrote, you will understand that your question is stupid, it does not apply to reality..

If I try to answer your question, I would have to explain:
a. reality
b. how a database server in the real world works, and
c. how a particular database server works.
That is called education.

As evidenced, I spend an awful lot of time and energy helping you, because you alone have attempted to cross the great chasm between academic isolation from reality, and reality. But no, sorry, I cannot provide basic education. You have down loaded SAP/Sybase Express Edition. Go and get educated yourself.

In short, “works perfectly” means works perfectly in all the circumstances that I have executed the command, across about 120 database servers that I have experience with, in the real world. Which are multi-threaded, and have configurable resources at every resource level, and share those resources, and resolve contention, without error. All of which you are, as evidenced, totally ignorant of, which is why you depend on primitive abstract notions divorced from reality, that you CAN understand.

> > But there is an important second point: anyone who has even pedestrian
> > knowledge of SQL on a genuine SQL Platform, would know that [separate
> > to the count changing constantly because the table is active], that
> > that is not a “problem”, that that is not the way to obtain a COUNT on
> > a large table.
>
> Sure. But it still illustrates the point, which is about the kind of
> issues you may run into when running queries at READ COMMITTED.

No. You TRIED to illustrate some idiotic point, but you FAILED. The entire attempt, each element, was hysterically stupid, and I destroyed each element of your stupidity.

> It is
> not suggesting that you *should* run those queries at READ COMMITTED.

Sometimes we do. Sometimes we don’t. It depends on what we want, which includes [does not deny] the reality of the server; and the activity; and the accuracy that is required for the task.

> Counting was not the best example; but you can replace the query with
> some other computation.

No. You are prosecuting the insanity, the onus is upon you to define the “problem”. It is you that has the “problem”, you define it, you provide an example. From my side, not being intellectually crippled, (a) I don’t have the “problem”, and (b) in response to your attempts to impose your insanity on us, (c) I have rejected each element of your “problem”, purely by Logic (science). You are as unscientific as a certified lunatic.

No, the moon has not fallen into the river. Our necks are not broken and we can look up, skyward. No we don’t need your “method” to fish the moon out of the water. It is up to you to define your moon in the water, yellow or pink or blood.

Cheers
Derek