Re:

[Adrian Klaver]

On 1/20/25 10:10, Lana ABADIE wrote:

Hi all
I bumped into a weird case that i don't really understand...maybe 
someone in this list could have a clue
We have 2 Postgres databases configured as master/slave 
replica (Postgresq 12, RHEL8)
We have applications which write data into the master and applications 
which reads data from the replica.
A group of applications reads data using libpq: it declares a select 
statement as cursor and then there is fetch which can retrieve at most 
25k rows.


Add the complete SELECT and CURSOR code.

The select statement contains a between clause with T1 and T2. T1 is 
injected via input parameter but T2=floor(extract (epoch from 
coalesce(pg_last_xact_replay_timestamp(),now(-120. It is passed 
directly like that in the query.


What does T1 represent and how is it derived?

In other words we have something like select * from ZZ where ... and 
timestamp between $T1 and floor(extract (epoch from 
coalesce(pg_last_xact_replay_timestamp(),now(-120;
when this query gets executed, from time to time it returns a truncated 
number of rows.. less than if i was doing between T1 and T1...


I don't understand above, add more complete definition.  Example data 
would be nice.


T2 being an integer so either T2rows of zero or T2>=T1 and I would expect at least #rows greater or 
equal to the number of rows between T1 and T1,

Note that we are talking about  a total number of rows less than 2000.
Then when i fixed T2, in other words i do a query using between $T1 and 
$T2 (where T2=floor(extract (epoch from 
coalesce(pg_last_xact_replay_timestamp(),now(-120) then there is no 
issues, number of rows are retrieved correctly.
I also confirmed via metrics collection that the data is there when the 
query is being performed.

I would appreciate any explanations on this behavior, and hoping i'm clear.
Thanks
Doris


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

Re: Automatic deletion of orphaned rows

[Greg Sabino Mullane]

On Wed, Jan 22, 2025 at 2:00 AM Runxi Yu  wrote:

> I therefore propose a feature, to be able to specify in a table schema
> that a row should be deleted if orphaned.
>

I think you mean "childless" rows, as "orphaned" has a different meaning
traditionally.

When and how would this deletion take place? And why not just run the
delete yourself?
It would help to show us exactly the behavior you want. Here's some sample
tables we
can use:

create table parent( id int primary key );

create table kid( refid int references parent(id) );

insert into parent values (1),(2),(3);

insert into kid values (1);

-- remove any rows non-referenced rows (aka childless)
delete from parent where not exists (select 1 from kid where refid=parent.id
);

select * from parent;
 id

  1

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: Automatic deletion of orphaned rows

[Ron Johnson]

On Wed, Jan 22, 2025 at 9:37 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, January 22, 2025, Ron Johnson 
> wrote:
>>
>>
>>> I therefore propose a feature, to be able to specify in a table schema
>>> that a row should be deleted if orphaned.
>>
>>
>> For one thing, rows *can't* be orphaned if there's a foreign key
>> reference.
>>
>
> The description was correct even though using probably imprecise
> terminology.  The basic goal is to delete childless parents.
>

That's kinda what I thought he wrote, but it's so far beyond "normal" that
I dismissed the possibility.  Parents are allowed to not have
children, after all.

Is there *ANY* DBMS with a built-in reverse FK "parents must have children"
feature?

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Automatic deletion of orphaned rows

[David G. Johnston]

On Wednesday, January 22, 2025, Ron Johnson  wrote:
>
>
>> I therefore propose a feature, to be able to specify in a table schema
>> that a row should be deleted if orphaned.
>
>
> For one thing, rows *can't* be orphaned if there's a foreign key
> reference.
>

The description was correct even though using probably imprecise
terminology.  The basic goal is to delete childless parents.

David J.

Re: Automatic deletion of orphaned rows

[Ron Johnson]

On Wed, Jan 22, 2025 at 2:00 AM Runxi Yu  wrote:

> Hi,
>
> While writing a new program, I encountered the following:
>
> I have three tables: A, B, and X. Rows in X are referenced by A and/or B
> via foreign keys, one or more times. I would like to delete all orphaned
> rows in X, i.e. a row in X is deleted if and only if it is no longer
> referenced by any row in A or B. (When inserting these rows, I would
> insert X first, then the reference in A or B, in the same transaction.)
>
> To the best of my knowledge, there is no such functionality natively
> built into PostgreSQL. Alternatives include (1) using triggers or (2)
> using application logic. Both would involve locking the row in table X,
> and since I don't see a native "reference count" feature in PostgreSQL,
> the reference count would have to be maintained as a field in X. Both of
> these alternatives could get somewhat messy. In more complex schemas
> where circular references may be involved and a mark-and-sweep garbage
> collector is preferred, this would be even more difficult to implement.
>
> I therefore propose a feature, to be able to specify in a table schema
> that a row should be deleted if orphaned.


For one thing, rows *can't* be orphaned if there's a foreign key
reference.  That's the whole point of creating a foreign key.

As to automatically deleting children, ON DELETE CASCADE has been a feature
of foreign keys for at least 20 years.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Meaning of + symbol at end of column string

[David G. Johnston]

On Wednesday, January 22, 2025, Rich Shepard 
wrote:

> I use scripts to enter new rows in tables yet on rare occasions when I
> select all columns from a table I'll find a "+" at the end of a string
> value in one column. My web search finds no explanation.
>
> What does that + symbol mean in this context?
>

The value has a line break break character sequence at that point (I forget
if it has any awareness of CR and CRLF differences…)

David J.

Meaning of + symbol at end of column string

[Rich Shepard]

I use scripts to enter new rows in tables yet on rare occasions when I
select all columns from a table I'll find a "+" at the end of a string
value in one column. My web search finds no explanation.

What does that + symbol mean in this context?

TIA,

Rich

Re: Meaning of + symbol at end of column string

[Rich Shepard]

On Wed, 22 Jan 2025, David G. Johnston wrote:


The value has a line break break character sequence at that point (I
forget if it has any awareness of CR and CRLF differences…)


David,

Ah! A newline (\n). Thanks very much. I fix the row with an update and now I
understand that the original input line was wrapped.

Much appreciated,

Rich

Re: Records count mismatch with logical replication

[Durgamahesh Manne]

On Wed, 22 Jan, 2025, 03:11 Adrian Klaver, 
wrote:

> On 1/21/25 11:40, Durgamahesh Manne wrote:
> >
> >
> > On Wed, 22 Jan, 2025, 00:22 Adrian Klaver,  > > wrote:
> >
> >
> >
> > On 1/21/25 10:06 AM, Durgamahesh Manne wrote:
> >
> >  >
> >  > Hi Adrian Klaver
> >  >
> >  > 22,906,216 bytes/10,846 rows  works out to 2112 bytes per row.
> >  >
> >  > Is that a reasonable per row estimate?
> >  >
> >  > Yes  sometimes would be vary
> >
> > If I am following the lag went from 350GB behind to 22MB.
> >
> > Is the issue that the lag has stalled at 22MB?
> >
> >  >
> >  > Regards,
> >  > Durga Mahesh
> >  >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
> > Hi Adrian Klaver
> >
> > Is the issue that the lag has stalled at 22MB?
> >
> > Depends on load of source
> > The lag would be either decrease or increase in Kb 's and Mb's (not Gb's)
> > It s not constant as Data being replicated to target
>
> Previously you stated:
>
> "Both are under same vpc security groups"
>
> Does this refer to AWS?
>
> If so per:
>
> https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html
>
> "The following diagram shows an example VPC. The VPC has one subnet in
> each of the Availability Zones in the Region, EC2 instances in each
> subnet, and an internet gateway to allow communication between the
> resources in your VPC and the internet."
>
> So where are the two Postgres instances physically located relative to
> each other?
>
> >
> > But records count varies with difference of more than 10 thousand
>
> Have you looked at the I/0 statistics between the Postgres instances?
>
> >
> > How to mitigate this issue in simplest way ?
>
> Until it is determined what is causing the lag there is no way to deal
> with it.
>
> >
> > Regards
> > Durga Mahesh
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> Hi

Hi
⁹So where are the two Postgres instances physically located relative to
each other?
Both in lreland under same vpc security groups


>
> But records count varies with difference of more than 10 thousand

Have you looked at the I/0 statistics between the Postgres instances?

Seems everything looks good with pg replication slots

Does this pg logical slot get changes function help to pull pending changes
to subscription that can be sync with publication server for real time sync
?

Regards,
Durgamahesh