Обсуждение: Reverse engineering problem on views

Hi,

I have noticed this possible bug:

Using pgadmin V1.2.0 on Windows 2000 Desktop to a remote Windows 2000
Server Postgres V7.4.1 running on cygwin and to Postgres V8Beta2

Created this view.

CREATE OR REPLACE VIEW public.test_view ASSELECT or0.actvle AS treename  FROM gnrcitm g  LEFT JOIN (gnrcitmothrref g0
JOINother_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
 
r0.catnmeclssid::text = 'Tree Name'::text) or0 ON g.gnrcitmid =
or0.gnrcitmid

Retrieved view into SQL dialog window using copy SQL option

This gave

-- View: "test_view"

-- DROP VIEW test_view;

CREATE OR REPLACE VIEW test_view ASSELECT or0.actvle AS treename  FROM gnrcitm g  LEFT JOIN ((gnrcitmothrref g0  JOIN
other_refr0 ON g0.idntfyrefid = r0.idntfyrefid AND
 
r0.catnmeclssid::text = 'Tree Name'::text) or0) ON g.gnrcitmid =
or0.gnrcitmid;

When executed this gives  ERROR:  syntax error at or near ")" at character
273.

There seems to be an error caused by the reverse engineering adding the
extra bracketting on the left join clauses. If the extra brackets are
deleted the SQLis executed correctly.

Any suggestions?

Great work pgadmin, really enjoy using it.

Regards
Laurie Burrow



:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

Hi Andreas (again!)

I haven't even looked at this one - I assume it's likely to be your
reformatting code that I never quite got my head around?

Also, to save writing another email - I notice when connecting now a
black rectangle is shown briefly. I assume this is the status message
that you added? If so, it never seems to draw properly on Windows. Dunno
about Unix.

Regards, Dave.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of
> laurie.burrow@powerconv.alstom.com
> Sent: 06 December 2004 13:25
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Reverse engineering problem on views
>
> Hi,
>
> I have noticed this possible bug:
>
> Using pgadmin V1.2.0 on Windows 2000 Desktop to a remote
> Windows 2000 Server Postgres V7.4.1 running on cygwin and to
> Postgres V8Beta2
>
> Created this view.
>
> CREATE OR REPLACE VIEW public.test_view AS  SELECT or0.actvle
> AS treename
>    FROM gnrcitm g
>    LEFT JOIN (gnrcitmothrref g0
>    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
> r0.catnmeclssid::text = 'Tree Name'::text) or0 ON g.gnrcitmid
> = or0.gnrcitmid
>
> Retrieved view into SQL dialog window using copy SQL option
>
> This gave
>
> -- View: "test_view"
>
> -- DROP VIEW test_view;
>
> CREATE OR REPLACE VIEW test_view AS
>  SELECT or0.actvle AS treename
>    FROM gnrcitm g
>    LEFT JOIN ((gnrcitmothrref g0
>    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
> r0.catnmeclssid::text = 'Tree Name'::text) or0) ON
> g.gnrcitmid = or0.gnrcitmid;
>
> When executed this gives  ERROR:  syntax error at or near ")"
> at character 273.
>
> There seems to be an error caused by the reverse engineering
> adding the extra bracketting on the left join clauses. If the
> extra brackets are deleted the SQLis executed correctly.
>
> Any suggestions?
>
> Great work pgadmin, really enjoy using it.
>
> Regards
> Laurie Burrow
>
>
>
> :.________________
> CONFIDENTIALITY : This  e-mail  and  any attachments are
> confidential and may be privileged. If  you are not a named
> recipient, please notify the sender immediately and do not
> disclose the contents to another person, use it for any
> purpose or store or copy the information in any medium.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 10 December 2004 10:44
> To: Dave Page
> Cc: laurie.burrow@powerconv.alstom.com; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Reverse engineering problem on views
>
>
> Displays correctly on both win32 and unix for me. Is your
> machine too fast? Try connecting to a non-running server. No
> problem if it just flashes on fast connections.

On a non-running server it is black for 5 seconds or so until the error
message box displays - then it draws.

My machine is a 1.7GHz Pentium M laptop, with 1GB of RAM, so it's not
the fastest, but far from unusable :-)

/D

Dave Page wrote:
> Hi Andreas (again!)
> 
> I haven't even looked at this one - I assume it's likely to be your
> reformatting code that I never quite got my head around?

Still waiting for review in my mailbox.

> Also, to save writing another email - I notice when connecting now a
> black rectangle is shown briefly. I assume this is the status message
> that you added? If so, it never seems to draw properly on Windows.

> Dunno about Unix.

Displays correctly on both win32 and unix for me. Is your machine too 
fast? Try connecting to a non-running server. No problem if it just 
flashes on fast connections.

Regards,
Andreas

laurie.burrow@powerconv.alstom.com wrote:

> Retrieved view into SQL dialog window using copy SQL option
> 
> This gave
> 
> -- View: "test_view"
> 
> -- DROP VIEW test_view;
> 
> CREATE OR REPLACE VIEW test_view AS
>  SELECT or0.actvle AS treename
>    FROM gnrcitm g
>    LEFT JOIN ((gnrcitmothrref g0
>    JOIN other_ref r0 ON g0.idntfyrefid = r0.idntfyrefid AND
> r0.catnmeclssid::text = 'Tree Name'::text) or0) ON g.gnrcitmid =
> or0.gnrcitmid;
> 
> When executed this gives  ERROR:  syntax error at or near ")" at character
> 273.

I can confirm this; the backend's pg_get_viewdef will give these 
parentheses too much with pretty-print option=true. Quite awkward, since 
this option should *suppress* additional parens...

I'll check the backend code.


Regards,
Andreas