Re: Actions requiring commit

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).