1

I'm starting a new project in postgreSQL (lastest) having worked with MySQL 5.5 for a while.

In the past I've made heavy use of blackhole table to simplify my application code.
It allows me to do one insert in application code:

INSERT INTO blackhole1 (val1, val2, val3, val4 ...

CREATE TRIGGER ai_blackhole1_each AFTER INSERT ....
BEGIN
  INSERT INTO table1 (....
  INSERT INTO table2 (....
  INSERT INTO log (.....

And have a trigger in the blackhole table insert the values into different tables.

What do I use in postgreSQL to replace this functionality?

I know I can use a stored procedure, but that means that I cannot connect data-aware controls to a
blackhole-table. So I would love the stick as close to the MySQL original as possible.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • 2
    PostgreSQL [supports triggers also](http://www.postgresql.org/docs/8.1/static/sql-createtrigger.html) and you could certainly duplicate this sort of functionality easily enough. Take the second example on [this page](http://developer.postgresql.org/pgdocs/postgres/plpgsql-trigger.html), say. However, this "blackhole" thing has a serious code smell. – Jordan Running Oct 13 '11 at 08:43
  • 1
    @Jordan, where's the codesmell? How come it's OK to have a VIEW to present a multitable select as if it's a singletable select, but it's not OK to use a blackhole to present a single table insert whilst really doing a multi-table insert? – Johan Oct 13 '11 at 09:05
  • Can you imagine another developer joining your project and trying to figure out what this `blackhole1` table is, or the correct order of values to `INSERT` into it? Not to mention the fact that you're just hiding three `INSERT`s behind a fourth. And since you mentioned views, both MySQL and PostgreSQL allow `INSERT`s into views (the latter with an `INSTEAD` rule). Why not just do that? It would be a lot easier for another developer (or you) to figure out down the road. – Jordan Running Oct 13 '11 at 09:21
  • 1
    @Jordan, thanks for the INSTEAD rule, I see the potential for abuse of a blackhole, but if you write and document it properly it is not a huge issue. INSERTS into views don't work if the view does much beyond `select * from` – Johan Oct 13 '11 at 09:37
  • @Jordan I've had a look at `INSTEAD`. **`yikes`**, at least with the blackhole you know you are working with black magic, here from the application side there is no hint that there's a INSTEAD or ALSO rule that rewrites your INSERTS and UPDATES – Johan Oct 13 '11 at 09:42
  • Blackhole *looks like* code smell.. but isnt a so bad idea after all; not harder to explain/mainain than views. And what about transactions: if somewhere the inserts into the function fail, you'll have the 'blackhole' row's data for historycal purpose and/or to see what happens wrong! – Strae Oct 13 '11 at 15:24
  • 2
    http://stackoverflow.com/questions/7756617/is-a-blackhole-table-evil – Strae Oct 13 '11 at 15:32
  • @DaNieL, if anything inside the blackhole trigger fails the whole transaction will be rolled back. So **that** trick will not work, only if you write to a non-transactional table will that work. – Johan Oct 13 '11 at 18:35

1 Answers1

3

With PostgreSQL 9.1 you can create triggers the same way you can do it with MySQL. Note that it is not possible to create triggers on views in versions before 9.1.

Do you use storage engine BLACKHOLE for your blackhole tables in MySQL or is it just a name? There are no pluggable storage engines in PostgreSQL, but you can get the same behavior as with storage engine BLACKHOLE in MySQL with INSTEAD OF triggers on a view in PostgreSQL. I don't quite get your point concerning data-aware controls: afaik you don't have any data-awareness in a BLACKHOLE table (the storage engine), but on the other hand you can of course e.g. map a hibernate entity to a database view.

Whether it is a good or bad idea to use triggers to simplify application code depends on the actual use case. For example I prefer triggers over application logic for logging and auditing, because this approach offers a single solution for different applications connecting to the database as well as for ad hoc queries/statements by an administrator. But from my experience triggers do not remove complexity but just shift it to the database layer. This generally makes a multi-layered application harder to extend and maintain.

tscho
  • 2,024
  • 15
  • 15