Skip site navigation (1) Skip section navigation (2)

Re: Foreign table permissions and cloning

From: Thom Brown <thom(at)linux(dot)com>
To: Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Foreign table permissions and cloning
Date: 2011-04-01 12:13:54
Message-ID: AANLkTi=jtNa1z6J+6VZfgN6JhJ08HX1+2zA4SO21+K+h@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Rushabh LathiaDate: 2011-04-01 13:14:33
Subject: edb-postgres.exe has encountered a problem on windows
Previous:From: Andrew DunstanDate: 2011-04-01 12:13:34
Subject: Re: Should psql support URI syntax?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group