← Back to team overview

maria-developers team mailing list archive

Re: Patch for MDEV-12874 - UPDATE statements with the same source and target


Hello Sergei,
I agree, I can probably use can_compare_record in my second loop.
The use of a temporary table only to handle the case of blobs, seems to me too heavy.
If I add a check to exclude update of blob column (or switch to multi update) , is my patch be acceptable ?

I will try to understand multi_update code to see if I can use it (but my skills are a bit limited)

Many thanks.


> -----Message d'origine-----
> De : Sergei Golubchik [mailto:serg@xxxxxxxxxxx]
> Envoyé : mardi 29 août 2017 14:01
> À : jerome brauge
> Cc : maria-developers@xxxxxxxxxxxxxxxxxxx; Alexander Barkov
> Objet : Re: Patch for MDEV-12874 - UPDATE statements with the same
> source and target
> Hi, Jérôme!
> Sorry, it took a while, there were releases in the plan :(
> On Aug 18, jerome brauge wrote:
> > Hi, Sergei
> > Here is a version that reuses used logic when one searches and updates
> > the same index.
> > The principal impact is that we can't use the can_compare optimization
> > because we have to store all new values for all keys of tempfile.
> I don't quite understand that. You have to store new values in the tempfile,
> yes, but why does it mean that can_compare cannot be used?
> But here's a bug in your implementation, you cannot simply write
> table->record[0] to a temp file. This will not write blobs, because
> they're stored out of record.
> The correct solution is not to use an IO_CACHE for records, but to create a
> temporary table. It's create_tmp_table() function, and, for example,
> multi_update::initialize_tables() uses it.
> Hmm, I wonder, whether we could just reuse multi_update.
> Could be rather simple solution, if it'll work.
> > But this correct a bug in my previous implementation (on update
> > through a view).  This simplifies the code a bit, but not much (no
> > needs of tempfile_key).  I don't see how to do this more simpler.
> > If you have better ideas, I listen your suggestions.
> The way I see it now, there are essentially two distinct features.
> Using the same table in the WHERE clase. And in the SET clause.
> When the table is only present in the WHERE clause, we could use pretty
> much the same code as for used_key_is_modified (an additional loop over
> records before the main update loop, and a tempfile).
> When the table is only present in the SET clause, we don't need
> used_key_is_modified, but we need your tempfile_newdata and an
> additional loop after the main update loop.
> In the worst case you have three loops now (although this could, probably,
> be reduced to two).
> But I would think that this use case isn't particularly common and it is not
> worth the troubles distinguishing these use cases yet. So, there's no need to
> redo anything in this regard.
> Regards,
> Sergei
> Chief Architect MariaDB
> and security@xxxxxxxxxxx