"Craig Ringer" <craig@xxxxxxxxxxxxxxxxxxxxx> wrote in message news:4C33DC32.7080007@xxxxxxxxxxxxxxxxxxxxxxxx > 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. You have a point there. > > 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 ? ) Well, it certainly looks more advanced, as far as this might be called an advantage. I don't know by enough to judge the issue, but those are all interesting questions you bring up. Thanks. Regards, Davor > > -- > Craig Ringe > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
