On 2017/08/02 20:40, Robert Haas wrote:
> On Wed, Aug 2, 2017 at 3:46 AM, Ashutosh Bapat
> <ashutosh.ba...@enterprisedb.com> wrote:
>> If the user has specified "not valid" for a constraint on the foreign
>> table, there is high chance that s/he is aware of the fact that the
>> remote table that the foreign table points to has some rows which will
>> violet the constraint. So, +1.
>
> +1 from me, too.
Alright, thanks.
Attached is a patch. I think this could be considered a bug-fix,
backpatchable to 9.6 which introduced this behavior change [1].
Thoughts?
Thanks,
Amit
[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f27a6b15e656
From a0967f1a71a65e7802f504002a6dc3dfd1f4a6bb Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 3 Aug 2017 10:31:21 +0900
Subject: [PATCH] Honor NOT VALID specification in CREATE FOREIGN TABLE
It should be possible for a user to mark the constraints on foreign
tables as NOT VALID even when creating the table, because the remote
data they point to may contain constraint-violating rows, which the
database has no way to detect and show an error message for.
---
doc/src/sgml/ref/create_foreign_table.sgml | 7 +++++++
doc/src/sgml/ref/create_table.sgml | 7 +++++++
src/backend/parser/parse_utilcmd.c | 3 ++-
3 files changed, 16 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml
b/doc/src/sgml/ref/create_foreign_table.sgml
index 065c982082..72bf37b8b9 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -222,6 +222,13 @@ CHECK ( <replaceable
class="PARAMETER">expression</replaceable> ) [ NO INHERIT ]
A constraint marked with <literal>NO INHERIT</> will not propagate to
child tables.
</para>
+
+ <para>
+ It is possible to mark the constraint as <literal>NOT VALID</> if it is
+ specified as a table constraint. If marked as such, the database will
+ not assume that the constraint holds for all the rows in the table until
+ it is validated by using the <literal>VALIDATE CONSTRAINT</> command.
+ See <xref linkend="SQL-ALTERFOREIGNTABLE">.
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index e9c2c49533..72de64a03e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -652,6 +652,13 @@ FROM ( { <replaceable
class="PARAMETER">numeric_literal</replaceable> | <replace
(<productname>PostgreSQL</> versions before 9.5 did not honor any
particular firing order for <literal>CHECK</literal> constraints.)
</para>
+
+ <para>
+ Note that even if the constraint is marked as <literal>NOT VALID</>,
+ it is considered validated as the table that is just created cannot
+ contain any data. In other words, specifying <literal>NOT VALID</> in
+ this case has no effect.
+ </para>
</listitem>
</varlistentry>
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 9f37f1b920..e54322b460 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -165,6 +165,7 @@ transformCreateStmt(CreateStmt *stmt, const char
*queryString)
Oid existing_relid;
ParseCallbackState pcbstate;
bool like_found = false;
+ bool is_foreign_table = IsA(stmt, CreateForeignTableStmt);
/*
* We must not scribble on the passed-in CreateStmt, so copy it. (This
is
@@ -330,7 +331,7 @@ transformCreateStmt(CreateStmt *stmt, const char
*queryString)
/*
* Postprocess check constraints.
*/
- transformCheckConstraints(&cxt, true);
+ transformCheckConstraints(&cxt, !is_foreign_table ? true : false);
/*
* Output results.
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
