Thread: inheriting a rule or a trigger?

Hi,

I'm trying to protect created_by and created_on columns from accidental 
update. Most of my tables inherit from a 'source' table that has those 
columns, so I was thinking of creating a rule or trigger that does 
nothing on update to these columns. But apparently rules and triggers 
don't apply to child tables.

Is there another way to have the same effect, short of a rule/trigger on 
each table?

Hi,

I have two postgres databases "Db1" and "Db2" on different linux servers.
"DB1" is my master server and its data updates very frequently.

My application access DB2 database, which in turn requires data from Db1
database. 

The same can be achieved in either of the following ways:
1. I can have the same tables from Db1 in the Db2 database and use some
replication process to update these tables say using Slony-I. And then our
application accesses the data from the single database.

2. Or I can use dblink to fetch the data from the master database only.

My problem is my master database changes frequently and I always require the
latest information.

Please suggest which is the right approach in this situation.

Thanks,
Jyoti Seth

Try not to start a new thread (question) by replying to an old one - it 
can mean some people don't see your question.

Jyoti Seth wrote:
> Hi,
> 
> I have two postgres databases "Db1" and "Db2" on different linux servers.
> "DB1" is my master server and its data updates very frequently.
> 
> My application access DB2 database, which in turn requires data from Db1
> database. 

OK

> The same can be achieved in either of the following ways:
> 1. I can have the same tables from Db1 in the Db2 database and use some
> replication process to update these tables say using Slony-I. And then our
> application accesses the data from the single database.

Yes

> 2. Or I can use dblink to fetch the data from the master database only.

Yes

also:

3. Write a layer in your application / between the application & 
database which routes queries to the correct database.

> My problem is my master database changes frequently and I always require the
> latest information.

Well, if by "latest" you mean you can't have any delay at all you'll 
have to use #2 or #3.

However, that could be slow if you need to join a lot of data from DB1 
to DB2. Can you provide more details of what each contains?

--   Richard Huxton  Archonet Ltd

My master database contains prices of some products that vary after few
hours and many other details that are also reqd in Db2 which doesn't change
that frequently. My slave database contains the details of queries sent by
the customers.
These queries are to find the price of the product and other details. (Some
part of the info reqd from Db1 varies frequently where some information is a
kind of master data that doesn't vary frequently).

So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
for other details or there is some other better option

Thanks,
Jyoti

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com] 
Sent: Wednesday, July 25, 2007 3:54 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database synchronization

Try not to start a new thread (question) by replying to an old one - it 
can mean some people don't see your question.

I will take care in future.

Jyoti Seth wrote:
> Hi,
> 
> I have two postgres databases "Db1" and "Db2" on different linux servers.
> "DB1" is my master server and its data updates very frequently.
> 
> My application access DB2 database, which in turn requires data from Db1
> database. 

OK

> The same can be achieved in either of the following ways:
> 1. I can have the same tables from Db1 in the Db2 database and use some
> replication process to update these tables say using Slony-I. And then our
> application accesses the data from the single database.

Yes

> 2. Or I can use dblink to fetch the data from the master database only.

Yes

also:

3. Write a layer in your application / between the application & 
database which routes queries to the correct database.

> My problem is my master database changes frequently and I always require
the
> latest information.

Well, if by "latest" you mean you can't have any delay at all you'll 
have to use #2 or #3.

However, that could be slow if you need to join a lot of data from DB1 
to DB2. Can you provide more details of what each contains?

--   Richard Huxton  Archonet Ltd

Jyoti Seth wrote:
> My master database contains prices of some products that vary after few
> hours and many other details that are also reqd in Db2 which doesn't change
> that frequently. My slave database contains the details of queries sent by
> the customers.
> These queries are to find the price of the product and other details. (Some
> part of the info reqd from Db1 varies frequently where some information is a
> kind of master data that doesn't vary frequently).
> 
> So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
> for other details or there is some other better option

If you can live with a time-lag measured in seconds, then slony will 
work just fine. If you can't then you'll want dblink.

One other thing you might consider - if you attach "valid_from" time to 
each price then you can put them in place in advance, and make sure they 
are replicated before needed.

--   Richard Huxton  Archonet Ltd

Thanks a lot for your suggestion. It will help me in taking the decision.

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com] 
Sent: Wednesday, July 25, 2007 6:10 PM
To: Jyoti Seth
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Database synchronization

Jyoti Seth wrote:
> My master database contains prices of some products that vary after few
> hours and many other details that are also reqd in Db2 which doesn't
change
> that frequently. My slave database contains the details of queries sent by
> the customers.
> These queries are to find the price of the product and other details.
(Some
> part of the info reqd from Db1 varies frequently where some information is
a
> kind of master data that doesn't vary frequently).
> 
> So should I use all the ways i.e #2 or #3 to get price info and #1 (slony)
> for other details or there is some other better option

If you can live with a time-lag measured in seconds, then slony will 
work just fine. If you can't then you'll want dblink.

One other thing you might consider - if you attach "valid_from" time to 
each price then you can put them in place in advance, and make sure they 
are replicated before needed.

--   Richard Huxton  Archonet Ltd

Jyoti Seth wrote:
> Hello,
> 
> I have one query related to slony-I. Can I replicate table data in different
> schemas. Say if have table 'country' in database 'stock' and schema
> 'stocksch'. And I want to replicate the data in table 'country' of database
> 'smsdb' and schema 'smssch'. Here, the country table is present in two
> different schemas. Can I replicate the country table data from schema
> stocksch to schema smssch or I have to create a schema with the same name
> for replication.

I don't think slony lets you rename tables/schemas during transfer. I'm 
not sure many people want it, and it would be a complicated thing to do 
properly in the general case (you'll need to consider dependant objects 
- FKeys, views, functions etc).


--   Richard Huxton  Archonet Ltd

On Tue, Jul 31, 2007 at 09:22:04AM +0100, Richard Huxton wrote:
> I don't think slony lets you rename tables/schemas during transfer. I'm 
> not sure many people want it, and it would be a complicated thing to do 
> properly in the general case (you'll need to consider dependant objects 
> - FKeys, views, functions etc).

It doesn't allow this, but you could cause it to happen anyway on the
replica using a trigger.  You have to use STORE TRIGGER to make this
work.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.    --George Orwell

* Jyoti Seth <jyotiseth2001@gmail.com> wrote:

Hi,

if you can live with some seconds lag, you can use an separate
transfer process which looks at the source of your prices and 
and updates the second DB if some price changes. 

Depending on how often prices change, there're two good options:

a) add an timestamp field to the original price table, which is   maintained by an after-trigger. The transfer
regularily  (ie. every few secs) fetches all those records which have   been changed since last poll (via the new
timestampfield).
 

b) create an new table for an journal of the price changes.  this journal is filled by rules on the original table and
contains exactly what's needed to reproduce the price changes  in the other DB. The transfer process regularily fetches
the journal and rolls it out in the second DB.  
 
I've implemented the method b) in an realtime stock (FOREX) 
trading applications where trading automatically robots execute 
transactions on the customer's broker accounts on signals 
provided by an team of profession traders. (the client can 
subscribe to several trading signal channels and define in 
which quantities transactions should be performed from which
channels should be executed on their broker accounts and the
broking platform does all the time critical work). The robots
work entirely on separate databases (on separate hosts),
several transfer processes feed in robot commands and fetch
back results to the primary database. So time critical things
are separated to own servers. It works quite fine :)


cu
-- 
---------------------------------------------------------------------Enrico Weigelt    ==   metux IT service -
http://www.metux.de/
---------------------------------------------------------------------Please visit the OpenSource QM Taskforce:
http://wiki.metux.de/public/OpenSource_QM_TaskforcePatches/ Fixes for a lot dozens of packages in dozens of
versions:http://patches.metux.de/
---------------------------------------------------------------------