Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 403a3d9

Browse filesBrowse files
alvherrealvherre
committed
pg_dump: Lock all relations, not just plain tables
Now that LOCK TABLE can take any relation type, acquire lock on all relations that are to be dumped. This prevents schema changes or deadlock errors that could cause a dump to fail after expending much effort. The server is tested to have the capability and the feature disabled if it doesn't, so that a patched pg_dump doesn't fail when connecting to an unpatched server. Backpatch to 9.5. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reported-by: Wells Oliver <wells.oliver@gmail.com> Discussion: https://postgr.es/m/20201021200659.GA32358@alvherre.pgsql
1 parent f893e68 commit 403a3d9
Copy full SHA for 403a3d9

File tree

Expand file treeCollapse file tree

4 files changed

+78
-7
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+78
-7
lines changed

‎src/bin/pg_dump/pg_backup.h

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_backup.h
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -198,6 +198,8 @@ typedef struct Archive
198198
int minRemoteVersion; /* allowable range */
199199
int maxRemoteVersion;
200200

201+
bool hasGenericLockTable; /* can LOCK TABLE do non-table rels */
202+
201203
int numWorkers; /* number of parallel processes */
202204
char *sync_snapshot_id; /* sync snapshot id for parallel operation */
203205

‎src/bin/pg_dump/pg_backup_db.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_backup_db.c
+64Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -531,6 +531,70 @@ EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
531531
}
532532
}
533533

534+
/*
535+
* Does LOCK TABLE work on non-table relations on this server?
536+
*
537+
* Note: assumes it is called out of any transaction
538+
*/
539+
bool
540+
IsLockTableGeneric(Archive *AHX)
541+
{
542+
ArchiveHandle *AH = (ArchiveHandle *) AHX;
543+
PGresult *res;
544+
char *sqlstate;
545+
bool retval;
546+
547+
if (AHX->remoteVersion >= 140000)
548+
return true;
549+
else if (AHX->remoteVersion < 90500)
550+
return false;
551+
552+
StartTransaction(AHX);
553+
554+
/*
555+
* Try a LOCK TABLE on a well-known non-table catalog; WRONG_OBJECT_TYPE
556+
* tells us that this server doesn't support locking non-table rels, while
557+
* LOCK_NOT_AVAILABLE and INSUFFICIENT_PRIVILEGE tell us that it does.
558+
* Report anything else as a fatal problem.
559+
*/
560+
#define ERRCODE_INSUFFICIENT_PRIVILEGE "42501"
561+
#define ERRCODE_WRONG_OBJECT_TYPE "42809"
562+
#define ERRCODE_LOCK_NOT_AVAILABLE "55P03"
563+
res = PQexec(AH->connection,
564+
"LOCK TABLE pg_catalog.pg_class_tblspc_relfilenode_index IN ACCESS SHARE MODE NOWAIT");
565+
switch (PQresultStatus(res))
566+
{
567+
case PGRES_COMMAND_OK:
568+
retval = true;
569+
break;
570+
case PGRES_FATAL_ERROR:
571+
sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);
572+
if (strcmp(sqlstate, ERRCODE_WRONG_OBJECT_TYPE) == 0)
573+
{
574+
retval = false;
575+
break;
576+
}
577+
else if (strcmp(sqlstate, ERRCODE_LOCK_NOT_AVAILABLE) == 0 ||
578+
strcmp(sqlstate, ERRCODE_INSUFFICIENT_PRIVILEGE) == 0)
579+
{
580+
retval = true;
581+
break;
582+
}
583+
/* else, falls through */
584+
default:
585+
warn_or_exit_horribly(AH, "LOCK TABLE failed for \"%s\": %s",
586+
"pg_catalog.pg_class_tblspc_relfilenode_index",
587+
PQerrorMessage(AH->connection));
588+
retval = false; /* not reached */
589+
break;
590+
}
591+
PQclear(res);
592+
593+
CommitTransaction(AHX);
594+
595+
return retval;
596+
}
597+
534598
void
535599
StartTransaction(Archive *AHX)
536600
{

‎src/bin/pg_dump/pg_backup_db.h

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_backup_db.h
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,8 @@ extern PGresult *ExecuteSqlQueryForSingleRow(Archive *fout, const char *query);
2020

2121
extern void EndDBCopyMode(Archive *AHX, const char *tocEntryTag);
2222

23+
extern bool IsLockTableGeneric(Archive *AHX);
24+
2325
extern void StartTransaction(Archive *AHX);
2426
extern void CommitTransaction(Archive *AHX);
2527

‎src/bin/pg_dump/pg_dump.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_dump.c
+10-7Lines changed: 10 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1176,6 +1176,9 @@ setup_connection(Archive *AH, const char *dumpencoding,
11761176
ExecuteSqlStatement(AH, "SET row_security = off");
11771177
}
11781178

1179+
/* Detect whether LOCK TABLE can handle non-table relations */
1180+
AH->hasGenericLockTable = IsLockTableGeneric(AH);
1181+
11791182
/*
11801183
* Start transaction-snapshot mode transaction to dump consistent data.
11811184
*/
@@ -6843,16 +6846,16 @@ getTables(Archive *fout, int *numTables)
68436846
* assume our lock on the child is enough to prevent schema
68446847
* alterations to parent tables.
68456848
*
6846-
* NOTE: it'd be kinda nice to lock other relations too, not only
6847-
* plain or partitioned tables, but the backend doesn't presently
6848-
* allow that.
6849-
*
6850-
* We only need to lock the table for certain components; see
6849+
* We only need to lock the relation for certain components; see
68516850
* pg_dump.h
6851+
*
6852+
* On server versions that support it, we lock all relations not just
6853+
* plain tables.
68526854
*/
68536855
if (tblinfo[i].dobj.dump &&
6854-
(tblinfo[i].relkind == RELKIND_RELATION ||
6855-
tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
6856+
(fout->hasGenericLockTable ||
6857+
tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE ||
6858+
tblinfo[i].relkind == RELKIND_RELATION) &&
68566859
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
68576860
{
68586861
resetPQExpBuffer(query);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.