Thread: Logging select statements
Hi, I have an application where user's can view records in a short form with their first select and view a long form with a second select. The first view I term an impression. The second view I term a click. I'd like to log the impression's and click's. I'm wondering which is the most effiecient way to do this. I know I can do it in the application, PHP, by looping through the result set and inserting into a logging table but am wondering if it quicker to write a rule or trigger so that each individual select is logged into a logging table as it's selected. For example: If I have a table of 3000 row's and the user submits a query which retrieve's 100 rows. In the first senario I could loop through the 100, using a language PHP or Perl, and make 100 inserts after the first select is complete. Thus 1 select plus 100 inserts. Can you see a way to do this all in SQL that would be better/faster/more efficient without using PHP/Perl ? Many thanks Regards Rudi.
Thats why people who want entreprise apps must use enterprise frameworks. In J2EE for instance you could use LOG4J which is sorta equivalent of syslog for java. See if there is a logging module for PHP. PgSQL has no clue of who the user is. I dont think delegating this logging task to pgSQL is a good idea. Dont get me wrong, I like and use php myself, but only when i know the exact limits of how far the specific project will go in the future. On Wed, 9 Jul 2003, Rudi Starcevic wrote: > Hi, > > I have an application where user's can view records in a short form with > their first select > and view a long form with a second select. > The first view I term an impression. > The second view I term a click. > > I'd like to log the impression's and click's. > I'm wondering which is the most effiecient way to do this. > > I know I can do it in the application, PHP, by looping through the > result set and inserting into a logging table but > am wondering if it quicker to write a rule or trigger so that each > individual select is logged into a logging table > as it's selected. > > For example: > If I have a table of 3000 row's and the user submits a query which > retrieve's 100 rows. > In the first senario I could loop through the 100, using a language PHP > or Perl, and make 100 inserts after the first select is complete. > Thus 1 select plus 100 inserts. > > Can you see a way to do this all in SQL that would be better/faster/more > efficient without using PHP/Perl ? > > Many thanks > Regards > Rudi. > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
Thanks Achilleus,<br /><br /> I know there's a couple of ways I could do this.<br /><br /> In my first email I can see asenario of 1 select plus 100 inserts.<br /><br /> Another may be 1 select plus 1 insert.<br /> For example;<br /> In a tableof 3000 rows a user submits a query which returns 100 rows.<br /> I could loop through the result set and build a stringof id's ( 1,2,5,7,8,9,44,22 etc ) and<br /> make one insert into a logging table of the entire string.<br /><br />At a later time, say once every 24 hours, I could select each row of id's and make further inserts into another<br /> loggingtable.<br /> Eg. I extract 1 row with a string of 100 key's and make 100 inserts into a second log table.<br /><br/> I could even then use a 'count(id), date GROUP BY date' select to add a single row to a further logging table<br/> which has one row per id with a count of all impressions/click's for that day.<br /><br /> Thanks<br /> I'm justtrying to explore way's of logging hits and maximize performance for the end user.<br /> I hope I explained all thatOK and didn't ramble to much.<br /><br /> Cheers<br /> Rudi.<br /><br /><br /><br /><br /><br /><br /><br /><br /> AchilleusMantzios wrote:<br /><blockquote cite="midPine.LNX.4.44.0307090935410.5838-100000@matrix.gatewaynet.com" type="cite"><prewrap="">Thats why people who want entreprise apps must use enterprise frameworks. In J2EE for instance you could use LOG4J which is sorta equivalent of syslog for java. See if there is a logging module for PHP. PgSQL has no clue of who the user is. I dont think delegating this logging task to pgSQL is a good idea. Dont get me wrong, I like and use php myself, but only when i know the exact limits of how far the specific project will go in the future. On Wed, 9 Jul 2003, Rudi Starcevic wrote: </pre><blockquote type="cite"><pre wrap="">Hi, I have an application where user's can view records in a short form with their first select and view a long form with a second select. The first view I term an impression. The second view I term a click. I'd like to log the impression's and click's. I'm wondering which is the most effiecient way to do this. I know I can do it in the application, PHP, by looping through the result set and inserting into a logging table but am wondering if it quicker to write a rule or trigger so that each individual select is logged into a logging table as it's selected. For example: If I have a table of 3000 row's and the user submits a query which retrieve's 100 rows. In the first senario I could loop through the 100, using a language PHP or Perl, and make 100 inserts after the first select is complete. Thus 1 select plus 100 inserts. Can you see a way to do this all in SQL that would be better/faster/more efficient without using PHP/Perl ? Many thanks Regards Rudi. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster </pre></blockquote><pre wrap=""> </pre></blockquote><br />
Rudi, i would suggest using a proved framework for logging. Why dont you try to build a function in PHP that acts as syslog. .i.e you have facility (name of your app/page) priority (usually e.g. info in your case) action (what to do with the message, file, apache log, pgsql insert etc..) Then in your calling function you specify facility,priority,your message, and the previous table is consulted as to which action to take. In your message you could put in as ::text the numbers you want. For instance if the page is named foo.php, then the facility would be foo.php-impression, foo.php-click. So you only call a php function. On Wed, 9 Jul 2003, Rudi Starcevic wrote: > Thanks Achilleus, > > I know there's a couple of ways I could do this. > > In my first email I can see a senario of 1 select plus 100 inserts. > > Another may be 1 select plus 1 insert. > For example; > In a table of 3000 rows a user submits a query which returns 100 rows. > I could loop through the result set and build a string of id's ( > 1,2,5,7,8,9,44,22 etc ) and > make one insert into a logging table of the entire string. > > At a later time, say once every 24 hours, I could select each row of > id's and make further inserts into another > logging table. > Eg. I extract 1 row with a string of 100 key's and make 100 inserts into > a second log table. > > I could even then use a 'count(id), date GROUP BY date' select to add a > single row to a further logging table > which has one row per id with a count of all impressions/click's for > that day. > > Thanks > I'm just trying to explore way's of logging hits and maximize > performance for the end user. > I hope I explained all that OK and didn't ramble to much. > > Cheers > Rudi. > > > > > > > > > Achilleus Mantzios wrote: > > >Thats why people who want entreprise apps > >must use enterprise frameworks. > > > >In J2EE for instance you could use LOG4J > >which is sorta equivalent of syslog for java. > > > >See if there is a logging module for PHP. > > > >PgSQL has no clue of who the user is. > >I dont think delegating this logging task > >to pgSQL is a good idea. > > > >Dont get me wrong, > >I like and use php myself, but only when i know > >the exact limits of how far the specific project > >will go in the future. > > > >On Wed, 9 Jul 2003, Rudi Starcevic wrote: > > > > > > > >>Hi, > >> > >>I have an application where user's can view records in a short form with > >>their first select > >>and view a long form with a second select. > >>The first view I term an impression. > >>The second view I term a click. > >> > >>I'd like to log the impression's and click's. > >>I'm wondering which is the most effiecient way to do this. > >> > >>I know I can do it in the application, PHP, by looping through the > >>result set and inserting into a logging table but > >>am wondering if it quicker to write a rule or trigger so that each > >>individual select is logged into a logging table > >>as it's selected. > >> > >>For example: > >>If I have a table of 3000 row's and the user submits a query which > >>retrieve's 100 rows. > >>In the first senario I could loop through the 100, using a language PHP > >>or Perl, and make 100 inserts after the first select is complete. > >>Thus 1 select plus 100 inserts. > >> > >>Can you see a way to do this all in SQL that would be better/faster/more > >>efficient without using PHP/Perl ? > >> > >>Many thanks > >>Regards > >>Rudi. > >> > >> > >> > >> > >> > >> > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Don't 'kill -9' the postmaster > >> > >> > >> > > > > > > > > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
Achilleus, Thanks - I'll look into that. Cheers Regards Rudi. > Rudi, i would suggest using a proved framework > for logging. > > Why dont you try to build a function in PHP > that acts as syslog. > .i.e you have > facility (name of your app/page) > priority (usually e.g. info in your case) > action (what to do with the message, file, apache log, pgsql insert > etc..) > > Then in your calling function you specify facility,priority,your > message, and the previous table is consulted as to which action to take. > > In your message you could put in as ::text the numbers you want. > > For instance if the page is named foo.php, then > the facility would be foo.php-impression, > foo.php-click. > > So you only call a php function. > > On Wed, 9 Jul 2003, Rudi Starcevic wrote: > > > Thanks Achilleus, > > > > I know there's a couple of ways I could do this. > > > > In my first email I can see a senario of 1 select plus 100 inserts. > > > > Another may be 1 select plus 1 insert. > > For example; > > In a table of 3000 rows a user submits a query which returns 100 rows. > > I could loop through the result set and build a string of id's ( > > 1,2,5,7,8,9,44,22 etc ) and > > make one insert into a logging table of the entire string. > > > > At a later time, say once every 24 hours, I could select each row of > > id's and make further inserts into another > > logging table. > > Eg. I extract 1 row with a string of 100 key's and make 100 inserts into > > a second log table. > > > > I could even then use a 'count(id), date GROUP BY date' select to add a > > single row to a further logging table > > which has one row per id with a count of all impressions/click's for > > that day. > > > > Thanks > > I'm just trying to explore way's of logging hits and maximize > > performance for the end user. > > I hope I explained all that OK and didn't ramble to much. > > > > Cheers > > Rudi. > > > > > > > > > > > > > > > > > > Achilleus Mantzios wrote: > > > > >Thats why people who want entreprise apps > > >must use enterprise frameworks. > > > > > >In J2EE for instance you could use LOG4J > > >which is sorta equivalent of syslog for java. > > > > > >See if there is a logging module for PHP. > > > > > >PgSQL has no clue of who the user is. > > >I dont think delegating this logging task > > >to pgSQL is a good idea. > > > > > >Dont get me wrong, > > >I like and use php myself, but only when i know > > >the exact limits of how far the specific project > > >will go in the future. > > > > > >On Wed, 9 Jul 2003, Rudi Starcevic wrote: > > > > > > > > > > > >>Hi, > > >> > > >>I have an application where user's can view records in a short form with > > >>their first select > > >>and view a long form with a second select. > > >>The first view I term an impression. > > >>The second view I term a click. > > >> > > >>I'd like to log the impression's and click's. > > >>I'm wondering which is the most effiecient way to do this. > > >> > > >>I know I can do it in the application, PHP, by looping through the > > >>result set and inserting into a logging table but > > >>am wondering if it quicker to write a rule or trigger so that each > > >>individual select is logged into a logging table > > >>as it's selected. > > >> > > >>For example: > > >>If I have a table of 3000 row's and the user submits a query which > > >>retrieve's 100 rows. > > >>In the first senario I could loop through the 100, using a language PHP > > >>or Perl, and make 100 inserts after the first select is complete. > > >>Thus 1 select plus 100 inserts. > > >> > > >>Can you see a way to do this all in SQL that would be better/faster/more > > >>efficient without using PHP/Perl ? > > >> > > >>Many thanks > > >>Regards > > >>Rudi. > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >>---------------------------(end of broadcast)--------------------------- > > >>TIP 4: Don't 'kill -9' the postmaster > > >> > > >> > > >> > > > > > > > > > > > > > > > -- > ================================================================== > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel: +30-210-8981112 > fax: +30-210-8981877 > email: achill at matrix dot gatewaynet dot com > mantzios at softlab dot ece dot ntua dot gr
Hi Rudi, You can't trigger on a SELECT, but you could wrap your SQL in a set returning function... http://techdocs.postgresql.org/guides/SetReturningFunctions Here is a rough and ready solution: CREATE TABLE access_log ( id int not null ); CREATE TABLE datatable (id int not null primary key,somedata varchar(255) not null); INSERT INTO datatable VALUES( 1, 'apple' ); INSERT INTO datatable VALUES( 2, 'orange' ); INSERT INTO datatable VALUES( 3, 'banana' ); CREATE OR REPLACE FUNCTION get_rows_and_log( varchar ) RETURNS SETOF record AS 'DECLARE r record;BEGIN FOR r IN EXECUTE ''SELECT * FROM '' || $1 LOOP INSERT INTO access_log VALUES( r.id ); RETURN NEXT r; END LOOP; RETURN;END; ' LANGUAGE 'plpgsql'; Now, as an example, do: SELECT * FROM get_rows_and_log( 'datatable' ) AS data( id int, somedata varchar); You'll get the data returned, and the log entries will be made. You can put your WHERE clause in the parameter: SELECT * FROM get_rows_and_log( 'datatable WHERE somedata LIKE ''%e''' ) AS data( id int, somedata varchar); Hope that is what you were after! Cheers Matthew. On Wednesday, July 9, 2003, at 04:55 PM, Rudi Starcevic wrote: > Thanks Achilleus, > > I know there's a couple of ways I could do this. > > In my first email I can see a senario of 1 select plus 100 inserts. > > Another may be 1 select plus 1 insert. > For example; > In a table of 3000 rows a user submits a query which returns 100 rows. > I could loop through the result set and build a string of id's ( > 1,2,5,7,8,9,44,22 etc ) and > make one insert into a logging table of the entire string.
Matthew, Gee thanks .. I just read over Stephan's Set Returning Function last night .. I was trying to see how I could use it. > Hope that is what you were after! Indeed it is. Your 'rough and ready solution' solution is a mighty fine place to begin. Thanks aplenty to you and Achilleus for taking the time to look at this for me - another reason why I love PostgreSQL ! :-) Best regards Rudi. > Hi Rudi, > > You can't trigger on a SELECT, but you could wrap your SQL in a set > returning function... > > http://techdocs.postgresql.org/guides/SetReturningFunctions > > Here is a rough and ready solution: > > CREATE TABLE access_log ( id int not null ); > > CREATE TABLE datatable ( > id int not null primary key, > somedata varchar(255) not null > ); > > INSERT INTO datatable VALUES( 1, 'apple' ); > INSERT INTO datatable VALUES( 2, 'orange' ); > INSERT INTO datatable VALUES( 3, 'banana' ); > > CREATE OR REPLACE FUNCTION get_rows_and_log( varchar ) RETURNS SETOF > record AS > ' > DECLARE > r record; > BEGIN > FOR r IN EXECUTE ''SELECT * FROM '' || $1 LOOP > INSERT INTO access_log VALUES( r.id ); > RETURN NEXT r; > END LOOP; > RETURN; > END; > ' > LANGUAGE 'plpgsql'; > > Now, as an example, do: > > SELECT * FROM get_rows_and_log( 'datatable' ) AS data( id int, > somedata varchar); > > You'll get the data returned, and the log entries will be made. > > You can put your WHERE clause in the parameter: > > SELECT * FROM get_rows_and_log( 'datatable WHERE somedata LIKE > ''%e''' ) AS data( id int, somedata varchar); > > Hope that is what you were after! > > Cheers > > Matthew. > > On Wednesday, July 9, 2003, at 04:55 PM, Rudi Starcevic wrote: > > > Thanks Achilleus, > > > > I know there's a couple of ways I could do this. > > > > In my first email I can see a senario of 1 select plus 100 inserts. > > > > Another may be 1 select plus 1 insert. > > For example; > > In a table of 3000 rows a user submits a query which returns 100 rows. > > I could loop through the result set and build a string of id's ( > > 1,2,5,7,8,9,44,22 etc ) and > > make one insert into a logging table of the entire string.