On Jun 29, 3:45 am, armstrong.w...@xxxxxxxxx (Whit Armstrong) wrote: > Thanks, Tom. > > Let me give a little more detail on my actual data rather than the > simple example I sent. > > I have a 60GB table of loan balances, which I've partitioned into 26 tables. > > The loan id's are a sequence of 6 characters, so the partitioning rule > I've used is the first character of the loan id, which yields roughly > equal sized partitions of 2.8 GB or so. > > Each loan can only have one balance per month, so the primary key on > each partition is set to be loan_id and asofdate. > > However, this data is meant to be available via a rails application, > hence, the need for a surrogate key of integers which is unique across > the entire set of partitions. > > Creation of new rows in the partitioned tables should not be an issue > under normal circumstances because I see that all of the child tables > use the same sequence for generating new id's. > > However, what makes me nervous is that there is no explicit constraint > in the database that prevents duplicate id's from being created, and > I'm not sure how the rails app would react if for whatever reason > duplicate id keys wound up in the table. > > Any suggestions? > > Thanks, > Whit > > > > On Sun, Jun 28, 2009 at 1:27 PM, Tom Lane<t...@xxxxxxxxxxxxx> wrote: > > Whit Armstrong <armstrong.w...@xxxxxxxxx> writes: > >> I have a simple example copied from the 8.3 manual on partitioning > >> (http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html). > > >> My question is, if you create a serial type in the parent table which > >> is meant to be the primary key across all the partitions, how does one > >> guarantee uniqueness of that key? > > > One doesn't. That is not an appropriate way to set up a partitioned > > table. You need a primary key that can actually be used as a meaningful > > partitioning key. In this example, the id is completely useless and > > what you should be looking at is making the data_value be the primary > > key. > > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-gene...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Whit, you could consider using GUIDs instead of Integer for primary keys. Here is a nice blog post explaining how to do this in Rails: http://ariejan.net/2008/08/12/ruby-on-rails-uuid-as-your-activerecord-primary-key/ Best Regards, Arndt Lehmann -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
