I tried a couple toy examples with various combinations of use styles.
Three with "automatic" reading from sequences:
create table test(i serial);
create table test(i int GENERATED BY DEFAULT AS IDENTITY);
create table test(i int default nextval('s1'));
...where s1 has some non-default parameters:
CREATE SEQUENCE s1 START 100 MAXVALUE 100 INCREMENT BY -1;
...and then two with explicit use of s1, one inserting the 'nextval' into a table with no default, and one with no table at all, just selecting from the sequence.
The last two seem to work similarly to the first three, so it seems like FOR ALL TABLES adds all sequences as well. Is that expected? The documentation for CREATE PUBLICATION mentions sequence options, but doesn't really say how these options should be used.
Here's the script:
# alter system set wal_level='logical';
# restart
# port 7777 is subscriber
echo
echo "PUB:"
psql -c "drop sequence if exists s1;"
psql -c "drop publication if exists pub1;"
echo
echo "SUB:"
psql -p 7777 -c "drop sequence if exists s1;"
psql -p 7777 -c "drop subscription if exists sub1 ;"
echo
echo "PUB:"
psql -c "CREATE SEQUENCE s1 START 100 MAXVALUE 100 INCREMENT BY -1;"
psql -c "CREATE PUBLICATION pub1 FOR ALL TABLES;"
echo
echo "SUB:"
psql -p 7777 -c "CREATE SEQUENCE s1 START 100 MAXVALUE 100 INCREMENT BY -1;"
psql -p 7777 -c "CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost dbname=john application_name=sub1 port=5432' PUBLICATION pub1;"
echo
echo "PUB:"
psql -c "select nextval('s1');"
psql -c "select nextval('s1');"
psql -c "select * from s1;"
sleep 1
echo
echo "SUB:"
psql -p 7777 -c "select * from s1;"
psql -p 7777 -c "drop subscription sub1 ;"
psql -p 7777 -c "select nextval('s1');"
psql -p 7777 -c "select * from s1;"
...with the last two queries returning
nextval
---------
67
(1 row)
last_value | log_cnt | is_called
------------+---------+-----------
67 | 32 | t
So, I interpret that the decrement by 32 got logged here.
Also, running
CREATE PUBLICATION pub2 FOR ALL SEQUENCES WITH (publish = 'insert, update, delete, truncate, sequence');
...reports success, but do non-default values of "publish = ..." have an effect (or should they), or are these just ignored? It seems like these cases shouldn't be treated orthogonally.
--
John Naylor
EDB:
http://www.enterprisedb.com
