pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIdeas to deal with table corruption
8+ messages / 7 participants
[nested] [flat]
* Ideas to deal with table corruption
@ 2018-01-06 10:11 Luis Marin <luismarinaray@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Luis Marin @ 2018-01-06 10:11 UTC (permalink / raw)
To: pgsql-admin; pgsql-general@postgresql.org
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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 17:39 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Luis Marin <luismarinaray@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2018-01-06 17:39 UTC (permalink / raw)
To: Luis Marin <luismarinaray@gmail.com>; pgsql-admin; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 18:16 Rui DeSousa <rui.desousa@icloud.com>
parent: Luis Marin <luismarinaray@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2018-01-06 18:16 UTC (permalink / raw)
To: Luis Marin <luismarinaray@gmail.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org
How large are the given tables and is the databases in heavy use at the time? It sounds like either blocking is occurring or 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 start to enforce it; however, existing data may not conform thus Postgres will report it as not valid. Then you can validate the foreign key which occurs concurrently. This is the approach I use on live production systems to avoid blocking issues.
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 18:23 Peter Geoghegan <pg@bowt.ie>
parent: Luis Marin <luismarinaray@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2018-01-06 18:23 UTC (permalink / raw)
To: Luis Marin <luismarinaray@gmail.com>; +Cc: pgsql-admin@postgresql.org, pgsql-general General <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 18:30 Melvin Davidson <melvin6925@gmail.com>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 8+ messages in thread
From: Melvin Davidson @ 2018-01-06 18:30 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Luis Marin <luismarinaray@gmail.com>; pgsql-admin@postgresql.org, pgsql-general General <pgsql-general@postgresql.org>
On Sat, Jan 6, 2018 at 1:23 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> 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
>
>
*>"some stay in the state waiting forever"*
*Don't forget to create indexes on the FK's in the table they reference!*
*Also, it would be nice to know the PostgreSQL version and O/S.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 19:11 Corey Taylor <corey.taylor.fl@gmail.com>
parent: Melvin Davidson <melvin6925@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Corey Taylor @ 2018-01-06 19:11 UTC (permalink / raw)
To: Melvin Davidson <melvin6925@gmail.com>; +Cc: Peter Geoghegan <pg@bowt.ie>; Luis Marin <luismarinaray@gmail.com>; pgsql-admin@postgresql.org, pgsql-general General <pgsql-general@postgresql.org>
On Sat, Jan 6, 2018 at 12:30 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:
>
> *Don't forget to create indexes on the FK's in the table they reference!*
>
>
> *Also, it would be nice to know the PostgreSQL version and O/S.*
>
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 referenced columns always have an
index (the one underlying the primary key or unique constraint); so checks
on whether a referencing row has a match will be efficient."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 19:14 scott ribe <scott_ribe@elevated-dev.com>
parent: Corey Taylor <corey.taylor.fl@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: scott ribe @ 2018-01-06 19:14 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, pgsql-general General <pgsql-general@postgresql.org>
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 referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing 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 require a table scan on the referencing table.
--
Scott Ribe
https://www.linkedin.com/in/scottribe/
(303) 722-0567
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Ideas to deal with table corruption
@ 2018-01-06 19:59 Rui DeSousa <rui.desousa@icloud.com>
parent: Corey Taylor <corey.taylor.fl@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2018-01-06 19:59 UTC (permalink / raw)
To: Corey Taylor <corey.taylor.fl@gmail.com>; +Cc: Melvin Davidson <melvin6925@gmail.com>; Peter Geoghegan <pg@bowt.ie>; Luis Marin <luismarinaray@gmail.com>; pgsql-admin@postgresql.org, pgsql-general General <pgsql-general@postgresql.org>
Correct, and there is no need to create an index on a unique constraint or primary key as they are already implemented via indexes. I can’t count how many duplicate indexes I’ve dropped in the past. I use this view help find duplicates in a 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
;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-01-06 19:59 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-06 10:11 Ideas to deal with table corruption Luis Marin <luismarinaray@gmail.com>
2018-01-06 17:39 ` Adrian Klaver <adrian.klaver@aklaver.com>
2018-01-06 18:16 ` Rui DeSousa <rui.desousa@icloud.com>
2018-01-06 18:23 ` Peter Geoghegan <pg@bowt.ie>
2018-01-06 18:30 ` Melvin Davidson <melvin6925@gmail.com>
2018-01-06 19:11 ` Corey Taylor <corey.taylor.fl@gmail.com>
2018-01-06 19:14 ` scott ribe <scott_ribe@elevated-dev.com>
2018-01-06 19:59 ` Rui DeSousa <rui.desousa@icloud.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox