[GENERAL] Generating unique session ids
Hi, I need to generate sessions for logged in users to my website which uses pgsql. So i decided to write a function which is this: --- CREATE OR REPLACE FUNCTION session_createsession(int4, varchar) RETURNS text AS $BODY$ DECLARE sid TEXT; BEGIN sid := md5(random()); INSERT INTO sessions (id, accountid, ipaddress) VALUES (sid, $1, $2); return sid; EXCEPTION WHEN unique_violation THEN return session_createsession($1, $2); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; --- As the id field is primary key, it should generate a unique violation if duplicate ids created, might be seen rarely but wanted to solve it anyway. So i decided to check it by changing sid := md5(random()); to sid := extract(minute from now()); When i run it, returns the minute as session key and inserts an entryy for it, but if i call it again in the same minute, it never ends execution. I expected it to return the minute when system clock minute changes but it runs forever. Am i doing something wrong? I mean, there might be some implemendation to have now() to return same value in a trancastion or something but could not be sure. If there is something like that, is that function safe to create session ids? Because if it goes into an infinite loop like it does with the minute thing, it'd drain all system resources. Thanks. ---(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: [GENERAL] Timestamp vs timestamptz
Thanks for the replies, and thanks for mentioning the DST thing. So, i'm going to use tstz. I just don't want my data to be affected by timezone changes and dst etc. I had a game server which had a timer system, when i delay something it was creating an object with timestamp now + delaytime and check for the timers in main loop. Was a windows server and automatically adjucted dst, and everything stopped in game :) All timers was pointing like 1hour and 13ms later. I had to restart it. I just don't wanna have problems like this. Thanks again all for helping. Martijn van Oosterhout wrote: On Thu, Jul 13, 2006 at 04:35:20PM -0700, Antimon wrote: Hi, I'm working on a web project with pgsql, i did use mysql before and stored epoch in database so i'm not familiar with these datatypes. What i wanna ask is, if i don't need to display timestamps in different timezones, shall i use timestamptz anyway? I mean, i'm gonna need timestamp columns on some tables for internal calculations and stuff like delaying actions, adding a row with a timestamp of 10 minutes later and check for them every minute, fetch elapsed ones and process, not to display them to users. The choice between timezone and timezonetz depends on what you're using it for: timestamptz identifies a specific point in time. It will be adjusted before output to reflect the timezone of the person selecting it. timestamp is a representation of a wall clock. The difference is easy to show when you're dealing with daylight savings times. In central european time the date '2006-03-26 02:30:00' doesn't exist, yet you can store it in a timestamp, but not in a timestamptz. For timestamptz, the time jumps from 2006-03-26 02:00:00 +0100 to 2006-03-26 03:00:00 +0200. And calculations take this into account. Similarly when daylight savings ends, a timestamptz can handle the fact that 2:30 am occurs twice, whereas timestamp won't. You can use the X AT TIME ZONE Y construct to convert between the two. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. --x+6KMIRAuhnl3hBn Content-Type: application/pgp-signature Content-Disposition: inline; filename=signature.asc Content-Description: Digital signature X-Google-AttachSize: 190 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Timestamp vs timestamptz
Hi, I'm working on a web project with pgsql, i did use mysql before and stored epoch in database so i'm not familiar with these datatypes. What i wanna ask is, if i don't need to display timestamps in different timezones, shall i use timestamptz anyway? I mean, i'm gonna need timestamp columns on some tables for internal calculations and stuff like delaying actions, adding a row with a timestamp of 10 minutes later and check for them every minute, fetch elapsed ones and process, not to display them to users. Will there be any advantages of using timestamptz type for this? I thought, only if i move the website to some other server with another timezone or something, all my timestamps and delayed actions would still be accurate to the system clock.. Shall i just use timestmap type or timestamptz for this? Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Large text data
Hi, I need to store text entries and i use text datatype. I want to ask if it will be better to split text and entry information? I mean, i can use a table like, (id, authorid, insertdate, editdate, threadid, textdata) or i can have an entrytexts table (id, entryid, textdata) and a foreign key on entryid - entries.id. Which would be better? I might need to do some range searches and orders on entries so i thought splitting text might decrease some overhead? Or shall i just use one table? Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL client api
I know this is not even a test but i thought it might be a client library issue, not server itself. Well it is not, i tried it on .net with npgsql which is a .net client library (Not using libpq). Results are same. Connect time does not have much effect by the way. ---(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: [GENERAL] PostgreSQL client api
Hi, I'm sorry about being understood like i was trying to compare pgsql with mysql. I was trying stuff, did this and saw that huge difference (even it is not bad alone, but comparing to mysql), and thought that might be some library issue causing slow reads from server. I don't need any rdbms to be faster on selecting a constant integer. My point was the library, not server performance. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL client api
Just noticed, On windows, these results are produced. But on linux, postgresql performs great. So postgre has a bad windows integration than mysql. Well since it supports win32 for a long time, it makes sense. I did some multi client tests and postgre could not even catch mysql so i decided to go test it on my linux box. It seems that the performance issue is about windows version. ---(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: [GENERAL] PostgreSQL client api
Just tried, yes, pconnect boosts multi user performance. But causes this: http://img526.imageshack.us/img526/6302/pgsql7th.jpg :) Need to modify max persistent connection settings. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL client api
Yes i tried and realized apache child processes.. Then i looked for another pooling solution, the project pgpool i found. No windows binaries, it might run on cygwin. After all, i think postgreSQL is not meant to run on windows production for ~2 more major releases or something. It performs great on linux thats for sure but i cannot do the platform decision everytime, so gonna stick with mySQL on windows and postgre on linux when i can. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL client api
Hi,
I was testing MySQL and PgSQL performances on my home box (amd athlon
64 3000, 1gig ddr ram, sata I hdd, win xp (x86)), select and insert
times seeemed identical with innoDB.
But when i try to query both using php, there's a huge difference even
for a funny query like select 1
Here's the code:
?php
$mtime = microtime(true);
$pdo = new PDO('pgsql:host=localhost;dbname=test', testacc, pw);
for ($i = 0; $i 1; $i++)
{
$result = $pdo-query(Select +$i);
}
echo microtime(true) - $mtime;
echo br;
$mtime = microtime(true);
$pdo = new PDO('mysql:host=localhost;dbname=test', testacc, pw);
for ($i = 0; $i 1; $i++)
{
$result = $pdo-query(Select +$i);
}
echo microtime(true) - $mtime;
echo br;
?
output is:
2.7696590423584
0.89393591880798
Nearly 3 times slower even w/o any table queries. But i could not
reproduce this by writing stored procs on both which selects 0-1 in
a loop to a variable. results were almost same.
(I tried pg_ and mysqli_ functions too, results were not too different)
Is it mysql client libraries performs better? Or postgre stored procs
are 3x faster? I cannot understand, since there is not even an io
operation or any query planning stuff, what is the cause of this?
Thanks.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Php abstraction layers
I wrote a wrapper class based on pg_ functions. Added some logging and exception throwing capabilities etc. I'm planning to use only prepared statements and pg_query_params function when using user submitted data in queries to avoid sql-injection. I believe it is enough but gonna do some tests. This is the best way i think. Thanks to everyone. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Php abstraction layers
Hi, I'm working on a new web project based on php and i decided to use PostgreSQL 8.x as dbms. For triggers, views, stored procedures etc. I was going to write a simple wrapper class and use pg_* functions. But some friends adviced me to use an abstraction layer. I checked PEAR:DB and AdoDB. They look pretty but i don't understand why sould i need one? Project will be postgre dependant because of the database structure, i don'T need to support multiple dbms types. Since i may not even have a chance to convert database structure. And php 5.1's postgresql library has all new features implemented. In this situation, what would be the advantage of using an abstraction layer? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Php abstraction layers
Thanks for the reply. I checked new 5.1 pg_ functions and i wanna ask something else. What do you think about PDO? It is not an abstraction layer, just something like wrapper. I thought as it supports both widely used dbmss, php developers would focus on it more than pg or mysqli functions and that can make it powerful. Would it be a good decision to use PDO instead of pg_ functions? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
