Re: partitioning using dblink

Hi,

I started thinking that using dblink I could "easily" get some kind of read only multi-server partitioning, if only VIEWs could be declared with "INHERITS"...

That way I think I could

1) add as many views as the number of DBs as

CREATE VIEW mytable_part_n AS
<SELECT using dblink on remote server_n>
INHERITS mytable

to every DB I have

2) A select on the DB that asks for data on multiple DBs (because it uses data from different partition) would ask the proper data to the proper server...

I think that it would be very nice...
But, since VIEWs can't be declared using INHERITS, that won't work...

Am I wrong?

I know that putting INHERITS and CHECKs on the VIEWs are not a good idea, but I think some method to declare a TABLE as being "remote" would be very cool... I don't know, maybe using a new "storage_parameter"...

___________________________________
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html

Scara Maccai wrote:

> I started thinking that using dblink I could "easily" get some kind of
> read only multi-server partitioning, if only VIEWs could be declared
> with "INHERITS"...

I think you can do pretty much the same thing with PL/Proxy; see
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

> I think you can do pretty much the same thing with
PL/Proxy; see
>
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

Mmmh, I actually looked into that but I thought it
only worked with user functions...
am I wrong?

What I'd like to have is an almost-transparent
horizontal partitioning system, and
I think that everything is there: postgresql
partitioning (which even has partition pruning)
+ dblink should be enough...

it's only that you can't use them together, because
with dblink you should use
VIEWs but partitioning can't work with those (which I
find correct, BTW).
What I would like is for Postgresql to know that a
table is actually a remote table...

BUT!!!
since "Views in PostgreSQL are implemented using the
rule system", I could do
(pseudo-sql)

CREATE TABLE mypartion HINERITS blabla CHECK
CONSTR..[...] ;

CREATE RULE "_RETURN" AS ON SELECT TO myview DO
INSTEAD
SELECT * FROM dblink;

Can't I??? That would be horizontal partitioning using
dblink+postgresql!!!

Am I wrong???

___________________________________
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html