Re: [GENERAL] Creating a session variable in Postgres
Hi Thx Mike, it's the best solution i think.
But i did some modifications to the code since i need to store an integer I
wrote the following:
#include "postgres.h"
#include "fmgr.h"
int32 session_data;
PG_FUNCTION_INFO_V1(setvalue);
Datum setvalue(PG_FUNCTION_ARGS) {
session_data = PG_GETARG_INT32(0);
PG_RETURN_BOOL(true);
}
PG_FUNCTION_INFO_V1(getvalue);
Datum getvalue(PG_FUNCTION_ARGS) {
PG_RETURN_INT32(session_data);
}
ANY COMMENTS ARE WELCOMED.
Najib.
- Original Message -
From: "Mike Mascari" <[EMAIL PROTECTED]>
To: "Nagib Abi Fadel" <[EMAIL PROTECTED]>
Cc: "Manfred Koizar" <[EMAIL PROTECTED]>; "Bruce Momjian"
<[EMAIL PROTECTED]>; "generalpost" <[EMAIL PROTECTED]>
Sent: Friday, June 04, 2004 11:21 AM
Subject: Re: [GENERAL] Creating a session variable in Postgres
> Nagib Abi Fadel wrote:
>
> > So considering those facts, it would be better to use the Sequence
Method,
> > since it would only require cleaning up one table
> > Or is there anything else i am missing ???
>
> It is becoming more of a toss-op. Prior to 7.4, the system indexes
> would grow until a manual REINDEX was issued in a stand-alone
> backend. In 7.4, the dead tuples remain, but at least can be re-used
> once they've been marked that way by the occassional vacuum.
> autovacuum will tend to make dead-tuple reclaimation transparent,
> like Oracle.
>
> The absolutely cheapest method is to write a pair of functions in
> 'C' that sets/gets a global variable:
>
> #include "postgres.h"
> #include "fmgr.h"
>
> #define MAX_DATA 64
>
> char session_data[MAX_DATA] = "";
>
> PG_FUNCTION_INFO_V1(setvalue);
>
> Datum setvalue(PG_FUNCTION_ARGS) {
>
> text *value;
> long len;
>
> value = PG_GETARG_TEXT_P(0);
> len = VARSIZE(value) - VARHDRSZ;
> if (len >= MAX_DATA) {
>elog(ERROR, "setvalue: value too long: %li", len);
> }
> memcpy(session_data, VARDATA(value), len);
> session_data[len] = 0;
>
> PG_RETURN_BOOL(true);
>
> }
>
> PG_FUNCTION_INFO_V1(getvalue);
>
> Datum getvalue(PG_FUNCTION_ARGS) {
>
> text *result;
> long len;
>
> len = strlen(session_data) + VARHDRSZ;
> result = (text *) palloc(len);
> VARATT_SIZEP(result) = len;
> memcpy(VARDATA(result), session_data, len - VARHDRSZ);
>
> PG_RETURN_TEXT_P(result);
>
> }
>
> -- Compile
>
> gcc -c example.c -I/usr/include/pgsql/server
> gcc -shared -o pgexample.so pgexample.o
>
> -- Install somewhere PostgreSQL can get at it
>
> cp pgexample.so /usr/local/mypglibs
>
> -- Create the functions where path-to-lib is the path to
> -- the shared library.
>
> CREATE OR REPLACE FUNCTION setvalue(text) RETURNS boolean
> AS '/usr/local/mypglibs/pgexample.so'
> LANGUAGE 'C' WITH (isStrict);
>
> CREATE OR REPLACE FUNCTION getvalue() RETURNS text
> AS '/usr/local/mypglibs/pgexample.so'
> LANGUAGE 'C' WITH (isStrict);
>
> Now all you need to to is invoke setvalue() at the start of the
> session, and build views around getvalue():
>
> CREATE VIEW v_foo AS
> SELECT *
> FROM foo
> WHERE foo.key = getvalue();
>
> At the start of a session:
>
> SELECT setvalue('Mike Mascari was here');
>
> Hope that helps,
>
> Mike Mascari
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Creating a session variable in Postgres
Wait i did the following test: SELECT count(*) from pg_class ; count --- 894 SELECT count(*) from pg_attribute ; count --- 7264 CREATE temp table temp_test (id integer); CREATE TABLE SELECT count(*) from pg_class ; count --- 895 SELECT count(*) from pg_attribute ; count --- 7272 I DISCONNECTED AND RECONNECTED TO THE DATABASE: NOW SELECT count(*) from pg_class ; count --- 894 SELECT count(*) from pg_attribute ; count --- 7264 According to those results the meta_data for a temp table are automatically removed when the table is destroyed So - Original Message - From: "Nagib Abi Fadel" <[EMAIL PROTECTED]> To: "Manfred Koizar" <[EMAIL PROTECTED]> Cc: "Mike Mascari" <[EMAIL PROTECTED]>; "Bruce Momjian" <[EMAIL PROTECTED]>; "generalpost" <[EMAIL PROTECTED]> Sent: Friday, June 04, 2004 11:14 AM Subject: Re: [GENERAL] Creating a session variable in Postgres > So considering those facts, it would be better to use the Sequence Method, > since it would only require cleaning up one table > Or is there anything else i am missing ??? > > > - Original Message - > From: "Manfred Koizar" <[EMAIL PROTECTED]> > To: "Nagib Abi Fadel" <[EMAIL PROTECTED]> > Cc: "Mike Mascari" <[EMAIL PROTECTED]>; "Bruce Momjian" > <[EMAIL PROTECTED]>; "generalpost" <[EMAIL PROTECTED]> > Sent: Friday, June 04, 2004 08:32 AM > Subject: Re: [GENERAL] Creating a session variable in Postgres > > > > On Fri, 4 Jun 2004 08:25:38 +0200, "Nagib Abi Fadel" > > <[EMAIL PROTECTED]> wrote: > > >The use of sequence would require to clean up the table every N hour . > > > > Right. > > > > >The use of temporary table wouldn't require any cleanup. > > > > Wrong. You would have to clean up the meta data, at least pg_class and > > pg_attribute, maybe pg_index also. For the price of one temp table you > > can have several rows in a permanent table. > > > > Servus > > Manfred > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Creating a session variable in Postgres
So considering those facts, it would be better to use the Sequence Method, since it would only require cleaning up one table Or is there anything else i am missing ??? - Original Message - From: "Manfred Koizar" <[EMAIL PROTECTED]> To: "Nagib Abi Fadel" <[EMAIL PROTECTED]> Cc: "Mike Mascari" <[EMAIL PROTECTED]>; "Bruce Momjian" <[EMAIL PROTECTED]>; "generalpost" <[EMAIL PROTECTED]> Sent: Friday, June 04, 2004 08:32 AM Subject: Re: [GENERAL] Creating a session variable in Postgres > On Fri, 4 Jun 2004 08:25:38 +0200, "Nagib Abi Fadel" > <[EMAIL PROTECTED]> wrote: > >The use of sequence would require to clean up the table every N hour . > > Right. > > >The use of temporary table wouldn't require any cleanup. > > Wrong. You would have to clean up the meta data, at least pg_class and > pg_attribute, maybe pg_index also. For the price of one temp table you > can have several rows in a permanent table. > > Servus > Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Creating a session variable in Postgres
Well i thought about that but i don't feel like it is a clean way. Actually i need to create a dynamic view depending on the user choice of a certain variable via a web application. Let's say for example the variable is called "X". The view is called "t_view" and the temporary table is called "t_temp". Each time a user connects to the web, the application will initialize the variable X and it will be inserted into the temporary table t_temp. i defined the following view: CREATE VIEW t_view as select * from SomeTable where id = (select X from t_temp); This didn't work first cause the temporary table does not exist. So i created the temporary table then created the view "t_view" and then the view was created (i kind of fooled the system). Now every time a user access the web application he will choose a value for X and the t_temp will be created and X inserted in it. I solved my problem but it does not seem like a "clean way". Any ideas ?? I have now a DYNAMIC view - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Nagib Abi Fadel" <[EMAIL PROTECTED]> Cc: "generalpost" <[EMAIL PROTECTED]> Sent: Wednesday, June 02, 2004 04:53 PM Subject: Re: [GENERAL] Creating a session variable in Postgres > Nagib Abi Fadel wrote: > > Is it possible to create a session variable for each user in Postresql ?? > > No. The best you can do is create a temp table and put a value in > there. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Creating a session variable in Postgres
Is it possible to create a session variable for each user in Postresql ?? Thx
[GENERAL] ON DELETE RULE problem
Hi, I need to run the following delete command on a view table1_v : " delete from table1_v where table1_var1=$table1_var1 and table1_var2 not in (select t2.id from table2_v t2,table3_v t3 where t3.vers_id=t2.vers_id and t3.var3=$var3); " I have not been able to figure out how to create the corresponding rule. can anyone help. thx.
[GENERAL] XOR logical operator
Is there a XOR logical operator in Postgresql, or a function for XOR ?? I only found in the docs a Binary XOR (#). I need to do the following checkup: (field1 is NULL XOR field2 is NULL XOR filed3 is NULL) i can't right it like this: ( (field1 is NUll or field2 is NUll) and (field1 is NUll or field3 is NUll) and (field2 is NUll or field3 is NUll) ) But if i have alot of fields : field1,field2,...,field5 ... this will take a hell of a time I can write a function F1 that does the following: if a field is NULL it will return 1 else it will return 0 then i can do: (F1(field1) # F1(field2) # F1(field3) ...) but i just wanted to see if XOR already exists ... __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Triggers tutorial
Hi, is there any good trigger tutorial on the net ?? thx __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Cross database foreign key workaround?
You can try to use dblink (function returning results from a remote database)and create some triggers with it in order to make remote referential integrity. Or if there's a lot of links between the tables in the 2 databases it may be better to use one database. --- David Busby <[EMAIL PROTECTED]> wrote: > List, > What are the recommended work arounds for cross > database foreign keys? > As I understand it transactions are not atomic with > the TCL method. I have > a situation that requires a master database and then > a separate database for > every subscriber. Subscribers need read/write to > both databases. I chose > separate databases because there are 20+ large > tables that would require > uid/gid columns, indexes and where conditions to > separate information by > subscriber. I thought that was too much overhead. > Should I just use my > application to make changes and ensure references > that need to take place > across databases? Or should I add a uid/gid to all > necessary tables, create > indexes and update all necessary where clauses? > Ideas? > > /B > > > ---(end of > broadcast)--- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] refential integrity to multiple tables ??
What u suggest here is having a null entry in table TABLE_TYPE1 and TABLE_TYPE2. (This does not seem to be right.) That way i would be able to make a referential integrity to each table by creating the table transaction like follows: CREATE TABLE transaction ( transaction_id, amount, type1_id default null REFERENCES TABLE_TYPE1 (type1_id) on delete cascade, type2_id default null REFERENCES TABLE_TYPE2 (type2_id) on delete cascade, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) If someone deletes the null row in either table (TABLE_TYPE1 or TABLE_TYPE2) this would be a disaster. (Someone who replaced me in my post for instance) But in other hand i will make a join between two tables instead of three if i want to retrieve some informations for a specific type. Or i could create the table without referential integrity ??? The decision is confusing a little bit ... What should i choose ?? Thx for your help.Mattias Kregert <[EMAIL PROTECTED]> wrote: Maybe you should skip the "type" field and instead have id columns for each of the types and then on insert set the id for only one of the types. You could also make a constraint to make sure only one of the type id's can be specified: CREATE TABLE transaction ( transaction_id, amount, type1_id default null, type2_id default null, CONSTRAINT (type1_id IS NULL OR type2_id IS NULL) ) I have done something like this, myself... /Mattias - Original Message ----- From: Nagib Abi Fadel To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 7:53 AM Subject: [GENERAL] refential integrity to multiple tables ?? HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE. IS IT POSSIBLE TO DO THAT??? I made a turn around to this problem by creating two tables: - table TYPE1_TRANSACTION (type1_id,transaction_id) - table TYPE2_TRANSACTION (type2_id,transaction_id) But this does not seem so right for me ?? thx for any help Do you Yahoo!?The New Yahoo! Shopping - with improved product search Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [GENERAL] refential integrity to multiple tables ??
--- Richard Huxton <[EMAIL PROTECTED]> wrote: > On Wednesday 08 October 2003 06:53, Nagib Abi Fadel > wrote: > > HI, > > > > let's say i have a tansaction table called > TRANSACTION > > (transaction_id,amount,type,type_id) > > > > Let's say a transaction can have multiple types: > TYPE1, TYPE2 for example. > > > > EACH type has his own definition and his own > table. > > > > Every transaction has a type that could be type1 > or type2 that's why if the > > type is TYPE1 i want to make a referential > integrity to the TYPE1_TABLE and > > if the type is TYPE2 i want to make a referential > integrity to the > > TYPE2_TABLE. > > > > IS IT POSSIBLE TO DO THAT??? > > You're looking at it the wrong way around, but in > any case there are still > problems. > > transaction_core(trans_id, trans_name, trans_type) > transaction_type1(tt1_core_id, tt1_extra1, > tt1_extra2...) > transaction_type2(tt2_core_id, tt2_extra1, > tt2_extra2...) > > And have tt1_core reference trans_id (not the other > way around). Do the same > for tt2_core and we can guarantee that the two > transaction types refer to a > valid trans_id in transaction_core. > > Now, what gets trickier is to specify that tt1_core > should refer to a row in > transaction_core where trans_type=1. > Ideally, we could have a foreign-key to a view, or > specify a constant in the > FK definition. We can't so you have to repeat the > type field in > transaction_type1/2 and keep it fixed for every row. > > HTH > -- > Richard Huxton > Archonet Ltd Actually a type1_id can have mutiple corresponding transaction_ids (same thing for type2) that's why i created the tables as follows: create table transaction( transaction_id serial P K, amount int,...) create table TABLE_TYPE1( type1_id serial P K, ... ) create table transaction_type1( type1_id int, transaction_id int ) for example we can have the following possible entries in table transaction_type1: type1_id,transaction_id 100,101 100,102 100,103 200,312 200,313 200,314 200,315 Same thing for type 2. I can also add that a transaction id can be of type1 or (exclusive) of type2 and never of two types at the same time. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] refential integrity to multiple tables ??
HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE. IS IT POSSIBLE TO DO THAT??? I made a turn around to this problem by creating two tables: - table TYPE1_TRANSACTION (type1_id,transaction_id) - table TYPE2_TRANSACTION (type2_id,transaction_id) But this does not seem so right for me ?? thx for any help Do you Yahoo!? The New Yahoo! Shopping - with improved product search
[GENERAL] databse design tutorial
Hi everybody, i need a free tutorial on database design can any help ?? thx Do you Yahoo!? The New Yahoo! Shopping - with improved product search
