PostgreSQL score 0.5 invalid input syntax for integer

Thanks for reporting this issue! Once again, MySQL is just way laxer than Postgres regarding these things.
Well, nothing for it. Let's see if the attached SQL syntax works for all three DMBSs we support.

Oops.

No idea what's happening there in Postgres. This should help debugging.

And this has a chance of fixing, or at least helping, with the MySQL fails.

This is getting exhausting …

Thanks for trying to help!
However, as you can see from the test results, #14 is already pretty good. There's just some pretty inexplicable fail on Postgres, with an illegal serialized value being stored, which I don't really know how to debug. Can you reproduce that problem/test fail locally? Debugging that would really help – I can neither reproduce nor explain it.

Using type "BIGINT" shouldn't be necessary as the database column itself uses normal "INT" as well. And I really don't know what the purpose of all these additional function calls would be.

Oh, sorry, wrote that comment before seeing your last post. Awesome work, thanks for finding that out!
Not sure how this is caused/triggered by this patch, though. But if your conclusion is true, it's a big problem in any case, I guess.
Do you know where that SQL is created, and how we can fix this on our side?
However if Postgres really creates a field with a max length of (apparently) 47 characters for a type called string_long, this sounds to me like a Core bug.

Is not a core problem tho, I'm concluding that type field might work too, the thing is, the task when is saved is broken.

Sorry, I don't understand this. Why do you say that this is not a Core bug?

Not sure if we should do this, but I did see a couple of things that can be improved in the patch.

1. +++ b/modules/search_api_db/src/Plugin/search_api/backend/Database.php
   @@ -930,9 +930,13 @@ class Database extends BackendPluginBase implements PluginFormInterface {
   +              $multiplier = min((int) $multiplier, 4294967295);
   ...
   +                  ->expression('score', 'CAST((score * CAST(COALESCE(:mult,\'0\') AS INTEGER)) AS INTEGER)', array(':mult' => $multiplier))
   

   We already cast to an integer, so why do it again here?

2. +++ b/src/Entity/Task.php
   @@ -136,12 +138,29 @@ class Task extends ContentEntityBase implements TaskInterface {
   +      switch ($this->get('type')->value) {
   +
   +        case 'updateIndex':
   +
   +          $class = Index::class;
   +
   +          break;
   +
   +      }
   

   Coding standards: There's too many blank lines here, it should be like this, I think.

   switch {
   case:
   $class
   break;
   
   }
   

3. +++ b/src/Entity/Task.php
   @@ -136,12 +138,29 @@ class Task extends ContentEntityBase implements TaskInterface {
   +        throw new $e->getMessage();
   

   Does this work? It doesn't look like it should. Don't we need to, at least, do: throw new \Exception($e->getMessage());?

4. +++ b/src/Task/TaskManager.php
   @@ -114,11 +114,16 @@ class TaskManager implements TaskManagerInterface {
   +    $serializer = \Drupal::service('serializer');
   

   The task manager uses constructor DI to inject needed services, let's do that for this service as well.

I'm very sorry, but I really don't understand what you're trying to say, and your patch mostly looks like temporary hacks and nothing that could be committed to the module. And, again, most of the changes look like they're completely unnecessary seeing as #14 already almost passes.
Please try to explain again what the problem is that causes the test fails for #14. If it's a truncated field value – how would using a different serializer help?
If you have identified the problem and know how to fix it (and it's really not a problem in Core), then please use my patch in #14 as a base and only add the changes that are necessary to make the tests pass on Postgres.

Sorry, but it's really almost impossible for me to tell what you're trying to say. Do you maybe have a colleague with better English who can translate? I think this is working for neither of us.

Anyways, I think you're now saying that Postgres can't handle the special characters introduced by serializing an entity? In that case, we can just serialize it's values, can't we? Using the serializer service would a) introduce a dependency on the "Serialization" module and b) apparently require us to know beforehand what class the serialized data should have (which we don't).
Patch attached (interdiff compared to #3, which is apparently the only serious patch in this issue, without any debugging code).

Also the UPDATE process of the Index is completely wrong. It do the score against the Score getting bigger and bigger the INTEGER.

If I interpret this correctly, I don't think you're reading the code right there. Or how do you think it should look?

So, if we want to improve the postgres handling, we should make sure that this passes on postgres?

Yes, of course! (All patches should pass on Postgres.)
So, seems like it's time for another debugging patch!

Oh. Right.

More debugging!

Tried sqlfiddle.com as an alternative, but that doesn't really seem to work.
Maybe I should try again to set up Postgres locally? Can't really be more tedious than this (he said, unconvinced).

Forgot the Postgres test above, but might not matter, actually: I managed to install Postgres this time and found a cast syntax that seems to work for all three DBMSs! (Although I couldn't actually reproduce the initial bug reported here with my Postgres installation, so not completely sure.)
Let's give it a try! (Interdiff compared to #39, of course.)

What?

Hm, OK, seems the cast doesn't really get us anything – Postgres refuses to multiply an integer with a float literal in any case, it doesn't even get to the surrounding cast.
This workaround should work in any case, though – no idea why I didn't think of that before.

Looks pretty good in my local tests, but to avoid problems with very large numbers (as we already saw in a different issue) I guess we should only use that if necessary, and with as small a scaling factor as possible (maximum 1000, though – who really needs more than three digits of precision there?).

Tests are green, I like the workaroud. It's not perfect but does sound like a good idea. Should we protect against too large numbers by using max(PHP_MAX_INT, pow(10, min(3, $after_point_digits)));?

If you don't think that's needed, we can commit this I think.

Needs a re-roll. Some lunatic committed a patch changing all arrays to short syntax …

No, such a check is definitely not necessary, especially for the line you quote – that's at most 1000. And the total multiplier can't be much larger. The only integer overflow that might happen is in the database table, and I don't think we can really guard against that. The problem of the DBMS, I'd say. (At least until enough people complain.)

So, if this passes, I'll commit it for RC 2.

Thanks for explaining! Looks good!

OK, good to hear.
Committed.
Thanks again for everyone's work here!