Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Julie Nishimura]

Correction about version:

PostgreSQL 8.3.11

Thanks again!!!


From: Julie Nishimura 
Sent: Thursday, May 23, 2019 11:57 PM
To: pgsql-general@lists.postgresql.org; pgsql-general
Subject: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 
of 8 (99%)

Hello,
We have an issue with fsm_relations utilization reaching 99%, I was able to 
vacuum a handful of tables, but it wasn't enough to make a noticeable 
difference. I think at this point we will need to increase the number of 
fsm_relations from 80,000 to 100,000 which will require a restart. Because 
there aren't any more dead rows to delete. I confirmed this by connecting to 
each db and running the following query:
SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by 
n_dead_tup desc
daily_mail_shared_state_cddt_3588-
relname | n_live_tup | n_dead_tup
++
article_errors | 0 | 0
article_names | 3375193 | 0
indexdefs | 0 | 0
tabledefs | 0 | 0

Above output, shows n_dead_tup is zeroed out, this makes me believe that we 
need to increase the number of fsm relations to a number between 90k and 
100k.But I might be wrong, need your advice.

PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Thanks!

Re: pg_stat_user_tables.n_tup_ins empty for partitioned table

[Luca Ferrari]

On Wed, May 22, 2019 at 7:55 PM Andres Freund  wrote:
> That clearly seems wrong. Could you try build a small reproducer?

Apparently not, I've tried to simulate the same but without any
success, that is n_tup_ins is always correctly set.
However, I've noted that this behavior applies up to february

# select schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del,
n_live_tup from pg_stat_user_tables where n_tup_ins = 0 and n_live_tup
> 0;
 schemaname | relname  | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup
+--+---+---+---+
 spire  | y2018m09 | 0 |  28961860 | 0 |9708398
 spire  | y2018m10 | 0 |  29791202 | 0 |   14902436
 spire  | y2018m11 | 0 |  28191462 | 0 |   14086279
 spire  | y2018m12 | 0 |  29676478 | 0 |   14828806
 spire  | y2019m01 | 0 |  28769406 | 0 |   14381782
 spire  | y2019m02 | 0 |  27088208 | 0 |   13541677
 spire  | sensori  | 0 |   892 | 0 |446

now what I did change back then was to avoid a single INSERT...SELECT
statement and provide a FOR...SELECT loop with every single insert
within it. But I don't think this is the reason, could it be a restore
from a backup I don't remember?
Unluckily I cannot reproduce this behavior so far.
And I stand correct, the PostgreSQL version is 11.1:

# select version();
 version
-
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit


Any idea is appreciated.

Re: pg_stat_user_tables.n_tup_ins empty for partitioned table

[David Rowley]

On Fri, 24 May 2019 at 19:20, Luca Ferrari  wrote:
>
> On Wed, May 22, 2019 at 7:55 PM Andres Freund  wrote:
> > That clearly seems wrong. Could you try build a small reproducer?
>
> Apparently not, I've tried to simulate the same but without any
> success, that is n_tup_ins is always correctly set.
> However, I've noted that this behavior applies up to february

Did you perhaps reset the stats or failover to a standby around Feb?

What does: select stats_Reset from pg_stat_database where datname =
current_database(); say?

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: pg_stat_user_tables.n_tup_ins empty for partitioned table

[Luca Ferrari]

On Fri, May 24, 2019 at 9:26 AM David Rowley
 wrote:
> What does: select stats_Reset from pg_stat_database where datname =
> current_database(); say?

Good guess:

# select stats_reset from pg_stat_database where datname =
current_database();
  stats_reset
---
 2019-03-28 14:40:01.945332+01

Since the partitioned table of that month has an n_tup_ins that is an
order lower than n_live_tup I suspect this could be the cause:

# select schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del,
n_live_tup from pg_stat_user_tables where relname = 'y2019m03';
-[ RECORD 1 ]
schemaname | spire
relname| y2019m03
n_tup_ins  | 1671778
n_tup_upd  | 27167473
n_tup_del  | 0
n_live_tup | 15231270

Since each table grows around 20 tuples per hour, that is 48
tuples per day, it did have 3.5 days to insert in that month that is
168000 tuples from the reset to the end of march, that is also the
value of n_tup_ins.
In conclusion, I did hit a tuple reset (but don't remember why).
Around that days I was experimenting, unsuccesfully, with pg_backrest.
I say unsuccesfully because due to our policy I could not connect the
salve via ssh to the host. Could it be that hit a reset of the stats?

However, sorry for the noise.

Luca

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Adrian Klaver]

On 5/23/19 11:57 PM, Julie Nishimura wrote:

Hello,
We have an issue with fsm_relations utilization reaching 99%, I was able 


How are you arriving at the above percentage?

How many tables/indexes do you have in the database(s)?

to vacuum a handful of tables, but it wasn't enough to make a noticeable 
difference. I think at this point we will need to increase the number of 
fsm_relations from 80,000 to 100,000 which will require a restart. 
Because there aren't any more dead rows to delete. I confirmed this by 
connecting to each db and running the following query:
SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by 
n_dead_tup desc

daily_mail_shared_state_cddt_3588-
relname | n_live_tup | n_dead_tup
++
article_errors | 0 | 0
article_names | 3375193 | 0
indexdefs | 0 | 0
tabledefs | 0 | 0

Above output, shows n_dead_tup is zeroed out, this makes me believe that 
we need to increase the number of fsm relations to a number between 90k 
and 100k.But I might be wrong, need your advice.


PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Thanks!



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

Questions about btree_gin vs btree_gist for low cardinality columns

[Jeremy Finzel]

I have been hoping for clearer direction from the community about
specifically btree_gin indexes for low cardinality columns (as well as low
cardinality multi-column indexes).  In general there is very little
discussion about this both online and in the docs.  Rather, the emphasis
for GIN indexes discussed is always on full text search of JSON indexing,
not btree_gin indexes.

However, I have never been happy with the options open to me for indexing
low cardinality columns and was hoping this could be a big win.  Often I
use partial indexes as a solution, but I really want to know how many use
cases btree_gin could solve better than either a normal btree or a partial
index.

Here are my main questions:

1.

"The docs say regarding *index only scans*: The index type must support
index-only scans. B-tree indexes always do. GiST and SP-GiST indexes
support index-only scans for some operator classes but not others. Other
index types have no support. The underlying requirement is that the index
must physically store, or else be able to reconstruct, the original data
value for each index entry. As a counterexample, GIN indexes cannot support
index-only scans because each index entry typically holds only part of the
original data value."

This is confusing to say "B-tree indexes always do" and "GIN indexes cannot
support index-only scans", when we have a btree_gin index type.
Explanation please ???

Is it true that for a btree_gin index on a regular column, "each index
entry typically holds only part of the original data value"?  Do these
still not support index only scans?  Could they?  I can't see why they
shouldn't be able to for a single indexed non-expression field?

2.

Lack of index only scans is definitely a downside.  However, I see
basically identical performance, but way less memory and space usage, for
gin indexes.  In terms of read-only performance, if index only scans are
not a factor, why not always recommend btree_gin indexes instead of regular
btree for low cardinality fields, which will yield similar performance but
use far, far less space and resources?

3.

This relates to 2.  I understand the write overhead can be much greater for
GIN indexes, which is why the fastupdate feature exists.  But again, in
those discussions in the docs, it appears to me they are emphasizing that
penalty more for full text or json GIN indexes.  Does the same overhead
apply to a btree_gin index on a regular column with no expressions?

Those are my questions.

FYI, I can see an earlier thread about this topic (
https://www.postgresql.org/message-id/flat/E260AEE7-95B3-4142-9A4B-A4416F1701F0%40aol.com#5def5ce1864298a3c0ba2d4881a660c2),
but a few questions were left unanswered and unclear there.

I first started seriously considering using btree_gin indexes for low
cardinality columns, for example some text field with 30 unique values
across 100 million rows, after reading a summary of index types from
Bruce's article: https://momjian.us/main/writings/pgsql/indexing.pdf

This article was also helpful but yet again I wonder it's broader
viability:
http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bitmap-indexes/


Thank you!
Jeremy

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Julie Nishimura]

Adrian, this value was set in config file, and alerting comes from monitoring.

Would it be right query to count objects in each database (there are 75 dbs on 
this server totaling close to 20 tb):

SELECT
count(1) as object_count
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r','i')

?

Thanks!


From: Adrian Klaver 
Sent: Friday, May 24, 2019 7:19 AM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 
79569 of 8 (99%)

On 5/23/19 11:57 PM, Julie Nishimura wrote:
> Hello,
> We have an issue with fsm_relations utilization reaching 99%, I was able

How are you arriving at the above percentage?

How many tables/indexes do you have in the database(s)?

> to vacuum a handful of tables, but it wasn't enough to make a noticeable
> difference. I think at this point we will need to increase the number of
> fsm_relations from 80,000 to 100,000 which will require a restart.
> Because there aren't any more dead rows to delete. I confirmed this by
> connecting to each db and running the following query:
> SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by
> n_dead_tup desc
> daily_mail_shared_state_cddt_3588-
> relname | n_live_tup | n_dead_tup
> ++
> article_errors | 0 | 0
> article_names | 3375193 | 0
> indexdefs | 0 | 0
> tabledefs | 0 | 0
>
> Above output, shows n_dead_tup is zeroed out, this makes me believe that
> we need to increase the number of fsm relations to a number between 90k
> and 100k.But I might be wrong, need your advice.
>
> PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)
>
> Thanks!


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

Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations used: 79569 of 80000 (99%)

[Adrian Klaver]

On 5/24/19 10:16 AM, Julie Nishimura wrote:
Adrian, this value was set in config file, and alerting comes from 
monitoring.


Yes, but what is the monitoring actually doing to get that value?



Would it be right query to count objects in each database (there are 75 
dbs on this server totaling close to 20 tb):


SELECT
count(1) as object_count
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r','i')

?

Thanks!


*From:* Adrian Klaver 
*Sent:* Friday, May 24, 2019 7:19 AM
*To:* Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
*Subject:* Re: POSTGRES_FSM_RELATIONS CRITICAL: DB control fsm relations 
used: 79569 of 8 (99%)

On 5/23/19 11:57 PM, Julie Nishimura wrote:

Hello,
We have an issue with fsm_relations utilization reaching 99%, I was able 


How are you arriving at the above percentage?

How many tables/indexes do you have in the database(s)?

to vacuum a handful of tables, but it wasn't enough to make a noticeable 
difference. I think at this point we will need to increase the number of 
fsm_relations from 80,000 to 100,000 which will require a restart. 
Because there aren't any more dead rows to delete. I confirmed this by 
connecting to each db and running the following query:
SELECT relname, n_live_tup, n_dead_tup from pg_stat_user_tables order by 
n_dead_tup desc

daily_mail_shared_state_cddt_3588-
relname | n_live_tup | n_dead_tup
++
article_errors | 0 | 0
article_names | 3375193 | 0
indexdefs | 0 | 0
tabledefs | 0 | 0

Above output, shows n_dead_tup is zeroed out, this makes me believe that 
we need to increase the number of fsm relations to a number between 90k 
and 100k.But I might be wrong, need your advice.


PostgreSQL 8.2.15 (Greenplum Database 4.3.8.1 build 1)

Thanks!



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



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

Snippets?

[Ken Lacrosse]

Is there any way in postgresql to have a "snippet" of SQL code which you could 
apply to all tables.  Something you could add which would ensure that every 
table always has a Created, Changed and Deleted column for example.  Sort of 
like a C include I suppose.  Of course if I'm building a DB schema by hand I 
could just copy and paste those columns but it still seems a bit too, darn 
humans!, error prone.


Ken LaCrosse
Senior IT InfoSec and Infrastructure Monitoring Specialist
I.T. | American Recovery Service & Skipbusters
Phone: (800)398-6480 x3758
Email: klacro...@pkwillis.com
www.pkwillis.com

* Visibility is key. *
Without visibility you can't see.
What you can't see you can't measure.
What you can't measure you can't planfully change.
What you can't planfully change you can't manage.
And if we can't manage then why are we here?

NOTICE: The information contained in this transmission, including attachments, 
may contain confidential information that is privileged, confidential and/or 
exempt from disclosure by applicable law. It is intended only for the use of 
the person(s) or entity to which it is addressed. If the reader of this 
transmission is not the intended recipient, the reader is hereby notified that 
any review, use, dissemination, distribution or duplication of this 
communication (including any reliance thereon) is strictly prohibited. If you 
have received this transmission in error, please contact the sender by reply 
email, then delete and destroy the material in its entirety, whether in 
electronic or hard copy format. WE SPECIFICALLY DISCLAIM RESPONSIBILITY FOR ANY 
UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. Thank you.

Re: Snippets?

[Ron]

On 5/24/19 1:15 PM, Ken Lacrosse wrote:


Is there any way in postgresql to have a "snippet" of SQL code which you 
could apply to all tables.  Something you could add which would ensure 
that every table always has a Created, Changed and Deleted column for 
example.  Sort of like a C include I suppose.  Of course if I’m building a 
DB schema by hand I could just copy and paste those columns but it still 
seems a bit too, darn humans!, error prone.




Inheritance might be what you want.

--
Angular momentum makes the world go 'round.

Re: Snippets?

[Adrian Klaver]

On 5/24/19 11:15 AM, Ken Lacrosse wrote:
Is there any way in postgresql to have a "snippet" of SQL code which you 
could apply to all tables.  Something you could add which would ensure 
that every table always has a Created, Changed and Deleted column for 
example.  Sort of like a C include I suppose.  Of course if I’m building 
a DB schema by hand I could just copy and paste those columns but it 
still seems a bit too, darn humans!, error prone.


Not that I know of.

Things I have done:
1) Template table that I includes those fields that I then add to.

FYI the Sqitch schema management system allows you to do the above:
https://sqitch.org/docs/manual/sqitch-add/#templates

2) A script that I run over table definition to add the fields.



Ken LaCrosse

Senior IT InfoSec and Infrastructure Monitoring Specialist

I.T. | American Recovery Service & Skipbusters

Phone: (800)398-6480 x3758

Email: klacro...@pkwillis.com 

www.pkwillis.com 

* Visibility is key. *

Without visibility you can't see.

What you can't see you can't measure.

What you can't measure you can’t planfully change.

What you can’t planfully change you can't manage.

And if we can’t manage then why are we here?

NOTICE: The information contained in this transmission, including 
attachments, may contain confidential information that is privileged, 
confidential and/or exempt from disclosure by applicable law. It is 
intended only for the use of the person(s) or entity to which it is 
addressed. If the reader of this transmission is not the intended 
recipient, the reader is hereby notified that any review, use, 
dissemination, distribution or duplication of this communication 
(including any reliance thereon) is strictly prohibited. If you have 
received this transmission in error, please contact the sender by reply 
email, then delete and destroy the material in its entirety, whether in 
electronic or hard copy format. WE SPECIFICALLY DISCLAIM RESPONSIBILITY 
FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. 
Thank you.



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

Re: Snippets?

[Ron]

On 5/24/19 1:27 PM, Adrian Klaver wrote:

On 5/24/19 11:15 AM, Ken Lacrosse wrote:
Is there any way in postgresql to have a "snippet" of SQL code which you 
could apply to all tables. Something you could add which would ensure 
that every table always has a Created, Changed and Deleted column for 
example. Sort of like a C include I suppose.  Of course if I’m building a 
DB schema by hand I could just copy and paste those columns but it still 
seems a bit too, darn humans!, error prone.


Not that I know of.

Things I have done:
1) Template table that I includes those fields that I then add to.

FYI the Sqitch schema management system allows you to do the above:
https://sqitch.org/docs/manual/sqitch-add/#templates

2) A script that I run over table definition to add the fields.


What about INHERITS?

test=# create table CCD_template (
test(#  Created timestamptz,
test(#  Changed timestamptz,
test(#  Deleted timestamptz );
CREATE TABLE

test=# create table foobar (
  field1 integer,
  field2 bytea
) inherits (CCD_template);
CREATE TABLE
test=#
test=# \d foobar
 Table "public.foobar"
 Column  |   Type   | Modifiers
-+--+---
 created | timestamp with time zone |
 changed | timestamp with time zone |
 deleted | timestamp with time zone |
 field1  | integer  |
 field2  | bytea    |
Inherits: ccd_template






Ken LaCrosse

Senior IT InfoSec and Infrastructure Monitoring Specialist

I.T. | American Recovery Service & Skipbusters

Phone: (800)398-6480 x3758

Email: klacro...@pkwillis.com 

www.pkwillis.com 

* Visibility is key. *

Without visibility you can't see.

What you can't see you can't measure.

What you can't measure you can’t planfully change.

What you can’t planfully change you can't manage.

And if we can’t manage then why are we here?

NOTICE: The information contained in this transmission, including 
attachments, may contain confidential information that is privileged, 
confidential and/or exempt from disclosure by applicable law. It is 
intended only for the use of the person(s) or entity to which it is 
addressed. If the reader of this transmission is not the intended 
recipient, the reader is hereby notified that any review, use, 
dissemination, distribution or duplication of this communication 
(including any reliance thereon) is strictly prohibited. If you have 
received this transmission in error, please contact the sender by reply 
email, then delete and destroy the material in its entirety, whether in 
electronic or hard copy format. WE SPECIFICALLY DISCLAIM RESPONSIBILITY 
FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY ATTACHMENTS TO IT. 
Thank you.





--
Angular momentum makes the world go 'round.

Re: Snippets?

[Adrian Klaver]

On 5/24/19 12:24 PM, Ron wrote:

On 5/24/19 1:27 PM, Adrian Klaver wrote:

On 5/24/19 11:15 AM, Ken Lacrosse wrote:
Is there any way in postgresql to have a "snippet" of SQL code which 
you could apply to all tables. Something you could add which would 
ensure that every table always has a Created, Changed and Deleted 
column for example. Sort of like a C include I suppose.  Of course if 
I’m building a DB schema by hand I could just copy and paste those 
columns but it still seems a bit too, darn humans!, error prone.


Not that I know of.

Things I have done:
1) Template table that I includes those fields that I then add to.

FYI the Sqitch schema management system allows you to do the above:
https://sqitch.org/docs/manual/sqitch-add/#templates

2) A script that I run over table definition to add the fields.


What about INHERITS?


It is an option, though it does have baggage:
https://www.postgresql.org/docs/11/sql-createtable.html
"INHERITS ( parent_table [, ... ] )

The optional INHERITS clause specifies a list of tables from which 
the new table automatically inherits all columns. Parent tables can be 
plain tables or foreign tables.


Use of INHERITS creates a persistent relationship between the new 
child table and its parent table(s). Schema modifications to the 
parent(s) normally propagate to children as well, and by default the 
data of the child table is included in scans of the parent(s).


..."""

There is more.

If I was to go that route I would use LIKE:

"LIKE source_table [ like_option ... ]

The LIKE clause specifies a table from which the new table 
automatically copies all column names, their data types, and their 
not-null constraints.


Unlike INHERITS, the new table and original table are completely 
decoupled after creation is complete. Changes to the original table will 
not be applied to the new table, and it is not possible to include data 
of the new table in scans of the original table.


..."




test=# create table CCD_template (
test(#  Created timestamptz,
test(#  Changed timestamptz,
test(#  Deleted timestamptz );
CREATE TABLE

test=# create table foobar (
   field1 integer,
   field2 bytea
) inherits (CCD_template);
CREATE TABLE
test=#
test=# \d foobar
  Table "public.foobar"
  Column  |   Type   | Modifiers
-+--+---
  created | timestamp with time zone |
  changed | timestamp with time zone |
  deleted | timestamp with time zone |
  field1  | integer  |
  field2  | bytea    |
Inherits: ccd_template






Ken LaCrosse

Senior IT InfoSec and Infrastructure Monitoring Specialist

I.T. | American Recovery Service & Skipbusters

Phone: (800)398-6480 x3758

Email: klacro...@pkwillis.com 

www.pkwillis.com 

* Visibility is key. *

Without visibility you can't see.

What you can't see you can't measure.

What you can't measure you can’t planfully change.

What you can’t planfully change you can't manage.

And if we can’t manage then why are we here?

NOTICE: The information contained in this transmission, including 
attachments, may contain confidential information that is privileged, 
confidential and/or exempt from disclosure by applicable law. It is 
intended only for the use of the person(s) or entity to which it is 
addressed. If the reader of this transmission is not the intended 
recipient, the reader is hereby notified that any review, use, 
dissemination, distribution or duplication of this communication 
(including any reliance thereon) is strictly prohibited. If you have 
received this transmission in error, please contact the sender by 
reply email, then delete and destroy the material in its entirety, 
whether in electronic or hard copy format. WE SPECIFICALLY DISCLAIM 
RESPONSIBILITY FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY 
ATTACHMENTS TO IT. Thank you.








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

RE: Snippets?

[Ken Lacrosse]

Thanks guys.  VERY informative.  I'll be looking at all 3 approaches: INHERITS, 
LIKE and Sqitch.

Ken LaCrosse
Senior IT InfoSec and Infrastructure Monitoring Specialist
I.T. | American Recovery Service & Skipbusters
Phone: (800)398-6480 x3758
Email: klacro...@pkwillis.com
www.pkwillis.com

* Visibility is key. *
Without visibility you can't see.
What you can't see you can't measure.
What you can't measure you can’t planfully change.
What you can’t planfully change you can't manage.
And if we can’t manage then why are we here?


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Friday, May 24, 2019 12:30
To: Ron; pgsql-general@lists.postgresql.org
Subject: Re: Snippets?

On 5/24/19 12:24 PM, Ron wrote:
> On 5/24/19 1:27 PM, Adrian Klaver wrote:
>> On 5/24/19 11:15 AM, Ken Lacrosse wrote:
>>> Is there any way in postgresql to have a "snippet" of SQL code which
>>> you could apply to all tables. Something you could add which would
>>> ensure that every table always has a Created, Changed and Deleted
>>> column for example. Sort of like a C include I suppose.  Of course if
>>> I’m building a DB schema by hand I could just copy and paste those
>>> columns but it still seems a bit too, darn humans!, error prone.
>>
>> Not that I know of.
>>
>> Things I have done:
>> 1) Template table that I includes those fields that I then add to.
>>
>> FYI the Sqitch schema management system allows you to do the above:
>> https://sqitch.org/docs/manual/sqitch-add/#templates
>>
>> 2) A script that I run over table definition to add the fields.
>
> What about INHERITS?

It is an option, though it does have baggage:
https://www.postgresql.org/docs/11/sql-createtable.html
"INHERITS ( parent_table [, ... ] )

 The optional INHERITS clause specifies a list of tables from which
the new table automatically inherits all columns. Parent tables can be
plain tables or foreign tables.

 Use of INHERITS creates a persistent relationship between the new
child table and its parent table(s). Schema modifications to the
parent(s) normally propagate to children as well, and by default the
data of the child table is included in scans of the parent(s).

..."""

There is more.

If I was to go that route I would use LIKE:

"LIKE source_table [ like_option ... ]

 The LIKE clause specifies a table from which the new table
automatically copies all column names, their data types, and their
not-null constraints.

 Unlike INHERITS, the new table and original table are completely
decoupled after creation is complete. Changes to the original table will
not be applied to the new table, and it is not possible to include data
of the new table in scans of the original table.

..."


>
> test=# create table CCD_template (
> test(#  Created timestamptz,
> test(#  Changed timestamptz,
> test(#  Deleted timestamptz );
> CREATE TABLE
>
> test=# create table foobar (
>field1 integer,
>field2 bytea
> ) inherits (CCD_template);
> CREATE TABLE
> test=#
> test=# \d foobar
>   Table "public.foobar"
>   Column  |   Type   | Modifiers
> -+--+---
>   created | timestamp with time zone |
>   changed | timestamp with time zone |
>   deleted | timestamp with time zone |
>   field1  | integer  |
>   field2  | bytea|
> Inherits: ccd_template
>
>
>>
>>>
>>> Ken LaCrosse
>>>
>>> Senior IT InfoSec and Infrastructure Monitoring Specialist
>>>
>>> I.T. | American Recovery Service & Skipbusters
>>>
>>> Phone: (800)398-6480 x3758
>>>
>>> Email: klacro...@pkwillis.com 
>>>
>>> www.pkwillis.com 
>>>
>>> * Visibility is key. *
>>>
>>> Without visibility you can't see.
>>>
>>> What you can't see you can't measure.
>>>
>>> What you can't measure you can’t planfully change.
>>>
>>> What you can’t planfully change you can't manage.
>>>
>>> And if we can’t manage then why are we here?
>>>
>>> NOTICE: The information contained in this transmission, including
>>> attachments, may contain confidential information that is privileged,
>>> confidential and/or exempt from disclosure by applicable law. It is
>>> intended only for the use of the person(s) or entity to which it is
>>> addressed. If the reader of this transmission is not the intended
>>> recipient, the reader is hereby notified that any review, use,
>>> dissemination, distribution or duplication of this communication
>>> (including any reliance thereon) is strictly prohibited. If you have
>>> received this transmission in error, please contact the sender by
>>> reply email, then delete and destroy the material in its entirety,
>>> whether in electronic or hard copy format. WE SPECIFICALLY DISCLAIM
>>> RESPONSIBILITY FOR ANY UNAUTHORIZED USE OF THIS COMMUNICATION OR ANY
>>> ATTACHMENTS TO IT. Thank you.
>>
>>
>


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


NOTICE: The information contained in this transmission, inclu