After dropping the rule - Not able to insert / server crash (one time ONLY)

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)

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).

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

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

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 +