Re: create all indexes and triggers in a schema after bulk load

thanks. will give it a try On 17/11/2021 5:16 pm, hubert depesz lubaczewski wrote: On Wed, Nov 17, 2021 at 05:13:35PM +1000, Akheel Ahmed wrote: Hi Group I am looking at doing a CDC-based upgrade using the following steps: 1. pg_dump schema on source, but exclude indexes and triggers 2. rec

Re: create all indexes and triggers in a schema after bulk load

On Wed, Nov 17, 2021 at 05:13:35PM +1000, Akheel Ahmed wrote: > Hi Group > > I am looking at doing a CDC-based upgrade using the following steps: > > > 1. pg_dump schema on source, but exclude indexes and triggers > > 2. recreate schema on target > > 3. Do a full load from source to target >

create all indexes and triggers in a schema after bulk load

Hi Group I am looking at doing a CDC-based upgrade using the following steps: 1. pg_dump schema on source, but exclude indexes and triggers 2. recreate schema on target 3. Do a full load from source to target 4. Recreate all the indexes and triggers on the target The migration will automati

Re: Managing major PostgreSQL upgrades

Thanks. My first instinct might be to simply maintain your own list of missing packages, and ensure they all get installed prior to the pg_upgrade run (something which ansible can handily do). Now, depending on the level of homogeneity of the server farm under your purview, this might pose its own

Re: Managing major PostgreSQL upgrades

On Mon, Nov 15, 2021 at 8:48 PM Saul Perdomo wrote: > Hey Tiff, > > We're in a similar boat. We currently lean on (mostly custom) ansible > scripting to automate all the repeatable tasks we can, but automation of > major PG version upgrades is something we are yet to tackle -- although we > plan

Re: Postgres Equivalent of Oracle Package

Hi út 16. 11. 2021 v 20:51 odesílatel DAVID ROTH napsal: > I can see how schemas can be used to keep related tables and other object > together. I may be missing something but I don't see how they can be used > to emulate the "once-per-session" behavior of Oracle packages. Have I > missed somet

Re: Row estimate on empty tables

Michael Lewis writes: > ref: > https://www.postgresql.org/message-id/9772.1510348781%40sss.pgh.pa.us > This functionality hasn't changed in the years since this post, right? I'm > dealing with temp tables specifically, but seem to be getting bit by this > behavior occasionally so I would like to c

Re: Account privileges

On Tuesday, November 16, 2021, Prathima Mulpuri wrote: > > Please share any ideas and solutions for my requirement. > Drop the “function or a stored procedure” requirement, use an external application language instead. Node.js for example. Separate the listing of extra/missing privileges from t

Account privileges

Hi all, I need some help regarding Postgres and I have checked and tried many queries. I am working on RDS Postgres 13 and in the process of preparing the script for auditing all the user privileges . As a part of our auditing, I need a script to list down all the privileges of all the users to

Row estimate on empty tables

ref: https://www.postgresql.org/message-id/9772.1510348781%40sss.pgh.pa.us This functionality hasn't changed in the years since this post, right? I'm dealing with temp tables specifically, but seem to be getting bit by this behavior occasionally so I would like to confirm. *Michael Lewis | Dat

Re: Plans with bad estimates/paths

On 11/16/21 9:22 PM, Joe Wildish wrote: > Hello, > > I am struggling with a problem that appears planning related. I'm > hoping folk here may be able to advise on how best to tackle the > issue. > > We have a system that ingests JSON messages containing order data. > The data from these messages

Plans with bad estimates/paths

Hello, I am struggling with a problem that appears planning related. I'm hoping folk here may be able to advise on how best to tackle the issue. We have a system that ingests JSON messages containing order data. The data from these messages is inserted into a normalised table structure; "order"

Re: Postgres Equivalent of Oracle Package

I can see how schemas can be used to keep related tables and other object together. I may be missing something but I don't see how they can be used to emulate the "once-per-session" behavior of Oracle packages. Have I missed something? > On 11/16/2021 12:27 PM Pavel Stehule wrote: > > >

Re: Postgres Equivalent of Oracle Package

Hi út 16. 11. 2021 v 18:23 odesílatel DAVID ROTH napsal: > One of the nice things about Oracle packages is that the code is loaded > and global values are set and stored only once per session. This is very > useful for values that are used repeatedly. > > What is the best way of emulating this b

Postgres Equivalent of Oracle Package

One of the nice things about Oracle packages is that the code is loaded and global values are set and stored only once per session. This is very useful for values that are used repeatedly. What is the best way of emulating this behavior in Postgresql?

Re: How is JSON stored in PG ?

On 16/11/2021 15:54, David Gauthier wrote: Pros and Cons for using JSON ? If it helps, here's my current use-case. I'm currently working on a project wherein data is captured from students for eventual submission to the relevant government department. The exact set of data required changes

Re: Performance question about using autosave=always and cleanupSavepoints=true

On Tue, 16 Nov 2021 at 11:09, Joel Rabinovitch wrote: > Hi, > > > > Currently, our application supports SQL Server databases and Oracle > schemas. We are updating our application to support PostgreSQL schemas. We > are using version 13.4 of PostgreSQL. > > > > Our application is written in Java a

Re: How is JSON stored in PG ?

On 11/16/21 07:54, David Gauthier wrote: Hi: psql (11.5, server 11.3) on linux I'm considering using JSON as a datatype for something I'm working on. The reasons are... 1) the 'metadata' (if you want to call it that) in JSON is very flexible.  Doesn't require an alter table or anything lik

Performance question about using autosave=always and cleanupSavepoints=true

Hi, Currently, our application supports SQL Server databases and Oracle schemas. We are updating our application to support PostgreSQL schemas. We are using version 13.4 of PostgreSQL. Our application is written in Java and connects to PostgreSQL schemas using JDBC. In our framework, we have

How is JSON stored in PG ?

Hi: psql (11.5, server 11.3) on linux I'm considering using JSON as a datatype for something I'm working on. The reasons are... 1) the 'metadata' (if you want to call it that) in JSON is very flexible. Doesn't require an alter table or anything like that to change. 2) The customers for this dat

Re: historical log of data records

On Tue, 2021-11-16 at 15:24 +0530, Sanjay Minni wrote: > > On Tue, 16 Nov 2021 at 14:50, Laurenz Albe wrote: > > On Tue, 2021-11-16 at 13:56 +0530, Sanjay Minni wrote: > > > I need to keep a copy of old data as the rows are changed.  > > > > > > For a general RDBMS I could think of keeping all t

Re: historical log of data records

On 2021-11-16 13:56:51 +0530, Sanjay Minni wrote: > I need to keep a copy of old data as the rows are changed.  > > For a general RDBMS I could think of keeping all the data in the same table > with a flag to indicate older copies of updated /  deleted rows or keep a > parallel table and copy thes

Multiple multi-column gist indexes that shares one geometry field

Hi, I am using Postgres 11 with postgis. I am maintaining a postgres table containing real-world objects. The table has three columns: - "polygon" of type geometry, which indicates the object's location. - "create_time" of type timezone - "state" of type string, can be one of "VALID", "

Re: reading this group other than thru mails

On 11/16/21 03:36, Sanjay Minni wrote: Is there any way to read this group other than thru mails or search old mails other than thru the interface at https://www.postgresql.org/list/pgsql-general . I dont seem to find these very friendly. There

Re: historical log of data records

Alban, Its a simple financial transaction processing application, the application permits editing / updating / deleting of entered data even multiple times but audit trail of the data tracing through all versions to its original must be preserved. (as outlined - Programmatically i could approach i

Re: historical log of data records

> On 16 Nov 2021, at 10:20, Laurenz Albe wrote: > > On Tue, 2021-11-16 at 13:56 +0530, Sanjay Minni wrote: >> I need to keep a copy of old data as the rows are changed. >> >> For a general RDBMS I could think of keeping all the data in the same table >> with a flag >> to indicate older copie

Re: historical log of data records

Are you referring to Table Partitioning ? https://www.postgresql.org/docs/14/ddl-partitioning.html with warm regards Sanjay Minni +91-9900-902902 http://planage.com https://www.linkedin.com/in/sanjayminni/ On Tue, 16 Nov 2021 at 14:50, Laurenz Albe wrote: > On Tue, 2021-11-16 at 13:56 +0530,

Re: historical log of data records

On Tue, 2021-11-16 at 13:56 +0530, Sanjay Minni wrote: > I need to keep a copy of old data as the rows are changed.  > > For a general RDBMS I could think of keeping all the data in the same table > with a flag > to indicate older copies of updated /  deleted rows or keep a parallel table > and

reading this group other than thru mails

Is there any way to read this group other than thru mails or search old mails other than thru the interface at https://www.postgresql.org/list/pgsql-general. I dont seem to find these very friendly. Sanjay Minni +91-9900-902902

historical log of data records

I need to keep a copy of old data as the rows are changed. For a general RDBMS I could think of keeping all the data in the same table with a flag to indicate older copies of updated / deleted rows or keep a parallel table and copy these rows into the parallel data under program / trigger control