From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Christopher A Hotchkiss <christopher(dot)a(dot)hotchkiss(at)jpmchase(dot)com> |
Cc: | "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org> |
Subject: | Re: Trigger Display Bugs in 1.12 pgAdmin |
Date: | 2010-09-24 22:54:02 |
Message-ID: | 4C9D2C0A.9040007@lelarge.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
Hi,
Le 24/09/2010 21:26, Christopher A Hotchkiss a écrit :
> To Who It May Concern,
> I am using the new 1.12 pgAdmin on Windows XP SP3 and I have notice two display inaccuracies when connecting to a Postgres 9 database.
>
> For example when creating the following trigger:
>
> CREATE TRIGGER c_aud_trg
> BEFORE INSERT OR UPDATE OR DELETE
> ON ca
> FOR EACH ROW
> EXECUTE PROCEDURE c_aud_trg_trfunc();
>
> It will get created correctly (checking with pgdump based on suggestions from the pgsql list).
>
> However it will be displayed as:
> CREATE TRIGGER c_aud_trg
> BEFORE INSERT OR UPDATE
> ON ca
> FOR EACH ROW
> EXECUTE PROCEDURE c_aud_trg_func(E'\\x');
>
> If that same trigger is dropped and re-added using what is in the database, the following shows up:
> CREATE TRIGGER c_aud_trg
> BEFORE INSERT OR UPDATE
> ON ca
> FOR EACH ROW
> EXECUTE PROCEDURE c_aud_trg_func(E'\\x5c7800');
>
Your specific issue is quite easy to fix. It's done on the attached patch.
Unfortunately, it shows another issue. The issue I found relates to the
new GUC bytea_output. Here is a quick example.
ticketx=# CREATE TRIGGER c_aud_trg2
BEFORE INSERT OR UPDATE OR DELETE
ON ca
FOR EACH ROW
EXECUTE PROCEDURE c_aud_trg_trfunc2('&');
This statement creates a trigger that executes a procedure which accepts
one argument. Let's try to get the contents of the column tgargs of the
pg_trigger table:
ticketx=# select tgargs from pg_trigger where tgname='c_aud_trg2';
tgargs
--------
\x2600
(1 row)
This is the new hex output for a bytea column (yes, tgargs is a bytea).
Let's try the old output:
ticketx=# set bytea_output to escape;
SET
ticketx=# select tgargs from pg_trigger where tgname='c_aud_trg2';
tgargs
--------
&\000
(1 row)
Much better. So, the only way to fix this, AFAICT, is to set bytea_ouput
to "escape" when connected to a 9.0 server. My question is this: should
I set the parameter at the connection start, or should I set and unset
it during the search of triggers? The latter is less error prone, but
requires to execute three more queries (SHOW bytea_output, SET
bytea_output TO escape, SET bytea_output TO old_value).
> The second issue is the ordering of 'update of' triggers. For example when creating the following trigger:
> CREATE TRIGGER ca_trig
> BEFORE UPDATE OF columnA OR DELETE
> ON ca
> FOR EACH ROW
> EXECUTE PROCEDURE c_h_trg_func ();
>
> It will be displayed as:
> CREATE TRIGGER ca_trig
> BEFORE UPDATE OR DELETE OF columnA
> ON ca
> FOR EACH ROW
> EXECUTE PROCEDURE c_h_trg_func ();
>
> This is a syntax error.
>
Fixed on the attached patch.
> On a side note, does pgAdmin or Postgres have a bug tracker like bugzilla/trac/jira/etc?
>
Postgres doesn't. pgAdmin has one, but it's readonly for most people.
Usually, I put in it each bug I'm able to reproduce. It's available at
http://code.pgadmin.org/, and your issue is here:
http://code.pgadmin.org/trac/ticket/240
Thanks for your report.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Attachment | Content-Type | Size |
---|---|---|
0001-Fix-the-reverse-engineered-query-for-triggers.patch | text/x-diff | 0 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2010-09-24 23:09:26 | Re: Trigger Display Bugs in 1.12 pgAdmin |
Previous Message | Christopher A Hotchkiss | 2010-09-24 19:26:02 | Trigger Display Bugs in 1.12 pgAdmin |