On 12/10/2010 5:16 PM, Christopher Browne wrote:
> luda posch<ludaludaluda at gmail.com>  writes:
>>  I want to be able to see specifically what sql statements pertain to which SYNC
>>  events.  It does not look like either of my log tables have any relation to the
>>  sl_event.ev_seqno, although I do see in the log tables things that look like
>>  that is where the information i am looking for is (ie: log_cmdtype,
>>  log_cmddata)
>>
>>  I thought that log_actionseq would pertain to sl_event.ev_seqno, but no entires
>>  exist in my logs where log_actionseq=ev_seqno, so I am confused.
>>
>>  does anybody know where to find this information?
>>
>>  (I am not looking for an algorithm to convert log_cmdtype and log_cmddata into
>>  SQL statements, I am looking for the location of the specific information
>>  pertaining to a SYNC event.)
>
> Definitely nontrivial.
>
> The association is that a SYNC consists of all the transactions with
> XIDs that are:
>   a) After the previous SYNC
>   b) Before the current SYNC
>   c) Not outstanding in the current SYNC
>   d) Possibly from before the previous SYNC, but committed after that
>      SYNC.
>
> [Possibly that's not a perfect explanation...  The query that computes
> the relationship between a SYNC and the sl_log_* entries for it is
> pretty gory!]

Let me try.

On any given node there is a row in sl_setsync for each set, the node is 
subscribed to. sl_setsync.ssy_snapshot is the txid_snapshot of the last 
processed sync. Let that be snapshot1.

There is also a txid_snapshot coming with the next SYNC event which is 
sl_event.ev_snapshot. Let that be snapshot2.

These are serializable transaction snapshots generated by the SYNC 
events on the origin. The log rows contain a log_txid field that tells 
which transaction on the origin had created them. In Postgres a 
serializable transaction can only see rows that were committed before 
the transaction created its snapshot.

To get all the sl_log_1 and sl_log_2 rows, that need to be processed to 
get the subscriber from snapshot1 (where it is now) to snapshot2, we 
have to select all the log rows who's log_txid was NOT visible by 
snapshot1 and was visible by snapshot2. This way Slony subscribers 
always leap from one serializable snapshot (of the origin) to another. 
In other words, after processing this part of the log, the subscriber 
database looks exactly like the origin looked to the serializable 
transaction that created snapshot2. Had that transaction been a pg_dump 
instead, the dump would contain exactly what the subscriber looks like 
right now.

The actual query that implements this logic looks like this:

> select log_origin, log_txid, log_tableid, log_actionseq,
> 		log_cmdtype, octet_length(log_cmddata),
> 		case when octet_length(log_cmddata) <= 8192 then log_cmddata else null end
> 	from "_T1".sl_log_1
> 	where log_origin = 11
> 	and log_tableid in (1,2,3,4)
> 	and log_txid >= '6594216' and log_txid < '6594282'
> 	and "pg_catalog".txid_visible_in_snapshot(log_txid, '6594282:6594282:')
> union all
> select log_origin, log_txid, log_tableid, log_actionseq,
> 		log_cmdtype, octet_length(log_cmddata),
> 		case when octet_length(log_cmddata) <= 8192 then log_cmddata else null end
> 	from "_T1".sl_log_1
> 	where log_origin = 11
> 	and log_tableid in (1,2,3,4)
> 	and log_txid in (
> 		select * from "pg_catalog".txid_snapshot_xip('6594216:6594216:')
> 		except
> 		select * from "pg_catalog".txid_snapshot_xip('6594282:6594282:')
> 	)
>
> order by log_actionseq

And there are two more UNION ALL's for the other sl_log table while a 
log switch is in progress. To make matters even worse, the snapshot 
information in this query comes partially from the event on the data 
provider and partially from the sl_setsync table on the subscriber. 
Since the event corresponding to the sl_setsync entry has been confirmed 
already, it might not exist on the data provider any more. So you 
definitely need to query both databases to get all the information 
needed to assemble that query.

Anyhow, this is definitely not a query to quickly type into psql.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin