bug/confusion with pg_log_standby_snapshot()

Is pg_log_standby_snapshot() expected to cause a WAL segment to be emitted in an otherwise idle system? In my lab setup, the primary did not, despite invoking pg_log_standby_snapshot() on it, even when several times the archive_timeout value passed after using that function. The setup was all

Re: select from composite type

On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver wrote: > > > > > attribute_list is an array of composite type (with 20 fields). > > I am trying to wrap my head around "array of composite type". Please > provide an example. > ARRAY[ (1,2)::point, (3,4)::point ]::point[] The main problem is the

Re: select from composite type

On 2/4/24 14:50, Lorusso Domenico wrote: Hello guys, I'm trying to find out the equivalent behaviour of unnest, when I've got just a composite type. This is the statement is something like that (but more complex):  _sqlUpdate text=$$ with s as ( select * from ($1) ) update myView as q set

Re: Question on partitioning

On Sun, Feb 4, 2024 at 4:40 PM veem v wrote: > Hello All, > In postgresql, Is it possible to partition an existing nonpartitioned > table having data already residing in it and indexes and constraints > defined in it, without the need of manually moving the data around, to make > it faster?

Re: How to do faster DML

On Sat, Feb 3, 2024 at 11:09 AM Lok P wrote: [snip] > show maintenance_work_mem; - 4155MB > show work_mem; - 8MB > show shared_buffers ; -22029684 > Those are pretty small values. What are your server specs?

select from composite type

Hello guys, I'm trying to find out the equivalent behaviour of unnest, when I've got just a composite type. This is the statement is something like that (but more complex): _sqlUpdate text=$$ with s as ( select * from ($1) ) update myView as q set (attribute_fullname, modify_user_id)

Question on partitioning

Hello All, In postgresql, Is it possible to partition an existing nonpartitioned table having data already residing in it and indexes and constraints defined in it, without the need of manually moving the data around, to make it faster? Similarly merging multiple partitions to one partition or

Re: How to do faster DML

On Sun, Feb 4, 2024 at 9:25 PM Ron Johnson wrote: > 1. Load the children before attaching them to the parent. > 2. Create the child indices, PK and FKs before attaching to the parent. > 3. Do step 2 in multiple parallel jobs. (cron is your friend.) > 4. Attach the children to the "naked" (no

Re: How to do faster DML

On Sun, Feb 4, 2024 at 9:18 PM Greg Sabino Mullane wrote: > Partitioning will depend a lot on how you access the data. Is it always > using that transaction_date column in the where clause? Can you share the > table definition via \d? We've talked about this table quite a bit, but not > actually

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

On 2/4/24 10:42, Marcelo Marques wrote: BUG #18328: yum update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86 The RPM package is separate from the core code. The

Re: Yum Update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86_64 from pgdg-common

BUG #18328: yum update nothing provides libarmadillo.so.12()(64bit) needed by gdal36-libs-3.6.4-6PGDG.rhel9.x86 On Fri, Feb 2, 2024 at 11:05 AM Marcelo Marques wrote: > *PROBLEM* > > *yum update nothing provides

Re: How to do faster DML

On Sun, Feb 4, 2024 at 10:30 AM Lok P wrote: > On Sun, Feb 4, 2024 at 8:14 PM Dennis White > wrote: > >> I'm surprised no one has mentioned perhaps it's a good idea to partition >> this table while adding the pk. By your own statements the table is >> difficult to work with as is. Without

Re: How to do faster DML

Partitioning will depend a lot on how you access the data. Is it always using that transaction_date column in the where clause? Can you share the table definition via \d? We've talked about this table quite a bit, but not actually seen what it looks like. We are flying blind a little bit. You

Re: How to do faster DML

On Sun, Feb 4, 2024 at 8:14 PM Dennis White wrote: > I'm surprised no one has mentioned perhaps it's a good idea to partition > this table while adding the pk. By your own statements the table is > difficult to work with as is. Without partitioning the table, row inserts > would need to walk the

Re: How to do faster DML

I'm surprised no one has mentioned perhaps it's a good idea to partition this table while adding the pk. By your own statements the table is difficult to work with as is. Without partitioning the table, row inserts would need to walk the pk index and could be a factor. If this is static table then

Re: How to do faster DML

> > insert into mytable2 select * from mytable1 order by ctid limit 10_000_000 > offset 20_000_000; > You can use min/max values grouping them by 10_000_000 records, so you don´t need that offset, then generate commands and run them. select format('insert into mytable2 select * from mytable1

Re: How to do faster DML

Lok: On Sat, 3 Feb 2024 at 21:44, Lok P wrote: > On Sun, Feb 4, 2024 at 12:50 AM Francisco Olarte > wrote: > However , as we have ~5billion rows in the base table and out of that , we > were expecting almost half i.e. ~2billion would be duplicates. And you said, > doing the inserts using

Re: How to do faster DML

> On 3 Feb 2024, at 13:20, Lok P wrote: > > Hello All, > A non partitioned table having ~4.8 billion rows in it and having data size > as ~1.4TB , row size as ~313 bytes having ~127 columns in it. This has got > approx ~1billion+ duplicate rows inserted in it and we want to get the >

Re: How to do faster DML

On 2024-02-04 02:14:20 +0530, Lok P wrote: > However , as we have ~5billion rows in the base table and out of that , we > were > expecting almost half i.e. ~2billion would be duplicates. That's way more than I expected from your original description. And it of course raises the question whether