Thread: Basic question on PGSQL and Transactions
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.
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
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
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!