[Pljava-dev] Re: DatabaseMetaData bug #1196

Juan,
Thanks for your suggestions and comments. I'm CC'ing this reply to the
pljava-dev mailing list since it contains a lot of info that might be
relevant to the larger group.

> Well... thank you for your fast response... in fact... I started
> yestarday to use plJava and PostgreSQL... I've a short free time
> period during this week to make some I+D investigations and I decided
> to take a look at postgre/plJava.
>
> I haven't look source yet, but I'll do shortly and I noticed the bug
> trying to make some generic and funny triggers, after exploring traces
> I noticed the cause.
>
> Thus, without haveing a deep look at source or even without knowing
> much about Postgre features I can't give you any advice, except theese
> ones, with the afraid of saying something really stupid:
>
> - "" Those under public schema: I think this will be the right choice.
> Maybe would be interesting to include those not only in public schema,
> but including those with public access granted too.

The schema parameter is just "a schema name" and it does not relate to
access restrictions at all. So to clarify:

"" All tables that has been created in the public schema, regardless of
access restrictions.

> - null: This is the great point of the question. Should we (you) offer
> information about tables/columns which the user cannot access?
> (current_schemas)
> Should we offer information about those ones which the user has not
> access knowing he won't never be able to access them?

The same thing applies here. The fact that you're not allowed to access
a table doesn't mean that you're not allowed to see that it exists. Such
information can be very relevant in some cases.

So Filip, should we change this to include all? current_schemas(true) is
perhaps too limiting?

> We (you) should also think about the point that makes here the
> catalog. Before schema we should filter by catalog, and having same
> rule as this schema parameter, "" no catalog (maybe public), null
> don't include catalog in the search (another great point...), etc.
> Both schema/catalog parameters should have the same behaviour, since
> users will expect same behaviour on them.

To my knowledge PostgreSQL doesn't implement catalogs so I think we can
disregard that at present.

> A little tricky example: I have a historical FK-Values deleted table.
> What happens in a trigger if I you want to delete a row with any
> column being FK of another table, when I want to create the historic
> entrance? All users should have Insert Grant on historic (by prompt or
> even by any view), but maybe some users won't be able to access some
> schemas... the trigger should be able to "look" for any place where
> the column is referenced in order to create the log in the log table.
> This way I think, at least null should include ALL schemas / catalogs
> in the search. Although, it will be a great security hole doing so...

I agree that all tables should be included and I don't think it is a
security hole at all. As I said, it's a big difference between using
something and being allowed to see that it exists.

> Another way you can see where can we use a null == all (not only
> current) is, for example, a wizzard to make connections to database,
> netBeans for example allow you to "put" the url and then "pick" a
> catalog. (I haven't tried to log with a restricted user yet... when
> I'm developing... well... I feel like superuser ;) )
>
> One of the things that made me think "hey this postgreSQL is great for
> real programmers, no just kids" is the think of leaving a great
> freedom to programmers, although it is our responsability to avoid
> problems that other vendors solve with "mutable tables" or things like
> that.
>
> I don't know if this would satisfy your question/doubt or simply, make
> you see clearly a solution, as I told you before, I've been less than
> 24 hours using your plJava (and I think it's really GREAT! and fast
> -even being java!-) and could help much in developing better
> crossplataform apps.

I hope you will able to keep that opinion as you move forward ;-)

>
> Now it's my time to ask a question, if you don't mind, about a thing
> that came across my mind after the firsts watch on plJava:
>
> Do you think it would be possible to:
> - In a trigger, notify some kind of external container (web server,
> app server, ejb server or even a stand alone app with some registered
> listeners -of course those should be listening a socket) that some
> data has changed, to force refreshing of the data.

The notification in itself would simple since it's very easy to do a
http request from within PLJava. Just create a java.net.URL and open it.
That way you could contact a servlet that can take care of the rest.
Other options include using JMS, some home brewed RMI solution, or
perhaps plain sockets to a well known port number.

The problem with this is that you need to be careful about transaction
boundaries. If you send a signal to someone from within a trigger
stating that "this has now happened" and that in turn triggers a select
that uses another connection, that select might not see what you see
since your transaction is still pending.

A prototype that is a remedy for this type of problem exists in the
current PLJava code. The Session class (the implementation under
org.postgresql.pljava.internal only, this is not visible in the
interface yet) has methods for adding a transaction listener. Such a
listener will get called after a commit and abort. That's the time when
you should notify external resources. If you experiment with this, I'd
be *very* interested in hearing about your findings.

The prototype is not perfect. A perfect scenario would involve 2-phase
commit. That's however not yet supported by the PostgreSQL backend.
People are working on it though so it's perhaps not so far away.

> I don't know about exact uses yet, but I'm sure if somebody will be
> writting an API about this it would be a really great improvement to
> app dbs (even new JDBC beta / future implementatios will be focused in
> DB events...)
>
> Well, I think this is all by now, again, forgive me if I have said
> some really stupid -again... n00b at postgre/plJava- and thank you for
> your fast reply.
>
> Sincererly,
>
> Juan Ara.

I hope that PLJava will meet your expectations. If you continue using
it, please subscribe to the pljava-dev at gborg.postgresql.org mailing
list. It is also available as a news group at new.gmane.org as
gmane.comp.db.postgresql.pljava.

Regards,
Thomas Hallgren