The Software Workshop inc. - software that fits! ™                  ExtSQL - Extended SQL
.
 
. Home   Contact Us    login
.
 
 
 

    Home

    Documentation

    Downloads

    FAQ

    News & Events

    Privacy Policy

    Report Problem

    Support

    Terms & Conditions


Quick Feedback!
This is a new project,
feedback is welcome
(and will be read!)

Notes on PostgreSQL Development

PostgreSQL development status overview (Nov 2009).

We are not as far along as expected with PostgreSQL development and welcome any community support in getting a good patch set completed. Due to internal staff issues we have been late in also posting this to development sites/mailing lists used for PostreSQL -- our apologies, and it should be done soon!

For the MySQL version there is extensive install documentation available. This has not yet been prepared for PostgreSQL. The current patch set is over 5300 lines in size and can be a bit intimidating. This page contains all the tips/notes from our staff along with links to our patch sets.

One key item is our desire to maintain a common codebase between the ExtSQL extensions for both PostgreSQL & MySQL. We have tried to contain most of the functionality of ExtSQL within the files extsql.h and extsql.c. The files are #ifdef'd for PGSQL and we are about 90% common. We encourage any developer to recognize that and try to keep it going!.

This article, which is a bit dated, provides a good overview of the internal design of the ExtSQL patches. Another noteworthy item is that after patching, ALL changes we made are bracketed by comment lines:
// EXTSQL START and // EXTSQL END.
It should make it easy for you to grep files and find our changes.

NOTE: we provide some convenience links on this page to various files. These are just for illustration purposes and are using a barely tested 8.4.1 compatible version of the patches. If you want the latest, go to our download page and get the latest patches!

Memory management surprise!

Our initial work was done with MySQL. It uses a threaded architecture to handle each client connection, but all server processes share the same code & data segments. Performance is at a premium and that allowed us to make use of global variables to do simple book keeping and maintain status between threads.

We decided to start with Posgres 7.4.19 (we now have a working set for 8.4.1), it is the rpm version for RHEL 4. We are a bit embarrassed to say we completed quite a bit of the port to PostgreSQL before realizing it was quite a different beast (we should have known elephants and dolphins are very different!).

Of course, PostgreSQL developers know it is NOT threaded. The main 'postmaster' process does an 'exec' of the child 'postgres' servers to handle each client connection. Each server has its own code/data segment -- only a little process info is shared via explicit calls to a shared memory subsystem!

It was actually quite surprising how far we had gotten in testing before we realized our basic error. Since most of our quick testing just consisted of a single client connecting to the server -- the statistics output looked good. It was only when connecting with other clients that we noted the problem!

The Fix: We took a look at the global stuff we had (snippet here from extsql.h):

  STATS_CLASS_DATA statData[STATS_MAX_CLASSES]; // our own complex structure typedef
  char *extsql_class_list, *extsql_reload_file;

  char *statsAllocArray[STATS_MAX_NUM_ALLOC]; // used to track mem allocs for reset/free
  int allocIndex; // our position in the global statsAllocArray

  pthread_mutex_t LOCK_stats_load, LOCK_stats_clock, LOCK_thread_count;

  char *extsql_key, *extsql_users, *extsql_version;

  ulong extsql_active, // temp stop extsql activity, can be user re-activated by SET
    extsql_disabled,   // perm stop extsql activity, severe/license error, only cleared by server restart
    extsql_reload_in_progress,  // temp stop extsql activity during startType reset/reload
    extsql_debug;
In the MySQL version we were doing dynamic (malloc) allocations for the actual statistical data area. A pointer to that was part of the statData structure. But within PostgreSQL, even those mallocs were occuring in a memory space private to each server process -- no good for us!

We knew we would suffer another level of de-reference if we had to allocate these variables in a shared memory segment. One design concern was we had test MySQL code to which this would have to be backported -- we didn't want a recoding nightmare!

Finally, we found the 'syntatic sugar' we needed. In extsql.h we defined a structure that will hold all our globals and be located in shared memory:

typedef struct global_alloc { 
STATS_CLASS_DATA statData[STATS_MAX_CLASSES];
char *extsql_class_list, *extsql_reload_file;

char *statsAllocArray[STATS_MAX_NUM_ALLOC]; // used to track mem allocs for reset/free
...
} GLOBAL, *pGLOBAL;

extern pGLOBAL Shared;

The New Code: We wrote our own "malloc" routine that just used our shared memory region. It allowed us to simply do an editor search/replace of every global variable and just preface it with "Shared->". With this in extsql.c we included an invocation of

 ShmemPtr = (char *) ShmemInitStruct("ExtSQL Data", SHMEM_SIZE, &memFound);
  ... // okay alloc the global space
 Shared = (pGLOBAL) my_malloc(sizeof(GLOBAL), MYF(MY_WME | MY_ZEROFILL) );
// examples of usage
  //init our var addr tracking array to unstored
  bzero((char *) Shared->varAddrTrackArray, sizeof(Shared->varAddrTrackArray));
  Shared->varAddrCount = 0;
...
 Shared->statsAllocArray[Shared->allocIndex++] = (char *)stats_class_data->instanceIndex;

As each postgres process is started to service a client. We call the same init routine to attach to the existing shared memory area.

Patch Installation, Build, & Test

Patch/Build: This should be pretty straight forward -- as long as you are using RHEL 4, on x86, and want to build a 8.4.1 version of the server! Below we show you a commented script session starting with unpacking a 8.4.1 source distribution. PRETTY EASY! NOTE: Take a close look at our configure file (do_conf-8.1)
/home/phil/tmp> tar -zxf postgresql-8.4.1.tar.gz

/home/phil/tmp/postgresql-8.4.1> cd postgresql-8.4.1

/home/phil/tmp/postgresql-8.4.1> cp /export/mysql/extsql-build/conf/do_conf-8.1 .

/home/phil/tmp/postgresql-8.4.1> cp /export/mysql/extsql-build/patch/patch-8.4.2.0p .

/home/phil/tmp/postgresql-8.4.1> cat do_conf-8.1 
configure '--prefix=/mysql/extsql-test/install/8.4.1' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' 
'--with-pam' '--with-krb5' '--enable-thread-safety' '--docdir=/mysql/extsql-test/install/8.4.1/doc/postgresql' 
'CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv' 
'CPPFLAGS=-D_GNU_SOURCE' 'CFLAGS_SL=-fpic' 'LIBS=-lpam -lssl -lcrypto -lkrb5 -lz -lreadline -ltermcap -lcrypt -ldl -lm'


/home/phil/tmp/postgresql-8.4.1>/usr/bin/patch --dry-run -p0 -lNut -F0 < patch-8.4.2.0p
patching file src/backend/utils/misc/Makefile
patching file src/backend/nodes/copyfuncs.c
patching file src/backend/nodes/equalfuncs.c
patching file src/backend/parser/gram.y
patching file src/backend/postmaster/postmaster.c
patching file src/backend/tcop/pquery.c
patching file src/backend/tcop/utility.c
patching file src/backend/utils/init/postinit.c
patching file src/backend/utils/misc/guc.c
patching file src/include/nodes/nodes.h
patching file src/include/nodes/parsenodes.h
patching file src/include/storage/proc.h
patching file src/include/tcop/dest.h
patching file src/include/utils/guc.h
patching file src/include/parser/kwlist.h
patching file src/backend/utils/misc/extsql.c
patching file src/include/utils/extsql.h

/home/phil/tmp/postgresql-8.4.1> /usr/bin/patch -V t -p0 -lNut -F0 < patch-8.4.2.0p
patching file src/backend/utils/misc/Makefile
patching file src/backend/nodes/copyfuncs.c
patching file src/backend/nodes/equalfuncs.c
patching file src/backend/parser/gram.y
patching file src/backend/postmaster/postmaster.c
patching file src/backend/tcop/pquery.c
patching file src/backend/tcop/utility.c
patching file src/backend/utils/init/postinit.c
patching file src/backend/utils/misc/guc.c
patching file src/include/nodes/nodes.h
patching file src/include/nodes/parsenodes.h
patching file src/include/storage/proc.h
patching file src/include/tcop/dest.h
patching file src/include/utils/guc.h
patching file src/include/parser/kwlist.h
patching file src/backend/utils/misc/extsql.c
patching file src/include/utils/extsql.h

/home/phil/tmp/postgresql-8.4.1> /do_conf-8.1
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking which template to use... linux
checking whether to build with 64-bit integer date/time support... yes
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
....
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking ./src/backend/port/dynloader/linux.c to src/backend/port/dynloader.c
config.status: linking ./src/backend/port/sysv_sema.c to src/backend/port/pg_sema.c
config.status: linking ./src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking ./src/backend/port/dynloader/linux.h to src/include/dynloader.h
config.status: linking ./src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port


/home/phil/tmp/postgresql-8.4.1>make
make -C doc all
make[1]: Entering directory `/home/phil/tmp/postgresql-8.4.1/doc'
...
make[1]: Leaving directory `/home/phil/tmp/postgresql-8.4.1/config'
All of PostgreSQL successfully made. Ready to install.


Install/ Starting the Server: If you have modified the configure for your test environment (and we only recommend this in a test area). You can stop your existing PostgreSQL installation, and copy the new postmaster as we will show below.

FIRST - make sure to add the following lines to the file data/postgresql.conf. Make sure to put your username in the extsql_users list. For a complete reference on the configuration options, check our main documentation page under "Usage & Details".

#--------------------------------------------------------------------------
# ExtSQL
#--------------------------------------------------------------------------
extsql_users = 'thebook, phil'
extsql_class_list = '
       db, max-30, time-55, units-m, (Questions, Com_delete, Com_insert, Com_select, Com_update), 
       user, max-30, time-55, units-h, (Questions, Com_delete, Com_insert, Com_select, Com_update), 
       server, max-1, time-5, units-d, (Questions, Com_delete, Com_insert, Com_select, Com_update)' 
#extsql_debug = 2054    # STATS_DUMP_START(2048) + STATS_DUMP_ONCE(2) + STATS_SHOW_PARAMS(4)

THEN - you can start your new server.

/mysql/extsql-test/install/8.4.1> bin/pg_ctl -D /mysql/extsql-test/install/8.4.1/data  -l logfile -m fast stop

/mysql/extsql-test/install/8.4.1> cp -a  /home/phil/tmp/postgresql-8.4.1/src/backend/postgres bin

/mysql/extsql-test/install/8.4.1> tail -f logfile &

/mysql/extsql-test/install/8.4.1> bin/pg_ctl -D /mysql/extsql-test/install/8.4.1/data  -l logfile -m fast start
IF - you are monitoring the logfile, you should see something like the following:
LOG:  ExtSQL start type(NORMAL): 
LOG:  ExtSQL: access allowed only to users: thebook, phil
LOG:  ExtSQL ACTIVE allocated memory: 67320 bytes for 3 classes
Current status (11/26/09) - Simple example of usage with PSQL

The newest version is patch-8.4.2.0p -- it contains the shared memory fixes described above and is 'fairly' stable. We still do not recommend it for a production server until you have confidence in the command sequences you normally use. Your help and bug reports are welcome.

Again, our documentation has complete syntax. Below is a simple example of usage:

thebook@hammer /pub/comwww/softwareworkshop/test>psql  thebook
Welcome to psql 8.4.1, the PostgreSQL interactive terminal.


thebook=# select * from friend limit 1;
    firstname    |       lastname       |      city       | state | age 
-----------------+----------------------+-----------------+-------+-----
 Mary            | Smith                | Lyons           | NY    |  45
(1 row)

thebook=# select * from friend limit 1;
    firstname    |       lastname       |      city       | state | age 
-----------------+----------------------+-----------------+-------+-----
 Mary            | Smith                | Lyons           | NY    |  45
(1 row)

thebook=# select * from friend limit 1;
    firstname    |       lastname       |      city       | state | age 
-----------------+----------------------+-----------------+-------+-----
 Mary            | Smith                | Lyons           | NY    |  45
(1 row)

thebook=# update friend set city='Boulder' where city='Lyons';
UPDATE 4
thebook=# show statistics * from db;
   db   | Questions | Com_delete | Com_insert | Com_select | Com_update 
--------+-----------+------------+------------+------------+------------
 (null) | 5         | 0          | 0          | 3          | 1
(1 row)

thebook=# show statistics * from "user";
  user   | Questions | Com_delete | Com_insert | Com_select | Com_update 
---------+-----------+------------+------------+------------+------------
 thebook | 6         | 0          | 0          | 3          | 1
(1 row)

thebook=# show statistics;
    Item    |                                      Value                                       
------------+----------------------------------------------------------------------------------
 Usage      | SHOW STATISTICS (* | Var[,Var]) FROM Class [LIKE 'Instance']
 Usage      |      [WHERE Var ('<'|'>'|'=') num] [ORDER BY Var] [HISTORY] [LIMIT rows_or_time]
 Version    | 
 Stat Users | thebook, phil
            |  
 db         | Max instances/in use (30/1), Max vars (5), Max time (55), Time units(m)
 db         |  Questions Com_delete Com_insert Com_select Com_update 
            |  
 user       | Max instances/in use (30/1), Max vars (5), Max time (55), Time units(h)
 user       |  Questions Com_delete Com_insert Com_select Com_update 
            |  
 server     | Max instances/in use (1/1), Max vars (5), Max time (5), Time units(d)
 server     |  Questions Com_delete Com_insert Com_select Com_update 
(13 rows)

#
#  An example which include Connections in the class list and show history
#
thebook=#  show statistics * from db history;
   db    |    minutes     | Questions | Connections | Com_delete | Com_insert | Com_select 
---------+----------------+-----------+-------------+------------+------------+------------
 thebook | 10/26/09 09:45 | 1         | 0           | 0          | 0          | 0          
 thebook | 10/26/09 09:44 | 8         | 1           | 0          | 0          | 5          
 thebook | 10/26/09 09:43 | 0         | 1           | 0          | 0          | 0          

 

[Home]    [FAQ List]    [About Us]    [Contact Us]   
 

NOTE: MySQL® is a registered trademark of Sun Microsystems. 
ExtSQL® is registered trademark of Software Workshop Inc.
ExtSQL is a separate product and should not be confused with MySQL
or PostgreSQL. It contains independently developed additional features,
released under the GPL,v.2.

©Copyright 1996-2009 Software Workshop Inc. 
u