Re: Does PostgreSQL check database integrity at startup?

[Jan Wieck]

On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost  wrote:

>
> The checksums included in PG are page-level and therefore there simply
> isn't one to look at if the file is zero bytes.
>

And even if the file wasn't zero bytes you can't tell from the per page
CRCs if you have all the pages you should have. You could have extra pages
that aren't supposed to  be there or missing some (or any mix of the two).
A per page CRC is useless for those cases.


Regards, Jan





>
> Thanks!
>
> Stephen
>



-- 
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info

Re: Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

[Stephen Frost]

Greetings Andreas,

* Andreas Joseph Krogh (andr...@visena.com) wrote:
> Here's a simple test-case:
>  
> createdb test
> test=# create table foo(id serial primary key, name varchar not null);
> test=# CREATE TYPE BigIntTuple2 AS (f1 bigint, f2 bigint);

Hah, appears we allocate a relfilenode to types too, though it's not
entirely clear to me why (evidently, they're not used..).

Anyhow, adding a filter on relkind addresses it (though it's not very
future-proof, unfortunately):

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size 
as size
FROM 
  pg_settings s 
  JOIN pg_database db on (s.name = 'data_directory') 
  JOIN pg_class c on (datname = current_database()) 
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid) 
WHERE
  relfilenode <> 0 
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema'
  AND c.relkind in ('r','i','m','p');

Thanks!

Stephen


signature.asc
Description: PGP signature

Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

[Andreas Joseph Krogh]

På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost <
sfr...@snowman.net >:
* Andreas Joseph Krogh (andr...@visena.com) wrote:
 >  SELECT
 >    quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
 >    s.setting || '/base/' || db.oid || '/' || c.relfilenode,
 >    (pg_stat_file(s.setting || '/base/' || db.oid || '/' ||
 > c.relfilenode)).size as size
 >  FROM
 >    pg_settings s
 >    JOIN pg_database db on (s.name = 'data_directory')
 >    JOIN pg_class c on (datname = current_database())
 >    JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
 >  WHERE
 >    relfilenode <> 0
 >    AND nsp.nspname !~ '^pg_'
 >    AND nsp.nspname <> 'information_schema';
 >
 >  Technically speaking, while these queries are correct for PG10, in prior
 >  versions of PostgreSQL it's possible to have user schemas that begin
 >  with 'pg_' and therefore the filtering in the WHERE clause would have to
 >  be more specific.
 >
 >  Note that both of these need to be run as a superuser in older versions
 >  of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
 >  and be able to run the first query.  We don't currently support being
 >  able to GRANT a non-superuser the ability to run pg_stat_file(), but
 >  that will likely be coming in PG 11.
 >
 >  Thanks!
 >  
 > That doesn't seem to work with custom types:

 Nothing in this query referred to types, so I'm not sure what custom
 types would have to do with it..?

 > andreak@[local]:5433 10.1 andreak=# SELECT 
 >   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
 >   s.setting || '/base/' || db.oid || '/' || c.relfilenode,
 >   (pg_stat_file(s.setting || '/base/' || db.oid || '/' || 
c.relfilenode)).size
 > as size
 >  FROM  
 >   pg_settings s  
 >   JOIN pg_database db on (s.name = 'data_directory')  
 >   JOIN pg_class c on (datname = current_database())  
 >   JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)  
 >  WHERE
 >   relfilenode <> 0  
 >   AND nsp.nspname !~ '^pg_'
 >   AND nsp.nspname <> 'information_schema';
 >  ERROR:  could not stat file
 > "/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such
 > file or directory
 >   
 > │ public.biginttuple2 │
 > /home/andreak/programs/postgresql-10/data/base/22039391/22039392 │

 Considering this is saaying 'no such file or directory', I'm guessing
 that somehow your data directory isn't what is listed in pg_settings..?

 Alternatively, perhaps that table was concurrently dropped?

 Are you able to provide any specifics about your system?  Does the
 database directory exist?  Does that path look reasonable?  I find it
 kind of interesting that the OID of the database and the relfilenode are
 so close together- exactly what did you do to test this query?
 
 
Here's a simple test-case:
 
createdb test
test=# create table foo(id serial primary key, name varchar not null);
test=# CREATE TYPE BigIntTuple2 AS (f1 bigint, f2 bigint);
  
SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode
FROM
  pg_settings s
  JOIN pg_database db on (s.name  = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';
  

┌─┬──┐
 │  ?column?   │ ?column? 
│
 
├─┼──┤
 │ public.foo_id_seq   │ 
/home/andreak/programs/postgresql-10/data/base/22058766/22058767 │
 │ public.foo  │ 
/home/andreak/programs/postgresql-10/data/base/22058766/22058769 │
 │ public.foo_pkey │ 
/home/andreak/programs/postgresql-10/data/base/22058766/22058776 │
 │ public.biginttuple2 │ 
/home/andreak/programs/postgresql-10/data/base/22058766/22058778 │
 
└─┴──┘
  
 
SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size 
as size
FROM
  pg_settings s
  JOIN pg_database db on (s.name  = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';
  
ERROR:  could not stat file 
"/home/andreak/programs/postgresql-10/data/base/22058766/22058778": No such 
file or directory
  
$ file /home/andreak/programs/postgresql-10/data/base/22058766/22058776 
 /home/andreak/programs/postgresql-10/data/base/22058766/22058776: lif file
  
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS

RE: Change column type macaddr to macaddr[]

[Ertan Küçükoğlu]

>From: David G. Johnston [mailto:david.g.johns...@gmail.com] 
>Sent: Sunday, December 31, 2017 2:56 AM
>To: Ertan Küçükoğlu 
>Cc: pgsql-gene...@postgresql.org
>Subject: Re: Change column type macaddr to macaddr[]
>
>Maybe try:
>
>Using array[mac]::macaddr[]
>
>David J. 

Yes, that did the job.

Thank you.

Re: Change column type macaddr to macaddr[]

[David G. Johnston]

On Saturday, December 30, 2017, Ertan Küçükoğlu 
wrote:

> Hello,
>
> I am using PostgreSQL 9.6.6 on armv8l-unknown-linux-gnueabihf, compiled by
> gcc (Raspbian 6.3.0-18+rpi1) 6.3.0 20170516, 32-bit
>
> I have a table with column name mac type macaddr. I need to change it into
> array of macaddr ( macaddr[] ) type. Among all others I did try below:
>
> licenses=# alter table licreqm alter column mac type macaddr[] using
> mac::macaddr[];
> ERROR:  cannot cast type macaddr to macaddr[]
> LINE 1: ... licreqm alter column mac type macaddr[] using mac::macaddr[...
>  ^
> I believe there should be a way, I just do not know it myself. Anyone can
> help me?
>

Maybe try:

Using array[mac]::macaddr[]

David J.

Change column type macaddr to macaddr[]

[Ertan Küçükoğlu]

Hello,

I am using PostgreSQL 9.6.6 on armv8l-unknown-linux-gnueabihf, compiled by
gcc (Raspbian 6.3.0-18+rpi1) 6.3.0 20170516, 32-bit

I have a table with column name mac type macaddr. I need to change it into
array of macaddr ( macaddr[] ) type. Among all others I did try below:

licenses=# alter table licreqm alter column mac type macaddr[] using
mac::macaddr[];
ERROR:  cannot cast type macaddr to macaddr[]
LINE 1: ... licreqm alter column mac type macaddr[] using mac::macaddr[...
 ^
I believe there should be a way, I just do not know it myself. Anyone can
help me?

Thanks & regards,
Ertan Küçükoğlu

Re: Sv: Re: Does PostgreSQL check database integrity at startup?

[Stephen Frost]

* Andreas Joseph Krogh (andr...@visena.com) wrote:
>  SELECT
>    quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
>    s.setting || '/base/' || db.oid || '/' || c.relfilenode,
>    (pg_stat_file(s.setting || '/base/' || db.oid || '/' || 
> c.relfilenode)).size as size
>  FROM
>    pg_settings s
>    JOIN pg_database db on (s.name = 'data_directory')
>    JOIN pg_class c on (datname = current_database())
>    JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
>  WHERE
>    relfilenode <> 0
>    AND nsp.nspname !~ '^pg_'
>    AND nsp.nspname <> 'information_schema';
> 
>  Technically speaking, while these queries are correct for PG10, in prior
>  versions of PostgreSQL it's possible to have user schemas that begin
>  with 'pg_' and therefore the filtering in the WHERE clause would have to
>  be more specific.
> 
>  Note that both of these need to be run as a superuser in older versions
>  of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
>  and be able to run the first query.  We don't currently support being
>  able to GRANT a non-superuser the ability to run pg_stat_file(), but
>  that will likely be coming in PG 11.
> 
>  Thanks!
>  
> That doesn't seem to work with custom types:

Nothing in this query referred to types, so I'm not sure what custom
types would have to do with it..?

> andreak@[local]:5433 10.1 andreak=# SELECT 
>   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
>   s.setting || '/base/' || db.oid || '/' || c.relfilenode,
>   (pg_stat_file(s.setting || '/base/' || db.oid || '/' || 
> c.relfilenode)).size 
> as size
>  FROM  
>   pg_settings s  
>   JOIN pg_database db on (s.name = 'data_directory')  
>   JOIN pg_class c on (datname = current_database())  
>   JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)  
>  WHERE
>   relfilenode <> 0  
>   AND nsp.nspname !~ '^pg_'
>   AND nsp.nspname <> 'information_schema';
>  ERROR:  could not stat file 
> "/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such 
> file or directory
>   
> │ public.biginttuple2 │ 
> /home/andreak/programs/postgresql-10/data/base/22039391/22039392 │

Considering this is saaying 'no such file or directory', I'm guessing
that somehow your data directory isn't what is listed in pg_settings..?

Alternatively, perhaps that table was concurrently dropped?

Are you able to provide any specifics about your system?  Does the
database directory exist?  Does that path look reasonable?  I find it
kind of interesting that the OID of the database and the relfilenode are
so close together- exactly what did you do to test this query?

Thanks!

Stephen


signature.asc
Description: PGP signature

Sv: Re: Does PostgreSQL check database integrity at startup?

[Andreas Joseph Krogh]

På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost <
sfr...@snowman.net >:
Greetings,

 * Melvin Davidson (melvin6...@gmail.com) wrote:
 > My query works as designed and has done so
 > for two years. It shows the filenames for schemas and tables in the
 > database.

 I'm glad to hear that it works in your specific use-case.
 Unfortunately, it doesn't work in the general case and therefore isn't a
 good example.  A proper query to return the filename for each user table
 in the current database is:

 SELECT
   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
   s.setting || '/base/' || db.oid || '/' || c.relfilenode
 FROM
   pg_settings s
   JOIN pg_database db on (s.name = 'data_directory')
   JOIN pg_class c on (datname = current_database())
   JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
 WHERE
   relfilenode <> 0
   AND nsp.nspname !~ '^pg_'
   AND nsp.nspname <> 'information_schema';

 Note that, as discussed earlier in this thread, this doesn't actually
 answer what Edson was asking for.  Here's the query that would answer
 his original request:

 SELECT
   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
   s.setting || '/base/' || db.oid || '/' || c.relfilenode,
   (pg_stat_file(s.setting || '/base/' || db.oid || '/' || 
c.relfilenode)).size as size
 FROM
   pg_settings s
   JOIN pg_database db on (s.name = 'data_directory')
   JOIN pg_class c on (datname = current_database())
   JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
 WHERE
   relfilenode <> 0
   AND nsp.nspname !~ '^pg_'
   AND nsp.nspname <> 'information_schema';

 Technically speaking, while these queries are correct for PG10, in prior
 versions of PostgreSQL it's possible to have user schemas that begin
 with 'pg_' and therefore the filtering in the WHERE clause would have to
 be more specific.

 Note that both of these need to be run as a superuser in older versions
 of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
 and be able to run the first query.  We don't currently support being
 able to GRANT a non-superuser the ability to run pg_stat_file(), but
 that will likely be coming in PG 11.

 Thanks!
 
That doesn't seem to work with custom types:
 
andreak@[local]:5433 10.1 andreak=# SELECT 
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size 
as size
 FROM  
  pg_settings s  
  JOIN pg_database db on (s.name = 'data_directory')  
  JOIN pg_class c on (datname = current_database())  
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)  
 WHERE
  relfilenode <> 0  
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';
 ERROR:  could not stat file 
"/home/andreak/programs/postgresql-10/data/base/22039391/22039392": No such 
file or directory
  
 
│ public.biginttuple2 │ 
/home/andreak/programs/postgresql-10/data/base/22039391/22039392 │
  
 
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Does PostgreSQL check database integrity at startup?

[Stephen Frost]

Greetings,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> My query works as designed and has done so
> for two years. It shows the filenames for schemas and tables in the
> database.

I'm glad to hear that it works in your specific use-case.
Unfortunately, it doesn't work in the general case and therefore isn't a
good example.  A proper query to return the filename for each user table
in the current database is:

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode
FROM
  pg_settings s
  JOIN pg_database db on (s.name = 'data_directory')
  JOIN pg_class c on (datname = current_database())
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid)
WHERE
  relfilenode <> 0
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';

Note that, as discussed earlier in this thread, this doesn't actually
answer what Edson was asking for.  Here's the query that would answer
his original request:

SELECT
  quote_ident(nsp.nspname) || '.' || quote_ident(c.relname),
  s.setting || '/base/' || db.oid || '/' || c.relfilenode,
  (pg_stat_file(s.setting || '/base/' || db.oid || '/' || c.relfilenode)).size 
as size
FROM 
  pg_settings s 
  JOIN pg_database db on (s.name = 'data_directory') 
  JOIN pg_class c on (datname = current_database()) 
  JOIN pg_namespace nsp on (c.relnamespace = nsp.oid) 
WHERE
  relfilenode <> 0 
  AND nsp.nspname !~ '^pg_'
  AND nsp.nspname <> 'information_schema';

Technically speaking, while these queries are correct for PG10, in prior
versions of PostgreSQL it's possible to have user schemas that begin
with 'pg_' and therefore the filtering in the WHERE clause would have to
be more specific.

Note that both of these need to be run as a superuser in older versions
of PG.  In PostgreSQL 10, a user could be GRANT'd 'pg_read_all_settings'
and be able to run the first query.  We don't currently support being
able to GRANT a non-superuser the ability to run pg_stat_file(), but
that will likely be coming in PG 11.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[GPT]

I do not have any intention to persuade anyone. The time runs faster
than any committee!

My question was very clear because I do not know the Pg's internal and
I would like to zero the side (fatal) (if any) effect.

So please, if you really know, tell me if there will be any problem in
case I define my own nonstandard view.

Krgds

On 12/30/17, Tom Lane  wrote:
> GPT  writes:
>> I would like to add an extra boolean attribute to table columns,
>
> The contents of the information_schema views are specified by the SQL
> standard (ISO 9075).  Since the entire point of having those views is to
> have a standard-conformant way of accessing the information in the system
> catalogs, adding nonstandard columns would rather defeat the purpose.
> If you can persuade the relevant ISO standards committee that this is a
> good idea, we will adopt the change when it appears in a published version
> of that standard --- otherwise, it seems quite unlikely that we'd accept
> a change to the view definition.
>
> You'd probably be better off to define your own nonstandard view, IMO.
>
>   regards, tom lane
>

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[GPT]

Future is always administerable especially when there are not problems
in the present!

The reasons I am not in favour of it:

1) Writing for second time the column names is a "repeat yourself"
(that makes me really sick!),
2) The possibility of adding attributes into columns is a good feature
(at least not bad one) which can help a lot,
3) Imagine that is_nullable (NULL attribute) did not exist. I am sure
that you could survive; but how easier it makes your life, that now it
does exist,
4) Some columns additional to the existing ones cost nothing when
there is a good purpose, especially when there ara plenty of them in
the vicinity which most of the times are empty but, of course, serving
a good purpose!
5) Kind dispute is always a good resource of new ideas,
6)There is always a space of improvement and better solution (Ah, how
haven't I thought it before?) especially in IT "art" sector.

So, adding an extra boolean column to "information_schema.columns" or
"pg_attribute" will cause any "fatal" problem in the present?

Krgds

On 12/30/17, PT  wrote:
> On Sat, 30 Dec 2017 20:33:28 +0100
> GPT  wrote:
>
>> Please visit the following link:
>>
>> https://stackoverflow.com/q/48028501/8895614
>>
>> but thing may go further/deeper than it seems.
>
> Adding columns to a built-in system table is liable to cause all sorts of
> problems in the future: when you upgrade, migrate to another server, need
> to
> create dev environments, etc.
>
> The view technique described in the stackunderwhelm article seems like
> a good solution to me. Maybe if you could explain why you are against
> it?
>
>> Krds
>>
>> On 12/30/17, Karsten Hilbert  wrote:
>> > On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote:
>> >
>> >> I would like to add an extra boolean attribute to table columns,
>> >> something like NULL. Unfortunately Pg does not support such a feature:
>> >>
>> >> ADD ATTRIBUTE  TYPE  TO COLUMN
>> >> ;
>> > ...
>> >> I have already been suggested to use VIEW or dynamic SQL but I am
>> >> looking something different.
>> >
>> > Can you explain why ?
>> >
>> > One thing that comes to mind is complex user types.
>> >
>> > Or columns of type table.
>> >
>> > Karsten
>> > --
>> > GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>> > E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>> >
>> >
>>
>
>
> --
> Bill Moran
>

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[Tom Lane]

GPT  writes:
> I would like to add an extra boolean attribute to table columns,

The contents of the information_schema views are specified by the SQL
standard (ISO 9075).  Since the entire point of having those views is to
have a standard-conformant way of accessing the information in the system
catalogs, adding nonstandard columns would rather defeat the purpose.
If you can persuade the relevant ISO standards committee that this is a
good idea, we will adopt the change when it appears in a published version
of that standard --- otherwise, it seems quite unlikely that we'd accept
a change to the view definition.

You'd probably be better off to define your own nonstandard view, IMO.

regards, tom lane

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[PT]

On Sat, 30 Dec 2017 20:33:28 +0100
GPT  wrote:

> Please visit the following link:
> 
> https://stackoverflow.com/q/48028501/8895614
> 
> but thing may go further/deeper than it seems.

Adding columns to a built-in system table is liable to cause all sorts of
problems in the future: when you upgrade, migrate to another server, need to
create dev environments, etc.

The view technique described in the stackunderwhelm article seems like
a good solution to me. Maybe if you could explain why you are against
it?

> Krds
> 
> On 12/30/17, Karsten Hilbert  wrote:
> > On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote:
> >
> >> I would like to add an extra boolean attribute to table columns,
> >> something like NULL. Unfortunately Pg does not support such a feature:
> >>
> >> ADD ATTRIBUTE  TYPE  TO COLUMN
> >> ;
> > ...
> >> I have already been suggested to use VIEW or dynamic SQL but I am
> >> looking something different.
> >
> > Can you explain why ?
> >
> > One thing that comes to mind is complex user types.
> >
> > Or columns of type table.
> >
> > Karsten
> > --
> > GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> > E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
> >
> >
> 


-- 
Bill Moran

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[GPT]

Please visit the following link:

https://stackoverflow.com/q/48028501/8895614

but thing may go further/deeper than it seems.

Krds

On 12/30/17, Karsten Hilbert  wrote:
> On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote:
>
>> I would like to add an extra boolean attribute to table columns,
>> something like NULL. Unfortunately Pg does not support such a feature:
>>
>> ADD ATTRIBUTE  TYPE  TO COLUMN
>> ;
> ...
>> I have already been suggested to use VIEW or dynamic SQL but I am
>> looking something different.
>
> Can you explain why ?
>
> One thing that comes to mind is complex user types.
>
> Or columns of type table.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>

Re: Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[Karsten Hilbert]

On Sat, Dec 30, 2017 at 08:17:34PM +0100, GPT wrote:

> I would like to add an extra boolean attribute to table columns,
> something like NULL. Unfortunately Pg does not support such a feature:
> 
> ADD ATTRIBUTE  TYPE  TO COLUMN
> ;
...
> I have already been suggested to use VIEW or dynamic SQL but I am
> looking something different.

Can you explain why ?

One thing that comes to mind is complex user types.

Or columns of type table.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Adding an extra boolean column to "information_schema.columns" or "pg_attribute"

[GPT]

I would like to add an extra boolean attribute to table columns,
something like NULL. Unfortunately Pg does not support such a feature:

ADD ATTRIBUTE  TYPE  TO COLUMN
;

For example:

ADD ATTRIBUTE is_displayed TYPE boolean TO COLUMN world.human.feature;

so that I can write:

SELECT column_name FROM information_schema.columns
WHERE table_schema = world AND table_name = 'human' AND is_displayed = 'Yes';

Please let me know if there would be any failure of an internal part
or other real technical problem in case I add this extra column by
hand and all relevant stuffs (index, maybe constraint) to either
"information_schema.columns" or "pg_attribute".

I have already been suggested to use VIEW or dynamic SQL but I am
looking something different.

Tia

Re: Does PostgreSQL check database integrity at startup?

[Melvin Davidson]

On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost  wrote:

> Greetings Alban,
>
> * Alban Hertroys (haram...@gmail.com) wrote:
> > In fact, I don't see it mentioned explicitly anywhere, but are we
> actually looking at a problem?
>
> From the discussion in the google barman group, it's clear that the file
> shouldn't be zero bytes in this specific case.
>
> > I'm not convinced by the argument that a CRC check of a 0 byte file on a
> standby would not detect corruption. At the least, the CRC would be
> different or we would be left with a CRC on the master that we can't match
> any CRC's on the slave to if the file is larger on the master.
>
> As discussed, files on the primary can be different at a byte level from
> those on replicas and still be perfectly valid and correct, for a
> variety of reasons from hint bit differences to differences due to the
> replica not being at exactly the same point as the primary.
>
> > If CRC's can be relied on to detect corruption (which they were designed
> to do), then that answers Edson's question.
>
> The checksums included in PG are page-level and therefore there simply
> isn't one to look at if the file is zero bytes.
>
> Thanks!
>
> Stephen
>










*Steve Atkins and Stephen Frost,You both remind me of gawkers at a
disaster, pointing out "mistakes" thatfirst responders are making, but
doing nothing yourselves to help.My query works as designed and has done so
for two years. It shows the filenames for schemas and tables in the
database. If you don't like it,TOO BAD! Shame on you! Use your brains and
write your own query. Nuff said, I shall reply no more. *
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Does PostgreSQL check database integrity at startup?

[Stephen Frost]

Greetings Alban,

* Alban Hertroys (haram...@gmail.com) wrote:
> In fact, I don't see it mentioned explicitly anywhere, but are we actually 
> looking at a problem?

From the discussion in the google barman group, it's clear that the file
shouldn't be zero bytes in this specific case.

> I'm not convinced by the argument that a CRC check of a 0 byte file on a 
> standby would not detect corruption. At the least, the CRC would be different 
> or we would be left with a CRC on the master that we can't match any CRC's on 
> the slave to if the file is larger on the master.

As discussed, files on the primary can be different at a byte level from
those on replicas and still be perfectly valid and correct, for a
variety of reasons from hint bit differences to differences due to the
replica not being at exactly the same point as the primary.

> If CRC's can be relied on to detect corruption (which they were designed to 
> do), then that answers Edson's question.

The checksums included in PG are page-level and therefore there simply
isn't one to look at if the file is zero bytes.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Does PostgreSQL check database integrity at startup?

[Alban Hertroys]

> On 30 Dec 2017, at 6:09, Jan Wieck  wrote:
> 
> This whole discussion left out the critical point of
> 
> how on earth did that file end up having zero bytes?
> 
> There is the possibility that this is due to a bug in PostgreSQL. But over 
> the 20+ years I've been using and hacking it, that has never happened.
> 
> OP never told us what type of OS, hardware and specifically storage subsystem 
> that "slave" was running on. Filesystems zapping files to zero after a 
> catastrophic IO failure (no longer throwing them into lost+found) isn't 
> unheard of.

If this is indeed a case of a corrupt database, then it's possible that we're 
looking at a system that claims to have the disk cache flushed when it hasn't 
yet done that.

It gets even more fun if there are multiple table-spaces in use with different 
behaviour in "whatever is causing this" (fsync, file system zappery, etc.); the 
WAL log can get out of synch with the table files in that case. The WAL would 
reflect a situation where more has been flushed to the respective files than is 
actually the case - data got lost.

But of course it's also entirely possible that these empty files are not in 
fact a problem, as suggested up-thread. 

No reason to panic - yet.
In fact, I don't see it mentioned explicitly anywhere, but are we actually 
looking at a problem?
The standby server started up just fine, from what I'm reading. Is there any 
evidence of corruption?

So far, the database has been labeled corrupt by Edson because a base file was 
0 bytes, but that's apparently not a definitive indication of corruption.
What would be definitive is if querying certain relations causes errors or does 
give results that were never in that database. I do think that those 0 byte 
files are a good starting point, by querying the relations that those belong to 
(if any). Otherwise we may be searching for a needle in a haystack that doesn't 
necessarily contain one.
I do realise that not finding any evidence of corruption does not necessarily 
mean there is none.

I'm not convinced by the argument that a CRC check of a 0 byte file on a 
standby would not detect corruption. At the least, the CRC would be different 
or we would be left with a CRC on the master that we can't match any CRC's on 
the slave to if the file is larger on the master.

If CRC's can be relied on to detect corruption (which they were designed to 
do), then that answers Edson's question.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.