Skip to content

Commit c2e3020

Browse files
GrunthosGrunthos
committed
- Only disable triggers for the table being loaded
- disable triggers prior to BLOB load - better query for loading blob xrefs - Fixes to error handling code
1 parent 129f1a3 commit c2e3020

File tree

3 files changed

+70
-27
lines changed

3 files changed

+70
-27
lines changed

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 50 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,9 @@ void RestoreArchive(Archive* AHX, RestoreOptions *ropt)
252252
*/
253253
if (_canRestoreBlobs(AH) && AH->createdBlobXref)
254254
{
255+
/* NULL parameter means disable ALL user triggers */
256+
_disableTriggers(AH, NULL, ropt);
257+
255258
te = AH->toc->next;
256259
while (te != AH->toc) {
257260

@@ -275,6 +278,9 @@ void RestoreArchive(Archive* AHX, RestoreOptions *ropt)
275278

276279
te = te->next;
277280
}
281+
282+
/* NULL parameter means enable ALL user triggers */
283+
_enableTriggers(AH, NULL, ropt);
278284
}
279285

280286
/*
@@ -335,13 +341,16 @@ static void _disableTriggers(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ro
335341
*/
336342
if (ropt->superuser)
337343
{
338-
/* If we're not allowing changes for ownership, then remember the user
339-
* so we can change it back here. Otherwise, let _reconnectAsOwner
340-
* do what it has to do.
341-
*/
342-
if (ropt->noOwner)
343-
oldUser = strdup(ConnectedUser(AH));
344-
_reconnectAsUser(AH, "-", ropt->superuser);
344+
if (!_restoringToDB(AH) || !ConnectedUserIsSuperuser(AH))
345+
{
346+
/* If we're not allowing changes for ownership, then remember the user
347+
* so we can change it back here. Otherwise, let _reconnectAsOwner
348+
* do what it has to do.
349+
*/
350+
if (ropt->noOwner)
351+
oldUser = strdup(ConnectedUser(AH));
352+
_reconnectAsUser(AH, "-", ropt->superuser);
353+
}
345354
}
346355

347356
ahlog(AH, 1, "Disabling triggers\n");
@@ -351,7 +360,16 @@ static void _disableTriggers(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ro
351360
* command when one is available.
352361
*/
353362
ahprintf(AH, "-- Disable triggers\n");
354-
ahprintf(AH, "UPDATE \"pg_class\" SET \"reltriggers\" = 0 WHERE \"relname\" !~ '^pg_';\n\n");
363+
364+
/*
365+
* Just update the AFFECTED table, if known.
366+
*/
367+
368+
if (te && te->name && strlen(te->name) > 0)
369+
ahprintf(AH, "UPDATE \"pg_class\" SET \"reltriggers\" = 0 WHERE \"relname\" ~* '%s';\n",
370+
te->name);
371+
else
372+
ahprintf(AH, "UPDATE \"pg_class\" SET \"reltriggers\" = 0 WHERE \"relname\" !~ '^pg_';\n\n");
355373

356374
/*
357375
* Restore the user connection from the start of this procedure
@@ -378,14 +396,17 @@ static void _enableTriggers(ArchiveHandle *AH, TocEntry *te, RestoreOptions *rop
378396
*/
379397
if (ropt->superuser)
380398
{
381-
/* If we're not allowing changes for ownership, then remember the user
382-
* so we can change it back here. Otherwise, let _reconnectAsOwner
383-
* do what it has to do
384-
*/
385-
if (ropt->noOwner)
386-
oldUser = strdup(ConnectedUser(AH));
399+
if (!_restoringToDB(AH) || !ConnectedUserIsSuperuser(AH))
400+
{
401+
/* If we're not allowing changes for ownership, then remember the user
402+
* so we can change it back here. Otherwise, let _reconnectAsOwner
403+
* do what it has to do
404+
*/
405+
if (ropt->noOwner)
406+
oldUser = strdup(ConnectedUser(AH));
387407

388-
_reconnectAsUser(AH, "-", ropt->superuser);
408+
_reconnectAsUser(AH, "-", ropt->superuser);
409+
}
389410
}
390411

391412
ahlog(AH, 1, "Enabling triggers\n");
@@ -397,9 +418,19 @@ static void _enableTriggers(ArchiveHandle *AH, TocEntry *te, RestoreOptions *rop
397418
ahprintf(AH, "-- Enable triggers\n");
398419
ahprintf(AH, "BEGIN TRANSACTION;\n");
399420
ahprintf(AH, "CREATE TEMP TABLE \"tr\" (\"tmp_relname\" name, \"tmp_reltriggers\" smallint);\n");
400-
ahprintf(AH, "INSERT INTO \"tr\" SELECT C.\"relname\", count(T.\"oid\") FROM \"pg_class\" C,"
401-
" \"pg_trigger\" T WHERE C.\"oid\" = T.\"tgrelid\" AND C.\"relname\" !~ '^pg_' "
402-
" GROUP BY 1;\n");
421+
422+
/*
423+
* Just update the affected table, if known.
424+
*/
425+
if (te && te->name && strlen(te->name) > 0)
426+
ahprintf(AH, "INSERT INTO \"tr\" SELECT C.\"relname\", count(T.\"oid\") FROM \"pg_class\" C,"
427+
" \"pg_trigger\" T WHERE C.\"oid\" = T.\"tgrelid\" AND C.\"relname\" ~* '%s' "
428+
" GROUP BY 1;\n", te->name);
429+
else
430+
ahprintf(AH, "INSERT INTO \"tr\" SELECT C.\"relname\", count(T.\"oid\") FROM \"pg_class\" C,"
431+
" \"pg_trigger\" T WHERE C.\"oid\" = T.\"tgrelid\" AND C.\"relname\" !~ '^pg_' "
432+
" GROUP BY 1;\n");
433+
403434
ahprintf(AH, "UPDATE \"pg_class\" SET \"reltriggers\" = TMP.\"tmp_reltriggers\" "
404435
"FROM \"tr\" TMP WHERE "
405436
"\"pg_class\".\"relname\" = TMP.\"tmp_relname\";\n");
@@ -580,7 +611,7 @@ void StartRestoreBlob(ArchiveHandle* AH, int oid)
580611
if (loOid == 0)
581612
die_horribly(AH, "%s: unable to create BLOB\n", progname);
582613

583-
ahlog(AH, 1, "Restoring BLOB oid %d as %d\n", oid, loOid);
614+
ahlog(AH, 2, "Restoring BLOB oid %d as %d\n", oid, loOid);
584615

585616
InsertBlobXref(AH, oid, loOid);
586617

‎src/bin/pg_dump/pg_backup_archiver.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ typedef z_stream *z_streamp;
5959

6060
#define K_VERS_MAJOR 1
6161
#define K_VERS_MINOR 4
62-
#define K_VERS_REV 8
62+
#define K_VERS_REV 10
6363

6464
/* Data block types */
6565
#define BLK_DATA 1

‎src/bin/pg_dump/pg_backup_db.c

Lines changed: 19 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -593,21 +593,30 @@ void FixupBlobRefs(ArchiveHandle *AH, char *tablename)
593593
ahlog(AH, 1, " - %s.%s\n", tablename, attr);
594594

595595
resetPQExpBuffer(tblQry);
596-
appendPQExpBuffer(tblQry, "Update \"%s\" Set \"%s\" = x.newOid From %s x "
597-
"Where x.oldOid = \"%s\".\"%s\";",
598596

599-
tablename, attr, BLOB_XREF_TABLE, tablename, attr);
597+
/*
598+
* We should use coalesce here (rather than 'exists'), but it seems to
599+
* be broken in 7.0.2 (weird optimizer strategy)
600+
*/
601+
appendPQExpBuffer(tblQry, "UPDATE \"%s\" SET \"%s\" = ",tablename, attr);
602+
appendPQExpBuffer(tblQry, " (SELECT x.newOid FROM \"%s\" x WHERE x.oldOid = \"%s\".\"%s\")",
603+
BLOB_XREF_TABLE, tablename, attr);
604+
appendPQExpBuffer(tblQry, " where exists"
605+
"(select * from %s x where x.oldOid = \"%s\".\"%s\");",
606+
BLOB_XREF_TABLE, tablename, attr);
600607

601-
ahlog(AH, 10, " - sql = %s\n", tblQry->data);
608+
ahlog(AH, 10, " - sql:\n%s\n", tblQry->data);
602609

603610
uRes = PQexec(AH->blobConnection, tblQry->data);
604611
if (!uRes)
605612
die_horribly(AH, "%s: could not update attr %s of table %s. Explanation from backend '%s'\n",
606-
progname, attr, tablename, PQerrorMessage(AH->connection));
613+
progname, attr, tablename, PQerrorMessage(AH->blobConnection));
607614

608615
if ( PQresultStatus(uRes) != PGRES_COMMAND_OK )
609-
die_horribly(AH, "%s: error while updating attr %s of table %s. Explanation from backend '%s'\n",
610-
progname, attr, tablename, PQerrorMessage(AH->connection));
616+
die_horribly(AH, "%s: error while updating attr %s of table %s (result = %d)."
617+
" Explanation from backend '%s'\n",
618+
progname, attr, tablename, PQresultStatus(uRes),
619+
PQerrorMessage(AH->blobConnection));
611620

612621
PQclear(uRes);
613622
}
@@ -631,7 +640,10 @@ void CreateBlobXrefTable(ArchiveHandle* AH)
631640

632641
ahlog(AH, 1, "Creating table for BLOBS xrefs\n");
633642

643+
/*
634644
appendPQExpBuffer(qry, "Create Temporary Table %s(oldOid oid, newOid oid);", BLOB_XREF_TABLE);
645+
*/
646+
appendPQExpBuffer(qry, "Create Table %s(oldOid oid, newOid oid);", BLOB_XREF_TABLE);
635647

636648
_executeSqlCommand(AH, AH->blobConnection, qry, "can not create BLOB xref table '" BLOB_XREF_TABLE "'");
637649

0 commit comments

Comments
 (0)