I wish to have simpler solution too :)
The reason I want to use "complicated" table partition way is that :
(1) I have a few thousands lot's row
(2) Each lot will associate with millions row of unit.
(3) If I use a single unit tables, my unit tables will have billions of row :o
The design works fine (requirement/ performance/ reliability...). Only until the last minute we put it run under
multi-threadedenvironment before shipment, I shoot on my own foot :)
As this stored procedure isn't being called frequent (and it is not the performance bottle neck), I was thinking to
havea "hack" solution.
Having a mutual exclusive (process level) protection around the whole function. Each time, only allow one process to
executeget_existing_or_create_lot.
In Windows programming world, we call it mutex. But in PostgreSQL, what I shall use? LOCK command? But LOCK command
requireda dummy table.
FUNCTION get_existing_or_create_lot
BEGIN
LOCK dummy_table
...
...
END;$BODY$
Thanks and Regards
Yan Cheng CHEOK
--- On Mon, 4/26/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in partition.
> To: "Yan Cheng CHEOK" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.org
> Date: Monday, April 26, 2010, 2:04 PM
> Yan Cheng CHEOK <yccheok@yahoo.com>
> writes:
> > Currently, I have a stored
> procedure(get_existing_or_create_lot), which will be called
> by 2 or more processes simultaneously.
> > Every process will have a unique lot name. What the
> store procedure does it
>
> > 1) Insert lot name into "lot" table. A unique lot id
> will be returned after insertion into "lot" table.
>
> > 2) Check if unit_{id} table does exist. For example,
> if the returned lot id is 14, PostgreSQL will check whether
> "unit_14" table does exist. If no, "CREATE TABLE unit_14..."
> will be executed.
>
> > Unfortunately, I get the run time error ;
> > 2010-04-26 13:28:28 MYTERROR: deadlock
> detected
>
> The reason for the error is probably that establishing the
> FK reference
> to table "lot" requires an exclusive lock on "lot", so each
> occurrence
> of this creation will serialize on that, in addition to
> anything else
> it might be locking.
>
> My opinion is that you're shooting yourself in the foot
> with a poorly
> chosen database layout. Forget all the subtables and
> just have one
> big unit table. It'll be far simpler and probably
> perform better too.
>
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>