Обсуждение: SQL/MED estimated time of arrival?

Hi SQL/MED developers,<br /><br /> Our company has just finished development of a database extension for Informix that
providestabular access to various types of structured files (NetCDF and HDF5, with more types to come). We would like
toport this logic to run on PostgreSQL, since many of our potential customers use PostgreSQL.<br /><br /> On Informix,
wewere able to take advantage of the VTI (Virtual Table Interface) feature to support "table" scans and indexing. (See
<aeudora="autourl" href="http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_vti.html">
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_vti.html</a>.) Do you have any idea of how
longit will be before SQL/MED on PostgreSQL will be available, and perhaps how similar it will be to Informix VTI?<br
/><br/> Thanks,<br /> Eric.<br /><p> ********************************************** <br /> Eric Davies, M.Sc. <br />
SeniorProgrammer Analyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave., Suite 418<br /> Victoria BC
V8P2L5<br /> Canada<br /><br /> Tel: (250) 704-4428<br /> Web: <a eudora="autourl" href="http://www.barrodale.com/">
<fontcolor="#0000FF"><u>http://www.barrodale.com</u></font></a><br /> Email: eric@barrodale.com <br />
**********************************************<br/><br /><br /> 

On Wed, 03 Nov 2010 13:32:18 -0700
Eric Davies <eric@barrodale.com> wrote:
> On Informix, we were able to take advantage of the VTI (Virtual Table 
> Interface) feature to support "table" scans and indexing. (See 
> http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_vti.html 
> .) Do you have any idea of how long it will be before SQL/MED on 
> PostgreSQL will be available, and perhaps how similar it will be to 
> Informix VTI?

SQL/MED is now under discussion/development for PostgreSQL 9.1, and
9.1 would be released one year after 9.0, maybe around Sep 2011? For
detail of release schedule, please see the development plan of
PostgreSQL 9.1.

http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan

I looked into VTI documents you've pointed.  ISTM that VTI and SQL/MED
would have a lot of common ideas, and most of VTI items would be able
to be mapped to one of SQL/MED items, except features about updating
data and indexing.

For example:

* PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
* am_scancost()         -> FdwRoutine.EstimateCosts()
* am_open()             -> FdwRoutine.Open()
* am_beginscan()        -> first call of FdwRoutine.Iterate()?
* am_getnext()          -> FdwRoutine.Iterate()
* am_rescan()           -> FdwRoutine.ReOpen()
* am_close()            -> FdwRoutine.Close()
* Table descriptor      -> Relation, Form_pg_class
* Qual descriptor       -> PlanState.qual

I hope the summary of SQL/MED described in wiki page helps you.

http://wiki.postgresql.org/wiki/SQL/MED

Any comments and questions are welcome.

Regards,
--
Shigeru Hanada

On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
> For example:
> * PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
> * am_scancost()         -> FdwRoutine.EstimateCosts()
> * am_open()             -> FdwRoutine.Open()
> * am_beginscan()        -> first call of FdwRoutine.Iterate()?

It might be good to have a separated "beginscan" method if we use
asynchronous scans in multiple foreign servers in one query
because multiple foreign servers can run their queries in parallel.
(Imagine that pushing-down aggregate function into each foreign server.)
I think it is different from "open" because it is called
before query execution, for example by EXPLAIN.

> * am_getnext()          -> FdwRoutine.Iterate()
> * am_rescan()           -> FdwRoutine.ReOpen()
> * am_close()            -> FdwRoutine.Close()
> * Table descriptor      -> Relation, Form_pg_class
> * Qual descriptor       -> PlanState.qual

Do you think you have all counterpart methods for VTI AMs?
If so, it's a good news ;-)  We could support foreign table
features as same level as Informix.

--
Itagaki Takahiro

On Thu, 4 Nov 2010 18:22:52 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:
> On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
> <hanada@metrosystems.co.jp> wrote:
> > For example:
> > * PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
> > * am_scancost()         -> FdwRoutine.EstimateCosts()
> > * am_open()             -> FdwRoutine.Open()
> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
> 
> It might be good to have a separated "beginscan" method if we use
> asynchronous scans in multiple foreign servers in one query
> because multiple foreign servers can run their queries in parallel.
> (Imagine that pushing-down aggregate function into each foreign server.)

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Pushing aggregate down to foreign server would be efficient, but need
another hook which can create one ForeignScan node which have "Agg +
ForeignScan" functionality.  Same optimization would be able to apply
for Sort and Limit.  Such optimization should be done in optimizer
with estimated costs?  Or FDW's hook function may change plan tree
which was created by planner?

> I think it is different from "open" because it is called
> before query execution, for example by EXPLAIN.

Right, I've misunderstood.

VTI programmer's guide says that am_open is called before processing
SQL to initialize input or output, and called for not only SELECT but
also other queries using a virtual table such as INSERT and DROP TABLE. 
The am_open would have no counterpart in SQL/MED.

> Do you think you have all counterpart methods for VTI AMs?
> If so, it's a good news ;-)  We could support foreign table
> features as same level as Informix.

Not all, but most of them for read-only access.

VTI supports updating external data and various management tasks via
SQL, but SQL/MED supports (at least in standard) only read access. 
The full set of ACCESS_METHOD functions are:
   am_create       CREATE FOREIGN TABLE   am_drop         DROP TABLE
   am_stats        gather statistics (ANALYZE)   am_check        verify data structure and index consistency
   am_open         initialize access to a virtual table                   (might connect to external server)   am_close
      finalize access to a virtual table
 
   am_scancost     estimate cost of a scan   am_beginscan    initialize scan   am_getbyid      get a tuple by row-id
am_getnext     get next tuple(s)   am_rescan       reset state of scanning   am_endscan      finalize scan
 
   am_insert       insert a tuple and return row-id   am_update       update a tuple by row-id   am_delete       delete
atuple by row-id   am_truncate     truncate table
 

VTI might be similar to storage engine of MySQL or heap-am of PG,
rather than SQL/MED of PG.

Like FOREIGN INDEX of HiRDB, Informix has Virtual Index Interface, and
am_getbyid is used to get a tuple by row-id.  I'll research more about
VTI and VII for revising design of SQL/MED.

Regards,
--
Shigeru Hanada

On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
>> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
>> It might be good to have a separated "beginscan" method if we use
>> asynchronous scans in multiple foreign servers in one query
>
> You mean that separated beginscan (FdwRoutine.BeginScan?) starts
> asynchronous query and returns immediately, and FdwRoutine.Iterate
> returns result of that query?

Yes. Each BeginScan() in the executor node tree will be called at
the beginning of executor's run. The callback should not block
the caller. OTOH, Iterate() are called at the first time tuples
in the node are required.

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

--
Itagaki Takahiro

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:
> On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
> <hanada@metrosystems.co.jp> wrote:
> >> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
> >> It might be good to have a separated "beginscan" method if we use
> >> asynchronous scans in multiple foreign servers in one query
> >
> > You mean that separated beginscan (FdwRoutine.BeginScan?) starts
> > asynchronous query and returns immediately, and FdwRoutine.Iterate
> > returns result of that query?
> 
> Yes. Each BeginScan() in the executor node tree will be called at
> the beginning of executor's run. The callback should not block
> the caller. OTOH, Iterate() are called at the first time tuples
> in the node are required.

Thanks, now I see your point.  Current FdwRoutine has no appropriate
function because Open is called from ExecutorStart which is used by
EXPLAIN too.

But then we have mismatch between executor node interface and FDW
interface about BeginScan.  Should we add new function such as
ExecBeginNode and call ExecBeginXXX for each plan node?

New Query Processing Control Flow would be:
# based on README of executor directory
   CreateQueryDesc
   ExecutorStart       CreateExecutorState           creates per-query context       switch to per-query context to run
ExecInitNode      ExecInitNode --- recursively scans plan tree           CreateExprContext               creates
per-tuplecontext           ExecInitExpr
 
   ExecutorRun       ExecBeginNode(new) --- recursively scans plan tree           call ExecBeginXXXS for each plan node
     ExecProcNode --- recursively called in per-query context           ExecEvalExpr --- called in per-tuple context
      ResetExprContext --- to free memory
 
   ExecutorEnd       ExecEndNode --- recursively releases resources       FreeExecutorState           frees per-query
contextand child contexts
 
   FreeQueryDesc

> PL/Proxy has a similar functionality with RUN ON ALL to start queries
> in parallel. So, I think it's a infrastructure commonly required.
I noticed the lack of consideration about cache invalidation from
reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

Regards,
--
Shigeru Hanada

Shigeru HANADA <hanada@metrosystems.co.jp> writes:
> Thanks, now I see your point.  Current FdwRoutine has no appropriate
> function because Open is called from ExecutorStart which is used by
> EXPLAIN too.

> But then we have mismatch between executor node interface and FDW
> interface about BeginScan.  Should we add new function such as
> ExecBeginNode and call ExecBeginXXX for each plan node?

That seems like a massive amount of new code, and wasted cycles during
every query startup, to fix a very small problem.

There's a flag EXEC_FLAG_EXPLAIN_ONLY that tells node Init functions
whether the query is going to be run "for real" or only EXPLAINed.
Use that to decide whether to do any real work.
        regards, tom lane

2010/11/5 Shigeru HANADA <hanada@metrosystems.co.jp>:
> On Fri, 5 Nov 2010 16:27:49 +0900
> Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:
>> PL/Proxy has a similar functionality with RUN ON ALL to start queries
>> in parallel. So, I think it's a infrastructure commonly required.
> I noticed the lack of consideration about cache invalidation from
> reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

And if we really make this async query come true, I suggest designing
resource (i.e. remote connection) management very carefully. When the
executor fails in the middle of its execution, it possibly fails to
release its own resource; close() in ExecutorEnd() will never be
called. As far as I know files and memory are released automatically
in the current mechanism, but MED APIs will use their own resources
other than them.

Regards,


-- 
Hitoshi Harada

Hitoshi Harada <umi.tanuki@gmail.com> writes:
> And if we really make this async query come true, I suggest designing
> resource (i.e. remote connection) management very carefully. When the
> executor fails in the middle of its execution, it possibly fails to
> release its own resource; close() in ExecutorEnd() will never be
> called. As far as I know files and memory are released automatically
> in the current mechanism, but MED APIs will use their own resources
> other than them.

The way to fix that is for the FDW to hook into the ResourceOwner
mechanism (via RegisterResourceReleaseCallback).  Then it can track
and clean up things it knows about just as "automatically" as anything
else is.

Of course, if you lose your network connection to the remote DB,
you have to assume it will clean up of its own accord.
        regards, tom lane

On Fri, 05 Nov 2010 10:43:45 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Shigeru HANADA <hanada@metrosystems.co.jp> writes:
> > Thanks, now I see your point.  Current FdwRoutine has no appropriate
> > function because Open is called from ExecutorStart which is used by
> > EXPLAIN too.
> 
> > But then we have mismatch between executor node interface and FDW
> > interface about BeginScan.  Should we add new function such as
> > ExecBeginNode and call ExecBeginXXX for each plan node?
> 
> That seems like a massive amount of new code, and wasted cycles during
> every query startup, to fix a very small problem.

Agreed.

> There's a flag EXEC_FLAG_EXPLAIN_ONLY that tells node Init functions
> whether the query is going to be run "for real" or only EXPLAINed.
> Use that to decide whether to do any real work.

I missed that flag.  That flag would make ExecInitForeignScan be able
to skip calling FdwRoutine.BeginScan when the query was an EXPLAIN
without ANALYZE.  Thanks for the suggestion.

Regards,
--
Shigeru Hanada

On Sat, 6 Nov 2010 16:04:37 +0900
Hitoshi Harada <umi.tanuki@gmail.com> wrote:
> 2010/11/5 Shigeru HANADA <hanada@metrosystems.co.jp>:
> > On Fri, 5 Nov 2010 16:27:49 +0900
> > Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:
> >> PL/Proxy has a similar functionality with RUN ON ALL to start queries
> >> in parallel. So, I think it's a infrastructure commonly required.
> > I noticed the lack of consideration about cache invalidation from
> > reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)
> 
> And if we really make this async query come true, I suggest designing
> resource (i.e. remote connection) management very carefully. When the
> executor fails in the middle of its execution, it possibly fails to
> release its own resource; close() in ExecutorEnd() will never be
> called. As far as I know files and memory are released automatically
> in the current mechanism, but MED APIs will use their own resources
> other than them.

Yes, managegement of FDW's resources is very important issue.  Curren
FdwRoutine includes ConnectServer and FreeFSConnection, but they might
not be enough to manage FDW's resources by backend in common way. 
Because connection is not only resource FDW use.  Possible resources
are:
 - Files (Virtual File descriptor would help to manage) - Database connections (might be cached) - Server-side cursors
(wouldbe released with DB connection?) - Heap memory (for instance, libpq uses malloc)
 

For example, if postgresql_fdw uses server-side cursor to retreive
result tuples, it would be required to CLOSE cursors at the end of
transaction.  Closing cursor at the end of session wouldn't be good
idea because clients might pool and reuse connections.

How about removing them, ConnectServer and FreeFSConnection, from
FdwRoutine and leaving the responsibility of resource management to
each FDW?  Each FDW would have to use mechanism such as Virtual File
and ResourceOwner to manage resources properly, though.

Regards,
--
Shigeru Hanada

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro <itagaki.takahiro@gmail.com> wrote:
> On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
> <hanada@metrosystems.co.jp> wrote:
> >> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
> >> It might be good to have a separated "beginscan" method if we use
> >> asynchronous scans in multiple foreign servers in one query
> >
> > You mean that separated beginscan (FdwRoutine.BeginScan?) starts
> > asynchronous query and returns immediately, and FdwRoutine.Iterate
> > returns result of that query?
>
> Yes. Each BeginScan() in the executor node tree will be called at
> the beginning of executor's run. The callback should not block
> the caller. OTOH, Iterate() are called at the first time tuples
> in the node are required.

Please find attached WIP patch for BeginScan.  Postgresql_fdw has been
changed to use server-side cursor for sample.  It's DECLAREd with HOLD
option to avoid transaction management, though.

Other changes since 20101025 patch are:

  - Some document fixes.
  - Don't call ConnectServer from ExecInitForeignScan. Instead,
    postgresql_fdw calls it from pgOpen().  This change is only trial
    and would be removed later.
  - Add "schema" column to output of \det psql command.
  - New \dE psql command shows list of foreign tables in \d format.
  - \d+ <foreign table> psql command shows per-column options.

If the changes (at least adding BeginScan) are OK, I'll clean the
patch up and post it soon.

Regards,
--
Shigeru Hanada

Shigeru HANADA <hanada@metrosystems.co.jp> writes:
> How about removing them, ConnectServer and FreeFSConnection, from
> FdwRoutine and leaving the responsibility of resource management to
> each FDW?  Each FDW would have to use mechanism such as Virtual File
> and ResourceOwner to manage resources properly, though.

For the most part, we expect that ResourceOwners only do something
useful during error cleanup.  That is, you *should* have a
close-connection type of function that is expected to be called during
normal query shutdown.  The ResourceOwner hooks will operate to
compensate for the lack of this call in an error recovery case.
The reason for doing things that way is so that we can check for
unintentional resource leakage in the non-error code paths.
        regards, tom lane

On Mon, 08 Nov 2010 10:31:22 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Shigeru HANADA <hanada@metrosystems.co.jp> writes:
> > How about removing them, ConnectServer and FreeFSConnection, from
> > FdwRoutine and leaving the responsibility of resource management to
> > each FDW?  Each FDW would have to use mechanism such as Virtual File
> > and ResourceOwner to manage resources properly, though.
>
> For the most part, we expect that ResourceOwners only do something
> useful during error cleanup.  That is, you *should* have a
> close-connection type of function that is expected to be called during
> normal query shutdown.  The ResourceOwner hooks will operate to
> compensate for the lack of this call in an error recovery case.
> The reason for doing things that way is so that we can check for
> unintentional resource leakage in the non-error code paths.

I fixed postgresql_fdw to use RegisterResourceReleaseCallback() to
close all connections in error cases including user interrupt.  But
I'm not sure if I used the mechanism correctly because all I could
find about the API was only few documents, README of resowner and
function comments.  I tested the codes in cases below and confirmed
that all connections have been closed.

  - remote query error with wrong relation name
  - user interrupt, Ctrl+C on psql during long query

Of course, in non-error case, all connections are closed via normal
query shutdown path.

During fixing connection cleanup, I've removed connection pooling
from postgresql_fdw to make resource management simple.  Now
postgresql_fdw uses one connection for all of ForeignScan nodes in a
local query, but doesn't keep the connection alive beyond queries.
Originally, sharing connection is intended to execute multiple remote
query in a transaction for consistency.

I think external tools such as pgpool or pgbouncer would be better to
pool connections.  Is it reasonable?

Also, ExecInitForeignScan() was fixed to call ConnectServer() and
BeginScan() only when the EXEC_FLAG_EXPLAIN_ONLY is not set.

Regards,
--
Shigeru Hanada

Hi Gentlemen,<br /><br /> Thank you for the time estimate and the interface discussion. It sounds like the PostgreSQL
SQL/MEDcode will be very useful when it is done. Our product provides read-only access to files, so
updates/inserts/deletesaren't an issue for us.<br /><br /> One thing that is not clear to me is indexing support. Will
itbe possible to index a SQL/MED table as if it were a regular table? What would be the equivalent of Informix's row
ids?<br /><br /> Eric.<br /><br /><br /><p> ********************************************** <br /> Eric Davies, M.Sc.
<br/> Senior Programmer Analyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave., Suite 418<br />
VictoriaBC V8P 2L5<br /> Canada<br /><br /> Tel: (250) 704-4428<br /> Web: <a eudora="autourl"
href="http://www.barrodale.com/"><font color="#0000FF"><u>http://www.barrodale.com</u></font></a><br /> Email:
eric@barrodale.com<br /> **********************************************<br /><br /><br /> 

On Fri, 12 Nov 2010 08:27:54 -0800
Eric Davies <eric@barrodale.com> wrote:
> Thank you for the time estimate and the interface discussion. It 
> sounds like the PostgreSQL SQL/MED code will be very useful when it 
> is done. Our product provides read-only access to files, so 
> updates/inserts/deletes aren't an issue for us.
> 
> One thing that is not clear to me is indexing support. Will it be 
> possible to index a SQL/MED table as if it were a regular table?

No, SQL/MED would not support indexing foreign tables, at least in
first version.  Because it would be difficult to use common row id for
various FDWs.  To support indexing foreign tables might need to change
common structure of index tuple to be able to hold virtual row-id, not
ItemPointerData.

Instead, FDW can handle expressions which are parsed from WHERE clause
and JOIN condition of original SQL, and use them to optimize scanning. 
For example, FDW for PostgreSQL pushes some conditions down to remote
side to decrease result tuples to be transferred.  I hope this idea
helps you.

>                                                                  What 
> would be the equivalent of Informix's row ids?

Answer to the second question would be "ItemPointerData".  It consists
of a block number and an offset in the block, and consume 6 bytes for
each tuple.  With this information, PostgreSQL can access to a data
tuple directly.  Actual definition is:

typedef struct ItemPointerData
{   BlockIdData ip_blkid;   OffsetNumber ip_posid;
} ItemPointer;

Does Informix uses common row-id (AFAIK it's 4 bytes integer) for
both of virtual tables and normal tables?

Regards,
--
Shigeru Hanada

On Mon, Nov 15, 2010 at 12:41, Shigeru HANADA <hanada@metrosystems.co.jp> wrote:
> No, SQL/MED would not support indexing foreign tables, at least in
> first version.  Because it would be difficult to use common row id for
> various FDWs.

I think the reason is the SQL standard never mention about indexes.
It is not a specific issue for SQL/MED.

> To support indexing foreign tables might need to change
> common structure of index tuple to be able to hold virtual row-id, not
> ItemPointerData.

I'm not sure we actually need foreign indexes because the query text
sent to another server is same whether the foreign table has indexes.
Of course, foreign indexes might be useful to calculate costs to scan
foreign tables, but the cost also comes from non-index conditions.

I think foreign table and foreign index are a model for row-based
databases, including postgres. But other DBs might have different
cost models. So, it would be better to encapsulate such operations in FDW.

--
Itagaki Takahiro

With Informix VTI, indexing is the same for native tables as for virtual tables, except the interpretation of the 32
bitrowid is left up to the developer. When you define the VTI class, you optionally supply a method that can fetch data
basedon a 32 bit rowid, and it's the responsibility of your non-indexed scanning methods to provide rowids along with
therow tuple. <br /><br /> Having local indexes can be very useful if you have a user that issues queries like:<br />
  select count(*) from some_external_table where .... ;<br /> With VTI, the "count" aggregate doesn't get pushed down,
meaningthat without a local index, your scanning method has to return as many tuples as match the where clause, which
canbe very slow.<br /><br /> Local indexes also affords the opportunity of using specialized indexes built into the
database.My guess is that without some form of rowids being passed back and forth, you couldn't define non-materialized
viewsof virtual tables that could be indexed.<br /><br /> That said, we implemented our own btree-like index that used
thepushed down predicates because fetching data one row at a time wasn't desirable with our design choices, and we
wantedto support virtual tables with more than 4 billion rows.<br /><br /> Eric<br /> At 07:41 PM 11/14/2010, Shigeru
HANADAwrote:<br /><blockquote cite="" class="cite" type="cite">On Fri, 12 Nov 2010 08:27:54 -0800<br /> Eric Davies
<eric@barrodale.com>wrote:<br /> > Thank you for the time estimate and the interface discussion. It <br />
>sounds like the PostgreSQL SQL/MED code will be very useful when it <br /> > is done. Our product provides
read-onlyaccess to files, so <br /> > updates/inserts/deletes aren't an issue for us.<br /> > <br /> > One
thingthat is not clear to me is indexing support. Will it be <br /> > possible to index a SQL/MED table as if it
werea regular table?<br /><br /> No, SQL/MED would not support indexing foreign tables, at least in<br /> first
version. Because it would be difficult to use common row id for<br /> various FDWs.  To support indexing foreign tables
mightneed to change<br /> common structure of index tuple to be able to hold virtual row-id, not<br />
ItemPointerData.<br/><br /> Instead, FDW can handle expressions which are parsed from WHERE clause<br /> and JOIN
conditionof original SQL, and use them to optimize scanning. <br /> For example, FDW for PostgreSQL pushes some
conditionsdown to remote<br /> side to decrease result tuples to be transferred.  I hope this idea<br /> helps you.<br
/><br/> >                                                                  What <br /> > would be the equivalent
ofInformix's row ids?<br /><br /> Answer to the second question would be "ItemPointerData".  It consists<br /> of a
blocknumber and an offset in the block, and consume 6 bytes for<br /> each tuple.  With this information, PostgreSQL
canaccess to a data<br /> tuple directly.  Actual definition is:<br /><br /> typedef struct ItemPointerData<br /> {<br
/>    BlockIdData ip_blkid;<br />     OffsetNumber ip_posid;<br /> } ItemPointer;<br /><br /> Does Informix uses common
row-id(AFAIK it's 4 bytes integer) for<br /> both of virtual tables and normal tables?<br /><br /> Regards,<br /> --<br
/>Shigeru Hanada</blockquote><p> ********************************************** <br /> Eric Davies, M.Sc. <br /> Senior
ProgrammerAnalyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave., Suite 418<br /> Victoria BC V8P
2L5<br/> Canada<br /><br /> Tel: (250) 704-4428<br /> Web: <a eudora="autourl" href="http://www.barrodale.com/"> <font
color="#0000FF"><u>http://www.barrodale.com</u></font></a><br/> Email: eric@barrodale.com <br />
**********************************************<br/><br /><br /> 

Thanks for the information about Informix VTI.  Because I'm not
familiar to Informix, I might have missed your point.  Would you mind
telling me more about Informix VTI?

On Mon, 15 Nov 2010 08:45:14 -0800
Eric Davies <eric@barrodale.com> wrote:
> With Informix VTI, indexing is the same for native tables as for 
> virtual tables, except the interpretation of the 32 bit rowid is left 
> up to the developer. When you define the VTI class, you optionally 
> supply a method that can fetch data based on a 32 bit rowid, and it's 
> the responsibility of your non-indexed scanning methods to provide 
> rowids along with the row tuple.

ISTM that index on a VTI table could be inconsistent when original
(remote) data was changed in the way other than VTI.  Is it assumed
that the data source is never updated without VTI interface?

> Having local indexes can be very useful if you have a user that 
> issues queries like:
>     select count(*) from some_external_table where .... ;
> With VTI, the "count" aggregate doesn't get pushed down, meaning that 
> without a local index, your scanning method has to return as many 
> tuples as match the where clause, which can be very slow.

How can Informix server optimize such kind of query?  Counts the index
tuple which match the WHERE clause?  If so, such optimization seems to
be limited to "count" and wouldn't be able to be useful for "max" or
"sum".  Or, specialized index or VTI class is responsible to the
optimization?

> Local indexes also affords the opportunity of using specialized 
> indexes built into the database. My guess is that without some form 
> of rowids being passed back and forth, you couldn't define 
> non-materialized views of virtual tables that could be indexed.
> 
> That said, we implemented our own btree-like index that used the 
> pushed down predicates because fetching data one row at a time wasn't 
> desirable with our design choices, and we wanted to support virtual 
> tables with more than 4 billion rows.

I couldn't see the way to handle virtual table with more than 4
billion rows with 32 bit rowids in local index.  Do you mean that your
"btree-like index" searches result rows by predicates directly and
skips getbyid()?

Regards,
--
Shigeru Hanada

At 01:36 AM 11/16/2010, Shigeru HANADA wrote:<br /><blockquote cite="" class="cite" type="cite">Thanks for the
informationabout Informix VTI.  Because I'm not<br /> familiar to Informix, I might have missed your point.  Would you
mind<br/> telling me more about Informix VTI?</blockquote><br /><br /><blockquote cite="" class="cite" type="cite">On
Mon,15 Nov 2010 08:45:14 -0800<br /> Eric Davies <eric@barrodale.com> wrote:<br /> > With Informix VTI,
indexingis the same for native tables as for <br /> > virtual tables, except the interpretation of the 32 bit rowid
isleft <br /> > up to the developer. When you define the VTI class, you optionally <br /> > supply a method that
canfetch data based on a 32 bit rowid, and it's <br /> > the responsibility of your non-indexed scanning methods to
provide<br /> > rowids along with the row tuple.<br /><br /> ISTM that index on a VTI table could be inconsistent
whenoriginal<br /> (remote) data was changed in the way other than VTI.  Is it assumed<br /> that the data source is
neverupdated without VTI interface?</blockquote><br /> Yes, the data sources are assumed to updated only through the
VTIinterface.<br /> With our UFI product, the data sources are assumed to be unchanging files, you'd need to re-index
themif they changed.<br /><br /><br /><blockquote cite="" class="cite" type="cite">> Having local indexes can be
veryuseful if you have a user that <br /> > issues queries like:<br /> >     select count(*) from
some_external_tablewhere .... ;<br /> > With VTI, the "count" aggregate doesn't get pushed down, meaning that <br />
>without a local index, your scanning method has to return as many <br /> > tuples as match the where clause,
whichcan be very slow.<br /><br /> How can Informix server optimize such kind of query?  Counts the index<br /> tuple
whichmatch the WHERE clause? </blockquote><br /> That would be my assumption.<br /><br /><br /><blockquote cite=""
class="cite"type="cite"> If so, such optimization seems to<br /> be limited to "count" and wouldn't be able to be
usefulfor "max" or<br /> "sum".  Or, specialized index or VTI class is responsible to the<br />
optimization?</blockquote><br/> If there is an index on the column you want to sum/min/max, and your where clause
restrictsthe query to a particular set of rows based on that index, Informix can get the values for that column from
theindex (which it needed to scan anyhow) without looking at the table. This isn't particular to VTI, it's just a
cleveruse of indexes.<br /><br /> Here is a clipping from one of the Informix manuals on the topic: <dl><dd>The way
thatthe optimizer chooses to read a table is called an <i>access plan</i>. The simplest method to access a table is to
readit sequentially, which is called a <i>table scan</i>. The optimizer chooses a table scan when most of the table
mustbe read or the table does not have an index that is useful for the query.<a name="idx2646"></a><dd><a
name="idx2647"></a>Theoptimizer can also choose to access the table by an index. If the column in the index is the same
asa column in a filter of the query, the optimizer can use the index to retrieve only the rows that the query requires.
Theoptimizer can use a <i>key-only index scan</i><a name="idx2647"></a> if the columns requested are within one index
onthe table. The database server retrieves the needed data from the index and does not access the associated table.
<dd><aname="wq276"></a>Important: <dd><a name="idx2649"></a>The optimizer does not choose a key-only scan for a VARCHAR
column.If you want to take advantage of key-only scans, use the ALTER TABLE with the MODFIY<a name="idx2649"></a>
clauseto change the column to a CHAR data type.<a name="idx2655"></a><dd>The optimizer compares the cost of each plan
todetermine the best one. The database server derives cost from estimates of the number of I/O operations required,
calculationsto produce the results, rows accessed, sorting, and so forth.<br /><br /><br /><blockquote cite=""
class="cite"type="cite"></blockquote></dl>> Local indexes also affords the opportunity of using specialized <br />
>indexes built into the database. My guess is that without some form <br /> > of rowids being passed back and
forth,you couldn't define <br /> > non-materialized views of virtual tables that could be indexed.<br /> > <br />
>That said, we implemented our own btree-like index that used the <br /> > pushed down predicates because
fetchingdata one row at a time wasn't <br /> > desirable with our design choices, and we wanted to support virtual
<br/> > tables with more than 4 billion rows.<br /><br /> I couldn't see the way to handle virtual table with more
than4<br /> billion rows with 32 bit rowids in local index.  Do you mean that your<br /> "btree-like index" searches
resultrows by predicates directly and<br /> skips getbyid()?<br /> Exactly. Our own "rowids" can be up to 64 bits but
arenever seen by Informix. As far as Informix is concerned, it's a regular table scan because the use of our indexes is
hidden.<br/><br /><br /><blockquote cite="" class="cite" type="cite">Regards,<br /> --<br /> Shigeru
Hanada</blockquote><br/><br /> Cheers,<br /> Eric.<br /><br /><p> ********************************************** <br />
EricDavies, M.Sc. <br /> Senior Programmer Analyst<br /> Barrodale Computing Services Ltd. <br /> 1095 McKenzie Ave.,
Suite418<br /> Victoria BC V8P 2L5<br /> Canada<br /><br /> Tel: (250) 704-4428<br /> Web: <a eudora="autourl"
href="http://www.barrodale.com/"><font color="#0000FF"><u>http://www.barrodale.com</u></font></a><br /> Email:
eric@barrodale.com<br /> **********************************************<br /><br /><br /> 

Thanks for the additional information!

On Tue, 16 Nov 2010 09:31:43 -0800
Eric Davies <eric@barrodale.com> wrote:
> At 01:36 AM 11/16/2010, Shigeru HANADA wrote:
> >On Mon, 15 Nov 2010 08:45:14 -0800
> >Eric Davies <eric@barrodale.com> wrote:
> >ISTM that index on a VTI table could be inconsistent when original
> >(remote) data was changed in the way other than VTI.  Is it assumed
> >that the data source is never updated without VTI interface?
> 
> Yes, the data sources are assumed to updated only through the VTI interface.
> With our UFI product, the data sources are assumed to be unchanging 
> files, you'd need to re-index them if they changed.

ISTM that it would be difficult for SQL/MED to assume that external
data is unchangeable.  Also I'm not sure that index types which
PostgreSQL has currently are useful for external data.  So I think
that indexes for external data would be better to be encapsulated into
FDWs.

> >  If so, such optimization seems to
> >be limited to "count" and wouldn't be able to be useful for "max" or
> >"sum".  Or, specialized index or VTI class is responsible to the
> >optimization?
> 
> If there is an index on the column you want to sum/min/max, and your 
> where clause restricts the query to a particular set of rows based on 
> that index, Informix can get the values for that column from the 
> index (which it needed to scan anyhow) without looking at the table. 
> This isn't particular to VTI, it's just a clever use of indexes.
<snip>

The optimization in Informix is specific to "key-only scan" but not to
VTI, isn't it?  Then, it would be better to implement such kind of
optimization independently (separated from SQL/MED).  I found "Index
Only Scan" thread in pgsql-hackers, but I don't know its conclusion...

> >I couldn't see the way to handle virtual table with more than 4
> >billion rows with 32 bit rowids in local index.  Do you mean that your
> >"btree-like index" searches result rows by predicates directly and
> >skips getbyid()?
> 
> Exactly. Our own "rowids" can be up to 64 bits but are never seen by 
> Informix. As far as Informix is concerned, it's a regular table scan 
> because the use of our indexes is hidden.

The design of pushing down predicates which you've taken might be
similar to the one in the FDW for PostgreSQL.  FDW for PostgreSQL
generates WHERE clause from predicates and restrict the tuples to be
returned.  I think that you can port your VTI class to SQL/MED as a
FDW for indexed-files, without local index.

Regards,
--
Shigeru Hanada

Some random comments on the patch:

ReleaseConnection is a very generic name for a global function, would be 
good to prefix it with "pgsqlfdw" or something. Same with any other 
globally visible functions.

Please use the built-in contain_mutable_functions(Node *) instead of 
custom is_immutable_func(). Or at least func_volatile(Oid)

Is it really a good idea to allow LOCK TABLE on foreign tables in its 
current form? It only locks the local foreign table object, not the 
table in the remote server.

Sorry if this was fiercely discussed already, but I don't think the file 
FDW belongs in core. I'd rather see it as a contrib module

I would've expected the contrib install script to create the foreign 
data wrapper for me. While you can specify options to a foreign data 
wrapper, the CREATE FOREIGN DATA WRAPPER seems similar to CREATE 
LANGUAGE, ie. something that happens when the foreign data wrapper 
library is installed.

How do you specify a foreign table that has a different name in the 
remote server? For example, if I wanted to create a foreign table called 
"foo", that fetched rows from a remote table called "bar"?

I would really like to see the SQL query that's shipped to the remote 
host in EXPLAIN. That's essential information for analyzing a query that 
involves a foreign table.

What about transactions? Does the SQL/MED standard have something to say 
about that?


In general, I'm surprised that there's no hook at all into the planning 
phase. You have this TODO comment postgresql_fdw:

>         /*
>          * TODO: omit (deparse to "NULL") columns which are not used in the
>          * original SQL.
>          *
>          * We must parse nodes parents of this ForeignScan node to determine unused
>          * columns because some columns may be used only in parent Sort/Agg/Limit
>          * nodes.
>          */

Parsing the parents of the ForeignScan node seems like a backwards way 
of solving the problem. The planner should tell the FDW what columns it 
needs. And there should be some way for the FDW to tell the planner 
which quals it can handle, so that the executor doesn't need to recheck 
them.

You could make the planner interface infinitely complicated, but that's 
no excuse for doing nothing at all. The interface needs some thought...

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

Thanks for the comments.

Attached patch includes fixes your comments marked (*), and other
small fixes such as oid system column support by postgresql_fdw and
file_fdw.

On Fri, 19 Nov 2010 12:16:00 +0200
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> ReleaseConnection is a very generic name for a global function, would be
> good to prefix it with "pgsqlfdw" or something. Same with any other
> globally visible functions.

(*)Agreed, merged two files and make all functions other than
postgresql_fdw_handler() private.  Refactored name of public functions
defined for file_fdw to have common prefix "FileState".

> Please use the built-in contain_mutable_functions(Node *) instead of
> custom is_immutable_func(). Or at least func_volatile(Oid)

(*)I didn't know the function, thanks.  Replaced custom
is_immutable_func() with contain_mutable_functions().

> Is it really a good idea to allow LOCK TABLE on foreign tables in its
> current form? It only locks the local foreign table object, not the
> table in the remote server.

The first reason to allow LOCK TABLE is to make pg_dump be able to
export definition of foreign tables, and second is allow to lock
normal table which has been inherited by foreign table(s).  It would
be able to allow FDWs to delegate lock requestto remote server with
new hook in LockTableRecurse() or somewhere, but IMHO locking remote
would be overkill because SQL/MED doesn't mention about lock.

> Sorry if this was fiercely discussed already, but I don't think the file
> FDW belongs in core. I'd rather see it as a contrib module

It's in core from some passive reasons:

  - The file_fdw shared codes with COPY FROM heavily in first proposal.
  - Built-in FDW makes creating regression tests easier.  Especially
    CREATE FOREIGN TABLE DDL requires FDW with valid handler.

Moving to contrib would need adding "dummy FDW" or something which has
valid handler to keep regression tests about DDL in core...

> I would've expected the contrib install script to create the foreign
> data wrapper for me. While you can specify options to a foreign data
> wrapper, the CREATE FOREIGN DATA WRAPPER seems similar to CREATE
> LANGUAGE, ie. something that happens when the foreign data wrapper
> library is installed.

(*)It seems to have been deleted by mistake, fixed.

> How do you specify a foreign table that has a different name in the
> remote server? For example, if I wanted to create a foreign table called
> "foo", that fetched rows from a remote table called "bar"?

You can specify name of schema, table and column with generic option
of postgresql_fdw objects.

 object | option name |        context
--------+-------------+-------------------------
 schema | nspname     | foreign table
 table  | relname     | foreign table
 column | colname     | column of foreign table

> I would really like to see the SQL query that's shipped to the remote
> host in EXPLAIN. That's essential information for analyzing a query that
> involves a foreign table.

Me too :-)
You can see the SQL if you set client_min_messages to debug1 or lower,
but it's just debug message.

New hook in ExplainNode, or new attribute of ForeignScan, would be
necessary to show FWD-specific information in EXPLAIN result.  ISTM
this issue should be considered with the following issue about
planner-hook because such information should be generated in planner
phase.

> What about transactions? Does the SQL/MED standard have something to say
> about that?

SQL/MED says nothing about transaction management.  It's perhaps
because SQL/MED allows only read access.

> In general, I'm surprised that there's no hook at all into the planning
> phase. You have this TODO comment postgresql_fdw:
>
> >         /*
> >          * TODO: omit (deparse to "NULL") columns which are not used in the
> >          * original SQL.
> >          *
> >          * We must parse nodes parents of this ForeignScan node to determine unused
> >          * columns because some columns may be used only in parent Sort/Agg/Limit
> >          * nodes.
> >          */
>
> Parsing the parents of the ForeignScan node seems like a backwards way
> of solving the problem. The planner should tell the FDW what columns it
> needs. And there should be some way for the FDW to tell the planner
> which quals it can handle, so that the executor doesn't need to recheck
> them.
>
> You could make the planner interface infinitely complicated, but that's
> no excuse for doing nothing at all. The interface needs some thought...

Optimization about column would be minor issue, rather might be
removable.  I'll research about foreign path/plan creation again to
figure the issues about planning out.

Regards,
--
Shigeru Hanada

On Fri, Nov 19, 2010 at 9:55 AM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
> [ new SQL/MED patch ]

I can't help noticing that this patch adds 8,982 lines and removes
408, making it far larger any other patch I've ever seen on this list.And what that means is that committing all of
thisin one go is going
 
to be very, very difficult.  Now, on the plus side, as 9000+ line
patches go, this one looks pretty well-written, at least after ten
seconds of looking at it, which is great as far as it goes, but the
sheer size is still going to make it just about impossible for anyone
to review it effectively and have real confidence that the whole thing
is commit-quality.  To have a chance of getting a significant portion
of this into PostgreSQL 9.1, it really needs to be broken up into
INDEPENDENTLY COMMITTABLE SUB-PATCHES.

The key words here are "independently committable".  Breaking up a
patch into sub-patches by directory, for example, is completely
useless - we're not, for example, going to commit the code first and
the docs separately.  Let me say that again - the ONLY useful way of
breaking up a patch is to divide it into pieces such that EACH piece,
by itself, would represent a credible commit.  Each piece should be
posted to a separate thread and a separate discussion should be had
about the merits and demerits of each one.  Each should have a
separate CommitFest entry and, ideally, a separate reviewer.  Of
course, it may not be possible to fully evaluate a given patch without
looking at the other ones, but the extent to which this is necessary
should be minimized; otherwise you haven't really broken it up
usefully.

Ultimately, we probably want and need to get this patch down to chunks
of less than 2000 lines each.  But for starters, it looks quite simple
to break this into three chunks: one for the PostgreSQL FDW, one for
the CSV FDW, and one for the core functionality.  I think that the CSV
FDW, like the PG FDW, should be a loadable module.  (I wonder if it
would be more sensible to name all the FDWs as "fdw_foo" rather than
"foo_fdw", so that they alphabetize together, but I believe that Tom
has shot down similar suggestions in the past, so maybe it's not a
good idea after all.)  So let's do that and then see if we can find
anything that we can either simplify (so it takes fewer lines of code)
or pull out and commit separately (because, for example, it's some
kind of refactoring that is a good idea independently of this patch).

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

On Mon, Nov 22, 2010 at 11:16, Robert Haas <robertmhaas@gmail.com> wrote:
> To have a chance of getting a significant portion
> of this into PostgreSQL 9.1, it really needs to be broken up into
> INDEPENDENTLY COMMITTABLE SUB-PATCHES.

Did we discuss about syntax-only patch is not acceptable because
it makes the head broken state at the previous commit-fest?
I think that's why the patch becomes so large.

So, our guideline to submit a large patch would be: * Split patch into commitable sub-patches (2000 lines each), * But
submita series of patches at once.
 

Am I understanding correctly?

-- 
Itagaki Takahiro

On Sun, Nov 21, 2010 at 10:14 PM, Itagaki Takahiro
<itagaki.takahiro@gmail.com> wrote:
> On Mon, Nov 22, 2010 at 11:16, Robert Haas <robertmhaas@gmail.com> wrote:
>> To have a chance of getting a significant portion
>> of this into PostgreSQL 9.1, it really needs to be broken up into
>> INDEPENDENTLY COMMITTABLE SUB-PATCHES.
>
> Did we discuss about syntax-only patch is not acceptable because
> it makes the head broken state at the previous commit-fest?
> I think that's why the patch becomes so large.

Right, I remember that discussion.  Hopefully the distinction between
that conversation and this one is clear.

> So, our guideline to submit a large patch would be:
>  * Split patch into commitable sub-patches (2000 lines each),

It's not a hard number - it's more important that the patch *make
sense* than what the exact line count is.  But I think that's a
reasonable guideline to shoot for.  Ideally, smaller still would
probably be even better, but sometimes it just can't be done.  Also,
note that pulling off small chunks is a valuable way to make progress.For example, if we notice that there's a 100-line
refactoringin the 
FDW patch that stands on its own, by all means let's pull it out and
commit it.

>  * But submit a series of patches at once.

When necessary, yes.  Of course, the best thing is if you can make
them truly independent and submit the one after another.  Get one
committed, move on to the next.  But if you can't, then you can't.  In
this case, there's not much help for the fact that to decide whether
the FDW patch is a good idea you're probably going to at least want to
glance at the PGFDW and CSVFDW patches -- but it's possible we could
decide to commit the core support first, and then work on getting the
implementations committed afterwards, if we're confident that the
basic design is all right but more work is needed down in the details.

> Am I understanding correctly?

I think so.

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

On Sun, 21 Nov 2010 21:16:05 -0500
Robert Haas <robertmhaas@gmail.com> wrote:
<snip>
> Ultimately, we probably want and need to get this patch down to chunks
> of less than 2000 lines each.  But for starters, it looks quite simple
> to break this into three chunks: one for the PostgreSQL FDW, one for
> the CSV FDW, and one for the core functionality.  I think that the CSV
> FDW, like the PG FDW, should be a loadable module.

I've separated the patch into tree parts.  They have codes, documents
and tests within, and file_fdw and pgsql_fdw can be applied onto
fdw_core for each, or together.  I hope the separation helps the
review of the patches.  Contents of each patch are:

  fdw_core : DDL for FDW HANDLER and FOREIGN TABLE, ForeignScan
  file_fdw : FDW for file, as contrib (loadable) module
  pgsql_fdw: FDW for PG, as contrib module

The fdw_core patch is still very large (it has 10336 lines), but about
the half of the patch is unchanged lines.  The brief of lines actually
changed are:

    backend codes     : 3000
    regression tests  :  500
    sgml documents    : 1500

Basic functionality is not changed, but when I separate file_fdw from
core, I relaxed the requirement that FDW must have HANDLER to create
FOREIGN TABLE.  Now FDW is required to have valid Iterate function
only when scanning (or EXPLAINing) the foreign table actually.  It
enables testing DDLs for FOREIGN TABLE with HANDLER-less-FDW.

>                                                     (I wonder if it
> would be more sensible to name all the FDWs as "fdw_foo" rather than
> "foo_fdw", so that they alphabetize together, but I believe that Tom
> has shot down similar suggestions in the past, so maybe it's not a
> good idea after all.)

I agree the naming you suggested, but it would need to change the name
of existing function postgresql_fdw_validator to keep consistency.   I
couldn't find any discussion about the naming of fdw.

Regards,
--
Shigeru Hanada

On 23.11.2010 12:09, Shigeru HANADA wrote:
> On Sun, 21 Nov 2010 21:16:05 -0500
> Robert Haas<robertmhaas@gmail.com>  wrote:
> <snip>
>> Ultimately, we probably want and need to get this patch down to chunks
>> of less than 2000 lines each.  But for starters, it looks quite simple
>> to break this into three chunks: one for the PostgreSQL FDW, one for
>> the CSV FDW, and one for the core functionality.  I think that the CSV
>> FDW, like the PG FDW, should be a loadable module.
>
> I've separated the patch into tree parts.  They have codes, documents
> and tests within, and file_fdw and pgsql_fdw can be applied onto
> fdw_core for each, or together.  I hope the separation helps the
> review of the patches.

The docs need some work. The CREATE FOREIGN TABLE reference page seems 
to be copy-pasted from CREATE TABLE, because it talks about constraints 
and WITH/WITHOUT OIDS which surely don't apply to foreign tables.

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

On Tue, 23 Nov 2010 12:30:52 +0200
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> The docs need some work. The CREATE FOREIGN TABLE reference page seems 
> to be copy-pasted from CREATE TABLE, because it talks about constraints 
> and WITH/WITHOUT OIDS which surely don't apply to foreign tables.

Thanks for the comments.

The page you pointed has been edited for foreign table.  In current
design, OID system column and CHECK constraints are supported.

OID is supported to get oid from the source table (yes, it works only
for postgresql_fdw but it seems useful to support).  CHECK constraint
is supported to enable constraint exclusion.  In addition, TABLEOID is
supported to show which table is the actual source.

I agree that some kind of documents, such as "How to create new FDW",
should be written.

Regards,
--
Shigeru Hanada

On 23.11.2010 14:22, Shigeru HANADA wrote:
> On Tue, 23 Nov 2010 12:30:52 +0200
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  wrote:
>> The docs need some work. The CREATE FOREIGN TABLE reference page seems
>> to be copy-pasted from CREATE TABLE, because it talks about constraints
>> and WITH/WITHOUT OIDS which surely don't apply to foreign tables.
>
> Thanks for the comments.
>
> The page you pointed has been edited for foreign table.  In current
> design, OID system column and CHECK constraints are supported.

Oh, ok.

> OID is supported to get oid from the source table (yes, it works only
> for postgresql_fdw but it seems useful to support).

I don't think that's worthwhile. Oids on user tables is a legacy 
feature, not recommended for new applications. And if you have to access 
an existing table that uses oids, you can define a regular column for 
the oid:

CREATE FOREIGN TABLE foreigntable (oid oid, data int4) SERVER myserver;

>  CHECK constraint
> is supported to enable constraint exclusion.

Hmm, my gut reaction is that that's a premature optimization. But what 
about DEFAULTs then, surely that doesn't make sense for a foreign table?

> I agree that some kind of documents, such as "How to create new FDW",
> should be written.

A well-documented file FDW implementation goes a long way for that. But 
a chapter that explains SQL/MED, how to create foreign tables, servers, 
user mappings etc, and how they behave. That we need.

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

On Tue, 2010-11-23 at 20:18 +0200, Heikki Linnakangas wrote:
> On 23.11.2010 14:22, Shigeru HANADA wrote:

> > OID is supported to get oid from the source table (yes, it works only
> > for postgresql_fdw but it seems useful to support).
> 
> I don't think that's worthwhile. Oids on user tables is a legacy 
> feature, not recommended for new applications.

Agreed. We should do everything we can to NOT encourage their use.

JD
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Tue, Nov 23, 2010 at 5:09 AM, Shigeru HANADA
<hanada@metrosystems.co.jp> wrote:
> I've separated the patch into tree parts.  They have codes, documents
> and tests within, and file_fdw and pgsql_fdw can be applied onto
> fdw_core for each, or together.  I hope the separation helps the
> review of the patches.  Contents of each patch are:
>
>  fdw_core : DDL for FDW HANDLER and FOREIGN TABLE, ForeignScan
>  file_fdw : FDW for file, as contrib (loadable) module
>  pgsql_fdw: FDW for PG, as contrib module

I think it would be useful to repost each of these on their own thread
and adjust the CF app to have an entry referring to each thread.  That
way we can keep discussion and review for each one separate from the
others.  Note that this thread is already totally off-topic anyway -
it started as a question about when SQL/MED would land and then it got
patches posted to it.

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

On Tue, 23 Nov 2010 10:22:02 -0800
"Joshua D. Drake" <jd@commandprompt.com> wrote:
> On Tue, 2010-11-23 at 20:18 +0200, Heikki Linnakangas wrote:
> > On 23.11.2010 14:22, Shigeru HANADA wrote:
> 
> > > OID is supported to get oid from the source table (yes, it works only
> > > for postgresql_fdw but it seems useful to support).
> > 
> > I don't think that's worthwhile. Oids on user tables is a legacy 
> > feature, not recommended for new applications.
> 
> Agreed. We should do everything we can to NOT encourage their use.

Agreed.  I'll remove OIDs support and repost the patch in new thread.

Regards,
--
Shigeru Hanada