Re: Extending postgres objects with attributes - Mailing list pgsql-general

"Craig Ringer" <craig@postnewspapers.com.au> wrote in message
news:4C33DC32.7080007@postnewspapers.com.au...
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>