Re: [SQL] Database synchronization
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. Thanks, Jyoti Seth On 7/26/07, Jyoti Seth <[EMAIL PROTECTED]> wrote: > > Thanks a lot for your suggestion. It will help me in taking the decision. > > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > 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 > >
Re: [SQL] Database synchronization
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] calling webservice through postgresql function
Good day, Jyoti.
JS>> (e.g. pl/perlu).
DT> __You as man (by hands) install connect__ between DBMS and external world
DT> by procedural language once again.
DT> You are wrong once again:
DT> advantage is to __refuse__ from procedural language (to install connect)
DT> instead of put it from outside of DBMS into inside of DBMS.
DT> All, which is necessary, is DBMS itself listen some port/protocol.
My version:
(1.1)
User send to remote database (HTTP will ask login):
c:/dir> xml2http.exe database.my.com username password a.xml
for example
c:/dir> dbf2xml.exe a.dbf | xml2http.exe database.my.com username password
(1.2)
User send to local database (at transformation data between DBMS-s):
c:/dir> xml2http.exe 127.0.0.1 username password a.xml
for example
c:/dir> dbf2xml.exe a.dbf | xml2http.exe 127.0.0.1 username password
(2.1)
Database send to local file (at transformation data between DBMS-s):
pg> copy a.b.c to filename.xml;
(2.2)
Database send to remote database
(if 'sys.username' and 'sys.password' are equal null,
then database sends as anonymous):
pg> update sys set
pg>ral="database.remote.com",
pg>username=
pg>password= ;
pg> a.b.c ->;
or
pg> update sys set
pg>ran="101.102.103.104",
pg>username=
pg>password= ;
pg> a.b.c ->;
or if you prefer to export per table
pg> update sys set ral="database.remote.com";
pg> create view av as select * from a where ...
pg> create view bv as select * from b where ...
pg> create view cv as select * from c where ...
pg> av ->;
pg> bv ->;
pg> cv ->;
Once again: users need simple instruments, instead of your (in you link)
JS>>CREATE OR REPLACE FUNCTION
JS>>use DBI;
JS>>my $dbh = DBI->connect('dbi:mysql:'.$_[0],$_[1],$_[2],
JS>> { RaiseError => 1, AutoCommit = > });
Do you understand me ?
Dmitry Turin
SQL4 (4.1.3) http://sql40.chat.ru
HTML6 (6.3.0) http://html6.by.ru
Unicode2 (2.0.1) http://unicode2.chat.ru
Computer2 (2.0.3) http://computer20.chat.ru
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Database synchronization
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 | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Alternative to INTERSECT
Hi all. I have the following schema:
CREATE TABLE test (
id integer NOT NULL,
field character varying NOT NULL,
value character varying NOT NULL
);
ALTER TABLE ONLY test
ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
CREATE INDEX test_like_idx ON test USING btree (id, field, value
varchar_pattern_ops);
Using INTERSECT I want to retrieve the rows matching (pseudo-code) "firstname
LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
on=> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'kro%';
id
1
(1 row)
Is there a way to make this more efficient with another construct, or
INTERSECT the only way to accomplish the desired result?
--
Andreas Joseph Krogh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to INTERSECT
On Tue, 31 Jul 2007 17:30:51 +
Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> Hi all. I have the following schema:
>
> CREATE TABLE test (
> id integer NOT NULL,
> field character varying NOT NULL,
> value character varying NOT NULL
> );
>
> ALTER TABLE ONLY test
> ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
>
> CREATE INDEX test_like_idx ON test USING btree (id, field, value
> varchar_pattern_ops);
>
> Using INTERSECT I want to retrieve the rows matching (pseudo-code)
> "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
Why not:
WHERE (t.field = lastname AND t.value LIKE 'kro%')
OR (t.field = firsname AND (
t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
)
Not tested. If you're having performance problems is probably less
like that the INTERSECT is the problem with all those LIKE's in
there? Is t.value indexed?
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Connection Limit
Thanks for the help guys, i'll try your suggestions when i get a chance. Jon. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alternative to INTERSECT
On Tuesday 31 July 2007 18:52:22 Josh Trutwin wrote:
> On Tue, 31 Jul 2007 17:30:51 +
>
> Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote:
> > Hi all. I have the following schema:
> >
> > CREATE TABLE test (
> > id integer NOT NULL,
> > field character varying NOT NULL,
> > value character varying NOT NULL
> > );
> >
> > ALTER TABLE ONLY test
> > ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
> >
> > CREATE INDEX test_like_idx ON test USING btree (id, field, value
> > varchar_pattern_ops);
> >
> > Using INTERSECT I want to retrieve the rows matching (pseudo-code)
> > "firstname LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>
> Why not:
>
> WHERE (t.field = lastname AND t.value LIKE 'kro%')
>OR (t.field = firsname AND (
>t.value LIKE 'jose%' OR t.value LIKE 'andrea%')
>)
>
> Not tested. If you're having performance problems is probably less
> like that the INTERSECT is the problem with all those LIKE's in
> there? Is t.value indexed?
Yes, as I wrote:
CREATE INDEX test_like_idx ON test USING btree
(id, field, value varchar_pattern_ops);
And I'm observing that it uses that index.
Your query doesn't cut it, let me try to explain what I'm trying to achieve:
Suppose I have the following data:
INSERT INTO test VALUES (1, 'firstname', 'andreas');
INSERT INTO test VALUES (1, 'firstname', 'joseph');
INSERT INTO test VALUES (1, 'lastname', 'krogh');
INSERT INTO test VALUES (2, 'firstname', 'andreas');
INSERT INTO test VALUES (2, 'lastname', 'noname');
The reason for why I use INTERSECT is that I want:
SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'kro%';
To return only id 1, and the query:
SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'non%';
To return no rows at all (cause nobydy's name is "andreas joseph noname").
Your suggestion doesn't cover this case.
--
AJK
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Alternative to INTERSECT
On 7/31/07, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > Is there a way to make this more efficient with another construct, or > INTERSECT the only way to accomplish the desired result? SELECT f1.ID FROM TEST f1 JOIN TEST f2 ON f1.ID = f2.ID JOIN TEST f3 ON f2.ID = f3.ID WHERE f1.FIELD = 'firstname' AND f1.VALUE LIKE 'andrea%' AND f2.FIELD = 'firstname' AND f2.VALUE LIKE 'jose%' AND f3.FIELD = 'lastname' AND f3.VALUE LIKE 'kro%'; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Alternative to INTERSECT
On Tue, 31 Jul 2007, Andreas Joseph Krogh wrote:
> Hi all. I have the following schema:
>
> CREATE TABLE test (
> id integer NOT NULL,
> field character varying NOT NULL,
> value character varying NOT NULL
> );
>
> ALTER TABLE ONLY test
> ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
>
> CREATE INDEX test_like_idx ON test USING btree (id, field, value
> varchar_pattern_ops);
>
> Using INTERSECT I want to retrieve the rows matching (pseudo-code) "firstname
> LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'"
>
> on=> SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
> LIKE 'andrea%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
> LIKE 'jose%'
> INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
> LIKE 'kro%';
Do you want something with only a firstname of jose or a firstname of jose
and something other than andrea (and no others) to match or not? I'd read
the pseudo-code to say yes, but AFAICT the query says no.
In general, some form of self-join would probably work, but the details
depend on exactly what should be returned.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
