[SQL] Problems ensuring uniqueness?
Hi there. I'm currently using Postgresql 7.0.3, accessing using the JDBC drivers. I'm enforcing the most severe transaction isolation available (serializable). I'm currently doing something like the following (this is pseudo code, with the if being performed in Java): begin lock table foo in access exclusive mode select count(*) from foo where key1 = bar, key2 = baz if count > 1 rollback else insert into foo (key1, key2, other) values (bar, baz, stuff) commit Now, table foo has a primary key made up of key1 & key2. Without concurrent access, the code works great. However, provided I use enough threads, I inevitably get back errors from the database indicating that I've tried to insert a record which violates the uniquness of the primary key. Is there away to avoid this error by doing my SQL & locking properly, or should I just catch the exception and deal with it? --Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems ensuring uniqueness?
On 18 Jun 2001 17:42:15 -0400, Tom Lane wrote: > Christopher Smith <[EMAIL PROTECTED]> writes: > Um ... surely that should be "if count > 0" ? Or was that just a > transcription error? > > This approach certainly ought to work as desired given the exclusive > lock, so a silly typo seems like a plausible explanation... Sorry, it is indeed a transcription error (sadly). The actual line in question looks like this in Java: boolean answer = resultSetCount.getInt(1) > 0; I accidently transcribed the field offset instead of the comparison constant probably should split that up into two lines of code to avoid confusion like this... --Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems ensuring uniqueness?
On 18 Jun 2001 18:04:14 -0400, Tom Lane wrote: > Christopher Smith <[EMAIL PROTECTED]> writes: > >> Um ... surely that should be "if count > 0" ? Or was that just a > >> transcription error? > >> > >> This approach certainly ought to work as desired given the exclusive > >> lock, so a silly typo seems like a plausible explanation... > > > Sorry, it is indeed a transcription error (sadly). > > Oh well. The next thought, given that you mention threads, is that > you've got multiple threads issuing commands to the same backend > connection; in which case the interlocking you think you have doesn't > exist at all... You got it bang on... I thought I had isolated access to the connections properly, but shortly after posting that last e-mail, had a eureka moment. I presume this will make the bug go away, so I'll encourage everyone to ignore this thread (other than as a warning ;-) until I've confirmed I still have the problem after making the correct adjustments. --Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] query speed joining tables
I have 4 tables that I need to query... 3 of the tables are links by the field userid. below are table sql fragments. as well as the query. The subquery causes very high CPU usages. It typically returns ~3000 matches. Is there another way to rewrite this? SELECT user_login.userid FROM user_login,user_details_p,user_match_details WHERE user_login.userid = user_details_p.userid ANDuser_details_p.userid = user_match_details.userid ANDuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age >=18 AND user_details_p.age <=50 ANDuser_match_details.min_age <= 30 ANDuser_match_details.max_age >= 30 ANDuser_details_p.ethnictype = 'Caucasian (White)' ANDstrpos(user_match_details.ethnicity,'Asian') !=0 ANDuser_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' )order by user_login.last_login desc; Table "public.user_login" Column | Type | Modifiers +--+--- userid | character varying(30) | not null password | character varying(30) | not null email | character varying(50) | not null last_login | timestamp with time zone | not null Indexes: user_login_pkey primary key btree (userid), Table "public.user_details_p" Column | Type | Modifiers -+--+--- userid | character varying(30) | not null gender | character varying(1) | age | integer | height | character varying(10) | ethnicty pe | character varying(30) | education | character varying(30) | createdate | timestamp with time zone | default now() zipcode | character varying(5) | birthdate | date | default now() zodiac | character varying(40) | seekgender | character varying(2) | Indexes: user_details_p_pkey primary key btree (userid), user_details_p_age_idx btree (age), &nb sp; user_details_p_ethnic_idx btree (ethnictype), user_details_p_gender_idx btree (gender), user_details_p_last_login_idx btree (last_login), user_details_p_seekgender_idx btree (seekgender), user_details_p_state_idx btree (state)Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.user_match_details" Column | Type | Modifiers --++--- userid | character varying(30) | not null soughtmate | character varying(200) | ethnicity | character varying(200) | marital_status | character varying(200) | min_age | integer | max_age | integer &nbs p; | city | character varying(50) | state | character varying(2) | zipcode | integer | match_distance | integer | Indexes: user_match_details_pkey primary key btree (userid)Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE Table "public.zips_300" Column | Type | Modifiers -+--+--- origin | character varying(5) | destination | character varying(5) | Indexes: zips_300_origin_idx btree (origin) Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: [SQL] query speed joining tables
I understand cachable functions but your proposed application is a little unclear. is it possible to see an example? thanks in advance. Josh Berkus <[EMAIL PROTECTED]> wrote: Chris,Here are probably your two main query problems:> strpos(user_match_details.ethnicity,'Asian') !=0 ANDIt is impossible for Postgres to use an index for the above as it is written. I would suggest one of two improvements;1) Modify the schema so that multiple ethnicity details are kept in a sub-table rather than a free-form text field you have to search, or:2) Create a cachable function for "contains_asian" and index on that.> user_details_p.zipcode in (select zips_max.destination from zips_max where zips_max.origin='90210' )> order by user_login.last_login desc;Use a "WHERE EXISTS" clause instead of "IN". -- -Josh BerkusAglio Database SolutionsSan Francisco---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanlyDo you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
Re: [SQL] query speed joining tables
my mistakes, zips_max should be zips_300. Tomasz Myrta <[EMAIL PROTECTED]> wrote: Christopher Smith wrote:> I have 4 tables that I need to query... 3 of the tables are links by the> field userid.>> below are table sql fragments. as well as the query. The subquery> causes very high>> CPU usages. It typically returns ~3000 matches. Is there another way to> rewrite this?>> SELECT user_login.userid FROM user_login,user_details_p,user_match_details> WHERE user_login.userid = user_details_p.userid AND> user_details_p.userid = user_match_details.userid AND> user_details_p.gender ='W' AND> user_details_p.seekgender ='M' AND> user_details_p.age >=18 AND> user_details_p.age <=50 AND> user_match_details.min_age <= 30 AND> user_match_details.max_age >= 30 AND> user_details_p.ethnictype = 'Caucasian (White)' AND> strpos(user_matc h_details.ethnicity,'Asian') !=0 AND> user_details_p.zipcode in (select zips_max.destination from zips_max> where zips_max.origin='90210' )> order by user_login.last_login desc;explicit joins show better idea of your query and helps postgres choosing indexing.select useridfromuser_loginjoin user_details using (userid)join user_match_details using (userid)whereuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age between 18 and 50 anduser_match_details.min_age <= 30 ANDuser_match_details.max_age >= 30 ANDuser_details_p.ethnictype = 'Caucasian (White)' ANDstrpos(user_match_details.ethnicity,'Asian') !=0 ANDuser_details_p.zipcode in (select zips_max.destination from zips_maxwhere zips_max.origin='90210' )order by user_login.last_login desc;How can I help you with subquery if you didn't write even zips_max definition?!?If origin is unique value in that table, you can change subquery into join on "from" list.> Table "public.user_login"> Column | Type | Modifiers> +--+---> userid | character varying(30) | not null> password | character varying(30) | not null> email | character varying(50) | not null> last_login | timestamp with time zone | not null> Indexes: user_login_pkey primary key btree (userid),Do you really need userid as varchar?indexing on int4 or int8 would be much faster than varcharWhy do you have 3 tables? It looks like only one table would be enough. Remember, that null values don't take too much space.>>>>> Table "public.user_details_p"> Column | Type | Modifiers> -+--+---> userid | character varying(30) | not null> gender | character varying(1) |> age | integer |> height | character varying(10) |> ethnicty pe | character varying(30) |> education | character varying(30) |> createdate | timestamp with time zone | default now()> zipcode | character varying(5) |> birthdate | date | default now()> zodiac | character varying(40) |> seekgender | character varying(2) |> Indexes: user_details_p_pkey primary key btree (userid),> user_details_p_age_idx btree (age),> &nb sp; user_details_p_ethnic_idx btree (ethnictype),> user_details_p_gender_idx btree (gender),>> user_details_p_last_login_idx btree (last_login),> user_details_p_seekgender_idx btree (seekgender),> user_details_p_state_idx btree (state)There is too many indexes -index on gender (2 possible values) is useless,index on ethnic (how many values - I think not too many?) is possibly uselessCon sider creating single index on several fields for queries like this:select...whereuser_details_p.gender ='W' ANDuser_details_p.seekgender ='M' ANDuser_details_p.age between 18 and 50 andindex on (age,seekgender,gender)>> Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES> user_login(userid) ON UPDATE NO ACTI> ON ON DELETE CASCADE>>> Table "public.user_match_details"> Column | Type | Modifiers> --++---> userid | character varying(30) | not null> soughtmate | character varying(200) |> ethnicity | character varying(200) |> marital_status | character varying(200) |> min_age | integer |> max_age | integer &nbs p; |> city | character varying(50) |> state | character varying(2) |> zipcode | integer |> match_distance | integer |> Indexes: user_match_ details_pkey primary key btree (userid)> Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES> user_login(userid) ON UPDATE NO ACTION ON DELETE CASCADE>> Table "public.zips_300"> Column | Type | Modifiers> -+--+---> origin | character varying(5) |> destination | character varying(5) |> Indexes: zips_300_origin_idx btree (origin)If you need more specific answer, you have to add more information - how many records do you have in your tables and how many possible values do you use for example for zipcodes, ethnicity etc.Regards,Tomasz MyrtaDo you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now
