Re: playing with catalog tables limits? dangers? was: seq bug 2073 and time machine - Mailing list pgsql-general

Ivan Sergio Borgonovo wrote, On 25-Aug-2008 18:48:
> On Mon, 25 Aug 2008 12:07:23 -0400
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
>>> Alvaro Herrera <alvherre@commandprompt.com> wrote:
>>>> If you're feeling corageous, you can remove the pg_depend
>>>> entries for that sequence.  Make sure to try it in a
>>>> transaction and drop
>>> I'd like to understand better the risks of being courageous?
>>> I think my life would be easier if I'd know when it is safe to
>>> put hands in the system tables.
>> Well, it's safe if (a) you know what you're doing, (b) you don't
>> make any mistakes, and (c) you don't forget any changes needed to
>> keep all the catalogs consistent.
>>
>> You can protect yourself against (b) by using a transaction, but
>> the other two tend to require hacker-grade knowledge of how the
>> backend works, so we try to discourage people from doing it.
>
> Why hacker-grade knowledge of the backend?
> With "hacker-grade" you mean: undocumented or RTSL?

The issue is that 'directly editing the system tables' has NO safety net
to protect you. You can do everything, even causing assumptions that the
software will make to become invalid. In general this causes any
combination of data corruption, server crashes and/or other bad things.

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.

What Tom calls "hacker-grade" knowledge is that you know what the
assumptions are and how you can ensure that you will not violate them.
In general, several hackers that work with the system catalogs, probably
know quite a few of them. Its not something that the DBA should know,
the commands in the documentation will provide that kind of protection.

> Isn't the knowledge about how catalog stuff maps on SQL to "guess"
> how to achieve certain results?

This "maps" is dependent on the actual implementation you are running.
(I did have trouble understanding the actual question here).

>> pg_depend in particular tends to have rather obscure contents,
>> and what's worse is that messing it up usually doesn't have any
>> immediately-obvious consequences.
>
> OK... what about concurrent works?
> eg. supposing I write the correct SQL should I take care to be the
> only one accessing the DB in that moment?

Depends on what you are doing. This is an instance what the regular
interface enforces. For safety, its probably a good idea to be the only
one, but its not a requirement.

> What could be the use case of directly accessing the catalog?

Ideally, NONE!

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.

The main reason for having the option to enable 'direct catalog access'
is to allow developers to add or modify features and test them while
they are partially implemented. For example, the feature exists in the
backend, but no-one implemented a command to actually turn it on or
modify its parameters. This way the feature can already be tested, while
they are discussing which commands should be provided to the regular users.

> I'd like to have an idea if it is something to invest my time in.
> My main interest would be refactoring.

I don't get what you are asking here...

Hope this helps.

Regards,

- Joris