Re: plpgsql: trigger insert new into other table (archive)

On 10/09/2012 09:55 AM, Matthijs Möhlmann wrote:

> Now we need to implement that all insert and update queries should be
> replicated to the some_archive database. Well, the question is how to
> do that?

Whatever you do, please don't roll your own. This is a solved problem.
If you plan on doing this kind of replication, please look into Slony,
Bucardo, or Londiste. They're all trigger-based replication systems that
have been in production use by several companies for years now. You're
much better off using them than cobbling something together yourself.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

From pgsql-general-owner(at)postgresql(dot)org Tue Oct 9 18:49:26 2012
Received: from makus.postgresql.org ([98.129.198.125])
by malur.postgresql.org with esmtp (Exim 4.72)
(envelope-from <pierce(at)hogranch(dot)com>)
id 1TLert-0000m4-VB
for pgsql-general(at)postgresql(dot)org; Tue, 09 Oct 2012 18:49:26 +0000
Received: from hogranch.com ([75.101.82.47])
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <pierce(at)hogranch(dot)com>)
id 1TLers-0006xi-LZ
for pgsql-general(at)postgresql(dot)org; Tue, 09 Oct 2012 18:49:25 +0000
Received: from [192.168.0.2] (porker [192.168.0.2])
by hogranch.com (8.11.6/8.11.6) with ESMTP id q99InG919740
for <pgsql-general(at)postgresql(dot)org>; Tue, 9 Oct 2012 11:49:16 -0700
Message-ID: <507471A7(dot)7080102(at)hogranch(dot)com>
Date: Tue, 09 Oct 2012 11:49:11 -0700
From: John R Pierce <pierce(at)hogranch(dot)com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: pgsql-general(at)postgresql(dot)org
Subject: Re: pgxs problem...
References: <5073CB2B(dot)2070802(at)hogranch(dot)com> <10676(dot)1349790723(at)sss(dot)pgh(dot)pa(dot)us>
In-Reply-To: <10676(dot)1349790723(at)sss(dot)pgh(dot)pa(dot)us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-HawgScanner-Information: Please contact the ISP for more information
X-HawgScanner: Found to be clean
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/269
X-Sequence-Number: 191237

On 10/09/12 6:52 AM, Tom Lane wrote:
> John R Pierce<pierce(at)hogranch(dot)com> writes:
>> >I'm trying to build something (pljava, dont laugh) for IBM AIX (don't
>> >laugh). I have my own build of postgresql 9.1.6 on AIX, compiled with
>> >IBM XLC, it works quite nicely, but this build is bombing..
>> >/opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh
>> >libpljava.a >libpljava.exp
>> >/bin/sh:
>> >/opt/pgsql91/lib/pgxs/src/makefiles/../../src/backend/port/aix/mkldexport.sh:
>> >not found.
> Hm. There is a src/backend/port/aix/mkldexport.sh in the source tree,
> and I see that Makefile.shlib relies on it, but I don't see any evidence
> that we make any effort to install it. For the moment you could just
> copy it over to the pgxs tree ... but if we wanted to support AIX a bit
> better it seems like we ought to do that automatically.

mm, k. so I manually copied that file over, and now get...

....
/usr/vacpp/bin/xlc -q64 -I/home/postgres/src/include -O2 -qarch=pwr5
-qtuneºlanced -qnoansialias -o pljava.so libpljava.a
-Wl,-bE:libpljava.exp -L/opt/pgsql91/lib -L/home/postgres/src/lib
-Wl,-blibpath:'/opt/pgsql91/lib:/opt/pgsql91/lib:/home/postgres/src/lib:/usr/lib:/lib'
-Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
-Wl,-bI:/opt/pgsql91/bin/postgres/postgres.imp -L.
-L"/usr/java5_64/jre/bin/j9vm" -ljvm
ld: 0706-003 Cannot find or read import file:
/opt/pgsql91/bin/postgres/postgres.imp
ld:accessx(): A parameter must be a directory.

so thats /another/ missing file... hmmm, a file of that name is in lib,
not bin/postgres ...

$ find . -name postgres.imp
./src/backend/postgres.imp
./src/test/regress/tmp_check/install/opt/pgsql91/lib/postgres.imp

$ find /opt/pgsql91 -name postgres.imp
/opt/pgsql91/lib/postgres.imp

and, muh-oh... $PG_PREFIX/bin/postgres is the binary executable, so I
*can't* put a copy of postgres.imp there, hah. and a bit of poking
around the pljava makefiles, I'm not figuring out how its cooking that
path name up?!?

--
john r pierce N 37, W 122
santa cruz ca mid-left coast