Re: [JDBC] [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
On 10 January 2012 06:57, Radosław Smogura wrote: > In real world BLOBs are transfered as references, and those references are > managed in way as the trigger does. Nacked PG doesn't support deletion, Oid is > universal type so it can't be used by GC approach, unles collector will know > which Oid is LOB oid. So you just end up with a conservative collector, not an exact collector. In practice conservative collectors work OK. (You can use a subtype to identify OIDs-that-refer-to-a-LO as suggested elsewhere in the thread if you want an exact collector) Oliver -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
On 10 January 2012 00:29, Oliver Jowett wrote: > So I'm still confused about what you'd like to see changed in the JDBC > driver. Can you explain? Perhaps what you're looking for here is "it all just works out of the box". In that case, the missing piece seems to be that the DDL that Hibernate emits (or the DDL which you have set up by hand - I don't know how you have things set up) does not match the data model that Hibernate is expecting. If Hibernate is expecting a data model where the lifecycle of the LO is managed by the database and there's only at most one reference to a particular LO (i.e. you can't link to the same LO from multiple places), then it can set up appropriate cleanup triggers on LO columns as part of its DDL automatically. Or it could just map blobs to bytea, which might be a more natural mapping in that case anyway and doesn't require triggers etc. So you'd need to talk to the Hibernate guys about making those changes. Oliver -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
On 10 January 2012 00:06, Stefan Keller wrote:
> 2012/1/9 Oliver Jowett :
>> Otherwise, what should JDBC do differently here? Be specific. It would
>
> First, I pretty sure that Hibernate nor the Tomcat/Java GC are
> misconfigured - since it works now after having installed the trigger
> by hand.
You misunderstand - by GC I mean the process that collects garbage LOs
that are no longer referenced. I don't mean the JVM's heap GC.
You need a GC process like this if you are using LOs and not managing
their lifetimes explicitly from the application. Consider it part of
the necessary DB setup. You've already discovered the usual mechanisms
for it ('lo' or 'vacuumlo' depending on exactly what your data model
looks like).
> To become more specific read the first two sections as a first hint
> here in this official doc:
> http://www.postgresql.org/docs/current/interactive/lo.html
FWIW, that documentation is pretty old (the JDBC docs now live
separately on jdbc.postgresql.org; the JDBC references in that
appendix are mostly historical)
But I'm not sure quite what you're referring to - those docs are
fairly clear about what you need to do? Specifically:
> Now this is fine for PostgreSQL-specific applications, but standard code
> using JDBC or ODBC won't delete the objects, resulting in orphan objects —
> objects that are not referenced by anything, and simply occupy disk space.
Which is exactly my point - if you are going to use generic JDBC code
that does not explicitly delete LOs when they become detached, then
you need a separate mechanism to clean them up - that's just the way
the model works. If you want to avoid that, don't use LOs, use bytea.
So I'm still confused about what you'd like to see changed in the JDBC
driver. Can you explain?
Oliver
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
On 9 January 2012 14:29, Stefan Keller wrote:
> 2012/1/9 Oliver Jowett :
>> As a LO is independent storage that might have multiple references to> it
>> (the OID might be stored in many places), without explicit deletion> you
>> need a GC mechanism to collect unreferenced LOs eventually -> that's what
>> vacuumlo etc are doing.
> I can follow that. But that's not what the JDBC user expects nor is it
> explained (nor mentioned) in the JDBC docs.
>
> From a conceptual view I have just an entity MyWebcam with an
> attribute called image. Attribute image is of attribute cardinality
> 1:1 (and private):
>
> // Java using Hibernate/JPA:
> @Entity
> @Lob
> @Basic(fetch=FetchType.LAZY)
> public class MyWebcam {
> private byte[] image;
> private String name;
> public byte[] getImage() { return image; }
> public void setImage(byte[] _image) { image=_image; }
> // ... other stuff
> }
>
> That's the classic use case.
> Isn't it obvious that if setImage() sets another byte[] that the image
> space get's cleared by the layers below?
> And since Hibernate chose to use one variant of JDBC, it's also JDBC
> which has to take care about orphans.
Well, either the Hibernate mapping is misconfigured, or your database
is misconfigured i.e. you are not collecting garbage LOs. If you have
a suitable GC mechanism configured, then what happens?
Otherwise, what should JDBC do differently here? Be specific. It would
be helpful if you could provide a native JDBC example, rather than a
Hibernate example, since it's not clear what JDBC calls are being made
by Hibernate.
Oliver
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues
On 9 January 2012 12:40, Stefan Keller wrote: > There's an important principle that the code which "allocates" > resources is also responsible to release it. That's one resource allocation model, yes. The other common model is that resources are freed when there are no remaining references to them, i.e. a GC model. > AFAIK in this case it's JDBC which choses to use LO (which creates > pg_largeobjects entries) and it's therefore also JDBC which has to > clean up. If the application calls LargeObjectManager.create() then it's also responsible for eventually calling LargeObjectManager.unlink(). If you're using JDBC's Blob API, there's no API there to tell the driver to actually delete the underlying data (there is Blob.free(), but that appears to just be about freeing local resources, not the underlying storage). As a LO is independent storage that might have multiple references to it (the OID might be stored in many places), without explicit deletion you need a GC mechanism to collect unreferenced LOs eventually - that's what vacuumlo etc are doing. What do you suggest that the driver does differently here? (Perhaps we could do something like interpret Blob.truncate(0) as "delete the blob right now" - but is that what Hibernate actually does?) (Much of this is the whole LO vs. bytea argument all over again. If you want to store data with a lifetime that's the same as the row it's embedded in, then bytea is a much better mapping) Oliver -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] How to just "link" to some data feed
Albretch Mueller wrote: On Tue, Jun 3, 2008 at 11:03 PM, Oliver Jowett <[EMAIL PROTECTED]> wrote: That's essentially the same as the COPY you quoted in your original email, isn't it? So.. what exactly is it you want to do that COPY doesn't do? ~ well, actually, not exactly; based on: ~ http://postgresql.com.cn/docs/8.3/static/sql-copy.html ~ COPY [FROM|TO] ~ import/export the data into/out of PG, so you will be essentially duplicating the data and having to synch it. This is exactly what I am trying to avoid, I would like for PG to handle the data right from the data feed As Dave said, PG won't magically keep the data up to date for you, you will need some external process to do the synchronization with the feed. That could use COPY if it wanted .. Then you said: Hmm! Doesn't PG have a way to do something like this, say in MySQL: load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, uniqComments) and even in low end (not real) DBs like MS Access? But isn't this doing exactly what PG's COPY does - loads data, once, from a local file, with no ongoing synchronization? Is there a technical reason for that, or should I apply for a RFE? Personally I don't see this sort of synchronization as something that you want the core DB to be doing anyway. The rules for how you get the data, how often you check for updates, how you merge the updates, and so on are very application specific. -O -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [JDBC] org.postgresql.util.PSQLException: An I/O error occured
surabhi.ahuja wrote: hi, dont such messages get logged to postgreslog. You might see something in the backend logs -- "unexpected client EOF" perhaps? Or just idle backend processes. It depends on exactly what is going wrong, and the timing of it. Also, is it possible to check wht firewall is dropping the connection between the client and the server. That is something you will have to diagnose yourself, it is specific to your network. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] org.postgresql.util.PSQLException: An I/O error occured
surabhi.ahuja wrote: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend. Caused by: java.net.SocketException: Broken pipe This is a network error that the driver can't do anything about. If you have a stateful firewall between the client and the server, perhaps it is dropping the connection because it has been idle. -O ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: Certainly supporting COPY via STDIN within the java code seems preferable. Why do you say that? That option does not exist because the Postgresql JDBC driver does not support it. If you raise this on pgsql-jdbc (so far, I haven't seen anything on that list from you at all..) we can look at putting support in. In fact Kris just started a thread to that end -- perhaps you would like to comment on it? -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy
Greg Stark wrote: Oliver Jowett <[EMAIL PROTECTED]> writes: Bernard was also objecting to the overhead of pushing the data down a TCP pipe when it's already available locally, I think.. I didn't find any real difference there when I compared the two methods, though. What makes you think it's necessarily available locally? Nothing in general -- that was just the case he had. -O ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Tom Lane wrote: > Oliver Jowett <[EMAIL PROTECTED]> writes: > >>It sounds like what you really want is the ability to grant something >>like FILE access without granting all superuser rights? Sounds like a >>feature request, not a bug, to me :-) > > > AFAICT, the complaint really boils down to there not being any support > for COPY-from-client in the JDBC driver. Bernard was also objecting to the overhead of pushing the data down a TCP pipe when it's already available locally, I think.. I didn't find any real difference there when I compared the two methods, though. -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Tom Lane wrote: > What is the story on JDBC COPY support, anyway? I'm aware that there's > an unofficial patch for that, but I'm not clear about why it's not made > it into the accepted version. I didn't like the whole "here is an undifferentiated stream of data" approach -- there were some JDBC interfaces we could adapt to read/write typed data. That never happened, though. I suppose we could apply a patch similar to the original one, given that there doesn't seem like much interest in a typed version, but it's likely to need rework as there's been at least one overhaul of the driver's protocol handling layer since then. -O ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > 2) Split up security risk calculations between the two directions "TO" > and "FROM" and relax security. Look at MySQL for clues. The > application developer can manage security on file system permission > level. I looked at MySQL's docs briefly and its behaviour seems almost the same as PostgreSQL's with some minor differences: - the equivalent to COPY is "LOAD DATA INFILE" - the equivalent to FROM STDIN is "LOCAL" - for non-LOCAL loads, the DB user must have FILE privilege which is "file access on server host". Given FILE privilege in MySQL, you can read existing files and create new files based on the access the server user has. It sounds like what you really want is the ability to grant something like FILE access without granting all superuser rights? Sounds like a feature request, not a bug, to me :-) Also, you better hope that there's no sensitive information readable by the server user that could be used to gain superuser access.. such as .pgpass files or info from pg_hba.conf, for example. -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > Oliver and interested list members: [...] And please fix your anti-spam system so it doesn't send me a "you must jump through these hoops to send me email" message every time please! (usual cc: to poster removed for that reason) -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > This difference of performance is the main reason for the COPY > command, and this is also the reason why bulk loading through the JDBC > interface will never match the performance of the COPY fith files > command. In some admittedly unscientific tests I see less than 10% difference between server-side COPY and client-side COPY FROM STDIN (psql's \copy command) on a 28mb input file. That's down in the per-run noise. Doing it via JDBC will undoubtably add some extra overhead, but I'd estimate that it's about the same sort of overhead as writing your data out to a file from Java in the first place takes. If you've already got the data in a file, why not just use psql's \copy command? This uses COPY FROM STDIN, reads the file as the user running psql, and does not require superuser permissions. > The whole architectural setup for such "bulk" loading is a mess. Do you have a concrete suggestion for improving bulk loading that doesn't open security holes? -O ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > The majority of JDBC users trying to bulk load tables would not want > to send the data through their connection. This connection is designed > to send commands and to transfer only as much data as necessary and as > little as possible. I don't understand why this is true at all -- for example, our application currently does bulk INSERTs over a JDBC connection, and moving to COPY has been an option I looked at in the past. Importing lots of data from a remote machine is hardly an uncommon case. > The need is only created by the limitations of the Postgres COPY > command. > > I can't see why a workaround should be developed instead of or before > fixing the COPY command. > > It works in other DB engines. I guess that other DB engines don't care about unprivileged DB users reading any file that the backend can access. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a
Bernard wrote: > I want to follow what is suggested here. How are STDIN and STDOUT > addressed when using the JDBC driver? The current JDBC driver doesn't support this mode of COPY. There was some work done in the past to support this but it never got to the point of making it into the official driver; see the pgsql-jdbc archives for details. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [JDBC] pg_locks.transaction field type
Joseph Shraibman wrote: The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. Is xid a type of number? It's an internal backend type; I'm not familiar with the details. Perhaps your server should convert instances of PGobject to their string representations before returning them across RMI. That's what I'll do. Are there any other classes besides org.postgresql.util.PGobject that I have to worry about? There are other classes for things like intervals and the geometric types, but they should all be subclasses of PGobject. -O ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] pg_locks.transaction field type
Joseph Shraibman wrote: Is it a jdbc bug that is returning the answer as org.postgresql.util.PGobject instead of some kind of Number? The column's type is 'xid' which the driver doesn't currently handle, so it gets put into the "wrap it in PGobject" bucket. I'm not sure what's changed between 7.4 & 8.0 -- did you also change JDBC driver versions? Perhaps your server should convert instances of PGobject to their string representations before returning them across RMI. -O ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [JDBC] Bad plan for queries with IN clause
Csaba Nagy wrote: > However, some of the queries still take the sequential scan route. The > most puzzling in all this is that I've tried to "prepare" the same query > in psql, and then "explain execute" the prepared query, and it gave me > an index scan plan... so now I'm clueless, and have no idea why would > the same query prepared by java yield a different plan than prepared > manually... I thought that the query plan is created when you prepare > the statement, and not on each execution, right ? And I would expect > that the same query prepared multiple times would give the same plan, > provided that the tables didn't change significantly... One gotcha is that by default the JDBC driver will use an unnamed statement for the first few executions of a particular PreparedStatement, then switch to using a (reused) named statement thereafter. The unnamed statement path can result in different plans to what you'd get with PREPARE or a named statement, as it delays planning until the first execution, then takes the actual parameter values into account when doing selectivity estimates. In contrast PREPARE and named statements plan immediately using placeholder estimates. You can tweak the threshold for this on a per-connection or per-statement basis via PGConnection.setPrepareThreshold() and PGStatement.setPrepareThreshold() (statements inherit the connection's value on creation by default). The connection default is also settable via the prepareThreshold URL parameter. If you set it to 1, *every* PreparedStatement execution uses a named statement. If you set it to 0, named statements are never used. > Could it be that the JDBC driver is > preparing with wrong parameter types ? I thought 8.0 is more forgiving > in this respect anyway. One thing that may be useful for debugging this: if you set logLevel=2 as a URL parameter the JDBC driver will log the protocol messages it sends and receives to the JDBC log writer (stderr by default) -- that includes the type OIDs and whether it's using an unnamed or a named statement. -O ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Invalid unicode in COPY problem
Madison Kelly wrote: Is there a way to store the name in raw binary? Yes: bytea. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [JDBC] Using Postgres with Latin1 (ISO8859-1)
J. Michael Crawford wrote: Encoding translations that didn't work: a) Getting encoded bytes from the result set. We tried the following block five times, once for each different encoding we were trying to test with the database: b) Getting a string, turning it bytes, and then translating. Same process as above, but we use result.getString... No matter what, strings showed up as gibberish in one JVM or another, depending upon the native encoding of the database. A Latin1 database worked in the windows JVM, a Unicode in the Linux JVM, but not the other way around. The "right way" is to just use getString() and not do any translation yourself. The driver has already done the transcoding from whatever the DB encoding is, to the internal UTF-16 string representation. You don't need to mess with byte-based representations. When you then display that string, you will need to use an appropriate encoding, obviously.. Can you provide a self-contained testcase that demonstrates getString() doing the wrong thing? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
