Scheduling pg_repack job with pg_cron

[Durgamahesh Manne]

Hi
Respected Team

Is there any way to schedule a pg_repack job with pg_cron within the
instance ?

If yes then please please let me know the best approach to schedule it with
pg_cron within the instance.( not in bastion host)

your response is highly valuable

Regards.
Durga Mahesh

Re: Scheduling pg_repack job with pg_cron

[Muhammad Imtiaz]

Hi ,

Yes, you can use it. Create a bash script containing the pg_repack
command logic, then call this bash script from a PL/pgSQL function.
Finally, schedule this PL/pgSQL function using pg_cron as follows.
SELECT cron.schedule('pg_repack_job', '00 3 * * 1-5', $$SELECT repackfunc();$$);


*Muhammad Imtiaz*

*PostgreSQL Technical Support Lead *
*/ Pakistan R&D*
*Mobile: +923345072521*
*Email: imtia...@bitnine.net *



On Tue, Jul 30, 2024 at 1:58 PM Durgamahesh Manne 
wrote:

> Hi
> Respected Team
>
> Is there any way to schedule a pg_repack job with pg_cron within the
> instance ?
>
> If yes then please please let me know the best approach to schedule it
> with pg_cron within the instance.( not in bastion host)
>
> your response is highly valuable
>
> Regards.
> Durga Mahesh
>

Understanding conflicts on publications and subscriptions

[Koen De Groote]

Reading this document:
https://www.postgresql.org/docs/16/logical-replication-conflicts.html

There is talk of the "disable_on_error" option when creating a subscription.

The conflicts this applies to, I am assuming are only conflicts caused on
the side of the subscription?

As an attempt to apply new data doesn't work, because of modifications made
since the initial copy, is that correct?


I'm a bit confused by errors on the side of the publisher. Reading this
document: https://www.postgresql.org/docs/16/sql-createpublication.html

It states:

> The tables added to a publication that publishes UPDATE and/or DELETE 
> operations
must have REPLICA IDENTITY defined. Otherwise those operations will be
disallowed on those tables.

This is not related to the subscription option "disable_on_error", I take
it?

Because it sure would be nice if there was a way to do a similar thing for
the subscription, disabling it on error.

Am I getting this right? "disable_on_error" is only on subscription, and
errors on the publishers related to replica identity are not tied to that?

Thanks for your time.

Re: Understanding conflicts on publications and subscriptions

[Koen De Groote]

Just to add a thought:

If the subscriber gets a bit of logic to say "Something went wrong, so I'm
automatically stopping what I'm doing", it sounds logical to give the
publisher the same ability.

On Tue, Jul 30, 2024 at 3:47 PM Koen De Groote  wrote:

> Reading this document:
> https://www.postgresql.org/docs/16/logical-replication-conflicts.html
>
> There is talk of the "disable_on_error" option when creating a
> subscription.
>
> The conflicts this applies to, I am assuming are only conflicts caused on
> the side of the subscription?
>
> As an attempt to apply new data doesn't work, because of modifications
> made since the initial copy, is that correct?
>
>
> I'm a bit confused by errors on the side of the publisher. Reading this
> document: https://www.postgresql.org/docs/16/sql-createpublication.html
>
> It states:
>
> > The tables added to a publication that publishes UPDATE and/or DELETE 
> > operations
> must have REPLICA IDENTITY defined. Otherwise those operations will be
> disallowed on those tables.
>
> This is not related to the subscription option "disable_on_error", I take
> it?
>
> Because it sure would be nice if there was a way to do a similar thing for
> the subscription, disabling it on error.
>
> Am I getting this right? "disable_on_error" is only on subscription, and
> errors on the publishers related to replica identity are not tied to that?
>
> Thanks for your time.
>

Re: Understanding conflicts on publications and subscriptions

[David G. Johnston]

On Tuesday, July 30, 2024, Koen De Groote  wrote:
>
> If the subscriber gets a bit of logic to say "Something went wrong, so I'm
> automatically stopping what I'm doing", it sounds logical to give the
> publisher the same ability.
>

The wording for that option is:
 Specifies whether the subscription should be automatically disabled if any
errors are detected by subscription workers during data replication from
the publisher.

A subscription worker has no clue what the publisher is doing.  It operates
on the “when I see data I act on it” model.

As for whether the publisher should have this clause - the errors in
question are logical, data-oriented, errors, which the publisher is
incapable of having.

I believe what you are effectively requesting is that instead of
disallowing updates and deletes on the added table that lacks replica
identity you wish for the addition itself to fail.  That would have made a
better default behavior with an option to override when the current
behavior is desired.  But it seems too late to change this decision now.

David J.

Re: Understanding conflicts on publications and subscriptions

[Koen De Groote]

David,

By "addition" do you mean "adding the table to the publication"? I suppose
that's an option, though I was more thinking about disabling the
publication if an error occurs, similarly to how a subscription is disabled
if "disable_on_error" is set to true, and an error occurs there.

However, thinking about that is fantasizing, at this point.

My main worry is understanding the behavior as it is. And if my
understanding is correct: if a table doesn't have a replica identity, any
UPDATE or DELETE statement that happens on the publisher, for that table,
will be refused.

Is that correct?

Regards,
Koen


On Tue, Jul 30, 2024 at 4:04 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tuesday, July 30, 2024, Koen De Groote  wrote:
>>
>> If the subscriber gets a bit of logic to say "Something went wrong, so
>> I'm automatically stopping what I'm doing", it sounds logical to give the
>> publisher the same ability.
>>
>
> The wording for that option is:
>  Specifies whether the subscription should be automatically disabled if
> any errors are detected by subscription workers during data replication
> from the publisher.
>
> A subscription worker has no clue what the publisher is doing.  It
> operates on the “when I see data I act on it” model.
>
> As for whether the publisher should have this clause - the errors in
> question are logical, data-oriented, errors, which the publisher is
> incapable of having.
>
> I believe what you are effectively requesting is that instead of
> disallowing updates and deletes on the added table that lacks replica
> identity you wish for the addition itself to fail.  That would have made a
> better default behavior with an option to override when the current
> behavior is desired.  But it seems too late to change this decision now.
>
> David J.
>

Re: Understanding conflicts on publications and subscriptions

[David G. Johnston]

On Tue, Jul 30, 2024 at 7:16 AM Koen De Groote  wrote:

>  And if my understanding is correct: if a table doesn't have a replica
> identity, any UPDATE or DELETE statement that happens on the publisher, for
> that table, will be refused.
>
>
That is how I read the sentence "Otherwise those operations will be
disallowed on those tables."

Upon adding said table to a publication, future attempts to run updates and
deletes will result in failures in the transactions performing said DML.

Feel free to experiment that the behavior indeed matches the wording in the
documentation.

David J.

Trigger usecase

[sud]

Hello,

We have a streaming application (using apache flink and kafka) which
populates data in the tables of a postgres database version 15.4.

Now while loading transactions data we also get some reference data
information from source (for example customer information) and for these ,
we dont want to modify or override the existing customer data but want to
keep the old data with a flag as inactive and the new record should get
inserted with flag as active. So for such use case , should we cater this
inside the apache flink application code or should we handle this using
trigger on the table level which will execute on each INSERT and execute
this logic?

I understand trigger is difficult to debug and monitor stuff. But here in
this case , team mates is saying , we shouldn't put such code logic into a
streaming application code so should rather handle through trigger.

I understand, technically its possible bith the way, but want to understand
experts opinion on this and pros ans cons?

Regards
Sud

Re: Trigger usecase

[David G. Johnston]

On Tue, Jul 30, 2024 at 8:16 AM sud  wrote:

>
> I understand, technically its possible bith the way, but want to
> understand experts opinion on this and pros ans cons?
>
>
Have client code call a function that performs the relevant work directly
instead of having a trigger perform similar work.

Probably should just remove insert/update/delete permissions from most
users on that table as well, make the function owned by the table owner and
with security definer so it is allowed to perform the needed work.  But the
normal application user is unable to bypass using said function to perform
DML on the table.

David J.

Re: Trigger usecase

[Laurenz Albe]

On Tue, 2024-07-30 at 20:46 +0530, sud wrote:
> Now while loading transactions data we also get some reference data 
> information from
> source (for example customer information) and for these , we dont want to 
> modify or
> override the existing customer data but want to keep the old data with a flag 
> as
> inactive and the new record should get inserted with flag as active. So for 
> such
> use case , should we cater this inside the apache flink application code or 
> should
> we handle this using trigger on the table level which will execute on each 
> INSERT
> and execute this logic? 
> 
> I understand trigger is difficult to debug and monitor stuff. But here in 
> this case ,
> team mates is saying , we shouldn't put such code logic into a streaming 
> application
> code so should rather handle through trigger. 
> 
> I understand, technically its possible bith the way, but want to understand 
> experts
> opinion on this and pros ans cons?

It is largely a matter of taste.

The advantage of a trigger is that it works even if somebody bypasses the 
application
to insert data.

I think that triggers are easy to debug, but again, that's a matter of taste.

Yours,
Laurenz Albe

Re: Trigger usecase

[sud]

On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe 
wrote:

>
> It is largely a matter of taste.
>
> The advantage of a trigger is that it works even if somebody bypasses the
> application
> to insert data.
>
> I think that triggers are easy to debug, but again, that's a matter of
> taste.
>
>
> Thank you David and Laurenz.

Creating triggers to populates some audit table or say populating data in
audit columns (created_by, updated_by,created_date,updated_date) is fine i
believe, however this use case was to load/persist data in table with SCD-2
style, so is it good idea to use the trigger for such use case?

Not sure of the exact pros and cons, but we were following certain rules
like , if it's business logic which needs to be implemented in Database,
then it should not be done using triggers but rather should be done through
database procedure/functions. Hope this understanding correct.

Regards
Sud

Re: Trigger usecase

[Adrian Klaver]

On 7/30/24 11:46, sud wrote:


On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe > wrote:



It is largely a matter of taste.

The advantage of a trigger is that it works even if somebody
bypasses the application
to insert data.

I think that triggers are easy to debug, but again, that's a matter
of taste.


Thank you David and Laurenz.

Creating triggers to populates some audit table or say populating data 
in audit columns (created_by, updated_by,created_date,updated_date) is 
fine i believe, however this use case was to load/persist data in table 
with SCD-2 style, so is it good idea to use the trigger for such use case?


Not sure of the exact pros and cons, but we were following certain rules 
like , if it's business logic which needs to be implemented in Database, 
then it should not be done using triggers but rather should be done 
through database procedure/functions. Hope this understanding correct.


Triggers have to use procedures/functions so I am not understanding the 
issue.




Regards
Sud


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Trigger usecase

[David G. Johnston]

On Tue, Jul 30, 2024 at 11:46 AM sud  wrote:

>
> Not sure of the exact pros and cons, but we were following certain rules
> like , if it's business logic which needs to be implemented in Database,
> then it should not be done using triggers but rather should be done through
> database procedure/functions. Hope this understanding correct.
>

That is my personal take.  For process-oriented stuff you can follow the
trail of calls all the way through to the end of the process and its final
result.  With triggers you follow the trail to the insert/update/delete
then stop thinking that's it, while in reality it continues because you
have triggers performing yet more work.

David J.

Re: Trigger usecase

[Adrian Klaver]

On 7/30/24 13:28, David G. Johnston wrote:
On Tue, Jul 30, 2024 at 11:46 AM sud > wrote:



Not sure of the exact pros and cons, but we were following certain
rules like , if it's business logic which needs to be implemented in
Database, then it should not be done using triggers but rather
should be done through database procedure/functions. Hope this
understanding correct.


That is my personal take.  For process-oriented stuff you can follow the 
trail of calls all the way through to the end of the process and its 
final result.  With triggers you follow the trail to the 
insert/update/delete then stop thinking that's it, while in reality it 
continues because you have triggers performing yet more work.




"On insert/update/delete to this table the following actions are taken 
via triggers using the supplied function/procedure:


Insert
Data is sent to audit table using table_audit()
Update
Data is sent to audit table using table_audit()
Delete
Data is sent to audit table using table_audit()

See function specific documentation below

[...]
"

David J.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Understanding conflicts on publications and subscriptions

[Koen De Groote]

It indeed seems to be that.

My initial thought of " will be disallowed on those tables" was "on the
subscriber side". After all, why have a publication be of any effect if
there's nobody subscribing to it.

But it appears the publication influences behavior, regardless of there
being a subscriber, which feels counter-intuitive to me.

Thanks for stepping me through it.

On Tue, Jul 30, 2024 at 4:34 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Jul 30, 2024 at 7:16 AM Koen De Groote  wrote:
>
>>  And if my understanding is correct: if a table doesn't have a replica
>> identity, any UPDATE or DELETE statement that happens on the publisher, for
>> that table, will be refused.
>>
>>
> That is how I read the sentence "Otherwise those operations will be
> disallowed on those tables."
>
> Upon adding said table to a publication, future attempts to run updates
> and deletes will result in failures in the transactions performing said DML.
>
> Feel free to experiment that the behavior indeed matches the wording in
> the documentation.
>
> David J.
>
>

Re: Trigger usecase

[Alban Hertroys]

> On 30 Jul 2024, at 17:16, sud  wrote:
> 
> Hello, 
> 
> We have a streaming application (using apache flink and kafka) which 
> populates data in the tables of a postgres database version 15.4.
> 
> Now while loading transactions data we also get some reference data 
> information from source (for example customer information) and for these , we 
> dont want to modify or override the existing customer data but want to keep 
> the old data with a flag as inactive and the new record should get inserted 
> with flag as active. So for such use case , should we cater this inside the 
> apache flink application code or should we handle this using trigger on the 
> table level which will execute on each INSERT and execute this logic? 
> 
> I understand trigger is difficult to debug and monitor stuff. But here in 
> this case , team mates is saying , we shouldn't put such code logic into a 
> streaming application code so should rather handle through trigger. 

Is your data consistent if this operation doesn’t happen correctly? Is it okay 
to have no, or multiple, records where the flag is active for the same 
application transaction?

The benefit of doing this in a trigger is that the operations happen in a 
single database transaction, guaranteeing that there is only ever a single row 
that has the active flag set for every application transaction.

There are other ways to guarantee that, using exclusion constraints (which you 
should probably have on this table anyway), which would allow to handle such in 
the application. Such constraints can raise exceptions in your code, that need 
handling.

So I say, at least put an exclusion constraint on that table if you didn’t 
already, and then decide what approach suits you best.

Alban Hertroys
--
Als je de draak wilt steken met iemand,
dan helpt het,
als die een punthoofd heeft.