[Pgcluster-general] Major problem, multiple thread update with placeholders hangs

[Hans-Juergen Schoenig]

good morning ...

this seems like a problem we had when we forked off Cybercluster and  
did some concurrency problem.
if it is the same thing i think we fixed that. i am not sure the fix  
can be ported back to PGCluster however easily.
if i remember correctly it might have been some locking issue.

	many thanks,

		hans



On Jan 31, 2008, at 9:48 AM, Tommi Berg wrote:

> Yet again hello,
>
> This time I discovered a major problem. Using perl DBD::Pg and running
> simple INSERT (or UPDATE) to a table with 2 or more forks with
> placeholders it always hangs. Is there any solution for this or does
> this finally make pgcluster 1.7.0 unusable? Is there any version
> available which could be used at production?
>
> Example:
> Table:
> create table t1 (c1 integer);
>
> This works: my $r = $dbh->do("INSERT INTO t1 (c1) VALUES ($id)");
> This fails: my $r = $dbh->do("INSERT INTO t1 (c1) VALUES (?)",  
> undef, $id);
>
> The test was run on loadbalancer and first thread tried to insert
> clusterdb1 and second at clusterdb2. The both inserts seems to be
> successfully inserted at clusterdb3, but 1 and 2 hasn't done either.
> Processlist at clusterdb2 shows two insert queries and clusterdb1  
> shows
> one. On clusterdb2 one stament is at stage "BIND" and other at "PARSE
> waiting".
>
> Running the same at single thread works.
>
> Pgreplicate debug loops with following message:
> 2008-01-31 10:38:42 [1444] DEBUG:PGRdo_replicate():query :: P
> 2008-01-31 10:38:42 [1444] DEBUG:cmdSts=P
> 2008-01-31 10:38:42 [1444] DEBUG:cmdType=D
> 2008-01-31 10:38:42 [1444] DEBUG:rlog=3
> 2008-01-31 10:38:42 [1444] DEBUG:port=5432
> 2008-01-31 10:38:42 [1444] DEBUG:pid=7767
> 2008-01-31 10:38:42 [1444] DEBUG:from_host=clusterdb2
> 2008-01-31 10:38:42 [1444] DEBUG:dbName=toba
> 2008-01-31 10:38:42 [1444] DEBUG:userName=toba
> 2008-01-31 10:38:42 [1444] DEBUG:recieve sec=1201768722
> 2008-01-31 10:38:42 [1444] DEBUG:recieve usec=143572
> 2008-01-31 10:38:42 [1444] DEBUG:query_size=3
> 2008-01-31 10:38:42 [1444] DEBUG:request_id=3
> 2008-01-31 10:38:42 [1444] DEBUG:replicate_id=0
> 2008-01-31 10:38:42 [1444] DEBUG:recovery_status=0
> 2008-01-31 10:38:42 [1444] DEBUG:sem_lock [1] req
> 2008-01-31 10:38:42 [1445] DEBUG:PGRdo_replicate():query ::
> 2008-01-31 10:38:42 [1445] DEBUG:cmdSts=P
> 2008-01-31 10:38:42 [1445] DEBUG:cmdType=S
> 2008-01-31 10:38:42 [1445] DEBUG:rlog=3
> 2008-01-31 10:38:42 [1445] DEBUG:port=5432
> 2008-01-31 10:38:42 [1445] DEBUG:pid=30694
> 2008-01-31 10:38:42 [1445] DEBUG:from_host=clusterdb1
> 2008-01-31 10:38:42 [1445] DEBUG:dbName=toba
> 2008-01-31 10:38:42 [1445] DEBUG:userName=toba
> 2008-01-31 10:38:42 [1445] DEBUG:recieve sec=1201768722
> 2008-01-31 10:38:42 [1445] DEBUG:recieve usec=183625
> 2008-01-31 10:38:42 [1445] DEBUG:query_size=1
> 2008-01-31 10:38:42 [1445] DEBUG:request_id=5
> 2008-01-31 10:38:42 [1445] DEBUG:replicate_id=0
> 2008-01-31 10:38:42 [1445] DEBUG:recovery_status=0
> 2008-01-31 10:38:42 [1445] DEBUG:sem_lock [1] req
> _______________________________________________
> Pgcluster-general mailing list
> Pgcluster-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgcluster-general



--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgcluster-general/attachments/20080131/bbac2f10/attachment.html