Re: Out of memory on SELECT (from sort?) in 8.3 - Mailing list pgsql-general

> Is this particular query using xml features, and if so which?  Actually,
> could you just show us the whole query and the schemas of the tables
> it's using?

No, other than returning a column of type xml. The query for the EXPLAIN
output I posted before is this:

select
    lead.id as leadId,
    lead.xml as xml,
    lead.processing_state as processingState,
    lead.processing_step as processingStep,
    lead.processing_attempts as processingAttempts,
    lead.created as createdDate,
    lead.last_processed as lastProcessedDate
from lead lead
where
    (date(lead.created at time zone interval '-06')
        between date('2008-08-15') and date('2008-08-15'))
    or
    (date(lead.modified at time zone interval '-06')
        between date('2008-08-15') and date('2008-08-15'))
    order by lead.id

And the schema of the lead table is this:

lms_nna=# \d lead
                    Table "public.lead"
       Column        |           Type           | Modifiers
---------------------+--------------------------+-----------
 id                  | bigint                   | not null
 xml                 | xml                      |
 source              | character varying(50)    | not null
 destination         | character varying(50)    |
 processing_state    | character varying(20)    | not null
 created             | timestamp with time zone | not null
 modified            | timestamp with time zone | not null
 last_processed      | timestamp with time zone |
 processing_step     | integer                  |
 processing_attempts | integer                  |
Indexes:
    "lead_pkey" PRIMARY KEY, btree (id), tablespace "lms_index"
    "lead_created_idx" btree (created), tablespace "lms_index"
    "lead_destination_idx" btree (destination), tablespace "lms_index"
    "lead_modified_idx" btree (modified), tablespace "lms_index"
    "lead_processing_step_idx" btree (processing_step), tablespace
"lms_index"
    "lead_source_idx" btree (source), tablespace "lms_index"
    "processing_state_idx" btree (processing_state), tablespace "lms_index"
    "reporting_date_idx" btree (date(timezone('-06:00:00'::interval,
created))), tablespace "lms_index"
    "reporting_last_processed_date_idx" btree
(date(timezone('-06:00:00'::interval, last_processed))), tablespace
"lms_index"
    "reporting_modified_idx" btree (date(timezone('-06:00:00'::interval,
modified)))
Foreign-key constraints:
    "fk_lead_destination" FOREIGN KEY (destination) REFERENCES
external_system(name) ON UPDATE RESTRICT ON DELETE RESTRICT
    "fk_lead_source" FOREIGN KEY (source) REFERENCES external_system(name)
ON UPDATE RESTRICT ON DELETE RESTRICT
Triggers:
    _lms_logtrigger_11 AFTER INSERT OR DELETE OR UPDATE ON lead FOR EACH
ROW EXECUTE PROCEDURE _lms.logtrigger('_lms', '11', 'kvvvvvvvvv')
    create_lead_reporting_data AFTER INSERT OR UPDATE ON lead FOR EACH ROW
EXECUTE PROCEDURE update_lead_reporting_data()
    set_modified BEFORE INSERT OR UPDATE ON lead FOR EACH ROW EXECUTE
PROCEDURE update_modified()

Would you also like the schemas for the referenced FOREIGN KEY tables? You
can see we have some insert/update triggers there, which I can provide the
source for if you need (update_lead_reporting_data() is quite large,
though).

-- m@