[HACKERS] Get the offset of a tuple inside a table
Hi, When I using an index scan, can I get the offset of the tuple in the table? Thanks -- Pei
Re: [HACKERS] Get the offset of a tuple inside a table
Hi Szymon, Kevin, The offset is the order of a tuple in a Sequential Scan. What I want to do is, for a given key return the tuples that Index scan can find, and return the rest tuples by a seq scan. So, I need to know which tuples have been returned by maintain a bitmap, and to avoid return the same tuple twice. If I can know the offset of a tuple in the order of file scan, then I can force the seq scan to skip it. By ctid, can I know how much tuples are in a block, is it an constant? I think a more general solution would be build a hash table on ctid. What do you think? Thanks -- Pei On Tue, Sep 21, 2010 at 5:44 PM, Szymon Guz mabew...@gmail.com wrote: On 21 September 2010 23:02, Pei He hepeim...@gmail.com wrote: Hi, When I using an index scan, can I get the offset of the tuple in the table? Thanks -- Pei What do you mean by the offset in the table?
Re: [HACKERS] Get the offset of a tuple inside a table
I doesn't realized postgresql can be such clever. Another problem I just found is after one tuple is deleted, there will be some slots unoccupied. The offset cannot be computed even know how much tuples a page can contain. I need one hash table. Thanks -- Pei On Tue, Sep 21, 2010 at 8:47 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Pei He wrote: The offset is the order of a tuple in a Sequential Scan. That's not a safe assumption. Try starting a sequential scan against a large table on one connection; then before it finishes, start the same query on another connection. The second query joins the one already in progress and then starts over, wrapping around. -Kevin
Re: [HACKERS] Get the offset of a tuple inside a table
Hi Tom, The bitmapset works for me. I want to implement the operator for the following query: Select * from a left join b on a.id = b.id order by b.id; In a left outer join, I want the tuples that have matches in the inner table appear first. So, the order by clause is need. If there is a index on a.id, I can use the tuples in b to probe the index. After return all the tuples retrieved through index, it needs to return the rest tuples in a, because it is a left outer join in the query. What I need to do is remember what have been returned by the index, and avoid to return it twice. The bitmapscan needs to remember what have to been retrieved later, so it used the tidbitmap. But, for me, I need the bitmapset. Thanks, your reply helps me to find the bitmapset. Regards -- Pei On Tue, Sep 21, 2010 at 9:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Pei He hepeim...@gmail.com writes: What I want to do is, for a given key return the tuples that Index scan can find, and return the rest tuples by a seq scan. So, I need to know which tuples have been returned by maintain a bitmap, and to avoid return the same tuple twice. If I can know the offset of a tuple in the order of file scan, then I can force the seq scan to skip it. As pointed out, offset is an unworkable concept here. That's why the tidbitmap code doesn't work with offsets; it works with tids. You don't really need to reinvent this wheel. Go read the bitmapscan code. (One wonders though what you think you are going to save if you have to do a seqscan anyway. Where's the advantage over just doing a seqscan?) regards, tom lane
[HACKERS] Compute the number of tuples in a block
Hi, In ctid, there are the block num, and the tuple offset inside the block. How can I know the maximum number of tuples in a block? The block size would be BLCKSZ. I am not quite sure where is the best place to find the size of the tuple in a table. Thanks -- Pei
[HACKERS] How to construct an exact plan
Hi, I have developed a new operators, and I want to do some tests on it. I do not want the optimizer to choose the plan for me, and I need to construct a plan as exact as I want. Can anyone provide me a way to achieve that? Thanks -- Pei -- 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] How to construct an exact plan
I forgot to mention that I am using postgresql 8.2.5. Thanks -- Pei On Mon, Aug 30, 2010 at 1:34 PM, Pei He hepeim...@gmail.com wrote: Hi, I have developed a new operators, and I want to do some tests on it. I do not want the optimizer to choose the plan for me, and I need to construct a plan as exact as I want. Can anyone provide me a way to achieve that? Thanks -- Pei -- 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] How to construct an exact plan
Hi, I am hacking postgresql 8.2.5. a) and b) do not work for me. The situation is that I made a join operator, and a scan operator. And, The join operator requires the scan operator as the inner. So, I need to have the full control of the join plan. I am not ready to provide the optimization support for the two new operators. And, I want to run some performance tests before working on the optimization part. So, I want to know if it is possible to directly create a path or a plan, and do a unit test for the operators. Thanks -- Pei On Mon, Aug 30, 2010 at 1:59 PM, Josh Berkus j...@agliodbs.com wrote: I have developed a new operators, and I want to do some tests on it. I do not want the optimizer to choose the plan for me, and I need to construct a plan as exact as I want. Can anyone provide me a way to achieve that? a) easy: choose a simple enough query that its plan is always predictable. b) moderate: choose a query whose plan is predictable if you manipulate the enable_* configuration settings c) hard: hack the PostgreSQL planner to choose a specific execution plan, and recompile Postgres. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] About the types of fields in a data structure
Hi, When I check the types of fields in a data structure, I found most fields are defined as general types, as List, Node. Then, To know the content inside the List, I need to track the usage of the fields. For example, the fromClause in SelectStmt is defined as List. And, the content in the ListCell is with the type of RangeVar. Is there other easier way to check the types information, rather than track through the code? Or is there any document about it? Thanks -- Pei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] About debug two versions of postgresql in eclipse
Hi, I want to run two different versions of postgresql-8.2.5 under eclipse. But, it requires me to change PGDATA and LD_LIBRARY_PATH to switch. Moreover, to let eclipse know the changes, I need to modify .profile under my home folder, and log out and log in. (I am using Ubuntu.) Is there a way that I can debug two versions of postgresql without closing eclipse? Thanks -- Pei -- 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] About debug two versions of postgresql in eclipse
Thanks, Nicolas. It works. -- Pei On Tue, Aug 24, 2010 at 2:38 PM, Nicolas Barbier nicolas.barb...@gmail.com wrote: 2010/8/24 Pei He hepeim...@gmail.com: I want to run two different versions of postgresql-8.2.5 under eclipse. But, it requires me to change PGDATA and LD_LIBRARY_PATH to switch. Moreover, to let eclipse know the changes, I need to modify .profile under my home folder, and log out and log in. (I am using Ubuntu.) Is there a way that I can debug two versions of postgresql without closing eclipse? In the each run configuration, in the Environment tab, add those environment variables, and given them values that correspond to the PG version that corresponds to that run configuration. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem of Magic Block in Postgres 8.2
Hi,
I have some old code for extension functions in Postgres 8.0. And, I
am trying to make it work with Postgres 8.2.
One problem is about the Magic Block.
The extension functions was developed by C++ mixed with C.
The code is like:
extern C Datum spgistinsert(PG_FUNCTION_ARGS)
{
...
}
I have added enough PG_MODULE_MAGIC; to the source code. (Also,
include 'fmgr.h' and 'postgres.h')
But, when I create the function inside Postgres, it still complains as
following:
ERROR: incompatible library
/home/hepei/bin/Chameleon/lib/libspgist_trie.so: missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT: /* Load the trie extension */
/* */
load '/home/hepei/bin/Chameleon/lib/libspgist_trie.so';
ERROR: incompatible library
/home/hepei/bin/Chameleon/lib/libspgist_trie.so: missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
I am capable to define a simple function in a single file by C, and
make it work with postgres 8.2. However, my old code is more complex,
which involves more source code, library, and it is also using both
C++ and C. So, I am not sure where the problem comes from, and how to
solve it.
Thanks
Look forward your reply
--
Pei
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Ask help for putting SP-Gist into postgresql
Hi, I am trying to put the SP-Gist package, a general index framework for space partitioning trees , into Postgresql source code. SP-Gist was developed for postgresql 8.0. However, now it does not work with the new version. So, for the submitted patch, what version of postgresql is required? And, is there anybody can help with that? Please cc to my email, when reply. Thanks -- Pei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
