Slony1 PostgreSQL Replication - Forthcoming 1.1.1 release

Author

Forthcoming 1.1.1 release

Christopher Browne

2005-09-23, 8:24 pm

Listed below is a context diff for a patch which I intend to apply to
the 1.1 branch next week in order to generate a 1.1.1 release.

There are three things justifying this:

1. Ian Burrell's fix to the multibyte problem which bites anyone
using Unicode or Big 5 character sets.

<http://gborg.postgresql.org/piperma...uly/000655.html>

This is a rather serious problem which needs to be addressed.

2. Some textual changes (yes, somewhat self-serving :-)) to log
shipping to help us use it to populate temporal databases.

Pointedly, the setsyncTracking_offl
ine() function has been augmented
with a SYNC time parameter which passes in the end time of each SYNC.

In addition, this function raises a notice every time it processes a
log so that the Gentle User will have some indication in logs/stdout
as to what logs have been processed. That's just too easy an
improvement to pass up :-).

3. When working with post-7.3 versions of PostgreSQL, the COPY
statements include field names.

This was a feature request a while back which didn't quite make it
into 1.1; it turns out that we need this for handling temporal
databases.

This set of changes conspicuously does NOT include responses to some
of the recent requests surrounding modifying the way sl_log_1 is
accessed; that's fodder for 1.2, and I will see about getting to that.

Apologies that I haven't been getting to some of these things quickly;
I had other higher priority projects. (To be followed by some
vacation, in October :-).)

I do have several patches partially tested that need to head into the
code base in October in preparation for 1.2; as that introduces
Windows support, that will doubtless be an exciting time.

Index: doc/adminguide/logshipping.sgml
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/doc/adminguide/logshipping.sgml,v
retrieving revision 1.10
diff -c -u -r1.10 logshipping.sgml
--- doc/adminguide/logshipping.sgml 29 Jun 2005 20:13:37 -0000 1.10
+++ doc/adminguide/logshipping.sgml 23 Sep 2005 19:21:12 -0000
@@ -238,13 +238,36 @@
dashes at the end of the <quote>header</quote> material:

<programlisting>
--- Slony-I sync log
--- Node 11, event 745
+-- Slony-I log shipping archive
+-- Node 11, Event 656
start transaction;
-select "_T1". setsyncTracking_offl
ine(1, '744', '745');
---------------------------------------------------------------------
+
+select "_T1". setsyncTracking_offl
ine(1, '655', '656', '2005-09-23 18:37:40.206342');
+-- end of log archiving header
</programlisting></para></listitem>

+
+<listitem><para> Note that the header includes a timestamp indicating
+SYNC time.
+<programlisting>
+-- Slony-I log shipping archive
+-- Node 11, Event 109
+start transaction;
+
+select "_T1". setsyncTracking_offl
ine(1, '96', '109', '2005-09-23 19:01:31.267403');
+-- end of log archiving header
+</programlisting></para>
+
+<para> This timestamp represents the time at which the
+<command>SYNC</command> was issued on the origin node.</para>
+
+<para> The value is stored in the log shipping configuration table
+sl_setsync_offline.</para>
+
+<para> If you are constructing a temporal database, this is likely to
+represent the time you will wish to have apply to all of the data in
+the given log shipping transaction log. </para>
+</listitem>
</itemizedlist>
</sect2>
</sect1>
Index: src/backend/slony1_funcs.sql
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.sql,v
retrieving revision 1.66
diff -c -u -r1.66 slony1_funcs.sql
--- src/backend/slony1_funcs.sql 20 Jul 2005 09:02:39 -0000 1.66
+++ src/backend/slony1_funcs.sql 23 Sep 2005 19:21:15 -0000
@@ -5196,3 +5196,29 @@

comment on view @NAMESPACE@.sl_status is 'View showing how far behind remote nodes are.';

+create or replace function @NAMESPACE@.copyFields(integer)
+returns text
+as '
+declare
+ result text;
+ prefix text;
+ prec record;
+begin
+ result := '''';
+ prefix := ''(''; -- Initially, prefix is the opening paren
+
+ for prec in select @NAMESPACE@.slon_quote_input(a.attname) as column from @NAMESPACE@.sl_table t, pg_catalog.pg_attribute a where t.tab_id = $1 and t.tab_reloid = a.attrelid and a.attnum > 0 order by attnum
+ loop
+ result := result || prefix || prec.column;
+ prefix := '',''; -- Subsequently, prepend columns with commas
+ end loop;
+ result := result || '')'';
+ return result;
+end;
+' language plpgsql;
+
+comment on function @NAMESPACE@.copyFields(integer) is
+'Return a string consisting of what should be appended to a COPY statement
+to specify fields for the passed-in tab_id.
+
+In PG versions > 7.3, this looks like (field1,field2,...fieldn)';
Index: src/backend/slony1_funcs.v73.sql
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.v73.sql,v
retrieving revision 1.7
diff -c -u -r1.7 slony1_funcs.v73.sql
--- src/backend/slony1_funcs.v73.sql 7 Jun 2005 21:51:05 -0000 1.7
+++ src/backend/slony1_funcs.v73.sql 23 Sep 2005 19:21:16 -0000
@@ -26,3 +26,13 @@
end;
' language plpgsql;

+comment comment on function @NAMESPACE@.truncateTable(text) is
+'Delete all data from specified table';
+
+create or replace function @NAMESPACE@.pre74()
+returns integer
+as 'select 1' 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';
Index: src/backend/slony1_funcs.v74.sql
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/backend/slony1_funcs.v74.sql,v
retrieving revision 1.6
diff -c -u -r1.6 slony1_funcs.v74.sql
--- src/backend/slony1_funcs.v74.sql 19 Apr 2005 15:47:03 -0000 1.6
+++ src/backend/slony1_funcs.v74.sql 23 Sep 2005 19:21:16 -0000
@@ -26,3 +26,13 @@
end;
' language plpgsql;

+comment on function @NAMESPACE@.truncateTable(text) is
+'Delete all data from specified 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';
Index: src/slon/remote_worker.c
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/src/slon/remote_worker.c,v
retrieving revision 1.88
diff -c -u -r1.88 remote_worker.c
--- src/slon/remote_worker.c 8 Aug 2005 15:51:18 -0000 1.88
+++ src/slon/remote_worker.c 23 Sep 2005 19:21:21 -0000
@@ -248,11 +248,12 @@
static int open_log_archive (int node_id, char *seqbuf);
static int close_log_archive ();
static void terminate_log_archiv
e ();
-static int generate_archive_hea
der (int node_id, char *seqbuf);
+static int generate_archive_hea
der (int node_id, const char *seqbuf);
static int submit_query_to_arch
ive(SlonDString *ds);
static int submit_string_to_arc
hive (const char *s);
static int submit_raw_data_to_a
rchive (const char *s);
-static int logarchive_tracking (const char *namespace, int sub_set, const char *firstseq, const char *seqbuf);
+static int logarchive_tracking (const char *namespace, int sub_set, const char *firstseq,
+ const char *seqbuf, const char *timestamp);
static int write_void_log (int node_id, char *seqbuf, const char *message);

#define TERMINATE_QUERY_AND_
ARCHIVE dstring_free(&query); terminate_log_archiv
e();
@@ -1397,7 +1398,7 @@
node->no_id, archive_tmp, strerror(errno));
slon_abort();
}
- rc = logarchive_tracking(
rtcfg_namespace, ddl_setid, seqbuf, seqbuf);
+ rc = logarchive_tracking(
rtcfg_namespace, ddl_setid, seqbuf, seqbuf, event->ev_timestamp_c);
if (rc < 0) {
slon_log(SLON_ERROR,
" remoteWorkerThread_%
d: "
"Could not generate DDL archive tracker %s - %s",
@@ -2364,11 +2365,14 @@
SlonDString indexregenquery;
int ntuples1;
int ntuples2;
+ int ntuples3;
int tupno1;
int tupno2;
PGresult *res1;
PGresult *res2;
PGresult *res3;
+ PGresult *res4;
+ int nodeon73;
int rc;
int set_origin = 0;
SlonNode *sub_node;
@@ -2922,15 +2926,60 @@
/*
* Begin a COPY from stdin for the table on the local DB
*/
- slon_log(SLON_DEBU
G4, " remoteWorkerThread_%
d: "
+ slon_log(SLON_DEB
UG2, " remoteWorkerThread_%
d: "
"Begin COPY of table %s\n",
node->no_id, tab_fqname);

+ dstring_init(&query2);
+ slon_mkquery(&query2, "select %s.copyFields(%d);",
+ rtcfg_namespace, tab_id);
+
+ res3 = PQexec(pro_dbconn, dstring_data(&query2));
+
+ if (PQresultStatus(res2
) != PGRES_TUPLES_OK) {
+ slon_log(SLON_ER
ROR, " remoteWorkerThread_%
d: \"%s\" %s\n",
+ node->no_id, dstring_data(&query2),
+ PQresultErrorMessage
(res3));
+ PQclear(res3);
+ PQclear(res1);
+ slon_disconnectd
b(pro_conn);
+ dstring_free(&query1);
+ terminate_log_ar
chive();
+ return -1;
+ }
+
+ slon_mkquery(&query2, "select %s.pre74();",
+ rtcfg_namespace);
+ res4 = PQexec(loc_dbconn, dstring_data(&query2));
+
+ if (PQresultStatus(res4
) != PGRES_TUPLES_OK) {
+ slon_log(SLON_ER
ROR, " remoteWorkerThread_%
d: \"%s\" %s\n",
+ node->no_id, dstring_data(&query2),
+ PQresultErrorMessage
(res4));
+ PQclear(res4);
+ PQclear(res3);
+ PQclear(res1);
+ slon_disconnectd
b(pro_conn);
+ dstring_free(&query1);
+ dstring_free(&query2);
+ terminate_log_ar
chive();
+ return -1;
+ }
+
+ /* Are we running on < PG 7.4??? result = */
+ nodeon73 = atoi(PQgetvalue(res4
, 0, 0));
+
+ slon_log(SLON_DEB
UG2, " remoteWorkerThread_%
d: "
+ " nodeon73 is %d\n",
+ node->no_id, nodeon73);
+
slon_mkquery(&query1,
"select %s. truncateTable('%s');
"
- "copy %s from stdin; ",
+ "copy %s %s from stdin; ",
rtcfg_namespace,
- tab_fqname, tab_fqname);
+ tab_fqname, tab_fqname,
+ nodeon73 ? "" : PQgetvalue(res3, 0, 0)
+ );
res2 = PQexec(loc_dbconn, dstring_data(&query1));
if (PQresultStatus(res2
) != PGRES_COPY_IN)
{
@@ -2946,9 +2995,9 @@
return -1;
}
if (archive_dir) {
- slon_log(SLON_DEB
UG4, "start log ship copy of %s\n", tab_fqname);
slon_mkquery(&query1,
- "delete from %s;copy %s from stdin;", tab_fqname, tab_fqname);
+ "delete from %s;copy %s %s from stdin;", tab_fqname, tab_fqname,
+ nodeon73 ? "" : PQgetvalue(res3, 0, 0));
rc = submit_query_to_arch
ive(&query1);
if (rc < 0) {
slon_log(SLON_ERROR,
" remoteWorkerThread_d
: "
@@ -2965,7 +3014,8 @@
* Begin a COPY to stdout for the table on the provider DB
*/
slon_mkquery(&query1,
- "copy %s to stdout; ", tab_fqname);
+ "copy %s %s to stdout; ", tab_fqname, PQgetvalue(res3, 0, 0));
+ PQclear(res3);
res3 = PQexec(pro_dbconn, dstring_data(&query1));
if (PQresultStatus(res3
) != PGRES_COPY_OUT)
{
@@ -3471,7 +3521,6 @@
ssy_maxxid = PQgetvalue(res1, 0, 2);
ssy_xip = PQgetvalue(res1, 0, 3);

- dstring_init(&query2);
slon_mkquery(&query2,
"log_xid >= '%s' or (log_xid >= '%s'",
ssy_maxxid, ssy_minxid);
@@ -3692,21 +3741,21 @@
*/
if (archive_dir)
{
- rc = open_log_archive(nod
e->no_id, seqbuf);
- if (rc == -1) {
- slon_log(SLON_ERROR,
" remoteWorkerThread_%
d: "
- "Cannot open archive file %s - %s\n",
- node->no_id, archive_tmp, strerror(errno));
- dstring_free(&query);
- return 60;
- }
- rc = generate_archive_hea
der(node->no_id, seqbuf);
- if (rc < 0) {
- slon_log(SLON_ERROR,
" remoteWorkerThread_%
d: "
- "Cannot write to archive file %s - %s",
- node->no_id, archive_tmp, strerror(errno));
- return 60;
- }
+ rc = open_log_archive(nod
e->no_id, seqbuf);
+ if (rc == -1) {
+ slon_log(SLON_ER
ROR, " remoteWorkerThread_%
d: "
+ "Cannot open archive file %s - %s\n",
+ node->no_id, archive_tmp, strerror(errno));
+ dstring_free(&query);
+ return 60;
+ }
+ rc = generate_archive_hea
der(node->no_id, seqbuf);
+ if (rc < 0) {
+ slon_log(SLON_ER
ROR, " remoteWorkerThread_%
d: "
+ "Cannot write to archive file %s - %s",
+ node->no_id, archive_tmp, strerror(errno));
+ return 60;
+ }
}

/*
@@ -3774,7 +3823,7 @@
int64 prov_seqno;

prov_seqno = get_last_forwarded_c
onfirm(event->ev_origin,
- provide
r->no_id);
+ provider->no_id);
if (prov_seqno < 0)
{
slon_log(SLON_WARN, " remoteWorkerThread_%
d: "
@@ -4018,14 +4067,18 @@
*/
if (archive_dir)
{
- rc = logarchive_tracking(
rtcfg_namespace, sub_set,
- PQgetvalue(res1, tupno1, 1), seqbuf);
- if (rc < 0) {
- slon_log(SLON_ERROR,
" remoteWorkerThread_%
d: "
- "Cannot write to archive file %s - %s",
- node->no_id, archive_tmp, strerror(errno));
- return 60;
- }
+ slon_log(SLON_D
EBUG2, "writing archive log...\n");
+ fflush(stderr);

+ fflush(stdout);

+ rc = logarchive_tracking(
rtcfg_namespace, sub_set,
+ PQgetvalue(res1, tupno1, 1), seqbuf,
+ event->ev_timestamp_c);
+ if (rc < 0) {
+ slon_log(SLON_
ERROR, " remoteWorkerThread_%
d: "
+ "Cannot write to archive file %s - %s",
+ node->no_id, archive_tmp, strerror(errno));
+ return 60;
+ }
}
}
PQclear(res1);
@@ -4904,16 +4957,27 @@
int close_log_archive () {
int rc = 0;
if (archive_dir) {
- rc = fprintf(archive_fp, "\n------------------------------------------------------------------\n-- End Of Archive Log\n------------------------------------------------------------------\ncommit;\n");
+ rc = fprintf(archive_fp,
+ "\n------------------------------------------------------------------\n"
+ "-- End Of Archive Log\n"
+ "------------------------------------------------------------------\n"
+ "commit;\n"
+ "vacuum analyze %s. sl_setsync_offline;\
n",
+ rtcfg_namespace);
rc = fclose(archive_fp);
rc = rename(archive_tmp, archive_name);
}
return rc;
}

-int logarchive_tracking (const char *namespace, int sub_set, const char *firstseq, const char *seqbuf) {
- return fprintf(archive_fp, "\nselect %s. setsyncTracking_offl
ine(%d, '%s', '%s');\n-- end of log archiving header\n------------------------------------------------------------------\n-- start of Slony-I data\n---------------------------------------------
---------------------\n",
- namespace, sub_set, firstseq, seqbuf);
+int logarchive_tracking (const char *namespace, int sub_set, const char *firstseq,
+ const char *seqbuf, const char *timestamp) {
+ return fprintf(archive_fp, "\nselect %s. setsyncTracking_offl
ine(%d, '%s', '%s', '%s');\n"
+ "-- end of log archiving header\n"
+ "------------------------------------------------------------------\n"
+ "-- start of Slony-I data\n"
+ "------------------------------------------------------------------\n",
+ namespace, sub_set, firstseq, seqbuf, timestamp);
}

int submit_query_to_arch
ive(SlonDString *ds) {
@@ -4935,15 +4999,12 @@
}
}

-int generate_archive_hea
der (int node_id, char *seqbuf) {
- time_t now;
- now = time(NULL);
+int generate_archive_hea
der (int node_id, const char *seqbuf) {
return fprintf(archive_fp,
"-- Slony-I log shipping archive\n"
"-- Node %d, Event %s\n"
- "-- at... %s\n"
"start transaction;\n",
- node_id, seqbuf, ctime(&now));
+ node_id, seqbuf);
}

/* write_void_log() writes out a "void" log consisting of the message
Index: tools/slony1_dump.sh
====================
====================
====================
=======
RCS file: /usr/local/cvsroot/slony1/slony1-engine/tools/slony1_dump.sh,v
retrieving revision 1.1
diff -c -u -r1.1 slony1_dump.sh
--- tools/slony1_dump.sh 17 Feb 2005 06:59:05 -0000 1.1
+++ tools/slony1_dump.sh 23 Sep 2005 19:21:21 -0000
@@ -91,15 +91,15 @@
create table $clname.sl_setsync_offline (
ssy_setid int4,
ssy_seqno int8,
+ ssy_synctime timestamptz,

CONSTRAINT "sl_setsync-pkey"
PRIMARY KEY (ssy_setid)
);

-
--- ----------------------------------------------------------------------
+-- -----------------------------------------------------------------------------
-- FUNCTION sequenceSetValue_off
line (seq_id, seq_origin, ev_seqno, last_value)
--- ----------------------------------------------------------------------
+-- -----------------------------------------------------------------------------
create or replace function $clname. sequenceSetValue_off
line(int4, int8) returns int4
as '
declare
@@ -127,15 +127,16 @@
end;
' language plpgsql;

--- ----------------------------------------------------------------------
--- FUNCTION setsyncTracking_offl
ine (seq_id, seq_origin, ev_seqno, last_value)
--- ----------------------------------------------------------------------
+-- ---------------------------------------------------------------------------------------
+-- FUNCTION setsyncTracking_offl
ine (seq_id, seq_origin, ev_seqno, last_value, sync_time)
+-- ---------------------------------------------------------------------------------------
create or replace function $clname. setsyncTracking_offl
ine(int4, int8, int8) returns int8
as '
declare
p_set_id alias for \$1;
p_old_seq alias for \$2;
p_new_seq alias for \$3;
+ p_sync_time alias for \$4;
v_row record;
begin
select ssy_seqno into v_row from $clname.sl_setsync_offline
@@ -148,8 +149,9 @@
raise exception ''Slony-I: set % is on sync %, this archive log expects %'',
p_set_id, v_row.ssy_seqno, p_old_seq;
end if;
+ raise notice ''Slony-I: Process set % sync % time'', p_set_id, p_new_seq, p_sync_time;

- update $clname.sl_setsync_offline set ssy_seqno = p_new_seq
+ update $clname.sl_setsync_offline set ssy_seqno = p_new_seq, ssy_synctime = p_sync_time
where ssy_setid = p_set_id;
return p_new_seq;
end;

--
(format nil "~S@~S" "cbbrowne" "ca.afilias.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Jim Archer

2005-09-23, 8:24 pm

--On Friday, September 23, 2005 5:44 PM -0400 Christopher Browne
<cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:

> Listed below is a context diff for a patch which I intend to apply to
> the 1.1 branch next week in order to generate a 1.1.1 release.

Great news, thanks!

That leads me to wonder, how does one go about upgrading Slony in a
production environment? Do we basically have to drop the Slony schema and
rebuild all the nodes from scratch?

Christopher Browne

2005-09-23, 8:24 pm

Jim Archer wrote:

> --On Friday, September 23, 2005 5:44 PM -0400 Christopher Browne
> <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:
>
>
>
> Great news, thanks!
>
> That leads me to wonder, how does one go about upgrading Slony in a
> production environment? Do we basically have to drop the Slony schema
> and rebuild all the nodes from scratch?
>

Nope. That would be very UnCool.

See <http://slony.info/>

Upgrading

Beginning with version 1.0.5 the slon replication engine refuses to work
against any database that does not have the stored procedures for the
same version loaded or where the shared object containing the C language
support functions and the log trigger does not match the version number.

This means that the Slony installation on all nodes in a cluster must be
upgraded at once.

The proper upgrade procedure is this:

1. Stop the slon replication engine on all nodes.
2. Install the new Slony version on all nodes.
3. Execute a slonik script containing the command

update functions (id = );

for every node in the cluster.
4. Start all slon replication engines.

We've been careful to make sure that an upgrade adds tables and columns
as needed to the internal Slony-I tables

Jim Archer

2005-09-23, 8:24 pm

--On Friday, September 23, 2005 5:57 PM -0400 Christopher Browne
<cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:

> Nope. That would be very UnCool.
>
> See <http://slony.info/>
>
>
> Upgrading

Ah, that's great! Thanks, and I'm sorry I didn't check the web site before
asking.

Best,

Jim

Andreas Pflug

2005-09-24, 3:23 am

Christopher Browne wrote:
> Listed below is a context diff for a patch which I intend to apply to
> the 1.1 branch next week in order to generate a 1.1.1 release.

4. sl_confirm.con_timestamp default should be fixed
http://gborg.postgresql.org/piperma...ust/002793.html

5. rebuildListenEntries
for enabled nodes only
http://gborg.postgresql.org/piperma...ber/002909.html

Regards,
Andreas

Hannu Krosing

2005-09-24, 8:24 pm

On R, 2005-09-23 at 17:44 -0400, Christopher Browne wrote:

> This set of changes conspicuously does NOT include responses to some
> of the recent requests surrounding modifying the way sl_log_1 is
> accessed;

In case you are referring to compressing ctids, I attach a complete
patch to remote_worker.c , fixing an issue of ignoring last run of
ctid's (required bugfix) and collapsing parser case's for digits
(question of choice).

> that's fodder for 1.2, and I will see about getting to that.

Perhaps you could change the index on sl_log_1 to something more useful.

Currently the index is on

create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
(log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq);

which, on postgresql 7.4, is useful only in case there is only one set
subscribed from the master,

And in 99.9% of cases (that is when ther is more than 1 xid), the index
is not used for sorting, that is log_actionseq in the index is mostly
dead weight.

Also, as slon forces use of indexscan, it actually forces an indexscan
on constant column ( @NAMESPACE@.sl_log_1.log_origin ) which performs
even worse than plain seqscan.

Of course these problems manifest themselves in a really bad way only on
postgresql 7.4, meaning that they will eventually go away even without
doing anything.

In any case mention the index issue in readme, perhaps recommending
using an index like this:

create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
(log_xid @NAMESPACE@.xxid_ops);

instead, which should be at least somewhat useful in most cases actually
used by slon, and does not force pessimal plans in some.

--
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>

Vivek Khera

2005-09-26, 11:24 am

On Sep 23, 2005, at 5:44 PM, Christopher Browne wrote:

> Listed below is a context diff for a patch which I intend to apply to
> the 1.1 branch next week in order to generate a 1.1.1 release.
>

Also a patch to fix failure to build on a box without perl:

http://gborg.postgresql.org/piperma...eral/2005-July/
002498.html

Christopher Browne

2005-09-26, 11:24 am

Vivek Khera wrote:

>
> On Sep 23, 2005, at 5:44 PM, Christopher Browne wrote:
>
>
> Also a patch to fix failure to build on a box without perl:
>
> http://gborg.postgresql.org/piperma...eral/2005-July/
> 002498.html

That seems to be in place in both 1.1_STABLE and in HEAD, although the
form seems to vary a bit from the patch you suggested in the July email.

Both branches have the following in tools/altperl/Makefile:

all:
ifndef PERL
^I@echo "The altperl tools require that Perl be installed."
^Iexit 1;
endif

....

install: all installdirs
ifndef toolsbin
^I@echo "The altperl tools won't be installed unless --with-perltools is
specified in configure"
else

Christopher Browne

2005-09-26, 1:24 pm

Andreas Pflug wrote:

> Christopher Browne wrote:
>
>
I'm not certain that I can agree with either.

> 4. sl_confirm.con_timestamp default should be fixed
> http://gborg.postgresql.org/piperma...ust/002793.html
>

The proposed patch wouldn't do anything to fix the perceived problem
because the timestamp is assigned as CURRENT_TIME in function
query_append_event()
in src/slon/remote_worker.c

"insert into %s.sl_confirm "
" (con_origin, con_received, con_seqno,
con_timestamp) "
" values (%d, %d, '%s', CURRENT_TIMESTAMP); ",

That is where the change would have to be applied.

That is actually a pretty safe place to put now(), so I'll do that...

> 5. rebuildListenEntries
for enabled nodes only
> http://gborg.postgresql.org/piperma...ber/002909.html
>

I have a concern about this in that having extra nodes around could lead
to the cleanup thread deciding it can NEVER purge out old data for
enabled nodes if any activity ever hits one of the unenabled nodes.

I am VERY reluctant to do this as a 1.1 change because I would prefer to
see it heavily tested before deployment, and the upcoming 1.1 changes
won't be seeing the full redo-all-platforms-from-scratch testing that
1.2 will.

In any case, the use of "virtual nodes" strikes me as being a
questionable way to do things.

You're using the "virtual nodes" to store the "admin conninfo"
information, right?

If so, then it seems to me that a better way to handle this is NOT to
create "virtual nodes," but rather to create a new table, sl_adminconn,
which stores this information and makes it unnecessary to have "fake nodes."

I'd much rather add a new table (ideally with foreign key constraints
and such) which are invisible to slon than have to add logic to ignore
the "virtual nodes."

Andreas Pflug

2005-09-26, 1:24 pm

Christopher Browne wrote:

> The proposed patch wouldn't do anything to fix the perceived problem
> because the timestamp is assigned as CURRENT_TIME in function
> query_append_event()
in src/slon/remote_worker.c
>
> "insert into %s.sl_confirm "
> " (con_origin, con_received, con_seqno,
> con_timestamp) "
> " values (%d, %d, '%s', CURRENT_TIMESTAMP); ",
>
> That is where the change would have to be applied.
>
> That is actually a pretty safe place to put now(), so I'll do that...

AFAIR I got an SQL exception from a pl function that triggered the
default when enabling a node, so your fix doesn't replace mine, but
fixes another occurrence.

> I have a concern about this in that having extra nodes around could lead
> to the cleanup thread deciding it can NEVER purge out old data for
> enabled nodes if any activity ever hits one of the unenabled nodes.

Ignoring non-enabled nodes seems the correct handling for all activities
in general. Non-enabled nodes won't create events or consume events, so
there's no need for listens to them (independent of my "misuse" of nodes).

> I am VERY reluctant to do this as a 1.1 change because I would prefer to
> see it heavily tested before deployment, and the upcoming 1.1 changes
> won't be seeing the full redo-all-platforms-from-scratch testing that
> 1.2 will.
>
> In any case, the use of "virtual nodes" strikes me as being a
> questionable way to do things.
>
> You're using the "virtual nodes" to store the "admin conninfo"
> information, right?

Correct, as path. Actually, the admin node *is* a node, simply not a
slon node but a controlling participant in the slony replication setup.

>
> If so, then it seems to me that a better way to handle this is NOT to
> create "virtual nodes," but rather to create a new table, sl_adminconn,
> which stores this information and makes it unnecessary to have "fake nodes."

It needs replication as well. With 1.0 everything was fine, only 1.1
auto-listen creation broke it.

> I'd much rather add a new table (ideally with foreign key constraints
> and such) which are invisible to slon than have to add logic to ignore
> the "virtual nodes."

We want to support 1.0 and up. Ignoring disabled nodes would accomplish
this. A new table would mean we can support 1.0 and 1.2, but not 1.1.

Regards,
Andreas

Christopher Browne

2005-09-26, 8:25 pm

Hannu Krosing wrote:

> Perhaps you could change the index on sl_log_1 to something more useful.
>
>Currently the index is on
>
>create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
> (log_origin, log_xid @NAMESPACE@.xxid_ops, log_actionseq);
>
>which, on postgresql 7.4, is useful only in case there is only one set
>subscribed from the master,
>
>And in 99.9% of cases (that is when ther is more than 1 xid), the index
>is not used for sorting, that is log_actionseq in the index is mostly
>dead weight.
>
>Also, as slon forces use of indexscan, it actually forces an indexscan
>on constant column ( @NAMESPACE@.sl_log_1.log_origin ) which performs
>even worse than plain seqscan.
>
>Of course these problems manifest themselves in a really bad way only on
>postgresql 7.4, meaning that they will eventually go away even without
>doing anything.
>
>In any case mention the index issue in readme, perhaps recommending
>using an index like this:
>
>create index sl_log_1_idx1 on @NAMESPACE@.sl_log_1
> (log_xid @NAMESPACE@.xxid_ops);
>
>instead, which should be at least somewhat useful in most cases actually
>used by slon, and does not force pessimal plans in some.
>
>
I will indeed see about adding this, pronto.

I just had the entertainment of discovering a node (with 3 sets) where
the database, being somewhat uninteresting, had been down for 3 weeks.

I thought I'd add this index "for grins", as I was watching the log
queries run typically for 6-8 seconds.

As soon as the index was created, the following became typical:

DEBUG2 remoteWorkerThread_2
: SYNC 480236 processing
DEBUG2 remoteWorkerThread_2
: syncing set 3 with 1 table(s) from provider 1
DEBUG2 remoteWorkerThread_2
: syncing set 1 with 74 table(s) from provider 1
DEBUG2 remoteWorkerThread_2
: syncing set 2 with 1 table(s) from provider 1
DEBUG2 remoteHelperThread_2
_1: 0.014 seconds delay for first row
DEBUG2 remoteHelperThread_2
_1: 0.023 seconds until close cursor

I'd say that's a worthwhile index to have around :-).

Ian Burrell

2005-09-26, 8:25 pm

On 9/23/05, Christopher Browne <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:
>
> 1. Ian Burrell's fix to the multibyte problem which bites anyone
> using Unicode or Big 5 character sets.
>
> <http://gborg.postgresql.org/piperma...uly/000655.htm=
l>
>
> This is a rather serious problem which needs to be addressed.
>

This patch hasn't been applied to the REL_1_1_STABLE branch yet. It
is only on the HEAD branch.

- Ian

Ian Burrell

2005-09-26, 8:25 pm

I just ran into a bug which crashes slonik when it is passed a file
which doesn't exist. It looks like it was fixed on HEAD as part of
revision 1.43 on src/slonik/slonik.c. There are other changes in that
revision so I have extracted the relevant patch and appended it. It
looks like it could be applied separately.

- Ian

--- src/slonik/slonik.c 25 May 2005 16:16:02 -0000 1.42
+++ src/slonik/slonik.c 29 Jun 2005 01:48:15 -0000 1.43
@@ -101,6 +200,11 @@
FILE *fp;

fp =3D fopen(argv[optind], "r");
+ if (fp =3D=3D NULL)
+ {
+ printf("could not open file '%s'\n",
argv[optind]);
+ return -1;
+ }
scan_new_input_file(
fp);
current_file =3D (char *)argv[optind++];
yylineno =3D 1;

Christopher Browne

2005-09-26, 8:25 pm

Ian Burrell <ianburrell- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org> writes:
> On 9/23/05, Christopher Browne <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:
>
> This patch hasn't been applied to the REL_1_1_STABLE branch yet. It
> is only on the HEAD branch.

It's now on REL_1_1_STABLE...
--
let name="cbbrowne" and tld="ca.afilias.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Christopher Browne

2005-09-26, 8:25 pm

Ian Burrell <ianburrell- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org> writes:
> I just ran into a bug which crashes slonik when it is passed a file
> which doesn't exist. It looks like it was fixed on HEAD as part of
> revision 1.43 on src/slonik/slonik.c. There are other changes in that
> revision so I have extracted the relevant patch and appended it. It
> looks like it could be applied separately.
>
> - Ian
>
> --- src/slonik/slonik.c 25 May 2005 16:16:02 -0000 1.42
> +++ src/slonik/slonik.c 29 Jun 2005 01:48:15 -0000 1.43
> @@ -101,6 +200,11 @@
> FILE *fp;
>
> fp = fopen(argv[optind], "r");
> + if (fp == NULL)
> + {
> + printf("could not open file '%s'\n",
> argv[optind]);
> + return -1;
> + }
> scan_new_input_file(
fp);
> current_file = (char *)argv[optind++];
> yylineno = 1;

That seems a reasonable change to apply. It's not a BIG thing, but
it's fairly obvious that the scan will then fail.

Thanks!
--
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Sponsored Links