Could regexp_matches be immutable?
in [PgSql]
|
From: David Fetter on 15 Oct 2009 14:07 On Thu, Oct 15, 2009 at 10:22:52AM -0700, Josh Berkus wrote: > > (I'd bet lunch that the one about add_missing_from is bogus, too, > > or could easily be made so. mysql isn't forgiving about missing > > FROM items, so it's hard to believe that they have a lot of such > > things no matter how little they care about Postgres.) > > OpenACS does the old-style DELETEs without a subselect, so they rely > on add-missing-from for that. I had to debug this for another user. Is OpenACS getting enough new deployments to fix this? Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 20 Oct 2009 20:48 Rod Taylor <rod.taylor(a)gmail.com> writes: > I tried making a functional index based on an expression containing > the 2 argument regexp_matches() function. Is there a reason why this > function is not marked immutable instead of normal? So I went to see about making the changes to remove regex_flavor, and was astonished to find that all the regex-related functions are already marked immutable, and AFAICS always have been. This is clearly wrong, and we would have to fix it if we weren't about to remove the GUC. (In principle we should advise people to change the markings in existing databases, but given the lack of complaints it's probably not worth the trouble --- I doubt many applications change regex_flavor on the fly.) So, having dismissed my original off-the-cuff answer to Rod, the next question is what's really going wrong for him. I get this from a quick trial: regression=# create table tt1(f1 text, f2 text); CREATE TABLE regression=# create index tt1i on tt1(regexp_matches(f1,f2)); ERROR: index expression cannot return a set IOW the problem is that regexp_matches returns SETOF, not that it's marked stable (as it should have been). I'm not sure what semantics you were expecting the index to have, but we don't have any useful support for indexes on sets. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Eisentraut on 21 Oct 2009 05:57 On Tue, 2009-10-20 at 20:48 -0400, Tom Lane wrote: > So I went to see about making the changes to remove regex_flavor, and > was astonished to find that all the regex-related functions are already > marked immutable, and AFAICS always have been. This is clearly wrong, > and we would have to fix it if we weren't about to remove the GUC. > (In principle we should advise people to change the markings in existing > databases, but given the lack of complaints it's probably not worth the > trouble --- I doubt many applications change regex_flavor on the fly.) Are you sure this wasn't intentional, because it breaks performance and we doubted that many applications would change regex_flavor on the fly? -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 21 Oct 2009 09:12 Peter Eisentraut <peter_e(a)gmx.net> writes: > On Tue, 2009-10-20 at 20:48 -0400, Tom Lane wrote: >> So I went to see about making the changes to remove regex_flavor, and >> was astonished to find that all the regex-related functions are already >> marked immutable, and AFAICS always have been. This is clearly wrong, >> and we would have to fix it if we weren't about to remove the GUC. > Are you sure this wasn't intentional, because it breaks performance and > we doubted that many applications would change regex_flavor on the fly? Intentional or not, it's wrong :-( In practice I doubt there are many cases where constant-folding a regex would be possible or performance-critical. The real use of having it be immutable is probably Rod's, ie, using it in an index. And that is *obviously* really dangerous if there's a GUC affecting the results. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Rod Taylor on 21 Oct 2009 09:45
> So, having dismissed my original off-the-cuff answer to Rod, the next > question is what's really going wrong for him. Â I get this from > a quick trial: I wish I had kept specific notes on what I was actually trying to do. I tried to_number first then the expression as seen below. I guess I saw the error again and assumed it was the same as for to_number. sk=# BEGIN; BEGIN sk=# sk=# create table t1 (col1 text); CREATE TABLE sk=# INSERT INTO t1 values ('Z342432'); INSERT 0 1 sk=# INSERT INTO t1 values ('REW9432'); INSERT 0 1 sk=# sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1; regexp_matches ---------------- 342432 9432 (2 rows) sk=# sk=# create index t1_idx ON t1 (( (regexp_matches(col1, '(\d+)$'))[1] )); ERROR: index expression cannot return a set sk=# sk=# ROLLBACK; ROLLBACK It is interesting that "citext" seems to be functional with exactly the same statements. sk=# BEGIN; BEGIN sk=# sk=# create table t1 (col1 citext); CREATE TABLE sk=# INSERT INTO t1 values ('Z342432'); INSERT 0 1 sk=# INSERT INTO t1 values ('REW9432'); INSERT 0 1 sk=# sk=# SELECT (regexp_matches(col1, '(\d+)$'))[1] from t1; regexp_matches ---------------- 342432 9432 (2 rows) sk=# sk=# create index t1_idx ON t1 (( (regexp_matches(col1, '(\d+)$'))[1] )); CREATE INDEX sk=# sk=# ROLLBACK; ROLLBACK The function regexp_replace(col1, '^[^0-9]+', '') does seem to do the trick for text. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |