Re: [BUGS] BUG #14529: Missing non pk data for "before image" inlogical decoding - Mailing list pgsql-bugs

Hi,

On 06/02/17 17:12, philippe.beaudoin@dalibo.com wrote:

The following bug has been logged on the website:

Bug reference:      14529
Logged by:          Philippe BEAUDOIN
Email address:      philippe.beaudoin@dalibo.com
PostgreSQL version: 9.6.1
Operating system:   Linux
Description:        

I am currently studying the logical decoding mechanism as an alternative to
log triggers for the E-Maj extension.
Using pg_recvlogical and pgbench, I have been able to reproduce an issue
that I met.

[...snip...]

BEGIN 5454770
table public.pgbench_accounts: UPDATE: old-key: aid[integer]:24469
bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                  ' new-tuple:
aid[integer]:24469 bid[integer]:1 abalance[integer]:-3373
filler[character]:'                                                                                  '
table public.pgbench_tellers: UPDATE: old-key: tid[integer]:2 bid[integer]:1
tbalance[integer]:0 new-tuple: tid[integer]:2 bid[integer]:1
tbalance[integer]:-3373 filler[character]:null
table public.pgbench_branches: UPDATE: old-key: bid[integer]:1
bbalance[integer]:0 new-tuple: bid[integer]:1 bbalance[integer]:-3373
filler[character]:null
table public.pgbench_history: INSERT: tid[integer]:2 bid[integer]:1
aid[integer]:24469 delta[integer]:-3373 mtime[timestamp without time
zone]:'2017-01-21 09:51:29.728733' filler[character]:null
COMMIT 5454770
BEGIN 5454771
table public.pgbench_accounts: UPDATE: old-key: aid[integer]:4016
bid[integer]:1 abalance[integer]:0 filler[character]:'                                                                                  ' new-tuple:
aid[integer]:4016 bid[integer]:1 abalance[integer]:3308 filler[character]:'                                                                                '
table public.pgbench_tellers: UPDATE: old-key: tid[integer]:2 bid[integer]:1
tbalance[integer]:-3373 new-tuple: tid[integer]:2 bid[integer]:1
tbalance[integer]:-65 filler[character]:null
table public.pgbench_branches: UPDATE: old-key: bid[integer]:1
bbalance[integer]:-3373 new-tuple: bid[integer]:1 bbalance[integer]:-65
filler[character]:null
table public.pgbench_history: INSERT: tid[integer]:2 bid[integer]:1
aid[integer]:4016 delta[integer]:3308 mtime[timestamp without time
zone]:'2017-01-21 09:51:29.74555' filler[character]:null
COMMIT 5454771
#-------------------------------------------------------------------------------

The incomplete UPDATEs concern pgbench_tellers and pgbench_branches
tables.

Seems to me like what you see here is not bug but just behavior of
test_decoding plugin which skips NULL values from old-key printing - see
the tuple_to_stringinfo() function and calls to it from pg_decode_change().

Thank a lot, Petr, for your quick answer.

Effectively, the code you have pointed out is clear. And looking more closely at the pgbench scenario and table structure confirms your explanation.

The documentation doesn't explain this detail level. But as this output plugin is only an example, I understand the documentation remains light.

Sorry for the trouble.
Philippe.