pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
More problems with 7.2 & functions
6+ messages / 2 participants
[nested] [flat]

* More problems with 7.2 & functions
@ 2002-02-16 18:10 Josh Berkus <josh@agliodbs.com>
  2002-02-16 18:37 ` Re: More problems with 7.2 & functions Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-16 18:52 ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Josh Berkus @ 2002-02-16 18:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

Tom:

Thanks for the answer on Readline.

Now, more trouble upgrading ...
I'm getting this error:

ERROR: replace_vars_with_subplan_refs: variable not in subplan target
 list

It happens whenever I try to execute a function that worked perfectly
 under 7.1.3.  The function builds a query out of VARCHAR strings and
 then EXECUTES it. 

I've tried running VACUUM ANALYZE on the database, and re-building ther
 function.  No effect. 

Sorry for e-mailing you directly, but I promised the client they'd have
 the machine back at 8am on monday ...

-Josh Berkus

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: More problems with 7.2 & functions
  2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
@ 2002-02-16 18:37 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-02-16 18:37 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: More problems with 7.2 & functions
  2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
@ 2002-02-16 18:52 ` Josh Berkus <josh@agliodbs.com>
  2002-02-16 19:06   ` Re: More problems with 7.2 & functions Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2002-02-16 18:52 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

Folks, Tom:

More on that error:

The error:


Is being caused by a view referenced in the function.  As I said
 before, this view worked under Postgres 7.1.3 but now it fails under
 7.2.  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.

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 status USING (status)) JOIN
 text_lists ON ((invoices.invoice_type = text_lists.list_id))) JOIN
 orders 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 ((((invoices
 JOIN status USING (status)) JOIN text_lists ON ((invoices.invoice_type
 = text_lists.list_id))) JOIN clients ON ((invoices.ref_usq =
 clients.usq))) JOIN clients cl2 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_usq FROM (((((invoices JOIN
 status 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 ~* '^per
 temp'::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 JOIN
 vw_bills_pending vbp ON ((clients.usq = vbp.client_usq)));

Grazie!

-Josh


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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: More problems with 7.2 & functions
  2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  2002-02-16 18:52 ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
@ 2002-02-16 19:06   ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-16 19:58     ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2002-02-16 19:06 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: More problems with 7.2 & functions
  2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  2002-02-16 18:52 ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  2002-02-16 19:06   ` Re: More problems with 7.2 & functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-16 19:58     ` Josh Berkus <josh@agliodbs.com>
  2002-02-16 20:02       ` Re: More problems with 7.2 & functions Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2002-02-16 19:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

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 further
 sleuthing on my own.  Here's the source of the problem:

The last select of the union view includes a custom function in the
 SELECT clause.  This function works on its own.  It works in the
 SELECT clause when called from the command line.  It also works if I
 call 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 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), and I have a workaround,  I will document the
 circumstances:

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

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

*sigh*.  Only me ....

Thanks for you patience, Tom.

-Josh









^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: More problems with 7.2 & functions
  2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  2002-02-16 18:52 ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
  2002-02-16 19:06   ` Re: More problems with 7.2 & functions Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-16 19:58     ` Re: More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
@ 2002-02-16 20:02       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-02-16 20:02 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-02-16 20:02 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-16 18:10 More problems with 7.2 & functions Josh Berkus <josh@agliodbs.com>
2002-02-16 18:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-16 18:52 ` Josh Berkus <josh@agliodbs.com>
2002-02-16 19:06   ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-16 19:58     ` Josh Berkus <josh@agliodbs.com>
2002-02-16 20:02       ` Tom Lane <tgl@sss.pgh.pa.us>

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