Alvaro Herrera <[email protected]> writes:
> The error message wording needs some work; maybe
> errmsg("file \"%s\" could not be executed", filename)
[...]
> I happened to notice that there are several pieces of code that are
> calling SPI_connect and SPI_finish without checking the return value,
> notably the FTS code and xml.c.
Please find attached pg_execute_from_file.v4.patch with fixes. I've used
the usual error messages for SPI_connect() and finish this time.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13897,13902 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 13897,13909 ----
<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>
***************
*** 13935,13940 **** SELECT (pg_stat_file('filename')).modification;
--- 13942,13956 ----
</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
***************
*** 13957,13962 **** SELECT (pg_stat_file('filename')).modification;
--- 13973,13979 ----
<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/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
***************
*** 21,26 ****
--- 22,28 ----
#include <dirent.h>
#include "catalog/pg_type.h"
+ #include "executor/spi.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
***************
*** 264,266 **** pg_ls_dir(PG_FUNCTION_ARGS)
--- 266,340 ----
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);
+
+ /*
+ * We abuse some internal knowledge from spi.h here. As we don't know
+ * which queries are going to get executed, we don't know what to expect
+ * as an OK return code from SPI_execute(). We assume that
+ * SPI_OK_CONNECT, SPI_OK_FINISH and SPI_OK_FETCH are quite improbable,
+ * though, and the errors are negatives. So a valid return code is
+ * considered to be SPI_OK_UTILITY or anything from there.
+ */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ if (SPI_execute(query_string, false, 0) < SPI_OK_UTILITY)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATA_EXCEPTION),
+ errmsg("File '%s' could not be executed", filename)));
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+
+ 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/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
