pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH 2/2] pg_dump: lock all rels, not just RELKIND_RELATION
6+ messages / 3 participants
[nested] [flat]
* [PATCH 2/2] pg_dump: lock all rels, not just RELKIND_RELATION
@ 2020-07-07 23:14 Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2020-07-07 23:14 UTC (permalink / raw)
---
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_db.c | 37 ++++++++++++++++++++++++++++++++++
src/bin/pg_dump/pg_backup_db.h | 2 ++
src/bin/pg_dump/pg_dump.c | 17 +++++++++-------
4 files changed, 51 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index a6a8e6f2fd..6c79319164 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -198,6 +198,8 @@ typedef struct Archive
int minRemoteVersion; /* allowable range */
int maxRemoteVersion;
+ bool hasGenericLockTable; /* can LOCK TABLE do non-table rels */
+
int numWorkers; /* number of parallel processes */
char *sync_snapshot_id; /* sync snapshot id for parallel operation */
diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c
index 5ba43441f5..274c44c5ec 100644
--- a/src/bin/pg_dump/pg_backup_db.c
+++ b/src/bin/pg_dump/pg_backup_db.c
@@ -531,6 +531,43 @@ EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
}
}
+/*
+ * Does LOCK TABLE work on non-table relations on this server?
+ *
+ * Note: assumes it is called out of any transaction
+ */
+bool
+IsLockTableGeneric(Archive *AHX)
+{
+ ArchiveHandle *AH = (ArchiveHandle *) AHX;
+ PGresult *res;
+ bool retval;
+
+ StartTransaction(AHX);
+
+ res = PQexec(AH->connection, "LOCK TABLE pg_catalog.pg_stats; ");
+ switch (PQresultStatus(res))
+ {
+ case PGRES_COMMAND_OK:
+ retval = true;
+ break;
+ case PGRES_FATAL_ERROR:
+ retval = false;
+ break;
+ default:
+ warn_or_exit_horribly(AH, "LOCK TABLE failed for \"%s\": %s",
+ "pg_catalog.pg_stats",
+ PQerrorMessage(AH->connection));
+ retval = false; /* not reached */
+ break;
+ }
+ PQclear(res);
+
+ CommitTransaction(AHX);
+
+ return retval;
+}
+
void
StartTransaction(Archive *AHX)
{
diff --git a/src/bin/pg_dump/pg_backup_db.h b/src/bin/pg_dump/pg_backup_db.h
index 8888dd34b9..355beec1f7 100644
--- a/src/bin/pg_dump/pg_backup_db.h
+++ b/src/bin/pg_dump/pg_backup_db.h
@@ -20,6 +20,8 @@ extern PGresult *ExecuteSqlQueryForSingleRow(Archive *fout, const char *query);
extern void EndDBCopyMode(Archive *AHX, const char *tocEntryTag);
+extern bool IsLockTableGeneric(Archive *AHX);
+
extern void StartTransaction(Archive *AHX);
extern void CommitTransaction(Archive *AHX);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ff45e3fb8c..dde8683d47 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1176,6 +1176,10 @@ setup_connection(Archive *AH, const char *dumpencoding,
ExecuteSqlStatement(AH, "SET row_security = off");
}
+ /* Detect whether LOCK TABLE can handle non-table relations */
+ AH->hasGenericLockTable =
+ AH->remoteVersion >= 90500 ? IsLockTableGeneric(AH) : false;
+
/*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
@@ -6843,16 +6847,15 @@ getTables(Archive *fout, int *numTables)
* assume our lock on the child is enough to prevent schema
* alterations to parent tables.
*
- * NOTE: it'd be kinda nice to lock other relations too, not only
- * plain or partitioned tables, but the backend doesn't presently
- * allow that.
- *
- * We only need to lock the table for certain components; see
+ * We only need to lock the relation for certain components; see
* pg_dump.h
+ *
+ * On server versions that support it, we lock all relations not just
+ * plain tables.
*/
if (tblinfo[i].dobj.dump &&
- (tblinfo[i].relkind == RELKIND_RELATION ||
- tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
+ (fout->hasGenericLockTable ||
+ tblinfo[i].relkind == RELKIND_RELATION) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
--
2.20.1
--vkogqOf2sHV7VnPd--
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v2 2/2] pg_dump: lock all rels, not just RELKIND_RELATION
@ 2020-07-07 23:14 Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2020-07-07 23:14 UTC (permalink / raw)
---
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_db.c | 64 ++++++++++++++++++++++++++++++++++
src/bin/pg_dump/pg_backup_db.h | 2 ++
src/bin/pg_dump/pg_dump.c | 17 +++++----
4 files changed, 78 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index a6a8e6f2fd..6c79319164 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -198,6 +198,8 @@ typedef struct Archive
int minRemoteVersion; /* allowable range */
int maxRemoteVersion;
+ bool hasGenericLockTable; /* can LOCK TABLE do non-table rels */
+
int numWorkers; /* number of parallel processes */
char *sync_snapshot_id; /* sync snapshot id for parallel operation */
diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c
index 5ba43441f5..a9eba97b84 100644
--- a/src/bin/pg_dump/pg_backup_db.c
+++ b/src/bin/pg_dump/pg_backup_db.c
@@ -531,6 +531,70 @@ EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
}
}
+/*
+ * Does LOCK TABLE work on non-table relations on this server?
+ *
+ * Note: assumes it is called out of any transaction
+ */
+bool
+IsLockTableGeneric(Archive *AHX)
+{
+ ArchiveHandle *AH = (ArchiveHandle *) AHX;
+ PGresult *res;
+ char *sqlstate;
+ bool retval;
+
+ if (AHX->remoteVersion >= 140000)
+ return true;
+ else if (AHX->remoteVersion < 90500)
+ return false;
+
+ StartTransaction(AHX);
+
+ /*
+ * Try a LOCK TABLE on a well-known non-table catalog; WRONG_OBJECT_TYPE
+ * tells us that this server doesn't support locking non-table rels, while
+ * LOCK_NOT_AVAILABLE and INSUFFICIENT_PRIVILEGE tell us that it does.
+ * Report anything else as a fatal problem.
+ */
+#define ERRCODE_INSUFFICIENT_PRIVILEGE "42501"
+#define ERRCODE_WRONG_OBJECT_TYPE "42809"
+#define ERRCODE_LOCK_NOT_AVAILABLE "55P03"
+ res = PQexec(AH->connection,
+ "LOCK TABLE pg_catalog.pg_aggregate_fnoid_index IN ACCESS SHARE MODE NOWAIT");
+ switch (PQresultStatus(res))
+ {
+ case PGRES_COMMAND_OK:
+ retval = true;
+ break;
+ case PGRES_FATAL_ERROR:
+ sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+ if (strcmp(sqlstate, ERRCODE_WRONG_OBJECT_TYPE) == 0)
+ {
+ retval = false;
+ break;
+ }
+ else if (strcmp(sqlstate, ERRCODE_LOCK_NOT_AVAILABLE) == 0 ||
+ strcmp(sqlstate, ERRCODE_INSUFFICIENT_PRIVILEGE) == 0)
+ {
+ retval = true;
+ break;
+ }
+ /* else, falls through */
+ default:
+ warn_or_exit_horribly(AH, "LOCK TABLE failed for \"%s\": %s",
+ "pg_catalog.pg_stats",
+ PQerrorMessage(AH->connection));
+ retval = false; /* not reached */
+ break;
+ }
+ PQclear(res);
+
+ CommitTransaction(AHX);
+
+ return retval;
+}
+
void
StartTransaction(Archive *AHX)
{
diff --git a/src/bin/pg_dump/pg_backup_db.h b/src/bin/pg_dump/pg_backup_db.h
index 8888dd34b9..355beec1f7 100644
--- a/src/bin/pg_dump/pg_backup_db.h
+++ b/src/bin/pg_dump/pg_backup_db.h
@@ -20,6 +20,8 @@ extern PGresult *ExecuteSqlQueryForSingleRow(Archive *fout, const char *query);
extern void EndDBCopyMode(Archive *AHX, const char *tocEntryTag);
+extern bool IsLockTableGeneric(Archive *AHX);
+
extern void StartTransaction(Archive *AHX);
extern void CommitTransaction(Archive *AHX);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ff45e3fb8c..03f9d4d9e8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1176,6 +1176,9 @@ setup_connection(Archive *AH, const char *dumpencoding,
ExecuteSqlStatement(AH, "SET row_security = off");
}
+ /* Detect whether LOCK TABLE can handle non-table relations */
+ AH->hasGenericLockTable = IsLockTableGeneric(AH);
+
/*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
@@ -6843,16 +6846,16 @@ getTables(Archive *fout, int *numTables)
* assume our lock on the child is enough to prevent schema
* alterations to parent tables.
*
- * NOTE: it'd be kinda nice to lock other relations too, not only
- * plain or partitioned tables, but the backend doesn't presently
- * allow that.
- *
- * We only need to lock the table for certain components; see
+ * We only need to lock the relation for certain components; see
* pg_dump.h
+ *
+ * On server versions that support it, we lock all relations not just
+ * plain tables.
*/
if (tblinfo[i].dobj.dump &&
- (tblinfo[i].relkind == RELKIND_RELATION ||
- tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
+ (fout->hasGenericLockTable ||
+ tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE ||
+ tblinfo[i].relkind == RELKIND_RELATION) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
--
2.20.1
--HlL+5n6rz5pIUxbD--
^ permalink raw reply [nested|flat] 6+ messages in thread
* [PATCH v3 2/2] pg_dump: lock all rels, not just RELKIND_RELATION
@ 2020-07-07 23:14 Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2020-07-07 23:14 UTC (permalink / raw)
---
src/bin/pg_dump/pg_backup.h | 2 ++
src/bin/pg_dump/pg_backup_db.c | 64 ++++++++++++++++++++++++++++++++++
src/bin/pg_dump/pg_backup_db.h | 2 ++
src/bin/pg_dump/pg_dump.c | 17 +++++----
4 files changed, 78 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index a6a8e6f2fd..6c79319164 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -198,6 +198,8 @@ typedef struct Archive
int minRemoteVersion; /* allowable range */
int maxRemoteVersion;
+ bool hasGenericLockTable; /* can LOCK TABLE do non-table rels */
+
int numWorkers; /* number of parallel processes */
char *sync_snapshot_id; /* sync snapshot id for parallel operation */
diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c
index 5ba43441f5..28b2892538 100644
--- a/src/bin/pg_dump/pg_backup_db.c
+++ b/src/bin/pg_dump/pg_backup_db.c
@@ -531,6 +531,70 @@ EndDBCopyMode(Archive *AHX, const char *tocEntryTag)
}
}
+/*
+ * Does LOCK TABLE work on non-table relations on this server?
+ *
+ * Note: assumes it is called out of any transaction
+ */
+bool
+IsLockTableGeneric(Archive *AHX)
+{
+ ArchiveHandle *AH = (ArchiveHandle *) AHX;
+ PGresult *res;
+ char *sqlstate;
+ bool retval;
+
+ if (AHX->remoteVersion >= 140000)
+ return true;
+ else if (AHX->remoteVersion < 90500)
+ return false;
+
+ StartTransaction(AHX);
+
+ /*
+ * Try a LOCK TABLE on a well-known non-table catalog; WRONG_OBJECT_TYPE
+ * tells us that this server doesn't support locking non-table rels, while
+ * LOCK_NOT_AVAILABLE and INSUFFICIENT_PRIVILEGE tell us that it does.
+ * Report anything else as a fatal problem.
+ */
+#define ERRCODE_INSUFFICIENT_PRIVILEGE "42501"
+#define ERRCODE_WRONG_OBJECT_TYPE "42809"
+#define ERRCODE_LOCK_NOT_AVAILABLE "55P03"
+ res = PQexec(AH->connection,
+ "LOCK TABLE pg_catalog.pg_class_tblspc_relfilenode_index IN ACCESS SHARE MODE NOWAIT");
+ switch (PQresultStatus(res))
+ {
+ case PGRES_COMMAND_OK:
+ retval = true;
+ break;
+ case PGRES_FATAL_ERROR:
+ sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);
+ if (strcmp(sqlstate, ERRCODE_WRONG_OBJECT_TYPE) == 0)
+ {
+ retval = false;
+ break;
+ }
+ else if (strcmp(sqlstate, ERRCODE_LOCK_NOT_AVAILABLE) == 0 ||
+ strcmp(sqlstate, ERRCODE_INSUFFICIENT_PRIVILEGE) == 0)
+ {
+ retval = true;
+ break;
+ }
+ /* else, falls through */
+ default:
+ warn_or_exit_horribly(AH, "LOCK TABLE failed for \"%s\": %s",
+ "pg_catalog.pg_class_tblspc_relfilenode_index",
+ PQerrorMessage(AH->connection));
+ retval = false; /* not reached */
+ break;
+ }
+ PQclear(res);
+
+ CommitTransaction(AHX);
+
+ return retval;
+}
+
void
StartTransaction(Archive *AHX)
{
diff --git a/src/bin/pg_dump/pg_backup_db.h b/src/bin/pg_dump/pg_backup_db.h
index 8888dd34b9..355beec1f7 100644
--- a/src/bin/pg_dump/pg_backup_db.h
+++ b/src/bin/pg_dump/pg_backup_db.h
@@ -20,6 +20,8 @@ extern PGresult *ExecuteSqlQueryForSingleRow(Archive *fout, const char *query);
extern void EndDBCopyMode(Archive *AHX, const char *tocEntryTag);
+extern bool IsLockTableGeneric(Archive *AHX);
+
extern void StartTransaction(Archive *AHX);
extern void CommitTransaction(Archive *AHX);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ff45e3fb8c..03f9d4d9e8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1176,6 +1176,9 @@ setup_connection(Archive *AH, const char *dumpencoding,
ExecuteSqlStatement(AH, "SET row_security = off");
}
+ /* Detect whether LOCK TABLE can handle non-table relations */
+ AH->hasGenericLockTable = IsLockTableGeneric(AH);
+
/*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
@@ -6843,16 +6846,16 @@ getTables(Archive *fout, int *numTables)
* assume our lock on the child is enough to prevent schema
* alterations to parent tables.
*
- * NOTE: it'd be kinda nice to lock other relations too, not only
- * plain or partitioned tables, but the backend doesn't presently
- * allow that.
- *
- * We only need to lock the table for certain components; see
+ * We only need to lock the relation for certain components; see
* pg_dump.h
+ *
+ * On server versions that support it, we lock all relations not just
+ * plain tables.
*/
if (tblinfo[i].dobj.dump &&
- (tblinfo[i].relkind == RELKIND_RELATION ||
- tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
+ (fout->hasGenericLockTable ||
+ tblinfo[i].relkind == RELKIND_PARTITIONED_TABLE ||
+ tblinfo[i].relkind == RELKIND_RELATION) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
--
2.20.1
--TB36FDmn/VVEgNH/--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Output shell variable from psql
@ 2024-04-08 15:17 Murthy Nunna <mnunna@fnal.gov>
2024-04-08 15:30 ` Re: Output shell variable from psql Wim Bertels <wim.bertels@ucll.be>
0 siblings, 1 reply; 6+ messages in thread
From: Murthy Nunna @ 2024-04-08 15:17 UTC (permalink / raw)
To: pgsql-admin
Hi,
Is there a way to output shell variable from psql?
export TEST_V=7
echo Value is ${TEST_V}
Value is 7
psql -U postgres -p 5439
\echo Value is ${TEST_V}
Value is ${TEST_V}
Thanks!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Output shell variable from psql
2024-04-08 15:17 Output shell variable from psql Murthy Nunna <mnunna@fnal.gov>
@ 2024-04-08 15:30 ` Wim Bertels <wim.bertels@ucll.be>
2024-04-08 15:45 ` RE: Output shell variable from psql Murthy Nunna <mnunna@fnal.gov>
0 siblings, 1 reply; 6+ messages in thread
From: Wim Bertels @ 2024-04-08 15:30 UTC (permalink / raw)
To: pgsql-admin; mnunna@fnal.gov <mnunna@fnal.gov>
Murthy Nunna schreef op ma 08-04-2024 om 15:17 [+0000]:
> U ontvangt niet vaak e-mail van mnunna@fnal.gov. Meer informatie over
> waarom dit belangrijk is
>
> Hi,
>
> Is there a way to output shell variable from psql?
>
> export TEST_V=7
> echo Value is ${TEST_V}
> Value is 7
>
> psql -U postgres -p 5439
> \echo Value is ${TEST_V}
> Value is ${TEST_V}
$ export test=E
$ psql
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
wim=# \! echo $test
E
wim=#
hth,
Wim
>
> Thanks!
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Output shell variable from psql
2024-04-08 15:17 Output shell variable from psql Murthy Nunna <mnunna@fnal.gov>
2024-04-08 15:30 ` Re: Output shell variable from psql Wim Bertels <wim.bertels@ucll.be>
@ 2024-04-08 15:45 ` Murthy Nunna <mnunna@fnal.gov>
0 siblings, 0 replies; 6+ messages in thread
From: Murthy Nunna @ 2024-04-08 15:45 UTC (permalink / raw)
To: Wim Bertels <wim.bertels@ucll.be>; pgsql-admin
Thanks! That worked.
-----Original Message-----
From: Wim Bertels <wim.bertels@ucll.be>
Sent: Monday, April 8, 2024 10:30 AM
To: pgsql-admin@postgresql.org; Murthy Nunna <mnunna@fnal.gov>
Subject: Re: Output shell variable from psql
[EXTERNAL] – This message is from an external sender
Murthy Nunna schreef op ma 08-04-2024 om 15:17 [+0000]:
> U ontvangt niet vaak e-mail van mnunna@fnal.gov. Meer informatie over
> waarom dit belangrijk is
>
> Hi,
>
> Is there a way to output shell variable from psql?
>
> export TEST_V=7
> echo Value is ${TEST_V}
> Value is 7
>
> psql -U postgres -p 5439
> \echo Value is ${TEST_V}
> Value is ${TEST_V}
$ export test=E
$ psql
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
wim=# \! echo $test
E
wim=#
hth,
Wim
>
> Thanks!
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-08 15:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-07 23:14 [PATCH 2/2] pg_dump: lock all rels, not just RELKIND_RELATION Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-07-07 23:14 [PATCH v2 2/2] pg_dump: lock all rels, not just RELKIND_RELATION Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-07-07 23:14 [PATCH v3 2/2] pg_dump: lock all rels, not just RELKIND_RELATION Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-04-08 15:17 Output shell variable from psql Murthy Nunna <mnunna@fnal.gov>
2024-04-08 15:30 ` Re: Output shell variable from psql Wim Bertels <wim.bertels@ucll.be>
2024-04-08 15:45 ` RE: Output shell variable from psql Murthy Nunna <mnunna@fnal.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox