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;