I have the hang issue in Postgres, When I am going to insert into record in a 
table.
Table structure is enclosed in the discussion.

Also we found the issue with unique indexes. When I removed the unique index 
insert operation is working fine.
I need help from this core group that weather this is the bug in the Postgres 
code or we are using unique index wrongly.

Regards
Tarkeshwar

From: Niklas Andersson
Sent: 09 July 2014 18:21
To: M Tarkeshwar Rao; Leo Zhou; postgres-disc...@mailman.lmera.ericsson.se
Subject: RE: [postgres-discuss] Insert query hangs

I wouldn't advice you to drop the indexes in a production environment, as they 
are usually very important to get fast queries.

 Your index doesn't seem to be of much use though, as it looks like you are 
only indexing one single column that is an integer. It seems as it is not 
needed. Usually you use indexes with two or more columns to speed up queries, 
or you join on those columns.

 If you want to make sure that that column is unique, I would advice you to 
define it as a primary key. You could also use the keyword unique, but in this 
case I would prefer to define it as a primary key.

Then in order to always get a new, unique integer as a primary key, I would 
suggest you have a look at CREATE SEQUENCE. The syntax comes from how Oracle 
does it and it works very nice [1]

But, this doesn't explain why your current index is causing problems, becuase 
it _shouldnt_ ;-/

I think you would need some tools to have a check on the server load. Or have a 
look at how EXPLAIN works, Unfortunately I don't have that deep competence :-(

[1] http://www.postgresql.org/docs/8.1/static/sql-createsequence.html


Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 2:29 PM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs
What should I do resolve this issue?

Change the structure of Table or I should not create the index.

From: Niklas Andersson
Sent: 09 July 2014 17:58
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Yes, and the more data, the longer it takes to rebuild the index.

 This is why you drop the indexes during certain copy operations, if you have 
indexes enabled the copy would take forever.

Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 2:22 PM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs
Fine now I understand why it is taking time.

Is it possible that insert operation will take time when unique index is 
already created on the table and table has some data within it?


From: Niklas Andersson
Sent: 09 July 2014 17:20
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Can this be of help [1]?

[1] 
http://www.postgresql.org/docs/9.2/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 1:41 PM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

CREATE TABLE eventlogentry

(

   tableindex integer,

   object character varying(80),

   method character varying(80),

   bgwuser character varying(80),

   "time" character(23),

   realuser character varying(80),

   host character varying(80),

   application character varying(80)

)

WITH (

   OIDS=FALSE

)

TABLESPACE mmdata;

ALTER TABLE eventlogentry

   OWNER TO mmsuper;

GRANT ALL ON TABLE eventlogentry TO mmsuper; GRANT SELECT ON TABLE 
eventlogentry TO report;



CREATE UNIQUE INDEX ind1_eventlogentry

   ON eventlogentry

   USING btree

   (tableindex )

TABLESPACE mmindex;



I am sharing the table structure. When we removed the unique index it is 
working fine.

And when created normal index(not unique) it is working fine.



After removing unique index we tried to recreate it but it is giving following 
infinite logs :


concurrent insert in progress within table "eventlogentry"

caveat when building a unique index concurrently is that the uniqueness 
constraint is already being enforced against other transactions when the second 
table scan begins





Regards

Tarkeshwar


From: Niklas Andersson
Sent: 09 July 2014 16:10
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Hi,

 You have some info on checking on corrupt tables here [1], but I am pretty 
sure you'll be helped by using the REINDEX option [2].

 If you have lots of data, and lots of inserts sometimes you can run into 
performance issues. I.e the server doesn't have the time to rebuild the index 
at the speed you're sending inserts. But then the app still wouldn't hang, but 
almost grind to a halt and give you sluggish performance.

 Now, I don't know if this database has lots of deletes, but sometimes it makes 
sense to clean it up a bit. (Think: defrag), then VACUUM is helpful [3]

[1] http://blog.apptamers.com/post/32050443731/corrupted-postgres-table
[2] http://www.postgresql.org/docs/8.1/static/sql-reindex.html
[3] http://www.postgresql.org/docs/9.1/static/sql-vacuum.html

Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 12:32 PM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs
Actually problem is in customer site in their production environment.
They have their database MDB. In that db they have insert issue with one table.

They shared the data folder with us.

We past the same data folder in our local DB which has the same name MDB and 
reproduced the issue.

After reproducing the issue we run pg_dump(with data) on corrupted table and 
restore it on another local db.
After restoring in another db we tested the insert operation and found it is 
working fine.

We analysed one more thing when we removed the unique index from the table it 
is working fine.
Is there any issue in indexing?

Is there any option to repair the table or its indexing?

Regards
Tarkeshwar

From: Niklas Andersson
Sent: 09 July 2014 15:44
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Ah, then it sounds like you had corrupt data in that first table. When you did 
the pgdump, did you also export and import the data and not only the structure?

Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 12:05 PM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs
You mean to say diff in the name of the table?
I have done one more thing I imported(using pgdump) the 2nd table(which have 
the hang issue) in another DB.

In the 2nd DB it is working fine.

Regards
Tarkeshwar

From: Niklas Andersson
Sent: 09 July 2014 15:21
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Well...in that case I would export the database schema and run a diff on the 
tables.

If the structure is identical, the only thing left is the actual naming. If you 
have non-ascii characters or some unicode-stuff, doing a diff would tell you 
that. Sometimes it is hard to see issues like that with the eye.

Regards,
Niklas
________________________________
From: M Tarkeshwar Rao
Sent: Wednesday, 09 July 2014 11:46 AM
To: Niklas Andersson; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs
It just a plain insert statement. We are not using triggers in it.
Just a simple table with 6 columns one integer and rest are varchar(80) fields.

We are firing insert from command prompt. Two tables with same structure and 
same data.

Insert on one table working fine but for 2nd table it is going in hanging state.

Regards
Tarkeshwar

From: Niklas Andersson
Sent: 09 July 2014 15:03
To: M Tarkeshwar Rao; Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs

Can it be a trigger [1] or some PL/SQL statement that hangs? If the table 
structure is identical that is?

[1] http://www.postgresql.org/docs/9.0/static/trigger-definition.html

Regards,
Niklas
________________________________
From: 
postgres-discuss-boun...@mailman.lmera.ericsson.se<mailto:postgres-discuss-boun...@mailman.lmera.ericsson.se>
 [postgres-discuss-boun...@mailman.lmera.ericsson.se] on behalf of M Tarkeshwar 
Rao
Sent: Wednesday, 09 July 2014 11:13 AM
To: Leo Zhou; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: Re: [postgres-discuss] Insert query hangs
Hi Leo,


We have a table in a database DB1 with name Test. We imported this database 
from another machine.
When I fire insert statement it is going in the hang state.

Then I created another table with same structure and with same data within it 
as in table Test.
Then I fired the insert statement. It is working fine.

I am not able find the reason for this. Can you please help me out on this. 
This scenario easily reproducible.

I have a standalone system and postgresql  9.1 installed on it.

Regards
Tarkeshwar

From: Leo Zhou
Sent: 19 June 2014 15:40
To: M Tarkeshwar Rao; 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: RE: [postgres-discuss] Insert query hangs


Hi Tarkeshwar,



   According to my experience, if you are using standalone PG server, it could 
be transaction confliction. For example one session insert data into table, but 
other session lock table or change table.

  You can check the pg_lock system view. Refer to : 
https://wiki.postgresql.org/wiki/Lock_Monitoring.





BRs,

Leo





From: 
postgres-discuss-boun...@mailman.lmera.ericsson.se<mailto:postgres-discuss-boun...@mailman.lmera.ericsson.se>
 
[mailto:postgres-discuss-boun...@mailman.lmera.ericsson.se]<mailto:[mailto:postgres-discuss-boun...@mailman.lmera.ericsson.se]>
 On Behalf Of M Tarkeshwar Rao
Sent: Thursday, June 19, 2014 5:13 PM
To: 
postgres-disc...@mailman.lmera.ericsson.se<mailto:postgres-disc...@mailman.lmera.ericsson.se>
Subject: [postgres-discuss] Insert query hangs
Importance: High


Hi,



Insert query hangs, what could be the reason. Is there any way to find out?

Any timeout feature is there with query which can be set at client or server 
end?



We need Postgres debug steps. How can we set the debugging option?



Regards

Tarkeshwar

Reply via email to