Re: Type scale returned by PQfmod() 65531 for time/timestamp output parameter?

Hi all,

(PostgreSQL 10.beta2)

I am using the PQfmod() function to get type information of result set columns.

When using a stored function returning output parameters defined with as time[(n)]
or timestamp[(n)], PQfmod() always returns the same SCALE (65531/0xFFFB), no matter
what time/timestamp precision is used.

With a regular SELECT on the same table columns I get other scale values, that allow
me to distinguish the time/timestamp fraction precision:

2/0x0002 for time/timestamp(6)
1/0x0001 for time/timestamp(5)
0/0x0000 for time/timestamp(4)
65535/0xFFFF for time/timestamp(3)
65534/0xFFFE for time/timestamp(2)
65533/0xFFFD for time/timestamp(1)
65532/0xFFFC for time/timestamp(0)
etc

I get the scale as follows:

#define VARHDRSZ 4
...
int pgfmod = PQfmod(st->pgResult, i);
int pgprec = (pgfmod >> 16);
int pgscal = ((pgfmod - VARHDRSZ) & 0xffff); <-- here
int pgleng = (pgfmod - VARHDRSZ);
...

Is this correct?
I Could not find detailed documentation about the interpretation of PQfmod()...

Here the SQL code of the stored function:

create function proc240(
in p_pkey integer,
out p_dt_y2i timestamp without time zone,
out p_dt_y2s timestamp without time zone,
out p_dt_y2f2 timestamp(2) without time zone,
out p_dt_y2f3 timestamp(3) without time zone,
out p_dt_y2f5 timestamp(5) without time zone,
out p_dt_h2i time without time zone,
out p_dt_h2s time without time zone,
out p_dt_h2f2 time(2) without time zone,
out p_dt_h2f3 time(3) without time zone,
out p_dt_h2f5 time(5) without time zone
)
as $$
begin
select
dt_y2i,
dt_y2s,
dt_y2f2,
dt_y2f3,
dt_y2f5,
dt_h2i,
dt_h2s,
dt_h2f2,
dt_h2f3,
dt_h2f5
into
p_dt_y2i,
p_dt_y2s,
p_dt_y2f2,
p_dt_y2f3,
p_dt_y2f5,
p_dt_h2i,
p_dt_h2s,
p_dt_h2f2,
p_dt_h2f3,
p_dt_h2f5
from t240 where pkey = p_pkey;
end;
$$ language plpgsql

Then I am doing this:

select * from proc240(101)

To get the output parameters.

Thanks for you help!
Seb

Sebastien FLAESCH <sf(at)4js(dot)com> writes:
> When using a stored function returning output parameters defined with as time[(n)]
> or timestamp[(n)], PQfmod() always returns the same SCALE (65531/0xFFFB), no matter
> what time/timestamp precision is used.

Your misunderstanding is in assuming that typmod decoration on function
parameters means anything. It doesn't; the function is effectively just
declared as taking or returning plain time or timestamp.

Perhaps someday that will change, but it'd be a big task with a lot of
hard decisions to make. For instance, do we allow creation of both
f(time(2)) and f(time(4)), and if so what's the rule for choosing which
one to call?

regards, tom lane

On 02/15/2018 05:25 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> When using a stored function returning output parameters defined with as time[(n)]
>> or timestamp[(n)], PQfmod() always returns the same SCALE (65531/0xFFFB), no matter
>> what time/timestamp precision is used.
>
> Your misunderstanding is in assuming that typmod decoration on function
> parameters means anything. It doesn't; the function is effectively just
> declared as taking or returning plain time or timestamp.
>
> Perhaps someday that will change, but it'd be a big task with a lot of
> hard decisions to make. For instance, do we allow creation of both
> f(time(2)) and f(time(4)), and if so what's the rule for choosing which
> one to call?
>
> regards, tom lane
>
>

Thanks for this quick answer Tom,

I just wanted to make sure that this is expected.

We can deal with this, by interpreting 65531/0xFFFB as a precision of 5 to match our needs.

Would be nice however to have some clear documentation about PQfmod() interpretation...
What means exactly 65531/0xFFFB?
Unknown time/timestamp fraction of sec precision?

Maybe the way stored function output parameters are returned could be reviewed?

select * from proc240(101)

Is it possible to cast() output parameters?

I could not find information about using output parameters in:

/docs/10/static/sql-syntax-calling-funcs.html

Cheers,
Seb

Sebastien FLAESCH <sf(at)4js(dot)com> writes:
> Would be nice however to have some clear documentation about PQfmod() interpretation...
> What means exactly 65531/0xFFFB?

You could try running it through the typmodout function for the column's
datatype. I don't offhand know of any built-in types for which that would
be a really plausible typmod, though. Are you sure your client code isn't
mistakenly narrowing it to int16 somewhere?

regards, tom lane

On 02/16/2018 04:15 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> Would be nice however to have some clear documentation about PQfmod() interpretation...
>> What means exactly 65531/0xFFFB?
>
> You could try running it through the typmodout function for the column's
> datatype. I don't offhand know of any built-in types for which that would
> be a really plausible typmod, though. Are you sure your client code isn't
> mistakenly narrowing it to int16 somewhere?
>
> regards, tom lane
>
>

As I wrote in my initial mail, I do the following:

#define VARHDRSZ 4
...
int pgfmod = PQfmod(st->pgResult, i);
int pgprec = (pgfmod >> 16);
int pgscal = ((pgfmod - VARHDRSZ) & 0xffff);
int pgleng = (pgfmod - VARHDRSZ);

As no clear documentation is available for PQfmod() interpretation I looked at the internal
header files and ECPG sources.

The VARHDRSZ is sizeof(int32), but it's not available in standard PostgreSQL PQ header files.

It can be found in include/postgresql/server/c.h:

#define VARHDRSZ ((int32) sizeof(int32))

In the ECPG sources you can see:

src/interfaces/ecpg/ecpglib/descriptor.c:

case ECPGd_scale:
if (!get_int_item(lineno, var, vartype, (PQfmod(ECPGresult, index) - VARHDRSZ) & 0xffff))
{
va_end(args);
return (false);
}

ecpg_log("ECPGget_desc: SCALE = %d\n", (PQfmod(ECPGresult, index) - VARHDRSZ) & 0xffff);
break;

case ECPGd_precision:
if (!get_int_item(lineno, var, vartype, PQfmod(ECPGresult, index) >> 16))
{
va_end(args);
return (false);
}

ecpg_log("ECPGget_desc: PRECISION = %d\n", PQfmod(ECPGresult, index) >> 16);
break;

Seb

Sebastien FLAESCH <sf(at)4js(dot)com> writes:
> On 02/16/2018 04:15 PM, Tom Lane wrote:
>> You could try running it through the typmodout function for the column's
>> datatype. I don't offhand know of any built-in types for which that would
>> be a really plausible typmod, though. Are you sure your client code isn't
>> mistakenly narrowing it to int16 somewhere?

> As I wrote in my initial mail, I do the following:

> #define VARHDRSZ 4
> int pgfmod = PQfmod(st->pgResult, i);
> int pgprec = (pgfmod >> 16);
> int pgscal = ((pgfmod - VARHDRSZ) & 0xffff);
> int pgleng = (pgfmod - VARHDRSZ);

That might --- I don't recall offhand --- be the right decoding for
the typmod of a column of type NUMERIC. It's certainly not right
for any other datatype. Also, you don't seem to be accounting for
the fact that negative typmod always means "no typmod specified".

regards, tom lane

On 02/19/2018 05:06 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> On 02/16/2018 04:15 PM, Tom Lane wrote:
>>> You could try running it through the typmodout function for the column's
>>> datatype. I don't offhand know of any built-in types for which that would
>>> be a really plausible typmod, though. Are you sure your client code isn't
>>> mistakenly narrowing it to int16 somewhere?
>
>> As I wrote in my initial mail, I do the following:
>
>> #define VARHDRSZ 4
>> int pgfmod = PQfmod(st->pgResult, i);
>> int pgprec = (pgfmod >> 16);
>> int pgscal = ((pgfmod - VARHDRSZ) & 0xffff);
>> int pgleng = (pgfmod - VARHDRSZ);
>
> That might --- I don't recall offhand --- be the right decoding for
> the typmod of a column of type NUMERIC. It's certainly not right
> for any other datatype. Also, you don't seem to be accounting for
> the fact that negative typmod always means "no typmod specified".
>
> regards, tom lane
>

Thank you Tom.

From our tests it seems to be ok to extract the time/timestamp scale...

This really needs clarification, I would appreciate that the community gives
more information about PQfmod()...

We implement a proprietary database interface lib based on libpq, which needs
to provide columnn type information (similar to ODBC's SQLDescriceCol[W]())

Type information is also used to properly do data type conversions (for ex to
fetch a time(n) into a varchar(50) variable in our language),

It is mission critical for us.

=> I will have a look at psqlODBC ...

Seb

Sebastien FLAESCH <sf(at)4js(dot)com> writes:
> This really needs clarification, I would appreciate that the community gives
> more information about PQfmod()...

Like I said before, it's datatype-specific and you need to look at the
typmodin/typmodout support functions for each type to see what they do.

regards, tom lane

On 02/20/2018 03:39 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> This really needs clarification, I would appreciate that the community gives
>> more information about PQfmod()...
>
> Like I said before, it's datatype-specific and you need to look at the
> typmodin/typmodout support functions for each type to see what they do.
>
> regards, tom lane
>

Thank you Tom.

I don't know what these functions are, sorry if I misunderstood.

Are you suggesting me to dig into the PostgreSQL server sources / internals?

Any starting point I should look at?

Seb

Sebastien FLAESCH <sf(at)4js(dot)com> writes:
> On 02/20/2018 03:39 PM, Tom Lane wrote:
>> Like I said before, it's datatype-specific and you need to look at the
>> typmodin/typmodout support functions for each type to see what they do.

> Are you suggesting me to dig into the PostgreSQL server sources / internals?

Yup.

> Any starting point I should look at?

regression=# select distinct typmodout from pg_type where typmodout != 0;
typmodout
----------------------
intervaltypmodout
timestamptypmodout
timestamptztypmodout
timetypmodout
timetztypmodout
bpchartypmodout
varchartypmodout
numerictypmodout
bittypmodout
varbittypmodout
(10 rows)

I think all of those are under src/backend/utils/adt/ in the sources.
Briefly their charter is to produce the textual representation of a
typmod value for the data type, or an empty string if there's no typmod
constraint. Although in principle code outside the datatype shouldn't
assume anything at all about the encoding of typmod, there's a widespread
assumption that all negative values (not just -1) mean "no constraint".

regards, tom lane

On 02/21/2018 07:36 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf(at)4js(dot)com> writes:
>> On 02/20/2018 03:39 PM, Tom Lane wrote:
>>> Like I said before, it's datatype-specific and you need to look at the
>>> typmodin/typmodout support functions for each type to see what they do.
>
>> Are you suggesting me to dig into the PostgreSQL server sources / internals?
>
> Yup.
>
>> Any starting point I should look at?
>
> regression=# select distinct typmodout from pg_type where typmodout != 0;
> typmodout
> ----------------------
> intervaltypmodout
> timestamptypmodout
> timestamptztypmodout
> timetypmodout
> timetztypmodout
> bpchartypmodout
> varchartypmodout
> numerictypmodout
> bittypmodout
> varbittypmodout
> (10 rows)
>
> I think all of those are under src/backend/utils/adt/ in the sources.
> Briefly their charter is to produce the textual representation of a
> typmod value for the data type, or an empty string if there's no typmod
> constraint. Although in principle code outside the datatype shouldn't
> assume anything at all about the encoding of typmod, there's a widespread
> assumption that all negative values (not just -1) mean "no constraint".
>
> regards, tom lane
>

OK Thanks!
Seb