Обсуждение: Stored procedures returning multiple values... ?
Hello friends, many months ago I starting messing with stored procedures with PostgreSQL, and at the time, I found out that I could only return "one" value when I used a stored procedure. I was wondering if this has changed or has/will/could be changed/implemented with PostgreSQL 7.0? It would be fun if stored procedured could return multiple values, wouldn't it? ;-) Thanks, ___________________________________________ Henrique Pantarotto Administrador de Sistemas - SITE São Paulo Terra Networks Brasil S/A A Internet do Brasil agora é TERRA Tel: (11) 5505-5728 r.214/260/238 ICQ: 6934285 pantarotto@terra.com.br <Hatuna Matata, é liiiinndo dizer!>
Is my question stupid? ;-) > -----Mensagem original----- > De: owner-pgsql-general@postgreSQL.org > [mailto:owner-pgsql-general@postgreSQL.org]Em nome de Henrique > Pantarotto > Enviada em: Sexta-feira, 25 de Fevereiro de 2000 09:27 > Para: pgsql-general@hub.org > Assunto: [GENERAL] Stored procedures returning multiple values... ? > > > Hello friends, > > many months ago I starting messing with stored procedures with PostgreSQL, > and at the time, I found out that I could only return "one" value when I > used a stored procedure. > > I was wondering if this has changed or has/will/could be > changed/implemented > with PostgreSQL 7.0? > > It would be fun if stored procedured could return multiple > values, wouldn't > it? ;-) > > > Thanks, > ___________________________________________ > Henrique Pantarotto > Administrador de Sistemas - SITE São Paulo > Terra Networks Brasil S/A > A Internet do Brasil agora é TERRA > Tel: (11) 5505-5728 r.214/260/238 > ICQ: 6934285 > pantarotto@terra.com.br > <Hatuna Matata, é liiiinndo dizer!> > > > > ************ >
Henrique - No, not stupid, just hard. I find it interesting that I read your repost of your question immediately following a pair of postings to the PGSQL-HACKERS list by Karel Zak about his implementation of cached/stored procedures. ;-) As it stands, the "stored procedures" in pgsql are really user defined functions, and can only return values. Changing that is a major amount of work, and hasn't happened for 7.0 (or 7.1) Karel Zak's work is also post 7.0, I believe, unless The Powers That Be (the core developers) decide that his changes are sufficently isolated s othat any bugs are unlikely to affect other code. In other words, no not yet, but soon! Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Mon, Feb 28, 2000 at 08:53:09AM -0300, Henrique Pantarotto wrote: > Is my question stupid? ;-) > > > -----Mensagem original----- > > De: owner-pgsql-general@postgreSQL.org > > [mailto:owner-pgsql-general@postgreSQL.org]Em nome de Henrique > > Pantarotto > > Enviada em: Sexta-feira, 25 de Fevereiro de 2000 09:27 > > Para: pgsql-general@hub.org > > Assunto: [GENERAL] Stored procedures returning multiple values... ? > > > > > > Hello friends, > > > > many months ago I starting messing with stored procedures with PostgreSQL, > > and at the time, I found out that I could only return "one" value when I > > used a stored procedure. > > > > I was wondering if this has changed or has/will/could be > > changed/implemented > > with PostgreSQL 7.0? > > > > It would be fun if stored procedured could return multiple > > values, wouldn't > > it? ;-) > > > > > > Thanks, > > ___________________________________________ > > Henrique Pantarotto > > Administrador de Sistemas - SITE São Paulo > > Terra Networks Brasil S/A > > A Internet do Brasil agora é TERRA > > Tel: (11) 5505-5728 r.214/260/238 > > ICQ: 6934285 > > pantarotto@terra.com.br > > <Hatuna Matata, é liiiinndo dizer!> > > > > > > > > ************ > > > > > ************ >
Ross, thank you for your reply! Pgsql developers has done a great job so far, and I am sure things will keep like that. Thanks and regards from Brazil, ___________________________________________ Henrique Pantarotto Administrador de Sistemas - SITE São Paulo Terra Networks Brasil S/A A Internet do Brasil agora é TERRA Tel: (11) 5505-5728 r.214/260/238 ICQ: 6934285 pantarotto@terra.com.br <Hatuna Matata, é liiiinndo dizer!> > -----Original Message----- > From: Ross J. Reedstrom [mailto:reedstrm@wallace.ece.rice.edu] > Sent: Monday, February 28, 2000 1:00 PM > To: Henrique Pantarotto > Cc: pgsql-general@hub.org > Subject: Re: RES: [GENERAL] Stored procedures returning multiple > values... ? > > > Henrique - > No, not stupid, just hard. I find it interesting that I read your > repost of your question immediately following a pair of postings to the > PGSQL-HACKERS list by Karel Zak about his implementation of cached/stored > procedures. ;-) > > As it stands, the "stored procedures" in pgsql are really user defined > functions, and can only return values. Changing that is a major amount > of work, and hasn't happened for 7.0 (or 7.1) Karel Zak's work is also > post 7.0, I believe, unless The Powers That Be (the core developers) > decide that his changes are sufficently isolated s othat any bugs are > unlikely to affect other code. > > In other words, no not yet, but soon! > > Ross > -- > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > > On Mon, Feb 28, 2000 at 08:53:09AM -0300, Henrique Pantarotto wrote: > > Is my question stupid? ;-) > > > > > -----Mensagem original----- > > > De: owner-pgsql-general@postgreSQL.org > > > [mailto:owner-pgsql-general@postgreSQL.org]Em nome de Henrique > > > Pantarotto > > > Enviada em: Sexta-feira, 25 de Fevereiro de 2000 09:27 > > > Para: pgsql-general@hub.org > > > Assunto: [GENERAL] Stored procedures returning multiple values... ? > > > > > > > > > Hello friends, > > > > > > many months ago I starting messing with stored procedures > with PostgreSQL, > > > and at the time, I found out that I could only return "one" > value when I > > > used a stored procedure. > > > > > > I was wondering if this has changed or has/will/could be > > > changed/implemented > > > with PostgreSQL 7.0? > > > > > > It would be fun if stored procedured could return multiple > > > values, wouldn't > > > it? ;-) > > > > > > > > > Thanks, > > > ___________________________________________ > > > Henrique Pantarotto > > > Administrador de Sistemas - SITE São Paulo > > > Terra Networks Brasil S/A > > > A Internet do Brasil agora é TERRA > > > Tel: (11) 5505-5728 r.214/260/238 > > > ICQ: 6934285 > > > pantarotto@terra.com.br > > > <Hatuna Matata, é liiiinndo dizer!> > > > > > > > > > > > > ************ > > > > > > > > > ************ > >
Hi, I was doing a few tests to optimize my perl web app, and this is what I got. without database: 140 hits/sec with a rollback/begin and a select: 90 hits/sec with a rollback/begin, select and an update (but no commit): 70 hits/sec with a rollback/begin, select + update + commit: 13 hits/sec Any idea how to speed things up? Turning off sync would be dangerous right? The minimum my web app does is: 1) rollback/begin 2) select session information 3) update session information (new session time out) 4) commit update I written a simple script which does the same thing and the figures are the same. Any idea how to speed things up without switching to another database engine or session control method? Or do I have to live with 13 hits/sec max? )-; Does MySQL turn off sync? I don't think it does, but it seems to be able to do updates (and thus syncs) a lot faster. I know postgresql has got transactions and all that, but from the "time" statistics, the CPU isn't really being pushed, so if it's not sync what's it waiting for? (benchmark does 100 sets of the four steps). time ./benchmark 0.26user 0.02system 0:07.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (295major+221minor)pagefaults 0swaps Whereas if I remove the commit: time ./benchmark 0.21user 0.02system 0:01.57elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (294major+223minor)pagefaults 0swaps Any suggestions welcome! Cheerio, Link.
At 02:51 PM 3/04/00 +0800, Lincoln Yeoh wrote: >Hi, > >I was doing a few tests to optimize my perl web app, and this is what I got. A few questions, if you don't do the commit, then you loose your data, so there isn't much point in doing that is there ... How much RAM does you machine have? Where is the blocking happening, what does vmstat tell you (is it blocking on disk, ram or what?) What sort of disk drives are you using? How many postmasters are you running, what is the maximum you have set (I think the default is 32 or somthing) Does you code pool database connections at all or does it reconnect for each statement (select, update, etc) -- Mr Grumpy is now a virtual personality ... http://www.cyber4.org/members/grumpy/camera/index.html
Lincoln Yeoh wrote: > > Hi, > > I was doing a few tests to optimize my perl web app, and this is what I got. > > without database: 140 hits/sec > with a rollback/begin and a select: 90 hits/sec > with a rollback/begin, select and an update (but no commit): 70 hits/sec > with a rollback/begin, select + update + commit: 13 hits/sec > > Any idea how to speed things up? Turning off sync would be dangerous right? > > The minimum my web app does is: > 1) rollback/begin > 2) select session information > 3) update session information (new session time out) > 4) commit update > > I written a simple script which does the same thing and the figures are the > same. > > Any idea how to speed things up without switching to another database > engine or session control method? Or do I have to live with 13 hits/sec max? > > )-; > > Does MySQL turn off sync? I don't think it does, but it seems to be able to > do updates (and thus syncs) a lot faster. I know postgresql has got > transactions and all that, but from the "time" statistics, the CPU isn't > really being pushed, so if it's not sync what's it waiting for? A statement in the mySQL documentation's change log for 3.22.9 leads me to believe that mySQL does not flush dirty kernel buffers to disk with a call to fsync() on each insert/update/delete: "You can now start mysqld on Win32 with the --flush option. This will flush all tables to disk after each update. This makes things much safer on NT/Win98 but also MUCH slower." And in the change log for 3.22.18: "Added option -O flush-time=# to mysqld. This is mostly useful on Win32 and tells how often MySQL should close all unused tables and flush all updated tables to disk." These statements imply that unlike PostgreSQL, which defaults to fsync() ON, mySQL defaults to fsync() OFF. By the way, we have been running a production PostgreSQL server on 6.5beta for over a year with fsync() off (-o -F) without problems. If your server doesn't suffer from kernel crashes, and is backed by a UPS, there's no reason in running PostgreSQL with fsync() on. It seems pretty clear that the mySQL folks didn't even consider a flushing option until the port to Win32, where the "kernel" was far from reliable... Hope that helps, Mike Mascari > > (benchmark does 100 sets of the four steps). > > time ./benchmark > 0.26user 0.02system 0:07.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (295major+221minor)pagefaults 0swaps > > Whereas if I remove the commit: > > time ./benchmark > 0.21user 0.02system 0:01.57elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (294major+223minor)pagefaults 0swaps > > Any suggestions welcome! > > Cheerio, > Link.
At 04:23 AM 03-04-2000 -0400, Mike Mascari wrote: >> >> Does MySQL turn off sync? I don't think it does, but it seems to be able to >> do updates (and thus syncs) a lot faster. I know postgresql has got >> transactions and all that, but from the "time" statistics, the CPU isn't >> really being pushed, so if it's not sync what's it waiting for? > >A statement in the mySQL documentation's change log for 3.22.9 >leads me to believe that mySQL does not flush dirty kernel >buffers to disk with a call to fsync() on each >insert/update/delete: Yah, seems like it now. I must have been fooled by the statement that it does a write() after every SQL statement. I created a shell script with 100 syncs, and it took 6.25 seconds to run. So I've got egg on my face now :*), and the bottleneck is sync not postgresql. >By the way, we have been running a production PostgreSQL server >on 6.5beta for over a year with fsync() off (-o -F) without >problems. If your server doesn't suffer from kernel crashes, and >is backed by a UPS, there's no reason in running PostgreSQL with >fsync() on. It seems pretty clear that the mySQL folks didn't >even consider a flushing option until the port to Win32, where >the "kernel" was far from reliable... Got UPS, linux 2.2.14 but I don't dare go fsyncless because another bunch is using the postgresql engine for another app. I'd like to run another postgres backend but the docs are sparse on running multiple independent postmasters/postgres. Should be possible- just start it on a different port, but the likely trouble areas will be the start/stop scripts - whether they can cope with killing just the relevant postgres stuff (instead of everything ;) ). Actually a good balance would be to have a separate database engine just for session handling, as syncs won't be important on this, it means more memory used, but that's not too difficult to fix nowadays <grin>. Any idea how much faster will it be without the sync? e.g. how many (begin, select, update, commit) per second? I'm guessing that with fsync off it's going to be just slightly slower than the no commit version e.g. 50-70 per second, woohoo. In that case if MySQL really doesn't do syncs on each SQL write, then things are about even and that's another feather in the Postgresql developers' caps. I seem to need to do vacuum analyze quite often to just maintain performance, should I do it with cron or have something that does it during low load times (which could mean never if I'm unlucky, or a death spiral as things go slower and slower ;) ). Cheerio, Link.
>By the way, we have been running a production PostgreSQL server >on 6.5beta for over a year with fsync() off (-o -F) without >problems. If your server doesn't suffer from kernel crashes, and >is backed by a UPS, there's no reason in running PostgreSQL with >fsync() on. It seems pretty clear that the mySQL folks didn't >even consider a flushing option until the port to Win32, where >the "kernel" was far from reliable... OK tried it briefly with -o -F. Got 68 hits/sec! That's quite a remarkable speed up from 13-15 hits/sec. On a slower machine the speed up was only from 6 hits/s to 13 hits/s. Switched back to sync tho. Too cowardly ;). Plus speed is not so critical at the moment. Well at least I know there's more juice on tap if I really need it... Cheerio, Link.