BUG #16730: Create table like with inheritance and self referencing index

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16730: Create table like with inheritance and self referencing index

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16730
Logged by:          Sofoklis Papasofokli
Email address:      [hidden email]
PostgreSQL version: 10.15
Operating system:   PostgreSQL 10.15 (Ubuntu 10.15-1.pgdg20.04+1) on
Description:        

The following simplified example used to work on previous versions, but not
on 10.15, we tested it again on version 10.11 and it works.

We read in the release notes of 10.15 that there is a change when using like
with inheritance, maybe it is related.


CREATE TABLE id_base
(
    id SERIAL PRIMARY KEY

);


CREATE TABLE inherited_table
(
    LIKE id_base INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING
INDEXES,


    "self_ref_if" INT,

    CONSTRAINT self_ref_id_fk FOREIGN KEY ("self_ref_if")
        REFERENCES inherited_table (id) MATCH SIMPLE
        ON UPDATE RESTRICT ON DELETE RESTRICT
)
    INHERITS (id_base);

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16730: Create table like with inheritance and self referencing index

Tom Lane-2
PG Bug reporting form <[hidden email]> writes:
> The following simplified example used to work on previous versions, but not
> on 10.15, we tested it again on version 10.11 and it works.

Hmph.  Apparently I was too careless about the order of execution of
the sub-commands created by a LIKE clause.

I pushed this patch:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=513db7b7004c8d1fa15dea4747a32cbc8f9621ef

which takes care of the particular case you show, but now I wonder
if there are other issues lurking nearby.  If you're in a position
to test this and see if it fixes your real application, please do.

Thanks for the report!

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16730: Create table like with inheritance and self referencing index

Sofoklis Papasofokli
Hi Tom,

Thanks for the quick response.

I have not done a patch before, I will try to figure it out over the weekend and get back to you on how it works for our exact scenario.

Thanks again!

Best regards,
Sofoklis

On Thu, Nov 19, 2020 at 10:10 PM Tom Lane <[hidden email]> wrote:
PG Bug reporting form <[hidden email]> writes:
> The following simplified example used to work on previous versions, but not
> on 10.15, we tested it again on version 10.11 and it works.

Hmph.  Apparently I was too careless about the order of execution of
the sub-commands created by a LIKE clause.

I pushed this patch:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=513db7b7004c8d1fa15dea4747a32cbc8f9621ef

which takes care of the particular case you show, but now I wonder
if there are other issues lurking nearby.  If you're in a position
to test this and see if it fixes your real application, please do.

Thanks for the report!

                        regards, tom lane


--
Sofoklis
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16730: Create table like with inheritance and self referencing index

Sofoklis Papasofokli
Hi Tom,

Just writing to confirm that i managed to patch the 10.15 version with your fix and run all our scripts.
Everything runs normally.

Thanks for the great work!

On Fri, Nov 20, 2020 at 12:32 PM Sofoklis Papasofokli <[hidden email]> wrote:
Hi Tom,

Thanks for the quick response.

I have not done a patch before, I will try to figure it out over the weekend and get back to you on how it works for our exact scenario.

Thanks again!

Best regards,
Sofoklis

On Thu, Nov 19, 2020 at 10:10 PM Tom Lane <[hidden email]> wrote:
PG Bug reporting form <[hidden email]> writes:
> The following simplified example used to work on previous versions, but not
> on 10.15, we tested it again on version 10.11 and it works.

Hmph.  Apparently I was too careless about the order of execution of
the sub-commands created by a LIKE clause.

I pushed this patch:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=513db7b7004c8d1fa15dea4747a32cbc8f9621ef

which takes care of the particular case you show, but now I wonder
if there are other issues lurking nearby.  If you're in a position
to test this and see if it fixes your real application, please do.

Thanks for the report!

                        regards, tom lane


--
Sofoklis


--
Sofoklis
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16730: Create table like with inheritance and self referencing index

Tom Lane-2
Sofoklis Papasofokli <[hidden email]> writes:
> Just writing to confirm that i managed to patch the 10.15 version with your
> fix and run all our scripts.
> Everything runs normally.

Great, thanks for testing!

                        regards, tom lane