Search Path vs Synonyms

Matthew Seaborn wrote:

Given the situation where a user connecting to the database
needs access to two separate schemas: the primary schema
which contains the data they will be updating and a second
schema which contains read-only reference data, used by many
users, that will be using in joins on queries.

I don't want to have to use fully qualified names (I am
migrating code from Oracle which uses synonyms), so what is
the best way (in both performance and reliability) to refer
to the tables; Search Path or Synonyms?

There are no synonyms in PostgreSQL: synonyms are Oracle's way
of search_path (though more selective).

You excluded the best solution, namely to qualify the objects.

If your user should be able to access *all* tables in both
schemas unqualified and there are no name collisions between
objects in the schemas, I would recommend search_path.

Otherwise, use views.

Yours,
Laurenz Albe

Ah, I am actually using EnterpriseDB (a commercial 'fork' of PSQL) which does have synonyms.

Is it possible set define the default search_path for a given user?

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: 24 July 2009 07:22
To: Matthew Seaborn; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Search Path vs Synonyms

Matthew Seaborn wrote:

Given the situation where a user connecting to the database
needs access to two separate schemas: the primary schema
which contains the data they will be updating and a second
schema which contains read-only reference data, used by many
users, that will be using in joins on queries.

I don't want to have to use fully qualified names (I am
migrating code from Oracle which uses synonyms), so what is
the best way (in both performance and reliability) to refer
to the tables; Search Path or Synonyms?

There are no synonyms in PostgreSQL: synonyms are Oracle's way
of search_path (though more selective).

You excluded the best solution, namely to qualify the objects.

If your user should be able to access *all* tables in both
schemas unqualified and there are no name collisions between
objects in the schemas, I would recommend search_path.

Otherwise, use views.

Yours,
Laurenz Albe

________________________________________________________________________

CONFIDENTIALITY - This email and any files transmitted with it, are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If this has come to you in error, you must not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately and delete them from your system.

SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating with Perform Group by email you consent to us monitoring and reading any such correspondence.

VIRUSES - Although this email message has been scanned for the presence of computer viruses, the sender accepts no liability for any damage sustained as a result of a computer virus and it is the recipient�s responsibility to ensure that email is virus free.

AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily represent those of Perform Group.

COPYRIGHT - Copyright of this email and any attachments belongs to Perform Group, Companies House Registration number 6324278.

Matthew Seaborn wrote:

Ah, I am actually using EnterpriseDB (a commercial 'fork' of PSQL) which does have synonyms.

then you likely will get more help from EnterpriseDB's technical
support, as only they are really familiar with their proprietary and
commercial product.

On Fri, Jul 24, 2009 at 09:38, Matthew
Seaborn<Matthew.Seaborn@performgroup.com> wrote:

Ah, I am actually using EnterpriseDB (a commercial 'fork' of PSQL) which does have synonyms.

For support on EnterpriseDB you should contact EnterpriseDB, not the
PostgreSQL community. We can only answer about the opensource product.

Is it possible set define the default search_path for a given user?

In the community version you can. I don't know if this works in
EnterpriseDB. You'd just use
ALTER USER userid SET search_path='schema1,schema2'

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Whilst I need EDB for a few of their features, I am keen to keep as PSQL compliant as possible.

Thanks for the help

ALTER USER userid SET search_path TO schema1,schema2;

worked nicely.

-----Original Message-----
From: Magnus Hagander [mailto:magnus@hagander.net]
Sent: 24 July 2009 08:57
To: Matthew Seaborn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Search Path vs Synonyms

On Fri, Jul 24, 2009 at 09:38, Matthew
Seaborn<Matthew.Seaborn@performgroup.com> wrote:

Ah, I am actually using EnterpriseDB (a commercial 'fork' of PSQL) which does have synonyms.

For support on EnterpriseDB you should contact EnterpriseDB, not the
PostgreSQL community. We can only answer about the opensource product.

Is it possible set define the default search_path for a given user?

In the community version you can. I don't know if this works in
EnterpriseDB. You'd just use
ALTER USER userid SET search_path='schema1,schema2'

--
Magnus Hagander
Self: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

________________________________________________________________________

CONFIDENTIALITY - This email and any files transmitted with it, are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If this has come to you in error, you must not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately and delete them from your system.

SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating with Perform Group by email you consent to us monitoring and reading any such correspondence.

VIRUSES - Although this email message has been scanned for the presence of computer viruses, the sender accepts no liability for any damage sustained as a result of a computer virus and it is the recipient�s responsibility to ensure that email is virus free.

AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily represent those of Perform Group.

COPYRIGHT - Copyright of this email and any attachments belongs to Perform Group, Companies House Registration number 6324278.