FAQ
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

Search Discussions

  • Heikki Linnakangas at Jul 20, 2011 at 5:26 pm

    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
  • David Fetter at Jul 21, 2011 at 1:48 am

    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@fetter.org> http://fetter.org/
    Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
    Skype: davidfetter XMPP: david.fetter@gmail.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
  • Albe Laurenz at Jul 21, 2011 at 7:32 am

    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
  • Shigeru Hanada at Jul 21, 2011 at 5:27 am
    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
  • Tom Lane at Jul 21, 2011 at 7:00 pm

    "Albe Laurenz" <laurenz.albe@wien.gv.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
  • Albe Laurenz at Jul 22, 2011 at 8:09 am

    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
  • Heikki Linnakangas at Jul 22, 2011 at 9:29 am

    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
  • Albe Laurenz at Jul 22, 2011 at 12:09 pm

    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
  • Robert Haas at Jul 25, 2011 at 1:39 pm

    On Fri, Jul 22, 2011 at 8:09 AM, Albe Laurenz 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
  • Tom Lane at Jul 22, 2011 at 2:01 pm

    Heikki Linnakangas 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
  • Robert Haas at Jul 22, 2011 at 3:34 pm

    On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane wrote:
    Heikki Linnakangas <heikki.linnakangas@enterprisedb.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
  • Tom Lane at Jul 22, 2011 at 4:02 pm

    Robert Haas writes:
    On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane 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
  • Robert Haas at Jul 22, 2011 at 4:05 pm

    On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Fri, Jul 22, 2011 at 10:01 AM, Tom Lane 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
  • Tom Lane at Jul 22, 2011 at 4:13 pm

    Robert Haas writes:
    On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane 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
  • Robert Haas at Jul 22, 2011 at 4:18 pm

    On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Fri, Jul 22, 2011 at 12:02 PM, Tom Lane 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
  • Tom Lane at Jul 22, 2011 at 4:32 pm

    Robert Haas writes:
    On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane 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
  • Robert Haas at Jul 22, 2011 at 4:45 pm

    On Fri, Jul 22, 2011 at 12:32 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane 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
  • Albe Laurenz at Jul 23, 2011 at 6:54 am

    Robert Haas wrote:
    On Fri, Jul 22, 2011 at 12:32 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Fri, Jul 22, 2011 at 12:13 PM, Tom Lane 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
  • Tom Lane at Jul 23, 2011 at 2:58 pm

    "Albe Laurenz" <laurenz.albe@wien.gv.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 at Jul 23, 2011 at 10:51 am

    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
  • Tom Lane at Jul 23, 2011 at 2:42 pm

    Andrew Dunstan 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
  • Andrew Dunstan at Jul 23, 2011 at 2:54 pm

    On 07/23/2011 10:42 AM, Tom Lane wrote:
    Andrew Dunstan<andrew@dunslane.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
  • Tom Lane at Jul 23, 2011 at 3:05 pm

    Andrew Dunstan 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
  • Albe Laurenz at Aug 26, 2011 at 11:27 am

    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
  • Andrew Dunstan at Aug 26, 2011 at 2:04 pm

    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
  • Tom Lane at Aug 26, 2011 at 2:09 pm

    Andrew Dunstan 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
  • Dave Page at Aug 26, 2011 at 2:36 pm

    On Fri, Aug 26, 2011 at 3:09 PM, Tom Lane wrote:
    Andrew Dunstan <andrew@dunslane.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
  • Hitoshi Harada at Aug 27, 2011 at 4:05 pm

    2011/8/26 Albe Laurenz <laurenz.albe@wien.gv.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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 20, '11 at 3:00p
activeAug 27, '11 at 4:05p
posts29
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase