Обсуждение: Search Path vs Synonyms
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?
Thanks.
________________________________________________________________________
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: > 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 intendedsolely for the use of the individual or entity to whom they are addressed. If this has come to you in error, youmust not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately anddelete them from your system. SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating withPerform 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 liabilityfor any damage sustained as a result of a computer virus and it is the recipient�s responsibility to ensure thatemail is virus free. AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily representthose 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 intendedsolely for the use of the individual or entity to whom they are addressed. If this has come to you in error, youmust not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately anddelete them from your system. SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating withPerform 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 liabilityfor any damage sustained as a result of a computer virus and it is the recipient�s responsibility to ensure thatemail is virus free. AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily representthose of Perform Group. COPYRIGHT - Copyright of this email and any attachments belongs to Perform Group, Companies House Registration number 6324278.