[Postgres-xl-general] Can't drop database

Aaron Jackson ajackson at revionics.com
Fri Sep 19 22:05:17 PDT 2014

There is something I didn't account for.  I am now using two datanodes per server.  Is it possible that they somehow have overlapping shared memory usage?  The dropdb() function does call CountOtherDBBackends() - and I can't come up with any other reason why it would happen to find another database backend other than the two are counting each other.

It might also explain why the datanodes refuse to drop at all.

From: Aaron Jackson [ajackson at revionics.com]
Sent: Friday, September 19, 2014 11:51 PM
To: Mason Sharp
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Can't drop database

This problem appears to have reappeared.  Simple process that uses about 4 threads to load data into the database concurrently.  At one point, the load failed due to timeout and I began to query pg_stat_activity on the underlying datanodes.

All 6 datanodes had the following query reported ...


Coord2 and Coord3 also reported the same query.  Coord1 had no such query running.

When I tried to drop the database I was told there was 1 prepared transaction using the database.  Now, I will go through and hunt down the errant node and reset it but this is exactly what happened last time.  And it only appears to happen when I'm doing dataload via copy on more than one table concurrently.

From: Mason Sharp [msharp at translattice.com]
Sent: Wednesday, August 20, 2014 4:19 PM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Can't drop database

On Tue, Aug 5, 2014 at 12:42 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
One last note,

This appears to be have been related to the population routine I've written.  To my knowledge, there's nothing special about it other than it attempt to load multiple streams concurrently.  Aside from being a convoluted mass of javascript (it runs under node.js) - I don't see anything that would cause this to be a problem.  It looks for relationships in the tables and then given a directory with a bunch of CSV, attempts to load the files in order of their assumed dependence.  However, I have no had 3 successive attempts result in a database that is stuck in a 2PC - so it's not random and it's definitely repeatable.

So, for example, you have a directory with the following files:


And corresponding tables.  Assuming that B has a FK relationship with C, it will load as follows:

A|C -> B

Where A and C are done in parallel and B is done after C has completed.  We use the COPY command and to my knowledge, nothing else is special.

Can you please clarify this?  C refers to B, but you load C first? I am assuming that means you only load C, but do not yet create the foreign key constraint to B? Or did you actually mean that B refers to C?

Anyway, the loading in parallel should not matter. We can try to reproduce this if you clarify.

Can you provide any other details, like, is B replicated? All are hash distributed on their primary key, etc.?



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-general-postgres-xl.org/attachments/20140920/91f249e9/attachment.htm>

More information about the postgres-xl-general mailing list