Thread: 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
Attachment
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
Attachment
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
Attachment
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
Attachment
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