src/include/access/README.caql_usage

CaQL Usage
==========

CaQL, the Catalog Query Language, is a simplified SQL language and C
api for catalog operations.  This api unifies and supplants the
existing heap, index, systable, and syscache api's.  In addition, this
api can automatically use and enforce the correct locking and snapshot
modes for the specified catalog operation.  The CaQL conversion
project is the initial phase of the Unified Catalog Service, an
initiative which attempts to address many deficiencies in catalog
correctness, stability, extensibility, scalability, and performance.
While the basic api is simple, it has sufficient extensions to support
legacy requirements for unusual locking or index operations.  In this
document, the camelcase "CaQL" refers to the SQL-like language, while
the lowercase "caql" prefix describes functions in the catquery.c
module.

The C api supports a variety of methods that emulate the existing
interfaces, plus additional functionality.  These functions take two
common arguments: a cqContext and a cq_list.

A cqContext encapsulates all the scan, locking, and snapshot state for
a query.  If you supply a null cqContext, catquery will manufacture a
default context with the appropriate locking and snapshot modes.  caql
also has several functions to initialize the context to use special
locking and snapshot modes to support legacy requirements.

A cq_list is a CaQL statement plus a variable-length list of "Datum"
arguments, bound in a cql() macro declaration.  The cq_list must have
sufficient arguments to match the number of bind values (bindvals) in
the WHERE clause.

CaQL - the language
===================

CaQL is a small subset of SQL.  The following operations are supported:

SELECT * FROM <tablename> [WHERE <colname> = <bindval> [ AND ...]] [FOR UPDATE]
SELECT COUNT(*) ...
SELECT <colname> ...

DELETE FROM <tablename> [WHERE <colname> = <bindval> [ AND ...]] 

For a SELECT statement, the "FOR UPDATE" clause changes the default
locking from AccessShare to RowExclusive.  The SELECT list only
supports "*", "COUNT(*)", or a single column name (where the column is
of type Oid, Name, or Text).  The option WHERE clause supports an
ANDed list of "<colname> = <bindval>" expressions.  In the current
implementation, only five bind values (labeled ":1" to ":5") are
supported.

Extended Usage - sorting/ordering
=================================

Clients may use the optional "ORDER BY <colname> [, <colname...]"
clause to specify an ordering when iterating over a set of tuples.  If
no ordering is specified the tuple output order is random.  The ORDER
BY clause immediately precedes the FOR UPDATE (or terminates the
SELECT statement if FOR UPDATE is not specified).  CaQL also supports
GreaterThan/LessThan comparison in the WHERE clause for this case, eg
<,>,<=,>=.  In the current implementation, ordering requires the
existence of a matching index.  Note that SQL-style
ASCENDING/DESCENDING is not necessary: clients use caql_getnext() or
caql_getprev() to ascend or descend as they fetch tuples.

Sample Usage
============

    /* 
     * Example 1: find the first tuple for resource type and 
     * lock it for update 
     */

    HeapTuple   tuple;

    tuple = caql_getfirst(
            NULL,
            cql("SELECT * FROM pg_resourcetype"
                " WHERE resname = :1 FOR UPDATE",
                CStringGetDatum(pNameIn)));


    /* 
     * Example 2: build a context using an existing open relation, and see
     * if any queues match the supplied name 
     */
    pg_resqueue_rel = heap_open(ResQueueRelationId, RowExclusiveLock);
    if (caql_getcount(
            caql_addrel(cqclr(&cqc), pg_resqueue_rel),
            cql("SELECT COUNT(*) FROM pg_resqueue WHERE rsqname = :1",
                CStringGetDatum(stmt->queue))))
    {
        ereport(ERROR,
                (errcode(ERRCODE_DUPLICATE_OBJECT),
                 errmsg("resource queue \"%s\" already exists",
                        stmt->queue)));
    }


    /* Example 3: drop the extended attributes for this queue */
    int numDel;
    numDel =
        caql_getcount(
                NULL,
                cql("DELETE FROM pg_resqueuecapability WHERE resqueueid = :1",
                    ObjectIdGetDatum(queueid))
                ); 


    /* 
     * Example 4: build a context using an existing open relation, 
     * and iterate over all of the matching tuples 
     */
    gp_db_interface_rel = heap_open(GpDbInterfacesRelationId, AccessShareLock);

    pcqCtx = caql_beginscan(
            caql_addrel(cqclr(&cqc), gp_db_interface_rel),
            cql("SELECT * FROM gp_db_interfaces "
                " WHERE dbid = :1 ",
                ObjectIdGetDatum(c->dbid)));

    while (HeapTupleIsValid(tuple = caql_getnext(pcqCtx)))
    {
     ...
    }

    /* Finish up scan and close appendonly catalog. */
    caql_endscan(pcqCtx);
    heap_close(gp_db_interface_rel, AccessShareLock);


    /* 
     * Example 5: select only the dbid column from first matching tuple 
     * of this table, and set a count of how many tuples matched (0, 1, >1) 
     */

    Oid dbid;
    int fetchCount;

    dbid = caql_getoid_plus(
            NULL,
            &fetchCount,
            NULL,
            cql("SELECT dbid FROM gp_segment_configuration "
						" WHERE content = :1 "
						  " AND role = :2 ",
                Int16GetDatum(contentid),
						CharGetDatum('m')));


    /* 
     * Example 6: select only the constraint name from first matching tuple 
     * of this table
     */

	char	   *cname;

    cname = caql_getcstring(
             NULL,
          	   cql("SELECT conname FROM pg_constraint "
				   " WHERE oid = :1 ",
				  ObjectIdGetDatum(constraintId)));


Constructing and Modifying the caql context
===========================================

Almost all of the caql functions take a cqContext (caql context)
argument.  This context maintains information on table locking,
visibility, and fetch iteration status.  If a null context argument is
supplied, the caql function will internally generate a default
context.  While functions like caql_getfirst() and caql_getoid() only
have a context for the duration of their execution, The
caql_beginscan() function returns an initialized context which is a
required argument for subsequent caql_getnext() and caql_endscan()
functions.  

The most basic modification to the context is to add an existing,
open, locked relation.  If this relation is not supplied, the caql
function will open and lock the tables specified in the cql() query,
and close them at the end of execution (or at caql_endscan() for an
iterator).  If the relation is supplied using caql_addrel(), the caql
function will select tuples from the supplied relation, and leave it
open at the end of execution.  This pattern is common when multiple
SELECTs and UPDATEs are performed against the same table in a single
transaction.  The basic way to do this is:

    cqContext   cqc; /* declare the context on the stack */

    cqclr(&cqc); /* clear the context */

	caql_addrel(&cqc, some_open_relation); /* add an open relation */

However, since the context modification functions return a pointer to
the modified cqContext, you can nest or "chain" them:

    /* 
     * clear the context, add open relation pg_resqueue_rel, and supply it
     * to caql_getcount() 
     */
    numDel =
        caql_getcount(
            caql_addrel(cqclr(&cqc), pg_resqueue_rel),
            cql("DELETE FROM pg_resqueue WHERE rsqname = :1",
                CStringGetDatum(stmt->queue))))

Additional context modification function are caql_indexOK(),
caql_lockmode(), caql_snapshot(), and caql_syscache().  These
functions mainly exist to support legacy code requirements for the
initial CaQL conversion, enforcing particular patterns of index,
heapscan or syscache usage.  As the Unified Catalog Service evolves to
an external service, usage of these functions may be deprecated, as
the underlying implementation will be very different.  Best practice
is to avoid their usage if possible.

CaQL Context Lifetime
=====================

The caql context is valid from caql_beginscan() to caql_endscan().
After caql_endscan(), any open tables are closed, and the fetched
tuple is freed.  However, if a context pointer is supplied to
caql_getfirst() or caql_getfirst_only(), the tuple is copied out, so
the caql_cntext contains a valid pointer to the last fetched tuple
*after* the call.  In addition, if the relation is supplied using
caql_addrel(), then the caql context relation pointer remains valid
until heap_close() is called for the supplied relation.

INSERT, UPDATE, DELETE
======================

CaQL also supports INSERT, UPDATE, and DELETE operations in a
cursor-like context.  The caql context contains a pointer to the last
tuple fetched.  Several functions can operate upon the last, or
"current" tuple:

 void caql_delete_current(cqContext *pCtx);
 void caql_update_current(cqContext *pCtx, HeapTuple tup);
 HeapTuple caql_modify_current(cqContext *pCtx, Datum *replValues,
		   					 bool *replIsnull,
							 bool *doReplace);

caql_delete_current() performs a simple_heap_delete() on the current
tuple, and caql_update_current() performs a simple_heap_update() plus
CatalogUpdateIndexes().  caql_modify_current() peforms
heap_modify_tuple() on current.

CaQL supports an "INSERT" statement in caql_beginscan() which obtains
the correct table locking and maintains it until caql_endscan(), eg:

	/* open pg_proc_callback */
	pcqCtx = caql_beginscan(
			NULL,
			cql("INSERT INTO pg_proc_callback ",
				NULL));

	/* Build the tuple and insert it */
	nulls[Anum_pg_proc_callback_profnoid - 1]	  = false;
	nulls[Anum_pg_proc_callback_procallback - 1]  = false;
	nulls[Anum_pg_proc_callback_promethod - 1]	  = false;
	values[Anum_pg_proc_callback_profnoid - 1]	  = ObjectIdGetDatum(profnoid);
	values[Anum_pg_proc_callback_procallback - 1] = ObjectIdGetDatum(procbk);
	values[Anum_pg_proc_callback_promethod - 1]	  = CharGetDatum(promethod);

	tup = caql_form_tuple(pcqCtx, values, nulls);
	
	/* Insert tuple into the relation */
	caql_insert(pcqCtx, tup);  /* implicit update of index as well */

	caql_endscan(pcqCtx);

Note that the INSERT statement does not have a VALUES clause, or take
any arguments.  The actual insertion happens when a tuple is supplied
to caql_insert().  The caql_form_tuple() performs a heap_form_tuple
for the table specified by INSERT, and caql_insert() performs a
simple_heap_insert() and CatalogUpdateIndexes().  The caql_insert()
statement does not require the usage of the cql("INSERT INTO...")
statement -- it also works with caql contexts from 
cql("SELECT ... FOR UPDATE"), eg:

	pg_type_desc = heap_open(TypeRelationId, RowExclusiveLock);

	/* initialize a caql context for the subsequent INSERT and
	   UPDATE operations after the caql_getfirst()  
	   */
	pcqCtx = caql_addrel(cqclr(&cqc), pg_type_desc);

	tup = caql_getfirst(
			pcqCtx,
			cql("SELECT * FROM pg_type "
				" WHERE typname = :1 "
				" AND typnamespace = :2 "
				" FOR UPDATE ",
				CStringGetDatum((char *) typeName),
				ObjectIdGetDatum(typeNamespace)));

	if (HeapTupleIsValid(tup))
	{
		/*
		 * Okay to update existing shell type tuple
		 */
		tup = caql_modify_current(pcqCtx,
								  values,
								  nulls,
								  replaces);

		caql_update_current(pcqCtx, tup);
		/* and Update indexes (implicit) */
	}
	else
	{
		tup = caql_form_tuple(pcqCtx, values, nulls);
		/* Insert tuple into the relation */
		typeObjectId = caql_insert(pcqCtx, tup);
		/* and Update indexes (implicit) */
	}

	heap_close(TypeRelationId, RowExclusiveLock);

In this example, the caql context is "preloaded" with a valid, open,
relation, and a single tuple is fetched.  Since caql_getfirst() is
used, the context has a valid pointer to a copy of a tuple after the
call.  If the tuple is valid, it is updated, else a new tuple is
constructed and inserted into the open table.

Operational Details
===================

The calico.pl module extracts the CaQL statements from the cql()
macros in the C source files and generates a file called catquery.c.
This file contains a set of "basic" functions which perform the
underlying primitive database operations for the associated CaQL
statement.  calico.pl uses gperf, the gnu perfect hash utility, to
construct a static dispatch table that maps the CaQL statements to the
basic functions.  At runtime, the caql functions hash the cql()
statements to find and execute the associated basic database
functions.  All of the basic functions are pre-constructed during the
initial build and compilation of the database engine -- using a novel
CaQL statement at runtime results in an error.  The make system also
constructs caql.md5, the md5 hashes for the c source files used to
generate catquery.c.  The installcheck "caql" test compares the md5
signatures for the current set of sources with the historic signatures
for the catquery source files to ensure that the current postgres
executable is up-to-date.

Painful SysCache Brain Damage
=============================

I had to add some functions to handle SysCache routines that didn't
easily map into beginscan/getnext/endscan.

/* retrieve the last (current) tuple -- really only useful for 
 * caql_get_attname_scan()
 */
HeapTuple caql_get_current(cqContext *pCtx);

/*
 * adapted from original lsyscache.c/get_attnum()
 *
 *		Given the relation id and the attribute name,
 *		return the "attnum" field from the attribute relation.
 *
 *		Returns InvalidAttrNumber if the attr doesn't exist (or is dropped).
 */
AttrNumber caql_getattnumber(Oid relid, const char *attname);


/* ----------------------------------------------------------------
 * caql_getattname()
 *
 * The equivalent of SearchSysCacheCopyAttName - 
 * caql_getfirst(pCtx,	
 *    "SELECT * FROM pg_attribute
 *     WHERE attrelid = :relid
 *     AND attname = :attname
 *     AND attisdropped is false"
 *    );
 *
 * That is, find the existing ("undropped") attribute and return
 * a copy.
 * NOTE: need to be careful if this pCtx is used for update, as this
 * function "simulates" the ATTNAME cache lookup.
 * ----------------------------------------------------------------
 */
HeapTuple caql_getattname(cqContext *pCtx, Oid relid, const char *attname);

/* ----------------------------------------------------------------
 * caql_getattname_scan()
 *
 * The equivalent of SearchSysCacheAttName - 
 * caql_beginscan(pCtx,	
 *    "SELECT * FROM pg_attribute
 *     WHERE attrelid = :relid
 *     AND attname = :attname
 *     AND attisdropped is false"
 *    );
 *
 * That is, find the existing ("undropped") attribute and return
 * a context where the tuple is already fetched.  Retrieve the tuple
 * using caql_get_current()
 * NOTE: this is hideous.  My abject apologies.
 * NOTE: need to be careful if this pCtx is used for update, as this
 * function "simulates" the ATTNAME cache lookup.
 * ----------------------------------------------------------------
 */
cqContext *
caql_getattname_scan(cqContext *pCtx0, Oid relid, const char *attname)


/* catcache list-search interface.  Users of this must import catcache.h too */
/* 
 * In general, catquery will choose the syscache when the cql
 * statement contains an equality predicate on *all* of the syscache
 * primary key index columns, eg: 
 *
 *   cql("SELECT * FROM pg_amop WHERE amopopr = :1 and amopclaid = :2 ")
 *
 * will use the AMOPOPID syscache with index
 * AccessMethodOperatorIndexId.  However, the cql statement for a
 * list-search requires an equality predicate on a subset of the
 * initial columns of the index, with *all* of the index columns
 * specified in an ORDER BY clause, eg:
 *
 *   cql("SELECT * FROM pg_amop WHERE amopopr = :1 "
 *       " ORDER BY amopopr, amopclaid ")
 *
 * will use a syscache list-search if this cql statement is an
 * argument to caql_begin_CacheList().  However, the syscache will
 * *not* be used for this statement if it is supplied for
 * caql_beginscan(), since SearchSysCache() can only return (at most)
 * a single tuple.
 *
 * NOTE: caql_begin_CacheList() will assert (Insist!) at runtime if
 * the cql statement does not map to a syscache lookup.
 * NOTE: it may be possible to "collapse" this API into the existing
 * beginscan/getnext/endscan.
 */
struct catclist *caql_begin_CacheList(cqContext *pCtx, cq_list *pcql);
void caql_end_CacheList(struct catclist *cl)