0

I'm migrating from MySQL to PostgreSQL, but I'm getting the following error:

PG::TooManyArguments: ERROR: cannot pass more than 100 arguments to a function

when running queries like this:

 Project.where(id: ids)

Which is translated to

"SELECT \"projects\".* FROM \"projects\" WHERE \"projects\".\"id\" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100) ORDER BY FIELD(projects.id, '1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100')"

For me it's a common use case to query by specific IDs and it worked pretty well with MySQL. Is there any way to make this work with PostgreSQL?

I'm using PostgreSQL 13.2 on a docker container.

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Radamanthys Junior
  • 21
  • 2

2 Answers2

0

According to the error you have, cause is the function not the query itself. you can pass 32K arguments to the query and it will work (2byte int limit). As for functions, postgres by default has 100 arg limit (set during compilation). you can try to compile from source and set that number to higher value (I dont recommend doing that, unless you really understand the consequences).

Best approach would be probably to look into how to replace FIELD() function that is executed and modify so that you don't run into the problem. Can you change your system so that you can use column in DB to sort by? That way you dont need to pass those IDs for sorting. Or, if you have to use IDs, what about using CASE for sorting, like in this SO question: Simulating MySQL's ORDER BY FIELD() in Postgresql

Iuri G.
  • 10,460
  • 4
  • 22
  • 39
  • This should at least be a configurable, easy to change option in PostgreSQL, it's a big disappointment this restriction. – Radamanthys Junior Apr 12 '21 at 13:13
  • To clarify, 100 arg restriction is on number of arguments a function call can have, and there is probably good reason (guessing performance or memory) it is not that easy to change. Maybe you should change your approach so you dont run into this restriction. Can you generate `CASE` statement instead of using custom `FIELD()` function? like this: https://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql#1310188 – Iuri G. Apr 13 '21 at 15:48
  • It's funny because it worked pretty good on PostgreSQL 10, I'm now getting the latest version when this feature used to work, and that`s probably be the answer, unfortunately. – Radamanthys Junior Apr 13 '21 at 16:59
0

The only "fix" I could find was downgrading PostgreSQL docker image to 11.11 where this error does not happen.

Radamanthys Junior
  • 21
  • 2