[SQL] transactional shared variable in postgresql
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.
Re: [SQL] transactional shared variable in postgresql
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 <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] transactional shared variable in postgresql
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Random()
On 11/17/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da Rocha folgendes: > Hi list, > > I have a bigint collumn and I would like to generate a random number within the > numbers of my column. select from order by random() limit 1; This query will tend to get slower as the table grows because of the sorting. it possible to get a row from a random offset how about select from limit 1 offset 2*random() ; ? regds mallah. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] transactional shared variable in postgresql
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Random()
am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes: > >select from order by random() limit 1; > > This query will tend to get slower as the table grows because of the > sorting. Right. > > it possible to get a row from a random offset > how about > > select from limit 1 offset 2*random() ; For instance, you have a table with only 500 rows and random() returns 0.999? In other words: in think, this is a bad idea, unless you use a constant for the offset-calculation thats not larger than the number of rows in this table. For this, you need this value, but select count(1) from table force a seqscan -> slow. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transactional shared variable in postgresql
On 11/18/06, Volkan YAZICI <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] transactional shared variable in postgresql
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transactional shared variable in postgresql
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Random()
On 11/18/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes: > >select from order by random() limit 1; > > This query will tend to get slower as the table grows because of the > sorting. Right. > > it possible to get a row from a random offset > how about > > select from limit 1 offset 2*random() ; For instance, you have a table with only 500 rows and random() returns 0.999? In other words: in think, this is a bad idea, unless you use a constant for the offset-calculation thats not larger than the number of rows in this table. For this, you need this value, but select count(1) from table force a seqscan -> slow. but order by random() would also forces a seqscan , looks like the random offset method performs better explain analyze select count(*) from general.profile_master; ++ | QUERY PLAN | ++ | Aggregate (cost=139214.26..139214.27 rows=1 width=0) (actual time= 3071.178..3071.179 rows=1 loops=1) | | -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=0) (actual time=0.032..2686.842 rows=601240 loops=1) | | Total runtime: 3071.268ms | ++ (3 rows) tradein_clients=> explain analyze SELECT profile_id from general.profile_master limit 1 offset 601240*random(); ++ | QUERY PLAN | ++ | Limit (cost=13770.30..13770.53 rows=1 width=4) (actual time= 1614.146..1614.147 rows=1 loops=1)| | -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=4) (actual time=0.036..1375.742 rows=429779 loops=1) | | Total runtime: 1614.187ms | ++ (3 rows) tradein_clients=> explain analyze SELECT profile_id from general.profile_master order by random() limit 1; +--+ | QUERY PLAN | +--+ | Limit (cost=207079.39..207079.40 rows=1 width=4) (actual time= 11715.694..11715.695 rows=1 loops=1) | | -> Sort (cost=207079.39..208590.45 rows=604421 width=4) (actual time= 11715.691..11715.691 rows=1 loops=1)| | Sort Key: random() | | -> Seq Scan on profile_master (cost=0.00..139214.26 rows=604421 width=4) (actual time=0.036..4605.259 rows=601241 loops=1) | | Total runtime: 11716.039ms | +--+ (5 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] transactional shared variable in postgresql
On 11/19/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Rajesh Kumar Mallah" <[EMAIL PROTECTED]> 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
Re: [SQL] transactional shared variable in postgresql
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 <[EMAIL PROTECTED]> wrote: On 11/19/06, Tom Lane <[EMAIL PROTECTED]> wrote: > "Rajesh Kumar Mallah" <[EMAIL PROTECTED]> 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 > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How convert UNICODE
Hi I must copy rows from local database (UNICODE) to remote database SQL_ASCII (not on local machine) with converting selected columns to iso8859_2 or iso_8859_5. Can I make it with COPY? On Sat, 18 Nov 2006 09:42:14 -0500, Travis Whitton wrote > You can use the postgresql COPY command to dump a single column to a file. Do this for each column you need to convert. From there, use iconv to convert the data. When you're done dumping and converting, use the unix paste command to reassemble the data. If you're not on a unix-based platform, a simple perl script should do the job. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] planner used functional index in 7.3.6, now does a seq scan in 7.4.7 after upgrade
Greetings,
I've just upgraded from 7.3.6 to 7.4.7 (running on Debian Linux). I
dumped and reloaded my db as part of the upgrade. Everything is working
great, except that one query that executed in < 1 ms on 7.3.6 now takes
> 500 ms on 7.4.7. When I look at the query plan, the planner is no
longer taking advantage of a functional index (the index is correctly
defined as immutable), and is doing a sequence scan instead. I'm
stumped -- I can't see any reason why this would be broken. Out of
desperation, I tried dropping and recreating the index, and dropping and
recreating the function. I've also run "ANALYZE" on the tables, in
addition to the regular analysis and vacuums that I run frequently as
part of regular maintenance. No luck.
Has anyone else run into something like this? Any hints would be much
appreciated. FWIW, I've provided all the gory details below.
Thank you!!
Chris
This is the query:
SELECT
data_stored.id as data_stored_id,
patient_data.id as patient_data_id,
stereo_image_attributes.id as
stereo_id,
stereo_image_attributes.x_offset as
stereo_x_offset,
stereo_image_attributes.y_offset as
stereo_y_offset,
stereo_image_attributes.stereo_swap as
stereo_swap,
stereo_image_attributes.analysis_date as
stereo_analysis_date,
stereo_image_attributes.analysis_detail as
stereo_analysis_detail,
stereo_image_attributes.analysis_storage_id as
stereo_analysis_storage_id
FROM
opt_stereo_pair_image_attributes stereo_image_attributes,
opt_patient_data patient_data,
opt_patient_data_entries patient_data_entry,
opt_patient_data_stored data_stored
WHERE
patient_data.version_id = ? AND
patient_data_entry.patient_data_id = patient_data.id AND
patient_data_entry.id =
data_stored.patient_data_entry_id AND
data_stored.id = stereo_id (
stereo_image_attributes.left_patient_data_stored_id,
stereo_image_attributes.right_patient_data_id
stereo_image_attributes.left_patient_data_id )
I then run EXPLAIN ANALYZE on 7.3.6 and 7.4.7, with the same query
parameter. here's what EXPLAIN ANALYZE yields on 7.3.6. note that it
*is* using the functional index "stereo_pair_image_attributes_stereo_id"
QUERY PLAN
---
Nested Loop (cost=0.00..5331.34 rows=2 width=64) (actual
time=0.13..0.52 rows=2 loops=1)
-> Nested Loop (cost=0.00..153.23 rows=4 width=20) (actual
time=0.10..0.45 rows=7 loops=1)
-> Nested Loop (cost=0.00..8.52 rows=16 width=12) (actual
time=0.05..0.18 rows=37 loops=1)
-> Index Scan using patient_data_version_id on
opt_patient_data patient_data (cost=0.00..4.75 rows=1 width=4) (actual
time=0.03..0.03 rows=1 loops=1)
Index Cond: (version_id = 323268)
-> Index Scan using opt_patient_data_id_key on
opt_patient_data_entries patient_data_entry (cost=0.00..3.40 rows=30
width=8) (actual time=0.01..0.09 rows=37 loops=1)
Index Cond: (patient_data_entry.patient_data_id =
"outer".id)
-> Index Scan using opt_patient_data_stored_entry_count on
opt_patient_data_stored data_stored (cost=0.00..8.85 rows=2 width=8)
(actual time=0.01..0.01 rows=0 loops=37)
Index Cond: ("outer".id = data_stored.patient_data_entry_id)
-> Index Scan using stereo_pair_image_attributes_stereo_id on
opt_stereo_pair_image_attributes stereo_image_attributes
(cost=0.00..1454.62 rows=451 width=44) (actual time=0.01..0.01 rows=0
loops=7)
Index Cond: ("outer".id =
stereo_id(stereo_image_attributes.left_patient_data_stored_id,
stereo_image_attributes.right_patient_data_id,
stereo_image_attributes.left_patient_data_id))
Total runtime: 0.62 msec
(12 rows)
And then on 7.4.7. note that it is *not* using the functional index,
but is instead doing a seq scan on opt_stereo_pair_image_attributes.
QUERY PLAN
--
Re: [SQL] planner used functional index in 7.3.6, now does a seq scan in 7.4.7 after upgrade
Chris Tennant <[EMAIL PROTECTED]> writes: > here's the definition of the function (as immutable): > CREATE OR REPLACE FUNCTION stereo_id (INTEGER, INTEGER, INTEGER) RETURNS > INTEGER AS > 'BEGIN RETURN CASE WHEN $2 = $3 THEN $1 ELSE -1 END;END;' >LANGUAGE 'plpgsql' IMMUTABLE; > and here's the original definition of the functional index: > CREATE INDEX stereo_pair_image_attributes_stereo_id > ON opt_stereo_pair_image_attributes > (stereo_id(right_patient_data_id,left_patient_data_id)); Um, that index seems to be on some other function that may have the same name, but only takes two arguments? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
