[Pgpool-general] Replication Flawed?

[Jim C. Nasby]

On Mon, May 22, 2006 at 12:13:42PM +0200, Robert Ayres wrote:
> !-- DIV {margin:0px;}-->> >And how else could you do it
> >> and ensure data consistency?
> 
> >There could be many ways. One of them is table locking as I suggested.
> 
> After consideration you are indeed correct that locking a table before executing a query can produce consistent data.  However I am not too favourable to shifting some of the responsibility of a HA solution upon the application itself.  For me, whatever solution is offered should be transparent to the application otherwise you are only going to run into further problems by splitting this responsibility.
> 
> The idea of LOCK TABLES has other drawbacks:
> 
> *) Not an SQL standard, therefore some interpretation is required by the application of the  database underneath e.g. 'LOCK TABLE t WRITE' (MySQL) or 'LOCK TABLE films IN EXCLUSIVE MODE' (Postgres)

Actually, I think it is a standard, since every other database I've
worked with follows the same syntax.

> *) Isn't table level locking going to deny you the extra concurrency of row-level locking?  Although perhaps you could argue the ordering of statements e.g. Sequoia would have the same effect on performance

The DDL situation is a major drawback for anyone wanting to do table
partitioning. But in the case of partitioning, you could probably get
away without wrapping it in SYNCs, provided that there was enough delay
between creating or dropping a partition and commands that use that
partition.

BTW, does the OP have partitioning scripts that they can post somewhere?
Right now everyone has to roll their own AFAIK; having at least an
example would be handy.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461