Re: md5 of table - Mailing list pgsql-general

From Sim Zacks
Subject Re: md5 of table
Date
Msg-id 4E63104A.8000109@compulab.co.il
Whole thread Raw
In response to Re: md5 of table  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
>>>> I thought about using dblink and the EXCEPT query, but then I need to
>>>> know the field list of each query result, which is a pain in the butt.
>>> That is not correct.  As long as the table definitions are precisely
>>> the same, you can move records across dblink without specifying
>>> fields.  You do this by using record type for the composite which
>>> dblink sends across as text.
>>>
>>> merlin
>> Do you have a quick example? This is what I have tried:
>>
>> select * from tbla
>> except
>> select * from dblink('host=dbhost dbname=otherdb user=myuser
>> password=mypwd'::text, 'select * from tbla')
>>
>> The error I get back is:
>> ERROR:  a column definition list is required for functions returning
>> "record"
> sure:
> select tbla from tbla
> except
> select t::tbla from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla::text from tbla') R(t text);
We tried something like that. Unfortunately, in 8.2 you can't cast a row
type as text.

> there's a bunch of ways to do that -- you can also do the md5 on the
> remote side so you can just send the digests.
> select * from tbla
> except
> select (t::tbla).* from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla::text from tbla') R(t text);
>
> should also work.
>
> This *might* work -- I didn't try.  It's been a while since I've used
> stock dblink.
> select * from tbla
> except
> select (t).* from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select tbla from tbla') R(t tbla);
>
> merlin

This looks like it might work for us. At least I would only need the
table name for the field list instead of the entire column list.


pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: Adding Additional Standby
Next
From: Jasen Betts
Date:
Subject: Re: FK violation on (emtpy) parent table