Search Postgresql Archives

Re: How to release locks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> It is not because of the locks. There is only running transaction (and it
> got every locks). Not sure why the backend stucks there, seems it doesn't
> aware of the broken client. In normal situations, if you killed a client,
> then the server will print something like "could not receive data from
> client" then exit the backend.
>
> If you can attach to the problematic postgres process, that would be more
> helpful to identify the problem.

My ODBC client uploads 9 MB binary file to server bytea field using 1 MB
binary hex encoded blocks using code something like:

START TRANSACTION;
DELETE FROM localfil WHERE LOWER(filename)='alguss.exe';
INSERT INTO localfil (filename,filedirect,BlockNumber,lastchange,contents)
     values( 'alguss.exe', 'algus', 1,CURRENT_TIMESTAMP, decode(
'1mbbinarystring', 'hex') );
INSERT INTO localfil (filename,filedirect,BlockNumber,lastchange,contents)
      values( 'alguss.exe', 'algus', 2,CURRENT_TIMESTAMP, decode(
'1mbbinarystring', 'hex') );
.... etc 8 insert statemens
COMMIT;

insert command takes a long time. I terminated the client application from
Windows XP client manager during insert command.
At next re-run application and pgadmin both hang in DELETE FROM line

I think I can reproduce this. Postgres server is in FreeBSD.
I can require server admin to do something after the problem occurs.

What should I do after reproducing the problem ?

Table structure is

CREATE TABLE public.localfil (

Filename CHAR(50),

FileDirect CHAR(8),

BlockNumber INTEGER,

lastchange timestamp NOT NULL,

Contents BYTEA NOT NULL,

PRIMARY KEY ( Filename, FileDirect, BlockNumber )

);

CREATE TRIGGER localfil_trig BEFORE INSERT OR UPDATE

ON localfil EXECUTE PROCEDURE setlastchange();

and table is empty. 




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux