Re: Postgres Database Backup Size

[Laurenz Albe]

Shiwangini Shishulkar wrote:
> Any way to reduce backup size of postgres backups or it's default behavior of 
> postgres to take backups which are double in size?

The smallest backup would be a compressed custom format "pg_dump":

  pg_dump -F c -Z 9 ...

I is very unusual for a custom format dump to be bigger than
the original database.  How did you measure the database size?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

How to gracefully keep my specific index in memory ?

[James(王旭)]

Hello:


As the title,How to keep a specific index in memory gracefully?


After some statistical query, I can determine that not all indexes can be fit 
into memory, but one of the most frequently used indexes(say idx_xyz) can be 
definitely fit into memory(specifically ,[the size of idx_xyz]=20% x [memory 
size]).


I know there's pgprewarm, but I feel with pgprewarm I can't keep things under 
control, e.g. no realtime monitor, being squeezed out of memory ,.etc.


Is it possible that I can simply do something like "select idx_xyz into xxx" 
and keep the specific index in memory forever?


Best regards,
James

Re: PostgreSQL lock file

[Олег Самойлов]

> Best practice for this sort of thing is considered to be to run the two
> Postgres instances under different user IDs.  That avoids them thinking
> that the other one's lock files are relevant, and it provides an extra
> layer of security against possible conflicts between the two instances.
> 
>   regards, tom lane

Thanks, an interesting  notice. I already did changes in start script to check 
instance by pg_ctl status and delete lock file if status<>0. But your 
recommendation is looked better.

Re: How to gracefully keep my specific index in memory ?

[Laurenz Albe]

James(王旭) wrote:
> As the title,How to keep a specific index in memory gracefully?
> 
> After some statistical query, I can determine that not all indexes can be fit 
> into memory,
> but one of the most frequently used indexes(say idx_xyz) can be definitely 
> fit into
> memory(specifically ,[the size of idx_xyz]=20% x [memory size]).
> 
> I know there's pgprewarm, but I feel with pgprewarm I can't keep things under 
> control, e.g. no realtime monitor, being squeezed out of memory ,.etc.
> 
> Is it possible that I can simply do something like "select idx_xyz into xxx" 
> and keep the specific index in memory forever?

If the indexes are frequently used, they should remain cached anyway.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Fwd: Postgres HA - pacemaker RA do not support auto failback

[Shital A]

Hello,

Postgres version : 9.6
OS:Rhel 7.6

We are working on HA setup for postgres cluster of two nodes in
active-passive mode.

Installed:
Pacemaker 1.1.19
Corosync 2.4.3

The pacemaker agent with this installation doesn't support automatic
failback. What I mean by that is explained below:
1. Cluster is setup like A - B with A as master.
2. Kill services on A, node B will come up as master.
3. node A is ready to join the cluster, we have to delete the lock file it
creates on any one of the node and execute the cleanup command to get the
node back as standby

Step 3 is manual so HA is not achieved in real sense.

Please help to check:
1. Is there any version of the resouce agent which supports automatic
failback? To avoid generation of lock file and deleting it.

2. If there is no such support, what checks should be added in pgsql RA to
achieve

Please suggest.
Thanks.

Re: Fwd: Postgres HA - pacemaker RA do not support auto failback

[Adrien Nayrat]

On 8/12/19 2:57 PM, Shital A wrote:
> 
> 
> Hello,
> 
> Postgres version : 9.6
> OS:Rhel 7.6
> 
> We are working on HA setup for postgres cluster of two nodes in active-passive
> mode. 
> 
> Installed: 
> Pacemaker 1.1.19
> Corosync 2.4.3
> 
> The pacemaker agent with this installation doesn't support automatic failback.
> What I mean by that is explained below:
> 1. Cluster is setup like A - B with A as master. 
> 2. Kill services on A, node B will come up as master.
> 3. node A is ready to join the cluster, we have to delete the lock file it
> creates on any one of the node and execute the cleanup command to get the node
> back as standby
> 
> Step 3 is manual so HA is not achieved in real sense. 
> 
> Please help to check:
> 1. Is there any version of the resouce agent which supports automatic 
> failback?
> To avoid generation of lock file and deleting it.
> 
> 2. If there is no such support, what checks should be added in pgsql RA to 
> achieve 
> 
> Please suggest.
> Thanks.
> 

Hello,

Which RA did you use? AFAIK there is two RA :
- pgsql : 
https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/pgsql
- PAF : http://clusterlabs.github.io/PAF/ (Which is different from pgsql RA :
http://clusterlabs.github.io/PAF/FAQ.html#why-new-ra-for-postgresql)

If I am right, PAF should handle the case when there no need to perform pgrewind
(if the old master is not too advanced in the transaction log).

Regards,

-- 
Adrien NAYRAT
https://blog.anayrat.info



signature.asc
Description: OpenPGP digital signature

constrain with MATCH full and NULL values in referenced table

[stan]

I am creating a table that has 2 values in it which are keys pointing to 2
other tables. I need for the UNIQUE combination of these 2 keys to exist in
a fourth table. It has been recommended to use a foreign key constraint with
the MATCH FULL parameter. 

Here is my question, does this deal with NULLS in the 4th table? I am
concerned that this constraint might fail to reject an entry if one, or both
of the 2 key values being inserted in the table are NULLS,.



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: constrain with MATCH full and NULL values in referenced table

[Adrian Klaver]

On 8/12/19 8:11 AM, stan wrote:

I am creating a table that has 2 values in it which are keys pointing to 2
other tables. I need for the UNIQUE combination of these 2 keys to exist in
a fourth table. It has been recommended to use a foreign key constraint with
the MATCH FULL parameter.


Without the table schema it difficult for me to figure out what it is 
you are attempting.


See below for how MATCH FULL works:

https://www.postgresql.org/docs/11/sql-createtable.html



Here is my question, does this deal with NULLS in the 4th table? I am
concerned that this constraint might fail to reject an entry if one, or both
of the 2 key values being inserted in the table are NULLS,.






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

Re: constrain with MATCH full and NULL values in referenced table

[Tom Lane]

stan  writes:
> I am creating a table that has 2 values in it which are keys pointing to 2
> other tables. I need for the UNIQUE combination of these 2 keys to exist in
> a fourth table. It has been recommended to use a foreign key constraint with
> the MATCH FULL parameter. 
> Here is my question, does this deal with NULLS in the 4th table? I am
> concerned that this constraint might fail to reject an entry if one, or both
> of the 2 key values being inserted in the table are NULLS,.

If you don't want nulls there, maybe add NOT NULL constraints to those
columns?

regards, tom lane

Invitation for OSS Community Research based in the University of Leeds! Many Thanks!

[Xinming Guo]

Dear PostgreSQL Community Members,

I am a master student based at the University of Leeds (UK), and currently 
working on a research investigating the relationships between open source 
software community culture, governance and structure, motivation of 
participation and the innovation capability of OSS community. I found 
PostgreSQL community is very active and supportive with members around the 
world, which makes it perfect for this research.

This research data is to be collected via questionnaire with the anonymous link 
below:
https://leedsubs.eu.qualtrics.com/jfe/form/SV_cUPSObEbxQqEX1b

The questionnaire should take you 5-10 minutes to complete. I would very much 
appreciate if you could help to participate this research, and if you have any 
questions about the research, I am happy to answer, just e-mail me on 
bn1...@leeds.ac.uk. Thanks a lot!

Best Regards,

Sophy Guo

Re: constrain with MATCH full and NULL values in referenced table

[Adrian Klaver]

On 8/12/19 8:51 AM, stan wrote:
Please reply to list also.
Ccing list.


On Mon, Aug 12, 2019 at 08:17:33AM -0700, Adrian Klaver wrote:

On 8/12/19 8:11 AM, stan wrote:

I am creating a table that has 2 values in it which are keys pointing to 2
other tables. I need for the UNIQUE combination of these 2 keys to exist in
a fourth table. It has been recommended to use a foreign key constraint with
the MATCH FULL parameter.


Without the table schema it difficult for me to figure out what it is you
are attempting.

See below for how MATCH FULL works:

https://www.postgresql.org/docs/11/sql-createtable.html


 From that page:

MATCH FULL will not allow one column of a multicolumn foreign key to be null
unless all foreign key columns are null; if they are all null, the row is
not required to have a match in the referenced table.

I think that means that what I am trying to enforce will not work.

The table the insert is on has 2 columns, each of these is a foreign key to
other table. I need the unique combination of these 2 keys to exist in a 3rd
table that is a rate table. It has 3 columns, key 1, key 2, and rate. Looks
like to me, if neither of the 2 keys are in the rate table the constraint
will allow the insert. Do I have this wrong?


The docs are referring to a multicolumn FK so something like:

create table parent_tbl(fld_1 integer, fld_2 integer, UNIQUE(fld_1, fld_2));

create table child_tbl(fk_fld_1 integer, fk_fld_2 integer, FOREIGN KEY 
(fk_fld_1, fk_fld_2) REFERENCES parent_tbl(fld_1, fld_2));


\d child_tbl
  Table "public.child_tbl"
  Column  |  Type   | Collation | Nullable | Default
--+-+---+--+-
 fk_fld_1 | integer |   |  |
 fk_fld_2 | integer |   |  |
Foreign-key constraints:
"child_tbl_fk_fld_1_fkey" FOREIGN KEY (fk_fld_1, fk_fld_2) 
REFERENCES parent_tbl(fld_1, fld_2)


Not sure what your setup is. That is why it is important to show the 
actual schema.










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

Re: constrain with MATCH full and NULL values in referenced table

[Francisco Olarte]

Stan:

On Mon, Aug 12, 2019 at 5:11 PM stan  wrote:
>
> I am creating a table that has 2 values in it which are keys pointing to 2
> other tables. I need for the UNIQUE combination of these 2 keys to exist in
> a fourth table. It has been recommended to use a foreign key constraint with
> the MATCH FULL parameter.
>
> Here is my question, does this deal with NULLS in the 4th table? I am
> concerned that this constraint might fail to reject an entry if one, or both
> of the 2 key values being inserted in the table are NULLS,.

If you have:

Table TA (a: PK)
Table TB (b: PK)
Table TAB( a, b,)  PK(A,B), FK(a ref TA), FK(b ref TB)
Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)

Note TAB cannot have nulls in A,B as it is the PK.

And you insert (null, null) in FOURTH it will be treated as in single
column, allowed by the fk ( but you may have non null constraints on
either a or b).
If you try to insert (a1, null) or (null, b1), it will ber rejected,
MATCH FULL does not allow null/non-null mix.

OTOH, if you use MATCH SIMPLE the partial-null cases will be not
checked at all, as if they where not null. As stated in the docs, you
can use extra single column FK in a and/or b to  get them checked in
TA/TB, and also you can put non-null constraints on either on them.

The exact combo depends on what you are trying to model, which gives
you what you want. I.e., say I want to:
1.- check a,b combos.
2.- Allow (a,null) but have it checked against ta.
3.- Forbid (null,b)
4.- Aloow (null, null)
You can use MATCH simple FK(a,b) against TAB for (1,4), single column
FK(a) against TA for(2)  and a check constraint (A is not null OR B is
null , If I'm not confused ) for (3,4).
( Note you do not have to check b against tb, because if b is present,
a is present, a,b is checked against TAB and TAB.b is checked against
TB ).

(match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
constraint forbids 3)

The DB deals with nulls in many way, you just have to enumerate your
conditions and elaborate on that.
Note in this case it FAILS to reject an entry if b is null, because I
dessigned it that way, but DOES REJECT if a is null and B is not.

Regards.
Francisco Olarte.

Subject: Re: constrain with MATCH full and NULL values in referenced table

[stan]

Cc: pgsql-general.lists.postgresql@panix.com
Subject: Re: constrain with MATCH full and NULL values in referenced table
User-Agent: Mutt/1.12.1 (2019-06-15)
X-Editor: gVim

On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:
> Stan:
> 
> On Mon, Aug 12, 2019 at 5:11 PM stan  wrote:
> >
> > I am creating a table that has 2 values in it which are keys pointing to 2
> > other tables. I need for the UNIQUE combination of these 2 keys to exist in
> > a fourth table. It has been recommended to use a foreign key constraint with
> > the MATCH FULL parameter.
> >
> > Here is my question, does this deal with NULLS in the 4th table? I am
> > concerned that this constraint might fail to reject an entry if one, or both
> > of the 2 key values being inserted in the table are NULLS,.
> 
> If you have:
> 
> Table TA (a: PK)
> Table TB (b: PK)
> Table TAB( a, b,)  PK(A,B), FK(a ref TA), FK(b ref TB)
> Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)
> 
> Note TAB cannot have nulls in A,B as it is the PK.
> 
> And you insert (null, null) in FOURTH it will be treated as in single
> column, allowed by the fk ( but you may have non null constraints on
> either a or b).
> If you try to insert (a1, null) or (null, b1), it will ber rejected,
> MATCH FULL does not allow null/non-null mix.
> 
> OTOH, if you use MATCH SIMPLE the partial-null cases will be not
> checked at all, as if they where not null. As stated in the docs, you
> can use extra single column FK in a and/or b to  get them checked in
> TA/TB, and also you can put non-null constraints on either on them.
> 
> The exact combo depends on what you are trying to model, which gives
> you what you want. I.e., say I want to:
> 1.- check a,b combos.
> 2.- Allow (a,null) but have it checked against ta.
> 3.- Forbid (null,b)
> 4.- Aloow (null, null)
> You can use MATCH simple FK(a,b) against TAB for (1,4), single column
> FK(a) against TA for(2)  and a check constraint (A is not null OR B is
> null , If I'm not confused ) for (3,4).
> ( Note you do not have to check b against tb, because if b is present,
> a is present, a,b is checked against TAB and TAB.b is checked against
> TB ).
> 
> (match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
> constraint forbids 3)
> 
> The DB deals with nulls in many way, you just have to enumerate your
> conditions and elaborate on that.
> Note in this case it FAILS to reject an entry if b is null, because I
> dessigned it that way, but DOES REJECT if a is null and B is not.
> 

Thank you.

Testing seems to verify that I have this correct.

I thought I would include what I came up with, so it gets in the archive.
Some fields eliminated for clarity.

The task_instance table is the one the original question was in reference
to.

CREATE TABLE employee (
employee_keyinteger DEFAULT nextval('employee_key_serial')
PRIMARY KEY ,
id varchar(5)  NOT NULL UNIQUE ,
first_name varchar  NOT NULL,
);

CREATE TABLE work_type (
work_type_key  integer DEFAULT nextval('work_type_key_serial') 
PRIMARY KEY ,
type   smallint UNIQUE ,
descripvarchar UNIQUE ,
modtimetimestamptz DEFAULT current_timestamp
);

CREATE TABLE rate (
employee_key   integer NOT NULL,
work_type_key  integer NOT NULL,
rate   numeric (5, 2) NOT NULL,
descripvarchar ,
modtimetimestamptz DEFAULT current_timestamp ,
FOREIGN KEY (employee_key) references employee(employee_key) ,
FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
);


CREATE TABLE task_instance (
task_instance  integer DEFAULT nextval('task_instance_key_serial')
PRIMARY KEY ,
project_keyinteger NOT NULL ,
employee_key   integer NOT NULL ,
work_type_key  integer NOT NULL ,
hours  numeric (5, 2) NOT NULL ,
work_start timestamptz ,
work_end   timestamptz ,
modtimetimestamptz DEFAULT current_timestamp ,
descripvarchar ,
FOREIGN KEY (employee_key) references employee(employee_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
FOREIGN KEY (work_type_key , employee_key) REFERENCES rate (work_type_key , 
employee_key) MATCH FULL 
);


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin


- End forwarded message -

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Subject: Re: constrain with MATCH full and NULL values in referenced table

[Adrian Klaver]

On 8/12/19 10:06 AM, stan wrote:

Cc: pgsql-general.lists.postgresql@panix.com
Subject: Re: constrain with MATCH full and NULL values in referenced table
User-Agent: Mutt/1.12.1 (2019-06-15)
X-Editor: gVim

On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:

Stan:

On Mon, Aug 12, 2019 at 5:11 PM stan  wrote:


I am creating a table that has 2 values in it which are keys pointing to 2
other tables. I need for the UNIQUE combination of these 2 keys to exist in
a fourth table. It has been recommended to use a foreign key constraint with
the MATCH FULL parameter.

Here is my question, does this deal with NULLS in the 4th table? I am
concerned that this constraint might fail to reject an entry if one, or both
of the 2 key values being inserted in the table are NULLS,.


If you have:

Table TA (a: PK)
Table TB (b: PK)
Table TAB( a, b,)  PK(A,B), FK(a ref TA), FK(b ref TB)
Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)

Note TAB cannot have nulls in A,B as it is the PK.

And you insert (null, null) in FOURTH it will be treated as in single
column, allowed by the fk ( but you may have non null constraints on
either a or b).
If you try to insert (a1, null) or (null, b1), it will ber rejected,
MATCH FULL does not allow null/non-null mix.

OTOH, if you use MATCH SIMPLE the partial-null cases will be not
checked at all, as if they where not null. As stated in the docs, you
can use extra single column FK in a and/or b to  get them checked in
TA/TB, and also you can put non-null constraints on either on them.

The exact combo depends on what you are trying to model, which gives
you what you want. I.e., say I want to:
1.- check a,b combos.
2.- Allow (a,null) but have it checked against ta.
3.- Forbid (null,b)
4.- Aloow (null, null)
You can use MATCH simple FK(a,b) against TAB for (1,4), single column
FK(a) against TA for(2)  and a check constraint (A is not null OR B is
null , If I'm not confused ) for (3,4).
( Note you do not have to check b against tb, because if b is present,
a is present, a,b is checked against TAB and TAB.b is checked against
TB ).

(match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
constraint forbids 3)

The DB deals with nulls in many way, you just have to enumerate your
conditions and elaborate on that.
Note in this case it FAILS to reject an entry if b is null, because I
dessigned it that way, but DOES REJECT if a is null and B is not.



Thank you.

Testing seems to verify that I have this correct.

I thought I would include what I came up with, so it gets in the archive.
Some fields eliminated for clarity.

The task_instance table is the one the original question was in reference
to.

CREATE TABLE employee (
 employee_keyinteger DEFAULT nextval('employee_key_serial')
 PRIMARY KEY ,
 id varchar(5)  NOT NULL UNIQUE ,
 first_name varchar  NOT NULL,
);

CREATE TABLE work_type (
 work_type_key  integer DEFAULT nextval('work_type_key_serial')
 PRIMARY KEY ,
 type   smallint UNIQUE ,
 descripvarchar UNIQUE ,
 modtimetimestamptz DEFAULT current_timestamp
);

CREATE TABLE rate (
 employee_key   integer NOT NULL,
 work_type_key  integer NOT NULL,
 rate  numeric (5, 2) NOT NULL,
 descripvarchar ,
 modtimetimestamptz DEFAULT current_timestamp ,
 FOREIGN KEY (employee_key) references employee(employee_key) ,
 FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
 CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
);


CREATE TABLE task_instance (
 task_instance  integer DEFAULT nextval('task_instance_key_serial')
 PRIMARY KEY ,
 project_keyinteger NOT NULL ,
 employee_key   integer NOT NULL ,
 work_type_key  integer NOT NULL ,
 hours numeric (5, 2) NOT NULL ,
 work_start timestamptz ,
 work_end   timestamptz ,
 modtimetimestamptz DEFAULT current_timestamp ,
 descripvarchar ,


Aren't the marked ones below redundant?:


 FOREIGN KEY (employee_key) references employee(employee_key) ,

   ^^

 FOREIGN KEY (project_key) references project(project_key) ,
 FOREIGN KEY (work_type_key) references work_type(work_type_key) ,

   ^

 FOREIGN KEY (work_type_key , employee_key) REFERENCES rate (work_type_key 
, employee_key) MATCH FULL


They are covered above.


);









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

wal receiver stops for 2 hour

[Олег Самойлов]

Hi all.

PostgeewSQL 11.4 on Centos 7.

I created a test bed on VirtualBox and test HA cluster by random failures in 
loop. Sometime, in case of longest switching from failure, I get strange 
behaviour. The walreceiver is stopped by timeout, but don't restarted for 2 
hours. May be I agree with stopping by timeout, but why postgresql wait for 2 
hour to start it again?

2019-08-12 16:34:31.118 MSK [1455] FATAL:  terminating walreceiver due to 
timeout
2019-08-12 16:34:31.119 MSK [1451] LOG:  record with incorrect prev-link 
DC7A2D84/100 at 0/D078A38
2019-08-12 18:34:50.222 MSK [14634] FATAL:  could not connect to the primary 
server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2019-08-12 18:34:50.234 MSK [8462] LOG:  fetching timeline history file for 
timeline 4 from primary server
2019-08-12 18:34:50.235 MSK [8462] LOG:  started streaming WAL from primary at 
0/D00 on timeline 3
2019-08-12 18:34:50.237 MSK [8462] LOG:  replication terminated by primary 
server
2019-08-12 18:34:50.237 MSK [8462] DETAIL:  End of WAL reached on timeline 3 at 
0/D078A38.
2019-08-12 18:34:50.238 MSK [1451] LOG:  new target timeline is 4
2019-08-12 18:34:50.239 MSK [8462] LOG:  restarted WAL streaming at 0/D00 
on timeline 4

May be reason is in restart_after_crash=off option (recommended fo HA clusters).

The postgresql config is default, except:

ident_file = '/var/lib/pgsql/pg_ident.conf'
hba_file = '/var/lib/pgsql/pg_hba.conf'
listen_addresses = '*'
log_filename = 'postgresql.%F.log'  # log file name pattern,
wal_keep_segments = 1
restart_after_crash = off

shared_buffers = 256MB
# may be this is a good compromise
synchronous_commit = remote_write
# other DC is the first, our is the last (this is different for each node)
synchronous_standby_names = 'FIRST 1 (tuchanka2a,tuchanka2c,tuchanka2b)'

And for slaves additionally:

primary_conninfo = 'host=krogan2 user=replicant application_name=tuchanka2d 
sslmode=disable'
recovery_target_timeline = 'latest'
standby_mode = 'on'

Re: Subject: Re: constrain with MATCH full and NULL values in referenced table

[stan]

On Mon, Aug 12, 2019 at 10:16:41AM -0700, Adrian Klaver wrote:
> On 8/12/19 10:06 AM, stan wrote:
> > Cc: pgsql-general.lists.postgresql@panix.com
> > Subject: Re: constrain with MATCH full and NULL values in referenced table
> > User-Agent: Mutt/1.12.1 (2019-06-15)
> > X-Editor: gVim
> > 
> > On Mon, Aug 12, 2019 at 06:22:54PM +0200, Francisco Olarte wrote:
> > > Stan:
> > > 
> > > On Mon, Aug 12, 2019 at 5:11 PM stan  wrote:
> > > > 
> > > > I am creating a table that has 2 values in it which are keys pointing 
> > > > to 2
> > > > other tables. I need for the UNIQUE combination of these 2 keys to 
> > > > exist in
> > > > a fourth table. It has been recommended to use a foreign key constraint 
> > > > with
> > > > the MATCH FULL parameter.
> > > > 
> > > > Here is my question, does this deal with NULLS in the 4th table? I am
> > > > concerned that this constraint might fail to reject an entry if one, or 
> > > > both
> > > > of the 2 key values being inserted in the table are NULLS,.
> > > 
> > > If you have:
> > > 
> > > Table TA (a: PK)
> > > Table TB (b: PK)
> > > Table TAB( a, b,)  PK(A,B), FK(a ref TA), FK(b ref TB)
> > > Table FOURTH(a,b,...) FK((A,B) ref TAB mach full)
> > > 
> > > Note TAB cannot have nulls in A,B as it is the PK.
> > > 
> > > And you insert (null, null) in FOURTH it will be treated as in single
> > > column, allowed by the fk ( but you may have non null constraints on
> > > either a or b).
> > > If you try to insert (a1, null) or (null, b1), it will ber rejected,
> > > MATCH FULL does not allow null/non-null mix.
> > > 
> > > OTOH, if you use MATCH SIMPLE the partial-null cases will be not
> > > checked at all, as if they where not null. As stated in the docs, you
> > > can use extra single column FK in a and/or b to  get them checked in
> > > TA/TB, and also you can put non-null constraints on either on them.
> > > 
> > > The exact combo depends on what you are trying to model, which gives
> > > you what you want. I.e., say I want to:
> > > 1.- check a,b combos.
> > > 2.- Allow (a,null) but have it checked against ta.
> > > 3.- Forbid (null,b)
> > > 4.- Aloow (null, null)
> > > You can use MATCH simple FK(a,b) against TAB for (1,4), single column
> > > FK(a) against TA for(2)  and a check constraint (A is not null OR B is
> > > null , If I'm not confused ) for (3,4).
> > > ( Note you do not have to check b against tb, because if b is present,
> > > a is present, a,b is checked against TAB and TAB.b is checked against
> > > TB ).
> > > 
> > > (match simple checks 1 and allows 2,3,4, FK(a) checks 2, and the check
> > > constraint forbids 3)
> > > 
> > > The DB deals with nulls in many way, you just have to enumerate your
> > > conditions and elaborate on that.
> > > Note in this case it FAILS to reject an entry if b is null, because I
> > > dessigned it that way, but DOES REJECT if a is null and B is not.
> > > 
> > 
> > Thank you.
> > 
> > Testing seems to verify that I have this correct.
> > 
> > I thought I would include what I came up with, so it gets in the archive.
> > Some fields eliminated for clarity.
> > 
> > The task_instance table is the one the original question was in reference
> > to.
> > 
> > CREATE TABLE employee (
> >  employee_keyinteger DEFAULT nextval('employee_key_serial')
> >  PRIMARY KEY ,
> >  id varchar(5)  NOT NULL UNIQUE ,
> >  first_name varchar  NOT NULL,
> > );
> > 
> > CREATE TABLE work_type (
> >  work_type_key  integer DEFAULT nextval('work_type_key_serial')
> >  PRIMARY KEY ,
> >  type   smallint UNIQUE ,
> >  descripvarchar UNIQUE ,
> >  modtimetimestamptz DEFAULT current_timestamp
> > );
> > 
> > CREATE TABLE rate (
> >  employee_key   integer NOT NULL,
> >  work_type_key  integer NOT NULL,
> >  rate  numeric (5, 2) NOT NULL,
> >  descripvarchar ,
> >  modtimetimestamptz DEFAULT current_timestamp ,
> >  FOREIGN KEY (employee_key) references employee(employee_key) ,
> >  FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
> >  CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
> > );
> > 
> > 
> > CREATE TABLE task_instance (
> >  task_instance  integer DEFAULT nextval('task_instance_key_serial')
> >  PRIMARY KEY ,
> >  project_keyinteger NOT NULL ,
> >  employee_key   integer NOT NULL ,
> >  work_type_key  integer NOT NULL ,
> >  hours numeric (5, 2) NOT NULL ,
> >  work_start timestamptz ,
> >  work_end   timestamptz ,
> >  modtimetimestamptz DEFAULT current_timestamp ,
> >  descripvarchar ,
> 
> Aren't the marked ones below redundant?:
> 
> >  FOREIGN KEY (employee_key) references employee(employee_key) ,
>^^
> >  FOREIGN KEY (project_k

Re: Subject: Re: constrain with MATCH full and NULL values in referenced table

[Adrian Klaver]

On 8/12/19 10:30 AM, stan wrote:

The task_instance table is the one the original question was in reference
to.

CREATE TABLE employee (
  employee_keyinteger DEFAULT nextval('employee_key_serial')
  PRIMARY KEY ,
  id varchar(5)  NOT NULL UNIQUE ,
  first_name varchar  NOT NULL,
);

CREATE TABLE work_type (
  work_type_key  integer DEFAULT nextval('work_type_key_serial')
  PRIMARY KEY ,
  type   smallint UNIQUE ,
  descripvarchar UNIQUE ,
  modtimetimestamptz DEFAULT current_timestamp
);

CREATE TABLE rate (
  employee_key   integer NOT NULL,
  work_type_key  integer NOT NULL,
  rate numeric (5, 2) NOT NULL,
  descripvarchar ,
  modtimetimestamptz DEFAULT current_timestamp ,
  FOREIGN KEY (employee_key) references employee(employee_key) ,
  FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
  CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
);


CREATE TABLE task_instance (
  task_instance  integer DEFAULT nextval('task_instance_key_serial')
  PRIMARY KEY ,
  project_keyinteger NOT NULL ,
  employee_key   integer NOT NULL ,
  work_type_key  integer NOT NULL ,
  hoursnumeric (5, 2) NOT NULL ,
  work_start timestamptz ,
  work_end   timestamptz ,
  modtimetimestamptz DEFAULT current_timestamp ,
  descripvarchar ,


Aren't the marked ones below redundant?:


  FOREIGN KEY (employee_key) references employee(employee_key) ,

^^

  FOREIGN KEY (project_key) references project(project_key) ,
  FOREIGN KEY (work_type_key) references work_type(work_type_key) ,

^

  FOREIGN KEY (work_type_key , employee_key) REFERENCES rate (work_type_key 
, employee_key) MATCH FULL


They are covered above.


);


OK, looks like learning time for me, again. Which is alwasy a good thing. My
thought here was that I needed to specify these on the dreivative table
(task_instnce). Are you teaching me that, since these constraints exist on
the tables that rate is derived from, I do not need to specify thmm for the
rate table?


If I'm following what you are trying to do then:

1) task_instance is dependent on the information in rate being present 
for a given combination of (work_type_key , employee_key).


2) If 1) is correct then you cannot create a record in task_instance 
until a record exists in rate.


3) 2) means you have already established a relationship to employee and 
work_type via rate.




The purpose of those is to verify that the key being inserted already exists
in the parent (eg employee) table.




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

Re: Subject: Re: constrain with MATCH full and NULL values in referenced table

[stan]

On Mon, Aug 12, 2019 at 10:40:20AM -0700, Adrian Klaver wrote:
> On 8/12/19 10:30 AM, stan wrote:
> > > > The task_instance table is the one the original question was in 
> > > > reference
> > > > to.
> > > > 
> > > > CREATE TABLE employee (
> > > >   employee_keyinteger DEFAULT 
> > > > nextval('employee_key_serial')
> > > >   PRIMARY KEY ,
> > > >   id varchar(5)  NOT NULL UNIQUE ,
> > > >   first_name varchar  NOT NULL,
> > > > );
> > > > 
> > > > CREATE TABLE work_type (
> > > >   work_type_key  integer DEFAULT nextval('work_type_key_serial')
> > > >   PRIMARY KEY ,
> > > >   type   smallint UNIQUE ,
> > > >   descripvarchar UNIQUE ,
> > > >   modtimetimestamptz DEFAULT current_timestamp
> > > > );
> > > > 
> > > > CREATE TABLE rate (
> > > >   employee_key   integer NOT NULL,
> > > >   work_type_key  integer NOT NULL,
> > > >   rate numeric (5, 2) NOT NULL,
> > > >   descripvarchar ,
> > > >   modtimetimestamptz DEFAULT current_timestamp ,
> > > >   FOREIGN KEY (employee_key) references employee(employee_key) ,
> > > >   FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
> > > >   CONSTRAINT rate_constraint UNIQUE (employee_key , work_type_key)
> > > > );
> > > > 
> > > > 
> > > > CREATE TABLE task_instance (
> > > >   task_instance  integer DEFAULT 
> > > > nextval('task_instance_key_serial')
> > > >   PRIMARY KEY ,
> > > >   project_keyinteger NOT NULL ,
> > > >   employee_key   integer NOT NULL ,
> > > >   work_type_key  integer NOT NULL ,
> > > >   hoursnumeric (5, 2) NOT NULL ,
> > > >   work_start timestamptz ,
> > > >   work_end   timestamptz ,
> > > >   modtimetimestamptz DEFAULT current_timestamp ,
> > > >   descripvarchar ,
> > > 
> > > Aren't the marked ones below redundant?:
> > > 
> > > >   FOREIGN KEY (employee_key) references employee(employee_key) ,
> > > ^^
> > > >   FOREIGN KEY (project_key) references project(project_key) ,
> > > >   FOREIGN KEY (work_type_key) references work_type(work_type_key) ,
> > > ^
> > > >   FOREIGN KEY (work_type_key , employee_key) REFERENCES rate 
> > > > (work_type_key , employee_key) MATCH FULL
> > > 
> > > They are covered above.
> > > 
> > > > );
> > 
> > OK, looks like learning time for me, again. Which is alwasy a good thing. My
> > thought here was that I needed to specify these on the dreivative table
> > (task_instnce). Are you teaching me that, since these constraints exist on
> > the tables that rate is derived from, I do not need to specify thmm for the
> > rate table?
> 
> If I'm following what you are trying to do then:
> 
> 1) task_instance is dependent on the information in rate being present for a
> given combination of (work_type_key , employee_key).
> 
> 2) If 1) is correct then you cannot create a record in task_instance until a
> record exists in rate.
> 
> 3) 2) means you have already established a relationship to employee and
> work_type via rate.
> 
Ah subtle.

Makes sense. 

In case it is not glaringly obvious to the casual observer, i am just
returning to the database world, after having spent many years in a totaly
unrelated on (power systens for large indutrials, if you are curios).

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

"Locking rows"

[stan]

I have a customer requirement/desire. The system is (among other things)
essentially a employee time sheet. The manager wants for an employee to not
be able to modify a given row in the table they enter time into once it is
committed. I personally see issues with this, but I am willing to try to give
him what he wants. If it creates issues we can sort them out, once he sees
the issues.

The only way I see to do this is to add a column (call it lock). I will
then set this up as a default entry with a value of TRUE. The form the
employee uses to enter this will, of course, not display this column. The
I will create a function that on UPDATE, checks for the presence of the 1 in
this row, and rejects the update. Then I will give the manager a form where
he can set this flag to FALSE.

Looks ugly, and convulsed to me. 

Is here a more "database native" way to handle this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: "Locking rows"

[Adrian Klaver]

On 8/12/19 10:51 AM, stan wrote:

I have a customer requirement/desire. The system is (among other things)
essentially a employee time sheet. The manager wants for an employee to not
be able to modify a given row in the table they enter time into once it is
committed. I personally see issues with this, but I am willing to try to give
him what he wants. If it creates issues we can sort them out, once he sees
the issues.

The only way I see to do this is to add a column (call it lock). I will
then set this up as a default entry with a value of TRUE. The form the
employee uses to enter this will, of course, not display this column. The
I will create a function that on UPDATE, checks for the presence of the 1 in
this row, and rejects the update. Then I will give the manager a form where
he can set this flag to FALSE.

Looks ugly, and convulsed to me.

Is here a more "database native" way to handle this?


Depends on who is doing the database record changes.

In other words are there defined roles:

https://www.postgresql.org/docs/11/sql-createrole.html

for the object(table) and the entity working with the table?



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

Re: "Locking rows"

[Adrian Klaver]

On 8/12/19 1:07 PM, stan wrote:

On Mon, Aug 12, 2019 at 12:14:25PM -0700, Adrian Klaver wrote:

On 8/12/19 10:51 AM, stan wrote:

I have a customer requirement/desire. The system is (among other things)
essentially a employee time sheet. The manager wants for an employee to not
be able to modify a given row in the table they enter time into once it is
committed. I personally see issues with this, but I am willing to try to give
him what he wants. If it creates issues we can sort them out, once he sees
the issues.

The only way I see to do this is to add a column (call it lock). I will
then set this up as a default entry with a value of TRUE. The form the
employee uses to enter this will, of course, not display this column. The
I will create a function that on UPDATE, checks for the presence of the 1 in
this row, and rejects the update. Then I will give the manager a form where
he can set this flag to FALSE.

Looks ugly, and convulsed to me.

Is here a more "database native" way to handle this?


Depends on who is doing the database record changes.

In other words are there defined roles:

https://www.postgresql.org/docs/11/sql-createrole.html

for the object(table) and the entity working with the table?


OK, maybe I could set up the "user" role, such that it does not have the
UPDATE permission on this table, just the INSERT one? Of course the "admin"
role would have the UPDATE attribute for this table.


'user' should also have SELECT if you want them to see their records.



Make sense?


Yes.






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

question about client/server version mismatches

[raf]

Hi,

macos-10.11.6
postgresql-9.5.16 (server)
postgresql-9.6.12 (client)

I've just rebooted my macos laptop for the first time in months
and have started having a problem loading database backups there.
I get this error output:

  pg_restore: [archiver (db)] Error while INITIALIZING:
  pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized 
configuration parameter "idle_in_transaction_session_timeout"
  Command was: SET idle_in_transaction_session_timeout = 0;

It seems that I was using 9.6 clients and a 9.5 server.
I'm not sure what the situation was before the reboot.
The server was probably 9.6.12 and all was well.
I never got around to fully upgrading the server
(or the old version wouldn't still be there).

Loading a 9.5 backup using the 9.6 pg_restore lead to
the above error. Changing to 9.5 clients fixed it.

Since the backup itself was from a 9.5.12 server, it
seems that the 9.6 parameter, idle_in_transaction_session_timeout,
must have been set by the 9.6 client even though it was
connected to a 9.5 server. Is that expected behaviour?

Do the clients know when each configuration parameter
was introduced and only use them when connected to servers
where they mean something? Or am I just misunderstaing
what's happening? I would have thought a new client
would be able to work with an old but supported server.

It's not a big deal. I'll get around to completing the
upgrade and it'll be fine again but I'm curious.

cheers,
raf

Re: question about client/server version mismatches

[Michael Paquier]

On Tue, Aug 13, 2019 at 12:45:35PM +1000, raf wrote:
> Since the backup itself was from a 9.5.12 server, it
> seems that the 9.6 parameter, idle_in_transaction_session_timeout,
> must have been set by the 9.6 client even though it was
> connected to a 9.5 server. Is that expected behaviour?

Yes, the compatibility of pg_dump is mentioned in the docs, and things
happen so as pg_dump can be used to transfer data to newer versions:
https://www.postgresql.org/docs/devel/app-pgdump.html
Please see from "Because pg_dump is used to transfer data to newer
versions of PostgreSQL...".

So doing a dump from a 9.5 instance using pg_dump from 9.6 would have
set the parameter.
--
Michael


signature.asc
Description: PGP signature

Re: How to gracefully keep my specific index in memory ?

[James(王旭)]

Thanks Laurenz Albe for reply.


Unfortunately this index is only frequently used during a certain period of 
time(such as 9:30am - 12:00am) .I usually encounter slow load of this index 
during some other time.
Then it seems to me the only answer for this case would be the pgprewarm and 
pg_cron ?
 
 
-- Original --
From:  "Laurenz Albe";
Date:  Mon, Aug 12, 2019 08:24 PM
To:  "James(王旭)"; 
"pgsql-general"; 

Subject:  Re: How to gracefully keep my specific index in memory ?

 

James(王旭) wrote:
> As the title,How to keep a specific index in memory gracefully?
> 
> After some statistical query, I can determine that not all indexes can be fit 
> into memory,
> but one of the most frequently used indexes(say idx_xyz) can be definitely 
> fit into
> memory(specifically ,[the size of idx_xyz]=20% x [memory size]).
> 
> I know there's pgprewarm, but I feel with pgprewarm I can't keep things under 
> control, e.g. no realtime monitor, being squeezed out of memory ,.etc.
> 
> Is it possible that I can simply do something like "select idx_xyz into xxx" 
> and keep the specific index in memory forever?

If the indexes are frequently used, they should remain cached anyway.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Fwd: Postgres HA - pacemaker RA do not support auto failback

[Shital A]

On Mon, 12 Aug 2019, 18:54 Adrien Nayrat, 
wrote:

> On 8/12/19 2:57 PM, Shital A wrote:
> >
> >
> > Hello,
> >
> > Postgres version : 9.6
> > OS:Rhel 7.6
> >
> > We are working on HA setup for postgres cluster of two nodes in
> active-passive
> > mode.
> >
> > Installed:
> > Pacemaker 1.1.19
> > Corosync 2.4.3
> >
> > The pacemaker agent with this installation doesn't support automatic
> failback.
> > What I mean by that is explained below:
> > 1. Cluster is setup like A - B with A as master.
> > 2. Kill services on A, node B will come up as master.
> > 3. node A is ready to join the cluster, we have to delete the lock file
> it
> > creates on any one of the node and execute the cleanup command to get
> the node
> > back as standby
> >
> > Step 3 is manual so HA is not achieved in real sense.
> >
> > Please help to check:
> > 1. Is there any version of the resouce agent which supports automatic
> failback?
> > To avoid generation of lock file and deleting it.
> >
> > 2. If there is no such support, what checks should be added in pgsql RA
> to achieve
> >
> > Please suggest.
> > Thanks.
> >
>
> Hello,
>
> Which RA did you use? AFAIK there is two RA :
> - pgsql :
> https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/pgsql
> - PAF : http://clusterlabs.github.io/PAF/ (Which is different from pgsql
> RA :
> http://clusterlabs.github.io/PAF/FAQ.html#why-new-ra-for-postgresql)
>
> If I am right, PAF should handle the case when there no need to perform
> pgrewind
> (if the old master is not too advanced in the transaction log).
>
> Regards,
>
> --
> Adrien NAYRAT
> https://blog.anayrat.info




Hello

We are using the pgsql RA installed with pacemaker.

Is PAF recommended over pgsql?

Has anyone changed pgsql to handle the lock file and other cases that might
not have been handled?

Please advise.

Thanks.