Re: Foreign table permissions and cloning

On 1 April 2011 12:57, Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> wrote:
> 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.

I'd be inclined to generalise it to say that default values can't be
used on a foreign table, and then say that as a result, serial and
bigserial can't be used.

> 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.

I see what you mean, so the error is unrelated to any foreign table
support and applies to any database object that's not a regular table.
Do we still want this behaviour for foreign tables, or should they be
made an exception as they are a type of table? Although to be fair, I
can't see the use case for it.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company