Change in result type of EXTRACT() with Pg14 upwards not handled

With major version 14 PostgreSQL's EXTRACT() function changed it's response type from "double precision" to "numeric". For modules like CPanel::JSON::XS which serialize data depending on the data type, this has the consequence that JSON output for the same SQL query will return a JSON string or a JSON float depending on which Pg server version is running.

We had the issue that this change broke the parsing logic of the statically typed JSON consumer.

As it is a matter of policy and backwards compatibility as how to address this issue, I've not yet wrapped that up in a test case/pull request.

What do you think should be the way this should be handled?

(a) Use _svsetnv in case of PG_NUMERIC and a newly introduced flag? Similar to _pg_int8_asstring at issue #100 and take into account the potential loss of precision. (b) Do not handle this as DBD::Pg users should update their usage of EXTRACT() and cast the return type in the SQL query?

This test query and the output using it against Pg15 vs Pg13 illustrates the different output types.

SELECT 1 as id,
EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)::integer as curr_ts_integer,
EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) as curr_ts_uncasted

$ perl pg_extract_data_type_test.pl db15name
[2022-11-10T10:08:49.921934+0100] Running with GH::DBI::Cache: at pg_extract_data_type_test.pl line 13. 
[2022-11-10T10:08:49.922939+0100] db: db15name, version: 15.0 (Debian 15.0-1.pgdg110+1) at pg_extract_data_type_test.pl line 22. 
SV = IV(0x564458067310) at 0x564458067320
  REFCNT = 1
  FLAGS = (IOK,pIOK)
  IV = 1668071330
SV = PV(0x564457c6c5c0) at 0x564458067338
  REFCNT = 1
  FLAGS = (POK,IsCOW,pPOK,UTF8)
  PV = 0x564458079390 "1668071329.926068"\0 [UTF8 "1668071329.926068"]
  CUR = 17
  LEN = 19
  COW_REFCNT = 1

{
   "1" : {
      "curr_ts_integer" : 1668071330,
      "id" : 1,
      "curr_ts_uncasted" : "1668071329.926068"
   }
}

$ perl  pg_extract_data_type_test.pl db13name
[2022-11-10T10:08:59.182096+0100] Running with GH::DBI::Cache: at pg_extract_data_type_test.pl line 13. 
[2022-11-10T10:08:59.182848+0100] db: db13name, version: 13.6 (Debian 13.6-1.pgdg110+1) at pg_extract_data_type_test.pl line 22. 
SV = IV(0x5591eba2d340) at 0x5591eba2d350
  REFCNT = 1
  FLAGS = (IOK,pIOK)
  IV = 1668071339
SV = NV(0x5591eba2d350) at 0x5591eba2d368
  REFCNT = 1
  FLAGS = (NOK,pNOK)
  NV = 1668071339.185991

{
   "1" : {
      "curr_ts_integer" : 1668071339,
      "id" : 1,
      "curr_ts_uncasted" : 1668071339.18599
   }
}

bucardo / dbdpg

Change in result type of EXTRACT() with Pg14 upwards not handled #117