Hello!

This time I’ll tell you not about using Python or another CSS / HTML trick and, alas, not about how I ported Wangers for 5 years, but about one important aspect of writing extensions for the great PostgresSQL database.

In fact, there are already quite a few articles on how to write an extension for Postgres in C (for example, this one ), including in Russian . But, as a rule, they describe fairly simple cases. In these articles and instructions, the authors implement the functions that receive input data, somehow process them, and then return a single number, string, or user-defined type. They do not have explanations of what to do if you need to run from the C code using a regular table that exists in the database or an index.

Tables from C can be accessed through a well-described but slow SPI (Server Programming Interface) , there is also a very complicated way, through buffers, and I will tell you about a compromise version. Under the cut, I tried to give code examples with detailed explanations.

The basics

I assume that you have already written your unpretentious functions, and have seen that they are declared in a clever way:

Datum to_regclass(PG_FUNCTION_ARGS); 

and just so you can not call such a function. Let's take a closer look at the example of the function to_regclass :

 Datum my_index_oid_datum = DirectFunctionCall1(to_regclass, CStringGetDatum("my_index")); Oid my_index_oid = DatumGetObjectId(my_index_oid_datum); 

In this code, I call the function to_regclass , using a macro, to convert the name of the database object (index, table, etc.) to its Oid (unique number in the directory). This function has only one argument, so the macro with the speaking name DirectFunctionCall1 has a one at the end. In the include / fmgr.h file , such macros are declared with up to 9 arguments. The arguments themselves are always represented by the generic type Datum , which is why the C string “my_index” is cast to Datum through the function CStringGetDatum . Postgresovye functions in principle communicate through Datum , so the result of our macro will be a value of type Datum . After that, you need to convert it to the Oid type using the DatumGetObjectId macro. All possible conversion options need to look here: include / postgres.h .
I will also clarify one more thing: in C, it is customary to declare variables at the beginning of a block, but for clarity, I declare them where I begin to use them. In practice, do not write.
')

Access to the table

Immediately explain why SPI is slow. The fact is that a request made using SPI goes through all the stages of parsing and planning. In addition, it seems uninteresting to me to go in a simple way, where there is no magic.
The next thing I want to say is that these names are confused in Postgres! Due to the long history of the project, there are many strange names in the code for the types, methods and functions.
Before reading further, it is advisable to have a basic understanding of MVCC Postgres. All the examples below work only as part of an already created transaction, and if you suddenly climbed to where it is not yet, you will need much more magic.

So, suppose we want to just run through the table, which contains two fields: int id and text nickname, and output them to the log. First we need to open the heap (table) with a certain lock:

 RangeVar *table_rv = makeRangeVar("public", "my_table", -1); Relation table_heap = heap_openrv(table_rv, AccessShareLock); 

Instead of the heap_openrv function, you can use heap_open , in which the first argument is the table Oid (can be obtained using the function in the first part of the article). I think the assignment of RangeVar is intuitive, but we ’ll dwell on locks. The types of locks are declared in the include / storage / lockdefs.h file with fairly clear comments. You can see this information in the table:

AccessShareLock	SELECT
RowShareLock	SELECT FOR UPDATE / FOR SHARE
RowExclusiveLock	INSERT, UPDATE, DELETE
ShareUpdateExclusiveLock	VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY
Share lock	CREATE INDEX (WITHOUT CONCURRENTLY)
ShareRowExclusiveLock	like EXCLUSIVE MODE, but allows ROW SHARE
ExclusiveLock	blocks ROW SHARE / SELECT ... FOR UPDATE
AccessExclusiveLock	ALTER TABLE, DROP TABLE, VACUUM FULL, and unqualified LOCK TABLE

Since we only wanted to go over the plate, that is, execute, SeqScan , select AccessShareLock . After we opened the heap, we need to initialize the table scan process:

 HeapScanDesc heapScan = heap_beginscan(sr_plans_heap, SnapshotSelf, 0, (ScanKey) NULL); 

As expected, our heap is the first argument of this function, but the SnapshotSelf requires clarification. The MVCC job in Postgres assumes that at any given time there can be several versions of one row of the table, and it is in the snapshot (transaction snapshot) that we can see and which we cannot see. In addition to SnapshotSelf , i.e. current snapshot transactions, for example, there are SnapshotAny , substituting which, we would be able to see also all deleted and modified tuples (rows of the table). Other types can look at include / utils / tqual.h . The following arguments for heap_beginscan are the number of search keys (ScanKey) and the keys themselves. The search keys (ScanKey) are essentially conditions, i.e. what you write in WHERE . To work with heap search keys are not very necessary, because You can always check the conditions yourself in your code. But when searching by index, we are not going away from their initialization and use.

And now the most important thing is to be a cycle:

 Datum values[2]; bool nulls[2]; for (;;) { HeapTuple local_tuple; local_tuple = heap_getnext(heapScan, ForwardScanDirection); if (local_tuple == NULL) break; heap_deform_tuple(local_tuple, table_heap->rd_att, values, nulls); elog(WARNING, "Test id:%i nick:%s", DatumGetInt32(values[0]), TextDatumGetCString(values[1]) ); } 

In this loop, we call the function heap_getnext , with which we get the next tuple until we return a null pointer. The heap_getnext function receives our HeapScanDesc and scanning directions, for us it will be relevant two: direct scanning - ForwardScanDirection and reverse - BackwardScanDirection . Now we have to unpack the tuple and get access to its fields, for this we call heap_deform_tuple , where we transfer our tuple, after which its description (which we take from heap) and two arrays (one for values ​​and the other for determining NULL values). Next, using the functions already familiar to us, we transform the elements of the values ​​array (consisting of Datum ) to ordinary C types.

And now let's not forget to close our heap scan (tables) and close the heap itself:

 heap_endscan(heapScan); heap_close(sr_plans_heap, AccessShareLock); 

We close the heap with the same type of blocking that we opened it with.

Work with an index

The index search API will be similar to the heap search, but will only require more lines of code to initialize. In the code, we will try to display messages only for strings, where the first argument answers the main question of life, the universe and all that. As for heap, to begin with, we give a piece of code with all the preparatory work:

 RangeVar *table_rv = makeRangeVar("public", "my_table", -1); Relation table_heap = heap_openrv(table_rv, AccessShareLock); table_idx_oid = DatumGetObjectId(DirectFunctionCall1( to_regclass, StringGetDatum("my_table_idx") )); Relation table_idx_rel = index_open(table_idx_oid, AccessShareLock); indexScan = index_beginscan(table_heap, table_idx_rel, SnapshotSelf, 1, 0); ScanKeyData key; ScanKeyInit(&key, 1, BTEqualStrategyNumber, F_INT4EQ, Int32GetDatum(42)); index_rescan(indexScan, &key, 1, NULL, 0); 

So, to search by index, we are the same as in the previous example, we need to open the heap (table) with which this index is associated. With the help of to_regclass we find the oid for our index my_table_idx, then open it with the help of index_open , finding the Relation we need. After that, we initialize the index_beginscan index scanning process, here the main difference from heap_beginscan is that we will have 1 search key (ScanKey).
ScanKeyInit , as the name implies, initializes the key for the search. The first argument is the key itself (type ScanKeyData ), then we specify the sequence number of the argument that will be searched (numbered 1), then the search strategy goes. In fact, this is similar to the operator in the condition (other strategies can be found here include / access / startnum.h ), then we specify the oid directly of the function that will perform our comparison operation (these oid are declared in the include / utils / fmgroids.h file ) . And finally, our last argument is the Datum , which should contain the value that should be searched.

Next comes another new index_rescan function, and it serves to start a search by index. The index_beginscan alone is not enough here. At the input, this function receives a list of keys (we have only one), the number of these keys, after which the keys to sort and the number of keys to sort (they are used for the ORDER BY condition, which is not in this example). It seems that all the preparations have passed, and you can show the main loop, however, it will be very similar to what happened with heap:

 for (;;) { HeapTuple local_tuple; ItemPointer tid = index_getnext_tid(indexScan, ForwardScanDirection); if (tid == NULL) break; local_tuple = index_fetch_heap(indexScan); heap_deform_tuple(local_tuple, table_heap->rd_att, values, nulls); elog(WARNING, "Test id:%i nick:%s", DatumGetInt32(values[0]), DatumGetCString(PG_DETOAST_DATUM(values[1])) ); } 

Since now we are running on the index, rather than the heap itself, we get ItemPointer, a special pointer to the entry in the index (if you are interested in the details, refer to the relevant documentation www.postgresql.org/docs/9.5/static/storage-page -layout.html or directly to the include / storage / bufpage.h file using which we still need to get the tuple from heap. In this loop, index_getnext_tid is functionally similar to heap_getnext and only index_fetch_heap is added, and the rest is completely analogous.

To finish our operation, as you can guess, we will need to close the search by index, the index itself and the open heap:

 index_endscan(indexScan); index_close(table_idx_rel, heap_lock); heap_close(table_heap, heap_lock); 

Data change

So, we learned how to do SeqScan and IndexScan, i.e. Search our table and even use an index for this, but how can we add something to it now? For this we need the functions simple_heap_insert and index_insert .

Before changing something in the table and related indexes, they must be opened with the necessary locks, in the manner shown earlier, after which you can insert:

 values[0] = Int32GetDatum(42); values[1] = CStringGetDatum("First q"); tuple = heap_form_tuple(table_heap->rd_att, values, nulls); simple_heap_insert(table_heap, tuple); index_insert( table_idx_rel, values, nulls, &(tuple->t_self), table_heap, UNIQUE_CHECK_NO ); 

Here we do the reverse operation, i.e. from the arrays of values ​​and nulls we form a tuple,
after which we add it to the heap and then add the corresponding entry to the index. After previous explanations, this code should be clear to you.
To update tuple, do the following:

 values[0] = Int32GetDatum(42); replaces[0] = true; newtuple = heap_modify_tuple( local_tuple, RelationGetDescr(table_heap), values, nulls, replaces ); simple_heap_update(table_heap, &newtuple->t_self, newtuple); 

We have an array of Boolean variables replaces, where information is stored, which field has changed. After that, we create a new tuple based on the old one, but with our edits using heap_modify_tuple . And at the very end we perform the update itself simple_heap_update . Since we have a new tuple, and the old one is marked as deleted, we also need to add an entry to the index for the new tuple in the way that was shown earlier.

Now remove the tuple is not difficult with the function simple_heap_delete , and it does not require explanation. It should be noted that it is not necessary to delete the corresponding entry in the index, it will happen automatically when cleaning is performed by the VACUUM operation.

Total

We learned how to access the table from C code, including the index. I tried to describe each function and its purpose in as much detail as possible, but if something turned out to be incomprehensible, ask in the comments, I will try to answer and complete the article.