public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/1] In psql \copy from, send data to server in larger chunks. 9+ messages / 7 participants [nested] [flat]
* [PATCH 1/1] In psql \copy from, send data to server in larger chunks. @ 2021-02-06 22:10 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Heikki Linnakangas @ 2021-02-06 22:10 UTC (permalink / raw) Previously, we would send each line as a separate CopyData message. That's pretty wasteful if the table is narrow, as each CopyData message has 5 bytes of overhead. For efficiency, buffer up and pack 8 kB of input data into each CopyData message. --- src/bin/psql/copy.c | 114 +++++++++++++++++++++++++++----------------- 1 file changed, 69 insertions(+), 45 deletions(-) diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c index 78f0dc5a507..8c56f2470e2 100644 --- a/src/bin/psql/copy.c +++ b/src/bin/psql/copy.c @@ -581,77 +581,101 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res) else { bool copydone = false; + int buflen; + bool at_line_begin = true; + if (showprompt) + { + const char *prompt = get_prompt(PROMPT_COPY, NULL); + + fputs(prompt, stdout); + fflush(stdout); + } + + /* + * In text mode, We have to read the input one line at a time, so that + * we can stop reading at the EOF marker (\.). We mustn't read beyond + * the EOF marker, because if the data is inlined in a SQL script, we + * would eat up the commands after the EOF marker. + */ + buflen = 0; while (!copydone) - { /* for each input line ... */ - bool firstload; - bool linedone; + { + int linelen; + char *fgresult; - if (showprompt) + if (at_line_begin && showprompt) { - const char *prompt = get_prompt(PROMPT_COPY, NULL); + const char *prompt; + sigint_interrupt_enabled = false; + + prompt = get_prompt(PROMPT_COPY, NULL); fputs(prompt, stdout); fflush(stdout); - } - firstload = true; - linedone = false; - - while (!linedone) - { /* for each bufferload in line ... */ - int linelen; - char *fgresult; - - /* enable longjmp while waiting for input */ sigint_interrupt_enabled = true; + } - fgresult = fgets(buf, sizeof(buf), copystream); - - sigint_interrupt_enabled = false; + /* enable longjmp while waiting for input */ + sigint_interrupt_enabled = true; - if (!fgresult) - { - copydone = true; - break; - } + fgresult = fgets(&buf[buflen], COPYBUFSIZ - buflen, copystream); - linelen = strlen(buf); + sigint_interrupt_enabled = false; - /* current line is done? */ - if (linelen > 0 && buf[linelen - 1] == '\n') - linedone = true; + if (!fgresult) + copydone = true; + else + { + linelen = strlen(fgresult); + buflen += linelen; - /* check for EOF marker, but not on a partial line */ - if (firstload) + if (buf[buflen - 1] == '\n') { - /* - * This code erroneously assumes '\.' on a line alone - * inside a quoted CSV string terminates the \copy. - * https://www.postgresql.org/message-id/[email protected] - */ - if (strcmp(buf, "\\.\n") == 0 || - strcmp(buf, "\\.\r\n") == 0) + /* check for EOF marker, but not on a partial line */ + if (at_line_begin) { - copydone = true; - break; + /* + * This code erroneously assumes '\.' on a line alone + * inside a quoted CSV string terminates the \copy. + * https://www.postgresql.org/message-id/[email protected] + */ + if ((linelen == 3 && memcmp(fgresult, "\\.\n", 3) == 0) || + (linelen == 4 && memcmp(fgresult, "\\.\r\n", 4) == 0)) + { + copydone = true; + } } - firstload = false; + if (copystream == pset.cur_cmd_source) + { + pset.lineno++; + pset.stmt_lineno++; + } + at_line_begin = true; } + else + at_line_begin = false; + } - if (PQputCopyData(conn, buf, linelen) <= 0) + /* + * If the buffer is full, or we've reached the EOF, flush it. + * + * Make sure there's always space for four more bytes in the buffer, + * plus a NUL terminator. That way, an EOF marker is never split + * across two fgets() calls, which simplies the logic. + */ + if (buflen >= COPYBUFSIZ - 5 || (copydone && buflen > 0)) + { + if (PQputCopyData(conn, buf, buflen) <= 0) { OK = false; copydone = true; break; } - } - if (copystream == pset.cur_cmd_source) - { - pset.lineno++; - pset.stmt_lineno++; + buflen = 0; } } } -- 2.30.0 --------------20DC7BBD34247104D0B00618-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-25 22:30 Tomas Vondra <[email protected]> 0 siblings, 2 replies; 9+ messages in thread From: Tomas Vondra @ 2022-04-25 22:30 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]>; +Cc: Amit Kapila <[email protected]> On 4/25/22 17:48, Alvaro Herrera wrote: > I just noticed that publishing tables on multiple publications with > different row filters and column lists has somewhat surprising behavior. > To wit: if a column is published in any row-filtered publication, then > the values for that column are sent to the subscriber even for rows that > don't match the row filter, as long as the row matches the row filter > for any other publication, even if that other publication doesn't > include the column. > > Here's an example. > > Publisher: > > create table uno (a int primary key, b int, c int); > create publication uno for table uno (a, b) where (a > 0); > create publication dos for table uno (a, c) where (a < 0); > > Here, we specify: publish columns a,b for rows with positive a, and > publish columns a,c for rows with negative a. > > What happened next will surprise you! Well, maybe not. On subscriber: > > create table uno (a int primary key, b int, c int); > create subscription sub_uno connection 'port=55432 dbname=alvherre' publication uno,dos; > > Publisher: > insert into uno values (1, 2, 3), (-1, 3, 4); > > Publication 'uno' only has columns a and b, so row with a=1 should not > have value c=3. And publication 'dos' only has columns a and c, so row > with a=-1 should not have value b=3. But, on subscriber: > > table uno; > a │ b │ c > ────┼───┼─── > 1 │ 2 │ 3 > -1 │ 3 │ 4 > > q.e.d. > > I think results from a too simplistic view on how to mix multiple > publications with row filters and column lists. IIRC we are saying "if > column X appears in *any* publication, then the value is published", > period, and don't stop to evaluate the row filter corresponding to each > of those publications. > Right. > The desired result on subscriber is: > > table uno; > a │ b │ c > ────┼───┼─── > 1 │ 2 │ > -1 │ │ 4 > > > Thoughts? > I'm not quite sure which of the two behaviors is more "desirable". In a way, it's somewhat similar to publish_as_relid, which is also calculated not considering which of the row filters match? But maybe you're right and it should behave the way you propose ... the example I have in mind is a use case replicating table with two types of rows - sensitive and non-sensitive. For sensitive, we replicate only some of the columns, for non-sensitive we replicate everything. Which could be implemented as two publications create publication sensitive_rows for table t (a, b) where (is_sensitive); create publication non_sensitive_rows for table t where (not is_sensitive); But the way it's implemented now, we'll always replicate all columns, because the second publication has no column list. Changing this to behave the way you expect would be quite difficult, because at the moment we build a single OR expression from all the row filters. We'd have to keep the individual expressions, so that we can build a column list for each of them (in order to ignore those that don't match). We'd have to remove various other optimizations - for example we can't just discard row filters if we found "no_filter" publication. Or more precisely, we'd have to consider column lists too. In other words, we'd have to merge pgoutput_column_list_init into pgoutput_row_filter_init, and then modify pgoutput_row_filter to evaluate the row filters one by one, and build the column list. I can take a stab at it, but it seems strange to not apply the same logic to evaluation of publish_as_relid. I wonder what Amit thinks about this, as he wrote the row filter stuff. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-27 04:55 Amit Kapila <[email protected]> parent: Tomas Vondra <[email protected]> 1 sibling, 2 replies; 9+ messages in thread From: Amit Kapila @ 2022-04-27 04:55 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]> On Tue, Apr 26, 2022 at 4:00 AM Tomas Vondra <[email protected]> wrote: > > On 4/25/22 17:48, Alvaro Herrera wrote: > > > The desired result on subscriber is: > > > > table uno; > > a │ b │ c > > ────┼───┼─── > > 1 │ 2 │ > > -1 │ │ 4 > > > > > > Thoughts? > > > > I'm not quite sure which of the two behaviors is more "desirable". In a > way, it's somewhat similar to publish_as_relid, which is also calculated > not considering which of the row filters match? > Right, or in other words, we check all publications to decide it and similar is the case for publication actions which are also computed independently for all publications. > But maybe you're right and it should behave the way you propose ... the > example I have in mind is a use case replicating table with two types of > rows - sensitive and non-sensitive. For sensitive, we replicate only > some of the columns, for non-sensitive we replicate everything. Which > could be implemented as two publications > > create publication sensitive_rows > for table t (a, b) where (is_sensitive); > > create publication non_sensitive_rows > for table t where (not is_sensitive); > > But the way it's implemented now, we'll always replicate all columns, > because the second publication has no column list. > > Changing this to behave the way you expect would be quite difficult, > because at the moment we build a single OR expression from all the row > filters. We'd have to keep the individual expressions, so that we can > build a column list for each of them (in order to ignore those that > don't match). > > We'd have to remove various other optimizations - for example we can't > just discard row filters if we found "no_filter" publication. > I don't think that is the right way. We need some way to combine expressions and I feel the current behavior is sane. I mean to say that even if there is one publication that has no filter (column/row), we should publish all rows with all columns. Now, as mentioned above combining row filters or column lists for all publications appears to be consistent with what we already do and seems correct behavior to me. To me, it appears that the method used to decide whether a particular table is published or not is also similar to what we do for row filters or column lists. Even if there is one publication that publishes all tables, we consider the current table to be published irrespective of whether other publications have published that table or not. > Or more > precisely, we'd have to consider column lists too. > > In other words, we'd have to merge pgoutput_column_list_init into > pgoutput_row_filter_init, and then modify pgoutput_row_filter to > evaluate the row filters one by one, and build the column list. > Hmm, I think even if we want to do something here, we also need to think about how to achieve similar behavior for initial tablesync which will be more tricky. > I can take a stab at it, but it seems strange to not apply the same > logic to evaluation of publish_as_relid. > Yeah, the current behavior seems to be consistent with what we already do. > I wonder what Amit thinks about > this, as he wrote the row filter stuff. > I feel we can explain a bit more about this in docs. We already have some explanation of how row filters are combined [1]. We can probably add a few examples for column lists. [1] - https://www.postgresql.org/docs/devel/logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FI... -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-27 06:12 Michael Paquier <[email protected]> parent: Amit Kapila <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: Michael Paquier @ 2022-04-27 06:12 UTC (permalink / raw) To: Amit Kapila <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]> On Wed, Apr 27, 2022 at 10:25:50AM +0530, Amit Kapila wrote: > I feel we can explain a bit more about this in docs. We already have > some explanation of how row filters are combined [1]. We can probably > add a few examples for column lists. I am not completely sure exactly what we should do here, but this stuff needs to be at least discussed. I have added an open item. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../Ymje09ToDVKpW%[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-27 09:43 Alvaro Herrera <[email protected]> parent: Tomas Vondra <[email protected]> 1 sibling, 1 reply; 9+ messages in thread From: Alvaro Herrera @ 2022-04-27 09:43 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; +Cc: Pg Hackers <[email protected]>; Amit Kapila <[email protected]> On 2022-Apr-26, Tomas Vondra wrote: > I'm not quite sure which of the two behaviors is more "desirable". In a > way, it's somewhat similar to publish_as_relid, which is also calculated > not considering which of the row filters match? I grepped doc/src/sgml for `publish_as_relid` and found no hits, so I suppose it's not a user-visible feature as such. > But maybe you're right and it should behave the way you propose ... the > example I have in mind is a use case replicating table with two types of > rows - sensitive and non-sensitive. For sensitive, we replicate only > some of the columns, for non-sensitive we replicate everything. Exactly. If we blindly publish row/column values that aren't in *any* publications, this may lead to leaking protected values. > Changing this to behave the way you expect would be quite difficult, > because at the moment we build a single OR expression from all the row > filters. We'd have to keep the individual expressions, so that we can > build a column list for each of them (in order to ignore those that > don't match). I think we should do that, yeah. > I can take a stab at it, but it seems strange to not apply the same > logic to evaluation of publish_as_relid. I wonder what Amit thinks about > this, as he wrote the row filter stuff. By grepping publicationcmds.c, it seems that publish_as_relid refers to the ancestor partitioned table that is used for column list and rowfilter determination, when a partition is being published as part of it. I don't think these things are exactly parallel. ... In fact I think they are quite orthogonal: probably you should be able to publish a partitioned table in two publications, with different rowfilters and different column lists (which can come from the topmost partitioned table), and each partition should still work in the way I describe above. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "[PostgreSQL] is a great group; in my opinion it is THE best open source development communities in existence anywhere." (Lamar Owen) ^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: bogus: logical replication rows/cols combinations @ 2022-04-27 10:08 [email protected] <[email protected]> parent: Amit Kapila <[email protected]> 1 sibling, 0 replies; 9+ messages in thread From: [email protected] @ 2022-04-27 10:08 UTC (permalink / raw) To: Amit Kapila <[email protected]>; Tomas Vondra <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]> On Wednesday, April 27, 2022 12:56 PM From: Amit Kapila <[email protected]> wrote: > On Tue, Apr 26, 2022 at 4:00 AM Tomas Vondra > <[email protected]> wrote: > > > > On 4/25/22 17:48, Alvaro Herrera wrote: > > > > > The desired result on subscriber is: > > > > > > table uno; > > > a │ b │ c > > > ────┼───┼─── > > > 1 │ 2 │ > > > -1 │ │ 4 > > > > > > > > > Thoughts? > > > > > > > I'm not quite sure which of the two behaviors is more "desirable". In a > > way, it's somewhat similar to publish_as_relid, which is also calculated > > not considering which of the row filters match? > > > > Right, or in other words, we check all publications to decide it and > similar is the case for publication actions which are also computed > independently for all publications. > > > But maybe you're right and it should behave the way you propose ... the > > example I have in mind is a use case replicating table with two types of > > rows - sensitive and non-sensitive. For sensitive, we replicate only > > some of the columns, for non-sensitive we replicate everything. Which > > could be implemented as two publications > > > > create publication sensitive_rows > > for table t (a, b) where (is_sensitive); > > > > create publication non_sensitive_rows > > for table t where (not is_sensitive); > > > > But the way it's implemented now, we'll always replicate all columns, > > because the second publication has no column list. > > > > Changing this to behave the way you expect would be quite difficult, > > because at the moment we build a single OR expression from all the row > > filters. We'd have to keep the individual expressions, so that we can > > build a column list for each of them (in order to ignore those that > > don't match). > > > > We'd have to remove various other optimizations - for example we can't > > just discard row filters if we found "no_filter" publication. > > > > I don't think that is the right way. We need some way to combine > expressions and I feel the current behavior is sane. I mean to say > that even if there is one publication that has no filter (column/row), > we should publish all rows with all columns. Now, as mentioned above > combining row filters or column lists for all publications appears to > be consistent with what we already do and seems correct behavior to > me. > > To me, it appears that the method used to decide whether a particular > table is published or not is also similar to what we do for row > filters or column lists. Even if there is one publication that > publishes all tables, we consider the current table to be published > irrespective of whether other publications have published that table > or not. > > > Or more > > precisely, we'd have to consider column lists too. > > > > In other words, we'd have to merge pgoutput_column_list_init into > > pgoutput_row_filter_init, and then modify pgoutput_row_filter to > > evaluate the row filters one by one, and build the column list. > > > > Hmm, I think even if we want to do something here, we also need to > think about how to achieve similar behavior for initial tablesync > which will be more tricky. I think it could be difficult to make the initial tablesync behave the same. Currently, we make a "COPY" command to do the table sync, I am not sure how to change the "COPY" query to achieve the expected behavior here. BTW, For the use case mentioned here: """ replicating table with two types of rows - sensitive and non-sensitive. For sensitive, we replicate only some of the columns, for non-sensitive we replicate everything. """ One approach to do this is to create two subscriptions and two publications which seems a workaround. ----- create publication uno for table uno (a, b) where (a > 0); create publication dos for table uno (a, c) where (a < 0); create subscription sub_uno connection 'port=55432 dbname=alvherre' publication uno; create subscription sub_dos connection 'port=55432 dbname=alvherre' publication dos; ----- Best regards, Hou zj ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-27 10:33 Amit Kapila <[email protected]> parent: Alvaro Herrera <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Amit Kapila @ 2022-04-27 10:33 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Pg Hackers <[email protected]> On Wed, Apr 27, 2022 at 3:13 PM Alvaro Herrera <[email protected]> wrote: > > On 2022-Apr-26, Tomas Vondra wrote: > > > I'm not quite sure which of the two behaviors is more "desirable". In a > > way, it's somewhat similar to publish_as_relid, which is also calculated > > not considering which of the row filters match? > > I grepped doc/src/sgml for `publish_as_relid` and found no hits, so > I suppose it's not a user-visible feature as such. > `publish_as_relid` is computed based on 'publish_via_partition_root' setting of publication which is a user-visible feature. > > But maybe you're right and it should behave the way you propose ... the > > example I have in mind is a use case replicating table with two types of > > rows - sensitive and non-sensitive. For sensitive, we replicate only > > some of the columns, for non-sensitive we replicate everything. > > Exactly. If we blindly publish row/column values that aren't in *any* > publications, this may lead to leaking protected values. > > > Changing this to behave the way you expect would be quite difficult, > > because at the moment we build a single OR expression from all the row > > filters. We'd have to keep the individual expressions, so that we can > > build a column list for each of them (in order to ignore those that > > don't match). > > I think we should do that, yeah. > This can hit the performance as we need to evaluate each expression for each row. > > I can take a stab at it, but it seems strange to not apply the same > > logic to evaluation of publish_as_relid. I wonder what Amit thinks about > > this, as he wrote the row filter stuff. > > By grepping publicationcmds.c, it seems that publish_as_relid refers to > the ancestor partitioned table that is used for column list and > rowfilter determination, when a partition is being published as part of > it. > Yeah, this is true when the corresponding publication has set 'publish_via_partition_root' as true. > I don't think these things are exactly parallel. > Currently, when the subscription has multiple publications, we combine the objects, and actions of those publications. It happens for 'publish_via_partition_root', publication actions, tables, column lists, or row filters. I think the whole design works on this idea even the initial table sync. I think it might need a major change (which I am not sure about at this stage) if we want to make the initial sync also behave similar to what you are proposing. I feel it would be much easier to create two different subscriptions as mentioned by Hou-San [1] for the case you are talking about if the user really needs something like that. > ... In fact I think they are quite orthogonal: probably you should be > able to publish a partitioned table in two publications, with different > rowfilters and different column lists (which can come from the > topmost partitioned table), and each partition should still work in the > way I describe above. > We consider the column lists or row filters for either the partition (on which the current operation is performed) or partitioned table based on 'publish_via_partition_root' parameter of publication. [1] - https://www.postgresql.org/message-id/OS0PR01MB5716B82315A067F1D78F247E94FA9%40OS0PR01MB5716.jpnprd0... -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-28 12:26 Peter Eisentraut <[email protected]> parent: Amit Kapila <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Peter Eisentraut @ 2022-04-28 12:26 UTC (permalink / raw) To: Amit Kapila <[email protected]>; Alvaro Herrera <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Pg Hackers <[email protected]> On 27.04.22 12:33, Amit Kapila wrote: > Currently, when the subscription has multiple publications, we combine > the objects, and actions of those publications. It happens for > 'publish_via_partition_root', publication actions, tables, column > lists, or row filters. I think the whole design works on this idea > even the initial table sync. I think it might need a major change > (which I am not sure about at this stage) if we want to make the > initial sync also behave similar to what you are proposing. If one publication says "publish if insert" and another publication says "publish if update", then the combination of that is clearly "publish if insert or update". Similarly, if one publication says "WHERE (foo)" and one says "WHERE (bar)", then the combination is "WHERE (foo OR bar)". But if one publication says "publish columns a and b if condition-X" and another publication says "publish columns a and c if not-condition-X", then the combination is clearly *not* "publish columns a, b, c if true". That is not logical, in the literal sense of that word. I wonder how we handle the combination of pub1: publish=insert WHERE (foo) pub2: publish=update WHERE (bar) I think it would be incorrect if the combination is pub1, pub2: publish=insert,update WHERE (foo OR bar). ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: bogus: logical replication rows/cols combinations @ 2022-04-28 17:30 Tomas Vondra <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Tomas Vondra @ 2022-04-28 17:30 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; Amit Kapila <[email protected]>; Alvaro Herrera <[email protected]>; +Cc: Pg Hackers <[email protected]> On 4/28/22 14:26, Peter Eisentraut wrote: > On 27.04.22 12:33, Amit Kapila wrote: >> Currently, when the subscription has multiple publications, we combine >> the objects, and actions of those publications. It happens for >> 'publish_via_partition_root', publication actions, tables, column >> lists, or row filters. I think the whole design works on this idea >> even the initial table sync. I think it might need a major change >> (which I am not sure about at this stage) if we want to make the >> initial sync also behave similar to what you are proposing. > > If one publication says "publish if insert" and another publication says > "publish if update", then the combination of that is clearly "publish if > insert or update". Similarly, if one publication says "WHERE (foo)" and > one says "WHERE (bar)", then the combination is "WHERE (foo OR bar)". > > But if one publication says "publish columns a and b if condition-X" and > another publication says "publish columns a and c if not-condition-X", > then the combination is clearly *not* "publish columns a, b, c if true". > That is not logical, in the literal sense of that word. > > I wonder how we handle the combination of > > pub1: publish=insert WHERE (foo) > pub2: publish=update WHERE (bar) > > I think it would be incorrect if the combination is > > pub1, pub2: publish=insert,update WHERE (foo OR bar). That's a good question, actually. No, we don't combine the publications like this, the row filters are kept "per action". But the exact behavior turns out to be rather confusing in this case. (Note: This has nothing to do with column lists.) Consider an example similar to what Alvaro posted earlier: create table uno (a int primary key, b int, c int); create publication uno for table uno where (a > 0) with (publish='insert'); create publication dos for table uno where (a < 0) with (publish='update'); And do this: insert into uno values (1, 2, 3), (-1, 3, 4) which on the subscriber produces just one row, because (a<0) replicates only updates: a | b | c ---+---+--- 1 | 2 | 3 (1 row) Now, let's update the (a<0) row. update uno set a = 2 where a = -1; It might seem reasonable to expect the updated row (2,3,4) to appear on the subscriber, but no - that's not what happens. Because we have (a<0) for UPDATE, and we evaluate this on the old row (matches) and new row (does not match). And pgoutput_row_filter() decides the update needs to be converted to DELETE, despite the old row was not replicated at all. I'm not sure if pgoutput_row_filter() can even make reasonable decisions with such configuration (combination of row filters, actions ...). But it sure seems confusing, because if you just inserted the updated row, it would get replicated. Which brings me to a second problem, related to this one. Imagine you create the subscription *after* inserting the two rows. In that case you get this: a | b | c ----+---+--- 1 | 2 | 3 -1 | 3 | 4 (2 rows) because tablesync.c ignores which actions is the publication (and thus the rowfilter) defined for. I think it's natural to expect that (INSERT + sync) and (sync + INSERT) produce the same output on the subscriber. I'm not sure we can actually make this perfectly sane with arbitrary combinations of filters and actions. It would probably depend on whether the actions are commutative, associative and stuff like that. But maybe we can come up with restrictions that'd make this sane? regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2022-04-28 17:30 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-02-06 22:10 [PATCH 1/1] In psql \copy from, send data to server in larger chunks. Heikki Linnakangas <[email protected]> 2022-04-25 22:30 Re: bogus: logical replication rows/cols combinations Tomas Vondra <[email protected]> 2022-04-27 04:55 ` Re: bogus: logical replication rows/cols combinations Amit Kapila <[email protected]> 2022-04-27 06:12 ` Re: bogus: logical replication rows/cols combinations Michael Paquier <[email protected]> 2022-04-27 10:08 ` RE: bogus: logical replication rows/cols combinations [email protected] <[email protected]> 2022-04-27 09:43 ` Re: bogus: logical replication rows/cols combinations Alvaro Herrera <[email protected]> 2022-04-27 10:33 ` Re: bogus: logical replication rows/cols combinations Amit Kapila <[email protected]> 2022-04-28 12:26 ` Re: bogus: logical replication rows/cols combinations Peter Eisentraut <[email protected]> 2022-04-28 17:30 ` Re: bogus: logical replication rows/cols combinations Tomas Vondra <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox