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@opensourcedevel.com> wrote:
> >
> > > From: John A. Sullivan III <jsullivan@opensourcedevel.com>
> > > Subject: [ADMIN] Querying the same column and table across schemas
> > > To: pgsql-admin@postgresql.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
centraladmin 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
wheresession_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