Thread: Basic question on PGSQL and Transactions

Basic question on PGSQL and Transactions

From
Henri De Feraudy
Date:
Hello, 
  I've been writing an application that allows me to edit simple maps. It involves Postgis but here I think I have run into 
a basic Postgres consideration.
I did a bit of editing of a map which involved creating a polygon with a selection, inserting into a database and reloading
the "layer" where this geometric information lies.
There I was editing quite happily for about an hour and a half. I left my application and came back to it in the evening:
all my work was lost.
Could this be due to the fact I had not committed my insertions to the database?
I mean, if you are inserting into a table from PLPGSQL do you have to do an explicit commit at some stage?
I'm really quite a newbie as you can see.

Re: Basic question on PGSQL and Transactions

From
Andy Colson
Date:
On 9/29/2010 11:31 AM, Henri De Feraudy wrote:
> Hello,
> I've been writing an application that allows me to edit simple maps. It
> involves Postgis but here I think I have run into
> a basic Postgres consideration.
> I did a bit of editing of a map which involved creating a polygon with a
> selection, inserting into a database and reloading
> the "layer" where this geometric information lies.
> There I was editing quite happily for about an hour and a half. I left
> my application and came back to it in the evening:
> all my work was lost.
> Could this be due to the fact I had not committed my insertions to the
> database?
> I mean, if you are inserting into a table from PLPGSQL do you have to do
> an explicit commit at some stage?
> I'm really quite a newbie as you can see.
>

Yes, you really must commit.

Now, sometimes, some languages/tools will "help" you and autocommit.  It
depends on what language/tools/etc you are using.

This is a wookie!  If that does not make sense, you must commit!
-south park, slightly paraphrased

-Andy

Re: Basic question on PGSQL and Transactions

From
Merlin Moncure
Date:
On Wed, Sep 29, 2010 at 12:38 PM, Andy Colson <andy@squeakycode.net> wrote:
> On 9/29/2010 11:31 AM, Henri De Feraudy wrote:
>>
>> Hello,
>> I've been writing an application that allows me to edit simple maps. It
>> involves Postgis but here I think I have run into
>> a basic Postgres consideration.
>> I did a bit of editing of a map which involved creating a polygon with a
>> selection, inserting into a database and reloading
>> the "layer" where this geometric information lies.
>> There I was editing quite happily for about an hour and a half. I left
>> my application and came back to it in the evening:
>> all my work was lost.
>> Could this be due to the fact I had not committed my insertions to the
>> database?
>> I mean, if you are inserting into a table from PLPGSQL do you have to do
>> an explicit commit at some stage?
>> I'm really quite a newbie as you can see.
>>
>
> Yes, you really must commit.
>
> Now, sometimes, some languages/tools will "help" you and autocommit.  It
> depends on what language/tools/etc you are using.

yes...one more note about this:  It is absolutely never (IMNSHO) a
good idea to intentionally hold transactions open while waiting on
user input except for testing purposes.

merlin

Re: Basic question on PGSQL and Transactions

From
Alban Hertroys
Date:
On 29 Sep 2010, at 18:31, Henri De Feraudy wrote:

> I mean, if you are inserting into a table from PLPGSQL do you have to do an
> explicit commit at some stage?

I suppose you meant psql here, as otherwise the above statement wouldn't make sense ;)

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4ca371b7678305060513005!