Thread
-
How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T07:30:38Z
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. -
Re: How to properly use TRIM()?
David G. Johnston <david.g.johnston@gmail.com> — 2026-03-07T07:41:43Z
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?” Nullif is correct, you just need to specify an empty array (of the correct type) for the second argument. Given that error message, an array of text is the correct type. Array[]::text[] David J.
-
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T08:11:18Z
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?” Nullif is 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. > -
Re: How to properly use TRIM()?
David G. Johnston <david.g.johnston@gmail.com> — 2026-03-07T14:29:48Z
On Saturday, March 7, 2026, Igor Korot <ikorot01@gmail.com> wrote: > ' > name | tablespace | included | storage > --------------+------------+----------+--------- > leagues_pkey | | {} | > (1 row) > > Included is one, storage is not. > That’s a terrible way to determine the data type of a column. Did read the error message you were given? Did you read the documentation? David J. -
Re: How to properly use TRIM()?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-07T15:44:30Z
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?” Nullif is 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
-
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T19:13:42Z
Hi, Adrian, On Sat, Mar 7, 2026 at 7:44 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > 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?” Nullif is 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[] Then why it’s not showing {} as in the “included” columns? Or it’s an ARRAY() implementation? Thank you. > > > > 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 > -
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T19:15:27Z
Hi, David, On Fri, Mar 6, 2026 at 11:41 PM 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?” Nullif is correct, you just need > to specify an empty array (of the correct type) for the second argument. > > Given that error message, an array of text is the correct type. > > Array[]::text[] > So what is the proper syntax? Thank you. > David J. > >
-
Re: How to properly use TRIM()?
Rob Sargent <robjsargent@gmail.com> — 2026-03-07T19:19:52Z
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. > 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 -
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T19:58:22Z
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 -
Re: How to properly use TRIM()?
David G. Johnston <david.g.johnston@gmail.com> — 2026-03-07T20:03:19Z
On Sat, Mar 7, 2026 at 12:58 PM Igor Korot <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. David J.
-
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T20:46:09Z
Hi, David, On Sat, Mar 7, 2026 at 12:03 PM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Sat, Mar 7, 2026 at 12:58 PM Igor Korot <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. 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. >
-
Re: How to properly use TRIM()?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-03-07T21:29:07Z
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
-
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T21:46:42Z
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
-
Re: How to properly use TRIM()?
David G. Johnston <david.g.johnston@gmail.com> — 2026-03-07T22:26:35Z
On Sat, Mar 7, 2026 at 2:46 PM Igor Korot <ikorot01@gmail.com> wrote: > 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? > > Ok, you are indeed performing an iteration of SQLGetData that does return SQL_NO_DATA when you've exhausted the contents of the field being retrieved. You still need to check ind[3] for: https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-function?view=sql-server-ver17#retrieving-data-with-sqlgetdata Step 2 I have no idea why you would end up in an infinite loop there though. I suppose maybe step 2's lack of describing the flow when the data is null means you need to break out of the loop manually after dealing with the null value in some manner. David J.
-
Re: How to properly use TRIM()?
Igor Korot <ikorot01@gmail.com> — 2026-03-07T23:08:52Z
Hi, David, On Sat, Mar 7, 2026 at 2:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > On Sat, Mar 7, 2026 at 2:46 PM Igor Korot <ikorot01@gmail.com> wrote: > >> 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? >> >> > Ok, you are indeed performing an iteration of SQLGetData that does return > SQL_NO_DATA when you've exhausted the contents of the field being retrieved. > > You still need to check ind[3] for: > > https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetdata-function?view=sql-server-ver17#retrieving-data-with-sqlgetdata > Step 2 > > I have no idea why you would end up in an infinite loop there though. I > suppose maybe step 2's lack of describing the flow when the data is null > means you need to break out of the loop manually > 3754 while( ( ret = SQLGetData( m_hstmt, 3, SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA ) (gdb) n 3756 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) (gdb) p ind[2] $1 = 4 (gdb) n 3758 auto numBytes = ind[2]; (gdb) 3759 if( ind[2] == SQL_NO_TOTAL ) (gdb) 3761 else if( ind[2] > 255 ) (gdb) 3763 str_to_uc_cpy( includedCol, included.get() ); (gdb) 3764 } (gdb) 3754 while( ( ret = SQLGetData( m_hstmt, 3, SQL_C_WCHAR, included.get(), 255, &ind[2] ) ) != SQL_NO_DATA ) (gdb) 3771 includedCol.erase( 0, 1 ); (gdb) p ind[2] $2 = 4 (gdb) p ret $3 = 100 (gdb) n This is what happens with column 3 when the array is empty. 3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR, index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA ) (gdb) 3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) (gdb) p ind[3] $4 = -1 (gdb) p ret $5 = 0 (gdb) n 3783 auto numBytes = ind[3]; (gdb) 3784 if( ind[3] == SQL_NO_TOTAL ) (gdb) 3786 else if( ind[3] > 255 ) (gdb) 3788 str_to_uc_cpy( options, index_param.get() ); (gdb) 3789 } (gdb) 3779 while( ( ret = SQLGetData( m_hstmt, pos, SQL_C_WCHAR, index_param.get(), 255, &ind[3] ) ) != SQL_NO_DATA ) (gdb) 3781 if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) (gdb) p ind[3] $6 = -1 (gdb) p ret $7 = 0 (gdb) And this one is for column 4 of the query. As you can see both calls return SQL_SUCCESS, but the indicator does contain SQL_NULL_DATA (-1). I think this is the bug in the driver, as it should return SQL_NO_DATA. I'll confirm with the ODBC list. Thx. after dealing with the null value in some manner. > > David J. > > > >
-
help debugging an issue with selectivity
Greg Hennessy <greg.hennessy@gmail.com> — 2026-03-17T17:29:39Z
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 -
Re: help debugging an issue with selectivity
Rob Sargent <robjsargent@gmail.com> — 2026-03-18T00:38:11Z
/— 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 > > > -
Re: help debugging an issue with selectivity
Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-18T02:37:35Z
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
-
Re: help debugging an issue with selectivity
Greg Hennessy <greg.hennessy@gmail.com> — 2026-03-18T15:56:20Z
>> 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. My apologies. > 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. > > Agreed. I should have noticed that my self. Greg