Search Postgresql Archives

Re: creating a function with another function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Oct 12, 2009 at 03:36:47PM -0500, Dave Huber wrote:
> > Surely, there are valid cases of having a function create a
> > function.  Suppose (just off the top of my head), you create a
> > helper function that generates triggers on a table for record
> > archiving.
> 
> My application is for archiving.  I'm using partitioned tables (each
> 100000 records)

That seems awfully small.  Are the rows really wide?

> to keep a rolling archive of the last 10 million.  I already need to
> setup a trigger function for inserting into the latest partition.
> What I'd like to do is automate the process of creating a new table
> with a name based off the record ID, a bigserial that is used to
> defining each partition.  Whether its automated or not, every 100000
> record inserts will require 3 actions:
> 
> 1. create a new partition,

This part is just begging for nasty race conditions.  It's better to
pre-create your partitions.

> 2. modify the insert trigger function, and

That, you could do with CREATE OR REPLACE.

> 3. truncate and drop the oldest partition.

If you're going to drop it, you don't need to truncate, and /vice versa/.

> A number of things complicate the process for me.  I'm using a COPY
> statement to bulk insert a varying number of records that can occur
> at different intervals of time.  Also, the application starts and
> stops at various times, so it must be able to initialize based on
> the state of the database on startup.
> 
> Is there a way to dynamically create table names within a function
> based on a numeric value?  I want to be able to name the partitions
> log_1, log_2, ..., log_x, where x is the record ID / 100000.

For the reason I stated earlier, on-demand DDL is not a great idea.
You can write a program in whatever language is convenient to generate
the CREATE TABLE statements and feed that in.

> I would also have to be able to truncate the same way, by
> dynamically specifying the name of the table to be deleted.

You could make an SQL function that executes a TRUNCATE.

> Well, I appreciate the help, anyway.

More help is available :)

Cheers,
David.

P.S.  Thanks for giving more context to what you're doing.  It's
always handy to have some of that :)
-- 
David Fetter <david@xxxxxxxxxx> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@xxxxxxxxx

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux