Thread: Actions requiring commit
This may sound wierd for those not coming from an Oracle background, but in Oracle land, certain actions such as writing to a native operating system file, placing a job on a queue for asynchronous processing, and such like actions don't actually happen until commit time. What's the postgres story? If I write to a native operating system file, (and don't commit), does the output appear immediately (no counting internal small delay) ? What about issuing a "system" (shell) comnmand? Causing an signal, (er "Notify" in postrges land, I guess) , putting something on a queue via PGQ or similar tool? Do all or any of those require a commit to "make it so" or not?
On Sun, Feb 14, 2010 at 7:07 PM, Lee <Lee@jamtoday.com> wrote: > What's the postgres story? If I write to a native operating system file, > (and don't commit), does the output appear immediately (no counting internal > small delay) ? > > What about issuing a "system" (shell) comnmand? Causing an signal, (er > "Notify" in postrges land, I guess) , putting something on a queue via PGQ > or similar > tool? In Postgres anything that Postgres manages itself only happens at commit. So aside from table modifications and schema changes NOTIFY only takes place at commit. But if you have a pl language like plperl or plsh write to a file that's outside Postgres's sphere of influence and it will happen right away. I'm not familiar with PGQ, I'm not sure which tack it takes. Its documentation should have an answer though. -- greg
> On Sun, Feb 14, 2010 at 7:07 PM, Lee <Lee@jamtoday.com> wrote: >> What's the postgres story? If I write to a native operating system file, >> (and don't commit), does the output appear immediately (no counting >> internal >> small delay) ? >> >> What about issuing a "system" (shell) comnmand? Causing an signal, (er >> "Notify" in postrges land, I guess) , putting something on a queue via >> PGQ >> or similar >> tool? > > In Postgres anything that Postgres manages itself only happens at > commit. So aside from table modifications and schema changes NOTIFY > only takes place at commit. But if you have a pl language like plperl > or plsh write to a file that's outside Postgres's sphere of influence > and it will happen right away. > > I'm not familiar with PGQ, I'm not sure which tack it takes. Its > documentation should have an answer though. > Thanks for the reply. Does that go for pl/pgsql as well? If I call a pl/pgsql procedure from the psql command line (or from another pl/pgsql procedure?) will commits in the called procedure cause commit in the calling procedure as well, or will the caller and callee have in effect different "threads" or sessions so that their respective commits are separated?
On Mon, Feb 15, 2010 at 1:50 AM, Lee Horowitz <leeh@panix.com> wrote: > > Does that go for pl/pgsql as well? > > If I call a pl/pgsql procedure from the psql command line (or from > another pl/pgsql procedure?) will commits in the called procedure cause > commit in the calling procedure as well, or will the caller and callee > have in effect different "threads" or sessions so that their respective > commits are separated? > pl/pgsql functions can't commit. Any database modifications are committed at the end of the transaction. We currently don't have "stored procedures" which live outside of transactions and can start and commit transactions on their own, only functions which live entirely within one transaction. -- greg
> On Mon, Feb 15, 2010 at 1:50 AM, Lee Horowitz <leeh@panix.com> wrote: >> >> Does that go for pl/pgsql as well? >> >> If I call a pl/pgsql procedure from the psql command line (or from >> another pl/pgsql procedure?) will commits in the called procedure cause >> commit in the calling procedure as well, or will the caller and callee >> have in effect different "threads" or sessions so that their respective >> commits are separated? >> > > pl/pgsql functions can't commit. Any database modifications are > committed at the end of the transaction. We currently don't have > "stored procedures" which live outside of transactions and can start > and commit transactions on their own, only functions which live > entirely within one transaction. > Uh oh! Lets see if I've got this right. A pl/perl or pl/python routine CAN commit, but a pl/pgsql procedure can not? Suppose I have a psql script that inserts a row into table A and then calls a pl/pgsql routine that inserts into table B. Upon return from the pl/pgsql routine, I can commit (thereby keeping the data in both tables), or I can roll back, (thus losing the data in both tables). If, instead of inserting into table B I were to write to a "flat" file, same story. The flat file would either be written if we issue a commit from the calling psql routine or be lost if we roll back. Ah, but if the called routine were pl/perl or pl/python then things would be different? Then in that case, we insert into table A in the psql routine, call the pl/perl or pl/python routine that inserts into table B or writes to a flat file, and now, still in the pl/perl or pl/sql routine we can commit (hence keeping table B and or the data written to the flat file) and then return back to the psql routine where we can either commit table A or roll it back?
On Mon, Feb 15, 2010 at 4:31 AM, Lee Horowitz <leeh@panix.com> wrote: > Suppose I have a psql script that inserts a row into table A and then calls > a pl/pgsql routine that inserts into table B. Upon return from the pl/pgsql > routine, I can commit (thereby keeping the data in both tables), or I can > roll back, (thus losing the data in both tables). Correct > > If, instead of inserting into table B I were to write to a "flat" file, > same story. The flat file would either be written if we issue a commit > from the calling psql routine or be lost if we roll back. > The flat file is outside Postgres's control. The modification to it will happen as soon as the perl or python or plpgsql code is run and can't be rolled back. > Ah, but if the called routine were pl/perl or pl/python then things would > be different? Then in that case, we insert into table A in the psql > routine, call the pl/perl or pl/python routine that inserts into table B > or writes to a flat file, and now, still in the pl/perl or pl/sql routine > we can commit (hence keeping table B and or the data written to the flat > file) and then return back to the psql routine where we can either commit > table A or roll it back? You can't commit inside a function regardless of the language. Functions live entirely inside a transaction. > -- greg
On 2010-02-15, Lee Horowitz <leeh@panix.com> wrote: >> On Sun, Feb 14, 2010 at 7:07 PM, Lee <Lee@jamtoday.com> wrote: >>> What's the postgres story? If I write to a native operating system file, >>> (and don't commit), does the output appear immediately (no counting >>> internal >>> small delay) ? >>> >>> What about issuing a "system" (shell) comnmand? Causing an signal, (er >>> "Notify" in postrges land, I guess) , putting something on a queue via >>> PGQ >>> or similar >>> tool? >> >> In Postgres anything that Postgres manages itself only happens at >> commit. So aside from table modifications and schema changes NOTIFY >> only takes place at commit. But if you have a pl language like plperl >> or plsh write to a file that's outside Postgres's sphere of influence >> and it will happen right away. >> >> I'm not familiar with PGQ, I'm not sure which tack it takes. Its >> documentation should have an answer though. >> > Thanks for the reply. > > Does that go for pl/pgsql as well? > > If I call a pl/pgsql procedure from the psql command line (or from > another pl/pgsql procedure?) will commits in the called procedure cause > commit in the calling procedure as well, or will the caller and callee > have in effect different "threads" or sessions so that their respective > commits are separated? procedures commit at the end of the original of the query that launched them (assuming autocommit, else never).
> > You can't commit inside a function regardless of the language. > Functions live entirely inside a transaction. > OK, now I imagine you're thinking "What's wrong with that dufus, how many times do I have to tell him you can't commit from inside a function before he gets it? " My problem is that I'be been told that I can use dblink() to make a second connection to the same database, and then, on that second connection, do an insert to some table and issue a commit which will make the data in that commit visible to other sessions, but NOT commit any pending data on the first connection. An underground assumption I must have made, was that opening the second connection, doing the insert to table B and subsequent commit, all that was to be done inside a function called from psql or from some pl/xxx routine which itself is called from psql. If I've "got" what you're trying to tel me, that can't be the way to do it. We've got to be in the original psql script. We do an insert into table A, we open a second connection (right there in the script? cant do it in a function?) then insert into table B using the second connection, then commit the second connection (all in the main psql routine) and then we can commit or roll back table A as circumstances dictate. Either way, data in table B persists and is visible to other sessions. Am I getting warmer?
On Mon, Feb 15, 2010 at 5:14 PM, Lee Horowitz <leeh@panix.com> wrote: > My problem is that I'be been told that I can use dblink() to make a second > connection to the same database, and then, on that second connection, do > an > insert to some table and issue a commit which will make the data in that > commit visible to other sessions, but NOT commit any pending data on the > first connection. > That's correct. The database server process handling the second connection won't know anything about the outer transaction. All it knows is that it received this connection with this insert and then was asked to commit it. The outer transaction can abort or commit and it won't affect the status of the insert that the dblink connection made. You could see some weird effects if you try to look at that newly inserted row in the outer transaction, but that's just because it was inserted after your transaction started. -- greg
> On Mon, Feb 15, 2010 at 5:14 PM, Lee Horowitz <leeh@panix.com> wrote: >> My problem is that I'be been told that I can use dblink() to make a >> second >> connection to the same database, and then, on that second connection, do >> an >> insert to some table and issue a commit which will make the data in that >> commit visible to other sessions, but NOT commit any pending data on the >> first connection. >> > > That's correct. The database server process handling the second > connection won't know anything about the outer transaction. All it > knows is that it received this connection with this insert and then > was asked to commit it. The outer transaction can abort or commit and > it won't affect the status of the insert that the dblink connection > made. > > You could see some weird effects if you try to look at that newly > inserted row in the outer transaction, but that's just because it was > inserted after your transaction started. > OK, so far so good. Now, just to be clear, are you saying in addition that the dblink(), insert,commit stuff has to be all "in line" in the original psql script and can't be in a pl/xxxx language?
"Lee Horowitz" <leeh@panix.com> writes:
> OK, so far so good. Now, just to be clear, are you saying in addition
> that the dblink(), insert,commit stuff has to be all "in line" in the
> original psql script and can't be in a pl/xxxx language?
No. The previous responses were about what you could do *directly* in a
PL function. They weren't considering the possibility of using dblink
to issue commands to another transaction.
regards, tom lane