Re: Foreign table permissions and cloning

On Fri, 1 Apr 2011 01:24:20 +0100
Thom Brown <thom(at)linux(dot)com> wrote:
> Also, there probably needs to be some elaboration of how a NOT NULL
> declaration operates on a foreign table column on the CREATE FOREIGN
> TABLE reference page. From what I can see, if the foreign table
> cannot be modified such as those defined using the file_fdw handler,
> it bears no relevance, and if the foreign table can be written to, it
> won't prevent NULL values being returned if they're already in there,
> just prevent them being entered (presumably). It also won't validate
> data in the writable foreign table upon its creation.

NOT NULL constraint on foreign table is just declaration and can't
force data integrity. And I noticed that CREATE FOREIGN TABLE
document doesn't mention that serial and bigserial can't be used in
foreign table. Please see foreign_table_doc.patch for this fix.

For constraint on foreign tables, once query-time-constraint was
considered, but such overhead would not be ignorable.

> And another problem I've found is if you try to create a table named
> the same as a foreign table, and you use the IF NOT EXISTS clause:
>
> postgres=# CREATE TABLE IF NOT EXISTS animals (id serial, stuff text);
> NOTICE: CREATE TABLE will create implicit sequence "animals_id_seq1"
> for serial column "animals.id"
> NOTICE: relation "animals" already exists, skipping
> CREATE TABLE
> postgres=# CREATE TABLE IF NOT EXISTS stuff (id serial, stuff text);
> NOTICE: CREATE TABLE will create implicit sequence "stuff_id_seq" for
> serial column "stuff.id"
> NOTICE: relation "stuff" already exists, skipping
> ERROR: referenced relation "stuff" is not a table
>
> The reverse doesn't error though, where you attempt to create a
> foreign table named the same as a regular table using IF NOT EXISTS.

Using int instead of serial or omitting "if not exists" prevends the
error, so I researched root cause.

CREATE TABLE with serial column is transformed into 3 DDLs:

(1) CREATE SEQUENCE, for serial column
(2) CREATE TABLE, skipped if table exists with same name
(3) ALTER SEQUENCE OWNED BY, associate sequence with table

This error occurs in (3) because process_owned_by() misunderstand
that existing table is new owner, but it's a foreign server and
shouldn't be used as owner. So same error occurs if the existing
relation was an index or a sequence.

Regards,
--
Shigeru Hanada