1

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, and 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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Matheus Farias
  • 716
  • 1
  • 10

3 Answers3

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.
adil shahid
  • 125
  • 4
  • Well, I did that in the second code section. First I create a Datum array to store the rows, named `row_arrays`, and fill them inside the for loop. They do store stuff, I've checked with GDB. Then I made a matrix that stored these rows in a array. But I receive an error. Can you give a code example to fix this issue? – Matheus Farias Aug 17 '23 at 18:47
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';
Mohamed Mokhtar - rrrokhtar
  • 678
  • 5
  • 12
  • Thing is, I need to create the `elements` matrix dynamically. I'm doing that using `malloc` and a nested for loop. But the data type in this situation is `Datum**`, and the first argument that `construct_md_array` uses is `Datum*`. I'll update the question to add how I'm fetching the data. – Matheus Farias Aug 21 '23 at 20:44
0

Check out this function, might help to simulate the required example as you asked.

#include "postgres.h"
#include "fmgr.h"
#include "utils/array.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(return_matrix);

Datum return_matrix(PG_FUNCTION_ARGS) {
    int rows = 3;
    int cols = 3;

    double **matrix = (double **) palloc(sizeof(double *) * rows);
    for (int i = 0; i < rows; i++) {
        matrix[i] = (double *) palloc(sizeof(double) * cols);
        for (int j = 0; j < cols; j++) {
            matrix[i][j] = i * cols + j; 
        }
    }

    Oid element_type = FLOAT8OID;
    int16 typlen;
    bool typbyval;
    char typalign;
    get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);

    ArrayType *result_array = construct_array(
        (void *) matrix,    // Pointer to the data
        rows * cols,        // Total number of elements
        element_type,       // Element type OID
        typlen,             // Length of each element
        typbyval,           // Whether element is passed by value
        typalign            // Alignment requirement
    );

    // Return the array as Datum
    PG_RETURN_ARRAYTYPE_P(result_array);
}
Vinay-Agens
  • 15
  • 2