Thread: transactional shared variable in postgresql
Hi ,<br /><br />Is there any way to set a variable from a web application (using dbi/perl , libpq etc),<br />and access thesame variable from a C trigger inside a transaction ?<br /><br />the %_SHARED hash available in plperl provides only sessionlevel isolation and <br />does not suit the requirement.<br /><br /><br />Original problem:<br /><br />we want tosetup table auditing using table_log or audittrail projects.<br />the triggers used in these systems uses the current databaseuser<br />to log to the audit tables.. <br /><br />In our webapps, we use the same username to connect to the database<br/>for all kind of updates. Hence we are not able to makeout whoo modified <br />what . However at applicationlevel we have different "userid" for different <br />users of the system. we want to somehow pass this "userid" to the databasee<br />server and accesss it from the triggers that implement the audit functions.<br /><br />wethought of using a table of single row and single column to store <br />the userid but we are concerned over the performancedue to locking <br />issues.<br /><br />Regds<br />Mallah.<br />
Why dont you try to make 'username' part of your query. While auditing updates and inserts, the username can be some how made a part of the tuple being inserted. Just a thought! --Imad www.EnterpriseDB.com On 11/18/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote: > Hi , > > Is there any way to set a variable from a web application (using dbi/perl , > libpq etc), > and access the same variable from a C trigger inside a transaction ? > > the %_SHARED hash available in plperl provides only session level isolation > and > does not suit the requirement. > > > Original problem: > > we want to setup table auditing using table_log or audittrail projects. > the triggers used in these systems uses the current database user > to log to the audit tables.. > > In our webapps, we use the same username to connect to the database > for all kind of updates. Hence we are not able to makeout whoo modified > what . However at application level we have different "userid" for different > users of the system. we want to somehow pass this "userid" to the databasee > server and accesss it from the triggers that implement the audit functions. > > we thought of using a table of single row and single column to store > the userid but we are concerned over the performance due to locking > issues. > > Regds > Mallah. >
hi, We do not want to modify our apps for doing auditing. we are considering table level auditing auditrail http://gborg.postgresql.org/project/audittrail/projdisplay.php seems to be doing a good job. i just need to access the "username" that starts the transaction in webapp from the trigger in audittrail. basically we want to log selected tables of database without modifying our web application code. is it possible to get transaction id (XID) for current transaction ? should it be used ? sorry for ignorance but i read some discouraging remarks regarding using it in apps as it is an internal stuff. regds mallah.
Hi, On Nov 18 06:00, Rajesh Kumar Mallah wrote: > Is there any way to set a variable from a web application (using > dbi/perl , libpq etc), and access the same variable from a C trigger > inside a transaction ? Why don't you use a temporary table for that purpose? Regards.
On 11/18/06, Volkan YAZICI <yazicivo@ttnet.net.tr> wrote: > Hi, > > On Nov 18 06:00, Rajesh Kumar Mallah wrote: > > Is there any way to set a variable from a web application (using > > dbi/perl , libpq etc), and access the same variable from a C trigger > > inside a transaction ? > > Why don't you use a temporary table for that purpose? sounds good, what i understand you mean to say that i create a temp table (with on commit drop option) insert my value there and read the value from inside the trigger. i do not know about the performance aspect of this approach though. i was thinking of a method that could be performed everytime my webapp connected to database. regds mallah. > > > Regards. >
Hi, On Nov 18 06:00, Rajesh Kumar Mallah wrote: > Is there any way to set a variable from a web application (using > dbi/perl , libpq etc), and access the same variable from a C trigger > inside a transaction ? Also you may consider using a sequence for storing an integer value. This approach will probably work faster than a temporary table, by the limitation of variable format to be integer. Regards.
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes: > In our webapps, we use the same username to connect to the database > for all kind of updates. Hence we are not able to makeout whoo modified > what . However at application level we have different "userid" for different > users of the system. we want to somehow pass this "userid" to the databasee > server and accesss it from the triggers that implement the audit functions. Perhaps it would be better to make your "userid" be the actual database user? But anyway, the custom GUC variable facility might serve your needs. http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html regards, tom lane
On 11/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hi,
wouldnt' it affect the connection pooling , i am using DBI::Cache and mod_perl ?
i dont think i understand the usage properly,
i did the below postgresql.conf
custom_variable_classes = 'general' # list of custom variable class names
general.employee = ''
am i supposed to see the variable general.employee when i do "show all " ?
i did not see it in fact!
my question is how can i set it and read it
regds
Mallah.
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> In our webapps, we use the same username to connect to the database
> for all kind of updates. Hence we are not able to makeout whoo modified
> what . However at application level we have different "userid" for different
> users of the system. we want to somehow pass this "userid" to the databasee
> server and accesss it from the triggers that implement the audit functions.
Perhaps it would be better to make your "userid" be the actual database
user? But anyway, the custom GUC variable facility might serve your
Hi,
wouldnt' it affect the connection pooling , i am using DBI::Cache and mod_perl ?
needs.
http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html
i dont think i understand the usage properly,
i did the below postgresql.conf
custom_variable_classes = 'general' # list of custom variable class names
general.employee = ''
am i supposed to see the variable general.employee when i do "show all " ?
i did not see it in fact!
my question is how can i set it and read it
regds
Mallah.
regards, tom lane
Check this one http://archives.postgresql.org/pgsql-patches/2004-04/msg00280.php I have not personally worked on what is described in this page but someone will correct you in the community if anything is going wrong here. --Imad www.EnterpriseDB.com On 11/19/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote: > > > On 11/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes: > > > In our webapps, we use the same username to connect to the database > > > for all kind of updates. Hence we are not able to makeout whoo modified > > > what . However at application level we have different "userid" for > different > > > users of the system. we want to somehow pass this "userid" to the > databasee > > > server and accesss it from the triggers that implement the audit > functions. > > > > Perhaps it would be better to make your "userid" be the actual database > > user? But anyway, the custom GUC variable facility might serve your > > Hi, > > wouldnt' it affect the connection pooling , i am using DBI::Cache and > mod_perl ? > > > needs. > > > http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html > > > i dont think i understand the usage properly, > > i did the below postgresql.conf > custom_variable_classes = 'general' # list of custom variable > class names > general.employee = '' > > am i supposed to see the variable general.employee when i do "show all " ? > i did not see it in fact! > > my question is how can i set it and read it > > regds > Mallah. > > > > regards, tom lane > > > >
On 11/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes: > > In our webapps, we use the same username to connect to the database > > for all kind of updates. Hence we are not able to makeout whoo modified > > what . However at application level we have different "userid" for > different > > users of the system. we want to somehow pass this "userid" to the > databasee > > server and accesss it from the triggers that implement the audit > functions. > > Perhaps it would be better to make your "userid" be the actual database > user? But anyway, the custom GUC variable facility might serve your > needs. > http://www.postgresql.org/docs/8.1/static/runtime-config-custom.html Dear Tom, Thanks for the suggestion , it seems to be working. i used GetConfigOption of guc.c to retrieve the value. I am setting the variable from psql eg: SET general.employee to 101; I hope what i am doing is correct . I have very little experience in using C for triggers. can you please tell if its fine. in postgresql.conf i declared. custom_variable_classes = 'general' And inside the TRIGGER FUNCTION i use the below ------------ x------------------------ int employee = 0 ; /* int for holding employee_id */ char *employee_str ; /* forstoring result for GetConfigOption */ employee_str = (char *) GetConfigOption("general.employee") ; if (employee_str != NULL) { employee = atoi(employee_str); } -------------x---------------------------- also if i do not cast using (char *) the compiler gives a warning: audit_trail.c:82: warning: assignment discards qualifiers from pointer target type why so? I have verified that the variable is isolated in transaction and serves my purpose. Regards Mallah. > > regards, tom lane >