Message-ID: <4C158F97.6030402@ashisuto.co.jp>
Date: Mon, 14 Jun 2010 11:10:31 +0900
From: Kenichiro Tanaka <ketanaka@ashisuto.co.jp>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; ja;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: out of memory
References: <AANLkTimHKcglpoyr3u5BbQ5i9-JqlY6xL1hmYcXc3QB0@mail.gmail.com>
In-Reply-To: <AANLkTimHKcglpoyr3u5BbQ5i9-JqlY6xL1hmYcXc3QB0@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello

I think this SQL returns the following  error.

ERROR:  missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
         ^

For a fact ,this SQL does not have the "email_trac" table  in from-clause.

1)Is this SQL right?
2)If the SQL is right, can you write how make your table?
   I'd like to try your SQL.
3)What version do you use?

This is my test.

================================================================
--PostgreSQL8.4.4. for CentOS5.3
begin;

create schema email_track;
create table email_track.crmentity
(crmid int,
deleted int,
createdtime date);

create table email_track.activity
(activityid int,
activitytype varchar(100),
date_start date,
subject varchar(100));


create table email_track.emaildetails
(emailid int
);

create table email_track.vantage_email_track
(vantage_email_track int,
mailid int);

create table email_track.seactivityrel
(activityid int);

set search_path to email_track,public;


explain analyze
select
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
from
(select * from crmentity where deleted = 0 and createdtime between 
(now() - interval '6 month') and now() ) as crmentity
inner join
(select * from activity where  activitytype = 'Emails' and date_start 
between (now() - interval '6 month')  and now()) as activity
on crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on 
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid = emaildetails.emailid;


ERROR:  missing FROM-clause entry for table "email_track" at character 24
STATEMENT:  explain analyze
         select
         email_track.count AS "Emails_Access_Count",
         activity.subject AS "Emails_Subject",
         crmentity.crmid AS EntityId_crmentitycrmid
         from
         (select * from crmentity where deleted = 0 and createdtime 
between (now() - interval '6 month') and now() ) as crmentity
         inner join
         (select * from activity where  activitytype = 'Emails' and 
date_start between (now() - interval '6 month')  and now()) as activity
         on crmentity.crmid=activity.activityid
         inner join emaildetails on emaildetails.emailid = crmentity.crmid
         inner join vantage_email_track on 
vantage_email_track.mailid=emaildetails.emailid
         left join seactivityrel on seactivityrel.activityid = 
emaildetails.emailid;
ERROR:  missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
         ^

--can not reproduce.
abort;

================================================================

(2010/06/13 22:25), AI Rumman wrote:
> Whenever I run this query, I get out of memory error:
>
>
> explain analyze
> *select *
> email_track.count AS "Emails_Access_Count",
> activity.subject AS "Emails_Subject",
> crmentity.crmid AS EntityId_crmentitycrmid
> *from *
> (select * from crmentity where deleted = 0 and createdtime between 
> (now() - interval '6 month') and now() ) as crmentity
> inner join
> (select * from activity where  activitytype = 'Emails' and date_start 
> between (now() - interval '6 month')  and now()) as activity
> on crmentity.crmid=activity.activityid
> inner join emaildetails on emaildetails.emailid = crmentity.crmid
> inner join vantage_email_track on 
> vantage_email_track.mailid=emaildetails.emailid
> left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
>