Problem about partitioned table

2018-10-19 Thread Mehman Jafarov 
Hi everyone,

I have a problem with partitioned table in PostgreSql.
Actually I use the version 10. I created the partitioned table in test
environment but face some problems with partitioned table constraint.
I google all about it last week and from the official site I get that
version 11 will be released and that feature will be supported as I
understand it.
>From version 11 documentation
"*Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on
partitioned tables*"
I install and configure yesterday as new 11 version released. And test it.
Unfortunately I didn't achieve again.
Neither I don't understand the new feature nor this case is actually not
supported.
Please help me about the problem.

In my test environment *CASE* is like that (I used the declarative
partitioning)

I have a *er_doc_to_user_relation* table before. And I partitioned this
table by list with column *state*.
I have  created two partitions as following
   *CREATE TABLE xx.er_doc_to_user_state_1_3*
* PARTITION OF xx.er_doc_to_user_relation
(oid,created_date,state,status,updated_date,branch_oid,state_update_date,user_position,*
*
fk_action_owner,fk_action_owner_org,fk_document,fk_flow,fk_org,fk_prew_doc_user_rel,fk_user,fk_workflow,fk_action_login_type)*
* FOR VALUES IN (1,3);*
* CREATE TABLE xx.er_doc_to_user_state_2_4_9*
* PARTITION OF xx.er_doc_to_user_relation
(oid,created_date,state,status,updated_date,branch_oid,state_update_date,user_position,*
*
fk_action_owner,fk_action_owner_org,fk_document,fk_flow,fk_org,fk_prew_doc_user_rel,fk_user,fk_workflow,fk_action_login_type)*
* FOR VALUES IN (2,4,9);*
After that I have created constraints and indexes for each partition
manually. Everything is OK until here.
When I try to create constraint in another table which references
*er_doc_to_user_relation* table.
Case 1: Try to create foreign key constraint reference to parent table
*er_doc_to_user_relation.*
   *   ALTER TABLE xx.er_doc_workflow_action*
*   ADD CONSTRAINT fk_doc_work_act FOREIGN KEY
(fk_to_user_doc_rel)*
*  REFERENCES xx.er_doc_to_user_relation(oid) MATCH SIMPLE*
*  ON UPDATE NO ACTION*
* ON DELETE NO ACTION;*
 Following error occurred:
  *ERROR: cannot reference partitioned table
"er_doc_to_user_relation"*
* SQL state: 42809*

 Because it is not supported so I try the second case as following.

Case 2: Try to create foreign key constraint  reference to each partitioned
table separately (*er_doc_to_user_state_1_3, er_doc_to_user_state_2_4_9*).
*  ALTER TABLE xx.er_doc_workflow_action*
*   ADD CONSTRAINT fk_doc_work_act_1_3 FOREIGN KEY
(fk_to_user_doc_rel)*
*  REFERENCES xx.er_doc_to_user_state_1_3(oid) MATCH SIMPLE*
* ON UPDATE NO ACTION*
* ON DELETE NO ACTION;*
 Following error occurred:
 * ERROR: insert or update on table
"er_doc_workflow_action" violates foreign key constraint
"fk_doc_work_act_1_3"*
* DETAIL: Key (fk_to_user_doc_rel)=(3hjbzok1mn100g) is not present in table
"er_doc_to_user_state_1_3". SQL state: 23503*

I think this error is normal because oid which is referenced is in other
partitioned table so it can't validate all data.
  If I try to create foreign key constraint on second partition again same
error will be occurred due to same reason.

  Note: I want to create constraint only one-to-one column (*fk_to_user_doc_rel
- oid*)

BIG QUESTION IS THAT

How can I solve this problem?  What is your recommendations?

*PLEASE HELP ME !!!*

-- 
*Best Regards,*
*Mehman Jafarov*
*DBA Aministrator at CyberNet LLC*

PostgreSQL 11 and security

2018-10-19 Thread ROS Didier 
Hi
   I would like to know what's new in security with PostgreSQL 11

   Thanks in advance

Best Regards

Didier ROS












Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.

Re: PostgreSQL 11 and security

2018-10-19 Thread Karsten Hilbert 
On Fri, Oct 19, 2018 at 10:22:05AM +, ROS Didier wrote:

>I would like to know what's new in security with PostgreSQL 11


https://www.postgresql.org/docs/current/static/release-11.html#id-1.11.6.5.5

sections "Permissions" and "Authentication"

And then, bugs have been fixed, the security implications of
which are not necessarily fully known.

Other than that, your question may need to become more specific.

Karsten
-- 
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Problem about partitioned table

2018-10-19 Thread Adrian Klaver 

On 10/19/18 2:03 AM, Mehman Jafarov wrote:

Hi everyone,

I have a problem with partitioned table in PostgreSql.
Actually I use the version 10. I created the partitioned table in test 
environment but face some problems with partitioned table constraint.
I google all about it last week and from the official site I get that 
version 11 will be released and that feature will be supported as I 
understand it.

 From version 11 documentation
"/Add support for |PRIMARY KEY|, |FOREIGN KEY|, indexes, and triggers on 
partitioned tables/"
I install and configure yesterday as new 11 version released. And test 
it. Unfortunately I didn't achieve again.
Neither I don't understand the new feature nor this case is actually not 
supported.

Please help me about the problem.


As you found out:

https://www.postgresql.org/docs/11/static/ddl-partitioning.html

5.10.2.3. Limitations

"While primary keys are supported on partitioned tables, foreign keys 
referencing partitioned tables are not supported. (Foreign key 
references from a partitioned table to some other table are supported.)"





   Note: I want to create constraint only one-to-one column 
(/fk_to_user_doc_rel - oid/)


BIG QUESTION IS THAT

How can I solve this problem?  What is your recommendations?


Well a FK is a form of a trigger, so maybe create your own trigger on 
the child table(s).




*PLEASE HELP ME !!!*

--
*/Best Regards,/*
*/Mehman Jafarov/*
*/DBA Aministrator at CyberNet LLC/*
*/
/*



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

Re: PGDG status and policy

2018-10-19 Thread Adrian Klaver 

On 10/18/18 3:36 PM, Steve Crawford wrote:
No, the *link* exists. The package doesn't (404 errors on, for example, 
Centos 7 x86_64 and several others).


According to this:

https://www.postgresql.org/message-id/04a8f81ad82d272d031f43c9f6f742e2fd3fcd30.camel%40gunduz.org

it should be fixed now. A quick check seems to indicate it is.



Cheers,
Steve

On Thu, Oct 18, 2018 at 3:22 PM Adrian Klaver > wrote:


On 10/18/18 3:02 PM, Steve Crawford wrote:
 > While looking to install version 11 I discovered that the PGDG Yum
 > repository page at
 > https://www.postgresql.org/download/linux/redhat/ includes
repository
 > RPMs for RHEL7  (x86_64 and ppc64Ie), and RHEL6 (x86_64 and i386).
 >
 > However there are no RPMs for version 6 of any derivatives (CentOS,
 > Scientific, Oracle) and the RPMs for all the RHEL derivatives are
for
 > ppc64Ie only.

My guess is the code supplying the dropdowns has not been refreshed.
Looks like the packages do exist:

https://yum.postgresql.org/repopackages.php

 >
 > To what extent are the missing repos simply due to packagers
being busy
 > and which are by policy? I looked for a packaging policy and
failed to
 > find one. Does such a thing exist?
 >
 > Cheers,
 > Steve
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

RE: PostgreSQL 11 and security

2018-10-19 Thread ROS Didier 
Hi Hilbert

Thank you for your answer. In fact, I need to update a security guide 
for PostgreSQL 11. 
And for that, I need to know what's new in the field of security for 
this version.

Best Regards
Didier ROS
-Message d'origine-
De : karsten.hilb...@gmx.net [mailto:karsten.hilb...@gmx.net] 
Envoyé : vendredi 19 octobre 2018 12:34
À : pgsql-general@lists.postgresql.org
Objet : Re: PostgreSQL 11 and security

On Fri, Oct 19, 2018 at 10:22:05AM +, ROS Didier wrote:

>I would like to know what's new in security with 
> PostgreSQL 11


https://www.postgresql.org/docs/current/static/release-11.html#id-1.11.6.5.5

sections "Permissions" and "Authentication"

And then, bugs have been fixed, the security implications of which are not 
necessarily fully known.

Other than that, your question may need to become more specific.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.

Re: PostgreSQL 11 and security

2018-10-19 Thread Adrian Klaver 

On 10/19/18 7:38 AM, ROS Didier wrote:

Hi Hilbert

Thank you for your answer. In fact, I need to update a security guide 
for PostgreSQL 11.
And for that, I need to know what's new in the field of security for 
this version.


Take a look at:

https://www.postgresql.org/docs/11/static/release-11.html

Two sections that stand out:

E.1.3.1.7. Authentication

E.1.3.1.8. Permissions




Best Regards
Didier ROS

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

Re: PGDG status and policy

2018-10-19 Thread Steve Crawford 
On Fri, Oct 19, 2018 at 7:34 AM Adrian Klaver 
wrote:

> On 10/18/18 3:36 PM, Steve Crawford wrote:
> > No, the *link* exists. The package doesn't (404 errors on, for example,
> > Centos 7 x86_64 and several others).
>
> According to this:
>
>
> https://www.postgresql.org/message-id/04a8f81ad82d272d031f43c9f6f742e2fd3fcd30.camel%40gunduz.org
>
> it should be fixed now. A quick check seems to indicate it is.
>
>
It is. Thanks. (I actually checked and discovered it was fixed, downloaded
and installed on two servers by the time the message came through.)

Cheers,
Steve

RE: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

2018-10-19 Thread Kevin Brannen 
From: Raghavendra Rao J S V [mailto:raghavendra...@gmail.com]
Sent: Thursday, October 18, 2018 7:26 AM
To: pgsql-general@lists.postgresql.org
Subject: pg_dump backup utility is taking more time around 24hrs to take the 
backup of 28GB

Hi All,

We are using pg_dump backup utility in order to take the backup of the 
database. Unfortunately,it is taking around 24hrs of time  to take the backup 
of  28GB database. Please guide me how to reduce the time and is there any 
parameter need to be modified which will help us to reduce the backup time. We 
are using Postgres 9.2 version

Note:-Kindly suggest me options using pg_dump only.

--
Regards,
Raghavendra Rao
===

I agree with Ozz, be sure you’re dumping to another drive than what your DB 
sits on.
Investigate the “-f d” option, and the “-j” option can be useful too. I’ll also 
point out that hardware makes a big difference. :)

We can dump a 180G DB in about 15min. Note, that is to an SSD RAID array and we 
do “-f d -j $num_cpus_minus_2” and there are 32 cpus on the server (so -j 30).

HTH,
Kevin

This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Fwd: What is the problem with this code?

2018-10-19 Thread Igor Korot 
Does anybody have an idea why the code below fails?

Thank you.


-- Forwarded message -
From: Igor Korot 
Date: Thu, Oct 18, 2018 at 11:49 PM
Subject: What is the problem with this code?
To: PostgreSQL ODBC list 


Hi, ALL,

[code]
std::wstring query1 = L"SHOW log_directory";
std::wstring query2 = L"SHOW log_filename";
SQLWCHAR *qry1 = new SQLWCHAR[query1.length() + 2];
SQLWCHAR *qry2 = new SQLWCHAR[query2.length() + 2];
memset( qry1, '\0', query1.length() + 2 );
memset( qry2, '\0', query2.length() + 2 );
uc_to_str_cpy( qry1, query1 );
uc_to_str_cpy( qry2, query2 );
RETCODE ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt );
if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
{
ret = SQLPrepare( m_hstmt, qry1, SQL_NTS );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
else
{
ret = SQLDescribeCol( m_hstmt, 1, columnName, 256,
&columnNameLen, &columnDataType, &columnDataSize, &columnDataDigits,
&columnDataNullable );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
[/code]

The SQLDescribeCol() call fails with the error invalid column number".

Does anybody have any idea?

Thank you.

Re: pg_dump backup utility is taking more time around 24hrs to take the backup of 28GB

2018-10-19 Thread Jeff Janes 
On Thu, Oct 18, 2018 at 8:26 AM Raghavendra Rao J S V <
raghavendra...@gmail.com> wrote:

> Hi All,
>
> We are using *pg_dump *backup utility in order to take the backup of the
> database. Unfortunately,it is taking around 24hrs of time  to take the
> backup of  28GB database. Please guide me how to reduce the time and is
> there any parameter need to be modified which will help us to reduce the
> backup time. We are using Postgres 9.2 version
>

What minor version?  How long does it take to do just a pg_dump -s?  Can
you use system tools (like top in Linux) to identify the bottleneck as CPU,
disk, etc.?

Cheers,

Jeff

Re: What is the problem with this code?

2018-10-19 Thread Thiemo Kellner 
In your place I would double check whether the table structure on the  
database is what you expect. Without knowing the code of mentioned  
function there seem only two numbers in the call. It quite misty in  
the crystal fortune telling ball to me.


Quoting Igor Korot :


Does anybody have an idea why the code below fails?

Thank you.


-- Forwarded message -
From: Igor Korot 
Date: Thu, Oct 18, 2018 at 11:49 PM
Subject: What is the problem with this code?
To: PostgreSQL ODBC list 


Hi, ALL,

[code]
std::wstring query1 = L"SHOW log_directory";
std::wstring query2 = L"SHOW log_filename";
SQLWCHAR *qry1 = new SQLWCHAR[query1.length() + 2];
SQLWCHAR *qry2 = new SQLWCHAR[query2.length() + 2];
memset( qry1, '\0', query1.length() + 2 );
memset( qry2, '\0', query2.length() + 2 );
uc_to_str_cpy( qry1, query1 );
uc_to_str_cpy( qry2, query2 );
RETCODE ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt );
if( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
{
ret = SQLPrepare( m_hstmt, qry1, SQL_NTS );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
else
{
ret = SQLDescribeCol( m_hstmt, 1, columnName, 256,
&columnNameLen, &columnDataType, &columnDataSize, &columnDataDigits,
&columnDataNullable );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1 );
result = 1;
}
[/code]

The SQLDescribeCol() call fails with the error invalid column number".

Does anybody have any idea?

Thank you.