From: Kenichiro Tanaka <ketanaka@ashisuto.co.jp>
To: pgsql-performance@postgresql.org
Subject: Re: out of memory
Date: Mon, 14 Jun 2010 11:10:31 +0900
Message-ID: <4C158F97.6030402@ashisuto.co.jp> (raw)
In-Reply-To: <AANLkTimHKcglpoyr3u5BbQ5i9-JqlY6xL1hmYcXc3QB0@mail.gmail.com>
References: <AANLkTimHKcglpoyr3u5BbQ5i9-JqlY6xL1hmYcXc3QB0@mail.gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ketanaka@ashisuto.co.jp
Subject: Re: out of memory
In-Reply-To: <4C158F97.6030402@ashisuto.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox