Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Le jeu. 7 avr. 2022, 07:43, Michael Paquier  a écrit :

>
> Looks clear to me that a different design is wanted here, and that
> this won't make it for v15, so I have marked the patch as returned
> with feedback in the CF app.
>
> Hello,

I agree with Michael, this won't be ready for PG15. I had planned to work
on this sooner but I life happened...

Have a great day,

Lætitia

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Michael Paquier]

On Sat, Mar 26, 2022 at 09:53:19AM +0100, Laetitia Avrot wrote:
> I think it's time to sum up what we want to do:
> 
> - We'd like to use switches to export objects according to a pattern.
> - For each object type we will have an --object=PATTERN flag and a
> --exclude-object=PATTERN
> - Having a short flag for each of the long flags is not mandatory
> - The object types that pg_dump can select so far are:
> - table (already written)
> - schema (already written)

> - extension (half-written, --exclude-extension not written)

I would be to blame on this item.

> - routine (TBD ASAP). Routine flag operates on stored functions, stored
> procedures, aggregate functions, and window functions.
> - By default, pg_dump does not export system objects but we found out that
> we could use --table='pg_catalog.*' to export them. This is a bug and will
> be fixed. pg_dump won't have the ability to export any system object
> anymore. Should the fix belong to that patch or do I need to create a
> separate patch? (Seems to me it should be separated)
> 
> If everyone is ok with the points above, I'll write both patches.

Looks clear to me that a different design is wanted here, and that
this won't make it for v15, so I have marked the patch as returned
with feedback in the CF app.
--
Michael


signature.asc
Description: PGP signature

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Sat, Mar 26, 2022 at 1:53 AM Laetitia Avrot 
wrote:

> Hello all,
>
> Le sam. 26 mars 2022 à 01:13, Michael Paquier  a
> écrit :
>
>> On Fri, Mar 25, 2022 at 10:09:33PM +0100, Daniel Gustafsson wrote:
>> > Agreed.  In this case it seems that adding --exclude-extension would
>> make sense
>> > to keep conistency.  I took a quick stab at doing so with the attached
>> while
>> > we're here.
>>
>> src/test/modules/test_pg_dump would be the best place for the addition
>> of a couple of tests with this new switch.  Better to check as well
>> what happens when a command collides with --extension and
>> --exclude-extension.
>>
>> printf(_("  -e, --extension=PATTERN  dump the specified
>> extension(s) only\n"));
>> +   printf(_("  --exclude-extension=PATTERN  do NOT dump the specified
>> extension(s)\n"));
>> Shouldn't this be listed closer to --exclude-table-data in the --help
>> output?
>>
>
> I think it's time to sum up what we want to do:
>
> - We'd like to use switches to export objects according to a pattern.
> - For each object type we will have an --object=PATTERN flag and a
> --exclude-object=PATTERN
> - Having a short flag for each of the long flags is not mandatory
> - The object types that pg_dump can select so far are:
> - table (already written)
> - schema (already written)
> - extension (half-written, --exclude-extension not written)
> - routine (TBD ASAP). Routine flag operates on stored functions,
> stored procedures, aggregate functions, and window functions.
> - By default, pg_dump does not export system objects but we found out that
> we could use --table='pg_catalog.*' to export them. This is a bug and will
> be fixed. pg_dump won't have the ability to export any system object
> anymore. Should the fix belong to that patch or do I need to create a
> separate patch? (Seems to me it should be separated)
>
> If everyone is ok with the points above, I'll write both patches.
>
>
That looks correct.

I would say we should make the --table change and the --exclude-extension
change as separate commits.

Michael's question brought up a point that we should address.  I do not
think having these (now) 4 pairs of options presented strictly
alphabetically in the documentation is a good choice and we should deviate
from that convention here for something more user-friendly, and to reduce
the repetitiveness that comes from having basically what could be one pair
of options actually implemented as 3 pairs.  My initial approach would be
to move them all to a subsection after the --help parameter and before the
section header for -d.  That section would be presented something like:

"""
These options allow for fine-grained control of which user objects are
produced by the dump (system objects are never dumped).  If no inclusion
options are specified all objects are dumped except those that are
explicitly excluded.  If even one inclusion option is specified then only
those objects selected for inclusion, and not excluded, will appear in the
dump.

These options can appear multiple times within a single pg_dump command
line. For each of these there is a mandatory pattern value, so the actual
option looks like, e.g., --table='public.*', which will select all
relations in the public schema. See (Patterns). When using wildcards, be
careful to quote the pattern if needed to prevent the shell from expanding
the wildcards.

When using these options, dependencies of the selected objects are not
automatically dumped, thus making such a dump potentially unsuitable for
restoration into a clean database.

This subset of options control which schemas to select objects from for an
otherwise normal dump.

--schema / -n
--exclude-schema / -N

The following subset specifies which non-schema objects to include.  These
are added to the objects that end up selected due to their presence in a
schema.  Specifically, the --exclude-schema option is ignored while
processing these options.

--table / -t
  Considers all relations, not just tables.  i.e., views, materialized
views, foreign tables, and sequences.
--routine
  Considers functions, procedures, aggregates, window functions
--extension / -e
  Considers extensions

When dumping data, only local table data is dumped by default.  Specific
table data can be excluded using the --exclude-table-data option.

Specifying a foreign server using --include-foreign-data will cause related
foreign table data to also be dumped.

The following subset specifies which objects to exclude.  An object that
matches one of these patterns will never be dumped.

--exclude-table / -T
--exclude-routine
--exclude-extension

The following options control the dumping of large objects:

-b
--blobs
Include large objects in the dump. This is the default behavior except when
--schema, --table, or --schema-only is specified. The -b switch is
therefore only useful to add large objects to dumps where a specific schema
or table has been requested. Note that blobs are considered data and
the

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Hello all,

Le sam. 26 mars 2022 à 01:13, Michael Paquier  a
écrit :

> On Fri, Mar 25, 2022 at 10:09:33PM +0100, Daniel Gustafsson wrote:
> > Agreed.  In this case it seems that adding --exclude-extension would
> make sense
> > to keep conistency.  I took a quick stab at doing so with the attached
> while
> > we're here.
>
> src/test/modules/test_pg_dump would be the best place for the addition
> of a couple of tests with this new switch.  Better to check as well
> what happens when a command collides with --extension and
> --exclude-extension.
>
> printf(_("  -e, --extension=PATTERN  dump the specified
> extension(s) only\n"));
> +   printf(_("  --exclude-extension=PATTERN  do NOT dump the specified
> extension(s)\n"));
> Shouldn't this be listed closer to --exclude-table-data in the --help
> output?
>

I think it's time to sum up what we want to do:

- We'd like to use switches to export objects according to a pattern.
- For each object type we will have an --object=PATTERN flag and a
--exclude-object=PATTERN
- Having a short flag for each of the long flags is not mandatory
- The object types that pg_dump can select so far are:
- table (already written)
- schema (already written)
- extension (half-written, --exclude-extension not written)
- routine (TBD ASAP). Routine flag operates on stored functions, stored
procedures, aggregate functions, and window functions.
- By default, pg_dump does not export system objects but we found out that
we could use --table='pg_catalog.*' to export them. This is a bug and will
be fixed. pg_dump won't have the ability to export any system object
anymore. Should the fix belong to that patch or do I need to create a
separate patch? (Seems to me it should be separated)

If everyone is ok with the points above, I'll write both patches.

Have a nice day,

Lætitia

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Michael Paquier]

On Fri, Mar 25, 2022 at 10:09:33PM +0100, Daniel Gustafsson wrote:
> Agreed.  In this case it seems that adding --exclude-extension would make 
> sense
> to keep conistency.  I took a quick stab at doing so with the attached while
> we're here.

src/test/modules/test_pg_dump would be the best place for the addition
of a couple of tests with this new switch.  Better to check as well
what happens when a command collides with --extension and
--exclude-extension.

printf(_("  -e, --extension=PATTERN  dump the specified extension(s) 
only\n"));
+   printf(_("  --exclude-extension=PATTERN  do NOT dump the specified 
extension(s)\n"));
Shouldn't this be listed closer to --exclude-table-data in the --help
output?
--
Michael


signature.asc
Description: PGP signature

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

"David G. Johnston"  writes:
> If we want to choose the other position I would just go with
> "--[no]-system-objects" options to toggle whether pattern matching grabs
> them by default (defaulting to no) and if someone wants to enable them for
> only specific object types they can --system-objects and then
> --exclude-type='pg_catalog' any that shouldn't be enabled.

Yeah, I could live with that.  Per-object-type control doesn't
seem necessary.

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Fri, Mar 25, 2022 at 2:55 PM Tom Lane  wrote:

> Daniel Gustafsson  writes:
> >> On 25 Mar 2022, at 19:37, Tom Lane  wrote:
> >> I'd vote for changing the behavior of --table rather than trying to
> >> be bug-compatible with this decision.
>
> > Agreed.  Question is what to do for "-t pg_class", should we still forbid
> > dumping system catalogs when they are pattern matched without wildcard
> or is
> > should that be ok? And should this depend on if "-n pg_catalog" is used?
>
> I don't think there's anything really wrong with just "we won't dump
> system objects, full stop"; I don't see much use-case for doing that
> except maybe debugging, and even that is a pretty thin argument.
>

+1

We could bug-fix in a compromise if we felt compelled by a user complaint
but I don't foresee any compelling ones for this.  The catalogs are
implementation details that should never have been exposed in this manner
in the first place.

If we want to choose the other position I would just go with
"--[no]-system-objects" options to toggle whether pattern matching grabs
them by default (defaulting to no) and if someone wants to enable them for
only specific object types they can --system-objects and then
--exclude-type='pg_catalog' any that shouldn't be enabled.

The documentation already says that the include options ignore -n/-N so the
solution that breaks this rule seems less appealing at a cursory glance.

David J.

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

Daniel Gustafsson  writes:
>> On 25 Mar 2022, at 19:37, Tom Lane  wrote:
>> I'd vote for changing the behavior of --table rather than trying to
>> be bug-compatible with this decision.

> Agreed.  Question is what to do for "-t pg_class", should we still forbid
> dumping system catalogs when they are pattern matched without wildcard or is
> should that be ok? And should this depend on if "-n pg_catalog" is used?

I don't think there's anything really wrong with just "we won't dump
system objects, full stop"; I don't see much use-case for doing that
except maybe debugging, and even that is a pretty thin argument.

However, a possible compromise is to say that we act as though
--exclude-schema=pg_catalog is specified unless you explicitly
override that with "--schema=pg_catalog".  (And the same for
information_schema, I suppose.)  This might be a bit hacky to
implement :-(

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Daniel Gustafsson]

> On 25 Mar 2022, at 19:37, Tom Lane  wrote:

> I'd vote for changing the behavior of --table rather than trying to
> be bug-compatible with this decision.

Agreed.  Question is what to do for "-t pg_class", should we still forbid
dumping system catalogs when they are pattern matched without wildcard or is
should that be ok? And should this depend on if "-n pg_catalog" is used?

--
Daniel Gustafsson   https://vmware.com/

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Daniel Gustafsson]

> On 25 Mar 2022, at 01:40, Tom Lane  wrote:
> 
> "David G. Johnston"  writes:
>> The extension object type does not seem to have gotten the
>> --exclude-extension capability that it would need to conform to the general
>> design exemplified by --table and hopefully extended out to the routine
>> object types.
> 
> We're not going to instantly build out every feature that would be
> suggested by a roadmap.

Agreed.  In this case it seems that adding --exclude-extension would make sense
to keep conistency.  I took a quick stab at doing so with the attached while
we're here.

--
Daniel Gustafsson   https://vmware.com/



0001-First-WIP-stab-at-exclude-extension-for-pg_dump.patch
Description: Binary data

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Friday, March 25, 2022, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Fri, Mar 25, 2022 at 10:57 AM Tom Lane  wrote:
> >> pg_dump never dumps system objects, so I don't see a need for
> >> a switch to tell it not to.
>
> > I considered pg_class to be a system object, which was dumped under -t
> '*'
>
> I'd vote for changing the behavior of --table rather than trying to
> be bug-compatible with this decision.
>
>
Agreed.

David J.

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

"David G. Johnston"  writes:
> On Fri, Mar 25, 2022 at 10:57 AM Tom Lane  wrote:
>> pg_dump never dumps system objects, so I don't see a need for
>> a switch to tell it not to.

> I considered pg_class to be a system object, which was dumped under -t '*'

Oh!  You're right, the --table switches will include system objects.
That seems like a bug TBH.  Even if it's intentional, it's surely
not behavior we want for functions.  You can somewhat easily
exclude system catalogs from matching --table since they all have
names starting with "pg_", but it'd be way more painful for functions
because (a) there are thousands and (b) they're not very predictably
named.

I'd vote for changing the behavior of --table rather than trying to
be bug-compatible with this decision.

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Fri, Mar 25, 2022 at 10:57 AM Tom Lane  wrote:

> "David G. Johnston"  writes:
>
> > Except succinctly
> > omitting system objects which should get its own general option.
>
pg_dump never dumps system objects, so I don't see a need for
> a switch to tell it not to.
>
>
I considered pg_class to be a system object, which was dumped under -t '*'

$ pg_dump -U postgres --schema-only -t '*' | grep 'CREATE.*pg_class'
CREATE TABLE pg_catalog.pg_class (
CREATE UNIQUE INDEX pg_class_oid_index ON pg_catalog.pg_class USING btree
(oid);
CREATE UNIQUE INDEX pg_class_relname_nsp_index ON pg_catalog.pg_class USING
btree (relname, relnamespace);
CREATE INDEX pg_class_tblspc_relfilenode_index ON pg_catalog.pg_class USING
btree (reltablespace, relfilenode);

$ psql -U postgres -c 'select version();'
 version

--
 PostgreSQL 13.6 (Ubuntu 13.6-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

David J.

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

"David G. Johnston"  writes:
> I don't find the --objectype-only option to be desirable.  psql
> --tables-only --functions-only just seems odd, no longer are they "only".
> I would go with --function-all (and maybe --function-system and
> --function-user) if going down this path but the wildcard feature can
> handle this just fine and we want that feature anyway.

Agreed.  "--function=*" is more general than "--function-only",
and shorter too, so what's not to like?

> Except succinctly
> omitting system objects which should get its own general option.

pg_dump never dumps system objects, so I don't see a need for
a switch to tell it not to.

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

Laetitia Avrot  writes:
> Thank you so much for your suggestion. I was really excited to find a
> generic term for Functions and Procedures, but "routine" also includes
> aggregation functions which I had excluded from my feature (see Postgres
> Glossary here:
> https://www.postgresql.org/docs/14/glossary.html#GLOSSARY-ROUTINE).

> I had decided not to include aggregate functions when I designed my patch
> because I thought most users wouldn't expect them in the result file. Was I
> wrong?

I'd vote for treating them as functions for this purpose.  I'd put
them in the same category as window functions: we use a separate
name for them for historical reasons, but they still walk and quack
pretty much like functions.

regards, tom lane

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Fri, Mar 25, 2022 at 9:44 AM Laetitia Avrot 
wrote:

>
> Actually, I thought of it after the --schema-only flag (which is kind of
> confusing, because it won't export only schema creation DDL).
>

--schema-only is talking about the different sections of the dump file, not
namespace schema objects in the database.

> My problem is how do you think we could get all the stored
> procedures/functions at once? --function=* ? It seems to me that exporting
> everything at once is the main use case (I'd be happy to be proven wrong),
> and it does not feel intuitive to use --function=*.
>

How does one specify "all but only tables" today?  If the answer is "we
don't" then we get to decide now.  I have no qualms with --objecttype=*
meaning all.

(goes and checks)
pg_dump --schema-only -t '*'  # indeed outputs all relations.  Annoyingly,
this seems to include pg_catalog relations as well, so one basically has to
specify --exclude-table='pg_catalog.*' as well in the typical case of only
wanting user objects.  Solving this with a new --no-system-objects that
would apply firstly seems like a nice feature to this pre-existing
behavior.  One might think that --exclude-schema='pg_catalog' would work,
but it is doesn't by design.  The design choice seems solid for user-space
schema names so just dealing with the system objects is my preferred
solution.

I don't find the --objectype-only option to be desirable.  psql
--tables-only --functions-only just seems odd, no longer are they "only".
I would go with --function-all (and maybe --function-system and
--function-user) if going down this path but the wildcard feature can
handle this just fine and we want that feature anyway.  Except succinctly
omitting system objects which should get its own general option.

David J.

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Hello David,

thank you for your interest in that patch.

Le ven. 25 mars 2022 à 01:17, David G. Johnston 
a écrit :

> On Thu, Mar 24, 2022 at 4:42 PM Chapman Flack 
> wrote:
>
>> On 03/27/21 08:57, Andrew Dunstan wrote:
>> > We can bikeshed the name of the flag at some stage. --procedures-only
>> > might also make sense
>>
>> Any takers for --routines-only ?
>>
>> "Routine" is the genuine, ISO SQL umbrella term for a function or
>> procedure, and we have used it that way in our docs and glossary.
>>
>>
> Regardless of the prefix name choice neither blobs, tables, nor schemas
> use the "-only" suffix so I don't see that this should.  I have no issue if
> we add three options for this: --routine/--procedure/--function (these are
> singular because --table and --schema are singular)
>

Actually, I thought of it after the --schema-only flag (which is kind of
confusing, because it won't export only schema creation DDL). I agree to
keep the flag name singular if we're using a pattern to cherry-pick the
objects we want. My problem is how do you think we could get all the stored
procedures/functions at once? --function=* ? It seems to me that exporting
everything at once is the main use case (I'd be happy to be proven wrong),
and it does not feel intuitive to use --function=*.

How would you see to create 3 flags: --functions-only /
--function= / --exclude-function= ?
And then we could create the same series of 3 flags for other objects.
Would that be too verbose?

>
> --blobs and --no-blobs are special so let us just build off of the API
> already implemented for --table/--exclude-table
>

> No short option is required, and honestly I don't think it is worthwhile
> to take up short options for this, acknowledging that we are leaving -t/-T
> (and -n/-N) in place for legacy support.
>

I agree


>
> --blobs reacts to these additional object types in the same manner that it
> reacts to --table.  As soon as any of these object type inclusion options
> is specified nothing except the options that are specified will be output.
> Both data and schema, though, for most object types, data is not relevant.
> If schema is not output then options that control schema content objects
> only are ignored.
>
> The --exclude-* options behave in the same way as defined for -t/-T,
> specifically the note in -T about when both are present.
>
> As with tables, the affirmative version of these overrides any --schema
> (-n/-N) specification provided.  But the --exclude-* versions of these do
> omit the named objects from the dump should they have been selected by
> --schema.
>

That's fine with me.

Have a nice day,

Lætitia


>
> David J.
>
>

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Hello Chapman,

Le ven. 25 mars 2022 à 00:42, Chapman Flack  a
écrit :

> On 03/27/21 08:57, Andrew Dunstan wrote:
> > We can bikeshed the name of the flag at some stage. --procedures-only
> > might also make sense
>
> Any takers for --routines-only ?
>
> "Routine" is the genuine, ISO SQL umbrella term for a function or
> procedure, and we have used it that way in our docs and glossary.
>

Thank you so much for your suggestion. I was really excited to find a
generic term for Functions and Procedures, but "routine" also includes
aggregation functions which I had excluded from my feature (see Postgres
Glossary here:
https://www.postgresql.org/docs/14/glossary.html#GLOSSARY-ROUTINE).

I had decided not to include aggregate functions when I designed my patch
because I thought most users wouldn't expect them in the result file. Was I
wrong?

Have a nice day,

Lætitia


>
> Regards,
> -Chap
>

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Hello Tom,

Le ven. 25 mars 2022 à 00:18, Tom Lane  a écrit :

> Daniel Gustafsson  writes:
> >> On 24 Mar 2022, at 23:38, Greg Stark  wrote:
> >> It looks like this discussion has reached a bit of an impasse with Tom
> >> being against this approach and Michael and Daniel being for it. It
> >> doesn't look like it's going to get committed this commitfest, shall
> >> we move it forward or mark it returned with feedback?
>
> > Lætitia mentioned the other day off-list that she was going to try and
> update
> > this patch with the pattern support proposed, so hopefully we will hear
> from
> > her shortly on that.
>
> To clarify: I'm not against having an easy way to dump all-and-only
> functions.  What concerns me is having such a feature that's designed
> in isolation, without a plan for anything else.  I'd like to create
> some sort of road map for future selective-dumping options, and then
> we can make sure that this feature fits into the bigger picture.
> Otherwise we're going to end up with an accumulation of warts, with
> inconsistent naming and syntax, and who knows what other sources of
> confusion.
>

This totally makes sense.

Have a nice day,

Lætitia


>
> regards, tom lane
>

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Laetitia Avrot]

Hello Michael,

Le mar. 25 janv. 2022 à 06:49, Michael Paquier  a
écrit :

> On Tue, Nov 09, 2021 at 03:23:07PM +0100, Daniel Gustafsson wrote:
> > Looking at this thread I think it makes sense to go ahead with this
> patch.  The
> > filter functionality worked on in another thread is dealing with
> cherry-picking
> > certain objects where this is an all-or-nothing switch, so I don't think
> they
> > are at odds with each other.
>
> Including both procedures and functions sounds natural from here.  Now
> I have a different question, something that has not been discussed in
> this thread at all.  What about patterns?  Switches like --table or
> --extension are able to digest a psql-like pattern to decide which
> objects to dump.  Is there a reason not to have this capability for
> this new switch with procedure names?  I mean to handle the case
> without the function arguments, even if the same name is used by
> multiple functions with different arguments.
>

Thank you for this suggestion.
We have --schema-only flag to export only the structure and then we have
--schema= flag to export the schemas following a pattern.
I don't think both features can't exist for functions (and stored
procedures), but I see them as different features. We could have
--functions-only and --function=.
In my humble opinion, the lack of --function= feature should block
this patch.

Have a great day,
Lætitia



> --
> Michael
>

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Thu, Mar 24, 2022 at 5:40 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > The extension object type does not seem to have gotten the
> > --exclude-extension capability that it would need to conform to the
> general
> > design exemplified by --table and hopefully extended out to the routine
> > object types.
>
> We're not going to instantly build out every feature that would be
> suggested by a roadmap.  However, I see in what you just wrote
> a plausible roadmap: eventually, all or most object types should
> have pg_dump switches comparable to, and syntactically aligned
> with, the --table and --exclude-table switches.  The expectation
> would be that if any of these selective-dump switches appear,
> then only objects matching at least one of them (and not matching
> any --exclude switch) will be dumped.  So for example
>
> pg_dump --table=foo* --function=bar*
>
> dumps tables whose names start with foo, and functions whose
> names start with bar, and nothing else.  (We'd need to spell out
> how these things interact with --schema, too.)
>
> In this scheme, Lætitia's desired functionality should be spelled
> "--function=*", or possibly "--routine=*", depending on what she
> wanted to happen with procedures.
>
> Thoughts?
>
>
My longer first post today [1] indeed was that roadmap you were looking
for.  I then re-read the part about --extension and realized I had missed
its existence and felt it desirable to note that within that roadmap the
existing --extension object type did not conform.

David J.

https://www.postgresql.org/message-id/CAKFQuwYcw%2BA%2BMyDQoVahKkEqJtgih3c1i-JLY_YPMucNfgQDkg%40mail.gmail.com

I think Gmail is messing with me by adding an unintended "Re:" to the
subject line which probably put my first response outside the thread.

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

"David G. Johnston"  writes:
> The extension object type does not seem to have gotten the
> --exclude-extension capability that it would need to conform to the general
> design exemplified by --table and hopefully extended out to the routine
> object types.

We're not going to instantly build out every feature that would be
suggested by a roadmap.  However, I see in what you just wrote
a plausible roadmap: eventually, all or most object types should
have pg_dump switches comparable to, and syntactically aligned
with, the --table and --exclude-table switches.  The expectation
would be that if any of these selective-dump switches appear,
then only objects matching at least one of them (and not matching
any --exclude switch) will be dumped.  So for example

pg_dump --table=foo* --function=bar*

dumps tables whose names start with foo, and functions whose
names start with bar, and nothing else.  (We'd need to spell out
how these things interact with --schema, too.)

In this scheme, Lætitia's desired functionality should be spelled
"--function=*", or possibly "--routine=*", depending on what she
wanted to happen with procedures.

Thoughts?

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Mon, Jan 24, 2022 at 10:49 PM Michael Paquier 
wrote:

> What about patterns?  Switches like --table or
> --extension are able to digest a psql-like pattern to decide which
> objects to dump.
>

The extension object type does not seem to have gotten the
--exclude-extension capability that it would need to conform to the general
design exemplified by --table and hopefully extended out to the routine
object types.

David J.

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[David G. Johnston]

On Thu, Mar 24, 2022 at 4:42 PM Chapman Flack  wrote:

> On 03/27/21 08:57, Andrew Dunstan wrote:
> > We can bikeshed the name of the flag at some stage. --procedures-only
> > might also make sense
>
> Any takers for --routines-only ?
>
> "Routine" is the genuine, ISO SQL umbrella term for a function or
> procedure, and we have used it that way in our docs and glossary.
>
>
Regardless of the prefix name choice neither blobs, tables, nor schemas use
the "-only" suffix so I don't see that this should.  I have no issue if we
add three options for this: --routine/--procedure/--function (these are
singular because --table and --schema are singular)

--blobs and --no-blobs are special so let us just build off of the API
already implemented for --table/--exclude-table

No short option is required, and honestly I don't think it is worthwhile to
take up short options for this, acknowledging that we are leaving -t/-T
(and -n/-N) in place for legacy support.

--blobs reacts to these additional object types in the same manner that it
reacts to --table.  As soon as any of these object type inclusion options
is specified nothing except the options that are specified will be output.
Both data and schema, though, for most object types, data is not relevant.
If schema is not output then options that control schema content objects
only are ignored.

The --exclude-* options behave in the same way as defined for -t/-T,
specifically the note in -T about when both are present.

As with tables, the affirmative version of these overrides any --schema
(-n/-N) specification provided.  But the --exclude-* versions of these do
omit the named objects from the dump should they have been selected by
--schema.

David J.

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Chapman Flack]

On 03/27/21 08:57, Andrew Dunstan wrote:
> We can bikeshed the name of the flag at some stage. --procedures-only
> might also make sense

Any takers for --routines-only ?

"Routine" is the genuine, ISO SQL umbrella term for a function or
procedure, and we have used it that way in our docs and glossary.

Regards,
-Chap

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

Daniel Gustafsson  writes:
>> On 24 Mar 2022, at 23:38, Greg Stark  wrote:
>> It looks like this discussion has reached a bit of an impasse with Tom
>> being against this approach and Michael and Daniel being for it. It
>> doesn't look like it's going to get committed this commitfest, shall
>> we move it forward or mark it returned with feedback?

> Lætitia mentioned the other day off-list that she was going to try and update
> this patch with the pattern support proposed, so hopefully we will hear from
> her shortly on that.

To clarify: I'm not against having an easy way to dump all-and-only
functions.  What concerns me is having such a feature that's designed
in isolation, without a plan for anything else.  I'd like to create
some sort of road map for future selective-dumping options, and then
we can make sure that this feature fits into the bigger picture.
Otherwise we're going to end up with an accumulation of warts, with
inconsistent naming and syntax, and who knows what other sources of
confusion.

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Daniel Gustafsson]

> On 24 Mar 2022, at 23:38, Greg Stark  wrote:

> It looks like this discussion has reached a bit of an impasse with Tom
> being against this approach and Michael and Daniel being for it. It
> doesn't look like it's going to get committed this commitfest, shall
> we move it forward or mark it returned with feedback?

Lætitia mentioned the other day off-list that she was going to try and update
this patch with the pattern support proposed, so hopefully we will hear from
her shortly on that.

--
Daniel Gustafsson   https://vmware.com/

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Greg Stark]

It looks like this discussion has reached a bit of an impasse with Tom
being against this approach and Michael and Daniel being for it. It
doesn't look like it's going to get committed this commitfest, shall
we move it forward or mark it returned with feedback?

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Michael Paquier]

On Tue, Nov 09, 2021 at 03:23:07PM +0100, Daniel Gustafsson wrote:
> Looking at this thread I think it makes sense to go ahead with this patch.  
> The
> filter functionality worked on in another thread is dealing with 
> cherry-picking
> certain objects where this is an all-or-nothing switch, so I don't think they
> are at odds with each other.

Including both procedures and functions sounds natural from here.  Now
I have a different question, something that has not been discussed in
this thread at all.  What about patterns?  Switches like --table or
--extension are able to digest a psql-like pattern to decide which
objects to dump.  Is there a reason not to have this capability for
this new switch with procedure names?  I mean to handle the case
without the function arguments, even if the same name is used by
multiple functions with different arguments.
--
Michael


signature.asc
Description: PGP signature

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Daniel Gustafsson]

> On 30 Jul 2021, at 12:55, Lætitia Avrot  wrote:
> 
> > On Fri, Jul 9, 2021 at 4:43 PM Tomas Vondra  
> > wrote:
> > 
> > The main question I have is whether this should include procedures. I'd
> > probably argue procedures should be considered different from functions
> > (i.e. requiring a separate --procedures-only option), because it pretty
> > much is meant to be a separate object type. We don't allow calling DROP
> > FUNCTION on a procedure, etc. It'd be silly to introduce an unnecessary
> > ambiguity in pg_dump and have to deal with it sometime later.
> 
> I respectfully disagree. In psql, the `\ef` and `\df` metacommands will also 
> list procedures, not just functions.

I tend to agree that we should include both, while they are clearly different I
don't think it would be helpful in this case to distinguish.

Looking at this thread I think it makes sense to go ahead with this patch.  The
filter functionality worked on in another thread is dealing with cherry-picking
certain objects where this is an all-or-nothing switch, so I don't think they
are at odds with each other.

--
Daniel Gustafsson   https://vmware.com/

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Lætitia Avrot]

>
>
> > On Fri, Jul 9, 2021 at 4:43 PM Tomas Vondra <
> tomas.von...@enterprisedb.com> wrote:
> >
> > The main question I have is whether this should include procedures. I'd
> > probably argue procedures should be considered different from functions
> > (i.e. requiring a separate --procedures-only option), because it pretty
> > much is meant to be a separate object type. We don't allow calling DROP
> > FUNCTION on a procedure, etc. It'd be silly to introduce an unnecessary
> > ambiguity in pg_dump and have to deal with it sometime later.
>
>
I respectfully disagree. In psql, the `\ef` and `\df` metacommands will
also list procedures, not just functions. So at one point we agreed to
consider for this client that functions were close enough to procedures to
use a simple metacommand to list/display without distinction. Why should it
be different for `pg_dump` ?

Have a nice day,

Lætitia

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Ryan Lambert]

> On Sat, Jul 10, 2021 at 5:06 AM Tomas Vondra <
tomas.von...@enterprisedb.com> wrote:
> On 7/10/21 1:43 AM, Tom Lane wrote:
>> Tomas Vondra  writes:
>>> The main question I have is whether this should include procedures.
>>
>> I feel a bit uncomfortable about sticking this sort of limited-purpose
>> selectivity mechanism into pg_dump.  I'd rather see a general filter
>> method that can select object(s) of any type.  Pavel was doing some
>> work towards that awhile ago, though I think he got frustrated about
>> the lack of consensus on details.  Which is a problem, but I don't
>> think the solution is to accrue more and more independently-designed-
>> and-implemented features that each solve some subset of the big problem.
>>
>
> I'm not against introducing such general filter mechanism, but why
> should it block this patch? I'd understand it the patch was adding a lot
> of code, but that's not the case - it's tiny. And we already have
> multiple filter options (to pick tables, schemas, extensions, ...).

> And if there's no consensus on details of Pavel's patch after multiple
> commitfests, how likely is it it'll start moving forward?

It seems to me that pg_dump already has plenty of limited-purpose options
for selectivity, adding this patch seems to fit in with the norm. I'm in
favor of this patch because it works in the same way the community is
already used to and meets the need. The other patch referenced upstream
appears to be intended to solve a specific problem encountered with very
long commands.  It is also introducing a new way of working with pg_dump
via a config file, and honestly I've never wanted that type of feature. Not
saying that wouldn't be useful, but that has not been a pain point for me
and seems overly complex. For the use cases I imagine this patch will help
with, being required to go through a config file seems excessive vs pg_dump
--functions-only.

> On Fri, Jul 9, 2021 at 4:43 PM Tomas Vondra 
wrote:
>
> The main question I have is whether this should include procedures. I'd
> probably argue procedures should be considered different from functions
> (i.e. requiring a separate --procedures-only option), because it pretty
> much is meant to be a separate object type. We don't allow calling DROP
> FUNCTION on a procedure, etc. It'd be silly to introduce an unnecessary
> ambiguity in pg_dump and have to deal with it sometime later.

+1

*Ryan Lambert*
RustProof Labs

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tomas Vondra]

On 7/10/21 1:43 AM, Tom Lane wrote:

Tomas Vondra  writes:

The main question I have is whether this should include procedures.


I feel a bit uncomfortable about sticking this sort of limited-purpose
selectivity mechanism into pg_dump.  I'd rather see a general filter
method that can select object(s) of any type.  Pavel was doing some
work towards that awhile ago, though I think he got frustrated about
the lack of consensus on details.  Which is a problem, but I don't
think the solution is to accrue more and more independently-designed-
and-implemented features that each solve some subset of the big problem.



I'm not against introducing such general filter mechanism, but why 
should it block this patch? I'd understand it the patch was adding a lot 
of code, but that's not the case - it's tiny. And we already have 
multiple filter options (to pick tables, schemas, extensions, ...).


And if there's no consensus on details of Pavel's patch after multiple 
commitfests, how likely is it it'll start moving forward?



regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Justin Pryzby]

On Fri, Jul 09, 2021 at 07:43:02PM -0400, Tom Lane wrote:
> Tomas Vondra  writes:
> > The main question I have is whether this should include procedures.
> 
> I feel a bit uncomfortable about sticking this sort of limited-purpose
> selectivity mechanism into pg_dump.  I'd rather see a general filter
> method that can select object(s) of any type.  Pavel was doing some
> work towards that awhile ago, though I think he got frustrated about
> the lack of consensus on details.

That's this: https://commitfest.postgresql.org/33/2573/

-- 
Justin

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

Tomas Vondra  writes:
> The main question I have is whether this should include procedures.

I feel a bit uncomfortable about sticking this sort of limited-purpose
selectivity mechanism into pg_dump.  I'd rather see a general filter
method that can select object(s) of any type.  Pavel was doing some
work towards that awhile ago, though I think he got frustrated about
the lack of consensus on details.  Which is a problem, but I don't
think the solution is to accrue more and more independently-designed-
and-implemented features that each solve some subset of the big problem.

regards, tom lane

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tomas Vondra]

Hi,

I took a quick look at the patch today. There was some minor bitrot
requiring a rebase, so I attach the rebased patch as v3.

The separate 0002 part contains some minor fixes - a couple
typos/rewording in the docs (would be good if a native speaker looked at
it, thought), and a slightly reworked chunk of code from pg_dump.c. The
change is more a matter of personal preference than correctness - it
just seems simpler this way, but ymmv. And I disliked that the comment
said "If we have to export only the functions .." but the if condition
was actually the exact opposite of that.

The main question I have is whether this should include procedures. I'd
probably argue procedures should be considered different from functions
(i.e. requiring a separate --procedures-only option), because it pretty
much is meant to be a separate object type. We don't allow calling DROP
FUNCTION on a procedure, etc. It'd be silly to introduce an unnecessary
ambiguity in pg_dump and have to deal with it sometime later.

I wonder if we should allow naming a function to dump, similarly to how
--table works for tables, for example.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
>From 1acb9eb47277e6c3cc7d46f1fb5bef0cf669cad6 Mon Sep 17 00:00:00 2001
From: Tomas Vondra 
Date: Fri, 9 Jul 2021 22:47:23 +0200
Subject: [PATCH 1/2] v3

---
 doc/src/sgml/ref/pg_dump.sgml   | 39 --
 src/bin/pg_dump/pg_backup.h |  1 +
 src/bin/pg_dump/pg_dump.c   | 45 +++--
 src/test/modules/test_pg_dump/t/001_base.pl |  9 +
 4 files changed, 86 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index a6c0788592..fcc3fc663c 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -123,6 +123,11 @@ PostgreSQL documentation
 Table data, large objects, and sequence values are dumped.

 
+   
+This option cannot be used together with the --schema-only,
+or --functions-only option.
+   
+

 This option is similar to, but for historical reasons not identical
 to, specifying --section=data.
@@ -136,13 +141,14 @@ PostgreSQL documentation
   

 Include large objects in the dump.  This is the default behavior
-except when --schema, --table, or
---schema-only is specified.  The -b
+except when --schema, --table,
+--schema-only, or --functions-only
+is specified.  The -b
 switch is therefore only useful to add large objects to dumps
 where a specific schema or table has been requested.  Note that
 blobs are considered data and therefore will be included when
 --data-only is used, but not
-when --schema-only is.
+when --schema-only or --functions-only are.

   
  
@@ -582,6 +588,11 @@ PostgreSQL documentation
 be dumped.

 
+   
+This option cannot be used together with the --functions-only
+option.
+   
+

 
  When -t is specified, pg_dump
@@ -790,7 +801,22 @@ PostgreSQL documentation
  
 
  
-  --if-exists
+  --functions-only
+  
+   
+Dmup only the stored functions and stored procedure definitions, not data, not
+other objects definition.
+   
+
+   
+This option is the same than --schema-only but restricted
+to stored functions and stored procedures.
+   
+  
+ 
+
+ 
+ --if-exists
   

 Use conditional commands (i.e., add an IF EXISTS
@@ -819,6 +845,11 @@ PostgreSQL documentation
 The only exception is that an empty pattern is disallowed.

 
+   
+This option cannot be used together with the --schema-only,
+or --functions-only option.
+   
+

 
  When --include-foreign-data is specified,
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 3c1cd858a8..e332d06317 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -153,6 +153,7 @@ typedef struct _dumpOptions
 	/* flags for various command-line long options */
 	int			disable_dollar_quoting;
 	int			column_inserts;
+	int			functions_only;
 	int			if_exists;
 	int			no_comments;
 	int			no_security_labels;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 321152151d..7837656085 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -380,6 +380,7 @@ main(int argc, char **argv)
 		{"enable-row-security", no_argument, &dopt.enable_row_security, 1},
 		{"exclude-table-data", required_argument, NULL, 4},
 		{"extra-float-digits", required_argument, NULL, 8},
+		{"functions-only", no_argument, &dopt.functions_only, 1},
 		{"if-exists", no_argument, &dopt.if_exists, 1},
 		{"inserts", no_argument, NULL, 9},
 		{"

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[ahsan hadi]

The following review has been posted through the commitfest application:
make installcheck-world:  tested, failed
Implements feature:   tested, passed
Spec compliant:   tested, passed
Documentation:not tested

Few minor comments :

- The latest patch has some hunk failures
- Regression with master has many failures with/without the patch, it is 
difficult to tell if the patch is causing any failures.
- This is probably intended behaviour that --functions-only switch is also 
dumping stored procedures? 
- If i create a procedure with 
LANGUAGE plpgsql
SECURITY INVOKER
It is not including "SECURITY INVOKER" in the dump. That's probably because 
INVOKER is default security rights.

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Lætitia Avrot]

>
>
>> Using --functions-only along with --table= does not error out and
> warn the user, instead it creates a dump containing only the SET commands.
> An error similar to using --functions-only along with --data-only seems
> like a good idea.
>
>
Thank you for giving my patch a try.
I added the new if statement, so that the program will error should the
`--functions-only` be used alongside the `--table` option.

The patch has been added to the next commifest.

Have a nice day,

Lætitia


pg_dump_functions_only_v0_2.patch
Description: Binary data

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Ryan Lambert]

On Sat, Mar 27, 2021 at 6:23 AM Lætitia Avrot 
wrote:

> Hello,
>
> You'll find enclosed the first version of my patch.
>

I tested a couple simple use cases.  This is great, Thank you!


> I did not include the possibility of using a file to list tables to be
> exported as Tom suggested because I genuinely think it is a totally
> different matter. It does not mean I'm not open to the possibility, it just
> felt weird.
>
> The patch allows using a `--functions-only` flag in `pg_dump` to export
> only functions and stored procedures. My code was build and passed tests on
> the last master branch of the PostgreSQL project. I added regression tests.
> Documentation has been updated too and generation of the documentation
> (HTML, man page, pdf in A4 and letter US format) has been tested
> successfully.
>
> I did not add a warning in the documentation that the file provided might
> end up in a not restorable file or in a file restoring broken functions or
> procedures. Do you think I should?
>

The docs for both the --table and --schema options do warn about this.  On
the other hand, --data-only has no such warning. I'd lean towards matching
--data-only for this.


>
> I don't know if this patch has any impact on performance. I guess that
> adding 4 if statements will slow down `pg_dump` a little bit.
>
> Have a nice day,
>
> Lætitia
>

Using --functions-only along with --table= does not error out and
warn the user, instead it creates a dump containing only the SET commands.
An error similar to using --functions-only along with --data-only seems
like a good idea.

Cheers,

*Ryan Lambert*
RustProof Labs

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Andrew Dunstan]

On 3/27/21 8:22 AM, Lætitia Avrot wrote:
> Hello,
>
> You'll find enclosed the first version of my patch. I did not include
> the possibility of using a file to list tables to be exported as Tom
> suggested because I genuinely think it is a totally different matter.
> It does not mean I'm not open to the possibility, it just felt weird.
>
> The patch allows using a `--functions-only` flag in `pg_dump` to
> export only functions and stored procedures. My code was build and
> passed tests on the last master branch of the PostgreSQL project. I
> added regression tests. Documentation has been updated too and
> generation of the documentation (HTML, man page, pdf in A4 and letter
> US format) has been tested successfully.


We can bikeshed the name of the flag at some stage. --procedures-only
might also make sense


>
> I did not add a warning in the documentation that the file provided
> might end up in a not restorable file or in a file restoring broken
> functions or procedures. Do you think I should?


No, I don't think it's any different from any of the other similar switches.


>
> I don't know if this patch has any impact on performance. I guess that
> adding 4 if statements will slow down `pg_dump` a little bit.
>
>

Not likely to be noticeable.


Please add this to the next commitfest.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Lætitia Avrot]

Hello,

You'll find enclosed the first version of my patch. I did not include the
possibility of using a file to list tables to be exported as Tom suggested
because I genuinely think it is a totally different matter. It does not
mean I'm not open to the possibility, it just felt weird.

The patch allows using a `--functions-only` flag in `pg_dump` to export
only functions and stored procedures. My code was build and passed tests on
the last master branch of the PostgreSQL project. I added regression tests.
Documentation has been updated too and generation of the documentation
(HTML, man page, pdf in A4 and letter US format) has been tested
successfully.

I did not add a warning in the documentation that the file provided might
end up in a not restorable file or in a file restoring broken functions or
procedures. Do you think I should?

I don't know if this patch has any impact on performance. I guess that
adding 4 if statements will slow down `pg_dump` a little bit.

Have a nice day,

Lætitia

Le mer. 17 mars 2021 à 18:16, Tom Lane  a écrit :

> Vik Fearing  writes:
> > On 3/17/21 6:00 PM, Lætitia Avrot wrote:
> >> However, when I finally got the time to look at it in detail, I found
> out
> >> there was no way to solve the dependencies in the functions and
> procedures,
> >> so that the exported file, when re-played could lead to invalid objects.
> >>  ...
> >> So, my question is: what do you think about such a feature? is it worth
> it?
>
> > Yes, it is absolutely worth it to be able to extract just the functions
> > from a database.  I have wanted this several times.
>
> Selective dumps always have a risk of not being restorable on their
> own; I don't see that "functions only" is noticeably less safe than
> "just these tables", or other cases that we support already.
>
> What I'm wondering about is how this might interact with the
> discussion at [1].
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/message-id/flat/CAFj8pRB10wvW0CC9Xq=1XDs=zcqxer3cblcnza+qix4cuh-...@mail.gmail.com
>


pg_dump_functions_only_v0_1.patch
Description: Binary data

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Tom Lane]

Vik Fearing  writes:
> On 3/17/21 6:00 PM, Lætitia Avrot wrote:
>> However, when I finally got the time to look at it in detail, I found out
>> there was no way to solve the dependencies in the functions and procedures,
>> so that the exported file, when re-played could lead to invalid objects.
>>  ...
>> So, my question is: what do you think about such a feature? is it worth it?

> Yes, it is absolutely worth it to be able to extract just the functions
> from a database.  I have wanted this several times.

Selective dumps always have a risk of not being restorable on their
own; I don't see that "functions only" is noticeably less safe than
"just these tables", or other cases that we support already.

What I'm wondering about is how this might interact with the
discussion at [1].

regards, tom lane

[1] 
https://www.postgresql.org/message-id/flat/CAFj8pRB10wvW0CC9Xq=1XDs=zcqxer3cblcnza+qix4cuh-...@mail.gmail.com

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Fabrízio de Royes Mello]

On Wed, Mar 17, 2021 at 2:00 PM Lætitia Avrot 
wrote:
>
> Hey hackers,
>
> I had this idea, that I raised and cherished like my baby to add a switch
in `pg_dump` to allow exporting stored functions (and procedures) only.
>
> However, when I finally got the time to look at it in detail, I found out
there was no way to solve the dependencies in the functions and procedures,
so that the exported file, when re-played could lead to invalid objects.
>
> So, I decided this would not make Postgres better and decide to walk off
this patch.
>
> Anyhow, when sharing my thoughts, several people told me to ask the
community about adding this feature because this could be useful in some
use cases. Another argument is that should you have all your functions in
one schema and your tables in another, exporting only the function schema
will lead to the same kind of file that could lead to invalid objects
created when the file is re-played against a database that does not have
the tables.
>
> Of course, the documentation would add a warning against object
invalidity should only the stored functions/procedures be exported.
>
> So, my question is: what do you think about such a feature? is it worth
it?
>

Make total sense since we already have --function=NAME(args) on pg_restore
and it doesn't solve dependencies... so we can add it to also export
function/procedure contents.

+1 for general idea.

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

[Vik Fearing]

On 3/17/21 6:00 PM, Lætitia Avrot wrote:
> Hey hackers,
> 
> I had this idea, that I raised and cherished like my baby to add a switch
> in `pg_dump` to allow exporting stored functions (and procedures) only.
> 
> However, when I finally got the time to look at it in detail, I found out
> there was no way to solve the dependencies in the functions and procedures,
> so that the exported file, when re-played could lead to invalid objects.
> 
> So, I decided this would not make Postgres better and decide to walk off
> this patch.
> 
> Anyhow, when sharing my thoughts, several people told me to ask the
> community about adding this feature because this could be useful in some
> use cases. Another argument is that should you have all your functions in
> one schema and your tables in another, exporting only the function schema
> will lead to the same kind of file that could lead to invalid objects
> created when the file is re-played against a database that does not have
> the tables.
> 
> Of course, the documentation would add a warning against object invalidity
> should only the stored functions/procedures be exported.
> 
> So, my question is: what do you think about such a feature? is it worth it?

Yes, it is absolutely worth it to be able to extract just the functions
from a database.  I have wanted this several times.
-- 
Vik Fearing

pg_dump new feature: exporting functions only. Bad or good idea ?

[Lætitia Avrot]

Hey hackers,

I had this idea, that I raised and cherished like my baby to add a switch
in `pg_dump` to allow exporting stored functions (and procedures) only.

However, when I finally got the time to look at it in detail, I found out
there was no way to solve the dependencies in the functions and procedures,
so that the exported file, when re-played could lead to invalid objects.

So, I decided this would not make Postgres better and decide to walk off
this patch.

Anyhow, when sharing my thoughts, several people told me to ask the
community about adding this feature because this could be useful in some
use cases. Another argument is that should you have all your functions in
one schema and your tables in another, exporting only the function schema
will lead to the same kind of file that could lead to invalid objects
created when the file is re-played against a database that does not have
the tables.

Of course, the documentation would add a warning against object invalidity
should only the stored functions/procedures be exported.

So, my question is: what do you think about such a feature? is it worth it?

Have a nice day,

Lætitia