Thread: Enum in foreign table: error and correct way to handle.
I'm creating a foreign table (foo_table) in database_a. foo_table lives in database_b.foo_table has an enum (bar_type) as one of its columns. Because this enum is in database_b, the creation of the foreign table fails in database_a. database_a doesn't understand the column type. Running the following in database_a
CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER database_b
One gets the error:
ERROR: type "bar_type" does not exist
I could just create a copy of bar_type in database_a, but this feels duplicative and possibly a future cause of inconsistency / trouble. Would anyone have thoughts on best practices for handling?
Thank you!
Peter
On 21/05/15 04:23, Peter Swartz wrote: > I'm creating a foreign table (foo_table) in database_a. foo_table lives in > database_b.foo_table has an enum (bar_type) as one of its columns. Because > this enum is in database_b, the creation of the foreign table fails in > database_a. database_a doesn't understand the column type. Running the > following in database_a > > CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER > database_b > > One gets the error: > > ERROR: type "bar_type" does not exist > > I could just create a copy of bar_type in database_a, but this feels > duplicative and possibly a future cause of inconsistency / trouble. Would > anyone have thoughts on best practices for handling? A foreign table is basically an ad-hoc remote data source for the local database, so the onus is on the local database to maintain its definition of the remote table, whether it's in another (or even the same) PostgreSQL server or a completely different data source, especially as the local definition can be different from the remote one. This does mean that there's no simple way of ensuring any remote dependencies are present on the local server. PostgreSQL 9.5 will provide the IMPORT FOREIGN SCHEMA command, however this is limited to table/view definitions. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thank you for the note Ian. I definitely see your point about the onus being on the local database to maintain the definition of the remote table. Do you or anyone have this list have any experience with the resulting behavior if the definition of the enum were to become out of sync between the local database and the foreign database? In other words, suppose the foreign database adds a value to the enum, and the foreign table now has rows with this new value, while the local definition of the enum remains unchanged. Obviously, the appropriate action on my part is to maintain consistency of enum definition between the foreign and local database, but I'm curious about what behavior would result if there was an error in this manual updating process.
I may dig into this a bit further myself in a few test databases, to see what happens. Will post a response if I do.
With regards,
Peter
On Thu, May 21, 2015 at 8:06 PM, Ian Barwick <ian@2ndquadrant.com> wrote:
A foreign table is basically an ad-hoc remote data source for the local database,On 21/05/15 04:23, Peter Swartz wrote:
> I'm creating a foreign table (foo_table) in database_a. foo_table lives in
> database_b.foo_table has an enum (bar_type) as one of its columns. Because
> this enum is in database_b, the creation of the foreign table fails in
> database_a. database_a doesn't understand the column type. Running the
> following in database_a
>
> CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER
> database_b
>
> One gets the error:
>
> ERROR: type "bar_type" does not exist
>
> I could just create a copy of bar_type in database_a, but this feels
> duplicative and possibly a future cause of inconsistency / trouble. Would
> anyone have thoughts on best practices for handling?
so the onus is on the local database to maintain its definition of the
remote table, whether it's in another (or even the same) PostgreSQL server
or a completely different data source, especially as the local definition can
be different from the remote one.
This does mean that there's no simple way of ensuring any remote dependencies are
present on the local server. PostgreSQL 9.5 will provide the IMPORT FOREIGN SCHEMA
command, however this is limited to table/view definitions.
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Peter Swartz <peter.goodings.swartz@gmail.com> writes:
> suppose the foreign database adds a value to the enum, and the foreign
> table now has rows with this new value, while the local definition of the
> enum remains unchanged. Obviously, the appropriate action on my part is to
> maintain consistency of enum definition between the foreign and local
> database, but I'm curious about what behavior would result if there was an
> error in this manual updating process.
What I'd expect to happen is that you'd get errors when retrieving rows
that had the values not known on the local side.
One potential way around this is to declare the foreign table's columns
as "text" rather than enums; you would lose some error checking on the
local side, but the remote server would enforce validity whenever you
stored something. (But I'm not sure whether this hack behaves desirably
for WHERE conditions on the enum column; you'd need to test.)
regards, tom lane
Thank you for the message Tom; sounds great. I'll try that out, will check on the planner's resultant behavior and email back.
Peter
On Sat, May 23, 2015 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Peter Swartz <peter.goodings.swartz@gmail.com> writes:
> suppose the foreign database adds a value to the enum, and the foreign
> table now has rows with this new value, while the local definition of the
> enum remains unchanged. Obviously, the appropriate action on my part is to
> maintain consistency of enum definition between the foreign and local
> database, but I'm curious about what behavior would result if there was an
> error in this manual updating process.
What I'd expect to happen is that you'd get errors when retrieving rows
that had the values not known on the local side.
One potential way around this is to declare the foreign table's columns
as "text" rather than enums; you would lose some error checking on the
local side, but the remote server would enforce validity whenever you
stored something. (But I'm not sure whether this hack behaves desirably
for WHERE conditions on the enum column; you'd need to test.)
regards, tom lane