From: Rudi Starcevic <rudi@oasis.net.au>
To: pgsql-sql@postgresql.org
Subject: Re: Logging select statements
Date: Wed, 09 Jul 2003 16:55:57 +1000
Message-ID: <3F0BBC7D.7060303@oasis.net.au> (raw)
In-Reply-To: <Pine.LNX.4.44.0307090935410.5838-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0307090935410.5838-100000@matrix.gatewaynet.com>
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
>>
>>
>>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rudi@oasis.net.au
Subject: Re: Logging select statements
In-Reply-To: <3F0BBC7D.7060303@oasis.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox