Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>What's happening here may be that your trigger is interacting in an unexpected way with another trigger or a rule.</p> <p>If an earlier trigger <code>DELETE</code>s from a partition then does a <code>RETURN NULL</code> to prevent the action from being applied to the base table its self, later triggers do not fire because the action has been cancelled (and redirected, but the later triggers don't know that).</p> <p>A similar but more confusing situation arises with views, where <code>DO INSTEAD</code> mean queries get rewritten so they never refer to the base table in the first place.</p> <p>If you're using triggers on partitioned tables, you'll want to create them on each partition as well as the main table, or be careful about your trigger ordering. Triggers are run as <code>BEFORE</code> triggers then <code>AFTER</code> triggers in alphabetical order of trigger name.</p> <p>Given DDL:</p> <pre><code>create table demo (id integer); CREATE OR REPLACE FUNCTION cancel_tg() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE; CREATE OR REPLACE FUNCTION notice_tg() RETURNS trigger AS $$ BEGIN RAISE NOTICE 'Trigger fired'; -- This just makes it a no-op: IF tg_op = 'INSERT' OR tg_op = 'DELETE' THEN RETURN NEW; ELSE RETURN OLD; END IF; END; $$ LANGUAGE plpgsql VOLATILE; </code></pre> <p>the cancelling trigger before the notice trigger produces no message:</p> <pre><code>regress=&gt; CREATE TRIGGER aa_cancel BEFORE INSERT OR UPDATE OR DELETE ON demo FOR EACH ROW EXECUTE PROCEDURE cancel_tg(); CREATE TRIGGER regress=&gt; CREATE TRIGGER bb_notice BEFORE INSERT OR UPDATE OR DELETE ON demo FOR EACH ROW EXECUTE PROCEDURE notice_tg(); CREATE TRIGGER regress=&gt; insert into demo values (1); INSERT 0 0 </code></pre> <p>The other way around fires the notice, because <code>aa_notice</code> fires before <code>bb_cancel</code>:</p> <pre><code>regress=&gt; drop trigger bb_notice on demo; DROP TRIGGER regress=&gt; drop trigger aa_cancel on demo; DROP TRIGGER regress=&gt; CREATE TRIGGER aa_notice BEFORE INSERT OR UPDATE OR DELETE ON demo FOR EACH ROW EXECUTE PROCEDURE notice_tg(); CREATE TRIGGER regress=&gt; CREATE TRIGGER bb_cancel BEFORE INSERT OR UPDATE OR DELETE ON demo FOR EACH ROW EXECUTE PROCEDURE cancel_tg(); CREATE TRIGGER regress=&gt; insert into demo values (1); NOTICE: Trigger fired INSERT 0 0 </code></pre> <p>This means that <code>AFTER</code> triggers will never fire on tables that use trigger partitioning, and <code>BEFORE</code> triggers must be alphabetically before the partitioning trigger. Yet another way in which partitioning in PostgreSQL is a bit of an ugly hack...</p>
    singulars
    1. This table or related slice is empty.
    1. POPostgresql Partinioned Table Delete Trigger Fails
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCraig Ringer
    1. USCraig Ringer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COHello Craig. I don't know if this is a trigger interaction anyway i pasted a full SQL of this behavior from schema creation to all the steps necessary. It's here http://pastebin.com/mZBFEtaY . I can only imagine this could be a bug in the trigger processing since I'm not hacking the triggers or something like that it looks pretty ... well normal.
      singulars
      1. PO
      1. USPythonWolf
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload