Automatic deletion of orphaned rows

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 ro

Re: concatenating hstores in a group by?

On 1/21/25 10:02, Brent Wood wrote: Yes, a Timescale hypertable with 500,000,000 rows of about 15 key/value pairs per record. I'm not sure why there is both a gin & gist index on the hstore, or the merits of each. Questions: 1) What is the timezone setting for the database? 2) What is the

Re: Records count mismatch with logical replication

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.

Re: Records count mismatch with logical replication

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 f

Re: Records count mismatch with logical replication

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 stal

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 11:26 PM Adrian Klaver wrote: > On 1/21/25 09:38, Durgamahesh Manne wrote: > > > > > > > > > Hi Adrian Klaver > > > > Really Thanks for your quick response > > > > This happened during repack lag went to more than 350Gb then gradually > > decreased to minimal lag after run

Re: concatenating hstores in a group by?

Yes, a Timescale hypertable with 500,000,000 rows of about 15 key/value pairs per record. I'm not sure why there is both a gin & gist index on the hstore, or the merits of each. Thanks  \d t_reading_hstore_sec Table "public.t_reading_hstore_sec"   

Re: Records count mismatch with logical replication

On 1/21/25 09:38, Durgamahesh Manne wrote: Hi Adrian Klaver Really Thanks for your quick response This happened during repack lag went to more than 350Gb then gradually decreased to minimal lag after running pg_repack I don't use pg_repack so I don't know what effect it would have on t

Re: Records count mismatch with logical replication

On Tue, Jan 21, 2025 at 9:24 PM Adrian Klaver wrote: > On 1/21/25 04:08, Durgamahesh Manne wrote: > > Hi Team, > > > > I have publication and subscription servers .So seems data replication > > running with minimal lag but records count mismatch with more than 10 > > thousand records between sou

Re: concatenating hstores in a group by?

On 1/19/25 12:09, Brent Wood wrote: Thanks for the replies, appreciated... My current solution is: /select trip_code,/ /            station_no,/ /            timer_sec + interval '12 hour' as NZST,/ /            timer_sec as utc,/ /            hstore_to_json(string_agg(values_sec::text, ', ')::

[no subject]

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

Re: concatenating hstores in a group by?

Thanks for the replies, appreciated... My current solution is: select trip_code, station_no, timer_sec + interval '12 hour' as NZST, timer_sec as utc, hstore_to_json(string_agg(values_sec::text, ', ')::hstore) as values_sec from (select '$TRIP

Re: Records count mismatch with logical replication

On 1/21/25 04:08, Durgamahesh Manne wrote: Hi  Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this iss

Re: Design of a reliable task processing queue

On Sun, Jan 19, 2025 at 9:23 AM Alex Burkhart wrote: > I'm looking for help to organize locks and transaction for a reliable task > queue. Have a look at https://github.com/tembo-io/pgmq for inspiration maybe. --DD

Records count mismatch with logical replication

Hi Team, I have publication and subscription servers .So seems data replication running with minimal lag but records count mismatch with more than 10 thousand records between source and destination tables Could you please help in resolving this issue? Regards, Durga Mahesh