Thread: The pg_stop_backup will return one row with three values.

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/14/continuous-archiving.html
Description:

It appears that this may be out of date.

pg_stop_backup
---------------------------------------------------------------------------
 (0/2000138,"START WAL LOCATION: 0/2000028 (file
000000010000000000000002)+
 CHECKPOINT LOCATION: 0/2000060
+
 BACKUP METHOD: streamed
+
 BACKUP FROM: primary
+
 START TIME: 2021-12-19 13:39:19 EST
+
 LABEL: label
+
 START TIMELINE: 1
+
 ","")


Seems to be returning 7 values ?

Dave

On Sun, 2021-12-19 at 21:08 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/14/continuous-archiving.html
> Description:
> 
> It appears that this may be out of date.
> 
> pg_stop_backup
> ---------------------------------------------------------------------------
>  (0/2000138,"START WAL LOCATION: 0/2000028 (file 000000010000000000000002)+
>  CHECKPOINT LOCATION: 0/2000060                                           +
>  BACKUP METHOD: streamed                                                  +
>  BACKUP FROM: primary                                                     +
>  START TIME: 2021-12-19 13:39:19 EST                                      +
>  LABEL: label                                                             +
>  START TIMELINE: 1                                                        +
>  ","")
> 
> 
> Seems to be returning 7 values ?

No, that is one value with 7 newlines in it.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Mon, Dec 20, 2021 at 07:19:32AM -0500, Dave Cramer wrote:
> The docs say it returns 3 values ?

They do.  Here is the relevant part:
https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
"The result of the function is a single record. The lsn column holds
the backup's ending write-ahead log location (which again can be
ignored). The second and third columns are NULL when ending an
exclusive backup; after a non-exclusive backup they hold the desired
contents of the label and tablespace map files."

For exclusive backups, where pg_stop_backup() is used without an
argument, 1 row is returned with the LSN marking the end of the
backup.  When pg_stop_backup(true) is used, you would get one row with
three attributes: the end LSN, the label file as NULL and a tablespace
map as NULL.

For non-exclusive backups, pg_stop_backup(false) returns those three
fields, all of them being not NULL.  pg_stop_backup() without an
argument cannot be used for non-exclusive backups.
--
Michael

On Mon, Dec 20, 2021 at 8:19 PM Dave Cramer <davecramer@postgres.rocks> wrote:
>
> On Mon, 20 Dec 2021 at 07:03, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>>
>> On Sun, 2021-12-19 at 21:08 +0000, PG Doc comments form wrote:
>> > Page: https://www.postgresql.org/docs/14/continuous-archiving.html
>> > Description:
>> >
>> > It appears that this may be out of date.
>> >
>> > pg_stop_backup
>> > ---------------------------------------------------------------------------
>> >  (0/2000138,"START WAL LOCATION: 0/2000028 (file 000000010000000000000002)+
>> >  CHECKPOINT LOCATION: 0/2000060                                           +
>> >  BACKUP METHOD: streamed                                                  +
>> >  BACKUP FROM: primary                                                     +
>> >  START TIME: 2021-12-19 13:39:19 EST                                      +
>> >  LABEL: label                                                             +
>> >  START TIMELINE: 1                                                        +
>> >  ","")
>> >
>> >
>> > Seems to be returning 7 values ?
>>
>> No, that is one value with 7 newlines in it.
>>
>
> The docs say it returns 3 values ?

It's 3 values, one of them containing a lot of newlines.  To make it clearer:

=# select (pg_stop_backup(false, false)).*;
    lsn    |                           labelfile
    | spcmapfile
-----------+---------------------------------------------------------------+------------
 0/9000138 | START WAL LOCATION: 0/9000028 (file 000000010000000000000009)+|
           | CHECKPOINT LOCATION: 0/9000060                               +|
           | BACKUP METHOD: streamed                                      +|
           | BACKUP FROM: primary                                         +|
           | START TIME: 2021-12-20 21:11:10 CST                          +|
           | LABEL: meh                                                   +|
           | START TIMELINE: 1                                            +|
           |                                                               |
(1 row)