gleu's blog

Aller au contenu | Aller au menu | Aller à la recherche

PlanetPostgreSQL

Fil des billets

Just a few hours left to submit your talks to pgconf.eu 2013

Quick blog post to remind you that you still have a few hours to submit your talks to pgconf.eu if you haven't done so yet. Read our call for paper, and submit your proposals to be part of an awesome PostgreSQL event :)

Expose the estimation of number of changed tuples since last analyze

This is the commit message of commit c87ff71f374652936a6089215a30998492b14d52 in the PostgreSQL git repository. This new feature is really interesting. It was written by Mark Kirkwood, reviewed by Laurenz Albe, and commited by Magnus Hagander.

It allows one to see when the autovacuum will fire an ANALYZE on a table. This is extremely useful. At least, to me, when I do a training course. Before, you only had dead_tuples in pg_stat_all_tuples to guess when autovacuum will fire a VACUUM. You had nothing to guess when it will fire an ANALYZE. As the commit message says, this information is available in PostgreSQL but not publicly available. This new function makes it available for the next PostgreSQL release. The PostgreSQL project only adds new features in the development branch of PostgreSQL. So the older releases won't have it. Unless you have an extension which will do the same thing.

That's what I did today: add the function in an extension, and add the extension on pgxn.org so that anyone can install it.

It's actually quite simple to install. The easier way is to use pgxnclient:

[guillaume@laptop ~]$ pgxnclient install mods_since_analyze
INFO: best version: mods_since_analyze 1.0.0
INFO: saving /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: unpacking: /tmp/tmpTHjC3c/mods_since_analyze-1.0.0.zip
INFO: building extension
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
INFO: installing extension
[sudo] password for guillaume: 
/usr/bin/mkdir -p '/usr/share/pgsql/extension'
/usr/bin/mkdir -p '/usr/share/pgsql/mods_since_analyze'
/usr/bin/mkdir -p '/usr/lib64/pgsql'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/usr/share/pgsql/extension/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/usr/share/pgsql/mods_since_analyze/'
/bin/sh /usr/lib64/pgsql/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/usr/lib64/pgsql/'

Done. Easy enough, right? :)

If you don't have pgxnclient, this is still quite easy but you need to do it manually:

  1. Download mods_since_analyze (mods_since_analyze-1.0.0.zip)
  2. Unzip the downloaded file
  3. Compile it
  4. Install it

In other words:

[guillaume@laptop ~]$ wget http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
--2013-07-07 18:34:09--  http://api.pgxn.org/dist/mods_since_analyze/1.0.0/mods_since_analyze-1.0.0.zip
Resolving api.pgxn.org (api.pgxn.org)... 88.198.47.126
Connecting to api.pgxn.org (api.pgxn.org)|88.198.47.126|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5686 (5.6K) [application/zip]
Saving to: ‘mods_since_analyze-1.0.0.zip’

100%[========================================================================================>] 5,686       --.-K/s   in 0.02s   

2013-07-07 18:34:10 (271 KB/s) - ‘mods_since_analyze-1.0.0.zip’ saved [5686/5686]

[guillaume@laptop ~]$ unzip mods_since_analyze-1.0.0.zip 
Archive:  mods_since_analyze-1.0.0.zip
cfe2bbb6ea83d02d402024821696aef7de01d7be
   creating: mods_since_analyze-1.0.0/
  inflating: mods_since_analyze-1.0.0/.gitignore  
  inflating: mods_since_analyze-1.0.0/AUTHORS  
  inflating: mods_since_analyze-1.0.0/COPYING  
  inflating: mods_since_analyze-1.0.0/INSTALL  
  inflating: mods_since_analyze-1.0.0/META.json  
  inflating: mods_since_analyze-1.0.0/Makefile  
  inflating: mods_since_analyze-1.0.0/README.md  
 extracting: mods_since_analyze-1.0.0/TODO  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.c  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.control.in  
  inflating: mods_since_analyze-1.0.0/mods_since_analyze.sql  
  inflating: mods_since_analyze-1.0.0/uninstall_mods_since_analyze.sql  
[guillaume@laptop ~]$ cd mods_since_analyze-1.0.0/
[guillaume@laptop mods_since_analyze-1.0.0]$ make && make install
cp mods_since_analyze.sql mods_since_analyze--1.0.sql
sed 's/DROP /ALTER EXTENSION mods_since_analyze ADD /' uninstall_mods_since_analyze.sql > mods_since_analyze--unpackaged--1.0.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o mods_since_analyze.o mods_since_analyze.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  -shared -o mods_since_analyze.so mods_since_analyze.o
sed 's/EXTVERSION/1.0/;s/EXTENSION/mods_since_analyze/;s/EXTCOMMENT/Expose the estimation of number of changed tuples since last analyze/' mods_since_analyze.control.in > mods_since_analyze.control
rm mods_since_analyze.o
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/mods_since_analyze'
/usr/bin/mkdir -p '/opt/postgresql-9.2/lib'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze.control '/opt/postgresql-9.2/share/extension/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./mods_since_analyze--unpackaged--1.0.sql ./mods_since_analyze--1.0.sql  '/opt/postgresql-9.2/share/mods_since_analyze/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  mods_since_analyze.so '/opt/postgresql-9.2/lib/'

Then you'll have to activate it on the database where you want to use it. You simply need to execute CREATE EXTENSION:

tests=# CREATE EXTENSION mods_since_analyze;
CREATE EXTENSION

It will add a single function:

tests=# \dx+ mods_since_analyze
  Objects in extension "mods_since_analyze"
             Object Description              
---------------------------------------------
 function pg_stat_get_mod_since_analyze(oid)
(1 row)

This function returns the number of changed tuples since the last ANALYZE (manual or automatic).

tests=# CREATE TABLE t1(id integer);
CREATE TABLE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+-----------------------------
now                               | 2013-07-07 22:32:41.25594+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:00.408815+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:04.569035+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 
pg_stat_get_mod_since_analyze     | 1000000

tests=# ANALYZE;
ANALYZE
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:09.00094+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

tests=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
tests=# DELETE FROM t1 WHERE id<100000;
DELETE 199998
tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:33:32.42597+02
pg_stat_get_last_autoanalyze_time | 
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 1199998

tests=# SELECT now(), pg_stat_get_last_autoanalyze_time('t1'::regclass),
  pg_stat_get_last_analyze_time('t1'::regclass), pg_stat_get_mod_since_analyze('t1'::regclass);
-[ RECORD 1 ]---------------------+------------------------------
now                               | 2013-07-07 22:34:52.344178+02
pg_stat_get_last_autoanalyze_time | 2013-07-07 22:34:15.050458+02
pg_stat_get_last_analyze_time     | 2013-07-07 22:33:07.096124+02
pg_stat_get_mod_since_analyze     | 0

It works from the 9.0 release to the 9.3 release.

Hope you'll enjoy it.

Only 20 days left to send your talk proposal for PostgreSQL Conference Europe 2013

If you haven't already send a proposal, you should seriously consider it. PostgreSQL Conference Europe 2013 will take place in Dublin, Ireland, on October 29-November 1, and we accept talks on a wide range of topics related to PostgreSQL:

  • Developing applications for PostgreSQL
  • Administering large scale PostgreSQL installations
  • Case studies and/or success stories of PostgreSQL deployments
  • PostgreSQL tools and utilities
  • PostgreSQL hacking
  • Community & user groups
  • Tuning the server
  • Migrating from other systems
  • Scaling/replication
  • Benchmarking & hardware
  • PostgreSQL related products

To be honest, I would love to see more case studies, as we usually don't have a lot and that they are really interesting to many people.

Well, there are only 20 days left to send your proposal. Read our call for paper, think about something you want to talk about, and submit your proposals (yes, you can submit more than one :) ).

By the way, we are also still looking for sponsors - please see our website for details about the sponsor benefits and the costs.

Adding read capabilities to a Foreign Data Wrapper

In the first article, we talked about the minimum required functions to be able to add a foreign data wrapper to a PostgreSQL database. Alas, we couldn't do much with it, as it didn't have other capabilities. And one of the most interesting capabilities of a FDW is to access remote datas. In other words, read them.

So, in this blog post, we'll see how we can add reading capabilities to our simple FDW.

In the previous blog post, we said that there are four hook functions to implement to get this capability:

  • BeginForeignScan, to begin executing a foreign scan
  • IterateForeignScan, to fetch one row from the foreign source
  • ReScanForeignScan, to restart the scan from the beginning
  • EndForeignScan, to end the scan and release resources

Actually, we also need some functions used by the planner:

  • Before 9.2
    • PlanForeignScan (<9.2)
  • In 9.2
    • GetForeignRelSize (>=9.2), to obtain relation size estimates for a foreign table
    • GetForeignPaths (>=9.2), to create possible access paths for a scan on a foreign table
    • GetForeignPlan (>=9.2), to create a ForeignScan plan node from the selected foreign access path

We first need to declare the functions:

/*
 * Callback functions
 */

/* Planner functions */
#if (PG_VERSION_NUM >= 90200)
static void simpleGetForeignRelSize(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid);
static void simpleGetForeignPaths(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid);
static ForeignScan *simpleGetForeignPlan(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid,
    ForeignPath *best_path,
    List *tlist,
    List *scan_clauses);
#else
static FdwPlan *simplePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel);
#endif

/* Executor reading functions */
static void simpleBeginForeignScan(ForeignScanState *node, int eflags);
static TupleTableSlot *simpleIterateForeignScan(ForeignScanState *node);
static void simpleReScanForeignScan(ForeignScanState *node);
static void simpleEndForeignScan(ForeignScanState *node);

And we need to set the hooks in the handler function, which looks like this now:

Datum
simple_fdw_handler(PG_FUNCTION_ARGS)
{
	FdwRoutine *fdwroutine = makeNode(FdwRoutine);

	elog(DEBUG1,"entering function %s",__func__);

	/* assign the handlers for the FDW */
#if (PG_VERSION_NUM >= 90200)
	fdwroutine->GetForeignRelSize = simpleGetForeignRelSize;
	fdwroutine->GetForeignPaths = simpleGetForeignPaths;
	fdwroutine->GetForeignPlan = simpleGetForeignPlan;
#else
	fdwroutine->PlanForeignScan = simplePlanForeignScan;
#endif
	fdwroutine->BeginForeignScan = simpleBeginForeignScan;
	fdwroutine->IterateForeignScan = simpleIterateForeignScan;
	fdwroutine->ReScanForeignScan = simpleReScanForeignScan;
	fdwroutine->EndForeignScan = simpleEndForeignScan;

	PG_RETURN_POINTER(fdwroutine);
}

The new code is the eight lines starting with fdwroutine->, and the ifdef statement. They set each of the hook functions we will need to read our remote datas.

For now, let's do something really simple so that we can compile it, run it, and understand how it works. We'll start with this code:

static void
simpleGetForeignRelSize(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid)
{
	SimpleFdwPlanState *fdw_private;

	elog(DEBUG1,"entering function %s",__func__);

	baserel->rows = 0;

	fdw_private = palloc0(sizeof(SimpleFdwPlanState));
	baserel->fdw_private = (void *) fdw_private;
}

static void
simpleGetForeignPaths(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid)
{
	Cost		startup_cost,
				total_cost;

	elog(DEBUG1,"entering function %s",__func__);

	startup_cost = 0;
	total_cost = startup_cost + baserel->rows;

	add_path(baserel, (Path *)
		create_foreignscan_path(root, baserel,
			 baserel->rows,
			 startup_cost,
			 total_cost,
			 NIL,
			 NULL,
			 NIL));
}

static ForeignScan *
simpleGetForeignPlan(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid,
    ForeignPath *best_path,
    List *tlist,
    List *scan_clauses)
{
	Index		scan_relid = baserel->relid;

	elog(DEBUG1,"entering function %s",__func__);

	scan_clauses = extract_actual_clauses(scan_clauses, false);

	return make_foreignscan(tlist,
		scan_clauses,
		scan_relid,
		NIL,
		NIL);
}

static void
simpleBeginForeignScan(ForeignScanState *node,
						  int eflags)
{
	elog(DEBUG1,"entering function %s",__func__);
}


static TupleTableSlot *
simpleIterateForeignScan(ForeignScanState *node)
{
	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;

	elog(DEBUG1,"entering function %s",__func__);

	ExecClearTuple(slot);

	return slot;
}


static void
simpleReScanForeignScan(ForeignScanState *node)
{
	elog(DEBUG1,"entering function %s",__func__);
}


static void
simpleEndForeignScan(ForeignScanState *node)
{
	elog(DEBUG1,"entering function %s",__func__);
}

That's quite a lot of code. Simply put, all executor void functions just call elog so that we know when the function is called. The only executor returning function is the one returning a tuple. We simply return an empty tuple right now. The planner functions do as less as they can to be able to do a SELECT statement. We'll explain what they do later.

Let's compile that code, and try it.

$ make
cp sql/simple_fdw.sql sql/simple_fdw--0.0.1.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/simple_fdw.o src/simple_fdw.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -shared -o simple_fdw.so src/simple_fdw.o -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  
$ make install
/usr/bin/mkdir -p '/opt/postgresql-9.2/lib'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  simple_fdw.so '/opt/postgresql-9.2/lib/simple_fdw.so'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./simple_fdw.control '/opt/postgresql-9.2/share/extension/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644  sql/simple_fdw--0.0.1.sql '/opt/postgresql-9.2/share/extension/'

OK, everything is in place. Let's create a database, and add our objects in it:

$ createdb my_simple_test
$ psql my_simple_test 
Expanded display is used automatically.
psql (9.2.4)
Type "help" for help.

my_simple_test=# CREATE EXTENSION simple_fdw;
CREATE EXTENSION
my_simple_test=# CREATE SERVER simple_server FOREIGN DATA WRAPPER simple_fdw;
CREATE SERVER
my_simple_test=# CREATE FOREIGN TABLE simple_table() SERVER simple_server;
CREATE FOREIGN TABLE

Now, let's read our new foreign table, but first we need to make sure we get DEBUG1 log messages:

my_simple_test=# SET client_min_messages TO debug1;
LOG:  duration: 0.188 ms  statement: set client_min_messages to debug1;
SET
my_simple_test=# SELECT * FROM simple_table ;
DEBUG:  entering function simple_fdw_handler
DEBUG:  entering function simpleGetForeignRelSize
DEBUG:  entering function simpleGetForeignPaths
DEBUG:  entering function simpleGetForeignPlan
DEBUG:  entering function simple_fdw_handler
DEBUG:  entering function simpleBeginForeignScan
DEBUG:  entering function simpleIterateForeignScan
DEBUG:  entering function simpleEndForeignScan
LOG:  duration: 0.438 ms  statement: select * from simple_table ;
(No rows)

So, what happened? the SELECT statement is executed in two parts: the planner part, and the executor part. The planner called our handler function to make sure that the hook functions were set. Then it called each of the functions it needed: simpleGetForeignRelSize, simpleGetForeignPaths, and simpleGetForeignPlan, as I'm on a PostgreSQL 9.2 release. Then the executor called once again the handler function. And then it started reading the table: it called simpleBeginForeignScan to prepare the reading, simpleIterateForeignScan as much as needed till it got an empty tuple (so only once in our case), and then simpleEndForeignScan to clean everything that needed to get cleaned. We got no rows because our code was that simple.

Now, we need to make it actually read something. We'll use an sqlite database as our example, but the idea is the same for every FDW you can find.

To read a table on an sqlite database, you need to open a database connection, prepare a query, and then loop on all the tuples returned by your query. At the end, you'll have to close the database connection. Here is a simple code that do it alone (I mean, outside of a FDW):

  sqlite3 *db;
  sqlite3_stmt * pStmt;
  const char * pzTail;

  rc = sqlite3_open("/path/to/database_file.db", &db);
  rc = sqlite3_prepare(db, "SELECT * FROM t1", -1, &pStmt, &pzTail);

  do {
        rc = sqlite3_step(pStmt);
        if (rc == SQLITE_ROW) {
            printf("value col 1 %d\n", sqlite3_column_int(pStmt, 0));
            printf("value col 2 %s\n", sqlite3_column_text(pStmt, 1));
        }
    } while (rc == SQLITE_ROW);

  sqlite3_close(db);

Of course, this is a really simple code, without error checking and stuff like that. Anyway, that's enough to work on our simple FDW. Remember that we have at least three functions to code:

  • simpleBeginForeignScan will open the database connection (sqlite3_open)
  • simpleIterateForeignScan will prepare the query (sqlite3_prepare) and fetch the first tuple (sqlite3_step) if it didn't do that already, otherwise it will only fetch the next tuple (sqlite3_step)
  • and simpleEndForeignScan will close the database connection (sqlite3_close)

The question is: how can we give the connection handle and the statement handle created in the first and second function to the second and third functions? and the answer is: with some private Foreign Data Wrapper structure that will be stored in the ForeignScanState structure.

So, onto the code. The first thing we need to do is add the sqlite header file:

#include <sqlite3.h>

We also need to declare our private data structure:

/*
 * FDW-specific information for ForeignScanState.fdw_state.
 */
typedef struct simpleFdwExecutionState
{
	sqlite3       *conn;
	sqlite3_stmt  *result;
	char          *query;
} SimpleFdwExecutionState;

This structure will contain the connection handle, the statement handle, and the query string.

Now, we will write the simpleBeginForeignScan. It will open the connection, and prepare the query. As our foreign table accepts one option, the table name, we'll take advantage of this to name the table we want to read. We don't yet have the code to get the options back. We'll write a function to get them. Here is its declaration:

static void simpleGetOptions(Oid foreigntableid, char **database, char **table);

And here is its code:

/*
 * Fetch the options for a simple_fdw foreign table.
 */
static void
simpleGetOptions(Oid foreigntableid, char **database, char **table)
{
	ForeignTable   *f_table;
	ForeignServer  *f_server;
	List           *options;
	ListCell       *lc;

	/*
	 * Extract options from FDW objects.
	 */
	f_table = GetForeignTable(foreigntableid);
	f_server = GetForeignServer(f_table->serverid);

	options = NIL;
	options = list_concat(options, f_table->options);
	options = list_concat(options, f_server->options);

	/* Loop through the options */
	foreach(lc, options)
	{
		DefElem *def = (DefElem *) lfirst(lc);

		if (strcmp(def->defname, "database") == 0)
			*database = defGetString(def);

		if (strcmp(def->defname, "table") == 0)
			*table = defGetString(def);
	}

	/* Check we have the options we need to proceed */
	if (!*database && !*table)
		ereport(ERROR,
			(errcode(ERRCODE_SYNTAX_ERROR),
			errmsg("a database and a table must be specified")
			));
}

The code is easy to read. We first get a handle on the foreign table and on the foreign server. We put all options into the options variable, and we loop through it. We check that we have the two options we need and we set the variables for them. It errors out if we don't have the two options we need (ie, the database name and the table name).

Now that we can get the two options through this function, we can write the code of the simpleBeginForeignScan hook function:

static void
simpleBeginForeignScan(ForeignScanState *node,
						  int eflags)
{
	sqlite3                  *db;
	SimpleFdwExecutionState  *festate;
	char                     *svr_database = NULL;
	char                     *svr_table = NULL;
	char                     *query;
    size_t                   len;

	elog(DEBUG1,"entering function %s",__func__);

	/* Fetch options  */
	simpleGetOptions(RelationGetRelid(node->ss.ss_currentRelation), &svr_database, &svr_table);

	/* Connect to the server */
	if (sqlite3_open(svr_database, &db)) {
		ereport(ERROR,
			(errcode(ERRCODE_FDW_OUT_OF_MEMORY),
			errmsg("Can't open sqlite database %s: %s", svr_database, sqlite3_errmsg(db))
			));
		sqlite3_close(db);
	}

	/* Build the query */
    len = strlen(svr_table) + 15;
    query = (char *)palloc(len);
    snprintf(query, len, "SELECT * FROM %s", svr_table);

	/* Stash away the state info we have already */
	festate = (SimpleFdwExecutionState *) palloc(sizeof(SimpleFdwExecutionState));
	node->fdw_state = (void *) festate;
	festate->conn = db;
	festate->result = NULL;
	festate->query = query;
}

Once again, it's quite easy to read. We first grab the database and table names. Then, we open the database with the sqlite3_open function. If it didn't work, we use ereport to report the error. If everything is fine, we build the query string, which will simply be a "SELECT * FROM" and our table name. Finally, we allocate some memory for our SimpleFdwExecutionState structure, and we push the connection handle and the query string in it.

Now that the FDW can open a connection, we need to be able to execute a query, and fetch each resulting row. This is the aim of the simpleIterateForeignScan function. It will prepare the query if it hasn't done so, and then it will fetch the next tuple. Here is its code:

static TupleTableSlot *
simpleIterateForeignScan(ForeignScanState *node)
{
	char        **values;
	HeapTuple   tuple;
	int         x;
	const char  *pzTail;
	int         rc;

	SimpleFdwExecutionState *festate = (SimpleFdwExecutionState *) node->fdw_state;
	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;

	elog(DEBUG1,"entering function %s",__func__);

	/* Execute the query, if required */
	if (!festate->result)
	{
		rc = sqlite3_prepare(festate->conn, festate->query, -1, &festate->result, &pzTail);
		if (rc!=SQLITE_OK) {
			ereport(ERROR,
				(errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),
				errmsg("SQL error during prepare: %s", sqlite3_errmsg(festate->conn))
				));
			sqlite3_close(festate->conn);
		}
	}

	ExecClearTuple(slot);

	/* get the next record, if any, and fill in the slot */
	if (sqlite3_step(festate->result) == SQLITE_ROW)
	{
		/* Build the tuple */
		values = (char **) palloc(sizeof(char *) * sqlite3_column_count(festate->result));

		for (x = 0; x < sqlite3_column_count(festate->result); x++)
			values[x] = sqlite3_column_text(festate->result, x);

		tuple = BuildTupleFromCStrings(
			TupleDescGetAttInMetadata(node->ss.ss_currentRelation->rd_att),
			values);
		ExecStoreTuple(tuple, slot, InvalidBuffer, false);
	}

	/* then return the slot */
	return slot;
}

sqlite3_column_count gives us the number of columns in the resulting tuple, and we use that in a for loop, to get each of the values. BuildTupleFromCStrings builds a HeapTuple from all gathered values, and ExecStoreTuple stores the HeapTuple in the slot.

Finally, we need the ending function. It will clean every part of memory we allocated to scan the remote table. So, it's pretty simple: we have to deallocate the query string, the statement handle, and the database handle:

static void
simpleEndForeignScan(ForeignScanState *node)
{
	SQLiteFdwExecutionState *festate = (SQLiteFdwExecutionState *) node->fdw_state;

	elog(DEBUG1,"entering function %s",__func__);

	if (festate->result)
	{
		sqlite3_finalize(festate->result);
		festate->result = NULL;
	}

	if (festate->conn)
	{
		sqlite3_close(festate->conn);
		festate->conn = NULL;
	}

	if (festate->query)
	{
		pfree(festate->query);
		festate->query = 0;
	}

}

And we're done. We should test it right away. I'll use the Chinook sample database (file ChinookDatabase1.4_CompleteVersion.zip) as an example database. So here is how we can use the simple FDW:

my_simple_test=# DROP EXTENSION simple_fdw cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server simple_server
drop cascades to foreign table simple_table
DROP EXTENSION
my_simple_test=# CREATE EXTENSION simple_fdw;
CREATE EXTENSION
my_simple_test=# CREATE SERVER simple_server
    FOREIGN DATA WRAPPER simple_fdw
    OPTIONS (database '/home/guillaume/FDW/example/chinook/Chinook_Sqlite.sqlite');
CREATE SERVER
my_simple_test=# CREATE FOREIGN TABLE genre (g_id integer, g_name text)
    SERVER simple_server
    OPTIONS (table 'Genre');
CREATE FOREIGN TABLE
my_simple_test=# SELECT * FROM genre;
 g_id |       g_name       
------+--------------------
    1 | Rock
    2 | Jazz
    3 | Metal
    4 | Alternative & Punk
    5 | Rock And Roll
    6 | Blues
    7 | Latin
    8 | Reggae
    9 | Pop
   10 | Soundtrack
   11 | Bossa Nova
   12 | Easy Listening
   13 | Heavy Metal
   14 | R&B/Soul
   15 | Electronica/Dance
   16 | World
   17 | Hip Hop/Rap
   18 | Science Fiction
   19 | TV Shows
   20 | Sci Fi & Fantasy
   21 | Drama
   22 | Comedy
   23 | Alternative
   24 | Classical
   25 | Opera
(25 rows)

That worked perfectly!

So, we had to create quite a lof of functions to be able to read some remote data: add some planner hook functions, add some executor reading hook functions, add some helper functions (actually one to get the list of options). That wasn't hard to do, but there are plenty of things to know.

To be honest, we didn't put many thoughts on the planner functions. Even if we get 25 rows, the planner still thinks we'll have 0 rows, and that it will cost nothing to read the remote data. And we didn't use the simpleReScanForeignScan function.

But this is enough for today. We'll go deeper in the planner hook functions in another blog post.

By the way, the simple FDW is available on this github repository: Writing A FDW. This repository will contain all the code written for this serie of blog posts.

The handler and the validator functions of a FDW

I'm still working on my foreign data wrapper. Actually, I haven't written a lot of code. Instead, I've been reading the code of the other foreign data wrappers and it helped me a lot to understand many things, and to see how far each went.

I'll try to start a few blog posts about coding a foreign data wrapper.

This first one (this one) will be about the two functions needed to declare a foreign data wrapper. Let's take a look at the CREATE FOREIGN DATA WRAPPER DDL statement:

CREATE FOREIGN DATA WRAPPER name
    [ HANDLER handler_function | NO HANDLER ]
    [ VALIDATOR validator_function | NO VALIDATOR ]
    [ OPTIONS ( option 'value' [, ... ] ) ]

The handler function is available since 9.1. The other options have always been available (at least from the beginning of the SQL/MED infrastructure in PostgreSQL, which is the 8.4 release).

The handler function is the function you use to declare all the foreign data wrappers hooks you'll need. Here are the complete list, with the first version they were available in :

  • plan functions (used by the planner)
    • GetForeignRelSize (>=9.2), obtain relation size estimates for a foreign table, called at the beginning of a query planning
    • GetForeignPaths (>=9.2), create possible access paths for a scan on a foreign table, called during query planning
    • GetForeignPlan (>=9.2), create a ForeignScan plan node from the selected foreign access path, called at the end of a query planning
    • PlanForeignScan (<9.2)
  • read functions (used by the executor)
    • BeginForeignScan, begin executing a foreign scan
    • IterateForeignScan, fetch one row from the foreign source
    • ReScanForeignScan, restart the scan from the beginning
    • EndForeignScan, end the scan and release resources
  • write functions (>=9.3, used by the executor)
    • AddForeignUpdateTargets, used to add extra hidden target columns to the list of columns that are to be retrieved from the foreign table during an UPDATE or DELETE
    • PlanForeignModify, perform any additional planning actions needed for an insert, update, or delete on a foreign table
    • BeginForeignModify, begin executing a foreign table modification operation
    • ExecForeignInsert, insert one tuple into the foreign table
    • ExecForeignUpdate, update one tuple into the foreign table
    • ExecForeignDelete, delete one tuple into the foreign table
    • EndForeignModify, end the table update and release resources
  • explain functions (used during the execution of an EXPLAIN)
    • ExplainForeignScan, print additional EXPLAIN output for a foreign table scan
    • ExplainForeignModify (>=9.3), print additional EXPLAIN output for a foreign table update
  • analyze function (>=9.2, used during the execution of an ANALYZE)
    • AnalyzeForeignTable, collect statistics for this foreign table when ANALYZE is executed on it

None are really required. Of course, if you implement none of them, the foreign data wrapper won't be really useful :)

All these hooks are to be set inside a structure of type FdwRoutine. It's best to use the makeNode() function to set all fields to NULL. Then, you'll be able to set each of the hooks you really need, and don't care about the rest.

At the end, this function hands back the FdwRoutine structure to the caller of the function.

So the code looks something like this:

Datum
my_fdw_handler(PG_FUNCTION_ARGS)
{
  FdwRoutine *fdwroutine = makeNode(FdwRoutine);

  // plan functions
#if (PG_VERSION_NUM >= 90200)
  fdwroutine->GetForeignRelSize = myGetForeignRelSize;
  fdwroutine->GetForeignPaths = myGetForeignPaths;
  fdwroutine->GetForeignPlan = myGetForeignPlan;
#else
  fdwroutine->PlanForeignScan = myPlanForeignScan;
#endif

  // read functions
  fdwroutine->BeginForeignScan = myBeginForeignScan;
  fdwroutine->IterateForeignScan = myIterateForeignScan;
  fdwroutine->ReScanForeignScan = myReScanForeignScan;
  fdwroutine->EndForeignScan = myEndForeignScan;

#if (PG_VERSION_NUM >= 90300)
  // write functions
  fdwroutine->AddForeignUpdateTargets = myAddForeignUpdateTargets;
  fdwroutine->PlanForeignModify = myPlanForeignModify;
  fdwroutine->BeginForeignModify = myBeginForeignModify;
  fdwroutine->ExecForeignInsert = myExecForeignInsert;
  fdwroutine->ExecForeignUpdate = myExecForeignUpdate;
  fdwroutine->ExecForeignDelete = myExecForeignDelete;
  fdwroutine->EndForeignModify = myEndForeignModify;
#endif

  // explain functions
  fdwroutine->ExplainForeignScan = myExplainForeignScan;
#if (PG_VERSION_NUM >= 90300)
  fdwroutine->ExplainForeignModify = myExplainForeignModify;
#endif

#if (PG_VERSION_NUM >= 90200)
  // analyze functions
  fdwroutine->AnalyzeForeignTable = myAnalyzeForeignTable;
#endif

  PG_RETURN_POINTER(fdwroutine);
}

Of course, all the my* functions must be previously declared. Rather than the actual code, the more interesting thing to know in a foreign data wrapper is which hooks it support. It really depends a lot on the FDW. For example, the Oracle FDW doesn't support yet the write functions. The MySQL FDW doesn't support the write, the explain, and the analyze functions. Etc.

The validator function checks the options given to a specific foreign object (foreign data wrapper, foreign server, user mapping, and foreign table). It gets a list of all the options, and the context of the options (aka the foreign object type). The function has to go through each of them, checks that they are valid, and error out if anything wrong happens (send back the ERRCODE_FDW_INVALID_OPTION_NAME error if it isn't a valid option, please).

A good way of doing it is to first add a FDW option structure, this way:

struct myFdwOption
{
  const char  *optname;
  Oid   optcontext;
};

The optname will obviously be the option name. The optcontext will contain the kind of object this option is for. There are four possibilities: ForeignDataWrapperRelationId, ForeignServerRelationId, UserMappingRelationId, and ForeignTableRelationId.

The options could be declared like this:

static struct myFdwOption valid_options[] =
{
  { "database",  ForeignServerRelationId },
  { "table",     ForeignTableRelationId },
  { NULL,     InvalidOid }
};

So, a simple code for the validator function might be:

Datum
my_fdw_validator(PG_FUNCTION_ARGS)
{
  List      *options_list = untransformRelOptions(PG_GETARG_DATUM(0));
  Oid       catalog = PG_GETARG_OID(1);
  ListCell  *cell;
  char      *my_database = NULL;
  char      *my_table = NULL;

  foreach(cell, options_list)
  {
    DefElem    *def = (DefElem *) lfirst(cell);

    if (!myIsValidOption(def->defname, catalog))
    {
      struct SQLiteFdwOption *opt;
      StringInfoData buf;

      /*
       * Unknown option specified, complain about it. Provide a hint
       * with list of valid options for the object.
       */
      initStringInfo(&buf);
      for (opt = valid_options; opt->optname; opt++)
      {
        if (catalog == opt->optcontext)
          appendStringInfo(&buf, "%s%s", (buf.len > 0) ? ", " : "",
               opt->optname);
      }

      ereport(ERROR, 
        (errcode(ERRCODE_FDW_INVALID_OPTION_NAME), 
        errmsg("invalid option \"%s\"", def->defname), 
        errhint("Valid options in this context are: %s", buf.len ? buf.data : "<none>")
        ));
    }

    /*
     * Here is the code for the valid options
     */

    if (strcmp(def->defname, "database") == 0)
    {
      if (my_database)
        ereport(ERROR,
          (errcode(ERRCODE_SYNTAX_ERROR),
          errmsg("redundant options: database (%s)", defGetString(def))
          ));

      my_database = defGetString(def);
    }
    else if (strcmp(def->defname, "table") == 0)
    {
      if (my_table)
        ereport(ERROR,
          (errcode(ERRCODE_SYNTAX_ERROR),
          errmsg("redundant options: table (%s)", defGetString(def))
          ));

      my_table = defGetString(def);
    }
  }

  PG_RETURN_VOID();
}

The myIsValidOption() function will go through the options and checks that it matches the right context. Just as is:

static bool
myIsValidOption(const char *option, Oid context)
{
  struct myFdwOption *opt;

  for (opt = valid_options; opt->optname; opt++)
  {
    if (context == opt->optcontext && strcmp(opt->optname, option) == 0)
      return true;
  }
  return false;
}

After compilation and installation of such a FDW, we can try creating foreign objects:

yeah=# CREATE EXTENSION my_fdw ;
CREATE EXTENSION
-- we can add a server without options
yeah=# CREATE SERVER myserver FOREIGN DATA WRAPPER my_fdw ;
CREATE SERVER
-- we cannot add a server with unknown options
yeah=# CREATE SERVER myserver3 FOREIGN DATA WRAPPER my_fdw OPTIONS (inconnu '10') ;
ERROR:  invalid option "inconnu"
HINT:  Valid options in this context are: database
-- but we can add one with a valid option
yeah=# CREATE SERVER myserver2 FOREIGN DATA WRAPPER my_fdw OPTIONS (database 'this one') ;
CREATE SERVER
-- we can also create foreign table without options
yeah=# CREATE FOREIGN TABLE ft(id integer) SERVER myserver2;
CREATE FOREIGN TABLE
-- we cannot add a foreign table with unknown options
yeah=# CREATE FOREIGN TABLE ft2(id integer) SERVER myserver2 OPTIONS (inconnu '10');
ERROR:  invalid option "inconnu"
HINT:  Valid options in this context are: table
-- we can add a foreign table with valid options
yeah=# CREATE FOREIGN TABLE ft2(id integer) SERVER myserver2 OPTIONS (table '10');
CREATE FOREIGN TABLE

So the validator function seems to work. The handler function doesn't do anything. If you try to read the foreign table, it will... err.. let's simply say that we still need something to make it work:

yeah=# select * from ft2;
The connection to the server was lost. Attempting reset: Failed.
!> 

We'll see how it can send back some lines next time.

sqlite foreign data wrapper

Last week, one of my customers asked me during a training why there is no foreign data wrapper for sqlite (actually, you can have one with multicorn, but there's no native one). I have no idea why but I was happy to learn that no sqlite FDW already existed. I wanted to write one for quite some time now, and it appeared to be the perfect idea.

So, in the evening, I started working on one. I took as foundation the blackhole foreign data wrapper, written by Andrew Dunstan. It helped a lot to start quickly. I found a bit surprising that it didn't include #ifdef for some functions and hooks, so that it could be compatible with 9.1 and 9.2. I added them in a patch that you can find here if you need them. Otherwise, you can simply delete some parts of the blackhole code.

After that, I tried to find how to open and close a sqlite database, and how to read a table in it. They have a really nice example in their "Quick start document" and "Introduction To The SQLite C/C++ Interface".

I wanted something really simple to start with. Almost two years ago, at pgconf.eu, I went to see Dave Page's talk on FDW (PostgreSQL at the center of your dataverse). So I already knew that you mainly need three functions to read a table: BeginForeignScan, IterateForeignScan, EndForeignScan. The first one has to open the connection (if it's not opened yet). The second one will be executed as many times as there are rows to grab. On its first execution, it must launch the query and get the first row. On every other iteration, it will grab a new row. And the third function helps cleaning memory. So I started to include the sqlite tutorial code in the blackhole FDW. And it worked great. I had to write some other functions, specifically the handler and the validator, but,in an hour, I had something working. Of course, it wasn't pretty. The database filename was written in the code, with no option to change it. The query executed remotely was also written in the code, which means you couldn't change the tablename without recompiling.

So I started to look at options for the foreign server and the foreign table. Most of the code was taken from Dave Page's mysql_fdw code. I quickly had a foreign data wrapper allowing two options: database (to change the database file), and table (to target a specific remote table).

The whole code is available on the sqllite_fdw github repository. I'll try to add the missing features as soon as possible. And when I'll have something working and tested, I'll post it on pgxn.

pgconf.eu 2012, a quick recap

One year of work, that's all it takes to build such an event. That doesn't seem a lot, but believe me, it is. And we are still working: invoices to pay, feedbacks to collect, etc. But it's worth it. It's more than simply "worth it". Seeing so many people come and have a good time talking about PostgreSQL, that's just awesome.

prague.png

I didn't see as many talks as I would have liked, but I did see some, mostly because I was a room host twice. In a weird way, the most interesting talks for me were talks from people I didn't know about before pgconf.eu and that I would have not seen if I weren't a room host. Roland Sonnenschein talked about PostgreSQL in Research & Development. He explained how he uses PostgreSQL in critical missions, and how he was able to sustain really big insert load with some tricks. The little tricks he gave were smart and inspiring. I hope I'll have the time to use that kind of tricks with some of my customers. PostBIS was another surprising talk with its explanation of the good and bad of the compression algorithm in PostgreSQL. Of the people I knew, the most interesting talks were from Alexey Klyukin ("Migrating Oracle queries to PostgreSQL"), Gianni Ciolli ("Debugging complex queries..."), and Hans-Jürgen Schönig ("Limiting PostgreSQL ressource consumption"). They were all inspiring, and I intend to use every bit of information when doing audits for my customers or when giving a training course on PostgreSQL.

My own talk ("Understanding EXPLAIN and its output") went pretty good, I think. The feedbacks I got were usually good. The slides and notes are available here if you're interested.

Of course, pgconf.eu isn't only about talks. The hallway track is also a place to be. I had a great time meeting old-timers, but also newcomers. Meeting Joe Celko and Markus Winand (the guy behind the use-the-index-luke website, and I recommend you to buy and read his book) was great.

I also went to the lightning talks. That's always a lot of fun. I can't think of anyone who could do a better job than Harald Armin Massa. He's really the right guy to introduce the speakers in a lightning talk. Can't wait to see him next year.

Well, that's it. For those who couldn't come (shame on you :) ), you can still read the slides on the PostgreSQL wiki (but sorry, no video), and mark on your calendar that another event is planned next year, in mid-october.

See you all next year!

pgAdmin 1.16 beta 1 is out

At the same time PostgreSQL 9.2 beta 1 was released, so did pgAdmin 1.16 beta 1 (with an unfortunate copy/paste error on the URL to download beta 1).

We don't have yet installers for Windows, and packages for Mac OS X. They will come at a later time.

What you can expect from pgAdmin 1.16 is complete compatibility with PostgreSQL 9.2. All the new features of PostgreSQL 9.2 are handled by pgAdmin 1.16. You can use pgAdmin 1.14 with PostgreSQL 9.2, but you won't get all the new parameters, and objects. Moreover, you'll have issues with the tablespaces node (one column disappears in the pg_tablespace system catalog).

One thing you shouldn't expect from pgAdmin 1.16, and I'm really sorry about that, is the database designer. This really cool tool isn't ready for prime time yet. We still need more time to fix the bugs, to make sure it's usable on every plateform, and to do all the little things that make a product great. You can still try it if you want, but you'll need to compile pgAdmin (look for the --enable-databasedesigner ./configure option).

I'm working right now on a visual tour for 1.16, but it may take some time before it gets out.

During the beta period, I'll also fix the bugs testers will encounter, and work on the documentation.

So, as you see, still a lot of work. But don't hesitate to install pgAdmin 1.16 beta 1, search for bugs, and report them on our mailing lists, so that we can have a really great 1.16.0 release.

Profiling PL/pgsql functions

I spent my whole week trying to fix PL/pgsql functions. More to the point, I tried to make them perform better. My customer complains that they aren't fast enough, and I agree with him: some of his PL/pgsql functions are really slow. Unfortunately, he uses a lot of PL/pgsql functions. One function may call many other functions, and it's hard to tell which one is the culprit (if there is only one).

My first step was to use pgFouine on a three-days log to know which queries are really slow on his system. Three queries stood out as being the real offenders. I tried to EXPLAIN ANALYZE them, but found that these queries involved lots of views and functions. Not really easy queries to fix. So I set track_functions to pl, so that I can see which functions were called by one query, and which functions are the really offending ones. Here is the result I got:

b1=# SELECT funcname, calls, total_time, self_time
b1-# FROM pg_stat_user_functions;
 funcname  | calls  | total_time | self_time 
-----------+--------+------------+-----------
 f0        |      1 |     328486 |      1375
 f1        |      1 |        210 |       210
 f2        |      1 |        123 |       123
 f3        |      1 |      20310 |     20310
 f4        |      1 |        763 |       763
 f51       |      1 |      33486 |     33486
 f52       |      1 |     250363 |    239703
 f53       |      1 |      19139 |     19139
 f6        |      1 |        399 |       399
 f7        |      1 |       1770 |      1770
 f8        |      1 |        543 |       543
 f_compute | 110588 |      10660 |     10660
(12 rows)

Some context first, I only executed f0(), which itself called all the other functions. So what do I get from this? f_compute() is executed 110588 times. That's a lot, but it doesn't consume much of f0() execution time (3%). f52() is only executed once, but took 76% of the total execution time of f0(). If I can make it run faster, I would probably gain a lot.

But there's more. f52() executes other functions, but its own execution took most of the time (95%). So what I really need to know is how long each of its statements last. There's not many way to know that actually. You can modify the function source code to add RAISE LOG statements in this function (you can find many examples on this depesz's post). It takes a lot of time to do that, you may do it wrong, you cannot always do it. You can also use the plprofiler from EnterpriseDB. That's the first thing I did. Due to the number of statements in each of the functions the query called, my query went from 5.4 minutes to more than two hours of execution time. I canceled it because I couldn't wait any longer. The only thing left was to modify the function source code, but I really hate this idea.

So I read the plprofiler code, and found a better way to do it. The plprofiler is a shared library that uses hooks inside the PL/pgsql interpreter to grab lots of information for each statement of each executed function. I only had to change the code to make it less a burden on my system. All I wanted was to get a line in the PostgreSQL log files when a function starts, when it ends, and when a function's statement is executed. It turns out that it's actually quite easy to do. You can find the code on the log_functions github page. After compilation, installation, and configuration of this tool, and then the execution of the query, I got this log (somewhat edited to make it fit better on this blog post):

10:56:24 LOG:  log_functions, DECLARE, f52
10:56:24 LOG:  log_functions, BEGIN, f52
10:56:24 LOG:  log_functions, STMT, line 7, type 14
10:56:24 LOG:  log_functions, STMT, line 14, type 14
10:56:24 LOG:  log_functions, STMT, line 23, type 14
10:56:24 LOG:  log_functions, STMT, line 32, type 14
10:56:24 LOG:  log_functions, STMT, line 41, type 14
10:56:24 LOG:  log_functions, STMT, line 52, type 14
10:56:24 LOG:  log_functions, STMT, line 75, type 14
11:00:14 LOG:  log_functions, STMT, line 99, type 14
11:00:14 LOG:  log_functions, STMT, line 110, type 14
11:00:14 LOG:  log_functions, STMT, line 122, type 14
11:00:14 LOG:  log_functions, STMT, line 133, type 14
11:00:14 LOG:  log_functions, STMT, line 145, type 14
11:00:14 LOG:  log_functions, STMT, line 156, type 14
11:00:14 LOG:  log_functions, STMT, line 168, type 14
11:00:14 LOG:  log_functions, STMT, line 179, type 14
11:00:14 LOG:  log_functions, STMT, line 191, type 14
11:00:14 LOG:  log_functions, STMT, line 202, type 14
11:00:14 LOG:  log_functions, STMT, line 214, type 14
11:00:14 LOG:  log_functions, STMT, line 225, type 14
11:00:14 LOG:  log_functions, STMT, line 237, type 14
11:00:14 LOG:  log_functions, STMT, line 248, type 14
11:00:14 LOG:  log_functions, STMT, line 260, type 14
11:00:14 LOG:  log_functions, STMT, line 271, type 14
11:00:14 LOG:  log_functions, STMT, line 283, type 14
11:00:14 LOG:  log_functions, STMT, line 294, type 14
11:00:14 LOG:  log_functions, STMT, line 304, type 14
11:00:14 LOG:  log_functions, STMT, line 306, type 13
11:00:14 LOG:  log_functions, STMT, line 308, type 10
11:00:14 LOG:  log_functions, END, f52

I also got lucky here. All the time spent on this function is spent on the statement in line 75. So what I really need to know is why it takes so long to do this query. The search of performance for this query is not really relevant for this blog post. I only wanted to show how you can profile a PL/pgsql function easily without changing its source code.

As I think it may be a useful tool for many people, it's available on my github page, and on pgxn. Hope it can help people profiling their PL/pgsql functions.

To end this blog post, know that there are many things one can do with hooks in PostgreSQL. This is just one of them. I already explained how you can use another one to log only superuser queries. This new one will totally fit in with my talk on hooks at PGCon 2012 :)

pgAdmin and wxWidgets 2.9

pgAdmin is a multiplatform tool. This means that you can use it on various operating systems, such as Windows, Mac OS X, Linux, and even Solaris. Not sure if that last one is still supported. To be honest, I write bugfixes and new features on my Linux laptop (Fedora 16), and checks them, whenever I can, on Mac OS X (Snow Leopard, on my old Mac Mini) and Windows (XP, still on the old Mac Mini). We use wxWidgets to make it easier to code user interfaces for all platforms. pgAdmin supports wxWidgets 2.8. The developers of wxWidgets are working hard on the next big release, 3.0. To ease the jump, they have a 2.9 release since quite some time. This 2.9 version will become 3.0 "when it's ready".

I'm really happy to see that we are now compatible with 2.9. There are probably still some itches to fix, but it's already quite usable.

Peter Geoghegan was the first one to work on this, and wrote a big patch, that Dave Page applied on 2011-02-01. There were a few fixes here, and there. The second phase, done by Peter, was the merge of the wxOGL contrib into our source tree (we had the author's permission for that). And then another bunch of fixes, mostly from reports sent by Colin Beckingham, and fixed by me.

Right now, I have something that works great on Linux.

Why is it important? because it offers a better support of new features of the operating systems, and it fixes a lot of issues we had previously with wxWidgets 2.8 that the developers of wxWidgets couldn't fix (either because it would require too much changes, or because they don't have the manpower to do it). 2.9 also adds some widgets that we would definitely love to use :

  • a property grid ;
  • a list control with a tree in the first column (to display tree where each item has informations to display in columns) ;
  • a native calendar control for Linux (the previous one is a wxWidgets custom control, and is not really usable) ;
  • a tree control with checkbox (we have that via a custom widget, one that we could get rid of... remember, less code is good) ;
  • and probably many other interesting enhancements (for example, support for GTK3 would make my day).

If you can test pgAdmin 1.15 with wxWidgets 2.9, please send any issues to pgadmin-hackers.

An import tool for pgAdmin

With pgAdmin 1.14 and prior releases, there were only two ways to insert data in a table:

  • use the restore tool (which uses pg_restore) ;
  • use the query tool to execute INSERT queries (COPY queries are allowed if they use a file, but not stdin/stdout).

There was no way to ask it to import a CSV file for example. And a thread began on this matter a week ago. As working on the COPY API of libpq was quite motivating, I worked on this a few hours, and finished yesterday with this patch.

This patch adds a new window that allows a user to choose a file, and to select all options available in the COPY command (but only options available for import, not export).

This window is displayed when someone selects the "Import" item in the table's contextual menu, as shown in the following screenshot:

importmenu.png

The import window has 5 tabs:

  • "File options" tab makes possible to choose a file, sets its format (text, CSV, or binary), and optionaly its encoding.

import_win_1.png

  • "Columns" tab allows the user to specify which columns are contained in the file. Right now, they must be in the same order than the table (because of the UI), but they don't have to be all present in the file.

import_win_2.png

  • "Misc Options", "Quote Options", and "NULL options" tabs are not used usually, but it could be handy to set another delimiter than the usual comma, amongs other possibilities.

import_win_3.png import_win_4.png import_win_5.png

When the user clicks the "Import" button, pgAdmin launches a COPY query that matches the selected options, and sends the file by chunks of 8KB. The scrollbar shows the progress of the import. If the import ends with success, the "Import" button is renamed "Done", and a click on it closes the dialog.

That was a really interesting patch to write, for the UI, and the COPY API. And it is a quite nice feature for pgAdmin.

How to log superuser queries

A few weeks ago, one of my customers asked me if they could log all queries coming from superuser users. They have to make sure that none of their DBA could fire a query without that query being logged.

You can already log queries with the log_statement, or log_min_duration_statement GUCs. You can set these GUCs cluster-wide or for specific users. But there's a big issue here: the user can change the value of these GUCs, making it impossible to be sure everything they do is logged.

I first thought of writing a patch to handle this in PostgreSQL, but it also has a major issue: it won't be available for 9.0, the release they use. All new features are added to the development release, and I don't want to maintain a custom PostgreSQL release for anyone (that's too much burden). So, I came up with a nicer way of doing it.

PostgreSQL allows you to add hooks to the executor since 8.4. And you can use these hooks to know which queries are executed and by who. Actually, that's how pg_stat_statements works. So, all I needed to do was to create a small shared library that will add hooks to the executor. The hooks will check that the user executing a query is a superuser, and if that's true, log the query. Quite simple, here is the code of the hook:

static void
pgluq_log(const char *query)
{
    Assert(query != NULL);

    if (superuser())
        elog(log_level, "%s %s: %s", log_label, GetUserNameFromId(GetUserId()), query);
}

Of course, the library has more code than just that: to add/remove the hooks, to take care of two module GUCs (log level, and log prefix), but the most important stuff is on the pglug_log() function.

The module is available right now on github, and I intend to publish it via the pgxn.org website once my account will be approved. Docs are available here. It's compatible with PostgreSQL 8.4, 9.0, and 9.1.

Next thing I will do for this module is to add support for syslog. Right now, it only logs to the same location than PostgreSQL.

Follow-up on pgconf.eu 2011

Just got an email asking me where we did put the slides of the talks. As they are a bit difficult to find, here is the URL: http://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2011. Unfortunately, you won't find every speaker's slides yet. And for the record, I fixed my slides (two SQL statements were wrong... kinda lame of me).

I guess we should have the room host collects them at the end of each talk. That's something I expect us to do next year.

BTW, attendees, I don't know if you've all already done that, but remember that you can add feedbacks for the event itself and for each of the talks you've attended. Here is the webpage to do that: http://2011.pgconf.eu/feedback/.

End of pgAdmin's GSoC 2011

Luis Ochoa sent me his latest patch some time ago. It took me a while to find some time to review his patch, to fix a few bugs, but I finally did it. And I applied the resulting patch this afternoon.

What we now have is really awesome: reverse engineering of an existing schema. Even more, we can build an SQL script to create the reversed model or only apply the changes between the model and a selected database. It could be another database than the one used for the reverse engineering. One simple example to show its power. You can reverse engineer your schema on your development database, connect to your production database and ask the database designer to build the script that will contain only the SQL to push the definition changes.

Of course, right now, the code is probably quite buggy. And it lacks a lot of features. First one would be a better datatype support. Only a few types are accepted right now. User defined types aren't obviously but also some uncommon types (like tsvector), and common ones (timestamp with timezone for example). Also lacking is a support of all the table properties (fillfactor, autovacuum settings are part of the missing ones).

If you want to take a look, you should probably see the video attached to this blogpost. You'll see how the database designer works with the pagila database (I fixed a few types in the schema to show a better demo but that's the only changes I made).

If you want to see it live, maybe you should come to pgconf.eu in Amsterdam. Luis Ochoa and I will be presenting this tool, do some demos, and wait for suggestions, and feature requests.

Wanna help us adding new features to pgAdmin?

And you don't know how to start? I've been there when I first worked on pgAdmin's source code. You need to understand pgAdmin's code, and you need to know wxWidgets. And PostgreSQL of course. Trust me, it takes quite some time to feel confident.

But there are a few things you can do to help us in our coding department. There are some little features, eagerly waited by some of our users, that are quite easy to code and that could help you start coding on pgAdmin. We have a list of feature requests on http://code.pgadmin.org and I spent some time this evening to add a juniorjob tag to each easy-to-code feature. If you're interested, you should get a look at this junior job list. It would really help us to have some people working on these tickets.

And if you don't want to work on C++ code, you can still help us with the documentation. We'll try to have a complete new manual for 1.16, but we need help to write it. We also badly need translators.

Last month work on pgAdmin

Yeah, my last blog post on pgAdmin is a month old. Time to give you some news.

Let's begin with PostgreSQL compatibility. 1.14 won't handle security labels. We haven't find time to add support for that. Good news is that 1.16 will. We already have that in git. Here is how it looks on a table:

X

See the new "Security labels" tab? that's it. A better news is that we already support security labels on shared objects (I mean databases, tablespaces, and roles). And that's a 9.2 new feature.

X

Speaking of 9.2, we also added support for invalid domains and invalid check constraints.

invaliddomain.png

The user can create these objects without the validate step; They can validate them later.

I think that's about it on PostgreSQL compatibility.

I know pgAdmin for quite a long time, and I've often been bitten by this old bug: open a property dialog, refresh the object on the browser, change the object in the property dialog... and crash. This shouldn't happen again. Once a property dialog is open for an object, the user can't refresh the object or any of its parents. He cannot either drop the object or any of its parents. And he cannot close the connection. Till he closes the property dialog. I'm really glad we finally put an end to this bug. Unfortunately, it's rather a big change in behaviour, so it won't be backpatched.

Luis Ochoa continued his work on his 2011 GSoC project (the database designer). He added load/save support. Files are in XML format, and have a .pgd extension. He also added multimodel diagram, so that the user can view his schema in different ways. Here are two views of the same design:

X

X

And last, we redesigned the options window, so that we could put more options without making the dialog bigger. It follows what LibreOffice and other programs are already doing:

X

So, despite the summer vacations, we're still going forward and at a good pace.

Allow change of a schema using pgAdmin's GUI

That's something I wanted to do for quite some time, and Thom Brown beats me to it. He added a "Schema" combobox in each object that's part of a schema. For example, on a sequence, you'll get this:

sequence.png

Before this patch, the user needed to execute the "ALTER SEQUENCE... SET SCHEMA" in the query tool, switch back to the browser, and refresh it. Now, it only has to get into the properties dialog of the sequence, select the right schema in the combobox, and click on the "OK" button. Much simpler.

Of course, it works with all objects you can find in a schema.

Thanks, Thom.

Nice talks at char(11)

As Dimitri already said, char(11) is over. This was my first time at this event, and I don't regret it. The talks were mostly great. My favorites were Jan's talk on the evolution of replication, Magnus's talk on the replication protocol, and Simon's talk on the replication features in core. The GreenPlum talks were also really interesting to me because there's not much information out there on the internet about that fork and finally getting some informations feels good.

Oh, and Harald has been a huge room host, really dedicated to his work. So, congrats to him too.

Well done, 2ndQuadrant guys. Hope there will be a char(12) next year. (And don't forget to add the talks' slides on a website so that we could have another look at them. Thanks :) )

Search Object tool in pgAdmin

Something was missing in pgAdmin, something I didn't believe at first. Jasmin Dizdarevic came up some (long) time ago with a patch implementing a Search Object tool. After lots of discussion, I commited the final patch.

The contextual menu of each database contains a new menu item: "Search objects...".

menu.png

When you select this item, it'll open a new window asking you to enter a name pattern, and press the "Find" button. Once you've done that, pgAdmin will search each object whose name contains the pattern. It'll will display each object found in the results list.

searchobject_tool.png

When you click on one of the results, it will select the object in the browser.

Quite impressive feature if you ask me, and, sure, one that was missing. There are probably a few more tweaks that could be interesting to enhance this feature (like search in the comments, and objects's icons in the results list). What's great is that we still have plenty of time to do that :)

Anyway, thanks a lot to Jasmin Dizdarevic!

Database Designer is finally in

I commited it this morning. First part of Luis's GSoC project this year is now commited. pgAdmin has a graphical database designer. It's not pretty yet, it doesn't have all features I would love to see implemented, so it's rather raw. But it's there. And we can build something great with this.

Here is what I just did with it.

dd.png

I'll try to find a way to record a session of work with the database designer.

Anyway, it's available in the master branch of pgAdmin's GIT repository (http://git.postgresql.org/gitweb?p=pgadmin3.git;a=summary). Go grab it, and tell us what you think of it (but keep in mind it's its early stage :) ).

- page 1 de 3