Обсуждение: After dropping the rule - Not able to insert / server crash (one timeONLY)

Hi,

While testing something , I found that even after rule has dropped  not 
able to insert data  and in an another scenario , there is a Crash/

Please refer this scenario's -

1) Rows not inserted after dropping the RULE

postgres=# create table e(n int);
CREATE TABLE
postgres=# create rule e1 as on insert to e do instead nothing;
CREATE RULE
postgres=# create function e11(n int) returns int as $$ begin insert 
into e values(10); return 1; end; $$ language 'plpgsql';
CREATE FUNCTION
postgres=# select e11(1);
  e11
-----
    1
(1 row)

postgres=# select e11(1);
  e11
-----
    1
(1 row)

postgres=# select * from e;  => Expected , due to  the RULE enforced
  n
---
(0 rows)


postgres=# drop rule e1 on e;  ==>Drop the rule
DROP RULE

postgres=# select e11(1);  =>again try to insert into table
  e11
-----
    1
(1 row)

postgres=# select * from e;  =>This time , value should be inserted but 
still showing 0 row.
  n
---
(0 rows)



2)Server crash (one time)

postgres=# create table en(n int);
CREATE TABLE
postgres=# create function en1(n int) returns int as $$ begin insert 
into en values(10); return 1; end; $$ language 'plpgsql';
CREATE FUNCTION
postgres=#
postgres=# select en1(1);
  en1
-----
    1
(1 row)

postgres=# select * from en;
  n
----
  10
(1 row)

postgres=# create rule en11 as on insert to en do instead nothing;
CREATE RULE
postgres=# select en1(1);
ostgres=# select en1(1);
TRAP: FailedAssertion("!(!stmt->mod_stmt)", File: "pl_exec.c", Line: 3721)
server closed the connection unexpectedly
     This probably means the server terminated abnormally
     before or while processing the request.
The connection to the server was lost. Attempting reset: 2017-12-11 
10:05:57.847 GMT [18493] LOG:  server process (PID 18604) was terminated 
by signal 6: Aborted
2017-12-11 10:05:57.847 GMT [18493] DETAIL:  Failed process was running: 
select en1(1);
2017-12-11 10:05:57.847 GMT [18493] LOG:  terminating any other active 
server processes
2017-12-11 10:05:57.847 GMT [18498] WARNING:  terminating connection 
because of crash of another server process
2017-12-11 10:05:57.847 GMT [18498] DETAIL:  The postmaster has 
commanded this server process to roll back the current transaction and 
exit, because another server process exited abnormally and possibly 
corrupted shared memory.
2017-12-11 10:05:57.847 GMT [18498] HINT:  In a moment you should be 
able to reconnect to the database and repeat your command.
Failed.
!> 2017-12-11 10:05:57.849 GMT [18493] LOG:  all server processes 
terminated; reinitializing


again try to connect and fire this same query -

postgres=# select en1(1);  =>This time no crash but again rows not inserted
  en1
-----
    1
(1 row)

This is not a regression, i am able to see such behavior in v9.6 as well.

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

Can someone review this thread and determine if this patch is needed? 
Thanks.

---------------------------------------------------------------------------

On Fri, Dec 22, 2017 at 04:58:47PM +0530, Dilip Kumar wrote:
> On Mon, Dec 11, 2017 at 4:33 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> 
>     On Mon, Dec 11, 2017 at 3:54 PM, tushar <tushar.ahuja@enterprisedb.com>
>     wrote:
> 
>         Hi,
> 
>         While testing something , I found that even after rule has dropped  not
>         able to insert data  and in an another scenario , there is a Crash/
> 
>         Please refer this scenario's -
> 
>         1) Rows not inserted after dropping the RULE
> 
>         postgres=# create table e(n int);
>         CREATE TABLE
>         postgres=# create rule e1 as on insert to e do instead nothing;
>         CREATE RULE
>         postgres=# create function e11(n int) returns int as $$ begin insert
>         into e values(10); return 1; end; $$ language 'plpgsql';
>         CREATE FUNCTION
>         postgres=# select e11(1);
>          e11
>         -----
>            1
>         (1 row)
> 
>         postgres=# select e11(1);
>          e11
>         -----
>            1
>         (1 row)
> 
>         postgres=# select * from e;  => Expected , due to  the RULE enforced
>          n
>         ---
>         (0 rows)
> 
> 
>         postgres=# drop rule e1 on e;  ==>Drop the rule
>         DROP RULE
> 
>         postgres=# select e11(1);  =>again try to insert into table
>          e11
>         -----
>            1
>         (1 row)
> 
>         postgres=# select * from e;  =>This time , value should be inserted but
>         still showing 0 row.
>          n
>         ---
>         (0 rows)
> 
> 
> I think this is becuase we are not invalidating the plan cache if rule is
> dropped.  You can reproduce same with prepared statements.
>  
> 
>         2)Server crash (one time)
> 
>         postgres=# create table en(n int);
>         CREATE TABLE
>         postgres=# create function en1(n int) returns int as $$ begin insert
>         into en values(10); return 1; end; $$ language 'plpgsql';
>         CREATE FUNCTION
>         postgres=#
>         postgres=# select en1(1);
>          en1
>         -----
>            1
>         (1 row)
> 
>         postgres=# select * from en;
>          n
>         ----
>          10
>         (1 row)
> 
>         postgres=# create rule en11 as on insert to en do instead nothing;
>         CREATE RULE
>         postgres=# select en1(1);
>         ostgres=# select en1(1);
>         TRAP: FailedAssertion("!(!stmt->mod_stmt)", File: "pl_exec.c", Line:
>         3721)
>         server closed the connection unexpectedly
>             This probably means the server terminated abnormally
>             before or while processing the request.
> 
> 
>     I have looked into this crash, seems like below assert in exec_stmt_execsql
>     is not correct
> 
>     case SPI_OK_REWRITTEN:
> 
>     Assert(!stmt->mod_stmt);
>    
>     In this issue first time execution of select en1(1); statement, stmt->
>     mod_stmt is set to true for the insert query inside the function. Then
>     before next execution we have created the rule "create rule en11 as on
>     insert to en do instead nothing;".  Because of this nothing will be
>     executed and output will be SPI_OK_REWRITTEN.  But we are asserting that 
>     stmt->mod_stmt should be false (which were set to true in first execution).
> 
> 
> IMHO if the query is rewritten, then this assert is not valid. I have attached
> a patch which removes this assert.
> 
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com

> diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
> index dd575e7..9b85df8 100644
> --- a/src/pl/plpgsql/src/pl_exec.c
> +++ b/src/pl/plpgsql/src/pl_exec.c
> @@ -3727,8 +3727,6 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
>              break;
>  
>          case SPI_OK_REWRITTEN:
> -            Assert(!stmt->mod_stmt);
> -
>              /*
>               * The command was rewritten into another kind of command. It's
>               * not clear what FOUND would mean in that case (and SPI doesn't


-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +