Re: [HACKERS] SQL/MED estimated time of arrival?
On Tue, 23 Nov 2010 10:22:02 -0800 Joshua D. Drake j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On 23.11.2010 12:09, Shigeru HANADA wrote: On Sun, 21 Nov 2010 21:16:05 -0500 Robert Haasrobertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Tue, 23 Nov 2010 12:30:52 +0200 Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On 23.11.2010 14:22, Shigeru HANADA wrote: On Tue, 23 Nov 2010 12:30:52 +0200 Heikki Linnakangasheikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Tue, Nov 23, 2010 at 5:09 AM, Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Fri, Nov 19, 2010 at 9:55 AM, Shigeru HANADA han...@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 this in 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Mon, Nov 22, 2010 at 11:16, Robert Haas robertmh...@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 submit a series of patches at once. Am I understanding correctly? -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Sun, Nov 21, 2010 at 10:14 PM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Mon, Nov 22, 2010 at 11:16, Robert Haas robertmh...@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 refactoring in 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
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 e...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
At 01:36 AM 11/16/2010, Shigeru HANADA wrote: 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 e...@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? 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. 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? That would be my assumption. 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. Here is a clipping from one of the Informix manuals on the topic: The way that the optimizer chooses to read a table is called an access plan. The simplest method to access a table is to read it sequentially, which is called a table scan. The optimizer chooses a table scan when most of the table must be read or the table does not have an index that is useful for the query. The optimizer can also choose to access the table by an index. If the column in the index is the same as a column in a filter of the query, the optimizer can use the index to retrieve only the rows that the query requires. The optimizer can use a key-only index scan if the columns requested are within one index on the table. The database server retrieves the needed data from the index and does not access the associated table. Important: 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 clause to change the column to a CHAR data type. The optimizer compares the cost of each plan to determine the best one. The database server derives cost from estimates of the number of I/O operations required, calculations to produce the results, rows accessed, sorting, and so forth. 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()? 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. Regards, -- Shigeru Hanada Cheers, Eric. ** Eric Davies, M.Sc. Senior Programmer Analyst Barrodale Computing Services Ltd. 1095 McKenzie Ave., Suite 418 Victoria BC V8P 2L5 Canada Tel: (250) 704-4428 Web: http://www.barrodale.com Email: e...@barrodale.com **
Re: [HACKERS] SQL/MED estimated time of arrival?
Thanks for the additional information! On Tue, 16 Nov 2010 09:31:43 -0800 Eric Davies e...@barrodale.com wrote: At 01:36 AM 11/16/2010, Shigeru HANADA wrote: On Mon, 15 Nov 2010 08:45:14 -0800 Eric Davies e...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
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.
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.
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.
Eric
At 07:41 PM 11/14/2010, Shigeru HANADA wrote:
On Fri, 12 Nov 2010 08:27:54 -0800
Eric Davies e...@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
**
Eric Davies, M.Sc.
Senior Programmer Analyst
Barrodale Computing Services Ltd.
1095 McKenzie Ave., Suite 418
Victoria BC V8P 2L5
Canada
Tel: (250) 704-4428
Web: http://www.barrodale.com
Email: e...@barrodale.com
**
Re: [HACKERS] SQL/MED estimated time of arrival?
On Fri, 12 Nov 2010 08:27:54 -0800
Eric Davies e...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Mon, Nov 15, 2010 at 12:41, Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
Hi Gentlemen, 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? What would be the equivalent of Informix's row ids? Eric. ** Eric Davies, M.Sc. Senior Programmer Analyst Barrodale Computing Services Ltd. 1095 McKenzie Ave., Suite 418 Victoria BC V8P 2L5 Canada Tel: (250) 704-4428 Web: http://www.barrodale.com Email: e...@barrodale.com **
Re: [HACKERS] SQL/MED estimated time of arrival?
On Sat, 6 Nov 2010 16:04:37 +0900 Hitoshi Harada umi.tan...@gmail.com wrote: 2010/11/5 Shigeru HANADA han...@metrosystems.co.jp: On Fri, 5 Nov 2010 16:27:49 +0900 Itagaki Takahiro itagaki.takah...@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 (would be 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Fri, 05 Nov 2010 10:43:45 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
2010/11/5 Shigeru HANADA han...@metrosystems.co.jp: On Fri, 5 Nov 2010 16:27:49 +0900 Itagaki Takahiro itagaki.takah...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
Hitoshi Harada umi.tan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Thu, 4 Nov 2010 18:22:52 +0900 Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA han...@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_statsgather statistics (ANALYZE) am_checkverify data structure and index consistency am_open initialize access to a virtual table (might connect to external server) am_closefinalize access to a virtual table am_scancost estimate cost of a scan am_beginscaninitialize 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 a tuple 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Fri, 5 Nov 2010 16:27:49 +0900 Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA han...@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-tuple context 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 context and 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Wed, 03 Nov 2010 13:32:18 -0700 Eric Davies e...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED estimated time of arrival?
On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA han...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
