Thread: More problems with 7.2 & functions

Re: More problems with 7.2 & functions

From

Tom Lane

Date:

16 February 2002, 16:43:10

"Josh Berkus" <josh@agliodbs.com> writes:
> I'm getting this error:
> ERROR: replace_vars_with_subplan_refs: variable not in subplan target
>  list

That's interesting ... but if you don't show us the function, how
do you expect any help?
        regards, tom lane

Re: More problems with 7.2 & functions

From

"Josh Berkus"

Date:

16 February 2002, 16:52:42

Folks, Tom:

More on that error:

The error:


Is being caused by a view referenced in the function.  As I saidbefore, this view worked under Postgres 7.1.3 but now
itfails under7.2.  What follows is the view definition.  Sorry it's not veryformatted, but I can't find my source file
copyso it's from pg_dump.
 

CREATE VIEW "sv_invoices" as 
SELECT invoices.usq, invoices.end_date, invoices.status,status.status_label, invoices.total_amount,
invoices.invoice_no,clients.client_name,orders.position_type AS invoice_name,invoices.client_usq FROM ((((invoices JOIN
statusUSING (status)) JOINtext_lists ON ((invoices.invoice_type = text_lists.list_id))) JOINorders ON
((invoices.ref_usq= orders.usq))) JOIN clients ON((invoices.client_usq = clients.usq))) WHERE (((status.relation
='invoices'::"varchar")AND (invoices.status <> 0)) AND(text_lists.list_value ~* '^per order'::text
 
UNION SELECT invoices.usq, invoices.end_date, invoices.status,status.status_label, invoices.total_amount,
invoices.invoice_no,cl2.client_name,'Combined', invoices.client_usq FROM ((((invoicesJOIN status USING (status)) JOIN
text_listsON ((invoices.invoice_type= text_lists.list_id))) JOIN clients ON ((invoices.ref_usq =clients.usq))) JOIN
clientscl2 ON ((invoices.client_usq = cl2.usq)))WHERE (((status.relation = 'invoices'::"varchar") AND
(invoices.status<>0)) AND (text_lists.list_value ~* '^combined'::text))) 
 
UNION SELECT invoices.usq, invoices.end_date, invoices.status,status.status_label, invoices.total_amount,
invoices.invoice_no,clients.client_name,((candidates.last_name || ', '::"varchar") ||candidates.first_name),
invoices.client_usqFROM (((((invoices JOINstatus USING (status)) JOIN text_lists ON ((invoices.invoice_type
=text_lists.list_id)))JOIN assignments ON ((invoices.ref_usq =assignments.usq))) JOIN candidates ON
((assignments.candidate_usq=candidates.usq))) JOIN clients ON ((invoices.client_usq =clients.usq))) WHERE
(((status.relation= 'invoices'::"varchar") AND(invoices.status <> 0)) AND (text_lists.list_value ~* '^pertemp'::text)))

UNION SELECT (clients.usq * -1),qf_next_invoice_due(clients.invoice_interval) AS qf_next_invoice_due,-255, 'Next Due',
vbp.total_timecards,NULL::VARCHAR,clients.client_name, '', clients.usq FROM (clients JOINvw_bills_pending vbp ON
((clients.usq= vbp.client_usq)));
 

Grazie!

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco

Re: More problems with 7.2 & functions

From

Tom Lane

Date:

16 February 2002, 17:13:09

"Josh Berkus" <josh@agliodbs.com> writes:
>  What follows is the view definition.  Sorry it's not very
>  formatted, but I can't find my source file copy so it's from pg_dump.

Could I trouble you for a full schema dump of these tables, so I don't
have to waste time trying to reverse-engineer tables that will match
the view?

Basically, when you're reporting a bug or possible bug, you ought to
make it as easy as possible for someone else to reproduce the problem...
        regards, tom lane

Re: More problems with 7.2 & functions

From

"Josh Berkus"

Date:

16 February 2002, 18:03:11

Tom,

> Could I trouble you for a full schema dump of these tables, so I
>  don't
> have to waste time trying to reverse-engineer tables that will match
> the view?

Yeah, I was hoping that it would be an "Oh, yeah, THAT bug" from you. Apparently it's not. 

To avoid having to dump half my database, I've done some furthersleuthing on my own.  Here's the source of the
problem:

The last select of the union view includes a custom function in theSELECT clause.  This function works on its own.  It
worksin theSELECT clause when called from the command line.  It also works if Icall it as the first select query of the
UNION,but not as the last.
 

However, the bug is NOT reproduceable with a less complex model --possibly not reproduceable on any other database.  I
havetried toreproduce it using a simpler view that I could post to you, and Ican't.  Since I can't give you the whole
databaseat this time (IPagreements), and I have a workaround,  I will document thecircumstances:
 

1. Build a UNION view with four SELECTs unioned, each of which has 10columns and references at least 4 tables.
2. The top 3 SELECTs should include a DATE column from one of thetables.
3. The 4th SELECT should include:a. a reference to another moderately complex viewb. a moderately complex (100 lines)
customplpgsql function resultingin a TIMESTAMP value which supplies a column to align with the DATEvalue in the top
threeSELECTs.
 

You may get the error I reported.  Under any other circumstances, itdoesn't happen.

*sigh*.  Only me ....

Thanks for you patience, Tom.

-Josh

Re: More problems with 7.2 & functions

From

Tom Lane

Date:

16 February 2002, 18:03:11

"Josh Berkus" <josh@agliodbs.com> writes:
> However, the bug is NOT reproduceable with a less complex model --
>  possibly not reproduceable on any other database.  I have tried to
>  reproduce it using a simpler view that I could post to you, and I
>  can't.  Since I can't give you the whole database at this time (IP
>  agreements),

Understood, but this is a planning-time issue so I don't actually need
any data.  Just the schema (and possibly the functions).  Can you strip
down the number of tables involved and/or rename tables and columns
enough to address your IP worries?
        regards, tom lane