public inbox for [email protected]
help / color / mirror / Atom feedRe: How to properly use TRIM()?
9+ messages / 5 participants
[nested] [flat]
* Re: How to properly use TRIM()?
@ 2026-03-07 07:41 David G. Johnston <[email protected]>
2026-03-07 08:11 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
2026-03-07 19:15 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
2026-03-17 17:29 ` help debugging an issue with selectivity Greg Hennessy <[email protected]>
0 siblings, 3 replies; 9+ messages in thread
From: David G. Johnston @ 2026-03-07 07:41 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, March 7, 2026, Igor Korot <[email protected]> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to properly use TRIM()?
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
@ 2026-03-07 08:11 ` Igor Korot <[email protected]>
2026-03-07 14:29 ` Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-07 15:44 ` Re: How to properly use TRIM()? Adrian Klaver <[email protected]>
2 siblings, 2 replies; 9+ messages in thread
From: Igor Korot @ 2026-03-07 08:11 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Sat, Mar 7, 2026 at 1:41 AM David G. Johnston
<[email protected]> wrote:
>
> On Saturday, March 7, 2026, Igor Korot <[email protected]> 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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to properly use TRIM()?
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-07 08:11 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
@ 2026-03-07 14:29 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2026-03-07 14:29 UTC (permalink / raw)
To: Igor Korot <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Saturday, March 7, 2026, Igor Korot <[email protected]> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to properly use TRIM()?
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-07 08:11 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
@ 2026-03-07 15:44 ` Adrian Klaver <[email protected]>
2026-03-07 19:13 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2026-03-07 15:44 UTC (permalink / raw)
To: Igor Korot <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to properly use TRIM()?
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-07 08:11 ` Re: How to properly use TRIM()? Igor Korot <[email protected]>
2026-03-07 15:44 ` Re: How to properly use TRIM()? Adrian Klaver <[email protected]>
@ 2026-03-07 19:13 ` Igor Korot <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Igor Korot @ 2026-03-07 19:13 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
Hi, Adrian,
On Sat, Mar 7, 2026 at 7:44 AM Adrian Klaver <[email protected]>
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
> [email protected]
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to properly use TRIM()?
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
@ 2026-03-07 19:15 ` Igor Korot <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Igor Korot @ 2026-03-07 19:15 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Hi, David,
On Fri, Mar 6, 2026 at 11:41 PM David G. Johnston <
[email protected]> wrote:
> On Saturday, March 7, 2026, Igor Korot <[email protected]> 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.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* help debugging an issue with selectivity
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
@ 2026-03-17 17:29 ` Greg Hennessy <[email protected]>
2026-03-18 02:37 ` Re: help debugging an issue with selectivity Tom Lane <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Greg Hennessy @ 2026-03-17 17:29 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: help debugging an issue with selectivity
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-17 17:29 ` help debugging an issue with selectivity Greg Hennessy <[email protected]>
@ 2026-03-18 02:37 ` Tom Lane <[email protected]>
2026-03-18 15:56 ` Re: help debugging an issue with selectivity Greg Hennessy <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2026-03-18 02:37 UTC (permalink / raw)
To: Greg Hennessy <[email protected]>; +Cc: [email protected]
Greg Hennessy <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: help debugging an issue with selectivity
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-17 17:29 ` help debugging an issue with selectivity Greg Hennessy <[email protected]>
2026-03-18 02:37 ` Re: help debugging an issue with selectivity Tom Lane <[email protected]>
@ 2026-03-18 15:56 ` Greg Hennessy <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Greg Hennessy @ 2026-03-18 15:56 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
>> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-03-18 15:56 UTC | newest]
Thread overview: 9+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-07 07:41 Re: How to properly use TRIM()? David G. Johnston <[email protected]>
2026-03-07 08:11 ` Igor Korot <[email protected]>
2026-03-07 14:29 ` David G. Johnston <[email protected]>
2026-03-07 15:44 ` Adrian Klaver <[email protected]>
2026-03-07 19:13 ` Igor Korot <[email protected]>
2026-03-07 19:15 ` Igor Korot <[email protected]>
2026-03-17 17:29 ` help debugging an issue with selectivity Greg Hennessy <[email protected]>
2026-03-18 02:37 ` Re: help debugging an issue with selectivity Tom Lane <[email protected]>
2026-03-18 15:56 ` Re: help debugging an issue with selectivity Greg Hennessy <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox