Impossible to create an object with an array field of more than 100 elements

[vladiksun]

Describe the bug

Currently apache age hits the postgres limit when trying to create an object with an array field of more than 100 elements

How are you accessing AGE (Command line, driver, etc.)?

• JDBC

What data setup do we need to do?

...
select ag_catalog.create_graph('test_graph');
...

What is the command that caused the error?

select * from ag_catalog.cypher('test_graph', $$
CREATE (any_vertex: test_label { `largeArray`: [
                                        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, 101
                                    ]
                                }
        )
RETURN any_vertex $$
) as (test agtype);

[54023] ERROR: cannot pass more than 100 arguments to a function

Expected behavior
A label properties object has to be created with an array field more than 100 elements

Environment (please complete the following information):

• Version: [e.g. 1.3.0]

Additional context
Probably there is a way to bypass this limitation by using an array as a parameter.
Currently, we are in a research if we can move to apache age from another vendor.
This kind of blocks us.

[vladiksun]

Probably this issue might be considered as a duplicate of #949, but we believe we have a different use case.

[maazzzzz]

@vladiksun this isn't really a bug with age rather a configurable limit of PostgreSQL. If you'd like to change it, you have to make a 1 line change in the source code of PostgreSQL and compile it manually. The line you'd have to change is line#43 of pg-srcdir/include/pg_config_manual.h, you can assign your desired higher value to the defined FUNC_MAX_ARGS in that line and this will resolve your issue.

[vladiksun]

@maazzzzz Thanks for your advice but this is unfortunately not feasible for us because the Postgres distribution is precompiled and delivered by a dedicated vendor, so we cannot customize it.
Are there any technical ways to fix it inside Apache AGE code by refactoring the way large arrays are handled ?

[maazzzzz]

Are there any technical ways to fix it inside Apache AGE code by refactoring the way large arrays are handled ?

@vladiksun I'm sure you've already seen the workaround posted in the issue you linked above. Aside from that, i cant say with complete certainty, I'll have to look into it but I don't believe thats possible . @jrgemignani can better comment on that.

Also what would be the max length of an array in your workflow?

[vladiksun]

@maazzzzz Thank you. We expect an array to grow and shrink dynamically, depends on business logic. Could be several hundreds items.

[Amr-Shams]

I have asked a similar question Stack Overflow
In brief, the options to workaround the Postgres 100 argument limit in this case are:

1- Use an array as a single argument instead of individual arguments
2- Split the large array into multiple smaller arrays and pass each as a separate argument
3- Increase the max_function_args config parameter and recompile Postgres (not recommended)
4- Handle large arrays in the application layer instead of in the database
The preferred options are #1 and #2 - use array types and split into multiple arrays. Option #3, recompiling Postgres, is not recommended and should only be used as a last resort.

[vladiksun]

@Amr-Shams thanks for your comments on this.
We see those options like this
1 - Ideally can be done automatically from within apache age code.
2 - Ideally can be done automatically from within apache age code.
3 - can be OS specific and not recommended by Postgres team.
4 - those arrays are an application state but could be externalized to a plain jsonb column. This would require additional effort to migrate existing solution and change data model.

[jrgemignani]

There might be a way to modify the transform logic to work-around this limitation. I was able to find where in the code the issue is created and I will take a deeper look tomorrow.

[vladiksun]

@jrgemignani thank you for taking a look at this.

[jrgemignani]

The function agtype_build_list takes a list of arguments - that can be of any type and include expressions. As they can be of any input type, they may need to be transformed or resolved at execution time. This is why the elements are arguments to that function. And being arguments to a function, they are limited to 100. That being said, it "should" be possible to break up a large list in the transform phase and then use the concat operator to join the pieces in the execution phase.

I will work on a PR to fix this

[jrgemignani]

@vladiksun Curious, how big is the largest list that you expect?

[vladiksun]

@jrgemignani In our scenarios the largest is no more than 500 elements

[jrgemignani]

@vladiksun I have -

• Ceated PR Fix issue #989: Impossible to create array with > 100 items #1001 that will fix this issue in the master branch. It is currently in review.
• Verified that one can create lists of at least 800 items

Note: there are no restrictions on the array size. The fix works by breaking large lists into lists of no greater than 100 items and then wrapping those lists and the sub results with the concatenation operator. So, it will likely incur a small performance hit on creation. The larger the list, the larger the hit.

[vladiksun]

@jrgemignani thank you for fixing this

[jrgemignani]

@vladiksun The fix is now merged into the master branch (on postgresql version 11). We will be migrating the latest patches into the higher branches in a few weeks. But, you are welcome to use the master for your work.

[MuhammadTahaNaveed]

Closing this as completed. Fix in PR #1001.