pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtransactional shared variable in postgresql
10+ messages / 4 participants
[nested] [flat]
* transactional shared variable in postgresql
@ 2006-11-18 12:30 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 12:30 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 13:46 imad <immaad@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: imad @ 2006-11-18 13:46 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 15:01 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: imad <immaad@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 15:01 UTC (permalink / raw)
To: imad <immaad@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 17:36 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Volkan YAZICI @ 2006-11-18 17:36 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 17:58 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 17:58 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 18:34 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Volkan YAZICI @ 2006-11-18 18:34 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 18:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
3 siblings, 2 replies; 10+ messages in thread
From: Tom Lane @ 2006-11-18 18:40 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 19:15 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-18 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-18 19:45 imad <immaad@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: imad @ 2006-11-18 19:45 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
> >
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: transactional shared variable in postgresql
@ 2006-11-20 10:37 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Rajesh Kumar Mallah @ 2006-11-20 10:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 ; /* for storing 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-11-20 10:37 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-18 12:30 transactional shared variable in postgresql Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 13:46 ` imad <immaad@gmail.com>
2006-11-18 15:01 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 17:36 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-18 17:58 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 18:34 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-18 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-18 19:15 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-11-18 19:45 ` imad <immaad@gmail.com>
2006-11-20 10:37 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
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