Re: Lower or Upper case for F.33. pg_trgm
> On 16 Aug 2022, at 12:17, PG Doc comments form wrote:
> I have a question regarding the trigram algorithm and I can not find any
> information about it in your documentation:
Maybe we should add something about this?
> Do you distinguish between lower and uppercase? Or do you consider all words
> in lowercase?
There is support for compiling pg_trgm case sensitive, but it's by default case
insensitive.
# SELECT word_similarity('word', 'WORD');
word_similarity
-
1
(1 row)
> Happy to get a short feedback from you,
I would recommend the pg_general mailinglist as that will be a safer way to get
general questions answered.
--
Daniel Gustafsson https://vmware.com/
Lower or Upper case for F.33. pg_trgm
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/pgtrgm.html Description: Hey guys, I have a question regarding the trigram algorithm and I can not find any information about it in your documentation: Do you distinguish between lower and uppercase? Or do you consider all words in lowercase? Happy to get a short feedback from you, Greetings, Marc
Re: Lower or Upper case for F.33. pg_trgm
Op 16-08-2022 om 12:36 schreef Daniel Gustafsson:
On 16 Aug 2022, at 12:17, PG Doc comments form wrote:
I have a question regarding the trigram algorithm and I can not find any
information about it in your documentation:
Maybe we should add something about this?
Yeah, it's a bit strange that none of the following strings yield any
info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there
is no mention of the ~ versus ~* difference.
Maybe worth to (already in pgtrgm.html) give the simple hint:
~ is case-sensitive
~* is case-insensitive
In any case a link to functions-matching.html seems indicated.
Erik Rijkers
Do you distinguish between lower and uppercase? Or do you consider all words
in lowercase?
There is support for compiling pg_trgm case sensitive, but it's by default case
insensitive.
# SELECT word_similarity('word', 'WORD');
word_similarity
-
1
(1 row)
Happy to get a short feedback from you,
I would recommend the pg_general mailinglist as that will be a safer way to get
general questions answered.
--
Daniel Gustafsson https://vmware.com/
Re: Lower or Upper case for F.33. pg_trgm
> On 16 Aug 2022, at 12:54, Erik Rijkers wrote: > > Op 16-08-2022 om 12:36 schreef Daniel Gustafsson: >>> On 16 Aug 2022, at 12:17, PG Doc comments form >>> wrote: >>> I have a question regarding the trigram algorithm and I can not find any >>> information about it in your documentation: >> Maybe we should add something about this? > > Yeah, it's a bit strange that none of the following strings yield any info on > that page: 'case', 'sensitiv', 'upper', 'lower', and that there is no > mention of the ~ versus ~* difference. > > Maybe worth to (already in pgtrgm.html) give the simple hint: > ~ is case-sensitive > ~* is case-insensitive > > In any case a link to functions-matching.html seems indicated. Yeah, I think there is room for improvements here. Are you up for drafting a patch for this? -- Daniel Gustafsson https://vmware.com/
AW: PostgreSQL pdf shows 12.11 instead of 12.12
> > Hello % > > The PostgreSQL pdf (A4) shows 12.11 instead of 12.12 > > https://www.postgresql.org/files/documentation/pdf/12/postgresql-12-A4 > .pdf > > >This link shows 12.12 for me. Perhaps you have some cache in your browser that >needs to be cleared? >//Magnus Thanks Magnus. It was indeed a caching issue on one of my Computers. On all others the correct version 12.12 is displayed. Disclaimer: Diese Nachricht und ihr eventuell angehängte Dateien sind nur für den Adressaten bestimmt. Sie kann vertrauliche oder gesetzlich geschützte Daten oder Informationen beinhalten. Falls Sie diese Nachricht irrtümlich erreicht hat, bitten wir Sie höflich, diese unter Ausschluss jeglicher Reproduktion zu löschen und die absendende Person zu benachrichtigen. Danke für Ihre Hilfe. This message and any attached files are for the sole use of the recipient named above. It may contain confidential or legally protected data or information. If you have received this message in error, please delete it without making any copies whatsoever and notify the sender. Thank you for your assistance. smime.p7s Description: S/MIME cryptographic signature
Re: PostgreSQL pdf shows 12.11 instead of 12.12
On Tue, Aug 16, 2022 at 7:48 AM William Sescu (Suva) wrote: > > Hello % > > > > The PostgreSQL pdf (A4) shows 12.11 instead of 12.12 > > > > https://www.postgresql.org/files/documentation/pdf/12/postgresql-12-A4.pdf > > This link shows 12.12 for me. Perhaps you have some cache in your browser that needs to be cleared? //Magnus
Re: Lower or Upper case for F.33. pg_trgm
Thanks for your fast response. Is this a question for me? I am fine with a short hint regarding the default. A link to another documentation is also fine. Am Di., 16. Aug. 2022 um 13:46 Uhr schrieb Daniel Gustafsson < dan...@yesql.se>: > > On 16 Aug 2022, at 12:54, Erik Rijkers wrote: > > > > Op 16-08-2022 om 12:36 schreef Daniel Gustafsson: > >>> On 16 Aug 2022, at 12:17, PG Doc comments form > wrote: > >>> I have a question regarding the trigram algorithm and I can not find > any > >>> information about it in your documentation: > >> Maybe we should add something about this? > > > > Yeah, it's a bit strange that none of the following strings yield any > info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there is > no mention of the ~ versus ~* difference. > > > > Maybe worth to (already in pgtrgm.html) give the simple hint: > > ~ is case-sensitive > > ~* is case-insensitive > > > > In any case a link to functions-matching.html seems indicated. > > Yeah, I think there is room for improvements here. Are you up for > drafting a > patch for this? > > -- > Daniel Gustafsson https://vmware.com/ > >
Re: Lower or Upper case for F.33. pg_trgm
Op 16-08-2022 om 13:46 schreef Daniel Gustafsson: On 16 Aug 2022, at 12:54, Erik Rijkers wrote: Op 16-08-2022 om 12:36 schreef Daniel Gustafsson: On 16 Aug 2022, at 12:17, PG Doc comments form wrote: I have a question regarding the trigram algorithm and I can not find any information about it in your documentation: Maybe we should add something about this? Yeah, it's a bit strange that none of the following strings yield any info on that page: 'case', 'sensitiv', 'upper', 'lower', and that there is no mention of the ~ versus ~* difference. Maybe worth to (already in pgtrgm.html) give the simple hint: ~ is case-sensitive ~* is case-insensitive In any case a link to functions-matching.html seems indicated. Yeah, I think there is room for improvements here. Are you up for drafting a patch for this? How is this? (bluntly stating 'similarity comparisons are case-insensitive' - although I'm not really sure..) Erik -- Daniel Gustafsson https://vmware.com/ --- ./doc/src/sgml/pgtrgm.sgml.orig 2022-08-16 14:50:08.586555358 +0200 +++ ./doc/src/sgml/pgtrgm.sgml 2022-08-16 14:56:39.358617804 +0200 @@ -416,6 +416,8 @@ the above-described similarity operators, and additionally support trigram-based index searches for LIKE, ILIKE, ~, ~* and = queries. + The similarity comparisons are case-insensitive, but these queries can be + case-sensitive (see ). Inequality operators are not supported. Note that those indexes may not be as efficient as regular B-tree indexes for equality operator. @@ -534,7 +536,8 @@ Beginning in PostgreSQL 9.3, these index types also support index searches for regular-expression matches - (~ and ~* operators), for example + (~ and ~* operators, resp. case-sensitive and + case-insensitive), for example SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';
Re: Lower or Upper case for F.33. pg_trgm
Erik Rijkers writes: > (bluntly stating 'similarity comparisons are case-insensitive' - > although I'm not really sure..) Perhaps like "similarity comparisons are case-insensitive in a standard build of pg_trgm", if you want to nod to the existence of a compile option without going into detail. regards, tom lane
Re: Lower or Upper case for F.33. pg_trgm
> On 16 Aug 2022, at 15:53, Tom Lane wrote:
>
> Erik Rijkers writes:
>> (bluntly stating 'similarity comparisons are case-insensitive' -
>> although I'm not really sure..)
>
> Perhaps like "similarity comparisons are case-insensitive in a
> standard build of pg_trgm", if you want to nod to the existence
> of a compile option without going into detail.
Looking at this I'm leaning towards paring down the diff posted upthread with
pretty much this, I think that will provide value while avoid causing
confusion.
As a related side note, there are four instances of "case insensitive{ly}" in
the docs with all other instances using "case-insensitive{ly}". I'm inclined
to fix those four to use a dash while at it to be consistent across all pages.
--
Daniel Gustafsson https://vmware.com/
pg_trgm_case.diff
Description: Binary data
Re: Lower or Upper case for F.33. pg_trgm
Daniel Gustafsson writes:
> Looking at this I'm leaning towards paring down the diff posted upthread with
> pretty much this, I think that will provide value while avoid causing
> confusion.
WFM.
> As a related side note, there are four instances of "case insensitive{ly}" in
> the docs with all other instances using "case-insensitive{ly}". I'm inclined
> to fix those four to use a dash while at it to be consistent across all pages.
+1
regards, tom lane
Confusing bit in the "EXAMPLES" section for INSERT
https://www.postgresql.org/docs/devel/sql-insert.html#id-1.9.3.152.9 Insert a distributor, or do nothing for rows proposed for insertion when an > existing, excluded row (a row with a matching constrained column or columns > after before row insert triggers fire) exists. Example assumes a unique > index has been defined that constrains values appearing in the did column: > Suggesting this as replacement: Insert a distributor, or do nothing if the proposed row conflicts with an existing row. Per-row triggers BEFORE INSERT are fired, and possible effects on the proposed applied, before checking for conflicts. Example assumes a unique index on the did column exists: Regards Erwin
Re: set time zone
On Tue, Dec 7, 2021 at 02:28:09PM +, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/sql-set.html
> Description:
>
> Documentation for the set time zone command can be clearer. What it says for
> v13:
>
> TIME ZONE
> SET TIME ZONE value is an alias for SET timezone TO value. The syntax SET
> TIME ZONE allows special syntax for the time zone specification. Here are
> examples of valid values:
> 'PST8PDT'
> The time zone for Berkeley, California.
>
> See Section 8.5.3 for more information about time zones.
>
> Issue #1.
> There is an example of 'PST8PDT' but there is no explanation as to how to
> build that string to set the time zone. If you follow the recommendation to
> see Section 8.5.3, you encounter Issue #2...
>
> Issue #2.
> Yes you can find more information about time zones in section 8.5.3, but you
> have to read carefully for this paragraph in Section 8.5.3, which explains
> how NOT to set the Time Zone
>
> A time zone abbreviation, for example PST. Such a specification merely
> defines a particular offset from UTC, in contrast to full time zone names
> which can imply a set of daylight savings transition rules as well. The
> recognized abbreviations are listed in the pg_timezone_abbrevs view (see
> Section 51.91). You cannot set the configuration parameters TimeZone or
> log_timezone to a time zone abbreviation, but you can use abbreviations in
> date/time input values and with the AT TIME ZONE operator.
>
> However the prior paragraph says this:
>
> A full time zone name, for example America/New_York. The recognized time
> zone names are listed in the pg_timezone_names view (see Section 51.92).
> PostgreSQL uses the widely-used IANA time zone data for this purpose, so the
> same time zone names are also recognized by other software.
>
> This is where you can find the correct name to use in the set time zone
> command.
>
> SUGGESTION:
>
> Change the documentation for the SET (SQL) command to read as follows, under
> the TIME ZONE section:
>
> TIME ZONE
> ... syntax for the time zone specification. The time zone specification can
> be found in the pg_timezone_names view (see Section 51.92).
Actually, both pg_timezone_names values and POSIX-style time zone
specifications are supported by SET. In the attached patch I mention
that time zone abbreviations are not supported, and then reference the
chapter. I think that should help.
> Issue #3
> The syntax diagram shows this:
>
> SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value'
> | DEFAULT }
> SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
>
> Good job, but - the "{ timezone" entry is not referred to. Below in the TIME
> ZONE Section it says this:
> TIME ZONE
> SET TIME ZONE value is an alias for SET timezone TO value.
>
> The word "value" is highlighted but is not applicable to the syntax diagram
> above.
>
> SUGGESTION
>
> Change the syntax diagram to read:
> SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value'
> | DEFAULT }
> SET [ SESSION | LOCAL ] TIME ZONE { timezone_name | LOCAL | DEFAULT }
>
> and the lower section to read:
>
> TIME ZONE
> SET TIME ZONE timezone_name is an alias for SET timezone TO timezone_name.
I ended up changing the syntax from 'timezone' to 'value' and added
quoted and non-quoted syntax options, again in the attached patch.
Did I miss anything?
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml
index 5878c61f9b..5459b29525 100644
--- a/doc/src/sgml/ref/set.sgml
+++ b/doc/src/sgml/ref/set.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
-SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
+SET [ SESSION | LOCAL ] TIME ZONE { value | 'value' | LOCAL | DEFAULT }
@@ -192,8 +192,8 @@ SELECT setseed(value);
TIME ZONE
- SET TIME ZONE value is an alias
- for SET timezone TO value. The
+ SET TIME ZONE 'value' is an alias
+ for SET timezone TO 'value'. The
syntax SET TIME ZONE allows special syntax
for the time zone specification. Here are examples of valid
values:
@@ -254,7 +254,8 @@ SELECT setseed(value);
- See for more information
+ Time zone abbreviations are not supported by SET;
+ see for more information
about time zones.
Re: Confusing bit in the "EXAMPLES" section for INSERT
Added a missing word: Insert a distributor, or do nothing if the proposed row conflicts with an existing row. Per-row triggers BEFORE INSERT are fired, and possible effects on the proposed row applied, before checking for conflicts. Example assumes a unique index on the did column exists: Regards Erwin On Tue, 16 Aug 2022 at 23:09, Erwin Brandstetter wrote: > https://www.postgresql.org/docs/devel/sql-insert.html#id-1.9.3.152.9 > > Insert a distributor, or do nothing for rows proposed for insertion when >> an existing, excluded row (a row with a matching constrained column or >> columns after before row insert triggers fire) exists. Example assumes a >> unique index has been defined that constrains values appearing in the did >> column: >> > > Suggesting this as replacement: > > Insert a distributor, or do nothing if the proposed row conflicts with an > existing row. Per-row triggers BEFORE INSERT are fired, and possible > effects on the proposed applied, before checking for conflicts. Example > assumes a unique index on the did column exists: > > Regards > Erwin >
Re: Confusing bit in the "EXAMPLES" section for INSERT
Seeing that an earlier, related passage in the text reads: > Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, ... I update my suggestion to use the same expression: Insert a distributor, or do nothing if the proposed row conflicts with an existing row. Per-row BEFORE INSERT triggers are fired, and possible effects on the proposed row applied, before checking for conflicts. Example assumes a unique index on the did column exists: Regards Erwin On Tue, 16 Aug 2022 at 23:09, Erwin Brandstetter wrote: > https://www.postgresql.org/docs/devel/sql-insert.html#id-1.9.3.152.9 > > Insert a distributor, or do nothing for rows proposed for insertion when >> an existing, excluded row (a row with a matching constrained column or >> columns after before row insert triggers fire) exists. Example assumes a >> unique index has been defined that constrains values appearing in the did >> column: >> > > Suggesting this as replacement: > > Insert a distributor, or do nothing if the proposed row conflicts with an > existing row. Per-row triggers BEFORE INSERT are fired, and possible > effects on the proposed applied, before checking for conflicts. Example > assumes a unique index on the did column exists: > > Regards > Erwin >
Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely
Bruce Momjian writes: > I have written the attached patch to mention this issue about sql_body > functions. Spell-check, please. Seems OK otherwise. regards, tom lane
Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely
On Sat, Dec 25, 2021 at 02:36:27PM +, Erki Eessaar wrote: > > Hello > > PostgreSQL 14 added the feature: "Allow SQL-language functions and procedures > to use SQL-standard function bodies." > > If I understand correctly, then in this case the system will track > dependencies between tables and routines that use the tables. Thus, the > SECURITY DEFINER routines that use the new approach do not require the > following mitigation, i.e., SET search_path= is not needed. The following part > of documentation does not mention this. > > https://www.postgresql.org/docs/current/sql-createfunction.html# > SQL-CREATEFUNCTION-SECURITY > > [elephant] PostgreSQL: Documentation: 14: CREATE FUNCTION >Overloading. PostgreSQL allows function overloading; that is, the >same name can be used for several different functions so long as >they have distinct input argument types.Whether or not you use it, >this capability entails security precautions when calling functions >in databases where some users mistrust other users; see Section >10.3.. Two functions are considered the same if they have the same >... >www.postgresql.org I have written the attached patch to mention this issue about sql_body functions. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 7e6d52c7dc..976d4caad6 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -779,7 +779,10 @@ SELECT * FROM dup(42); Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to -ensure that the function cannot be misused. For security, +ensure that the function cannot be misused. This is particularly +important for non-sql_body functions because +their function bodies are evaulated at run-time, not creation time. +For security, should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects (e.g., tables, functions, and
Re: Lower or Upper case for F.33. pg_trgm
Sounds good to me. Am Di., 16. Aug. 2022 um 15:53 Uhr schrieb Tom Lane : > Erik Rijkers writes: > > (bluntly stating 'similarity comparisons are case-insensitive' - > > although I'm not really sure..) > > Perhaps like "similarity comparisons are case-insensitive in a > standard build of pg_trgm", if you want to nod to the existence > of a compile option without going into detail. > > regards, tom lane >
Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely
On Tue, Aug 16, 2022 at 03:34:22PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > I have written the attached patch to mention this issue about sql_body > > functions. > > Spell-check, please. Seems OK otherwise. Just when I think I didn't add enough text to warrant a spell check. :-( Updated patch attached. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 7e6d52c7dc..35869bf6ba 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -779,7 +779,10 @@ SELECT * FROM dup(42); Because a SECURITY DEFINER function is executed with the privileges of the user that owns it, care is needed to -ensure that the function cannot be misused. For security, +ensure that the function cannot be misused. This is particularly +important for non-sql_body functions because +their function bodies are evaluated at run-time, not creation time. +For security, should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects (e.g., tables, functions, and
