Hi,
I am wondering how I would go about updating 2 database's in one go,
safely.

The databases are in different locations and so the network is a point of
failure.

I was hoping I could do it in a single transaction block? is this
possible. If not can anyone suggest any alternatives? I also had thought
about triggers but don't really know enough about them at present :-).

Thanks for any help.
Cheers
Chris

--

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.
Errors and omissions excepted. Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of edNET or lightershade ltd. Finally, the
recipient should check this email and any attachments for the presence of
viruses. edNET and lightershade ltd accepts no liability for any damage
caused by any virus transmitted by this email.

--

--
Virus scanned by edNET.

Search Discussions

  • Josh Berkus at Jan 30, 2002 at 4:56 pm
    Chris,
    I am wondering how I would go about updating 2 database's in one go,
    safely.

    The databases are in different locations and so the network is a
    point of
    failure.

    I was hoping I could do it in a single transaction block? is this
    possible. If not can anyone suggest any alternatives? I also had
    thought
    about triggers but don't really know enough about them at present
    :-).
    You cannot do this though SQL, which, due to the SQL92 spec, is limited
    to one database only. You will have to involve another programming
    language.

    If this multi-database interaction is crucial to your application, then
    you will need to implement a full middleware layer to stand between
    the interface and the databases. Such a layer would best be developed
    in Java or Perl::DBI, but you can do COM or .NET if you're a glutton
    for punishment :-) For Java and Perl, there are several application
    development frameworks, such as Enhydra, that will help you do this.

    With a middleware layer, then, you can do the following in your
    middleware code:
    1. Start the update on database #1 without committing;
    2. Start the update on database #2 without committing;
    3. Test for failure. If either fails, rollback.
    4. If both succeed, commit both.

    For widely dispersed database servers (e.g. one in California, one in
    New Zealand) you'd need to take into account the slow nature of the
    connection and use messaging protocols and a 2-phase commit strategy
    rather than holding the transaction open as above.

    -Josh Berkus

    ______AGLIO DATABASE SOLUTIONS___________________________
    Josh Berkus
    Complete information technology josh@agliodbs.com
    and data management solutions (415) 565-7293
    for law firms, small businesses fax 621-2533
    and non-profit organizations. San Francisco
  • Torbjörn Andersson at Jan 30, 2002 at 5:00 pm
    02-01-30 10.21 Chris Thompson thompson@lightershade.com
    I was hoping I could do it in a single transaction block? is this
    possible.
    Everything between BEGIN and END is rolled back unless all actions in the
    block are completed succesfully. AFAIK that is the definition of a
    transaction.

    Apart from that I'm not sure how to connect to a database inside a block,
    let alone how to switch connections.
    If not can anyone suggest any alternatives? I also had thought
    about triggers but don't really know enough about them at present :-).
    How immportant is it that both databases are sychronised? Probably you
    should look into replication instead. I think there is an Open Source
    project regarding replicating Postgres.


    Regards,

    Vänliga hälsningar

    Torbjörn Andersson
    ---------------------------------------------------
    Embryo Communication phone: +46 (0) 31-774 39 11(00)
    Kungsgatan 7a fax: +46 (0)31 774 07 80
    S-411 19 Göteborg mobile: 0708-30 70 04
    Sweden home: http://www.embryo.se/
    mail: torbjorn.andersson@embryo.se
    ---------------------------------------------------
    "Att idag tänka annorlunda än igår skiljer den vise från den envise." — John
    Steinbeck

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 30, '02 at 9:21a
activeJan 30, '02 at 5:00p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase