Thread: many sql file and one transaction

many sql file and one transaction

From

salah jubeh

Date:

18 October 2011, 09:11:33

Hello,

I have many SQL script files to update schema, delete data, unit test ....etc. I want to run all the files in one transaction using shell script to ease the installation procedure. I can do that from the psql client by using the \i option

BEGIN;

\i / .../ module1.sql

\i / .../ module2.sql

\i / .../ module_etc.sql
COMMIT;

Is there a way to do that using psql command shell script.

I.E.

I want to run the following in one transaction,

psql -f module1.sql

psql -f module2.sql

psql -f module_etc.sql

Thanks in advance

Re: many sql file and one transaction

From

Alban Hertroys

Date:

18 October 2011, 09:21:52

On 18 October 2011 14:11, salah jubeh <s_jubeh@yahoo.com> wrote:
> Hello,
>
> I have many SQL script files to update schema, delete data, unit test
> ....etc.  I want to run all the files in one transaction using shell script
> to ease the installation procedure. I can do that from the psql client by
> using the \i option
>
> BEGIN;
> \i  / .../ module1.sql
> \i  / .../ module2.sql
> \i  / .../ module_etc.sql
> COMMIT;
>
> Is there a way to do that  using psql command  shell script.
>
> I.E.
> I want to run the following  in one transaction,
> psql  -f  module1.sql
> psql  -f  module2.sql
> psql  -f  module_etc.sql

You can't use the above, as each psql instance has their own session.

You could cat the files together and feed them to psql though.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: many sql file and one transaction

From

salah jubeh

Date:

18 October 2011, 09:40:14

Hello,

Thanks for the reply.

I considered cat as an option but I did not go for it, because of the number of sql files I have is large which makes the code not readable

The second thing, which is more important is because I have some advantages with using -f such as the line number which causing the error.

Regards

From: "andreas@a-kretschmer.de" <andreas@a-kretschmer.de>
To: salah jubeh <s_jubeh@yahoo.com>
Sent: Tuesday, October 18, 2011 2:23 PM
Subject: Re: [GENERAL] many sql file and one transaction

Zitat von salah jubeh <s_jubeh@yahoo.com>:

> Hello,
>
>
> I have many SQL script files to update schema, delete data, unit
> test ....etc. I want to run all the files in one transaction using
> shell script to ease the installation procedure. I can do that from
> the psql client by using the \i option
>
>
> BEGIN;
>
> \i / .../ module1.sql
>
> \i / .../ module2.sql
>
> \i / .../ module_etc.sql
> COMMIT;
>
>
> Is there a way to do that using psql command shell script.
>
>
> I.E.
>
> I want to run the following in one transaction,
>
> psql -f module1.sql
>
> psql -f module2.sql
>
> psql -f module_etc.sql

cat module1.sql module2.sql module_etc.sql | psql

Regards, Andreas

Re: many sql file and one transaction

From

Cédric Villemain

Date:

18 October 2011, 09:58:30

2011/10/18 salah jubeh <s_jubeh@yahoo.com>:
> Hello,
> Thanks for the reply.
> I considered  cat as an option but I did not go for it, because of the
> number of sql files I have is large  which makes the code not readable
> The second thing, which is more important is because I have some advantages
> with  using -f such as the line number which causing the error.

you can do :

cat module1.sql \
      module2.sql \
      module_etc.sql \
 | psql -f -



> Regards
>
>
>
>
>
> ________________________________
> From: "andreas@a-kretschmer.de" <andreas@a-kretschmer.de>
> To: salah jubeh <s_jubeh@yahoo.com>
> Sent: Tuesday, October 18, 2011 2:23 PM
> Subject: Re: [GENERAL] many sql file and one transaction
>
>
> Zitat von salah jubeh <s_jubeh@yahoo.com>:
>
>> Hello,
>>
>>
>> I have many SQL script files to update schema, delete data, unit
>> test ....etc.  I want to run all the files in one transaction using
>> shell script to ease the installation procedure. I can do that from
>> the psql client by using the \i option
>>
>>
>> BEGIN;
>>
>> \i  / .../ module1.sql
>>
>> \i  / .../ module2.sql
>>
>> \i  / .../ module_etc.sql
>> COMMIT;
>>
>>
>> Is there a way to do that  using psql command  shell script.
>>
>>
>> I.E.
>>
>> I want to run the following  in one transaction,
>>
>> psql  -f  module1.sql
>>
>> psql  -f  module2.sql
>>
>> psql  -f  module_etc.sql
>
>
> cat module1.sql module2.sql module_etc.sql | psql
>
>
> Regards, Andreas
>
>
>
>



--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Re: many sql file and one transaction

From

Merlin Moncure

Date:

18 October 2011, 10:23:15

On Tue, Oct 18, 2011 at 7:57 AM, Cédric Villemain
<cedric.villemain.debian@gmail.com> wrote:
> 2011/10/18 salah jubeh <s_jubeh@yahoo.com>:
>> Hello,
>> Thanks for the reply.
>> I considered  cat as an option but I did not go for it, because of the
>> number of sql files I have is large  which makes the code not readable
>> The second thing, which is more important is because I have some advantages
>> with  using -f such as the line number which causing the error.
>
> you can do :
>
> cat module1.sql \
>      module2.sql \
>      module_etc.sql \
>  | psql -f -
>
>
>
>> Regards
>>
>>
>>
>>
>>
>> ________________________________
>> From: "andreas@a-kretschmer.de" <andreas@a-kretschmer.de>
>> To: salah jubeh <s_jubeh@yahoo.com>
>> Sent: Tuesday, October 18, 2011 2:23 PM
>> Subject: Re: [GENERAL] many sql file and one transaction
>>
>>
>> Zitat von salah jubeh <s_jubeh@yahoo.com>:
>>
>>> Hello,
>>>
>>>
>>> I have many SQL script files to update schema, delete data, unit
>>> test ....etc.  I want to run all the files in one transaction using
>>> shell script to ease the installation procedure. I can do that from
>>> the psql client by using the \i option
>>>
>>>
>>> BEGIN;
>>>
>>> \i  / .../ module1.sql
>>>
>>> \i  / .../ module2.sql
>>>
>>> \i  / .../ module_etc.sql
>>> COMMIT;
>>>
>>>
>>> Is there a way to do that  using psql command  shell script.
>>>
>>>
>>> I.E.
>>>
>>> I want to run the following  in one transaction,
>>>
>>> psql  -f  module1.sql
>>>
>>> psql  -f  module2.sql
>>>
>>> psql  -f  module_etc.sql
>>
>>
>> cat module1.sql module2.sql module_etc.sql | psql

also don't forget the -1 option to psql, which will wrap all your
commands in a transaction, and:
\set ON_ERROR_STOP

which will abort if there's an error.  either put this in all your
scripts, your .psqlrc, or echo it into psql like this:

cat <(echo "\set ON_ERROR_STOP")
     module1.sql \
     module2.sql \
     module_etc.sql \
 | psql -1

you'll want to abort on error just to avoid the chance that a token in
your script will inadvertently close the transaction and cause a bunch
of garbage to be committed in the database -- this is likely to happen
say if you have a quotation error on function body.

merlin

Re: many sql file and one transaction

From

salah jubeh

Date:

18 October 2011, 10:56:57

Thanks guys as you have pointed , I think the best solution is to go for CAT and set the appropriate options for psql.

Regards

From: Merlin Moncure <mmoncure@gmail.com>
To: Cédric Villemain <cedric.villemain.debian@gmail.com>
Cc: salah jubeh <s_jubeh@yahoo.com>; "andreas@a-kretschmer.de" <andreas@a-kretschmer.de>; pgsql <pgsql-general@postgresql.org>
Sent: Tuesday, October 18, 2011 3:23 PM
Subject: Re: [GENERAL] many sql file and one transaction

On Tue, Oct 18, 2011 at 7:57 AM, Cédric Villemain
<cedric.villemain.debian@gmail.com> wrote:
> 2011/10/18 salah jubeh <s_jubeh@yahoo.com>:
>> Hello,
>> Thanks for the reply.
>> I considered cat as an option but I did not go for it, because of the
>> number of sql files I have is large which makes the code not readable
>> The second thing, which is more important is because I have some advantages
>> with using -f such as the line number which causing the error.
>
> you can do :
>
> cat module1.sql \
> module2.sql \
> module_etc.sql \
> | psql -f -
>
>
>
>> Regards
>>
>>
>>
>>
>>
>> ________________________________
>> From: "andreas@a-kretschmer.de" <andreas@a-kretschmer.de>
>> To: salah jubeh <s_jubeh@yahoo.com>
>> Sent: Tuesday, October 18, 2011 2:23 PM
>> Subject: Re: [GENERAL] many sql file and one transaction
>>
>>
>> Zitat von salah jubeh <s_jubeh@yahoo.com>:
>>
>>> Hello,
>>>
>>>
>>> I have many SQL script files to update schema, delete data, unit
>>> test ....etc. I want to run all the files in one transaction using
>>> shell script to ease the installation procedure. I can do that from
>>> the psql client by using the \i option
>>>
>>>
>>> BEGIN;
>>>
>>> \i / .../ module1.sql
>>>
>>> \i / .../ module2.sql
>>>
>>> \i / .../ module_etc.sql
>>> COMMIT;
>>>
>>>
>>> Is there a way to do that using psql command shell script.
>>>
>>>
>>> I.E.
>>>
>>> I want to run the following in one transaction,
>>>
>>> psql -f module1.sql
>>>
>>> psql -f module2.sql
>>>
>>> psql -f module_etc.sql
>>
>>
>> cat module1.sql module2.sql module_etc.sql | psql

also don't forget the -1 option to psql, which will wrap all your
commands in a transaction, and:
\set ON_ERROR_STOP

which will abort if there's an error. either put this in all your
scripts, your .psqlrc, or echo it into psql like this:

cat <(echo "\set ON_ERROR_STOP")
module1.sql \
module2.sql \
module_etc.sql \
| psql -1

you'll want to abort on error just to avoid the chance that a token in
your script will inadvertently close the transaction and cause a bunch
of garbage to be committed in the database -- this is likely to happen
say if you have a quotation error on function body.

merlin