Re: GSoC 2017: Foreign Key Arrays

>
> 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
<https://www.postgresql.org/docs/devel/static/catalog-pg-opfamily.html>

{

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
<https://www.postgresql.org/docs/current/static/catalog-pg-opclass.html>

{
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;

opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype |
opcdefault | opckeytype

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

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
<https://www.postgresql.org/docs/devel/static/catalog-pg-amproc.html>

{

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
<https://www.postgresql.org/docs/10/static/xindex.html#xindex-gin-support-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
<https://www.postgresql.org/docs/devel/static/catalog-pg-amop.html>

{

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;

amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose |
amopopr | amopmethod | amopsortfamily

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

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;

amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose |
amopopr | amopmethod | amopsortfamily

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

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
<https://www.postgresql.org/docs/devel/static/catalog-pg-operator.html>

{
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;

oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash |
oprleft | oprright | oprresult | oprcom | oprnegate | oprcode |
oprrest | oprjoin

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

@> | 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

oprname | oprnamespace | oprowner | oprkind | oprcanmerge | oprcanhash |
oprleft | oprright | oprresult | oprcom | oprnegate | oprcode |
oprrest | oprjoin

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

@> | 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
<https://www.postgresql.org/docs/devel/static/catalog-pg-proc.html>

{

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;

proname | pronamespace | proowner | prolang | procost | prorows |
provariadic | protransform | proisagg | proiswindow | prosecdef |
proleakproof | proisstrict | proretset | provolatile | proparallel | pron

args | pronargdefaults | prorettype | proargtypes | proallargtypes |
proargmodes | proargnames | proargdefaults | protrftypes | prosrc |
probin | proconfig | proacl

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

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

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.