pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PHP and COPY
5+ messages / 3 participants
[nested] [flat]

* PHP and COPY
@ 2003-08-15 18:15 DeJuan Jackson <djackson@speedfc.com>
  2003-08-15 19:08 ` Re: PHP and COPY DeJuan Jackson <djackson@speedfc.com>
  2003-08-16 12:21 ` Re: PHP and COPY Adam Witney <awitney@sghms.ac.uk>
  0 siblings, 2 replies; 5+ messages in thread

From: DeJuan Jackson @ 2003-08-15 18:15 UTC (permalink / raw)
  To: pgsql-php

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





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

* Re: PHP and COPY
  2003-08-15 18:15 PHP and COPY DeJuan Jackson <djackson@speedfc.com>
@ 2003-08-15 19:08 ` DeJuan Jackson <djackson@speedfc.com>
  2003-08-15 21:58   ` Re: PHP and COPY Joshua D. Drake <jd@commandprompt.com>
  1 sibling, 1 reply; 5+ messages in thread

From: DeJuan Jackson @ 2003-08-15 19:08 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; pgsql-php

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





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

* Re: PHP and COPY
  2003-08-15 18:15 PHP and COPY DeJuan Jackson <djackson@speedfc.com>
  2003-08-15 19:08 ` Re: PHP and COPY DeJuan Jackson <djackson@speedfc.com>
@ 2003-08-15 21:58   ` Joshua D. Drake <jd@commandprompt.com>
  2003-08-15 22:18     ` Re: PHP and COPY DeJuan Jackson <djackson@speedfc.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Joshua D. Drake @ 2003-08-15 21:58 UTC (permalink / raw)
  To: DeJuan Jackson <djackson@speedfc.com>; +Cc: pgsql-php

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





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

* Re: PHP and COPY
  2003-08-15 18:15 PHP and COPY DeJuan Jackson <djackson@speedfc.com>
  2003-08-15 19:08 ` Re: PHP and COPY DeJuan Jackson <djackson@speedfc.com>
  2003-08-15 21:58   ` Re: PHP and COPY Joshua D. Drake <jd@commandprompt.com>
@ 2003-08-15 22:18     ` DeJuan Jackson <djackson@speedfc.com>
  0 siblings, 0 replies; 5+ messages in thread

From: DeJuan Jackson @ 2003-08-15 22:18 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-php

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





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

* Re: PHP and COPY
  2003-08-15 18:15 PHP and COPY DeJuan Jackson <djackson@speedfc.com>
@ 2003-08-16 12:21 ` Adam Witney <awitney@sghms.ac.uk>
  1 sibling, 0 replies; 5+ messages in thread

From: Adam Witney @ 2003-08-16 12:21 UTC (permalink / raw)
  To: DeJuan Jackson <djackson@speedfc.com>; pgsql-php


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




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


end of thread, other threads:[~2003-08-16 12:21 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-15 18:15 PHP and COPY DeJuan Jackson <djackson@speedfc.com>
2003-08-15 19:08 ` DeJuan Jackson <djackson@speedfc.com>
2003-08-15 21:58   ` Joshua D. Drake <jd@commandprompt.com>
2003-08-15 22:18     ` DeJuan Jackson <djackson@speedfc.com>
2003-08-16 12:21 ` Adam Witney <awitney@sghms.ac.uk>

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