Logical replication: duplicate key problem

[Johann Spies]

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
anywhere else than the logical replication.

For some tables (some of them having many millions of records) the
process of replication seems to go smoothly. But for too many tables I
get this type of error messages:

2018-03-18 08:00:45.915 SAST [13512] ERROR:  duplicate key value
violates unique constraint "country_pkey"
2018-03-18 08:00:46.088 SAST [13513] ERROR:  duplicate key value
violates unique constraint "abstract_id_key"

In many of those cases it involves a serial field.  In at least one
case in involved a citext field with a unique constraint.

Now just wonder how logical replication between the two servers can
produce such errors if the constraints on both sides are the same?  Is
this a bug?

Regards
Johann

-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Logical replication: duplicate key problem

[Adrian Klaver]

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 updated data for the schema involved from
anywhere else than the logical replication.

For some tables (some of them having many millions of records) the
process of replication seems to go smoothly. But for too many tables I
get this type of error messages:

2018-03-18 08:00:45.915 SAST [13512] ERROR:  duplicate key value
violates unique constraint "country_pkey"
2018-03-18 08:00:46.088 SAST [13513] ERROR:  duplicate key value
violates unique constraint "abstract_id_key"


Are you getting the same errors on server 1?

Are you sure nothing else is touching server 2?



In many of those cases it involves a serial field.  In at least one
case in involved a citext field with a unique constraint.

Now just wonder how logical replication between the two servers can
produce such errors if the constraints on both sides are the same?  Is
this a bug?

Regards
Johann




--
Adrian Klaver
adrian.kla...@aklaver.com

PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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 where are they at? Can
I delete some of them?

All values come from pgAdmin 4 and checked by my own SQL
queries(postgresql-9.6).
I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE

Re: PostgreSQL 9.6 Temporary files

[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.


Indexes?


>I also see that I have 68GB of temporary files however I only found

Where can you see that?


>2.4MB
>at postgres/data/base/pgsql_tmp.
>
>Could you tell me what are those temporary files and where are they at?
>Can
>I delete some of them?


No, never delete files in datadir!

>
>All values come from pgAdmin 4 and checked by my own SQL
>queries(postgresql-9.6).
>I already run vacuum full and there is few dead tuples.

A few dead tuples arn't a real problem.


>
>Best regards,
>Jimmy AUGUSTINE


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

[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 however I only found 
2.4MB at postgres/data/base/pgsql_tmp.


Exactly how did you determine this?



Could you tell me what are those temporary files and where are they at? 
Can I delete some of them?


All values come from pgAdmin 4 and checked by my own SQL 
queries(postgresql-9.6).


Can you show actual queries used?


I already run vacuum full and there is few dead tuples.

Best regards,
Jimmy AUGUSTINE



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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.
>
>
> Indexes?
>
> Indexes are included into 80 GB that I mentioned.

>I also see that I have 68GB of temporary files however I only found
>
> Where can you see that?
>
> I used pgAdmin 4 and I see statistics on my global database.

>2.4MB
> >at postgres/data/base/pgsql_tmp.
> >
> >Could you tell me what are those temporary files and where are they at?
> >Can
> >I delete some of them?
>
>
> No, never delete files in datadir!
>
> >
> >All values come from pgAdmin 4 and checked by my own SQL
> >queries(postgresql-9.6).
> >I already run vacuum full and there is few dead tuples.
>
> A few dead tuples arn't a real problem.
>
>
> >
> >Best regards,
> >Jimmy AUGUSTINE
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

Re: PostgreSQL 9.6 Temporary files

[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 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.
>>
>
> Exactly how did you determine this?
>
>
>> Could you tell me what are those temporary files and where are they at?
>> Can I delete some of them?
>>
>> All values come from pgAdmin 4 and checked by my own SQL
>> queries(postgresql-9.6).
>>
>
> Can you show actual queries used?
>
> I already run vacuum full and there is few dead tuples.
>>
>> Best regards,
>> Jimmy AUGUSTINE
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
> > 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.


*I am not sure what your query was that deteremined table and index sizes,
but try using the query instead.*

*Note that total_size is the size of the table and all it's indexes.*




























*SELECT n.nspname as schema,   c.relname as table,   a.rolname as
owner,   c.relfilenode as filename,   c.reltuples::bigint,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' ||
quote_ident(c.relname) )) as size,
pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' ||
quote_ident(c.relname) )) as total_size,
pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )
as size_bytes,   pg_total_relation_size(quote_ident(n.nspname) || '.'
|| quote_ident(c.relname) ) as total_size_bytes,   CASE WHEN
c.reltablespace = 0THEN 'pg_default'ELSE (SELECT
t.spcname FROM pg_tablespace t WHERE (t.oid =
c.reltablespace) )END as tablespaceFROM
pg_class c  JOIN pg_namespace n ON (n.oid = c.relnamespace)  JOIN pg_authid
a ON ( a.oid = c.relowner )  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
AND quote_ident(relname) NOT LIKE 'pg_%' AND quote_ident(relname) NOT
LIKE 'information%' AND quote_ident(relname) NOT LIKE 'sql_%' AND
quote_ident(relkind) IN ('r')ORDER BY total_size_bytes DESC, 1, 2;-- *



*Melvin DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXCEmployment by invitation only!*

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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 however I only found 2.4MB
>> at postgres/data/base/pgsql_tmp.
>>
>
> Exactly how did you determine this?
>

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));


>
>> Could you tell me what are those temporary files and where are they at?
>> Can I delete some of them?
>>
>> All values come from pgAdmin 4 and checked by my own SQL
>> queries(postgresql-9.6).
>>
>
> Can you show actual queries used?
>
>
> I already run vacuum full and there is few dead tuples.
>>
>> Best regards,
>> Jimmy AUGUSTINE
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL 9.6 Temporary files

[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 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.


Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));



So where did the 68GB number for temporary files come from?



--
Adrian Klaver
adrian.kla...@aklaver.com

found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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.  Here is an example:

2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19 12:08:33
CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before relfrozenxid
740087784","automatic vacuum of table
""template0.pg_catalog.pg_authid"""""
2018-03-19 12:08:33.957 CDT,,,14892,,5aafee91.3a2c,2,,2018-03-19 12:08:33
CDT,59/340953,0,ERROR,XX001,"found xmin 2906288383 from before relfrozenxid
740087784","automatic vacuum of table
""template0.pg_catalog.pg_auth_members"""""


Any insight would be much appreciated.

Thanks,
Jeremy

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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 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.
>>
>>
>> Exactly how did you determine this?
>>
>> I used this command and sum result for all database :
>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>
>> And this for complete database :
>> SELECT pg_size_pretty(pg_database_size('Database Name'));
>>
>>
> So where did the 68GB number for temporary files come from?
>
> I don't measure this value by my own. I was disappointed by the gap
between the two queries, so I checked pgAdmin 4 and I saw this value.

>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL 9.6 Temporary files

[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 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.


     Exactly how did you determine this?

I used this command and sum result for all database :
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

And this for complete database :
SELECT pg_size_pretty(pg_database_size('Database Name'));


So where did the 68GB number for temporary files come from?

I don't measure this value by my own. I was disappointed by the gap 
between the two queries, so I checked pgAdmin 4 and I saw this value.


In what section of pgAdmin4?

Or do you know what query it used?




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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 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.
>>>
>>
>> Exactly how did you determine this?
>>
>>
>>> Could you tell me what are those temporary files and where are they at?
>>> Can I delete some of them?
>>>
>>> All values come from pgAdmin 4 and checked by my own SQL
>>> queries(postgresql-9.6).
>>>
>>
>> Can you show actual queries used?
>>
>> I already run vacuum full and there is few dead tuples.
>>>
>>> Best regards,
>>> Jimmy AUGUSTINE
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>> > 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.
>
>
> *I am not sure what your query was that deteremined table and index sizes,
> but try using the query instead.*
>
> *Note that total_size is the size of the table and all it's indexes.*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *SELECT n.nspname as schema,   c.relname as table,   a.rolname as
> owner,   c.relfilenode as filename,   c.reltuples::bigint,
> pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' ||
> quote_ident(c.relname) )) as size,
> pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' ||
> quote_ident(c.relname) )) as total_size,
> pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )
> as size_bytes,   pg_total_relation_size(quote_ident(n.nspname) || '.'
> || quote_ident(c.relname) ) as total_size_bytes,   CASE WHEN
> c.reltablespace = 0THEN 'pg_default'ELSE (SELECT
> t.spcname FROM pg_tablespace t WHERE (t.oid =
> c.reltablespace) )END as tablespaceFROM
> pg_class c  JOIN pg_namespace n ON (n.oid = c.relnamespace)  JOIN pg_authid
> a ON ( a.oid = c.relowner )  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
> AND quote_ident(relname) NOT LIKE 'pg_%' AND quote_ident(relname) NOT
> LIKE 'information%' AND quote_ident(relname) NOT LIKE 'sql_%' AND
> quote_ident(relkind) IN ('r')ORDER BY total_size_bytes DESC, 1, 2;-- *
>
>
>
> *Melvin DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
> Command – UXCEmployment by invitation only!*
>

Re: PostgreSQL 9.6 Temporary files

[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:
>>
>>>
>>>
>>> 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 however I only
>>> found 2.4MB at postgres/data/base/pgsql_tmp.
>>>
>>>
>>> Exactly how did you determine this?
>>>
>>> I used this command and sum result for all database :
>>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>> And this for complete database :
>>> SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>> So where did the 68GB number for temporary files come from?
>>
>> I don't measure this value by my own. I was disappointed by the gap
> between the two queries, so I checked pgAdmin 4 and I saw this value.
>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>


*>I don't measure this value by my own. I was disappointed by the gap
between the two queries, so I checked pgAdmin 4 and I saw this value. *

*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.*












*Try this query instead to show individual database sizes.SELECT oid,
   datname,pg_size_pretty(pg_database_size(datname))as
size_pretty,pg_database_size(datname) as size,   (SELECT
pg_size_pretty (SUM( pg_database_size(datname))::bigint)FROM
pg_database)  AS total,   ((pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname)) FROM
pg_database) ) * 100)::numeric(6,3) AS pct  FROM pg_database   ORDER BY
datname;*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

[Melvin Davidson]

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/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.

>>>
>>> Exactly how did you determine this?
>>>
>>>
 Could you tell me what are those temporary files and where are they at?
 Can I delete some of them?

 All values come from pgAdmin 4 and checked by my own SQL
 queries(postgresql-9.6).

>>>
>>> Can you show actual queries used?
>>>
>>> I already run vacuum full and there is few dead tuples.

 Best regards,
 Jimmy AUGUSTINE

>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>> > 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.
>>
>>
>> *I am not sure what your query was that deteremined table and index
>> sizes, but try using the query instead.*
>>
>> *Note that total_size is the size of the table and all it's indexes.*
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *SELECT n.nspname as schema,   c.relname as table,   a.rolname as
>> owner,   c.relfilenode as filename,   c.reltuples::bigint,
>> pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' ||
>> quote_ident(c.relname) )) as size,
>> pg_size_pretty(pg_total_relation_size(quote_ident(n.nspname) || '.' ||
>> quote_ident(c.relname) )) as total_size,
>> pg_relation_size(quote_ident(n.nspname) || '.' || quote_ident(c.relname) )
>> as size_bytes,   pg_total_relation_size(quote_ident(n.nspname) || '.'
>> || quote_ident(c.relname) ) as total_size_bytes,   CASE WHEN
>> c.reltablespace = 0THEN 'pg_default'ELSE (SELECT
>> t.spcname FROM pg_tablespace t WHERE (t.oid =
>> c.reltablespace) )END as tablespaceFROM
>> pg_class c  JOIN pg_namespace n ON (n.oid = c.relnamespace)  JOIN pg_authid
>> a ON ( a.oid = c.relowner )  WHERE quote_ident(nspname) NOT LIKE 'pg_%'
>> AND quote_ident(relname) NOT LIKE 'pg_%' AND quote_ident(relname) NOT
>> LIKE 'information%' AND quote_ident(relname) NOT LIKE 'sql_%' AND
>> quote_ident(relkind) IN ('r')ORDER BY total_size_bytes DESC, 1, 2;-- *
>>
>>
>>
>> *Melvin DavidsonMaj. Database & Exploration SpecialistUniverse
>> Exploration Command – UXCEmployment by invitation only!*
>>
>
>


*>I used this command and I found the same value in total_size column. *

*Please be specific. Exactly WHAT is the SQL query? *


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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 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.
>>
>>
>>  Exactly how did you determine this?
>>
>> I used this command and sum result for all database :
>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>
>> And this for complete database :
>> SELECT pg_size_pretty(pg_database_size('Database Name'));
>>
>>
>> So where did the 68GB number for temporary files come from?
>>
>> I don't measure this value by my own. I was disappointed by the gap
>> between the two queries, so I checked pgAdmin 4 and I saw this value.
>>
>
> In what section of pgAdmin4?
>
In section "Statistics" when I click on my database.

Or do you know what query it used?
>
I have found this but not sure

SELECT temp_files AS "Temporary files"
 , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;


>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL 9.6 Temporary files

[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:
>>>
>>>  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.
>>>
>>>
>>>  Exactly how did you determine this?
>>>
>>> I used this command and sum result for all database :
>>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
>>>
>>> And this for complete database :
>>> SELECT pg_size_pretty(pg_database_size('Database Name'));
>>>
>>>
>>> So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>>> between the two queries, so I checked pgAdmin 4 and I saw this
>value.
>>>
>>
>> In what section of pgAdmin4?
>>
>In section "Statistics" when I click on my database.
>
>Or do you know what query it used?
>>
>I have found this but not sure
>
>SELECT temp_files AS "Temporary files"
>   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;


That's aggregated. Not current values.

Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: PostgreSQL 9.6 Temporary files

[Adrian Klaver]

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 here:

https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-DBOBJECT


*
*Try this query instead to show individual database sizes.

SELECT oid,
    datname,
    pg_size_pretty(pg_database_size(datname))as size_pretty,
    pg_database_size(datname) as size,
    (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
   FROM pg_database)  AS total,
    ((pg_database_size(datname) / (SELECT SUM( 
pg_database_size(datname))
    FROM pg_database) ) * 
100)::numeric(6,3) AS pct

   FROM pg_database
   ORDER BY datname;
*

--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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:
>>>


 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 however I only
 found 2.4MB at postgres/data/base/pgsql_tmp.


 Exactly how did you determine this?

 I used this command and sum result for all database :
 SELECT pg_size_pretty(pg_total_relation_size('table_name'));

 And this for complete database :
 SELECT pg_size_pretty(pg_database_size('Database Name'));


>>> So where did the 68GB number for temporary files come from?
>>>
>>> I don't measure this value by my own. I was disappointed by the gap
>> between the two queries, so I checked pgAdmin 4 and I saw this value.
>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>
>
> *>I don't measure this value by my own. I was disappointed by the gap
> between the two queries, so I checked pgAdmin 4 and I saw this value. *
>
> *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.*
>
>
>
>
>
>
>
>
>
>
>
>
> *Try this query instead to show individual database sizes.SELECT oid,
>datname,pg_size_pretty(pg_database_size(datname))as
> size_pretty,pg_database_size(datname) as size,   (SELECT
> pg_size_pretty (SUM( pg_database_size(datname))::bigint)FROM
> pg_database)  AS total,   ((pg_database_size(datname) / (SELECT SUM(
> pg_database_size(datname)) FROM
> pg_database) ) * 100)::numeric(6,3) AS pct  FROM pg_database   ORDER BY
> datname;*
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: PostgreSQL 9.6 Temporary files

[Jimmy Augustine]

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:
> >>>
> >>>  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.
> >>>
> >>>
> >>>  Exactly how did you determine this?
> >>>
> >>> I used this command and sum result for all database :
> >>> SELECT pg_size_pretty(pg_total_relation_size('table_name'));
> >>>
> >>> And this for complete database :
> >>> SELECT pg_size_pretty(pg_database_size('Database Name'));
> >>>
> >>>
> >>> So where did the 68GB number for temporary files come from?
> >>>
> >>> I don't measure this value by my own. I was disappointed by the gap
> >>> between the two queries, so I checked pgAdmin 4 and I saw this
> >value.
> >>>
> >>
> >> In what section of pgAdmin4?
> >>
> >In section "Statistics" when I click on my database.
> >
> >Or do you know what query it used?
> >>
> >I have found this but not sure
> >
> >SELECT temp_files AS "Temporary files"
> >   , temp_bytes AS "Size of temporary files"FROM   pg_stat_database db;
>
>
> That's aggregated. Not current values.
>

Ah did you know some documentation about that ?

> Andreas
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

Re: PostgreSQL 9.6 Temporary files

[Adrian Klaver]

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 Support Company





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Alvaro Herrera]

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 database from 9.4 to
> 9.5.  Here is an example:
> 
> 2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19 12:08:33
> CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before relfrozenxid
> 740087784","automatic vacuum of table
> ""template0.pg_catalog.pg_authid"""""

Can you please supply output of pg_controldata?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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:   262BE/FE96240
Prior checkpoint location:262BA/623D5E40
Latest checkpoint's REDO location:262BA/F5499E98
Latest checkpoint's REDO WAL file:0001000262BA00F5
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  16/3132524419
Latest checkpoint's NextOID:  1090653331
Latest checkpoint's NextMultiXactId:  2142
Latest checkpoint's NextMultiOffset:  5235
Latest checkpoint's oldestXID:1829964553
Latest checkpoint's oldestXID's DB:   12376
Latest checkpoint's oldestActiveXID:  3131774441
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 16400
Latest checkpoint's oldestCommitTsXid:1829964553
Latest checkpoint's newestCommitTsXid:3132524418
Time of latest checkpoint:Mon 19 Mar 2018 12:54:08 PM CDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
wal_level setting:logical
wal_log_hints setting:off
max_connections setting:  2000
max_worker_processes setting: 10
max_prepared_xacts setting:   0
max_locks_per_xact setting:   64
track_commit_timestamp setting:   on
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Size of a large-object chunk: 2048
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0

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 materialized
> > views.  FWIW, we did use pg_upgrade to upgrade this database from 9.4 to
> > 9.5.  Here is an example:
> >
> > 2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19
> 12:08:33
> > CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before
> relfrozenxid
> > 740087784","automatic vacuum of table
> > ""template0.pg_catalog.pg_authid"""""
>
> Can you please supply output of pg_controldata?
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 materialized
> > views.  FWIW, we did use pg_upgrade to upgrade this database from 9.4 to
> > 9.5.  Here is an example:
> >
> > 2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19
> 12:08:33
> > CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before
> relfrozenxid
> > 740087784","automatic vacuum of table
> > ""template0.pg_catalog.pg_authid"""""
>
> Can you please supply output of pg_controldata?
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Please forgive my accidental top-post.  Here:

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:   262BE/FE96240
Prior checkpoint location:262BA/623D5E40
Latest checkpoint's REDO location:262BA/F5499E98
Latest checkpoint's REDO WAL file:0001000262BA00F5
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  16/3132524419
Latest checkpoint's NextOID:  1090653331
Latest checkpoint's NextMultiXactId:  2142
Latest checkpoint's NextMultiOffset:  5235
Latest checkpoint's oldestXID:1829964553
Latest checkpoint's oldestXID's DB:   12376
Latest checkpoint's oldestActiveXID:  3131774441
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 16400
Latest checkpoint's oldestCommitTsXid:1829964553
Latest checkpoint's newestCommitTsXid:3132524418
Time of latest checkpoint:Mon 19 Mar 2018 12:54:08 PM CDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
wal_level setting:logical
wal_log_hints setting:off
max_connections setting:  2000
max_worker_processes setting: 10
max_prepared_xacts setting:   0
max_locks_per_xact setting:   64
track_commit_timestamp setting:   on
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Size of a large-object chunk: 2048
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0


Thanks,
Jeremy

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Andres Freund]

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 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.  Here is an example:
> > >
> > > 2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19
> > 12:08:33
> > > CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before
> > relfrozenxid
> > > 740087784","automatic vacuum of table
> > > ""template0.pg_catalog.pg_authid"""""
> >
> > Can you please supply output of pg_controldata?

> Latest checkpoint's NextXID:  16/3132524419
> Latest checkpoint's NextMultiXactId:  2142
> Latest checkpoint's NextMultiOffset:  5235
> Latest checkpoint's oldestXID:1829964553
> Latest checkpoint's oldestXID's DB:   12376
> Latest checkpoint's oldestActiveXID:  3131774441
> Latest checkpoint's oldestMultiXid:   1
> Latest checkpoint's oldestMulti's DB: 16400

Hm, based on these it doesn't look like multixacts were involved (based
on oldestMultiXid it's highly unlikley there've multi wraparound, and
there's not much multixact usage on system tables anyway).  Which
suggests that there might have been actual corrpution here.

Jeremy:
- which version of 9.4 and 9.5 ran on this? Do you know?
- Can you install the pageinspect extension? If so, it might be a
  CREATE EXTENSION pageinspect;
  CREATE OR REPLACE FUNCTION check_rel(rel regclass, OUT blockno int8, OUT lp 
int2, OUT xmin xid)
RETURNS SETOF RECORD
LANGUAGE SQL
AS $$
SELECT blockno, lp, t_xmin
FROM
generate_series(0, pg_relation_size($1::text) / 8192 - 1) blockno, -- 
every block in the relation
heap_page_items(get_raw_page($1::text, blockno::int4)) -- every item on 
the page
WHERE
t_xmin IS NOT NULL -- filter out empty items
AND t_xmin != 1 -- filter out bootstrap
AND t_xmin != 2 -- filter out frozen transaction id
AND (t_infomask & ((x'0100' | x'0200')::int)) != ((x'0100' | 
x'0200')::int) -- filter out frozen rows with xid present
AND age(t_xmin) > age((SELECT relfrozenxid FROM pg_class WHERE oid = 
$1)) -- xid cutoff filter
$$;
  SELECT * FROM check_rel('pg_authid') LIMIT 100;

  and then display all items for one of the affected pages like
  SELECT * FROM heap_page_items(get_raw_page('pg_authid', 34343));


Alvaro:
- Hm, we talked about code adding context for these kind of errors,
  right? Is that just skipped for csvlog?
- Alvaro, does the above check_rel() function make sense?

Greetings,

Andres Freund

Re: PostgreSQL 9.6 Temporary files

[Adrian Klaver]

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 Klaver
adrian.kla...@aklaver.com

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 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.  Here is an example:
> > > >
> > > > 2018-03-19 12:08:33.946 CDT,,,14892,,5aafee91.3a2c,1,,2018-03-19
> > > 12:08:33
> > > > CDT,59/340951,0,ERROR,XX001,"found xmin 2906288382 from before
> > > relfrozenxid
> > > > 740087784","automatic vacuum of table
> > > > ""template0.pg_catalog.pg_authid"""""
> > >
> > > Can you please supply output of pg_controldata?
>
> > Latest checkpoint's NextXID:  16/3132524419
> > Latest checkpoint's NextMultiXactId:  2142
> > Latest checkpoint's NextMultiOffset:  5235
> > Latest checkpoint's oldestXID:1829964553
> > Latest checkpoint's oldestXID's DB:   12376
> > Latest checkpoint's oldestActiveXID:  3131774441
> > Latest checkpoint's oldestMultiXid:   1
> > Latest checkpoint's oldestMulti's DB: 16400
>
> Hm, based on these it doesn't look like multixacts were involved (based
> on oldestMultiXid it's highly unlikley there've multi wraparound, and
> there's not much multixact usage on system tables anyway).  Which
> suggests that there might have been actual corrpution here.
>
> Jeremy:
> - which version of 9.4 and 9.5 ran on this? Do you know?
>

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.


> - Can you install the pageinspect extension? If so, it might be a
>   CREATE EXTENSION pageinspect;
>   CREATE OR REPLACE FUNCTION check_rel(rel regclass, OUT blockno int8, OUT
> lp int2, OUT xmin xid)
> RETURNS SETOF RECORD
> LANGUAGE SQL
> AS $$
> SELECT blockno, lp, t_xmin
> FROM
> generate_series(0, pg_relation_size($1::text) / 8192 - 1) blockno,
> -- every block in the relation
> heap_page_items(get_raw_page($1::text, blockno::int4)) -- every
> item on the page
> WHERE
> t_xmin IS NOT NULL -- filter out empty items
> AND t_xmin != 1 -- filter out bootstrap
> AND t_xmin != 2 -- filter out frozen transaction id
> AND (t_infomask & ((x'0100' | x'0200')::int)) != ((x'0100' |
> x'0200')::int) -- filter out frozen rows with xid present
> AND age(t_xmin) > age((SELECT relfrozenxid FROM pg_class WHERE oid
> = $1)) -- xid cutoff filter
> $$;
>   SELECT * FROM check_rel('pg_authid') LIMIT 100;
>

Small note - Needs to be this because != is not supported for xid:

AND NOT t_xmin = 1 -- filter out bootstrap
AND NOT t_xmin = 2 -- filter out frozen transaction id


>
>   and then display all items for one of the affected pages like
>   SELECT * FROM heap_page_items(get_raw_page('pg_authid', 34343));
>
>
> Alvaro:
> - Hm, we talked about code adding context for these kind of errors,
>   right? Is that just skipped for csvlog?
> - Alvaro, does the above check_rel() function make sense?
>
> Greetings,
>
> Andres Freund
>

The function does NOT show any issue with either of those tables.

One very interesting thing that is puzzling us - we have taken several san
snapshots of the system real time that are running on the exact same
version 9.5.11, and they do NOT show the same error when we vacuum these
tables.  It makes us wonder if simply a db restart would solve the issue.

We will continue to investigate but interested in your feedback about what
we have seen thus far.

Thanks,
Jeremy

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Andres Freund]

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 lead to
highly contended pages being skipped during vacuum, and we'd still
update relfrozenxid. IIRC it required the table to be extended at the
same time or something?


> 
> > - Can you install the pageinspect extension? If so, it might be a
> >   CREATE EXTENSION pageinspect;
> >   CREATE OR REPLACE FUNCTION check_rel(rel regclass, OUT blockno int8, OUT
> > lp int2, OUT xmin xid)
> > RETURNS SETOF RECORD
> > LANGUAGE SQL
> > AS $$
> > SELECT blockno, lp, t_xmin
> > FROM
> > generate_series(0, pg_relation_size($1::text) / 8192 - 1) blockno,
> > -- every block in the relation
> > heap_page_items(get_raw_page($1::text, blockno::int4)) -- every
> > item on the page
> > WHERE
> > t_xmin IS NOT NULL -- filter out empty items
> > AND t_xmin != 1 -- filter out bootstrap
> > AND t_xmin != 2 -- filter out frozen transaction id
> > AND (t_infomask & ((x'0100' | x'0200')::int)) != ((x'0100' |
> > x'0200')::int) -- filter out frozen rows with xid present
> > AND age(t_xmin) > age((SELECT relfrozenxid FROM pg_class WHERE oid
> > = $1)) -- xid cutoff filter
> > $$;
> >   SELECT * FROM check_rel('pg_authid') LIMIT 100;
> >
> 
> Small note - Needs to be this because != is not supported for xid:
> 
> AND NOT t_xmin = 1 -- filter out bootstrap
> AND NOT t_xmin = 2 -- filter out frozen transaction id

Only on older releases ;). But yea, that looks right.



> >   and then display all items for one of the affected pages like
> >   SELECT * FROM heap_page_items(get_raw_page('pg_authid', 34343));
> >
> >
> > Alvaro:
> > - Hm, we talked about code adding context for these kind of errors,
> >   right? Is that just skipped for csvlog?
> > - Alvaro, does the above check_rel() function make sense?
> >
> > Greetings,
> >
> > Andres Freund
> >
> 
> The function does NOT show any issue with either of those tables.

Uh, huh?  Alvaro, do you see a bug in my query?

Greetings,

Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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.
>
> Ok.  IIRC there used to be a bug a few years back that sometimes lead to
> highly contended pages being skipped during vacuum, and we'd still
> update relfrozenxid. IIRC it required the table to be extended at the
> same time or something?
>
>
> >
> > > - Can you install the pageinspect extension? If so, it might be a
> > >   CREATE EXTENSION pageinspect;
> > >   CREATE OR REPLACE FUNCTION check_rel(rel regclass, OUT blockno int8,
> OUT
> > > lp int2, OUT xmin xid)
> > > RETURNS SETOF RECORD
> > > LANGUAGE SQL
> > > AS $$
> > > SELECT blockno, lp, t_xmin
> > > FROM
> > > generate_series(0, pg_relation_size($1::text) / 8192 - 1)
> blockno,
> > > -- every block in the relation
> > > heap_page_items(get_raw_page($1::text, blockno::int4)) --
> every
> > > item on the page
> > > WHERE
> > > t_xmin IS NOT NULL -- filter out empty items
> > > AND t_xmin != 1 -- filter out bootstrap
> > > AND t_xmin != 2 -- filter out frozen transaction id
> > > AND (t_infomask & ((x'0100' | x'0200')::int)) != ((x'0100' |
> > > x'0200')::int) -- filter out frozen rows with xid present
> > > AND age(t_xmin) > age((SELECT relfrozenxid FROM pg_class WHERE
> oid
> > > = $1)) -- xid cutoff filter
> > > $$;
> > >   SELECT * FROM check_rel('pg_authid') LIMIT 100;
> > >
> >
> > Small note - Needs to be this because != is not supported for xid:
> >
> > AND NOT t_xmin = 1 -- filter out bootstrap
> > AND NOT t_xmin = 2 -- filter out frozen transaction id
>
> Only on older releases ;). But yea, that looks right.
>
>
>
> > >   and then display all items for one of the affected pages like
> > >   SELECT * FROM heap_page_items(get_raw_page('pg_authid', 34343));
> > >
> > >
> > > Alvaro:
> > > - Hm, we talked about code adding context for these kind of errors,
> > >   right? Is that just skipped for csvlog?
> > > - Alvaro, does the above check_rel() function make sense?
> > >
> > > Greetings,
> > >
> > > Andres Freund
> > >
> >
> > The function does NOT show any issue with either of those tables.
>
> Uh, huh?  Alvaro, do you see a bug in my query?
>
> Greetings,
>
> Andres Freund
>

FWIW, if I remove the last filter, I get these rows and I believe row 7/57/
2906288382 is the one generating error:

SELECT * FROM check_rel('pg_authid') LIMIT 100;
 blockno | lp |xmin
-++
   7 |  4 | 2040863716
   7 |  5 | 2040863716
   7 |  8 | 2041172882
   7 |  9 | 2041172882
   7 | 12 | 2041201779
   7 | 13 | 2041201779
   7 | 16 | 2089742733
   7 | 17 | 2090021318
   7 | 18 | 2090021318
   7 | 47 | 2090021898
   7 | 48 | 2090021898
   7 | 49 | 2102749003
   7 | 50 | 2103210571
   7 | 51 | 2103210571
   7 | 54 | 2154640913
   7 | 55 | 2163849781
   7 | 56 | 2295315714
   7 | 57 | 2906288382
   7 | 58 | 2906329443
   7 | 60 | 3131766386
   8 |  1 | 2089844462
   8 |  2 | 2089844462
   8 |  3 | 2089844463
   8 |  6 | 2089844463
   8 |  9 | 2295318868
(25 rows)

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Andres Freund]

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') LIMIT 100;
>  blockno | lp |xmin
> -++
>7 |  4 | 2040863716
>7 |  5 | 2040863716
>7 |  8 | 2041172882
>7 |  9 | 2041172882
>7 | 12 | 2041201779
>7 | 13 | 2041201779
>7 | 16 | 2089742733
>7 | 17 | 2090021318
>7 | 18 | 2090021318
>7 | 47 | 2090021898
>7 | 48 | 2090021898
>7 | 49 | 2102749003
>7 | 50 | 2103210571
>7 | 51 | 2103210571
>7 | 54 | 2154640913
>7 | 55 | 2163849781
>7 | 56 | 2295315714
>7 | 57 | 2906288382
>7 | 58 | 2906329443
>7 | 60 | 3131766386
>8 |  1 | 2089844462
>8 |  2 | 2089844462
>8 |  3 | 2089844463
>8 |  6 | 2089844463
>8 |  9 | 2295318868
> (25 rows)

Could you show the contents of those two pages with a query like I had
in an earlier email?

Greetings,

Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 looks like it's from
> the future.
>
> > SELECT * FROM check_rel('pg_authid') LIMIT 100;
> >  blockno | lp |xmin
> > -++
> >7 |  4 | 2040863716
> >7 |  5 | 2040863716
> >7 |  8 | 2041172882
> >7 |  9 | 2041172882
> >7 | 12 | 2041201779
> >7 | 13 | 2041201779
> >7 | 16 | 2089742733
> >7 | 17 | 2090021318
> >7 | 18 | 2090021318
> >7 | 47 | 2090021898
> >7 | 48 | 2090021898
> >7 | 49 | 2102749003
> >7 | 50 | 2103210571
> >7 | 51 | 2103210571
> >7 | 54 | 2154640913
> >7 | 55 | 2163849781
> >7 | 56 | 2295315714
> >7 | 57 | 2906288382
> >7 | 58 | 2906329443
> >7 | 60 | 3131766386
> >8 |  1 | 2089844462
> >8 |  2 | 2089844462
> >8 |  3 | 2089844463
> >8 |  6 | 2089844463
> >8 |  9 | 2295318868
> > (25 rows)
>
> Could you show the contents of those two pages with a query like I had
> in an earlier email?
>
> Greetings,
>
> Andres Freund
>

SELECT heap_page_items(get_raw_page('pg_authid', 7));


heap_page_items

 
(1,4720,1,108,1897434979,0,0,"(7,1)",11,2825,32,1101110011100000,507769370)
 
(2,4608,1,108,1897442758,0,18,"(7,2)",11,2825,32,100011111011001000100000,507776451)
 
(3,4496,1,108,1897442758,0,20,"(7,3)",11,2825,32,101000111011001000100000,507776452)
 
(4,4384,1,108,2040863716,0,37,"(7,4)",11,2313,32,1011011011101110001100101000,525105004)
 
(5,4272,1,108,2040863716,0,39,"(7,5)",11,2313,32,10001011011011101110001100101000,525105005)
 (6,0,3,0,)
 (7,0,3,0,)
 
(8,4160,1,108,2041172882,0,49,"(7,8)",11,2313,32,1111010010101100011100101000,525219118)
 
(9,4048,1,108,2041172882,0,51,"(7,9)",11,2313,32,1000010010101100011100101000,525219119)
 (10,0,3,0,)
 (11,0,3,0,)
 
(12,3936,1,108,2041201779,0,181,"(7,12)",11,2313,32,1000110101000100011100101000,525236779)
 
(13,3824,1,108,2041201779,0,183,"(7,13)",11,2313,32,101101000100011100101000,525236780)
 (14,0,3,0,)
 (15,0,3,0,)
 
(16,3712,1,108,2089742733,0,0,"(7,16)",11,2313,32,110001011100111000111000,532706210)
 
(17,3600,1,108,2090021318,0,1,"(7,17)",11,2313,32,11001111010010111000,532753458)
 
(18,3488,1,108,2090021318,0,3,"(7,18)",11,2313,32,100011001111010010111000,532753459)
 
(19,8080,1,108,753125991,0,2,"(7,19)",11,2825,32,101100010101110010111111,236796556)
 
(20,7968,1,108,753125991,0,4,"(7,20)",11,2825,32,1000101100010101110010111111,236796557)
 
(21,7856,1,108,753125992,0,19,"(7,21)",11,2825,32,1000110010010101110010111111,236796563)
 
(22,7744,1,108,753125992,0,21,"(7,22)",11,2825,32,101010010101110010111111,236796564)
 
(23,7632,1,108,753125993,0,36,"(7,23)",11,2825,32,110110010101110010111111,236796570)
 
(24,7520,1,108,753125993,0,38,"(7,24)",11,2825,32,1000110110010101110010111111,236796571)
 
(25,7408,1,108,753125994,0,53,"(7,25)",11,2825,32,100011010101110010111111,236796577)
 
(26,7296,1,108,753125994,0,55,"(7,26)",11,2825,32,110001010101110010111111,236796578)
 
(27,7184,1,108,753125995,0,70,"(7,27)",11,2825,32,100101010101110010111111,236796584)
 
(28,7072,1,108,753125995,0,72,"(7,28)",11,2825,32,1000100101010101110010111111,236796585)
 
(29,6960,1,108,753125997,0,87,"(7,29)",11,2825,32,1000110011010101110010111111,236796595)
 
(30,6848,1,108,753125997,0,89,"(7,30)",11,2825,32,101011010101110010111111,236796596)
 
(31,6736,1,108,753125998,0,104,"

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 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') LIMIT 100;
>> >  blockno | lp |xmin
>> > -++
>> >7 |  4 | 2040863716
>> >7 |  5 | 2040863716
>> >7 |  8 | 2041172882
>> >7 |  9 | 2041172882
>> >7 | 12 | 2041201779
>> >7 | 13 | 2041201779
>> >7 | 16 | 2089742733 <(208)%20974-2733>
>> >7 | 17 | 2090021318
>> >7 | 18 | 2090021318
>> >7 | 47 | 2090021898
>> >7 | 48 | 2090021898
>> >7 | 49 | 2102749003 <(210)%20274-9003>
>> >7 | 50 | 2103210571 <(210)%20321-0571>
>> >7 | 51 | 2103210571 <(210)%20321-0571>
>> >7 | 54 | 2154640913 <(215)%20464-0913>
>> >7 | 55 | 2163849781 <(216)%20384-9781>
>> >7 | 56 | 2295315714 <(229)%20531-5714>
>> >7 | 57 | 2906288382
>> >7 | 58 | 2906329443
>> >7 | 60 | 3131766386
>> >8 |  1 | 2089844462 <(208)%20984-4462>
>> >8 |  2 | 2089844462 <(208)%20984-4462>
>> >8 |  3 | 2089844463 <(208)%20984-4463>
>> >8 |  6 | 2089844463 <(208)%20984-4463>
>> >8 |  9 | 2295318868 <(229)%20531-8868>
>> > (25 rows)
>>
>> Could you show the contents of those two pages with a query like I had
>> in an earlier email?
>>
>> Greetings,
>>
>> Andres Freund
>>
>
> SELECT heap_page_items(get_raw_page('pg_authid', 7));
>
>
> heap_page_items
> 
> 
> 
>  (1,4720,1,108,1897434979,0,0,"(7,1)",11,2825,32,
> 110111001100
> 00100000,507769370)
>  (2,4608,1,108,1897442758,0,18,"(7,2)",11,2825,32,
> 1000111110110010
> 00100000,507776451)
>  (3,4496,1,108,1897442758,0,20,"(7,3)",11,2825,32,
> 1010001110110010
> 00100000,507776452)
>  (4,4384,1,108,2040863716,0,37,"(7,4)",11,2313,32,
> 10110110111011100011
> 00101000,525105004)
>  (5,4272,1,108,2040863716,0,39,"(7,5)",11,2313,32,
> 100010110110111011100011
> 00101000,525105005)
>  (6,0,3,0,)
>  (7,0,3,0,)
>  (8,4160,1,108,2041172882,0,49,"(7,8)",11,2313,32,
> 11110100101011000111
> 00101000,525219118)
>  (9,4048,1,108,2041172882,0,51,"(7,9)",11,2313,32,
> 10000100101011000111
> 00101000,525219119)
>  (10,0,3,0,)
>  (11,0,3,0,)
>  (12,3936,1,108,2041201779,0,181,"(7,12)",11,2313,32,
> 10001101010001000111
> 00101000,525236779)
>  (13,3824,1,108,2041201779,0,183,"(7,13)",11,2313,32,
> 1011010001000111
> 00101000,525236780)
>  (14,0,3,0,)
>  (15,0,3,0,)
>  (16,3712,1,108,2089742733 <(208)%20974-2733>,0,0,"(7,16)",11,2313,32,
> 1100010111001110
> 00111000,532706210)
>  (17,3600,1,108,2090021318,0,1,"(7,17)",11,2313,32,
> 1100111101001000
> 00111000,532753458)
>  (18,3488,1,108,2090021318,0,3,"(7,18)",11,2313,32,
> 10001100111101001000
> 00111000,532753459)
>  (19,8080,1,108,753125991,0,2,"(7,19)",11,2825,32,
> 10110001010111001011
> 1111,236796556)
>  (20,7968,1,108,753125991,0,4,"(7,20)",11,2825,32,
> 100010110001010111001011
> 1111,236796557)
>  (21,7856,1,108,753125992,0,19,"(7,21)",11,2825,32,
> 100011001001010111001011
> 1111,236796563)
>  (22,7744,1,108,753125992,0,21,"(7,22)",11,2825,32,
> 10101001010111001011
> 1111,236796564)
>  (23,7632,1,108,753125993,0,36,"(7,23)",11,2825,32,
> 11011001010111001011
> 1111,236796570)
>  (24,7520,1,108,753125993,0,38,"(7,24)",11,2825,32,
> 100011011001010111001011
> 1111,236796571)
>  (25,7408,1,108,753125994,0,53,"(7,25)",11,2825,32,
> 10001101010111001011
> 1111,236796577)
>  (26,7296,1,108,753125994,0,55,"(7,26)",11,2825,32,
> 11000101010111001011
> 1111,236796578)
>  (27,7184,1,108,753125995,0,70,"(7,27)",11,2825,32,

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Andres Freund]

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 not visible as corrupted.

Greetings,

Andres Freund

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Peter Geoghegan]

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: STRING_AGG and GROUP BY

[Alexander Farber]

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
> mid_tiles,
> (SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS
> mid_words
> FROM moves​
>
> There are other ways to write that that could perform better but the idea
> holds.
>
>
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
FROM (
SELECT
mid,
JSONB_ARRAY_ELEMENTS(m.tiles) AS x
FROM moves m
WHERE m.gid = 1
) AS z
GROUP BY mid),
cte2 AS (
SELECT
mid,
STRING_AGG(y, ', ') AS words
FROM (
SELECT
mid,
FORMAT('%s (%s)', s.word, s.score) AS y
FROM scores s
WHERE s.gid = 1
) AS z
GROUP BY mid)
SELECT mid, tiles, words
FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;

Regards
Alex

P.S. Below is the complete test data in case SQL Fiddle link stops working:

CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);

CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
player2 integer NOT NULL REFERENCES players ON DELETE CASCADE
);

CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb NOT NULL
);

CREATE TABLE scores (
mid bigint  NOT NULL REFERENCES moves ON DELETE CASCADE,
uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
wordtextNOT NULL CHECK(word ~ '^[A-Z]{2,}$'),
score   integer NOT NULL CHECK(score >= 0)
);

INSERT INTO players (name) VALUES ('Alice'), ('Bob');
INSERT INTO games (player1, player2) VALUES (1, 2);

INSERT INTO moves (uid, gid, played, tiles) VALUES
(1, 1, now() + interval '1 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "A"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '2 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "X"}, {"col": 8, "row": 12, "value": 10, "letter": "X"}, {"col":
9, "row": 12, "value": 1, "letter": "Z"}]
'::jsonb),
(1, 1, now() + interval '3 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "K"}, {"col": 8, "row": 12, "value": 10, "letter": "K"}, {"col":
9, "row": 12, "value": 1, "letter": "M"}, {"col": 10, "row": 12, "value":
2, "letter": "N"}]
'::jsonb),
(2, 1, now() + interval '4 min', '[]'::jsonb),
(1, 1, now() + interval '5 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '6 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "P"}, {"col": 8, "row": 12, "value": 10, "letter": "Q"}]
'::jsonb);

INSERT INTO scores (mid, uid, gid, word, score) VALUES
(1, 1, 1, 'AACD', 40),
(2, 2, 1, 'XXZ', 30),
(2, 2, 1, 'XAB', 30),
(3, 1, 1, 'KKMN', 40),
(3, 1, 1, 'KYZ', 30),
(5, 1, 1, 'ABCD', 40),
(6, 2, 1, 'PQ', 20),
(6, 2, 1, 'PABCD', 50);

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 :

staging=# SELECT * FROM page_header(get_raw_page('pg_authid', 7));
  lsn   | checksum | flags | lower | upper | special | pagesize |
version | prune_xid
+--+---+---+---+-+--+-+---
 262B4/10FDC478 |0 | 1 |   304 |  2224 |8192 | 8192 |
 4 | 0
(1 row)

@Andres :

This is from snapshot (on 9.5.12, but we didn't have the error either on a
9.5.11 snap):

 heap_page_items
-
 (1,0,0,0,)
 (2,0,0,0,)
 (3,0,0,0,)
 (4,0,0,0,)
 (5,0,0,0,)
 
(6,8080,1,108,3137434815,0,0,"(7,6)",11,10505,32,111011001001,2166427518)
 
(7,7936,1,144,3137434816,0,0,"(7,7)",11,10507,32,1100011101110111010110111011,3504005358)
 (8,0,0,0,)
 (9,0,0,0,)
 
(10,7792,1,144,3137434817,0,0,"(7,10)",11,10507,32,1100010101100011011101101000,401353834)
 
(11,7680,1,108,3137434818,0,0,"(7,11)",11,10505,32,111000110100100100010111,2248708806)
 (12,0,0,0,)
 (13,0,0,0,)
 
(14,7568,1,108,3137434819,0,0,"(7,14)",11,10505,32,1111000011011011100010100101,2770187214)
 
(15,7456,1,108,3137434820,0,0,"(7,15)",11,10505,32,100000010100010100001011,2235343503)
 (16,0,0,0,)
 (17,0,0,0,)
 (18,0,0,0,)
 (19,0,0,0,)
 (20,0,0,0,)
 (21,0,0,0,)
 (22,0,0,0,)
 (23,0,0,0,)
 (24,0,0,0,)
 (25,0,0,0,)
 (26,0,0,0,)
 (27,0,0,0,)
 (28,0,0,0,)
 (29,0,0,0,)
 (30,0,0,0,)
 (31,0,0,0,)
 (32,0,0,0,)
 (33,0,0,0,)
 (34,0,0,0,)
 (35,0,0,0,)
 (36,0,0,0,)
 (37,0,0,0,)
 (38,0,0,0,)
 (39,0,0,0,)
 (40,0,0,0,)
 (41,0,0,0,)
 (42,0,0,0,)
 (43,0,0,0,)
 (44,0,0,0,)
 (45,0,0,0,)
 (46,0,0,0,)
 (47,0,0,0,)
 (48,0,0,0,)
 (49,0,0,0,)
 (50,0,0,0,)
 (51,0,0,0,)
 
(52,7344,1,108,3137434821,0,0,"(7,52)",11,10505,32,1000110110111000101001010101,2191859675)
 
(53,7232,1,108,3137434822,0,0,"(7,53)",11,10505,32,1110101101000110011011100100,661027542)
 (54,0,0,0,)
 (55,0,0,0,)
 (56,0,0,0,)
 (57,0,0,0,)
 (58,0,0,0,)
 (59,0,0,0,)
 (60,0,0,0,)
 
(61,7120,1,108,3137434823,0,0,"(7,61)",11,10505,32,1001011101011101010111010100,732568296)
 
(62,6976,1,144,3137434824,0,0,"(7,62)",11,10507,32,1100101001100100101011010100,674571301)
 
(63,6864,1,108,3137434825,0,0,"(7,63)",11,10505,32,1000100110110101111001010001,2319695577)
 
(64,6720,1,144,3137434826,0,0,"(7,64)",11,10507,32,1100011001100011100100101000,345892418)
 
(65,6608,1,108,3137434827,0,0,"(7,65)",11,10505,32,1101010100010010101011001010,1398049410)
 
(66,6496,1,108,3137434828,0,0,"(7,66)",11,10505,32,1010100000110101001000101010,1414188820)
 
(67,6384,1,108,3137434829,0,0,"(7,67)",11,10505,32,1011010101110011001110101001,2513301164)
 (68,0,0,0,)
 (69,0,0,0,)
 (70,0,0,0,)
(70 rows)


Thanks,
Jeremy

Re: STRING_AGG and GROUP BY

[David G. Johnston]

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
> FROM (
> SELECT
> mid,
> JSONB_ARRAY_ELEMENTS(m.tiles) AS x
> FROM moves m
> WHERE m.gid = 1
> ) AS z
> GROUP BY mid),
> cte2 AS (
> SELECT
> mid,
> STRING_AGG(y, ', ') AS words
> FROM (
> SELECT
> mid,
> FORMAT('%s (%s)', s.word, s.score) AS y
> FROM scores s
> WHERE s.gid = 1
> ) AS z
> GROUP BY mid)
> SELECT mid, tiles, words
> FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;
>
>
​​Yes.  It does end up presuming that the sets moves.mid and scores.mid
​are identical but that is probably a safe assumption.  Repetition of m.gid
= 1 is worth avoiding in theory though depending on how its done the
solution can be worse than the problem (if the planner ends up unable to
push the predicate down).

​David J.

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Peter Geoghegan]

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
> +--+---+---+---+-+--+-+---
>  262B4/10FDC478 |0 | 1 |   304 |  2224 |8192 | 8192 |
> 4 | 0
> (1 row)

Thanks.

That looks normal. I wonder if the contents of that page looks
consistent with the rest of the table following manual inspection,
though. I recently saw system catalog corruption on a 9.5 instance
where an entirely different relation's page ended up in pg_attribute
and pg_depend. They were actually pristine index pages from an
application index. I still have no idea why this happened.

This is very much a guess, but it can't hurt to check if the contents
of the tuples themselves are actually sane by inspecting them with
"SELECT * FROM pg_authid". heap_page_items() doesn't actually care
about the shape of the tuples in the page, so this might have been
missed.

-- 
Peter Geoghegan

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 | prune_xid
> > +--+---+---+---+
> -+--+-+---
> >  262B4/10FDC478 |0 | 1 |   304 |  2224 |8192 | 8192 |
> > 4 | 0
> > (1 row)
>
> Thanks.
>
> That looks normal. I wonder if the contents of that page looks
> consistent with the rest of the table following manual inspection,
> though. I recently saw system catalog corruption on a 9.5 instance
> where an entirely different relation's page ended up in pg_attribute
> and pg_depend. They were actually pristine index pages from an
> application index. I still have no idea why this happened.
>
> This is very much a guess, but it can't hurt to check if the contents
> of the tuples themselves are actually sane by inspecting them with
> "SELECT * FROM pg_authid". heap_page_items() doesn't actually care
> about the shape of the tuples in the page, so this might have been
> missed.
>
> --
> Peter Geoghegan
>

The data all looks fine.  I even forced the index scan on both indexes
which also looks fine.

Thanks,
Jeremy