Re: Logging select statements - Mailing list pgsql-sql

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