Ideas to deal with table corruption

[Luis Marin]

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

Re: What generates pg_config.h?

[Adrian Klaver]

On 01/05/2018 09:36 PM, Travis Allison wrote:
Tom, I guess I don't understand the point of having a different 
pg_config.h in my user/include/postgresql directory than the postgresql 
server that I am running.  You mentioned building client code that would 
reference the pg_config.h in user/include/postgresql.  A couple of 
questions:


What type of client code are you referring to?
What is the point of having a pg_config.h for a server that I am not 
even running?


Not Tom, but to answer:

As to client code, that would include the Postgres CLI client psql or 
the Python adapter psycopg2 among others. This code does not need to be 
on the server machine, it can be anywhere you want a client to connect 
to a Postgres server.


The point of a more recent pg_config.h is that client code compiled with 
it would be backwards compatible whereas compiling against an older 
version would result in code that would not work against newer servers.


The problem you are running into is that the build process is using both 
the older(9.6.5) and newer(10.0) pg_config.h at the same time.





Travis





On Fri, Jan 5, 2018 at 8:05 PM, Tom Lane > wrote:


Travis Allison mailto:travisalli...@gmail.com>> writes:
> I suppose an easy workaround is to copy my version 9.6 pg_config.h into my
> user/include/postgresql directory and replace the one installed by
> libpq-dev.

> Can you think of any detrimental side-effects from doing that?

I would not recommend that: it's likely to break code that's expecting
the Ubuntu-standard layout.  I think your best course is to adapt the
particular package you're having trouble with to work with that layout.
Maybe contact its author for help?

                         regards, tom lane





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

Re: What generates pg_config.h?

[Christoph Berg]

Re: Adrian Klaver 2018-01-06 <67591f85-a910-2e0b-1fdd-9c774eacd...@aklaver.com>
> The problem you are running into is that the build process is using both the
> older(9.6.5) and newer(10.0) pg_config.h at the same time.

Fwiw, all issues I've seen so far of that kind could be resolved by
putting -I$(pg_config --includedir-server) before -I$(pg_config
--includedir) in the Makefile.

I don't know of any PG extension existing in the wild that does not
work with having both /usr/include/postgresql/$version/server/pg_config.h
and /usr/include/postgresql/pg_config.h installed - and we are
packaging a lot of extensions for apt.postgresql.org for non-latest
majors.

Christoph

Re: Ideas to deal with table corruption

[Adrian Klaver]

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

Re: Ideas to deal with table corruption

[Rui DeSousa]

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.

Re: Ideas to deal with table corruption

[Peter Geoghegan]

On Sat, Jan 6, 2018 at 2:11 AM, Luis Marin  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

Re: Ideas to deal with table corruption

[Melvin Davidson]

On Sat, Jan 6, 2018 at 1:23 PM, Peter Geoghegan  wrote:

> On Sat, Jan 6, 2018 at 2:11 AM, Luis Marin 
> 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.

Re: Ideas to deal with table corruption

[Corey Taylor]

On Sat, Jan 6, 2018 at 12:30 PM, Melvin Davidson 
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."

Re: Ideas to deal with table corruption

[scott ribe]

On Jan 6, 2018, at 12:11 PM, Corey Taylor  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

Re: Ideas to deal with table corruption

[Rui DeSousa]

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
;

Re: What generates pg_config.h?

[Travis Allison]

I wanted to thank all of you for solving my problem.  You have all been
unbelievably helpful!

Travis

On Sat, Jan 6, 2018 at 7:42 AM, Christoph Berg  wrote:

> Re: Adrian Klaver 2018-01-06 <67591f85-a910-2e0b-1fdd-
> 9c774eacd...@aklaver.com>
> > The problem you are running into is that the build process is using both
> the
> > older(9.6.5) and newer(10.0) pg_config.h at the same time.
>
> Fwiw, all issues I've seen so far of that kind could be resolved by
> putting -I$(pg_config --includedir-server) before -I$(pg_config
> --includedir) in the Makefile.
>
> I don't know of any PG extension existing in the wild that does not
> work with having both /usr/include/postgresql/$version/server/pg_config.h
> and /usr/include/postgresql/pg_config.h installed - and we are
> packaging a lot of extensions for apt.postgresql.org for non-latest
> majors.
>
> Christoph
>

Two columns with same name in subselect--any way to SELECT without *?

[Ken Tanzer]

Hi.  You can have multiple columns with the same name, and use it as a
subselect, like this silly example:

SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other;
SELECT * FROM (SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other) foo;

But is there any way to select either of those columns without taking all
the fields with *?

SELECT my_col,other FROM (select 'a' AS my_col,'b' AS my_col,'foo' AS
other) foo;
ERROR:  column reference "my_col" is ambiguous

I suspect there isn't, but just wondering if there's some way I'm not
aware of.

I found myself wanting to specify the columns  by position, similar to
ORDER or GROUP.  (*.1, foo.1 or something like that), but that's
probably wishful thinking!

Cheers,

Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Two columns with same name in subselect--any way to SELECT without *?

[Adrian Klaver]

On 01/06/2018 08:46 PM, Ken Tanzer wrote:
Hi.  You can have multiple columns with the same name, and use it as a 
subselect, like this silly example:


SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other;
SELECT * FROM (SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other) foo;

But is there any way to select either of those columns without taking 
all the fields with *?


SELECT my_col,other FROM (select 'a' AS my_col,'b' AS my_col,'foo' AS other) 
foo;
ERROR:  column reference "my_col" is ambiguous

I suspect there isn't, but just wondering if there's some way I'm not 
aware of.


?:
SELECT bar.my_col, foo.my_col FROM (SELECT 'a' AS my_col) as bar , 
(select 'b' AS my_col,'foo' AS other) foo;


 my_col | my_col
+
 a  | b

Though I would think this would just be pushing the point where you get 
confused what my_col is really pointing to down the road.




I found myself wanting to specify the columns by position, similar to 
ORDER or GROUP. (*.1, foo.1 or something like that), but that's probably 
wishful thinking!


Cheers,

Ken



--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



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

Re: Two columns with same name in subselect--any way to SELECT without *?

[Ken Tanzer]

On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver 
wrote:

> On 01/06/2018 08:46 PM, Ken Tanzer wrote:
>
>> Hi.  You can have multiple columns with the same name, and use it as a
>> subselect, like this silly example:
>>
>> SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other;
>> SELECT * FROM (SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other) foo;
>>
>> But is there any way to select either of those columns without taking all
>> the fields with *?
>>
>> SELECT my_col,other FROM (select 'a' AS my_col,'b' AS my_col,'foo' AS
>> other) foo;
>> ERROR:  column reference "my_col" is ambiguous
>>
>> I suspect there isn't, but just wondering if there's some way I'm not
>> aware of.
>>
>
> ?:
> SELECT bar.my_col, foo.my_col FROM (SELECT 'a' AS my_col) as bar , (select
> 'b' AS my_col,'foo' AS other) foo;
>
>  my_col | my_col
> +
>  a  | b
>
> Though I would think this would just be pushing the point where you get
> confused what my_col is really pointing to down the road.
>

Thanks Adrian, but I was really wondering about the case where the two
columns are already in a single result set.  I came across this issue
accidentally, and it's not causing any problems.  Just trying to understand
the possibilities/limitations for future reference.

Cheers,
Ken

Re: Two columns with same name in subselect--any way to SELECT without *?

[Ken Tanzer]

On Sat, Jan 6, 2018 at 9:16 PM, Ken Tanzer  wrote:

>
>
> On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver 
> wrote:
>
>> On 01/06/2018 08:46 PM, Ken Tanzer wrote:
>>
>>> Hi.  You can have multiple columns with the same name, and use it as a
>>> subselect, like this silly example:
>>>
>>> SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other;
>>> SELECT * FROM (SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other) foo;
>>>
>>> But is there any way to select either of those columns without taking
>>> all the fields with *?
>>>
>>> SELECT my_col,other FROM (select 'a' AS my_col,'b' AS my_col,'foo' AS
>>> other) foo;
>>> ERROR:  column reference "my_col" is ambiguous
>>>
>>> I suspect there isn't, but just wondering if there's some way I'm not
>>> aware of.
>>>
>>
>> ?:
>> SELECT bar.my_col, foo.my_col FROM (SELECT 'a' AS my_col) as bar ,
>> (select 'b' AS my_col,'foo' AS other) foo;
>>
>>  my_col | my_col
>> +
>>  a  | b
>>
>> Though I would think this would just be pushing the point where you get
>> confused what my_col is really pointing to down the road.
>>
>
> Thanks Adrian, but I was really wondering about the case where the two
> columns are already in a single result set.  I came across this issue
> accidentally, and it's not causing any problems.  Just trying to understand
> the possibilities/limitations for future reference.
>
> Cheers,
> Ken
>
> So having thought about this a little more, it seems like once you create
a result set with identically-named columns, those columns are effectively
crippled.  In that they can be viewed (via SELECT *), but not referenced,
used or acted upon in any way.  Still just wanting to confirm this is/is
not the case.  Thanks!

Ken

Re: Two columns with same name in subselect--any way to SELECT without *?

[David G. Johnston]

On Saturday, January 6, 2018, Ken Tanzer  wrote:
>
> So having thought about this a little more, it seems like once you create
> a result set with identically-named columns, those columns are effectively
> crippled.  In that they can be viewed (via SELECT *), but not referenced,
> used or acted upon in any way.  Still just wanting to confirm this is/is
> not the case.  Thanks!
>

I'm not really following the intent of the question but if your client API
allows for column references by position instead of name you can still
distinguish between them, too.

In subqueries or CREATE TABLE AS, no, the system, is going to force you to
differentiate them since all SQL references are by name.

David J.