|
|
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
|
|
|
