Обсуждение: BUG: Session getting disconnected while executing a function to create materialised views in PostgreSQL 9.6
RGVhciBUZWFtLA0KDQpXaGlsZSBjcmVhdGluZyBhIDkuNiBkYXRhYmFzZSBlbnZpcm9ubWVudCB3 ZSBoYXZlIGNvbWUgYWNyb3NzIGEgc2l0dWF0aW9uIHdoZXJlIHRoZSBzZXNzaW9uIGlzIGdldHRp bmcgZGlzY29ubmVjdGVkIHdoaWxlIHJ1bm5pbmcgYSBmdW5jdGlvbi4gQmVsb3cgYXJlIHRoZSBE QiBkZXRhaWxzIGFuZCBmdW5jdGlvbiBkZXRhaWxzLg0KDQpEQiBWZXJzaW9uOiBQb3N0Z3JlU1FM IDkuNmJldGEzIG9uIHg4Nl82NC1wYy1saW51eC1nbnUsIGNvbXBpbGVkIGJ5IGdjYyAoR0NDKSA0 LjQuNyAyMDEyMDMxMyAoUmVkIEhhdCA0LjQuNy0xNyksIDY0LWJpdA0KREIgQ2xpZW50OiBQZ0Fk bWluIDMgdjEuMjIuMQ0KREIgQ2xpZW50IE9wZXJhdGluZyBTeXN0ZW06IE1hYyBPUyB2MTAuMTEu Mw0KDQpEQiBGdW5jdGlvbiBkZWZpbml0aW9uIChzYW1wbGUgdGVzdCBmdW5jdGlvbiB0byByZXBs aWNhdGUgdGhlIGlzc3VlKTotDQpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiB0ZXN0aW5nX2Z1 bmMoKQ0KICAgICAgIFJFVFVSTlMgdm9pZCBBUyAkJA0KICAgICAgIEJFR0lODQoNCiAgICAgICAg ICAgICAgICAgICBEUk9QIE1BVEVSSUFMSVpFRCBWSUVXIElGIEVYSVNUUyB0ZXN0MTsNCg0KICAg ICAgICAgICAgICAgICAgIENSRUFURSBNQVRFUklBTElaRUQgVklFVyB0ZXN0MSBBUw0KICAgICAg ICAgICAgICAgICAgICAoDQogICAgICAgICAgICAgICAgICAgICAgIFNFTEVDVCAxIGFzIGEsMiBh cyBiLDMgYXMgYyw0IGFzIGQsNSBhcyBlLDYgYXMgZg0KICAgICAgICAgICAgICAgICAgICAgICBP UkRFUiBCWQ0KICAgICAgICAgICAgICAgICAgICAgIDENCiAgICAgICAgICAgICAgICAgICAgKSBX SVRIIE5PIERBVEE7DQoNCiAgICAgICBFTkQ7DQogICAgICAgJCQgTEFOR1VBR0UgcGxwZ3NxbDsN Cg0KUmVwbGljYXRpb24gU2NlbmFyaW86DQoNCiAgKiAgIENvbXBpbGUgdGhlIGFib3ZlIGNvZGUu IFJ1biBzZWxlY3QgcXVlcnkgb2YgdGhlIGFib3ZlIGZ1bmN0aW9uIChJLmUuIFNlbGVjdCB0ZXN0 aW5nX2Z1bmMoKTspDQoNClJlc3VsdDoNCg0KICAqICAgU2Vzc2lvbiBnZXRzIGRpc2Nvbm5lY3Rl ZCBmb3JjaWJseS4NCg0KRmV3IG90aGVyIG9ic2VydmF0aW9uczoNCg0KICAqICAgVGhlIHNhbWUg Y29kZSBzbmlwcGV0IHJ1bnMgZmluZSBpbiBQb3N0cmVTUUwgdjkuNQ0KICAqICAgV2hlbiB3ZSBy ZW1vdmUgdGhlIGNsYXVzZSBXSVRIIE5PIERBVEEsIHRoZW4gdGhlIGZ1bmN0aW9ucyBydW5zIHN1 Y2Nlc3NmdWxseQ0KDQpQbGVhc2UgbGV0IG1lIGtub3cgaW4gY2FzZSBhbnkgbW9yZSBkZXRhaWxz IGFyZSByZXF1aXJlZCBmcm9tIG91ciBzaWRlLg0KDQpSZWdhcmRzLA0KU2hpdmFtIFNheGVuYQ0K
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
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
Вложения
>>>>> "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)
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
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
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
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