Direct execution of COPY command

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Tarifer
Posts: 24
Joined: Thu 26 May 2011 08:13

Direct execution of COPY command

Post by Tarifer » Mon 05 Feb 2018 09:23

Hello!

I want execute direct loading of data (not PgLoader) with:

Code: Select all

COPY table_name FROM STDIN
How can I do it?

I have Professional license (no source code).
I use PgLoader now, but it works too slow and use 100% CPU on client. If I can use direct COPY, it should be many faster.


Thanks

nickbarnes
Posts: 18
Joined: Mon 18 Nov 2013 01:37

Re: Direct execution of COPY command

Post by nickbarnes » Thu 08 Feb 2018 01:59

It's complicated, and a bit fragile, but possible. Mine looks something like this:

Code: Select all

uses Classes, Math, PgAccess, PgClasses, PgError, PgSQLNet, PgSQLProtocol, DBAccess, CRTypes;

procedure StreamToTable(Connection: TPgConnection; TableName: String; Stream: TStream);
const
  C_BufferSize = 256*1024; // Experimentally determined optimum; yours may be different
  C_ClientErrorMessage = 'COPY terminated by client';
var
  Net: TPgSQLNet;
  Buffer: TBytes;
  ByteCount: Integer;
  Query: TPgQuery;
  Protocol: TPgSQLProtocol;
begin
  Connection.Connect; // Make sure IConnection is assigned
  Protocol := (TDBAccessUtils.GetIConnection(Connection) as TPgSQLConnection).GetProtocol;

  Query := TPgQuery.Create(nil);
  try
    Query.Connection := Connection;
    Query.SQL.Text := 'COPY ' + TableName + ' FROM STDIN';
    Query.Prepare; // Necessary to suppress automatic UnPrepare while we're in COPY mode
    Query.ExecSQL;
    try
      Net := nil;
      while Stream.Position < Stream.Size do begin
        Protocol.BeginCopyDataBlock(Net);
        try
          ByteCount := Min(Stream.Size - Stream.Position, C_BufferSize);
          SetLength(Buffer, ByteCount);
          Stream.ReadBuffer(Buffer, ByteCount);
          Net.WriteBytes(TValueArr(Buffer), 0, ByteCount);
        finally
          Protocol.EndCopyDataBlock;
        end;
      end;
      Protocol.PutCopyEnd;
    except
      on E: Exception do begin
        if Protocol.ProtocolState = psCopyIn then begin
          // We're still in COPY mode, i.e. error was client-side. Tell the server.
          try
            Net.WriteAnsiChar(PG_MSG_COPY_FAIL);
            Net.WriteInt32(4 + Length(C_ClientErrorMessage) + 1);
            Net.WriteAnsiString(C_ClientErrorMessage);
            Net.FlushSend;
            Net.WriteAnsiChar(PG_COMMAND_SYNC);
            Net.WriteInt32(4);
            Net.FlushSend;
            Protocol.ProcessMessageQueue;
          except
            on E: EPgError do begin
              // query_canceled is the expected response to a CopyFail; swallow it
              if E.ErrorCode <> '57014' then begin
                raise;
              end;
            end;
          end;
        end;
        raise;
      end;
    end;
  finally
    Query.Free;
  end;
end;
It's up to you to ensure that the connection's client_encoding matches the TStream's contents.

If you want the affected record count, I think that requires changes to the source (as does COPY TO STDOUT support).

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Direct execution of COPY command

Post by azyk » Fri 09 Feb 2018 14:13

You can vote for implementing the COPY FROM/TO functionality at our UserVoice: https://devart.uservoice.com/forums/104 ... ctionality . If the suggestiong receives a sufficient number of votes, we will consider the possibility of its implementation.

Tarifer
Posts: 24
Joined: Thu 26 May 2011 08:13

Re: Direct execution of COPY command

Post by Tarifer » Fri 16 Feb 2018 08:27

Thanks!
It is very trick code :)

Post Reply