Versioning Schema/Stored Procedures

The way I handle this is to version the entire schema and have scripts
that know how to upgrade from one version to another. If you think about
it, you really want/need everything in the database to be designed to
run together anyway. I've yet to find a case where I'd want some of the
stuff in the schema to be older than other stuff.

case where it makes sense to ha
On Fri, Dec 16, 2005 at 02:41:58PM -0800, vishal saberwal wrote:

hi all,

We installed a first version (1.0.0.1) of our schema. then came a few
patches we had for a few stored procedures and tables (1.0.0.2). Then even
more (1.0.0.3) (1.0.0.4). Some chose to upgrade to version 1.0.0.3 and stick
to it, while some others chose to upgrade to 1.0.0.4.

Now when i have some more schema updates, how should i find out what
(incremental) updates the client needs?

One way might be to store [ 'version', 'schema', 'Date_time_change',
'User_who_Changed' ] in a table. But i want to store the versions for each
table/stored procedures/views. I could create a table to store these
components with similar details. But i do not want to duplicate the work. I
just want to upgrade the components i need to, so as to avoid downtime for
teh applications taht do not need the component.

Is it possible to modify pg_class to have another 'version' column so that i
can version each relation and other components?
Is there a better way to do schema versioing to the level of tables, stored
procedures and views?

thanks,
vish

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

So, is there no way i can version the stored procedures or tables.
Has anyone done anything similar.
Does any system table have a description or any field i could store the
version for the tables/stored procedures/views.

thanks jim, but i think, If its just 20% tables changing and that two in a
sub-schema storing person details for example, i might not want to have
downtime on other applications that are not using person sub-system.

thanks in advance,
vish

On Mon, Dec 19, 2005 at 08:35:14AM -0800, vishal saberwal wrote:

So, is there no way i can version the stored procedures or tables.
Has anyone done anything similar.
Does any system table have a description or any field i could store the
version for the tables/stored procedures/views.

No, there's not. And I don't think there's enough demand to warrant
putting support for something like this in the backend.

thanks jim, but i think, If its just 20% tables changing and that two in a
sub-schema storing person details for example, i might not want to have
downtime on other applications that are not using person sub-system.

Well, then setup a table so that you can store version info for specific
components/schemas/what-have-you.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461