--- On Tue, 1/1/08, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>
> Please provide a self-contained example.
Basically the example demonstrates the difference a single tuple UPDATE when preformed the following two ways:
UPDATE table ...; -- Sees OLD.
BEGIN; UPDATE table ...; COMMIT; --Sees NEW.
I was my understanding that all single DML statement are wrapped in their own transaction so I thought that these two statements should preform the same.
Regards,
Richard Broersma Jr.
--
-- PostgreSQL database dump
--
-- Started on 2008-01-02 05:35:55
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 7 (class 2615 OID 605027)
-- Name: instrumentation; Type: SCHEMA; Schema: -; Owner: teaminst
--
CREATE SCHEMA instrumentation;
ALTER SCHEMA instrumentation OWNER TO teaminst;
--
-- TOC entry 1784 (class 0 OID 0)
-- Dependencies: 6
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
--
-- TOC entry 325 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--
CREATE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
SET search_path = instrumentation, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1502 (class 1259 OID 605179)
-- Dependencies: 1769 1770 1771 1772 1773 1774 1775 1776 7
-- Name: looptimelines; Type: TABLE; Schema: instrumentation; Owner: teaminst; Tablespace:
--
CREATE TABLE looptimelines (
startdate date DEFAULT ('now'::text)::date NOT NULL,
enddate date DEFAULT '9999-12-31'::date NOT NULL,
startproject_code character varying(15) NOT NULL,
endproject_code character varying(15),
area integer NOT NULL,
process character(1) NOT NULL,
loop_nbr integer NOT NULL,
suffix character(1) DEFAULT ''::bpchar NOT NULL,
service_desc text DEFAULT '--HOLD--'::text NOT NULL,
CONSTRAINT looptimelines_area_check CHECK (((area >= 0) AND (area <= 99))),
CONSTRAINT looptimelines_loop_nbr_check CHECK (((loop_nbr >= 1) AND (loop_nbr <= 9999))),
CONSTRAINT looptimelines_process_check CHECK (((process >= 'A'::bpchar) AND (process <= 'Z'::bpchar))),
CONSTRAINT looptimelines_suffix_check CHECK ((((suffix >= 'A'::bpchar) AND (suffix <= 'Z'::bpchar)) OR (suffix = ''::bpchar)))
);
ALTER TABLE instrumentation.looptimelines OWNER TO teaminst;
SET search_path = public, pg_catalog;
--
-- TOC entry 276 (class 1247 OID 16405)
-- Dependencies: 6 1497
-- Name: breakpoint; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE breakpoint AS (
func oid,
linenumber integer,
targetname text
);
ALTER TYPE public.breakpoint OWNER TO postgres;
--
-- TOC entry 278 (class 1247 OID 16408)
-- Dependencies: 6 1498
-- Name: frame; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE frame AS (
level integer,
targetname text,
func oid,
linenumber integer,
args text
);
ALTER TYPE public.frame OWNER TO postgres;
--
-- TOC entry 320 (class 1247 OID 16417)
-- Dependencies: 6 1501
-- Name: proxyinfo; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE proxyinfo AS (
serverversionstr text,
serverversionnum integer,
proxyapiver integer,
serverprocessid integer
);
ALTER TYPE public.proxyinfo OWNER TO postgres;
--
-- TOC entry 316 (class 1247 OID 16411)
-- Dependencies: 6 1499
-- Name: targetinfo; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE targetinfo AS (
target oid,
schema oid,
nargs integer,
argtypes oidvector,
targetname name,
argmodes "char"[],
argnames text[],
targetlang oid,
fqname text,
returnsset boolean,
returntype oid
);
ALTER TYPE public.targetinfo OWNER TO postgres;
--
-- TOC entry 318 (class 1247 OID 16414)
-- Dependencies: 6 1500
-- Name: var; Type: TYPE; Schema: public; Owner: postgres
--
CREATE TYPE var AS (
name text,
varclass character(1),
linenumber integer,
isunique boolean,
isconst boolean,
isnotnull boolean,
dtype oid,
value text
);
ALTER TYPE public.var OWNER TO postgres;
SET search_path = instrumentation, pg_catalog;
--
-- TOC entry 41 (class 1255 OID 605215)
-- Dependencies: 7 325 322 322
-- Name: sample_for_new_or_old(looptimelines, looptimelines); Type: FUNCTION; Schema: instrumentation; Owner: postgres
--
CREATE FUNCTION sample_for_new_or_old(mynew looptimelines, myold looptimelines) RETURNS character varying
AS $$
BEGIN
IF 'NEW'::VARCHAR = ( SELECT 'NEW'::VARCHAR
FROM Instrumentation.looptimelines AS L
WHERE ROW( L.* ) = ROW( myNEW.* ) ) THEN
RETURN 'NEW'::VARCHAR;
ELSIF 'OLD'::VARCHAR = ( SELECT 'OLD'::VARCHAR
FROM Instrumentation.looptimelines AS L
WHERE ROW( L.* ) = ROW( myOLD.* ) ) THEN
RETURN 'OLD'::VARCHAR;
ELSE
RETURN 'NONE'::VARCHAR;
END IF;
END;
$$
LANGUAGE plpgsql STABLE SECURITY DEFINER;
ALTER FUNCTION instrumentation.sample_for_new_or_old(mynew looptimelines, myold looptimelines) OWNER TO postgres;
--
-- TOC entry 42 (class 1255 OID 605216)
-- Dependencies: 325 7
-- Name: test_visibility(); Type: FUNCTION; Schema: instrumentation; Owner: postgres
--
CREATE FUNCTION test_visibility() RETURNS trigger
AS $$
DECLARE
visib varchar;
BEGIN
visib = Instrumentation.sample_for_new_or_old( NEW, OLD );
RAISE NOTICE 'After performing the % operation, the % record is
visible before the commit.', TG_OP, visib;
IF TG_OP = 'DELETE' THEN
RETURN OLD;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql STABLE SECURITY DEFINER;
ALTER FUNCTION instrumentation.test_visibility() OWNER TO postgres;
SET search_path = public, pg_catalog;
--
-- TOC entry 22 (class 1255 OID 16419)
-- Dependencies: 6
-- Name: pldbg_abort_target(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_abort_target(session integer) RETURNS SETOF boolean
AS '$libdir/pldbgapi', 'pldbg_abort_target'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_abort_target(session integer) OWNER TO postgres;
--
-- TOC entry 23 (class 1255 OID 16420)
-- Dependencies: 6
-- Name: pldbg_attach_to_port(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_attach_to_port(portnumber integer) RETURNS integer
AS '$libdir/pldbgapi', 'pldbg_attach_to_port'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_attach_to_port(portnumber integer) OWNER TO postgres;
--
-- TOC entry 24 (class 1255 OID 16421)
-- Dependencies: 6 276
-- Name: pldbg_continue(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_continue(session integer) RETURNS breakpoint
AS '$libdir/pldbgapi', 'pldbg_continue'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_continue(session integer) OWNER TO postgres;
--
-- TOC entry 25 (class 1255 OID 16422)
-- Dependencies: 6
-- Name: pldbg_create_listener(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_create_listener() RETURNS integer
AS '$libdir/pldbgapi', 'pldbg_create_listener'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_create_listener() OWNER TO postgres;
--
-- TOC entry 26 (class 1255 OID 16423)
-- Dependencies: 6
-- Name: pldbg_deposit_value(integer, text, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_deposit_value(session integer, varname text, linenumber integer, value text) RETURNS boolean
AS '$libdir/pldbgapi', 'pldbg_deposit_value'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_deposit_value(session integer, varname text, linenumber integer, value text) OWNER TO postgres;
--
-- TOC entry 27 (class 1255 OID 16424)
-- Dependencies: 6
-- Name: pldbg_drop_breakpoint(integer, oid, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_drop_breakpoint(session integer, func oid, linenumber integer) RETURNS boolean
AS '$libdir/pldbgapi', 'pldbg_drop_breakpoint'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_drop_breakpoint(session integer, func oid, linenumber integer) OWNER TO postgres;
--
-- TOC entry 28 (class 1255 OID 16425)
-- Dependencies: 276 6
-- Name: pldbg_get_breakpoints(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_breakpoints(session integer) RETURNS SETOF breakpoint
AS '$libdir/pldbgapi', 'pldbg_get_breakpoints'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_breakpoints(session integer) OWNER TO postgres;
--
-- TOC entry 31 (class 1255 OID 16428)
-- Dependencies: 320 6
-- Name: pldbg_get_proxy_info(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_proxy_info() RETURNS proxyinfo
AS '$libdir/pldbgapi', 'pldbg_get_proxy_info'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_proxy_info() OWNER TO postgres;
--
-- TOC entry 29 (class 1255 OID 16426)
-- Dependencies: 6
-- Name: pldbg_get_source(integer, oid); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_source(session integer, func oid) RETURNS text
AS '$libdir/pldbgapi', 'pldbg_get_source'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_source(session integer, func oid) OWNER TO postgres;
--
-- TOC entry 30 (class 1255 OID 16427)
-- Dependencies: 6 278
-- Name: pldbg_get_stack(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_stack(session integer) RETURNS SETOF frame
AS '$libdir/pldbgapi', 'pldbg_get_stack'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_stack(session integer) OWNER TO postgres;
--
-- TOC entry 40 (class 1255 OID 16437)
-- Dependencies: 316 6
-- Name: pldbg_get_target_info(text, "char"); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_target_info(signature text, targettype "char") RETURNS targetinfo
AS '$libdir/targetinfo', 'pldbg_get_target_info'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_target_info(signature text, targettype "char") OWNER TO postgres;
--
-- TOC entry 32 (class 1255 OID 16429)
-- Dependencies: 6 318
-- Name: pldbg_get_variables(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_get_variables(session integer) RETURNS SETOF var
AS '$libdir/pldbgapi', 'pldbg_get_variables'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_get_variables(session integer) OWNER TO postgres;
--
-- TOC entry 33 (class 1255 OID 16430)
-- Dependencies: 276 6
-- Name: pldbg_select_frame(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_select_frame(session integer, frame integer) RETURNS breakpoint
AS '$libdir/pldbgapi', 'pldbg_select_frame'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_select_frame(session integer, frame integer) OWNER TO postgres;
--
-- TOC entry 34 (class 1255 OID 16431)
-- Dependencies: 6
-- Name: pldbg_set_breakpoint(integer, oid, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_set_breakpoint(session integer, func oid, linenumber integer) RETURNS boolean
AS '$libdir/pldbgapi', 'pldbg_set_breakpoint'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_set_breakpoint(session integer, func oid, linenumber integer) OWNER TO postgres;
--
-- TOC entry 35 (class 1255 OID 16432)
-- Dependencies: 6
-- Name: pldbg_set_global_breakpoint(integer, oid, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_set_global_breakpoint(session integer, func oid, linenumber integer, targetpid integer) RETURNS boolean
AS '$libdir/pldbgapi', 'pldbg_set_global_breakpoint'
LANGUAGE c;
ALTER FUNCTION public.pldbg_set_global_breakpoint(session integer, func oid, linenumber integer, targetpid integer) OWNER TO postgres;
--
-- TOC entry 36 (class 1255 OID 16433)
-- Dependencies: 276 6
-- Name: pldbg_step_into(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_step_into(session integer) RETURNS breakpoint
AS '$libdir/pldbgapi', 'pldbg_step_into'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_step_into(session integer) OWNER TO postgres;
--
-- TOC entry 37 (class 1255 OID 16434)
-- Dependencies: 6 276
-- Name: pldbg_step_over(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_step_over(session integer) RETURNS breakpoint
AS '$libdir/pldbgapi', 'pldbg_step_over'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_step_over(session integer) OWNER TO postgres;
--
-- TOC entry 38 (class 1255 OID 16435)
-- Dependencies: 6 276
-- Name: pldbg_wait_for_breakpoint(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_wait_for_breakpoint(session integer) RETURNS breakpoint
AS '$libdir/pldbgapi', 'pldbg_wait_for_breakpoint'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_wait_for_breakpoint(session integer) OWNER TO postgres;
--
-- TOC entry 39 (class 1255 OID 16436)
-- Dependencies: 6
-- Name: pldbg_wait_for_target(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION pldbg_wait_for_target(session integer) RETURNS integer
AS '$libdir/pldbgapi', 'pldbg_wait_for_target'
LANGUAGE c STRICT;
ALTER FUNCTION public.pldbg_wait_for_target(session integer) OWNER TO postgres;
--
-- TOC entry 21 (class 1255 OID 16418)
-- Dependencies: 6
-- Name: plpgsql_oid_debug(oid); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION plpgsql_oid_debug(functionoid oid) RETURNS integer
AS '$libdir/plugins/plugin_debugger', 'plpgsql_oid_debug'
LANGUAGE c STRICT;
ALTER FUNCTION public.plpgsql_oid_debug(functionoid oid) OWNER TO postgres;
SET search_path = instrumentation, pg_catalog;
--
-- TOC entry 1780 (class 0 OID 605179)
-- Dependencies: 1502
-- Data for Name: looptimelines; Type: TABLE DATA; Schema: instrumentation; Owner: teaminst
--
COPY looptimelines (startdate, enddate, startproject_code, endproject_code, area, process, loop_nbr, suffix, service_desc) FROM stdin;
2008-01-02 9999-12-31 02U20420 \N 84 P 1 Second Loop
2008-01-01 2008-01-22 02U20410 02U20420 84 P 1 First Loop
\.
--
-- TOC entry 1778 (class 2606 OID 605195)
-- Dependencies: 1502 1502 1502 1502 1502 1502
-- Name: looptimelines_primary_key; Type: CONSTRAINT; Schema: instrumentation; Owner: teaminst; Tablespace:
--
ALTER TABLE ONLY looptimelines
ADD CONSTRAINT looptimelines_primary_key PRIMARY KEY (enddate, area, process, loop_nbr, suffix);
--
-- TOC entry 1779 (class 2620 OID 605217)
-- Dependencies: 42 1502
-- Name: test_visibility; Type: TRIGGER; Schema: instrumentation; Owner: teaminst
--
CREATE CONSTRAINT TRIGGER test_visibility
AFTER INSERT OR DELETE OR UPDATE ON looptimelines
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE test_visibility();
--
-- TOC entry 1785 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
-- Completed on 2008-01-02 05:36:01
--
-- PostgreSQL database dump complete
--
sampledb=# \d
List of relations
Schema | Name | Type | Owner
-----------------+---------------+-------+----------
instrumentation | looptimelines | table | teaminst
(1 row)
sampledb=# select * from looptimelines;
startdate | enddate | startproject_code | endproject_code | area | process | loop_nbr | suffix | service_desc
------------+------------+-------------------+-----------------+------+---------+----------+--------+--------------
2008-01-02 | 9999-12-31 | 02U20420 | | 84 | P | 1 | | Second Loop
2008-01-01 | 2008-01-12 | 02U20410 | 02U20420 | 84 | P | 1 | | First Loop
(2 rows)
sampledb=# set search_path to instrumentation;
SET
sampledb=# \d
List of relations
Schema | Name | Type | Owner
-----------------+---------------+-------+----------
instrumentation | looptimelines | table | teaminst
(1 row)
sampledb=# select * from looptimelines;
startdate | enddate | startproject_code | endproject_code | area | process | loop_nbr | suffix | service_desc
------------+------------+-------------------+-----------------+------+---------+----------+--------+--------------
2008-01-02 | 9999-12-31 | 02U20420 | | 84 | P | 1 | | Second Loop
2008-01-01 | 2008-01-12 | 02U20410 | 02U20420 | 84 | P | 1 | | First Loop
(2 rows)
^
sampledb=# UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' WHERE endproject_code = '02U20420';
NOTICE: After performing the UPDATE operation, the OLD record is
visible before the commit.
UPDATE 1
sampledb=# BEGIN; UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' WHERE endproject_code = '02U20420'; COMMIT;
BEGIN
UPDATE 1
NOTICE: After performing the UPDATE operation, the NEW record is
visible before the commit.
COMMIT
sampledb=#
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
