* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> Stephen Frost wrote:
> -- Start of PGP signed section.
> > * Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> > > Would you show an example of the invalid value this is trying to avoid?
> >
> > Well, the way I discovered the problem was by sending a timestamp in
> > double format when the server was expecting one in int64 format. This
> > is when using the binary data method for timestamps. I'll generate a
> > small example program/schema later today and post it to the list.
>
> So you are passing the data via binary COPY or a C function?
Sorry I wasn't clear, it's using libpq and binary data using an 'insert'
statement. The code looks something like this:
PQexec(connection,"prepare addrow (timestamp) as insert into mytable
values ($1)");
lengths[0] = sizeof(double);
formats[0] = 1;
*(double*)(values[0]) = tv_sec - ((POSTGRES_EPOCH_JDATE -
UNIX_EPOCH_DATE) * 86400) + (tv_sec / 1000000.00);
PQexecPrepared(connection,"addrow",1,(void*)values,lengths,formats,0);
While the new code is something like:
int64_t pg_timestamp;
PQexec(connection,"prepare addrow (timestamp) as insert into mytable
values ($1)");
lengths[0] = sizeof(int64_t);
formats[0] = 1;
pg_timestamp = ((tv_sec - ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) *
86400)) * (int64_t)1000000) + tv_usec;
*(int64_t*)(values[0]) = bswap_64(pg_timestamp);
PQexecPrepared(connection,"addrow",1,(void*)values,lengths,formats,0);
I'll see about writing up a proper test case/schema. Looks like I'm
probably most of the way there at this point, really. ;)
Stephen