Not able to purge partition

Hello All, We created two partitioned tables one being parent and other child. Both are range partitioned and one table has a foreign key to the other parent table. Now , when we run the partition maintenance job to drop the older partitions which meet the partition retention criteria, it's not le

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/20/24 15:52, Jeff Ross wrote: On 3/20/24 16:25, Adrian Klaver wrote: On 3/20/24 15:18, Jeff Ross wrote: Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database.  An order, in this case, is an initial row from a table and all of

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

Adrian Klaver writes: > Haven't had a chance to go through this yet. I'm going to say though > that Tom Lane is looking for a shorter generic case that anyone could > run on their system. Yeah, it's a long way from that trigger function definition to a working (i.e. failing) example. Shortenin

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/20/24 15:52, Jeff Ross wrote: On 3/20/24 16:25, Adrian Klaver wrote: On 3/20/24 15:18, Jeff Ross wrote: Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database.  An order, in this case, is an initial row from a table and all of

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/20/24 16:50, Tom Lane wrote: Jeff Ross writes: I then get this error: NOTICE:  update cargotel_common.copy_orders_to_dev set copy_completed = 't', copy_completed_timestamp = clock_timestamp() where id = 21 ERROR:  cannot commit while a portal is pinned Would you mind supplying a self-co

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/20/24 16:25, Adrian Klaver wrote: On 3/20/24 15:18, Jeff Ross wrote: Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database.  An order, in this case, is an initial row from a table and all of the rows in all of the tables in t

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

Jeff Ross writes: > I then get this error: > NOTICE:  update cargotel_common.copy_orders_to_dev set copy_completed = > 't', copy_completed_timestamp = clock_timestamp() where id = 21 > ERROR:  cannot commit while a portal is pinned Would you mind supplying a self-contained example that triggers

Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/20/24 15:18, Jeff Ross wrote: Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database.  An order, in this case, is an initial row from a table and all of the rows in all of the tables in that database/schema that are needed to sa

After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

Greetings! I built a trigger fired process that copies an "order" from our production database to our dev database.  An order, in this case, is an initial row from a table and all of the rows in all of the tables in that database/schema that are needed to satisfy all of the foreign key constr

Re: Dropping a temporary view?

On 3/20/24 13:00, Celia McInnis wrote: On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver > wrote: __ On 3/20/24 10:54 AM, Celia McInnis wrote: Comments below more to sort out the process in my head then anything else. Hi Adrian The on

Re: Dropping a temporary view?

On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver wrote: > > On 3/20/24 10:54 AM, Celia McInnis wrote: > > Comments below more to sort out the process in my head then anything else. > > Hi Adrian > > The only behaviour changed for the debugging was to make the view > non-temporary, so that I could ve

Re: Dropping a temporary view?

On 3/20/24 10:54 AM, Celia McInnis wrote: Comments below more to sort out the process in my head then anything else. Hi Adrian The only behaviour changed for the debugging was to make the view non-temporary, so that I could verify in psql that the content of the view was what I wanted it to

Re: Dropping a temporary view?

Top-posting is frowned upon on these lists. Please try to reply online or at worse after the comments you are referencing. On Wed, Mar 20, 2024, 10:54 Celia McInnis wrote: > > > No, unfortunately I didn't do an explain on the slow query - and it's too > late now since the views are removed. How

Re: Dropping a temporary view?

Hi Adrian The only behaviour changed for the debugging was to make the view non-temporary, so that I could verify in psql that the content of the view was what I wanted it to be. Debugging CGI software can be quite difficult, so it's always good to have debugging hooks as a part of the software -

Re: Dropping a temporary view?

Good, that's what I'd hope. I'm still not sure why it took more than 7 minutes in psql to select the old non-temporary view contents after dropping the newer temporary view of the same name. There were no delays in producing the original non-temporary view. If I can reproduce the problem in psql, I

Re: Dropping a temporary view?

On 3/20/24 09:51, Celia McInnis wrote: The view is being used in some web query software that multiple people will be accessing and the contents of the view depend on what the person is querying, so I think that temporary views or tables are a good idea. I change to non-temporary views or table

Re: Dropping a temporary view?

On 3/20/24 10:51, Celia McInnis wrote: The view is being used in some web query software that multiple people will be accessing and the contents of the view depend on what the person is querying, so I think that temporary views or tables are a good idea. I change to non-temporary views or ta

Re: Dropping a temporary view?

> On Mar 20, 2024, at 09:51, Celia McInnis wrote: > > The view is being used in some web query software that multiple people will > be accessing and the contents of the view depend on what the person is > querying, so I think that temporary views or tables are a good idea. There's nothing w

Re: Dropping a temporary view?

The view is being used in some web query software that multiple people will be accessing and the contents of the view depend on what the person is querying, so I think that temporary views or tables are a good idea. I change to non-temporary views or tables (in a test version of the software which

Re: Dropping a temporary view?

On Wednesday, March 20, 2024, Celia McInnis wrote: > > > Is there some reason why it then took 7 minutes to select from the > non-temporary view tempview after I dropped the temporary view tempview? > >> >> The fact that you had and then dropped the temporary view has no relationship to how some o

Re: Dropping a temporary view?

On 3/20/24 08:39, Celia McInnis wrote: Ok, thanks - so I guess that means that if there is both a temporary and a non temporary view called "tempvie", DROP VIEW tempview; will remove the 1st tempview found, which with my path is the temporary one. Is there some reason why it then took 7 minut

Re: SSL error on install of PEM during Posgres install

On 3/20/24 06:26, mark bradley wrote: I am getting the following error message during install of PEM while installing Posgres. How can I fix this? What is your connection string? In postgresql.conf what is ssl set to per below? https://www.postgresql.org/docs/current/runtime-config-connec

Re: Dropping a temporary view?

Ok, thanks - so I guess that means that if there is both a temporary and a non temporary view called "tempvie", DROP VIEW tempview; will remove the 1st tempview found, which with my path is the temporary one. Is there some reason why it then took 7 minutes to select from the non-temporary view te

Re: Dropping a temporary view?

On 2024-03-20 15:58 +0100, Celia McInnis wrote: > I am using postresql 16, am trying to use temporary views in a piece of > software that I am writing, and would like it to be able to drop and > recreate temporary views. It seems from the documentation that I can only > use "CREATE OR REPLACE TEMPO

Dropping a temporary view?

Hi I am using postresql 16, am trying to use temporary views in a piece of software that I am writing, and would like it to be able to drop and recreate temporary views. It seems from the documentation that I can only use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same colu

Re: SSL error on install of PEM during Posgres install

Hi Mark, On 2024-03-20 14:26 +0100, mark bradley wrote: > I am getting the following error message during install of PEM while > installing Posgres. > > [cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23] Please include error messages as plain text. For reference, screenshot says: "psql: error: connecti

SSL error on install of PEM during Posgres install

I am getting the following error message during install of PEM while installing Posgres. [cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23] How can I fix this?