Thread: uuid c function contrib

uuid c function contrib

From
Ron Peterson
Date:
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/

Re: uuid c function contrib

From
Ron Peterson
Date:
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/


Re: uuid c function contrib

From
Ron Peterson
Date:
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/

Re: uuid c function contrib

From
Ron Peterson
Date:
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/

c function returning high resolution timestamp

From
Ron Peterson
Date:
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 );
}



Re: c function returning high resolution timestamp

From
Andreas Seltenreich
Date:
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

Re: c function returning high resolution timestamp

From
Ron Peterson
Date:
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/