public inbox for [email protected]  

help / color / mirror / Atom feed

trigger fails
9+ messages / 3 participants
[nested] [flat]

* trigger fails
@ 2021-08-28 13:20  Roger Mason <[email protected]>
  0 siblings, 2 replies; 9+ messages in thread

From: Roger Mason @ 2021-08-28 13:20 UTC (permalink / raw)
  To: pgsql-novice

Hello,

I want to trigger this function on insert to an existing table:

CREATE OR REPLACE FUNCTION get_final_energy (id text)
  RETURNS TABLE (
    jid text,
    "timestamp" text,
    machine text,
    scf integer,
    energy double precision
  )
  AS $function$
BEGIN
  RETURN query WITH a AS (
    SELECT
      --      public.results.jid AS ajid,
      regexp_split_to_table(public.results.totenergy_out, '\n') AS teo
    FROM
      public.results
    WHERE
      public.results.jid = id
),
b AS (
  SELECT
    public.results.jid,
    public.results. "timestamp" AS timestamp,
    public.results.machine AS machine,
    cast(
      CASE WHEN split_part(a.teo, ' ', 2) = '' THEN
        '0'
      ELSE
        split_part(a.teo, ' ', 2)
      END AS integer) AS scf,
    cast(
      CASE WHEN split_part(a.teo, ' ', 3) = '' THEN
        '0.0'
      ELSE
        split_part(a.teo, ' ', 3)
      END AS double precision) AS energy
  FROM
    public.results,
    a
  WHERE
    public.results.jid = id
  GROUP BY
    public.results.jid,
    public.results. "timestamp",
    public.results.machine,
    a.teo
),
c AS (
  SELECT DISTINCT ON (b.jid)
    b.jid AS jid,
    b. "timestamp" AS "timestamp",
    b.machine AS machine,
    b.scf AS scf,
    b.energy AS energy
  FROM
    b
  ORDER BY
    jid,
    scf DESC
)
SELECT
  *
FROM
  c RETURN;
END;
$function$
LANGUAGE plpgsql;

There are associated trigger functions:

CREATE OR REPLACE FUNCTION trigger_final_energy_table_create ()
  RETURNS TRIGGER
  AS $$
BEGIN
  CREATE TABLE IF NOT EXISTS final_energy (
    jid text,
    "timestamp" text,
    machine text,
    scf integer,
    energy double precision
  );
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION trigger_final_energy_table_insert ()
  RETURNS TRIGGER
  AS $$
BEGIN
  INSERT INTO final_energy
  SELECT
    *
  FROM
    get_final_energy (NEW.jid);
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

With these triggers:

CREATE TRIGGER atrigger_final_energy_table_create
  AFTER INSERT ON results
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_final_energy_table_create ();

CREATE TRIGGER btrigger_final_energy_table_insert
  AFTER INSERT ON results
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_final_energy_table_insert ();

All this code seems to run when I insert data into the 'results' table,
the 'final_energy' table gets created with the specifiled columns but no
data are inserted.

I have tried various modifications of get_final_energy without success.

If someone could point me to an abvious flaw or suggest how to debug
this it woould be most helpful.

Thanks for reding this long message,
Roger





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

* Re: trigger fails
@ 2021-08-28 14:38  David G. Johnston <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 0 replies; 9+ messages in thread

From: David G. Johnston @ 2021-08-28 14:38 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Saturday, August 28, 2021, Roger Mason <[email protected]> wrote:

>
> I have tried various modifications of get_final_energy without success.
>

If get_final_energy() isn’t working as you expect (and its definition is
highly suspect so I wouldn’t be surprised) then all of this talk about
triggers is just adding unnecessary distraction.  Don’t add triggers to the
mix until you know the components are all functioning correctly.

I’d also suggest not doing create table if not exists in a trigger.
Whatever code you use to install the triggers should create the table
immediately and then the trigger just assumes it is present.

As for debugging the big query, you just need to learn by doing.  Start
writing it again from scratch and when you get to a point where it provide
unusual results stop and figure out why.

David J.


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

* Re: trigger fails
@ 2021-08-28 15:50  David G. Johnston <[email protected]>
  parent: Roger Mason <[email protected]>
  1 sibling, 1 reply; 9+ messages in thread

From: David G. Johnston @ 2021-08-28 15:50 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Sat, Aug 28, 2021 at 7:04 AM Roger Mason <[email protected]> wrote:

>
>     get_final_energy (NEW.jid);
>
>
If you want to use this function within a trigger it should be redefined to
accept "NEW" (i.e., the table record itself), not the jid column (IOW,
don't perform a select on the table during the middle of the trigger
execution).

David J.


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

* Re: trigger fails
@ 2021-08-29 12:28  Roger Mason <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 1 reply; 9+ messages in thread

From: Roger Mason @ 2021-08-29 12:28 UTC (permalink / raw)
  To: pgsql-novice

Hello,

David G. Johnston writes:

> On Sat, Aug 28, 2021 at 7:04 AM Roger Mason <[email protected]> wrote:
>
>>
>>     get_final_energy (NEW.jid);
>>
>>
> If you want to use this function within a trigger it should be redefined to
> accept "NEW" (i.e., the table record itself), not the jid column (IOW,
> don't perform a select on the table during the middle of the trigger
> execution).

I have not been able to get this to work: the trigger runs and the
trigger function is called but no new data are inserted into the target
table.  Having spent several days on this without result it is time to
give up.

Thanks again for your help.

Roger





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

* Re: trigger fails
@ 2021-08-30 08:17  hubert depesz lubaczewski <[email protected]>
  parent: Roger Mason <[email protected]>
  0 siblings, 1 reply; 9+ messages in thread

From: hubert depesz lubaczewski @ 2021-08-30 08:17 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Sun, Aug 29, 2021 at 09:58:09AM -0230, Roger Mason wrote:
> I have not been able to get this to work: the trigger runs and the
> trigger function is called but no new data are inserted into the target
> table.  Having spent several days on this without result it is time to
> give up.

Can you provide whole, testable sql script? With create table, and test
insert, that should generate something in the target table, that is
missing?

Best regards,

depesz






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

* Re: trigger fails
@ 2021-08-30 12:09  hubert depesz lubaczewski <[email protected]>
  parent: hubert depesz lubaczewski <[email protected]>
  0 siblings, 1 reply; 9+ messages in thread

From: hubert depesz lubaczewski @ 2021-08-30 12:09 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Mon, Aug 30, 2021 at 09:34:46AM -0230, Roger Mason wrote:
> > Can you provide whole, testable sql script? With create table, and test
> > insert, that should generate something in the target table, that is
> > missing?

it's better to reply to mailing list, unless it's a secret, as others
can comment and possibly provide answer.

In any case, after running your queries, I got:

ERROR:  relation "results" does not exist

This was on test script line 78, which was:

CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
EXECUTE PROCEDURE trigger_f_energy_insert ();

So, can't test it, sorry.

Best regards,

depesz






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

* Re: trigger fails
@ 2021-08-30 13:33  Roger Mason <[email protected]>
  parent: hubert depesz lubaczewski <[email protected]>
  0 siblings, 1 reply; 9+ messages in thread

From: Roger Mason @ 2021-08-30 13:33 UTC (permalink / raw)
  To: [email protected]; +Cc: pgsql-novice

Hello again,

hubert depesz lubaczewski writes:

> it's better to reply to mailing list, unless it's a secret, as others
> can comment and possibly provide answer.
>
> In any case, after running your queries, I got:
>
> ERROR:  relation "results" does not exist
>
> This was on test script line 78, which was:
>
> CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
> EXECUTE PROCEDURE trigger_f_energy_insert ();
>
> So, can't test it, sorry.

This should be sufficient:

create table results (timestamp text,jid text,machine text,info_out
text);

Again please don't waste too much time on this.

Best wishes,
Roger





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

* Re: trigger fails
@ 2021-08-30 13:36  hubert depesz lubaczewski <[email protected]>
  parent: Roger Mason <[email protected]>
  0 siblings, 1 reply; 9+ messages in thread

From: hubert depesz lubaczewski @ 2021-08-30 13:36 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Mon, Aug 30, 2021 at 11:03:33AM -0230, Roger Mason wrote:
> Hello again,
> 
> hubert depesz lubaczewski writes:
> 
> > it's better to reply to mailing list, unless it's a secret, as others
> > can comment and possibly provide answer.
> >
> > In any case, after running your queries, I got:
> >
> > ERROR:  relation "results" does not exist
> >
> > This was on test script line 78, which was:
> >
> > CREATE TRIGGER trigger_f_energy_insert AFTER INSERT ON results FOR EACH ROW
> > EXECUTE PROCEDURE trigger_f_energy_insert ();
> >
> > So, can't test it, sorry.
> 
> This should be sufficient:
> 
> create table results (timestamp text,jid text,machine text,info_out
> text);

Now your script fails with:
ERROR:  column results.totenergy_out does not exist

If you want help, please, pretty please with cherry on top - make
*usable* test script. 
Write it, test by psql -f ..., and then send if it:
1. works
2. shows the problem

Best regards,

depesz






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

* Re: trigger fails
@ 2021-09-01 17:56  Roger Mason <[email protected]>
  parent: hubert depesz lubaczewski <[email protected]>
  0 siblings, 0 replies; 9+ messages in thread

From: Roger Mason @ 2021-09-01 17:56 UTC (permalink / raw)
  To: pgsql-novice

Hello,

hubert depesz lubaczewski writes:

> Now your script fails with:
> ERROR:  column results.totenergy_out does not exist
>
> If you want help, please, pretty please with cherry on top - make
> *usable* test script. 
> Write it, test by psql -f ..., and then send if it:
> 1. works
> 2. shows the problem

I apologise for sending an unworkable script and wasting your time.

Thank you for your help.  I now have a version that works correctly,
although with limited testing so far.

Best wishes,
Roger





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

end of thread, other threads:[~2021-09-01 17:56 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-28 13:20 trigger fails Roger Mason <[email protected]>
2021-08-28 14:38 ` David G. Johnston <[email protected]>
2021-08-28 15:50 ` David G. Johnston <[email protected]>
2021-08-29 12:28   ` Roger Mason <[email protected]>
2021-08-30 08:17     ` hubert depesz lubaczewski <[email protected]>
2021-08-30 12:09       ` hubert depesz lubaczewski <[email protected]>
2021-08-30 13:33         ` Roger Mason <[email protected]>
2021-08-30 13:36           ` hubert depesz lubaczewski <[email protected]>
2021-09-01 17:56             ` Roger Mason <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox