Check what has been done for a uncommitted prepared transaction
Hi: I want to know what happens been done for an uncommitted prepared transaction with pg_waldump, however I can't find it. demo=# begin; BEGIN demo=*# select txid_current(); txid_current -- 608 (1 row) demo=*# prepare transaction 's'; PREPARE TRANSACTION demo=# insert into mm select generate_series(1, 1000); INSERT 0 1000 demo=# === pg_wal> ~/postgres/bin/pg_waldump 0001008F | grep 'tx: 608' rmgr: Transaction len (rec/tot):138/ 138, tx:608, lsn: 0/8F68C020, prev 0/8F68BFD0, desc: PREPARE gid s: 2020-05-14 15:00:33.212997 CST I can get the log for "prepared command" only, but nothing was found for the insert statement. what should I do? My version is 9.4. Thanks
Re: Check what has been done for a uncommitted prepared transaction
On Thu, May 14, 2020 at 3:38 PM Andy Fan wrote: > I can get the log for "prepared command" only, but nothing was found for > the insert > statement. what should I do? > > My version is 9.4. > > Sorry, my production version is 9.4 and my demo above is v12. I tried in 9.4, I still have troubles to get the logs. Thanks
Re: Check what has been done for a uncommitted prepared transaction
On Thu, May 14, 2020 at 4:05 PM Michael Paquier wrote: > On Thu, May 14, 2020 at 03:38:24PM +0800, Andy Fan wrote: > > I want to know what happens been done for an uncommitted prepared > > transaction with pg_waldump, however I can't find it. > > > > demo=*# prepare transaction 's'; > > PREPARE TRANSACTION > > demo=# insert into mm select generate_series(1, 1000); > > INSERT 0 1000 > > > > I can get the log for "prepared command" only, but nothing was found for > > the insert statement. what should I do? > > Because in your previous sequence you inserted the data after > preparing the transaction and they are part of a completely different > transaction, no? > Thanks, actually I don't know how to use prepared transaction and how it works. I care about this because there is a long prepared transaction exists in our customer, and we want to know what this transaction has done（like any data it changed). All the things I know is the data comes from pg_prepared_xact, but it doesn't help a lot. Best Regards Andy Fan
Re: Check what has been done for a uncommitted prepared transaction
On Thu, May 14, 2020 at 9:33 PM Laurenz Albe wrote: > On Thu, 2020-05-14 at 16:26 +0800, Andy Fan wrote: > > Thanks, actually I don't know how to use prepared transaction and how > it works. > > I care about this because there is a long prepared transaction exists in > our customer, > > and we want to know what this transaction has done（like any data it > changed). > > All the things I know is the data comes from pg_prepared_xact, but it > doesn't help a lot. > > Idf you have the transaction ID from "pg_prepared_xact", you could check > what locks are held: > > SELECT * FROM pg_locks WHERE transactionid = ...; > > Than might give you a clue. > > Thanks a lot. this transaction only lock a transactionid lock, so I assume there is nothing is done in this transaction. locktype| database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ---+--+--+--+---++---+-+---+--++--+---+-+-- transactionid | | | | || 1181845808 | | | | -1/1181845808 | | ExclusiveLock | t | f (1 row) Best Regards Andy Fan
PG Crashed at CheckExprStillValid with state == NULL (PG 11.2)
Hi: The call stack is below: #0 0x7fdecbe924eb in raise () from /lib64/libpthread.so.0 #1 0x00886888 in . () #2 #3 0x006cb364 in ?? () #4 0x006cb2a4 in CheckExprStillValid () --> *state == NULL* #5 0x006c9563 in ExecInterpExprStillValid () #6 0x006f2601 in ExecIndexEvalRuntimeKeys () #7 0x006ea8b5 in ExecReScanBitmapIndexScan () #8 0x006c20fa in ExecReScan () #9 0x006e8c18 in ExecReScanBitmapAnd () #10 0x006c21ca in ExecReScan () #11 0x006c2232 in ExecReScan () #12 0x006fed4f in ExecReScanNestLoop () #13 0x006c212e in ExecReScan () #14 0x006fea5b in ?? () #15 0x006dab67 in ?? () #16 0x006fea7d in ?? () #17 0x006dab67 in ?? () #18 0x006e32c5 in ?? () #19 0x006dab67 in ?? () #20 0x00701ba0 in ?? () #21 0x006dab67 in ?? () #22 0x00702161 in ?? () #23 0x006dab67 in ?? () #24 0x006d22ed in standard_ExecutorRun () #25 0x7fdec4140537 in ?? () from /u01/..._stat_plans.so #26 0x7fdec3b21670 in ?? () from /u01/...l/pg_stat_statements.so #27 0x7fdec3719850 in ?? () from /u01/.../auto_explain.so #28 0x0088b3ef in ?? () #29 0x0088af73 in PortalRun () #30 0x00885cf1 in PostgresMain () #31 0x007d9598 in ?? () #32 0x007d8aa3 in ?? () #33 0x007d5579 in PostmasterMain () #34 0x0072cefc in main () In my environment, if I run the query in psql, everything is good. However if I run it in some java code(including other sql), it would crash for 95% cases. I'm sorry that I can't provide a runnable test case, I'm posting here just to see if anyone has run into the similar case before or can provide some hints on this. Thanks! -- Best Regards Andy Fan
create unique constraint on jsonb->filed during create table
The following way works with 2 commands:
zhifan=# create table t1 (a jsonb);
CREATE TABLE
zhifan=# create unique index t1_a_name on t1 ((a->'name'));
CREATE INDEX
but know I want to merge them into 1 command, is it possible?
zhifan=# create table t2 (a jsonb, constraint uk_t2_a_name
unique((a->'name')));
ERROR: syntax error at or near "("
LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name'...
Re: create unique constraint on jsonb->filed during create table
Got it, thank you!
On Thu, Feb 28, 2019 at 12:48 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wednesday, February 27, 2019, Andy Fan
> wrote:
>
>>
>> The following way works with 2 commands:
>>
>> zhifan=# create table t1 (a jsonb);
>> CREATE TABLE
>> zhifan=# create unique index t1_a_name on t1 ((a->'name'));
>> CREATE INDEX
>>
>> but know I want to merge them into 1 command, is it possible?
>>
>> zhifan=# create table t2 (a jsonb, constraint uk_t2_a_name
>> unique((a->'name')));
>> ERROR: syntax error at or near "("
>> LINE 1: ...table t2 (a jsonb, constraint uk_t2_a_name unique((a->'name'...
>>
>
> Not according to the documentation. Unique table constraints can only
> reference columns in the table as a whole. An expression index must be
> created separately from the table to which it is attached.
>
> Or add a trigger to the table, populate an actual second column (making it
> unique), and add a table check constraint that that column and the
> expression are equal. I suspect you’ll be happier having the PK as actual
> column data anyway.
>
> David J.
>
>
