Re: Insert with Jsonb column hangs

> On Mar 9, 2024, at 9:01 AM, kuldeep singh wrote: > >  > Copy may not work in our scenario since we need to join data from multiple > tables & then convert it to json using row_to_json . This json data > eventually needs to be stored in a target table . >> Wait. You're getting the

Re: creating a subset DB efficiently ?

On 2024-03-08 11:22:17 -0500, David Gauthier wrote: > Related question... > The "delete from par_tbl_a where project <> 'a' " is taking forever. Have you checked whether all the FK columns have an index? Otherwise checking for references means a full table scan which may take a long time.

Re: Help diagnosing replication (copy) error

On Sun, Mar 10, 2024 at 3:16 AM Adrian Klaver wrote: > > Is the case still active? > Can you get information from them about what they saw? > > I've re-opened the case and asked for an explanation of the error and what they did to resolve it. Hopefully they shed some light on the 'mystery'.

Query plans not identical for `id in(1,2,3)` and `(id=1 or id=2 or id=3)`

Hi List. I have a list of bigint keys and I need to retrieve rows by these keys. Normally, I would split this list into pages of size 900 and perform several `SELECT ... WHERE key in($1,$2,...)`. However, the proprietary ORM we use can only produce this SQL: `SELECT ... WHERE (key=$1 or key=$2

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

Thanks, Would upgrading to the latest version of Postgres potentially solve the issue? On Sat, Mar 9, 2024 at 11:30 PM Tom Lane wrote: > hassan rafi writes: > > The issue of high query planning time seems to intermittently resolve > > itself, only to reoccur after a few hours. > > I wonder if

Re: creating a subset DB efficiently ?

I go more or less the other way. I have a script which follows the child...parent...grandparent...etc foreign keys in the source database and dumps only the records belonging to the selected "project" (your terminology, in my case it is "client"). I save the dumped data to an archive file. The

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

hassan rafi writes: > The issue of high query planning time seems to intermittently resolve > itself, only to reoccur after a few hours. I wonder if you are running into the lack of this fix: Author: Tom Lane Branch: master Release: REL_16_BR [9c6ad5eaa] 2022-11-22 14:40:20 -0500 Branch:

Re: creating a subset DB efficiently ?

On 3/8/24 08:22, David Gauthier wrote: Here's the situation - The DB contains data for several projects. - The tables of the DB contain data for all projects (data is not partitioned on project name or anything like that) - The "project" identifier (table column) exists in a few "parent"

Re: Insert with Jsonb column hangs

Thanks Adrian & Hector . I will try the copy approach & post the result here. On Sat, Mar 9, 2024 at 9:57 PM hector vass wrote: > > > On Sat, Mar 9, 2024 at 4:10 PM Adrian Klaver > wrote: > >> On 3/9/24 08:00, kuldeep singh wrote: >> > Copy may not work in our scenario since we need to join

Re: Insert with Jsonb column hangs

On Sat, Mar 9, 2024 at 4:10 PM Adrian Klaver wrote: > On 3/9/24 08:00, kuldeep singh wrote: > > Copy may not work in our scenario since we need to join data from > > multiple tables & then convert it to json using row_to_json . This > > json data eventually needs to be stored in a target

Re: Help diagnosing replication (copy) error

On 3/8/24 22:26, Steve Baldwin wrote: On Sat, Mar 9, 2024 at 11:06 AM Jeff Ross > wrote: RDS is a black box--who knows what's really going on there?  It would be interesting to see what the response is after you open a support case. I hope you'll

Re: Insert with Jsonb column hangs

copy syntax can include any valid select statement COPY (any valid select statement joining tables and converting it row_to_json) TO 'some_dump_file' or can copy a view CREATE VIEW myview (any valid select statement joining tables and converting it row_to_json); COPY myview TO

Re: Insert with Jsonb column hangs

On 3/9/24 08:00, kuldeep singh wrote: Copy may not work in our scenario since we need to join data from multiple tables & then  convert it to json using  row_to_json . This json data eventually  needs to be stored in a target table . Per: https://www.postgresql.org/docs/current/sql-copy.html

Re: Insert with Jsonb column hangs

Copy may not work in our scenario since we need to join data from multiple tables & then convert it to json using row_to_json . This json data eventually needs to be stored in a target table . Will it be better if we break the process into batches of like 10,000 rows & insert the data in its

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

Sure, we will plan to upgrade to the latest version. schemaname|relname |n_tup_ins|n_tup_upd |n_tup_del|n_live_tup|n_dead_tup|last_vacuum|last_autovacuum |

Re: Insert with Jsonb column hangs

On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh wrote: > Hi, > > We are inserting data close to 1M record & having a single Jsonb column > but query is getting stuck. > > We are using insert into select * .. , so all the operations are within > the DB. > > If we are running select query

Fwd: Getting error while upgrading

On Sat, Mar 9, 2024 at 12:18 PM omkar narkar wrote: > Hello Team, > > I am trying to upgrade my edb 10.5 community version to postgres 15.6 > version and while doing this i am getting error regarding OIDS are not > stable across Postgresql version (sys.callback_queue_table.user_data). > Kindly

Insert with Jsonb column hangs

Hi, We are inserting data close to 1M record & having a single Jsonb column but query is getting stuck. We are using insert into select * .. , so all the operations are within the DB. If we are running select query individually then it is returning the data in 40 sec for all rows but with

Re: creating a subset DB efficiently ?

On Fri, Mar 8, 2024 at 4:22 PM David Gauthier wrote: > Here's the situation > > - The DB contains data for several projects. > - The tables of the DB contain data for all projects (data is not > partitioned on project name or anything like that) > - The "project" identifier (table column)

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

It'd be worth checking that your default_statistics_target isn't set to anything wild, but beyond that, it'd be interesting to look at the output of vacuum verbose on some of the system catalogs as istm you might have catalog bloat. I should also mention that you're running a non-longer-supported

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

Postgres version: PostgreSQL 11.18, compiled by Visual C++ build 1800, 64-bit relname |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size| -++-+-+---++--+--+-+

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

On Sat, Mar 9, 2024 at 7:18 AM hassan rafi wrote: > Hi team, > > We are seeing unusually high query planning times on our Postgres server. > I am attaching a few query plans. > Postgresql version number? Rows in the tables? System load?

Getting error while upgrading

Hello Team, I am trying to upgrade my edb 10.5 community version to postgres 15.6 version and while doing this i am getting error regarding OIDS are not stable across Postgresql version (sys.callback_queue_table.user_data). Kindly help me to get the solution of this issue. Thanks and regards,

Seeing high query planning time on Azure Postgres Single Server version 11.

Hi team, We are seeing unusually high query planning times on our Postgres server. I am attaching a few query plans. select upc from store_seller_products where upc in