agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] Re: DatabaseMetaData bug #1196
3+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] Re: DatabaseMetaData bug #1196
@ 2005-03-03 19:16
1 sibling, 0 replies; 3+ messages in thread
From: @ 2005-03-03 19:16 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* [Pljava-dev] Re: DatabaseMetaData bug #1196
@ 2005-03-04 10:13
0 siblings, 0 replies; 3+ messages in thread
From: @ 2005-03-04 10:13 UTC (permalink / raw)
Juan Francisco Ara Monz?n wrote:
>
> Well... as linux service administrator... even a drop of information
> about a system, is a security hole. I must admit it's a bit paranoid,
> but the truth is out there...
I agree. But I still think its a different problem. The analogy is a
directory in a file system. You may be allowed to browse a directory
although all files in it are unreadable to you. Prohibiting you from
reading the directory in itself is fundamentally different from prohibit
access to its contained files.
> ...
>
> Just as a fast though, it *may* be interesting have also some kind of
> "context" listeners, notified when some important events ocurrs, for
> example DB Initialized, BD dropped, Connection opened, Connection
> closed. Connection will have then is own Session, but the context,
> IMHO, would be interesting. It'd be the place where you will be able
> to register your listeners. That way you could have Java code added,
> and possibly running code as soon as DB starts.
Not possible I'm afraid. The JVM is attached to a backend process. The
backend process is spawned when a new connection is established. Having
a JVM that spans multiple connections can only be done in a
multi-threaded system serving several connections over time. PostgreSQL
is inherently single threaded. Each time you connect to the database, a
new process, dedicated to server your connection only, is spawned. When
that process wants Java, a new JVM is started for that connection.
> ... Maybe by session you mean what I mean context, but I think session
> has more approach to "connection". If session approach is connection
> it's very important to make clarifications on documentation, since
> connections on a connection pool will have sessions that will not
> correspond always to the same transaction.
Good point. The connection and session are the same in the backend. In a
client that utilize a connection pool, several logical connections will
share the same session. Connection pools are essential if you want to
get the most out of PLJava so the documentation on this should be improved.
> >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.
>
> Well I had a small problem (maybe due to a long time without
> performing DB administration...):
> I noticed something strange when dealing with triggers under
> PostgreSQL and plJava: here is my scenario:
> Techincian table:
> <some data columns ... > <username>
> Users table:
> <username><password><type><other columns ...>
>
> FK_Reference tech/users on delete set NULL on update cascade (when you
> delete a user that techs user becomes null and if you change an entry
> on users, the referenced tech should b changed too).
>
> Strange thing comes when triggers came into play:
>
> Trigger before insert or update on technician "checkUser"
> pseudo-code:
> if updating:
> if oldUsername was not null nor empty and newUsername is distinct
> delete oldUser
> else oldUsername = emptystring //null check later on
> enf if
> if new userName is not null and differs from oldusername
> create new user
> end if
>
> end
>
> The expected behaviour (I was expecting this):
> If I made an Insert:
> all works fine, without problems
> If I made an Update:
> Trigger fires: (1st trigger "instance")
> Deleteion of oldUser
> FK_restriction fires
> set Null on the table (DB does this, not plJava)
> trigger fires again (2nd trigger "instance")
> newUserName is null and oldUsername doesn't exists
> in users, no problem, trigger does anything.
> trigger keeps on running, making new insert (1st trigger)
> all should have gone ok, but...
> in techs table, tech is null now.... really strange.
>
> With exception checks I found out the following (don't have a logger
> registered yet... doh!)
> - Exception thrown when newUsername == null and oldUsername doesn't
> exists in users (that'll fire after deletion of the user, within first
> second fire of the trigger caused by deletion on first fire of the
> trigger)
> - Exception thrown at the end of process to see value of newUserName
>
> Second exception is not thrown, so, my deduction is that first trigger
> is "halted" untill second trigger transaction ends.
>
> So, all should work fine, but... what kind of resultsets are we
> receiving on _new / _old withing TriggerData?
> Old is read_only, and _new is pesimistic*(see below) (I pressume).
I'm not sure what you mean by pessimistic here. The old and new row are
objects that allow your trigger to interact with the backend. In case of
a "before insert" or "before update" the new row can be manipulated and
if it is, that will be the row that the trigger returns to the backend
proper, i.e. that will be the row that will be the base for your insert,
update.
> With new being pessimistic translation of final SQL statements (at the
> end of the trigger) will be:
> update technics set(_newValues) where (_oldValues)
> _oldValues had changed. so there's not update anymore by the statement.
The where clause is not involved here. The backend has already decided
what row(s) to update. Remember that an UPDATE xxx WHERE yyy may result
in multiple rows being updated. Your trigger will be called once for
each row with different values of xxx.
In other words, if you change the __newValues you will still not change
what rows it is that will be updated. You don't affect what's after the
WHERE.
> Also, returned resultSes from default:connection are always read
> only... doh.. I think that will be fixed in the future (rs.deleteRow()
> would be nice...)
Perhaps. But don't hold your breath. The JDBC driver depends on the
internal SPI functions provided by PostgreSQL. The cursor that they
provide doesn't permit updates nor deletes and they are always "forward
only". PLJava can't do much about that. The only solution I can think of
(somehow maintaining row OID's) would become very complex and have a
negative impact on performance.
> >I hope that PLJava will meet your expectations. If you continue using
> >it, please subscribe to the pljava-dev at gborg.postgresql.org
> <mailto:pljava-dev at gborg.postgresql.org> mailing
> >list. It is also available as a news group at new.gmane.org
> <http://new.gmane.org; as
> >gmane.comp.db.postgresql.pljava.
>
> I'm suscribing just now but from this email. Please use jara at sgv.es
> instead of cbr600f at gmail.com
Ah, OK. I misread what you wrote previously then. I thought you wanted
it the other way around since you used the latter address when you
reported the bug.
>
> Please if don't hesiatate on telling me that this is not the right way
> to post/submit comments / questions / bugs / suggestions. I'll use bug
> forum and mail lists ahead now... but e d... well, I had some free
> time theesays and when I start to write... hum... I can't stop ;)
This is the right way. But please CC your mails to
pljava-dev at gborg.postgresql.org.
Regards,
Thomas Hallgren
^ permalink raw reply [nested|flat] 3+ messages in thread
* [Pljava-dev] Re: DatabaseMetaData bug #1196
@ 2005-03-07 17:31
1 sibling, 0 replies; 3+ messages in thread
From: @ 2005-03-07 17:31 UTC (permalink / raw)
> So Filip, should we change this to include all? current_schemas(true) is
> perhaps too limiting?
>
I agree. It's done in CVS.
Filip
PS: I've already answered before but I'm afraid that the mail has been lost
somewhere...
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-03-07 17:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-03 19:16 `
2005-03-07 17:31 `
2005-03-04 10:13 [Pljava-dev] Re: DatabaseMetaData bug #1196
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox