Correct way of using complex expressions as partitioning key

Hello! Is there some correct way to use complex expressions as a key for partitioned table? Inserting works as expected, but select runs over all partitions until use complete partition key expression as predicate test=# create table test ( id text, v1 bigint, v2 bigint

Re: Correct way of using complex expressions as partitioning key

On Fri, 2023-10-27 at 07:27 +0300, Alexander Rumyantsev wrote: > Is there some correct way to use complex expressions as a key for partitioned > table? > Inserting works as expected, but select runs over all partitions until use > complete > partition key expression as predicate > > test=# creat

need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

Hi  Need SQL logic/different approach method's  to find out way's a) to Implement check sum to validate table data is migrated correctly   Any guidance RegardsBharani SV

Re: Correct way of using complex expressions as partitioning key

Alexander Rumyantsev schrieb am 27.10.2023 um 06:27: > Hello! > > Is there some correct way to use complex expressions as a key for partitioned > table? > Inserting works as expected, but select runs over all partitions until use > complete partition key expression as predicate > > test=# crea

Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

Hi Why do you need checksums? Can you not employ a full outer join? My though behind this is that checksumming is quite costly cpuwise and you have to fiddle with each and every relevent column, be it as part of the checksum string be it as part of the joiner. The joiner would have the advant

Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

OP might be referring to migrating from, say, Oracle to Postgresql when oracle_fdw is not available. On 10/27/23 04:34, Thiemo Kellner wrote: Hi Why do you need checksums? Can you not employ a full outer join? My though behind this is that checksumming is quite costly cpuwise and you have

Re: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly

On 10/27/23 03:56, Y_esteembsv-forum wrote: Hi Need SQL logic/different approach method's  to find out way's a) to Implement check sum to validate table data is migrated correctly When migrating from Oracle to PostgreSQL, on both the Oracle side and the Postgresql side, I dumped each table (fo

BRIN index maintenance on table without primary key

Hello list. Key characteristics of my case: + HUGE table with 20G rows, ca 2TB + May be partitioned (have both versions on two test clusters ATM) + Plenty of inserts, no updates + No primary key - we had one IDENTITY bigint column until recently, but it proved useless and inefficient (index too

Re: Disk wait problem... may not be hardware...

On 2023-10-26 22:03:25 -0400, p...@pfortin.com wrote: > Are there any extra PG low level logs that can be turned on? Have you looked at the query plans as I recommended? (You might also want to enable track_io_timing to get extra information, but comparing just the query plans of fast and slow que

pg_checksums?

Hi, I have a few questions about pg_checksums. Long story short, checksums were disabled in our environment but are getting enabled now (again!) because of block corruptions which destroyed a few databases in a database cluster. And before you say "told you so", the decision to disable checksu

Re: pg_checksums?

On 10/27/23 13:34, Paul Förster wrote: Hi, I have a few questions about pg_checksums. Long story short, checksums were disabled in our environment but are getting enabled now (again!) because of block corruptions which destroyed a few databases in a database cluster. And before you say "told

Re: pg_checksums?

Hi Ron, > On Oct 27, 2023, at 21:02, Ron wrote: >> b) why isn't it possible to check whether checksums are enabled or not? > > (This is my tiny test instance.) > > $ pg_controldata | grep checksum > Data page checksum version: 0 > > postgres=# show data_checksums; > data_checksums >

Re: pg_checksums?

> On 27 Oct 2023, at 20:34, Paul Förster wrote: > a) why isn't it possible to enable checksumming while a database cluster is > up? It is surprisingly complicated to enable checksums on a live cluster, a patch was submitted a while back but ultimately never made it into postgres. The below thr

Re: pg_checksums?

On Fri, Oct 27, 2023 at 10:45:16PM +0200, Daniel Gustafsson wrote: > > On 27 Oct 2023, at 20:34, Paul Förster wrote: > > > a) why isn't it possible to enable checksumming while a database cluster is > > up? > > It is surprisingly complicated to enable checksums on a live cluster, a patch > was

Re: pg_checksums?

Hi Bruce, hi Daniel, > On Oct 27, 2023, at 23:21, Bruce Momjian wrote: > > On Fri, Oct 27, 2023 at 10:45:16PM +0200, Daniel Gustafsson wrote: >>> On 27 Oct 2023, at 20:34, Paul Förster wrote: >> >>> a) why isn't it possible to enable checksumming while a database cluster is >>> up? >> >> It

Re: PgAmin view

On Thu, Oct 26, 2023 at 5:56 AM Shaozhong SHI wrote: > If a PgAmin view is created and tables it queries are missing, what will > happen? > > If a PdAmin view is created, and it may take 20 hours to complete, what > will happen? > > Would views automatically run, when you start the PgAmin? > Vie

Re: Disk wait problem... may not be hardware...

Peter, Thanks for your patience; I've been feeling pressure to get this resolved; so have been lax in providing info here.. Hope the following helps... On Fri, 27 Oct 2023 19:07:11 +0200 Peter J. Holzer wrote: >On 2023-10-26 22:03:25 -0400, p...@pfortin.com wrote: >> Are there any extra PG low

Re: Disk wait problem... may not be hardware...

On 10/27/23 16:46, p...@pfortin.com wrote: Peter, Thanks for your patience; I've been feeling pressure to get this resolved; so have been lax in providing info here.. Hope the following helps... Something I hadn't noticed before: SQL-workbench/J (build 129.6) displays an execution timer at