Triggers and Multiple Schemas.

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.)

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

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.

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

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"

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"

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

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