Обсуждение: Ideas to deal with table corruption

On 01/06/2018 02:11 AM, Luis Marin wrote:
> 
> Dear community,
> 
> I am looking for ideas, to help me, check what is happening with a 
> possible  table corruption, I  have some FK that works ok, but some stay 
> in the state waiting forever, however selecting the same table works fine.

 From further comments below I think you are talking about creating a FK 
on a table, is that correct?

If not, what are you trying to do?

The table schema and code showing your process would be helpful.

And yes tailing the Postgres log file during the above could be helpful.

> 
> Since, I am a newbie, in PostgreSQL, what should be my firsts steps, in 
> order to have an idea how to fix this problem.
> 
> I am thinking in watching my postgresql log error file during the 
> requests for creating our FK,
> What do you suggest me
> 
> Centos 7.2
> Postgresql 9.4
> 
> Thanks


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 01/06/2018 02:11 AM, Luis Marin wrote:
> 
> Dear community,
> 
> I am looking for ideas, to help me, check what is happening with a 
> possible  table corruption, I  have some FK that works ok, but some stay 
> in the state waiting forever, however selecting the same table works fine.

 From further comments below I think you are talking about creating a FK 
on a table, is that correct?

If not, what are you trying to do?

The table schema and code showing your process would be helpful.

And yes tailing the Postgres log file during the above could be helpful.

> 
> Since, I am a newbie, in PostgreSQL, what should be my firsts steps, in 
> order to have an idea how to fix this problem.
> 
> I am thinking in watching my postgresql log error file during the 
> requests for creating our FK,
> What do you suggest me
> 
> Centos 7.2
> Postgresql 9.4
> 
> Thanks


-- 
Adrian Klaver
adrian.klaver@aklaver.com

How large are the given tables and is the databases in heavy use at the time? It sounds like either blocking is
occurringor you’re dealing with large tables and the validation is take a long time; which, in both case is normal. 

Try creating the foreign key without validation, i.e. use the “not valid” clause.  That will create the foreign key and
startto enforce it; however, existing data may not conform thus Postgres will report it as not valid.  Then you can
validatethe foreign key which occurs concurrently.  This is the approach I use on live production systems to avoid
blockingissues. 

i.e.:

 alter table member_outline
 add constraint member_outline_fkey1
 foreign key (dimension, member) references member(dimension, member)
 on update cascade not valid
 ;

 alter table member_outline
 validate constraint member_outline_fkey1
 ;

Nothing that you stated points to any table corruption.

How large are the given tables and is the databases in heavy use at the time? It sounds like either blocking is
occurringor you’re dealing with large tables and the validation is take a long time; which, in both case is normal. 

Try creating the foreign key without validation, i.e. use the “not valid” clause.  That will create the foreign key and
startto enforce it; however, existing data may not conform thus Postgres will report it as not valid.  Then you can
validatethe foreign key which occurs concurrently.  This is the approach I use on live production systems to avoid
blockingissues. 

i.e.:

 alter table member_outline
 add constraint member_outline_fkey1
 foreign key (dimension, member) references member(dimension, member)
 on update cascade not valid
 ;

 alter table member_outline
 validate constraint member_outline_fkey1
 ;

Nothing that you stated points to any table corruption.

On Sat, Jan 6, 2018 at 2:11 AM, Luis Marin <luismarinaray@gmail.com> wrote:
> I am looking for ideas, to help me, check what is happening with a possible
> table corruption, I  have some FK that works ok, but some stay in the state
> waiting forever, however selecting the same table works fine.
>
> Since, I am a newbie, in PostgreSQL, what should be my firsts steps, in
> order to have an idea how to fix this problem.
>
> I am thinking in watching my postgresql log error file during the requests
> for creating our FK,
> What do you suggest me

There are CentOS packages for amcheck on 9.4:

https://github.com/petergeoghegan/amcheck#redhatcentossles

I would start there.

-- 
Peter Geoghegan

On Sat, Jan 6, 2018 at 2:11 AM, Luis Marin <luismarinaray@gmail.com> wrote:
> I am looking for ideas, to help me, check what is happening with a possible
> table corruption, I  have some FK that works ok, but some stay in the state
> waiting forever, however selecting the same table works fine.
>
> Since, I am a newbie, in PostgreSQL, what should be my firsts steps, in
> order to have an idea how to fix this problem.
>
> I am thinking in watching my postgresql log error file during the requests
> for creating our FK,
> What do you suggest me

There are CentOS packages for amcheck on 9.4:

https://github.com/petergeoghegan/amcheck#redhatcentossles

I would start there.

-- 
Peter Geoghegan

On Jan 6, 2018, at 12:11 PM, Corey Taylor <corey.taylor.fl@gmail.com> wrote:
> Is it possible for an index to not exist on those columns?
>
> https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
>
> "A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the
referencedcolumns always have an index (the one underlying the primary key or unique constraint); so checks on whether
areferencing row has a match will be efficient." 

One should also remember that the foreign key itself should usually be indexed, lest a delete on the referenced table
requirea table scan on the referencing table. 

--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567

On Jan 6, 2018, at 12:11 PM, Corey Taylor <corey.taylor.fl@gmail.com> wrote:
> Is it possible for an index to not exist on those columns?
>
> https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
>
> "A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the
referencedcolumns always have an index (the one underlying the primary key or unique constraint); so checks on whether
areferencing row has a match will be efficient." 

One should also remember that the foreign key itself should usually be indexed, lest a delete on the referenced table
requirea table scan on the referencing table. 

--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567

Correct, and there is no need to create an index on a unique constraint or primary key as they are already implemented
viaindexes.  I can’t count how many duplicate indexes I’ve dropped in the past.  I use this view help find duplicates
ina given system.  Duplicate indexes just use up space and rob performance during updates and inserts.    

/*======================================================================================================
   q$Id: duplicate_index.sql 1 2015-12-19 15:40:29Z rui $
   Description: Find duplicate indexes
======================================================================================================*/
create or replace view duplicate_index
as
select base.indrelid::regclass as table_name
  , string_agg((dup.indexrelid::regclass)::text, E'\n') as indexes
  , pg_size_pretty(avg(pg_relation_size(dup.indexrelid))) as avg_size
from pg_index base
join pg_index dup on dup.indrelid = base.indrelid  -- table identifier
  and dup.indkey = base.indkey  --  columns indexed
  and dup.indclass = base.indclass  -- columns types
  and (
    dup.indexprs = base.indexprs -- expression predicate for columns
    or  (
      dup.indexprs is null
      and base.indexprs is null
    )
  )
  and (
    dup.indpred = base.indpred  -- expression predicate for where clause
    or (
       dup.indpred is null
       and base.indpred is null
      )
  )
  and dup.indexrelid != base.indexrelid  --index identifier
group by base.indrelid::regclass
  , concat(base.indkey::text, base.indclass::text, base.indexprs, base.indpred)
order by avg_size desc
  , base.indrelid::regclass
;

Correct, and there is no need to create an index on a unique constraint or primary key as they are already implemented
viaindexes.  I can’t count how many duplicate indexes I’ve dropped in the past.  I use this view help find duplicates
ina given system.  Duplicate indexes just use up space and rob performance during updates and inserts.    

/*======================================================================================================
   q$Id: duplicate_index.sql 1 2015-12-19 15:40:29Z rui $
   Description: Find duplicate indexes
======================================================================================================*/
create or replace view duplicate_index
as
select base.indrelid::regclass as table_name
  , string_agg((dup.indexrelid::regclass)::text, E'\n') as indexes
  , pg_size_pretty(avg(pg_relation_size(dup.indexrelid))) as avg_size
from pg_index base
join pg_index dup on dup.indrelid = base.indrelid  -- table identifier
  and dup.indkey = base.indkey  --  columns indexed
  and dup.indclass = base.indclass  -- columns types
  and (
    dup.indexprs = base.indexprs -- expression predicate for columns
    or  (
      dup.indexprs is null
      and base.indexprs is null
    )
  )
  and (
    dup.indpred = base.indpred  -- expression predicate for where clause
    or (
       dup.indpred is null
       and base.indpred is null
      )
  )
  and dup.indexrelid != base.indexrelid  --index identifier
group by base.indrelid::regclass
  , concat(base.indkey::text, base.indclass::text, base.indexprs, base.indpred)
order by avg_size desc
  , base.indrelid::regclass
;