Обсуждение: Cannot convert partitioned table to a view
Is there a special reason why masking views behind table partitions is not allowed?
Trying to do so raises an error:
CREATE RULE "_RETURN" AS ON SELECT TO xxx_20220715 DO INSTEAD SELECT * FROM yyy WHERE ...;
CREATE RULE "_RETURN" AS ON SELECT TO xxx_20220715 DO INSTEAD SELECT * FROM yyy WHERE ...;
ERROR: cannot convert partitioned table "xxx_20220715" to a view
That said, I can trick it if I have a view on a remote server and then attach it as a remote partition through the foreign data wrapper.
Are there any dangers that should be considered with such an approach?
I noticed that attaching the "_RETURN" rule to a table converts it to a view, and the operation is irreversible, i.e. once attached, I cannot drop the rule and put the relation back to a table one.
Are there any dangers that should be considered with such an approach?
I noticed that attaching the "_RETURN" rule to a table converts it to a view, and the operation is irreversible, i.e. once attached, I cannot drop the rule and put the relation back to a table one.
Cordially,
--
Kouber Saparev
Kouber Saparev <kouber@gmail.com> writes:
> I noticed that attaching the "_RETURN" rule to a table converts it to a
> view, and the operation is irreversible, i.e. once attached, I cannot drop
> the rule and put the relation back to a table one.
That is an ancient backwards-compatibility hack that you should not
rely on, and most certainly shouldn't try to make use of from user
code. It exists because very ancient versions of pg_dump didn't
know how to dump views as views. We're more likely to rip it out
as no-longer-needed than to expand what it does.
regards, tom lane
На чт, 6.10.2022 г. в 17:02 ч. Tom Lane <tgl@sss.pgh.pa.us> написа:
That is an ancient backwards-compatibility hack that you should not
rely on, and most certainly shouldn't try to make use of from user
code. It exists because very ancient versions of pg_dump didn't
know how to dump views as views. We're more likely to rip it out
as no-longer-needed than to expand what it does.
Does that mean that the ability to point remote partitions to views (on the remote side) will be forbidden in the future? Currently this feature is saving a lot of work (and disk-space) for us, as it allows us to introduce significant changes to the partitioned tables model without the need to populate these changes on all the hundreds of past partitions that do exist (and that we barely read anyway, that's why they reside on a remote server).
--
--
Kouber Saparev
Kouber Saparev <kouber@gmail.com> writes:
> Does that mean that the ability to point remote partitions to views (on the
> remote side) will be forbidden in the future?
No ... what has that got to do with this? We don't particularly care
what a foreign table is referencing, as long as it acts like a table.
regards, tom lane
No ... what has that got to do with this? We don't particularly care
what a foreign table is referencing, as long as it acts like a table.
Thank you.
We're more likely to rip it out as no-longer-needed than to expand what it does.
Excuse me, I didn't understand what was about to be ripped out then. Is it the ability to attach ON SELECT DO INSTEAD rules over tables in general?... Currently it is possible to do it (and the tables are converted to views, which is a bit confusing and counter-intentional at least in my case - I'd like the table to stay always a table), but not possible in case the table is a partition (yet still possible if the partition is remote pointing back to the same original server for instance).
Regards,
--
Kouber Saparev
Kouber Saparev <kouber@gmail.com> writes:
> Excuse me, I didn't understand what was about to be ripped out then.
The fact that creating a _RETURN rule for a table causes it to be
changed into a view. That's ugly and arguably dangerous.
regards, tom lane
On 10/7/22 15:27, Kouber Saparev wrote:
I think maybe you and Tom are talking about different things.
Why do you need rules and views, when a regular old partitioned table can have foreign tables in it?
На чт, 6.10.2022 г. в 17:02 ч. Tom Lane <tgl@sss.pgh.pa.us> написа:That is an ancient backwards-compatibility hack that you should not
rely on, and most certainly shouldn't try to make use of from user
code. It exists because very ancient versions of pg_dump didn't
know how to dump views as views. We're more likely to rip it out
as no-longer-needed than to expand what it does.Does that mean that the ability to point remote partitions to views (on the remote side) will be forbidden in the future? Currently this feature is saving a lot of work (and disk-space) for us, as it allows us to introduce significant changes to the partitioned tables model without the need to populate these changes on all the hundreds of past partitions that do exist (and that we barely read anyway, that's why they reside on a remote server).
I think maybe you and Tom are talking about different things.
Why do you need rules and views, when a regular old partitioned table can have foreign tables in it?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
The fact that creating a _RETURN rule for a table causes it to be
changed into a view. That's ugly and arguably dangerous.
Right, thank you.
Cheers,
--
Kouber Saparev