Open def- opened 2 years ago
sushantrmishra
commented
2 years ago Creating the slot using pg_create_physical_replication_slot is required for Streaming/replicating changes from a physical slot in PostgreSQL. In YugabyteDB, replication does not use streaming/replication protocol used in PostgreSQL, hence this function is not used and also not supported.
Though we should return an more relevant error message in YugabyteDB when pg_create_physical_replication_slot is used.
def-
commented
1 year ago Similar situation with pg_catalog.pg_replication_origin_xact_reset(), might also be expected:
yugabyte=# \x
Expanded display is on.
yugabyte=# SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------
pid | 628719
age |
usename |
query |
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------
pid | 663159
age | 02:53:51.284042
usename | yugabyte
query | select +
| 34 as c0, +
| subq_0.c0 as c1, +
| subq_0.c4 as c2, +
| subq_0.c4 as c3, +
| pg_catalog.pg_replication_origin_xact_reset() as c4, +
| +
| pg_catalog.var_samp( +
| cast((select feature_col from public.feature_tab_real limit 1 offset 1) +
| as float4)) over (partition by subq_0.c2,subq_0.c5 order by subq_0.c6) as c5, +
| (select a from public.pp_enumpart limit 1 offset 6) +
| as c6, +
| subq_0.c6 as c7, +
| subq_0.c5 as c8, +
| subq_0.c1 as c9, +
| dbms_pipe.create_pipe( +
| cast(cast(nullif( +
| pg_catalog.max( +
| cast(utl_file.tmpdir() as text)) over (partition by subq_0.c6,subq_0.c6 order by subq_0.c1,subq_0.c0),+
| oracle.sessiontimezone()) as text) as text)) as c10, +
| (select pg_catalog.max(feature_col) from public.feature_tab_time) +
| as c11 +
| from +
| (select +
| ref_0.b as c0, +
| ref_0.b as c1, +
| ref_0.b as c2, +
| ref_0.a as c3, +
| cast(coalesce(ref_0.b, +
| ref_0.b) as int4) as c4, +
| ref_0.b as c5, +
| ref_0.a as c6 +
| from +
|
yugabyte=# select *
from pg_stat_activity
where datname = 'yugabyte';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------------------
datid | 13288
datname | yugabyte
pid | 663159
usesysid | 13287
usename | yugabyte
application_name | sqlsmith::dut
client_addr | 127.0.0.1
client_hostname |
client_port | 42266
backend_start | 2022-12-28 20:26:41.171935+00
xact_start | 2022-12-28 20:45:28.677106+00
query_start | 2022-12-28 20:45:28.677177+00
state_change | 2022-12-28 20:45:28.67742+00
wait_event_type | LWLock
wait_event | extension
state | active
backend_xid |
backend_xmin | 28
query | select +
| 34 as c0, +
| subq_0.c0 as c1, +
| subq_0.c4 as c2, +
| subq_0.c4 as c3, +
| pg_catalog.pg_replication_origin_xact_reset() as c4, +
| +
| pg_catalog.var_samp( +
| cast((select feature_col from public.feature_tab_real limit 1 offset 1) +
| as float4)) over (partition by subq_0.c2,subq_0.c5 order by subq_0.c6) as c5, +
| (select a from public.pp_enumpart limit 1 offset 6) +
| as c6, +
| subq_0.c6 as c7, +
| subq_0.c5 as c8, +
| subq_0.c1 as c9, +
| dbms_pipe.create_pipe( +
| cast(cast(nullif( +
| pg_catalog.max( +
| cast(utl_file.tmpdir() as text)) over (partition by subq_0.c6,subq_0.c6 order by subq_0.c1,subq_0.c0),+
| oracle.sessiontimezone()) as text) as text)) as c10, +
| (select pg_catalog.max(feature_col) from public.feature_tab_time) +
| as c11 +
| from +
| (select +
| ref_0.b as c0, +
| ref_0.b as c1, +
| ref_0.b as c2, +
| ref_0.a as c3, +
| cast(coalesce(ref_0.b, +
| ref_0.b) as int4) as c4, +
| ref_0.b as c5, +
| ref_0.a as c6 +
| from +
|
backend_type | client backend
-[ RECORD 2 ]----+-----------------------------------------------------------------------------------------------------------------
datid | 13288
datname | yugabyte
pid | 673357
usesysid | 13287
usename | yugabyte
application_name | ysqlsh
client_addr | 127.0.0.1
client_hostname |
client_port | 42320
backend_start | 2022-12-28 23:37:47.377639+00
xact_start | 2022-12-28 23:39:22.896183+00
query_start | 2022-12-28 23:39:22.896183+00
state_change | 2022-12-28 23:39:22.896257+00
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 28
query | select * +
| from pg_stat_activity +
| where datname = 'yugabyte';
backend_type | client backendThe processes stay around on a yb-ctl stop:
[deen@devp yugabyte-db]$ ps aux|grep postgres
deen 628709 0.1 0.2 152892 47284 ? Ss Dec28 1:20 postgres: logger
deen 663159 0.4 0.7 448152 116352 ? Ssl Dec28 3:01 postgres: yugabyte yugabyte 127.0.0.1(42266) SELECT
deen 673500 0.0 0.4 366156 79008 ? Ssl Dec28 0:06 postgres: yugabyte yugabyte 127.0.0.1(42324) SELECT
deen 673710 0.0 0.4 371276 73728 ? Ssl Dec28 0:05 postgres: yugabyte yugabyte 127.0.0.1(42332) INSERTThis even hung yb-ctl destroy:
Destroying cluster.
2022-12-28 23:45:08,922 INFO: Stopping Server master-1 PID=628089
2022-12-28 23:45:08,922 INFO: Waiting for Server master-1 PID=628089 to stop...
2022-12-28 23:45:09,437 INFO: Stopping Server master-2 PID=628092
2022-12-28 23:45:09,437 INFO: Waiting for Server master-2 PID=628092 to stop...
2022-12-28 23:45:09,953 INFO: Stopping Server master-3 PID=628095
2022-12-28 23:45:09,953 INFO: Waiting for Server master-3 PID=628095 to stop...
2022-12-28 23:45:10,468 INFO: Stopping Server tserver-1 PID=628098
2022-12-28 23:45:10,469 INFO: Waiting for Server tserver-1 PID=628098 to stop...
2022-12-28 23:45:10,969 INFO: Stopping Postmaster for tserver-1 PID=628638
2022-12-28 23:45:10,969 INFO: Waiting for Postmaster for tserver-1 PID=628638 to stop...
2022-12-28 23:45:11,970 INFO: Waiting for Postmaster for tserver-1 PID=628638 to stop...
2022-12-28 23:45:12,971 INFO: Waiting for Postmaster for tserver-1 PID=628638 to stop...It only reacts to a kill -9
[deen@devp ~]$ ps aux|grep 628638
deen 628638 0.0 0.4 278488 67084 ? S Dec28 0:01 /home/deen/code/yugabyte-db/build/release-clang15-linuxbrew-dynamic/postgres/bin/postgres -D /home/deen/yugabyte-data/node-1/disk-1/pg_data -p 5433 -h 127.0.0.1 -k /tmp/.yb.127.0.0.1:5433 -c unix_socket_permissions=0700 -c logging_collector=on -c log_directory=/home/deen/yugabyte-data/node-1/disk-1/yb-data/tserver/logs -c yb_pg_metrics.node_name=devp:9000 -c yb_pg_metrics.port=13000 -c config_file=/home/deen/yugabyte-data/node-1/disk-1/pg_data/ysql_pg.conf -c hba_file=/home/deen/yugabyte-data/node-1/disk-1/pg_data/ysql_hba.conf
Jira Link: DB-858
Description
After a few hours SQLsmith gets stuck in ReplicationSlotAllocationLock, I can reproduce it with the last run query:
The query seems to stay around:
Cancelling with Ctrl-C has no effect.