Re: stuck on values in 8.2 - Mailing list pgsql-general

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@tacocat.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.