Re: [GENERAL] What is exactly a schema?

[marcelo]

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

Re: [GENERAL] What is exactly a schema?

[John R Pierce]

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

Re: [GENERAL] What is exactly a schema?

[David G. Johnston]

On Fri, Jul 14, 2017 at 9:13 AM, marcelo  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.
​

Re: [GENERAL] What is exactly a schema?

[David G. Johnston]

On Fri, Jul 14, 2017 at 9:01 AM, Jerry Sievers 
wrote:

> marcelo  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.

Re: [GENERAL] What is exactly a schema?

[marcelo]

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

Re: [GENERAL] What is exactly a schema?

[Jerry Sievers]

marcelo  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.consult...@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

Re: [GENERAL] What is exactly a schema?

[Thomas Kellerer]

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

Re: [GENERAL] What is exactly a schema?

[marcelo]

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  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

Re: [GENERAL] What is exactly a schema?

[Bill Moran]

On Fri, 14 Jul 2017 08:59:13 -0300
marcelo  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 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What is exactly a schema?

[marcelo]

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

Re: [GENERAL] What is exactly a schema?

[Berend Tober]

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

Re: [GENERAL] What is exactly a schema?

[Michael Paquier]

On Fri, Jul 14, 2017 at 12:00 PM, 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?

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