Обсуждение: [GENERAL] What is exactly a schema?
The question is not trivial. Could I maintain two or three separate/distinct "versions" of same database using one schema for every of them? Could some tables (in the public schema) be shared among all the schemas?
On Fri, Jul 14, 2017 at 12:00 PM, marcelo <marcelo.nicolet@gmail.com> wrote: > The question is not trivial. Could I maintain two or three separate/distinct > "versions" of same database using one schema for every of them? > Could some tables (in the public schema) be shared among all the schemas? The documentation gives a good starting point: https://www.postgresql.org/docs/9.6/static/ddl-schemas.html -- Michael
marcelo wrote: > The question is not trivial. Could I maintain two or three separate/distinct "versions" of same > database using one schema for every of them? > Could some tables (in the public schema) be shared among all the schemas? > > Yes and yes. In the Postgresql world, the word "schema" is maybe unfortunately overloaded, but whenever you read it think "namespace". In fact, in the systems catalog there are columns named "namespace" that store data referring to named schemas. -- B
Thank you. Now I have a related question. Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? I'm asking this because I will be using Devart's dotConnect and Entity developer to access the database. I have not full control, so I cannot set the search path immediately after the connection. If the first example is possible, I will replace the schema name on the fly, before connection attempt. TIA On 14/07/17 07:58, Berend Tober wrote: > marcelo wrote: >> The question is not trivial. Could I maintain two or three >> separate/distinct "versions" of same >> database using one schema for every of them? >> Could some tables (in the public schema) be shared among all the >> schemas? >> >> > > Yes and yes. In the Postgresql world, the word "schema" is maybe > unfortunately overloaded, but whenever you read it think "namespace". > In fact, in the systems catalog there are columns named "namespace" > that store data referring to named schemas. > > -- B > > >
On Fri, 14 Jul 2017 08:59:13 -0300 marcelo <marcelo.nicolet@gmail.com> wrote: > Thank you. > Now I have a related question. > Could I select a specific schema in the connection string? Say, by > example database=mydb.schemanumbertwo ? > > I'm asking this because I will be using Devart's dotConnect and Entity > developer to access the database. I have not full control, so I cannot > set the search path immediately after the connection. > > If the first example is possible, I will replace the schema name on the > fly, before connection attempt. I don't think you can do exactly what you're asking. However, you should be able to achieve the same result by setting a default schema for the user that you're connecting as. See the docs for ALTER ROLE and SET. -- Bill Moran <wmoran@potentialtech.com>
Thank you. I know that. It would be my last resort, because aside, I need that every app user must login to be able to assign logical privileges at the app level. Of course, I will have my own tables of users and roles, independently of the postgres users an roles. I will think of it. On 14/07/17 09:19, Bill Moran wrote: > On Fri, 14 Jul 2017 08:59:13 -0300 > marcelo <marcelo.nicolet@gmail.com> wrote: > >> Thank you. >> Now I have a related question. >> Could I select a specific schema in the connection string? Say, by >> example database=mydb.schemanumbertwo ? >> >> I'm asking this because I will be using Devart's dotConnect and Entity >> developer to access the database. I have not full control, so I cannot >> set the search path immediately after the connection. >> >> If the first example is possible, I will replace the schema name on the >> fly, before connection attempt. > I don't think you can do exactly what you're asking. However, you should > be able to achieve the same result by setting a default schema for the > user that you're connecting as. See the docs for ALTER ROLE and SET. >
marcelo schrieb am 14.07.2017 um 13:59: > Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? The JDBC driver does indeed support that: jdbc:postgresql://localhost/somedatabase?currentSchema=some_schema I think in the backround it then simply runs a set search_path = some_schema; after the connection has been established.
marcelo <marcelo.nicolet@gmail.com> writes: > Thank you. > Now I have a related question. > Could I select a specific schema in the connection string? Say, by > example database=mydb.schemanumbertwo ? > > I'm asking this because I will be using Devart's dotConnect and Entity > developer to access the database. I have not full control, so I cannot > set the search path immediately after the connection. Take a look at the PGOPTIONS env variable an/or libpq connect parameter variant. > > If the first example is possible, I will replace the schema name on > the fly, before connection attempt. > > TIA > > On 14/07/17 07:58, Berend Tober wrote: >> marcelo wrote: >>> The question is not trivial. Could I maintain two or three >>> separate/distinct "versions" of same >>> database using one schema for every of them? >>> Could some tables (in the public schema) be shared among all the >>> schemas? >>> >>> >> >> Yes and yes. In the Postgresql world, the word "schema" is maybe >> unfortunately overloaded, but whenever you read it think >> "namespace". In fact, in the systems catalog there are columns named >> "namespace" that store data referring to named schemas. >> >> -- B >> >> >> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
I'll be using Devart's dotConnect. I have two alternatives at this moment a) To set the user name to the required schema. This has the (little) drawback that forces user configuration for every schema... b) To manually do something like the JDBC driver you mention, but it triggers some questions b.1) To execute the set search_path one must be connected, database name included. I think by that time, the default schema is determined. Or I am wrong, am I? b.2) The search_path is valid for the database or restricted to the connection? Thank you On 14/07/17 09:59, Thomas Kellerer wrote: > marcelo schrieb am 14.07.2017 um 13:59: >> Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? > The JDBC driver does indeed support that: > > jdbc:postgresql://localhost/somedatabase?currentSchema=some_schema > > I think in the backround it then simply runs a > > set search_path = some_schema; > > after the connection has been established. > > > >
marcelo <marcelo.nicolet@gmail.com> writes:
> Thank you.
> Now I have a related question.
> Could I select a specific schema in the connection string? Say, by
> example database=mydb.schemanumbertwo ?
>
> I'm asking this because I will be using Devart's dotConnect and Entity
> developer to access the database. I have not full control, so I cannot
> set the search path immediately after the connection.
Take a look at the PGOPTIONS env variable an/or libpq connect parameter variant.
Yes, it can be done, but doing so precludes the use connection pooling technology (at least pg_bouncer, anyway, I suspect others).
David J.
I'll be using Devart's dotConnect. I have two alternatives at this moment
a) To set the user name to the required schema. This has the (little) drawback that forces user configuration for every schema...
With "ALTER ROLE SET" the user name and initial search_path are both explicitly specified and do not need to match...
b) To manually do something like the JDBC driver you mention, but it triggers some questions
b.1) To execute the set search_path one must be connected, database name included. I think by that time, the default schema is determined. Or I am wrong, am I?
You are mistaken. When doing it at the connection-level an actual "SET search_path" SQL command is not generated - instead the server simply uses the data present in the connection string to change runtime variables before the connection is made ready for use.
b.2) The search_path is valid for the database or restricted to the connection?
You need to rephrase this question. From a client's perspective everything is restricted to the connection - and a given connection only exists to a single database at any given time.
And if you can avoid top-posting it would be much appreciated.
David J.
On 7/14/2017 4:59 AM, marcelo wrote: > Now I have a related question. > Could I select a specific schema in the connection string? Say, by > example database=mydb.schemanumbertwo ? the default search_path is $user,public so if you connect with different SQL usernames for your different schemas, and have all your common tables in PUBLIC, then it will just fall out. you'll need to be careful with permissions, of course. -- john r pierce, recycling bits in santa cruz
I'm sorry. dotConnect for PostgreSql is able to set the schema at connection time. This may be set as part of the connection string, or as a dbconnection class' property. i was in doubt because the version I'm using is somewhat old, but decompiling it shows the property in place. So, I will close this thread. Thanks to all who answered. I acquired some new knowledge. On 14/07/17 13:50, John R Pierce wrote: > On 7/14/2017 4:59 AM, marcelo wrote: >> Now I have a related question. >> Could I select a specific schema in the connection string? Say, by >> example database=mydb.schemanumbertwo ? > > the default search_path is $user,public > > so if you connect with different SQL usernames for your different > schemas, and have all your common tables in PUBLIC, then it will just > fall out. you'll need to be careful with permissions, of course. > >