Обсуждение: BUG #13513: Turning a table into a view

The following bug has been logged on the website:

Bug reference:      13513
Logged by:          Piergiorgio Buongiovanni
Email address:      p.buongiovanni@net-international.com
PostgreSQL version: 9.2.13
Operating system:   Centos 6.4 (Final)
Description:

I create the table MyTable with a foreign key. In a second time I drop that
foreign key (using ALTER TABLE command) in order to turn the table MyTable
into a view, using the command: CREATE RULE "_RETURN" AS ON SELECT TO
MyTable DO INSTEAD...
The server doesn't allow me to esecute this command as it tells me that
there is a foreign key or a trigger on the table MyTable.
The problem does not occur if I create the table MyTable without foreign
keys at the beginnning.

On 2015-07-22 08:25:38 +0000, p.buongiovanni@net-international.com wrote:
> I create the table MyTable with a foreign key. In a second time I drop that
> foreign key (using ALTER TABLE command) in order to turn the table MyTable
> into a view, using the command: CREATE RULE "_RETURN" AS ON SELECT TO
> MyTable DO INSTEAD...
> The server doesn't allow me to esecute this command as it tells me that
> there is a foreign key or a trigger on the table MyTable.
> The problem does not occur if I create the table MyTable without foreign
> keys at the beginnning.

Why are you converting tables to views like that? That's really only
supported to break dependency cycles when restoring dumps...

The "problem" is that we don't reset relhastriggers (and some other
fields) when dropping them, which normally is fine.

Regards,

Andres

Hi Andres,

thank you very much for your email.

Just only to answer to your question, we are doing some tests with our RAD =
(Instant Developer) and we are trying to "cheating" it.
PostgreSQL is very powerful allowing us to use views as tables through the =
RULE mechanism.
Well. Our RAD doesn't treat views as tables (probably the problem is the JD=
BC driver) and we cannot insert data into a view.
So we create a table, then we import that table into the project and the RA=
D reads all the info (constraints, default values, etc.) and creates the fi=
elds correctly.
At the end we turn the table into a view simply using rules.

However I understand your explanation below and I agree with you.

Thanks very much

Regards

Piergiorgio



----- Messaggio originale -----
Da: "Andres Freund" <andres@anarazel.de>
A: "p buongiovanni" <p.buongiovanni@net-international.com>
Cc: pgsql-bugs@postgresql.org
Inviato: Mercoled=C3=AC, 22 luglio 2015 10:59:06
Oggetto: Re: [BUGS] BUG #13513: Turning a table into a view

On 2015-07-22 08:25:38 +0000, p.buongiovanni@net-international.com wrote:
> I create the table MyTable with a foreign key. In a second time I drop th=
at
> foreign key (using ALTER TABLE command) in order to turn the table MyTabl=
e
> into a view, using the command: CREATE RULE "_RETURN" AS ON SELECT TO
> MyTable DO INSTEAD...
> The server doesn't allow me to esecute this command as it tells me that
> there is a foreign key or a trigger on the table MyTable.
> The problem does not occur if I create the table MyTable without foreign
> keys at the beginnning.

Why are you converting tables to views like that? That's really only
supported to break dependency cycles when restoring dumps...

The "problem" is that we don't reset relhastriggers (and some other
fields) when dropping them, which normally is fine.

Regards,

Andres

Hi,

On 2015-07-22 11:53:16 +0200, Piergiorgio Buongiovanni wrote:
> Just only to answer to your question, we are doing some tests with our
> RAD (Instant Developer) and we are trying to "cheating" it.

Hah.

> PostgreSQL is very powerful allowing us to use views as tables through the RULE mechanism.
> Well. Our RAD doesn't treat views as tables (probably the problem is the JDBC driver) and we cannot insert data into
aview. 
> So we create a table, then we import that table into the project and the RAD reads all the info (constraints, default
values,etc.) and creates the fields correctly. 
> At the end we turn the table into a view simply using rules.

You can probably work around the problem by inserting a VACUUM
tbl_becoming_view; before the CREATE RULE. IIRC that should "recompute"
relhastriggers.

Regards,

Andres

Andres Freund <andres@anarazel.de> writes:
> You can probably work around the problem by inserting a VACUUM
> tbl_becoming_view; before the CREATE RULE. IIRC that should "recompute"
> relhastriggers.

Dunno, I think vacuum does update relhasindexes, but there would be no
reason for it to think about relhastriggers.

As you said upthread, this is not considered a supported feature; it's
something we accept in a very narrow set of cases for pg_dump's benefit.

            regards, tom lane

On 2015-07-22 09:42:15 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > You can probably work around the problem by inserting a VACUUM
> > tbl_becoming_view; before the CREATE RULE. IIRC that should "recompute"
> > relhastriggers.
>
> Dunno, I think vacuum does update relhasindexes, but there would be no
> reason for it to think about relhastriggers.

I still didn't test, but there appears to be code for relhastriggers:

void
vac_update_relstats(Relation relation,
                    BlockNumber num_pages, double num_tuples,
                    BlockNumber num_all_visible_pages,
                    bool hasindex, TransactionId frozenxid,
                    MultiXactId minmulti,
                    bool in_outer_xact)
{
...
        if (pgcform->relhastriggers && relation->trigdesc == NULL)
        {
            pgcform->relhastriggers = false;
            dirty = true;
        }


Andres