Thread: Questions about Views, Rules and DBLink

 Hi!

 I'm using dblink with some DBs and i'm having a few problems, more
precisely with the dblink_current_query() function.

 First I create the following views on a DB:

- create or replace view users as
 select *
 from dblink('hostaddr=127.0.0.1 dbname=teste user=postgres
password=postgres','select * from users')
 as t1(user_id int4, username varchar(20), passwd varchar(20), address
varchar(100), phone varchar(20), group_id int4);

- create or replace view utilizadores as
 select *
 from dblink('hostaddr=127.0.0.1 dbname=teste1 user=postgres
password=postgres','select * from utilizadores')
 as t1(user_id int4, nome varchar(100), sexo char(1), idade int2,
altura int4, peso int2);

With these views I can access and manipulate the records of tables
'users' and 'utilizadores' remotely using dblink_exec() with
dblink_current_query() as parameter directly and transparently, since
the remote tables have the same name of the views. The following rule
does the trick on insert (for example):

- CREATE OR REPLACE RULE users_ins AS ON INSERT TO users
    DO INSTEAD
    select dblink_exec(
    'hostaddr=127.0.0.1 dbname=teste user=postgres password=postgres',
    dblink_current_query()
    );


 So far so good, now I do the following:

- create or replace view users_util as
 select
    us.user_id, us.username, us.passwd, ut.nome, ut.sexo, ut.idade,
ut.altura, ut.peso, us.address, us.phone, us.group_id
 from
    users us, utilizadores ut
 where
    us.user_id = ut.user_id


 This creates a view that is a join of the previous views users and
utilizadores. Imagine I want to create a rule that on insert does
instead the insert on the view users:

 - CREATE OR REPLACE RULE users_util_ins AS ON INSERT TO users_util
    DO INSTEAD
    INSERT INTO users VALUES (
           NEW.user_id,
           NEW.username,
           NEW.passwd,
           NEW.address,
           NEW.phone,
           NEW.group_id
    )


 So now if I do:

- insert into users_util (username, passwd, nome, sexo, idade, altura,
peso, address, phone, group_id) values ('prof_04', 'prof_04',
'prof_04', 'm', 45, 165, 80, 'r. da frente, nº100', '12323572', 13);

 The rule should issue the query:

 - insert into users values ('prof_04', 'prof_04', 'r. da frente,
nº100', '12323572', 13);

The problem is that the query being sent to table users is the same
that I perform on the users_util view...


  So (finally), my question is why does this happen? Using instead on
the users_util insert rule shouldn't discard the original query and
rewrite it according to the specified on the rule?? Is this a problem
of dblink?


 Sorry about the extent of my message and thanks in advance,
 Joao Afonso

Joao Afonso <joaoaafonso@gmail.com> writes:
>   So (finally), my question is why does this happen? Using instead on
> the users_util insert rule shouldn't discard the original query and
> rewrite it according to the specified on the rule?? Is this a problem
> of dblink?

I hadn't noticed the dblink_current_query() function before, but now
that I see it, I consider it a pretty bad idea.  It certainly will not
help you the way you are hoping, because what it returns is the text of
the interactive command the backend is currently working on --- which
could be indefinitely far removed from the operation your rule is firing
for.

            regards, tom lane

Tom Lane wrote:
> Joao Afonso <joaoaafonso@gmail.com> writes:
>
>>  So (finally), my question is why does this happen? Using instead on
>>the users_util insert rule shouldn't discard the original query and
>>rewrite it according to the specified on the rule?? Is this a problem
>>of dblink?
>
> I hadn't noticed the dblink_current_query() function before, but now
> that I see it, I consider it a pretty bad idea.  It certainly will not
> help you the way you are hoping, because what it returns is the text of
> the interactive command the backend is currently working on --- which
> could be indefinitely far removed from the operation your rule is firing
> for.
>

When it was added (and discussed on the lists) it was also acknowledged
that it would not be useful in all situations. However, there was at
least one use case where it worked as intended and needed.

Joe

 Thanks for your help. But still, do you think there could be a way to
alter the dblink_current_query() function so that it could return the
right query? Or should I try to build the query in a function and send
it through dblink instead of dblink_current_query()?

  I've also been told that oracle has an auditing service that records
every action the users make, including the queries issued. If pg has
something like that I could use it instead.

On 8/1/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joao Afonso <joaoaafonso@gmail.com> writes:
> >   So (finally), my question is why does this happen? Using instead on
> > the users_util insert rule shouldn't discard the original query and
> > rewrite it according to the specified on the rule?? Is this a problem
> > of dblink?
>
> I hadn't noticed the dblink_current_query() function before, but now
> that I see it, I consider it a pretty bad idea.  It certainly will not
> help you the way you are hoping, because what it returns is the text of
> the interactive command the backend is currently working on --- which
> could be indefinitely far removed from the operation your rule is firing
> for.
>
>                        regards, tom lane
>