Thread

1. How can I optimize a research on text field?

   Jérome Knöbl <jknobl@mandanet.ch> — 1998-10-30T15:38:55Z

   Hello,
   
   What index method can I use for a text field to have a better
   optimisation.
   
   I tried btree and hash method, but when I make a clause like : where nom
   like '%PICASSO%'; the request don't use any index!?!
   
   example:
   mayer98=> explain select count(*) from artistes, oeuvres where
   artistes.ref=oeuvres.refartiste and nom like '%PICASSO%';
   NOTICE:  QUERY PLAN:
   
   Aggregate  (cost=4894.04 size=0 width=0)
     ->  Nested Loop  (cost=4894.04 size=874 width=8)
           ->  Seq Scan on artistes  (cost=4837.37 size=1 width=4)
           ->  Index Scan on oeuvres  (cost=56.67 size=685510 width=4)
   
   EXPLAIN
   
   Jérôme Knöbl
   
   
   
   

2. Re: [SQL] How can I optimize a research on text field?

   Mike Meyer <mwm@phone.net> — 1998-10-30T18:20:14Z

   On Fri, 30 Oct 1998, Jerome Knobl wrote:
   > I tried btree and hash method, but when I make a clause like : where nom
   > like '%PICASSO%'; the request don't use any index!?!
   
   Check the FAQ, question 3.10 (or thereabouts). Text searches only use
   the index if they are searching for text at the start of the field.
   
   	<mike
   
   
   
   

3. Rules (was: Re: [SQL] How can I optimize...)

   Jan Wieck <jwieck@debis.com> — 1998-11-04T11:35:17Z

   G.Elangovan wrote:
   
   >
   > Can any one help me to CREATE RULE
   > please give me an example with
   > a simple table(s)  definition and to create rule on that table
   
       It's   on   my   personal   TODO  to  create  a  rule  system
       documentation for users  too.  The  one  in  the  programmers
       manual  for  v6.4  is  a bit technical, but you might want to
       read it anyway.
   
       Anything below addresses the v6.4 rule system!
   
       Sample 1 - constraint follow/delete
   
           CREATE TABLE prim (
               pkey text,
               comment text);
   
           CREATE TABLE ref (
               rkey text,
               skey integer,
               comment text);
   
           --
           -- Rule to let ref.rkey follow prim.pkey
           --
           CREATE RULE prim_follow AS ON UPDATE TO prim
               WHERE NEW.pkey != OLD.pkey DO
               UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
   
           --
           -- Rule to constraint delete references from ref to pkey
           --
           CREATE RULE prim_delete AS ON DELETE TO prim DO
               DELETE FROM ref WHERE rkey = OLD.pkey;
   
           --
           -- Some tests
           --
           INSERT INTO prim VALUES ('k1', 'to get updated');
           INSERT INTO prim VALUES ('k2', 'to get deleted');
           INSERT INTO ref VALUES ('k1', 1, 'must follow');
           INSERT INTO ref VALUES ('k1', 2, 'must follow');
           INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
           INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
   
           UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
           SELECT * FROM ref;
           rkey|skey|comment
           ----+----+----------------
           k2  |   1|must get deleted
           k2  |   2|must get deleted
           new1|   1|must follow
           new1|   2|must follow
           (4 rows)
   
           DELETE FROM prim WHERE pkey = 'k2';
           SELECT * FROM ref;
           rkey|skey|comment
           ----+----+-----------
           new1|   1|must follow
           new1|   2|must follow
           (2 rows)
   
       Sample 2 - logging of table changes
   
           CREATE TABLE emp (
               ename text,
               dept text,
               salary money);
   
           CREATE TABLE emp_log (
               ename text,
               action text,
               newsal money,
               uname name,
               when  datetime);
   
           --
           -- Rule to log new created employees
           --
           CREATE RULE emp_ins AS ON INSERT TO emp DO
               INSERT INTO emp_log VALUES (
                   NEW.ename,
                   'employed',
                   NEW.salary,
                   getpgusername(),
                   'now'::text);
   
           --
           -- Rule to log when salary raises
           --
           CREATE RULE emp_raise AS ON UPDATE TO emp
               WHERE NEW.salary > OLD.salary DO
               INSERT INTO emp_log VALUES (
                   NEW.ename,
                   'raised',
                   NEW.salary,
                   getpgusername(),
                   'now'::text);
   
           --
           -- Rule to log when salary is lowered
           --
           CREATE RULE emp_lower AS ON UPDATE TO emp
               WHERE NEW.salary < OLD.salary DO
               INSERT INTO emp_log VALUES (
                   NEW.ename,
                   'lowered',
                   NEW.salary,
                   getpgusername(),
                   'now'::text);
   
           --
           -- Rule to log when employee is fired
           --
           CREATE RULE emp_fire AS ON DELETE TO emp DO
               INSERT INTO emp_log VALUES (
                   OLD.ename,
                   'fired',
                   '$0',
                   getpgusername(),
                   'now'::text);
   
   
           --
           -- Some tests
           --
           INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
           INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
   
           UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
           UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
   
           DELETE FROM emp WHERE ename = 'gates';
   
           SELECT * FROM emp_log;
           ename|action  |newsal    |uname|when
           -----+--------+----------+-----+----------------------------
           wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
           gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
           wieck|raised  |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
           gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
           gates|fired   |$0.00     |pgsql|Wed Nov 04 12:19:30 1998 MET
           (5 rows)
   
       Note: The explicit 'now'::text  is required.  Otherwise 'now' 
       is interpreted at CREATE RULE time and  all log  entries will
       contain that date instead of the time it happened.
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

4. Re: [SQL] How can I optimize a research on text field?

   G.Elangovan <elangog@wipinfo.soft.net> — 1998-11-04T14:29:45Z

   Can any one help me to CREATE RULE
   please give me an example with
   a simple table(s)  definition and to create rule on that table
   
   Thanku , looking for ur favourable reply
   
   
   
   

5. Re: Rules (was: Re: [SQL] How can I optimize...)

   Jan Wieck <jwieck@debis.com> — 1998-11-05T12:08:25Z

   >
   > I tried Creating RULE with both the example
   > In first example both the rule failed with error "old table not found"
   > In case of second example
   > i managed to create
   >
   > [...]
   >
   > ERROR:  old: Table does not exist.
   > Please giveme some more info on the KEY word (NEW and OLD).attribute
   > Note : iam using postgresql 6.3.2
   >
   > Thanku , for ur kind help
   
       DON'T  TRY  TO USE THE RULE SYSTEM IN A v6.3.2 DATABASE. IT'S
       BUGGY AND DOES NOT WORK AT ALL.
   
       All I wrote depends on the major changes  I've  done  in  the
       rule system for v6.4.
   
       In  v6.4 CURRENT is aliased with OLD and in v6.5 CURRENT will
       disappear completely.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

6. Re: Rules (was: Re: [SQL] How can I optimize...)

   G.Elangovan <elangog@wipinfo.soft.net> — 1998-11-05T15:36:37Z

   I tried Creating RULE with both the example
   In first example both the rule failed with error "old table not found"
   In case of second example
   i managed to create 
   
   but other three rule failed
   CREATE RULE emp_lower AS ON UPDATE TO emp
               WHERE NEW.salary < OLD.salary DO
               INSERT INTO emp_log VALUES (
                   NEW.ename,
                   'lowered',
                   NEW.salary,
                   getpgusername(),
                   'now'::text);
   ERROR:  old: Table does not exist.
   CREATE RULE emp_fire AS ON DELETE TO emp DO
               INSERT INTO emp_log VALUES (
                   OLD.ename,
                   'fired',
                   '$0',
                   getpgusername(),
                   'now'::text);
   ERROR:  old: Table does not exist.
   Please giveme some more info on the KEY word (NEW and OLD).attribute
   Note : iam using postgresql 6.3.2
   
   Thanku , for ur kind help
   
   
   On Wed, 4 Nov 1998, Jan Wieck wrote:
   
   > G.Elangovan wrote:
   > 
   > >
   > > Can any one help me to CREATE RULE
   > > please give me an example with
   > > a simple table(s)  definition and to create rule on that table
   > 
   >     It's   on   my   personal   TODO  to  create  a  rule  system
   >     documentation for users  too.  The  one  in  the  programmers
   >     manual  for  v6.4  is  a bit technical, but you might want to
   >     read it anyway.
   > 
   >     Anything below addresses the v6.4 rule system!
   > 
   >     Sample 1 - constraint follow/delete
   > 
   >         CREATE TABLE prim (
   >             pkey text,
   >             comment text);
   > 
   >         CREATE TABLE ref (
   >             rkey text,
   >             skey integer,
   >             comment text);
   > 
   >         --
   >         -- Rule to let ref.rkey follow prim.pkey
   >         --
   >         CREATE RULE prim_follow AS ON UPDATE TO prim
   >             WHERE NEW.pkey != OLD.pkey DO
   >             UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
   > 
   >         --
   >         -- Rule to constraint delete references from ref to pkey
   >         --
   >         CREATE RULE prim_delete AS ON DELETE TO prim DO
   >             DELETE FROM ref WHERE rkey = OLD.pkey;
   > 
   >         --
   >         -- Some tests
   >         --
   >         INSERT INTO prim VALUES ('k1', 'to get updated');
   >         INSERT INTO prim VALUES ('k2', 'to get deleted');
   >         INSERT INTO ref VALUES ('k1', 1, 'must follow');
   >         INSERT INTO ref VALUES ('k1', 2, 'must follow');
   >         INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
   >         INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
   > 
   >         UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
   >         SELECT * FROM ref;
   >         rkey|skey|comment
   >         ----+----+----------------
   >         k2  |   1|must get deleted
   >         k2  |   2|must get deleted
   >         new1|   1|must follow
   >         new1|   2|must follow
   >         (4 rows)
   > 
   >         DELETE FROM prim WHERE pkey = 'k2';
   >         SELECT * FROM ref;
   >         rkey|skey|comment
   >         ----+----+-----------
   >         new1|   1|must follow
   >         new1|   2|must follow
   >         (2 rows)
   > 
   >     Sample 2 - logging of table changes
   > 
   >         CREATE TABLE emp (
   >             ename text,
   >             dept text,
   >             salary money);
   > 
   >         CREATE TABLE emp_log (
   >             ename text,
   >             action text,
   >             newsal money,
   >             uname name,
   >             when  datetime);
   > 
   >         --
   >         -- Rule to log new created employees
   >         --
   >         CREATE RULE emp_ins AS ON INSERT TO emp DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'employed',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when salary raises
   >         --
   >         CREATE RULE emp_raise AS ON UPDATE TO emp
   >             WHERE NEW.salary > OLD.salary DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'raised',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when salary is lowered
   >         --
   >         CREATE RULE emp_lower AS ON UPDATE TO emp
   >             WHERE NEW.salary < OLD.salary DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'lowered',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when employee is fired
   >         --
   >         CREATE RULE emp_fire AS ON DELETE TO emp DO
   >             INSERT INTO emp_log VALUES (
   >                 OLD.ename,
   >                 'fired',
   >                 '$0',
   >                 getpgusername(),
   >                 'now'::text);
   > 
   > 
   >         --
   >         -- Some tests
   >         --
   >         INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
   >         INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
   > 
   >         UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
   >         UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
   > 
   >         DELETE FROM emp WHERE ename = 'gates';
   > 
   >         SELECT * FROM emp_log;
   >         ename|action  |newsal    |uname|when
   >         -----+--------+----------+-----+----------------------------
   >         wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
   >         gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
   >         wieck|raised  |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
   >         gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
   >         gates|fired   |$0.00     |pgsql|Wed Nov 04 12:19:30 1998 MET
   >         (5 rows)
   > 
   >     Note: The explicit 'now'::text  is required.  Otherwise 'now' 
   >     is interpreted at CREATE RULE time and  all log  entries will
   >     contain that date instead of the time it happened.
   > 
   > Jan
   > 
   > --
   > 
   > #======================================================================#
   > # It's easier to get forgiveness for being wrong than for being right. #
   > # Let's break this rule - forgive me.                                  #
   > #======================================== jwieck@debis.com (Jan Wieck) #
   > 
   > 
   > 
   
   
   

7. Re: Rules (was: Re: [SQL] How can I optimize...)

   G.Elangovan <elangog@wipinfo.soft.net> — 1998-11-05T16:54:20Z

   My first mail was bit incomplete
   
   Note : OLD is not recognized when i used to create rule on example given
   below,i tried with CURRENT instead of OLD , rule was created 
   but when i execute some operation on it backend connection get closed
   
   my practical result is
   CREATE TABLE prim (
   >             pkey text,
   >             comment text);
   > 
   >         CREATE TABLE ref (
   >             rkey text,
   >             skey integer,
   >             comment text);
   >           
   is Ok
   CREATE RULE emp_ins AS ON INSERT TO emp DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'employed',
   >                 NEW.salary,   
   >                 getpgusername(),
   >                 'now'::text);
   is also OK Created
   CREATE RULE emp_del as on DELETE to emp 
   do DELETE from emp_log where ename=OLD.ename
   
   old table not found
   but
   systemdb=> CREATE RULE emp_del as on DELETE to emp
   systemdb-> do DELETE from emp_log where ename=current.ename
   systemdb-> ;
   CREATE
   systemdb=> 
   systemdb=> select * from emp;         
   ename  |dept       |salary    
   -------+-----------+----------
   wieck  |development|$8,000.00 
   gates  |management |$15,000.00
   elangog|lucent     |$1,000.00 
   (3 rows)
   systemdb=> delete from emp where ename='gates';
   PQexec() -- Request was sent to backend, but backend closed the channel
   before responding.
           This probably means the backend terminated abnormally before or
   while processing the request.
   systemdb=> 
   
   please help me
   to create a rule on DELETE 
   
   Thanku ,expecting helpful reply
   
   
   
   
   
   
   
   On Wed, 4 Nov 1998, Jan Wieck wrote:
   
   > G.Elangovan wrote:
   > 
   > >
   > > Can any one help me to CREATE RULE
   > > please give me an example with
   > > a simple table(s)  definition and to create rule on that table
   > 
   >     It's   on   my   personal   TODO  to  create  a  rule  system
   >     documentation for users  too.  The  one  in  the  programmers
   >     manual  for  v6.4  is  a bit technical, but you might want to
   >     read it anyway.
   > 
   >     Anything below addresses the v6.4 rule system!
   > 
   >     Sample 1 - constraint follow/delete
   > 
   >         CREATE TABLE prim (
   >             pkey text,
   >             comment text);
   > 
   >         CREATE TABLE ref (
   >             rkey text,
   >             skey integer,
   >             comment text);
   > 
   >         --
   >         -- Rule to let ref.rkey follow prim.pkey
   >         --
   >         CREATE RULE prim_follow AS ON UPDATE TO prim
   >             WHERE NEW.pkey != OLD.pkey DO
   >             UPDATE ref SET rkey = NEW.pkey WHERE rkey = OLD.pkey;
   > 
   >         --
   >         -- Rule to constraint delete references from ref to pkey
   >         --
   >         CREATE RULE prim_delete AS ON DELETE TO prim DO
   >             DELETE FROM ref WHERE rkey = OLD.pkey;
   > 
   >         --
   >         -- Some tests
   >         --
   >         INSERT INTO prim VALUES ('k1', 'to get updated');
   >         INSERT INTO prim VALUES ('k2', 'to get deleted');
   >         INSERT INTO ref VALUES ('k1', 1, 'must follow');
   >         INSERT INTO ref VALUES ('k1', 2, 'must follow');
   >         INSERT INTO ref VALUES ('k2', 1, 'must get deleted');
   >         INSERT INTO ref VALUES ('k2', 2, 'must get deleted');
   > 
   >         UPDATE prim SET pkey = 'new1' WHERE pkey = 'k1';
   >         SELECT * FROM ref;
   >         rkey|skey|comment
   >         ----+----+----------------
   >         k2  |   1|must get deleted
   >         k2  |   2|must get deleted
   >         new1|   1|must follow
   >         new1|   2|must follow
   >         (4 rows)
   > 
   >         DELETE FROM prim WHERE pkey = 'k2';
   >         SELECT * FROM ref;
   >         rkey|skey|comment
   >         ----+----+-----------
   >         new1|   1|must follow
   >         new1|   2|must follow
   >         (2 rows)
   > 
   >     Sample 2 - logging of table changes
   > 
   >         CREATE TABLE emp (
   >             ename text,
   >             dept text,
   >             salary money);
   > 
   >         CREATE TABLE emp_log (
   >             ename text,
   >             action text,
   >             newsal money,
   >             uname name,
   >             when  datetime);
   > 
   >         --
   >         -- Rule to log new created employees
   >         --
   >         CREATE RULE emp_ins AS ON INSERT TO emp DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'employed',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when salary raises
   >         --
   >         CREATE RULE emp_raise AS ON UPDATE TO emp
   >             WHERE NEW.salary > OLD.salary DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'raised',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when salary is lowered
   >         --
   >         CREATE RULE emp_lower AS ON UPDATE TO emp
   >             WHERE NEW.salary < OLD.salary DO
   >             INSERT INTO emp_log VALUES (
   >                 NEW.ename,
   >                 'lowered',
   >                 NEW.salary,
   >                 getpgusername(),
   >                 'now'::text);
   > 
   >         --
   >         -- Rule to log when employee is fired
   >         --
   >         CREATE RULE emp_fire AS ON DELETE TO emp DO
   >             INSERT INTO emp_log VALUES (
   >                 OLD.ename,
   >                 'fired',
   >                 '$0',
   >                 getpgusername(),
   >                 'now'::text);
   > 
   > 
   >         --
   >         -- Some tests
   >         --
   >         INSERT INTO emp VALUES ('wieck', 'development', '$8000.00');
   >         INSERT INTO emp VALUES ('gates', 'management', '$15000.00');
   > 
   >         UPDATE emp SET salary = '$10000.00' WHERE ename = 'wieck';
   >         UPDATE emp SET salary = '$12000.00' WHERE ename = 'gates';
   > 
   >         DELETE FROM emp WHERE ename = 'gates';
   > 
   >         SELECT * FROM emp_log;
   >         ename|action  |newsal    |uname|when
   >         -----+--------+----------+-----+----------------------------
   >         wieck|employed|$8,000.00 |pgsql|Wed Nov 04 12:19:24 1998 MET
   >         gates|employed|$15,000.00|pgsql|Wed Nov 04 12:19:26 1998 MET
   >         wieck|raised  |$10,000.00|pgsql|Wed Nov 04 12:19:27 1998 MET
   >         gates|lowered |$12,000.00|pgsql|Wed Nov 04 12:19:28 1998 MET
   >         gates|fired   |$0.00     |pgsql|Wed Nov 04 12:19:30 1998 MET
   >         (5 rows)
   > 
   >     Note: The explicit 'now'::text  is required.  Otherwise 'now' 
   >     is interpreted at CREATE RULE time and  all log  entries will
   >     contain that date instead of the time it happened.
   > 
   > Jan
   > 
   > --
   > 
   > #======================================================================#
   > # It's easier to get forgiveness for being wrong than for being right. #
   > # Let's break this rule - forgive me.                                  #
   > #======================================== jwieck@debis.com (Jan Wieck) #
   > 
   > 
   > 
   >