Re: [BUGS] Notes about behaviour of SIMILAR TO operator
Hallo Tom,
I decided to improve similar_escape() function during the weekend.
Thank you very much for the excerpt from SQL standard (I think this is
much more complete than the text I found in a Working Draft from
August 1994). However, there are some more issues I'd like to make
clear before function redesigning.
First of all, I found that SQL spec says that when one of the items of
SIMILAR TO construct (, or
) is NULL, then result of the whole construct should
be unknown. However, PostgreSQL treats SIMILAR TO ... ESCAPE NULL the
same way as when no ESCAPE clause is present, which is wrong:
('a' similar to 'a') is true
('a' similar to 'a' escape null) is true (should be unknown!)
The behaviour above is caused by the fact that escape character is
passed to similar_escape() without checking for null value, and the
same null value is passed to it when there is no ESCAPE clause at all.
I think that either PostgreSQL should check for nulls in SIMILAR TO
construct before calling similar_escape(), or there should be two
versions of similar_escape() function: one getting only one argument
(for SIMILAR TO without ESCAPE) and second, getting two arguments
(a pattern and an escape char). Which solution is better?
> As near as I can tell, the SQL spec requires special characters to be
> escaped when they are inside a bracket construct. So indeed the above
> are invalid SQL regexes.
How the function should behave when such an invalid pattern is passed
as its argument? Should it throw an error (this is what SQL spec
says) or tolerate as much mistakes as possible, generating some
warnings only?
> Good point. Actually, do we want to force ARE mode, or something simpler?
> Perhaps ERE or even BRE would be a better match to the SQL spec.
I think that there is no difference which regexp dialect is choosen,
only the speed matters. Function translating SIMILAR TO patterns into
POSIX regular expressions will be more or less the same. What should
I choose then?
BTW, should I write some regression tests for SIMILAR TO? Is there
any guide how to do it (except PostgreSQL sources)? Should the
changes be written for CVS HEAD only or 7.4/7.3 branches either?
Regards,
--
Adam Buraczewski * Linux user #165585
GCS/TW d- s-:+>+:- a C+++() UL$ P++ L E++ W+ N++ o? K w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI D G++ e+++> h r+>++ y?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Notes about behaviour of SIMILAR TO operator
Adam Buraczewski <[EMAIL PROTECTED]> writes:
> ('a' similar to 'a' escape null) is true (should be unknown!)
Yeah, you are right; this is because we are overloading a "null" second
parameter to mean "the ESCAPE part wasn't present", which in hindsight
wasn't such a hot idea.
> I think that either PostgreSQL should check for nulls in SIMILAR TO
> construct before calling similar_escape(), or there should be two
> versions of similar_escape() function: one getting only one argument
> (for SIMILAR TO without ESCAPE) and second, getting two arguments
> (a pattern and an escape char). Which solution is better?
I think the latter is the only reasonable solution, but it will be
something that we cannot implement in the 7.4.* series, because adding
another function implies initdb. I'd suggest submitting one patch that
fixes everything but the NULL problem, which we could back-patch into
7.4, and then a second patch that splits the function into two for 7.5.
>> As near as I can tell, the SQL spec requires special characters to be
>> escaped when they are inside a bracket construct. So indeed the above
>> are invalid SQL regexes.
> How the function should behave when such an invalid pattern is passed
> as its argument? Should it throw an error (this is what SQL spec
> says) or tolerate as much mistakes as possible, generating some
> warnings only?
I don't have a strong opinion --- could go with either behavior. You
might want to take it up on the pgsql-sql list.
>> Good point. Actually, do we want to force ARE mode, or something simpler?
>> Perhaps ERE or even BRE would be a better match to the SQL spec.
> I think that there is no difference which regexp dialect is choosen,
> only the speed matters. Function translating SIMILAR TO patterns into
> POSIX regular expressions will be more or less the same. What should
> I choose then?
I doubt there would be any speed difference. The advantage of a dumber
RE flavor is that it would have fewer "extra" features that might be
unintentionally triggered by a translated pattern, leading to just the
sort of non-SQL-compliant behavior you are complaining of ...
> BTW, should I write some regression tests for SIMILAR TO?
Sure. Look at some of the existing regression tests for examples.
> Should the changes be written for CVS HEAD only or 7.4/7.3 branches
> either?
I don't see that we'd bother applying it to 7.3, but 7.4 branch yes,
if you avoid any changes in the function's API for the 7.4 version.
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[BUGS] PERFORM bug with FOUND?
Kind people, I've written a little hack, here included, which is supposed to find whether a user is in a group. I'd intended to do a PERFORM instead of SELECT INTO, but PERFORM appears to set FOUND to true no matter what. What's going on here? The hack in question... CREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS ' DECLARE the_user ALIAS FOR $1; the_group ALIAS FOR $2; dummy text; -- SELECT INTO dummy because PERFORM always returns true. -- Is this a bug? BEGIN SELECT INTO dummy u.usename FROM pg_user u , pg_group g WHERE u.usename = the_user AND g.groname = the_group AND u.usesysid = ANY (g.grolist); IF FOUND THEN RETURN true; ELSE RETURN false; END IF; END; ' LANGUAGE 'plpgsql' STRICT IMMUTABLE; TIA for any hints, tips or pointers :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 If you don't vote, don't bitch. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] PERFORM bug with FOUND?
[EMAIL PROTECTED] (David Fetter) writes:
> I've written a little hack, here included, which is supposed to find
> whether a user is in a group. I'd intended to do a PERFORM instead of
> SELECT INTO, but PERFORM appears to set FOUND to true no matter what.
What version are you using? It seems to work per spec in 7.4.
regression=# create function foo(name) returns bool as '
regression'# begin
regression'# perform * from pg_user where usename = $1;
regression'# return found;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo('postgres');
foo
-
t
(1 row)
regression=# select foo('not');
foo
-
f
(1 row)
regression=#
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[BUGS] PATCH: Uninitialized variable usage in contrib/pg_autovacuum
Sorry about the cross-post -- I'm not 100% sure which list this should be sent to. I noticed in the 7.4 release that in contrib/pg_autovacuum, args->logfile is not initialized if a log file is not specified on the command line. This causes an immediate segfault on systems that fill allocated memory with some value other than zero (my FreeBSD machine uses 0xD0). Several crashes later I discovered that args->user, password, host, and port are also used without being initialized. This doesn't appear to be fixed in CVS and I came up empty on a mailing list search -- hope it hasn't been reported already. Attached is a quick patch that fixes the problem. -- Craig --- contrib/pg_autovacuum/pg_autovacuum.c.orig Fri Nov 21 17:11:32 2003 +++ contrib/pg_autovacuum/pg_autovacuum.c Fri Nov 21 17:37:28 2003 @@ -811,6 +811,11 @@ args->analyze_scaling_factor = -1; args->debug = AUTOVACUUM_DEBUG; args->daemonize = 0; + args->user = 0; + args->password = 0; + args->host = 0; + args->logfile = 0; + args->port = 0; /* * Fixme: Should add some sanity checking such as positive integer ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
