Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 4:12 PM, Peter Geoghegan wrote: > On Mon, Mar 19, 2018 at 1:55 PM, Jeremy Finzel wrote: > > @Peter : > > > > staging=# SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > > lsn | checksum | flags | lower | upper | special | pagesize | > > version | prun

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:55 PM, Jeremy Finzel wrote: > @Peter : > > staging=# SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > lsn | checksum | flags | lower | upper | special | pagesize | > version | prune_xid > +--+---+---+---+-+

Re: STRING_AGG and GROUP BY

On Mon, Mar 19, 2018 at 1:54 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > I've come up with the following query, wonder if you meant something > similar - > > http://sqlfiddle.com/#!17/4ef8b/48 > > WITH cte1 AS ( > SELECT > mid, > STRING_AGG(x->>'letter', '') AS tiles > FR

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 3:53 PM, Peter Geoghegan wrote: > On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote: > > SELECT heap_page_items(get_raw_page('pg_authid', 7)); > > Can you post this? > > SELECT * FROM page_header(get_raw_page('pg_authid', 7)); > > -- > Peter Geoghegan > @Peter : stag

Re: STRING_AGG and GROUP BY

Thank you, David - On Fri, Mar 16, 2018 at 5:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber < > alexander.far...@gmail.com> wrote: > >> ​SELECT mid, >> > (SELECT string_agg(tiles, '; ') FROM tiles WHERE ta.mid = moves.mid) AS > mi

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:01 PM, Jeremy Finzel wrote: > SELECT heap_page_items(get_raw_page('pg_authid', 7)); Can you post this? SELECT * FROM page_header(get_raw_page('pg_authid', 7)); -- Peter Geoghegan

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-03-19 15:37:51 -0500, Jeremy Finzel wrote: > Does the fact that a snapshot does not have this issue suggest it could be > memory-related corruption and a db restart could clear it up? Could you show the page from the snapshot? I suspect it might just be a problem that's temporarily no

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 3:01 PM, Jeremy Finzel wrote: > > > On Mon, Mar 19, 2018 at 2:56 PM, Andres Freund wrote: > >> Hi, >> >> On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: >> > FWIW, if I remove the last filter, I get these rows and I believe row >> 7/57/ >> > 2906288382 is the one gener

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 2:56 PM, Andres Freund wrote: > Hi, > > On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: > > FWIW, if I remove the last filter, I get these rows and I believe row > 7/57/ > > 2906288382 is the one generating error: > > Oh, yea, that makes sense. It's wrapped around and l

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi, On 2018-03-19 14:53:58 -0500, Jeremy Finzel wrote: > FWIW, if I remove the last filter, I get these rows and I believe row 7/57/ > 2906288382 is the one generating error: Oh, yea, that makes sense. It's wrapped around and looks like it's from the future. > SELECT * FROM check_rel('pg_authid'

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 2:41 PM, Andres Freund wrote: > On 2018-03-19 14:37:24 -0500, Jeremy Finzel wrote: > > We upgraded to 9.5.5, and today we are running 9.5.11. And actually we > > upgraded from 9.3, not 9.4. We are still trying to figure out which > point > > release we were on at 9.3. >

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On 2018-03-19 14:37:24 -0500, Jeremy Finzel wrote: > We upgraded to 9.5.5, and today we are running 9.5.11. And actually we > upgraded from 9.3, not 9.4. We are still trying to figure out which point > release we were on at 9.3. Ok. IIRC there used to be a bug a few years back that sometimes le

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 1:17 PM, Andres Freund wrote: > Hi Jeremy, Alvaro, > > On 2018-03-19 13:00:13 -0500, Jeremy Finzel wrote: > > On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera < > alvhe...@alvh.no-ip.org> > > wrote: > > > > > Jeremy Finzel wrote: > > > > Getting some concerning errors in o

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 10:27 AM, Jimmy Augustine wrote: I tried this query and my database size is equal to 162GB. Well you can always look in $DATA directly. The database will be under $DATA/base/. You can find the like this: select oid, datname from pg_database where datname=''; -- Adrian Kla

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Hi Jeremy, Alvaro, On 2018-03-19 13:00:13 -0500, Jeremy Finzel wrote: > On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera > wrote: > > > Jeremy Finzel wrote: > > > Getting some concerning errors in one of our databases that is on 9.5.11, > > > on autovacuum from template0 database pg_authid and p

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Mon, Mar 19, 2018 at 12:46 PM, Alvaro Herrera wrote: > Jeremy Finzel wrote: > > Getting some concerning errors in one of our databases that is on 9.5.11, > > on autovacuum from template0 database pg_authid and pg_auth_members. I > > only saw some notes on the list about this error related to

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

pg_control version number:942 Catalog version number: 201510051 Database system identifier: 6351536019599012028 Database cluster state: in production pg_control last modified: Mon 19 Mar 2018 12:56:10 PM CDT Latest checkpoint location:

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

Jeremy Finzel wrote: > Getting some concerning errors in one of our databases that is on 9.5.11, > on autovacuum from template0 database pg_authid and pg_auth_members. I > only saw some notes on the list about this error related to materialized > views. FWIW, we did use pg_upgrade to upgrade this

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 10:29 AM, Jimmy Augustine wrote: That's aggregated. Not current values. Ah did you know some documentation about that ? https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW Andreas -- 2ndQuadrant - The PostgreSQL Suppor

Re: PostgreSQL 9.6 Temporary files

2018-03-19 18:25 GMT+01:00 Andreas Kretschmer : > On 19 March 2018 18:21:42 CET, Jimmy Augustine > wrote: > >2018-03-19 18:15 GMT+01:00 Adrian Klaver : > > > >> On 03/19/2018 10:12 AM, Jimmy Augustine wrote: > >> > >> > >>> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: > >>> > >>>

Re: PostgreSQL 9.6 Temporary files

I tried this query and my database size is equal to 162GB. 2018-03-19 18:17 GMT+01:00 Melvin Davidson : > > > On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine > wrote: > >> >> >> 2018-03-19 18:09 GMT+01:00 Adrian Klaver : >> >>> On 03/19/2018 10:04 AM, Jimmy Augustine wrote: >>> 2

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 10:17 AM, Melvin Davidson wrote: * *I think your problem is that SELECT pg_size_pretty(pg_total_relation_size('table_name')); only looks at the current database * *but SELECT pg_size_pretty(pg_database_size('Database Name')); looks at ALL databases. Not according to he

Re: PostgreSQL 9.6 Temporary files

On 19 March 2018 18:21:42 CET, Jimmy Augustine wrote: >2018-03-19 18:15 GMT+01:00 Adrian Klaver : > >> On 03/19/2018 10:12 AM, Jimmy Augustine wrote: >> >> >>> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: >>> >>> Dear Friends, >>> >>> I am newbie to

Re: PostgreSQL 9.6 Temporary files

2018-03-19 18:15 GMT+01:00 Adrian Klaver : > On 03/19/2018 10:12 AM, Jimmy Augustine wrote: > > >> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: >> >> Dear Friends, >> >> I am newbie to postgresql. >> I have 162 GB on my database but

Re: PostgreSQL 9.6 Temporary files

On Mon, Mar 19, 2018 at 1:17 PM, Jimmy Augustine wrote: > Hi, > > I used this command and I found the same value in total_size column. > > 2018-03-19 18:01 GMT+01:00 Melvin Davidson : > >> >> >> On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver < >> adrian.kla...@aklaver.com> wrote: >> >>> On 03/19

Re: PostgreSQL 9.6 Temporary files

On Mon, Mar 19, 2018 at 1:12 PM, Jimmy Augustine wrote: > > > 2018-03-19 18:09 GMT+01:00 Adrian Klaver : > >> On 03/19/2018 10:04 AM, Jimmy Augustine wrote: >> >>> >>> >>> 2018-03-19 17:45 GMT+01:00 Adrian Klaver >> >: >>> >>> On 03/19/2018 09:31 AM, Jimmy Au

Re: PostgreSQL 9.6 Temporary files

Hi, I used this command and I found the same value in total_size column. 2018-03-19 18:01 GMT+01:00 Melvin Davidson : > > > On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver > wrote: > >> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: >> >>> Dear Friends, >>> >>> I am newbie to postgresql. >>> I

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 10:12 AM, Jimmy Augustine wrote:     On 03/19/2018 09:31 AM, Jimmy Augustine wrote:         Dear Friends,         I am newbie to postgresql.         I have 162 GB on my database but when I check size of all         tables, I approxima

Re: PostgreSQL 9.6 Temporary files

2018-03-19 18:09 GMT+01:00 Adrian Klaver : > On 03/19/2018 10:04 AM, Jimmy Augustine wrote: > >> >> >> 2018-03-19 17:45 GMT+01:00 Adrian Klaver > >: >> >> On 03/19/2018 09:31 AM, Jimmy Augustine wrote: >> >> Dear Friends, >> >> I am newbie to p

found xmin from before relfrozenxid on pg_catalog.pg_authid

Getting some concerning errors in one of our databases that is on 9.5.11, on autovacuum from template0 database pg_authid and pg_auth_members. I only saw some notes on the list about this error related to materialized views. FWIW, we did use pg_upgrade to upgrade this database from 9.4 to 9.5. H

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 10:04 AM, Jimmy Augustine wrote: 2018-03-19 17:45 GMT+01:00 Adrian Klaver >: On 03/19/2018 09:31 AM, Jimmy Augustine wrote: Dear Friends, I am newbie to postgresql. I have 162 GB on my database but when I check size

Re: PostgreSQL 9.6 Temporary files

2018-03-19 17:45 GMT+01:00 Adrian Klaver : > On 03/19/2018 09:31 AM, Jimmy Augustine wrote: > >> Dear Friends, >> >> I am newbie to postgresql. >> I have 162 GB on my database but when I check size of all tables, I >> approximately obtain 80 GB. >> I also see that I have 68GB of temporary files ho

Re: PostgreSQL 9.6 Temporary files

On Mon, Mar 19, 2018 at 12:45 PM, Adrian Klaver wrote: > On 03/19/2018 09:31 AM, Jimmy Augustine wrote: > >> Dear Friends, >> >> I am newbie to postgresql. >> I have 162 GB on my database but when I check size of all tables, I >> approximately obtain 80 GB. >> I also see that I have 68GB of tempo

Re: PostgreSQL 9.6 Temporary files

Hi Andreas thanks for your response, 2018-03-19 17:44 GMT+01:00 Andreas Kretschmer : > On 19 March 2018 17:31:20 CET, Jimmy Augustine > wrote: > >Dear Friends, > > > >I am newbie to postgresql. > >I have 162 GB on my database but when I check size of all tables, I > >approximately obtain 80 GB.

Re: PostgreSQL 9.6 Temporary files

On 03/19/2018 09:31 AM, Jimmy Augustine wrote: Dear Friends, I am newbie to postgresql. I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB. I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp. E

Re: PostgreSQL 9.6 Temporary files

On 19 March 2018 17:31:20 CET, Jimmy Augustine wrote: >Dear Friends, > >I am newbie to postgresql. >I have 162 GB on my database but when I check size of all tables, I >approximately obtain 80 GB. Indexes? >I also see that I have 68GB of temporary files however I only found Where can you see

PostgreSQL 9.6 Temporary files

Dear Friends, I am newbie to postgresql. I have 162 GB on my database but when I check size of all tables, I approximately obtain 80 GB. I also see that I have 68GB of temporary files however I only found 2.4MB at postgres/data/base/pgsql_tmp. Could you tell me what are those temporary files and

Re: Logical replication: duplicate key problem

On 03/19/2018 12:30 AM, Johann Spies wrote: I have followed the following process: * pg_dump --schema-only on server 1 * restored that schema-only dump on server 2 * created a publication on server 1 including all the tables on server 1 * created a subscription on server 2 Server 2 does not get

Logical replication: duplicate key problem

I have followed the following process: * pg_dump --schema-only on server 1 * restored that schema-only dump on server 2 * created a publication on server 1 including all the tables on server 1 * created a subscription on server 2 Server 2 does not get updated data for the schema involved from any