Обсуждение: Insert query hangs

Hi all,

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

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
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of M Tarkeshwar Rao
Sent: 09 July 2014 14:49
To: pgsql-hackers-owner@postgresql.org; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
Subject: [GENERAL] Insert query hangs

Hi all,

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jul 9, 2014 at 11:34 AM, M Tarkeshwar Rao
<m.tarkeshwar.rao@ericsson.com> wrote:
>
> 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?

REINDEX is useful for indexes.

From the sounds of it you had an uncommitted transaction that had
inserted a conflicting id. If you were able to drop the index the
other transaction had committed (or aborted) by that time.

It's possible there was corruption but that doesn't usually lead to a
hang and you haven't provided enough information to think that was the
case.


--
greg

Hi Greg,

Sending few discussions with one of the postgres person. You will understand what is the problem.

Regards
Tarkeshwar 



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-discuss@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
othertransactions when the second table scan begins
 
 
 
Regards
Tarkeshwar
 
 
From: Niklas Andersson 
Sent: 09 July 2014 16:10
To: M Tarkeshwar Rao; Leo Zhou; postgres-discuss@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
havethe time to rebuild the index at the speed you're sending inserts. But then the app still wouldn't hang, but almost
grindto 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-discuss@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-discuss@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
importthe 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-discuss@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-discuss@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-discuss@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-discuss@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-bounces@mailman.lmera.ericsson.se [postgres-discuss-bounces@mailman.lmera.ericsson.se] on behalf
ofM Tarkeshwar Rao
 
Sent: Wednesday, 09 July 2014 11:13 AM
To: Leo Zhou; postgres-discuss@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-discuss@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
onesession 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-bounces@mailman.lmera.ericsson.se [mailto:postgres-discuss-bounces@mailman.lmera.ericsson.se] On
BehalfOf M Tarkeshwar Rao
 
Sent: Thursday, June 19, 2014 5:13 PM
To: postgres-discuss@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


-----Original Message-----
From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf Of Greg Stark
Sent: 09 July 2014 16:42
To: M Tarkeshwar Rao
Cc: pgsql-general@postgresql.org
Subject: Re: Insert query hangs

On Wed, Jul 9, 2014 at 11:34 AM, M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote:
>
> 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?

REINDEX is useful for indexes.

From the sounds of it you had an uncommitted transaction that had inserted a conflicting id. If you were able to drop
theindex the other transaction had committed (or aborted) by that time.
 

It's possible there was corruption but that doesn't usually lead to a hang and you haven't provided enough information
tothink that was the case.
 


--
greg

On 07/09/2014 03:34 AM, M Tarkeshwar Rao wrote:

Please do not top post.

>
> 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.

So you are just copying a directory from one machine to another?

Was the production machine running while they copied the directory?

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

So you then put the copied folder into an existing data directory on
your machine?

Are the production machine and your machine the same OS and
architecture(32/64 bit)?

Are the Postgres versions the same?

>
> 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.

So what happens if you do a pg_dump directly from the production machine
or is that what you are talking about above?

>
> 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
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of M Tarkeshwar Rao
> Sent: 09 July 2014 14:49
> To: pgsql-hackers-owner@postgresql.org; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
> Subject: [GENERAL] Insert query hangs
>
> Hi all,
>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jul 9, 2014 at 1:25 PM, M Tarkeshwar Rao
<m.tarkeshwar.rao@ericsson.com> wrote:
>
> 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

Hum, that is interesting.

So to sum up, your database works fine as long as the index isn't
present but you want to enforce this unique constraint. What version
of Postgres is this precisely (9.1.what?) Has it ever been upgraded
using pg_upgrade? Has it suffered a crash or been restored from
backups? What of Adrian Klaver's question about the database being
copied?

When you run create index you get this error? Please paste the actual
command and actual error messages. It should contain more information
than that. Try doing it with this set first:
 \set VERBOSITY 'verbose'

When you're getting these errors please run select * from
pg_stat_activity and paste that information -- remove any confidential
information but leave any INSERT, UPDATE, DELETEs on the table you're
indexing with just the confidential data removed.

Also, I don't think this is right but perhaps it could be a prepared
transaction? Try "select * from pg_prepared_xacts"?

If it's really corruption --which still seems a bit odd to me-- You
might find a simple vacuum or vacuum freeze on the table fixes up the
problem.

--
greg