[SQL] Serial not nulla
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?? Thanks Shavonne Shavonne Wijesinghe www.studioform.it Le informazioni contenute nella presente comunicazione e i relativi allegati possono essere riservate e sono, comunque destinate esclusivamente alle persone o alla Società sopra indicati. La diffusione, distribuzione e/o copiature del documento trasmesso da parte di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art. 616 c.p., che ai sensi del D. Lgs. n. 196/2003. Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo e di informarci immediatamente per telefono allo 0039362595044 o inviando un messaggio all'indirizzo e-mail [EMAIL PROTECTED] The informations in this communication is confidential and may also be legally privileged. It is intended for the addressee only. Access to this e-mail by anyone else is unauthorized. It is not to be relied upon by any person other than the addressee, except with our prior written approval. If you received this message please send an e-mail to the sender.
[SQL] performance issue - view and derived field
Hi folks. I have a view used_diary_details shown below. If I do a basic search on one of the fields of the underlying table the select takes under 1/2 second. If I do a search using the derived field it takes over 15 seconds. Anyone know how I can improve this as it's killing my app. -- Takes 1/2 second select count(ud_id) from used_diary_details where ud_required >= CURRENT_DATE and ud_required <= CURRENT_DATE+7; -- Takes 15 seconds select count(ud_id) from used_diary_details where valet_required >= CURRENT_DATE and valet_required <= CURRENT_DATE+7; create view used_diary_details as SELECT ud.*, ud.ud_required - CURRENT_DATE AS remaining, work_date(ud.ud_required,-2) as ud_clean_date, work_date(ud.ud_required,-2) - CURRENT_DATE AS ud_clean_days, coalesce(uco.count,0) AS comments, u.u_username, u.u_sales_mode AS dept, c.u_username AS completed_name, v.u_username as valet_completed_by, pv.u_username as pex_valet_completed_by, tos_desc, d.d_des as dealership, dd.dd_desc as department, dd.dd_tos_required, dd.dd_default_tos, tr.tr_desc as tax, not coalesce(tr.tr_notax,false) as tax_required, case when ud_valet_required is not null then ud_valet_required when ud_handover_date is not null and ud_required_time is null then work_date(ud_handover_date,-1) when ud_handover_date is not null and ud_required_time < '10:01' then work_date(ud_handover_date,-1) when ud_handover_date is not null and ud_required_time >= '10:01' then ud_handover_date when ud_required_time is null then work_date(ud_required,-1) when ud_required_time < '10:01' then work_date(ud_required,-1) else ud_required end as valet_required, dv_desc as valet FROM used_diary ud JOIN users u ON ud.ud_u_id = u.u_id LEFT JOIN users c ON ud.ud_completed_by = c.u_id LEFT JOIN users v ON ud.ud_valet_completed_by = v.u_id LEFT JOIN users pv ON ud.ud_pex_valet_completed_by = pv.u_id left join type_of_sale t on t.tos_id = ud.ud_tos_id join dealerships d on d.d_id = ud.ud_d_id join diary_departments dd on dd.dd_id = ud.ud_dd_id left join tax_rfl_values tr on tr.tr_id = ud_tr_id left join diary_valet_options dv on dv.dv_id = ud_pex_valet_option LEFT JOIN ( SELECT used_comments.uco_ud_id, count(used_comments.uco_ud_id) AS count FROM used_comments GROUP BY used_comments.uco_ud_id) uco ON uco.uco_ud_id = ud.ud_id; Table "public.used_diary" Column |Type ---+- ud_id | integer ud_d_id | integer ud_registration | character varying(12) ud_stock | character varying(7) ud_name | character varying(50) ud_required | date ud_rfl| character varying(25) ud_comments | text ud_created| timestamp with time zone ud_completed | timestamp with time zone ud_u_id | integer ud_completed_by | integer ud_dd_id | integer ud_vin| character varying(20) ud_authorized | timestamp without time zone ud_authorized_by | integer ud_tab| integer ud_tos_id | integer ud_debt | numeric(7,2) ud_m_id | integer ud_chit | character varying(12) ud_cc_id | character(2) ud_onsite_date| date ud_onsite | boolean ud_pl_id | character(1) ud_required_time | character varying(5) ud_tr_id | integer ud_pex_exists | boolean ud_pex_registration | character varying(12) ud_pex_make_model | character varying(40) ud_valet_instructions | text ud_valet_completed| timestamp without time zone ud_valet_completed_by | integer ud_pex_valet_completed| timestamp without time zone ud_pex_valet_completed_by | integer ud_pex_valet_option | integer ud_pex_valet_instructions | text ud_do_valet | boolean ud_valet_required | date ud_handover_date | date Indexes: "used_diary_pkey" PRIMARY KEY, btree (ud_id) "used_diary_completed_index" btree (ud_completed) "used_diary_dealer_index" btree (ud_d_id) "used_diary_dept_index" btree (ud_dd_id) "used_diary_handover_date" btree (ud_handover_date) "used_diary_reg_index" btree (ud_registration) "used_diary_required" btree (ud_required) "used_diary_stock_index" btree (ud_stock) "used_diary_ud_pex_valet_completed" btree (ud_pex_valet_completed) "used_diary_ud_valet_completed" btree (ud_valet_completed) "used_diary_valet_required"
[SQL] Postgres roles
Hi, I'd like to ask you a question about users, groups and roles. In older versions of Postgres we had users and groups as two separate entities Our IT department at work is called "sti", that's why: - I had a user "sti" (the username of our IT manager) - I had a group "sti" (the group containing all the users of our department: user1, user2.) When we upgraded to PostgreSQL 8.2.4, came up the concept of roles; users and groups became one entity So now I have in my database, only one role, called "sti" 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'; 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; I'd appreciate any help regarding this issue Pascal Tufenkji
Re: [SQL] Postgres roles
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 ? Pascal -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shane Ambler Sent: Friday, February 08, 2008 3:54 PM To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Postgres roles 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] What are the (various) best practices/opinions for table/column/constraint naming?
I'd like to see a list o t he various approaches, and a poll as to which are best and why, for naming table and columns and constraints. We've all seen several variations, but the most common (and pg used) seems to be: columns: primary key: _pk OR _id OR _rowid foreign key: _fk OR _join indexes: __idx sequences: __seq constraints: __req OR __constr (etc) The most used variations seem to be removing the '_', and/or to remove the table/column prefix from objects where it is implied (when there is seen to be no point trying to make the names unique, e.g when a label is needed): columns: primary key: pk example: SELECT person.pk [AS person_id], person.named, company.pk, company.named FROM contact AS person JOIN contact AS company ON person.companyid=company.pk Other variations suggest putting the type at the start of the object name: columns: primary key: pk_ etc foreign key: fk_ etc And other names which don't necessarily represent constraints or indexes and are only meaningful to the apps: columns: _id (integer numbers or alpha-num, abstract/machine meaningful: uuids, base-36 etc) _no (integer numbers, human meaningful) _nm OR _name (named value, e.g user_name, app_name, etc) _date OR _ts (datetime/timestamp, e.g created_date, modified_date etc) _info (informational value) And other naming conventions suggest using mixed/camel case (quoted-identifiers) instead of '_' delimiters, or no delimiters at all... Has anyone seen articles or iso/ansi endorsed best-practices for naming, or otherwise have an opinion about the variations? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Postgres roles
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
