Refactor pg_dump as a library?

On 14 April 2016 at 18:16, Jakob Egger <jakob@eggerapps.at> wrote:

Would anybody else be interested in a pg_dump library?

Yes. It comes up a lot, but so far nobody's had the time and energy to
actually do the required refactoring.

I'm the developer of a PostgreSQL GUI client, and I am looking for ways to

integrate pg_dump into my application. The main use case would be to get
the dump of individual tables (for example, when you want to create a table
similar to an existing one)

I'd find a pg_get_tabledef(...) built-in function more interesting for this
particular purpose than pg_dump as a library would be. We already have
pg_get_viewdef(...), pg_get_functiondef(...) etc.

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

On 04/14/2016 12:22 PM, Craig Ringer wrote:

I'd find a pg_get_tabledef(...) built-in function more interesting for
this particular purpose than pg_dump as a library would be. We already
have pg_get_viewdef(...), pg_get_functiondef(...) etc.

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL. I would not be surprised if we have some low
probability bugs which cause inconsistent backups there which just has
not hit enough people yet to have been reported. And this problem will
only get worse as we reduce the lock level of more DDL.

Andreas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 4/14/16 6:16 AM, Andreas Karlsson wrote:

On 04/14/2016 12:22 PM, Craig Ringer wrote:

I'd find a pg_get_tabledef(...) built-in function more interesting for
this particular purpose than pg_dump as a library would be. We already
have pg_get_viewdef(...), pg_get_functiondef(...) etc.

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL. I would not be surprised if we have some low
probability bugs which cause inconsistent backups there which just has
not hit enough people yet to have been reported. And this problem will
only get worse as we reduce the lock level of more DDL.

The other issue specific to pg_dump is it often needs to do something
different than what pg_get_*def would do to support version upgrades.

I agree that it would be nice to have better DDL generation capabilities
in the database, but I think the right way to do that is to create the
pg_dump library and then wrap that as a (version-specific) extension.
That way you could loan the pg_dump-9.5 extension in a 9.3 database if
you wanted.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2016-04-14 13:16:20 +0200, Andreas Karlsson wrote:

I am personally not a fan of the pg_get_Xdef() functions due to their heavy
reliance on the syscache which feels rather unsafe in combination with
concurrent DDL.

I'm not sure I find that convincing: The state portrayed by the syscache
is th state COPY/SELECT et al will be using. I think the angle to
attack this is rather to allow blocking 'globally visible' DDL
efficiently and correctly, rather than the hack pg_dump is using right now.

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 4/14/16 7:16 AM, Andreas Karlsson wrote:

On 04/14/2016 12:22 PM, Craig Ringer wrote:

I'd find a pg_get_tabledef(...) built-in function more interesting for
this particular purpose than pg_dump as a library would be. We already
have pg_get_viewdef(...), pg_get_functiondef(...) etc.

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL. I would not be surprised if we have some low
probability bugs which cause inconsistent backups there which just has
not hit enough people yet to have been reported. And this problem will
only get worse as we reduce the lock level of more DDL.

As far as I know pg_dump share locks everything before it starts so
there shouldn't be issues with concurrent DDL. Try creating a new
inherited table with FKs, etc. during a pg_dump and you'll see lots of
fun lock waits.

--
-David
david@pgmasters.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David Steele <david@pgmasters.net> writes:

On 4/14/16 7:16 AM, Andreas Karlsson wrote:

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL.

As far as I know pg_dump share locks everything before it starts so
there shouldn't be issues with concurrent DDL. Try creating a new
inherited table with FKs, etc. during a pg_dump and you'll see lots of
fun lock waits.

I think pg_dump is reasonably proof against DDL on tables. It is not
at all proof against DDL on other sorts of objects, such as functions,
because of the fact that the syscache will follow catalog updates that
occur after pg_dump's transaction snapshot.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 4/14/16 1:33 PM, Tom Lane wrote:

David Steele <david@pgmasters.net> writes:

On 4/14/16 7:16 AM, Andreas Karlsson wrote:

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL.

As far as I know pg_dump share locks everything before it starts so
there shouldn't be issues with concurrent DDL. Try creating a new
inherited table with FKs, etc. during a pg_dump and you'll see lots of
fun lock waits.

I think pg_dump is reasonably proof against DDL on tables. It is not
at all proof against DDL on other sorts of objects, such as functions,
because of the fact that the syscache will follow catalog updates that
occur after pg_dump's transaction snapshot.

Hmm, OK. I'll need to go look at that.

I thought that the backend running the pg_dump would fill it's syscache
when it took all the locks and then not update them during the actual
dump. If that's not the case then it's a bit scary, yes.

It seems to make a good case for physical backups vs. logical.

--
-David
david@pgmasters.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Apr 14, 2016 at 1:01 PM, Andres Freund <andres@anarazel.de> wrote:

On 2016-04-14 13:16:20 +0200, Andreas Karlsson wrote:

I am personally not a fan of the pg_get_Xdef() functions due to their heavy
reliance on the syscache which feels rather unsafe in combination with
concurrent DDL.

I'm not sure I find that convincing: The state portrayed by the syscache
is th state COPY/SELECT et al will be using. I think the angle to
attack this is rather to allow blocking 'globally visible' DDL
efficiently and correctly, rather than the hack pg_dump is using right now.

Maybe. I think that idea of changing the pg_get_Xdef() stuff to use
the transaction snapshot rather than the latest snapshot might be
worth considering, too.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Apr 14, 2016 at 1:01 PM, Andres Freund <andres@anarazel.de> wrote:

I'm not sure I find that convincing: The state portrayed by the syscache
is th state COPY/SELECT et al will be using. I think the angle to
attack this is rather to allow blocking 'globally visible' DDL
efficiently and correctly, rather than the hack pg_dump is using right now.

Maybe. I think that idea of changing the pg_get_Xdef() stuff to use
the transaction snapshot rather than the latest snapshot might be
worth considering, too.

The problem here is the connection to syscache; changing the behavior
of that, in a general context, is very scary. What we might be able to
do that would satisfy pg_dump's needs is to invent a mode in which you
can run a read-only transaction that uses the transaction snapshot to
populate syscache (and then flushes the syscache at the end). It would
have to be a pretty "hard" notion of read-only, not the squishy one we
have now, but I think it would work safely. Anything that might otherwise
break because of stale syscache entries should be prevented from having
bad side-effects by the read-only restriction.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/14/2016 07:28 PM, David Steele wrote:

As far as I know pg_dump share locks everything before it starts so
there shouldn't be issues with concurrent DDL. Try creating a new
inherited table with FKs, etc. during a pg_dump and you'll see lots of
fun lock waits.

I am pretty sure that it does not lock functions, types or casts. So if
you rename a function during pg_dump there should be a risk of getting a
backup which will fail on restore.

Andreas

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Apr 15, 2016 at 2:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Apr 14, 2016 at 1:01 PM, Andres Freund <andres@anarazel.de> wrote:

I'm not sure I find that convincing: The state portrayed by the syscache
is th state COPY/SELECT et al will be using. I think the angle to
attack this is rather to allow blocking 'globally visible' DDL
efficiently and correctly, rather than the hack pg_dump is using right now.

Maybe. I think that idea of changing the pg_get_Xdef() stuff to use
the transaction snapshot rather than the latest snapshot might be
worth considering, too.

The problem here is the connection to syscache; changing the behavior
of that, in a general context, is very scary. What we might be able to
do that would satisfy pg_dump's needs is to invent a mode in which you
can run a read-only transaction that uses the transaction snapshot to
populate syscache (and then flushes the syscache at the end). It would
have to be a pretty "hard" notion of read-only, not the squishy one we
have now, but I think it would work safely. Anything that might otherwise
break because of stale syscache entries should be prevented from having
bad side-effects by the read-only restriction.

I think that we could have an alternate set of functions which have
the same interface as the syscache functions but using the transaction
snapshot and don't actually cache anything, and it would be fine for
what the pg_dump support functions need. It wouldn't be nearly as
fast as the actual syscache, but server-side CPU when deparsing DDL
has never really been a problem to my knowledge. I mean, if we had
something like this, it could also be sped up by adding a cache that
is flushed when the snapshot changes, but I'm not very sure we really
need to go that far.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Apr 15, 2016 at 2:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The problem here is the connection to syscache; changing the behavior
of that, in a general context, is very scary. What we might be able to
do that would satisfy pg_dump's needs is to invent a mode in which you
can run a read-only transaction that uses the transaction snapshot to
populate syscache (and then flushes the syscache at the end).

I think that we could have an alternate set of functions which have
the same interface as the syscache functions but using the transaction
snapshot and don't actually cache anything, and it would be fine for
what the pg_dump support functions need.

The problem with that approach is that then you are talking about building
duplicate copies of entire layers of the system. For example, namespace.c
would have to be duplicated into one copy that uses syscache and one that
uses this not-quite-cache. If it were *only* syscache.c that had to be
duplicated, probably this would work, but ruleutils.c depends on an awful
lot of code above that level. Indeed, if it did not, the idea of
reimplementing it on the client side wouldn't be so unattractive.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Apr 14, 2016 at 01:40:21PM -0400, David Steele wrote:

On 4/14/16 1:33 PM, Tom Lane wrote:

David Steele <david@pgmasters.net> writes:

On 4/14/16 7:16 AM, Andreas Karlsson wrote:

I am personally not a fan of the pg_get_Xdef() functions due to their
heavy reliance on the syscache which feels rather unsafe in combination
with concurrent DDL.

As far as I know pg_dump share locks everything before it starts so
there shouldn't be issues with concurrent DDL. Try creating a new
inherited table with FKs, etc. during a pg_dump and you'll see lots of
fun lock waits.

I think pg_dump is reasonably proof against DDL on tables. It is not
at all proof against DDL on other sorts of objects, such as functions,
because of the fact that the syscache will follow catalog updates that
occur after pg_dump's transaction snapshot.

Hmm, OK. I'll need to go look at that.

I thought that the backend running the pg_dump would fill it's syscache
when it took all the locks and then not update them during the actual
dump. If that's not the case then it's a bit scary, yes.

It seems to make a good case for physical backups vs. logical.

I think another issue is that the pg_dump backend gets cache
invalidations from other backends that cause it to reload the cache with
new contents, so even if you pre-loaded the cache at snapshot time, you
would still need to ignore cache invalidations from other backends.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Apr 29, 2016 at 5:02 PM, Bruce Momjian <bruce@momjian.us> wrote:

I think pg_dump is reasonably proof against DDL on tables. It is not
at all proof against DDL on other sorts of objects, such as functions,
because of the fact that the syscache will follow catalog updates that
occur after pg_dump's transaction snapshot.

Hmm, OK. I'll need to go look at that.

I thought that the backend running the pg_dump would fill it's syscache
when it took all the locks and then not update them during the actual
dump. If that's not the case then it's a bit scary, yes.

It seems to make a good case for physical backups vs. logical.

I think another issue is that the pg_dump backend gets cache
invalidations from other backends that cause it to reload the cache with
new contents, so even if you pre-loaded the cache at snapshot time, you
would still need to ignore cache invalidations from other backends.

If you temporarily nailed the backend's snapshot for syscache lookups
to some specific MVCC snapshot, as Tom was suggesting, then it
wouldn't matter if it processed invalidations, because reload would
pull the same entries as before back into the cache. So I don't think
you'd *have* to do this, but you might want to do it as an
optimization.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Apr 18, 2016 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

I think that we could have an alternate set of functions which have
the same interface as the syscache functions but using the transaction
snapshot and don't actually cache anything, and it would be fine for
what the pg_dump support functions need.

The problem with that approach is that then you are talking about building
duplicate copies of entire layers of the system. For example, namespace.c
would have to be duplicated into one copy that uses syscache and one that
uses this not-quite-cache. If it were *only* syscache.c that had to be
duplicated, probably this would work, but ruleutils.c depends on an awful
lot of code above that level. Indeed, if it did not, the idea of
reimplementing it on the client side wouldn't be so unattractive.

Urgh. Does ruleutils.c really depend on everything in namespace.c?
When I last looked at this I had the idea that at least a good chunk
of ruleutils.c had only limited outside dependencies that might not be
too tough to manage. However, if that's not true, then I agree that's
a problem for this approach.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Apr 18, 2016 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The problem with that approach is that then you are talking about building
duplicate copies of entire layers of the system. For example, namespace.c
would have to be duplicated into one copy that uses syscache and one that
uses this not-quite-cache. If it were *only* syscache.c that had to be
duplicated, probably this would work, but ruleutils.c depends on an awful
lot of code above that level. Indeed, if it did not, the idea of
reimplementing it on the client side wouldn't be so unattractive.

Urgh. Does ruleutils.c really depend on everything in namespace.c?

Indirectly, probably most of it. For example, it uses format_type_be()
which depends on TypeIsVisible(), and it uses func_get_detail()
which depends on FuncnameGetCandidates(). And it's those intermediate
functions that are really bloating the depends-on footprint. As things
stand, ruleutils depends on significant fractions of backend/catalog/
and backend/parser/, all of which would have to be rewritten if you'd
like to make it use some alternate catalog-access infrastructure.

But really the killer point here is that it uses SPI in some places.
I've always wondered whether that was a good design choice, but right
now that implicates just about the whole backend.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, May 2, 2016 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Apr 18, 2016 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The problem with that approach is that then you are talking about building
duplicate copies of entire layers of the system. For example, namespace.c
would have to be duplicated into one copy that uses syscache and one that
uses this not-quite-cache. If it were *only* syscache.c that had to be
duplicated, probably this would work, but ruleutils.c depends on an awful
lot of code above that level. Indeed, if it did not, the idea of
reimplementing it on the client side wouldn't be so unattractive.

Urgh. Does ruleutils.c really depend on everything in namespace.c?

Indirectly, probably most of it. For example, it uses format_type_be()
which depends on TypeIsVisible(), and it uses func_get_detail()
which depends on FuncnameGetCandidates(). And it's those intermediate
functions that are really bloating the depends-on footprint. As things
stand, ruleutils depends on significant fractions of backend/catalog/
and backend/parser/, all of which would have to be rewritten if you'd
like to make it use some alternate catalog-access infrastructure.

But really the killer point here is that it uses SPI in some places.
I've always wondered whether that was a good design choice, but right
now that implicates just about the whole backend.

Ouch.

Well, I think the first thing to do here might be to reconsider
whether the footprint could be cut down. Removing the dependency on
SPI seems like a good idea even if we do nothing else. Nailing the
catalogs to a snapshot isn't crazy - the logical decoding stuff does
it already - but having such a wide dependency footprint does not seem
especially good.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, May 2, 2016 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mon, Apr 18, 2016 at 11:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The problem with that approach is that then you are talking about building
duplicate copies of entire layers of the system.

Urgh. Does ruleutils.c really depend on everything in namespace.c?

Indirectly, probably most of it. For example, it uses format_type_be()
which depends on TypeIsVisible(), and it uses func_get_detail()
which depends on FuncnameGetCandidates(). And it's those intermediate
functions that are really bloating the depends-on footprint.
But really the killer point here is that it uses SPI in some places.
I've always wondered whether that was a good design choice, but right
now that implicates just about the whole backend.

Ouch.

Well, I think the first thing to do here might be to reconsider
whether the footprint could be cut down. Removing the dependency on
SPI seems like a good idea even if we do nothing else. Nailing the
catalogs to a snapshot isn't crazy - the logical decoding stuff does
it already - but having such a wide dependency footprint does not seem
especially good.

Meh. I'd be the first to say that probably a lot of that is because it
was convenient; but I don't see any way to avoid it without duplicating
vast quantities of code, which does not sound like a great idea from a
maintainability standpoint. And have we mentioned the fact that some of
this code looks directly at the catalogs because there's no suitable
syscache?

Really I think the idea of fixing this with an alternate syscache is
a nonstarter.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello, Jakob.

You wrote:

JE> Would anybody else be interested in a pg_dump library? I've found
JE> a thread from 2013 related to the idea, but the discussion came to nothing.

JE> Thread started here:
JE> /messages/by-id/71e01949.2e16b.13df4707405.Coremail.shuai900217@126.com

JE> My Motivation:

JE> I'm the developer of a PostgreSQL GUI client, and I am looking
JE> for ways to integrate pg_dump into my application. The main use
JE> case would be to get the dump of individual tables (for example,
JE> when you want to create a table similar to an existing one)

JE> Bundling pg_dump with my application and calling it doesn't allow
JE> the fine grained control and integration I would like to have.
JE> Also, pg_dump always opens a new connection; I would prefer to use
JE> an existing database connection instead.

JE> In case anybody else is interested in this, I can offer to
JE> sponsor some developer time towards this effort.

JE> Best regards,
JE> Jakob

I proposed this several times, but nobody cares. Then we did it.
Our PostgresDAC component set
(http://microolap.com/products/connectivity/postgresdac/) has
TPSQLDump and TPSQLRestore classes.
Details: http://microolap.com/products/connectivity/postgresdac/help/tpsqldump_tpsqldump.htm

Also we've implemented PaGoDump and PaGoRestore utilities compatible
with native pg_dump/pg_restore: http://microolap.com/products/database/pagodump/

--
With best wishes,
Pavel mailto:pavel@gf.microolap.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers