Re: [SQL] amount of join's and sequential access to the tables involved
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió: > Gerardo Herzig writes: > > Hi all. Im working on a 'simple' query with 7, 8 left joins. After the > > 9nth join or so, explain analyze became to show the plan with many > > tables being read in sequential fashion. Of course, this slows down the > > query response in a factor on 10. > > increase join_collapse_limit, perhaps? > > regards, tom lane > Im sory Tom and all, again stuck with this. Now i have 9 joins, and join_collapse_limit = 20, and the query slows down big time when the 9th join appears. Another configuration variable to check against? Thanks again. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unable To Modify Table
I seem to have an issue where I can't modify a table due to another tables foreign key association: [CODE]trinity=# \d developers Table "public.developers" Column| Type | Modifiers --++--- id | character(10) | not null name| character(50) | not null address | character(50) | city| character(50) | state | character(2) | zip | character(10) | country | character(50) | phone | character(50) | email | character(255) | Indexes: "developers_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id) [/CODE] Now I want to change the formatting of field data in 'id' in table 'developers': [CODE]trinity=# SELECT id FROM developers; id 11 12 13 14 15 16 (109 rows) [/CODE] Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table. [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; ERROR: update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders" DETAIL: Key (id)=(11) is still referenced from table "orders". [/CODE] How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On 01/12/2012 08:42 AM, Carlos Mennens wrote: I seem to have an issue where I can't modify a table due to another tables foreign key association: ... How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? Try updating the values in both tables within a transaction with constraints set to deferred: http://www.postgresql.org/docs/current/static/sql-set-constraints.html Cheers, Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On Thursday, January 12, 2012 8:42:59 am Carlos Mennens wrote: > I seem to have an issue where I can't modify a table due to another > tables foreign key association: > > [CODE]trinity=# \d developers > Table "public.developers" > Column| Type | Modifiers > --++--- > id | character(10) | not null > name| character(50) | not null > address | character(50) | > city| character(50) | > state | character(2) | > zip | character(10) | > country | character(50) | > phone | character(50) | > email | character(255) | > Indexes: > "developers_pkey" PRIMARY KEY, btree (id) > Referenced by: > TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) > REFERENCES developers(id) > [/CODE] > > > Now when I try and change the values before I alter the field TYPE, I > get an error that another table (orders) with a foreign key associated > with public.developers 'id' field still has old values therefor can't > change / modify the 'developers' table. > > [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; > ERROR: update or delete on table "developers" violates foreign key > constraint "fk_orders_developers" on table "orders" > DETAIL: Key (id)=(11) is still referenced from table "orders". > [/CODE] > > How does one accomplish my goal? Is this difficult to change or once > that foreign key is created, are you stuck with that particular > constraint? You are pushing in the wrong direction. You need to make the change in the table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Carlos Mennens Sent: Thursday, January 12, 2012 11:43 AM To: PostgreSQL (SQL) Subject: [SQL] Unable To Modify Table I seem to have an issue where I can't modify a table due to another tables foreign key association: [CODE]trinity=# \d developers Table "public.developers" Column| Type | Modifiers --++--- id | character(10) | not null name| character(50) | not null address | character(50) | city| character(50) | state | character(2) | zip | character(10) | country | character(50) | phone | character(50) | email | character(255) | Indexes: "developers_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "orders" CONSTRAINT "fk_orders_developers" FOREIGN KEY (id) REFERENCES developers(id) [/CODE] Now I want to change the formatting of field data in 'id' in table 'developers': [CODE]trinity=# SELECT id FROM developers; id 11 12 13 14 15 16 (109 rows) [/CODE] Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table. [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; ERROR: update or delete on table "developers" violates foreign key constraint "fk_orders_developers" on table "orders" DETAIL: Key (id)=(11) is still referenced from table "orders". [/CODE] How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? --- There are two possible actions you can take with respect to an existing Primary Key; you can UPDATE it or you can DELETE it. When you define a FOREIGN KEY you can specify what you want to happen if the corresponding PRIMARY KEY is UPDATEd or DELETEd. Read the documentation on FOREIGN KEY in detail to understand why you are seeing that error and what modifications you can make to the FOREIGN KEY on "orders" to obtain different behavior. Keep in mind, also, that the TYPE of the PRIMARY KEY and FOREIGN KEY must match. Contrary to my earlier advice assigning a sequential ID (thus using a numeric TYPE) is one of the exceptions where you can use a number even though you cannot meaningfully perform arithmetic on the values. The reason you would use a numeric value instead of a character is that the value itself is arbitrary and the space required to store a number is less than the space required to store a string of the same length. There are many points-of-view regarding whether to use "serial" PRIMARY KEYs but regardless of whether you add one or not you should try and define a UNIQUE constraint on the table by using meaningful values. However, for things like Orders this is generally not possible and so you would want to generate a sequential identifier for every record. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
-Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Thursday, January 12, 2012 11:55 AM To: pgsql-sql@postgresql.org Cc: Carlos Mennens Subject: Re: [SQL] Unable To Modify Table > > How does one accomplish my goal? Is this difficult to change or once > that foreign key is created, are you stuck with that particular > constraint? You are pushing in the wrong direction. You need to make the change in the table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled. Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders" the error in question would never have appeared and the UPDATE would have succeeded. Carlos' goal is to change the value of a Primary Key that has already been used in a FOREIGN KEY constraint and he needs to learn to use the documentation to solve some of these basic questions instead of asking the list. His approach is correct, execute UPDATE against the "developers" table. Deferrable constraints and transactions work as well but are more complicated to setup and execute compared to the more direct ON UPDATE CASCADE modifier to the FOREIGN KEY. But learning both methods is good. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On Thursday, January 12, 2012 9:02:35 am David Johnston wrote: > > > Adrian, you are not helping...if ON UPDATE CASCADE was enabled on "orders" > the error in question would never have appeared and the UPDATE would have > succeeded. Carlos' goal is to change the value of a Primary Key that has > already been used in a FOREIGN KEY constraint and he needs to learn to use > the documentation to solve some of these basic questions instead of asking > the list. His approach is correct, execute UPDATE against the > "developers" table. My mistake, I got the table relationship order wrong. Sorry for the noise. > > David J. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Query Problem... Left OuterJoin / Tagging Issue
Hi, I've been working on this for quite awhile now and don't seem to get the proper query. I have basically 4 tables. 1. Table john_test contains the numbers of the calls. 2. Table john_country contains the country with prefix. 3. Table john_clients contains the clients and their sub_id's (because a client usually has a subclient, [ex. highway-2, highway-3]) and their prefix. 4. Table john_test contains the phone numbers. select * from john_client_rate limit 3; name |country | cali | cana | callrate | dir_id | trans_id | svc_id | base | incr | client_id --++--+--+--++--++--+--+--- highway | Afghanistan| | | 0.6212 | 0 | 0 ||6 |6 | 4 highway | Albania| | | 0.3945 | 0 | 1 ||6 |6 | 4 highway | Bahamas| | | 0.0513 | 0 | 1 ||6 |6 | 4 (3 rows) select * from john_country limit 3; country| state | prefix | area_code ---+---+--+--- Afghanistan | | 93 | Aland Islands | | 35818120 | Albania | | 355 | (3 rows) select * from john_clients limit 3; id | client_id | sub_id | name | prefix | type +---++--+-+ 80 |80 | 0 | highway | 71081 | client 80 |80 | 0 | highway | 7107011 | client 80 |80 | 0 | highway | 71091 | client (3 rows) select * from john_test limit 3; client_id | name | phonenum | calledphonenum | phonenumtranslat | direction | duration --+-+--+-+--+---+-- 2 | highway | 83863011351927330133 | 20100147011351927330133 | | outbound | 363 2 | highway | 83863011441179218126 | 1943011441179218126 | | outbound | 83 2 | highway | 83863011441179218126 | 20100147011441179218126 | | outbound | 32 (3 rows) What I want to do is to remove the prefix, and retain the number using the following query: select john_test.name, john_test.gwrxdcdn, john_test.duration as dur, john_client_rate.name as name2, john_client_rate.country, john_country.prefix, substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as strip, get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) as realdur from john_test left outer join john_client_rate on (prefix in ( select prefix from john_client_rate where john_test.phonenum ~ ( '^' || john_country.prefix) order by length(john_country.prefix) desc limit '1' ) ) limit 20; I have achieved this already, now I want to identify which country it's supposed to be for. Problem is sometimes the "stripped" number that is retained shows: 8661234567 or 8889876543 This would indicate that the call is already toll free without me being able to identify the country. How can I get over this? Further, I am planning to use multiple joins since I have several tables and so as to identify missing countries. On this questions which query is better? Query 1: Select table1.column,table2.column,table3.column from table1 left outer join table 2 on (table1.column=table2.column) left outer join table3 on (table2.column=table3.column) ; or Query 2: Select table1.column,table2.column,table3.column from table1,table2,table3 where [conditions] ; Ultimately, I want to run one query that will satisfy these things and help me insert into a table that will have it "TAGGED" properly with the right Country, Client(name), prefix, and Rate for computation with Duration. Hope you can reply to me asap. This is of urgent importance. Thank you and any help would be greatly appreciated! - JT
Re: [SQL] Fwd: i want small information regarding postgres
Alvaro Herrera wrote: >> would someone with the appropriate authority please unsubscribe this >> person's email address from this list so we don't all get a bounce message >> after every email we send to the list? Thanks. > Just did it. In the future, please email sysadm...@postgresql.org with > mailing list complaints, as I don't read this list (or indeed many > others) I had reported this to pgsql-sql-ow...@postgresql.org. Where do that end up? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
