[Feature Request] Make helio_api.drop_indexes less weird

[AlekSi]

Purpose of the feature.

helio_api.drop_indexes is the only procedure with the INOUT parameter. All other functions are… functions. That creates problems for us: FerretDB/FerretDB#4730. This parameter seems to return BSON in the BSONHEX text representation?.. Or something. In any case, having a single function (that is not a function) that stands out that much is bad for API consistency.

It would be nice to have it as a regular function we could use.

Describe the solution you'd like

There is a "normal" function to drop an index.

Describe alternatives you've considered

Add more hacks to support this procedure.

Additional context

N/A.

[jayanta-mondal]

@AlekSi Thanks Alexey for bring this to our attention.

Just to understand your requirement, can you tell us a bit more about how are you using or the intended use of the drop_index API?

As you know FUNCTIONs can't start/commit/roll back transactions from within, and we need that capability in drop index, which involves marking the index being deleted and committing that information before proceeding with the delete. So we chose PROCEDURE organically.

We are looking into the output type/formatting issue.

[AlekSi]

As you know FUNCTIONs can't start/commit/roll back transactions from within

I did not actually know that. 😬

But why the last parameter is INOUT instead of just OUT, then?

After trying all possible ways, I still can't get the output as binary BSON instead of BSONHEX. PostgreSQL just does not provide a syntax for using type conversions in procedure calls, I think?