Re: Remote queries

From: "Chuck Shunk" <cshunk@shentel.net>

RE: [GENERAL] FOREIGN KEYHello everybody,

>This is my first time posting to this list, so forgive me if my question
seems dumb or >ill informed.


Question is clear, succinct and complete :-)
Email is in HTML rather than raw text :-(

>    INSERT INTO remote_sales_table VALUES
>        (SELECT sales_info, local_terminal_id FROM local_sales_table WHERE
uploaded =
>'n')

>However, as the two tables reside in two separate databases, that won't
work, will it?
>So, my question is, what is the best way to synchronize a master database
with multiple
>client databases?  Right now, I have a c++ routine that does the local
select to get
>all the sales data and then steps through each row and builds an insert for
the master
>server.  Is there a more elegant solution?

Not really - I had to do something similar recently and came up with a
similar solution. One thing that seemed to work for me was to have a
separate import_xxx_table along with my xxx_table. The external code
concentrated on checking the transport of the data and just stuffed it into
import_xxx_table. It then called a plpgsql function that did all the actual
work of importing/updating. There was the mirror image for the export
functionality.

This kept my interfaces nice and clean and kept all my data-munging inside
the database. The downside was plpgsql's error reporting leaves a little to
be desired, so debugging was a pain. If repeating the project, I would build
the system with a noddy setup and add the detail in step by step.

>If there is not a better solution already existant, I am going to build
some generic
>procedures and objects in c++ for synchronizing tables of this sort.  Is
anyone else
>interested in a project like this?  I'll have a project up on sourceforge
in a bit.

Check the "contrib" folder of the source distribution and have a look at the
replication code. Actually, I think there are two or three different
replication porjects out there, so perhaps have a rummage on google.

>By the way, this POS terminal project I'm talking about is already up on
sourceforge
>(it's called poskiosk), but please only look at the code if you're really
interested--I
>would be terribly embarrassed!  (It's kind of pathetic at this stage)

Any code is a start, and most of the people throwing rocks never write a
line of it.

- Richard Huxton