Just Someone (just...@gmail.com)

Cool!

That explains it fully. So i guess there will be a better performance
to the pre-generated views at the price of more views.

Thanks!

I have a database with multiple schemas all with the same structure
(but of course different data...).

I want to create a view that will be created in a shared schema, and
when executed will be executed against the current schema. Whenever I
try it, it seems the view is linked to a specific schema used when
creating it, and doesn't reevaluates based on the current schema.

Here is the pseudo structure/code:

schema1:
=======
create table t1 ...

schema2:
=======
create table t1 ...

shared_schema:
============
create table t3 ...

create the view:
===========
set search_path to shared_schema, schema1;
create view view1 as select * from t1;

try the view:
========
set search_path to shared_schema, schema1;
select * from view1;
set search_path to shared_schema, schema2;
select * from view1;

Results:
======
In the above, both select * from view1; will return the same data,
though the search path changed.

Is there a way to make the view use the current search_path?

I am using a similar solution, and I tested it with a test containing
20K+ different schemas. Postgres didn't show slowness at all even
after the 20K (over 2 million total tables) were created. So I have
feeling it can grow even more.

Guy.

If you rather keep SELinux on, you can still set the SELinux context
on the directory where you want the tablespaces to one postgres will
like.

To find what is the permissions you need, you can use ls -Z. It will
list the SELinux context. Check /var/lib/pgsql/data (or wherever
postgres data is pointing to), and then set this same permission on
the target dir using chcon.

For example, on my FC4 system all subdirectories on the data directory have:
root:object_r:postgresql_db_t or user_u:object_r:postgresql_db_t

So if you want to chage /path/to/foo/which/is/not/under/pgdata, run
(as root or sudo):

chcon root:object_r:postgresql_db_t /path/to/foo/which/is/not/under/pgdata

This way postgres can access it, and you get the SELinux security.

Bye,

Guy.

http://www.famundo.com
http://devblog.famundo.com
- Hide quoted text -

The schema can change, but I rather not.

The use case is a web app where you can tag items with tags
(many-2-many). There are multiple items you can tag: contacts,
schedules, lists, etc... And then you can search and categorize by
tags. The standard for this if you look aroung the web is to retrieve
the tagged records with any of the tags you select. Effectively an OR
query.

What I'm trying to do is search for items matching multiple tags at
the same time - and AND query. So that I can bring up all contacts
that are tagged with friends and movie-lovers.

Hope that clears it up a bit...

Guy.