pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsqlite_fdw crashes & errors
18+ messages / 3 participants
[nested] [flat]
* sqlite_fdw crashes & errors
@ 2018-09-16 12:01 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-16 12:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
postgresql v9.6.
As mentioned on the github page, the extension supports writes.
However, When I try to insert record into my foreign table my postgresql
crushes :
sqlite> .schema MARIEL_TEST
CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
INTEGER);
db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc
integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
CREATE FOREIGN TABLE
afa=# insert into MARIEL_TEST_foreign values(1,1,1);
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: Failed.
!>
!> .exit
-> ;
You are currently not connected to a database.
As you can see the database crashed. Does the reason is the extension or
something that I should investigate on the database itself ?
Moreover I tried to update the table and I'm getting the following error :
update MARIEL_TEST_foreign set ip=5 where vipid=2;
ERROR: no primary key column specified for foreign table
DETAIL: For UPDATE or DELETE, at least one foreign table column must be
marked as primary key column.
HINT: Set the option "key" on the columns that belong to the primary key.
foreign tables dont support primary key :
CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER
sqlite_server OPTIONS (table 'MARIEL_TEST');
ERROR: primary key constraints are not supported on foreign tables
so how can I update/delete a foreign table that has primary key ? OR with
that extension it isnt an option ?
Thanks , Mariel.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-16 12:40 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-16 12:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I solved the error with with primary key by specifying the option
(key:true) in the creation of the foreign table :
CREATE FOREIGN TABLE mariel_test(id integer OPTIONS (key 'true'),b text)
SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
However, my database crashes when I try to insert/update/delete the table :
insert into mariel_Test values(2,2);
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: Failed.
Any idea what I can check ?
בתאריך יום א׳, 16 בספט׳ 2018 ב-15:01 מאת Mariel Cherkassky <
mariel.cherkassky@gmail.com>:
> Hi,
> I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
> postgresql v9.6.
> As mentioned on the github page, the extension supports writes.
>
> However, When I try to insert record into my foreign table my postgresql
> crushes :
>
> sqlite> .schema MARIEL_TEST
> CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
> INTEGER);
>
> db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc
> integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
> CREATE FOREIGN TABLE
> afa=# insert into MARIEL_TEST_foreign values(1,1,1);
> 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: Failed.
> !>
> !> .exit
> -> ;
> You are currently not connected to a database.
>
> As you can see the database crashed. Does the reason is the extension or
> something that I should investigate on the database itself ?
>
> Moreover I tried to update the table and I'm getting the following error
> :
> update MARIEL_TEST_foreign set ip=5 where vipid=2;
> ERROR: no primary key column specified for foreign table
> DETAIL: For UPDATE or DELETE, at least one foreign table column must be
> marked as primary key column.
> HINT: Set the option "key" on the columns that belong to the primary key.
>
> foreign tables dont support primary key :
>
> CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER
> sqlite_server OPTIONS (table 'MARIEL_TEST');
> ERROR: primary key constraints are not supported on foreign tables
>
> so how can I update/delete a foreign table that has primary key ? OR with
> that extension it isnt an option ?
>
> Thanks , Mariel.
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-16 12:43 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-16 12:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Forgot to mention the error from the postgresql log :
2018-09-16 08:43:20 EDT 6027 DETAIL: Failed process was running: insert
into mariel_Test values(2,2);
2018-09-16 08:43:20 EDT 6027 LOG: terminating any other active server
processes
2018-09-16 08:43:20 EDT db1 23253 WARNING: terminating connection because
of crash of another server process
2018-09-16 08:43:20 EDT db1 23253 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.
2018-09-16 08:43:20 EDT db1 23253 HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT 22311 WARNING: terminating connection because of
crash of another server process
2018-09-16 08:43:20 EDT 22311 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.
2018-09-16 08:43:20 EDT 22311 HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT db2 22316 WARNING: terminating connection because
of crash of another server process
2018-09-16 08:43:20 EDT db2 22316 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.
2018-09-16 08:43:20 EDT db2 22316 HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT db2 22318 WARNING: terminating connection because
of crash of another server process
2018-09-16 08:43:20 EDT db2 22318 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.
2018-09-16 08:43:20 EDT db2 22318 HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2018-09-16 08:43:20 EDT 6027 LOG: all server processes terminated;
reinitializing
2018-09-16 08:43:21 EDT 26960 LOG: database system was interrupted; last
known up at 2018-09-16 08:39:06 EDT
2018-09-16 08:43:21 EDT 26960 LOG: database system was not properly shut
down; automatic recovery in progress
2018-09-16 08:43:21 EDT 26960 LOG: invalid record length at 142/3B33FC8:
wanted 24, got 0
2018-09-16 08:43:21 EDT 26960 LOG: redo is not required
2018-09-16 08:43:21 EDT 26960 LOG: checkpoint starting: end-of-recovery
immediate
בתאריך יום א׳, 16 בספט׳ 2018 ב-15:40 מאת Mariel Cherkassky <
mariel.cherkassky@gmail.com>:
> Hi,
> I solved the error with with primary key by specifying the option
> (key:true) in the creation of the foreign table :
> CREATE FOREIGN TABLE mariel_test(id integer OPTIONS (key 'true'),b text)
> SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
>
> However, my database crashes when I try to insert/update/delete the table
> :
>
> insert into mariel_Test values(2,2);
> 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: Failed.
>
> Any idea what I can check ?
>
> בתאריך יום א׳, 16 בספט׳ 2018 ב-15:01 מאת Mariel Cherkassky <
> mariel.cherkassky@gmail.com>:
>
>> Hi,
>> I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
>> postgresql v9.6.
>> As mentioned on the github page, the extension supports writes.
>>
>> However, When I try to insert record into my foreign table my postgresql
>> crushes :
>>
>> sqlite> .schema MARIEL_TEST
>> CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
>> INTEGER);
>>
>> db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer, colbtext,colc
>> integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
>> CREATE FOREIGN TABLE
>> afa=# insert into MARIEL_TEST_foreign values(1,1,1);
>> 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: Failed.
>> !>
>> !> .exit
>> -> ;
>> You are currently not connected to a database.
>>
>> As you can see the database crashed. Does the reason is the extension or
>> something that I should investigate on the database itself ?
>>
>> Moreover I tried to update the table and I'm getting the following error
>> :
>> update MARIEL_TEST_foreign set ip=5 where vipid=2;
>> ERROR: no primary key column specified for foreign table
>> DETAIL: For UPDATE or DELETE, at least one foreign table column must be
>> marked as primary key column.
>> HINT: Set the option "key" on the columns that belong to the primary key.
>>
>> foreign tables dont support primary key :
>>
>> CREATE FOREIGN TABLE MARIEL_TEST_foreign(id int primary key ) SERVER
>> sqlite_server OPTIONS (table 'MARIEL_TEST');
>> ERROR: primary key constraints are not supported on foreign tables
>>
>> so how can I update/delete a foreign table that has primary key ? OR with
>> that extension it isnt an option ?
>>
>> Thanks , Mariel.
>>
>>
>>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 00:49 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Mark Kirkwood @ 2018-09-18 00:49 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org
On 17/09/18 00:01, Mariel Cherkassky wrote:
> Hi,
> I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
> postgresql v9.6.
> As mentioned on the github page, the extension supports writes.
>
> However, When I try to insert record into my foreign table my
> postgresql crushes :
>
> sqlite> .schema MARIEL_TEST
> CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
> INTEGER);
>
> db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer,
> colbtext,colc integer) SERVER sqlite_server OPTIONS (table 'MARIEL_TEST');
> CREATE FOREIGN TABLE
> afa=# insert into MARIEL_TEST_foreign values(1,1,1);
> 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: Failed.
> !>
> !> .exit
> -> ;
Just tried this (Postgres 9.6.10 src build and sqlite_fdw on Ubuntu
18.04). Works for me. Hmmm interesting. I did one thing different from
you - my sqlite db is under Postgres home dir (/home/postgres/sqlite).
Thinking for a bit ... did you create the sqlite db as the postgres user
or someone else? If the latter then that might explain the crash (e.g
permission denied).
Cheers
Mark
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 06:40 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 06:40 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: pgsql-admin@lists.postgresql.org
What version of sqlite did you use ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-3:49 מאת Mark Kirkwood <
mark.kirkwood@catalyst.net.nz>:
> On 17/09/18 00:01, Mariel Cherkassky wrote:
> > Hi,
> > I installed sqlite_fdw(https://github.com/pgspider/sqlite_fdw) on my
> > postgresql v9.6.
> > As mentioned on the github page, the extension supports writes.
> >
> > However, When I try to insert record into my foreign table my
> > postgresql crushes :
> >
> > sqlite> .schema MARIEL_TEST
> > CREATE TABLE MARIEL_TEST( cola INTEGER PRIMARY KEY, colb TEXT, colc
> > INTEGER);
> >
> > db=# CREATE FOREIGN TABLE MARIEL_TEST_foreign(cola integer,
> > colbtext,colc integer) SERVER sqlite_server OPTIONS (table
> 'MARIEL_TEST');
> > CREATE FOREIGN TABLE
> > afa=# insert into MARIEL_TEST_foreign values(1,1,1);
> > 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: Failed.
> > !>
> > !> .exit
> > -> ;
>
> Just tried this (Postgres 9.6.10 src build and sqlite_fdw on Ubuntu
> 18.04). Works for me. Hmmm interesting. I did one thing different from
> you - my sqlite db is under Postgres home dir (/home/postgres/sqlite).
> Thinking for a bit ... did you create the sqlite db as the postgres user
> or someone else? If the latter then that might explain the crash (e.g
> permission denied).
>
> Cheers
> Mark
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 08:24 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Mark Kirkwood @ 2018-09-18 08:24 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
On 18/09/18 18:40, Mariel Cherkassky wrote:
> What version of sqlite did you use ?
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 08:36 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 08:36 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: pgsql-admin@lists.postgresql.org
I used version 3.6.24 maybe this is the reason ?
Moreover, I got a core dump and the output is :
root@node1_priviledged data]# gdb -q -c core
[New Thread 15599]
warning: no loadable sections found in added symbol-file system-supplied
DSO at 0x7ffec2934000
Core was generated by `postgres: postgres afa [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in ?? ()
any idea ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:24 מאת Mark Kirkwood <
mark.kirkwood@catalyst.net.nz>:
> Hi,
>
>
> On 18/09/18 18:40, Mariel Cherkassky wrote:
> > What version of sqlite did you use ?
>
> $ sqlite3 --version
> 3.22.0 2018-01-22 18:45:57
> 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 08:59 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Mark Kirkwood @ 2018-09-18 08:59 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
On 18/09/18 20:36, Mariel Cherkassky wrote:
> I used version 3.6.24 maybe this is the reason ?
> Moreover, I got a core dump and the output is :
> root@node1_priviledged data]# gdb -q -c core
> [New Thread 15599]
>
> warning: no loadable sections found in added symbol-file
> system-supplied DSO at 0x7ffec2934000
> Core was generated by `postgres: postgres afa [local] INSERT '.
> Program terminated with signal 11, Segmentation fault.
> #0 0x00007f128d3269df in ?? ()
>
>
You might need the debug symbols packages installed for Sqlite and
Postgres (how to do this depends on your OS).
The version difference is worth eliminating as a factor - maybe spin up
a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
regards
Mark
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 11:37 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 11:37 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: pgsql-admin@lists.postgresql.org
I installed the 3.22 version but got the same crash on the postgresql side.
I'm using CentOS release 6.10.
What is HW ? can you guide me regarding the debug symbols ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <
mark.kirkwood@catalyst.net.nz>:
> Hi,
>
> On 18/09/18 20:36, Mariel Cherkassky wrote:
> > I used version 3.6.24 maybe this is the reason ?
> > Moreover, I got a core dump and the output is :
> > root@node1_priviledged data]# gdb -q -c core
> > [New Thread 15599]
> >
> > warning: no loadable sections found in added symbol-file
> > system-supplied DSO at 0x7ffec2934000
> > Core was generated by `postgres: postgres afa [local] INSERT
> '.
> > Program terminated with signal 11, Segmentation fault.
> > #0 0x00007f128d3269df in ?? ()
> >
> >
>
> You might need the debug symbols packages installed for Sqlite and
> Postgres (how to do this depends on your OS).
>
> The version difference is worth eliminating as a factor - maybe spin up
> a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
> headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
>
> regards
> Mark
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 12:05 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 12:05 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: pgsql-admin@lists.postgresql.org
the core content :
Core was generated by `postgres: postgres afa [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
audit-libs-2.4.5-6.el6.x86_64 cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
glibc-2.12-1.212.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64
krb5-libs-1.10.3-65.el6.x86_64 libcom_err-1.41.12-24.el6.x86_64
libselinux-2.0.94-7.el6.x86_64 libxml2-2.7.6-21.el6_8.1.x86_64
nspr-4.19.0-1.el6.x86_64 nss-3.36.0-8.el6.x86_64
nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 nss-util-3.36.0-1.el6.x86_64
openldap-2.4.40-16.el6.x86_64 openssl-1.0.1e-57.el6.x86_64
pam-1.1.1-24.el6.x86_64 sqlite-3.6.20-1.el6_7.2.x86_64
zlib-1.2.3-29.el6.x86_64
(gdb) bt
#0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
#1 0x00000000007fdac1 in MemoryContextStrdup (context=0x1fd61d0,
string=0x0) at mcxt.c:1155
#2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized
out>, stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at
connection.c:288
#3 0x00007f11c1804efe in sqliteExecForeignInsert (estate=<value optimized
out>, resultRelInfo=<value optimized out>, slot=0x200a338, planSlot=<value
optimized out>) at sqlite_fdw.c:1167
#4 0x00000000005e8e89 in ExecInsert (node=0x200a008) at
nodeModifyTable.c:332
#5 ExecModifyTable (node=0x200a008) at nodeModifyTable.c:1512
#6 0x00000000005ceb28 in ExecProcNode (node=0x200a008) at
execProcnode.c:396
#7 0x00000000005cc62a in ExecutePlan (queryDesc=0x2001018,
direction=<value optimized out>, count=0) at execMain.c:1566
#8 standard_ExecutorRun (queryDesc=0x2001018, direction=<value optimized
out>, count=0) at execMain.c:338
#9 0x00000000006e491f in ProcessQuery (plan=0x1fec8f8,
sourceText=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);",
params=0x0, dest=<value optimized out>, completionTag=0x7ffec280b960 "")
at pquery.c:187
#10 0x00000000006e4b6a in PortalRunMulti (portal=0x2004a78, isTopLevel=1
'\001', setHoldSnapshot=0 '\000', dest=0x1fec9d8, altdest=0x1fec9d8,
completionTag=0x7ffec280b960 "") at pquery.c:1303
#11 0x00000000006e5234 in PortalRun (portal=0x2004a78,
count=9223372036854775807, isTopLevel=1 '\001', dest=0x1fec9d8,
altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:815
#12 0x00000000006e1ed1 in exec_simple_query (query_string=0x1fbbf18 "insert
into vip_foreign_3_22 values (2,2,2);") at postgres.c:1086
#13 0x00000000006e359c in PostgresMain (argc=<value optimized out>,
argv=<value optimized out>, dbname=0x1f0eb78 "afa", username=<value
optimized out>) at postgres.c:4072
#14 0x0000000000682ed5 in BackendRun (argc=<value optimized out>,
argv=<value optimized out>) at postmaster.c:4342
#15 BackendStartup (argc=<value optimized out>, argv=<value optimized out>)
at postmaster.c:4016
#16 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at
postmaster.c:1721
#17 PostmasterMain (argc=<value optimized out>, argv=<value optimized ou
בתאריך יום ג׳, 18 בספט׳ 2018 ב-14:37 מאת Mariel Cherkassky <
mariel.cherkassky@gmail.com>:
> I installed the 3.22 version but got the same crash on the postgresql
> side. I'm using CentOS release 6.10.
> What is HW ? can you guide me regarding the debug symbols ?
>
> בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <
> mark.kirkwood@catalyst.net.nz>:
>
>> Hi,
>>
>> On 18/09/18 20:36, Mariel Cherkassky wrote:
>> > I used version 3.6.24 maybe this is the reason ?
>> > Moreover, I got a core dump and the output is :
>> > root@node1_priviledged data]# gdb -q -c core
>> > [New Thread 15599]
>> >
>> > warning: no loadable sections found in added symbol-file
>> > system-supplied DSO at 0x7ffec2934000
>> > Core was generated by `postgres: postgres afa [local] INSERT
>> '.
>> > Program terminated with signal 11, Segmentation fault.
>> > #0 0x00007f128d3269df in ?? ()
>> >
>> >
>>
>> You might need the debug symbols packages installed for Sqlite and
>> Postgres (how to do this depends on your OS).
>>
>> The version difference is worth eliminating as a factor - maybe spin up
>> a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
>> headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
>>
>> regards
>> Mark
>>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 12:35 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 12:35 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: pgsql-admin@lists.postgresql.org
I did everything again, the core changed a bit :
[root@node1_priviledged sqlite-autoconf-3220000]# /usr/local/bin/sqlite3
/var/lib/pgsql/main.sqlite
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table mariel_Test (id int);
sqlite> insert into mariel_test values(1);
sqlite> select * from mariel_Test;
1
sqlite> .schema mariel_test
CREATE TABLE mariel_Test (id int)
postgresql side :
create server sqlite_server foreign data wrapper sqlite_fdw
options(database '/var/lib/pgsql/main.sqlite');
CREATE SERVER
db1=# create foreign table mariel_test_foreign (id integer) server
sqlite_server options(table 'mariel_test');
CREATE FOREIGN TABLE
db1=# select * from mariel_test_foreign ;
id
1
(1 row)
db1=# insert into mariel_test_foreign values(1);
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: Failed.
I had a core that was created and its content :
the core content :
[root@node1_priviledged data]# gdb -q -c core-postmaster-11-22870-1537273706
BFD: Warning: /data/pgsql/data/core-postmaster-11-22870-1537273706 is
truncated: expected core file size >= 3268317184, found: 3141574656.
[New Thread 22870]
Failed to read a valid object file image from memory.
Core was generated by `postgres: postgres db1 [local] INSERT '.
Program terminated with signal 11, Segmentation fault.
#0 0x00007f128d3269df in ?? ()
(gdb) bt
#0 0x00007f128d3269df in ?? ()
Cannot access memory at address 0x7ffec280b4b8
(gdb) bt full
#0 0x00007f128d3269df in ?? ()
No symbol table info available.
Cannot access memory at address 0x7ffec280b4b8
בתאריך יום ג׳, 18 בספט׳ 2018 ב-15:05 מאת Mariel Cherkassky <
mariel.cherkassky@gmail.com>:
> the core content :
>
>
> Core was generated by `postgres: postgres afa [local] INSERT '.
> Program terminated with signal 11, Segmentation fault.
> #0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
> Missing separate debuginfos, use: debuginfo-install
> audit-libs-2.4.5-6.el6.x86_64 cyrus-sasl-lib-2.1.23-15.el6_6.2.x86_64
> glibc-2.12-1.212.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64
> krb5-libs-1.10.3-65.el6.x86_64 libcom_err-1.41.12-24.el6.x86_64
> libselinux-2.0.94-7.el6.x86_64 libxml2-2.7.6-21.el6_8.1.x86_64
> nspr-4.19.0-1.el6.x86_64 nss-3.36.0-8.el6.x86_64
> nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 nss-util-3.36.0-1.el6.x86_64
> openldap-2.4.40-16.el6.x86_64 openssl-1.0.1e-57.el6.x86_64
> pam-1.1.1-24.el6.x86_64 sqlite-3.6.20-1.el6_7.2.x86_64
> zlib-1.2.3-29.el6.x86_64
>
> (gdb) bt
> #0 0x00007f128d3269df in __strlen_sse42 () from /lib64/libc.so.6
> #1 0x00000000007fdac1 in MemoryContextStrdup (context=0x1fd61d0,
> string=0x0) at mcxt.c:1155
> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized
> out>, stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at
> connection.c:288
> #3 0x00007f11c1804efe in sqliteExecForeignInsert (estate=<value optimized
> out>, resultRelInfo=<value optimized out>, slot=0x200a338, planSlot=<value
> optimized out>) at sqlite_fdw.c:1167
> #4 0x00000000005e8e89 in ExecInsert (node=0x200a008) at
> nodeModifyTable.c:332
> #5 ExecModifyTable (node=0x200a008) at nodeModifyTable.c:1512
> #6 0x00000000005ceb28 in ExecProcNode (node=0x200a008) at
> execProcnode.c:396
> #7 0x00000000005cc62a in ExecutePlan (queryDesc=0x2001018,
> direction=<value optimized out>, count=0) at execMain.c:1566
> #8 standard_ExecutorRun (queryDesc=0x2001018, direction=<value optimized
> out>, count=0) at execMain.c:338
> #9 0x00000000006e491f in ProcessQuery (plan=0x1fec8f8,
> sourceText=0x1fbbf18 "insert into vip_foreign_3_22 values (2,2,2);",
> params=0x0, dest=<value optimized out>, completionTag=0x7ffec280b960 "")
> at pquery.c:187
> #10 0x00000000006e4b6a in PortalRunMulti (portal=0x2004a78, isTopLevel=1
> '\001', setHoldSnapshot=0 '\000', dest=0x1fec9d8, altdest=0x1fec9d8,
> completionTag=0x7ffec280b960 "") at pquery.c:1303
> #11 0x00000000006e5234 in PortalRun (portal=0x2004a78,
> count=9223372036854775807, isTopLevel=1 '\001', dest=0x1fec9d8,
> altdest=0x1fec9d8, completionTag=0x7ffec280b960 "") at pquery.c:815
> #12 0x00000000006e1ed1 in exec_simple_query (query_string=0x1fbbf18
> "insert into vip_foreign_3_22 values (2,2,2);") at postgres.c:1086
> #13 0x00000000006e359c in PostgresMain (argc=<value optimized out>,
> argv=<value optimized out>, dbname=0x1f0eb78 "afa", username=<value
> optimized out>) at postgres.c:4072
> #14 0x0000000000682ed5 in BackendRun (argc=<value optimized out>,
> argv=<value optimized out>) at postmaster.c:4342
> #15 BackendStartup (argc=<value optimized out>, argv=<value optimized
> out>) at postmaster.c:4016
> #16 ServerLoop (argc=<value optimized out>, argv=<value optimized out>) at
> postmaster.c:1721
> #17 PostmasterMain (argc=<value optimized out>, argv=<value optimized ou
>
> בתאריך יום ג׳, 18 בספט׳ 2018 ב-14:37 מאת Mariel Cherkassky <
> mariel.cherkassky@gmail.com>:
>
>> I installed the 3.22 version but got the same crash on the postgresql
>> side. I'm using CentOS release 6.10.
>> What is HW ? can you guide me regarding the debug symbols ?
>>
>> בתאריך יום ג׳, 18 בספט׳ 2018 ב-11:59 מאת Mark Kirkwood <
>> mark.kirkwood@catalyst.net.nz>:
>>
>>> Hi,
>>>
>>> On 18/09/18 20:36, Mariel Cherkassky wrote:
>>> > I used version 3.6.24 maybe this is the reason ?
>>> > Moreover, I got a core dump and the output is :
>>> > root@node1_priviledged data]# gdb -q -c core
>>> > [New Thread 15599]
>>> >
>>> > warning: no loadable sections found in added symbol-file
>>> > system-supplied DSO at 0x7ffec2934000
>>> > Core was generated by `postgres: postgres afa [local] INSERT
>>> '.
>>> > Program terminated with signal 11, Segmentation fault.
>>> > #0 0x00007f128d3269df in ?? ()
>>> >
>>> >
>>>
>>> You might need the debug symbols packages installed for Sqlite and
>>> Postgres (how to do this depends on your OS).
>>>
>>> The version difference is worth eliminating as a factor - maybe spin up
>>> a VM of Ubuntu 18.04 and test (or build Sqlite 3.22 on your OS with dev
>>> headers and libs etc). BTW , what is your OS and HW? (could be a factor)!
>>>
>>> regards
>>> Mark
>>>
>>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 13:12 Andrew Gierth <andrew@tao11.riddles.org.uk>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Andrew Gierth @ 2018-09-18 13:12 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: mark.kirkwood@catalyst.net.nz; pgsql-admin@lists.postgresql.org
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value optimized
>> out> , stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at
>> connection.c:288
This is a bug in sqlite_fdw - it's assuming it can use sqlite3_sql to
get the sql source text back from a statement, but that's only true when
the statement was prepared using a different API than the one actually
being used. So sqlite3_sql ends up returning null, and pstrdup naturally
crashes on that.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 13:17 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-18 13:17 UTC (permalink / raw)
To: andrew@tao11.riddles.org.uk; +Cc: mark.kirkwood@catalyst.net.nz; pgsql-admin@lists.postgresql.org
So, how exactly it works for other people ? Shouldnt everyone who use this
extension should have suffered from this bug ? And why for selects it works
fine ?
בתאריך יום ג׳, 18 בספט׳ 2018 ב-16:13 מאת Andrew Gierth <
andrew@tao11.riddles.org.uk>:
> >>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
>
> >> #2 0x00007f11c1800e24 in sqlitefdw_report_error (elevel=<value
> optimized
> >> out> , stmt=0x20013f8, conn=<value optimized out>, sql=0x0, rc=1) at
> >> connection.c:288
>
> This is a bug in sqlite_fdw - it's assuming it can use sqlite3_sql to
> get the sql source text back from a statement, but that's only true when
> the statement was prepared using a different API than the one actually
> being used. So sqlite3_sql ends up returning null, and pstrdup naturally
> crashes on that.
>
> --
> Andrew (irc:RhodiumToad)
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 13:33 Andrew Gierth <andrew@tao11.riddles.org.uk>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Andrew Gierth @ 2018-09-18 13:33 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: mark.kirkwood@catalyst.net.nz; pgsql-admin@lists.postgresql.org
>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
Mariel> So, how exactly it works for other people ? Shouldnt everyone
Mariel> who use this extension should have suffered from this bug ? And
Mariel> why for selects it works fine ?
The bug is in error reporting, so it kicks in only when an error occurs
and then only when a null value for the sql text is passed to the
reporting function.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-18 23:02 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 1 reply; 18+ messages in thread
From: Mark Kirkwood @ 2018-09-18 23:02 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 19/09/18 01:33, Andrew Gierth wrote:
>>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> Mariel> So, how exactly it works for other people ? Shouldnt everyone
> Mariel> who use this extension should have suffered from this bug ? And
> Mariel> why for selects it works fine ?
>
> The bug is in error reporting, so it kicks in only when an error occurs
> and then only when a null value for the sql text is passed to the
> reporting function.
>
Ah right - I guess I was not tickling the bug correctly:
$ chmod 444 sqlite/test.db
$
$ psql
=# insert into MARIEL_TEST_foreign values(4,1,1);
ERROR: failed to execute remote SQL: rc=1 SQL logic error
sql=INSERT INTO main."MARIEL_TEST"("cola", "colb", "colc") VALUES (?,
?, ?)
regards
Mark
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-19 05:08 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-19 05:08 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: andrew@tao11.riddles.org.uk; pgsql-admin@lists.postgresql.org
But in my case i'm not getting the error you got. Instead, my postgres
instance crashes.
בתאריך יום ד׳, 19 בספט׳ 2018 ב-2:02 מאת Mark Kirkwood <
mark.kirkwood@catalyst.net.nz>:
> On 19/09/18 01:33, Andrew Gierth wrote:
> >>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> > Mariel> So, how exactly it works for other people ? Shouldnt everyone
> > Mariel> who use this extension should have suffered from this bug ? And
> > Mariel> why for selects it works fine ?
> >
> > The bug is in error reporting, so it kicks in only when an error occurs
> > and then only when a null value for the sql text is passed to the
> > reporting function.
> >
> Ah right - I guess I was not tickling the bug correctly:
>
> $ chmod 444 sqlite/test.db
> $
>
> $ psql
>
> =# insert into MARIEL_TEST_foreign values(4,1,1);
> ERROR: failed to execute remote SQL: rc=1 SQL logic error
> sql=INSERT INTO main."MARIEL_TEST"("cola", "colb", "colc") VALUES (?,
> ?, ?)
>
> regards
>
> Mark
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-19 07:50 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Mark Kirkwood @ 2018-09-19 07:50 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: andrew@tao11.riddles.org.uk; pgsql-admin@lists.postgresql.org
On 19/09/18 17:08, Mariel Cherkassky wrote:
> But in my case i'm not getting the error you got. Instead, my postgres
> instance crashes.
>
> >
> > The bug is in error reporting, so it kicks in only when an error
> occurs
> > and then only when a null value for the sql text is passed to the
> > reporting function.
>
^^^ My method of making it error appears to have sql text, somehow in
your case there is none (which is interesting), but maybe not worth
investigating as there is obviously a bug in the error handling - we can
retest when it is patched!
regards
Mark
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: sqlite_fdw crashes & errors
@ 2018-09-19 08:16 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 18+ messages in thread
From: Mariel Cherkassky @ 2018-09-19 08:16 UTC (permalink / raw)
To: mark.kirkwood@catalyst.net.nz; +Cc: andrew@tao11.riddles.org.uk; pgsql-admin@lists.postgresql.org
I opened a ticket in the git page of the extension. It seems like a bug in
the extension, the extension`s developer is working on it..
בתאריך יום ד׳, 19 בספט׳ 2018 ב-10:50 מאת Mark Kirkwood <
mark.kirkwood@catalyst.net.nz>:
> On 19/09/18 17:08, Mariel Cherkassky wrote:
>
> > But in my case i'm not getting the error you got. Instead, my postgres
> > instance crashes.
> >
> > >
> > > The bug is in error reporting, so it kicks in only when an error
> > occurs
> > > and then only when a null value for the sql text is passed to the
> > > reporting function.
> >
>
> ^^^ My method of making it error appears to have sql text, somehow in
> your case there is none (which is interesting), but maybe not worth
> investigating as there is obviously a bug in the error handling - we can
> retest when it is patched!
>
> regards
> Mark
>
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2018-09-19 08:16 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-16 12:01 sqlite_fdw crashes & errors Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-16 12:40 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-16 12:43 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 00:49 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-18 06:40 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 08:24 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-18 08:36 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 08:59 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-18 11:37 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 12:05 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 12:35 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 13:12 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2018-09-18 13:17 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-18 13:33 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2018-09-18 23:02 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-19 05:08 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-09-19 07:50 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-19 08:16 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox