Re: stuck on values in 8.2

I think I fixed the rest of my sql statements with the following:

insert into tokens (token)select values.token
from (values TOKEN_LIST_STRING ) as values(token)
left outer join tokens t using (token)
where t.token_idx is null

insert into user_token(user_idx, token_idx)select $self->{user_idx},
token_idxfrom tokens left outer join (select token_idx from
user_token where user_idx = $self->{user_idx}) ut using (token_idx)
where ut.token_idx is null
and token_idx in ($string)

I think does what I was trying to accomplish. At least the little
test sql seems to work.

interestingly, the time to process has gone from >100s to <1s.

On May 12, 2007, at 2:23 PM, Tom Allison wrote:

> Thank you very much for all your help!!!
>
> Solved this one rather nicely:
>
> my $glue = q{'),(E'};
>
> my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')";
>
> my $sql =<<SQL;
> SELECT values.token, t.token_idx, ut.token_idx
> FROM
> (values TOKEN_LIST_STRING) as values(token)
> left outer join tokens t using (token)
> left outer join
> ( select token_idx from user_token where user_idx = $self->
> {user_idx}) "ut"
> using (token_idx)
> SQL
>
> $sql =~ s/TOKEN_LIST_STRING/$string/o;
>
> -------------------------
> This is something on the order to 10-1000 times faster depending on
> the various result sets matching more/less across the various tables.
> "very nice..."
>
> I'm thinking I could do the same thing where I have a lot of inserts.
> But this is going to be considerably harder because I don't have
> any really good experience with doing this with transactional
> integrity.
>
> From this query, I eventually want to grab all the values.token and
> insert them into the token and user_token table.
>
> I can call:
> insert into tokens(token) values(('one'),('two'),('three'));
> then call:
> insert into user_token(user_idx, token_idx)
> select 14, token_idx from tokens where token in
> ('one','two','three')
>
> And that should work.
> HOWEVER:
> If I have two simultaneous INSERT INTO token(token) queries with
> overlapping values I'm going to get into all kinds of trouble with
> the integrity constraint on
> my index of unique tokens. Typically I'll get an integrity
> violation error.
>
> Or am I looking at something like:
> insert into tokens
> select ..
> values(..) as values(token)
> left outer join tokens using (token)
>
> (Am I getting the hang of this any better?)
>
>
>
> On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote:
>
>>
>> --- Tom Allison <tom(at)tacocat(dot)net> wrote:
>>
>>> This is getting really ugly...
>>> it won't finish in less than .. minutes.
>>>
>>> spam=> explain select u2.token_idx, t.token_idx, foo.token from
>>> tokens t left outer join user_token u1 using (token_idx),
>>> users u left outer join user_token u2 using (user_idx),
>>> (values('one'),('want'),('examine'),('three')) as foo(token)
>>> left outer join tokens using (token)
>>> where u.user_idx = 15;
>>
>> It looks to me that your query has (3) left joins and (3) implied
>> cross-joins. Perhaps reforming
>> your query to eliminate the cross-joins with help performance. In
>> regard to your tables <tokens>
>> and <user_tokens>, in this query you are referring to (2) separate
>> instances of these tables when
>> a single instance these tables would probably work just fine. i.e.
>>
>> tokens t vs. tokens,
>> user_token u1 vs user_token u2
>>
>> Regards,
>> Richard Broersma Jr.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster