Thread: open transaction?

open transaction?

From
"blackwater dev"
Date:
I just logged into postgres from the command line and did:

begin:

select blah;
select blah;

\q

Without thinking I closed by connection before committing or rolling back my transaction.  Did postgres handle this for me?  How do I see if the transaction is still open?

Thanks!

Re: open transaction?

From
Raymond O'Donnell
Date:
On 29/12/2008 11:48, blackwater dev wrote:

> Without thinking I closed by connection before committing or rolling
> back my transaction.  Did postgres handle this for me?  How do I see if
> the transaction is still open?

I'm reasonably sure that the transaction will get rolled back if the
connection dies.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: open transaction?

From
Raymond O'Donnell
Date:
On 29/12/2008 11:53, Raymond O'Donnell wrote:
> On 29/12/2008 11:48, blackwater dev wrote:
>
>> Without thinking I closed by connection before committing or rolling
>> back my transaction.  Did postgres handle this for me?  How do I see if
>> the transaction is still open?
>
> I'm reasonably sure that the transaction will get rolled back if the
> connection dies.

Just tried it, and that seems to be the case:

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test".
test=# create table t(f1 integer);
CREATE TABLE
test=# begin;
BEGIN
test=# insert into t values(1);
INSERT 0 1
test=# insert into t values(2);
INSERT 0 1
test=# \q
rod@teladesign:~$ psql -U postgres test
Password for user postgres:
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select * from t;
 f1
----
(0 rows)



Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: open transaction?

From
"Pavel Stehule"
Date:
Hello

maybe you have autocommit on

try

[pavel@localhost ~]$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '

regards
Pavel Stehule

2008/12/29 Raymond O'Donnell <rod@iol.ie>:
> On 29/12/2008 11:53, Raymond O'Donnell wrote:
>> On 29/12/2008 11:48, blackwater dev wrote:
>>
>>> Without thinking I closed by connection before committing or rolling
>>> back my transaction.  Did postgres handle this for me?  How do I see if
>>> the transaction is still open?
>>
>> I'm reasonably sure that the transaction will get rolled back if the
>> connection dies.
>
> Just tried it, and that seems to be the case:
>
> postgres=# create database test;
> CREATE DATABASE
> postgres=# \c test
> You are now connected to database "test".
> test=# create table t(f1 integer);
> CREATE TABLE
> test=# begin;
> BEGIN
> test=# insert into t values(1);
> INSERT 0 1
> test=# insert into t values(2);
> INSERT 0 1
> test=# \q
> rod@teladesign:~$ psql -U postgres test
> Password for user postgres:
> Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>       \h for help with SQL commands
>       \? for help with psql commands
>       \g or terminate with semicolon to execute query
>       \q to quit
>
> test=# select * from t;
>  f1
> ----
> (0 rows)
>
>
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: open transaction?

From
Terry Lee Tucker
Date:
On Monday 29 December 2008 06:48, blackwater dev wrote:
> I just logged into postgres from the command line and did:
>
> begin:
>
> select blah;
> select blah;
>
> \q
>
> Without thinking I closed by connection before committing or rolling back
> my transaction.  Did postgres handle this for me?  How do I see if the
> transaction is still open?
>
> Thanks!

If you don't commit, it is rolled back when you exit.

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of OHL
2251 Jesse Jewell Pkwy
Gainesville, GA 30501
tel: (336) 372-6821 cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

Re: open transaction?

From
"Bayless Kirtley"
Date:
----- Original Message -----
From: "Terry Lee Tucker" <terry@chosen-ones.org>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 29, 2008 5:57 AM
Subject: Re: [GENERAL] open transaction?


> On Monday 29 December 2008 06:48, blackwater dev wrote:
>> I just logged into postgres from the command line and did:
>>
>> begin:
>>
>> select blah;
>> select blah;
>>
>> \q
>>
>> Without thinking I closed by connection before committing or rolling back
>> my transaction.  Did postgres handle this for me?  How do I see if the
>> transaction is still open?
>>
>> Thanks!
>
> If you don't commit, it is rolled back when you exit.
>
> --
> Terry Lee Tucker
> Turbo's IT Manager
> Turbo, division of OHL
> 2251 Jesse Jewell Pkwy
> Gainesville, GA 30501
> tel: (336) 372-6821 cell: (336) 404-6987
> terry@turbocorp.com
> www.turbocorp.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

But if all you did was SELECT, as you indicated, then no transaction
should have been started and it makes no difference.

Bayless


How can the error log be searched?

From
"Rob Richardson"
Date:
Greetings!

A customer reported an error generated by our database.  However, many
of our functions have errors in RAISE EXCEPTION statements, in which the
% character was omitted.  The error message the customer saw was
complaining that there were too many arguments to a RAISE statement.  We
were able to find the function that was throwing the error by using
PGAdmin, opening the Server Status window, going to the Log File tab,
and finding approximately when the error occurred.

But it would have been easier to find the problem if we could have
searched the logs.  There's no search function in the log file window.
Also, there's no apparent way to export the file.  Also, I can't even
use copy and paste.  So, how can I export the error messages into a
searchable text file?

Thanks very much!

RobR

Re: How can the error log be searched?

From
Adrian Klaver
Date:
On Monday 29 December 2008 11:28:13 am Rob Richardson wrote:
> Greetings!
>
> A customer reported an error generated by our database.  However, many
> of our functions have errors in RAISE EXCEPTION statements, in which the
> % character was omitted.  The error message the customer saw was
> complaining that there were too many arguments to a RAISE statement.  We
> were able to find the function that was throwing the error by using
> PGAdmin, opening the Server Status window, going to the Log File tab,
> and finding approximately when the error occurred.
>
> But it would have been easier to find the problem if we could have
> searched the logs.  There's no search function in the log file window.
> Also, there's no apparent way to export the file.  Also, I can't even
> use copy and paste.  So, how can I export the error messages into a
> searchable text file?
>
> Thanks very much!
>
> RobR

I don't use PgAdmin so I can't help you with that part. If you want to search
the file use ~/PGDATA/postgresql.conf to find the log directory and read the
file directly with the text editor of you choice.

--
Adrian Klaver
aklaver@comcast.net