Re: logical decoding/replication: new functions pg_ls_logicaldir and pg_ls_replslotdir - Mailing list pgsql-hackers

On Sat, Oct 23, 2021 at 11:10 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Fri, Oct 22, 2021 at 9:26 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I concluded that it's better to add a function to list metadata of an arbitrary
> > dir, rather than adding more functions to handle specific, hardcoded dirs:
> > https://www.postgresql.org/message-id/flat/20191227170220.GE12890@telsasoft.com
>
> I just had a quick look at the pg_ls_dir_metadata() patch(I didn't
> look at the other patches). While it's a good idea to have a single
> function for all the PGDATA directories, I'm not sure if one would
> ever need the info like type, change, creation path etc. If we do
> this, the function will become the linux equivalent command. I don't
> see the difference between modification and change time stamps. For
> debugging or analytical purposes in production environments, one would
> majorly look at the file name, it's size on the disk, modification
> time (to decide whether the file is stale or not, creation time (to
> decide how old is the file),  file/directory(maybe?). I'm not sure if
> your patch has a recursive option for pg_ls_dir_metadata(), if it has,
> I think it's more complex from a usability perspective.
>
> And the functions like pg_ls_tmpdir, pg_ls_tmpdir, pg_ls_waldir etc.
> (existing) and pg_ls_logicaldir, pg_ls_replslotdir (yet to have) will
> provide the better usability compared to a generic function. Having
> said this, I don't oppose having a generic function returning the file
> name, file size, modification time, creation time, but not other info,
> please. If one is interested in knowing the other information file
> type, path etc. they can go run linux/windows/OS commands.
>
> In summary what I think at this point is:
> 1) pg_ls_logicaldir, pg_ls_replslotdir - better for usability and
> serving special purpose like their peers

I've added 3 functions pg_ls_logicalsnapdir, pg_ls_logicalmapdir,
pg_ls_replslotdir, and attached the patch. The sample output looks
like [1]. Please review it further.

Here's the CF entry - https://commitfest.postgresql.org/35/3390/

[1]
postgres=# select pg_ls_logicalsnapdir();
             pg_ls_logicalsnapdir
-----------------------------------------------
 (0-14A50C0.snap,128,"2021-10-30 09:15:56+00")
 (0-14C46D8.snap,128,"2021-10-30 09:16:05+00")
 (0-14C97C8.snap,132,"2021-10-30 09:16:20+00")

postgres=# select pg_ls_logicalmapdir();
                      pg_ls_logicalmapdir
---------------------------------------------------------------
 (map-31d5-4eb-0_CDDDE88-2d9-2db,108,"2021-10-30 09:24:34+00")
 (map-31d5-4eb-0_CDDDE88-2da-2db,108,"2021-10-30 09:24:34+00")
 (map-31d5-4eb-0_CE48038-2dc-2de,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE6BAF0-2dd-2df,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CD97DE0-2d9-2d9,36,"2021-10-30 09:24:30+00")
 (map-31d5-4eb-0_CE24808-2da-2dd,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE01200-2dc-2dc,36,"2021-10-30 09:24:34+00")
 (map-31d5-4eb-0_CDDDE88-2db-2db,36,"2021-10-30 09:24:34+00")
 (map-31d5-4eb-0_CE6BAF0-2dc-2df,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CDBA920-2d9-2da,108,"2021-10-30 09:24:32+00")
 (map-31d5-4eb-0_CE01200-2da-2dc,108,"2021-10-30 09:24:34+00")
 (map-31d5-4eb-0_CE6BAF0-2d9-2df,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE24808-2db-2dd,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE6BAF0-2db-2df,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE24808-2dd-2dd,36,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CE24808-2dc-2dd,108,"2021-10-30 09:24:35+00")
 (map-31d5-4eb-0_CD74E48-2d8-2d8,36,"2021-10-30 09:24:25+00")
 (map-31d5-4eb-0_CE24808-2d9-2dd,108,"2021-10-30 09:24:35+00")

 postgres=# select pg_ls_replslotdir('mysub');
                        pg_ls_replslotdir
-----------------------------------------------------------------
 (xid-722-lsn-0-2000000.spill,36592640,"2021-10-30 09:18:29+00")
 (xid-722-lsn-0-5000000.spill,4577860,"2021-10-30 09:18:32+00")
 (state,200,"2021-10-30 09:18:25+00")
 (xid-722-lsn-0-1000000.spill,25644220,"2021-10-30 09:18:29+00")
 (xid-722-lsn-0-4000000.spill,36592640,"2021-10-30 09:18:32+00")
 (xid-722-lsn-0-3000000.spill,36592640,"2021-10-30 09:18:32+00")

Regards,
Bharath Rupireddy.