Re: Changeset Extraction v7.5

On 7 February 2014 21:04, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On February 7, 2014 9:58:14 PM CET, Thom Brown <thom(at)linux(dot)com> wrote:
>>On 7 February 2014 19:35, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>> 0004: wal_decoding: Documentation for replication slots and changeset
>>extraction
>>
>>The usage of pg_create_decoding_replication_slot does show the "(1
>>row)" line.
>>
>>The output of "SELECT * FROM pg_replication_slots;" is out-of-date.
>>
>>There appears to be a column named "slot_name" and "slottype". Could
>>one of these have or not have the underscore for consistency?
>>
>>The example also shows output from pg_decoding_slot_get_changes after
>>inserting 2 rows, but when I run the same example, there are no rows
>>returned:
>>
>># BEGIN;
>>BEGIN
>>
>>*# INSERT INTO data(data) VALUES('1');
>>INSERT 0 1
>>
>>*# INSERT INTO data(data) VALUES('1');
>>INSERT 0 1
>>
>>*# COMMIT;
>>COMMIT
>>
>># SELECT * FROM pg_decoding_slot_get_changes('regression_slot', 'now',
>>'include-xids', '0');
>> location | xid | data
>>----------+-----+------
>>(0 rows)
>>
>>
>>I inserted a single row outside of a transaction, and got the expected
>>output. Then I ran the above again, and got an output, but an
>>unexpected one:
>>
>>SELECT * FROM pg_decoding_slot_get_changes('regression_slot', 'now',
>>'include-xids', '0');
>> location | xid | data
>>-----------+-----+-----------------------------------------------
>> 0/16C8B90 | 769 | BEGIN
>> 0/16C8D50 | 769 | table "data": INSERT: id[int4]:3 data[text]:1
>> 0/16C8D50 | 769 | COMMIT
>>(3 rows)
>>
>>And running the transaction with inserts again, there's no output from
>>that same function command. I always get an output from isolated
>>INSERT statements. I should point out that in my .psqlrc file I have
>>"\set ON_ERROR_ROLLBACK". If I use psql -X, this symptom no longer
>>occurs, so I think the automatic savepoints are interfering, and the
>>effect appears to be inconsistent.
>
> More complete answer later, but any chance you're using synchronous commit = off?

No:

# show synchronous_commit ;
synchronous_commit
--------------------
on
(1 row)

My custom config is:

wal_level = 'logical'
max_replication_slots = '1'
shared_buffers = 3900MB
temp_buffers = 16MB
work_mem = 16MB
maintenance_work_mem = 256MB
checkpoint_segments = 32
random_page_cost = 1.1
effective_cache_size = 12GB
logging_collector = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h '

--
Thom