pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
6+ messages / 6 participants
[nested] [flat]

* COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
@ 2005-04-06 18:46  Mischa <mischa.Sandberg@telus.net>
  0 siblings, 4 replies; 6+ messages in thread

From: Mischa @ 2005-04-06 18:46 UTC (permalink / raw)
  To: pgsql-performance

This thread seems to be focusing in on COPY efficiency,
I'd like to ask something I got no answer to, a few months ago.

Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
I accidentally strung together several \n-terminated input lines,
and sent them to the server with a single "putline".

To my (happy) surprise, I ended up with exactly that number of rows
in the target table.

Is this a bug? Is this fundamental to the protocol?

Since it hasn't been documented (but then, "endcopy" isn't documented),
I've been shy of investing in perf testing such mass copy calls.
But, if it DOES work, it should be reducing the number of network 
roundtrips.

So. Is it a feechur? Worth stress-testing? Could be VERY cool.

-- 
"Dreams come true, not free."




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
@ 2005-04-06 20:06  Dave Held <dave.held@arrayservicesgrp.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Dave Held @ 2005-04-06 20:06 UTC (permalink / raw)
  To: pgsql-performance

> -----Original Message-----
> From: Mischa [mailto:mischa.Sandberg@telus.net]
> Sent: Wednesday, April 06, 2005 1:47 PM
> To: pgsql-performance@postgresql.org
> Subject: COPY Hacks (WAS: RE: [PERFORM] Postgresql vs 
> SQLserver for this
> application ?)
> 
> [...]
> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".
> 
> To my (happy) surprise, I ended up with exactly that number of
> rows in the target table.
> 
> Is this a bug? Is this fundamental to the protocol?

Just guessing without looking at the code, I assume that the
server doesn't care if you send your data in lines, words, or
massive blocks.  It simply looks for the newline terminator to
determine end-of-block.  The reason putline works nicely is
probably that it terminates your rows with a newline character.
But as you noticed, you can do that yourself.  I would say that
it's intrinsic to the way I/O is typically done.  It may very
well be that the function that implements COPY never sees when
exactly you make a function call from Perl, but only sees a
buffer getting filled up with data that it needs to parse.  From
that perspective, it's easy to see why you simply need to 
properly terminate your rows to get the expected behavior.
Consider COPYing from a file...odds are it doesn't read data
from the file exactly 1 row at a time, but rather some block-
size multiple at a time.  The only way COPY could work correctly
is if it ignored the size of data sent to it and only parsed on
\n boundaries.

> Since it hasn't been documented (but then, "endcopy" isn't 
> documented), I've been shy of investing in perf testing such
> mass copy calls. But, if it DOES work, it should be reducing
> the number of network roundtrips.
> [...]

Feel free to use your technique.  I would be *extremely* 
surprised if there were a reason it shouldn't work.

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
@ 2005-04-06 20:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mischa <mischa.Sandberg@telus.net>
  3 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2005-04-06 20:27 UTC (permalink / raw)
  To: Mischa <mischa.Sandberg@telus.net>; +Cc: pgsql-performance

Mischa <mischa.Sandberg@telus.net> writes:
> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".

> To my (happy) surprise, I ended up with exactly that number of rows
> in the target table.

> Is this a bug?

No, it's the way it's supposed to work.  "putline" really just sends a
stream of data ... there's no semantic significance to the number of
putline calls you use to send the stream, only to the contents of the
stream.  (By the same token, it's unlikely that deliberately aggregating
such calls would be much of a win.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this
@ 2005-04-07 02:04  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Mischa <mischa.Sandberg@telus.net>
  3 siblings, 0 replies; 6+ messages in thread

From: Christopher Kings-Lynne @ 2005-04-07 02:04 UTC (permalink / raw)
  To: Mischa <mischa.Sandberg@telus.net>; +Cc: pgsql-performance

> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".
> 
> To my (happy) surprise, I ended up with exactly that number of rows
> in the target table.
> 
> Is this a bug? Is this fundamental to the protocol?
> 
> Since it hasn't been documented (but then, "endcopy" isn't documented),
> I've been shy of investing in perf testing such mass copy calls.
> But, if it DOES work, it should be reducing the number of network 
> roundtrips.

I think it's documented in the libpq docs...

Chris



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
@ 2005-04-07 12:21  Harald Fuchs <use_reply_to@protecting.net>
  parent: Mischa <mischa.Sandberg@telus.net>
  3 siblings, 0 replies; 6+ messages in thread

From: Harald Fuchs @ 2005-04-07 12:21 UTC (permalink / raw)
  To: pgsql-performance

In article <1112813199.42542e8f17b4d@webmail.telus.net>,
Mischa <mischa.Sandberg@telus.net> writes:

> This thread seems to be focusing in on COPY efficiency,
> I'd like to ask something I got no answer to, a few months ago.

> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".

> To my (happy) surprise, I ended up with exactly that number of rows
> in the target table.

> Is this a bug? Is this fundamental to the protocol?

> Since it hasn't been documented (but then, "endcopy" isn't documented),
> I've been shy of investing in perf testing such mass copy calls.
> But, if it DOES work, it should be reducing the number of network 
> roundtrips.

> So. Is it a feechur? Worth stress-testing? Could be VERY cool.

Using COPY from DBD::Pg _is_ documented - presumed you use DBD::Pg
version 1.41 released just today.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
@ 2005-04-08 02:03  Greg Sabino Mullane <greg@turnstep.com>
  parent: Mischa <mischa.Sandberg@telus.net>
  3 siblings, 0 replies; 6+ messages in thread

From: Greg Sabino Mullane @ 2005-04-08 02:03 UTC (permalink / raw)
  To: pgsql-performance


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".
...
> So. Is it a feechur? Worth stress-testing? Could be VERY cool.

As explained elsewhere, not really a feature, more of a side-effect.
Keep in mind, however, that any network round-trip time saved has to
be balanced against some additional overhead of constructing the
combined strings in Perl before sending them over. Most times COPY
is used to parse a newline-separated file anyway. If you have a slow
network connection to the database, it *might* be a win, but my
limited testing shows that it is not an advantage for a "normal"
connection: I added 1 million rows via COPY using the normal way
(1 million pg_putline calls), via pg_putline of 1000 rows at a
time, and via 10,000 rows at a time. They all ran in 22 seconds,
with no statistical difference between them. (This was the "real" time,
the system time was actually much lower for the combined calls).

It can't hurt to test things out on your particular system and see
if it makes a real difference: it certainly does no harm as long as
you make sure the string you send always *end* in a newline.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504072201
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCVeZrvJuQZxSWSsgRAoP+AJ9jTNetePMwKv9rdyu6Lz+BjSiDOQCguoSU
ie9TaeIxUuvd5fhjFueacvM=
=1hWn
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-04-08 02:03 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-06 18:46 COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?) Mischa <mischa.Sandberg@telus.net>
2005-04-06 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-07 02:04 ` Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2005-04-07 12:21 ` Harald Fuchs <use_reply_to@protecting.net>
2005-04-08 02:03 ` Greg Sabino Mullane <greg@turnstep.com>
2005-04-06 20:06 Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?) Dave Held <dave.held@arrayservicesgrp.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox