Thread: PHP and COPY

PHP and COPY

From
DeJuan Jackson
Date:
Does anyone know how to execute a COPY from PHP?
I keep getting parse errors on all the data.

PHP 4.3.2
PostgreSQL 7.3.4

example:

CREATE table tmp(data text);

COPY tmp FROM STDIN;
test1
test2
test3
\.



Re: PHP and COPY

From
DeJuan Jackson
Date:
such as?

To answer your question.  I have a order system on my local Postgres
(for multiple clients 50+), and need to have an offsite independent
database to drive orders for a website (for one client).
None of the replication solutions I have seen (at a resonable price)
allow me to limit the data being replicated by a specific field (which I
must do from a liability standpoint), so I'm rolling my own replication
using php as the scripting language.  I've got the data being
transferred using inserts just fine, but I would suspect a 50-300%
improvement in transfer speed if I could resort to COPY instead (I'm
inserting records in transactions of 500 rows each for testing).  One of
the tables that I need to replicate is a Multi-Gig table (which is about
the same size as all the others combined).

Joshua D. Drake wrote:

> Hello,
>
>  Why in the world would you want to do this? It seems that there
> should be a better way.
>
> Sincerely,
>
> Joshua Drake
>
> DeJuan Jackson wrote:
>
>> Does anyone know how to execute a COPY from PHP?
>> I keep getting parse errors on all the data.
>>
>> PHP 4.3.2
>> PostgreSQL 7.3.4
>>
>> example:
>>
>> CREATE table tmp(data text);
>>
>> COPY tmp FROM STDIN;
>> test1
>> test2
>> test3
>> \.
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>
>
>



Re: PHP and COPY

From
"Joshua D. Drake"
Date:
 > To answer your question.  I have a order system on my local Postgres
(for multiple clients 50+), and need to have an offsite independent
database to drive orders for a website (for one client).

> None of the replication solutions I have seen (at a resonable price)
> allow me to limit the data being replicated by a specific field (which
> I must do from a liability standpoint), so I'm rolling my own
> replication using php as the scripting language.  I've got the data
> being transferred using inserts just fine, but I would suspect a
> 50-300% improvement in transfer speed if I could resort to COPY
> instead (I'm inserting records in transactions of 500 rows each for
> testing).  One of the tables that I need to replicate is a


You would definatley see an improvement from using COPY but you could
have other problems. COPY does not happen within a transaction block it
just kind of shoves everything in there. You are going to be better
served in
the long run doing inserts.

Sincerely,


Joshua Drake



> Multi-Gig table (which is about the same size as all the others
> combined).
>
> Joshua D. Drake wrote:
>
>> Hello,
>>
>>  Why in the world would you want to do this? It seems that there
>> should be a better way.
>>
>> Sincerely,
>>
>> Joshua Drake
>>
>> DeJuan Jackson wrote:
>>
>>> Does anyone know how to execute a COPY from PHP?
>>> I keep getting parse errors on all the data.
>>>
>>> PHP 4.3.2
>>> PostgreSQL 7.3.4
>>>
>>> example:
>>>
>>> CREATE table tmp(data text);
>>>
>>> COPY tmp FROM STDIN;
>>> test1
>>> test2
>>> test3
>>> \.
>>>
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 7: don't forget to increase your free space map settings
>>
>>
>>
>>
>



Re: PHP and COPY

From
DeJuan Jackson
Date:
Not really, if I was using COPY I wouldn't need the transaction.  The
reason I use the transaction is to make the inserts faster, not for any
integrity issues.

Joshua D. Drake wrote:

> > To answer your question.  I have a order system on my local Postgres
> (for multiple clients 50+), and need to have an offsite independent
> database to drive orders for a website (for one client).
>
>> None of the replication solutions I have seen (at a resonable price)
>> allow me to limit the data being replicated by a specific field
>> (which I must do from a liability standpoint), so I'm rolling my own
>> replication using php as the scripting language.  I've got the data
>> being transferred using inserts just fine, but I would suspect a
>> 50-300% improvement in transfer speed if I could resort to COPY
>> instead (I'm inserting records in transactions of 500 rows each for
>> testing).  One of the tables that I need to replicate is a
>
>
>
> You would definatley see an improvement from using COPY but you could
> have other problems. COPY does not happen within a transaction block
> it just kind of shoves everything in there. You are going to be better
> served in
> the long run doing inserts.
>
> Sincerely,
>
>
> Joshua Drake
>
>
>
>> Multi-Gig table (which is about the same size as all the others
>> combined).
>>
>> Joshua D. Drake wrote:
>>
>>> Hello,
>>>
>>>  Why in the world would you want to do this? It seems that there
>>> should be a better way.
>>>
>>> Sincerely,
>>>
>>> Joshua Drake
>>>
>>> DeJuan Jackson wrote:
>>>
>>>> Does anyone know how to execute a COPY from PHP?
>>>> I keep getting parse errors on all the data.
>>>>
>>>> PHP 4.3.2
>>>> PostgreSQL 7.3.4
>>>>
>>>> example:
>>>>
>>>> CREATE table tmp(data text);
>>>>
>>>> COPY tmp FROM STDIN;
>>>> test1
>>>> test2
>>>> test3
>>>> \.
>>>>
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 7: don't forget to increase your free space map settings
>>>
>>>
>>>
>>>
>>>
>>
>



Re: PHP and COPY

From
Adam Witney
Date:
Try something along these lines..... (this snippet is untested but should
get you started)


$dbh = pg_connect($connstr);

$sql = "COPY table (field1, field2, field3) from stdin;";

$result = pg_query($dbh, $sql);

$data = "blah\tblah\tblah\n";

$stat = pg_put_line($dbh, $data);

if(!$stat)
    {
     echo "copy failed: ".pg_errormessage($dbh)."<br>\n";
    }

pg_put_line($dbh, "\\.\n");
pg_end_copy($dbh);





> Does anyone know how to execute a COPY from PHP?
> I keep getting parse errors on all the data.
>
> PHP 4.3.2
> PostgreSQL 7.3.4
>
> example:
>
> CREATE table tmp(data text);
>
> COPY tmp FROM STDIN;
> test1
> test2
> test3
> \.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings