public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/1] In psql \copy from, send data to server in larger chunks. 4+ messages / 3 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; 4+ 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] 4+ messages in thread
* Re: Switching XLog source from archive to streaming when primary available @ 2023-01-17 14:14 Bharath Rupireddy <[email protected]> 2023-01-19 00:50 ` Re: Switching XLog source from archive to streaming when primary available Nathan Bossart <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Bharath Rupireddy @ 2023-01-17 14:14 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; Cary Huang <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> On Thu, Jan 12, 2023 at 6:21 AM Nathan Bossart <[email protected]> wrote: > > On Tue, Oct 18, 2022 at 07:31:33AM +0530, Bharath Rupireddy wrote: > > In summary, the standby state machine in WaitForWALToBecomeAvailable() > > exhausts all the WAL in pg_wal before switching to streaming after > > failing to fetch from archive. The v8 patch proposed upthread deviates > > from this behaviour. Hence, attaching v9 patch that keeps the > > behaviour as-is, that means, the standby exhausts all the WAL in > > pg_wal before switching to streaming after fetching WAL from archive > > for at least streaming_replication_retry_interval milliseconds. > > I think this is okay. The following comment explains why archives are > preferred over existing files in pg_wal: > > * When doing archive recovery, we always prefer an archived log file even > * if a file of the same name exists in XLOGDIR. The reason is that the > * file in XLOGDIR could be an old, un-filled or partly-filled version > * that was copied and restored as part of backing up $PGDATA. > > With your patch, we might replay one of these "old" files in pg_wal instead > of the complete version of the file from the archives, That's true even today, without the patch, no? We're not changing the existing behaviour of the state machine. Can you explain how it happens with the patch? On HEAD, after failing to read from the archive, exhaust all wal from pg_wal and then switch to streaming mode. With the patch, after reading from the archive for at least streaming_replication_retry_interval milliseconds, exhaust all wal from pg_wal and then switch to streaming mode. > but I think that is > still correct. We'll just replay whatever exists in pg_wal (which may be > un-filled or partly-filled) before attempting streaming. If that fails, > we'll go back to trying the archives again. > > Would you mind testing this scenario? How about something like below for testing the above scenario? If it looks okay, I can add it as a new TAP test file. 1. Generate WAL files f1 and f2 and archive them. 2. Check the replay lsn and WAL file name on the standby, when it replays upto f2, stop the standby. 3. Set recovery to fail on the standby, and stop the standby. 4. Generate f3, f4 (partially filled) on the primary. 5. Manually copy f3, f4 to the standby's pg_wal. 6. Start the standby, since recovery is set to fail, and there're new WAL files (f3, f4) under its pg_wal, it must replay those WAL files (check the replay lsn and WAL file name, it must be f4) before switching to streaming. 7. Generate f5 on the primary. 8. The standby should receive f5 and replay it (check the replay lsn and WAL file name, it must be f5). 9. Set streaming to fail on the standby and set recovery to succeed. 10. Generate f6 on the primary. 11. The standby should receive f6 via archive and replay it (check the replay lsn and WAL file name, it must be f6). If needed, we can look out for these messages to confirm it works as expected: elog(DEBUG2, "switched WAL source from %s to %s after %s", xlogSourceNames[oldSource], xlogSourceNames[currentSource], lastSourceFailed ? "failure" : "success"); ereport(LOG, (errmsg("restored log file \"%s\" from archive", xlogfname))); Essentially, it covers what the documentation https://www.postgresql.org/docs/devel/warm-standby.html says: "In standby mode, the server continuously applies WAL received from the primary server. The standby server can read WAL from a WAL archive (see restore_command) or directly from the primary over a TCP connection (streaming replication). The standby server will also attempt to restore any WAL found in the standby cluster's pg_wal directory. That typically happens after a server restart, when the standby replays again WAL that was streamed from the primary before the restart, but you can also manually copy files to pg_wal at any time to have them replayed." Thoughts? -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Switching XLog source from archive to streaming when primary available 2023-01-17 14:14 Re: Switching XLog source from archive to streaming when primary available Bharath Rupireddy <[email protected]> @ 2023-01-19 00:50 ` Nathan Bossart <[email protected]> 2023-01-21 05:43 ` Re: Switching XLog source from archive to streaming when primary available Bharath Rupireddy <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Nathan Bossart @ 2023-01-19 00:50 UTC (permalink / raw) To: Bharath Rupireddy <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; Cary Huang <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> On Tue, Jan 17, 2023 at 07:44:52PM +0530, Bharath Rupireddy wrote: > On Thu, Jan 12, 2023 at 6:21 AM Nathan Bossart <[email protected]> wrote: >> With your patch, we might replay one of these "old" files in pg_wal instead >> of the complete version of the file from the archives, > > That's true even today, without the patch, no? We're not changing the > existing behaviour of the state machine. Can you explain how it > happens with the patch? My point is that on HEAD, we will always prefer a complete archive file. With your patch, we might instead choose to replay an old file in pg_wal because we are artificially advancing the state machine. IOW even if there's a complete archive available, we might not use it. This is a behavior change, but I think it is okay. >> Would you mind testing this scenario? > > How about something like below for testing the above scenario? If it > looks okay, I can add it as a new TAP test file. > > 1. Generate WAL files f1 and f2 and archive them. > 2. Check the replay lsn and WAL file name on the standby, when it > replays upto f2, stop the standby. > 3. Set recovery to fail on the standby, and stop the standby. > 4. Generate f3, f4 (partially filled) on the primary. > 5. Manually copy f3, f4 to the standby's pg_wal. > 6. Start the standby, since recovery is set to fail, and there're new > WAL files (f3, f4) under its pg_wal, it must replay those WAL files > (check the replay lsn and WAL file name, it must be f4) before > switching to streaming. > 7. Generate f5 on the primary. > 8. The standby should receive f5 and replay it (check the replay lsn > and WAL file name, it must be f5). > 9. Set streaming to fail on the standby and set recovery to succeed. > 10. Generate f6 on the primary. > 11. The standby should receive f6 via archive and replay it (check the > replay lsn and WAL file name, it must be f6). I meant testing the scenario where there's an old file in pg_wal, a complete file in the archives, and your new GUC forces replay of the former. This might be difficult to do in a TAP test. Ultimately, I just want to validate the assumptions discussed above. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Switching XLog source from archive to streaming when primary available 2023-01-17 14:14 Re: Switching XLog source from archive to streaming when primary available Bharath Rupireddy <[email protected]> 2023-01-19 00:50 ` Re: Switching XLog source from archive to streaming when primary available Nathan Bossart <[email protected]> @ 2023-01-21 05:43 ` Bharath Rupireddy <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Bharath Rupireddy @ 2023-01-21 05:43 UTC (permalink / raw) To: Nathan Bossart <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; Cary Huang <[email protected]>; PostgreSQL Hackers <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]> On Thu, Jan 19, 2023 at 6:20 AM Nathan Bossart <[email protected]> wrote: > > On Tue, Jan 17, 2023 at 07:44:52PM +0530, Bharath Rupireddy wrote: > > On Thu, Jan 12, 2023 at 6:21 AM Nathan Bossart <[email protected]> wrote: > >> With your patch, we might replay one of these "old" files in pg_wal instead > >> of the complete version of the file from the archives, > > > > That's true even today, without the patch, no? We're not changing the > > existing behaviour of the state machine. Can you explain how it > > happens with the patch? > > My point is that on HEAD, we will always prefer a complete archive file. > With your patch, we might instead choose to replay an old file in pg_wal > because we are artificially advancing the state machine. IOW even if > there's a complete archive available, we might not use it. This is a > behavior change, but I think it is okay. Oh, yeah, I too agree that it's okay because manually copying WAL files directly to pg_wal (which eventually get replayed before switching to streaming) isn't recommended anyway for production level servers. I think, we covered it in the documentation that it exhausts all the WAL present in pg_wal before switching. Isn't that enough? + Specifies amount of time after which standby attempts to switch WAL + source from WAL archive to streaming replication (get WAL from + primary). However, exhaust all the WAL present in pg_wal before + switching. If the standby fails to switch to stream mode, it falls + back to archive mode. > >> Would you mind testing this scenario? ndby should receive f6 via archive and replay it (check the > > replay lsn an> > > > I meant testing the scenario where there's an old file in pg_wal, a > complete file in the archives, and your new GUC forces replay of the > former. This might be difficult to do in a TAP test. Ultimately, I just > want to validate the assumptions discussed above. I think testing the scenario [1] is achievable. I could write a TAP test for it - https://github.com/BRupireddy/postgres/tree/prefer_archived_wal_v1. It's a bit flaky and needs a little more work (1 - writing a custom script for restore_command that sleeps only after fetching an existing WAL file from archive, not sleeping for a history file or a non-existent WAL file. 2- finding a command-line way to sleep on Windows.) to stabilize it, but it seems doable. I can spend some more time, if one thinks that the test is worth adding to the core, perhaps discussing it separately from this thread. [1] RestoreArchivedFile(): /* * When doing archive recovery, we always prefer an archived log file even * if a file of the same name exists in XLOGDIR. The reason is that the * file in XLOGDIR could be an old, un-filled or partly-filled version * that was copied and restored as part of backing up $PGDATA. * -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-21 05:43 UTC | newest] Thread overview: 4+ 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]> 2023-01-17 14:14 Re: Switching XLog source from archive to streaming when primary available Bharath Rupireddy <[email protected]> 2023-01-19 00:50 ` Re: Switching XLog source from archive to streaming when primary available Nathan Bossart <[email protected]> 2023-01-21 05:43 ` Re: Switching XLog source from archive to streaming when primary available Bharath Rupireddy <[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