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