You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by "avamingli (via GitHub)" <gi...@apache.org> on 2024/11/06 08:10:28 UTC
[PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
avamingli opened a new pull request, #702:
URL: https://github.com/apache/cloudberry/pull/702
Allow answer query using materialized views which have external or foreign tables.
Since we don't know if the data is up to date of externel table outside CBDB, introduce a new GUC:
**`aqumv_allow_foreign_table`**
Let user decide if they want to use matview instead of query on external tables.
```sql
create materialized view aqumv_ext_mv as
select * from aqumv_ext_r;
explain (costs off, verbose)
select * from aqumv_ext_r;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: id
-> Seq Scan on aqumv.aqumv_ext_mv
Output: id
Optimizer: Postgres query optimizer
```
Index could also be used if there were on matviews.
```sql
create index on aqumv_ext_mv(id);
explain (costs off, verbose)
select * from aqumv_ext_r where id = 5;
QUERY PLAN
----------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Output: id
-> Index Only Scan using aqumv_ext_mv_id_idx on aqumv.aqumv_ext_mv
Output: id
Index Cond: (aqumv_ext_mv.id = 5)
Optimizer: Postgres query optimizer
```
<!--Thank you for contributing!-->
<!--In case of an existing issue or discussions, please reference it-->
fix #ISSUE_Number
<!--Remove this section if no corresponding issue.-->
---
### Change logs
_Describe your change clearly, including what problem is being solved or what feature is being added._
_If it has some breaking backward or forward compatibility, please clary._
### Why are the changes needed?
_Describe why the changes are necessary._
### Does this PR introduce any user-facing change?
_If yes, please clarify the previous behavior and the change this PR proposes._
### How was this patch tested?
_Please detail how the changes were tested, including manual tests and any relevant unit or integration tests._
### Contributor's Checklist
Here are some reminders and checklists before/when submitting your pull request, please check them:
- [ ] Make sure your Pull Request has a clear title and commit message. You can take [git-commit](https://github.com/cloudberrydb/cloudberrydb/blob/main/.gitmessage) template as a reference.
- [ ] Sign the Contributor License Agreement as prompted for your first-time contribution(*One-time setup*).
- [ ] Learn the [coding contribution guide](https://cloudberrydb.org/contribute/code), including our code conventions, workflow and more.
- [ ] List your communication in the [GitHub Issues](https://github.com/cloudberrydb/cloudberrydb/issues) or [Discussions](https://github.com/orgs/cloudberrydb/discussions) (if has or needed).
- [ ] Document changes.
- [ ] Add tests for the change
- [ ] Pass `make installcheck`
- [ ] Pass `make -C src/test installcheck-cbdb-parallel`
- [ ] Feel free to request `cloudberrydb/dev` team for review and approval when your PR is ready🥳
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2487752119
@my-ship-it
As we have refresh fast path at #682, but for external tables we don't know the status(always up to date in gp_maview_aux).
This will make REFRESH command fail to do the real thing from external data.
So, we should skip fast path for the views have external tables. That need catalog change to record if a view has external tables outside CBDB.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2458961412
see more details in #693
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2461136340
> > aqumv_allow_foreign_table
>
> PostgreSQL-style GUC would have name like enable_XXX, huh? So, maybe enable_aqumv_foreign_table
Not sure.. I follow this one: `allow_system_table_mods`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1832082132
##########
src/backend/optimizer/plan/aqumv.c:
##########
@@ -151,6 +151,9 @@ answer_query_using_materialized_views(PlannerInfo *root,
has_subclass(origin_rel_oid))
return mv_final_rel;
+ if (get_rel_relkind(origin_rel_oid) == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table)
Review Comment:
> create readable external table aqumv_ext_r(id int)
location ('demoprot://aqumvtextfile.txt') format 'text';
1. can we mantain `aqumvtextfile.txt`status in `gp_matview_aux`use file timestamp?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it merged PR #702:
URL: https://github.com/apache/cloudberry/pull/702
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1832014589
##########
src/backend/optimizer/plan/aqumv.c:
##########
@@ -151,6 +151,9 @@ answer_query_using_materialized_views(PlannerInfo *root,
has_subclass(origin_rel_oid))
return mv_final_rel;
+ if (get_rel_relkind(origin_rel_oid) == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table)
Review Comment:
GP forbids creating a matview as select from writable external table.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "my-ship-it (via GitHub)" <gi...@apache.org>.
my-ship-it commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2490337693
> @my-ship-it As we have refresh fast path at #682, but for external tables we don't know the status(always up to date in gp_maview_aux). This will make REFRESH command fail to do the real thing from external data.
>
> So, we should skip fast path for the views have external tables. That need catalog change to record if a view has external tables outside CBDB.
Yes, I think it is a reasonable behavior. Thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2497106586
> > @my-ship-it As we have refresh fast path at #682, but for external tables we don't know the status(always up to date in gp_maview_aux). This will make REFRESH command fail to do the real thing from external data.
> > So, we should skip fast path for the views have external tables. That need catalog change to record if a view has external tables outside CBDB.
>
> Yes, I think it is a reasonable behavior. Thanks!
Done in 273dae106985d139305fc79f7267ba8d8e8f507c,
add `has_foreign` to identify a mv's Query has foreign tables.
If true, we will avoid Refresh fast path no matter what the datastatus of mv is.
This needs a catalog change, create a label for it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "reshke (via GitHub)" <gi...@apache.org>.
reshke commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2459980924
> aqumv_allow_foreign_table
PostgreSQL-style GUC would have name like enable_XXX, huh? So, maybe enable_aqumv_allow_foreign_table
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1868912345
##########
src/include/catalog/gp_matview_aux.h:
##########
@@ -27,6 +27,7 @@ CATALOG(gp_matview_aux,7153,GpMatviewAuxId) BKI_SHARED_RELATION
{
Oid mvoid; /* materialized view oid */
NameData mvname; /* materialized view name */
+ bool has_foreign; /* view query has foreign tables? */
Review Comment:
This is intended, for future view have multiple tables.
Think about that: `view mv1 as select * from t1 join foreign_table_t2 on true`;
If one of the tables is foreign table, the field `has_foreign` is true for mv1, and should avoid using REFRESH fast path.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "yjhnupt (via GitHub)" <gi...@apache.org>.
yjhnupt commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1868789241
##########
src/include/catalog/gp_matview_aux.h:
##########
@@ -27,6 +27,7 @@ CATALOG(gp_matview_aux,7153,GpMatviewAuxId) BKI_SHARED_RELATION
{
Oid mvoid; /* materialized view oid */
NameData mvname; /* materialized view name */
+ bool has_foreign; /* view query has foreign tables? */
Review Comment:
use `char relkind` has more extensibility.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "yjhnupt (via GitHub)" <gi...@apache.org>.
yjhnupt commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1868789241
##########
src/include/catalog/gp_matview_aux.h:
##########
@@ -27,6 +27,7 @@ CATALOG(gp_matview_aux,7153,GpMatviewAuxId) BKI_SHARED_RELATION
{
Oid mvoid; /* materialized view oid */
NameData mvname; /* materialized view name */
+ bool has_foreign; /* view query has foreign tables? */
Review Comment:
use `char relkind` has more extensibility, to avoid next catalog change.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1832087462
##########
src/backend/optimizer/plan/aqumv.c:
##########
@@ -151,6 +151,9 @@ answer_query_using_materialized_views(PlannerInfo *root,
has_subclass(origin_rel_oid))
return mv_final_rel;
+ if (get_rel_relkind(origin_rel_oid) == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table)
Review Comment:
It's just an example, they could be files, remote servers or another databases outside CBDB. You couldn't know the status of them and pointless for that. See more in #693
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "yjhjstz (via GitHub)" <gi...@apache.org>.
yjhjstz commented on code in PR #702:
URL: https://github.com/apache/cloudberry/pull/702#discussion_r1832011862
##########
src/backend/optimizer/plan/aqumv.c:
##########
@@ -151,6 +151,9 @@ answer_query_using_materialized_views(PlannerInfo *root,
has_subclass(origin_rel_oid))
return mv_final_rel;
+ if (get_rel_relkind(origin_rel_oid) == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table)
Review Comment:
> create readable external table aqumv_ext_r ()
can we get `readable` from table ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
Re: [PR] [AQUMV]Enable answer query using Materialized View for external table. [cloudberry]
Posted by "avamingli (via GitHub)" <gi...@apache.org>.
avamingli commented on PR #702:
URL: https://github.com/apache/cloudberry/pull/702#issuecomment-2516473249
Rebased to resolve conflicts.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org