Re: playing with catalog tables limits? dangers? was: seq bug 2073 and time machine

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: playing with catalog tables limits? dangers? was: seq bug 2073 and time machine
Date: 2008-08-26 07:29:24
Message-ID: 20080826092924.7a41523d@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, 25 Aug 2008 21:57:52 +0200
Joris Dobbelsteen <joris(at)familiedobbelsteen(dot)nl> wrote:

> The 'regular interface', that you will find in the documentation,
> ensures that the assumptions remain valid. It will not allow
> changes that cause these assumption to become invalid.
> (As a side note: you might see conditions where the assumption are
> violated when using the regular interface: these will be called
> bugs).

> Most of the assumptions are undocumented, but if they happen to be
> documented, there is little reason to actually assume they will
> remain valid over different versions (even between e.g. 8.3 and
> 8.3.1, though that will probably happen, but for other reasons).
> They are not intended to be used by regular users, rather by the
> system itself.

Stability wouldn't be a problem. I was just thinking to directly
accessing the catalog for refactoring.
eg. I need to temporarily disable a set of constraint or I need to
globally change the name of a column or get rid of serial in a set
of columns or change the signature of a function...

But stability have an effect on documentation...

If I had to mass change the db (schema, functions, constraints..) at
the moment I can think about these options:
- use a script that parse my SQL code and write another SQL script
to achieve the change
- backup and use sed
- use a script that read the catalog and generate the needed SQL

Sometimes the "safety net" could be a burden.

[snip]

> However there are cases where strange and unexpected things happen
> and editing the catalog directly can get the database server back
> into a proper/consistent state. You seem to have encountered such
> a situation.

I think such situations are rare... and I can ask the list ;)

I was thinking if learning how the catalog works could open some
refactoring path I would generally consider too expensive.
pgadmin3 let you retrieve the schema, the definition of functions,
dependencies. psql \d doesn't list the dependencies (yeah it does
somehow) and it is scriptable... somehow...
I was looking for something that could work like grep and sed
(actually some languages have more complex refactoring tools) on DB
definition, without having to use grep and sed on a plain text
backup.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2008-08-26 07:39:25 Re: Easy upgrade on Cpanel *without* downtime
Previous Message Chris Sano 2008-08-26 07:06:55 determining existence of database and language