Re: [HACKERS] External Replication
m...@rpzdesign.com m...@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 -- 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] External Replication
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: m...@rpzdesign.com m...@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, -- 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] External Replication
Excerpts from m...@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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] External Replication
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 commands become ALTER TABLE RENAME to SIGy All DROP TABLE Sig commands are not changed so that the SIG tables actually drop. (Almost like a TRASH Can for postgresql!) ALL TRUNCATE TABLE Become ALTER TABLE RENAME to SIGy AND THEN CREATE TABLE SELECT AS SIG 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 m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] External Replication
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, m...@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 -- 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] External Replication
On Friday, September 21, 2012 08:12:26 PM m...@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 -- 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] External Replication
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 m...@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 -- 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] External Replication
Hi, On Friday, September 21, 2012 11:06:46 PM m...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
