Re: [SQL] Query kill
On Fri, 12 Jul 2002 01:01:31 -0400 (EDT) in message <[EMAIL PROTECTED]>, Bruce Momjian <[EMAIL PROTECTED]> wrote: > Rudi Starcevic wrote: > > Hello, > > > > If I write a query that is inefficient or in an eternal loop how > > do I stop it without restarting the postmaster ? > > > > I can see many postmaster processed appearing in the output of the 'ps' > > command. > > Do I need to stop/kill them all or can I stop just the query I want ? > > Just send a SIGINT to the process. That simulates a ^C, which works too > from the client like psql. Is there a way to deny permission for certain users to execute a query that exceeds some expected cost? For example, I have a query builder from user input that could produce a query that ends up doing something that the query planner thinks will take 8M units of work. Generally, this is an unconstrained join between my biggest tables, a result that is neither fast nor useful. If I could set a threshold of 1M units for the webapp user, I could trap this sort of thing before they cause quality of service issues. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] select question
On Wed, 28 Aug 2002 16:12:41 -0400 in message
<[EMAIL PROTECTED]>, george young <[EMAIL PROTECTED]> wrote:
> [postgreql 7.2, linux]
> I have a table T with columns run, wafer, and test:
>T(run text, wafer int, test text)
> Given a run and a set of wafers, I need the set of tests that match
> *all* the specified wafers:
>
> run wafer test
> a 1 foo
> a 2 foo
> a 3 foo
> a 3 bar
>
> E.g.
> Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches
>both 1 and 3.
> Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and
>bar match 3.
>
> Is there some neat way to do this in a single query?
>
select test from T
where run='a'
and wafers in ('1','3')
group by test
eric
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Hairy question - transpose columns
> I'm sure there's a better way, but I think a series of union alls would > do it but be rather computationally expensive. > > select cod_var, Year, Month, 1 as Day, RainDay1 as Rain > where Ten=1 > union all > select cod_var, Year, Month, 2 as Day, RainDay2 as Rain > where Ten=1 You could do the following: select cod_var, Year, Month, 1+((ten-1)*10) as Day, RainDay1 as Rain where RainDay1 is not null union all select cod_var, Year, Month, 2+((ten-1)*10) as Day, RainDay2 as Rain where RainDay2 is not null .. I'm sure that there is a function that could do this too, but I'd tend to just convert the data and be done with it. eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Row Locking?
I have a long running process that performs outside actions on the content of a table. The actions could all be done in parallel (if I had n processors), but I need to ensure that the process is attempted exactly one time per applicable row. My current design for one thread is the following (simplified to a test case) create table foo (pending boolean, done boolean, idx serial); select * from foo where pending='f' and done='f' limit 1; update foo set pending='t' where idx=[[returned idx]]; commit; do stuff outside database update foo set pending='f', done='t' where idx=[[returned idx]]; commit; Extending this to multiple threads if proving problematic. No locking leads to a race condition between the select and update. If I change the select to a SELECT ... FOR UPDATE it apparently locks the table against all other select for updates, then when the update is committed, the second thread returns nothing, even when there are other rows in the table that could be returned. Is there a single row locking against select? Or can I effeciently do the equivalent of update set pending, then select the row that I just updated to get the contents? (perhaps without doing a table scan to find the oid of the row that I just updated). I can't afford to lock the entire table. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query for filtering records
I'm having trouble subtracting groups from other groups. I've got a data model that has the following essential features: create table contacts (num int, properties); create table groups (groupNum int, contactNum int); Where not all contacts will be in a group, some groups will contain most contacts, and there will be something like hundreds of groups and tens of thousands of contacts. I allow people to build groups using criteria, which I need to programatically translate to sql. One somewhat common pattern is: Select all contacts in group a, who have property b, and who aren't in groups c,d,e,f... My first shot was subqueries: select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where groups.groupNum=a and contact.p3=b and not num in (select contactNum from groups where groupNum=c) and not num in (select contactNum from groups where groupNum=d) and not num in (select contactNum from groups where groupNum=e) and not num in (select contactNum from groups where groupNum=f) This is slow. agonizingly so. With an inner join, I'm not convinced that the subtraction is actually correct., but it is much faster. Unfortunatley, faster incorrect answers are rarely helpful. Outer joins seem even worse than subselects for speed, but it does appear to give the correct answer. (example with a single join.) select num from contacts left outer join groups on (contacts.num=groups.contactNum and groups.groupNum=b) where dl_groupDonor._groupNum is null and p3=c I've got to be missing something here, because this is much slower from the (slow) procedural system that I'm porting from. I've been avoiding using union / intersect since I don't really ever know what columns are going to be in the query. perhaps I should revisit that decision and try to work around it. eric ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Query for filtering records
> SELECT * ... > FROM ... > WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or > groupnum='d' OR ... ) > > is bound to be _much_ faster! Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that. > And even better is > > SELECT * > FROM ... contacts c1 > WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or > groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum) > > > EXISTS is almost always faster in PG. Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort. Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine. thanks eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Is there a more elegant way to write this query?...
On Nov 10, 2003, at 1:02 PM, Nick Fankhauser wrote: Hi- I'm suffering from a performance problem, but when I look at my query, I'm not convinced that there isn't a better way to handle this in SQL. -So I'm seeking advice here before I go to the performance list. An explain analyze would help. What I'm trying to do is link these tables to get back a single row per actor that shows the actor's name, the number of cases that actor is assigned to, and if they only have one case, I want the number for that case. This means I have to do some grouping to get the case count, but I'm then forced to use an aggregate function like max on the other fields. I hope there's a better way. Any suggestions? How about: select actor.actor_full_name, actor.actor_id, s1.ctCases, s1.case_id, case_data.case_public_id from actor inner join ( select actor_id, count(*) as ctCases, max(case_id) as case_id from actor_case_assignment group by actor_id) as s1 on (actor.actor_id = s1.actor_id) left outer join case_data using (s1.case_id=case_data.case_id) limit 1000; If you don't need the public_id, then you don't even need to join in the case data table. eric ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
