Re: pg_dump with postgis extension dumps rules separately

Lists: pgsql-hackers
From: Joe Conway <mail(at)joeconway(dot)com>
To: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pg_dump with postgis extension dumps rules separately
Date: 2013-04-06 23:49:01
Message-ID: 5160B46D.2060904@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

If I create a database and install postgis as an extension, and then run
pg_dump I get this:

[...]
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
[...]
CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO
INSTEAD NOTHING;
[...]

Shouldn't that CREATE RULE be implicitly part of the CREATE EXTENSION?

If so, is this a pg_dump bug, PostGIS bug, or pilot error?

FWIW I see CREATE OR REPLACE RULE statements in the PostGIS extension
SQL script.

Thanks,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-04-08 14:42:30
Message-ID: m28v4thynd.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> Shouldn't that CREATE RULE be implicitly part of the CREATE EXTENSION?

Yes. It's a bug, been reported before, it's on my todo list. I have
arranged some time to care about it while in beta, I won't be able to
have at it before then…

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-04-08 15:02:57
Message-ID: 5162DC21.3040600@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 04/08/2013 07:42 AM, Dimitri Fontaine wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> Shouldn't that CREATE RULE be implicitly part of the CREATE EXTENSION?
>
> Yes. It's a bug, been reported before, it's on my todo list. I have
> arranged some time to care about it while in beta, I won't be able to
> have at it before then…

OK, maybe I'll try to take a look in the meantime.

Did you have any comment on the other pg_dump patch (reviewed by Vibhor)?

Thanks,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-04-08 15:34:23
Message-ID: m2a9p9ghog.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> OK, maybe I'll try to take a look in the meantime.

That would be awesome :)

> Did you have any comment on the other pg_dump patch (reviewed by Vibhor)?

This whole extension table filtering and dumping is more in Tom's realm,
so I guess that if you want to have another pair of eyes on your patch
before commit'ing it yourself, you will need him to have a look.

That said, I didn't spot anything obvious that I want to report myself,
so I would label it "ready for commiter".

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-04-26 19:32:47
Message-ID: 517AD65F.8040108@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/08/2013 08:34 AM, Dimitri Fontaine wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> OK, maybe I'll try to take a look in the meantime.
>
> That would be awesome :)
>
>> Did you have any comment on the other pg_dump patch (reviewed by
>> Vibhor)?
>
> This whole extension table filtering and dumping is more in Tom's
> realm, so I guess that if you want to have another pair of eyes on
> your patch before commit'ing it yourself, you will need him to have
> a look.
>
> That said, I didn't spot anything obvious that I want to report
> myself, so I would label it "ready for commiter".

Committed back to 9.1

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRetZfAAoJEDfy90M199hl8tAP/1CpNniKJIQeZgye3RBY1l6I
TrO+ZEfXoh5EWRkx6uxB1hUm0mMdKIgAWVHIxbFXSaFkB1NW8oX8l1kme9fsVIV2
9xWEviccg/9+iJ/+8xdTCAn8VD4FjcjkXSayjPowekMia28IwHzEA+dww9LWHzvA
TzFC5RAApdWlm9LYAp2O+9U81mbUkXkIokwuMfX9a2jlH7AxCInyo9HvgilWRlkQ
/XcFBuSLqFHOles3f9QFM7ra5uq4Da3niYUkto7GJcOeT/jyFcApgi0mLGx6akpe
807S2F2dkNOCiJeyGe/ZhYV0n6YP2dQMYwa7Lhb1eQswT7VTLEVmw22LuVUWQLOU
WZhHlX5YkiUqkDCXSaAMPCXM/dFdmQanVgmhv2IehL7ZnBbPM4/AL1+GftV6OHpS
k6eogOhVEWOVkfNZ70K144IUocxcPPwPUyo25ov8jJpMqcRuxv43o4/nAITln5Fe
CF2cY+rGTsxJJEzO2m5rXkrA9WuisPLJeS97EZE1XbbDA6CwX++O8yBvbt61NEr0
JvtnkfxnPmMvFIDH7NyVxNgUoT/jh7IbeqjEVA2l1jkWawuRAQg10Woycj0S1+7w
J0nO2T8PJrLsue+Un+NcwZH38iL12a1a1IHTTD8IW4VCF+JCPzf4ka2nHWY/bjX8
D49hPm+YZ97OeifObvfP
=2Jbv
-----END PGP SIGNATURE-----


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-04-26 19:35:14
Message-ID: m2d2th2ih9.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> Committed back to 9.1

Thanks,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: postgres hackers <pgsql-hackers(at)postgresql(dot)org>, Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-28 21:59:20
Message-ID: 51A528B8.1050500@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/06/2013 04:49 PM, Joe Conway wrote:
> If I create a database and install postgis as an extension, and
> then run pg_dump I get this:
>
> [...] CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
> [...] CREATE RULE geometry_columns_delete AS ON DELETE TO
> geometry_columns DO INSTEAD NOTHING; [...]
>
> Shouldn't that CREATE RULE be implicitly part of the CREATE
> EXTENSION?
>
> If so, is this a pg_dump bug, PostGIS bug, or pilot error?
>
> FWIW I see CREATE OR REPLACE RULE statements in the PostGIS
> extension SQL script.

The attached one-liner seems to do the trick. It should probably be
backpatched to 9.1. Remaining questions:

1) Are there other database object types, likely to be included in
extension scripts, that are also lacking dependency records to
their extension?

2) How should we handle already installed extensions, which will still
lack dependency records after this bugfix?

Thanks,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRpSi4AAoJEDfy90M199hlzn4P/j2tgs35b2Y3YoMJHIRDUYmK
uihsKybUYN1uYlS58Igv04lhqWk4MMFFzfwvztENP2SzVysMkA7QoP0BIKy/lF+b
CWwouTLkygnU/a9Mj8TTXMc4YINp4zHOK/XKZaong6zIwCGIXtXp9acl6m7wDI1v
S2FkeRB2dJXyC/Vxv0n9p5JfW75KG6DadJa4ZlcsBx7yV1cwnmePLhoDvsX5fPro
BlD4pFV+GgyW8d65kZxuzIQ/Wy44o0f97yDdeZKi4mzEYooakWzl5iZN5idEBQ3i
LDgjwrCPvod0t8sYGSMaz9qc/fPpWAt4sPkwC6QOCE0u7PJnbZ0oGEGb0JBFGPBc
nV/1sib9KXRfALEUknKYALBqnFhZsaGOTFV9yKhtvscqn/Hmk0mXyocVB9rihcO6
7ipgOgpeqFsS7IQMtiFBUIFPl2ARtD01NKIHbDIKFTQPfss6XXTgIBYmT8W0ldaT
f2jxCEN5SzdCq/G3rx5Z2Dlqau3WIfYiSmWyAG/I2UDBtr7/J7TOSKoJh1+3ntvT
Vxc9b+z8dEz3wE143JOhi1aCNCQ7ybI/K44EhkLjSR4hC6CQiCKlI4OP5gaFj8FJ
YhxTe4FscYTYZVVguBTOKxMzrI1caIt+3LEJ3C7GTkTrQnYc/oZL4v86XlbV24ro
V8IUaO0XFeam7oDxYOZw
=d/qa
-----END PGP SIGNATURE-----

Attachment Content-Type Size
fix-extension-ruledeps.00.diff text/x-patch 526 bytes

From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 07:30:43
Message-ID: m2txlm6y58.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> The attached one-liner seems to do the trick. It should probably be
> backpatched to 9.1. Remaining questions:

Thanks for the patch (and testing, etc, that it entails)!

> 1) Are there other database object types, likely to be included in
> extension scripts, that are also lacking dependency records to
> their extension?

To be honest I'm quite surprised that we missed rules at all. I think
what happened is that for views we only track the pg_class entry they
have, and missed that you can still use rules in other contexts…

Thinking about it, what happens with your patch for an extension
providing views: I'd guess the RULE(s) are registered themselves as well
as the RULEs they build-on, and I don't know what to expect of the
pg_dump behavior in such case…

> 2) How should we handle already installed extensions, which will still
> lack dependency records after this bugfix?

I don't really see any other way here than providing an upgrade script
that will somehow re-attach those objects, either by directly messing
with pg_depends (that is, when there's a systematic way to get the OIDs
of the missing RULEs), or by maybe doing a drop/create on the RULEs?

Regards,
--
Dimitri Fontaine 06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: Joe Conway <mail(at)joeconway(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 10:31:09
Message-ID: 20130529103109.GA3955@alap2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2013-05-29 09:30:43 +0200, Dimitri Fontaine wrote:
> > 2) How should we handle already installed extensions, which will still
> > lack dependency records after this bugfix?
>
> I don't really see any other way here than providing an upgrade script
> that will somehow re-attach those objects, either by directly messing
> with pg_depends (that is, when there's a systematic way to get the OIDs
> of the missing RULEs), or by maybe doing a drop/create on the RULEs?

Couldn't ALTER EXTENSION ... ADD ...; be brought up to speed to support
this?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Joe Conway <mail(at)joeconway(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 12:52:46
Message-ID: m2obbu6j8h.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-05-29 09:30:43 +0200, Dimitri Fontaine wrote:
>> > 2) How should we handle already installed extensions, which will still
>> > lack dependency records after this bugfix?
>>
>> I don't really see any other way here than providing an upgrade script
>> that will somehow re-attach those objects, either by directly messing
>> with pg_depends (that is, when there's a systematic way to get the OIDs
>> of the missing RULEs), or by maybe doing a drop/create on the RULEs?
>
> Couldn't ALTER EXTENSION ... ADD ...; be brought up to speed to support
> this?

I'll blame the Time Zone Difference Recovering. I felt like missing
something…

Thanks, regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 14:35:42
Message-ID: 51A6123E.3080205@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/29/2013 05:52 AM, Dimitri Fontaine wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> On 2013-05-29 09:30:43 +0200, Dimitri Fontaine wrote:
>>>> 2) How should we handle already installed extensions, which
>>>> will still lack dependency records after this bugfix?
>>>
>>> I don't really see any other way here than providing an upgrade
>>> script that will somehow re-attach those objects, either by
>>> directly messing with pg_depends (that is, when there's a
>>> systematic way to get the OIDs of the missing RULEs), or by
>>> maybe doing a drop/create on the RULEs?
>>
>> Couldn't ALTER EXTENSION ... ADD ...; be brought up to speed to
>> support this?
>
> I'll blame the Time Zone Difference Recovering. I felt like
> missing something…

Seems like the perfect idea, but is that something we would backpatch?

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRphI+AAoJEDfy90M199hl4HgP/3LdS4YsE8IiwadEKxDZeBEP
ubtS4a2CwNrWwATpScK7H8BHQfB2Jd7uw5+HeXTbbSdmt2XFz/fCcQtaTuJRNwUw
8OHlqqS9mrgsKHgzE9+1wCUBfmpsldBaw7HetmNnacrQICVir0YXTbHfBewH/wAT
BQ+9qjLXom8gLgZ1rSysa/V4QEYdCMCSIsW/A55zkpAcvIfACmGxgzL9msoMiWxR
qdWIMd9Pop8yrAO/r3e64TG+48V18/U17mdhhPx3/svH9FP+Nee4N6t1AozNETUN
OHVJDCZmxa0k3mCj4lJud95qARDLa+1PZ+FMf5tk9+WVlUdT9tMh66RBKilmyaBk
WZReY3plRaK74xnDPo/M5a0PVu7SDaqQVWXArzorZLf5J3hL0LUE8EmkGXYZk860
WpUFOMVZqux96NmFhgNgOuNBCHQN3zztCGwgHA6Y26ajIQQnYs74XnNo/kGdje1h
A16dmxLWUEwthpPoU/DyRrObavIWI6OMMUDZ3TlQ0CK8KziLGgD2JV4uzXjyrm+w
xwNFbR5j6dFkolO32Z8v01JHH1iKCQhMo9TzTAP8w9oRPvYVxX7QbotCKV7+X0mu
uBmAB9y3EjiVvuHCkuDFw2tJ/u9/p9R0cbJYdEzNyOvG22/LL6/RaTgyjg6d+jZN
Mso43jWnjh2O6Cxy0v6N
=Mf13
-----END PGP SIGNATURE-----


From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 14:43:59
Message-ID: 20130529144359.GD3955@alap2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2013-05-29 07:35:42 -0700, Joe Conway wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 05/29/2013 05:52 AM, Dimitri Fontaine wrote:
> > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> >> On 2013-05-29 09:30:43 +0200, Dimitri Fontaine wrote:
> >>>> 2) How should we handle already installed extensions, which
> >>>> will still lack dependency records after this bugfix?
> >>>
> >>> I don't really see any other way here than providing an upgrade
> >>> script that will somehow re-attach those objects, either by
> >>> directly messing with pg_depends (that is, when there's a
> >>> systematic way to get the OIDs of the missing RULEs), or by
> >>> maybe doing a drop/create on the RULEs?
> >>
> >> Couldn't ALTER EXTENSION ... ADD ...; be brought up to speed to
> >> support this?
> >
> > I'll blame the Time Zone Difference Recovering. I felt like
> > missing something…
>
> Seems like the perfect idea, but is that something we would backpatch?

Sounds better to me than manually fiddling with pg_depend... We can't
really drop and recreate the RULEs, there might be dependencies
preventing that.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


From: Joe Conway <mail(at)joeconway(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 22:31:18
Message-ID: 51A681B6.2070104@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/29/2013 07:43 AM, Andres Freund wrote:
> On 2013-05-29 07:35:42 -0700, Joe Conway wrote:
>> On 05/29/2013 05:52 AM, Dimitri Fontaine wrote:
>>> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>>>> On 2013-05-29 09:30:43 +0200, Dimitri Fontaine wrote:
>>>>>> 2) How should we handle already installed extensions,
>>>>>> which will still lack dependency records after this
>>>>>> bugfix?
>>>>>
>>>>> I don't really see any other way here than providing an
>>>>> upgrade script that will somehow re-attach those objects,
>>>>> either by directly messing with pg_depends (that is, when
>>>>> there's a systematic way to get the OIDs of the missing
>>>>> RULEs), or by maybe doing a drop/create on the RULEs?
>>>>
>>>> Couldn't ALTER EXTENSION ... ADD ...; be brought up to speed
>>>> to support this?
>>>
>>> I'll blame the Time Zone Difference Recovering. I felt like
>>> missing something…
>>
>> Seems like the perfect idea, but is that something we would
>> backpatch?
>
> Sounds better to me than manually fiddling with pg_depend... We
> can't really drop and recreate the RULEs, there might be
> dependencies preventing that.

OK, simple enough. New patch attached. I still need to do some testing
to verify this does not break anything, but other than that, any
complaints (including the notion of backpatching this back to 9.1)?

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRpoG2AAoJEDfy90M199hl3WYP/0xCtvVQKRwZ1hWS2xxVd7OU
PFHfkX7/fLvEWi/ubra8VXexfjxO9cERLBQ5dMxxflq8sh3YHhNGLwa4TOthBzoA
zmUUgS/d6EDt9ZipSlE+L9pV3r6gfZDfz0x5RRv3aIWxxW5yOXfp1umfL1l6AGpU
e9uasllNhwOoY/voie6Aj9gSdtFMtAejXBQGsnUpj+JCITZPkRzxMfDBwTmbIPtT
U4fiHzO3fbuwtoyvjZIdF6d1GJ8U0/oLGS4AFS9nq27GFTj1PlWjMgeEKku0Nqgx
s4jIYNH/uczw9+RM8R+WF+934IcWJ47jGxQLaaS6Oog9egcbFXdjpoMjLkcaYcNb
uR1RELh/C341QFHvJQjYiwBxfPwd9kAtGdfTp/wEGnhJ1TflbaVjrOqgoWvSn6Op
emTOFhaGoAvCS2lgPhMxy2YwToKqYpiYQ8oaQQlcitG2JZaBX9X6ewFNkx2HKOrU
bickTfzLaKggKC52AsOUY1zdJBJB8Tx+srZsIV3ipDOrdftzv4hFXpWo9il+NoLr
zu4//jFmZsZXN/Y7xafnBkDWGxVizLohGVIkgTwsuaUtCjYAWPJpg7my6y3IXHfd
NpaUvKgz430XTGwxem2ICLJmLeMhVOUVIQvsV4TVDRnR+db9rq9buu611iPKcsBz
30VxweOi2keQn8C+7I/1
=vij9
-----END PGP SIGNATURE-----

Attachment Content-Type Size
fix-extension-ruledeps.01.diff text/x-patch 1.5 KB

From: Joe Conway <mail(at)joeconway(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-29 22:55:46
Message-ID: 51A68772.6040404@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/29/2013 03:31 PM, Joe Conway wrote:
> On 05/29/2013 07:43 AM, Andres Freund wrote:
>>>>> Couldn't ALTER EXTENSION ... ADD ...; be brought up to
>>>>> speed to support this?

>> Sounds better to me than manually fiddling with pg_depend... We
>> can't really drop and recreate the RULEs, there might be
>> dependencies preventing that.
>
> OK, simple enough. New patch attached. I still need to do some
> testing to verify this does not break anything, but other than
> that, any complaints (including the notion of backpatching this
> back to 9.1)?

Here's a cleaned up version, which also includes documentation. I'll
commit back to 9.1 in a day or two unless there are any objections.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRpodyAAoJEDfy90M199hlpBoP/jOp/KlgZvAL2bOZarTW/Zba
2NMQP3VV3BO7NMO8PcX9xxwZa2zhfCxr2A0GT6IJXgqTDfBCBZLFVsNGPCxS1Yik
DfrhUWxNmyPEIvwVWNCgf2G9UkOcoVLU7ROn000EDly2Fhhi0NTvHWlFWhHaM2kY
64sgjV+b++/JWzWBZntnPZH2VScv9AxaJXqNFV32AADfNOGymc17lTBalnWUzYHE
E1xWn9rZWjM35zEvBpoUcyn3jcf1NryCZIP0HGD3Vn/sW0slltBiAjnjtskhC8iF
iBLcFGvR2jZK9vvry19gVnX5dHTSM71Lxp02+x1KEEMsbqma/VFdtakUSlJUeIWH
ou6ND7lQcriyethluAJ56A4vPyHxzCVjU3aghdQiTuli7lB/2I8GOklhitGz9C2W
/firBDExUd12Um2Fc2zwQzm3s+2Hj3VMR3SVQkbXVfdNAJVqw/QWSeKl3I2CqcJH
mTDNQ9Il8LiOpUUwl9YLEazEyEvlAbfodOl8weO8WBH9QXNm0FXIHdDZeUSSmgY7
7ZHP0IScCNRkF/wxSsWI5VjQtp3GGWeyqYhpuc62CJO8aUICBxeqpB40L11Mplsh
sO8btaIOnZnzKyZqM0fhhT3+y7KbkG59VzuwhXrTV0BvXR7K0McoIJfhI9az6mAf
B5y77JTZL7Ig4HS9IyZD
=Tr4H
-----END PGP SIGNATURE-----

Attachment Content-Type Size
fix-extension-ruledeps.02.diff text/x-patch 2.2 KB

From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-30 09:02:17
Message-ID: m238t4263q.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> Here's a cleaned up version, which also includes documentation. I'll
> commit back to 9.1 in a day or two unless there are any objections.

Looks good to me.

Were you able to test it against an extension containing both rules and
views, to check that pg_dump has no problem with the new set of
dependencies?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-30 14:05:24
Message-ID: 51A75CA4.7070200@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/30/2013 02:02 AM, Dimitri Fontaine wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> Here's a cleaned up version, which also includes documentation.
>> I'll commit back to 9.1 in a day or two unless there are any
>> objections.
>
> Looks good to me.
>
> Were you able to test it against an extension containing both rules
> and views, to check that pg_dump has no problem with the new set
> of dependencies?

PostGIS has both:

test=# \dv
List of relations
Schema | Name | Type | Owner
- --------+-------------------+------+----------
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | raster_columns | view | postgres
public | raster_overviews | view | postgres
(4 rows)

select tablename, rulename from pg_rules ;
tablename | rulename
- ------------------+-------------------------
pg_settings | pg_settings_n
pg_settings | pg_settings_u
geometry_columns | geometry_columns_delete
geometry_columns | geometry_columns_update
geometry_columns | geometry_columns_insert
(5 rows)

8<----------------
# pg_dump test > /tmp/test-01.dmp
# dropdb test
# createdb test
# psql test < /tmp/test-01.dmp
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
REVOKE
REVOKE
GRANT
GRANT
# pg_dump test > /tmp/test-02.dmp
# diff /tmp/test-01.dmp /tmp/test-02.dmp
...<no differences>...
8<----------------

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRp1ykAAoJEDfy90M199hlLQcP/1OkpFeb99EO9xca0RD+WIHS
FrqhBEJDHA4ujODvitZRMTFjpS1WH4Difm7P05Lvbr1xEUmwuSD6oBw/VQ1p6cxs
RyIvUM1uLVhR/nwjMeymner9kOINPu4rBVKf+7EgPJQcFvZuUSzafNGH1l70p6wk
dXMA2ggjjFdvF6voVxaKkHFbs+uttURNDZ2l0f6eb4QJRZta+NuFCOtIkPTqBESx
oABWuoAutAEX0Z7b0iEyNjjDLduPzjMIqQm8Y6NfsGmkEYd2jrpYVl04T8hcbqf0
vFJ2NvblvuaHoRIhq/ZYbFt9dQKIdoUtNuzR8MOK474mD/VrX6v/xquGh1rcmL4x
1k94Lis3Cf/QEBUEqwKNribkOLemaxEEDVnVTCWSC59FoDbaAZuiwtYspHYwAQED
D3nZ9jknEr+Bziqw6y8KP3wQGAbyssIKdtXFlw2u1BFeFjuWK5pL8vR3vi08j/Ij
diycCOBLotJGlkaHEt7vCNMTbHlIru4d4yblh0hbB6wL6JvI2HbGlK5chPPqIu+O
zHpPGUuTy7lgi+0809k5ceoqYUDJJTo0yu/3BuvLeaZwJqfS9QBIjCdryb/0MCVn
QJ6u3r54aSz4FQHP8iDoDnfbZIAdpCtjlqiTxLARxxYZqWt9nHoW0bC9fZpTkBfT
YxJX5C74NCVHE2Qdqnqx
=fbuL
-----END PGP SIGNATURE-----


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-05-30 15:11:12
Message-ID: m2obbsv6y7.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
>> Were you able to test it against an extension containing both rules
>> and views, to check that pg_dump has no problem with the new set
>> of dependencies?
>
> PostGIS has both:
[...]
> # pg_dump test > /tmp/test-02.dmp
> # diff /tmp/test-01.dmp /tmp/test-02.dmp
> ...<no differences>...

Perfect, thanks!

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 03:46:35
Message-ID: CA+TgmoZff5KKLRR2oFwMZjbcoG0AQPHVtYu0-gi72jfEvWdAtA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Wed, May 29, 2013 at 6:55 PM, Joe Conway <mail(at)joeconway(dot)com> wrote:
>> OK, simple enough. New patch attached. I still need to do some
>> testing to verify this does not break anything, but other than
>> that, any complaints (including the notion of backpatching this
>> back to 9.1)?
>
> Here's a cleaned up version, which also includes documentation. I'll
> commit back to 9.1 in a day or two unless there are any objections.

Changing SQL syntax in the back-branches isn't normally something we
do, but I confess I don't see any real reason not to do it in this
case.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


From: Joe Conway <mail(at)joeconway(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 14:57:32
Message-ID: 51AA0BDC.5080708@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/31/2013 08:46 PM, Robert Haas wrote:
> On Wed, May 29, 2013 at 6:55 PM, Joe Conway <mail(at)joeconway(dot)com>
> wrote:
>>> OK, simple enough. New patch attached. I still need to do some
>>> testing to verify this does not break anything, but other than
>>> that, any complaints (including the notion of backpatching
>>> this back to 9.1)?
>>
>> Here's a cleaned up version, which also includes documentation.
>> I'll commit back to 9.1 in a day or two unless there are any
>> objections.
>
> Changing SQL syntax in the back-branches isn't normally something
> we do, but I confess I don't see any real reason not to do it in
> this case.

That was part of my hesitation, but I don't see any better way to fix
existing installations and this is pretty well self-contained. Any
other opinions out there?

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRqgvcAAoJEDfy90M199hlmG0P/0LmkNBvMrMqASA4zyhtKGTG
3Wd+/wC2cHPrfVqFmEKsuCStWTiQxzdcNGgPBfzdg5QskB8xcAr81ggH3mW5ldHE
Gnz9ZJ6LaAWeqAg0IjIir2spQmZbNfPc9BY+vnTQAoSPmJwoXgFLnJSdW8+5JrLR
qwrRv3f6jJzYPXYdSXu91fDCwNi7mZmcqjJRtjO58xI+hcrNsKMjGnloryeifrVP
N1ZI2vrPiwUBmKR01RTjjfTjCA1iBxwABLbzknO4hNchE7l8ghcXmE/K5Zkaj8E4
QXQk/dx5EzXlKtOqBpKh2QpZZDoKD1NAR9u+SSsbjjdgzXM+L3SkslvlisbCEbrH
HwYys2honEk38SzxeDeqpmLBDmEqccfuq/VIqe82szbusn58kmq7RbU/veScIwkA
5eAOzi+YbbaK1ThS2CZKrt9DqhUgaIhj66X7+bmhusPxG1cQyGnV8Tetol50Hyo4
6unkqiQhr4qfXwDtrUDDtdBxTiFWsIwXCe3zytp9J6HStHN1OjGfjDM8Mu71wwiH
44PqYnugaJff7I6fLC+qDWX5VD5i+7gSm8/Q7awt1hk7L5gLsFU6qQmJVkpY2HJs
RQ3G2aoB2pKyvnbeYvFb9Ny1LH2I8gnUW0vXZ69T7ecvRLm4IC4wmFc3SGmUXP+x
xbRWb6LW+RXPhsZYooKQ
=RP/c
-----END PGP SIGNATURE-----


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:07:53
Message-ID: 21134.1370099273@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> On 05/31/2013 08:46 PM, Robert Haas wrote:
>> Changing SQL syntax in the back-branches isn't normally something
>> we do, but I confess I don't see any real reason not to do it in
>> this case.

> That was part of my hesitation, but I don't see any better way to fix
> existing installations and this is pretty well self-contained. Any
> other opinions out there?

I don't like this approach much.

1. It does nothing to fix the issue in *existing* databases, which
won't have pg_depend entries like this.

2. In general, we have assumed that properties of tables, such as
indexes and constraints, cannot be independent members of extensions.
It's not clear to me why rules should be different.

regards, tom lane


From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Joe Conway <mail(at)joeconway(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:16:25
Message-ID: 20130601151625.GB6732@awork2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2013-06-01 11:07:53 -0400, Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
> > On 05/31/2013 08:46 PM, Robert Haas wrote:
> >> Changing SQL syntax in the back-branches isn't normally something
> >> we do, but I confess I don't see any real reason not to do it in
> >> this case.
>
> > That was part of my hesitation, but I don't see any better way to fix
> > existing installations and this is pretty well self-contained. Any
> > other opinions out there?
>
> I don't like this approach much.
>
> 1. It does nothing to fix the issue in *existing* databases, which
> won't have pg_depend entries like this.

Well, you can now write an extension upgrade script that adds the
missing dependencies. To me that sounds better than letting it fiddle
with pg_depend.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:23:10
Message-ID: 51AA11DE.4080702@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/01/2013 08:07 AM, Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> On 05/31/2013 08:46 PM, Robert Haas wrote:
>>> Changing SQL syntax in the back-branches isn't normally
>>> something we do, but I confess I don't see any real reason not
>>> to do it in this case.
>
>> That was part of my hesitation, but I don't see any better way to
>> fix existing installations and this is pretty well
>> self-contained. Any other opinions out there?
>
> I don't like this approach much.
>
> 1. It does nothing to fix the issue in *existing* databases, which
> won't have pg_depend entries like this.

The grammar change does allow the pg_depend entries to be added
through ALTER EXTENSION. It works perfectly.

> 2. In general, we have assumed that properties of tables, such as
> indexes and constraints, cannot be independent members of
> extensions. It's not clear to me why rules should be different.

I can look at having pg_dump ignore these entries, but I suspect that
will be quite a bit more invasive.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRqhHeAAoJEDfy90M199hlCwwP/215zTz6F1/pPDUowppEjQfd
YNCeufgm9ZpcycOjhz/wBFGSaOcPOn5eoBwcYC6XqVGDemU8MVUENcpydq2ltRzl
ks5o1LZsWRnYh594v3Wi6K0neQ9G4qx9Lx03k9RdE7TWVdnu4JziQb6BNPEyfa+D
9kCt6tHXOv2xYwr2FeVieH6dlDpEwScFSG59nUlE2mM7i9/eM7cuiCw7EJZpXJuD
48hkcEZkYlBZAAL6JAErEqMkl8bEB8JEk2s/YkoH8W/qkZrnd21k8IeHPcWBh2DH
2vVJBGBLZL2wYEMT1Qu5phiYhlUoXnHgIHPPVPeLl3Vx2U6D+00vswuwmKKD2T1/
aAgdQOX8ubNr9GJfAeBZ/GeLdAqr4sei1lzxM2LJkVmu7szE+6DYXyFvB3kO3Fxh
IXxDmhCWv7OeKPMo5OGjV3/Vjzxsxx85BDsz/TFhIyNaKvzz2UacspcKlZ51Sr6i
5FYSRPII8g3FPtt1/8ed/Js9ZQOscqrUw/gxrttexGs1I4R8ZooUesD2pSrED9wn
CKYY5AYWihH3cugyUZbqOOBTVEtgFpzKCo1p8zLUizTnlR6pnrRTWt+7/0Q+rSw/
SXnCwbeE4aaESghTOSb6P5l9JkCIbprz+URWYYmHWm6k0CnMUuKtM1syYBkdx+ew
Mptd78Ya3Wl6rRkC0pa5
=8g4V
-----END PGP SIGNATURE-----


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Joe Conway <mail(at)joeconway(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:31:05
Message-ID: 21430.1370100665@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-06-01 11:07:53 -0400, Tom Lane wrote:
>> I don't like this approach much.
>>
>> 1. It does nothing to fix the issue in *existing* databases, which
>> won't have pg_depend entries like this.

> Well, you can now write an extension upgrade script that adds the
> missing dependencies. To me that sounds better than letting it fiddle
> with pg_depend.

Per my point #2, that would be the wrong solution, quite aside from the
wrongness of dumping the fixup burden on the extension author. For one
thing, the extension author has no idea whether his script is being
loaded into a database that has this patch. If it doesn't, adding a
command like this would cause the script to fail outright. If it does,
then the command is unnecessary, since the patch also includes a code
change that adds the dependency.

But in any case, making rules act differently from other table
properties for this purpose seems seriously wrong.

regards, tom lane


From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Joe Conway <mail(at)joeconway(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:32:59
Message-ID: 20130601153259.GD6732@awork2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2013-06-01 11:31:05 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2013-06-01 11:07:53 -0400, Tom Lane wrote:
> >> I don't like this approach much.
> >>
> >> 1. It does nothing to fix the issue in *existing* databases, which
> >> won't have pg_depend entries like this.
>
> > Well, you can now write an extension upgrade script that adds the
> > missing dependencies. To me that sounds better than letting it fiddle
> > with pg_depend.
>
> Per my point #2, that would be the wrong solution, quite aside from the
> wrongness of dumping the fixup burden on the extension author. For one
> thing, the extension author has no idea whether his script is being
> loaded into a database that has this patch. If it doesn't, adding a
> command like this would cause the script to fail outright. If it does,
> then the command is unnecessary, since the patch also includes a code
> change that adds the dependency.

> But in any case, making rules act differently from other table
> properties for this purpose seems seriously wrong.

What's your proposal to fix this situation then?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


From: Joe Conway <mail(at)joeconway(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:39:11
Message-ID: 51AA159F.6020200@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/01/2013 08:32 AM, Andres Freund wrote:
> On 2013-06-01 11:31:05 -0400, Tom Lane wrote:
>> But in any case, making rules act differently from other table
>> properties for this purpose seems seriously wrong.
>
> What's your proposal to fix this situation then?

I gather Tom would rather see this handled by filtering in pg_dump.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRqhWfAAoJEDfy90M199hlZ4oQAIBha6LI6cCtPUTPgh4JT0Jy
oUH/+TWZbVUxe2AMMskxDh65DhUjTpHliEQiM6Eyd6gGx9icSKxHMo7pfvfqNIZi
kZQeY2x0un1RRd1yyydNuZYKk9cJWOzTJl+OaGCVUlVAnre1hs6ykkeWVotRDvJz
jMMs+XasEIr7MNNIbJqKGKNkiSD53gOOybouxzgqitsf/6+qp4DTmHgDurzptxgD
HpskiKBJkA7Trb5Xukd6SrhajzYVaF8+DAHzBaZBwKXvg/wr4JN1NEHpr8O3+itP
iIWbnR2iGxgkFRTvwwiJx+Phc2BJIVRwBzAyN4AAaiM7WykX14ztmyIQf5cEUNF5
abpFxMedMq0yATwU/5XBZ/HPLkCRv9mK+6zQUXrQ0rd2KaM/wMDA1DdpfH9C0vd7
6MPUNrq8U2V3UxJudMx59wnQMVSDoVNuxPn+wRBnUtpyIorwYIOVybRt/T3/F4tm
6UCoaBtGF4EWvdxtBpr9B9rl/xSc/JxMr6TNV+3S7EITg/QUbqKlcsMvza16PIXu
cFPKuI4VeKyKRt7bTV9hE0HRqL15qsnOQhZZ9aSH9kcqozpWCglHmWLiUljvIRtt
z4OMKXPOaayZWFLfex/dFd+AXE396sLBgadKCr5Y+L0U08SNCVVAXK9k84zwJOcy
MhOClw1EUQ9WTGaFmMfP
=FALs
-----END PGP SIGNATURE-----


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 15:57:09
Message-ID: 21686.1370102229@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> I can look at having pg_dump ignore these entries, but I suspect that
> will be quite a bit more invasive.

Actually, I believe the answer is just that getSchemaData() is doing
things in the wrong order:

if (g_verbose)
write_msg(NULL, "reading rewrite rules\n");
getRules(fout, &numRules);

/*
* Identify extension member objects and mark them as not to be dumped.
* This must happen after reading all objects that can be direct members
* of extensions, but before we begin to process table subsidiary objects.
*/
if (g_verbose)
write_msg(NULL, "finding extension members\n");
getExtensionMembership(fout, extinfo, numExtensions);

Per that comment, getRules() should be called down where indexes,
constraints, and triggers are processed.

regards, tom lane


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 16:39:08
Message-ID: 22242.1370104748@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I wrote:
> Actually, I believe the answer is just that getSchemaData() is doing
> things in the wrong order:

BTW, I'm inclined to think it's also wrong that the getEventTriggers()
call was just added at the end; those things are certainly not table
subsidiary objects. I don't know if we allow event triggers to be
extension members, but if we did, that call would have to occur before
getExtensionMembership().

regards, tom lane


From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-01 16:47:18
Message-ID: 51AA2596.30708@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/01/2013 09:39 AM, Tom Lane wrote:
> I wrote:
>> Actually, I believe the answer is just that getSchemaData() is
>> doing things in the wrong order:
>
> BTW, I'm inclined to think it's also wrong that the
> getEventTriggers() call was just added at the end; those things are
> certainly not table subsidiary objects. I don't know if we allow
> event triggers to be extension members, but if we did, that call
> would have to occur before getExtensionMembership().

Thanks! I'll have a look.

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRqiWWAAoJEDfy90M199hlvAgP+gNNd+Vf70i4ecANCffYIqa7
PrvuKBAP/ZwWwISO7G/T5JbmKrhsySsrVWCQqSdIj62eLzkgbToqIbQuygcUjg3t
SfwjxuSqf8P8oR+LWkKnU2pcY/WbpdnmJHYO0e6Y+Fn2I/OP3yImkUm+2O9nMflI
v0M2qxcVXu1/aUnfdU7+BLZli0S+gUp+FoiO9O+YaAzK7jCyg3q0QbCXLh9ygEim
5ABZCONiTbH3eALRHfeD1uBHAU60gdbiFPwSK7zBCW9FzVKbU5IFV1qIl4oKTlzo
rpvgXnJ7r1EngyHUnXZfTltnhCN6joOiRA3GLDflA0e+f933zJf/KXnRzRcjb1+H
vpmtKWdM9qnH7XcNQYe9EJXkLEcktctgDs01cgaFBy1EK6qEjFD7FAbfRMMpLVTp
4/HIQX62SezGJTzCW06Qz6/Gt2ycJ5HtLmrEmYrzhOjN+ZEsaZBr3ad/nU/zExEZ
TzNF8tX9SJzgEFd87x1Xz1pNeX+ewJ8uI87aqyWTIeHPG3GjjFUKehVYmBPGRawl
bWrGYo1G65b0h3jvSzAFEL82e0wzaEoxXyoBuogaefNohrCNrpiKUIfPwjuCimC0
MCGgmS8Kj76sqw/MTq2vcSY2ynEgse1O0weWI3sDM/M/dCvIQSCtNqfvTWL+PISL
01MhTFzyWFQa5E5206w/
=imAT
-----END PGP SIGNATURE-----


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Joe Conway <mail(at)joeconway(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-02 20:10:13
Message-ID: m27gicmfyy.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Actually, I believe the answer is just that getSchemaData() is doing
>> things in the wrong order:

Each time I have to look at the pg_dump parts I discover new things.
I've been misleading Joe in telling him I though the problem must have
been in extension dependency tracking for rules, without taking the
necessary time to have a real look at the code. Sorry about that.

> BTW, I'm inclined to think it's also wrong that the getEventTriggers()
> call was just added at the end; those things are certainly not table
> subsidiary objects. I don't know if we allow event triggers to be
> extension members, but if we did, that call would have to occur before
> getExtensionMembership().

Event triggers sure should be allowed as extension members. That leaves
me wondering if there's another possible code arrangement in that
function so that it's easier to maintain. Maybe something with a couple
of structs and a function that knows how to use them to fill in the
variables, but I can see we have some inter-dependencies and some
getSomething functions have quite a specialized API.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


From: Joe Conway <mail(at)joeconway(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-04 02:37:23
Message-ID: 51AD52E3.1020308@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/02/2013 03:10 PM, Dimitri Fontaine wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> Actually, I believe the answer is just that getSchemaData() is
>>> doing things in the wrong order:

Indeed Tom, as usual, seems to have the best correct answer :-)

New patch attached works as expected and requires nothing special for
existing databases with installed extensions such as PostGIS with RULEs.

> Each time I have to look at the pg_dump parts I discover new
> things. I've been misleading Joe in telling him I though the
> problem must have been in extension dependency tracking for rules,
> without taking the necessary time to have a real look at the code.
> Sorry about that.
>
>> BTW, I'm inclined to think it's also wrong that the
>> getEventTriggers() call was just added at the end; those things
>> are certainly not table subsidiary objects. I don't know if we
>> allow event triggers to be extension members, but if we did, that
>> call would have to occur before getExtensionMembership().
>
> Event triggers sure should be allowed as extension members. That
> leaves me wondering if there's another possible code arrangement in
> that function so that it's easier to maintain. Maybe something with
> a couple of structs and a function that knows how to use them to
> fill in the variables, but I can see we have some
> inter-dependencies and some getSomething functions have quite a
> specialized API.

I moved getEventTriggers() as well.

I was surprised by a couple of things looking at this code. First,
getRules() is written differently than other table subsidiary objects'
get functions. Secondly, I would have expected
getExtensionMembership() to be recursive -- instead it looks to only
go one level deep. Perhaps the longer term fix would be to identify
extension dependencies recursively, and then the reliance on strict
ordering here could be relaxed. But I don't think we want to make that
change in 9.3 at this point.

Objections to this version?

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with undefined - http://www.enigmail.net/

iQIcBAEBAgAGBQJRrVLjAAoJEDfy90M199hleA4P/iuGvghfqAo9dogK5e75e7gx
AgL27/WvagrI4mhQp0RQUD3LJx52/XbGqNUMJiGBSuLgGcdwTCyC4yzLAXMEWKD6
x1l1u9mtSAhokk2KjwxdKEzFIzIQ/fYNan5FtXOlgiyq+A7v2hGsFC7ChPgnU6eW
H4nnI94lm3gW7GqxFS3NNjJ0pTDKwAUCYqfoiIjA58WXSUMZoVc5F+DCsS4YjDVG
wve9zf3HVhPxVi/BNCQfRF1grpZfNJFWjSRo1IclCUCqcQWr4BWyXkNuDmSft67S
4UqIkvZyPM+jCoPrJIbqo363CHYICHJ1jfeeYIn+8FnWOtm+fJoXncZbDaKQm9la
iqeHv6qiQzRAq7ui59Nwgo+gSK8IKQYdXilu4wq4LgF0RSb9NTiWldSs+H2FmGLs
k1VNcpGdKlKzp7gOtEAMjd+HWgbYV7Worv7nQY7MJSG81Vnx+LMfiRwSb8Tvrzox
RJcd2zTX3P2ohaczUjRNT6dC9tWT84r+fbelMIOk1ZL2RJixYyzTft7YSrfaz08N
iYL8ho9JPGgO6AX90cpc879HVwkJR3Ffxdu/FPH1AsgtcVO4XUBJlRex1oYWWF+y
FA3Hr+yUhPtyf3Ad/PUGxPpY6ZFTkg1w5prRpDIDKXLnTtWChKrgBsiKow3K5IFA
cQ2OpvmBNiTBkNkbYBKE
=cAP6
-----END PGP SIGNATURE-----

Attachment Content-Type Size
fix-extension-ruledeps.03.diff text/x-patch 1.2 KB

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-04 02:57:03
Message-ID: 13907.1370314623@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> I was surprised by a couple of things looking at this code. First,
> getRules() is written differently than other table subsidiary objects'
> get functions. Secondly, I would have expected
> getExtensionMembership() to be recursive -- instead it looks to only
> go one level deep. Perhaps the longer term fix would be to identify
> extension dependencies recursively, and then the reliance on strict
> ordering here could be relaxed. But I don't think we want to make that
> change in 9.3 at this point.

I'm not sure that's appropriate: extension membership is not a recursive
concept AFAICS. In any case, as you say, it's something for more
analysis later.

> Objections to this version?

I'd have put the getRules call where getEventTriggers is now, or at
least adjacent to getTriggers in one direction or the other. I'm
not sure there is anything functionally wrong with what you have here;
but IMO rules and table-level triggers are pretty much the same kind
of critters so far as pg_dump is concerned, to wit, they're table
properties not free-standing objects (which is exactly the point of this
change). So it seems to me to make sense to process them together.

BTW, don't forget that the getRules move needs to be back-patched.

regards, tom lane


From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-10 00:34:22
Message-ID: 51B51F0E.3020707@joeconway.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/03/2013 07:57 PM, Tom Lane wrote:
> I'd have put the getRules call where getEventTriggers is now, or
> at least adjacent to getTriggers in one direction or the other.
> I'm not sure there is anything functionally wrong with what you
> have here; but IMO rules and table-level triggers are pretty much
> the same kind of critters so far as pg_dump is concerned, to wit,
> they're table properties not free-standing objects (which is
> exactly the point of this change). So it seems to me to make sense
> to process them together.

OK, done this way and committed.

> BTW, don't forget that the getRules move needs to be back-patched.

This too.

Thanks,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRtR8OAAoJEDfy90M199hlCvsP/0esIWG7S7yPh91tGHQmoUiP
OlbbIwQRVAVvKAdStT3RtvI/NjmflZrqMmCXueGoy3dOrVZ+NcfMW09gLOSLxpjV
0PEnWBvU9JiyhQ4dyRjfqygZzD4AJZwMhtgPJqIIZUTsoctIPGwW5PZocTeuNJvu
RzM4I+nfQSMkqTFuYawyD8l8uH802DfkiTrKCFAPvEExdzQOPac4Mc042tWdJiLU
BlbqAF3Tw2V4MqHSnvumvRoIitFkWi3IpVkfIrsSZJ3a+meZP8Sqp2dMZNP2f8i9
u6drVw8hrYibHQvEG+xYhUBtPEfqrkIh7hqREtfyuMHyaXT+DcpIKcMjhHVeA/X0
1+lxGcW7I/IQZF5d8ql59xGdMPG+xjDxo04e2tu9E+yyfF82uFIBa6dNmFEA4Scr
fegoYLHr/VJv3FHXTv5nFPxcuXA/H+2C+0WaJaweLSrh0Sg33myKV46m2YXb1KRA
mtW/ahV2g2yLq7uYK7rwvEZltUVKmko6uMuJzAOf75rT8hXkZEy6poMTIqH+wc4B
qs+ZzgDIu5e/YgPMdgLNidfLJHuUchfcOYvleUGynzydZoLQTdO9DI3wGxrhEL4m
kFu/ypmahMrYmb/2dG6cMfyLuF7DKwyysGzUBA5HISoywvSy55CEqaZKs5muE3eb
XFB8fC0rphS4dLOrRU0a
=0LVS
-----END PGP SIGNATURE-----


From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_dump with postgis extension dumps rules separately
Date: 2013-06-10 09:31:33
Message-ID: m238sqfh1m.fsf@2ndQuadrant.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> OK, done this way and committed.

Thanks,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support