Re: What is the correct way to extract values from an int8 array in SPI? - Mailing list pgsql-general

Tom Lane írta:
> Boszormenyi Zoltan <zb@cybertec.at> writes:
>
>> //    ids =
>> PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(prod_inv->vals[0],
>> prod_inv->tupdesc, 1, &isnull)));
>>
>
> well, for one thing, you probably want DatumGetPointer ...

You chose the commented out line to comment on. :-)
The original line was after this one:

ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull);


I only experimented with whether I need to detoast the array value.

>  for another,
> you shouldn't really be converting to Pointer here at all, since the
> next line expects ids to still be a Datum.  On some platforms you can
> get away with being fuzzy about the distinction between Datum and
> Pointer, but it doesn't surprise me in the least that such code would
> fail elsewhere.  Try paying more attention to compiler warnings ;-)
>

I did. :-) PointerGetDatum(PG_DETOAST_DATUM(SPI_getbinval(...)))
doesn't emit any warnings.

> The lack of any checks for null-ness scares me, too.
>   Aside from the
> multiple places where you're just plain ignoring an isnull return flag,
> there's the risk that the array might contain one or more nulls,


I omitted this particular check because:
- ids bigint[] NOT NULL, and
- the code that builds the content of the array ensures
  that no array member can be NULL. They are bigint IDs
  from another table. :-)

>  in
> which case you can't address the last element that way (even if that
> element itself isn't null).
>

Yeah, this is what bothers me.

$ psql -p 5433 index_test
psql (8.4.1)
Type "help" for help.

index_test=# select array_length(ids,1) from
product.t_product_inv_titleonly where word='cpu';
 array_length
--------------
           96
(1 row)

index_test=# select ids from product.t_product_inv_titleonly where
word='cpu';

                                             


                                             


ids

                                             


                                             



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------

{29767643,29783831,33973788,33994384,33966944,33974483,33945574,33988076,33957605,33985034,29050215,33925825,33961012,29066655,33955860,33981152,33990118,33937422,33

972534,33923080,33921945,33979786,33926521,33983828,33980602,33932253,33926012,33925643,40361238,42814197,45923261,33933417,33952470,33988350,33930668,33925627,339799

81,33937362,31250473,35083034,33958934,33946597,33948953,33993455,33987994,33923724,33934644,33961183,34905945,33931220,33973198,33979613,33993878,31973668,47835781,4

7835782,47836159,47866522,47866523,47867628,33943565,33966303,45072269,33955440,33959714,33948651,33977798,30113741,33975105,33943434,33932791,33954807,33922152,33971

756,27401475,27407609,27401410,27405102,33620032,33621234,33624659,30116651,33966940,30116815,30121547,30113990,30115882,33958841,30123948,33953821,33929316,47373326,
47374380,47374458,30123436,33930912}
(1 row)


You can see that the above array doesn't have NULLs.
But this has debug output has revealed the real problem:

    ids = SPI_getbinval(prod_inv->vals[0], prod_inv->tupdesc, 1, &isnull);
    n_ids = DatumGetInt32(DirectFunctionCall2(array_length, ids,
Int32GetDatum(1)));
    ids_arr = DatumGetArrayTypeP(ids);
    ids_data = (Datum *) ARR_DATA_PTR(ids_arr);

    /* Set up the initial indexes for binary search */
    idx_min = 0;
    idx_max = n_ids - 1;
    idx_mid = (idx_max + idx_min) / 2;

    elog(NOTICE, "n_ids %d idx_min %d idx_max %d idx_mid %d", n_ids,
idx_min, idx_max, idx_mid);

    for (k = 0; k < n_ids; k++)
        elog(NOTICE, "Datum %d %ld", k, ids_data[k]);

index_test=# SELECT product.website_simple_query_ids('cpu', true, 10, 2000);
NOTICE:  n_ids 96 idx_min 0 idx_max 95 idx_mid 47
NOTICE:  Datum 0 29767643
NOTICE:  Datum 1 0
NOTICE:  Datum 2 29783831
NOTICE:  Datum 3 0
...
NOTICE:  Datum 91 0
NOTICE:  Datum 92 33934644
NOTICE:  Datum 93 0
NOTICE:  Datum 94 33961183
NOTICE:  Datum 95 0
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

So, it seems the answer to my question is:
only the array is received as Datum, the actual data
in the array are not. They are stored in the specified
data type of the array, which in this case is int64.

I wonder about more complex arrays, like ones that
contain composite types. Are the elements stored in
the C struct representation of the SQL type in this case,
or as Datums? Or in the (possibly unpadded) format
that the binary out functions produce?

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/