Обсуждение: How to add a new build-in operator
Hi, I'm planning to implement a new type of scan,scan by TID. It's on TODO * Allow WHERE restriction on ctid. First,I want to define an equal operator between TID. Seems OID's 1700-1799 are reserved for numeric type. Can I use 1800 as its OID ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
> I'm planning to implement a new type of scan,scan by TID.
> It's on TODO * Allow WHERE restriction on ctid.
> First,I want to define an equal operator between TID.
> Seems OID's 1700-1799 are reserved for numeric type.
> Can I use 1800 as its OID ?
Certainly, or perhaps it would be better to recycle an OID from
farther down? We have some open values, and if you only need a few it
would work well.
You probably already know this, but just in case,
cd src/include/catalog
./unused_oids
will help you find some OIDs to use.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> I'm planning to implement a new type of scan,scan by TID. > It's on TODO * Allow WHERE restriction on ctid. > > First,I want to define an equal operator between TID. > Seems OID's 1700-1799 are reserved for numeric type. > Can I use 1800 as its OID ? > You can use any unused oid for your purposes. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > I'm planning to implement a new type of scan,scan by TID. > > It's on TODO * Allow WHERE restriction on ctid. > > First,I want to define an equal operator between TID. > > Certainly, or perhaps it would be better to recycle an OID from > farther down? We have some open values, and if you only need a few it > would work well. > > You probably already know this, but just in case, > > cd src/include/catalog > ./unused_oids > I didn't know it. Thanks. I would use OIDs for '=' operator between TIDs as follows.387 for = (tid, tid)1292 for tideq(tid, tid) Unfortunately,TIDs are changed by UPDATE operations. So we would need some functions in order to get the latest TID of a specified tuple such as currtid(relationid/name, tid) which returns tid. I would provide functions for both relid and relname and use 1293-1294 for OIDs of these functions. Comments ? If there's no objection,I would commit them to the current tree. Moreover,we would need to know TIDs of inserted tuples. What is a reasonable way to do so ? 1. Add TID to return_info of INSERT commands. 2. Provide a function to get TID of the last inserted tuple of the session(backend). ... Any ideas ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
[Charset iso-8859-1 unsupported, filtering to ASCII...] > > > I'm planning to implement a new type of scan,scan by TID. > > > It's on TODO * Allow WHERE restriction on ctid. > > > First,I want to define an equal operator between TID. > > > > Certainly, or perhaps it would be better to recycle an OID from > > farther down? We have some open values, and if you only need a few it > > would work well. > > > > You probably already know this, but just in case, > > > > cd src/include/catalog > > ./unused_oids > > > > I didn't know it. > Thanks. Oops, no mention of that in the developers FAQ. Let me do that now. > I would use OIDs for '=' operator between TIDs as follows. > 387 for = (tid, tid) > 1292 for tideq(tid, tid) > > > Unfortunately,TIDs are changed by UPDATE operations. > So we would need some functions in order to get the latest > TID of a specified tuple such as > currtid(relationid/name, tid) which returns tid. > I would provide functions for both relid and relname and > use 1293-1294 for OIDs of these functions. > > Comments ? > If there's no objection,I would commit them to the current tree. Sounds good. > > Moreover,we would need to know TIDs of inserted tuples. > What is a reasonable way to do so ? > 1. Add TID to return_info of INSERT commands. > 2. Provide a function to get TID of the last inserted tuple > of the session(backend). Either sounds good. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
>
> > > > I'm planning to implement a new type of scan,scan by TID.
> > > > It's on TODO * Allow WHERE restriction on ctid.
> > > > First,I want to define an equal operator between TID.
> > >
[snip]
>
> > I would use OIDs for '=' operator between TIDs as follows.
> > 387 for = (tid, tid)
> > 1292 for tideq(tid, tid)
> >
> >
> > Unfortunately,TIDs are changed by UPDATE operations.
> > So we would need some functions in order to get the latest
> > TID of a specified tuple such as
> > currtid(relationid/name, tid) which returns tid.
> > I would provide functions for both relid and relname and
> > use 1293-1294 for OIDs of these functions.
> >
> > Comments ?
> > If there's no objection,I would commit them to the current tree.
>
> Sounds good.
>
I have committed them to the current tree.
Needs initdb.
Now we could enjoy WHERE restriction on ctid as follows.
Unfortunately,the scan is still sequential.
=> create table t1 (dt text);
CREATE
=> insert into t1 values ('data inserted');
INSERT 45833 1
=> select ctid,* from t1;
ctid |dt
-----+----------
(0,1)|data inserted
(1 row)
=> select * from t1 where ctid='(0,1)';
dt
----------
data inserted
(1 row)
=> update t1 set dt='data updated';
UPDATE 1
=> select * from t1 where ctid='(0,1)';
dt
--
(0 rows)
=> select ctid,* from t1 where ctid=currtid2('t1', '(0,1)');
ctid |dt
-----+------------
(0,2)|data updated
(1 row)
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
> I have committed them to the current tree. > Needs initdb. TODO list updated. Seems the tid could be accessed directly, by checking the page list, and if it is valid, just going to the tid. I assume you are working on that issue, or do you need assistance? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:maillist@candle.pha.pa.us] > Sent: Monday, October 11, 1999 10:45 PM > To: Hiroshi Inoue > Cc: Thomas Lockhart; pgsql-hackers > Subject: Re: Scan by TID (was RE: [HACKERS] How to add a new > build-in operator) > > > > I have committed them to the current tree. > > Needs initdb. > > TODO list updated. Seems the tid could be accessed directly, by > checking the page list, and if it is valid, just going to the tid. I > assume you are working on that issue, or do you need assistance? > Yes,I have done a part of my story. I would add new type of path and scan by which we are able to access tids directly. I don't understand planner/executor stage well. So I'm happy if someone could check my story. Regards. Hiroshi Inoue Inoue@tpf.co.jp
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Yes,I have done a part of my story.
> I would add new type of path and scan by which we are able to access
> tids directly.
Yes, new path type, new plan type, probably a new access method if
you want to keep things clean in the executor, cost-estimation routines
in the planner, etc. etc.
Looks like a lot of work, and a lot of added code bulk that will
have to be maintained. I haven't figured out why you think it's
worth it... tids are so transient that I don't see much need for
finding tuples by them...
regards, tom lane
> > TODO list updated. Seems the tid could be accessed directly, by > > checking the page list, and if it is valid, just going to the tid. I > > assume you are working on that issue, or do you need assistance? > > > > Yes,I have done a part of my story. > I would add new type of path and scan by which we are able to access > tids directly. > > I don't understand planner/executor stage well. > So I'm happy if someone could check my story. My guess is that you are going to have to hard-code something into the backend to handle non-scan lookup of this type specially. Normally, either there is an index lookup, or a sequential scan. In your case, you "know" the actual location of the row, or at least a request for a possible valid location. You could create a fake index that really doesn't exist, but returns a tid that exactly matches the requested tid, or you could have the code check for a specific TID type, and heap_fetch the desired row directly, rather than performing a sequential scan. See the developers FAQ on how to do a heap_fetch with a tid. You can also use <I>heap_fetch()</I> to fetch rows by blocknumber/offset. The block number/offset is the tid. Of course, you have to make sure the tid is valid. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Yes,I have done a part of my story. > > I would add new type of path and scan by which we are able to access > > tids directly. > > Yes, new path type, new plan type, probably a new access method if > you want to keep things clean in the executor, cost-estimation routines > in the planner, etc. etc. > > Looks like a lot of work, and a lot of added code bulk that will > have to be maintained. I haven't figured out why you think it's > worth it... tids are so transient that I don't see much need for > finding tuples by them... That's why I just suggested a more short-circuited option of snatching tid oids from expressions, and doing a heap_fetch directly at that point to avoid the index scan. Seems it could be done in just one file. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Yes,I have done a part of my story. > > I would add new type of path and scan by which we are able to access > > tids directly. > > Yes, new path type, new plan type, probably a new access method if > you want to keep things clean in the executor, cost-estimation routines > in the planner, etc. etc. > > Looks like a lot of work, and a lot of added code bulk that will > have to be maintained. I haven't figured out why you think it's > worth it... tids are so transient that I don't see much need for > finding tuples by them... Ingres has access by tid, and it does come in handy for quick-and-dirty uses where you just want to snag a bunch of rows and operate on them without too much fuss. I believe if we had it, some things internally may prove to be easier with them accessible in a query. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Tuesday, October 12, 1999 4:08 AM > To: Hiroshi Inoue > Cc: Bruce Momjian; pgsql-hackers > Subject: Re: Scan by TID (was RE: [HACKERS] How to add a new build-in > operator) > > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > Yes,I have done a part of my story. > > I would add new type of path and scan by which we are able to access > > tids directly. > > Yes, new path type, new plan type, probably a new access method if > you want to keep things clean in the executor, cost-estimation routines > in the planner, etc. etc. > > Looks like a lot of work, and a lot of added code bulk that will > have to be maintained. You are right and It's the reason I have announced this issue many times. > I haven't figured out why you think it's > worth it... tids are so transient that I don't see much need for > finding tuples by them... > As far as I know,many DBMSs have means to access tuples directly. I have been wondering why PostgreSQL doesn't support such means. PostgreSQL isn't perfect and this kind of means are necessary to make up for the deficiency,I think. For example how do we implement advanced features of ODBC /JDBC drivers etc without this kind of support ? OIDs are preferable for such means because they are invariant. But unfortunately OIDs have no guarantee that have indexes and even with indexes it isn't the fastest way. TIDs are transient,so I have provided built-in functions currtid() to follow update chain of links. Regards. Hiroshi Inoue Inoue@tpf.co.jp
> -----Original Message----- > From: Bruce Momjian [mailto:maillist@candle.pha.pa.us] > Sent: Tuesday, October 12, 1999 4:14 AM > To: Tom Lane > Cc: Hiroshi Inoue; pgsql-hackers > Subject: Re: Scan by TID (was RE: [HACKERS] How to add a new build-in > operator) > > > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > > Yes,I have done a part of my story. > > > I would add new type of path and scan by which we are able to access > > > tids directly. > > > > Yes, new path type, new plan type, probably a new access method if > > you want to keep things clean in the executor, cost-estimation routines > > in the planner, etc. etc. > > > > Looks like a lot of work, and a lot of added code bulk that will > > have to be maintained. I haven't figured out why you think it's > > worth it... tids are so transient that I don't see much need for > > finding tuples by them... > > That's why I just suggested a more short-circuited option of snatching > tid oids from expressions, and doing a heap_fetch directly at that point > to avoid the index scan. Seems it could be done in just one file. > I have thought the way as Tom says and I have a prospect to do it. But it would take a lot of work. Where to snatch and return to(or exit from) planner/executor in your story ? Hiroshi Inoue Inoue@tpf.co.jp.
> > > Looks like a lot of work, and a lot of added code bulk that will > > > have to be maintained. I haven't figured out why you think it's > > > worth it... tids are so transient that I don't see much need for > > > finding tuples by them... > > > > That's why I just suggested a more short-circuited option of snatching > > tid oids from expressions, and doing a heap_fetch directly at that point > > to avoid the index scan. Seems it could be done in just one file. > > > > I have thought the way as Tom says and I have a prospect to do it. > But it would take a lot of work. > > Where to snatch and return to(or exit from) planner/executor > in your story ? Basically, if I remember, in the executor, access to a table either opens the table for sequential scan, does an index scan, or has the value it needs already in a result of a previous join result. If we put something in the executor so when a sequential/index scan is requested on a table that has a restriction on tid, you could just do a heap_fetch and return the single row, rather than putting the query through the whole scan process for every row checking to see if it matches the WHERE restriction. Seems like a few lines in the executor could do the entire job of fetching by tid by short-circuiting the sequential/index scan. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> If we put something in the executor so when a sequential/index scan is
> requested on a table that has a restriction on tid, you could just do a
> heap_fetch and return the single row, rather than putting the query
> through the whole scan process for every row checking to see if it
> matches the WHERE restriction.
> Seems like a few lines in the executor could do the entire job of
> fetching by tid by short-circuiting the sequential/index scan.
If I understand what you're proposing here, it would be a horrible
mangling of the system structure and doubtless a fruitful source
of bugs. I don't think we should be taking shortcuts with this issue.
If we think fast access by TID is worth supporting at all, we should
expend the work to do it properly.
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > > If we put something in the executor so when a sequential/index scan is > > requested on a table that has a restriction on tid, you could just do a > > heap_fetch and return the single row, rather than putting the query > > through the whole scan process for every row checking to see if it > > matches the WHERE restriction. > > > Seems like a few lines in the executor could do the entire job of > > fetching by tid by short-circuiting the sequential/index scan. > > If I understand what you're proposing here, it would be a horrible > mangling of the system structure and doubtless a fruitful source > of bugs. I don't think we should be taking shortcuts with this issue. > If we think fast access by TID is worth supporting at all, we should > expend the work to do it properly. But to do that whole thing properly, you are adding tons of complexity in access methods and stuff just to support one type that by definition is very internal to the backend. Just my ideas. I understand you concern. I just thought a few well-placed lines could do the trick without adding tons of other stuff. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce,I think that an index interface would be simpler than you think. The index does not need any disk storage which takes out virtually all the complexity in implementation. All that you really need to implement is the scan interface, and the only state that the scan needs is a single flag that indicates when getnext has already been called once. All that getnext need do is return the ctid, and flip the flag so that it knows to return null on the next call. You also need to ensure that the access method functions used by the optimizer return appropriate values to ensure that the cost of an index search is always zero. I have some suitable functions for that. With all due respect to people who I am sure know a lot more about this than I do, it seems to me that extensive use of TIDs in user code might place an unwelcome restraint on the internal database design. If you follow the arguments of the reiserfs people, the whole idea of a buffered cache with fix size blocks is a necessary hack to cope with a less than optimal underlying filesystem. In the ideal world that reiserfs promises (:-)) disk access efficiency would be independent of file-size, and it would be feasible to construct the buffered cache from raw tuples of variable size. The files on disk would be identified by OID. reiserfs uses a B-tree varient to cope with very large name spaces. Similar considerations would seem to apply if the storage layer of the database is separated from the rest of the backend by a high-speed qnetwork interface on something like a hard-disk farm. ( See for example some of the Mariposa work ). Until things like that actually happen (Version 10.* perhaps) I can see that TIDs are a useful addition, but you might want to fasten them in with a pyrotechnic interface so that you can blow them away if need be. I have a URL for the reiserfs stuff at home, if anyone is interested email me and I will dig it up and post it. Bernie Frankpitt
Interesting. I see what you mean. We have a pyrotechnic API already installed. > Bruce, > I think that an index interface would be simpler than you think. > The index does not need any disk storage which takes out virtually all > the complexity in implementation. All that you really need to implement > is the scan interface, and the only state that the scan needs is a > single flag that indicates when getnext has already been called once. > All that getnext need do is return the ctid, and flip the flag so that > it knows to return null on the next call. You also need to ensure that > the access method functions used by the optimizer return appropriate > values to ensure that the cost of an index search is always zero. I > have some suitable functions for that. > > > With all due respect to people who I am sure know a lot more about this > than I do, it seems to me that extensive use of TIDs in user code might > place an unwelcome restraint on the internal database design. If you > follow the arguments of the reiserfs people, the whole idea of a > buffered cache with fix size blocks is a necessary hack to cope with a > less than optimal underlying filesystem. In the ideal world that > reiserfs promises (:-)) disk access efficiency would be independent of > file-size, and it would be feasible to construct the buffered cache from > raw tuples of variable size. The files on disk would be identified by > OID. reiserfs uses a B-tree varient to cope with very large name > spaces. > > Similar considerations would seem to apply if the storage layer of the > database is separated from the rest of the backend by a high-speed > qnetwork interface on something like a hard-disk farm. ( See for > example some of the Mariposa work ). > > Until things like that actually happen (Version 10.* perhaps) I can see > that TIDs are a useful addition, but you might want to fasten them in > with a pyrotechnic interface so that you can blow them away if need be. > > I have a URL for the reiserfs stuff at home, if anyone is interested > email me and I will dig it up and post it. > > Bernie Frankpitt > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bernard Frankpitt <frankpit@pop.dn.net> writes:
> With all due respect to people who I am sure know a lot more about this
> than I do, it seems to me that extensive use of TIDs in user code might
> place an unwelcome restraint on the internal database design.
Yes, we'd certainly have to label it as an implementation-dependent
feature that might change or vanish in the future. But as long as
people understand that they are tying themselves to a particular
implementation, I can see the usefulness of making this feature
accessible. I'm still dubious that it's actually worth the work ...
but as long as I'm not the one doing the work, I can hardly object ;-).
I just want to be sure that we don't create a maintenance headache
for ourselves by corrupting the system structure. We've spent a
lot of time cleaning up after past shortcuts, and still have many
more to deal with; introducing new ones doesn't seem good.
regards, tom lane
> Bernard Frankpitt <frankpit@pop.dn.net> writes: > > With all due respect to people who I am sure know a lot more about this > > than I do, it seems to me that extensive use of TIDs in user code might > > place an unwelcome restraint on the internal database design. > > Yes, we'd certainly have to label it as an implementation-dependent > feature that might change or vanish in the future. But as long as > people understand that they are tying themselves to a particular > implementation, I can see the usefulness of making this feature > accessible. I'm still dubious that it's actually worth the work ... > but as long as I'm not the one doing the work, I can hardly object ;-). > > I just want to be sure that we don't create a maintenance headache > for ourselves by corrupting the system structure. We've spent a > lot of time cleaning up after past shortcuts, and still have many > more to deal with; introducing new ones doesn't seem good. Agreed. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > > > Bernard Frankpitt <frankpit@pop.dn.net> writes: > > > With all due respect to people who I am sure know a lot more > about this > > > than I do, it seems to me that extensive use of TIDs in user > code might > > > place an unwelcome restraint on the internal database design. > > > > Yes, we'd certainly have to label it as an implementation-dependent > > feature that might change or vanish in the future. But as long as > > people understand that they are tying themselves to a particular > > implementation, I can see the usefulness of making this feature > > accessible. I'm still dubious that it's actually worth the work ... > > but as long as I'm not the one doing the work, I can hardly object ;-). > > > > I just want to be sure that we don't create a maintenance headache > > for ourselves by corrupting the system structure. We've spent a > > lot of time cleaning up after past shortcuts, and still have many > > more to deal with; introducing new ones doesn't seem good. > > Agreed. > I think it isn't so difficult to implement a new type of scan on trial. But I'm not sure my story is right and I'm afraid to invite a maintenance headache like intersexcept .... May I proceed the work ? Regards. Hiroshi Inoue Inoue@tpf.co.jp