Does PG support bulk operation in embedded C
To explain pls refer to this for DB2
Essentially in one single sql call, we can do
-- Add new rows
-- Update a set of rows where each row is identified by a bookmark
-- Delete a set of rows where each row is identified by a bookmark
-- Fetch a set of rows where each row is identified by a bookmark
This gives tremendous performance benefits as the network round trip is
avoided for each sql.
I am looking for an equivalent of this in PG and C language.
Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 05/19/2015 04:47 PM, Ravi Krishna wrote:
To explain pls refer to this for DB2
Essentially in one single sql call, we can do
-- Add new rows
-- Update a set of rows where each row is identified by a bookmark
-- Delete a set of rows where each row is identified by a bookmark
-- Fetch a set of rows where each row is identified by a bookmarkThis gives tremendous performance benefits as the network round trip is
avoided for each sql.I am looking for an equivalent of this in PG and C language.
For embedded C, I believe you are looking for:
http://www.postgresql.org/docs/9.4/static/ecpg.html
Thanks.
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Not sure whether I am understanding this. I checked embedded C and did not find any section which describes what I have asked, that is the ability to do multiple inserts, or updates or deletes in one sql call. For example, if my application does the following<br/>
<br/>
BEGIN TRANSACTION<br/>
INSERT INTO TABLE_A<br/>
UPDATE TABLE_B<br/>
INSERT INTO TABLE_C</div>
<div>COMMIT TRANSACTION</div>
<div><br/>
DB2 provides to combine the three sql operations into an array and make a call to DB2 which executes the array (that is all 3 sqls as one single call).<br/>
<br/>
I am looking for something similar in PG.<br/>
<br/>
thanks</div>
<div>
<div name="quote" style="margin: 10px 5px 5px 10px; padding: 10px 0px 10px 10px; border-left-color: rgb(195, 217, 229); border-left-width: 2px; border-left-style: solid; -ms-word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin: 0px 0px 10px;"><b>Sent:</b> Tuesday, May 19, 2015 at 8:13 PM<br/>
<b>From:</b> "Joshua D. Drake" <jd@commandprompt.com><br/>
<b>To:</b> "Ravi Krishna" <srkrishna@gmx.com>, pgsql-sql@postgresql.org<br/>
<b>Cc:</b> pgsql-general@postgresql.org<br/>
<b>Subject:</b> Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C</div>
<div name="quoted-content"><br/>
On 05/19/2015 04:47 PM, Ravi Krishna wrote:<br/>
><br/>
> To explain pls refer to this for DB2<br/>
><br/>
> <a href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html" target="_blank">http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html</a><br/>
><br/>
><br/>
> Essentially in one single sql call, we can do<br/>
> -- Add new rows<br/>
> -- Update a set of rows where each row is identified by a bookmark<br/>
> -- Delete a set of rows where each row is identified by a bookmark<br/>
> -- Fetch a set of rows where each row is identified by a bookmark<br/>
><br/>
> This gives tremendous performance benefits as the network round trip is<br/>
> avoided for each sql.<br/>
><br/>
> I am looking for an equivalent of this in PG and C language.<br/>
<br/>
For embedded C, I believe you are looking for:<br/>
<br/>
<a href="http://www.postgresql.org/docs/9.4/static/ecpg.html" target="_blank">http://www.postgresql.org/docs/9.4/static/ecpg.html</a><br/>
<br/>
<br/>
><br/>
> Thanks.<br/>
><br/>
><br/>
><br/>
<br/>
<br/>
--<br/>
Command Prompt, Inc. - <a href="http://www.commandprompt.com/" target="_blank">http://www.commandprompt.com/</a> 503-667-4564<br/>
PostgreSQL Centered full stack support, consulting and development.<br/>
Announcing "I'm offended" is basically telling the world you can't<br/>
control your own emotions, so everyone else should do it for you.<br/>
<br/>
<br/>
--<br/>
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br/>
To make changes to your subscription:<br/>
<a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a></div>
</div>
</div>
</div></div></body></html>
On 05/19/2015 05:27 PM, Ravi Krishna wrote:
Not sure whether I am understanding this. I checked embedded C and did
not find any section which describes what I have asked, that is the
ability to do multiple inserts, or updates or deletes in one sql call.
For example, if my application does the followingBEGIN TRANSACTION
INSERT INTO TABLE_A
UPDATE TABLE_B
INSERT INTO TABLE_C
COMMIT TRANSACTION
Well PostgreSQL certainly supports the above.
DB2 provides to combine the three sql operations into an array and make
a call to DB2 which executes the array (that is all 3 sqls as one single
call).
You can do this with inserts using multivalue.
INSERT INTO TABLE_A VALUES (), (), ();
I am not sure about UPDATE or DELETE, I know you can use WITH on DELETE
which gives you some flexibility.
I don't think you will get a one to one comparison but you should be
able to get close.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
On Wed, May 20, 2015 at 8:47 AM, Ravi Krishna <srkrishna@gmx.com> wrote:
Essentially in one single sql call, we can do
-- Add new rows
-- Update a set of rows where each row is identified by a bookmark
-- Delete a set of rows where each row is identified by a bookmark
-- Fetch a set of rows where each row is identified by a bookmarkThis gives tremendous performance benefits as the network round trip is
avoided for each sql.I am looking for an equivalent of this in PG and C language.
What you are looking at could be accomplished with a user-defined function:
http://www.postgresql.org/docs/devel/static/xfunc.html
Perhaps you are looking for something in C, now it would be less
complex to do it for example with pl/pgsql or another language, and
call it from a C client with a correct set of arguments satisfying
your needs.
--
Michael
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
If I'm reading what IBM has on the SQLBulkOperations function correctly
you have to know the size in advance - than does not sound like an
advantage.
Anyway, taking the function name 'bulk' into account - I think you want
find the best way to to insert a large number of row/records.
Review the 'copy' command to populate a database:
http://www.postgresql.org/docs/9.1/static/populate.html - read
carefully as it will increase performance in a dramatic way.
update and delete - well even in DB2 you are still doing the same thing
as Postgres - the only difference is it's handled without sending the
command. Whatever, the overhead - it's very small.
Johnf
On 05/19/2015 05:36 PM, Joshua D. Drake wrote:
On 05/19/2015 05:27 PM, Ravi Krishna wrote:
Not sure whether I am understanding this. I checked embedded C and did
not find any section which describes what I have asked, that is the
ability to do multiple inserts, or updates or deletes in one sql call.
For example, if my application does the followingBEGIN TRANSACTION
INSERT INTO TABLE_A
UPDATE TABLE_B
INSERT INTO TABLE_C
COMMIT TRANSACTIONWell PostgreSQL certainly supports the above.
DB2 provides to combine the three sql operations into an array and make
a call to DB2 which executes the array (that is all 3 sqls as one single
call).You can do this with inserts using multivalue.
INSERT INTO TABLE_A VALUES (), (), ();
I am not sure about UPDATE or DELETE, I know you can use WITH on
DELETE which gives you some flexibility.I don't think you will get a one to one comparison but you should be
able to get close.JD
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Anyway, taking the function name 'bulk' into account - I think you want
find the best way to to insert a large number of row/records.Review the 'copy' command to populate a database:
http://www.postgresql.org/docs/9.1/static/populate.html - read
carefully as it will increase performance in a dramatic way.
I am aware of the copy command, but we are talking about application inserting rows from a C++ task
with values coming in variables. Does COPY handle that? COPY looks more closer to sqlloader of Oracle.
DB2 Bulkcopy API is damn good. We have applications inserting tens of rows in one single call.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
I've set up in memory transfers using copy - you do that with the stdout
option. Performance is very good if you're on the same machine as the sql
server. Obviously network is the bottleneck otherwise.
On May 20, 2015 8:24 AM, "Ravi Krishna" <srkrishna@gmx.com> wrote:
Show quoted text
Anyway, taking the function name 'bulk' into account - I think you want
find the best way to to insert a large number of row/records.Review the 'copy' command to populate a database:
http://www.postgresql.org/docs/9.1/static/populate.html - read
carefully as it will increase performance in a dramatic way.I am aware of the copy command, but we are talking about application
inserting rows from a C++ task
with values coming in variables. Does COPY handle that? COPY looks more
closer to sqlloader of Oracle.DB2 Bulkcopy API is damn good. We have applications inserting tens of rows
in one single call.--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
You can do in-memory transfers using 'copy' - you do that with the stdout
option. Performance is very good if you're on the same machine as the sql
server. As far as I could see the transfer never touched the disk (once I
loaded the data into memory from a file) and I was getting blistering
insert speeds (this was back in 2009 but if I remember right, the copy
speed using this method was 50x the speed of a traditional insert command,
even when a bunch of inserts were wrapped in a transaction).
I dug up the code I used back then - it's in Ruby, using ActiveRecord, and
is from 2009 (hasn't been used since). But maybe it is useful for porting
to your environment: https://gist.github.com/science/15e97e414d5666c2f486
Obviously network is a likely bottleneck if you're not on the same box.
On May 20, 2015 8:24 AM, "Ravi Krishna" <srkrishna@gmx.com> wrote:
Show quoted text
Anyway, taking the function name 'bulk' into account - I think you want
find the best way to to insert a large number of row/records.Review the 'copy' command to populate a database:
http://www.postgresql.org/docs/9.1/static/populate.html - read
carefully as it will increase performance in a dramatic way.I am aware of the copy command, but we are talking about application
inserting rows from a C++ task
with values coming in variables. Does COPY handle that? COPY looks more
closer to sqlloader of Oracle.DB2 Bulkcopy API is damn good. We have applications inserting tens of rows
in one single call.--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql