inherit support for foreign tables

Shigeru Hanada <shigeru.hanada@gmail.com> writes:

I'd like to propose adding inheritance support for foriegn tables.
David Fetter mentioned this feature last July, but it seems stalled.
/messages/by-id/20130719005601.GA5760@fetter.org

The discussion there pointed out that not enough consideration had been
given to interactions with other commands. I'm not really satisfied
with your analysis here. In particular:

2) Allow foreign tables to have CHECK constraints
Like NOT NULL, I think we don't need to enforce the check duroing
INSERT/UPDATE against foreign table.

Really? It's one thing to say that somebody who adds a CHECK constraint
to a foreign table is responsible to make sure that the foreign data will
satisfy the constraint. It feels like a different thing to say that ALTER
TABLE ADD CONSTRAINT applied to a parent table will silently assume that
some child table that happens to be foreign doesn't need any enforcement.

Perhaps more to the point, inheritance trees are the main place where the
planner depends on the assumption that CHECK constraints represent
reality. Are we really prepared to say that it's the user's fault if the
planner generates an incorrect plan on the strength of a CHECK constraint
that's not actually satisfied by the foreign data? If so, that had better
be documented by this patch. But for a project that refuses to let people
create a local CHECK or FOREIGN KEY constraint without mechanically
checking it, it seems pretty darn weird to be so laissez-faire about
constraints on foreign data.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Nov 14, 2013 at 12:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

2) Allow foreign tables to have CHECK constraints
Like NOT NULL, I think we don't need to enforce the check duroing
INSERT/UPDATE against foreign table.

Really? It's one thing to say that somebody who adds a CHECK constraint
to a foreign table is responsible to make sure that the foreign data will
satisfy the constraint. It feels like a different thing to say that ALTER
TABLE ADD CONSTRAINT applied to a parent table will silently assume that
some child table that happens to be foreign doesn't need any enforcement.

Perhaps more to the point, inheritance trees are the main place where the
planner depends on the assumption that CHECK constraints represent
reality. Are we really prepared to say that it's the user's fault if the
planner generates an incorrect plan on the strength of a CHECK constraint
that's not actually satisfied by the foreign data? If so, that had better
be documented by this patch. But for a project that refuses to let people
create a local CHECK or FOREIGN KEY constraint without mechanically
checking it, it seems pretty darn weird to be so laissez-faire about
constraints on foreign data.

I can see both sides of this issue. We certainly have no way to force
the remote side to enforce CHECK constraints defined on the local
side, because the remote side could also be accepting writes from
other sources that don't have any matching constraint. But having said
that, I can't see any particularly principled reason why we shouldn't
at least check the new rows we insert ourselves. After all, we could
be in the situation proposed by KaiGai Kohei, where the foreign data
wrapper API is being used as a surrogate storage engine API - i.e.
there are no writers to the foreign side except ourselves. In that
situation, it would seem odd to randomly fail to enforce the
constraints.

On the other hand, the performance costs of checking every row bound
for the remote table could be quite steep. Consider an update on an
inheritance hierarchy that sets a = a + 1 for every row. If we don't
worry about verifying that the resulting rows satisfy all local-side
constraints, we can potentially ship a single update statement to the
remote server and let it do all the work there. But if we DO have to
worry about that, then we're going to have to ship every updated row
over the wire in at least one direction, if not both. If the purpose
of adding CHECK constraints was to enable constraint exclusion, that's
a mighty steep price to pay for it.

I think it's been previously proposed that we have some version of a
CHECK constraint that effectively acts as an assertion for query
optimization purposes, but isn't actually enforced by the system. I
can see that being useful in a variety of situations, including this
one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Nov 14, 2013 at 12:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

2) Allow foreign tables to have CHECK constraints
Like NOT NULL, I think we don't need to enforce the check duroing
INSERT/UPDATE against foreign table.

Really?

I think it's been previously proposed that we have some version of a
CHECK constraint that effectively acts as an assertion for query
optimization purposes, but isn't actually enforced by the system. I
can see that being useful in a variety of situations, including this
one.

Yeah, I think it would be much smarter to provide a different syntax
to explicitly represent the notion that we're only assuming the condition
is true, and not trying to enforce it.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/11/18 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.com> writes:

I think it's been previously proposed that we have some version of a
CHECK constraint that effectively acts as an assertion for query
optimization purposes, but isn't actually enforced by the system. I
can see that being useful in a variety of situations, including this
one.

Yeah, I think it would be much smarter to provide a different syntax
to explicitly represent the notion that we're only assuming the condition
is true, and not trying to enforce it.

I'd like to revisit this feature.

Explicit notation to represent not-enforcing (assertive?) is an
interesting idea. I'm not sure which word is appropriate, but for
example, let's use the word ASSERTIVE as a new constraint attribute.

CREATE TABLE parent (
id int NOT NULL,
group int NOT NULL,
name text
);

CREATE FOREIGN TABLE child_grp1 (
/* no additional column */
) INHERITS (parent) SERVER server1;
ALTER TABLE child_grp1 ADD CONSTRAINT chk_group1 CHECK (group = 1) ASSERTIVE;

If ASSERTIVE is specified, it's not guaranteed that the constraint is
enforced completely, so it should be treated as a hint for planner.
As Robert mentioned, enforcing as much as we can during INSERT/UPDATE
is one option about this issue.

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Thoughts?

--
Shigeru HANADA

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From PostgreSQL manual:
"A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply to
single tables, not to their inheritance children."

But is it possible to use index for derived table at all?
Why sequential search is used for derived table in the example below:

create table base_table (x integer primary key);
create table derived_table (y integer) inherits (base_table);
insert into base_table values (1);
insert into derived_table values (2,2);
create index derived_index on derived_table(x);
explain select * from base_table where x>=0;
QUERY PLAN
----------------------------------------------------------------------------------------------
Append (cost=0.14..4.56 rows=81 width=4)
-> Index Only Scan using base_table_pkey on base_table
(cost=0.14..3.55 rows=80 width=4)
Index Cond: (x >= 0)
-> Seq Scan on derived_table (cost=0.00..1.01 rows=1 width=4)
Filter: (x >= 0)
(5 rows)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jan 14, 2014 at 12:07 PM, knizhnik <knizhnik@garret.ru> wrote:

But is it possible to use index for derived table at all?

Yes, the planner will do an index scan when it makes sense.

Why sequential search is used for derived table in the example below:

insert into derived_table values (2,2);
create index derived_index on derived_table(x);
explain select * from base_table where x>=0;

With only 1 row in the table, the planner decides there's no point in
scanning the index. Try with more realistic data.

Regards,
Marti

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi all,

2014/1/14 Shigeru Hanada <shigeru.hanada@gmail.com>:

I'd like to revisit this feature.

Attached patch allows a foreign table to be a child of a table. It
also allows foreign tables to have CHECK constraints. These changes
provide us a chance to propagate query load to multiple servers via
constraint exclusion. If FDW supports async operation against remote
server, parallel processing (not stable but read-only case would be
find) can be achieved, though overhead of FDW mechanism is still
there.

Though this would be debatable, in current implementation, constraints
defined on a foreign table (now only NOT NULL and CHECK are supported)
are not enforced during INSERT or UPDATE executed against foreign
tables. This means that retrieved data might violates the constraints
defined on local side. This is debatable issue because integrity of
data is important for DBMS, but in the first cut, it is just
documented as a note.

Because I don't see practical case to have a foreign table as a
parent, and it avoid a problem about recursive ALTER TABLE operation,
foreign tables can't be a parent. An example of such problem is
adding constraint which is not unsupported for foreign tables to the
parent of foreign table. Propagated operation can be applied to
ordinary tables in the inheritance tree, but can't be to foreign
tables. If we allow foreign tables to be parent, it's difficult to
process ordinary tables below foreign tables in current traffic cop
mechanism.

For other commands recursively processed such as ANALYZE, foreign
tables in the leaf of inheritance tree are ignored.

Any comments or questions are welcome.
--
Shigeru HANADA

On 11/18/13, 8:36 AM, Robert Haas wrote:

On the other hand, the performance costs of checking every row bound
for the remote table could be quite steep. Consider an update on an
inheritance hierarchy that sets a = a + 1 for every row. If we don't
worry about verifying that the resulting rows satisfy all local-side
constraints, we can potentially ship a single update statement to the
remote server and let it do all the work there. But if we DO have to
worry about that, then we're going to have to ship every updated row
over the wire in at least one direction, if not both. If the purpose
of adding CHECK constraints was to enable constraint exclusion, that's
a mighty steep price to pay for it.

A sophisticated enough FDW could verify that the appropriate check already existed in tho foreign side, or it could do something like:

BEGIN;
UPDATE SET ... WHERE <where>
SELECT EXISTS( SELECT 1 WHERE <where> AND NOT (<check condition>) );

And then rollback if the SELECT returns true.

But obviously you can't always do that, so I think there's a place for both true constraints and "suggested constraints".
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2014/01/14 18:24), Shigeru Hanada wrote:

2013/11/18 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.com> writes:

I think it's been previously proposed that we have some version of a
CHECK constraint that effectively acts as an assertion for query
optimization purposes, but isn't actually enforced by the system. I
can see that being useful in a variety of situations, including this
one.

Yeah, I think it would be much smarter to provide a different syntax
to explicitly represent the notion that we're only assuming the condition
is true, and not trying to enforce it.

I'd like to revisit this feature.

Explicit notation to represent not-enforcing (assertive?) is an
interesting idea. I'm not sure which word is appropriate, but for
example, let's use the word ASSERTIVE as a new constraint attribute.

CREATE TABLE parent (
id int NOT NULL,
group int NOT NULL,
name text
);

CREATE FOREIGN TABLE child_grp1 (
/* no additional column */
) INHERITS (parent) SERVER server1;
ALTER TABLE child_grp1 ADD CONSTRAINT chk_group1 CHECK (group = 1) ASSERTIVE;

If ASSERTIVE is specified, it's not guaranteed that the constraint is
enforced completely, so it should be treated as a hint for planner.
As Robert mentioned, enforcing as much as we can during INSERT/UPDATE
is one option about this issue.

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.
qu

Does it make sense to apply "assertive" CHECK constraint on the qual
of ForeignScan to filter out tuples with violated values at the local
side, as if row-level security feature doing.
It enables to handle a situation that planner expects only "clean"
tuples are returned but FDW driver is unavailable to anomalies.

Probably, this additional check can be turned on/off on the fly,
if FDW driver has a way to inform the core system its capability,
like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
local checks.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Backward compatibility....

NOT NULL [ASSERTIVE] might be an option.

Thanks,
--
OSS Promotion Center / The PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thanks for the comments.

2014/1/21 KaiGai Kohei <kaigai@ak.jp.nec.com>:

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.
qu

Does it make sense to apply "assertive" CHECK constraint on the qual
of ForeignScan to filter out tuples with violated values at the local
side, as if row-level security feature doing.
It enables to handle a situation that planner expects only "clean"
tuples are returned but FDW driver is unavailable to anomalies.

Probably, this additional check can be turned on/off on the fly,
if FDW driver has a way to inform the core system its capability,
like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
local checks.

Hmm, IIUC you mean that local users can't (or don't need to) know that
data which violates the local constraints exist on remote side.
Applying constraints to the data which is modified through FDW would
be necessary as well. In that design, FDW is a bidirectional filter
which provides these features:

1) Don't push wrong data into remote data source, by applying local
constraints to the result of the modifying query executed on local PG.
This is not perfect filter, because remote constraints don't mapped
automatically or perfectly (imagine constraints which is available on
remote but is not supported in PG).
2) Don't retrieve wrong data from remote to local PG, by applying
local constraints

I have a concern about consistency. It has not been supported, but
let's think of Aggregate push-down invoked by a query below.

SELECT count(*) FROM remote_table;

If this query was fully pushed down, the result is the # of records
exist on remote side, but the result would be # of valid records when
we don't push down the aggregate. This would confuse users.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Backward compatibility….

Yep, backward compatibility (especially visible ones to users) should
be minimal, ideally zero.

NOT NULL [ASSERTIVE] might be an option.

Treating [ASSERTIVE | NOT ASSERTIVE] like DEFERRABLE, and allow
ingASSERTIVE for only foreign tables? It makes sense, though we need
consider exclusiveness . But It needs to default to ASSERTIVE on
foreign tables, and NOT ASSERTIVE (means "forced") on others. Isn't
is too complicated?

CREATE FOREIGN TABLE foo (
id int NOT NULL ASSERTIVE CHECK (id > 1) ASSERTIVE,
…
CONSTRAINT chk_foo_name_upper CHECK (upper(name) = name) ASSERTIVE
) SERVER server;

BTW, I noticed that this is like push-down-able expressions in
JOIN/WHERE. We need to check a CHECK constraint defined on a foreign
tables contains only expressions which have same semantics as remote
side (in practice, built-in and immutable)?
--
Shigeru HANADA

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2014/01/21 11:44), Shigeru Hanada wrote:

Thanks for the comments.

2014/1/21 KaiGai Kohei <kaigai@ak.jp.nec.com>:

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.
qu

Does it make sense to apply "assertive" CHECK constraint on the qual
of ForeignScan to filter out tuples with violated values at the local
side, as if row-level security feature doing.
It enables to handle a situation that planner expects only "clean"
tuples are returned but FDW driver is unavailable to anomalies.

Probably, this additional check can be turned on/off on the fly,
if FDW driver has a way to inform the core system its capability,
like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
local checks.

Hmm, IIUC you mean that local users can't (or don't need to) know that
data which violates the local constraints exist on remote side.
Applying constraints to the data which is modified through FDW would
be necessary as well. In that design, FDW is a bidirectional filter
which provides these features:

1) Don't push wrong data into remote data source, by applying local
constraints to the result of the modifying query executed on local PG.
This is not perfect filter, because remote constraints don't mapped
automatically or perfectly (imagine constraints which is available on
remote but is not supported in PG).
2) Don't retrieve wrong data from remote to local PG, by applying
local constraints

Yes. (1) can be done with ExecConstraints prior to FDW callback on
UPDATE or INSERT, even not a perfect solution because of side-channel
on the remote data source. For (2), my proposition tries to drop
retrieved violated tuples, however, the result is same.

I have a concern about consistency. It has not been supported, but
let's think of Aggregate push-down invoked by a query below.

SELECT count(*) FROM remote_table;

If this query was fully pushed down, the result is the # of records
exist on remote side, but the result would be # of valid records when
we don't push down the aggregate. This would confuse users.

Hmm. In this case, FDW driver needs to be responsible to push-down
the additional check quals into remote side, so it does not work
transparently towards the ForeignScan.
It might be a little bit complicated suggestion for the beginning
of the efforts.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Backward compatibility….

Yep, backward compatibility (especially visible ones to users) should
be minimal, ideally zero.

NOT NULL [ASSERTIVE] might be an option.

Treating [ASSERTIVE | NOT ASSERTIVE] like DEFERRABLE, and allow
ingASSERTIVE for only foreign tables? It makes sense, though we need
consider exclusiveness . But It needs to default to ASSERTIVE on
foreign tables, and NOT ASSERTIVE (means "forced") on others. Isn't
is too complicated?

I think it is not easy to implement assertive checks, except for
foreign tables, because all the write stuff to regular tables are
managed by PostgreSQL itself.
So, it is a good first step to add support "ASSERTIVE" CHECK on
foreign table only, and to enforce FDW drivers nothing special
from my personal sense.

How about committer's opinion?

Thanks,
--
OSS Promotion Center / The PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 20, 2014 at 9:44 PM, Shigeru Hanada
<shigeru.hanada@gmail.com> wrote:

Thanks for the comments.

2014/1/21 KaiGai Kohei <kaigai@ak.jp.nec.com>:

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.
qu

Does it make sense to apply "assertive" CHECK constraint on the qual
of ForeignScan to filter out tuples with violated values at the local
side, as if row-level security feature doing.
It enables to handle a situation that planner expects only "clean"
tuples are returned but FDW driver is unavailable to anomalies.

Probably, this additional check can be turned on/off on the fly,
if FDW driver has a way to inform the core system its capability,
like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
local checks.

Hmm, IIUC you mean that local users can't (or don't need to) know that
data which violates the local constraints exist on remote side.
Applying constraints to the data which is modified through FDW would
be necessary as well. In that design, FDW is a bidirectional filter
which provides these features:

1) Don't push wrong data into remote data source, by applying local
constraints to the result of the modifying query executed on local PG.
This is not perfect filter, because remote constraints don't mapped
automatically or perfectly (imagine constraints which is available on
remote but is not supported in PG).
2) Don't retrieve wrong data from remote to local PG, by applying
local constraints

I have a concern about consistency. It has not been supported, but
let's think of Aggregate push-down invoked by a query below.

SELECT count(*) FROM remote_table;

If this query was fully pushed down, the result is the # of records
exist on remote side, but the result would be # of valid records when
we don't push down the aggregate. This would confuse users.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Backward compatibility….

Yep, backward compatibility (especially visible ones to users) should
be minimal, ideally zero.

NOT NULL [ASSERTIVE] might be an option.

Treating [ASSERTIVE | NOT ASSERTIVE] like DEFERRABLE, and allow
ingASSERTIVE for only foreign tables? It makes sense, though we need
consider exclusiveness . But It needs to default to ASSERTIVE on
foreign tables, and NOT ASSERTIVE (means "forced") on others. Isn't
is too complicated?

CREATE FOREIGN TABLE foo (
id int NOT NULL ASSERTIVE CHECK (id > 1) ASSERTIVE,
…
CONSTRAINT chk_foo_name_upper CHECK (upper(name) = name) ASSERTIVE
) SERVER server;

BTW, I noticed that this is like push-down-able expressions in
JOIN/WHERE. We need to check a CHECK constraint defined on a foreign
tables contains only expressions which have same semantics as remote
side (in practice, built-in and immutable)?

I don't think that that ASSERTIVE is going to fly, because "assertive"
means (sayeth the Google) "having or showing a confident and forceful
personality", which is not what we mean here. It's tempting to do
something like try to replace the keyword "check" with "assume" or
"assert" or (stretching) "assertion", but that would require whichever
one we picked to be a fully-reserved keyword, which I can't think is
going to get much support here, for entirely understandable reasons.
So I think we should look for another option.

Currently, constraints can be marked NO INHERIT (though this seems to
have not been fully documented, as the ALTER TABLE page doesn't
mention it anywhere) or NOT VALID, so I'm thinking maybe we should go
with something along those lines. Some ideas:

- NO CHECK. The idea of writing CHECK (id > 1) NO CHECK is pretty
hilarious, though.
- NO VALIDATE. But then people need to understand that NOT VALID
means "we didn't validate it yet" while "no validate" means "we don't
ever intend to validate it", which could be confusing.
- NO ENFORCE. Requires a new (probably unreserved) keyword.
- NOT VALIDATED or NOT CHECKED. Same problems as NO CHECK and NO
VALIDATE, respectively, plus now we have to create a new keyword.

Another idea is to apply an extensible-options syntax to constraints,
like we do for EXPLAIN, VACUUM, etc. Like maybe:

CHECK (id > 1) OPTIONS (enforced false, valid true)

Yet another idea is to consider validity a three-state property:
either the constraint is valid (because we have checked it and are
enforcing it), or it is not valid (because we are enforcing it but
have not checked the pre-existing data), or it is assumed true
(because we are not checking or enforcing it but are believing it
anyway). So then we could have a syntax like this:

CHECK (id > 1) VALIDATE { ON | OFF | ASSERTION }

Other ideas?

One thing that's bugging me a bit about this whole line of attack is
that, in the first instance, the whole goal here is to support
inheritance hierarchies that mix ordinary tables with foreign tables.
If you have a table with children some of which are inherited and
others of which are not inherited, you're very likely going to want
your constraints enforced for real on the children that are tables and
assumed true on the children that are foreign tables, and none of what
we're talking about here gets us to that, because we normally want the
constraints to be identical throughout the inheritance hierarchy.
Maybe there's some way around that, but I'm back to wondering if it
wouldn't be better to simply silently force any constraints on a
foreign-table into assertion mode. That could be done without any new
syntax at all, and frankly I think it's what people are going to want
more often than not.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

One thing that's bugging me a bit about this whole line of attack is
that, in the first instance, the whole goal here is to support
inheritance hierarchies that mix ordinary tables with foreign tables.
If you have a table with children some of which are inherited and
others of which are not inherited, you're very likely going to want
your constraints enforced for real on the children that are tables and
assumed true on the children that are foreign tables, and none of what
we're talking about here gets us to that, because we normally want the
constraints to be identical throughout the inheritance hierarchy.

There's a nearby thread that's addressing this same question, in which
I make the case (again) that the right thing for postgres_fdw constraints
is that they're just assumed true. So I'm not sure why this conversation
is proposing to implement a lot of mechanism to do something different
from that.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jan 21, 2014 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

One thing that's bugging me a bit about this whole line of attack is
that, in the first instance, the whole goal here is to support
inheritance hierarchies that mix ordinary tables with foreign tables.
If you have a table with children some of which are inherited and
others of which are not inherited, you're very likely going to want
your constraints enforced for real on the children that are tables and
assumed true on the children that are foreign tables, and none of what
we're talking about here gets us to that, because we normally want the
constraints to be identical throughout the inheritance hierarchy.

There's a nearby thread that's addressing this same question, in which
I make the case (again) that the right thing for postgres_fdw constraints
is that they're just assumed true. So I'm not sure why this conversation
is proposing to implement a lot of mechanism to do something different
from that.

/me scratches head.

Because the other guy named Tom Lane took the opposite position on the
second message on this thread, dated 11/14/13?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

(2014/01/22 4:09), Robert Haas wrote:

On Mon, Jan 20, 2014 at 9:44 PM, Shigeru Hanada
<shigeru.hanada@gmail.com> wrote:

Thanks for the comments.

2014/1/21 KaiGai Kohei <kaigai@ak.jp.nec.com>:

In addition, an idea which I can't throw away is to assume that all
constraints defined on foreign tables as ASSERTIVE. Foreign tables
potentially have dangers to have "wrong" data by updating source data
not through foreign tables. This is not specific to an FDW, so IMO
constraints defined on foreign tables are basically ASSERTIVE. Of
course PG can try to maintain data correct, but always somebody might
break it.
qu

Does it make sense to apply "assertive" CHECK constraint on the qual
of ForeignScan to filter out tuples with violated values at the local
side, as if row-level security feature doing.
It enables to handle a situation that planner expects only "clean"
tuples are returned but FDW driver is unavailable to anomalies.

Probably, this additional check can be turned on/off on the fly,
if FDW driver has a way to inform the core system its capability,
like FDW_CAN_ENFORCE_CHECK_CONSTRAINT that informs planner to skip
local checks.

Hmm, IIUC you mean that local users can't (or don't need to) know that
data which violates the local constraints exist on remote side.
Applying constraints to the data which is modified through FDW would
be necessary as well. In that design, FDW is a bidirectional filter
which provides these features:

1) Don't push wrong data into remote data source, by applying local
constraints to the result of the modifying query executed on local PG.
This is not perfect filter, because remote constraints don't mapped
automatically or perfectly (imagine constraints which is available on
remote but is not supported in PG).
2) Don't retrieve wrong data from remote to local PG, by applying
local constraints

I have a concern about consistency. It has not been supported, but
let's think of Aggregate push-down invoked by a query below.

SELECT count(*) FROM remote_table;

If this query was fully pushed down, the result is the # of records
exist on remote side, but the result would be # of valid records when
we don't push down the aggregate. This would confuse users.

Besides CHECK constraints, currently NOT NULL constraints are
virtually ASSERTIVE (not enforcing). Should it also be noted
explicitly?

Backward compatibility�.

Yep, backward compatibility (especially visible ones to users) should
be minimal, ideally zero.

NOT NULL [ASSERTIVE] might be an option.

Treating [ASSERTIVE | NOT ASSERTIVE] like DEFERRABLE, and allow
ingASSERTIVE for only foreign tables? It makes sense, though we need
consider exclusiveness . But It needs to default to ASSERTIVE on
foreign tables, and NOT ASSERTIVE (means "forced") on others. Isn't
is too complicated?

CREATE FOREIGN TABLE foo (
id int NOT NULL ASSERTIVE CHECK (id > 1) ASSERTIVE,
�
CONSTRAINT chk_foo_name_upper CHECK (upper(name) = name) ASSERTIVE
) SERVER server;

BTW, I noticed that this is like push-down-able expressions in
JOIN/WHERE. We need to check a CHECK constraint defined on a foreign
tables contains only expressions which have same semantics as remote
side (in practice, built-in and immutable)?

I don't think that that ASSERTIVE is going to fly, because "assertive"
means (sayeth the Google) "having or showing a confident and forceful
personality", which is not what we mean here. It's tempting to do
something like try to replace the keyword "check" with "assume" or
"assert" or (stretching) "assertion", but that would require whichever
one we picked to be a fully-reserved keyword, which I can't think is
going to get much support here, for entirely understandable reasons.
So I think we should look for another option.

Currently, constraints can be marked NO INHERIT (though this seems to
have not been fully documented, as the ALTER TABLE page doesn't
mention it anywhere) or NOT VALID, so I'm thinking maybe we should go
with something along those lines. Some ideas:

- NO CHECK. The idea of writing CHECK (id > 1) NO CHECK is pretty
hilarious, though.
- NO VALIDATE. But then people need to understand that NOT VALID
means "we didn't validate it yet" while "no validate" means "we don't
ever intend to validate it", which could be confusing.
- NO ENFORCE. Requires a new (probably unreserved) keyword.
- NOT VALIDATED or NOT CHECKED. Same problems as NO CHECK and NO
VALIDATE, respectively, plus now we have to create a new keyword.

Another idea is to apply an extensible-options syntax to constraints,
like we do for EXPLAIN, VACUUM, etc. Like maybe:

CHECK (id > 1) OPTIONS (enforced false, valid true)

Yet another idea is to consider validity a three-state property:
either the constraint is valid (because we have checked it and are
enforcing it), or it is not valid (because we are enforcing it but
have not checked the pre-existing data), or it is assumed true
(because we are not checking or enforcing it but are believing it
anyway). So then we could have a syntax like this:

CHECK (id > 1) VALIDATE { ON | OFF | ASSERTION }

Other ideas?

One thing that's bugging me a bit about this whole line of attack is
that, in the first instance, the whole goal here is to support
inheritance hierarchies that mix ordinary tables with foreign tables.
If you have a table with children some of which are inherited and
others of which are not inherited, you're very likely going to want
your constraints enforced for real on the children that are tables and
assumed true on the children that are foreign tables, and none of what
we're talking about here gets us to that, because we normally want the
constraints to be identical throughout the inheritance hierarchy.
Maybe there's some way around that, but I'm back to wondering if it
wouldn't be better to simply silently force any constraints on a
foreign-table into assertion mode. That could be done without any new
syntax at all, and frankly I think it's what people are going to want
more often than not.

I'd like to vote for the idea of silently forcing any constraints on a
foreign-table into assertion mode. No new syntax and better documentation.

Thanks,

Best regards,
Etsuro Fujita

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi Hanada-san,

While still reviwing this patch, I feel this patch has given enough
consideration to interactions with other commands, but found the
following incorrect? behabior:

postgres=# CREATE TABLE product (id INTEGER, description TEXT);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE product1 () INHERITS (product) SERVER fs
OPTIONS (filename '/home/foo/product1.csv', format 'csv');
CREATE FOREIGN TABLE
postgres=# ALTER TABLE product ALTER COLUMN description SET STORAGE
EXTERNAL;
ERROR: "product1" is not a table or materialized view

ISTN the ALTER TABLE simple recursion mechanism (ie ATSimpleRecursion())
should be modified for the ALTER COLUMN SET STORAGE case.

I just wanted to quickly tell you this for you to take time to consider.

Thanks,

Best regards,
Etsuro Fujita

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2014-01-27 Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>:

While still reviwing this patch, I feel this patch has given enough
consideration to interactions with other commands, but found the following
incorrect? behabior:

postgres=# CREATE TABLE product (id INTEGER, description TEXT);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE product1 () INHERITS (product) SERVER fs
OPTIONS (filename '/home/foo/product1.csv', format 'csv');
CREATE FOREIGN TABLE
postgres=# ALTER TABLE product ALTER COLUMN description SET STORAGE
EXTERNAL;
ERROR: "product1" is not a table or materialized view

ISTN the ALTER TABLE simple recursion mechanism (ie ATSimpleRecursion())
should be modified for the ALTER COLUMN SET STORAGE case.

I just wanted to quickly tell you this for you to take time to consider.

Thanks for the review. It must be an oversight, so I'll fix it up soon.

--
Shigeru HANADA

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 27, 2014 at 05:06:19PM +0900, Etsuro Fujita wrote:

Hi Hanada-san,

While still reviwing this patch, I feel this patch has given enough
consideration to interactions with other commands, but found the
following incorrect? behabior:

postgres=# CREATE TABLE product (id INTEGER, description TEXT);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE product1 () INHERITS (product)
SERVER fs OPTIONS (filename '/home/foo/product1.csv', format 'csv');
CREATE FOREIGN TABLE
postgres=# ALTER TABLE product ALTER COLUMN description SET STORAGE
EXTERNAL;
ERROR: "product1" is not a table or materialized view

ISTN the ALTER TABLE simple recursion mechanism (ie
ATSimpleRecursion()) should be modified for the ALTER COLUMN SET
STORAGE case.

This points to a larger discussion about what precisely foreign tables
can and cannot inherit from local ones. I don't think that a generic
solution will be satisfactory, as the PostgreSQL FDW could, at least
in principle, support many more than the CSV FDW, as shown above.

In my estimation, the outcome of discussion above is not a blocker for
this patch.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Sent from my iPad

On 27-Jan-2014, at 21:03, David Fetter <david@fetter.org> wrote:

On Mon, Jan 27, 2014 at 05:06:19PM +0900, Etsuro Fujita wrote:
Hi Hanada-san,

While still reviwing this patch, I feel this patch has given enough
consideration to interactions with other commands, but found the
following incorrect? behabior:

postgres=# CREATE TABLE product (id INTEGER, description TEXT);
CREATE TABLE
postgres=# CREATE FOREIGN TABLE product1 () INHERITS (product)
SERVER fs OPTIONS (filename '/home/foo/product1.csv', format 'csv');
CREATE FOREIGN TABLE
postgres=# ALTER TABLE product ALTER COLUMN description SET STORAGE
EXTERNAL;
ERROR: "product1" is not a table or materialized view

ISTN the ALTER TABLE simple recursion mechanism (ie
ATSimpleRecursion()) should be modified for the ALTER COLUMN SET
STORAGE case.

This points to a larger discussion about what precisely foreign tables
can and cannot inherit from local ones. I don't think that a generic
solution will be satisfactory, as the PostgreSQL FDW could, at least
in principle, support many more than the CSV FDW, as shown above.

In my estimation, the outcome of discussion above is not a blocker for
this

I wonder what shall be the cases when foreign table is on a server which does not support *all* SQL features.

Does a FDW need to have the possible inherit options mentioned in its documentation for this patch?

Regards,

Atri

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers