Thread: Triggers and Multiple Schemas.

Triggers and Multiple Schemas.

From

"Paul Newman"

Date:

08 March 2006, 18:56:19

Hi,

We run with multiple identical schemas in our db. Each schema actually represents a clients db. What we’d like to do is have a common schema where trigger functions and the like are held whilst each trigger defined against the tables is in there own particular schema. This would mean that there is one function per trigger type to maintain.

However at the moment we are placing the trigger functions within each schema along with trigger itself. The reason is that we don’t know of a function or a variable that says “Give me the schema of the trigger that is calling this function”. We are therefore having to write the function into every schema and then use set search_path =br1; as the first line. This is a real headache to us since we are intending on putting 200 – 300 schemas in one db.

My question is … is there such a function or variable ? …. Or is there a better for us to achieve this ?

Regards

Paul Newman

Re: Triggers and Multiple Schemas.

From

Louis Gonzales

Date:

08 March 2006, 19:19:55

Paul Newman wrote:
<blockquote
 cite="midD5F7521105A39145BEA6A6F47AEFFA8837B9BE@sbserver.tripoint.local"
 type="cite">



<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
    {margin:0cm;
    margin-bottom:.0001pt;
    font-size:12.0pt;
    font-family:"Times New Roman";}
a:link, span.MsoHyperlink
    {color:blue;
    text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
    {color:purple;
    text-decoration:underline;}
span.EmailStyle17
    {mso-style-type:personal-compose;
    font-family:Arial;
    color:windowtext;}
@page Section1
    {size:612.0pt 792.0pt;
    margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
    {page:Section1;}
-->


  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Hi,
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> We run with
multiple identical schemas in our db.
Each schema actually represents a clients db. What we’d like to do is
have a common schema where trigger functions and the like are held
whilst each
trigger defined against the tables is in there own particular schema.
This would
mean that there is one function per trigger type to maintain.
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">However at
the moment we are placing the trigger
functions within each schema along with trigger itself. The reason is
that we don’t
know of a function or a variable that says “Give me the schema of the
trigger that is calling this function”. We are therefore having to
write
the function into every schema and then use set search_path =br1; as
the first
line. This is a real headache to us since we are intending on putting
200 –
300 schemas in one db.
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">My question
is … is there such a function or
variable ?  …. Or is there a better for us to achieve this ?
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Regards
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB"> 
  <span
 style="font-size: 10pt; font-family: Arial;" lang="EN-GB">Paul Newman


Paul,
When you say "multiple identical schemas" are they all separate
explicit schemas?  Or are they all under a general 'public' schema.
From my understanding, when you create a new db instance, it's under
the public level schema by default unless you create an explicit schema
and subsequently a db instance - or several - therein, effectively
establishing sibling db instances belonging to a single schema, I know
at least that data in the form of table access is allowed across the
siblings.  I'd also assume that this would be the case for triggers and
functions that could be identified or defined at the 'root' level
schema.

Now I'm sure there is associated jargon with this type of hierarchical
or tiered schema layout, so please don't anybody shoot me because of my
analogy to 'root' level scenario.

I think this is a great opportunity for somebody to add additional
insight with their experience with utilizing explicit schemas, rather
than the default public schema.

We have to remember, that for every database instance, there is at
least one schema to which it belongs, meaning that a schema and is a db
container of sorts, there can be many database instances that exist in
1 schema to - typically public by default.

I know I'm opening up a big can of worms... but hey... let's have it ;)

Re: Triggers and Multiple Schemas.

From

Scott Marlowe

Date:

08 March 2006, 19:22:05

On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:

> >
> Paul,
> When you say "multiple identical schemas" are they all separate
> explicit schemas?  Or are they all under a general 'public' schema.
> From my understanding, when you create a new db instance, it's under
> the public level schema by default unless you create an explicit
> schema and subsequently a db instance - or several - therein,
> effectively establishing sibling db instances belonging to a single
> schema, I know at least that data in the form of table access is
> allowed across the siblings.  I'd also assume that this would be the
> case for triggers and functions that could be identified or defined at
> the 'root' level

Ummm.  In PostgreSQL schemas are contained within databases, not the
other way around.  It's cluster contains databases contains schemas
contains objects (tables, sequences, indexes, et. al.)

Re: Triggers and Multiple Schemas.

From

Louis Gonzales

Date:

08 March 2006, 19:32:27

Scott Marlowe wrote:
<blockquote cite="mid1141849323.6249.9.camel@state.g2switchworks.com"
 type="cite">
  On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:



    Paul,
When you say "multiple identical schemas" are they all separate
explicit schemas?  Or are they all under a general 'public' schema.
From my understanding, when you create a new db instance, it's under
the public level schema by default unless you create an explicit
schema and subsequently a db instance - or several - therein,
effectively establishing sibling db instances belonging to a single
schema, I know at least that data in the form of table access is
allowed across the siblings.  I'd also assume that this would be the
case for triggers and functions that could be identified or defined at
the 'root' level



Ummm.  In PostgreSQL schemas are contained within databases, not the
other way around.  It's cluster contains databases contains schemas
contains objects (tables, sequences, indexes, et. al.)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


I stand corrected.  That's right.  But under a database you create your
explicit schemas, to organize tables which constitute your separate
data, where all of the schemas belonging to a database instance, can
share resources without conflicting with one another.

I apologize for giving the inaccurate description of database to schema
relationship.

Re: Triggers and Multiple Schemas.

From

Scott Marlowe

Date:

08 March 2006, 19:34:20

On Wed, 2006-03-08 at 14:32, Louis Gonzales wrote:
> Scott Marlowe wrote:
> > On Wed, 2006-03-08 at 14:19, Louis Gonzales wrote:
> >
> >
> > > Paul,
> > > When you say "multiple identical schemas" are they all separate
> > > explicit schemas?  Or are they all under a general 'public' schema.
> > > From my understanding, when you create a new db instance, it's under
> > > the public level schema by default unless you create an explicit
> > > schema and subsequently a db instance - or several - therein,
> > > effectively establishing sibling db instances belonging to a single
> > > schema, I know at least that data in the form of table access is
> > > allowed across the siblings.  I'd also assume that this would be the
> > > case for triggers and functions that could be identified or defined at
> > > the 'root' level
> > >
> > Ummm.  In PostgreSQL schemas are contained within databases, not the
> > other way around.  It's cluster contains databases contains schemas
> > contains objects (tables, sequences, indexes, et. al.)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
> I stand corrected.  That's right.  But under a database you create
> your explicit schemas, to organize tables which constitute your
> separate data, where all of the schemas belonging to a database
> instance, can share resources without conflicting with one another.
>
> I apologize for giving the inaccurate description of database to
> schema relationship.

Heck, ya just got a couple terms crossed up.  No biggie.

And yes, what the OP wanted to do should work.  You just need to apply
the triggers to each schema's table individually.

I'd suggest scripting the whole thing in bash, perl, or php for easy
maintenance.

Re: Triggers and Multiple Schemas.

From

Michael Fuhr

Date:

08 March 2006, 19:41:51

On Tue, Mar 07, 2006 at 06:34:33AM -0000, Paul Newman wrote:
> However at the moment we are placing the trigger functions within each
> schema along with trigger itself. The reason is that we don't know of a
> function or a variable that says "Give me the schema of the trigger that
> is calling this function".

PL/pgSQL triggers receive the table's oid in TG_RELID.  You could
query pg_class and join to pg_namespace to get the table's schema
name.  Is that what you're looking for?

--
Michael Fuhr

Re: Triggers and Multiple Schemas.

From

Louis Gonzales

Date:

08 March 2006, 19:43:02

Paul,
What is the current schema layout for your db instances?  I don't think
it's possible to share across db instances like this:

dbname1.myschema.sometable
dbname2.myschema.sometable

But you can share resources of the following type:

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

I think that it's a mis-statement to call each separate schema a DB, but
the group of:
dbname.myschema2.(collection of objects) is effectively a separate DB,
in that, the tables are what constitute a functional db.

so you can treat
dbname.myschema1.(...)
and
dbname.myschema2.(...)
as separate databases that share common resources, because they belong
to the same db instances, namely "dbname"

Attachment

louis.gonzales.vcf

Re: Triggers and Multiple Schemas.

From

Richard Huxton

Date:

08 March 2006, 19:43:55

Paul Newman wrote:
> Hi,
>
>  We run with multiple identical schemas in our db. Each schema actually
> represents a clients db. What we'd like to do is have a common schema
> where trigger functions and the like are held whilst each trigger
> defined against the tables is in there own particular schema. This would
> mean that there is one function per trigger type to maintain.
>
>
>
> However at the moment we are placing the trigger functions within each
> schema along with trigger itself. The reason is that we don't know of a
> function or a variable that says "Give me the schema of the trigger that
> is calling this function".

You can pass a parameter into the function from the trigger definition.
That's probably the easiest way. In plpgsql, parameters appear in
TG_ARGV[]. Or, you could reverse-engineer the schema-name from TG_RELID.

http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html

HTH
--
   Richard Huxton
   Archonet Ltd

Re: Triggers and Multiple Schemas.

From

"Paul Newman"

Date:

08 March 2006, 22:19:07

Hi,
Yes my db is indeed like

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

Physically all data is in one db .. however each client has there own
schema (or virtual db). Each client schema has identical structure. And
a number of tables have triggers that are identical in each schema. My
problem at the moment is that I also define the trigger functions in
each schema. This is a complete nightmare to maintain in our case since
we will be very rapidly introducing upto about 400 identical schemas
into a single db.

The reason we are doing this is to have resource and connection pooling
(therefore scalability) for many of our clients who run our system.

So how can I get the schema name of the calling table trigger and use it
in the form of set Search_path at the beginning of the function ?

Regards

Paul Newman

-----Original Message-----
From: Louis Gonzales [mailto:louis.gonzales@linuxlouis.net]
Sent: 08 March 2006 20:43
To: Scott Marlowe
Cc: Paul Newman; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.

Paul,
What is the current schema layout for your db instances?  I don't think
it's possible to share across db instances like this:

dbname1.myschema.sometable
dbname2.myschema.sometable

But you can share resources of the following type:

dbname.myschema1.sometable
dbname.myschema2.sometable
dbname.myschema2.sometable2
dbname.myschema2.sometable3

I think that it's a mis-statement to call each separate schema a DB, but

the group of:
dbname.myschema2.(collection of objects) is effectively a separate DB,
in that, the tables are what constitute a functional db.

so you can treat
dbname.myschema1.(...)
and
dbname.myschema2.(...)
as separate databases that share common resources, because they belong
to the same db instances, namely "dbname"

Re: Triggers and Multiple Schemas.

From

Michael Fuhr

Date:

08 March 2006, 22:48:14

On Wed, Mar 08, 2006 at 11:16:55PM -0000, Paul Newman wrote:
> So how can I get the schema name of the calling table trigger and use it
> in the form of set Search_path at the beginning of the function ?

Here's an example:

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
DECLARE
    schemaname  text;
    oldpath     text;
BEGIN
    SELECT INTO schemaname n.nspname
      FROM pg_namespace AS n
      JOIN pg_class AS c ON c.relnamespace = n.oid
      WHERE c.oid = TG_RELID;

    oldpath := current_setting('search_path');

    PERFORM set_config('search_path', schemaname, true);
    RAISE INFO 'schema = %  oldpath = %', schemaname, oldpath;
    PERFORM set_config('search_path', oldpath, false);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA foo;
CREATE SCHEMA bar;

CREATE TABLE foo.tablename (id integer);
CREATE TABLE bar.tablename (id integer);

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

CREATE TRIGGER bartrig BEFORE INSERT OR UPDATE ON bar.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();


Now let's insert some records:

test=> INSERT INTO foo.tablename VALUES (1);
INFO:  schema = foo  oldpath = public
INSERT 0 1

test=> INSERT INTO bar.tablename VALUES (2);
INFO:  schema = bar  oldpath = public
INSERT 0 1

--
Michael Fuhr

Re: Triggers and Multiple Schemas.

From

"Paul Newman"

Date:

09 March 2006, 04:59:32

Hi Michael,

Haven't tried it yet .. but THANK YOU !
I will try it later today .... assuming it works it will say us a LOT of
maintenance!

Regards

Paul Newman

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 08 March 2006 23:48
To: Paul Newman
Cc: Louis Gonzales; Scott Marlowe; pgsql general
Subject: Re: [GENERAL] Triggers and Multiple Schemas.

On Wed, Mar 08, 2006 at 11:16:55PM -0000, Paul Newman wrote:
> So how can I get the schema name of the calling table trigger and use
it
> in the form of set Search_path at the beginning of the function ?

Here's an example:

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
DECLARE
    schemaname  text;
    oldpath     text;
BEGIN
    SELECT INTO schemaname n.nspname
      FROM pg_namespace AS n
      JOIN pg_class AS c ON c.relnamespace = n.oid
      WHERE c.oid = TG_RELID;

    oldpath := current_setting('search_path');

    PERFORM set_config('search_path', schemaname, true);
    RAISE INFO 'schema = %  oldpath = %', schemaname, oldpath;
    PERFORM set_config('search_path', oldpath, false);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA foo;
CREATE SCHEMA bar;

CREATE TABLE foo.tablename (id integer);
CREATE TABLE bar.tablename (id integer);

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

CREATE TRIGGER bartrig BEFORE INSERT OR UPDATE ON bar.tablename
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();


Now let's insert some records:

test=> INSERT INTO foo.tablename VALUES (1);
INFO:  schema = foo  oldpath = public
INSERT 0 1

test=> INSERT INTO bar.tablename VALUES (2);
INFO:  schema = bar  oldpath = public
INSERT 0 1

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match