BUG: Session getting disconnected while executing a function to create materialised views in PostgreSQL 9.6
Dear Team,
While creating a 9.6 database environment we have come across a situation where the session is getting disconnected while running a function. Below are the DB details and function details.
DB Version: PostgreSQL 9.6beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit
DB Client: PgAdmin 3 v1.22.1
DB Client Operating System: Mac OS v10.11.3
DB Function definition (sample test function to replicate the issue):-
CREATE OR REPLACE FUNCTION testing_func()
RETURNS void AS $$
BEGIN
DROP MATERIALIZED VIEW IF EXISTS test1;
CREATE MATERIALIZED VIEW test1 AS
(
SELECT 1 as a,2 as b,3 as c,4 as d,5 as e,6 as f
ORDER BY
1
) WITH NO DATA;
END;
$$ LANGUAGE plpgsql;
Replication Scenario:
* Compile the above code. Run select query of the above function (I.e. Select testing_func();)
Result:
* Session gets disconnected forcibly.
Few other observations:
* The same code snippet runs fine in PostreSQL v9.5
* When we remove the clause WITH NO DATA, then the functions runs successfully
Please let me know in case any more details are required from our side.
Regards,
Shivam Saxena
On Thu, Aug 11, 2016 at 6:30 PM, Saxena, Shivam <shsaxena@teamdrg.com> wrote:
While creating a 9.6 database environment we have come across a situation
where the session is getting disconnected while running a function. Below
are the DB details and function details.
Thanks for the report!
Few other observations:
The same code snippet runs fine in PostreSQL v9.5
When we remove the clause WITH NO DATA, then the functions runs successfully
9.5.4 and other is impacted as well by this crash, down to 9.3, and
this is an oversight of 1651b9aa that made a matview query with WITH
NO DATA not run the parser/planner when executed. spi.c has a special
handling for CTAS, and this query runs into that, so any such query
executed through the SPI is going to crash on that:
(lldb) up 1
frame #4: 0x0000000105986dde
postgres`_SPI_execute_plan(plan=0x00007f9ff383c038,
paramLI=0x0000000000000000, snapshot=0x0000000000000000,
crosscheck_snapshot=0x0000000000000000, read_only='\0',
fire_triggers='\x01', tcount=0) + 1902 at spi.c:2228
2225 else
2226 {
2227 /* Must be an IF NOT EXISTS that
did nothing */
-> 2228 Assert(ctastmt->if_not_exists);
2229 _SPI_current->processed = 0;
2230 }
2231
(lldb) p *ctastmt
(CreateTableAsStmt) $1 = {
type = T_CreateTableAsStmt
query = 0x00007f9ff385e4d0
into = 0x00007f9ff3868ef0
relkind = OBJECT_MATVIEW
is_select_into = '\0'
if_not_exists = '\0'
}
I'll produce a patch in the worst case by tomorrow morning my time.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Aug 11, 2016 at 9:10 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
I'll produce a patch in the worst case by tomorrow morning my time.
And attached is the patch. I arrived at the conclusion that the
assertion being broken here just needs to be relaxed a bit so as it
understands that this code path can be taken by a matview WITH NO
DATA. I have bundled a regression test as well. This should be
backpatched down to 9.3.
--
Michael
Attachments:
matview-func-fix.patchapplication/x-download; name=matview-func-fix.patchDownload+35-2
"Michael" == Michael Paquier <michael.paquier@gmail.com> writes:
I'll produce a patch in the worst case by tomorrow morning my time.
Michael> And attached is the patch. I arrived at the conclusion that
Michael> the assertion being broken here just needs to be relaxed a bit
Michael> so as it understands that this code path can be taken by a
Michael> matview WITH NO DATA. I have bundled a regression test as
Michael> well. This should be backpatched down to 9.3.
Might it make sense in the regression test to check that ROW_COUNT ends
up correctly set?
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Thu, Aug 11, 2016 at 10:21 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
"Michael" == Michael Paquier <michael.paquier@gmail.com> writes:
I'll produce a patch in the worst case by tomorrow morning my time.
Michael> And attached is the patch. I arrived at the conclusion that
Michael> the assertion being broken here just needs to be relaxed a bit
Michael> so as it understands that this code path can be taken by a
Michael> matview WITH NO DATA. I have bundled a regression test as
Michael> well. This should be backpatched down to 9.3.Might it make sense in the regression test to check that ROW_COUNT ends
up correctly set?
I didn't think that this was necessary, the existence of the relations
being a sufficient guarantee.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Michael Paquier <michael.paquier@gmail.com> writes:
And attached is the patch. I arrived at the conclusion that the
assertion being broken here just needs to be relaxed a bit so as it
understands that this code path can be taken by a matview WITH NO
DATA.
Man, that looks familiar. Didn't we fix a similar oversight somewhere
else, not long ago? Wonder if there are more. But a quick grep for
if_not_exists doesn't find anything, so I'm not quite sure what I'm
half-remembering ...
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Michael Paquier <michael.paquier@gmail.com> writes:
And attached is the patch. I arrived at the conclusion that the
assertion being broken here just needs to be relaxed a bit so as it
understands that this code path can be taken by a matview WITH NO
DATA. I have bundled a regression test as well. This should be
backpatched down to 9.3.
Pushed. The Assert was still a bit too strong: as you had it, it
still crashed on CREATE TABLE AS ... WITH NO DATA.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Fri, Aug 12, 2016 at 12:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Paquier <michael.paquier@gmail.com> writes:
And attached is the patch. I arrived at the conclusion that the
assertion being broken here just needs to be relaxed a bit so as it
understands that this code path can be taken by a matview WITH NO
DATA. I have bundled a regression test as well. This should be
backpatched down to 9.3.Pushed. The Assert was still a bit too strong: as you had it, it
still crashed on CREATE TABLE AS ... WITH NO DATA.
Thanks, I missed this point. Hacking at night is never good..
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs