Re: POC: PLpgSQL FOREACH IN JSON ARRAY - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: POC: PLpgSQL FOREACH IN JSON ARRAY |
| Date | |
| Msg-id | CAFj8pRAw4sBhS+RH2+ABoaTqTBKRe_zqyy2dxmpPKdyF7uvxgg@mail.gmail.com Whole thread |
| In response to | POC: PLpgSQL FOREACH IN JSON ARRAY (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: Support EXCEPT for TABLES IN SCHEMA publications
Re: POC: PLpgSQL FOREACH IN JSON ARRAY |
| List | pgsql-hackers |
Hi
čt 23. 7. 2026 v 20:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
> >> * it's not clear to me that these routines have any business knowing
> >> about the "target type"; they certainly shouldn't contain comments
> >> alluding to PLpgSQL's conversion abilities. I think probably you
> >> just want them to pass back the data type they are producing and
> >> let PLpgSQL decide whether it wants to convert or not.
>
> > The iterator should know the target type for more reasons:
>
> I wasn't super convinced by these arguments, so today I tried to
> benchmark the performance question by asking Claude Code to do
> that. It came up with some interesting results, notably that this
> only matters for composite types not scalars. It also suggested
> that we could make the API cleaner with minimal performance
> loss by inventing a separate "coerce()" callback. As presented,
> its patch is a mess because it chose to allow run-time selection
> among several different ways, which of course we wouldn't do.
> But anyway, take a look at that option and see if it makes sense
> to you.
>
> (I should note that the benchmarking was done in a VM on my laptop.
> So it's hardly an industrial-strength setup, but since we only
> care about single-threaded performance here, I think the results
> are probably reasonably trustworthy. Feel free to reproduce for
> yourself though.)
> regards, tom lane
>
čt 23. 7. 2026 v 20:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
> >> * it's not clear to me that these routines have any business knowing
> >> about the "target type"; they certainly shouldn't contain comments
> >> alluding to PLpgSQL's conversion abilities. I think probably you
> >> just want them to pass back the data type they are producing and
> >> let PLpgSQL decide whether it wants to convert or not.
>
> > The iterator should know the target type for more reasons:
>
> I wasn't super convinced by these arguments, so today I tried to
> benchmark the performance question by asking Claude Code to do
> that. It came up with some interesting results, notably that this
> only matters for composite types not scalars. It also suggested
> that we could make the API cleaner with minimal performance
> loss by inventing a separate "coerce()" callback. As presented,
> its patch is a mess because it chose to allow run-time selection
> among several different ways, which of course we wouldn't do.
> But anyway, take a look at that option and see if it makes sense
> to you.
>
> (I should note that the benchmarking was done in a VM on my laptop.
> So it's hardly an industrial-strength setup, but since we only
> care about single-threaded performance here, I think the results
> are probably reasonably trustworthy. Feel free to reproduce for
> yourself though.)
>
I am thinking about possibilities on how to design this API. The main problem is in fact so container type like jsonb can contain nested typed values (possibly labeled values), and then there is not only one valid possibility of how to iterate over an array. Second issue is overhead based on packing and unpacking value, that is necessary when iterate and coerce are distinct methods. Third issue is responsibility - usually we want to push all logic to the iterator. The usage of iterators can be different too. Inside PL/pgSQL I have complete environment for casting inside and I mostly prefer casting inside PL/pgSQL (from consistency reasons), outside PL/pgSQL users probably prefer manual casting or casting fully provided by iterator (with possibility to choose implicit or explicit casting).
Can there be a solution with one optional argument (of iterate method), that can hold a setup?
so API can looks like
typedef enum ForeachAIteratorMode
{
USE_COMMON_TYPE,
BASE_TYPES_WITHOUT_COERCION,
BASE_TYPES_WITH_POSSIBLE_COERCION,
FORCE_IMPLICIT_COERCION,
FORCE_EXPLICIT_COERCION
} ForeachAIteratorMode;
typedef struct ForeachAIteratorOptions
{
Oid result_typid,
int32 result_typmod,
ForeachAIteratorMode mode
} ForeachAIteratorOptions
struct
{
bool (*iterate) (ForeachAIterator *self,
Datum *value,
Oid *typid,
int32 *typmod,
ForeachAIteratorOptions *options,)
}
Regards
Pavel
>
pgsql-hackers by date: