Home > mailing lists

Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

From	Mark Rofail
Subject	Re: [HACKERS] GSoC 2017: Foreign Key Arrays
Date	June 3, 2017 01:28:07
Msg-id	CAJvoCus-eACvA4-eHqrG4ft38z6wd6QLpu0Vfzu3J2xFfEotCg@mail.gmail.com Whole thread Raw
In response to	Re: [HACKERS] GSoC 2017: Foreign Key Arrays (Alexander Korotkov <aekorotkov@gmail.com>)
Responses	Re: [HACKERS] GSoC 2017: Foreign Key Arrays
List	pgsql-hackers

Tree view

src/include/catalog/pg_amop.h
src/include/catalog/pg_amproc.h
src/include/catalog/pg_opclass.h
src/include/catalog/pg_opfamily.h

Thanks to Alexander's reply I have been able to jump from catalog table to table till I found the function I was looking for.

My goal is to add a new operator (@>(anyarray,anyelement)) to the (array_ops) op class.
I am going to post the steps I took to locate the procedure, the following is the trail of tables I followed.

pg_opfamily

pg_opfamily defines operator families.

Link to docs

{

opfmethod; /* index access method opfamily is for */

opfname; /* name of this opfamily */

opfnamespace; /* namespace of this opfamily */

opfowner; /* opfamily owner */

}

gin=# select oid, * from pg_opfamily where opfmethod = 2742;

oid | opfmethod | opfname | opfnamespace | opfowner

------+-----------+----------------+--------------+----------

2745 | 2742 | array_ops | 11 | 10

3659 | 2742 | tsvector_ops | 11 | 10

4036 | 2742 | jsonb_ops | 11 | 10

4037 | 2742 | jsonb_path_ops | 11 | 10

(4 rows)

as this table defines operator families I won't need to modify them.

pg_opclass

pg_opclass defines index access method operator classes.

Link to docs

{
opcmethod; /* index access method opclass is for */
opcname; /* name of this opclass */
opcnamespace; /* namespace of this opclass */
opcowner; /* opclass owner */
opcfamily; /* containing operator family */
opcintype; /* type of data indexed by opclass */
opcdefault; /* T if opclass is default for opcintype
opckeytype; /* type of data in index, or InvalidOid */
}

gin=# select * from pg_opclass where opcfamily = 2745;

-----------+-----------+--------------+----------+-----------+-----------+------------+------------

2742 | array_ops | 11 | 10 | 2745 | 2277 | t | 2283

(1 row)

as this table defines operator classes I won't need to modify them.

this led me to pg_amproc

pg_amproc

pg_amproc stores information about support procedures associated with access method operator families.

Link to docs

{

amprocfamily; /* the index opfamily this entry is for */

amproclefttype; /* procedure's left input data type */

amprocrighttype; /* procedure's right input data type */

amprocnum[1]; /* support procedure index */

amproc; /* OID of the proc */

}

gin=# select * from pg_amproc where amprocfamily = 2745;

amprocfamily | amproclefttype | amprocrighttype | amprocnum | amproc

------------------+--------------------+---------------------+---------------+----------------------------

2745 | 2277 | 2277 | 2 | pg_catalog.ginarrayextract

2745 | 2277 | 2277 | 3 | ginqueryarrayextract

2745 | 2277 | 2277 | 4 | ginarrayconsistent

2745 | 2277 | 2277 | 6 | ginarraytriconsistent

(4 rows)

[1]amprocnum refers to this table

as this table defines support procedures I won't need to modify them.

this led me to pg_amop

pg_amop

pg_amop stores information about operators associated with access method operator families.

Link to docs

{

amopfamily; /* the index opfamily this entry is for */

amoplefttype; /* operator's left input data type */

amoprighttype; /* operator's right input data type */

amopstrategy; /* operator strategy number */

amoppurpose; /* is operator for 's'earch or 'o'rdering? */

amopopr; /* the operator's pg_operator OID */

amopmethod; /* the index access method this entry is for

amopsortfamily; /* ordering opfamily OID, or 0 if search op

}

=# select * from pg_amop where amopfamily = 2745;

------------+--------------+---------------+--------------+-------------+---------+------------+----------------

2745 | 2277 | 2277 | 1 | s | 2750 | 2742 | 0

2745 | 2277 | 2277 | 2 | s | 2751 | 2742 | 0

2745 | 2277 | 2277 | 3 | s | 2752 | 2742 | 0

2745 | 2277 | 2277 | 4 | s | 1070 | 2742 | 0

(4 rows)

I will need to add a record of my new operator to this table by appending this line to src/include/catalog/pg_amop.h

DATA(insert (2745 2277 2277 1 s 2750 2742 0 ));

This will result in the following entry

=# select * from pg_amop where amopfamily = 2745;

------------+--------------+---------------+--------------+-------------+---------+------------+----------------

2745 | 2277 | 2283 | 1 | s | 2750 | 2742 | 0

this led me to pg_operator

pg_operator

pg_operator stores information about operators.

Link to docs

{
oprname; /* name of operator */
oprnamespace; /* OID of namespace containing this oper */
oprowner; /* operator owner */
oprkind; /* 'l', 'r', or 'b' */
oprcanmerge; /* can be used in merge join? */
oprcanhash; /* can be used in hash join? */
oprleft; /* left arg type, or 0 if 'l' oprkind */
oprright; /* right arg type, or 0 if 'r' oprkind */
oprresult; /* result datatype */
oprcom; /* OID of commutator oper, or 0 if none */
oprnegate; /* OID of negator oper, or 0 if none */
oprcode; /* OID of underlying function */
oprrest; /* OID of restriction estimator, or 0 */
oprjoin; /* OID of join estimator, or 0 */
}

postgres=# select * from pg_operator where oid = 2751;

---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------------+--------------+------------------

@> | 11 | 10 | b | f | f | 2277 | 2277 | 16 | 2752 | 0 | arraycontains | arraycontsel | arraycontjoinsel

(1 row)

I will need to add a record of my new operator to this table by appending this line to src/include/catalog/pg_operator.h

However, as this is dependent on the procedure I have yet to create there are still uknown values

DATA(insert OID = <uniqueProcId> ( "@>" PGNSP PGUID b f f 2277 2283 16 2752 0 arraycontainselem ???? ???? ));

DESCR("contains");

#define OID_ARRAY_CONTAINS_OP <uniqueProcId>

This will lead to this entry

---------+--------------+----------+---------+-------------+------------+---------+----------+-----------+--------+-----------+---------------+--------------+------------------

@> | 11 | 10 | b | f | f | 2277 | 2283 | 16 | 2752 | 0 | arraycontainselem | ???? | ????

(1 row)

this led me to pg_proc

pg_proc

pg_proc stores information about functions (or procedures)

Link to docs

{

proname; /* procedure name */

pronamespace; /* OID of namespace containing this proc */

proowner; /* procedure owner */

prolang; /* OID of pg_language entry */

procost; /* estimated execution cost */

prorows; /* estimated # of rows out (if proretset) */

provariadic; /* element type of variadic array, or 0 */

protransform; /* transforms calls to it during planning */

proisagg; /* is it an aggregate? */

proiswindow; /* is it a window function? */

prosecdef; /* security definer */

proleakproof; /* is it a leak-proof function? */

proisstrict; /* strict with respect to NULLs? */

proretset; /* returns a set? */

provolatile; /* see PROVOLATILE_ categories below */

proparallel; /* see PROPARALLEL_ categories below */

pronargs; /* number of arguments */

pronargdefaults; /* number of arguments with defaults */

prorettype; /* OID of result type */

proargtypes; /* parameter types (excludes OUT params) */

proallargtypes[1]; /* all param types (NULL if IN only) */

proargmodes[1]; /* parameter modes (NULL if IN only) */

proargnames[1]; /* parameter names (NULL if no names) */

proargdefaults; /* list of expression trees for argument

protrftypes[1]; /* types for which to apply transforms */

prosrc; /* procedure source text */

probin; /* secondary procedure info (can be NULL) */

proconfig[1]; /* procedure-local GUC settings */

proacl[1]; /* access permissions */

}

postgres=# select * from pg_proc where oid = 2748;

---------------+--------------+----------+---------+---------+---------+-------------+--------------+----------+-------------+-----------+--------------+-------------+-----------+-------------+-------------+-----

-----+-----------------+------------+-------------+----------------+-------------+-------------+----------------+-------------+---------------+--------+-----------+--------

arraycontains | 11 | 10 | 12 | 1 | 0 | 0 | - | f | f | f | f | t | f | i | s |

2 | 0 | 16 | 2277 2277 | | | | | | arraycontains | | |

(1 row)

I have yet to study this table thoroughly.

This finally led me to the arraycontains procedure in src/backend/utils/adt/arrayfuncs.c

Datum
arraycontains(PG_FUNCTION_ARGS)
{
AnyArrayType *array1 = PG_GETARG_ANY_ARRAY(0);
AnyArrayType *array2 = PG_GETARG_ANY_ARRAY(1);
Oid collation = PG_GET_COLLATION();
bool result;

result = array_contain_compare(array2, array1, collation, true,
&fcinfo->flinfo->fn_extra);

/* Avoid leaking memory when handed toasted input. */
AARR_FREE_IF_COPY(array1, 0);
AARR_FREE_IF_COPY(array2, 1);

PG_RETURN_BOOL(result);
}

This corrosponds to the operator @<(anyarray, anyarray) which is the generalised form of my proposed operator @<(anyarray, anyelement).
Studying the syntax will help me produce a function that follows the postgres style rules.

pgsql-hackers by date:

From: Andres Freund
Date: 03 June 2017, 00:45:59
Subject: Re: [HACKERS] walsender termination error messages worse in v10

From: Petr Jelinek
Date: 03 June 2017, 01:55:22
Subject: Re: [HACKERS] walsender termination error messages worse in v10

Re: [HACKERS] GSoC 2017: Foreign Key Arrays - Mailing list pgsql-hackers

Previous

Next