Databases
StackOverflow2013
Postsdbo
POPostgres partition management with triggers. Issue altering tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgres partition management with triggers. Issue altering tables
    primarykey
    data
    text
    <p><strong>SHORT STORY:</strong> I have a partitioned postgres database with a table to track the partitions and triggers. The triggers need to alter the constraints on the partition tables (their valid_date [daterange] changes depending on the other partition's valid_date) and preferably be able to delete tables, but this is causing errors because the tables are in use by the trigger chain'.</p> <blockquote> <pre><code>cannot ALTER TABLE "core_geometryrecord_8_2" because it is being used by active queries in this session </code></pre> </blockquote> <p><strong>FULL QUESTION:</strong> <strong>I am giving a thorough explanation of my schema and trigger architecture in case it is needed or someone wants to know why I feel the need for a 'convoluted' system, though it may not be necessary.</strong></p> <p>I am refactoring a large database of geometry data to make it easier and faster to use. The old systems has 'geometry tables' that hold related geometries (I.E. Counties of the USA). The data should really be grouped by "date_valid" and the "geometrytable_id".</p> <p>We decided to use postgres partitioning with constraints on the date_valid (a date range) and the geometrytable_id (foreign key).</p> <p>Because the dates are sensitive and take a lot of book keeping to keep correct I tried my first hand at managing a large part of a DB with triggers (I already knew I needed an insert trigger for the partitioned table so why not). </p> <p>I designed a set of triggers that manages the list of partitions and does all the book keeping when you insert or remove a geometry. Here is what they do.</p> <ul> <li>On insert into partition list table, create the partition table and apply constraints. If there is a partitionvalid for a date overlapping this new one (Call Update Trigger), trim it. If there is a partition valid for a date after this one, trim new partition end date to start date of next partition (keep a continuous timeline).</li> <li>On delete from partition list table, delete the partition table. Recalculate the date ranges of the partition_list entries (Call Update Trigger) around the removed partition (if any) to make the dates continuous.</li> <li>On Update partition list table, DROP OLD DATE CONSTRAINT, change date_valid on all the rows in the partition table to the new date_valid value of the partition, then CREATE DATE CONSTRAINT with new date (constraint is that the items have the exact same date_valid as partition).</li> <li>On insert into geometryrecord (the partitioned table), find what partition should be used, or INSERT into partition list table (TRIGGERS ON INSERT). Insert NEW into the correct partition.</li> <li>On Delete from geometryrecord, if partition table is now empty, delete the partition entry from partition list table (CALLS DELETE TRIGGER).</li> </ul> <p>It keeps getting angry at me for trying to change the table in any way if I have touched it at all in any of the triggers in the chain. The partition list table's triggers work perfectly if I directly insert into that table, but deleting from a geometry table (which calls the same trigger) flips out even though all it is doing is a select (to see if the partition table is empty). Inserting into the partitioned geometry table can also cause issues because I have to remove the constraint in order to change the date_valid of the rows. </p> <p>There has to be a way to do this that I just don't understand. (I also had to make the deleting of the table a more passive 'mark it for deletion by a cron job' because I can't delete the table from a trigger call originating from the table I want to delete.)</p> <p>Any advice would be greatly welcome. I just can't believe that no one has ever needed to do something like this, so I assume I just don't know what I am doing :).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDaniel Vérité
    1. USJessy Diamond Exum
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgres partition management with triggers. Issue altering tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou are doing DDLs from inside a trigger?
      singulars
      1. POPostgres partition management with triggers. Issue altering tables
      1. USwildplasser
    2. COWoah, I guess I just learned the difference between DDL and DML (mostly that there is a difference). Yeah, I need to do alters or drops from within triggers.
      singulars
      1. POPostgres partition management with triggers. Issue altering tables
      1. USJessy Diamond Exum
 

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