On 06/07/10 17:47, Davor J. wrote: > Thanks Craig. > > I still find it a bit awkward that we have to use "priv check function"-s > because we can't define triggers on or reference to system tables. I think > that allowing it would significantly extend Postgres possibilities. Certainly being able to have fkey references to system tables and have triggers on them - or select system tables at least - would both be a bonus. > From a quick google it seems that triggers on system tables is allowed in > MySQL. > > So, all this just leaves me wondering why this is not possible in Postgres. I don't know all the reasons (its complicated) but I do know one big reason: many of the system tables are shared across the cluster. They're not specific to one particular database. Where would a trigger fire? Could triggers fire in multiple databases? What transaction context would these triggers run in? It's ... ugly. There's ongoing talk about trying to reduce the number of cluster-wide system tables - in particular, to permit user lists specific to a single database. At least one other issue is that many system tables are accessed and modified via non-SPI interfaces below the SQL level. Triggers don't make sense. AFAIK such direct access is sometimes done for performance reasons, sometimes historical reasons, and sometimes because access is required during setup/init before the SPI is ready to use. Knowing MySQL, I wouldn't be too shocked to discover that triggers are permitted on system tables ... but don't always work how you'd expect. That said, it might simply be designed to direct all modifications to those tables through SQL-level interfaces and to cleanly handle triggers cancelling or altering the effects of the queries issued. I doubt it. One thing that helps MySQL support system table triggers is that MySQL "databases" are like PostgreSQL "schemas" - they're just namespaces. Every database in MySQL is accessible from every other database, they can be combined in queries, etc. There's no equivalent of the shared-system-table problem. ( Personally I don't really understand why Pg has its cluster-and-databases design. Are there advantages I can't see over the schema-like thin databases design used by MySQL ? ) -- Craig Ringe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
