In PostgreSQL there is alignment padding of various datatypes which can increase (or decrease) the size of records (as stored on disk) in varying ways (see these links, 1 , 2 , 3 ): I searched for "Oracle alignement padding" (and also for SQL Server, MySQL and FirebirdSQL) and various combinations of these words and came up with one link of interest for Oracle (C struct related). For the other systems I found nothing of interest (mostly stuff like LPAD()/RPAD() or other presentation related material). I did find this by Eric Raymond - generic C struct material again. http://www.catb.org/esr/structure-packing/ which is also about C structs Note: **"should generalize to any compiled language with C-like structures."** (end of section 1. - Who should read this.) For a shorter read on the same topic, see here . Providing a "behind the scenes" remedy has been discussed (logical vs. physical order) on the PostgreSQL lists but has gone nowhere - see the discussions here and also this answer . So, as I understand it, this alignment issue is *_fundamental_* to C/C++ (and all compiled languages with structs - esr) and to the best of my knowledge, the kernels of all the systems I searched for are written in various combinations of C/C++. However, only PostgreSQL appears to be discussing the issue but not other systems. My question is that I'm wondering have all the other mainstream systems have implemented some sort of column rearrangement behind the scenes? I'd be interested in hearing how other systems have mitigated any problems caused by this if they haven't done some sort of column rearranging? EDIT: On searching further, I have found these two posts (1 , 2 ) about marshalling of C structs (which, if I've understood correctly) are the way that records move from disk to memory and back - the similarity between a C struct and a record structure being reasonably obvious. So, and this is again AIUI, you can either store C structs (or records) in a sort of way that directly maps to the processor memory alignment requirements (and "wastes" space) **OR** the struct can be marshalled (or serialised - v. similar) as a bit (or byte) stream and stored that way (less "waste" but more processor intensive). **But** this poses a further problem! The developers of PostgreSQL are very adept at C programming and would know about these techniques - so why would they not have done what (**apparently** AFAICS) other systems have done and removed this overhead?

This is my .c file

Datum show_line(PG_FUNCTION_ARGS)
{
    /* Get Input*/
    HeapTupleHeader tup_hdr;
    TupleDesc tup_desc;
    Oid tupType;
    int32 tupTypmod;
    bool isNull;
    // int tuplen;
    // bool *nulls;
    int i;
    tup_hdr = PG_GETARG_HEAPTUPLEHEADER(0);
    elog(NOTICE, "Get tup_hdr");
    tupType = HeapTupleHeaderGetTypeId(tup_hdr);
    tupTypmod = HeapTupleHeaderGetTypMod(tup_hdr);
    tup_desc = lookup_rowtype_tupdesc(tupType, tupTypmod);
    // tup_desc = lookup_rowtype_tupdesc(
    //     HeapTupleHeaderGetTypeId(tup_hdr),
    //     HeapTupleHeaderGetTypMod(tup_hdr));

    Datum value = GetAttributeByNum(tup_hdr, 5, &isNull);
    if (isNull)
    {
        elog(NOTICE, "Get value is NULL");
    }
    else
    {
        elog(NOTICE, "Get value %f", DatumGetFloat8(value));
    }

    elog(NOTICE, "Get tup_desc");
    elog(NOTICE, "get tupType %d", tupType);
    elog(NOTICE, "get tupTypmod %d", tupTypmod);
    if (tup_desc == NULL)
    {
        elog(NOTICE, "tup_desc is NULL");
        PG_RETURN_INT32(0);
    }

    int natts = tup_desc->natts;
    elog(NOTICE, "Get natts %d", natts);

    for (i = 0; i natts; i++)
    {
        Form_pg_attribute attr;
        char *name;
        elog(NOTICE, "Get attr %d, Step 1", i);
        attr = TupleDescAttr(tup_desc, i);
        elog(NOTICE, "Get attr %d, Step 2", i);
        name = NameStr(attr->attname);
        elog(NOTICE, "Attribute %d: %s", i, name);
    }
    PG_RETURN_INT32(0);
}

this is SQL file

CREATE
OR REPLACE FUNCTION show_line(iris) RETURNS int AS 'MODULE_PATHNAME',
'show_line' LANGUAGE C STRICT;

I can successfully compile and install it in Postgres. But when execute it, the psql will crash

test_db=# SELECT
    show_line(iris) as target
from
    iris
limit
    4;
NOTICE:  Get tup_hdr
NOTICE:  Get value 0.000000
NOTICE:  Get tup_desc
NOTICE:  get tupType 24617
NOTICE:  get tupTypmod -1
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>

It seems it crashed when run in

int natts = tup_desc->natts;

I follow the function in example composite_to_json in src/backend/utils/adt/json.c

I am trying to write a postgres stored procedure that can take a variable number of arguments of potentially different types (i.e. a heterogeneous list of arguments). As I learned from here , the heterogeneous variadic argument list is only possible in C. To learn how to write such a C function, I worked on a simple example that takes the heterogeneous list , converts each argument to a json string, and then returns the concatenated string. My code for this minimal example is listed below. I think I got the shape of this function right my mimicking how jsonb_build_object() works. But I can't figure out how to convert each argument value to json be and then turn it into a c-string. I tried to adapt the code of the to_json function (as indicated by //problem_lines marker below). But couldn't get the code to even compile. I am not very familiar with the internals of server side C programming with PostgreSQL (as of 16). *Could anyone with the expertise help explain how to make the conversion to json string work?* -- My code so far -- #include "postgres.h" #include "catalog/pg_type.h" #include "fmgr.h" #include "funcapi.h" //#include "utils/array.h" // for var_concat //#include "common/jsonapi.h" #include "utils/json.h" #include "utils/jsonb.h" #include "utils/jsonfuncs.h" PG_MODULE_MAGIC; // take a heterogeneous variadic list of values; convert them into json string, and then return the concatenated string Datum var_concat_worker(int nargs, const Datum *args, const bool *nulls, const Oid *types, bool absent_on_null, bool unique_keys) { int i; Jsonb *jb; char *strVal; char *result; int bufLen = 16; result = (char*)malloc(bufLen*sizeof(char)); Datum val; if (nargs flinfo, 0); JsonTypeCategory tcategory; Oid outfuncoid; if (val_type == InvalidOid) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("could not determine input data type"))); json_categorize_type(val_type, true, &tcategory, &outfuncoid); // jb = datum_to_jsonb(val, tcategory, outfuncoid); strVal = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); if (strlen(strVal) + strlen(result) >= bufLen) { bufLen = bufLen * 2; result = realloc(result, bufLen); } strcat(result, strVal); } PG_RETURN_CSTRING(result); } /* SQL function var_concat(variadic "any") */ Datum var_concat(PG_FUNCTION_ARGS) { Datum *args; bool *nulls; Oid *types; /* build argument values to build the object */ int nargs = extract_variadic_args(fcinfo, 0, true, &args, &types, &nulls); if (nargs < 0) PG_RETURN_NULL(); PG_RETURN_DATUM(var_concat_worker(nargs, args, nulls, types, false, false)); } Related: https://dba.stackexchange.com/questions/264327

I'm making a FDW (Foreign Data Wrapper) in PostgreSQL for myself using C. It seems to be working rudimentary, but I have one problem. When I pass it a query like the following via psql:

select * from FOREIGN_TABLE_A where COL1=1 and COL2=1;

...then the FDW ignores the second search predicate (COL2=1). How can I make PostgreSQL use my second search predicate? I tried debugging but PostgreSQL never returned any useful results. Are there any rules for enabling PostgreSQL to use multiple predicates in a FDW in GetForeignRelSize(), GetForeignPaths() or GetForeignPlan()?

I am interested in how PostgreSQL's extension system is implemented. How can a postgres server call user-defined C which is built separately from the server. ---- This question comes from [Reddit u/lllouiskuang on /r/postgresql](https://www.reddit.com/r/PostgreSQL/comments/14dyipc/how_is_postgresqls_extension_system_implemented/)

Given the two definitions below, is there any difference?

CREATE FUNCTION foo(OUT foobar int4)
RETURNS SETOF int4
AS 'MODULE_PATHNAME', 'foo'
LANGUAGE C STRICT VOLATILE;

And,

CREATE FUNCTION foo()
RETURNS TABLE(foobar int4)
AS 'MODULE_PATHNAME', 'foo'
LANGUAGE C STRICT VOLATILE;

That is to say is there a difference between the _"Result data type"_ as displayed with \df between * TABLE(foobar integer) * and, SETOF interger where the Argument data types includes OUT foobar integer

I cannot get environment variable in my PostgreSQL C extension code. For example, this function always returns 111: #include "postgres.h" #include "fmgr.h" #include PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(myinc); Datum myinc(PG_FUNCTION_ARGS) { int32 arg = PG_GETARG_INT32(0); char* envar = getenv("MYINC"); if (envar) { PG_RETURN_INT32(arg + atoi(envar)); } else { PG_RETURN_INT32(111); } } While this C program works as expected, it prints whatever MYINC is: #include #include int main() { printf("MYINC: %s", getenv("MYINC")); return 0; }

As far as I understand, SPI_getbinval returns a pointer into the passed row. That is, the following would be unsafe:

dat = SPI_getbinval(SPI_tuptable->vals, SPI_tuptable->tupdesc, 1, &isnull);
SPI_finished();
if (isnull)
    PG_RETURN_NULL();
// Unsafe since SPI_finished() deallocates SPI_tuptable
return dat;

The SPI interface provides the utility SPI_copytuple. Is the following safe? Is there any problem with using SPI_tuptable->tupdesc? Moreover, is there a more efficient way to do this directly with the returned Datum instead of copying the entire HeapTuple?

// We copy the whole tuple into the outer context.  Better way to copy an individual Datum?
dat = SPI_getbinval(SPI_copytuple(SPI_tuptable->vals), SPI_tuptable->tupdesc, 1, &isnull);
SPI_finished();
if (isnull)
  PG_RETURN_NULL();
return dat;

[Researching this question](https://dba.stackexchange.com/q/301920/2639) , I see there is a value for SetFunctionReturnMode called SFRM_Materialize_Preferred. What is this? Can this be used to write more performant functions? There are multiple occurrances of [SRFM_Materialize in contrib/](https://github.com/search?q=org%3Apostgres+SFRM_Materialize+contrib%2F&type=code) but [none of SRFM_Materalize_Preferred](https://github.com/search?q=org%3Apostgres+SFRM_Materialize_Preferred+contrib%2F&type=code)

When you're extending [PostgreSQL in C](https://www.postgresql.org/docs/current/xfunc-c.html) , how does one introspect/debug/dump a variable? Is there a method other than returning a custom result set to the client? Something like a printf that I can receive in a log on the server, or on the client?

In PostgreSQL there is a field called allowedMode called SFRM_Materialize_Random > The Tuplestore must be created with randomAccess = true if SFRM_Materialize_Random is set in allowedModes, but it can (and preferably should) be created with randomAccess = false if not. In which case is this set?

[Currently the docs says,](https://www.postgresql.org/docs/14/xfunc-c.html#XFUNC-C-RETURN-SET) > There are two ways you can build a composite data value (henceforth a “tuple”): you can build it from an array of Datum values, or from an array of C strings that can be passed to the input conversion functions of the tuple's column data types. When it comes to returning that "tuple" it says, > One way to declare this function in SQL is: > >

> CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);
>
> CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
>     RETURNS SETOF __retcomposite
>     AS 'filename', 'retcomposite'
>     LANGUAGE C IMMUTABLE STRICT;
>

> > A different way is to use OUT parameters: > >

> CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
>     OUT f1 integer, OUT f2 integer, OUT f3 integer)
>     RETURNS SETOF record
>     AS 'filename', 'retcomposite'
>     LANGUAGE C IMMUTABLE STRICT;
>

> > Notice that in this method the output type of the function is formally an anonymous record type. But is there a way to put the contents of __retcomposite in C too? It seems kind of sloppy to have to write/maintain the prototype for the function in C, and in another language (SQL). To expand a bit, [there is a hook when you load the .so into the database with _PG_init](https://stackoverflow.com/a/42579770/124486) . Is there no way to write my function signature there. It seems redundant to have to do something [like this](https://github.com/EvanCarroll/pg-dump-fcinfo/blob/main/src/dump_fcinfo.c#L22) where I tell C what all my types are (the initial defaults) when I then have to tell SQL [the very same thing](https://github.com/EvanCarroll/pg-dump-fcinfo/blob/main/dump_fcinfo--0.0.1.sql#L2) .

PostgreSQL documents it's [Set Returning Functions with C extensions like this](https://www.postgresql.org/docs/current/xfunc-c.html) , > C-language functions have two options for returning sets (multiple rows). > * In one method, called **ValuePerCall mode**, a set-returning function is called repeatedly (passing the same arguments each time) and it returns one new row on each call, until it has no more rows to return and signals that by returning NULL. The set-returning function (SRF) must therefore save enough state across calls to remember what it was doing and return the correct next item on each call. > * In the other method, called **Materialize mode**, an SRF fills and returns a tuplestore object containing its entire result; then only one call occurs for the whole result, and no inter-call state is needed. If you're optimizing for execution speed, which is faster **ValuePerCall** or **Materialize** mode? Obviously Materialize mode will take more memory but it seems like it should faster if your goal is to return a whole set, I don't see that documented anywhere though. ---- These seem to be documented in the code as [Set Returning Function Mode](https://doxygen.postgresql.org/execnodes_8h.html#ac46fbb67a00d131ce2173bc6b85164f8) SFRM_ValuePerCall and SFRM_Materialize.

I'm looking to get into writing extension in C. I want to better understand what this argument is and how they get changed in different invocations. Is there anything that can dump this argument out or a method to debug the fcinfo argument? In [this question](https://dba.stackexchange.com/q/302049/2639) , I learned that it's actually a pointer to a FunctionCallInfoBaseData struct, but is there an easy way to dump that on function invocation?

PostgreSQL Documents all their C functions with a "V1" interface, but they don't actually show what they get,

PG_FUNCTION_INFO_V1(add_one);

Datum
add_one(PG_FUNCTION_ARGS)
{
    int32   arg = PG_GETARG_INT32(0);

    PG_RETURN_INT32(arg + 1);
}

In the above what does PG_FUNCTION_ARGS declare the function to accept? Many V1 function seem to have a fcinfo magically show up in the definition, I'm guessing it's brought in here but what is it and does anything else get brought in with this macro?

I have a sensor which is generating a data vector let's say every second (adjustable to up to 100 vectors per second) at a remote location. The location got 4G modem to send the data. Due to power limitations I have chosen to use a Raspberry Pi 3+ to receive the data from the sensor, decode it and send it to my VPS server. On my server running Ubuntu 18.04 I have installed TimescaleDB on top of PostgreSQL which is a good combination to handle time-series data and I would like to store the sensor data in there and publish then on my website. I'm keen to hear your thoughts of how the data flow could look like and I have some questions. 1) What would be the best way of inserting the sensor data from the RPi into the server DB? Is it save to insert them directly from the RPi using the libpq library in my C software or should I transport the data to the server in a different way and then insert the data into the DB at the server side? 2) At the RPi side, I would like to have a local buffer to prevent losing data if the connection to the server breaks. I could simply use a circular buffer in my C program or a named pipe, or I could install PostgreSQL/TimescaleDB locally, insert the data there and then use postgres_fdw or dblink to transfer the data from there to my server DB and delete the entries from my local DB. What would you suggest?

private void button2_Click(object sender, EventArgs e) { con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "delete from MYDB where First name='" + first.Text + "'"; cmd.ExecuteNonQuery(); con.Close(); disp_data(); MessageBox.Show("Contact deleted successfully"); }

I’ve written a C program which does the following when it starts: 1. Connects to a remote Linux MySQL server over the network 2. Creates a new, uniquely-named database 3. Sends a “USE _____” command to specify we want to use that database 4. Creates a single table within the database Here’s examples of the actual commands my code is sending: 1. (no command sent to initiate connection) 2. "CREATE DATABASE IF NOT EXISTS myDB_2019424_205718;" 3. “USE myDB_2019424_205718;” 4. “CREATE TABLE IF NOT EXISTS myTable ( timestamp VARCHAR(15) , Column1 VARCHAR(8) , Column2 VARCHAR(4) , Column3 VARCHAR(4) );” All of the above works great. The trouble is, once the database is set up and ready, I need the C program to crunch a lot of numbers on-the-fly, and periodically send “INSERT INTO” commands to the server. Here’s the first “INSERT INTO” command my code sends: INSERT INTO myTable VALUES ( '1556139439', NULL, NULL, NULL ); When my code sends its first “INSERT INTO” command, the server immediately sends back an error and my code reports “****** MySQL server has gone away ******” I Googled that error, of course, and I see from posts like this that the two possible culprits are a low timeout or small packet threshold. So I’ve reset those to maximum on my server: mysql> SELECT @@wait_timeout; +----------------+ | @@wait_timeout | +----------------+ | 28800 | +----------------+ 1 row in set (0.00 sec) mysql> SELECT @@max_allowed_packet; +----------------------+ | @@max_allowed_packet | +----------------------+ | 107373568 | +----------------------+ 1 row in set (0.00 sec) mysql> But still no luck. Worse, when I examine /var/log/mysql/error.log after a failed session, I see no error message recorded. One more clue… I’ve tried manually logging into the MySQL server and pasting these commands in one-by-one. All commands are accepted on the manual interface. There’s something buggy when my code sends the “INSERT INTO” command, and I can’t figure out what. Has anyone seen this before? Some system information… My C code is running on an Ubuntu container, version 16.04.3 LTS. I’ve installed the latest mysql library. The SQL server is running on an identical container; the actual version of MySQL Server is Ver 14.14 Distrib 5.7.25. Sooo… what could be the problem here? I’ve included an excerpt of my C code below. (The full code is too long and confusing to include here.) Any suggestions or advice will be appreciated.

*How can I read a json/jsonb field in a PostgreSQL server side function?* I am trying to write a server side function using C and SPI for PostgreSQL 11. The json field in mind is simple and contain only unnested key (text) and value (float8) pairs. I'd like to read these key value pairs into my C code and process it. But after much searching on Google and here at DB.SE, I can't find any documentation on this. The results I got are for using json/jsonb on the client side, e.g.: https://dba.stackexchange.com/questions/102906 https://stackoverflow.com/questions/32458524 I see there is PG_GETARG_JSONB, e.g. here . But it is buried beneath other things and seems to require an external library . Any pointers are appreciated.