Re: [GENERAL] Schemas and serials

[Scott Marlowe]

On Sat, Jul 29, 2017 at 1:17 PM, Melvin Davidson 
wrote:

>
> On Sat, Jul 29, 2017 at 3:38 PM, tel medola  wrote:
>
>> Depends.
>> When you create your tables in new schema, the script was the same from
>> "qa"?
>> Sequences, tables, etc.. belong to the schema where was created.
>>
>> Roberto.
>>
>> Em sáb, 29 de jul de 2017 às 16:17, marcelo 
>> escreveu:
>>
>>> Some days ago I asked regarding tables located in different schemas.
>>> Now, my question is
>>> Suppose I have two schemas (other than public): "qa" and "production".
>>> Initially I create all my tables in "qa". All of them have a primary key
>>> of type serial.
>>> Later, I will copy the tables definitions to production.
>>> It will automatically create the sequences in the new schema, starting
>>> at zero?
>>> TIA
>>> Marcelo
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>
>
>
>
> *Marcelo,>Initially I create all my tables in "qa". All of them have a
> primary key of type serial. >Later, I will copy the tables definitions to
> production.*
>
> *A word of caution, creating tables in a qa "schema" and then transferring
> to production is not the normal/correct (or safe) way to do development.*
>
> *The standard procedure is to create a seperate "qa" database (and/or
> server) with the exact same schema(s) as production. Then, after testing *
>
> *is completed, the schemas/tables are copied to production.*
>
>
This. Staging should look as much like production as possible for testing
purposes. If you HAVE to use the same server (not a good idea) then make
separate clusters. Separate servers here can save you a LOT of heartache
from someone fat fingering a script meant for staging hitting production.

Re: [GENERAL] Schemas and serials

[marcelo]

Addendum:

Some minutes ago, using EMS SQL Manager Lite, I tried what I was asking.
First, I created a new schema. Then, I duplicated some of the 
transactional tables from the public schema, which is acting as a 
definition repository for those tables, to the new "transactional" 
schema. After that, the serial sequence was created in the test schema.
The only caution is to inspect the sql to be executed, checking to which 
schema points every foreign key; the default, obviously, is public. That 
is OK when the FK goes to one of the reference tables; but must be 
changed when it must go to another transactional one. The example would 
be "order" and "order_detail": customer, product, etc must be referenced 
from public, but the FK from order_detail must point to season.order.


So, the question is solved, at least using some "postgresql complaint" tool.

Marcelo

On 29/07/17 17:17, Melvin Davidson wrote:


On Sat, Jul 29, 2017 at 3:38 PM, tel medola > wrote:


Depends.
When you create your tables in new schema, the script was the same
from "qa"?
Sequences, tables, etc.. belong to the schema where was created.

Roberto.

Em sáb, 29 de jul de 2017 às 16:17, marcelo
>
escreveu:

Some days ago I asked regarding tables located in different
schemas.
Now, my question is
Suppose I have two schemas (other than public): "qa" and
"production".
Initially I create all my tables in "qa". All of them have a
primary key
of type serial.
Later, I will copy the tables definitions to production.
It will automatically create the sequences in the new schema,
starting
at zero?
TIA
Marcelo


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



*Marcelo,
>Initially I create all my tables in "qa". All of them have a primary 
key of type serial.

>Later, I will copy the tables definitions to production.

*
*A word of caution, creating tables in a qa "schema" and then 
transferring to production is not the normal/correct (or safe) way to 
do development.

*
*The standard procedure is to create a seperate "qa" database (and/or 
server) with the exact same schema(s) as production. Then, after testing

*
*is completed, the schemas/tables are copied to production.
*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Schemas and serials

[marcelo]

Melvin:

My example was somewhat inexact. The full question is as follows:
I need to have two groups of tables: the "reference" ones (examples: 
city, country, customer) which will "reside" in the public schema, and 
the transaccional ones, which will reside in a schema representing one 
year/season. These table's definitions must be copied to a new schema at 
the start of new year/season.

One of these tables create script could be as follows

/CREATE TABLE dailyprogram//
//(//
//  id serial NOT NULL,//
//  date timestamp without time zone NOT NULL,//
//  packerid integer NOT NULL,//
//  CONSTRAINT "PK_dailyprogram" PRIMARY KEY (id)//
//)//
//WITH (//
//  OIDS=FALSE//
//);//
//ALTER TABLE dailyprogram//
//  OWNER TO postgres;//
/
My reworded question is: if I run this sql in the new schema, the 
implicit '/CREATE SEQUENCE dailyprogram_id_seq;/' statement will be 
executed in the new schema, so the sequence will be reset to zero?


TIA

PS: Of course, I considered the other option: to have a  table 
representing the seasons, and every main transactional table with a 
foreign key to this season table, but it add a level of indirection to a 
database which is now very convoluted.


On 29/07/17 17:17, Melvin Davidson wrote:


On Sat, Jul 29, 2017 at 3:38 PM, tel medola > wrote:


Depends.
When you create your tables in new schema, the script was the same
from "qa"?
Sequences, tables, etc.. belong to the schema where was created.

Roberto.

Em sáb, 29 de jul de 2017 às 16:17, marcelo
>
escreveu:

Some days ago I asked regarding tables located in different
schemas.
Now, my question is
Suppose I have two schemas (other than public): "qa" and
"production".
Initially I create all my tables in "qa". All of them have a
primary key
of type serial.
Later, I will copy the tables definitions to production.
It will automatically create the sequences in the new schema,
starting
at zero?
TIA
Marcelo


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



*Marcelo,
>Initially I create all my tables in "qa". All of them have a primary 
key of type serial.

>Later, I will copy the tables definitions to production.

*
*A word of caution, creating tables in a qa "schema" and then 
transferring to production is not the normal/correct (or safe) way to 
do development.

*
*The standard procedure is to create a seperate "qa" database (and/or 
server) with the exact same schema(s) as production. Then, after testing

*
*is completed, the schemas/tables are copied to production.
*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Schemas and serials

[Melvin Davidson]

On Sat, Jul 29, 2017 at 3:38 PM, tel medola  wrote:

> Depends.
> When you create your tables in new schema, the script was the same from
> "qa"?
> Sequences, tables, etc.. belong to the schema where was created.
>
> Roberto.
>
> Em sáb, 29 de jul de 2017 às 16:17, marcelo 
> escreveu:
>
>> Some days ago I asked regarding tables located in different schemas.
>> Now, my question is
>> Suppose I have two schemas (other than public): "qa" and "production".
>> Initially I create all my tables in "qa". All of them have a primary key
>> of type serial.
>> Later, I will copy the tables definitions to production.
>> It will automatically create the sequences in the new schema, starting
>> at zero?
>> TIA
>> Marcelo
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>




*Marcelo,>Initially I create all my tables in "qa". All of them have a
primary key of type serial. >Later, I will copy the tables definitions to
production.*

*A word of caution, creating tables in a qa "schema" and then transferring
to production is not the normal/correct (or safe) way to do development.*

*The standard procedure is to create a seperate "qa" database (and/or
server) with the exact same schema(s) as production. Then, after testing *

*is completed, the schemas/tables are copied to production.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Schemas and serials

[tel medola]

Depends.
When you create your tables in new schema, the script was the same from
"qa"?
Sequences, tables, etc.. belong to the schema where was created.

Roberto.

Em sáb, 29 de jul de 2017 às 16:17, marcelo 
escreveu:

> Some days ago I asked regarding tables located in different schemas.
> Now, my question is
> Suppose I have two schemas (other than public): "qa" and "production".
> Initially I create all my tables in "qa". All of them have a primary key
> of type serial.
> Later, I will copy the tables definitions to production.
> It will automatically create the sequences in the new schema, starting
> at zero?
> TIA
> Marcelo
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Schemas and serials

[marcelo]

Some days ago I asked regarding tables located in different schemas.
Now, my question is
Suppose I have two schemas (other than public): "qa" and "production".
Initially I create all my tables in "qa". All of them have a primary key 
of type serial.

Later, I will copy the tables definitions to production.
It will automatically create the sequences in the new schema, starting 
at zero?

TIA
Marcelo


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