Re: External Replication

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(at)rpzdesign(dot)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(at)rpzdesign(dot)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(at)rpzdesign(dot)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(at)rpzdesign(dot)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(at)rpzdesign(dot)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(at)rpzdesign(dot)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(at)rpzdesign(dot)com" <md(at)rpzdesign(dot)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(at)rpzdesign(dot)com" <md(at)rpzdesign(dot)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(at)rpzdesign(dot)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