Dimitri Fontaine <[email protected]> writes:
> That's very great news. I'm left with moving the bulk of the code away
> from genfile.c and into postgres.c, and have the former be a user
> callable shell around the later, I suppose. Right?
Here it is, looks much better this way.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13895,13900 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 13895,13907 ----
<entry><type>record</type></entry>
<entry>Return information about a file</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_execute_from_file(<parameter>filename</> <type>text</>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>Executes the <acronym>SQL</> commands contained in a file</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 13933,13938 **** SELECT (pg_stat_file('filename')).modification;
--- 13940,13954 ----
</programlisting>
</para>
+ <indexterm>
+ <primary>pg_execute_from_file</primary>
+ </indexterm>
+ <para>
+ <function>pg_execute_from_file</> makes the server
+ execute <acronym>SQL</> commands to be found in a file. This function is
+ reserved to superusers.
+ </para>
+
<para>
The functions shown in <xref linkend="functions-advisory-locks"> manage
advisory locks. For details about proper use of these functions, see
***************
*** 13955,13960 **** SELECT (pg_stat_file('filename')).modification;
--- 13971,13977 ----
<entry><type>void</type></entry>
<entry>Obtain exclusive advisory lock</entry>
</row>
+
<row>
<entry>
<literal><function>pg_advisory_lock(<parameter>key1</> <type>int</>, <parameter>key2</> <type>int</>)</function></literal>
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
***************
*** 73,79 ****
#include "utils/snapmgr.h"
#include "mb/pg_wchar.h"
-
extern char *optarg;
extern int optind;
--- 73,78 ----
***************
*** 1139,1144 **** exec_simple_query(const char *query_string)
--- 1138,1277 ----
}
/*
+ * exec_multiple_queries
+ *
+ * Execute all queries found in the given query_string. Main use is
+ * pg_execute_from_file() function in backend/utils/adt/genfile.c
+ *
+ * Main differences from exec_simple_query are:
+ * - don't override unnamed portal
+ * - don't start nor finish a transaction
+ * - don't set stats or tracing markers
+ */
+ void
+ exec_multiple_queries(const char *portal_name, const char *query_string)
+ {
+ CommandDest dest = DestNone;
+ MemoryContext oldcontext;
+ List *parsetree_list;
+ ListCell *parsetree_item;
+ bool save_log_statement_stats = log_statement_stats;
+ bool was_logged = false;
+ bool isTopLevel;
+ char msec_str[32];
+
+ oldcontext = MemoryContextSwitchTo(MessageContext);
+ parsetree_list = pg_parse_query(query_string);
+ MemoryContextSwitchTo(oldcontext);
+
+ isTopLevel = false;
+
+ foreach(parsetree_item, parsetree_list)
+ {
+ Node *parsetree = (Node *) lfirst(parsetree_item);
+ bool snapshot_set = false;
+ const char *commandTag;
+ char completionTag[COMPLETION_TAG_BUFSIZE];
+ List *querytree_list,
+ *plantree_list;
+ Portal portal;
+ DestReceiver *receiver;
+ int16 format = 0; /* TEXT */
+
+ commandTag = CreateCommandTag(parsetree);
+
+ /* If we got a cancel signal in parsing or prior command, quit */
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Set up a snapshot if parse analysis/planning will need one.
+ */
+ if (analyze_requires_snapshot(parsetree))
+ {
+ PushActiveSnapshot(GetTransactionSnapshot());
+ snapshot_set = true;
+ }
+
+ /*
+ * OK to analyze, rewrite, and plan this query.
+ *
+ * Switch to appropriate context for constructing querytrees (again,
+ * these must outlive the execution context).
+ */
+ oldcontext = MemoryContextSwitchTo(MessageContext);
+
+ querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
+ NULL, 0);
+
+ plantree_list = pg_plan_queries(querytree_list, 0, NULL);
+
+ /* Done with the snapshot used for parsing/planning */
+ if (snapshot_set)
+ PopActiveSnapshot();
+
+ /* If we got a cancel signal in analysis or planning, quit */
+ CHECK_FOR_INTERRUPTS();
+
+ /*
+ * Create a portal to run the query or queries in. If there already
+ * is one with given portal_name, silently drop it.
+ */
+ portal = CreatePortal(portal_name, true, true);
+ /* Don't display the portal in pg_cursors */
+ portal->visible = false;
+
+ /*
+ * We don't have to copy anything into the portal, because everything
+ * we are passing here is in MessageContext, which will outlive the
+ * portal anyway.
+ */
+ PortalDefineQuery(portal,
+ NULL,
+ query_string,
+ commandTag,
+ plantree_list,
+ NULL);
+
+ /*
+ * Start the portal. No parameters here.
+ */
+ PortalStart(portal, NULL, InvalidSnapshot);
+ PortalSetResultFormat(portal, 1, &format);
+
+ /*
+ * Now we can create the destination receiver object.
+ */
+ receiver = CreateDestReceiver(dest);
+ if (dest == DestRemote)
+ SetRemoteDestReceiverParams(receiver, portal);
+
+ /*
+ * Switch back to transaction context for execution.
+ */
+ MemoryContextSwitchTo(oldcontext);
+
+ /*
+ * Run the portal to completion, and then drop it (and the receiver).
+ */
+ (void) PortalRun(portal,
+ FETCH_ALL,
+ isTopLevel,
+ receiver,
+ receiver,
+ completionTag);
+
+ (*receiver->rDestroy) (receiver);
+
+ PortalDrop(portal, false);
+
+ if (!IsA(parsetree, TransactionStmt) && lnext(parsetree_item) != NULL)
+ {
+ CommandCounterIncrement();
+ }
+ }
+ }
+
+ /*
* exec_parse_message
*
* Execute a "Parse" protocol message.
*** a/src/backend/utils/adt/genfile.c
--- b/src/backend/utils/adt/genfile.c
***************
*** 7,12 ****
--- 7,13 ----
* Copyright (c) 2004-2010, PostgreSQL Global Development Group
*
* Author: Andreas Pflug <[email protected]>
+ * Dimitri Fontaine <[email protected]>
*
* IDENTIFICATION
* src/backend/utils/adt/genfile.c
***************
*** 264,266 **** pg_ls_dir(PG_FUNCTION_ARGS)
--- 265,321 ----
SRF_RETURN_DONE(funcctx);
}
+
+ /*
+ * Read a file then execute the SQL commands it contains.
+ */
+ Datum
+ pg_execute_from_file(PG_FUNCTION_ARGS)
+ {
+ text *filename_t = PG_GETARG_TEXT_P(0);
+ char *filename;
+ FILE *file;
+ int64 fsize = -1, nbytes;
+ struct stat fst;
+ char *query_string = NULL;
+
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ (errmsg("must be superuser to get file information"))));
+
+ /*
+ * Only superuser can call pg_execute_from_file, and CREATE EXTENSION
+ * uses that too. Don't double check the PATH. Also note that
+ * extension's install files are not in $PGDATA but `pg_config
+ * --sharedir`.
+ */
+ filename = text_to_cstring(filename_t);
+
+ if (stat(filename, &fst) < 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat file \"%s\": %m", filename)));
+
+ fsize = Int64GetDatum((int64) fst.st_size);
+
+ if ((file = AllocateFile(filename, PG_BINARY_R)) == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\" for reading: %m",
+ filename)));
+
+ if (ferror(file))
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read file \"%s\": %m", filename)));
+
+ query_string = (char *)palloc((fsize+1)*sizeof(char));
+ memset(query_string, 0, fsize+1);
+ nbytes = fread(query_string, 1, (size_t) fsize, file);
+ pg_verifymbstr(query_string, nbytes, false);
+ FreeFile(file);
+
+ exec_multiple_queries(filename, query_string);
+ PG_RETURN_VOID();
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3386,3399 **** DESCR("reload configuration files");
DATA(insert OID = 2622 ( pg_rotate_logfile PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_rotate_logfile _null_ _null_ _null_ ));
DESCR("rotate log file");
! DATA(insert OID = 2623 ( pg_stat_file PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2249 "25" "{25,20,1184,1184,1184,1184,16}" "{i,o,o,o,o,o,o}" "{filename,size,access,modification,change,creation,isdir}" _null_ pg_stat_file _null_ _null_ _null_ ));
DESCR("return file information");
! DATA(insert OID = 2624 ( pg_read_file PGNSP PGUID 12 1 0 0 f f f t f v 3 0 25 "25 20 20" _null_ _null_ _null_ _null_ pg_read_file _null_ _null_ _null_ ));
DESCR("read text from a file");
! DATA(insert OID = 2625 ( pg_ls_dir PGNSP PGUID 12 1 1000 0 f f f t t v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_ls_dir _null_ _null_ _null_ ));
DESCR("list all files in a directory");
! DATA(insert OID = 2626 ( pg_sleep PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "701" _null_ _null_ _null_ _null_ pg_sleep _null_ _null_ _null_ ));
DESCR("sleep for the specified time in seconds");
DATA(insert OID = 2971 ( text PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "16" _null_ _null_ _null_ _null_ booltext _null_ _null_ _null_ ));
DESCR("convert boolean to text");
--- 3386,3401 ----
DATA(insert OID = 2622 ( pg_rotate_logfile PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_rotate_logfile _null_ _null_ _null_ ));
DESCR("rotate log file");
! DATA(insert OID = 2623 ( pg_stat_file PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2249 "25" "{25,20,1184,1184,1184,1184,16}" "{i,o,o,o,o,o,o}" "{filename,size,access,modification,change,creation,isdir}" _null_ pg_stat_file _null_ _null_ _null_ ));
DESCR("return file information");
! DATA(insert OID = 2624 ( pg_read_file PGNSP PGUID 12 1 0 0 f f f t f v 3 0 25 "25 20 20" _null_ _null_ _null_ _null_ pg_read_file _null_ _null_ _null_ ));
DESCR("read text from a file");
! DATA(insert OID = 2625 ( pg_ls_dir PGNSP PGUID 12 1 1000 0 f f f t t v 1 0 25 "25" _null_ _null_ _null_ _null_ pg_ls_dir _null_ _null_ _null_ ));
DESCR("list all files in a directory");
! DATA(insert OID = 2626 ( pg_sleep PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "701" _null_ _null_ _null_ _null_ pg_sleep _null_ _null_ _null_ ));
DESCR("sleep for the specified time in seconds");
+ DATA(insert OID = 3627 ( pg_execute_from_file PGNSP PGUID 12 1 0 0 f f f t f v 1 0 2278 "25" _null_ _null_ _null_ _null_ pg_execute_from_file _null_ _null_ _null_ ));
+ DESCR("execute queries read from a file");
DATA(insert OID = 2971 ( text PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "16" _null_ _null_ _null_ _null_ booltext _null_ _null_ _null_ ));
DESCR("convert boolean to text");
*** a/src/include/postgres.h
--- b/src/include/postgres.h
***************
*** 687,690 **** extern int ExceptionalCondition(const char *conditionName,
--- 687,693 ----
const char *errorType,
const char *fileName, int lineNumber);
+ extern void exec_multiple_queries(const char *portal_name,
+ const char *query_string);
+
#endif /* POSTGRES_H */
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 442,447 **** extern Datum pg_relation_filepath(PG_FUNCTION_ARGS);
--- 442,448 ----
extern Datum pg_stat_file(PG_FUNCTION_ARGS);
extern Datum pg_read_file(PG_FUNCTION_ARGS);
extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
+ extern Datum pg_execute_from_file(PG_FUNCTION_ARGS);
/* misc.c */
extern Datum current_database(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
