Обсуждение: DB & Schema
During the execution of ProcessUtility() function in /src/backend/xxx/utility.c, the CreateStmt node type is processed to create a table. Is there a global function in the context of the backend process that will deliver what the current database and schema names? The querystring cannot be relied upon for discerning this information. marco
On 21.09.2012 17:58, md@rpzdesign.com wrote: > During the execution of ProcessUtility() function in > /src/backend/xxx/utility.c, > > the CreateStmt node type is processed to create a table. > > Is there a global function in the context of the backend process that > will deliver what the current database and schema names? There's a global variable MyDatabaseId for the database the backend is connected to. It doesn't change after login. There's no such thing as a "current schema", but I think you'll want to take a look at src/backend/catalog/namespace.c, which handles the search_path. There's a variable activeCreationNamespace there; look at the functions in namespace.c to see how it works. - Heikki
Heikki: Thanks for the response. I am writing an external replication engine and putting hooks into postgres to send "signals" via a unix socket to the outside world. All of the logic and implementation will occur OUTSIDE the postgres codebase and will not interfere with any WAL based replication schemes. The usual "Trigger" level asynch replication does on not suffice since it does not handle new databases, new schemas, new tables, new indexes, alter everything, new functions, etc. So I started putting into utility.c->xxx_ProcessUtility(..., stmt* parsetree,....) so that discrete Does anybody have other ideas where to better locate the "Hooks" for external replication/signaling than utility.c? One drawback is that I have to patch directly into those areas where new relation IDs are created so I can pass them outside of the process to the replication engine. Process Utility does a really bad job of retaining its work as it processes the statements, so I have to patch code where the relation IDS are created. Those new IDS are never retained when leaving ProcessUtility, its work is done. Is there a way to put a "Trigger" on pg_class, pg_database, pg_namespace instead of patching the statically compiled binary to simulate the triggers? Cheers, marco On 9/21/2012 10:15 AM, Heikki Linnakangas wrote: > On 21.09.2012 17:58, md@rpzdesign.com wrote: >> During the execution of ProcessUtility() function in >> /src/backend/xxx/utility.c, >> >> the CreateStmt node type is processed to create a table. >> >> Is there a global function in the context of the backend process that >> will deliver what the current database and schema names? > > There's a global variable MyDatabaseId for the database the backend is > connected to. It doesn't change after login. > > There's no such thing as a "current schema", but I think you'll want > to take a look at src/backend/catalog/namespace.c, which handles the > search_path. There's a variable activeCreationNamespace there; look at > the functions in namespace.c to see how it works. > > - Heikki > >
On Friday, September 21, 2012 08:12:26 PM md@rpzdesign.com wrote: > Heikki: > > Thanks for the response. I am writing an external replication engine > and putting hooks into postgres to send "signals" via a unix socket to > the outside world. > > All of the logic and implementation will occur OUTSIDE the postgres > codebase and > will not interfere with any WAL based replication schemes. > > The usual "Trigger" level asynch replication does on not suffice since > it does not handle > new databases, new schemas, new tables, new indexes, alter everything, > new functions, etc. > > So I started putting into utility.c->xxx_ProcessUtility(..., stmt* > parsetree,....) so that discrete > > Does anybody have other ideas where to better locate the "Hooks" for > external replication/signaling > than utility.c? Look into the new event triggers started recently. Commits http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3855968f328918b6cd1401dd11d109d471a54d40 and 3a0e4d36ebd7f477822d5bae41ba121a40d22ccc Look into earlier discussions around event/command triggers why putting stuff plainly into ProcessUtility is not all that helpful... Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres: Thanks for the link on the GIT patch code. I did a big major domo search and found some stuff related to command triggers: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php "Look into the new event triggers started recently." - Are these command triggers currently in the 9.2.0 code base or is it in a alpha 9.2.1xxx? I searched the 9.2.0 code base and found nothing on CREATE TRIGGER that had anything to do with other than TABLES and VIEWS. I cannot wait for stable code to arrive, I need to add it today. Since the hackers group is already working on this, I will not even try to contribute this work, unless someone wants it. marco On 9/21/2012 1:19 PM, Andres Freund wrote: > On Friday, September 21, 2012 08:12:26 PM md@rpzdesign.com wrote: >> Heikki: >> >> Thanks for the response. I am writing an external replication engine >> and putting hooks into postgres to send "signals" via a unix socket to >> the outside world. >> >> All of the logic and implementation will occur OUTSIDE the postgres >> codebase and >> will not interfere with any WAL based replication schemes. >> >> The usual "Trigger" level asynch replication does on not suffice since >> it does not handle >> new databases, new schemas, new tables, new indexes, alter everything, >> new functions, etc. >> >> So I started putting into utility.c->xxx_ProcessUtility(..., stmt* >> parsetree,....) so that discrete >> >> Does anybody have other ideas where to better locate the "Hooks" for >> external replication/signaling >> than utility.c? > Look into the new event triggers started recently. > > Commits > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3855968f328918b6cd1401dd11d109d471a54d40 > and > 3a0e4d36ebd7f477822d5bae41ba121a40d22ccc > > Look into earlier discussions around event/command triggers why putting stuff > plainly into ProcessUtility is not all that helpful... > > Greetings, > > Andres >
Hi, On Friday, September 21, 2012 11:06:46 PM md@rpzdesign.com wrote: > Thanks for the link on the GIT patch code. > > I did a big major domo search and found some stuff related to command > triggers: > > http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php > > "Look into the new event triggers started recently." - > > Are these command triggers currently in the 9.2.0 code base or is it in > a alpha 9.2.1xxx? Its not in 9.2 and will only be in 9.3 Andres -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Andres & those involved in the 9.3 Command triggers: You may want to consider changing the command TRIGGER into a command FILTER and possibly post processing TRIGGER that is determined to be called INSIDE the FILTER. Or some way to pass information between the FILTER and the post processing trigger. Also, something information as to whether a series of statements was ROLLED BACK would be helpful. How to rebuild the parsetree so external replication could catch a clue that all of the last series of updates was actually rolled back! For example, I want to enforce a policy where all DROP TABLE xxxx commands become ALTER TABLE xxxx RENAME to SIGyyyyy All DROP TABLE Sigyyyy commands are not changed so that the SIG tables actually drop. (Almost like a TRASH Can for postgresql!) ALL TRUNCATE TABLE xxxx Become ALTER TABLE xxxx RENAME to SIGyyyyy AND THEN CREATE TABLE xxxx SELECT AS SIGyyyy This way, there is not possible way to have to enforce a Waiting period for replication to begin its work when errant DROP / TRUNCATES occur. All of this is important for Easy Setup/Initiation, Self-Healing, Fault Tolerant, Scaleable, INEXPENSIVE, External REPLICATION, a larger subject indeed. I want CLOUD behavior without CLOUD prices. Anybody who is working on the 9.3 COMMAND TRIGGER, drop me a note if you wish. marco On 9/21/2012 3:41 PM, Andres Freund wrote: > Hi, > > On Friday, September 21, 2012 11:06:46 PM md@rpzdesign.com wrote: >> Thanks for the link on the GIT patch code. >> >> I did a big major domo search and found some stuff related to command >> triggers: >> >> http://archives.postgresql.org/pgsql-hackers/2012-03/msg00169.php >> >> "Look into the new event triggers started recently." - >> >> Are these command triggers currently in the 9.2.0 code base or is it in >> a alpha 9.2.1xxx? > Its not in 9.2 and will only be in 9.3 > > Andres
"md@rpzdesign.com" <md@rpzdesign.com> writes: > You may want to consider changing the command TRIGGER into a command FILTER > and possibly post processing TRIGGER that > is determined to be called INSIDE the FILTER. Or some way to pass > information between the FILTER and the post processing trigger. The only current "event" supported by the system is the "ddl_command_start" one. We mean to add some more, and triggers wanting to communicate data in between "ddl_command_start" and "ddl_command_end" (for example) will have to use something like a table. > Also, something information as to whether a series of statements was ROLLED > BACK would be helpful. Event Triggers are not an autonomous transaction: any effect they have in the database is rolled-backed when the main transaction is rolled backed. You can use LISTEN/NOTIFY or PGQ that both know how to handle that semantics. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dmitri: Thanks for the response. I am resigned to just patch each major release with my own pile of hook code that I can quickly graft into the code base, currently grafting 9.2.0. My strategy is to let the PG code base float around with all the work of the fine hackers on this list, maybe debate a couple of things with some friendly types, but really just put my effort into logic piled into external replication daemon which will NOT change due to anything in the PG core. If one day, the code base actually feeds me the event information I need, maybe I will change it. And I have not seen anybody request my hook code but a few have responded that the are working on things in the code base, release date unknown. Cheers, marco On 9/24/2012 10:20 AM, Dimitri Fontaine wrote: > "md@rpzdesign.com" <md@rpzdesign.com> writes: >> You may want to consider changing the command TRIGGER into a command FILTER >> and possibly post processing TRIGGER that >> is determined to be called INSIDE the FILTER. Or some way to pass >> information between the FILTER and the post processing trigger. > The only current "event" supported by the system is the > "ddl_command_start" one. We mean to add some more, and triggers wanting > to communicate data in between "ddl_command_start" and "ddl_command_end" > (for example) will have to use something like a table. > >> Also, something information as to whether a series of statements was ROLLED >> BACK would be helpful. > Event Triggers are not an autonomous transaction: any effect they have > in the database is rolled-backed when the main transaction is rolled > backed. You can use LISTEN/NOTIFY or PGQ that both know how to handle > that semantics. > > Regards,
Excerpts from md@rpzdesign.com's message of lun sep 24 14:24:31 -0300 2012: > And I have not seen anybody request my hook code but a few have > responded that the are working > on things in the code base, release date unknown. Well, typically that's not how our development works -- people here don't *request* your changes. Instead, you submit them for inclusion, people here criticize them a lot, it morphs from feedback (if you have the energy and a strong enough skin), and eventually after much pain and many tears it gets committed. The result is typically much more solid than whatever you can build in your own dark corner, but of course it takes longer. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services