Thread: psql and COPY BINARY
Examining why psql won't do sensible stuff with COPY BINARY, I realized that psql still uses PQgetline, which is marked obsolete since 7.4. Is this intentional or just a "never reviewed because it works"? Unfortunately, psql/copy.c also states "if you want to use copy in your app, this is the code to steal". Seems not a good template code any more. Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Examining why psql won't do sensible stuff with COPY BINARY, I realized
> that psql still uses PQgetline, which is marked obsolete since 7.4.
> Is this intentional or just a "never reviewed because it works"?
There wasn't any obvious bang for the buck in rewriting it.
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Examining why psql won't do sensible stuff with COPY BINARY, I realized >>that psql still uses PQgetline, which is marked obsolete since 7.4. >>Is this intentional or just a "never reviewed because it works"? > > > There wasn't any obvious bang for the buck in rewriting it. Well a non-binary copy could take as much as 5 times as much as a binary copy. I hit this when COPYing 1.5GB of data, getting a 6.6GB file. This made the 100MBit LAN connection a bottleneck. I might post a patch to support BINARY in psql some day. Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> There wasn't any obvious bang for the buck in rewriting it.
> Well a non-binary copy could take as much as 5 times as much as a binary
> copy. I hit this when COPYing 1.5GB of data, getting a 6.6GB file. This
> made the 100MBit LAN connection a bottleneck.
Or vice versa --- the binary format is *not* necessarily smaller than text.
As an example, an integer column that contains only small values (say 1
or 2 digits) will need 8 bytes as binary and only 2 or 3 as text.
Fixing psql to handle binary copy isn't an unreasonable thing to do,
but I can't get real excited about it either ...
regards, tom lane
Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > >>Tom Lane wrote: >> >>>There wasn't any obvious bang for the buck in rewriting it. > > >>Well a non-binary copy could take as much as 5 times as much as a binary >>copy. I hit this when COPYing 1.5GB of data, getting a 6.6GB file. This >>made the 100MBit LAN connection a bottleneck. > > > Or vice versa --- the binary format is *not* necessarily smaller than text. > As an example, an integer column that contains only small values (say 1 > or 2 digits) will need 8 bytes as binary and only 2 or 3 as text. > > Fixing psql to handle binary copy isn't an unreasonable thing to do, > but I can't get real excited about it either ... Having a choice can't be too bad. A COMPRESSED option would be even better, but that's backend stuff (does TOAST use an algorithm that's platform independent?). Would have reduced the sample above to about 130MB. Regards, Andreas
I submitted a patch a while back to change that but I withdrew it because I wasn't 100% confident I'd done it right. Here is the link to it: http://archives.postgresql.org/pgsql-patches/2005-03/msg00242.php It's probably 99% there - just a bit of checking. Chris Andreas Pflug wrote: > Examining why psql won't do sensible stuff with COPY BINARY, I realized > that psql still uses PQgetline, which is marked obsolete since 7.4. > Is this intentional or just a "never reviewed because it works"? > Unfortunately, psql/copy.c also states "if you want to use copy in your > app, this is the code to steal". Seems not a good template code any more. > > > Regards, > Andreas > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
>>Examining why psql won't do sensible stuff with COPY BINARY, I realized >>that psql still uses PQgetline, which is marked obsolete since 7.4. >>Is this intentional or just a "never reviewed because it works"? > > There wasn't any obvious bang for the buck in rewriting it. The obvious one (and why I submitted a patch - see previous email) was for people who use the psql source code as sample code, which is what I was doing at the time for PHP 5.1's PostgreSQL module. Chris
Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > Examining why psql won't do sensible stuff with COPY BINARY, I realized
> > that psql still uses PQgetline, which is marked obsolete since 7.4.
> > Is this intentional or just a "never reviewed because it works"?
>
> There wasn't any obvious bang for the buck in rewriting it.
We have a TODO:
o Update pg_dump and psql to use the new COPY libpq API (Christopher)
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073