Does pg support to write a new buffer cache as an extension?
Re: Buffer Cache Problem
Hi, I have 3 questions here: 1. I see comments in but_internals.h below: * Also, in places we do one-time reads of the flags without bothering to * lock the buffer header; this is generally for situations where we don't * expect the flag bit being tested to be changing. In fact, the flag is in state filed which is an atomic_u32, so we don’t need to acquire buffer header lock in any case, but for this comment, seems it’s saying we need to hold a buffer header lock when read flag in general. 2. Another question: * We can't physically remove items from a disk page if another backend has * the buffer pinned. Hence, a backend may need to wait for all other pins * to go away. This is signaled by storing its own pgprocno into * wait_backend_pgprocno and setting flag bit BM_PIN_COUNT_WAITER. At present, * there can be only one such waiter per buffer. The comments above, in fact for now, if a backend plan to remove items from a disk page, this is a mutation operation, so this backend must hold a exclusive lock for this buffer page, then in this case, there are no other backends pinning this buffer, so the pin refcount must be 1 (it’s by this backend), then this backend can remove the items safely and no need to wait other backends (because there are no other backends pinning this buffer). So my question is below: The operation “storing its own pgprocno into * wait_backend_pgprocno and setting flag bit BM_PIN_COUNT_WAITER” is whether too expensive, we should not do like this, right? 3. Where is the array? * Per-buffer I/O condition variables are currently kept outside this struct in * a separate array. They could be moved in here and still fit within that * limit on common systems, but for now that is not done.
Re: Buffer Cache Problem
> 2023年11月10日 22:31，jacktby jacktby 写道： > > In the bus_internal.h,I see > > Note: Buffer header lock (BM_LOCKED flag) must be held to examine or change > tag, state or wait_backend_pgprocno fields. > > As we all know, this buffer header lock is implemented by a bit in state > filed, and this state field is a atomic_u32 type, so in fact we don’t need to > hold buffer lock when we update state, this comment has error，right? Oh, sorry this is true, in fact we never acquire a spin lock when update the state.
Re: Buffer Cache Problem
In the bus_internal.h,I see Note: Buffer header lock (BM_LOCKED flag) must be held to examine or change tag, state or wait_backend_pgprocno fields. As we all know, this buffer header lock is implemented by a bit in state filed, and this state field is a atomic_u32 type, so in fact we don’t need to hold buffer lock when we update state, this comment has error，right?
Buffer Cache Problem
Hi, postgres hackers, I’m studying postgres buffer cache part. So I open this
thread to communicate some buffer cache codes design and try to improve some
tricky codes.
For Buffer Cache, we know it’s a buffer array, every bucket of this array is
consist of a data page and its header which is used to describe the state of
the buffer.
This is the origin code of buffer header:
typedef struct BufferDesc
{
BufferTag tag;/* ID of page contained in
buffer */
int buf_id; /* buffer's index
number (from 0) */
/* state of the tag, containing flags, refcount and usagecount */
pg_atomic_uint32 state;
int wait_backend_pgprocno; /* backend of pin-count
waiter */
int freeNext; /* link in freelist
chain */
LWLock content_lock; /* to lock access to buffer contents */
} BufferDesc;
For field wait_backend_pgprocno, the comment is "backend of pin-count waiter”,
I have problems below:
1. it means which processId is waiting this buffer, right?
2. and if wait_backend_pgprocno is valid, so it says this buffer is in use by
one process, right?
3. if one buffer is wait by another process, it means all buffers are out of
use, right? So let’s try this: we have 5 buffers with ids (1,2,3,4,5), and they
are all in use, now another process with processId 8017 is coming, and it
choose buffer id 1, so buffer1’s wait_backend_pgprocno is 8017, but later
buffer4 is released, can process 8017 change to get buffer4? how?
4. wait_backend_pgprocno is a “integer” type, not an array, why can one buffer
be wait by only one process?
Hope your reply, thanks!! I’m willing to do contributions after I study buffer
cache implementations.
Re: Set enable_seqscan doesn't take effect?
> 2023年9月28日 12:26，David G. Johnston 写道： > > On Wednesday, September 27, 2023, jacktby jacktby <mailto:jack...@gmail.com>> wrote: >> postgres=# SET enable_seqscan = off; >> SET >> postgres=# explain select * from t; >>QUERY PLAN >> - >> Seq Scan on t (cost=100.00..123.60 rows=1360 width=32) > > It wouldn’t cost 10billion to return the first tuple if that setting wasn’t > working. > > That is the “discouragement” the documentation is referring to. > > I do agree the wording in the docs could be improved since it is a bit > self-contradictory and unspecific, but it is explicitly clear a plan with > sequential scan can still be chosen even with this set to off. > > David J. > Yes, I think that’s it.Thanks.
Re: Set enable_seqscan doesn't take effect?
> 2023年9月28日 01:07，Andres Freund 写道： > > Hi, > > On 2023-09-28 00:37:41 +0800, jacktby jacktby wrote: >> postgres=# SET enable_seqscan = off; >> SET >> postgres=# explain select * from t; >> QUERY PLAN >> - >> Seq Scan on t (cost=100.00..123.60 rows=1360 width=32) >> (1 row) >> >> postgres=# select * from t; >> a >> --- >> [1,2] >> (1 row) > > Sorry to be the grump here: > > You start several threads a week, often clearly not having done much, if any, > prior research. Often even sending the same question to multiple lists. It > should not be hard to find an explanation for the behaviour you see here. > > pgsql-hackers isn't a "do my work for me service". We're hacking on > postgres. It's fine to occasionally ask for direction, but you're very clearly > exceeding that. > > Greetings, > > Andres Freund I’m so sorry for that. I think I’m not very familiar with pg, so I ask many naive questions. And I apologize for my behavior.
Re: Set enable_seqscan doesn't take effect?
> 2023年9月28日 01:07，Andres Freund 写道： > > Hi, > > On 2023-09-28 00:37:41 +0800, jacktby jacktby wrote: >> postgres=# SET enable_seqscan = off; >> SET >> postgres=# explain select * from t; >> QUERY PLAN >> - >> Seq Scan on t (cost=100.00..123.60 rows=1360 width=32) >> (1 row) >> >> postgres=# select * from t; >> a >> --- >> [1,2] >> (1 row) > > Sorry to be the grump here: > > You start several threads a week, often clearly not having done much, if any, > prior research. Often even sending the same question to multiple lists. It > should not be hard to find an explanation for the behaviour you see here. > > pgsql-hackers isn't a "do my work for me service". We're hacking on > postgres. It's fine to occasionally ask for direction, but you're very clearly > exceeding that. > > Greetings, > > Andres Freund I’m so sorry for that. I think I’m not very familiar with pg, so I ask many naive questions. And I apologize for my behavior.
Re: Set enable_seqscan doesn't take effect?
> 2023年9月28日 01:07，Andres Freund 写道： > > Hi, > > On 2023-09-28 00:37:41 +0800, jacktby jacktby wrote: >> postgres=# SET enable_seqscan = off; >> SET >> postgres=# explain select * from t; >> QUERY PLAN >> - >> Seq Scan on t (cost=100.00..123.60 rows=1360 width=32) >> (1 row) >> >> postgres=# select * from t; >> a >> --- >> [1,2] >> (1 row) > > Sorry to be the grump here: > > You start several threads a week, often clearly not having done much, if any, > prior research. Often even sending the same question to multiple lists. It > should not be hard to find an explanation for the behaviour you see here. > > pgsql-hackers isn't a "do my work for me service". We're hacking on > postgres. It's fine to occasionally ask for direction, but you're very clearly > exceeding that. > > Greetings, > > Andres Freund I’m so sorry for that. I think I’m not very familiar with pg, so I ask many naive questions. And I apologize for my behavior.
Set enable_seqscan doesn't take effect?
postgres=# SET enable_seqscan = off; SET postgres=# explain select * from t; QUERY PLAN - Seq Scan on t (cost=100.00..123.60 rows=1360 width=32) (1 row) postgres=# select * from t; a --- [1,2] (1 row)
Re: Index AmInsert Parameter Confused?
> 2023年9月27日 18:08，Matthias van de Meent 写道： > > On Wed, 27 Sept 2023 at 05:03, jacktby jacktby <mailto:jack...@gmail.com>> wrote: >> >> >> >>> 2023年9月27日 00:45，Matthias van de Meent 写道： >>> >>> On Tue, 26 Sept 2023 at 18:38, jacktby jacktby wrote: >>>> >>>> typedef bool (*aminsert_function) (Relation indexRelation, >>>> Datum *values, >>>> bool *isnull, >>>> ItemPointer heap_tid, >>>> Relation heapRelation, >>>> IndexUniqueCheck checkUnique, >>>> bool indexUnchanged, >>>> struct IndexInfo *indexInfo); >>>> >>>> Why is there a heap_tid, We haven’t inserted the value, so where does it >>>> from ? >>> >>> Index insertion only happens after the TableAM tuple has been >>> inserted. As indexes refer to locations in the heap, this TID contains >>> the TID of the table tuple that contains the indexed values, so that >>> the index knows which tuple to refer to. >>> >>> Note that access/amapi.h describes only index AM APIs; it does not >>> cover the table AM APIs descibed in access/tableam.h >>> >>> Kind regards, >>> >>> Matthias van de Meent >> 1.Thanks, so if we insert a tuple into a table which has a index on itself, >> pg will insert tuple into heap firstly, and the give the heaptid form heap >> to the Index am api right? > > Correct. I think this is also detailed in various places of the > documentation, yes. > >> 2. I’m trying to implement a new index, but I just need the data held in >> index table, I hope it’s not inserted into heap, because the all data I want >> can be in index table. > > In PostgreSQL, a table maintains the source of truth for the data, and > indexes are ephemeral data structures that improve the speed of > querying the data in their table. As such, dropping an index should > not impact the availability of the table's data. > If the only copy of your (non-derived) data is in the index, then it > is likely that some normal table operations will result in failures > due to the tableAM/indexAM breaking built-in assumptions about access > methods and data availability. > > Kind regards, > > Matthias van de Meent > Neon (https://neon.tech <https://neon.tech/>) So do I need to free the ItemPointer and Values in the func implemented by myself?
Re: Index AmInsert Parameter Confused?
> 2023年9月27日 00:45，Matthias van de Meent 写道： > > On Tue, 26 Sept 2023 at 18:38, jacktby jacktby wrote: >> >> typedef bool (*aminsert_function) (Relation indexRelation, >> Datum *values, >> bool *isnull, >> ItemPointer heap_tid, >> Relation heapRelation, >> IndexUniqueCheck checkUnique, >> bool indexUnchanged, >> struct IndexInfo *indexInfo); >> >> Why is there a heap_tid, We haven’t inserted the value, so where does it >> from ? > > Index insertion only happens after the TableAM tuple has been > inserted. As indexes refer to locations in the heap, this TID contains > the TID of the table tuple that contains the indexed values, so that > the index knows which tuple to refer to. > > Note that access/amapi.h describes only index AM APIs; it does not > cover the table AM APIs descibed in access/tableam.h > > Kind regards, > > Matthias van de Meent 1.Thanks, so if we insert a tuple into a table which has a index on itself, pg will insert tuple into heap firstly, and the give the heaptid form heap to the Index am api right? 2. I’m trying to implement a new index, but I just need the data held in index table, I hope it’s not inserted into heap, because the all data I want can be in index table.
Index AmInsert Parameter Confused?
typedef bool (*aminsert_function) (Relation indexRelation, Datum *values, bool *isnull, ItemPointer heap_tid, Relation heapRelation, IndexUniqueCheck checkUnique, bool indexUnchanged, struct IndexInfo *indexInfo); Why is there a heap_tid, We haven’t inserted the value, so where does it from ?
What's the ItemPointer's meaning here?
/* Typedef for callback function for table_index_build_scan */ typedef void (*IndexBuildCallback) (Relation index, ItemPointer tid, Datum *values, bool *isnull, bool tupleIsAlive, void *state); When we build an index on an existed heap table, so pg will read the tuple one by one and give the tuple’s hepatid as this ItemPointer, is that right?
Why need a lock?
I find this in source code #define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */ I think if there is no CONCURRENTLY, so why we need a lock, I think that’s unnecessary. What’s the point?
How to Know the number of attrs?
typedef struct TupleDescData
{
int natts; /* number of attributes
in the tuple */
Oid tdtypeid; /* composite type ID
for tuple type */
int32 tdtypmod; /* typmod for tuple type */
int tdrefcount; /* reference count, or
-1 if not counting */
TupleConstr *constr;/* constraints, or NULL if none */
/* attrs[N] is the description of Attribute Number N+1 */
FormData_pg_attribute attrs[FLEXIBLE_ARRAY_MEMBER];
} TupleDescData;
Hi, the attrs use FLEXIBLE_ARRAY_MEMBER, so which API should I use to get the
real length of this array?
How to Know the number of attrs?
typedef struct TupleDescData
{
int natts; /* number of attributes
in the tuple */
Oid tdtypeid; /* composite type ID
for tuple type */
int32 tdtypmod; /* typmod for tuple type */
int tdrefcount; /* reference count, or
-1 if not counting */
TupleConstr *constr;/* constraints, or NULL if none */
/* attrs[N] is the description of Attribute Number N+1 */
FormData_pg_attribute attrs[FLEXIBLE_ARRAY_MEMBER];
} TupleDescData;
Hi, the attrs use FLEXIBLE_ARRAY_MEMBER, so which API should I use to get the
real length of this array?
Re: Implement a column store for pg?
> 2023年9月15日 20:31，jacktby jacktby 写道： > > I’m trying to implement a new column store for pg, is there a good example to > reference? That’s too complex, I just need to know the interface about design a column store. In fact, I just need a simple example, and I will implement it by myself, what I’m confusing is that, I don’t know how to implement a MVCC, because old version is tuple, this will make a big difference to the transaction?
Implement a column store for pg?
I’m trying to implement a new column store for pg, is there a good example to reference?
Buffer ReadMe Confuse
In buffer README, I see “Pins may not be held across transaction boundaries, however.” I think for different transactions, they can pin the same buffer page, why not? For concurrent read transactions, they could read the one and the same buffer page.
What's the eviction algorithm of newest pg version?
A normal LRU cache or implement it reference to a research paper?
Re: How to add built-in func?
> 2023年9月12日 00:34，Chapman Flack 写道： > > On 2023-09-11 12:28, jacktby jacktby wrote: >>> 2023年9月11日 23:51，Aleksander Alekseev 写道： >>> often better) add a corresponding extension to /contrib/. You can find >>> a complete example here [1] for instance, see v4-0001 patch and the >>> function pg_get_relation_publishing_info(). Make sure it has a proper >>> volatility [2]. The patch [3] shows how to add an extension. >>> [1]: >>> https://postgr.es/m/CAAWbhmjcnoV7Xu6LHr_hxqWmVtehv404bvDye%2BQZcUDSg8NSKw%40mail.gmail.com >>> [2]: https://www.postgresql.org/docs/current/xfunc-volatility.html >>> [3]: >>> https://postgr.es/m/CAJ7c6TMSat6qjPrrrK0tRTgZsdXwFAbkDn5gjeDtFnUFrjZX-g%40mail.gmail.com >>> -- >> I need to make it used for a new operator in my pg. > > You can implement both a function and an operator (and all that goes with) > in an extension, without having to hack at all on PostgreSQL itself. > You can then, if it seems generally useful enough, offer that extension > to go in contrib/. If it's agreed to be something everyone should have, > it could then make its way into core. > > Do you have it working as an extension yet? That can be a good way > to start, separating the difficulties you have to solve from the ones > you don't have to solve yet. > > Regards, > -Chap I solved it , but I need to use it in my new grammar, so I have to add in into core. That’s necessary. Thanks. But My own storage engine is implemented by extension. Extension is a good idea and I’m using it now.
Re: How to add built-in func?
> 2023年9月11日 23:51，Aleksander Alekseev 写道：
>
> Hi,
>
>> I only add below:
>>
>> Datum fake_dinstance2(PG_FUNCTION_ARGS)
>> {
>>PG_RETURN_INT16(0);
>> }
>> in src/backend/utils/adt/int8.c, and the I run “make install”,
>> But I can’t find the fake_distance2 in src/backend/utils/fmgrtab.c which is
>> generated by src/backend/utils/Gen_fmgrtab.pl. What else do I need to add?
>
> If the goal is to add a function that can be executed by a user (e.g.
> via psql) you have to add it to pg_proc.dat, or alternatively (and
> often better) add a corresponding extension to /contrib/. You can find
> a complete example here [1] for instance, see v4-0001 patch and the
> function pg_get_relation_publishing_info(). Make sure it has a proper
> volatility [2]. The patch [3] shows how to add an extension.
>
> [1]:
> https://postgr.es/m/CAAWbhmjcnoV7Xu6LHr_hxqWmVtehv404bvDye%2BQZcUDSg8NSKw%40mail.gmail.com
> [2]: https://www.postgresql.org/docs/current/xfunc-volatility.html
> [3]:
> https://postgr.es/m/CAJ7c6TMSat6qjPrrrK0tRTgZsdXwFAbkDn5gjeDtFnUFrjZX-g%40mail.gmail.com
> --
> Best regards,
> Aleksander Alekseev
I need to make it used for a new operator in my pg.
How to add built-in func?
I only add below:
Datum fake_dinstance2(PG_FUNCTION_ARGS)
{
PG_RETURN_INT16(0);
}
in src/backend/utils/adt/int8.c, and the I run “make install”,
But I can’t find the fake_distance2 in src/backend/utils/fmgrtab.c which is
generated by src/backend/utils/Gen_fmgrtab.pl. What else do I need to add?
Re: How to add a new pg oid?
> 2023年9月6日 18:50，jacktby jacktby 写道：
>
>
>
>> 2023年9月6日 18:19，jacktby jacktby 写道：
>>
>>
>>
>>> 2023年9月6日 01:47，David G. Johnston 写道：
>>>
>>> OIDs don't exist independently of the data they are associated with. Give
>>> more context if you want a better answer. Or just go look at the source
>>> code commits for when the last time something needing an OID got added to
>>> the core catalog.
>>>
>>> David J.
>>>
>>
>> { oid => '111', array_type_oid => '6099', descr => 'similarity columns',
>> typname => 'similarity_columns', typlen => '-1', typlen => '-1', typbyval
>> => 'f', typcategory => 'U',
>> typinput => 'byteain', typoutput => 'byteaout', typreceive => 'bytearecv',
>> typsend => 'byteasend', typalign => 'i', typstorage => 'x' },
>>
>> I add above into pg_type.dat. And then I add execute “make install” and
>> restart pg. And Then do below:
>> postgres=# SELECT typname from pg_type where typname like '%similarity%';
>> typname
>> -
>> (0 rows)
>>
>> I can’t get the type I added. What else I need to do?
> I add below in bootstrap.c:
> static const struct typinfo TypInfo[] = {
> {"similarity_columns", SimilarityColumns, 0, -1, false, TYPALIGN_INT,
> TYPSTORAGE_EXTENDED, InvalidOid,
>F_BYTEAIN, F_BYTEAOUT},
> ….
> }
> And then “make install” and restart pg.but still:
> postgres=# SELECT typname from pg_type where typname like '%similarity%';
> typname
> -
> (0 rows)
>
> Please give me help.
After initdb , I get it. Thanks
Re: How to add a new pg oid?
> 2023年9月6日 18:19，jacktby jacktby 写道：
>
>
>
>> 2023年9月6日 01:47，David G. Johnston 写道：
>>
>> OIDs don't exist independently of the data they are associated with. Give
>> more context if you want a better answer. Or just go look at the source
>> code commits for when the last time something needing an OID got added to
>> the core catalog.
>>
>> David J.
>>
>
> { oid => '111', array_type_oid => '6099', descr => 'similarity columns',
> typname => 'similarity_columns', typlen => '-1', typlen => '-1', typbyval
> => 'f', typcategory => 'U',
> typinput => 'byteain', typoutput => 'byteaout', typreceive => 'bytearecv',
> typsend => 'byteasend', typalign => 'i', typstorage => 'x' },
>
> I add above into pg_type.dat. And then I add execute “make install” and
> restart pg. And Then do below:
> postgres=# SELECT typname from pg_type where typname like '%similarity%';
> typname
> -
> (0 rows)
>
> I can’t get the type I added. What else I need to do?
I add below in bootstrap.c:
static const struct typinfo TypInfo[] = {
{"similarity_columns", SimilarityColumns, 0, -1, false, TYPALIGN_INT,
TYPSTORAGE_EXTENDED, InvalidOid,
F_BYTEAIN, F_BYTEAOUT},
….
}
And then “make install” and restart pg.but still:
postgres=# SELECT typname from pg_type where typname like '%similarity%';
typname
-
(0 rows)
Please give me help.
Re: How to add a new pg oid?
> 2023年9月6日 01:47，David G. Johnston 写道：
>
> OIDs don't exist independently of the data they are associated with. Give
> more context if you want a better answer. Or just go look at the source code
> commits for when the last time something needing an OID got added to the core
> catalog.
>
> David J.
>
{ oid => '111', array_type_oid => '6099', descr => 'similarity columns',
typname => 'similarity_columns', typlen => '-1', typlen => '-1', typbyval =>
'f', typcategory => 'U',
typinput => 'byteain', typoutput => 'byteaout', typreceive => 'bytearecv',
typsend => 'byteasend', typalign => 'i', typstorage => 'x' },
I add above into pg_type.dat. And then I add execute “make install” and restart
pg. And Then do below:
postgres=# SELECT typname from pg_type where typname like '%similarity%';
typname
-
(0 rows)
I can’t get the type I added. What else I need to do?
Re: How to add a new pg oid?
> 2023年9月5日 22:29，jacktby jacktby 写道： > I’m trying to add a new data type for my pg. How to do that? Can you give me more details or an example?
How to add a new pg oid?
Is there a complete doc to describe pg's traction implementation in detail?
Re: How to add a new operator for parser?
> 2023年8月6日 13:18，Julien Rouhaud 写道： > > On Sun, 6 Aug 2023, 12:34 jacktby jacktby, <mailto:jack...@gmail.com>> wrote: >> I’m trying to add a new operator for my pg application like greater_equals >> called “<~>", how many files I need to >> modify and how to do it? Can you give me an example? > > > you can look at some contrib for some examples of custom operator (and custom > datatype) implementation, like citext or btree_gin/gist I need to build a new datatype. It can contains different datatypes, like ‘(1,’a’,2.0)’,it’s a (interger,string,float) tuple type, and Then I need to add operator for it. How should I do?
How to add a new operator for parser?
I’m trying to add a new operator for my pg application like greater_equals called “<~>", how many files I need to modify and how to do it? Can you give me an example?
