Tuesday, August 30, 2011

Telling the cardinality of a relationship between two tables

I was wondering: how the hell do some data modeling tools to figure out the kind of cardinality existing between two tables out of just looking just to the table structures. Well, I don't think that's the case, they have to perform some sort of query on the data. I found some insights in this forum:


With one to one relationships the issue is very straightforward, just looking for uniqueness in foreign keys does the trick, but if such uniqueness doesn't exist there's no other choice, you've got a one to many relationship as many to many relationships cannot be represented directly with the relational model, you need a "cross reference" table to achieve this.

So, I wonder why took me so long to realize how to resolve this rather basic issue. I wish I could sleep a little more or a little better!

Postgresql WAL archiving / pg_archivecleanup

  • The thing with WAL archiving is as follows:

The postgresql WAL is always being generated but depending on our preferences - wal level - (archive, hot_standby, minimal )the quantity of logs that are kept at any particular moment varies; the minimal level is self explanatory: just a small number of logs are kept and those that are considered "old" (that is, that are behind a checkpoint or save) are recycled or renumbered to a higher value and then re-utilized. TODO:The archive level keeps up to max_wal_segments and I'm not sure how hot standby works in this particular aspect.

Though, there are some caveats and some precautions not to let the pg_xlog directory to grow indefinitely (i.e. using the pg_archivecleanup function). There's this interesting setting "archive_timeout" that will force the generation of a WAL segments in a constant interval regardless of whether the log is ready or not (if it's reached the 16mb requirement), nevertheless WAL files are always the same size (16mb), no matter what, so setting this parameter up with a low value will result in the bloating of your WAL storage much more rapidly.


pg_archivecleanup usage:

To configure a standby server to use pg_archivecleanup, put this into its recovery.conf configuration file:
archive_cleanup_command = 'pg_archivecleanup archivelocation %r'


One thing to have in mind is what happens when the pg_xlog directory runs out of space, should the situation arise,postgres will do a PANIC shutdown.


Monday, August 29, 2011

Postgresql - Streaming replication / Hot standby

  • Lately,I've been reading a lot about streaming replication. The concept is very straightforward and clear: one master sends WAL transactions to some slave(s) which in term receives and applies those transactions in an asynchronous fashion. With streaming replication you've got an exact backup from one postgresql cluster into an auxiliary server (no restrictions about ddl commands, large objects, etc). The difference with log shipping is that streaming replication works at WAL record level whilst log shipping sends entire WAL archives (16MB) at a time. The advantage with this approach is that in the case of failure, the stand by (slave) servers are behind just a few records (transactions), also in the documentation they explain that this kind of replication generates minimal overhead on the main (primary) server. The downside of this kind of replication is  that you don't have control over which objects are to be replicated. I need to think about situation where is behavior is unwanted, for the most part I don't really see it as an important disadvantage.

hot standby

  • The term refers to the ability of a slave node (wal receiver) to allow clients to perform read-only queries. This concept is important as the default settings of streaming replication won't allow you to  query slave servers.

Thursday, August 25, 2011

101 The Blocks Problem - Choosing data structures

The next step is choosing good data structures to deal with the problem.

  • When talking about putting blocks one on top of the other, comes natural to think in terms of stacks.

These stacks should be able to quickly tell whether a particular block is contained inside, for it is an illegal operation to make a movement of any kind if both a and b are piled within the same stack.

One advantage to this problem is having an upper bound of  n<= 25 blocks, so sequential searching is not an issue. I'm even tempted to use a n x n matrix of some sort.

It's late and I'm kind of tired, lets have some rest.

[sourcecode language="java"]

package blocks_problem;
import java.util.Scanner;

public class Main {
public static void main(String[] args) {
Scanner cin = new Scanner(System.in);
String line;
char a,b;
int numBlocks;
line = cin.nextLine();
numBlocks = Integer.parseInt(line);
while(cin.hasNextLine()) {
line = cin.nextLine();
String tokens[]  = line.split("\\s+");
a = tokens[1].charAt(0);
b = tokens[3].charAt(0);

else //tokens[2] == "over"

else // tokens[0] == "pile"

else //tokens[2] == "over"



pgpool-missing libpcp.so.0

looking for a shared library that wasn't there (pcp_node_recovery was complaining about), I figured out it wasn't included in the pgpool package. Diving into the web, found the answer:


installing libpcap resolves the issue. The funny thing is that the solution was given by MHM, that is no longer with us. I don't miss him, but may the lord help him.



pgpool-II & the recovery.conf file

I spent some time scratching my head about the recovery process  based on archived logs. I followed this guide to the letter: pgpool.projects.postgresql.org/contrib_docs/pgpool-II_for_beginners.pdf   and although I had successfully performed it, I didn't know how the hell the postgresql server knew  that I wanted to do a database recovery. It turns out that if the startup process (postmaster) finds a recovery.conf file in the database cluster, it reads the recovery command contained inside it, performs the recovery, and then renames it to "recovery.done". I should say this is a rather peculiar recovery mechanism. =-D


Wednesday, August 24, 2011

101 The Blocks Problem

I'll dedicate some time from now on to solve programming problems using Java, as I've used only c++  in the past, and having a working knowledge of java is a must in the professional practice these days.


This is just the beginning as my time is very scarce lately:

[sourcecode language="java"]

package blocks_problem;
import java.util.Scanner;

public class Main {
public static void main(String[] args) {
Scanner cin = new Scanner(System.in);
String line;
int numBlocks;
line = cin.nextLine();
numBlocks = Integer.parseInt(line);
System.out.println("Our block world has "+numBlocks+" blocks");
while(cin.hasNextLine()) {
line = cin.nextLine();
String tokens[]  = line.split("\\s+");
for(int i=0; i<4; i++){



what's failback & failover / pgpool - failover mechanism explanation

failback & failover

I'm delving into more terminology regarding database replication and recovery.

Failover. Refers to the process of switching production to a backup facility (normally your recovery site)

Failback. Is the process of returning production to its original location

pgpool - failover mechanism explanation

I took this diagram from the Gerd Koening's tutorial pgpool-II for beginners


Replication configuration

  1. node 1 (master) and node 2 are working along, and suddenly node 2 breaks for some reason.

  2. pgpool through its health_check detects that node 2 is having issues and shuts it down (degenerates it) . In this particular example the failover_command and failback_command parameters both just generate a log file depicting what's happening (who's the new master and the old master). The parameter controlling this situation is the "fail_over_on_backend_error", in this case set to true, meaning that fail over will be triggered when the writing to a backend communication socket fails. In this configuration the node is removed from the cluster.

  3. Calling the pcp_recovery_node triggers our create_base_backup (first stage) script which transfers an initial backup of the master server to the slave that was previously offline.

  4. On the second stage of recovery the last transactions are switched to database log.

  5. And finally a call to the pgpool_remote_start  expands the base backup,applies the last changes registered through the database log (that is being store in the slave) and restarts the database instance in recovery mode so the last WAL's are replayed.

Still, I need to figure out some more details.

removing services on debian

removing services on debian

I installed postgresql 9.0 on debian squeeze  using a backport package that installs it as a service. The issue is that all the configuration files are spread around with this type of installation, and I like everything tidy up in a single directory because that makes my life easier when working with multiple postgresql clusters (data directories) when performing  tests on several postgresql environments.

Disabling services on debian is as easy as using any of the tools mentioned here:


from then on, you can start your postgresql database manually and specify the data directory  you wish to work with.

Tuesday, August 23, 2011

pgpool - online recovery / STONITH / non-replicated databases

Folowing up the pgpool-II_replication_for_beginners tutorial.

pgpool - online recovery

  • what is it for? for reattaching a node  that has been degenerated or to attach a new node.


  • the parameters backend_XYZ in pgpool.conf have been redefined and pgpool reloaded.

  • enable pgpool online recovery on every node by compiling and installing the pgpool-recovery.sql script into the template1 database.

  • the sample scripts (in /pgpool-II-2.1/sample) should be contained inside the folder pointed by $PGDATA.

tip. checkout the sample scripts provided in the installation tarball.


The term STONITH is an acronym for "Shoot the Other Node in the Head". This is a concept related to clusters that refers to the situation in which failover occurs, the backup server takes over the role of master and then the old master restarts. STONITH is the mechanism  to inform the old master that is no longer the primary.

pgpool non-replicated databases

So,  what happens if while connected to pgpool you try to alter a database in the master (the node where pgpool is running) that doesn't exist in the slaves (the other nodes) ? pgpool closes the connection and won't even let you to connect to a database that doesn't exist on the slaves.

Friday, August 19, 2011

where is my postgresql-data cluster ?

where is my postgresql-data cluster

  • If ever in doubt about where the your postgresql configuration files are, you can stick to these queries:


And this one is of particular relevance:
SELECT name, setting FROM pg_settings WHERE category = 'File Locations'

The thing here is that you can have any number of pg clusters and work alternatively with each one of them. For example, you can have several versions of a cluster for testing purposes , and in that case this is one way of making sure you're working with the right cluster.

Thursday, August 18, 2011

postgresql - pg_restore / system-level backups /

postgresql - pg_restore

  • Until now I've been using psql to restore database dumps. The shortcoming to this approach is that you lack of any control over which parts of the dump file are you interested in the restoring (such as a single or a set of  table, a schema in particular, data only and so on). Other advantage that caught my attention is that you can restore tar files directly using this tool.


system-level backups

Backing up your system by copying the data directory or other directory you've created a postgresql cluster in, is very unreliable, especially when you plan to restore such backup in another machine. The target machine must be identical (including the postgresql environment) to the origin in order for this to have any chances of succeeding. The best option is to use pg_dump / pg_dumpall.




pgpool - backend_weight / rsync gotcha / du command

pgpool - backend_weight

There are several instances in which a mailing list provides more information about the workings of some tool than the formal documentation. This is the case with the pgpool-II parameter called "backend_weight". We can prioritize server reads through this parameter - the more weight, the more reads (SELECT's) are made to a server.


This obviously applies to the load balancing case, I wonder whether is useful for some other configurations.

rsync gotcha

In order for the rsync utility to work it must be installed in both origin and destination. rsync is awesome for it backs up only those things that have change since an initial backup.

'du' unix command

If you ever are in need of finding out the weight of a directory, you'll use this command. The -h flag woks as in other related commands to get the size in a human readable format. If you don't specify a path it will return you the size of your working directory.


dbt5 / ifup - ifdown /PITR - WAL/ unaccent


dbt5 is a OLTP benchmarking tool for Postgresql.  http://wiki.postgresql.org/wiki/DBT-5 , benchmarking is my thing nowadays, I'll be testing this utility in a near future.

ifup & ifdown

I'm used to use ifconfig but if you're setting a custom configuration for a network card through the /etc/interfaces file, you'll need to use the ifup command so that custom configuration takes effect.



Point in time recovery is the capability of restoring a database from incremental backups. You have a base backup and by replaying the WAL logs, you can go forward from that point up to any particular moment in the future regarding the state of the database.


unaccent is a text search dictionary and as its names implies, it removes accents from words, so it's more straight forward to process text. (make comparison, lookups, classifications, and so on). I haven't used dictionaries in postgresql before so I will be fun to learn to use it. Well, I just found out there's a wrapper function with the same name, so there's no need to use the text search dictionary directly.


Wednesday, August 17, 2011

Passwordless ssh logins

  • You can log-in into another machine through ssh without being prompted for a password following the method depicted here:


Watch out your file permissions 0600 for ~/.ssh/authorized_keys and 0700 for ~/.ssh , otherwise it won't work. The key you add to ~/.ssh/authorized_keys. This passwordless login will work only for the specific user of which you added the public key into the remote server's authorized_keys file.


Though, there are security concerns, it works for testing purposes such as sending the postgresql WAL to a backup server. Once you've played with it a bit, you can think of strengthening the exchange mechanism.

Another alternative is to use the ssh-copy-id script, which I find very convenient.

Tuesday, August 16, 2011

Postgresql-WAL / inodes/ Disk Journaling System/ diskchecker /more on pgpool

Postgresql- WAL

The write ahead log, might be the solution I'm looking for to guarantee data integrity in a replication scenario.


Inodes are data structures that hold information about files except their name and data. The thing that startled me regarding this concept is that there's a fixed number of files that a unix system can handle, based on a set disk-percentage (one percent, need to find a good source related to this number) that the inodes can occupy. It could be the case that your server is stuffed with a bunch of small files, that have cause the inodes to run out and even though there might be disk space left, you won't be able to create more files. I don't know either what's the probability of this happening, but might be a fact worth taking into account...


Disk Journaling System

DJS is very similar to the WAL concept reviewed on this post. Changes are applied first to a log or journal and then commited to the main file system, so in case of a failure the system can be brought back to operation with virtually no loss of data.

Having enabled the postgresql WAL inside a system that uses disk journaling can decrease performance. We can disable journaling on a file system through mount options. The advantage of using journaled file systems is that they improve boot speed after a crash.


Disk checker

There's an utility to measure what is the rate of errors once a disk failure occurs. The concern here is that most consumer and some SCSI drives come with the hardware write-caching option enabled, which results on risk of loosing data when a power outage, or unexpected failure takes place.


More on Pgpool

There's a really interesting article here:


Another advantage of using pgpool is that you can achieve an authentic "automatic failover" as all nodes are in perfect sync  no transactions are left out. (Well it also depends of the configuration you're using)

Pg-pool-II Introduction / Review of replication concepts

Now I'll be exploring yet another database replication solution oriented to Postgresql.

Pg-pool-II Introduction


  • Not only does replication but also connection pooling, load  balancing and query parallelization.

  • Supports multimaster configurations.http://www.mail-archive.com/pgpool-general@pgfoundry.org/msg02512.html

  • Its replication method is synchronous (commits are granted once all nodes have replicated the transaction) .

  • It works along with Postgresql streaming replication.


  • All the postgresql instances must be of the same major version.

  • Not advisable to use different OS and / or hardware architectures, especially if you intent to use online recovery.

  • Doesn't work with Windows

Review of replication concepts

One issue that RAF pointed me out (yet again, as he usually does)  is that synchronous replication in the proposed terms might impose a considerable extra load over transactions.If we have several nodes that are replicating data each one of them with a particular network delay, a transaction could take much time to commit. There's also a risk of our transactions failing constantly due to this overhead if our cluster architecture is not  properly planned.

Monday, August 15, 2011

Slony-I trigger handling

  • Triggers and functions are one aspect of database replication that I did not take into account when deliberating about database replication. I was focused solely on data,  but RAF today pointed this important part out. So disabling and enabling triggers on Postgresql is as straight forward as this:


From the documentation:

These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed. The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers will fire when the replication role is "origin" (the default) or "local". Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.

The concern about triggers and replication is that they are bound to cause fuzz (i.e. data duplication) if enabled in both master and replicas. Slony-I takes advantage of postgresql trigger behavior so triggers within replicas are not fired upon.

  • In Postgresql >= 8.3 ,there's a session variable called "session_replication_role" that in conjunction with the enable/disable trigger characteristic handles trigger management in a rather straight forward fashion.

One thing that caught my attention is that supposedly, if you delete the slony schema created during the cluster initialization,  you leave your database as if slony-I were never there. I'm not sure how to test this as I'm not so acquainted with triggers "under the hood", but I'll figure that out when SCC teach us about that particular matter.
I would love to experiment with Postgresql 9.0 >= native replication once I've gotten a better grasp of replication in general.

Slony failure test cases / Stopping the postgresql service kindly/ non-empty tables

Slony node failure test cases

I'll be testing failure scenarios within the slony cluster operation.


1 master node, and 2 slave nodes subscribed to the master node.

+Case 1. Master node goes off.

  • Let's stop the postgresql service running in  the master node.

Stopping the postgresql service kindly

Beware, as there are active connections being attended by the server, it won't stop itself issuing a simple pg_ctl stop. You are advice to use some particular built in shutdown mode instead of simply killing the postgres process as this could result in unexpected loss of data or some sort of corruption of postgres.



  • The slon daemon enters sleep mode whilst  waiting for the master server to be available again.

Bringing the master node to life again, restores the cluster's normal operation.

+Case 2. One slave node goes off.

The possible question here is whether everything else is still working.

Issuing a simple update in the master node:

update pgbench_history set tid = 15 where id > 1400000;

#there where 100000 rows affected by this update

and now querying "select count(*) from pgbench_history where tid = 15;" gives us the expected result.

+Case 2a. Bringing the broken slave node back to life.

If I start the postgres service and the slon daemon again in the node that was purposely "shutdown" it starts to catch up 'till is  properly synchronized.

The issue here is: what's the maximum lapse within of which you are able to re-attach a "broken" node so It can catch up with the missing sync packages. I mean, as far as I understand, the synchronization occurs thanks to SYNC packages which are stored...(where the hell are they stored?). Well I need to figure that out in order to know  if the new attached node can update it's state no matter how long ago was left behind, or if it's more convenient to recreate the  tables involved in the replication set from the ground up or truncate them.

Gotcha: non-empty tables when subscribing nodes

  • When subscribing to a replication set you better make sure that the tables involved in the set are empty, otherwise the initial replication can consume more time than it should, specially if they're big.

Friday, August 12, 2011

Pacemaker / Perl : GetOptions


Pacemaker is a resource manager for clusters. It keeps track of which node services are working  and executes corrective actions for those that fail, and makes sure the application using those services is available. It will be interesting to find out under which criteria does it consider a service or node to be broken ( network timeouts?).

[caption id="attachment_400" align="aligncenter" width="300" caption="Introduction to pacemaker"][/caption]


GetOptions is a perl module that parses command line options you pass to a perl script. I found out about it while trying to make a perl script work. For instance, dissecting a perl script I'm dealing with:

[sourcecode language="perl"]
("H|help" => \$help,
"d|database=s" => \$database,
"h|host=s" => \$host,
"u|user=s" => \$user,
"c|cluster=s" => \$cluster,
"p|password=s" => \$password,
"P|port=i" => \$port,
"m|mailprog=s" => \$mailprog,
"f|finalquery=s" => \$finalquery,
"r|recipient=s" => \$recipient);


we see that in this particular script, we could specify flags such as -d databaseName, -h localhost and so on.

Wednesday, August 10, 2011

RELAIS -Moprosoft -MPS

Puesto que el contexto del tema es aplicable de manera muy particular a México, este será un post en español =-).

  • Hoy asistí junto con mis compañeros de trabajo a la presentación del proyecto RELAIS en la sede de CANACINTRA.

  • Entre otras cosas  el proyecto implica el fomento a la competitividad de procesos de calidad en la industria de software en latinoamérica. Los paises participantes en esta etapa son Perú, Colombia, Brazil y México. Se pretende crear cadenas integradoras de generación de valor, apoyo y colaboración mediante proyectos de capacitación e implementación de Modelos de Procesos de Calidad de Software  financiados por el FOMIN (Fondo Multilaterial de Inversiones).

  • El proyecto se encuentra en una fase muy incipiente y está tratando de hacerse un empate entre los modelos: MOPROSOFT de México y MPS Br. de Brazil. Por lo que entendí MPS es un modelo muy completo ya que involucra una serie de niveles que hace que su implantación sea más sencillas para pequeñas y medianas empresas. Lo que me llamó la atención es que primero de impulsarán proyectos para que en México se capacite en MPS y en Brazil en MOPROSOFT. Yo supongo que va a haber mucha confusión al estar usando ambos modelos; se está llevando a cabo una labor  por parte de la gente involucrada en la creación de MOPROSOFT para  mapearlo con MPS de modo que se minimicen los posibles conflictos entre ellos.

  • Comencé a revisar los conceptos de MOPROSOFT y estoy trabajando en un pequeño mapa mental (en proceso).

[caption id="attachment_389" align="aligncenter" width="300" caption="Moprosoft Conceptos Básicos"][/caption]

Algo que también salió a colación de la posible participación de las universidades como organizaciones implementadoras fue la inquietud de gente del IPN respecto a que casi en ninguna universidad ya sea pública o privada nos hablan de procesos. Yo recuerdo que alguna vez nos dejaron un trabajo de investigación en la UAM, en la materia de Metodologías de Analisis y Diseño de Sistemas de Información, pero no se le dió mucha trascendencia.

Ahh que caray... no puedo dormir, seguiré divagando sobre cuestiones filosóficas =-D.

Tuesday, August 9, 2011

What's High Availability?

My topic of study these last weeks has been about implementing database replication and high availability. I've made several advances into the former and read about the later. I'll be reviewing some of my findings on the later in this post.

Let's define what we mean by "High Availability"

  • Is a system design approach and associated service implementation that ensures a prearranged level of operational performance will be met during a contractual measurement period.

From this definition stands out the keywords "to ensure", "prearranged or contractual" and "operational performance" "period",

  • In information technology, high availability refers to a system or component that is continuously operational for a desirably long length of time.


From this one: "continuously operational for a desirably long length of time"

This definition from symantec is very interesting:

  • High availability, as a general rule, is a configuration of hardware and software that allows for monitoring of services provided by a system and for the transfer of those services to another system in the case of a failure - including catastrophic, complete system failures.




Symantec detracts the common belief that high availability is necessarily "always available", and advocates for defining it as a set of monitoring techniques, and some sort of the overlapping between fault tolerance and disaster recovery.

Having compared these concepts I could embrace the following hybrid:

High availability is the contractual responsibility of ensuring a prearranged and continuous level of performance of a system within a defined period, by monitoring it and using redundancy and failover techniques transparent to the user.

Now, walking on more solid grounds lets explore some other concepts from the oracle docs:

  • Types of  high availability solutions:

Local: provide high availability in a single data center deployment

Disaster recovery solutions. Are usually geographically distributed deployments that protect your applications from disasters such as floods or regional network outages.

  • The most important mechanism for high availability is redundancy. Redundancy determines the categorization of local high availability solutions.

  • Categorization or levels of redundancy:

+ active-active solutions.Deployment of two or more active system instances and ca be used to improve scalability as well provide high availability. All instances handle requests concurrently.

+ active-passive solutions. Deployment of an active instance that handles requests and a passive instance that is on standby. In addition, a heartbeat mechanism is set up between these two instances. The monitoring tool (heartbeat mechanism) takes care of performing a failover procedure when appropriate. Active-passive solutions are also generally referred to as cold failover clusters.


There's a sea of terminology regarding this interesting topic, but there will be more in the following days =-D.





Monday, August 8, 2011

Static IP on debian / What is my DNS?/ Network Time Protocol (ntp) / watch

  • In opensuse is pretty straight forward using the Network Manager module. In debian  you only need to tweak your /etc/network/interfaces file. For example:

[sourcecode language="bash"]
# /etc/network/interfaces -- configuration file for ifup(8), ifdown(8)

# The loopback interface
auto lo
iface lo inet loopback

# The first network card - this entry was created during the Debian installation
# (network, broadcast and gateway are optional)
auto eth0

iface eth0 inet static

of course, you'll need to restart your networking service afterwards.



  • In order to know what your DNS is, just cat the content of the /etc/resolv.conf file.


  • network time protocol aka 'ntp' . Is an essential requirement for running a cluster without glitches, so the nodes are in agreement about the current time. Configuring a ntp server in opensuse is as easy as it gets, but I needed to get acquainted with a general approach, and found this nifty guide:


and although redundant, here's some information to configure it in opensuse.


One thing that caught my attention is that there's about +-1 second offset between server/clients. It seems that it gets more accurate in time. Some tutorials advise using ntpdate, but this is no longer encouraged as  ntpd -q, is a more appropriate way of doing an initial synchronization.

  • watch. As I was connecting to a remote system running the ntp client, I needed to get a hold of that system's hour in a basis of seconds in order to compare the exact hour between nodes. So I asked the linux guru, and this is what I got:

watch -n 1 'date'

simple,elegant,and did the trick.


Sunday, August 7, 2011

checkinstall / fakeroot / hierarchies with sql /

  • checkinstall is a pretty awesome linux utility that helps you create deb and rpm packages from a program's source code.

The main reason to use it is that in many instances there's no such thing as a binary package for your linux distribution, so you go ahead, compile the program and install it. But now, how the hell do you uninstall it if there's not an uninstall or some rule like that defined on the makefile?


  • fakeroot is yet another utility that creates a fake root environment in order to generate deb packages, as there are many restrictions on this regard to unprivileged users. I ignore whether there are more usages, I'll look it up later.

  • representing categories / subcategories relationships using plain sql can be a challenging problem. We have mainly two alternatives the adjacency list and nested set models. There's a nice introduction here : http://pugs.postgresql.org/files/ModelingTrees.pdf . I'll be studying and implementing them in the following days, just for the sake of it (and as I cannot sleep properly lately =-) , well, I never do).

Peace and love!