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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jul 14, 2017 at 9:01 AM, Jerry Sievers <gsievers19@comcast.net>
wrote:
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.
On Fri, Jul 14, 2017 at 9:13 AM, marcelo <marcelo.nicolet@gmail.com> wrote:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general