Have you registered for OSCON yet? The 2004 O'Reilly Open Source Convention July 26-30, 2004 will be held in Portland, OR. For those of us in or near the States, this is the PostgreSQL conference to attend. PostgreSQL people will be there in full force.

Our PostgreSQL Birds of a Feather Session at OSCON will be on Wednesday evening at 7:00pm. Our agenda will be:

* Meet PostgreSQL speakers and core team members.
* Hear the latest news about PostgreSQL 7.5.
* Meet other PostgreSQL users.
* Voice your comments, experience and ideas about PostgreSQL.

PostgreSQL will also have a table. We are working out the details of this now.

The PostgreSQL Track will be very full of great presentations by a good variety of impressive people. I recommend all of them without hesitation. But don't miss the panel discussion of Replication.

The tutorials will be held on the Monday and Tuesday before the open conference.

Monday July 26
Introduction to PostgreSQL	8:45am - 12:15pm	A. Elein Mustain, Varlena, LLC
Power PostgreSQL: Extending the Database with C	1:45pm - 5:15pm	Joseph Conway
Tuesday July 27
How to Play Together Nicely: Strategies for DBA and Application Developers	1:45pm - 5:15pm	Greg Sabino Mullane

Please contact me if you are planning on coming and want to know what we are planning and how you can join rest of us. You may also want to tune into the pgsql-advocacy list to hear some of the public discussions of PostgreSQL at OSCON.

Contributors: elein at varlena.com

Asychronous Events Integrating Posgresql queries into an event loop By Mark Harrison 27-May-2004

Postgresql has good support for asynchronous programming, but it definitely took a bit of work to unravel the code from the existing documentation. I've attached the current version of the application note for your use.

Application Note: Integrating Posgresql queries into an event loop.

Problem

If the queries are small and finish quickly, there is probably OK. Hanging for a few milliseconds will not be a problem.

However, if you have a large or slow query,this can be significant. For example, one of my tables (call it "big") has about 14 million rows. It takes about two minutes for

    res = PQexec(conn, "select * from big");

Solution, part 1:

First, we need to break up the returned data into more manageable chunks. For this, we use an SQL cursor. Here are the relevant bits of code (error checking elided in order to make the flow more clear):

    res = PQexec(conn, "BEGIN");
    res = PQexec(conn, "DECLARE cur CURSOR FOR select * from big");

    while (1) {
        res = PQexec(conn, "FETCH 1000 in cur");
        if (PQntuples(res) == 0)
            break
        else
           //process rows of data
    }

    res = PQexec(conn, "CLOSE cur");
    res = PQexec(conn, "END");

This has two immediate benefits:

1. There is not a two minute pause while the data is being transferred and buffered from the server to the client.

2. The memory requirements for the client program are much lower.

Solution, part 2

As is typical for event loop programming, there are two main bits of code:

1. Set up query and callback.

    // establish the database connection.  Set the connection to
    // non-blocking mode, and create a cursor for fetching our data.
    // Flush the request to the server.
    conn = PQconnectdb("...");
    rc = PQsetnonblocking(conn, 1);
    res = PQexec(conn, "BEGIN");
    res = PQexec(conn, "DECLARE cur CURSOR FOR select * from big");
    rc = PQsendQuery(conn, "FETCH 1000 in cur");
    PQflush(conn);

    // Get the postgresql socket and add it to our event loop
    sock = PQsocket(conn);
    add_to_event_loop(READABLE, sock, myhandler);

    // Consume the input from the server.  This will clear the file
    // descriptor "readable" flag.  Process as much data as we can
    // in the while loop.  When we have retrieved all the data for
    // this FETCH, issue another FETCH command.  When there are no
    // more tuples returned, the query is finished and you can clean
    // up the request.
    rc = PQconsumeInput(conn);
    while (!PQisBusy(conn)) {
        rc = PQconsumeInput(conn); // (is this necessary?)
        res = PQgetResult(conn);
        if (res == NULL) {
            // we have finished all the rows for this FETCH.  We need
            // to send another FETCH to the server.
            rc = PQsendQuery(conn, "FETCH 1000 in cur");
            PQflush(conn);
            return;
        }
        else {
           if (PQntuples(res) == 0)
               // finished processing all rows.  Clean up the
               // result and remove your callback from the
               // event loop.
           else
               //process rows of data
        }
    }

    PQrequestCancel(conn);

Copying and Acknowledgements

This document is released under the same license as the Postgresql documentation. Thanks to Tom Lane for his help in understanding this API.

Contributors: Mark Harrison mh at pixar.com

Convert money type to numeric [GENERAL] Turn off "money" formatting? 07-Jun-2004

The money datatype will be deprecated. It is recommended that you use a numeric datatype instead.

If you have money types and want to convert them to a numeric type you can use the following plperl function. No doubt this function could be terser, but readability is important, too.

	create or replace function money2numeric (money)
	returns numeric as
	'
   	$ret = $_[0];
   	$ret =~ s/[\\$,]//g;    
   	return $ret;
	' language 'plperl' ;

Once you have this function defined, you can test it.

	create table moneytable (
	   id SERIAL PRIMARY KEY,
	   amt   money
	);
	insert into moneytable (amt) values ('$1,234.56');
	insert into moneytable (amt) values ('$7,890.12');
	select id, amt from moneytable;
	select id, money2numeric(amt) from moneytable;

If you want to change the amt column from a money type, then you must: 1)create a new column, 2) populate the new column 3) drop the old column 4) rename the new column with the old column name.

	alter table moneytable
	   add column num_amt numeric;
	update moneytable set num_amt=money2numeric(moneytable.amt)
	   from moneytable m2
	   where moneytable.id = m2.id;
	
	alter table moneytable
	   drop column amt;
	alter table moneytable
	   rename column num_amt to amt;

This series of steps should get you the same column name with the new type.

If you plan to continue using money types or to convert some but not all money columns to numeric, you may want the conversion routine from above to be created as a cast.

	create cast (money AS numeric)
	   with function money2numeric (money)
	as implicit;

Contributors: Jerry jerry.levan at eku.edu, Brendan Jurd blakjak at blakjak.sytes.net, Karel Zak zakkr at zf.jcu.cz, elein at varlena.com