Thread: Re: Restrict publishing of partitioned table with a foreign table as partition
Re: Restrict publishing of partitioned table with a foreign table as partition
From
Álvaro Herrera
Date:
On 2025-Feb-04, vignesh C wrote: > We should throw an error for partitioned tables that contain foreign > partitions, as this would include the data from these foreign tables > during the initial sync, while incremental changes would not be > replicated. Hmm, I would support the idea of allowing partitioned tables containing foreign partitions into publications, where only the tuples in non-foreign partitions would be transmitted -- both during initial sync and during replicated DML. This way, any partitioned tables with mixed local and foreign partitions would work okay for replication. If the subscriber wants the data in the foreign partitions, they can create the foreign table on the subscription side and all is well. From a users POV this is probably the most useful. Another aspect is the case where you create the publication first and the foreign table later. I didn't spot in the proposed patch any code that would check whether a publication including this schema exists, which we would have to do if we wanted to be watertight about rejecting publications with foreign tables. But I think that would be a bad direction to go it. I'd rather have the table-listing code for publications skip any foreign tables. For instance, GetPubPartitionOptionRelations() and PublicationAddTables() should skip tables with relkind RELKIND_FOREIGN. The sync code should also silently ignore all foreign tables. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "I am amazed at [the pgsql-sql] mailing list for the wonderful support, and lack of hesitasion in answering a lost soul's question, I just wished the rest of the mailing list could be like this." (Fotis) https://postgr.es/m/200606261359.k5QDxE2p004593@auth-smtp.hol.gr
On Tue, 4 Feb 2025 at 21:21, Álvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > On 2025-Feb-04, vignesh C wrote: > > > We should throw an error for partitioned tables that contain foreign > > partitions, as this would include the data from these foreign tables > > during the initial sync, while incremental changes would not be > > replicated. > > Hmm, I would support the idea of allowing partitioned tables containing > foreign partitions into publications, where only the tuples in > non-foreign partitions would be transmitted -- both during initial sync > and during replicated DML. This way, any partitioned tables with mixed > local and foreign partitions would work okay for replication. If the > subscriber wants the data in the foreign partitions, they can create the > foreign table on the subscription side and all is well. From a users > POV this is probably the most useful. > > Another aspect is the case where you create the publication first and > the foreign table later. I didn't spot in the proposed patch any code > that would check whether a publication including this schema exists, > which we would have to do if we wanted to be watertight about rejecting > publications with foreign tables. But I think that would be a bad > direction to go it. > > I'd rather have the table-listing code for publications skip any foreign > tables. For instance, GetPubPartitionOptionRelations() and > PublicationAddTables() should skip tables with relkind RELKIND_FOREIGN. > The sync code should also silently ignore all foreign tables. We can maintain the behavior you suggested when the PUBLISH_VIA_PARTITION_ROOT option is set to false. However, when PUBLISH_VIA_PARTITION_ROOT is true, the table data is copied from the root table (as intended by the user), which will also include the foreign table data. In this case, wouldn’t it be better to throw an error? Regards, Vignesh
Re: Restrict publishing of partitioned table with a foreign table as partition
From
Álvaro Herrera
Date:
On 2025-Feb-05, vignesh C wrote: > We can maintain the behavior you suggested when the > PUBLISH_VIA_PARTITION_ROOT option is set to false. However, when > PUBLISH_VIA_PARTITION_ROOT is true, the table data is copied from the > root table (as intended by the user), which will also include the > foreign table data. In this case, wouldn’t it be better to throw an > error? It sounds to me a reasonable restriction that you can only add a partitioned table to a publication if publish_via_partition_root=false. Then the case of hybrid partitioned tables is supported, but it requires that changes are published directly by partitions, which is sensible anyway. In this case, during CREATE FOREIGN TABLE of a partition with this condition, we must check whether any publications include the schema that the table is being created on (or attached, for ALTER TABLE ATTACH PARTITION), and whether there are any publications that are FOR ALL TABLES that would be affected. (If we later figure out a way to allow publish_via_partition_root and skip the tuples in foreign partitions, it's easy to remove the restriction.) -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "That sort of implies that there are Emacs keystrokes which aren't obscure. I've been using it daily for 2 years now and have yet to discover any key sequence which makes any sense." (Paul Thomas)