Thread: seq bug 2073 and time machine

I was trying to drop a serial.
Dropped the default for a column.
Now it seems I can't drop the sequence since I incurred in:

http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php

Is there a way I can still delete the sequence without using a
backup?

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

Ivan Sergio Borgonovo wrote:
> I was trying to drop a serial.
> Dropped the default for a column.
> Now it seems I can't drop the sequence since I incurred in:
>
> http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php
>
> Is there a way I can still delete the sequence without using a
> backup?

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 the
sequence in that same transaction, so that if you mess up the catalogs
too badly you can get out of it by rolling back.

In recent releases (I think 8.2 and beyond) you can use ALTER SEQUENCE
... OWNED BY to fix the problem without messing with the catalogs
directly.  This is obviously recommended if available.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Sun, 24 Aug 2008 17:26:24 -0400
Alvaro Herrera <alvherre@commandprompt.com> wrote:

> Ivan Sergio Borgonovo wrote:
> > I was trying to drop a serial.
> > Dropped the default for a column.
> > Now it seems I can't drop the sequence since I incurred in:

> > http://archives.postgresql.org/pgsql-bugs/2005-11/msg00304.php

> > Is there a way I can still delete the sequence without using a
> > backup?

> 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.

> the sequence in that same transaction, so that if you mess up the
> catalogs too badly you can get out of it by rolling back.

Fortunately that pk was referenced just once, so I copied the
content of the table elsewhere, dropped a constraint, dropped the
table and move the content in another one, moved the content back to
a table without serial, recreate the constraint.
Your method is simpler, should be faster and avoid to drop a
constraint but without understanding what's is going to happen
behind the scene it is hard for me to understand if it is safer as
well.

Of course modifying the catalog tables to modify the schema is not
going to be portable across DB... but what about risks and other
limits?

I'm thinking to access the catalog for eg. disabling/dropping a set
of constraint and reenabling/creating them back without the need to
do bookkeeping on the code or writing a script etc...

thanks

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

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.
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.

            regards, tom lane

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?
Isn't the knowledge about how catalog stuff maps on SQL to "guess"
how to achieve certain results?

> 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?

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

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

thanks

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

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

On Mon, 25 Aug 2008 21:57:52 +0200
Joris Dobbelsteen <joris@familiedobbelsteen.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