Обсуждение: How to properly use TRIM()?

Hi, ALL,

draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, NULLIF( TRIM( c.reloptions ), '' ) AS storage FROM pg_index
idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs WHERE
ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
n.nspname = 'public' AND t.relname = 'leagues';
ERROR:  function pg_catalog.btrim(text[]) does not exist
LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( c.re...
                                                             ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, NULLIF( TRIM( BOTH FROM c.reloptions ), '' ) AS storage FROM
pg_index idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs
WHERE ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
n.nspname = 'public' AND t.relname = 'leagues';
ERROR:  function pg_catalog.btrim(text[]) does not exist
LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( BOTH...
                                                             ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
draft=#

Thank you.

included, NULLIF( TRIM( c.reloptions ), '' ) AS storage FROM pg_index
idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs WHERE
ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
n.nspname = 'public' AND t.relname = 'leagues';
ERROR:  function pg_catalog.btrim(text[]) does not exist
LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( c.re...
                                                              

Hi, David,

On Sat, Mar 7, 2026 at 1:41 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Saturday, March 7, 2026, Igor Korot <ikorot01@gmail.com> wrote:
>>
>>
>> included, NULLIF( TRIM( c.reloptions ), '' ) AS storage FROM pg_index
>> idx, pg_class c, pg_namespace n, pg_class t, pg_indexes ixs WHERE
>> ixs.indexname = c.relname AND c.oid = idx.indexrelid AND t.oid =
>> idx.indrelid AND n.oid = c.relnamespace AND idx.indisprimary AND
>> n.nspname = 'public' AND t.relname = 'leagues';
>> ERROR:  function pg_catalog.btrim(text[]) does not exist
>> LINE 1: ...num) OFFSET idx.indnkeyatts) AS included, NULLIF( TRIM( c.re...
>>
>
>
> You are asking the wrong question.  The right question is “how does one turn an empty array into the null value?”
Nullifis correct, you just need to specify an empty array (of the correct type) for the second argument. 

I don't think pg_class.reloptions is an ARRAY...

draft=# SELECT c.relname AS name, ixs.tablespace, ARRAY(SELECT
a.attname FROM pg_attribute a WHERE a.attrelid = idx.indrelid AND
a.attnum = ANY(idx.indkey) AND a.attnum > 0 ORDER BY
array_position(idx.indkey, a.attnum) OFFSET idx.indnkeyatts) AS
included, c.reloptions AS storage FROM pg_index idx, pg_class c,
pg_namespace n, pg_class t, pg_indexes ixs WHERE ixs.indexname =
c.relname AND c.oid = idx.indexrelid AND t.oid = idx.indrelid AND
n.oid = c.relnamespace AND idx.indisprimary AND n.nspname = 'public'
AND t.relname = 'leagues';
     name     | tablespace | included | storage
--------------+------------+----------+---------
 leagues_pkey |            | {}       |
(1 row)

Included is one, storage is not.

Thank you.

>
> Given that error message, an array of text is the correct type.
>
> Array[]::text[]
>
> David J.
>

'
     name     | tablespace | included | storage
--------------+------------+----------+---------
 leagues_pkey |            | {}       |
(1 row)

Included is one, storage is not.

On 3/7/26 12:11 AM, Igor Korot wrote:
> Hi, David,

>> You are asking the wrong question.  The right question is “how does one turn an empty array into the null value?”
Nullifis correct, you just need to specify an empty array (of the correct type) for the second argument.
 
> 
> I don't think pg_class.reloptions is an ARRAY...

I think it is:

https://www.postgresql.org/docs/current/catalog-pg-class.html

reloptions text[]


> Included is one, storage is not.
> 
> Thank you.
> 
>>
>> Given that error message, an array of text is the correct type.
>>
>> Array[]::text[]
>>
>> David J.
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Rob,

On Sat, Mar 7, 2026 at 1:20 PM Rob Sargent <robjsargent@gmail.com> wrote:
>
>
>> reloptions text[]
>
>
> Then why it’s not showing {} as in the “included” columns?
>
> Or it’s an ARRAY() implementation?
>
> Thank you.
>
>>
>
> One is an empty array, the other is null. Those are not the same thing.

Following code successfully retrieves column 3 and exits, but keep
looping for column 4.

                while( ( ret = SQLGetData( m_hstmt, 3, SQL_C_WCHAR,
included.get(), 255, &ind[2] ) ) != SQL_NO_DATA )
                {
                    if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
                    {
                        auto numBytes = ind[2];
                        if( ind[2] == SQL_NO_TOTAL )
                            numBytes = 255;
                        else if( ind[2] > 255 )
                            numBytes = 255;
                        str_to_uc_cpy( includedCol, included.get() );
                    }
                    else
                    {
                        GetErrorMessage( errorMsg, STMT_ERROR  );
                        result = 1;
                    }
                }
                includedCol.erase( 0, 1 );
                includedCol.pop_back();
            }
            while( ( ret = SQLGetData( m_hstmt, 4, SQL_C_WCHAR,
index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA )
            {
                if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
                {
                    auto numBytes = ind[3];
                    if( ind[3] == SQL_NO_TOTAL )
                        numBytes = 255;
                    else if( ind[3] > 255 )
                        numBytes = 255;
                    str_to_uc_cpy( options, index_param.get() );
                }
                else
                {
                    GetErrorMessage( errorMsg, STMT_ERROR  );
                    result = 1;
                }
            }

when I tried to run it without nullif() and trim().

So I started looking for a way to return SQL_NO_DATA
on that 4th column...

Thank you.

P.S.: Sorry for kind of throwing ODBC code here. You have ODBC
related list, but this is where things get in the cross.

>
>>
>>
>> > Included is one, storage is not.
>> >
>> > Thank you.
>> >
>> >>
>> >> Given that error message, an array of text is the correct type.
>> >>
>> >> Array[]::text[]
>> >>
>> >> David J.
>> >>
>> >
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com

On 3/7/26 12:46 PM, Igor Korot wrote:
> Hi, David,
> 
> On Sat, Mar 7, 2026 at 12:03 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Sat, Mar 7, 2026 at 12:58 PM Igor Korot <ikorot01@gmail.com
>     <mailto:ikorot01@gmail.com>> wrote:
> 
>         So I started looking for a way to return SQL_NO_DATA
>         on that 4th column...
> 
> 
>     Doesn't "No Data" refer to the result set as a whole, not individual
>     columns?  I'd assume NULL is detected some other way.
> 
> 
> No, I think it’s column based.

1) My knowledge of ODBC is limited.

2) This:

https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/return-codes-odbc?view=sql-server-ver17

"SQL_NO_DATA     No more data was available. The application calls 
SQLGetDiagRec or SQLGetDiagField to retrieve additional information. One 
or more driver-defined status records in class 02xxx may be returned. 
Note: In ODBC 2.x, this return code was named SQL_NO_DATA_FOUND."

would seem to indicate that David Johnston is correct:

'Doesn't "No Data" refer to the result set as a whole, not individual 
columns?  I'd assume NULL is detected some other way.'

> The call to SQLGetData() returns data in one column.
> 
> And as stated it successfully retrieves empty array for column 3 and 
> moves on.
> 
> Thank you.
> 
> 
>     David J.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi, Adrian,

On Sat, Mar 7, 2026 at 3:29 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 3/7/26 12:46 PM, Igor Korot wrote:
> > Hi, David,
> >
> > On Sat, Mar 7, 2026 at 12:03 PM David G. Johnston
> > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> >
> >     On Sat, Mar 7, 2026 at 12:58 PM Igor Korot <ikorot01@gmail.com
> >     <mailto:ikorot01@gmail.com>> wrote:
> >
> >         So I started looking for a way to return SQL_NO_DATA
> >         on that 4th column...
> >
> >
> >     Doesn't "No Data" refer to the result set as a whole, not individual
> >     columns?  I'd assume NULL is detected some other way.
> >
> >
> > No, I think it’s column based.
>
> 1) My knowledge of ODBC is limited.
>
> 2) This:
>
> https://learn.microsoft.com/en-us/sql/odbc/reference/develop-app/return-codes-odbc?view=sql-server-ver17
>
> "SQL_NO_DATA    No more data was available. The application calls
> SQLGetDiagRec or SQLGetDiagField to retrieve additional information. One
> or more driver-defined status records in class 02xxx may be returned.
> Note: In ODBC 2.x, this return code was named SQL_NO_DATA_FOUND."
>
> would seem to indicate that David Johnston is correct:

From the SQLGetData() ODBC documentation
(https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-function?view=sql-server-ver17):

[quote]
When it returns the last part of the data, SQLGetData returns
SQL_SUCCESS. Neither SQL_NO_TOTAL nor zero can be returned on the last
valid call to retrieve data from a column, because the application
would then have no way of knowing how much of the data in the
application buffer is valid. If SQLGetData is called after this, it
returns SQL_NO_DATA. For more information, see the next section,
"Retrieving Data with SQLGetData."
[/quote]

However it looks like the driver does not behave as expected.

It keeps returning SQL_SUCCESS continuously...

Or am I misinterpreting the docs?

Thank you.

>
> 'Doesn't "No Data" refer to the result set as a whole, not individual
> columns?  I'd assume NULL is detected some other way.'
>
> > The call to SQLGetData() returns data in one column.
> >
> > And as stated it successfully retrieves empty array for column 3 and
> > moves on.
> >
> > Thank you.
> >
> >
> >     David J.
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

I am not sure if this belongs in pgsql-general or pgsql-hackers, I am 
trying first in psgl-general.

I am trying to understand setting a selectivity function that gets 
applied to an operator (to hopefully
provide better information for the planner/optmizer). This is for the 
q3c extension, source code found at
https://github.com/segasai/q3c.

There are functions for selectivity, and for an operator.

-- A dummy type used in the selectivity operator
create type q3c_type as (ra double precision, dec double precision,
        ra1 double precision, dec1 double precision);

-- A dummy operator function (always returns true)
CREATE OR REPLACE FUNCTION q3c_seloper(double precision, q3c_type)
         RETURNS bool
         AS 'MODULE_PATHNAME', 'pgq3c_seloper'
         LANGUAGE C STRICT IMMUTABLE COST 1000;

-- A selectivity function for the q3c operator
CREATE OR REPLACE FUNCTION q3c_sel(internal, oid, internal, int4)
         RETURNS float8
         AS 'MODULE_PATHNAME', 'pgq3c_sel'
         LANGUAGE C IMMUTABLE STRICT ;

-- A selectivity function for the q3c operator
CREATE OR REPLACE FUNCTION q3c_seljoin(internal, oid, internal, int2, 
internal)
         RETURNS float8
         AS 'MODULE_PATHNAME', 'pgq3c_seljoin'
         LANGUAGE C IMMUTABLE STRICT ;

-- distance operator with correct selectivity
CREATE OPERATOR ==<<>>== (
         LEFTARG = double precision,
         RIGHTARG = q3c_type,
         PROCEDURE = q3c_seloper,
         RESTRICT = q3c_sel,
         JOIN = q3c_seljoin
);

The C portions are declared as:

/* The actual selectivity function, it returns the ratio of the
  * search circle to the whole sky area
  */
PG_FUNCTION_INFO_V1(pgq3c_sel);
Datum pgq3c_sel(PG_FUNCTION_ARGS)

where the actual calculation portion is (not showing the setup portion):

         ratio = 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
         /* clamp at 0, 1*/
         CLAMP_PROBABILITY(ratio);
         elog(WARNING, "HERE0 pgq3c_sel.... %e", ratio);
         PG_RETURN_FLOAT8(ratio);
}

The join function is declared as:
PG_FUNCTION_INFO_V1(pgq3c_seljoin);
Datum pgq3c_seljoin(PG_FUNCTION_ARGS)
{

where the meat portion is:
        ratio = 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
         /* clamp at 0, 1*/
         CLAMP_PROBABILITY(ratio);
         elog(WARNING, "HERE0 pgq3c_seljoin.... %e", ratio);
         PG_RETURN_FLOAT8(ratio);
}

The two elog statements aren't in the orig code, I've added them to help 
me trace
the code. As far as I can tell, the these selectivity functions are 
called in
src/backend/optimizer/path/clausesel.c in the routine 
clause_selectivity_ext.
If I add similar elog statements, at about line 836, the code says:

               if (treat_as_join_clause(root, clause, rinfo, varRelid, 
sjinfo))
         {
             /* Estimate selectivity for a join clause. */
             if (opno > 6000)
                 elog(WARNING, "clause_selectivity: join_selectivity 
opno %d",opno);
             s1 = join_selectivity(root, opno,
                                   opclause->args,
                                   opclause->inputcollid,
                                   jointype,
                                   sjinfo);
             if (opno > 6000){
                 elog(WARNING, "join_selectivity: s1 %f", s1);
             }
         }
         else
         {
             /* Estimate selectivity for a restriction clause. */
             if (opno > 6000)
                 elog(WARNING, "clause_selectivity: 
restriction_selectivity opno %d", opno);
             s1 = restriction_selectivity(root, opno,
                                          opclause->args,
                                          opclause->inputcollid,
                                          varRelid);
             if (opno > 6000){
                 elog(WARNING, "restriction_selectivity: s1 %lf", s1);
             }
         }


When I actually execute this, I get output to the terminal of the form:
WARNING:  join_selectivity: operator id 16818 jointype 0 0
WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
WARNING:  datum result 4438812783922730423 0.000000
WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
WARNING:  join_selectivity: 0.000000 16818 jointype 0
WARNING:  join_selectivity: s1 0.000000
WARNING:  clause_selectivity: s1 0.000000

where it seems to me the q3c code is returning a non zero value, but in 
the guts of
postgres what is found is a zero value. If I want to verify I have the 
correct opr,
which is 16818, I can verify via:
q3c_test=# select 
oid,oprname,oprnamespace,oprowner,oprkind,oprleft,oprright,oprresult,oprcode 
from pg_operator where oid = 16818;
   oid  | oprname  | oprnamespace | oprowner | oprkind | oprleft | 
oprright | oprresult |   oprcode
-------+----------+--------------+----------+---------+---------+----------+-----------+-------------
  16818 | ==<<>>== |         2200 |    16391 | b       |    701 |    
16814 |        16 | q3c_seloper

which yeilds what I expect.

The join_selectivity is essentially a call in 
src/backend/optimizer/util/plancat.c of:
     result = DatumGetFloat8(OidFunctionCall5Coll(oprjoin,
             inputcollid,
             PointerGetDatum(root),
             ObjectIdGetDatum(operatorid),
             PointerGetDatum(args),
             Int16GetDatum(jointype),
             PointerGetDatum(sjinfo)));

     if (result < 0.0 || result > 1.0)
         elog(ERROR, "invalid join selectivity: %f", result);

while restriction_selectivity is a call to:
     result = DatumGetFloat8(OidFunctionCall4Coll(oprrest,
                     inputcollid,
                     PointerGetDatum(root),
                     ObjectIdGetDatum(operatorid),
                     PointerGetDatum(args),
                     Int32GetDatum(varRelid)));

This is the point where I run out of steam. The basic issue I have is 
that q3c code is attempting
to return a small, but non-zero value for the selectivity in two 
functions, but the guts of
postgresql has both the join_selectivity  and restriction_selectivity 
function return zero where
I think they shouldn't.

Any advice in how to make progress on this is welcome.  I'm using 
19devel (I can probably do a
git merge to move to a more up to  date version), and I'm running Fedora 
release 43 in case which
exact OS I'm using is relavent.

Greg

/— intentional top post—/
Have you been a very bad boy and usurped an existing email thread?

> On Mar 17, 2026, at 11:29 AM, Greg Hennessy <greg.hennessy@gmail.com> wrote:
>
> I am not sure if this belongs in pgsql-general or pgsql-hackers, I am trying first in psgl-general.
>
> I am trying to understand setting a selectivity function that gets applied to an operator (to hopefully
> provide better information for the planner/optmizer). This is for the q3c extension, source code found at
> https://github.com/segasai/q3c.
>
> There are functions for selectivity, and for an operator.
>
> -- A dummy type used in the selectivity operator
> create type q3c_type as (ra double precision, dec double precision,
>        ra1 double precision, dec1 double precision);
>
> -- A dummy operator function (always returns true)
> CREATE OR REPLACE FUNCTION q3c_seloper(double precision, q3c_type)
>         RETURNS bool
>         AS 'MODULE_PATHNAME', 'pgq3c_seloper'
>         LANGUAGE C STRICT IMMUTABLE COST 1000;
>
> -- A selectivity function for the q3c operator
> CREATE OR REPLACE FUNCTION q3c_sel(internal, oid, internal, int4)
>         RETURNS float8
>         AS 'MODULE_PATHNAME', 'pgq3c_sel'
>         LANGUAGE C IMMUTABLE STRICT ;
>
> -- A selectivity function for the q3c operator
> CREATE OR REPLACE FUNCTION q3c_seljoin(internal, oid, internal, int2, internal)
>         RETURNS float8
>         AS 'MODULE_PATHNAME', 'pgq3c_seljoin'
>         LANGUAGE C IMMUTABLE STRICT ;
>
> -- distance operator with correct selectivity
> CREATE OPERATOR ==<<>>== (
>         LEFTARG = double precision,
>         RIGHTARG = q3c_type,
>         PROCEDURE = q3c_seloper,
>         RESTRICT = q3c_sel,
>         JOIN = q3c_seljoin
> );
>
> The C portions are declared as:
>
> /* The actual selectivity function, it returns the ratio of the
>  * search circle to the whole sky area
>  */
> PG_FUNCTION_INFO_V1(pgq3c_sel);
> Datum pgq3c_sel(PG_FUNCTION_ARGS)
>
> where the actual calculation portion is (not showing the setup portion):
>
>         ratio = 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
>         /* clamp at 0, 1*/
>         CLAMP_PROBABILITY(ratio);
>         elog(WARNING, "HERE0 pgq3c_sel.... %e", ratio);
>         PG_RETURN_FLOAT8(ratio);
> }
>
> The join function is declared as:
> PG_FUNCTION_INFO_V1(pgq3c_seljoin);
> Datum pgq3c_seljoin(PG_FUNCTION_ARGS)
> {
>
> where the meat portion is:
>        ratio = 3.14 * rad * rad / 41252.;  /* pi*r^2/whole_sky_area */
>         /* clamp at 0, 1*/
>         CLAMP_PROBABILITY(ratio);
>         elog(WARNING, "HERE0 pgq3c_seljoin.... %e", ratio);
>         PG_RETURN_FLOAT8(ratio);
> }
>
> The two elog statements aren't in the orig code, I've added them to help me trace
> the code. As far as I can tell, the these selectivity functions are called in
> src/backend/optimizer/path/clausesel.c in the routine clause_selectivity_ext.
> If I add similar elog statements, at about line 836, the code says:
>
>               if (treat_as_join_clause(root, clause, rinfo, varRelid, sjinfo))
>         {
>             /* Estimate selectivity for a join clause. */
>             if (opno > 6000)
>                 elog(WARNING, "clause_selectivity: join_selectivity opno %d",opno);
>             s1 = join_selectivity(root, opno,
>                                   opclause->args,
>                                   opclause->inputcollid,
>                                   jointype,
>                                   sjinfo);
>             if (opno > 6000){
>                 elog(WARNING, "join_selectivity: s1 %f", s1);
>             }
>         }
>         else
>         {
>             /* Estimate selectivity for a restriction clause. */
>             if (opno > 6000)
>                 elog(WARNING, "clause_selectivity: restriction_selectivity opno %d", opno);
>             s1 = restriction_selectivity(root, opno,
>                                          opclause->args,
>                                          opclause->inputcollid,
>                                          varRelid);
>             if (opno > 6000){
>                 elog(WARNING, "restriction_selectivity: s1 %lf", s1);
>             }
>         }
>
>
> When I actually execute this, I get output to the terminal of the form:
> WARNING:  join_selectivity: operator id 16818 jointype 0 0
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  datum result 4438812783922730423 0.000000
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  join_selectivity: 0.000000 16818 jointype 0
> WARNING:  join_selectivity: s1 0.000000
> WARNING:  clause_selectivity: s1 0.000000
>
> where it seems to me the q3c code is returning a non zero value, but in the guts of
> postgres what is found is a zero value. If I want to verify I have the correct opr,
> which is 16818, I can verify via:
> q3c_test=# select oid,oprname,oprnamespace,oprowner,oprkind,oprleft,oprright,oprresult,oprcode from pg_operator where
oid= 16818; 
>   oid  | oprname  | oprnamespace | oprowner | oprkind | oprleft | oprright | oprresult |   oprcode
> -------+----------+--------------+----------+---------+---------+----------+-----------+-------------
>  16818 | ==<<>>== |         2200 |    16391 | b       |    701 |    16814 |        16 | q3c_seloper
>
> which yeilds what I expect.
>
> The join_selectivity is essentially a call in src/backend/optimizer/util/plancat.c of:
>     result = DatumGetFloat8(OidFunctionCall5Coll(oprjoin,
>             inputcollid,
>             PointerGetDatum(root),
>             ObjectIdGetDatum(operatorid),
>             PointerGetDatum(args),
>             Int16GetDatum(jointype),
>             PointerGetDatum(sjinfo)));
>
>     if (result < 0.0 || result > 1.0)
>         elog(ERROR, "invalid join selectivity: %f", result);
>
> while restriction_selectivity is a call to:
>     result = DatumGetFloat8(OidFunctionCall4Coll(oprrest,
>                     inputcollid,
>                     PointerGetDatum(root),
>                     ObjectIdGetDatum(operatorid),
>                     PointerGetDatum(args),
>                     Int32GetDatum(varRelid)));
>
> This is the point where I run out of steam. The basic issue I have is that q3c code is attempting
> to return a small, but non-zero value for the selectivity in two functions, but the guts of
> postgresql has both the join_selectivity  and restriction_selectivity function return zero where
> I think they shouldn't.
>
> Any advice in how to make progress on this is welcome.  I'm using 19devel (I can probably do a
> git merge to move to a more up to  date version), and I'm running Fedora release 43 in case which
> exact OS I'm using is relavent.
>
> Greg
>
>
>

Greg Hennessy <greg.hennessy@gmail.com> writes:
> I am not sure if this belongs in pgsql-general or pgsql-hackers, I am 
> trying first in psgl-general.

Doesn't matter a lot, but as Rob noted, you should not hijack an
existing thread.  Start your own thread, don't reply to an unrelated
message.

> When I actually execute this, I get output to the terminal of the form:
> WARNING:  join_selectivity: operator id 16818 jointype 0 0
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  datum result 4438812783922730423 0.000000
> WARNING:  HERE0 pgq3c_seljoin.... 5.873266e-12
> WARNING:  join_selectivity: 0.000000 16818 jointype 0
> WARNING:  join_selectivity: s1 0.000000
> WARNING:  clause_selectivity: s1 0.000000

> where it seems to me the q3c code is returning a non zero value, but in 
> the guts of postgres what is found is a zero value.

I don't see any discrepancy in your results, only in your choices of
printf specifiers.  "%f" defaults to "%.6f", that is, print only six
digits after the decimal point.  So a value down around 1e-12 is
going to print as all zeroes.

            regards, tom lane

I am not sure if this belongs in pgsql-general or pgsql-hackers, I am 
trying first in psgl-general.

Doesn't matter a lot, but as Rob noted, you should not hijack an
existing thread.  Start your own thread, don't reply to an unrelated
message.

I don't see any discrepancy in your results, only in your choices of
printf specifiers.  "%f" defaults to "%.6f", that is, print only six
digits after the decimal point.  So a value down around 1e-12 is
going to print as all zeroes.