Thread: lower() for varchar data by creating an index
Hi,
I have a table called t1 with field f1 of type varchar(40):
CREATE TABLE t1 (f1 varchar(40));
To make a case insensitive search I build the query like
SELECT f1 FROM t1 WHERE lower( f1 ) LIKE 'alex%';
This works fine. But when I try to make an index to speed up the query using the command
CREATE INDEX t1_f1_idx ON t1 (lower(f1));
I receive the following error:
ERROR: DefineIndex: function 'lower(varchar)' does not exist
Why by executing the query the function 'lower(varchar)' exists and by creating the index don't?
I use Postgres 7.0.
Best regards,
Alex
CREATE function lower(varchar) returns text as 'lower' language 'internal' with (iscachable); It's a bit slower than if it was actually in the backend but it's a very tiny difference. I spoke to Tom Lane about this, it's on the TODO list I believe for 7.1. In the meantime the above will work great.. - Mitch "The only real failure is quitting." ----- Original Message ----- From: Alex Guryanow <gav@nlr.ru> To: <pgsql-sql@postgresql.org> Sent: Wednesday, May 17, 2000 3:51 AM Subject: [SQL] lower() for varchar data by creating an index > Hi, > > I have a table called t1 with field f1 of type varchar(40): > > CREATE TABLE t1 (f1 varchar(40)); > > To make a case insensitive search I build the query like > > SELECT f1 FROM t1 WHERE lower( f1 ) LIKE 'alex%'; > > This works fine. But when I try to make an index to speed up the query using the command > > CREATE INDEX t1_f1_idx ON t1 (lower(f1)); > > I receive the following error: > > ERROR: DefineIndex: function 'lower(varchar)' does not exist > > Why by executing the query the function 'lower(varchar)' exists and by creating the index don't? > > I use Postgres 7.0. > > Best regards, > Alex > > >
OK, but how can I delete this function? By attempting to remove it I've got an error: my-db=> DROP FUNCTION lower(varchar); ERROR: RemoveFunction: function "lower" is built-in I want to delete it because I receive strange sorting results (you can read about them in my mail with subj "strange ORDER BY implementation"). Alex Wednesday, May 17, 2000, 6:31:50 PM Mitch wrote: MV> CREATE function lower(varchar) returns text as 'lower' language 'internal' MV> with (iscachable); MV> It's a bit slower than if it was actually in the backend but it's a very MV> tiny difference. I spoke to Tom Lane about this, it's on the TODO list I MV> believe for 7.1. In the meantime the above will work great.. MV> - Mitch MV> "The only real failure is quitting." MV> ----- Original Message ----- MV> From: Alex Guryanow <gav@nlr.ru> MV> To: <pgsql-sql@postgresql.org> MV> Sent: Wednesday, May 17, 2000 3:51 AM MV> Subject: [SQL] lower() for varchar data by creating an index >> Hi, >> >> I have a table called t1 with field f1 of type varchar(40): >> >> CREATE TABLE t1 (f1 varchar(40)); >> >> To make a case insensitive search I build the query like >> >> SELECT f1 FROM t1 WHERE lower( f1 ) LIKE 'alex%'; >> >> This works fine. But when I try to make an index to speed up the query MV> using the command >> >> CREATE INDEX t1_f1_idx ON t1 (lower(f1)); >> >> I receive the following error: >> >> ERROR: DefineIndex: function 'lower(varchar)' does not exist >> >> Why by executing the query the function 'lower(varchar)' exists and by MV> creating the index don't? >> >> I use Postgres 7.0. >> >> Best regards, >> Alex >> >> >>
Alex Guryanow <gav@nlr.ru> writes:
> my-db=> DROP FUNCTION lower(varchar);
> ERROR: RemoveFunction: function "lower" is built-in
You can get rid of it by deleting the pg_proc tuple directly. I wonder
though whether RemoveFunction isn't being overly protective --- is there
any good reason not to allow people to delete built-in functions?
Obviously you have only yourself to blame if you delete integer equals
or something equally critical ;-) ... but there are a boatload of
built-ins that are by no means critical. Comments anyone?
regards, tom lane
> Alex Guryanow <gav@nlr.ru> writes: > > my-db=> DROP FUNCTION lower(varchar); > > ERROR: RemoveFunction: function "lower" is built-in > > You can get rid of it by deleting the pg_proc tuple directly. I wonder > though whether RemoveFunction isn't being overly protective --- is there > any good reason not to allow people to delete built-in functions? > Obviously you have only yourself to blame if you delete integer equals > or something equally critical ;-) ... but there are a boatload of > built-ins that are by no means critical. Comments anyone? I would throw a notice and keep going. Should I commit the change? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> You can get rid of it by deleting the pg_proc tuple directly. I wonder
>> though whether RemoveFunction isn't being overly protective --- is there
>> any good reason not to allow people to delete built-in functions?
>> Obviously you have only yourself to blame if you delete integer equals
>> or something equally critical ;-) ... but there are a boatload of
>> built-ins that are by no means critical. Comments anyone?
> I would throw a notice and keep going. Should I commit the change?
What's the point of a notice? "You just deleted OID equals. Better
luck with your next database." Either we think this is too dangerous to
be allowed even to the dbadmin, or we don't.
Actually, isn't there a backend switch that you have to set in order to
do *really* dangerous stuff (DML operations on the system classes, for
example)? Maybe the right answer is to allow deletion of builtin
function entries only when that's set.
But on third thought, it's a little silly to guard the pg_proc entries
so carefully when we'll happily let the admin blow away the
corresponding pg_operator entries. So I'd say just lose that error
check completely...
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> You can get rid of it by deleting the pg_proc tuple directly. I wonder > >> though whether RemoveFunction isn't being overly protective --- is there > >> any good reason not to allow people to delete built-in functions? > >> Obviously you have only yourself to blame if you delete integer equals > >> or something equally critical ;-) ... but there are a boatload of > >> built-ins that are by no means critical. Comments anyone? > > > I would throw a notice and keep going. Should I commit the change? > > What's the point of a notice? "You just deleted OID equals. Better > luck with your next database." Either we think this is too dangerous to > be allowed even to the dbadmin, or we don't. > > Actually, isn't there a backend switch that you have to set in order to > do *really* dangerous stuff (DML operations on the system classes, for > example)? Maybe the right answer is to allow deletion of builtin > function entries only when that's set. > > But on third thought, it's a little silly to guard the pg_proc entries > so carefully when we'll happily let the admin blow away the > corresponding pg_operator entries. So I'd say just lose that error > check completely... But I think we should make sure they know they just deleted a built-in. Seems like good feedback to a user who accidentally deletes one then can't figure out why his database is busted. I can see that happening, and a NOTICE helps prevent really stupid bug reports. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Fri, 19 May 2000, Bruce Momjian wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > >> You can get rid of it by deleting the pg_proc tuple directly. I wonder > > >> though whether RemoveFunction isn't being overly protective --- is there > > >> any good reason not to allow people to delete built-in functions? > > >> Obviously you have only yourself to blame if you delete integer equals > > >> or something equally critical ;-) ... but there are a boatload of > > >> built-ins that are by no means critical. Comments anyone? > > > > > I would throw a notice and keep going. Should I commit the change? > > > > What's the point of a notice? "You just deleted OID equals. Better > > luck with your next database." Either we think this is too dangerous to > > be allowed even to the dbadmin, or we don't. > > > > Actually, isn't there a backend switch that you have to set in order to > > do *really* dangerous stuff (DML operations on the system classes, for > > example)? Maybe the right answer is to allow deletion of builtin > > function entries only when that's set. > > > > But on third thought, it's a little silly to guard the pg_proc entries > > so carefully when we'll happily let the admin blow away the > > corresponding pg_operator entries. So I'd say just lose that error > > check completely... > > > But I think we should make sure they know they just deleted a built-in. > Seems like good feedback to a user who accidentally deletes one then > can't figure out why his database is busted. I can see that happening, > and a NOTICE helps prevent really stupid bug reports. Perhaps this might be a possible idea: 1) Only let the PostgreSQL `super-user' delete internal functions, 2) Let her delete the delete the non-essential functions with a default to yes question before deleteion 3) Let her delete the nearly essential functions with a stronger worded message and a default to no. 4) Do not allow deleteion of vital functions. Somewhere in the doco please describe how to replace the functions from the template or where-ever. -- Sincerely etc., NAME Christopher Sawtell - iOpen Technologies Ltd.CELL PHONE 021 257 4451ICQ UIN 45863470EMAIL chris @ iopen. co . nz, csawtell @ xtra . co . nzWWW http://www.iopen.co.nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Tom Lane writes: > What's the point of a notice? "You just deleted OID equals. Better > luck with your next database." Either we think this is too dangerous to > be allowed even to the dbadmin, or we don't. I'd have to agree. > Actually, isn't there a backend switch that you have to set in order to > do *really* dangerous stuff (DML operations on the system classes, for > example)? Maybe the right answer is to allow deletion of builtin > function entries only when that's set. You might be referring to pg_shadow.usecatupd, but that only covers direct catalog updates. The postgres -O switch allows system schema changes, that doesn't apply either. Anyway, shouldn't you be able to do CREATE FUNCTION xxx (...) LANGUAGE 'internal'; to recreate it? (And that wouldn't actually require things like oideq to be in pg_proc, would it?) -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> What's the point of a notice? "You just deleted OID equals. Better
>> luck with your next database." Either we think this is too dangerous to
>> be allowed even to the dbadmin, or we don't.
> Anyway, shouldn't you be able to do CREATE FUNCTION xxx (...) LANGUAGE
> 'internal'; to recreate it? (And that wouldn't actually require things
> like oideq to be in pg_proc, would it?)
I was thinking that the CREATE needs to insert index entries, which
depends on having the datatype-specific procs that will be called by
the index access method. (Not sure if oideq is actually one of the
ones used by any of the indexes on pg_proc, but you get my drift.)
If I had some spare time I'd try it in a junk database...
regards, tom lane