Re: [SQL] Question regarding indices
Steve wrote: Original-Nachricht Datum: Sat, 11 Sep 2010 11:08:00 -0400 Von: Lew An: pgsql-sql@postgresql.org Betreff: Re: [SQL] Question regarding indices On 09/11/2010 08:29 AM, Steve wrote: I have a small question about the order of values in a query. Assume I have a table with the following fields: uid INT, data BIGINT, hits INT And an unique index on (uid, data). I use libpq C API to query data from the table. The query is something like this: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) Would the speed of the query be influenced if I would sort the data? What do you mean by "sort the data"? Which data? I mean sorting the values in the brackets. Instead of: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (2033,2499,590,19,201,659) I would then send this here: SELECT uid,data,hits FROM mytable WHERE uid=2 AND data IN (19,201,590,659,2033,2499) Off course this is a small dataset but the query usually has thousands of elements and not only the above 6 elements. If there will be thousands; why not create a temp table containing these values then join to table - might that be faster? -- 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] 9.0rc1 - query of view produces unexpected results
Doh!! Files attached this time. On 09/11/2010 07:31 PM, Nathan Grange wrote: Hello list, I don't know if it's me, or maybe even the way I designed these dependencies, but I'm getting unexpected results when i query a specific view. I can only reproduce the unexpected results with a very specific set of tables, views, and relationships. The bad results are only manifest in 9.0 (beta and rc1). Version 8.4.4 produces the expected results. To sum up the issue ... 1. View register_orders_view contains only 1 row of data ... /ttype | ropnum | shop_name ---++--- or| 30129 | District1/ 2. The query ... / select * from register_orders_view where ttype='z'/ ... should return no rows, however all rows are returned. Included are a buildDemo.sql file that will create the necessary tables and views, and a runDemo.sql to demonstrate the query from #2 above. If this is a valid result, can someone please help me understand why? If this is a case of "not the best" architecture a explanation would be greatly apprecitated. Or if this is a bug with 9.0, what actions do I take to make the PostgreSQL team awares? nate - CREATE TABLE territory ( terr_id integer NOT NULL PRIMARY KEY, name character varying NOT NULL ); COPY territory (terr_id, name) FROM stdin; 40 Foreign \. CREATE TABLE org ( org_id integer NOT NULL PRIMARY KEY, org_name character varying, terr_id integer REFERENCES territory(terr_id) ); COPY org (org_id, org_name, terr_id) FROM stdin; 3152District1 40 \. --- CREATE TABLE orders ( ordnum integer NOT NULL PRIMARY KEY, org_id integer REFERENCES org(org_id) ); COPY orders (ordnum, org_id) FROM stdin; 30129 3152 \. --- CREATE TABLE register ( ttype character(2) NOT NULL, ropnum integer NOT NULL, CONSTRAINT register_ttype_check CHECK ((ttype = ANY (ARRAY['ad'::bpchar, 'bf'::bpchar, 'or'::bpchar, 'po'::bpchar, 'pt'::bpchar, 'rq'::bpchar, 'wm'::bpchar, 'wt'::bpchar]))) ); COPY register (ttype, ropnum) FROM stdin; or 30129 \. -- CREATE VIEW org_view AS SELECT co.org_id ,co.org_name ,ct.name as tername fromorgco left join territory ct on ct.terr_id = co.terr_id; -- CREATE VIEW register_orders_view AS SELECT m.ttype , m.ropnum , co.org_name AS shop_name FROM register m JOIN orders oh ON ((oh.ordnum = m.ropnum)) JOIN org_view co ON ((co.org_id = oh.org_id)) WHERE m.ttype = 'or'; -- The following query shows that there are 3 rows of data "in" in the view. select * from register_orders_view; -- This next query shows the problem. Although there are no rows where the -- value for the ttype column is z, there are still 3 rows returned. select * from register_orders_view where ttype='z'; -- 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] 9.0rc1 - query of view produces unexpected results
Nathan Grange writes: >> Or if this is a bug with 9.0, what actions do I take to make the >> PostgreSQL team awares? I think you already did ;-) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Use "CREATE USER" in plpgsql function
Hello! I have function wich check user credentials and if test passed function must create new user with generated username and password. Language is plpgsql. For example: DECLARE creds RECORD; ... SELECT * INTO creds FROM ... creds is Record with fields userName(VARCHAR) and userPassword(VARCHAR) so when i use CREATE USER creds."userName" WITH PASSWORD creds."userPassword" i get an error, because creds."userName" is VARCHAR and thus when function runs it will be look like this: CREATE USER 'user_1' but right command is "CREATE USER user_1" OR " CREATE USER "user_1" " so question is how to "unembrace" this parameter (i mean creds."userName")? Thanks -- -- Alexander
Re: [SQL] Use "CREATE USER" in plpgsql function
Hi, On 15 September 2010 08:05, Tatarnikov Alexander wrote: > Hello! > > I have function wich check user credentials and if test passed function must > create new user with generated username and password. > > Language is plpgsql. > > so question is how to "unembrace" this parameter (i mean creds."userName")? Show the whole function please. > > Thanks > -- > -- > Alexander > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
