Thread: BUG #17530: pg_dump comment on triggers is "off" by comparison to all of the other objects...

The following bug has been logged on the website:

Bug reference:      17530
Logged by:          Kirk Wolak
Email address:      wolakk@gmail.com
PostgreSQL version: 14.3
Operating system:   Windows
Description:

I've been parsing through the file for our own system, and discovered the
Name: <value>
for the trigger is not quite the name of the trigger. I've included an
example of FUNCTIon for reference.

It's "change_log"<space>"id_change_log";  but the actual trigger name is
"id_change_log" on the table "change_log"

I would have EXPECTED $ as the function has, but that's part of it's "name"
in the function case.
PSQL Output Line:
Triggers:
    id_change_log BEFORE INSERT ON logspc.change_log FOR EACH ROW WHEN
(new.id IS NULL) EXECUTE FUNCTION logspc."id_change_log$change_log"()

finally, it also "feels" like the 2 values are reversed, based on the PSQL
\d output line...
Basically, it's not the correct name.  It should simply be "id_change_log"
(IMO, to be consistent with every other comment I've seen)

Thanks in advance...

--
-- Name: pkg_dilp_log_fix$do_log(text); Type: PROCEDURE; Schema: logspc;
Owner: postgres
--

CREATE PROCEDURE logspc."pkg_dilp_log_fix$do_log"(IN ijobtype text) ... ;


########### Issue is here:
--
-- Name: change_log id_change_log; Type: TRIGGER; Schema: logspc; Owner:
postgres
--

CREATE TRIGGER id_change_log BEFORE INSERT ON logspc.change_log FOR EACH ROW
WHEN ((new.id IS NULL)) EXECUTE FUNCTION
logspc."id_change_log$change_log"();


On Wed, Jun 22, 2022 at 8:29 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17530
Logged by:          Kirk Wolak
Email address:      wolakk@gmail.com
PostgreSQL version: 14.3
Operating system:   Windows
Description:       

I've been parsing through the file for our own system, and discovered the
Name: <value>
for the trigger is not quite the name of the trigger. I've included an
example of FUNCTIon for reference.

A function is a standalone object where a trigger cannot exist without being attached to a trigger.  Extrapolating the behavior of the later from the former is not all that productive.  It definitely doesn't constitute evidence of a bug.
 

It's "change_log"<space>"id_change_log";  but the actual trigger name is
"id_change_log" on the table "change_log"

Parent -> Child is a very common way to write this kind of thing so the order seems perfectly fine.
 

I would have EXPECTED $ as the function has, but that's part of it's "name"
in the function case.

I would think introducing a symbol that physically joins the two components together would be more confusing, not less.  One might come to assume it really is the full actual name of the trigger when it is not.
 
Basically, it's not the correct name.  It should simply be "id_change_log"
(IMO, to be consistent with every other comment I've seen)

You'll have to show the table name somewhere.  If you change this to be just the object name (which has merit) what do you do with that?  No matter what you do triggers are unique and so will have some unique aspect to them.  Lacking some other similar parent->child but independent object (i.e., not columns) object to compare against, and the fact this is both structured (I am curious what it does for names that require double-quotes, can that space be relied upon to be unambiguous?) and mostly meant for human consumption, I'd be inclined to leave well enough alone.

########### Issue is here:
--
-- Name: change_log id_change_log; Type: TRIGGER; Schema: logspc; Owner:
postgres
--

CREATE TRIGGER id_change_log BEFORE INSERT ON logspc.change_log [...]

David J.
Hi,

On Wed, Jun 22, 2022 at 11:05:45PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      17530
> Logged by:          Kirk Wolak
> Email address:      wolakk@gmail.com
> PostgreSQL version: 14.3
> Operating system:   Windows
> Description:        
> 
> I've been parsing through the file for our own system, and discovered the
> Name: <value>
> for the trigger is not quite the name of the trigger. I've included an
> example of FUNCTIon for reference.
> 
> It's "change_log"<space>"id_change_log";  but the actual trigger name is
> "id_change_log" on the table "change_log"

That's expected, the name in the comment has a qualifier when the object name
itself isn't guaranteed to be unique, with the format

"$table_name $trigger_name"

This is done similarly for all object types that don't have a guarantee of
unique name (policies, rules...).



On Wed, Jun 22, 2022 at 9:11 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
Hi,

On Wed, Jun 22, 2022 at 11:05:45PM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      17530
> Logged by:          Kirk Wolak
> Email address:      wolakk@gmail.com
> PostgreSQL version: 14.3
> Operating system:   Windows
> Description:       
>
> I've been parsing through the file for our own system, and discovered the
> Name: <value>
> for the trigger is not quite the name of the trigger. I've included an
> example of FUNCTIon for reference.
>
> It's "change_log"<space>"id_change_log";  but the actual trigger name is
> "id_change_log" on the table "change_log"

That's expected, the name in the comment has a qualifier when the object name
itself isn't guaranteed to be unique, with the format

"$table_name $trigger_name"

This is done similarly for all object types that don't have a guarantee of
unique name (policies, rules...).


I think my only complaint here would be:

--
-- Name: test table 2 test trigger 2; Type: TRIGGER; Schema: testschema; Owner: vagrant
--

CREATE TRIGGER "test trigger 2" BEFORE INSERT ON testschema."test table 2" FOR EACH ROW EXECUTE FUNCTION public.do_nothing();

I suppose it's not a big deal that the name isn't parsable, but doing the equivalent of: <quote_ident($table_name) quote($trigger_name)> would have merit.

Also, the argument about name uniqueness only extends to "within a given schema" yet we placed the schema name into its own section.

Again, I don't see much need to make a change so far, but given an compelling use case not otherwise readily solvable it would be something to entertain, IMO.

David J.

Julien Rouhaud <rjuju123@gmail.com> writes:
> On Wed, Jun 22, 2022 at 11:05:45PM +0000, PG Bug reporting form wrote:
>> It's "change_log"<space>"id_change_log";  but the actual trigger name is
>> "id_change_log" on the table "change_log"

> That's expected, the name in the comment has a qualifier when the object name
> itself isn't guaranteed to be unique, with the format
> "$table_name $trigger_name"
> This is done similarly for all object types that don't have a guarantee of
> unique name (policies, rules...).

Right.  A couple further observations:

* The relevant object names here are those of the table and trigger.
The OP seems to be confusing this with the name of the function that
the trigger uses --- but that could be completely different.

* There's not been any attempt to make the comment labels be
guaranteed-unique or machine-parseable.  If, say, you made a table
or trigger name that contains a space, it'd be hard to tell by
looking at the pg_dump comment which space separates the table
name from the trigger name, versus which one(s) are part of one
of those names.  If we were doing this from scratch today we'd
probably be more rigorous ... but it's been like this for twenty-ish
years, and I doubt there's a lot of appetite for redefining it now.

            regards, tom lane