How to optimize PostgreSQL Row Security Policies that involve related tables?

[David Ventimiglia]

Hello!

What are some good ways to create Row Security Policies that restrict rows
in one table using a filter on a related table, and get good performance?
It's difficult to describe but let me try to explain.

I'm using the venerable old Chinook database
.  Among its tables are
Artist, Album, and Track.  Track has a foreign key constraint on
Track.AlbumId, and Album has a foreign key constraint on Album.ArtistId.
Albums belong to Artists while Tracks belong to Albums.  I want to have two
policies:

   1. Restrict access to Album based on Album.ArtistId =
   current_setting('rls.artistId'), supposing that 'rls.artistId' will be set
   to the current artist's valid artistId.
   2. Restrict access to Track such that the current artist only sees
   tracks on their own albums and not on anybody else's albums.

One challenge is that the Track table doesn't actually have ArtistId
(naturally).  However, if we know there's a valid policy on Album, then we
should be able to leverage that in a policy on Track.  The trouble is, I
can't do a join in the Track policy (or at least, I haven't figured out how
to do a join), so I have to resort to a "exists" check.  Specifically, the
two policies look like this:

create policy artist_rls_policy ON "Album" for select to public using
("ArtistId"=(current_setting('rls.artistID'))::integer);

create policy album_rls_policy on "Track" for select to public
  using (
exists (select * from "Album" where "Album"."AlbumId" = "AlbumId") --
THIS IS THE TROUBLESOME POLICY CHECK HERE
);

But, the presence of the "exists" check leads to a suboptimal plan:

explain analyze select * from "Track";

 QUERY PLAN

-
 Seq Scan on "Track"  (cost=8.17..203181.49 rows=5001816 width=56) (actual
time=4.631..699.831 rows=10003504 loops=1)
   Filter: $0
   InitPlan 1 (returns $0)
 ->  Index Scan using "IFK_AlbumArtistId" on "Album"  (cost=0.15..8.17
rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)
   Index Cond: ("ArtistId" =
(current_setting('rls.artistID'::text))::integer)
   Filter: ("AlbumId" IS NOT NULL)
 Planning Time: 0.209 ms
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deforming
true
   Timing: Generation 0.673 ms, Inlining 0.000 ms, Optimization 0.391 ms,
Emission 3.793 ms, Total 4.857 ms
 Execution Time: 876.035 ms
(12 rows)

In my query, sure, I can explicitly join Track to Album to get a better
plan:

explain analyze select * from "Track" natural join "Album";

  QUERY
PLAN
---
 Nested Loop  (cost=8.75..37450.90 rows=14414 width=83) (actual
time=0.184..0.314 rows=14 loops=1)
   InitPlan 1 (returns $0)
 ->  Index Scan using "IFK_AlbumArtistId" on "Album" "Album_1"
 (cost=0.15..8.17 rows=1 width=0) (actual time=0.033..0.034 rows=1 loops=1)
   Index Cond: ("ArtistId" =
(current_setting('rls.artistID'::text))::integer)
   Filter: ("AlbumId" IS NOT NULL)
   ->  Index Scan using "PK_Album" on "Album"  (cost=0.15..22.82 rows=1
width=31) (actual time=0.136..0.252 rows=1 loops=1)
 Filter: ("ArtistId" =
(current_setting('rls.artistID'::text))::integer)
 Rows Removed by Filter: 346
   ->  Index Scan using "IFK_TrackAlbumId" on "Track"  (cost=0.43..32418.09
rows=500182 width=56) (actual time=0.044..0.051 rows=14 loops=1)
 Index Cond: ("AlbumId" = "Album"."AlbumId")
 Filter: $0
 Planning Time: 0.509 ms
 Execution Time: 0.364 ms
(13 rows)

But, that's redundant since there's enough information to process the query
correctly (albeit more slowly) without the join.  The question is, how if
at all can I create the policies to have both the desired composability and
also good performance?  I hope I've explained this well enough. I asked
this question a while back on StackOverflow

but
got no interest.  I also have sample code in a public GitHub repository here

that
illustrates the setup.  Any advice would be greatly appreciated.  Thank you!

Kind regards,
David A. Ventimiglia

Re: How to optimize PostgreSQL Row Security Policies that involve related tables?

[David Ventimiglia]

Sorry, I had a typo in my Row Policy.  Rather, it should look like this:

create policy album_rls_policy on "Track" for select to public
  using (
exists (select * from "Album" where "Album"."AlbumId" =
"Track"."AlbumId")
  );




On Mon, Aug 14, 2023 at 9:36 AM David Ventimiglia <
davidaventimig...@hasura.io> wrote:

> Hello!
>
> What are some good ways to create Row Security Policies that restrict rows
> in one table using a filter on a related table, and get good performance?
> It's difficult to describe but let me try to explain.
>
> I'm using the venerable old Chinook database
> <https://github.com/cwoodruff/ChinookDatabase>.  Among its tables are
> Artist, Album, and Track.  Track has a foreign key constraint on
> Track.AlbumId, and Album has a foreign key constraint on Album.ArtistId.
> Albums belong to Artists while Tracks belong to Albums.  I want to have two
> policies:
>
>1. Restrict access to Album based on Album.ArtistId =
>current_setting('rls.artistId'), supposing that 'rls.artistId' will be set
>to the current artist's valid artistId.
>2. Restrict access to Track such that the current artist only sees
>tracks on their own albums and not on anybody else's albums.
>
> One challenge is that the Track table doesn't actually have ArtistId
> (naturally).  However, if we know there's a valid policy on Album, then we
> should be able to leverage that in a policy on Track.  The trouble is, I
> can't do a join in the Track policy (or at least, I haven't figured out how
> to do a join), so I have to resort to a "exists" check.  Specifically, the
> two policies look like this:
>
> create policy artist_rls_policy ON "Album" for select to public using
> ("ArtistId"=(current_setting('rls.artistID'))::integer);
>
> create policy album_rls_policy on "Track" for select to public
>   using (
> exists (select * from "Album" where "Album"."AlbumId" = "AlbumId") --
> THIS IS THE TROUBLESOME POLICY CHECK HERE
> );
>
> But, the presence of the "exists" check leads to a suboptimal plan:
>
> explain analyze select * from "Track";
>
>  QUERY PLAN
>
>
> -
>  Seq Scan on "Track"  (cost=8.17..203181.49 rows=5001816 width=56) (actual
> time=4.631..699.831 rows=10003504 loops=1)
>Filter: $0
>InitPlan 1 (returns $0)
>  ->  Index Scan using "IFK_AlbumArtistId" on "Album"  (cost=0.15..8.17
> rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=1)
>Index Cond: ("ArtistId" =
> (current_setting('rls.artistID'::text))::integer)
>Filter: ("AlbumId" IS NOT NULL)
>  Planning Time: 0.209 ms
>  JIT:
>Functions: 7
>Options: Inlining false, Optimization false, Expressions true,
> Deforming true
>Timing: Generation 0.673 ms, Inlining 0.000 ms, Optimization 0.391 ms,
> Emission 3.793 ms, Total 4.857 ms
>  Execution Time: 876.035 ms
> (12 rows)
>
> In my query, sure, I can explicitly join Track to Album to get a better
> plan:
>
> explain analyze select * from "Track" natural join "Album";
>
>   QUERY
> PLAN
>
> ---
>  Nested Loop  (cost=8.75..37450.90 rows=14414 width=83) (actual
> time=0.184..0.314 rows=14 loops=1)
>InitPlan 1 (returns $0)
>  ->  Index Scan using "IFK_AlbumArtistId" on "Album" "Album_1"
>  (cost=0.15..8.17 rows=1 width=0) (actual time=0.033..0.034 rows=1 loops=1)
>Index Cond: ("ArtistId" =
> (current_setting('rls.artistID'::text))::integer)
>Filter: ("AlbumId" IS NOT NULL)
>->  Index Scan using "PK_Album" on "Album"  (cost=0.15..22.82 rows=1
> width=31) (actual time=0.136..0.252 rows=1 loops=1)
>  Filter: ("ArtistId" =
> (current_setting('rls.artistID'::text))::integer)
>  Rows Removed by Filter: 346
>->  Index Scan using "IFK_TrackAlbumId" on "Track"
>  (cost=0.43..32418.09 rows=500182 width=56) (actual time=0.044..0.051
> rows=14 loops=1)
>  Index Cond: ("AlbumId" = "

Help with a good mental model for estimating PostgreSQL throughput

[David Ventimiglia]

Hello!

Can someone help me develop a good mental model for estimating PostgreSQL
throughput?  Here's what I mean.  Suppose I have:

   - 1000 connections
   - typical query execution time of 1ms
   - but additional network latency of 100ms

What if at all would be an estimate of the number of operations that can be
performed within 1 second?  My initial guess would be ~1, but then
perhaps I'm overlooking something.  I expect a more reliable figure would
be obtained through testing, but I'm looking for an *a priori
*back-of-the-envelope
estimate.  Thanks!

Best,
David

Re: Help with a good mental model for estimating PostgreSQL throughput

[David Ventimiglia]

Thanks!  Let's say there are 10 cores, the workload is not CPU bound, and
there is a connection pooler like pgBouncer in place.  Would the number of
operations more likely be:

   - 1000 ms / total ms per operation * number of cores = 1000 ms / 101 ms
   * 10 = ~100
   - 1000 ms / total ms per operation * number of connections = 1000 ms /
   101 ms * 1000 = ~1
   - something else
   - impossible to determine without more information

Best,
David

On Mon, Oct 30, 2023 at 8:46 AM Laurenz Albe 
wrote:

> On Mon, 2023-10-30 at 08:05 -0700, David Ventimiglia wrote:
> > Can someone help me develop a good mental model for estimating
> PostgreSQL throughput?
> > Here's what I mean.  Suppose I have:
> >  * 1000 connections
> >  * typical query execution time of 1ms
> >  * but additional network latency of 100ms
> > What if at all would be an estimate of the number of operations that can
> be performed
> > within 1 second?  My initial guess would be ~1, but then perhaps I'm
> overlooking
> > something.  I expect a more reliable figure would be obtained through
> testing, but
> > I'm looking for an a priori back-of-the-envelope estimate.  Thanks!
>
> It depends on the number of cores, if the workload is CPU bound.
> If the workload is disk bound, look for the number of I/O requests a
> typical query
> needs, and how many of them you can perform per second.
>
> The network latency might well be a killer.
>
> Use pgBouncer with transaction mode pooling.
>
> Yours,
> Laurenz Albe
>

How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

Hello! How do I redirect logical decoding output from the PostgreSQL CLI
tool pg_recvlogical either to a file or to another command via a pipe? I
ask because when I try the obvious, no output is recorded or sent:


pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl


Lest there be any confusion, I already created the slot in an earlier step.
Moreover, I can verify that if I omit the output redirection >> sample then
it does work, insofar as it emits the expected change events when I perform
DML in another terminal window. When I include the redirection (or
alternatively, set up a pipeline), then nothing happens.

Note that I am aware of the option to pass a filename to the -f switch to
write to a file.  That works, but it's not what I'm after because it
doesn't help update my mental model of how this is supposed to work.  Based
on my current (flawed) mental model built up from command line experience
with other tools, this *should* work.  I should be able to send the output
to stdout and then redirect it to a file.  It surprises me that I cannot.

Anyway, thanks!

Best,

David

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

Let me just lay my cards on the table.  What I'm really trying to do is
capture change events with logical decoding and then send them back into
the database into a database table.  To do that, I believe I need to
process the event records into SQL insert statements somehow.  xargs is one
option.  jq is another.  My idea was to pipe the pg_recvlogical output
through a jq transform into psql, but that didn't work (neither did earlier
experiments with xargs).  Redirecting the output to an intermediate file
via stdout was just an attempt to reduce the problem to a simpler problem.
I had *thought* (incorrectly, as it turns out) that I was unable even to
redirect it to a file, but evidently that's not the case.  I can redirect
it to a file.  What I cannot seem to do is run it through a jq filter and
pipe it back into psql.  I can run it through a jq filter and redirect it
to a file, no problem.  But the minute I change it to pipe to psql, it
ceases to produce the desired result.

I tried illustrating this in this screencast:

https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y

Perhaps another way to put this is, how *does* one capture output from
pg_recvlogical and pipe it back into the database (or if you like, some
other database) with psql.  When I set out to do this I didn't think bash
pipes and redirection would be the hard part, and yet here I am.  Maybe
there's some other way, because I'm fresh out of ideas.

Best,
David

On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella <
rodrigoburgosme...@gmail.com> wrote:

>
> try use the following syntax (yes, with a 2 before the greater sign)
>
> pg_recvlogical -d postgres --slot test --start -f - 2>> sample.jsonl
>
> Atte
> JRBM
>
> El vie, 12 ene 2024 a las 16:35, David Ventimiglia (<
> davidaventimig...@hasura.io>) escribió:
>
>> Hello! How do I redirect logical decoding output from the PostgreSQL CLI
>> tool pg_recvlogical either to a file or to another command via a pipe? I
>> ask because when I try the obvious, no output is recorded or sent:
>>
>>
>> pg_recvlogical -d postgres --slot test --start -f - >> sample.jsonl
>>
>>
>> Lest there be any confusion, I already created the slot in an earlier
>> step. Moreover, I can verify that if I omit the output redirection >>
>> sample then it does work, insofar as it emits the expected change events
>> when I perform DML in another terminal window. When I include the
>> redirection (or alternatively, set up a pipeline), then nothing happens.
>>
>> Note that I am aware of the option to pass a filename to the -f switch to
>> write to a file.  That works, but it's not what I'm after because it
>> doesn't help update my mental model of how this is supposed to work.  Based
>> on my current (flawed) mental model built up from command line experience
>> with other tools, this *should* work.  I should be able to send the
>> output to stdout and then redirect it to a file.  It surprises me that I
>> cannot.
>>
>> Anyway, thanks!
>>
>> Best,
>>
>> David
>>
>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

Thanks.  I'm aware of all of those other alternatives, but the thing is,
I'm not trying to answer this broader question:

*"What are some options for capturing change events in PostgreSQL?"*

Rather, I'm trying to answer a narrower question:

*"How does one capture output from pg_recvlogical and pipe it back into the
database with psql?"*

Best,
David

On Sat, Jan 13, 2024 at 10:29 AM Adrian Klaver 
wrote:

> On 1/12/24 21:23, David Ventimiglia wrote:
> > Let me just lay my cards on the table.  What I'm really trying to do is
> > capture change events with logical decoding and then send them back into
> > the database into a database table.  To do that, I believe I need to
> > process the event records into SQL insert statements somehow.  xargs is
> > one option.  jq is another.  My idea was to pipe the pg_recvlogical
> > output through a jq transform into psql, but that didn't work (neither
> > did earlier experiments with xargs).  Redirecting the output to an
> > intermediate file via stdout was just an attempt to reduce the problem
> > to a simpler problem.  I had /thought/ (incorrectly, as it turns out)
> > that I was unable even to redirect it to a file, but evidently that's
> > not the case.  I can redirect it to a file.  What I cannot seem to do is
> > run it through a jq filter and pipe it back into psql.  I can run it
> > through a jq filter and redirect it to a file, no problem.  But the
> > minute I change it to pipe to psql, it ceases to produce the desired
> result.
> >
> > I tried illustrating this in this screencast:
> >
> > https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y
> > <https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y>
> >
> > Perhaps another way to put this is, how /does/ one capture output from
> > pg_recvlogical and pipe it back into the database (or if you like, some
> > other database) with psql.  When I set out to do this I didn't think
> > bash pipes and redirection would be the hard part, and yet here I am.
> > Maybe there's some other way, because I'm fresh out of ideas.
>
> This is going to depend a lot on what you define as a change event. Is
> that DDL changes or data changes or both?
>
> Some existing solutions that cover the above to a one degree or another:
>
> Event triggers:
>
> https://www.postgresql.org/docs/current/event-triggers.html
>
> PGAudit
>
> https://github.com/pgaudit/pgaudit/blob/master/README.md
>
> Or since you are part of the way there already just using logical
> replication entirely:
>
> https://www.postgresql.org/docs/current/logical-replication.html
>
>
> >
> > Best,
> > David
> >
> > On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella
> > mailto:rodrigoburgosme...@gmail.com>>
> wrote:
> >
> >
> > try use the following syntax (yes, with a 2 before the greater sign)
> >
> > pg_recvlogical -d postgres --slot test --start -f - 2>> sample.jsonl
> >
> > Atte
> > JRBM
> >
> > El vie, 12 ene 2024 a las 16:35, David Ventimiglia
> > (mailto:davidaventimig...@hasura.io>>)
> > escribió:
> >
> > Hello! How do I redirect logical decoding output from the
> > PostgreSQL CLI tool |pg_recvlogical| either to a file or to
> > another command via a pipe? I ask because when I try the
> > obvious, no output is recorded or sent:
> >
> > |pg_recvlogical -d postgres --slot test --start -f - >>
> > sample.jsonl |
> >
> > Lest there be any confusion, I already created the slot in an
> > earlier step. Moreover, I can verify that if I omit the output
> > redirection |>> sample| then it does work, insofar as it emits
> > the expected change events when I perform DML in another
> > terminal window. When I include the redirection (or
> > alternatively, set up a pipeline), then nothing happens.
> >
> > Note that I am aware of the option to pass a filename to the -f
> > switch to write to a file.  That works, but it's not what I'm
> > after because it doesn't help update my mental model of how this
> > is supposed to work.  Based on my current (flawed) mental model
> > built up from command line experience with other tools, this
> > /should/ work.  I should be able to send the output to stdout
> > and then redirect it to a file.  It surprises me that I cannot.
> >
> > Anyway, thanks!
> >
> > Best,
> >
> > David
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

The business problem I'm trying to solve is:

"How do I capture logical decoding events with the wal2json output encoder,
filter them with jq, and pipe them to psql, using pg_recvlogical?"

On Sat, Jan 13, 2024, 1:04 PM Ron Johnson  wrote:

> I think this might be an A-B problem.  Tell us the "business problem" you
> are trying to solve, not the problem you're having with your solution to
> the "business problem".
>
> (If you've already mentioned it, please restate it.)
>
> On Sat, Jan 13, 2024 at 11:49 AM David Ventimiglia <
> davidaventimig...@hasura.io> wrote:
>
>> Thanks.  I'm aware of all of those other alternatives, but the thing is,
>> I'm not trying to answer this broader question:
>>
>> *"What are some options for capturing change events in PostgreSQL?"*
>>
>> Rather, I'm trying to answer a narrower question:
>>
>> *"How does one capture output from pg_recvlogical and pipe it back into
>> the database with psql?"*
>>
>> Best,
>> David
>>
>> On Sat, Jan 13, 2024 at 10:29 AM Adrian Klaver 
>> wrote:
>>
>>> On 1/12/24 21:23, David Ventimiglia wrote:
>>> > Let me just lay my cards on the table.  What I'm really trying to do
>>> is
>>> > capture change events with logical decoding and then send them back
>>> into
>>> > the database into a database table.  To do that, I believe I need to
>>> > process the event records into SQL insert statements somehow.  xargs
>>> is
>>> > one option.  jq is another.  My idea was to pipe the pg_recvlogical
>>> > output through a jq transform into psql, but that didn't work (neither
>>> > did earlier experiments with xargs).  Redirecting the output to an
>>> > intermediate file via stdout was just an attempt to reduce the problem
>>> > to a simpler problem.  I had /thought/ (incorrectly, as it turns out)
>>> > that I was unable even to redirect it to a file, but evidently that's
>>> > not the case.  I can redirect it to a file.  What I cannot seem to do
>>> is
>>> > run it through a jq filter and pipe it back into psql.  I can run it
>>> > through a jq filter and redirect it to a file, no problem.  But the
>>> > minute I change it to pipe to psql, it ceases to produce the desired
>>> result.
>>> >
>>> > I tried illustrating this in this screencast:
>>> >
>>> > https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y
>>> > <https://asciinema.org/a/npzgcTN8DDjUdkaZlVyYJhZ5y>
>>> >
>>> > Perhaps another way to put this is, how /does/ one capture output from
>>> > pg_recvlogical and pipe it back into the database (or if you like,
>>> some
>>> > other database) with psql.  When I set out to do this I didn't think
>>> > bash pipes and redirection would be the hard part, and yet here I am.
>>> > Maybe there's some other way, because I'm fresh out of ideas.
>>>
>>> This is going to depend a lot on what you define as a change event. Is
>>> that DDL changes or data changes or both?
>>>
>>> Some existing solutions that cover the above to a one degree or another:
>>>
>>> Event triggers:
>>>
>>> https://www.postgresql.org/docs/current/event-triggers.html
>>>
>>> PGAudit
>>>
>>> https://github.com/pgaudit/pgaudit/blob/master/README.md
>>>
>>> Or since you are part of the way there already just using logical
>>> replication entirely:
>>>
>>> https://www.postgresql.org/docs/current/logical-replication.html
>>>
>>>
>>> >
>>> > Best,
>>> > David
>>> >
>>> > On Fri, Jan 12, 2024 at 8:42 PM Juan Rodrigo Alejandro Burgos Mella
>>> > mailto:rodrigoburgosme...@gmail.com>>
>>> wrote:
>>> >
>>> >
>>> > try use the following syntax (yes, with a 2 before the greater
>>> sign)
>>> >
>>> > pg_recvlogical -d postgres --slot test --start -f - 2>>
>>> sample.jsonl
>>> >
>>> > Atte
>>> > JRBM
>>> >
>>> > El vie, 12 ene 2024 a las 16:35, David Ventimiglia
>>> > (mailto:davidaventimig...@hasura.io
>>> >>)
>>> > escribió:
>>> >
>>> > Hello! How do I redirect logical decoding output from the
>>> > 

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

It satisfies business constraints.

On Sat, Jan 13, 2024, 5:01 PM Karsten Hilbert 
wrote:

> Am Sat, Jan 13, 2024 at 05:53:14PM -0500 schrieb Ron Johnson:
>
> > *No,* that's a technology problem.  What is the purpose of storing them
> > back in the database using psql?
>
> Or even the end goal to be achieved by that ?
>
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

I'm asking a question about technology. It has an answer. Whatever that
answer is, it's independent of anyone's purpose.

On Sat, Jan 13, 2024, 4:53 PM Ron Johnson  wrote:

> *No,* that's a technology problem.  What is the purpose of storing them
> back in the database using psql?
>
> On Sat, Jan 13, 2024 at 4:34 PM David Ventimiglia <
> davidaventimig...@hasura.io> wrote:
>
>> The business problem I'm trying to solve is:
>>
>> "How do I capture logical decoding events with the wal2json output
>> encoder, filter them with jq, and pipe them to psql, using pg_recvlogical?"
>>
>>>
>>>>>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

Thanks for the reply, Jim.  No, I'm afraid that's not the missing piece.  I
knew enough to use jq to transform the JSON output into SQL statements.
What I didn't know enough was about jq.  No, the missing piece turned out
not to have anything to do with PostgreSQL or pg_recvlogical (I guessed
incorrectly that it might), but rather with jq itself.  I didn't realize
that jq buffers its input and it turns out all I had to do was use its
--unbuffered switch.  The full chapter-and-verse is described in this Stack
Overflow question and answer
<https://stackoverflow.com/questions/75784345/how-to-pipe-pg-recvlogical-to-psql-for-logical-replication>
.

Cheers,
David

On Tue, Jan 16, 2024 at 12:57 PM Jim Nasby  wrote:

> On 1/13/24 3:34 PM, David Ventimiglia wrote:
> > The business problem I'm trying to solve is:
> >
> > "How do I capture logical decoding events with the wal2json output
> > encoder, filter them with jq, and pipe them to psql, using
> pg_recvlogical?"
>
> I think the missing piece here is that you can't simply pipe JSON into
> psql and expect anything useful to happen. Are you using jq to turn the
> JSON into actual SQL statements? What does some of your jq output look
> like?
> --
> Jim Nasby, Data Architect, Austin TX
>
>

Re: How to redirect output from PostgreSQL pg_recvlogical to a file or a pipe?

[David Ventimiglia]

Whoops!  Wrong SO link.  Here's the correct SO link:

https://stackoverflow.com/questions/77808615/how-to-use-logical-decoding-with-pg-recvlogical-to-pass-changes-through-a-non-tr

On Tue, Jan 16, 2024 at 1:15 PM David Ventimiglia <
davidaventimig...@hasura.io> wrote:

> Thanks for the reply, Jim.  No, I'm afraid that's not the missing piece.
> I knew enough to use jq to transform the JSON output into SQL statements.
> What I didn't know enough was about jq.  No, the missing piece turned out
> not to have anything to do with PostgreSQL or pg_recvlogical (I guessed
> incorrectly that it might), but rather with jq itself.  I didn't realize
> that jq buffers its input and it turns out all I had to do was use its
> --unbuffered switch.  The full chapter-and-verse is described in this
> Stack Overflow question and answer
> <https://stackoverflow.com/questions/75784345/how-to-pipe-pg-recvlogical-to-psql-for-logical-replication>
> .
>
> Cheers,
> David
>
> On Tue, Jan 16, 2024 at 12:57 PM Jim Nasby  wrote:
>
>> On 1/13/24 3:34 PM, David Ventimiglia wrote:
>> > The business problem I'm trying to solve is:
>> >
>> > "How do I capture logical decoding events with the wal2json output
>> > encoder, filter them with jq, and pipe them to psql, using
>> pg_recvlogical?"
>>
>> I think the missing piece here is that you can't simply pipe JSON into
>> psql and expect anything useful to happen. Are you using jq to turn the
>> JSON into actual SQL statements? What does some of your jq output look
>> like?
>> --
>> Jim Nasby, Data Architect, Austin TX
>>
>>