ERROR: cannot start subtransactions during a parallel operation
Hello, On a pG10.4 instance, my query ( a simple select from a view) throw this error: ERROR: cannot start subtransactions during a parallel operation CONTEXT: PL/pgSQL function check_validity(ltree[]) line 4 during statement block entry But prefixing this query by "set max_parallel_workers_gather=0" make it works. When I take off the column that is checked by a function, no need to add set max_parallel_workers_gather=0 . How could I continue to use the default max_parallel_workers_gather (2). Thank you
Re: ERROR: cannot start subtransactions during a parallel operation
Hello, how could I relax the subtransaction restriction, I used the Parallel Unsafe option, but still have the same issue. Rgds. Le ven. 29 juin 2018 20:47, Andres Freund a écrit : > Hi, > > On 2018-06-29 20:37:23 +0200, Tomas Vondra wrote: > > My guess is that it's a PL/pgSQL function with an EXCEPTION block, and > > there's no easy way to "fix" that. > > Obviously not going to immediately help the OP, but I do think we should > be able to relax the subtransaction restriction around parallelism > without too much work. Can't allow xids to be assigned, but that's > probably ok for a lot of exception handling cases. > > Greetings, > > Andres Freund >
Segfault logical replication PG 10.4
We discovered our pg_wal partition was full few days after setting our first logical publication on a PG 10.4 instance. Then, we can not synchronise our slave to the master, it triggers a segfault on the slave. We had to drop manually the subscription on slave and the slot on master. Then, we wanted to find the cause of this bug, stop connection between master and slave , after 30 minutes, the slave had a segfault and could not synchronise. Why does the slave can not synchronise without a complete creation subscription after dropping the slot? How to manage the replication, knowing we use cloud vm and issue network latency. Here the details of conf and error logs: Conf on master: max_replication_slots = 10 max_sync_workers_per_subscription = 2 wal_receiver_timeout: 60s wal_keep_segments : 1000 wal_receiver_status_interval :10 wal_retrieve_retry_interval :5 s max_logical_replication_workers :4 Conf on slave same except wal_keep_segments=0 Error log on slave: LOG: logical replication apply worker for subscription « " has started DEBUG: connecting to publisher using connection string "postgresql://USER@IP" LOG: worker process: logical replication worker for subscription 132253 (PID 25359) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly co rrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. LOG: all server processes terminated; reinitializing DEBUG: unregistering background worker "logical replication worker for subscription 132253" LOG: database system was interrupted; last known up at 2018-07-11 21:50:56 UTC DEBUG: checkpoint record is at 0/7DBFEF10 DEBUG: redo record is at 0/7DBFEF10; shutdown TRUE DEBUG: next transaction ID: 0:93714; next OID: 140237 DEBUG: next MultiXactId: 1; next MultiXactOffset: 0 DEBUG: oldest unfrozen transaction ID: 548, in database 1 DEBUG: oldest MultiXactId: 1, in database 1 DEBUG: commit timestamp Xid oldest/newest: 0/0 DEBUG: transaction ID wrap limit is 2147484195, limited by database with OID 1 DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 1 DEBUG: starting up replication slots LOG: recovered replication state of node 2 to 0/0 LOG: recovered replication state of node 3 to 0/0 LOG: recovered replication state of node 4 to 0/0 LOG: recovered replication state of node 5 to 56A5/29ACA918 LOG: database system was not properly shut down; automatic recovery in progress THANK YOU
Re: ERROR: cannot start subtransactions during a parallel operation
Hi all, Sorry for the late reply. I could handle the pb by transforming my function check ( with an exception ) by a simple sql immutable function. Function before CREATE OR REPLACE FUNCTION test_id(ltree[]) RETURNS boolean AS $BODY$ DECLARE id public.ltree; BEGIN FOR id IN SELECT unnest($1) LOOP PERFORMl id::id; IF () THEN RETURN FALSE; END IF; END LOOP; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; $BODY$ LANGUAGE plpgsql VOLATILE; AFTER CREATE OR REPLACE FUNCTION test_Id(ltree[]) RETURNS boolean AS $BODY$ SELECT NOT EXISTS ( SELECT 1 FROM unnest ($1) id WHERE id<>id::id ) $BODY$ LANGUAGE sql IMMUTABLE STRICT; > Le 1 juil. 2018 à 11:02, Mai Peng a écrit : > > Hello, how could I relax the subtransaction restriction, I used the Parallel > Unsafe option, but still have the same issue. > Rgds. > > Le ven. 29 juin 2018 20:47, Andres Freund <mailto:and...@anarazel.de>> a écrit : > Hi, > > On 2018-06-29 20:37:23 +0200, Tomas Vondra wrote: > > My guess is that it's a PL/pgSQL function with an EXCEPTION block, and > > there's no easy way to "fix" that. > > Obviously not going to immediately help the OP, but I do think we should > be able to relax the subtransaction restriction around parallelism > without too much work. Can't allow xids to be assigned, but that's > probably ok for a lot of exception handling cases. > > Greetings, > > Andres Freund
Re: Segfault logical replication PG 10.4
0056239faeac74 in main (argc=3, argv=0x5623a1b49ef0) at ./build/../src/backend/main/main.c:228 228 ./build/../src/backend/main/main.c: No such file or directory.``` BR > Le 14 juil. 2018 à 13:15, Peter Eisentraut > a écrit : > > On 12.07.18 00:56, Mai Peng wrote: >> We discovered our pg_wal partition was full few days after setting our >> first logical publication on a PG 10.4 instance. >> Then, we can not synchronise our slave to the master, it triggers a >> segfault on the slave. We had to drop manually the subscription on slave >> and the slot on master. >> Then, we wanted to find the cause of this bug, stop connection between >> master and slave , after 30 minutes, the slave had a segfault and could >> not synchronise. >> Why does the slave can not synchronise without a complete creation >> subscription after dropping the slot? > > Can you get a backtrace from the core dump produced by the segfault? > > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Segfault logical replication PG 10.4
Hello , Some new input: On slave, all domains ( with checks) have been replaced by a simple type. No crash on slave since this bypass. Is there something to fix in the ActiveSnapshot code ? BR > Le 18 juil. 2018 à 17:03, Tom Lane a écrit : > > Mai Peng writes: >> Here the backtrace > > Hmm .. so this can be summarized as "logical replication workers should > provide an ActiveSnapshot in case the user functions they call want one". > Makes me wonder how much other transactional infrastructure is needed > but not present. > > regards, tom lane
Re: Segfault logical replication PG 10.4
Thank you Alvaro :)
> Le 30 juil. 2018 à 22:33, Alvaro Herrera a écrit :
>
> On 2018-Jul-28, Alvaro Herrera wrote:
>
>> Aha, I see, thanks. Here's a complete fix with included testcase. In
>> an unpatched assert-enabled build, this crashes this
>>
>> TRAP: FailedAssertion("!(ActiveSnapshotSet())", File:
>> "/pgsql/source/REL_10_STABLE/src/backend/tcop/postgres.c", Line: 788)
>>
>> Will push on Monday.
>
> Pushed after changing the constraint in the test case to be less silly.
> Thanks for the report and diagnosis.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
