Re: Questions and experiences writing a Foreign Data Wrapper

I wrote a FDW for Oracle to a) learn some server coding
and b) see how well the FDW API works for me.

I came up with three questions/experiences:

1) GetUserMapping throws an error if there is no
user mapping for the user (or PUBLIC).
I think that it would be much more useful if
it would return NULL or something similar instead.
Otherwise one would have to check for existence
beforehand, which is no less complicated than what
GetUserMapping does.

2) If I decide to close remote database connections after
use, I would like to do so where reasonable.
I would like to keep the connection open between query
planning and query execution and close it when the
scan is done.
The exception could be named prepared statements.
Is there a way to tell if that is the case during
planing or execution?

3) I am confused by the order of function calls
during execution of a subplan. It is like this:
BeginForeignScan
ReScanForeignScan
IterateForeignScan
IterateForeignScan
...
ReScanForeignScan
IterateForeignScan
IterateForeignScan
...
EndForeignScan
So the first ReScan is done immediately after
BeginForeignScan. Moreover, internal parameters are not
set in the BeginForeignScan call.

This is probably working as designed, but BeginForeignScan
has no way to know whether it should execute a remote
query or not. I ended up doing that in the first call to
IterateForeignScan because I saw no other way.

That seems a bit unfortunate. Is there an easy way to
change that? If not, it should be documented.

Yours,
Laurenz Albe

On 20.07.2011 18:00, Albe Laurenz wrote:
> 2) If I decide to close remote database connections after
> use, I would like to do so where reasonable.
> I would like to keep the connection open between query
> planning and query execution and close it when the
> scan is done.
> The exception could be named prepared statements.
> Is there a way to tell if that is the case during
> planing or execution?

Hmm, maybe you could add a hook to close the connection when the
transaction ends. But actually, you'd want to keep the connection open
across transactions too. Some sort of a general connection caching
facility would be useful for many FDW.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, Jul 20, 2011 at 08:26:19PM +0300, Heikki Linnakangas wrote:
> On 20.07.2011 18:00, Albe Laurenz wrote:
> >2) If I decide to close remote database connections after
> > use, I would like to do so where reasonable.
> > I would like to keep the connection open between query
> > planning and query execution and close it when the
> > scan is done.
> > The exception could be named prepared statements.
> > Is there a way to tell if that is the case during
> > planing or execution?
>
> Hmm, maybe you could add a hook to close the connection when the
> transaction ends. But actually, you'd want to keep the connection
> open across transactions too. Some sort of a general connection
> caching facility would be useful for many FDW.

For what it's worth, DBI-Link caches FDW handles in perl session
variables. I didn't really consider cross-session handle caching
because the complexity would have been large and the benefit small.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

Hi Albe,

(2011/07/21 0:00), Albe Laurenz wrote:
> 1) GetUserMapping throws an error if there is no
> user mapping for the user (or PUBLIC).
> I think that it would be much more useful if
> it would return NULL or something similar instead.
> Otherwise one would have to check for existence
> beforehand, which is no less complicated than what
> GetUserMapping does.

Adding new parameter missing_ok and returning NULL for not-found might
be reasonable. BTW, what case do you want to handle the
nonexistence of user mapping by yourself?

> 2) If I decide to close remote database connections after
> use, I would like to do so where reasonable.
> I would like to keep the connection open between query
> planning and query execution and close it when the
> scan is done.
> The exception could be named prepared statements.
> Is there a way to tell if that is the case during
> planing or execution?

Only PlanForeignScan is called during planning (PREPARE), and others are
called in execution (EXECUTE), but this must miss your point.
It seems difficult to determine the planning is for simple SELECT or
PREPARE/EXECUTE, so you would need to keep the connection alive until
the end of local session to share the connection between planning and
execution.

> 3) I am confused by the order of function calls
> during execution of a subplan. It is like this:
> BeginForeignScan
> ReScanForeignScan
> IterateForeignScan
> IterateForeignScan
> ...
> ReScanForeignScan
> IterateForeignScan
> IterateForeignScan
> ...
> EndForeignScan
> So the first ReScan is done immediately after
> BeginForeignScan. Moreover, internal parameters are not
> set in the BeginForeignScan call.
>
> This is probably working as designed, but BeginForeignScan
> has no way to know whether it should execute a remote
> query or not. I ended up doing that in the first call to
> IterateForeignScan because I saw no other way.
>
> That seems a bit unfortunate. Is there an easy way to
> change that? If not, it should be documented.

Executing remote query only in BeginScan with current FDW API would be
difficult, and this issue can't be fixed in 9.1 release. So it would be
worth documenting that subplan's RescanForeignScan will be also called
before first IterateForeignScan call.

I'm planning to propose enhancement of FDW API in next CF for 9.2
release, so your comments are very valuable.

Regards,
--
Shigeru Hanada

Heikki Linnakangas wrote:
>> 2) If I decide to close remote database connections after
>> use, I would like to do so where reasonable.
>> I would like to keep the connection open between query
>> planning and query execution and close it when the
>> scan is done.
>> The exception could be named prepared statements.
>> Is there a way to tell if that is the case during
>> planing or execution?

> Hmm, maybe you could add a hook to close the connection when the
> transaction ends. But actually, you'd want to keep the connection
> open across transactions too. Some sort of a general connection
> caching facility would be useful for many FDW.

I agree, and that is how I implemented it at the moment.
But it might be nice to give the user the option, say, if they know
that it is a long session in a daemon process that accesses the
remote table only once a day.

I'll look into the hook option.

Here are some more ideas for FDW API functions/macros that might be
useful for FDW developers.

- A function that gives you the internal and external parameters at
execution time.
- A function that gives you a type's input and output function.
- A function that gives you the OID of the foreign table owner.
- A function that gives you the list of columns of the foreign
table (atttypid, atttypmod, attname, maybe others).

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> I wrote a FDW for Oracle to a) learn some server coding
> and b) see how well the FDW API works for me.

> I came up with three questions/experiences:

> 1) GetUserMapping throws an error if there is no
> user mapping for the user (or PUBLIC).
> I think that it would be much more useful if
> it would return NULL or something similar instead.

We could make it do that, but under what circumstances would it be
useful to not throw an error? It doesn't seem like you should try
to establish a remote connection anyway, if there's no mapping.

> 3) I am confused by the order of function calls
> during execution of a subplan. It is like this:
> BeginForeignScan
> ReScanForeignScan
> IterateForeignScan
> IterateForeignScan
> ...
> ReScanForeignScan
> IterateForeignScan
> IterateForeignScan
> ...
> EndForeignScan
> So the first ReScan is done immediately after
> BeginForeignScan. Moreover, internal parameters are not
> set in the BeginForeignScan call.

> This is probably working as designed, but BeginForeignScan
> has no way to know whether it should execute a remote
> query or not.

I'd say it probably shouldn't, ever. If you look at the executor's node
init functions, none of them do any actual data fetching. They just
prepare data structures.

regards, tom lane

Tom Lane wrote:
>> 1) GetUserMapping throws an error if there is no
>> user mapping for the user (or PUBLIC).
>> I think that it would be much more useful if
>> it would return NULL or something similar instead.

> We could make it do that, but under what circumstances would it be
> useful to not throw an error? It doesn't seem like you should try
> to establish a remote connection anyway, if there's no mapping.

I guess I misunderstood the concept of user mapping.
I assumed that the user of the mapping is the table owner and
the mapping provides default values for all foreign tables
the user owns.

Does it refer to the user that executes a query?
Or is a user mapping intended to be the only source of
connection information?

I guess it is time to read my SQL Standard, but some clarification
in the documentation sure wouldn't hurt.

>> 3) I am confused by the order of function calls
>> during execution of a subplan. It is like this:
>> BeginForeignScan
>> ReScanForeignScan
>> IterateForeignScan
>> IterateForeignScan
>> ...
>> ReScanForeignScan
>> IterateForeignScan
>> IterateForeignScan
>> ...
>> EndForeignScan
>> So the first ReScan is done immediately after
>> BeginForeignScan. Moreover, internal parameters are not
>> set in the BeginForeignScan call.
>
>> This is probably working as designed, but BeginForeignScan
>> has no way to know whether it should execute a remote
>> query or not.

> I'd say it probably shouldn't, ever. If you look at the executor's
node
> init functions, none of them do any actual data fetching. They just
> prepare data structures.

That might also be a good thing to document.

Yours,
Laurenz Albe

On 22.07.2011 11:08, Albe Laurenz wrote:
> Tom Lane wrote:
>>> 1) GetUserMapping throws an error if there is no
>>> user mapping for the user (or PUBLIC).
>>> I think that it would be much more useful if
>>> it would return NULL or something similar instead.
>
>> We could make it do that, but under what circumstances would it be
>> useful to not throw an error? It doesn't seem like you should try
>> to establish a remote connection anyway, if there's no mapping.
>
> I guess I misunderstood the concept of user mapping.
> I assumed that the user of the mapping is the table owner and
> the mapping provides default values for all foreign tables
> the user owns.
>
> Does it refer to the user that executes a query?

Typically, a user mapping maps the user that executes the query, to a
username and password in the remote system.

Instead of a username and password, it could be other connection
information needed to connect to the foreign server, like SSL
certificates. Or it could even specify the server to connect to, the
details are wrapper-specific.

Note that the "user that executes a query" might not be the user that
logged in, if the query is in a security definer function.

> Or is a user mapping intended to be the only source of
> connection information?

No, you can specify connection details at per-server and
per-foreign-table level too. The FDW implementation is free to accept or
reject options where-ever it wants.

> I guess it is time to read my SQL Standard, but some clarification
> in the documentation sure wouldn't hurt.

Agreed, there doesn't seem to be any documentation on user mappings,
aside from the reference page for the CREATE USER MAPPING command. The
"5.10 Foreign Data" section should explain what user mappings are. Want
to give it a shot?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:
>> I guess I misunderstood the concept of user mapping.

>> I guess it is time to read my SQL Standard, but some clarification
>> in the documentation sure wouldn't hurt.
>
> Agreed, there doesn't seem to be any documentation on user mappings,
> aside from the reference page for the CREATE USER MAPPING command. The
> "5.10 Foreign Data" section should explain what user mappings are.
Want
> to give it a shot?

Sure, see the attached 'fdw-usermapping-doc.patch'.

I also include a 'fdw-scan-doc.patch' that tells FDW implementors *not*
to start the scan in BeginForeignScan.

Yours,
Laurenz Albe

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 22.07.2011 11:08, Albe Laurenz wrote:
>> Or is a user mapping intended to be the only source of
>> connection information?

> No, you can specify connection details at per-server and
> per-foreign-table level too. The FDW implementation is free to accept or
> reject options where-ever it wants.

Well, if we are going to take that viewpoint, then not having a user
mapping *shouldn't* be an error, for any use-case. What would be an
error would be not having the foreign-user-name-or-equivalent specified
anywhere in the applicable options, but it's up to the FDW to notice and
complain about that.

I am not, however, convinced that that's a legitimate reading of the SQL
spec. Surely user mappings are meant to constrain which users can
connect to a given foreign server.

regards, tom lane

On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> On 22.07.2011 11:08, Albe Laurenz wrote:
>>> Or is a user mapping intended to be the only source of
>>> connection information?
>
>> No, you can specify connection details at per-server and
>> per-foreign-table level too. The FDW implementation is free to accept or
>> reject options where-ever it wants.
>
> Well, if we are going to take that viewpoint, then not having a user
> mapping *shouldn't* be an error, for any use-case.  What would be an
> error would be not having the foreign-user-name-or-equivalent specified
> anywhere in the applicable options, but it's up to the FDW to notice and
> complain about that.

+1.

> I am not, however, convinced that that's a legitimate reading of the SQL
> spec.  Surely user mappings are meant to constrain which users can
> connect to a given foreign server.

Surely that's the job for the table's ACL, no?

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I am not, however, convinced that that's a legitimate reading of the SQL
>> spec. Surely user mappings are meant to constrain which users can
>> connect to a given foreign server.

> Surely that's the job for the table's ACL, no?

No, a table ACL constrains access to a table. Different issue.

In particular I find the following in SQL-MED:2008 4.14.1:

NOTE 9 - Privileges granted on foreign tables are not privileges to use
the data constituting foreign tables, but privileges to use the
definitions of the foreign tables. The privileges to access the data
constituting the foreign tables are enforced by the foreign server,
based on the user mapping. Consequently, a request by an SQL-client to
access external data may raise exceptions.

regards, tom lane

On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I am not, however, convinced that that's a legitimate reading of the SQL
>>> spec.  Surely user mappings are meant to constrain which users can
>>> connect to a given foreign server.
>
>> Surely that's the job for the table's ACL, no?
>
> No, a table ACL constrains access to a table.  Different issue.
>
> In particular I find the following in SQL-MED:2008 4.14.1:
>
> NOTE 9 - Privileges granted on foreign tables are not privileges to use
> the data constituting foreign tables, but privileges to use the
> definitions of the foreign tables. The privileges to access the data
> constituting the foreign tables are enforced by the foreign server,
> based on the user mapping. Consequently, a request by an SQL-client to
> access external data may raise exceptions.

I read that to mean that the remote side might chuck an error
depending on the credentials used to connect. I don't read it to be
saying that the local side is required to do anything in particular.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In particular I find the following in SQL-MED:2008 4.14.1:
>>
>> NOTE 9 - Privileges granted on foreign tables are not privileges to use
>> the data constituting foreign tables, but privileges to use the
>> definitions of the foreign tables. The privileges to access the data
>> constituting the foreign tables are enforced by the foreign server,
>> based on the user mapping. Consequently, a request by an SQL-client to
>> access external data may raise exceptions.

> I read that to mean that the remote side might chuck an error
> depending on the credentials used to connect. I don't read it to be
> saying that the local side is required to do anything in particular.

Well, if you read it that way, then CREATE USER MAPPING with an empty
option set is a no-op: the behavior of the FDW would be the same whether
you'd executed it or not. Which doesn't seem to me to satisfy the
principle of least surprise, nor the letter of the spec.

regards, tom lane

On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> In particular I find the following in SQL-MED:2008 4.14.1:
>>>
>>> NOTE 9 - Privileges granted on foreign tables are not privileges to use
>>> the data constituting foreign tables, but privileges to use the
>>> definitions of the foreign tables. The privileges to access the data
>>> constituting the foreign tables are enforced by the foreign server,
>>> based on the user mapping. Consequently, a request by an SQL-client to
>>> access external data may raise exceptions.
>
>> I read that to mean that the remote side might chuck an error
>> depending on the credentials used to connect.  I don't read it to be
>> saying that the local side is required to do anything in particular.
>
> Well, if you read it that way, then CREATE USER MAPPING with an empty
> option set is a no-op: the behavior of the FDW would be the same whether
> you'd executed it or not.  Which doesn't seem to me to satisfy the
> principle of least surprise, nor the letter of the spec.

I think what they're saying is that they expect the credentials to be
stored in the user mapping. But that seems like a fairly silly
requirement, since it's not difficult to imagine wanting all of your
local users to connect to the remote side with the same set of
credentials; or wanting, perhaps, to connect to some data source that
doesn't even require credentials, like a CSV file.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, if you read it that way, then CREATE USER MAPPING with an empty
>> option set is a no-op: the behavior of the FDW would be the same whether
>> you'd executed it or not. Which doesn't seem to me to satisfy the
>> principle of least surprise, nor the letter of the spec.

> I think what they're saying is that they expect the credentials to be
> stored in the user mapping. But that seems like a fairly silly
> requirement, since it's not difficult to imagine wanting all of your
> local users to connect to the remote side with the same set of
> credentials ...

But if you want that, you'd do CREATE USER MAPPING FOR PUBLIC. What
disturbs me about this approach is that it'd have the effect of a public
mapping with no options existing by default, and being in fact
impossible to remove. Now, depending on what the FDW chooses to require
in the way of options, that might not be insecure; but it sure seems
like a foot-gun waiting to fire on somebody.

regards, tom lane

On Fri, Jul 22, 2011 at 12:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Well, if you read it that way, then CREATE USER MAPPING with an empty
>>> option set is a no-op: the behavior of the FDW would be the same whether
>>> you'd executed it or not.  Which doesn't seem to me to satisfy the
>>> principle of least surprise, nor the letter of the spec.
>
>> I think what they're saying is that they expect the credentials to be
>> stored in the user mapping.  But that seems like a fairly silly
>> requirement, since it's not difficult to imagine wanting all of your
>> local users to connect to the remote side with the same set of
>> credentials ...
>
> But if you want that, you'd do CREATE USER MAPPING FOR PUBLIC.  What
> disturbs me about this approach is that it'd have the effect of a public
> mapping with no options existing by default, and being in fact
> impossible to remove.  Now, depending on what the FDW chooses to require
> in the way of options, that might not be insecure; but it sure seems
> like a foot-gun waiting to fire on somebody.

Maybe. On the other hand, I think there's a pretty strong usability
argument against the way it works right now.

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

Robert Haas wrote:
>On Fri, Jul 22, 2011 at 12:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> Well, if you read it that way, then CREATE USER MAPPING with an empty
>>>> option set is a no-op: the behavior of the FDW would be the same whether
>>>> you'd executed it or not.  Which doesn't seem to me to satisfy the
>>>> principle of least surprise, nor the letter of the spec.

>>> I think what they're saying is that they expect the credentials to be
>>> stored in the user mapping.  But that seems like a fairly silly
>>> requirement, since it's not difficult to imagine wanting all of your
>>> local users to connect to the remote side with the same set of
>>> credentials ...

>> But if you want that, you'd do CREATE USER MAPPING FOR PUBLIC.  What
>> disturbs me about this approach is that it'd have the effect of a public
>> mapping with no options existing by default, and being in fact
>> impossible to remove.  Now, depending on what the FDW chooses to require
>> in the way of options, that might not be insecure; but it sure seems
>> like a foot-gun waiting to fire on somebody.

> Maybe. On the other hand, I think there's a pretty strong usability
> argument against the way it works right now.

There is no specific way it works right now; in effect it's up to
the implementor of the foreign data wrapper to give these constructs
any possible meaning.
What could and should be done is document how we *intend* these things
to get used so that implementors can adhere to that.

I don't like to think of a user mapping as a means to restrict access
to the foreign data source, because in effect that is the same as
restricting access to the foreign table, which is the ACL's job.
Moreover, that would contradict the way file_fdw is currently
implemented.

After reading the standard, I'm inclined to think that lack of
user mapping is the same as having no foreign credentials. This might
be appropriate in some cases, e.g. where you give the PostgreSQL
OS user permission to connect without credentials (think "trust").
That might be desirable if you want to avoid storing passwords in
system catalogs. So I think that there should be no error
"user mapping not found", but instead you should get "could not
authenticate" from the remote.

CREATE USER MAPPING FOR PUBLIC would be a no-op in this case.

Yours,
Laurenz Albe

On 07/22/2011 11:34 AM, Robert Haas wrote:
>>
>>
>>> No, you can specify connection details at per-server and
>>> per-foreign-table level too. The FDW implementation is free to accept or
>>> reject options where-ever it wants.
>> Well, if we are going to take that viewpoint, then not having a user
>> mapping *shouldn't* be an error, for any use-case. What would be an
>> error would be not having the foreign-user-name-or-equivalent specified
>> anywhere in the applicable options, but it's up to the FDW to notice and
>> complain about that.
> +1.

What does the standard say?

You can get around most of the inconvenience with an empty PUBLIC user
mapping, although it's mildly annoying if you've forgotten to make one.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> What does the standard say?

Well, there is not a statement in so many words that you have to have a
relevant USER MAPPING to use a foreign table. But the spec does specify
that an FDW's ConnectServer function takes a UserHandle as one input
parameter and should throw an exception if that handle isn't valid.
And as far as I can tell a UserHandle can only be created from a
relevant USER MAPPING entry. So I think the behavior I'm arguing for
would emerge from an FDW that was built using the spec-defined API.
We only have an opportunity to argue about it because we chose to
invent our own FDW API.

regards, tom lane

On 07/23/2011 10:42 AM, Tom Lane wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>> What does the standard say?
> Well, there is not a statement in so many words that you have to have a
> relevant USER MAPPING to use a foreign table. But the spec does specify
> that an FDW's ConnectServer function takes a UserHandle as one input
> parameter and should throw an exception if that handle isn't valid.
> And as far as I can tell a UserHandle can only be created from a
> relevant USER MAPPING entry. So I think the behavior I'm arguing for
> would emerge from an FDW that was built using the spec-defined API.
> We only have an opportunity to argue about it because we chose to
> invent our own FDW API.
>
>

In that case I think I'm in favor of the suggestion of an implied empty
user mapping for PUBLIC, as long as it can be overridden.

It does seem to be very late in the day to be arguing about such
details, though, unless we're talking about changing it in the 9.2 cycle.

cheers

andrew

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> I don't like to think of a user mapping as a means to restrict access
> to the foreign data source, because in effect that is the same as
> restricting access to the foreign table, which is the ACL's job.

No, the standard is quite clear that those are distinct things.
See the NOTE I quoted upthread.

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> In that case I think I'm in favor of the suggestion of an implied empty
> user mapping for PUBLIC, as long as it can be overridden.

But how would you do that (override it)? All you can do is create an
explicit mapping, and then you still have a mapping that allows access
to PUBLIC. (Or not, but if an empty one does, you're stuck.)

regards, tom lane

On Fri, Jul 22, 2011 at 8:09 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> Heikki Linnakangas wrote:
>>> I guess I misunderstood the concept of user mapping.
>
>>> I guess it is time to read my SQL Standard, but some clarification
>>> in the documentation sure wouldn't hurt.
>>
>> Agreed, there doesn't seem to be any documentation on user mappings,
>> aside from the reference page for the CREATE USER MAPPING command. The
>> "5.10 Foreign Data" section should explain what user mappings are.
> Want
>> to give it a shot?
>
> Sure, see the attached 'fdw-usermapping-doc.patch'.
>
> I also include a 'fdw-scan-doc.patch' that tells FDW implementors *not*
> to start the scan in BeginForeignScan.

I've applied these with some modifications.

Thanks for the patches!

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

I wrote:
> I wrote a FDW for Oracle to a) learn some server coding
> and b) see how well the FDW API works for me.

I have released the software on PgFoundry:
http://oracle-fdw.projects.postgresql.org/

Would it make sense to mention that in chapter 5.10
of the documentation?

Yours,
Laurenz Albe

On 08/26/2011 07:27 AM, Albe Laurenz wrote:
> I wrote:
>> I wrote a FDW for Oracle to a) learn some server coding
>> and b) see how well the FDW API works for me.
> I have released the software on PgFoundry:
> http://oracle-fdw.projects.postgresql.org/
>
> Would it make sense to mention that in chapter 5.10
> of the documentation?
>
>

I don't think so, any more than any other external module should be
mentioned in the docs. There are known FDWs for several well known
external databases, several file formats, and more exotic data sources
such as twitter. I don't think we want to maintain a list of these in
the docs.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 08/26/2011 07:27 AM, Albe Laurenz wrote:
>> http://oracle-fdw.projects.postgresql.org/
>>
>> Would it make sense to mention that in chapter 5.10
>> of the documentation?

> I don't think so, any more than any other external module should be
> mentioned in the docs. There are known FDWs for several well known
> external databases, several file formats, and more exotic data sources
> such as twitter. I don't think we want to maintain a list of these in
> the docs.

Wiki page, maybe?

regards, tom lane

On Fri, Aug 26, 2011 at 3:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 08/26/2011 07:27 AM, Albe Laurenz wrote:
>>> http://oracle-fdw.projects.postgresql.org/
>>>
>>> Would it make sense to mention that in chapter 5.10
>>> of the documentation?
>
>> I don't think so, any more than any other external module should be
>> mentioned in the docs. There are known FDWs for several well known
>> external databases, several file formats, and more exotic data sources
>> such as twitter. I don't think we want to  maintain a list of these in
>> the docs.
>
> Wiki page, maybe?

For example this one: http://wiki.postgresql.org/wiki/Foreign_data_wrappers

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

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

2011/8/26 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:
> I wrote:
>> I wrote a FDW for Oracle to a) learn some server coding
>> and b) see how well the FDW API works for me.
>
> I have released the software on PgFoundry:
> http://oracle-fdw.projects.postgresql.org/
>
> Would it make sense to mention that in chapter 5.10
> of the documentation?

Let's share it on PGXN! There are already three FDWs, and I'm gonig to
add one more.

Thanks,
--
Hitoshi Harada