Re: ***SPAM*** Re: WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

> On Sep 8, 2022, at 10:43 PM, Tom Lane wrote: > > Perry Smith writes: >> From within the container, files which I assume are created by >> PostgreSQL are ending up being owned by root rather than Postgres. > > If it looks that way from *inside* the container, tha

WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

This is an issue when PostgreSQL is running inside a container. In my quest to find an answer, I’ve discovered three instances that it has come up and various people have talked about fixes but no one seemed to notice what I found. I opened an issue here[1]. From within the container, files wh

Re: ***SPAM*** Re: With Recursive / Recursive View question

> On Aug 20, 2022, at 19:38, Christophe Pettus wrote: > > >> On Aug 20, 2022, at 15:42, Perry Smith wrote: >> >> To rephrase, is it possible to write a view that would work from the child >> terms out towards the ancestors? > > Assuming that the c

Re: ***SPAM*** Re: With Recursive / Recursive View question

> On Aug 20, 2022, at 19:38, Christophe Pettus wrote: > > > >> On Aug 20, 2022, at 15:42, Perry Smith wrote: >> >> To rephrase, is it possible to write a view that would work from the child >> terms out towards the ancestors? > > Assuming that th

With Recursive / Recursive View question

This select is almost instant: WITH RECURSIVE pathname(id, parent_id, basename) AS ( SELECT child.id, child.parent_id, child.basename FROM dirents child WHERE basename = '10732.emlx' UNION ALL SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename) F

Re: ***SPAM*** Re: Can I get the number of results plus the results with a single query?

> On Aug 15, 2022, at 08:55, David G. Johnston > wrote: > > On Monday, August 15, 2022, Perry Smith <mailto:p...@easesoftware.com>> wrote: > I’ve been toying with row_number() and then sort by row_number descending and > pick off the first row as the total number.

Can I get the number of results plus the results with a single query?

I like to have what I call “baby sitting” messages such as “Completed 15 out of 1023”. To do this, I need the number of results a query returns but I also need the results. Currently I’m doing this with two queries such as: SELECT COUNT(*) FROM table WHERE …. expression … SELECT * FROM

Re: Oddity that I don't understand

I see why… The select of the view is picking d.ino which is null because there is no match in the dirents table. Thanks guys! > On Aug 11, 2022, at 08:23, Perry Smith wrote: > > I’m tempted to ask “Is this a bug” but I predict there is an explanation. > > I have a view: &g

Oddity that I don't understand

I’m tempted to ask “Is this a bug” but I predict there is an explanation. I have a view: find_dups=# \sv+ dateien 1 CREATE OR REPLACE VIEW public.dateien AS 2SELECT d.id, 3 d.basename, 4 d.parent_id, 5 d.ino, 6 d.ext, 7 i.ftype, 8

Re: Help with large delete

Thank you TOM!!! So… I did: create index parent_id_index on dateien(parent_id); And now things are going much faster. As you can see, I had an index kinda sorta on the parent id but I guess the way I did it prevented Postgres from using it. > On Apr 17, 2022, at 06:58, Perry Smith wr

Re: Help with large delete

I’m sending this again. I don’t see that it made it to the list but there is also new info here. > On Apr 16, 2022, at 10:33, Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: > > Perry Smith mailto:p...@easesoftware.com>> writes: >> Currently I have one table th

Re: Help with large delete

> On Apr 16, 2022, at 13:56, Rob Sargent wrote: > > > >> On Apr 16, 2022, at 12:24 PM, Perry Smith wrote: >> >>  >> >>> On Apr 16, 2022, at 12:57, Jan Wieck >> <mailto:j...@wi3ck.info>> wrote: >>> >>> Make

Re: Help with large delete

> On Apr 16, 2022, at 12:57, Jan Wieck wrote: > > Make your connection immune to disconnects by using something like the screen > utility. Exactly… I’m using emacs in a server (daemon) mode so it stays alive. Then I do “shell” within it. > On Sat, Apr 16, 2022, 09

Re: ***SPAM*** Re: Help with large delete

> On Apr 16, 2022, at 10:33, Tom Lane wrote: > > Perry Smith writes: >> Currently I have one table that mimics a file system. Each entry has a >> parent_id and a base name where parent_id is an id in the table that must >> exist in the table or be null with

Help with large delete

Currently I have one table that mimics a file system. Each entry has a parent_id and a base name where parent_id is an id in the table that must exist in the table or be null with cascade on delete. I’ve started a delete of a root entry with about 300,000 descendants. The table currently has

Constraint ordering

I think (hope) I’ve made a bad assumption. I have my DB with one table with two constraint on new entries. The “first” is for the parent and basename be unique. The “second” is that the devno and inode are unique if it is a directory. When I was doing my early testing, the parent+basename co

Re: What have I done!?!?!? :-)

> On Apr 8, 2022, at 08:10, Magnus Hagander wrote: > > > > On Fri, Apr 8, 2022 at 3:07 PM Jan Wieck > wrote: > On 4/8/22 08:58, Magnus Hagander wrote: > > A side-note on this, which of course won't help the OP at this point, > > but if the general best practice of not

Re: What have I done!?!?!? :-)

> On Apr 8, 2022, at 07:47, Jan Wieck wrote: > > On 4/8/22 01:57, Nikolay Samokhvalov wrote: >> On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck > > wrote: >>So **IF** Active Record is using that feature, then it can dump any >>amount of garbage into your PostgreSQL data

Re: What have I done!?!?!? :-)

> On Apr 6, 2022, at 18:05, Lionel Bouton wrote: > > Hi Perry, > > Le 07/04/2022 à 00:25, Perry Smith a écrit : >> [...] > I'd say the later : in your case I would use a NULL parent_id for root(s). > Your way leads you to bend PostgreSQL until its back

What have I done!?!?!? :-)

disable_referential_integrity which takes a block of code. When the block of code exists, the constraints are put back. At least, that is what I thought. I’m wondering if the disabled constraints are still disabled somehow. If so, how would I check for that and how would I turn them back on