Re: [GENERAL] partitioning using dblink
Added to TODO: o Add checks to prevent a CREATE RULE views on inherited tables http://archives.postgresql.org/pgsql-general/2008-02/msg01420.php --- Tom Lane wrote: Scara Maccai [EMAIL PROTECTED] writes: I got that there should be no difference... plus, I don't get any errors, You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] partitioning using dblink
Tom Lane wrote: Scara Maccai [EMAIL PROTECTED] writes: I got that there should be no difference... plus, I don't get any errors, You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. Is this a TODO? Seems so. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] partitioning using dblink
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Scara Maccai [EMAIL PROTECTED] writes: I got that there should be no difference... plus, I don't get any errors, You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. Is this a TODO? Seems so. I think it's just a minor bugfix, but if you want to put it in TODO for a day or two, go ahead... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] partitioning using dblink
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Scara Maccai [EMAIL PROTECTED] writes: I got that there should be no difference... plus, I don't get any errors, You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. Is this a TODO? Seems so. I think it's just a minor bugfix, but if you want to put it in TODO for a day or two, go ahead... That's fine --- I will just push it to the patches queue so we know it is a live issue. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] partitioning using dblink
You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. Is there a specific reason why views can't be part of an inheritance tree? I mean: it's that we don't want it or it would be just difficult to implement? ___ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] partitioning using dblink
Scara Maccai [EMAIL PROTECTED] writes: Is there a specific reason why views can't be part of an inheritance tree? I mean: it's that we don't want it or it would be just difficult to implement? It would certainly require a lot of rethinking of assumptions, in the planner and elsewhere. I have no good idea of how large the actual patch might end up being if it were attempted. But it's not something that's high on anyone's wish-list, and there's a chance that it could interfere with development of inheritance behaviors that people *do* care about (like partitioning). regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] partitioning using dblink
I can't get views to participate in the hierarchy...
create table outings1 as select * from outings_root limit 0;
alter table outings1 inherit outings_root;
SELECT *
FROM dblink('host=myhost dbname=tacche port=5433 user=postgres
password=postgres'::text,
'SELECT * from outings1'::text) as
(id integer, date date, spot_id integer, notes text);
Selects to outings_root won't show data from host myhost. Selects from
outings1 will work as expected.
This is not what I got from the docs:
the information about a view in the PostgreSQL
system catalogs is exactly the same as it is for a table. So for the
parser, there is absolutely no difference between a table and a view
Am I doing something wrong?
If it worked I would have a very basic (but working!) form of horizontal
partitioning
___
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail:
http://it.docs.yahoo.com/nowyoucan.html
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] partitioning using dblink
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote: I can't get views to participate in the hierarchy... The partition exclusion _may_ work if you do something like: create view as select * from dblink/plproxy-from-part1 where part1 constraint union all select * from dblink/plproxy-from-part2 where part2 constraint So if you do 'select * from view where constraint;' the postgres will skip partitions which do not match. You may need to define the setof function immutable or something... i'm not sure. But any contraint exclusion wont change the fact you are doing select * from tbl; in remote db, which makes the exercise quite pointess IMHO. I obviously would recommend pl/proxy for such task, but that would expect you are able to write your queries. If you are in situation where you don't control the queries, then plproxy quite likely is not use. -- marko ---(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: [GENERAL] partitioning using dblink
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote: I'm sorry, I didn't understand you post... 1) Why does my current implementation is not working? Hierarchy doesn't work with views in general, not only with dblink Exactly, because inheritance/constraint exclusion wont work with views. 2) Why am I supposed to use unions in the view? So that query evaluator can exclude unnecessary partitions. Given view: create view.. as select * from blah() where id = 0 and id 1 union all select * from blah() where id = 1 and id 2 ... Then running query select * from view where id = 10; can skip partitions by simply examining where expression. Quite likely you need to tune it for your case. 3) I know that I am doing select * from tbl in the remote db; that is something I can work on later. At least I would like to see it working, since there is nothing in the docs that says it shouldn't be working... 4) I am not able to rewrite my queries. Have fun then. -- marko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] partitioning using dblink
I'm sorry, I didn't understand you post... 1) Why does my current implementation is not working? Hierarchy doesn't work with views in general, not only with dblink 2) Why am I supposed to use unions in the view? 3) I know that I am doing select * from tbl in the remote db; that is something I can work on later. At least I would like to see it working, since there is nothing in the docs that says it shouldn't be working... 4) I am not able to rewrite my queries. - Messaggio originale - Da: Marko Kreen [EMAIL PROTECTED] A: Scara Maccai [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Inviato: Venerdì 29 febbraio 2008, 10:46:09 Oggetto: Re: [GENERAL] partitioning using dblink On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote: I can't get views to participate in the hierarchy... The partition exclusion _may_ work if you do something like: create view as select * from dblink/plproxy-from-part1 where part1 constraint union all select * from dblink/plproxy-from-part2 where part2 constraint So if you do 'select * from view where constraint;' the postgres will skip partitions which do not match. You may need to define the setof function immutable or something... i'm not sure. But any contraint exclusion wont change the fact you are doing select * from tbl; in remote db, which makes the exercise quite pointess IMHO. I obviously would recommend pl/proxy for such task, but that would expect you are able to write your queries. If you are in situation where you don't control the queries, then plproxy quite likely is not use. -- marko ___ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] partitioning using dblink
Exactly, because inheritance/constraint exclusion wont work with views. Ok, so there should be something written in the docs about it... From: the information about a view in the PostgreSQL system catalogs is exactly the same as it is for a table. So for the parser, there is absolutely no difference between a table and a view I got that there should be no difference... plus, I don't get any errors, it's only that data in the view doesn't show up when I query the master table. 2) Why am I supposed to use unions in the view? So that query evaluator can exclude unnecessary partitions. Ok: that would be another way of having partitions, right? 3) I know that I am doing select * from tbl in the remote db; that is something I can work on later. At least I would like to see it working, since there is nothing in the docs that says it shouldn't be working... 4) I am not able to rewrite my queries. Have fun then. Yeah I know... Thank you ___ L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail: http://it.docs.yahoo.com/nowyoucan.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] partitioning using dblink
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote: Exactly, because inheritance/constraint exclusion wont work with views. Ok, so there should be something written in the docs about it... From: the information about a view in the PostgreSQL system catalogs is exactly the same as it is for a table. So for the parser, there is absolutely no difference between a table and a view I got that there should be no difference... plus, I don't get any errors, it's only that data in the view doesn't show up when I query the master table. Seems like bug in docs/code. But I think the paragraph is written with in queries, views can be used anywhere tables can in mind, not that you can administer them the same way. 2) Why am I supposed to use unions in the view? So that query evaluator can exclude unnecessary partitions. Ok: that would be another way of having partitions, right? Yes, effect should be same. -- marko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] partitioning using dblink
Scara Maccai [EMAIL PROTECTED] writes: I got that there should be no difference... plus, I don't get any errors, You should have. The system enforces (or tries to) that a view can't be part of an inheritance hierarchy, but you seem to have managed to find a sequence of operations that avoids those checks. Turning a table into a view with a manual CREATE RULE operation has always been a kluge, and it's missing a check that the table isn't part of an inheritance tree. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 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
Re: [GENERAL] partitioning using dblink
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] partitioning using dblink
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
