Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wed, 2022-04-06 at 18:41 +0200, W.P. wrote: > W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: > > On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: > > > Is it possible to repair this? > > Not unless you tell us what exactly you did, command by command. > > > > Yours, > > Laurenz Albe > > > > > >

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 20:09, Adrian Klaver pisze: On 4/6/22 11:04 AM, W.P. wrote: W dniu 06.04.2022 o 18:54, Ron pisze: I know about restoring backup. I just don't have one (at least with reasonnable age), as it is experimental installation. (Didn't ever been thinking about locating production

Resources on modeling ordered hierachies?

There a tons of articles about how to model hierarchies in SQL but I haven't seen any about dealing with hierarchies where the order of children is important. The canonical example is a simple outline 1. 1.1 1.1.1 1.2 2. 2.1 etc If I am doing an insert where parent is 1.1 it should name it 1.1.

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 brakes Yea. This is definitely walking

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

Hi Perry, Le 07/04/2022 à 00:25, Perry Smith a écrit : [...] Notice that parent_id is suppose to refer to an id in the same table — at least, that is what I’m trying to do.  I’m trying to create a “root” entry whose parent points to themselves Note that you don't usually define a root as hav

Re: Per-Table vacuum_freeze_min_age

On Wed, Apr 6, 2022 at 6:31 PM Adrian Klaver wrote: > On 4/6/22 3:28 PM, Chris Bisnett wrote: > > On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver > > wrote: > > > > > It can: > > > > > https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE

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

On Wednesday, April 6, 2022, Perry Smith wrote: > > I’m using Active Record with the psql adapter. It has a > 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. > Constraints basically a

Re: Per-Table vacuum_freeze_min_age

On 4/6/22 3:28 PM, Chris Bisnett wrote: On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver > wrote: It can: https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

Re: Per-Table vacuum_freeze_min_age

On Wed, Apr 6, 2022 at 6:24 PM Adrian Klaver wrote: > On 4/6/22 3:13 PM, Chris Bisnett wrote: > > Hi all! > > > > I have several large tables (1-2Tb) that are 99.9% writes (small number > > of updates) with a decent commit rate (20K/sec). The basic idea is that > > it’s generating a lot of data c

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

Rather than explain how I got here, I’ll just explain the state I’m in. From psql: files_development=# \d files Table "public.files" Column | Type | Collation | Nullable | Default +-

Re: Per-Table vacuum_freeze_min_age

On 4/6/22 3:13 PM, Chris Bisnett wrote: Hi all! I have several large tables (1-2Tb) that are 99.9% writes (small number of updates) with a decent commit rate (20K/sec). The basic idea is that it’s generating a lot of data continuously. When the table would reach the thresholds for autovacuum

Re: Per-Table vacuum_freeze_min_age

Chris Bisnett writes: > The only downside I’ve seen is that this is a global setting and my > understanding is that this would cause decreased performance when used with > tables with a lot of writes and deletes. Is there a technical reason this > setting cannot be applied at the database or table

Per-Table vacuum_freeze_min_age

Hi all! I have several large tables (1-2Tb) that are 99.9% writes (small number of updates) with a decent commit rate (20K/sec). The basic idea is that it’s generating a lot of data continuously. When the table would reach the thresholds for autovacuum a vacuum would start and would start generati

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 4/6/22 11:04 AM, W.P. wrote: W dniu 06.04.2022 o 18:54, Ron pisze: I know about restoring backup. I just don't have one (at least with reasonnable age), as it is experimental installation. (Didn't ever been thinking about locating production DB on USB drive :) ). If it is experimental y

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 06/04/2022 18:20, Laura Smith wrote: On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: That was not a "command". PG was working ok, DB on encrypted (LUKS on LVM2, ext4) USB drive. I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because y

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 18:54, Ron pisze: On 4/6/22 11:41, W.P. wrote: W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wednesday, April 6th, 2022 at 17:41, W.P. wrote: > That was not a "command". PG was working ok, DB on encrypted (LUKS on > LVM2, ext4) USB drive. > I mean, why ? If there was ever a "setting yourself up to fail" scenario, it would be this. Just because you can do it, doesn't mean you sho

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On 4/6/22 11:41, W.P. wrote: W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not a "command". PG was working ok, DB on

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

W dniu 06.04.2022 o 12:37, Laurenz Albe pisze: On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe That was not a "command". PG was working ok, DB on encrypted (LUKS on LVM2, ext4

Re: PostgreSQL 10.20 crashes / Antivirus

On 4/6/22 07:55, Thomas, Richard wrote: Ah... no - the AV is still allowed to run riot in the folder where the dump file is written to. Do you think that could be the issue? Should have added to previous post: Try directing the pg_dump to one of the directories that have AV scanning disabled

Re: PostgreSQL 10.20 crashes / Antivirus

On 4/6/22 07:55, Thomas, Richard wrote: Ah... no - the AV is still allowed to run riot in the folder where the dump file is written to. Do you think that could be the issue? I would say that would be the most likely cause. Mainly because I can not think of another for this, "Access Denied: A

RE: PostgreSQL 10.20 crashes / Antivirus

Ah... no - the AV is still allowed to run riot in the folder where the dump file is written to. Do you think that could be the issue? Richard -Original Message- From: Adrian Klaver Sent: 06 April 2022 15:42 To: Thomas, Richard ; pgsql-general@lists.postgresql.org Subject: Re: PostgreSQ

Re: PostgreSQL 10.20 crashes / Antivirus

On 4/6/22 03:59, Thomas, Richard wrote: I’ve been having problems with several PostgreSQL crashes over the last month (originally with 10.16, then more recently with 10.20, all on a Windows Server 2012R2 platform), always with a low level permissions error: "server process (PID 5664) was termi

Re: Cascade view drop permission checks

On Wed, Apr 6, 2022 at 6:48 AM Tom Lane wrote: > (b) what other behavior would be better? Dropping the table and > leaving a broken view behind isn't good. Neither is refusing to > let the owner drop her object. > > CREATE OR REPLACE VIEW name SELECT null::type, null::type, null::type; Where t

Re: Cascade view drop permission checks

"David G. Johnston" writes: > On Tuesday, April 5, 2022, m7o...@gmail.com wrote: >> It seems strange to me that somebody who is not a member of owner role can >> drop an object bypassing permission checks. >> Is this behaviour OK? > The system dropped the now defunct view, not alice. Bob accept

Re: Cascade view drop permission checks

On Wednesday, April 6, 2022, m7o...@gmail.com wrote: > David, thank you for the clarification. > Should we consider raising log level for cascade drops from NOTICE to > WARNING? By now cascade drops appears in log files only when log level >= > NOTICE. > > --- a/src/backend/catalog/dependency.c >

PostgreSQL 10.20 crashes / Antivirus

I've been having problems with several PostgreSQL crashes over the last month (originally with 10.16, then more recently with 10.20, all on a Windows Server 2012R2 platform), always with a low level permissions error: "server process (PID 5664) was terminated by exception 0xC022" (This NT err

Re: Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote: > Is it possible to repair this? Not unless you tell us what exactly you did, command by command. Yours, Laurenz Albe

Re: Cascade view drop permission checks

David, thank you for the clarification. Should we consider raising log level for cascade drops from NOTICE to WARNING? By now cascade drops appears in log files only when log level >= NOTICE. --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1105,7 +1105,7 @@ report

Problem with PG 11 database on OrangePi3 (ARMBIAN, ARM64) after disk disrupion (problem with USB disk connection)

Hi there, I had a problem with disconnecting USB disc with PG database on my OPi3. PG is 11. Now base refuses to start, with problem reported in log (see attached file). But I don't have replication configured! Is it possible to repair this? Laurent 2022-04-05 20:53:38.892 CEST [12022-04-

Re: Transaction and SQL errors

Hello Gilles and thanks for that link! I will definitively add that info to our issue tracker/db. Seb

Re: Transaction and SQL errors

Le 04/04/2022 à 18:20, Sebastien Flaesch a écrit : David, Personally, I can see where it has significant value for psql in interactive mode because people make typos.  Application code doesn't.  That removes a whole class of problems where the feature provides benefit. Sure, ap

Re: Cascade view drop permission checks

On Tuesday, April 5, 2022, m7o...@gmail.com wrote: > > -- alice > create or replace view sandbox_a.alice_view as > select category, name, setting > from pg_catalog.pg_settings; > > grant select on sandbox_a.alice_view to bob; > > -- bob > create or replace view sandbox_b.bob_view as > select di