Re: An I/O error occured while sending to the backend

2022-11-15 Thread gzh
Thank you very much for your advice. What I don't understand is that there is no problem when executing ① alone or ② alone, the error occurs when ① and ② are executed together . It works well when i let the application sleep after ① for 10 seconds before executing ② . Is ② affected by ① ?

Re: An I/O error occured while sending to the backend

2022-11-15 Thread Laurenz Albe
On Wed, 2022-11-16 at 13:04 +0800, gzh wrote: > I have developed an application using the jdbc driver and  > connecting to a postgresql database . > The application mainly does the following two things: > ① read data from the CSV file and insert it into the database > ② perform a database query >

An I/O error occured while sending to the backend

2022-11-15 Thread gzh
Hi, I have developed an application using the jdbc driver and connecting to a postgresql database . The application mainly does the following two things: ① read data from the CSV file and insert it into the database ② perform a database query In my application, the above ① and ② are

Re: PANIC: could not flush dirty data: Cannot allocate memory

2022-11-15 Thread Christoph Moench-Tegeder
## klaus.mailingli...@pernau.at (klaus.mailingli...@pernau.at): > AFAIU the problem is not related to the memory settings in > postgresql.conf. It is the kernel that > for whatever reasons report ENOMEM. Correct? Correct, there's a ENOMEM from the kernel when writing out data. > Filesystem is

Re: PANIC: could not flush dirty data: Cannot allocate memory

2022-11-15 Thread Thomas Munro
On Wed, Nov 16, 2022 at 1:24 AM wrote: > Filesystem is ext4. VM technology is mixed: VMware, KVM and XEN PV. > Kernel is 5.15.0-52-generic. > > We have not seen this with Ubutnu 18.04 and 20.04 (although we might not > have noticed it). > > I guess upgrading to postgresql 13/14/15 does not help

Toast Tables, pg_repack, and AWS RDS

2022-11-15 Thread Preston Hagar
Hi all, I'm working with a PG 11.13 database in RDS. We've been using pg_repack to reclaim space/reduce bloat successfully. I have a question regarding toast tables though: We have few really large tables (TB+) where probably 80-90% of the space is taken up by the toast table. Because of

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Frank Cazabon
On 15/11/2022 2:58 pm, Adrian Klaver wrote: On 11/15/22 10:54 AM, Frank Cazabon wrote: On 15/11/2022 2:44 pm, Tom Lane wrote: Frank Cazabon writes: If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(       )       RETURNS TABLE       (     

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Frank Cazabon
I don't think that's necessary, I'm 100% certain that it's VFP not able to interpret the size of what is coming back to it so it just gives it the biggest type it can. Thanks 15 Nov 2022 14:59:59 Ron : > On 11/15/22 12:54, Frank Cazabon wrote: >> >> On 15/11/2022 2:44 pm, Tom Lane wrote: >>>

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Ron
On 11/15/22 12:54, Frank Cazabon wrote: On 15/11/2022 2:44 pm, Tom Lane wrote: Frank Cazabon writes: If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(       )       RETURNS TABLE       (      Firstname character(30)       )       LANGUAGE

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Adrian Klaver
On 11/15/22 10:54 AM, Frank Cazabon wrote: On 15/11/2022 2:44 pm, Tom Lane wrote: Frank Cazabon writes: If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(       )       RETURNS TABLE       (      Firstname character(30)       )       LANGUAGE

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Frank Cazabon
On 15/11/2022 2:44 pm, Tom Lane wrote: Frank Cazabon writes: If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(     )     RETURNS TABLE     (    Firstname character(30)     )     LANGUAGE 'plpgsql' AS $BODY$ BEGIN     RETURN

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Frank Cazabon
On 15/11/2022 2:48 pm, Adrian Klaver wrote: On 11/15/22 10:43 AM, Frank Cazabon wrote: Please reply to list als. Ccing list Sorry about that, first time using this list and just assumed I was replying to the list and the list would then notify you SELECT * FROM public.testFunction();

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Adrian Klaver
On 11/15/22 10:44 AM, Tom Lane wrote: Frank Cazabon writes: Any idea what I need to do to get it to return the character(30) type? There's no chance of getting back the "30" part with this structure, because function signatures do not carry length restrictions. What I expect is happening

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Adrian Klaver
On 11/15/22 10:43 AM, Frank Cazabon wrote: Please reply to list als. Ccing list SELECT * FROM public.testFunction(); SELECT firstname from FROM public.testFunction(); This has the same result. How about?: SELECT firstname::varchar(30) from FROM public.testFunction(); Then FirstName

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Tom Lane
Frank Cazabon writes: > If however I have a function defined like this > CREATE OR REPLACE FUNCTION public.testfunction( >     ) >     RETURNS TABLE >     ( >    Firstname character(30) >     ) >     LANGUAGE 'plpgsql' > AS $BODY$ > BEGIN >     RETURN QUERY SELECT p.cFirstName FROM

Re: Calling function from VFP changes character field to Memo

2022-11-15 Thread Adrian Klaver
On 11/15/22 9:44 AM, Frank Cazabon wrote: Hi, I have a simple table Patients with one field FirstName of type character(30). If I SELECT FirstName From public.patients; I get back the expected character(30) field. If however I have a function defined like this CREATE OR REPLACE FUNCTION

Calling function from VFP changes character field to Memo

2022-11-15 Thread Frank Cazabon
Hi, I have a simple table Patients with one field FirstName of type character(30). If I SELECT FirstName From public.patients; I get back the expected character(30) field. If however I have a function defined like this CREATE OR REPLACE FUNCTION public.testfunction(     )     RETURNS

Index not getting cleaned even though vacuum is running

2022-11-15 Thread Karthik Jagadish (kjagadis)
Hi, We notice that vacuum is happening at regular intervals but the space occupied by indexes is always increasing. Any pointers as to why would this happen? Some outputs below. Auto vacuum is enabled but we notice index size is growing. $ psql -U postgres -d cgms -c "SELECT

Re: PostgreSQL server "idle in transaction"

2022-11-15 Thread Tom Lane
Adrian Klaver writes: > On 11/15/22 04:28, Matthias Apitz wrote: >> I have below the full ESQL/C log and do not understand, why the >> PostgreSQL server is thinking "idle in transaction". For me with the >> "COMMIT" on the line below marked with ^ the transaction was closed. > From your log:

Re: PostgreSQL server "idle in transaction"

2022-11-15 Thread Adrian Klaver
On 11/15/22 04:28, Matthias Apitz wrote: Hello, We face with our application servers (ESQL/C written) and a 14.1 server on Linux, the situation that the PostgreSQL backend for servers are saying "idle in transaction". One can see this in the table pg_stat_activity and also on the shell: $ ps

PostgreSQL server "idle in transaction"

2022-11-15 Thread Matthias Apitz
Hello, We face with our application servers (ESQL/C written) and a 14.1 server on Linux, the situation that the PostgreSQL backend for servers are saying "idle in transaction". One can see this in the table pg_stat_activity and also on the shell: $ ps -ef | grep transaction postgres 6979

Re: PANIC: could not flush dirty data: Cannot allocate memory

2022-11-15 Thread klaus . mailinglists
Thanks all for digging into this problem. AFAIU the problem is not related to the memory settings in postgresql.conf. It is the kernel that for whatever reasons report ENOMEM. Correct? Am 2022-11-14 22:54, schrieb Christoph Moench-Tegeder: ## klaus.mailingli...@pernau.at