Re: Querying the same column and table across schemas

On Fri, 2010-03-05 at 17:39 -0500, John A. Sullivan III wrote:
> On Fri, 2010-03-05 at 14:13 -0800, Bob Lunney wrote:
> >
> > --- On Fri, 3/5/10, John A. Sullivan III <jsullivan(at)opensourcedevel(dot)com> wrote:
> >
> > > From: John A. Sullivan III <jsullivan(at)opensourcedevel(dot)com>
> > > Subject: [ADMIN] Querying the same column and table across schemas
> > > To: pgsql-admin(at)postgresql(dot)org
> > > Date: Friday, March 5, 2010, 2:44 PM
> > > Hello, all. I'm working on a
> > > project using the X2Go terminal server
> > > project (www.x2go.org). They record session data in a
> > > postgresql
> > > database. Our environment is a little more secure
> > > than typical and we
> > > do not want it possible for one user to see another's
> > > session data. We
> > > thus have divided the session database into schemas each
> > > with an
> > > identical set of tables. Each user only writes and
> > > reads from their
> > > schema.
> > >
> > > However, we need to query all schemas as if they were
> > > one. Is there a
> > > way to do that?
> > >
> > > In other words, if we were a single schema database, we
> > > could do
> > >
> > > select session_id from sessions;
> > >
> > > to list all sessions. How can we accomplish the same
> > > thing to list all
> > > the sessions across all the schemas in a single query?
> > >
> > > I'm trying to avoid making a thousand call like
> > >
> > > select user1.session_id from user1.sessions;
> > >
> > > when I could do it in a single query especially since the
> > > database is
> > > remote and secured with SSL.
> > >
> > > Thanks - John
> > >
> >
> > John,
> >
> > How about creating a central admin schema and putting a trigger on all the sessions tables to write changes to the central admin schema's session table? The function could belong to the admin role and run with definer's security.
> >
> > Bob
> >
> >
> >
> >
> That sounds ideal and perhaps lower overhead than rerunning the view
> build query every time I query the view but, being a database ignoramus,
> it is quite beyond my skills :-( If I can scrape up the time, I'll do
> some research on triggers and functions. I have used functions before
> but never triggers. Thanks - John
>
>
After thinking about it quite a while, the advantages seemed to be so
great for doing this considering we are anticipating hundreds of
schemas, we jumped in and gave it a try. The following seems to be
working find after creating a postgres schema, the needed sessions table
inside that schema, and adding plpgsql language to the cluster:

CREATE OR REPLACE FUNCTION public.syncschema() RETURNS trigger AS '
DECLARE old_path TEXT;
BEGIN
-- Save old search_path; notice we must qualify current_setting
-- to ensure we invoke the right function
old_path := pg_catalog.current_setting(''search_path'');
-- Set a secure search_path: trusted schemas, then pg_temp.
-- We set is_local = true so that the old value will be restored
-- in event of an error before we reach the function end.
PERFORM pg_catalog.set_config(''search_path'', ''postgres, pg_temp'', true);
IF tg_op = ''INSERT'' THEN
insert into postgres.sessions (session_id,display,uname,server,client,status,init_time,last_time,cookie,agent_pid,gr_port,sound_port,fs_port) values (new.session_id,new.display,new.uname,new.server,new.client,new.status,new.init_time,new.last_time,new.cookie,new.agent_pid,new.gr_port,new.sound_port,new.fs_port);
ELSEIF tg_op = ''DELETE'' THEN
delete from postgres.sessions where session_id=old.session_id;
ELSEIF tg_op = ''UPDATE'' THEN
update postgres.sessions set display=new.display,uname=new.uname,server=new.server,client=new.client,status=new.status,init_time=new.init_time,last_time=new.last_time,cookie=new.cookie,agent_pid=new.agent_pid,gr_port=new.gr_port,sound_port=new.sound_port,fs_port=new.fs_port where session_id=old.session_id;
END IF;
-- Restore the search_path of the caller
PERFORM pg_catalog.set_config(''search_path'', old_path, true);
RETURN NULL;
END ' LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER syncschema AFTER INSERT OR DELETE OR UPDATE ON <schema name>.sessions FOR EACH ROW EXECUTE PROCEDURE public.syncschema();

Thanks very much - John