Question 233512 in PostgreSQL Question posted in
The official documentation can be found here.

How to return matrices in functions written in C for PostgreSQL?

I was developing a function for Apache AGE that allows us to store graphs, a PostgreSQL extension, to return an adjacency matrix from a given graph. I used SPI to connect to the database and to retrieve the data from some queries with it. The creation of the matrix in the C function works fine, I’ve created it as:

        // Allocate memory for adjacency matrix.
        adjacency_matrix = (int64**)malloc(vertex_count * sizeof(int64*));
        for (i = 0; i < vertex_count; i++)
        {
            adjacency_matrix[i] = (int64*)malloc(vertex_count * sizeof(int64));
        }

        // Fill the matrix with zeros.
        for (i = 0; i < vertex_count; i++)
        {
            for (j = 0; j < vertex_count; j++)
            {
                adjacency_matrix[i][j] = 0;
            }
        }

(adjacency_matrix was already created and vertex_count had a value before).

Then I’ve done the following to return the matrix in the function:

        // Create a PostgreSQL array of arrays to hold the matrix.
        Datum *row_arrays = (Datum *) palloc(sizeof(Datum) * vertex_count);

        // Populate the array of arrays with sub-arrays (rows).
        for (i = 0; i < vertex_count; i++)
        {
            Datum *row_values = (Datum *) palloc(sizeof(Datum) * vertex_count);
            for (j = 0; j < vertex_count; j++)
            {
                row_values[j] = Int64GetDatum(adjacency_matrix[i][j]);
            }

            // Construct a 1D array for each row and store it in the array of arrays.
            row_arrays[i] = PointerGetDatum(construct_array(
                row_values,
                vertex_count,
                INT8OID,
                sizeof(int64),
                true,
                'd'
            ));
        }

        // Construct the final array of arrays (2D array)
        matrix_array = construct_array(
            row_arrays,
            vertex_count,
            INT8ARRAYOID,
            sizeof(Datum),
            false,
            'd'
        );

        SPI_finish();

        // Free allocated memory for adjacency matrix.
        for (i = 0; i < vertex_count; i++)
        {
            free(adjacency_matrix[i]);
        }

        free(adjacency_matrix);

        PG_RETURN_ARRAYTYPE_P(matrix_array);

I’ve declared the function as:

CREATE FUNCTION ag_catalog.get_adjacency_matrix(graph_name name)
RETURNS integer[][]
LANGUAGE c
AS 'MODULE_PATHNAME';

But when I run the function, it returns:

ERROR:  cache lookup failed for type 2139062143

How can I return this matrix in PostgreSQL?

Tags:

2

Answers


  1. 0

    You need to ensure that postgresql recognizes your array datatype.
    Construct two arrays:

    • First array should be 1D array of rows, each row will be the 1D array of integers.
    • Second array should be the 2D array from those rows.
    • Final 2D array will be returned using PG_RETURN_ARRAYTYPE_P.
    Login or Signup to reply.
  2. 0

    The issue on your code is related to the tuples fetching which is not clearly mentioned on the provided code.

    That is the part of code that throws the issue from PostgreSQL code, which shows that the condition comes to true at some point.

        type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
        if (!HeapTupleIsValid(type_tuple))
            elog(ERROR, "cache lookup failed for type %u", argtype);

    I think you may need to fix that part.

    And for sake of returning matrix array absolutely I’ve made a very small sample independent on that fetching of the tuples which is working and makes it more probable to SPI part.

    Code (Not a perfect implementation provided but it is sake for simple working one)

    
PG_FUNCTION_INFO_V1(get_adjacency_matrix); 
Datum get_adjacency_matrix(PG_FUNCTION_ARGS){ 
        ArrayType  *array;
        Datum       elements[2][2];
        int16           typlen;
        bool            typbyval;
        int            typalign;
        bool *nulls;
        int dims = 2, lbs = 2;
        elements[0][0] = 0;
        elements[0][1] = 1;
        elements[1][0] = 2;
        elements[1][1] = 3;

        get_typlenbyvalalign(INT8OID, &typlen, &typbyval, &typalign);
        array = construct_md_array(elements, nulls, 2, &dims, &lbs, INT8OID, typlen, typbyval,
typalign);
        PG_RETURN_ARRAYTYPE_P(array); 
};

    SQL

    
CREATE FUNCTION ag_catalog.get_adjacency_matrix(graph_name name)
RETURNS integer[][]
LANGUAGE c
AS 'MODULE_PATHNAME';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search