Обсуждение: Serial not nulla

Shavonne Marietta Wijesinghe wrote:
> Hello
> 
> I am working with a database that has a Index number defined as Serial NOT NULL
> 
> I used this because,
> 1. I want to make sure that when many users login at the same time the Index number won't be repeated.
> 2. I don't have to increment it by programming (I use ASP)
> 
> But now i have a situation that i need to index numbers. For Example i could have a structure like this
> 
> INDEX1 - N_SHEET - TOT_SHEET
> 1 - 1 - 1
> 2 - 1 - 3
> 2 - 2 - 3
> 2 - 3 - 3
> 
> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to the INDEX. And while userA is filling
the3 row if userB loggs in i need to provide the INDEX1 with 3.
 
> 
> Any idea??
> 

As well as using the "Serial NOT NULL" you have also defined this column 
as PRIMARY KEY (or a unique index) which is what is preventing the 
duplicates in that column. (A primary key is enforced with a unique index)

From the sample shown you can use all three columns as the primary key 
with something similar to -

ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET", 
"TOT_SHEET");

(this implies that for each user they will have only one row for each 
combination of N_SHEET and TOT_SHEET) If you need to allow them to 
select the same 2 sheet numbers more than once then I would suggest you 
have an extra column for a primary key and redefine INDEX1 as the 
user_id. (or just add a user_id column and leave the INDEX1 as it is)



It's not recommended but you could also have the table without a primary 
key allowing duplicate value combinations. This would prevent you 
updating a single row though.



-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Pascal Tufenkji wrote:

> My questions are:
> 
>  
> 
> 1.      how do I identify the users assigned to this role :
> (in the older version)
> SELECT grolist from pg_group where groname = 'sti'; 
> 

"The view pg_group exists for backwards compatibility: it emulates a 
catalog that existed in PostgreSQL before version 8.1. It shows the 
names and members of all roles that are marked as not rolcanlogin, which 
is an approximation to the set of roles that are being used as groups."


Use pg_roles to get the user and group info. Use pg_auth_members to get 
the list of members that belong to each group role.
(any role can be used for a group but it is usually a role that has 
rolcanlogin set to false, and has members recorded in pg_auth_members)

http://www.postgresql.org/docs/8.2/interactive/user-manag.html
can explain it better - or more specifically
http://www.postgresql.org/docs/8.2/interactive/role-membership.html

> 
> 2.      how do I differ granting permissions on a table to the user sti from
> the whole members of the group sti
> (in the older version)
> GRANT SELECT ON table TO group sti;
> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
> 

Use a more descriptive name for the group or simply sti_group.


I am guessing that you have an issue because you now have one role 
called sti - that has carried the group members from the old version - 
this is the admin userid used to login but because it is used as a group 
it passes it's privileges to all members of sti.





-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Pascal Tufenkji wrote:
> Hi Shane,
> 
>  
> 
> You are exactly right.
> 
> My issue is that, I now have one role called sti - that has carried the
> group members from the old version -  
> 
> So what do you think my options are, so I can separate them? 
> 
> I have only one option in my mind:
> 
> -         Revoke the members from the role sti
> 
> -         Create a new role (that has rolcanlogin set to false) called
> sti_group
> 
> -         Assign the members to it
> 
> -         Finally, fix all the permissions for all the tables (add the
> permissions to the new group sti_group)
> which seems like a huge amount of work. 
> 
>  
> 
> In that case I'll be able to give permissions such as :
> 
> GRANT SELECT ON table TO sti_group;
> GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti;
> 
>  
> 
> Is there a better solution ?
> 

That is the solution and it does seem like a lot if you have lots of 
users and/or tables - I can think of a couple of ways to make it easy -

1. Use pgAdmin - it has a Grant wizard that will generate the sql for 
the grants and revokes on all the tables/functions etc for you. It can 
do an entire schema in a few clicks.

2. Generate the list of commands yourself - fill a text file with them 
and send them to psql.

"REVOKE sti FROM "+username+";"
"GRANT sti_group TO "+username+";"
"GRANT SELECT ON "+tablename+" TO sti_group;"
...
...


The second may be the way to go at least for the removing and adding 
group memberships from sti to sti_group as I don't see any helpers in 
pgAdmin for that.



-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Thanks alot Shane..Just what I wanted.. Didn't think of a solution with 3 
primary keys.

Have a nice day ^___^


Shavonne Wijesinghe
www.studioform.it


----- Original Message ----- 
From: "Shane Ambler" <pgsql@Sheeky.Biz>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, February 08, 2008 2:17 PM
Subject: Re: [SQL] Serial not nulla


> Shavonne Marietta Wijesinghe wrote:
>> Hello
>>
>> I am working with a database that has a Index number defined as Serial 
>> NOT NULL
>>
>> I used this because,
>> 1. I want to make sure that when many users login at the same time the 
>> Index number won't be repeated.
>> 2. I don't have to increment it by programming (I use ASP)
>>
>> But now i have a situation that i need to index numbers. For Example i 
>> could have a structure like this
>>
>> INDEX1 - N_SHEET - TOT_SHEET
>> 1 - 1 - 1
>> 2 - 1 - 3
>> 2 - 2 - 3
>> 2 - 3 - 3
>>
>> N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold 
>> on to the INDEX. And while userA is filling the 3 row if userB loggs in i 
>> need to provide the INDEX1 with 3.
>>
>> Any idea??
>>
>
> As well as using the "Serial NOT NULL" you have also defined this column 
> as PRIMARY KEY (or a unique index) which is what is preventing the 
> duplicates in that column. (A primary key is enforced with a unique index)
>
>
> From the sample shown you can use all three columns as the primary key 
> with something similar to -
>
> ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
> ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET", 
> "TOT_SHEET");
>
> (this implies that for each user they will have only one row for each 
> combination of N_SHEET and TOT_SHEET) If you need to allow them to select 
> the same 2 sheet numbers more than once then I would suggest you have an 
> extra column for a primary key and redefine INDEX1 as the user_id. (or 
> just add a user_id column and leave the INDEX1 as it is)
>
>
>
> It's not recommended but you could also have the table without a primary 
> key allowing duplicate value combinations. This would prevent you updating 
> a single row though.
>
>
>
> -- 
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz