Обсуждение: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
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@4js.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
Re: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
On 02/15/2018 05:25 PM, Tom Lane wrote: > Sebastien FLAESCH <sf@4js.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: https://www.postgresql.org/docs/10/static/sql-syntax-calling-funcs.html Cheers, Seb
Sebastien FLAESCH <sf@4js.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
Re: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
On 02/16/2018 04:15 PM, Tom Lane wrote: > Sebastien FLAESCH <sf@4js.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@4js.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
Re: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
On 02/19/2018 05:06 PM, Tom Lane wrote: > Sebastien FLAESCH <sf@4js.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@4js.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
Re: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
On 02/20/2018 03:39 PM, Tom Lane wrote: > Sebastien FLAESCH <sf@4js.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@4js.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
Re: Type scale returned by PQfmod() 65531 for time/timestamp outputparameter?
От
Sebastien FLAESCH
Дата:
On 02/21/2018 07:36 PM, Tom Lane wrote: > Sebastien FLAESCH <sf@4js.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