Обсуждение: uuid c function contrib
Hi, I've written some PostgreSQL C functions which expose the functionality of Theodore Ts'o's UUID library. I need to add a few sanity clauses here and there, but working (mostly) code can be found here: http://www.yellowbank.com/code/PostgreSQL/uuid/ I have one problem. My y_uuid_time function works on my laptop (Kubuntu/Dapper), but not on another box (Debian Sarge). PostgreSQL 8.1.4 in both cases. When it works, it looks like this: rpeterso=# select y_uuid_time( y_uuid_generate_time() ); y_uuid_time --------------------- 2006-10-17 14:29:34 When it doesn't, it looks like this: rpeterso=# select y_uuid_time( y_uuid_generate_time() ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> I've been staring at my array bounds so hard it's giving me headache. If anyone can identify what might be wrong, I'd greatly appreciate it. Best. -- Ron Peterson https://www.yellowbank.com/
On Tue, Oct 17, 2006 at 03:25:05PM -0400, Ron Peterson wrote: > I've written some PostgreSQL C functions which expose the functionality > of Theodore Ts'o's UUID library. I need to add a few sanity clauses > here and there, but working (mostly) code can be found here: > > http://www.yellowbank.com/code/PostgreSQL/uuid/ > I have one problem. My y_uuid_time function works on my laptop > (Kubuntu/Dapper), but not on another box (Debian Sarge). The usual remedy of walking away from the problem and coming back fresh helped me realize my stupid mistake. I still believe in sanity clauses, so I have a little more finessing to do, but the posted code works. Implements uuid_generation functions which return bytea values, a function to convert the bytea values to the canonical hex form, and a function to extract the time from time-based uuids. If you can use this, please do. Examples: rpeterso=# select y_uuid_to_hex( y_uuid_generate() ); y_uuid_to_hex -------------------------------------- 0bf56bb1-1e47-472f-82a8-cc8134d23d91 (1 row) rpeterso=# select y_uuid_to_hex( y_uuid_generate_random() ); y_uuid_to_hex -------------------------------------- 8e239390-1bd2-4b01-9ad9-8b3f0cfbb5b5 (1 row) rpeterso=# select y_uuid_to_hex( y_uuid_generate_time() ); y_uuid_to_hex -------------------------------------- e3a3a694-5e43-11db-82ad-0014bf7c74ed (1 row) rpeterso=# select y_uuid_time( y_uuid_generate_time() ); y_uuid_time ---------------------------- 2006-10-17 20:59:25.101671 Best. -- Ron Peterson https://www.yellowbank.com/
On Tue, Oct 17, 2006 at 09:07:08PM -0400, Ron Peterson wrote:
> On Tue, Oct 17, 2006 at 03:25:05PM -0400, Ron Peterson wrote:
>
> > I've written some PostgreSQL C functions which expose the functionality
> > of Theodore Ts'o's UUID library.
% select y_uuid_time( y_uuid_generate_time() );
y_uuid_time
----------------------------
2006-10-17 20:59:25.101671
My y_uuid_time function returns text. It would be better to have it
return a timestamp. What internal functions does PostgreSQL use for
timestamp manipulation?
int64 or maybe float atimestamp
atimestamp = convert_seconds+usecs_since_epoch( somedata )
PG_RETURN_TIMESTAMP( atimestamp )
I'm having a hard time finding any examples of functions returning
timestamps I can study to see how they are handled internally. I'm sure
it's only a line or two of code.
TIA.
--
Ron Peterson
https://www.yellowbank.com/
On Wed, Oct 18, 2006 at 04:31:57PM -0400, Ron Peterson wrote: > I'm having a hard time finding any examples of functions returning > timestamps I can study to see how they are handled internally. I'm sure > it's only a line or two of code. ...I just found date.c -- Ron Peterson https://www.yellowbank.com/
On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote: > On Wed, Oct 18, 2006 at 04:31:57PM -0400, Ron Peterson wrote: > > > I'm having a hard time finding any examples of functions returning > > timestamps I can study to see how they are handled internally. I'm sure > > it's only a line or two of code. > > ...I just found date.c I'm pretty close, but I'm still not understanding something about PostgreSQL's internal timestamp representation. If I do 'select now();', I get a return value with microsecond resolution, which would seem to indicate that internally, PostgreSQL is using an INT64 value rather than a float to hold the timestamp. My function below, however, always takes the float path through the ifdef. If I force the int64 path, I just get a garbage timestamp which still only has a seconds resolution. What do I need to do to generate a high resolution timestamp? TIA. Ron Peterson https://www.yellowbank.com/ #include "postgres.h" #include "fmgr.h" #include "utils/datetime.h" #include <uuid/uuid.h> #include <sys/time.h> #include <time.h> #include <string.h> PG_FUNCTION_INFO_V1( y_uuid_time ); Datum y_uuid_time( PG_FUNCTION_ARGS ) { if( PG_ARGISNULL(0) ) { PG_RETURN_NULL(); } bytea* uuid = PG_GETARG_BYTEA_P(0); typedef unsigned int uint; uuid_t uu; struct timeval tv; time_t t; Timestamp ts; uint epoch_offset; epoch_offset = (POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * SECS_PER_DAY; memcpy( uu, VARDATA( uuid ), 16 ); t = uuid_time( uu, &tv ); #ifdef HAVE_INT64_TIMESTAMP ts = (tv.tv_sec - epoch_offset) * 1000000 + tv.tv_usec; #else ts = (double)(tv.tv_sec - epoch_offset); #endif PG_RETURN_TIMESTAMP( ts ); }
Ron Peterson writes: > On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote: > I'm pretty close, but I'm still not understanding something about > PostgreSQL's internal timestamp representation. If I do 'select > now();', I get a return value with microsecond resolution, which would > seem to indicate that internally, PostgreSQL is using an INT64 value > rather than a float to hold the timestamp. Floating point timestamps /do/ have microsecond resolution over a limited range: ,----[ <http://www.postgresql.org/docs/8.1/static/datatype-datetime.html> ] | Microsecond precision is achieved for dates within a few years of | 2000-01-01, but the precision degrades for dates further away. When | timestamp values are stored as eight-byte integers (a compile-time | option), microsecond precision is available over the full range of | values. `---- > My function below, however, always takes the float path through the > ifdef. If I force the int64 path, I just get a garbage timestamp > which still only has a seconds resolution. The proper way to enable the int64 path is enabling integer timestamps during build-time: ,----[ ./configure --help|grep time ] | --enable-integer-datetimes enable 64-bit integer date/time support `---- regards, andreas
On Fri, Oct 20, 2006 at 03:32:05PM +0200, Andreas Seltenreich wrote: > Ron Peterson writes: > > > On Wed, Oct 18, 2006 at 04:43:40PM -0400, Ron Peterson wrote: > > I'm pretty close, but I'm still not understanding something about > > PostgreSQL's internal timestamp representation. If I do 'select > > now();', I get a return value with microsecond resolution, which would > > seem to indicate that internally, PostgreSQL is using an INT64 value > > rather than a float to hold the timestamp. > > Floating point timestamps /do/ have microsecond resolution over a > limited range: > > ,----[ <http://www.postgresql.org/docs/8.1/static/datatype-datetime.html> ] > | Microsecond precision is achieved for dates within a few years of > | 2000-01-01, but the precision degrades for dates further away. When > | timestamp values are stored as eight-byte integers (a compile-time > | option), microsecond precision is available over the full range of > | values. > `---- Ahah! Pghghtht, I've read that page many times, but never looking for programming information. Not a problem with the way the docs are organized, just a problem with the way my brain is organized. Thanks for taking the time to help a slow learner. Working code is posted here: http://www.yellowbank.com/code/PostgreSQL/y_uuid/ -- Ron Peterson https://www.yellowbank.com/