Re: Trigger Display Bugs in 1.12 pgAdmin

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