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

From: Sebastien FLAESCH <[email-masked]>
Date: Thu, 15 Feb 2018 16:03:22 +0100

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 15 Feb 2018 11:25:40 -0500

> 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

---

From: Sebastien FLAESCH <[email-masked]>
Date: Fri, 16 Feb 2018 09:56:05 +0100

> f(time(2)) and f(time(4)), and if so what's the rule for choosing which

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?

Is it possible to cast() output parameters?

I could not find information about using output parameters in:

https://www.postgresql.org/docs/10/static/sql-syntax-calling-funcs.html

Cheers,
Seb

---

From: Tom Lane <[email-masked]>
Date: Fri, 16 Feb 2018 10:15:52 -0500

> 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

---

From: Sebastien FLAESCH <[email-masked]>
Date: Mon, 19 Feb 2018 12:16:45 +0100

> 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);

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

---

From: Tom Lane <[email-masked]>
Date: Mon, 19 Feb 2018 11:06:23 -0500

>      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

---

From: Sebastien FLAESCH <[email-masked]>
Date: Tue, 20 Feb 2018 10:30:32 +0100

> the fact that negative typmod always means "no typmod specified".

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 20 Feb 2018 09:39:06 -0500

> 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

---

From: Sebastien FLAESCH <[email-masked]>
Date: Wed, 21 Feb 2018 17:41:44 +0100

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 21 Feb 2018 13:36:30 -0500

> 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

---

From: Sebastien FLAESCH <[email-masked]>
Date: Thu, 22 Feb 2018 13:35:12 +0100

> regression=# select distinct typmodout from pg_type where typmodout != 0;

OK Thanks!
Seb