2 * psql - the PostgreSQL interactive terminal
4 * Copyright (c) 2000-2004, PostgreSQL Global Development Group
6 * $PostgreSQL: pgsql/src/bin/psql/common.c,v 1.93 2004/10/30 23:10:50 tgl Exp $
8 #include "postgres_fe.h"
18 #include <unistd.h> /* for write() */
21 #include <io.h> /* for _write() */
23 #include <sys/timeb.h> /* for _ftime() */
30 #include "variables.h"
36 #include "mb/pg_wchar.h"
39 /* Workarounds for Windows */
40 /* Probably to be moved up the source tree in the future, perhaps to be replaced by
41 * more specific checks like configure-style HAVE_GETTIMEOFDAY macros.
45 typedef struct timeval TimevalStruct;
47 #define GETTIMEOFDAY(T) gettimeofday(T, NULL)
48 #define DIFF_MSEC(T, U) \
49 ((((int) ((T)->tv_sec - (U)->tv_sec)) * 1000000.0 + \
50 ((int) ((T)->tv_usec - (U)->tv_usec))) / 1000.0)
54 typedef struct _timeb TimevalStruct;
56 #define GETTIMEOFDAY(T) _ftime(T)
57 #define DIFF_MSEC(T, U) \
58 (((T)->time - (U)->time) * 1000.0 + \
59 ((T)->millitm - (U)->millitm))
62 extern bool prompt_state;
65 static bool command_no_begin(const char *query);
69 * "Safe" wrapper around strdup()
72 pg_strdup(const char *string)
78 fprintf(stderr, gettext("%s: xstrdup: cannot duplicate null pointer (internal error)\n"),
85 psql_error("out of memory\n");
92 pg_malloc(size_t size)
99 psql_error("out of memory\n");
106 pg_malloc_zero(size_t size)
110 tmp = pg_malloc(size);
111 memset(tmp, 0, size);
116 pg_calloc(size_t nmemb, size_t size)
120 tmp = calloc(nmemb, size);
123 psql_error("out of memory");
131 * -- handler for -o command line option and \o command
133 * Tries to open file fname (or pipe if fname starts with '|')
134 * and stores the file handle in pset)
135 * Upon failure, sets stdout and returns false.
138 setQFout(const char *fname)
142 /* Close old file/pipe */
143 if (pset.queryFout && pset.queryFout != stdout && pset.queryFout != stderr)
145 if (pset.queryFoutPipe)
146 pclose(pset.queryFout);
148 fclose(pset.queryFout);
151 /* If no filename, set stdout */
152 if (!fname || fname[0] == '\0')
154 pset.queryFout = stdout;
155 pset.queryFoutPipe = false;
157 else if (*fname == '|')
159 pset.queryFout = popen(fname + 1, "w");
160 pset.queryFoutPipe = true;
164 pset.queryFout = fopen(fname, "w");
165 pset.queryFoutPipe = false;
168 if (!(pset.queryFout))
170 psql_error("%s: %s\n", fname, strerror(errno));
171 pset.queryFout = stdout;
172 pset.queryFoutPipe = false;
178 pqsignal(SIGPIPE, pset.queryFoutPipe ? SIG_IGN : SIG_DFL);
187 * Error reporting for scripts. Errors should look like
188 * psql:filename:lineno: message
192 psql_error(const char *fmt,...)
197 if (pset.queryFout != stdout)
198 fflush(pset.queryFout);
201 fprintf(stderr, "%s:%s:%u: ", pset.progname, pset.inputfile, pset.lineno);
203 vfprintf(stderr, gettext(fmt), ap);
210 * for backend Notice messages (INFO, WARNING, etc)
213 NoticeProcessor(void *arg, const char *message)
215 (void) arg; /* not used */
216 psql_error("%s", message);
222 * Code to support query cancellation
224 * Before we start a query, we enable a SIGINT signal catcher that sends a
225 * cancel request to the backend. Note that sending the cancel directly from
226 * the signal handler is safe because PQcancel() is written to make it
227 * so. We use write() to print to stderr because it's better to use simple
228 * facilities in a signal handler.
230 * On win32, the signal cancelling happens on a separate thread, because
231 * that's how SetConsoleCtrlHandler works. The PQcancel function is safe
232 * for this (unlike PQrequestCancel). However, a CRITICAL_SECTION is required
233 * to protect the PGcancel structure against being changed while the other
234 * thread is using it.
236 static PGcancel *cancelConn = NULL;
238 static CRITICAL_SECTION cancelConnLock;
241 volatile bool cancel_pressed = false;
243 #define write_stderr(str) write(fileno(stderr), str, strlen(str))
249 handle_sigint(SIGNAL_ARGS)
251 int save_errno = errno;
254 /* Don't muck around if prompting for a password. */
258 if (cancelConn == NULL)
259 siglongjmp(main_loop_jmp, 1);
261 cancel_pressed = true;
263 if (PQcancel(cancelConn, errbuf, sizeof(errbuf)))
264 write_stderr("Cancel request sent\n");
267 write_stderr("Could not send cancel request: ");
268 write_stderr(errbuf);
270 errno = save_errno; /* just in case the write changed it */
276 consoleHandler(DWORD dwCtrlType)
280 if (dwCtrlType == CTRL_C_EVENT ||
281 dwCtrlType == CTRL_BREAK_EVENT)
286 /* Perform query cancel */
287 EnterCriticalSection(&cancelConnLock);
288 if (cancelConn != NULL)
290 cancel_pressed = true;
292 if (PQcancel(cancelConn, errbuf, sizeof(errbuf)))
293 write_stderr("Cancel request sent\n");
296 write_stderr("Could not send cancel request: ");
297 write_stderr(errbuf);
300 LeaveCriticalSection(&cancelConnLock);
305 /* Return FALSE for any signals not being handled */
310 setup_cancel_handler(void)
312 InitializeCriticalSection(&cancelConnLock);
313 SetConsoleCtrlHandler(consoleHandler, TRUE);
321 * Returns whether our backend connection is still there.
326 return PQstatus(pset.db) != CONNECTION_BAD;
333 * Verify that we still have a good connection to the backend, and if not,
334 * see if it can be restored.
336 * Returns true if either the connection was still there, or it could be
337 * restored successfully; false otherwise. If, however, there was no
338 * connection and the session is non-interactive, this will exit the program
339 * with a code of EXIT_BADCONN.
342 CheckConnection(void)
349 if (!pset.cur_cmd_interactive)
351 psql_error("connection to server was lost\n");
355 fputs(gettext("The connection to the server was lost. Attempting reset: "), stderr);
360 fputs(gettext("Failed.\n"), stderr);
367 fputs(gettext("Succeeded.\n"), stderr);
378 * Set cancelConn to point to the current database connection.
384 EnterCriticalSection(&cancelConnLock);
387 /* Free the old one if we have one */
388 if (cancelConn != NULL)
389 PQfreeCancel(cancelConn);
391 cancelConn = PQgetCancel(pset.db);
394 LeaveCriticalSection(&cancelConnLock);
402 * Free the current cancel connection, if any, and set to NULL.
405 ResetCancelConn(void)
408 EnterCriticalSection(&cancelConnLock);
412 PQfreeCancel(cancelConn);
417 LeaveCriticalSection(&cancelConnLock);
423 * on errors, print syntax error position if available.
425 * the query is expected to be in the client encoding.
428 ReportSyntaxErrorPosition(const PGresult *result, const char *query)
430 #define DISPLAY_SIZE 60 /* screen width limit, in screen cols */
431 #define MIN_RIGHT_CUT 10 /* try to keep this far away from EOL */
450 if (pset.verbosity == PQERRORS_TERSE)
453 sp = PQresultErrorField(result, PG_DIAG_STATEMENT_POSITION);
456 sp = PQresultErrorField(result, PG_DIAG_INTERNAL_POSITION);
458 return; /* no syntax error */
459 query = PQresultErrorField(result, PG_DIAG_INTERNAL_QUERY);
462 return; /* nothing to reference location to */
464 if (sscanf(sp, "%d", &loc) != 1)
466 psql_error("INTERNAL ERROR: unexpected statement position \"%s\"\n",
471 /* Make a writable copy of the query, and a buffer for messages. */
472 wquery = pg_strdup(query);
474 initPQExpBuffer(&msg);
477 * The returned cursor position is measured in logical characters.
478 * Each character might occupy multiple physical bytes in the string,
479 * and in some Far Eastern character sets it might take more than one
480 * screen column as well. We compute the starting byte offset and
481 * starting screen column of each logical character, and store these
482 * in qidx[] and scridx[] respectively.
485 /* we need a safe allocation size... */
486 slen = strlen(query) + 1;
488 qidx = (int *) pg_malloc(slen * sizeof(int));
489 scridx = (int *) pg_malloc(slen * sizeof(int));
493 for (i = 0; query[qoffset] != '\0'; i++)
496 scridx[i] = scroffset;
497 scroffset += PQdsplen(&query[qoffset], pset.encoding);
498 qoffset += PQmblen(&query[qoffset], pset.encoding);
501 scridx[i] = scroffset;
503 psql_assert(clen < slen);
505 /* convert loc to zero-based offset in qidx/scridx arrays */
508 /* do we have something to show? */
509 if (loc >= 0 && loc <= clen)
511 /* input line number of our syntax error. */
513 /* first included char of extract. */
515 /* last-plus-1 included char of extract. */
519 * Replace tabs with spaces in the writable copy. (Later we might
520 * want to think about coping with their variable screen width,
523 * Extract line number and begin and end indexes of line containing
524 * error location. There will not be any newlines or carriage
525 * returns in the selected extract.
527 for (i = 0; i < clen; i++)
529 /* character length must be 1 or it's not ASCII */
530 if ((qidx[i + 1] - qidx[i]) == 1)
532 if (wquery[qidx[i]] == '\t')
533 wquery[qidx[i]] = ' ';
534 else if (wquery[qidx[i]] == '\r' || wquery[qidx[i]] == '\n')
539 * count lines before loc. Each \r or \n counts
540 * as a line except when \r \n appear together.
542 if (wquery[qidx[i]] == '\r' ||
544 (qidx[i] - qidx[i - 1]) != 1 ||
545 wquery[qidx[i - 1]] != '\r')
547 /* extract beginning = last line start before loc. */
552 /* set extract end. */
561 /* If the line extracted is too long, we truncate it. */
564 if (scridx[iend] - scridx[ibeg] > DISPLAY_SIZE)
567 * We first truncate right if it is enough. This code might
568 * be off a space or so on enforcing MIN_RIGHT_CUT if there's
569 * a wide character right there, but that should be okay.
571 if (scridx[ibeg] + DISPLAY_SIZE >= scridx[loc] + MIN_RIGHT_CUT)
573 while (scridx[iend] - scridx[ibeg] > DISPLAY_SIZE)
579 /* Truncate right if not too close to loc. */
580 while (scridx[loc] + MIN_RIGHT_CUT < scridx[iend])
586 /* Truncate left if still too long. */
587 while (scridx[iend] - scridx[ibeg] > DISPLAY_SIZE)
595 /* the extract MUST contain the target position! */
596 psql_assert(ibeg <= loc && loc <= iend);
598 /* truncate working copy at desired endpoint */
599 wquery[qidx[iend]] = '\0';
601 /* Begin building the finished message. */
602 printfPQExpBuffer(&msg, gettext("LINE %d: "), loc_line);
604 appendPQExpBufferStr(&msg, "...");
607 * While we have the prefix in the msg buffer, compute its screen
611 for (i = 0; i < msg.len; i += PQmblen(&msg.data[i], pset.encoding))
612 scroffset += PQdsplen(&msg.data[i], pset.encoding);
614 /* Finish and emit the message. */
615 appendPQExpBufferStr(&msg, &wquery[qidx[ibeg]]);
617 appendPQExpBufferStr(&msg, "...");
619 psql_error("%s\n", msg.data);
621 /* Now emit the cursor marker line. */
622 scroffset += scridx[loc] - scridx[ibeg];
623 resetPQExpBuffer(&msg);
624 for (i = 0; i < scroffset; i++)
625 appendPQExpBufferChar(&msg, ' ');
626 appendPQExpBufferChar(&msg, '^');
628 psql_error("%s\n", msg.data);
632 termPQExpBuffer(&msg);
643 * Checks whether a result is valid, giving an error message if necessary;
644 * resets cancelConn as needed, and ensures that the connection to the backend
647 * Returns true for valid result, false for error state.
650 AcceptResult(const PGresult *result, const char *query)
659 switch (PQresultStatus(result))
661 case PGRES_COMMAND_OK:
662 case PGRES_TUPLES_OK:
663 case PGRES_EMPTY_QUERY:
665 /* Fine, do nothing */
669 /* keep cancel connection for copy out state */
680 psql_error("%s", PQerrorMessage(pset.db));
681 ReportSyntaxErrorPosition(result, query);
693 * This is the way to send "backdoor" queries (those not directly entered
694 * by the user). It is subject to -E but not -e.
696 * In autocommit-off mode, a new transaction block is started if start_xact
697 * is true; nothing special is done when start_xact is false. Typically,
698 * start_xact = false is used for SELECTs and explicit BEGIN/COMMIT commands.
700 * Note: we don't bother to check PQclientEncoding; it is assumed that no
701 * caller uses this path to issue "SET CLIENT_ENCODING".
704 PSQLexec(const char *query, bool start_xact)
711 psql_error("You are currently not connected to a database.\n");
715 echo_hidden = SwitchVariable(pset.vars, "ECHO_HIDDEN", "noexec", NULL);
716 if (echo_hidden != VAR_NOTSET)
718 printf("********* QUERY **********\n"
720 "**************************\n\n", query);
723 if (echo_hidden == 1) /* noexec? */
729 if (start_xact && PQtransactionStatus(pset.db) == PQTRANS_IDLE &&
730 !GetVariableBool(pset.vars, "AUTOCOMMIT"))
732 res = PQexec(pset.db, "BEGIN");
733 if (PQresultStatus(res) != PGRES_COMMAND_OK)
735 psql_error("%s", PQerrorMessage(pset.db));
743 res = PQexec(pset.db, query);
745 if (!AcceptResult(res, query) && res)
757 * PrintNotifications: check for asynchronous notifications, and print them out
760 PrintNotifications(void)
764 while ((notify = PQnotifies(pset.db)))
766 fprintf(pset.queryFout, gettext("Asynchronous notification \"%s\" received from server process with PID %d.\n"),
767 notify->relname, notify->be_pid);
768 fflush(pset.queryFout);
775 * PrintQueryTuples: assuming query result is OK, print its tuples
777 * Returns true if successful, false otherwise.
780 PrintQueryTuples(const PGresult *results)
782 /* write output to \g argument, if any */
785 FILE *queryFout_copy = pset.queryFout;
786 bool queryFoutPipe_copy = pset.queryFoutPipe;
788 pset.queryFout = stdout; /* so it doesn't get closed */
791 if (!setQFout(pset.gfname))
793 pset.queryFout = queryFout_copy;
794 pset.queryFoutPipe = queryFoutPipe_copy;
798 printQuery(results, &pset.popt, pset.queryFout);
800 /* close file/pipe, restore old setting */
803 pset.queryFout = queryFout_copy;
804 pset.queryFoutPipe = queryFoutPipe_copy;
810 printQuery(results, &pset.popt, pset.queryFout);
817 * ProcessCopyResult: if command was a COPY FROM STDIN/TO STDOUT, handle it
819 * Note: Utility function for use by SendQuery() only.
821 * Returns true if the query executed successfully, false otherwise.
824 ProcessCopyResult(PGresult *results)
826 bool success = false;
831 switch (PQresultStatus(results))
833 case PGRES_TUPLES_OK:
834 case PGRES_COMMAND_OK:
835 case PGRES_EMPTY_QUERY:
836 /* nothing to do here */
841 success = handleCopyOut(pset.db, pset.queryFout);
845 success = handleCopyIn(pset.db, pset.cur_cmd_source);
852 /* may need this to recover from conn loss during COPY */
853 if (!CheckConnection())
861 * PrintQueryResults: print out query results as required
863 * Note: Utility function for use by SendQuery() only.
865 * Returns true if the query executed successfully, false otherwise.
868 PrintQueryResults(PGresult *results)
870 bool success = false;
875 switch (PQresultStatus(results))
877 case PGRES_TUPLES_OK:
878 success = PrintQueryTuples(results);
881 case PGRES_COMMAND_OK:
886 snprintf(buf, sizeof(buf),
887 "%u", (unsigned int) PQoidValue(results));
890 if (pset.popt.topt.format == PRINT_HTML)
892 fputs("<p>", pset.queryFout);
893 html_escaped_print(PQcmdStatus(results),
895 fputs("</p>\n", pset.queryFout);
898 fprintf(pset.queryFout, "%s\n", PQcmdStatus(results));
900 SetVariable(pset.vars, "LASTOID", buf);
904 case PGRES_EMPTY_QUERY:
910 /* nothing to do here */
918 fflush(pset.queryFout);
925 * SendQuery: send the query string to the backend
926 * (and print out results)
928 * Note: This is the "front door" way to send a query. That is, use it to
929 * send queries actually entered by the user. These queries will be subject to
931 * To send "back door" queries (generated by slash commands, etc.) in a
932 * controlled way, use PSQLexec().
934 * Returns true if the query executed successfully, false otherwise.
937 SendQuery(const char *query)
940 TimevalStruct before,
946 psql_error("You are currently not connected to a database.\n");
950 if (GetVariableBool(pset.vars, "SINGLESTEP"))
954 printf(gettext("***(Single step mode: verify command)*******************************************\n"
956 "***(press return to proceed or enter x and return to cancel)********************\n"),
959 if (fgets(buf, sizeof(buf), stdin) != NULL)
963 else if (VariableEquals(pset.vars, "ECHO", "queries"))
971 if (PQtransactionStatus(pset.db) == PQTRANS_IDLE &&
972 !GetVariableBool(pset.vars, "AUTOCOMMIT") &&
973 !command_no_begin(query))
975 results = PQexec(pset.db, "BEGIN");
976 if (PQresultStatus(results) != PGRES_COMMAND_OK)
978 psql_error("%s", PQerrorMessage(pset.db));
987 GETTIMEOFDAY(&before);
989 results = PQexec(pset.db, query);
991 /* these operations are included in the timing result: */
992 OK = (AcceptResult(results, query) && ProcessCopyResult(results));
995 GETTIMEOFDAY(&after);
997 /* but printing results isn't: */
999 OK = PrintQueryResults(results);
1003 /* Possible microtiming output */
1004 if (OK && pset.timing)
1005 printf(gettext("Time: %.3f ms\n"), DIFF_MSEC(&after, &before));
1007 /* check for events that may occur during query execution */
1009 if (pset.encoding != PQclientEncoding(pset.db) &&
1010 PQclientEncoding(pset.db) >= 0)
1012 /* track effects of SET CLIENT_ENCODING */
1013 pset.encoding = PQclientEncoding(pset.db);
1014 pset.popt.topt.encoding = pset.encoding;
1015 SetVariable(pset.vars, "ENCODING",
1016 pg_encoding_to_char(pset.encoding));
1019 PrintNotifications();
1026 * Advance the given char pointer over white space and SQL comments.
1029 skip_white_space(const char *query)
1031 int cnestlevel = 0; /* slash-star comment nest level */
1035 int mblen = PQmblen(query, pset.encoding);
1038 * Note: we assume the encoding is a superset of ASCII, so that
1039 * for example "query[0] == '/'" is meaningful. However, we do NOT
1040 * assume that the second and subsequent bytes of a multibyte
1041 * character couldn't look like ASCII characters; so it is critical
1042 * to advance by mblen, not 1, whenever we haven't exactly identified
1043 * the character we are skipping over.
1045 if (isspace((unsigned char) *query))
1047 else if (query[0] == '/' && query[1] == '*')
1052 else if (cnestlevel > 0 && query[0] == '*' && query[1] == '/')
1057 else if (cnestlevel == 0 && query[0] == '-' && query[1] == '-')
1061 * We have to skip to end of line since any slash-star inside
1062 * the -- comment does NOT start a slash-star comment.
1071 query += PQmblen(query, pset.encoding);
1074 else if (cnestlevel > 0)
1077 break; /* found first token */
1085 * Check whether a command is one of those for which we should NOT start
1086 * a new transaction block (ie, send a preceding BEGIN).
1088 * These include the transaction control statements themselves, plus
1089 * certain statements that the backend disallows inside transaction blocks.
1092 command_no_begin(const char *query)
1097 * First we must advance over any whitespace and comments.
1099 query = skip_white_space(query);
1102 * Check word length (since "beginx" is not "begin").
1105 while (isalpha((unsigned char) query[wordlen]))
1106 wordlen += PQmblen(&query[wordlen], pset.encoding);
1109 * Transaction control commands. These should include every keyword
1110 * that gives rise to a TransactionStmt in the backend grammar, except
1111 * for the savepoint-related commands.
1113 * (We assume that START must be START TRANSACTION, since there is
1114 * presently no other "START foo" command.)
1116 if (wordlen == 5 && pg_strncasecmp(query, "abort", 5) == 0)
1118 if (wordlen == 5 && pg_strncasecmp(query, "begin", 5) == 0)
1120 if (wordlen == 5 && pg_strncasecmp(query, "start", 5) == 0)
1122 if (wordlen == 6 && pg_strncasecmp(query, "commit", 6) == 0)
1124 if (wordlen == 3 && pg_strncasecmp(query, "end", 3) == 0)
1126 if (wordlen == 8 && pg_strncasecmp(query, "rollback", 8) == 0)
1130 * Commands not allowed within transactions. The statements checked
1131 * for here should be exactly those that call PreventTransactionChain()
1134 * Note: we are a bit sloppy about CLUSTER, which is transactional in
1135 * some variants but not others.
1137 if (wordlen == 6 && pg_strncasecmp(query, "vacuum", 6) == 0)
1139 if (wordlen == 7 && pg_strncasecmp(query, "cluster", 7) == 0)
1143 * Note: these tests will match REINDEX TABLESPACE, which isn't really
1144 * a valid command so we don't care much. The other five possible
1145 * matches are correct.
1147 if ((wordlen == 6 && pg_strncasecmp(query, "create", 6) == 0) ||
1148 (wordlen == 4 && pg_strncasecmp(query, "drop", 4) == 0) ||
1149 (wordlen == 7 && pg_strncasecmp(query, "reindex", 7) == 0))
1153 query = skip_white_space(query);
1156 while (isalpha((unsigned char) query[wordlen]))
1157 wordlen += PQmblen(&query[wordlen], pset.encoding);
1159 if (wordlen == 8 && pg_strncasecmp(query, "database", 8) == 0)
1161 if (wordlen == 10 && pg_strncasecmp(query, "tablespace", 10) == 0)
1170 parse_char(char **buf)
1174 l = strtol(*buf, buf, 0);
1181 * Test if the current user is a database superuser.
1183 * Note: this will correctly detect superuserness only with a protocol-3.0
1184 * or newer backend; otherwise it will always say "false".
1194 val = PQparameterStatus(pset.db, "is_superuser");
1196 if (val && strcmp(val, "on") == 0)
1204 * Return the session user of the current connection.
1206 * Note: this will correctly detect the session user only with a
1207 * protocol-3.0 or newer backend; otherwise it will return the
1211 session_username(void)
1218 val = PQparameterStatus(pset.db, "session_authorization");
1222 return PQuser(pset.db);
1228 * substitute '~' with HOME or '~username' with username's home dir
1232 expand_tilde(char **filename)
1234 if (!filename || !(*filename))
1237 /* MSDOS uses tilde for short versions of long file names, so skip it. */
1240 /* try tilde expansion */
1241 if (**filename == '~')
1247 char home[MAXPGPATH];
1253 while (*p != '/' && *p != '\0')
1259 if (*(fn + 1) == '\0')
1260 get_home_path(home);
1261 else if ((pw = getpwnam(fn + 1)) != NULL)
1262 StrNCpy(home, pw->pw_dir, MAXPGPATH);
1265 if (strlen(home) != 0)
1269 newfn = pg_malloc(strlen(home) + strlen(p) + 1);
1270 strcpy(newfn, home);