evil optimizer - Mailing list pgsql-general

    I've got a query that the optimizer never returns from:

        select events.event_id, events.hostdate, events.ts, events.priority,
                tags.tag_name, events.stack,
                messages.message,
                usernames.user_name as user_name,
                wwwusers.user_name as wwwuser,
                files.file_name as filename,
                scripts.file_name as scriptname,
                events.linenum
        from events, tags, messages, usernames, usernames as wwwusers,
                files, files as scripts
        where events.tag_id=tags.tag_id
                and events.message_id=messages.message_id
                and usernames.user_id = events.user_id
                and wwwusers.user_id = events.wwwuser_id
                and files.file_id = events.file_id
                and scripts.file_id = events.script_id

    ...is there anything in there that looks like it should confuse
it?  The table definition is attached.  It will fail even if I have no
data at all, so it should be easy to reproduce:

createdb events
psql -f events.sql events
psql events
events> select * from event_view;

    That's all it takes.

--
SA, beyond.com           My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________
-- Copyright (c) 1998  Dustin Sallings
--
-- $Id: events.sql,v 1.2 1999/01/22 19:56:12 dustin Exp $
--
-- This is used to bootstrap your postgres database for event logging.

begin transaction;

-- add support for PL/pgsql

CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
    '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
                
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
    HANDLER plpgsql_call_handler
    LANCOMPILER 'PL/pgSQL';

-- tag table

create table tags (
    tag_id serial,
    tag_name text
);

grant all on tags to nobody;
create unique index tags_byname on tags(tag_name);

create function gettag(text) returns integer as
'
declare
    id integer;
begin
    select tag_id into id from tags where tag_name = $1;
    if not found then
        insert into tags(tag_name) values($1);
        select tag_id into id from tags where tag_name = $1;
    end if;
    return(id);
end;
' language 'plpgsql';

-- filenames

create table files (
    file_id serial,
    file_name text
);

grant all on files to nobody;
create unique index files_byname on files(file_name);

create function getfile(text) returns integer as
'
declare
    id integer;
begin
    select file_id into id from files where file_name = $1;
    if not found then
        insert into files(file_name) values($1);
        select file_id into id from files where file_name = $1;
    end if;
    return(id);
end;
' language 'plpgsql';

-- usernames

create table usernames (
    user_id serial,
    user_name text
);

grant all on usernames to nobody;
create unique index usernames_byname on usernames(user_name);

create function getuser(text) returns integer as
'
declare
    id integer;
begin
    select user_id into id from usernames where user_name = $1;
    if not found then
        insert into usernames(user_name) values($1);
        select user_id into id from usernames where user_name = $1;
    end if;
    return(id);
end;
' language 'plpgsql';

-- message data

create table messages (
    message_id serial,
    message text
);

grant all on messages to nobody;
create unique index message_text on messages(message);

create function getmsg(text) returns integer as
'
declare
    id integer;
begin
    select message_id into id from messages where message = $1;
    if not found then
        insert into messages(message) values($1);
        select message_id into id from messages where message = $1;
    end if;
    return(id);
end;
' language 'plpgsql';

-- actual event log

create table events (
    event_id serial,
    tag_id integer not null,
    priority integer not null,
    hostdate datetime not null,
    message_id integer not null,
    script_id integer not null,
    file_id integer not null,
    linenum integer not null,
    user_id integer not null,
    wwwuser_id integer not null,
    stack text not null,
    ts datetime default(datetime(now()))
);

grant all on events to nobody;
create index events_tag on events(tag_id);

-- example insert:
-- insert into events(tag_id, priority, hostdate, message_id, script_id,
--                 file_id, linenum, user_id, wwwuser_id, stack)
--  values(gettag('badfunc'), 5, 'date time', getmsg('error message'),
--         getfile('/.../scripts/scriptname.pl'), getfile('/.../libs/name.pl'),
--            843, getuser('nobody'), getwwwuser('dustin'), 'stack trace');

create view event_view as
    select events.event_id, events.hostdate, events.ts, events.priority,
        tags.tag_name, events.stack,
        messages.message,
        usernames.user_name as user_name,
        wwwusers.user_name as wwwuser,
        files.file_name as filename,
        scripts.file_name as scriptname,
        events.linenum
    from events, tags, messages, usernames, usernames as wwwusers,
        files, files as scripts
    where events.tag_id=tags.tag_id
        and events.message_id=messages.message_id
        and usernames.user_id = events.user_id
        and wwwusers.user_id = events.wwwuser_id
        and files.file_id = events.file_id
        and scripts.file_id = events.script_id
;

grant all on event_view to nobody;

commit;