pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Support for bulk reads/writes ?
3+ messages / 3 participants
[nested] [flat]

* Support for bulk reads/writes ?
@ 2002-02-11 20:06 charlie <ctoohey@pacbell.net>
  2002-02-11 20:24 ` Re: Support for bulk reads/writes ? Andrew Perrin <andrew_perrin@unc.edu>
  2002-02-11 22:58 ` Re: Support for bulk reads/writes ? David Stanaway <david@netventures.com.au>
  0 siblings, 2 replies; 3+ messages in thread

From: charlie @ 2002-02-11 20:06 UTC (permalink / raw)
  To: pgsql-sql

I have looked around quite a bit and could not find any information on
whether or not Postgres supports Oracle-style array usage for bulk
reading/writing of the database, e.g. bulk insert statements ?

I have a web application --- I'm acquiring/calculating various data
throughout a user's session that needs to be written to the database. I
could either just insert/update the data in Postgres as it is computed
throughout the session, or, I could keep the data in memory (using servlets,
storing the data at session scope) and then write it all to Postgres when
the session ends, if there were any advantage to doing so, i.e., if there
were a way that I could insert the data in bulk to save a bunch of trips
back and forth to the database. Otherwise, there would seem to be no
performance difference in whether the web app is executing a bunch of SQL 
statements over the course of the session vs. executing all the same 
statements at the end of the session.

The web application could experience high loads, in terms of the
number of simultaneous user sessions, which is why I'm concerned about
performance.

thanks,
charlie




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Support for bulk reads/writes ?
  2002-02-11 20:06 Support for bulk reads/writes ? charlie <ctoohey@pacbell.net>
@ 2002-02-11 20:24 ` Andrew Perrin <andrew_perrin@unc.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew Perrin @ 2002-02-11 20:24 UTC (permalink / raw)
  To: charlie <ctoohey@pacbell.net>; +Cc: pgsql-sql

I'm not entirely sure if I'm understanding you, but if I am then it
seems like transactions would do what you're asking, but batch them at the
backend point rather than the client point:

BEGIN;

INSERT...
INSERT...
INSERT...

COMMIT;

will wait until the COMMIT line to actually change the backend db.

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
 Assistant Professor of Sociology, U of North Carolina, Chapel Hill
      269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA


On Mon, 11 Feb 2002, charlie wrote:

> I have looked around quite a bit and could not find any information on
> whether or not Postgres supports Oracle-style array usage for bulk
> reading/writing of the database, e.g. bulk insert statements ?
> 
> I have a web application --- I'm acquiring/calculating various data
> throughout a user's session that needs to be written to the database. I
> could either just insert/update the data in Postgres as it is computed
> throughout the session, or, I could keep the data in memory (using servlets,
> storing the data at session scope) and then write it all to Postgres when
> the session ends, if there were any advantage to doing so, i.e., if there
> were a way that I could insert the data in bulk to save a bunch of trips
> back and forth to the database. Otherwise, there would seem to be no
> performance difference in whether the web app is executing a bunch of SQL 
> statements over the course of the session vs. executing all the same 
> statements at the end of the session.
> 
> The web application could experience high loads, in terms of the
> number of simultaneous user sessions, which is why I'm concerned about
> performance.
> 
> thanks,
> charlie
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Support for bulk reads/writes ?
  2002-02-11 20:06 Support for bulk reads/writes ? charlie <ctoohey@pacbell.net>
@ 2002-02-11 22:58 ` David Stanaway <david@netventures.com.au>
  1 sibling, 0 replies; 3+ messages in thread

From: David Stanaway @ 2002-02-11 22:58 UTC (permalink / raw)
  To: pgsql-sql; +Cc: charlie <ctoohey@pacbell.net>

You may find these code snippets handy	


CREATE FUNCTION editpresentationparam(int,text[][])
RETURNS int
AS 'DECLARE
          prid ALIAS FOR $1;
          pairs  ALIAS FOR $2;
          result int;
          rc int;
          i int;
     BEGIN
         result := 0;
         i := 1;
         DELETE FROM tblPresentationParam WHERE ppprID = prid;
         WHILE pairs[i][1] != '''' LOOP
             INSERT INTO tblPresentationParam (ppprID,pppnID,ppValue)
                 SELECT prid, pnID, pairs[i][2] FROM 
tblPresentationParamName WHERE pnName = pairs[i][1];
             GET DIAGNOSTICS rc = ROW_COUNT;
             result := result + rc;
             i := i + 1;
         END LOOP;
         RETURN result;
     END;'
LANGUAGE 'plpgsql';


<?php

//Turns a pair of values into a postgres array literal
//EG: "name","Bloggs, Bill 'owdy" ->
//  '{"name","Bloggs, Bill ''owdy"}'
function makepair($p1,$p2) {
   $pat = array ("/\\\\/","/\"/","/'/");
   $rep = array ( '\\\\' , '\"' ,"''");
   return '{"'.preg_replace($pat,$rep,$p1).
     '","'. preg_replace($pat,$rep,$p2).'"}';
}

function updatepresentationparam($formvars) {
   if(isset($formvars['ref'])&&preg_match('/^\d+$/',($formvars['ref']))) {
     $q="SELECT editpresentationparam(".$formvars['ref'].",'{";
     reset($formvars);
     while (list($pnname,$ppvalue) = each($formvars)) {
       if($pnname != "ref" && $pnname != "func" && $ppvalue != "") {
         $q.=makepair($pnname,$ppvalue).",";
       }
     }
     $q.="{,}}')";
     if(! ($res=psql_query_wrap($q))) {
       return -1;
     } else {
       $row = pg_fetch_array($res,0);
       return $row[0];
     }
   } else return -1;
}

?>

==============================
David Stanaway
Personal: david@stanaway.net
Work: david@netventures.com.au




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-02-11 22:58 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-11 20:06 Support for bulk reads/writes ? charlie <ctoohey@pacbell.net>
2002-02-11 20:24 ` Andrew Perrin <andrew_perrin@unc.edu>
2002-02-11 22:58 ` David Stanaway <david@netventures.com.au>

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