Permalink
Browse files

Bug 118

    In 8.4 and above use TRUNCATE ONLY instead of TRUNCATE when cleaning out
    tables before a subscription. TRUNCATE ONLY behaves as TRUNCTE did in 8.3.

    This fixes bug # 118 - subscribing to a set with an inherited
    table  where the inheriting table has a lower tab_id than the
    parent table deletes all of the data in the parent table

    Merged from REL_2_0_STABLE added support for more pg versions +
    accounted for that prepareTableForCopy is in the version specific file
    also modified add_empty_table_to_replication to call TruncateOnlyTable
    instead of 'truncate' This is the behaviour this function would want.
  • Loading branch information...
1 parent 813086a commit 0d1781229fd3cc6a2abac083174166945d08ed7f @ssinger ssinger committed Aug 10, 2010
View
@@ -1,3 +1,10 @@
+Release 1.2.22
+
+-Bug #118 - Use TRUNCATE ONLY when cleaning out tables on subscribers in
+ versions of Postgresql that require it.
+
+-Explicit support for version 9.0
+
Release 1.2.21
@@ -6092,8 +6092,7 @@ begin
end if;
else
-- On other nodes, TRUNCATE the table
- v_query := ''truncate '' || v_fqname || '';'';
- execute v_query;
+ perform @NAMESPACE@.TruncateOnlyTable(v_fqname);
end if;
-- If p_idxname is NULL, then look up the PK index, and RAISE EXCEPTION if one does not exist
if p_idxname is NULL then
@@ -111,3 +111,16 @@ create or replace function @NAMESPACE@.make_function_strict (text, text) returns
comment on function @NAMESPACE@.make_function_strict (text, text) is
'Equivalent to 8.1+ ALTER FUNCTION ... STRICT';
+
+
+create or replace function @NAMESPACE@.TruncateOnlyTable ( name) returns void as
+$$
+begin
+ execute 'truncate '|| @NAMESPACE@.slon_quote_input($1);
+end;
+$$
+LANGUAGE plpgsql;
+
+
+comment on function @NAMESPACE@.TruncateOnlyTable(name) is
+'Calls TRUNCATE with out specifying ONLY, syntax supported in versions 8.3 and below';
@@ -113,3 +113,17 @@ end
comment on function @NAMESPACE@.make_function_strict (text, text) is
'Equivalent to 8.1+ ALTER FUNCTION ... STRICT';
+
+
+
+create or replace function @NAMESPACE@.TruncateOnlyTable ( name) returns void as
+$$
+begin
+ execute 'truncate '|| @NAMESPACE@.slon_quote_input($1);
+end;
+$$
+LANGUAGE plpgsql;
+
+
+comment on function @NAMESPACE@.TruncateOnlyTable(name) is
+'Calls TRUNCATE with out specifying ONLY, syntax supported in versions 8.3 and below';
@@ -51,7 +51,7 @@ begin
-- Try using truncate to empty the table and fallback to
-- delete on error.
-- ----
- execute ''truncate '' || @NAMESPACE@.slon_quote_input(v_tab_fqname);
+ perform @NAMESPACE@.TruncateOnlyTable(v_tab_fqname);
raise notice ''truncate of % succeeded'', v_tab_fqname;
return 1;
exception when others then
@@ -126,3 +126,17 @@ end
comment on function @NAMESPACE@.make_function_strict (text, text) is
'Equivalent to 8.1+ ALTER FUNCTION ... STRICT';
+
+
+
+create or replace function @NAMESPACE@.TruncateOnlyTable ( name) returns void as
+$$
+begin
+ execute 'truncate '|| @NAMESPACE@.slon_quote_input($1);
+end;
+$$
+LANGUAGE plpgsql;
+
+
+comment on function @NAMESPACE@.TruncateOnlyTable(name) is
+'Calls TRUNCATE with out specifying ONLY, syntax supported in versions 8.3 and below';
@@ -43,7 +43,7 @@ begin
-- Try using truncate to empty the table and fallback to
-- delete on error.
-- ----
- execute ''truncate '' || @NAMESPACE@.slon_quote_input(v_tab_fqname);
+ perform @NAMESPACE@.TruncateOnlyTable(v_tab_fqname);
raise notice ''truncate of % succeeded'', v_tab_fqname;
-- ----
@@ -131,3 +131,18 @@ end
comment on function @NAMESPACE@.make_function_strict (text, text) is
'Equivalent to 8.1+ ALTER FUNCTION ... STRICT';
+
+
+
+
+create or replace function @NAMESPACE@.TruncateOnlyTable ( name) returns void as
+$$
+begin
+ execute 'truncate '|| @NAMESPACE@.slon_quote_input($1);
+end;
+$$
+LANGUAGE plpgsql;
+
+
+comment on function @NAMESPACE@.TruncateOnlyTable(name) is
+'Calls TRUNCATE with out specifying ONLY, syntax supported in versions 8.3 and below';
@@ -0,0 +1,149 @@
+-- ----------------------------------------------------------------------
+-- slony1_funcs.v83.sql
+--
+-- Version 8.3 specific part of the replication support functions.
+--
+-- Copyright (c) 2007-2009, PostgreSQL Global Development Group
+-- Author: Jan Wieck, Afilias USA INC.
+--
+-- $Id: slony1_funcs.v84.sql,v 1.1.2.3 2010-05-17 17:15:19 ssinger Exp $
+-- ----------------------------------------------------------------------
+
+
+-- ----------------------------------------------------------------------
+-- FUNCTION prepareTableForCopy(tab_id)
+--
+-- Remove all content from a table before the subscription
+-- content is loaded via COPY and disable index maintenance.
+-- ----------------------------------------------------------------------
+create or replace function @NAMESPACE@.prepareTableForCopy(int4)
+returns int4
+as '
+declare
+ p_tab_id alias for $1;
+ v_tab_oid oid;
+ v_tab_fqname text;
+begin
+ -- ----
+ -- Get the OID and fully qualified name for the table
+ -- ---
+ select PGC.oid,
+ @NAMESPACE@.slon_quote_brute(PGN.nspname) || ''.'' ||
+ @NAMESPACE@.slon_quote_brute(PGC.relname) as tab_fqname
+ into v_tab_oid, v_tab_fqname
+ from @NAMESPACE@.sl_table T,
+ "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
+ where T.tab_id = p_tab_id
+ and T.tab_reloid = PGC.oid
+ and PGC.relnamespace = PGN.oid;
+ if not found then
+ raise exception ''Table with ID % not found in sl_table'', p_tab_id;
+ end if;
+
+ -- ----
+ -- Try using truncate to empty the table and fallback to
+ -- delete on error.
+ -- ----
+ perform @NAMESPACE@.TruncateOnlyTable(v_tab_fqname);
+ raise notice ''truncate of % succeeded'', v_tab_fqname;
+
+ -- ----
+ -- Setting pg_class.relhasindex to false will cause copy not to
+ -- maintain any indexes. At the end of the copy we will reenable
+ -- them and reindex the table. This bulk creating of indexes is
+ -- faster.
+ -- ----
+ update pg_class set relhasindex = ''f'' where oid = v_tab_oid;
+
+ return 1;
+ exception when others then
+ raise notice ''truncate of % failed - doing delete'', v_tab_fqname;
+ update pg_class set relhasindex = ''f'' where oid = v_tab_oid;
+ execute ''delete from only '' || @NAMESPACE@.slon_quote_input(v_tab_fqname);
+ return 0;
+end;
+' language plpgsql;
+
+comment on function @NAMESPACE@.prepareTableForCopy(int4) is
+'Delete all data and suppress index maintenance';
+
+-- ----------------------------------------------------------------------
+-- FUNCTION finishTableAfterCopy(tab_id)
+--
+-- Reenable index maintenance and reindex the table after COPY.
+-- ----------------------------------------------------------------------
+create or replace function @NAMESPACE@.finishTableAfterCopy(int4)
+returns int4
+as '
+declare
+ p_tab_id alias for $1;
+ v_tab_oid oid;
+ v_tab_fqname text;
+begin
+ -- ----
+ -- Get the tables OID and fully qualified name
+ -- ---
+ select PGC.oid,
+ @NAMESPACE@.slon_quote_brute(PGN.nspname) || ''.'' ||
+ @NAMESPACE@.slon_quote_brute(PGC.relname) as tab_fqname
+ into v_tab_oid, v_tab_fqname
+ from @NAMESPACE@.sl_table T,
+ "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN
+ where T.tab_id = p_tab_id
+ and T.tab_reloid = PGC.oid
+ and PGC.relnamespace = PGN.oid;
+ if not found then
+ raise exception ''Table with ID % not found in sl_table'', p_tab_id;
+ end if;
+
+ -- ----
+ -- Reenable indexes and reindex the table.
+ -- ----
+ update pg_class set relhasindex = ''t'' where oid = v_tab_oid;
+ execute ''reindex table '' || @NAMESPACE@.slon_quote_input(v_tab_fqname);
+
+ return 1;
+end;
+' language plpgsql;
+
+comment on function @NAMESPACE@.finishTableAfterCopy(int4) is
+'Reenable index maintenance and reindex the table';
+
+create or replace function @NAMESPACE@.pre74()
+returns integer
+as 'select 0' language sql;
+
+comment on function @NAMESPACE@.pre74() is
+'Returns 1/0 based on whether or not the DB is running a
+version earlier than 7.4';
+
+create or replace function @NAMESPACE@.make_function_strict (text, text) returns void as
+'
+declare
+ fun alias for $1;
+ parms alias for $2;
+ stmt text;
+begin
+ stmt := ''ALTER FUNCTION "_@CLUSTERNAME@".'' || fun || '' '' || parms || '' STRICT;'';
+ execute stmt;
+ return;
+end
+' language plpgsql;
+
+comment on function @NAMESPACE@.make_function_strict (text, text) is
+'Equivalent to 8.1+ ALTER FUNCTION ... STRICT';
+
+
+
+
+create or replace function @NAMESPACE@.TruncateOnlyTable ( name) returns void as
+$$
+begin
+ execute 'truncate only '|| @NAMESPACE@.slon_quote_input($1);
+end;
+$$
+LANGUAGE plpgsql;
+
+
+comment on function @NAMESPACE@.TruncateOnlyTable(name) is
+'Calls TRUNCATE with out specifying ONLY, syntax supported in versions 8.3 and below';
View
@@ -1905,15 +1905,20 @@ load_slony_base(SlonikStmt * stmt, int no_id)
use_major = 8;
use_minor = 0;
}
- else if ((adminfo->pg_version >= 80100) && adminfo->pg_version < 80500) /* 8.1, 8.2, 8.3, 8.4 */
+ else if ((adminfo->pg_version >= 80100) && adminfo->pg_version < 80400) /* 8.1, 8.2, 8.3 */
{
use_major = 8;
use_minor = 1;
}
- else /* 8.5+ */
+ else if((adminfo->pg_version >= 80400) && adminfo->pg_version < 90100) /* 8.4+ */
{
use_major = 8;
- use_minor = 1;
+ use_minor = 4;
+ }
+ else
+ {
+ use_major=8;
+ use_minor=4;
printf("%s:%d: Possible unsupported PostgreSQL "
"version (%d) %d.%d, defaulting to 8.1 support\n",
stmt->stmt_filename, stmt->stmt_lno, adminfo->pg_version,
@@ -2005,20 +2010,25 @@ load_slony_functions(SlonikStmt * stmt, int no_id)
use_major = 8;
use_minor = 0;
}
- else if ((adminfo->pg_version >= 80100) && adminfo->pg_version < 80300) /* 8.1 and 8.2 */
+ else if ((adminfo->pg_version >= 80100) && adminfo->pg_version < 80400) /* 8.1 and 8.2 and 8.3*/
{
use_major = 8;
use_minor = 1;
}
- else /* 8.3 and above */
- {
- use_major = 8;
- use_minor = 1;
- printf("%s:%d: Possible unsupported PostgreSQL "
- "version %d.%d, defaulting to 8.1 support\n",
- stmt->stmt_filename, stmt->stmt_lno,
- (adminfo->pg_version/10000), ((adminfo->pg_version%10000)/100));
- }
+ else if ((adminfo->pg_version >= 80400) && adminfo->pg_version < 90100) /* 8.4 and above */
+ {
+ use_major = 8;
+ use_minor = 4;
+ }
+ else
+ {
+ use_major=8;
+ use_minor=4;
+ printf("%s:%d: Possible unsupported PostgreSQL "
+ "version %d.%d, defaulting to 8.1 support\n",
+ stmt->stmt_filename, stmt->stmt_lno,
+ (adminfo->pg_version/10000), ((adminfo->pg_version%10000)/100));
+ }
/* Load schema, DB version specific */
db_notice_silent = true;

0 comments on commit 0d17812

Please sign in to comment.