PostgreSQL – How to Connect Two Databases in Different Locations

Streaming replication fires the triggers on the master only. The slaves never see the actual table writes, but rather re-run the recovery logs from the master. Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level. No triggers are necessary. Triggers may be possible with Slony or Bucardo but I am not 100% sure. My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony. In 9.2 streaming replication will allow for cascading.

Hope this helps.

CREATE - When a Person are inserted in the database the next insert will be based on Person.type (L => Legal or N => Natural) that defines if will create a new record on LegalEntity or NaturalPerson with Person last inserted id as the foreign key.

Do all inserts for each "person" in a single transaction based on updatable views, not on base tables. Every dbms exposes a function that will return the last inserted ID number. (For PostgreSQL, this SO answer.)

READ - To provide a consistent data, we use JOINS with aditional term "AND T.type" for LegalEntity and NaturalPerson to return only relative data to Person.type FLAG. For eg. if Person it's a LegalEntity than we have EIN (Employer Identification Number or Federal Tax Id), if not, it's a NaturalPerson than have SSN (Security Social Number)

In the query you posted, you don't do that at all. Instead, you retrieve all the data for every type.

Instead, use two views (see above), one for legal persons and one for natural persons. Resist the urge to join every possible table in the views.

UPDATE - When you perform an update is needed to test whether there is specialization. This is because a record could originally have been created as type = N (Natural) and later want to upgrade it to Corporate (L = Legal). An INSERT to create original NaturalPerson was made, and now with the exchange of nature type is necessary to exclude logically or physically the NaturalPerson record and then create the new record of LegalEntity.

In the real world, you don't "upgrade" a natural person to be a corporation. (A person who "is" a corporation is legally two different things. That's why there are corporations.) In a database, if you run into such a situation, you have to take one of three paths.

• All the original data is wrong, because you entered data for a person who is not really a person. Delete the bad data, and enter new data for the corporation.
• All the original data is right; that person is actually a natural person. Reject all the updates that would make that natural person a corporation.
• All the original data is right, and that person is both a natural person and a corporation. Redesign your schema to accommodate this possibility.