On Thu, Aug 14, 2025 at 4:26 PM Alastair Turner <min...@decodable.me> wrote: > > On Wed, 13 Aug 2025 at 11:09, Amit Kapila <amit.kapil...@gmail.com> wrote: >> >> On Fri, Aug 8, 2025 at 10:01 AM Dilip Kumar <dilipbal...@gmail.com> wrote: >> > >> > On Fri, Aug 8, 2025 at 8:58 AM shveta malik <shveta.ma...@gmail.com> wrote: >> > > >> > > On Thu, Aug 7, 2025 at 3:08 PM Dilip Kumar <dilipbal...@gmail.com> wrote: >> > > > >> > > > So logically for PostgreSQL its an >> > > > user table but yeah this is created and managed by the extension. >> > > > >> > > >> > > Any idea if the user can alter/drop or perform any DML on it? I could >> > > not find any details on this part. >> > >> > In my experience, for such extension managed tables where we want them >> > to behave like catalog, generally users are just granted with SELECT >> > permission. So although it is not a catalog but for accessibility >> > wise for non admin users it is like a catalog. IMHO, even if we >> > choose to create a user table for conflict log history we can also >> > control the permissions similarly. What's your opinion on this? >> > >> >> Yes, I think it is important to control permissions on this table even >> if it is a user table. How about giving SELECT, DELETE, TRUNCATE >> permissions to subscription owner assuming we create one such table >> per subscription? >> >> It should be a user table due to following reasons (a) It is an ever >> growing table by definition and we need some level of user control to >> manage it (like remove the old data); (b) We may want some sort of >> partitioning streategy to manage it, even though, we decide to do it >> ourselves now but in future, we should allow user to also specify it; >> (c) We may also want user to specify what exact information she wants >> to get stored considering in future we want resolutions to also be >> stored in it. See a somewhat similar proposal to store errors during >> copy by Tom [1]; (d) In a near-by thread, we are discussing storing >> errors during copy in user table [2] and we have some similarity with >> that proposal as well. >> >> If we agree on this then the next thing to consider is whether we >> allow users to create such a table or do it ourselves. In the long >> term, we may want both but for simplicity, we can auto-create >> ourselves during CREATE SUBSCRIPTION with some option. BTW, if we >> decide to let user create it then we can consider the idea of TYPED >> tables as discussed in emails [3][4]. > > > Having it be a user table, and specifying the table per subscription sounds > good. This is very similar to how the load error tables for CloudBerry > behave, for instance. To have both options for table creation, CREATE ... IF > NOT EXISTS semantics work well - if the option on CREATE SUBSCRIPTION > specifies an existing table of the right type use it, or create one with the > name supplied. This would also give the user control over whether to have one > table per subscription, one central table or anything in between. >
Sounds reasonable. I think the first version we can let such a table be created automatically with some option(s) with subscription. Then, in subsequent versions, we can extend the functionality to allow existing tables. > > Rather than constraining permissions on the table, the CREATE SUBSCRIPTION > command could create a dependency relationship between the table and the > subscription.This would prevent removal of the table, even by a superuser. > Okay, that makes sense. But, we still probably want to disallow users from inserting or updating rows in the conflict table. >> >> For user tables, we need to consider how to avoid replicating these >> tables for publications that use FOR ALL TABLES specifier. One idea is >> to use EXCLUDE table functionality as being discussed in thread [5] >> but that would also be a bit tricky especially if we decide to create >> such a table automatically. One naive idea is that internally we skip >> sending changes from this table for "FOR ALL TABLES" publication, and >> we shouldn't allow creating publication for this table. OTOH, if we >> allow the user to create and specify this table, we can ask her to >> specify with EXCLUDE syntax in publication. This needs more thoughts. > > > If a dependency relationship is established between the error table and the > subscription, could this be used as a basis for filtering the error tables > from FOR ALL TABLES subscriptions? > Yeah, that is worth considering. -- With Regards, Amit Kapila.
