Bernard wrote:

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 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

Oliver and interested list members:

Thanks for the related information.

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.

In other words, COPY, and its corresponding commands in other datbase
engines are designed to transfer data using the fastest possible
method, which is typically to and from files.

For the majority JDBC users, there is no real need for a STDIN/STDOUT
option because they can always write to a file.

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.


Regards

Bernard




On Fri, 19 Aug 2005 11:10:42 +1200, you wrote:


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bernard wrote:

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.

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 2: Don't 'kill -9' the postmaster

Oliver and interested list members:

I was referring to the majority of users wanting to "bulk" load tables
not to the majority of all or whatever users who may or may not know
or care about the difference in performance between INSERT and COPY.

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.

The COPY command with STDIN or STDOUT is a speciality that the
majority of users would not normally ask for because they usually
think in terms of files and rightly so.

Comparable with a STDIN/STDOUT workaround would be to pipe input and
output to and from SQL stored procedures.

What I mean to say is that we want this to be strictly server side for
best performance and we don't want to get the client involved in the
raw processing which is in violation of any 3 tier client-server
architecture.

In addition to this, not only will the client and network be loaded
with additional processing demand, but the server load will also
increase because it has to service the JDBC interface for I/O.

The whole architectural setup for such "bulk" loading is a mess.

Regards,

Bernard


On Fri, 19 Aug 2005 12:27:01 +1200, you wrote:


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Bernard wrote:

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.

Do you have a concrete suggestion for improving bulk loading that
doesn't open security holes?

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Bernard wrote:
[...]

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 2: Don't 'kill -9' the postmaster

"Oliver Jowett" <oliver (AT) opencloud (DOT) com>
wrote:43052755.7000003 (AT) opencloud (DOT) com...
When exporting and importing data from other data sources, there maybe
many rows to be moved. In this special case, should COPY be faster than
INSERTs? Have pgsql-jdbc supported COPY?

If I read correctly, what Bernard want is COPY from/to server-side files.
That is actually a security risk for non-superuser. They may read or
overwrite
any files can read/write by postgres server process.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Bernard wrote:

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 5: don't forget to increase your free space map settings

On Fri, 19 Aug 2005, Bernard wrote:

If you want to do this without switching to a different UNIX user, can't
you already write a small SECURITY DEFINER function as a superuser that
does the copy from file based on arguments and then give permissions to
that function to the appropriate non-superusers?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Oliver Jowett <oliver (AT) opencloud (DOT) com> writes:
AFAICT, the complaint really boils down to there not being any support
for COPY-from-client in the JDBC driver. Which is definitely a feature
request, but not one directed to the server geeks ;-)

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.

regards, tom lane

---------------------------(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