Re: Performance opportunities for write-only audit tables?

2. Use UNLOGGED in the log table creates. Seriously? For audit tables? I guess that depends on what the log tables are used for Also in this case the logs are written to via a trigger I all within one transaction. So it may not matter. We use UNLOGGED because the audit logs are not

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 17:13:11 -0400, Alvaro Herrera wrote: > On 2018-May-24, Andres Freund wrote: > > > On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > > > BTW is it just a coincidence or are all the affected tables pg_authid? > > > Maybe the problem is shared relations ..? Maybe the fact that

Re: Performance opportunities for write-only audit tables?

On Thursday, May 24, 2018, Andrew Bartley wrote: > Hi, > > The two main techniques we use are. > > The idea here is to backup the rest of your DB to one backup regime and > the log tables to another. We set it up so at the end of the day the > current log table is backed up

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > > BTW is it just a coincidence or are all the affected tables pg_authid? > > Maybe the problem is shared relations ..? Maybe the fact that they have > > separate relfrozenxid (!?) in different databases?

Re: Performance opportunities for write-only audit tables?

Sorry that should be a "vacuum full freeze"... not just a "vacuum freeze" On Fri, 25 May 2018 at 07:07 Andrew Bartley wrote: > Hi, > > The two main techniques we use are. > > 1. Create a script to pass the -t param to pg_dump to exclude the log > tables. The idea here is

Re: Performance opportunities for write-only audit tables?

Hi, The two main techniques we use are. 1. Create a script to pass the -t param to pg_dump to exclude the log tables. The idea here is to backup the rest of your DB to one backup regime and the log tables to another. We set it up so at the end of the day the current log table is backed up and

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 16:49:40 -0400, Alvaro Herrera wrote: > BTW is it just a coincidence or are all the affected tables pg_authid? > Maybe the problem is shared relations ..? Maybe the fact that they have > separate relfrozenxid (!?) in different databases? Yes, that appears to be part of the problem.

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

> > BTW is it just a coincidence or are all the affected tables pg_authid? > Maybe the problem is shared relations ..? Maybe the fact that they have > separate relfrozenxid (!?) in different databases? > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development,

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > > Hmm .. surely > > xid = HeapTupleHeaderGetXmin(tuple); > > xmin_frozen = ((xid == FrozenTransactionId) || > >HeapTupleHeaderXminFrozen(tuple)); > > - if

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-May-24, Andres Freund wrote: > FWIW, even if that weren't the case: a) there'd be a lot more wrong with > this routine imo. b) some of the tuples affected clearly weren't > frozen... Right. BTW is it just a coincidence or are all the affected tables pg_authid? Maybe the problem is

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:30:54 -0700, Andres Freund wrote: > On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > > Hmm .. surely > > > > diff --git a/src/backend/access/heap/heapam.c > > b/src/backend/access/heap/heapam.c > > index 5016181fd7..5d7fa1fb45 100644 > > ---

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-05-24 13:08:53 -0400, Alvaro Herrera wrote: > Hmm .. surely > > diff --git a/src/backend/access/heap/heapam.c > b/src/backend/access/heap/heapam.c > index 5016181fd7..5d7fa1fb45 100644 > --- a/src/backend/access/heap/heapam.c > +++ b/src/backend/access/heap/heapam.c > @@ -6690,7 +6690,7

Re: Error on vacuum: xmin before relfrozenxid

2018-05-24 8:30 GMT+02:00 Andrey Borodin : > Hi! > > > 24 мая 2018 г., в 0:55, Paolo Crosato > написал(а): > > > > 1) VACUUM FULL was issued after the first time the error occurred, and a > couple of times later. CLUSTER was never run. > > 2)

Re: Parameter placeholders, $n vs ?

Tom Lane writes: > "David G. Johnston" writes: >> On Thursday, May 24, 2018, Lele Gaifax wrote: >>> So the questions: is the '?' style placeholder a supported variant? and >>> if so, should the ParamRef doc tell something

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hmm .. surely diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 5016181fd7..5d7fa1fb45 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -6690,7 +6690,7 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, xid =

Performance opportunities for write-only audit tables?

Hi, A project of mine uses a trigger-based approach to record changes to an audit table. The audit table is partitioned by month (pg 9.5, so old-fashioned partitioning). These tables are write-heavy but append-only and practically write-only: we never UPDATE or DELETE, and we seem to consult

Re: computing z-scores

On Thursday, May 24, 2018, David G. Johnston wrote: > On Thu, May 24, 2018 at 8:15 AM, Martin Mueller < > martinmuel...@northwestern.edu> wrote: > >> You construct a z-score for a set of values by subtracting the average >> from the value and dividing the result by

Re: computing z-scores

On Thursday, May 24, 2018, Ron wrote: > On 05/24/2018 10:15 AM, Martin Mueller wrote: > >> You construct a z-score for a set of values by subtracting the average >> from the value and dividing the result by the standard deviation. I know >> how to do this in a two-step

Re: computing z-scores

On 05/24/2018 10:15 AM, Martin Mueller wrote: You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the standard deviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. In a

Re: computing z-scores

On Thu, May 24, 2018 at 8:15 AM, Martin Mueller < martinmuel...@northwestern.edu> wrote: > You construct a z-score for a set of values by subtracting the average > from the value and dividing the result by the standard deviation. I know > how to do this in a two-step procedure. First, I compute

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Thu, May 24, 2018 at 12:38 PM, Maxim Boguk wrote: > > >> >> > ​About gdb bt - it's tricky because it is mission critical master db of >> > huge project. >> > I'll will try promote backup replica and check is issue persist there >> and >> > if yes - we will have our

computing z-scores

You construct a z-score for a set of values by subtracting the average from the value and dividing the result by the standard deviation. I know how to do this in a two-step procedure. First, I compute the average and standard deviation. In a second run I use the formula and apply it to each

RE: Extension/Language of plPerl in PostgreSQL

Okay Adrian. I will look into that. But I have one query, Does language pack for Perl available in EDB site ?. because I downloaded it from perl.org Thanks, Karthick -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Thursday, May 24, 2018 7:50 PM To:

Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 07:28 AM, Karthick Damodar wrote: Okay Adrian. I will look into that. But I have one query, Does language pack for Perl available in EDB site ?. because I downloaded it from perl.org You should be able to install it from the StackBuilder tool that is included with your EDB

Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 07:11 AM, Karthick Damodar wrote: Please reply to list also, more eyes on the problem. Ccing list. Adrian, Thanks for your response. So I need to install Perl version 5.24 it seems. Since I could have installed EDB package. Let me try this one. I have not used the EDB

Re: Parameter placeholders, $n vs ?

"David G. Johnston" writes: > On Thursday, May 24, 2018, Lele Gaifax wrote: >> So the questions: is the '?' style placeholder a supported variant? and >> if so, should the ParamRef doc tell something about that? > PostgreSQL's Prepare statement

Re: Extension/Language of plPerl in PostgreSQL

On 05/24/2018 06:31 AM, Karthick Damodar wrote: Hi Team, I had tried to install “*plperlu”* extension in PostgreSQL 9.5 and PostgreSQL 10. But I am returned with an following error message, ERROR: could not load library "C:/Program Files/PostgreSQL/9.5/lib/plperl.dll": The specified module

RE:Extension/Language of plPerl in PostgreSQL

Hi Team, I had tried to install "plperlu" extension in PostgreSQL 9.5 and PostgreSQL 10. But I am returned with an following error message, ERROR: could not load library "C:/Program Files/PostgreSQL/9.5/lib/plperl.dll": The specified module could not be found. SQL state: XX000. Note: I have

Re: Insert data if it is not existing

On 05/23/2018 06:03 PM, tango ward wrote: Updated my code to this: curr.pgsql.execute(''' INSERT INTO my_table(name, age) SELECT %s, %s WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) ''', ('Scott', 23)) If I remove SELECT statement, I will get an error message: error :

Re: Insert data if it is not existing

On 05/23/2018 09:39 PM, David G. Johnston wrote: On Wednesday, May 23, 2018, Adrian Klaver > wrote: '''INSERT INTO my_table(name, age) SELECT %s, %s WHERE NOT EXISTS(SELECT name FROM my_table WHERE

Re: Parameter placeholders, $n vs ?

On Thursday, May 24, 2018, Lele Gaifax wrote: > > So the questions: is the '?' style placeholder a supported variant? and if > so, > should the ParamRef doc tell something about that? > PostgreSQL's Prepare statement doesn't accept question mark as a parameter symbol, and

Re: Renice on Postgresql process

On 2018-05-07 11:04:31 -0700, Ben Chobot wrote: > On May 7, 2018, at 7:46 AM, Ayappan P2 wrote: > We are using Postgresql in AIX. Unlike some other databases, Postgresql > has > lot of other process running in the background along with the main > process. > >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

> > > > ​About gdb bt - it's tricky because it is mission critical master db of > > huge project. > > I'll will try promote backup replica and check is issue persist there and > > if yes - we will have our playground for a while, but it will require > > sometime to arrange.​ > > You should be ok

Re: partition table query allocate much memory

Hello Tao, I'm not sure it was a bug and I also cloud not explain why it allocated so much memory.Dosn't each sub partition table allocated the size of work_mem memory and not free it? It can, and it did it for hashed subPlan at least in PG 9.4, see

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat and PostGreSQL 9.2.24 on Debian

Hi, - Doing Streaming Replication between different minor version of PG is possible but not recommended [2] - Doing Streaming Replication between different OSes is not recommended pre ICU (pg10), please check you glibc versions. [1] [1]

Parameter placeholders, $n vs ?

Hi all, while improving the technical documentation of my current project, I hit a SQL statement that triggered a bug in my pg_query[1] based prettifier. The statement in question was using '?' as param placeholders instead of the '$n' style I'm used to: to my surprise the parser (pg_query uses

Re: Error on vacuum: xmin before relfrozenxid

Hi! > 24 мая 2018 г., в 0:55, Paolo Crosato написал(а): > > 1) VACUUM FULL was issued after the first time the error occurred, and a > couple of times later. CLUSTER was never run. > 2) Several failovers tests were perfomed before the cluster was moved to >