Regarding extension
Hi team, I am creating sample extension in postgres, For that "During PG_INIT, i want to get the list of database Id's in which my extension is installed". Is there a way to get this? and also another one. How to have trigger for create extension? Thanks in advance.
Re: Regarding extension
Thanks for your response Euler. 1) "id" i meant by database id I make my question simple, " during pg_init i want to get databaseid's in which my extension is installed... " 1. by using pg_database and pg_extension catalogs 2. if there any other way, kindly suggest me. 2) I have one sql file which will be loaded during create extension, in that file only i have code for event trigger for create extension on ddl_command_end event My question is "When giving create extension, sql file will be loaded at that time only, if that is the case, this event trigger will be invoked or not? " Thanks... On Thu, 3 Oct 2019 at 19:31, Euler Taveira wrote: > Em qui, 3 de out de 2019 às 02:24, Natarajan R > escreveu: > > > > I am creating sample extension in postgres, For that "During PG_INIT, i > want to get the list of database Id's in which my extension is installed". > Is there a way to get this? > > > I'm not sure what you mean by "ld". However, if you want to know an > extension is installed in a specific database, you should be logged in > it. That's because extension catalog is not global. > > > How to have trigger for create extension? > > > Event triggers. > > > -- >Euler Taveira Timbira - > http://www.timbira.com.br/ >PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento >
Re: Regarding extension
On Thu, 3 Oct 2019 at 20:54, Tomas Vondra wrote: > On Thu, Oct 03, 2019 at 07:51:04PM +0530, Natarajan R wrote: > >Thanks for your response Euler. > > > >1) > >"id" i meant by database id > > > >I make my question simple, " during pg_init i want to get databaseid's in > >which my extension is installed... " > >1. by using pg_database and pg_extension catalogs > >2. if there any other way, kindly suggest me. > > > > Well, there's also MyDatabaseId variable, which tells you the OID of the > current database. So you can use that, from the C code. In SQL, you can > simply run "SELECT current_database()" or something like that. > > Me: Thanks Tomas, But this is for that particular database only, I want to get the *list of database Id's* on which my extension is installed during *PG_INIT* itself... > > >2) > >I have one sql file which will be loaded during create extension, in that > >file only i have code for event trigger for create extension on > >ddl_command_end event > >My question is "When giving create extension, sql file will be loaded at > >that time only, if that is the case, this event trigger will be invoked or > >not? " > > > > I'm not sure I understand the question. Are you asking if the event > trigger will be invoked to notify you about creation of the extension > containing it? I'm pretty sure that won't happen - it will be executed > only for future CREATE EXTENSION commands. > > Me: Thanks Tomas, Yaah, what you said above is the way it should perform, but this trigger has been invoked in postgres 10.0 but not in postgres 10.4.. So, i am asking any GUC or anything need to be enabled to invoke this type of event triggers in 10.4 version tooo..
Shared memory
Why postgres not providing freeing shared memory?
HashTable KeySize
typedef struct HashTableKey
{
Oid dbId; // 4 bytes
int64 productid; // 8 bytes
}HashTableKey; (total size - 12 bytes)
typedef struct HashTableEntry
{
HashTableKey key;
ProductInfo *pdt;
}HashTableEntry;
HASHCTL hashInfo;
hashInfo.keysize = sizeof(HashTableKey);
hashInfo.entrysize = sizeof(HashTableEntry);
SampleHashTable = ShmemInitHash("productid vs product struct HashTable",
size, size, &hashInfo, HASH_ELEM | HASH_SHARED_MEM | HASH_BLOBS);
while printing keysize: elog(LOG,"Keysize = %d",sizeof(HashTableKey));
I am getting Keysize = 16, How? what should i need to do inorder to have
keysize = 12
pg_init
I want to read pg_database from pg_init... Is using heap_open() is possible? or else any other way is there ?
Postgres cache
Hi, I want to know how postgres stores catalog relations in cache in-depth. Is there any documentation for that?
Valgrind mem-check for postgres extension
Hi pg-hackers, I have written a postgres extension, and know that memory leak check can be done with valgrind. With the help of postgres_valgrind_wiki <https://wiki.postgresql.org/wiki/Valgrind> started postgres server with the valgrind(as given in the wiki) valgrind --leak-check=no --gen-suppressions=all \ --suppressions=src/tools/valgrind.supp --time-stamp=yes \ --error-markers=VALGRINDERROR-BEGIN,VALGRIND ERROR-END \ --log-file=$HOME/pg-valgrind/%p.log --trace-children=yes \ postgres --log_line_prefix="%m %p " \ --log_statement=all --shared_buffers=64MB 2>&1 | tee $HOME/pg-valgrind/postmaster.log I have few doubts in here, 1. When I run with *--leak-check=full*, I get memory leaks for postgres functions under possibly or definitely lost categories.. Is this expected? If yes, how shall i ignore it?(by creating .supp?).. kindly suggest 2. Is there any other way to test my extension memory leaks alone, because combining with postgres leaks is making instrumentation complex?.. 3. I have seen some macros for valgrind support within postgres source code under utils/memdebug.h, but couldn't get complete idea of using it from the comments in pg_config_manual.h under *USE_VALGRIND *macro, pls provide some guidance here.. Thank you, Natarajan R
Re: Compressed pluggable storage experiments
Hi all, This is a continuation of the above thread... >> > 4. In order to use WAL-logging each page must start with a standard 24 >> > byte PageHeaderData even if it is needless for storage itself. Not a >> > big deal though. Another (acutally documented) WAL-related limitation >> > is that only generic WAL can be used within extension. So unless >> > inserts are made in bulks it's going to require a lot of disk space to >> > accomodate logs and wide bandwith for replication. >> >> Not sure what to suggest. Either you should ignore this problem, or >> you should fix it. I am working on an environment similar to the above extension(pg_cryogen which experiments pluggable storage api's) but don't have much knowledge on pg's logical replication.. Please suggest some approaches to support pg's logical replication for a table with a custom access method, which writes generic wal record. On Wed, 17 Aug 2022 at 19:04, Tomas Vondra wrote: > On Fri, Oct 18, 2019 at 03:25:05AM -0700, Andres Freund wrote: > >Hi, > > > >On 2019-10-17 12:47:47 -0300, Alvaro Herrera wrote: > >> On 2019-Oct-10, Ildar Musin wrote: > >> > >> > 1. Unlike FDW API, in pluggable storage API there are no routines like > >> > "begin modify table" and "end modify table" and there is no shared > >> > state between insert/update/delete calls. > >> > >> Hmm. I think adding a begin/end to modifytable is a reasonable thing to > >> do (it'd be a no-op for heap and zheap I guess). > > > >I'm fairly strongly against that. Adding two additional "virtual" > >function calls for something that's rarely going to be used, seems like > >adding too much overhead to me. > > > > That seems a bit strange to me. Sure - if there's an alternative way to > achieve the desired behavior (clear way to finalize writes etc.), then > cool, let's do that. But forcing people to use invonvenient workarounds > seems like a bad thing to me - having a convenient and clear API is > quite valueable, IMHO. > > Let's see if this actually has a measuerable overhead first. > > > > >> > 2. It looks like I cannot implement custom storage options. E.g. for > >> > compressed storage it makes sense to implement different compression > >> > methods (lz4, zstd etc.) and corresponding options (like compression > >> > level). But as i can see storage options (like fillfactor etc) are > >> > hardcoded and are not extensible. Possible solution is to use GUCs > >> > which would work but is not extremely convinient. > >> > >> Yeah, the reloptions module is undergoing some changes. I expect that > >> there will be a way to extend reloptions from an extension, at the end > >> of that set of patches. > > > >Cool. > > > > Yep. > > > > >> > 3. A bit surprising limitation that in order to use bitmap scan the > >> > maximum number of tuples per page must not exceed 291 due to > >> > MAX_TUPLES_PER_PAGE macro in tidbitmap.c which is calculated based on > >> > 8kb page size. In case of 1mb page this restriction feels really > >> > limiting. > >> > >> I suppose this is a hardcoded limit that needs to be fixed by patching > >> core as we make table AM more pervasive. > > > >That's not unproblematic - a dynamic limit would make a number of > >computations more expensive, and we already spend plenty CPU cycles > >building the tid bitmap. And we'd waste plenty of memory just having all > >that space for the worst case. ISTM that we "just" need to replace the > >TID bitmap with some tree like structure. > > > > I think the zedstore has roughly the same problem, and Heikki mentioned > some possible solutions to dealing with it in his pgconfeu talk (and it > was discussed in the zedstore thread, I think). > > > > >> > 4. In order to use WAL-logging each page must start with a standard 24 > >> > byte PageHeaderData even if it is needless for storage itself. Not a > >> > big deal though. Another (acutally documented) WAL-related limitation > >> > is that only generic WAL can be used within extension. So unless > >> > inserts are made in bulks it's going to require a lot of disk space to > >> > accomodate logs and wide bandwith for replication. > >> > >> Not sure what to suggest. Either you should ignore this problem, or > >> you should fix it. > > > >I think if it becomes a problem you should ask for an rmgr ID to use for > >your extension, which we encode and then then allow to set the relevant > >rmgr callbacks for that rmgr id at startup. But you should obviously > >first develop the WAL logging etc, and make sure it's beneficial over > >generic wal logging for your case. > > > > AFAIK compressed/columnar engines generally implement two types of > storage - write-optimized store (WOS) and read-optimized store (ROS), > where the WOS is mostly just an uncompressed append-only buffer, and ROS > is compressed etc. ISTM the WOS would benefit from a more elaborate WAL > logging, but ROS should be mostly fine with the generic WAL logging. > > But yeah, we should test and measure how beneficial that actually is.
Logical replication support for generic wal record
Hi All, I am writing a postgres extension which writes only generic wal record, but this wal is not recognized by logical replication decoder. I have a basic understanding of how logical replication(COPY command for initial sync, wal replica for final sync) works, can you please tell us a way to support this? Thanks, Natarajan.R
Re: Logical replication support for generic wal record
On Mon, 22 Aug 2022 at 12:16, Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > On Mon, Aug 22, 2022 at 11:59 AM Natarajan R > wrote: > > > > Hi All, > > > > I am writing a postgres extension which writes only generic wal record, > but this wal is not recognized by logical replication decoder. I have a > basic understanding of how logical replication(COPY command for initial > sync, wal replica for final sync) works, can you please tell us a way to > support this? > > "Generic" resource manager doesn't have a decoding API, see [1], which > means that the generic WAL records will not get decoded. > > Can you be more specific about the use-case? Why use only "Generic" > type WAL records? Why not use "LogicalMessage" type WAL records if you > want your WAL records to be decoded? > > I am writing an extension which implements postgres table access method interface[1] with master-slave architecture, with the help of doc[1] i decided to go with generic_wal to achieve crash_safety and also for streaming replication. It seems like generic_wal couldn't help with logical replication.. But, I don't have knowledge on "LogicalMessage" Resource Manager, need to explore about it. [1] https://www.postgresql.org/docs/current/tableam.html
Re: Logical replication support for generic wal record
Thanks, I'll check it out. On Wed, 24 Aug 2022 at 18:00, Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > On Wed, Aug 24, 2022 at 5:12 PM Natarajan R wrote: > > > > > > On Mon, 22 Aug 2022 at 12:16, Bharath Rupireddy < > bharath.rupireddyforpostg...@gmail.com> wrote: > >> > >> On Mon, Aug 22, 2022 at 11:59 AM Natarajan R > wrote: > >> > > >> > Hi All, > >> > > >> > I am writing a postgres extension which writes only generic wal > record, but this wal is not recognized by logical replication decoder. I > have a basic understanding of how logical replication(COPY command for > initial sync, wal replica for final sync) works, can you please tell us a > way to support this? > >> > >> "Generic" resource manager doesn't have a decoding API, see [1], which > >> means that the generic WAL records will not get decoded. > >> > >> Can you be more specific about the use-case? Why use only "Generic" > >> type WAL records? Why not use "LogicalMessage" type WAL records if you > >> want your WAL records to be decoded? > >> > > I am writing an extension which implements postgres table access method > interface[1] with master-slave architecture, with the help of doc[1] i > decided to go with generic_wal to achieve crash_safety and also for > streaming replication. It seems like generic_wal couldn't help with logical > replication.. > > But, I don't have knowledge on "LogicalMessage" Resource Manager, need > to explore about it. > > > > > > [1] https://www.postgresql.org/docs/current/tableam.html > > I think the 'Custom WAL Resource Managers' feature would serve the > exact same purpose (as also pointed out by Amit upthread), you may > want to explore that feature [1]. Here's a sample extension using that > feature [2], for a different purpose though, but helps to understand > the usage of custom WAL rmgrs. > > I notice that the docs [3] don't mention the feature in the right > place, IMO, it can be improved to refer to custom-rmgr.sgml page, > cc-ing Jeff Davis for his thoughts. This would help developers quickly > try the feature out and saves time. > > [1] > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5c279a6d350205cc98f91fb8e1d3e4442a6b25d1 > [2] https://github.com/BRupireddy/pg_synthesize_wal > [2] https://www.postgresql.org/docs/devel/tableam.html > "For crash safety, an AM can use postgres' WAL, or a custom > implementation. If WAL is chosen, either Generic WAL Records can be > used, or a new type of WAL records can be implemented. Generic WAL > Records are easy, but imply higher WAL volume. Implementation of a new > type of WAL record currently requires modifications to core code > (specifically, src/include/access/rmgrlist.h)." > > -- > Bharath Rupireddy > RDS Open Source Databases: https://aws.amazon.com/rds/postgresql/ >
