Thread: Using timestamp(tz) in C functions

On 07/28/2016 02:15 PM, Keith Fiske wrote:
> Working on trying to get a C version of the maintenance function for my
> pg_partman extension working so I can hopefully make it more flexible
> and efficient.
>
> https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql
>
> Up until this point I've just been using SPI and the Datum conversion
> functions (DatumGetCString, DatumGetInt32) to get string and numeric
> data out of tables and things have been working fine. I'm at the first
> point in that function where I'll need to start dealing with timestamp data.
>
> https://gist.github.com/keithf4/81c32bf8b689c74b20c10ad8c91d45a3#file-pg_partman_bgw-c-L532
>
> There's what I've got working so far and links directly to the area
> where I'm having a problem. I found the DatumGetTimeTzADTP() function
> and the TimeTzADT data type looking through the source and that seems to
> be exactly what I'm looking for. However, when I get to the point of
> trying to simply use the time value in that variable (line 544),
> Postgres segfaults. So far I've just been trying to print the value out
> to the log to ensure I'm pulling it out correctly. The "time" value of
> the struct appears to be an int64, so I thought %ld would be the
> correct, but even using %d or %s fails.
>
> You can see in my original plpgsql function how I eventually intend to
> use the timestamp values. Here's a link directly to the equivalent section
>
> https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql#L139
>
> So, not sure if I'm even going about this the right manner for the way I
> intend to use the timestamp values. Still fairly new to C and getting
> use to postgres internals. Any help, or even an example of using
> timstamp data pulled from a table in C with SPI, would be great.

https://www.postgresql.org/docs/current/static/contrib-spi.html


F.34.5. moddatetime — Functions for Tracking Last Modification Time

moddatetime() is a trigger that stores the current time into a timestamp
field. This can be useful for tracking the last modification time of a
particular row within a table.

To use, create a BEFORE UPDATE trigger using this function. Specify a
single trigger argument: the name of the column to be modified. The
column must be of type timestamp or timestamp with time zone.

There is an example in moddatetime.example.


Can be seen here:


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/spi/moddatetime.c;h=c6d33b73557ff499faa0d475e032fd1f8164afbd;hb=HEAD
>
> Thanks!
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com


--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/28/16, Keith Fiske <keith@omniti.com> wrote:
> Working on trying to get a C version of the maintenance function for my
> pg_partman extension working so I can hopefully make it more flexible and
> efficient.
>
> https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql
>
> Up until this point I've just been using SPI and the Datum conversion
> functions (DatumGetCString, DatumGetInt32) to get string and numeric data
> out of tables and things have been working fine. I'm at the first point in
> that function where I'll need to start dealing with timestamp data.
>
> https://gist.github.com/keithf4/81c32bf8b689c74b20c10ad8c91d45a3#file-pg_partman_bgw-c-L532
>
> There's what I've got working so far and links directly to the area where
> I'm having a problem. I found the DatumGetTimeTzADTP() function and the
> TimeTzADT data type looking through the source and that seems to be exactly
> what I'm looking for. However, when I get to the point of trying to simply
> use the time value in that variable (line 544), Postgres segfaults. So far
> I've just been trying to print the value out to the log to ensure I'm
> pulling it out correctly. The "time" value of the struct appears to be an
> int64, so I thought %ld would be the correct, but even using %d or %s
> fails.
>
> You can see in my original plpgsql function how I eventually intend to use
> the timestamp values. Here's a link directly to the equivalent section
>
> https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql#L139
>
> So, not sure if I'm even going about this the right manner for the way I
> intend to use the timestamp values. Still fairly new to C and getting use
> to postgres internals. Any help, or even an example of using timstamp data
> pulled from a table in C with SPI, would be great.
>
> Thanks!
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>

I think it is not about timestamp(tz), but about usage of SPI.
Since DatumGetTimeTzADTP is just a macros implements type conversion
(declared at src/include/utils/date.h:60 (or 75)) you get segfault not
in it but when the code tries to get value by dereference pointer
(last_partition_timestamp->time).

Please, answer questions:
1. How many rows SPI_execute returns (value of "ret" variable)?
2. Is last_partition_timestamp != NULL? Where it points to?
3. Try to check SPI_result just after SPI_getbinval. Has it error code?

--
Best regards,
Vitaly Burovoy

On 7/29/16, Keith Fiske <keith@omniti.com> wrote:
> On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
> wrote:
>
>> On 7/28/16, Keith Fiske <keith@omniti.com> wrote:
>> > Working on trying to get a C version of the maintenance function for my
>> > pg_partman extension working so I can hopefully make it more flexible
>> > and
>> > efficient.
>> >
>> https://gist.github.com/keithf4/81c32bf8b689c74b20c10ad8c91d45a3#file-pg_partman_bgw-c-L532
>> >
>> > There's what I've got working so far and links directly to the area
>> > where
>> > I'm having a problem. I found the DatumGetTimeTzADTP() function and the
>> > TimeTzADT data type looking through the source and that seems to be exactly
>> > what I'm looking for. However, when I get to the point of trying to simply
>> > use the time value in that variable (line 544), Postgres segfaults. So far
>> > I've just been trying to print the value out to the log to ensure I'm
>> > pulling it out correctly. The "time" value of the struct appears to be
>> > an
>> > int64, so I thought %ld would be the correct, but even using %d or %s
>> > fails.
>> >
>> > Thanks!
>> >
>> > --
>> > Keith Fiske
>> > Database Administrator
>> > OmniTI Computer Consulting, Inc.
>> > http://www.keithf4.com
>> >
>>
>> I think it is not about timestamp(tz), but about usage of SPI.
>> Since DatumGetTimeTzADTP is just a macros implements type conversion
>> (declared at src/include/utils/date.h:60 (or 75)) you get segfault not
>> in it but when the code tries to get value by dereference pointer
>> (last_partition_timestamp->time).
>>
>> Please, answer questions:
>> 1. How many rows SPI_execute returns (value of "ret" variable)?
>> 2. Is last_partition_timestamp != NULL? Where it points to?
>> 3. Try to check SPI_result just after SPI_getbinval. Has it error code?
>
>
> It returns a single row. Here's an example of the results of the two
> queries that are run that lead to providing the timestamp value
>
> keith=# select partition_tablename from
> partman.show_partitions('partman_test.time_taptest_table', 'DESC') limit
> 1;      partition_tablename
> --------------------------------
>  time_taptest_table_p2016_08_02
> (1 row)
>
> keith=# select child_start_time from
> partman.show_partition_info('partman_test.time_taptest_table_p2016_08_02',
> '1 day', 'partman_test.time_taptest_table');
>     child_start_time
> ------------------------
>  2016-08-02 00:00:00-04
> (1 row)
>
> So there is valid data. As you're pointing out, this may just be a
> misunderstanding of how to actually use the Datum retrieval function and C
> in general. Appreciate the assistance.
>
> Keith
>

Please, add next lines (or debug this values via gdb) and post a result:

ret = SPI_execute(buf.data, true, 1);

ereport(NOTICE, (errmsg("query=%s", buf.data)));
ereport(NOTICE, (errmsg("ret=%i -- rows=%ju", ret, SPI_processed)));

last_partition_timestamp =
DatumGetTimeTzADTP(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc, 1, &isnull));

ereport(NOTICE, (errmsg("SPI_result=%d -- ptr=%p", SPI_result,
(void*)last_partition_timestamp )));

/* elog(LOG, "Just seeing if it's time partitioned: %ld",
last_partition_timestamp->time); --prevent segfaulting */


--
Best regards,
Vitaly Burovoy

On 7/29/16, Keith Fiske <keith@omniti.com> wrote:
> On Fri, Jul 29, 2016 at 11:49 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
> wrote:
>
>> On 7/29/16, Keith Fiske <keith@omniti.com> wrote:
>> > On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy <
>> vitaly.burovoy@gmail.com>
>> > wrote:
>> >
>> >> On 7/28/16, Keith Fiske <keith@omniti.com> wrote:
>> >> > Working on trying to get a C version of the maintenance function for
>> my
>> >> > pg_partman extension working so I can hopefully make it more
>> >> > flexible
>> >> > and
>> >> > efficient.
>> >> >
>> >>
>> https://gist.github.com/keithf4/81c32bf8b689c74b20c10ad8c91d45a3#file-pg_partman_bgw-c-L532
>> >> >
>> >> > There's what I've got working so far and links directly to the area
>> >> > where
>> >> > I'm having a problem. I found the DatumGetTimeTzADTP() function and
>> the
>> >> > TimeTzADT data type looking through the source and that seems to be
>> exactly
>> >> > what I'm looking for. However, when I get to the point of trying to
>> simply
>> >> > use the time value in that variable (line 544), Postgres segfaults.
>> So far
>> >> > I've just been trying to print the value out to the log to ensure
>> >> > I'm
>> >> > pulling it out correctly. The "time" value of the struct appears to
>> >> > be
>> >> > an
>> >> > int64, so I thought %ld would be the correct, but even using %d or
>> >> > %s
>> >> > fails.
>> >> >
>> >> > Thanks!
>> >> >
>> >> > --
>> >> > Keith Fiske
>> >> > Database Administrator
>> >> > OmniTI Computer Consulting, Inc.
>> >> > http://www.keithf4.com
>> >> >
>> >>
>> >> I think it is not about timestamp(tz), but about usage of SPI.
>> >> Since DatumGetTimeTzADTP is just a macros implements type conversion
>> >> (declared at src/include/utils/date.h:60 (or 75)) you get segfault not
>> >> in it but when the code tries to get value by dereference pointer
>> >> (last_partition_timestamp->time).
>> >>
>> >> Please, answer questions:
>> >> 1. How many rows SPI_execute returns (value of "ret" variable)?
>> >> 2. Is last_partition_timestamp != NULL? Where it points to?
>> >> 3. Try to check SPI_result just after SPI_getbinval. Has it error
>> >> code?
>> >
>> >
>> > It returns a single row. Here's an example of the results of the two
>> > queries that are run that lead to providing the timestamp value
>> >
>> > keith=# select partition_tablename from
>> > partman.show_partitions('partman_test.time_taptest_table', 'DESC')
>> > limit
>> > 1;      partition_tablename
>> > --------------------------------
>> >  time_taptest_table_p2016_08_02
>> > (1 row)
>> >
>> > keith=# select child_start_time from
>> >
>> partman.show_partition_info('partman_test.time_taptest_table_p2016_08_02',
>> > '1 day', 'partman_test.time_taptest_table');
>> >     child_start_time
>> > ------------------------
>> >  2016-08-02 00:00:00-04
>> > (1 row)
>> >
>> > So there is valid data. As you're pointing out, this may just be a
>> > misunderstanding of how to actually use the Datum retrieval function
>> > and
>> C
>> > in general. Appreciate the assistance.
>> >
>> > Keith
>> >
>>
>> Please, add next lines (or debug this values via gdb) and post a result:
>>
>> ret = SPI_execute(buf.data, true, 1);
>>
>> ereport(NOTICE, (errmsg("query=%s", buf.data)));
>> ereport(NOTICE, (errmsg("ret=%i -- rows=%ju", ret, SPI_processed)));
>>
>> last_partition_timestamp =
>> DatumGetTimeTzADTP(SPI_getbinval(SPI_tuptable->vals[0],
>> SPI_tuptable->tupdesc, 1, &isnull));
>>
>> ereport(NOTICE, (errmsg("SPI_result=%d -- ptr=%p", SPI_result,
>> (void*)last_partition_timestamp )));
>>
>> /* elog(LOG, "Just seeing if it's time partitioned: %ld",
>> last_partition_timestamp->time); --prevent segfaulting */
>>
>>
>> --
>> Best regards,
>> Vitaly Burovoy
>>
>
> From the postgresql logs:
>
> 2016-07-29 11:59:39 EDT [] [2021]: [10-1] user=,db=,e=00000 LOG:  Just
> checking that this thing is working. Loop: 0, parent_table:
> partman_test.time_taptest_table, partition_type: time, partition_interval:
> 00:15:00, control: col3, premake: 4, datetime_string: %YYYY_MM_DD_HH24MI,
> undo_in_progress: 0, sub_partition_set_full: 0, epoch: 0,
> infinite_time_partitions: 0
> 2016-07-29 11:59:39 EDT [] [2021]: [11-1] user=,db=,e=00000 NOTICE:
> query=SELECT child_start_time FROM
> partman.show_partition_info('partman_test.time_taptest_table_p2016_07_29_1245',
> '00:15:00', 'partman_test.time_taptest_table')
> 2016-07-29 11:59:39 EDT [] [2021]: [12-1] user=,db=,e=00000 NOTICE:  ret=5
> -- rows=1
> 2016-07-29 11:59:39 EDT [] [2021]: [13-1] user=,db=,e=00000 NOTICE:
> SPI_result=0 -- ptr=0x1dbc7bd713b00
> 2016-07-29 11:59:41 EDT [] [2012]: [10-1] user=,db=,e=00000 LOG:  worker
> process: pg_partman dynamic background worker (dbname=keith) (PID 2021) was
> terminated by signal 11: Segmentation fault
>
> FYI, Also got these notices when compiling:
>
> gcc -Wall -Wmissing-prototypes -Wpointer-arith
> -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute
> -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
> -g -O2 -fpic -I. -I./ -I/opt/pgsql953/include/server
> -I/opt/pgsql953/include/internal -D_GNU_SOURCE   -c -o src/pg_partman_bgw.o
> src/pg_partman_bgw.c -MMD -MP -MF .deps/pg_partman_bgw.Po
> In file included from /opt/pgsql953/include/server/postgres.h:48:0,
>                  from src/pg_partman_bgw.c:10:
> src/pg_partman_bgw.c: In function ‘pg_partman_run_maintenance_c’:
> src/pg_partman_bgw.c:623:41: warning: format ‘%ju’ expects argument of type
> ‘uintmax_t’, but argument 3 has type ‘uint32 {aka unsigned int}’
> [-Wformat=]
>                  ereport(NOTICE, (errmsg("ret=%i -- rows=%ju", ret,
> SPI_processed)));
>                                          ^
> /opt/pgsql953/include/server/utils/elog.h:117:14: note: in definition of
> macro ‘ereport_domain’
>     errfinish rest; \
>               ^
> src/pg_partman_bgw.c:623:17: note: in expansion of macro ‘ereport’
>                  ereport(NOTICE, (errmsg("ret=%i -- rows=%ju", ret,
> SPI_processed)));
>                  ^
>

Ok, It seems it is really returns data(ret=5 is SPI_OK_SELECT). I'm
sorry, my guess was wrong.
I'll manage to see it closer tonight.


--
Best regards,
Vitaly Burovoy