[Pljava-dev] Re: DatabaseMetaData bug #1196

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