Repeatable crash in pg_dump (with -d2 info)

David Schnur <dnschnur@gmail.com> writes:

I'm seeing pg_dump [8.3.15 Windows] crash reproducibly against a particular
database.

Can you provide a stack trace from the crash? There's some info about
that here:
http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows
though I think it's oriented towards backend crashes more than client-side
crashes.

Alternatively, do you have the SQL definition for the function you think
is causing the crash? There's not much that anybody else is going to be
able to do about this unless you can provide a reproducible test case.

regards, tom lane

Sure; the function is created programmatically as part of schema creation,
by the same user who owns (almost) everything else in the database. The
definition looks like this:

CREATE OR REPLACE FUNCTION datastore_unpack(
data_times TIMESTAMP WITH TIME ZONE[],
data_values DOUBLE PRECISION[],
OUT data_time TIMESTAMP WITH TIME ZONE,
OUT data_value DOUBLE PRECISION
) RETURNS SETOF RECORD AS $$
SELECT $1[rowx] AS data_time, $2[rowx] AS data_value
FROM generate_series(1, array_upper($1, 1)) AS rowx;
$$ LANGUAGE SQL STABLE;

It takes two parallel arrays and unpacks them into a set of records.

David Schnur <dnschnur@gmail.com> writes:

Sure; the function is created programmatically as part of schema creation,
by the same user who owns (almost) everything else in the database. The
definition looks like this:

CREATE OR REPLACE FUNCTION datastore_unpack(
data_times TIMESTAMP WITH TIME ZONE[],
data_values DOUBLE PRECISION[],
OUT data_time TIMESTAMP WITH TIME ZONE,
OUT data_value DOUBLE PRECISION
) RETURNS SETOF RECORD AS $$
SELECT $1[rowx] AS data_time, $2[rowx] AS data_value
FROM generate_series(1, array_upper($1, 1)) AS rowx;
$$ LANGUAGE SQL STABLE;

No crash observed here. So either we fixed it since 8.3.15 (doesn't
seem likely, as I see nothing potentially related in the commit logs),
or the trigger for the problem is not (just) this function. It seems
entirely plausible that the crash is associated with the
next-to-be-dumped object rather than this one, anyway.

How about that stack trace?

Alternatively, if you have an idea of the next object it might be
working on (most likely, the next function in alphabetical order)
you might see if loading just that function into an empty database
is enough to create a crash.

It's also entirely plausible that the crash requires multiple objects
to trigger it (for instance it might have something to do with
inter-object dependencies). So if you're not eager to burrow into
stack traces, you might see if you can strip your standard schema
down to something you could post that still triggers the crash
starting from an empty database.

regards, tom lane

I probably can't get a stack trace, but I was able to reproduce it with
just that function. Without the function, pg_dump works fine. I can DROP
the function, pg_dump works, then add it back again and pg_dump crashes.
Here are my steps:

initdb -A md5 --no-locale -E UTF8 -U testuser -D
"C:\Users\David\Desktop\testdb" -W

pg_ctl -U testuser -D "C:\Users\David\Desktop\testdb" start

CREATE DATABASE testdb OWNER testuser;

CREATE OR REPLACE FUNCTION datastore_unpack(
data_times TIMESTAMP WITH TIME ZONE[],
data_values DOUBLE PRECISION[],
OUT data_time TIMESTAMP WITH TIME ZONE,
OUT data_value DOUBLE PRECISION
) RETURNS SETOF RECORD AS $$
SELECT $1[rowx] AS data_time, $2[rowx] AS data_value
FROM generate_series(1, array_upper($1, 1)) AS rowx;
$$ LANGUAGE SQL STABLE;

pg_dump -v -F c -x -O -f "C:\Users\David\Desktop\dumptest" -U testuser
testdb

Here's the output from pg_dump:

pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined operator families
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition

And here's the output from the postmaster:

LOG: statement: SET DATESTYLE = ISO
LOG: statement: SET extra_float_digits TO 2
LOG: statement: SET synchronize_seqscans TO off
LOG: statement: BEGIN
LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
LOG: statement: SET search_path = pg_catalog
LOG: statement: SELECT tableoid, oid, nspname, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM
pg_namespace
LOG: statement: SELECT tableoid, oid, proname, prolang, pronargs,
proargtypes, prorettype, proacl, pronamespace,(SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT
proisagg AND pronamespace != (select oid from pg_namespace where nspname =
'pg_catalog')
LOG: statement: SELECT tableoid, oid, typname, typnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = typowner) as rolname,
typinput::oid as typinput, typoutput::oid as typoutput, typelem, typrelid,
CASE WHEN typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class
WHERE oid = typrelid) END as typrelkind, typtype, typisdefined, typname[0]
= '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
pg_type.typelem) = oid AS isarray FROM pg_type
LOG: statement: SELECT tableoid, oid, lanname, lanpltrusted,
lanplcallfoid, lanvalidator, lanacl, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = lanowner) as lanowner FROM pg_language
WHERE lanispl ORDER BY oid
LOG: statement: SELECT tableoid, oid, proname as aggname, pronamespace as
aggnamespace, pronargs, proargtypes, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = proowner) as rolname, proacl as aggacl FROM
pg_proc WHERE proisagg AND pronamespace != (select oid from pg_namespace
where nspname = 'pg_catalog')
LOG: statement: SELECT tableoid, oid, oprname, oprnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = oprowner) as rolname,
oprcode::oid as oprcode FROM pg_operator
LOG: statement: SELECT tableoid, oid, opcname, opcnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = opcowner) as rolname FROM
pg_opclass
LOG: statement: SELECT tableoid, oid, prsname, prsnamespace,
prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid,
prslextype::oid FROM pg_ts_parser
LOG: statement: SELECT tableoid, oid, tmplname, tmplnamespace,
tmplinit::oid, tmpllexize::oid FROM pg_ts_template
LOG: statement: SELECT tableoid, oid, dictname, dictnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = dictowner) as rolname,
dicttemplate, dictinitoption FROM pg_ts_dict
LOG: statement: SELECT tableoid, oid, cfgname, cfgnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = cfgowner) as rolname,
cfgparser FROM pg_ts_config
LOG: statement: SELECT tableoid, oid, opfname, opfnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) as rolname FROM
pg_opfamily
LOG: statement: SELECT tableoid, oid, conname, connamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = conowner) as rolname FROM
pg_conversion
LOG: statement: SELECT c.tableoid, c.oid, relname, relacl, relkind,
relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
relowner) as rolname, relchecks, reltriggers, relhasindex, relhasrules,
relhasoids, d.refobjid as owning_tab, d.refobjsubid as owning_col, (SELECT
spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS
reltablespace, array_to_string(c.reloptions, ', ') as reloptions from
pg_class c left join pg_depend d on (c.relkind = 'S' and d.classid =
c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid =
c.tableoid and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order
by c.oid
LOG: statement: SELECT inhrelid, inhparent from pg_inherits
LOG: statement: SELECT tableoid, oid, rulename, ev_class as ruletable,
ev_type, is_instead, ev_enabled FROM pg_rewrite ORDER BY oid
LOG: statement: SELECT tableoid, oid, castsource, casttarget, castfunc,
castcontext FROM pg_cast ORDER BY 3,4
LOG: statement: SELECT loid FROM pg_largeobject LIMIT 1
LOG: statement: SELECT classid, objid, refclassid, refobjid, deptype FROM
pg_depend WHERE deptype != 'p' ORDER BY 1,2
LOG: statement: SELECT tableoid, oid, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = datdba) as dba,
pg_encoding_to_char(encoding) as encoding, (SELECT spcname FROM
pg_tablespace t WHERE t.oid = dattablespace) as tablespace,
shobj_description(oid, 'pg_database') as description FROM pg_database WHERE
datname = 'testdb'
LOG: statement: SELECT description, classoid, objoid, objsubid FROM
pg_catalog.pg_description ORDER BY classoid, objoid, objsubid
LOG: statement: SET search_path = public, pg_catalog
LOG: statement: SELECT proretset, prosrc, probin, proallargtypes,
proargmodes, proargnames, provolatile, proisstrict, prosecdef, proconfig,
procost, prorows, (SELECT lanname FROM pg_catalog.pg_language WHERE oid =
prolang) as lanname FROM pg_catalog.pg_proc WHERE oid =
'16386'::pg_catalog.oid
LOG: statement: SELECT pg_catalog.format_type('1185'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1022'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1184'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('701'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1185'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1022'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('2249'::pg_catalog.oid, NULL)
LOG: could not receive data from client: No connection could be made
because the target machine actively refused it.

LOG: unexpected EOF on client connection

David Schnur <dnschnur@gmail.com> writes:

I probably can't get a stack trace, but I was able to reproduce it with
just that function. Without the function, pg_dump works fine. I can DROP
the function, pg_dump works, then add it back again and pg_dump crashes.

Hmph. I still can't reproduce this here, which seems to mean one of
three things:
1. We fixed this since 8.3.15 (still doesn't seem likely).
2. It's specific to Windows.
3. It's specific to the pg_dump build you are using.

I wonder whether anyone else can get this to happen on the strength of
David's description?

Here are my steps:

initdb -A md5 --no-locale -E UTF8 -U testuser -D
"C:\Users\David\Desktop\testdb" -W

pg_ctl -U testuser -D "C:\Users\David\Desktop\testdb" start

CREATE DATABASE testdb OWNER testuser;

CREATE OR REPLACE FUNCTION datastore_unpack(
data_times TIMESTAMP WITH TIME ZONE[],
data_values DOUBLE PRECISION[],
OUT data_time TIMESTAMP WITH TIME ZONE,
OUT data_value DOUBLE PRECISION
) RETURNS SETOF RECORD AS $$
SELECT $1[rowx] AS data_time, $2[rowx] AS data_value
FROM generate_series(1, array_upper($1, 1)) AS rowx;
$$ LANGUAGE SQL STABLE;

pg_dump -v -F c -x -O -f "C:\Users\David\Desktop\dumptest" -U testuser
testdb

Here's the output from pg_dump:

pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined operator families
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: reading table inheritance information
pg_dump: reading rewrite rules
pg_dump: reading type casts
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = off
pg_dump: saving database definition

And here's the output from the postmaster:

LOG: statement: SET DATESTYLE = ISO
LOG: statement: SET extra_float_digits TO 2
LOG: statement: SET synchronize_seqscans TO off
LOG: statement: BEGIN
LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
LOG: statement: SET search_path = pg_catalog
LOG: statement: SELECT tableoid, oid, nspname, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = nspowner) as rolname, nspacl FROM
pg_namespace
LOG: statement: SELECT tableoid, oid, proname, prolang, pronargs,
proargtypes, prorettype, proacl, pronamespace,(SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT
proisagg AND pronamespace != (select oid from pg_namespace where nspname =
'pg_catalog')
LOG: statement: SELECT tableoid, oid, typname, typnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = typowner) as rolname,
typinput::oid as typinput, typoutput::oid as typoutput, typelem, typrelid,
CASE WHEN typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class
WHERE oid = typrelid) END as typrelkind, typtype, typisdefined, typname[0]
= '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid =
pg_type.typelem) = oid AS isarray FROM pg_type
LOG: statement: SELECT tableoid, oid, lanname, lanpltrusted,
lanplcallfoid, lanvalidator, lanacl, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = lanowner) as lanowner FROM pg_language
WHERE lanispl ORDER BY oid
LOG: statement: SELECT tableoid, oid, proname as aggname, pronamespace as
aggnamespace, pronargs, proargtypes, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = proowner) as rolname, proacl as aggacl FROM
pg_proc WHERE proisagg AND pronamespace != (select oid from pg_namespace
where nspname = 'pg_catalog')
LOG: statement: SELECT tableoid, oid, oprname, oprnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = oprowner) as rolname,
oprcode::oid as oprcode FROM pg_operator
LOG: statement: SELECT tableoid, oid, opcname, opcnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = opcowner) as rolname FROM
pg_opclass
LOG: statement: SELECT tableoid, oid, prsname, prsnamespace,
prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid,
prslextype::oid FROM pg_ts_parser
LOG: statement: SELECT tableoid, oid, tmplname, tmplnamespace,
tmplinit::oid, tmpllexize::oid FROM pg_ts_template
LOG: statement: SELECT tableoid, oid, dictname, dictnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = dictowner) as rolname,
dicttemplate, dictinitoption FROM pg_ts_dict
LOG: statement: SELECT tableoid, oid, cfgname, cfgnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = cfgowner) as rolname,
cfgparser FROM pg_ts_config
LOG: statement: SELECT tableoid, oid, opfname, opfnamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) as rolname FROM
pg_opfamily
LOG: statement: SELECT tableoid, oid, conname, connamespace, (SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = conowner) as rolname FROM
pg_conversion
LOG: statement: SELECT c.tableoid, c.oid, relname, relacl, relkind,
relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
relowner) as rolname, relchecks, reltriggers, relhasindex, relhasrules,
relhasoids, d.refobjid as owning_tab, d.refobjsubid as owning_col, (SELECT
spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS
reltablespace, array_to_string(c.reloptions, ', ') as reloptions from
pg_class c left join pg_depend d on (c.relkind = 'S' and d.classid =
c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid =
c.tableoid and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order
by c.oid
LOG: statement: SELECT inhrelid, inhparent from pg_inherits
LOG: statement: SELECT tableoid, oid, rulename, ev_class as ruletable,
ev_type, is_instead, ev_enabled FROM pg_rewrite ORDER BY oid
LOG: statement: SELECT tableoid, oid, castsource, casttarget, castfunc,
castcontext FROM pg_cast ORDER BY 3,4
LOG: statement: SELECT loid FROM pg_largeobject LIMIT 1
LOG: statement: SELECT classid, objid, refclassid, refobjid, deptype FROM
pg_depend WHERE deptype != 'p' ORDER BY 1,2
LOG: statement: SELECT tableoid, oid, (SELECT rolname FROM
pg_catalog.pg_roles WHERE oid = datdba) as dba,
pg_encoding_to_char(encoding) as encoding, (SELECT spcname FROM
pg_tablespace t WHERE t.oid = dattablespace) as tablespace,
shobj_description(oid, 'pg_database') as description FROM pg_database WHERE
datname = 'testdb'
LOG: statement: SELECT description, classoid, objoid, objsubid FROM
pg_catalog.pg_description ORDER BY classoid, objoid, objsubid
LOG: statement: SET search_path = public, pg_catalog
LOG: statement: SELECT proretset, prosrc, probin, proallargtypes,
proargmodes, proargnames, provolatile, proisstrict, prosecdef, proconfig,
procost, prorows, (SELECT lanname FROM pg_catalog.pg_language WHERE oid =
prolang) as lanname FROM pg_catalog.pg_proc WHERE oid =
'16386'::pg_catalog.oid
LOG: statement: SELECT pg_catalog.format_type('1185'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1022'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1184'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('701'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1185'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('1022'::pg_catalog.oid, NULL)
LOG: statement: SELECT pg_catalog.format_type('2249'::pg_catalog.oid, NULL)
LOG: could not receive data from client: No connection could be made
because the target machine actively refused it.

LOG: unexpected EOF on client connection

regards, tom lane

On 11/29/2011 06:20 AM, David Schnur wrote:

I probably can't get a stack trace

Getting a usable stack trace on Windows isn't actually too hard. See:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows

That's focused on debugging the `postgres.exe' backend, but all the
setup applies just as well to pg_dump. You then start pg_dump via
windbg.exe or Visual Studio Express and, when it crashes, follow the
instructions given in the wiki to produce the backtrace.

--
Craig Ringer

On Mon, Nov 28, 2011 at 9:48 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:

Getting a usable stack trace on Windows isn't actually too hard.

The problem isn't getting the trace - I know how to do that - it's that I
don't have the pdbs for this build, and so the trace wouldn't be very
useful. I may be able to get or recreate them, but can't right now. I am
going to try this on Linux a little later today, though, to see if I can
narrow it down in that regard.

Hi Everybody,

This is an architectural question.
I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu)

I want to make sure that I have the correct understanding of the Postgres architecture and would like to enquire if there are any plans to change it.

Comparing Oracle and Postgres from the perspective of error handling on the transaction level I observed the following:

Oracle:
Begin transaction
Insert - no error
Implicit savepoint
Insert - error raised
Implicit rollback to the savepoint, no transaction loss, error raised on the insert statement that errored out.
End transaction, implicit commit, with the single error free insert.

Postgres:
Begin transaction
Insert - no error
Insert - error raised
Transaction loss = no implicit rollback to the single error free insert.

Is this a correct interpretation of the Postgres transaction error handling?
If so, are there any changes being considered, or perhaps already implemented?

Sincerely,
Kasia

Very naïve question here: Why would you want to save the data from the first insert?

I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction?

RobR

Kasia Tuszynska <ktuszynska@esri.com> wrote:

Oracle:
Begin transaction
Insert - no error
Implicit savepoint
Insert - error raised
Implicit rollback to the savepoint, no transaction loss, error
raised on the insert statement that errored out.
End transaction, implicit commit, with the single error free
insert.

Postgres:
Begin transaction
Insert - no error
Insert - error raised
Transaction loss = no implicit rollback to the single error free
insert.

Is this a correct interpretation of the Postgres transaction error
handling?

Well, in psql you can set ON_ERROR_ROLLBACK so that each statement
will be automatically preceded by a SAVEPOINT which will be
automatically rolled back if the statement has an error. There are
various constructs for accomplishing this in supported PLs,
depending on the language.

I'm not aware of any "explicitly start a transaction but guess at
whether a commit is intended" feature in PostgreSQL. An explicit
transaction is committed if and when you say so.

-Kevin

Hi Kevin,
Thank you, that is very helpful.
I am not worried about the implicit commits. The "no implicit savepoint" was more of an issue, since it created a necessity to create and destroy savepoints per each sql statement to capture any statement level error without losing a transaction, that approach has prohibitive performance repercussions.
I will check out the ON_ERROR_ROLLBACK feature.
Thank you,
Sincerely,
Kasia

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Tuesday, November 29, 2011 10:55 AM
To: Kasia Tuszynska; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction error handling

Kasia Tuszynska <ktuszynska@esri.com> wrote:

Oracle:
Begin transaction
Insert - no error
Implicit savepoint
Insert - error raised
Implicit rollback to the savepoint, no transaction loss, error
raised on the insert statement that errored out.
End transaction, implicit commit, with the single error free
insert.

Postgres:
Begin transaction
Insert - no error
Insert - error raised
Transaction loss = no implicit rollback to the single error free
insert.

Is this a correct interpretation of the Postgres transaction error
handling?

Well, in psql you can set ON_ERROR_ROLLBACK so that each statement
will be automatically preceded by a SAVEPOINT which will be
automatically rolled back if the statement has an error. There are
various constructs for accomplishing this in supported PLs,
depending on the language.

I'm not aware of any "explicitly start a transaction but guess at
whether a commit is intended" feature in PostgreSQL. An explicit
transaction is committed if and when you say so.

-Kevin

On Tue, Nov 29, 2011 at 10:57 AM, Kasia Tuszynska <ktuszynska@esri.com> wrote:

Postgres:
Begin transaction
Insert - no error
Insert - error raised
Transaction loss = no implicit rollback to the single error free insert.

Is this a correct interpretation of the Postgres transaction error handling?
If so, are there any changes being considered, or perhaps already implemented?

You can insert a savepoint explicitly if you wish, but without setting
one, then the whole transaction will be rolled back.

On Tue, 29 Nov 2011 09:57:24 -0800, Kasia Tuszynska wrote:

Hi Everybody,

This is an architectural question.
I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu)

I want to make sure that I have the correct understanding of the
Postgres architecture and would like to enquire if there are any plans
to change it.

Comparing Oracle and Postgres from the perspective of error handling on
the transaction level I observed the following:

Oracle:
Begin transaction Insert - no error Implicit savepoint Insert - error
raised Implicit rollback to the savepoint, no transaction loss, error
raised on the insert statement that errored out.
End transaction, implicit commit, with the single error free insert.

Postgres:
Begin transaction Insert - no error Insert - error raised Transaction
loss = no implicit rollback to the single error free insert.

Is this a correct interpretation of the Postgres transaction error
handling?
If so, are there any changes being considered, or perhaps already
implemented?

I suspect you may be barking up the wrong tree. Comparing default
behaviour of PSQL to SQL*Plus is not the same thing as comparing
PostgreSQL to Oracle.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
owner@postgresql.org] On Behalf Of Kasia Tuszynska
Sent: Tuesday, November 29, 2011 3:35 PM
To: Kevin Grittner; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction error handling

Hi Kevin,
Thank you, that is very helpful.
I am not worried about the implicit commits. The "no implicit
savepoint" was more of an issue, since it created a necessity to create
and destroy savepoints per each sql statement to capture any statement
level error without losing a transaction, that approach has prohibitive
performance repercussions.
I will check out the ON_ERROR_ROLLBACK feature.
Thank you,
Sincerely,
Kasia

Be aware that this option is a psql option and not one in the database itself, which means unless you are executing your SQL via psql it will not be of help to you.

Also the implementation of this is that psql issues implicit savepoints for you before each command in a transaction and handles the rollback for you if needed (which sounds an awful lot like the performance concern you have).

This is a major pain for porting Oracle based applications over for those that rely on this functionality.

Brad.

Hi,
Yes, I believe that you are right.

As far as I can gather, the postgres transaction error handling is like oracle stored procedures. If you do not catch the error the whole transaction is rolled back. I am curious why Postgres has gone with a model that does not allow the user a choice to deal with the statement level errors that may arise in a long transaction.

That either calls for very short transactions or an introduction of explicit savepoint creation and explicit savepoint destruction for every statement, if you - the user, want the ability to deal with statement errors that may arise.

I realize that it is almost impossible to change that architecture now, since it would be such a low level change, but I am surprised that it is not a common complaint from the user community, since bulk ddl loads would truly suffer.

I do not wish to compare Postgres to Oracle per se, I used oracle because I am more familiar with it than the Sql Server transaction model, they did a rewrite on transaction handling for SS 2005 and I never fully got into it.

Sincerely,
Kasia

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Walter Hurry
Sent: Tuesday, November 29, 2011 12:50 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction error handling

On Tue, 29 Nov 2011 09:57:24 -0800, Kasia Tuszynska wrote:

Hi Everybody,

This is an architectural question.
I am testing on Postgres 9.0.2 on windows and linux(suse, rhel, ubuntu)

I want to make sure that I have the correct understanding of the
Postgres architecture and would like to enquire if there are any plans
to change it.

Comparing Oracle and Postgres from the perspective of error handling on
the transaction level I observed the following:

Oracle:
Begin transaction Insert - no error Implicit savepoint Insert - error
raised Implicit rollback to the savepoint, no transaction loss, error
raised on the insert statement that errored out.
End transaction, implicit commit, with the single error free insert.

Postgres:
Begin transaction Insert - no error Insert - error raised Transaction
loss = no implicit rollback to the single error free insert.

Is this a correct interpretation of the Postgres transaction error
handling?
If so, are there any changes being considered, or perhaps already
implemented?

I suspect you may be barking up the wrong tree. Comparing default
behaviour of PSQL to SQL*Plus is not the same thing as comparing
PostgreSQL to Oracle.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

I have the same confusion...

于 2011/11/30 2:34, Rob Richardson 写道:

On 29 November 2011 21:34, Rob Richardson <RDRichardson@rad-con.com>
wrote:

If Oracle saves half of the data between the beginning and ending of the
transaction, doesn't that defeat the purpose of the transaction?

It sure enough kills Atomicity. I can see a use for this on importing data
from external sources that may violate existing unique keys, so illegal
inserts are ignored, but you still are left without any knowledge of what
rows where silently dropped. Since when is Oracle doing this, FMI? (It's
been a long while since I used it for anything serious)

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
owner@postgresql.org] On Behalf Of Rob Richardson
Sent: Tuesday, November 29, 2011 1:35 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction error handling

Very naïve question here: Why would you want to save the data from the
first insert?

You might want your code to recover from an error and take a different approach.

I thought the purpose of a transaction was to make sure that all steps
in the transaction executed, or none of them executed. If Oracle saves
half of the data between the beginning and ending of the transaction,
doesn't that defeat the purpose of the transaction?

This functionality is something that Postgres can do today. We expose the ability to do this with explicit savepoints. The difference is that Oracle allows you to set it on a per transaction basis (I believe) and it will behave this way for all statements in the transaction, where as we need to do it explicitly. Looking through the archives there does seem to be a performance problem on commit in Postgres if you issue a lot of savepoints (there were discussions of a fix but I am not sure the status of this).

Brad.

Hi,
Oracle does not save the data, in mid transaction, in the sense of a commit.
It keeps the association of the memory address related to the error free changes to the transaction and allows you – the developer to capture the error on that single incorrect change, and then continue with the subsequent sql statements that are part of that long transaction.
While in that state, the changes pertaining to that transaction are not written to any logs and are not written to file, you can still roll back the entire transaction.
Only when a commit occurs, does the transaction get written to SGA, archiving, file etc…

With Postgres that is not the case, if the 50th sql statement in a long transaction incurs an error, the whole transaction is rolled back for you automatically, you the developer have no say in that unless you bracket each statement with a savepoint creation and destruction, just to be able to capture the potential error that may arise on that 50th sql statement.

Sincerely,
Kasia
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bèrto ëd Sèra
Sent: Wednesday, November 30, 2011 12:49 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] transaction error handling

On 29 November 2011 21:34, Rob Richardson <RDRichardson@rad-con.com<mailto:RDRichardson@rad-con.com>> wrote:
If Oracle saves half of the data between the beginning and ending of the transaction, doesn't that defeat the purpose of the transaction?

It sure enough kills Atomicity. I can see a use for this on importing data from external sources that may violate existing unique keys, so illegal inserts are ignored, but you still are left without any knowledge of what rows where silently dropped. Since when is Oracle doing this, FMI? (It's been a long while since I used it for anything serious)

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.