Thread: DB & Schema

DB & Schema

From
"md@rpzdesign.com"
Date:
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



Re: DB & Schema

From
Heikki Linnakangas
Date:
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



External Replication

From
"md@rpzdesign.com"
Date:
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
>
>




Re: External Replication

From
Andres Freund
Date:
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



Re: External Replication

From
"md@rpzdesign.com"
Date:
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
>




Re: External Replication

From
Andres Freund
Date:
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



Re: External Replication

From
"md@rpzdesign.com"
Date:
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




Re: External Replication

From
Dimitri Fontaine
Date:
"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



Re: External Replication

From
"md@rpzdesign.com"
Date:
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,




Re: External Replication

From
Alvaro Herrera
Date:
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