Thread: pg_get_viewdef 7.4 et al
OK,
here's my proposal for a more economical use of parentheses.
- implicit casted variables are not put in parentheses
(myVar)::integer -> myVar::integer, no chance to misinterpret this
- explicit CASTs are represented as CAST(... AS ...) for better
distinction from implicit ones
- a join's ON expression will not have parentheses automatically JOIN
xxx ON (a.oid=b.relid) -> JOIN xx ON a.oid=b.relid
- JOIN clauses are only in parens if the right expression of a JOIN is a
JOIN itself. For the left fork, the usual left-to-right order is
evaluated by default, so no parentheses are needed.
- Operators will have their operands plain if they are simple. If
T_OpExpr or T_BoolExpr operands are complex themselves, the operands are
put in parens(a+b) -> a+b, a+b*c -> a+(b*c)
This strategy greatly improves readability.
Regards,
Andreas
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.137
diff -r1.137 ruleutils.c
2126a2127,2147> /*> * get_rule_expr_paren> * checks if the expression is of same kind as <type>> * if so,
parenthesesare needed.> * currently only used for T_OpExpr and T_BoolExpr> */> static void get_rule_expr_paren(Node
*node,deparse_context *context,
bool showimplicit, NodeTag type)> {> StringInfo buf = context->buf;>> bool need_paren = (nodeTag(node) ==
type);> if (need_paren)> appendStringInfoChar(buf, '(');>> get_rule_expr(node, context, showimplicit);>>
if (need_paren)> appendStringInfoChar(buf, ')');> }>>
2293,2294c2314,2315
< appendStringInfoChar(buf, '(');
< get_rule_expr((Node *) lfirst(args), context,
false);
---> get_rule_expr_paren((Node *) lfirst(args),
context,> false, T_BoolExpr);
2297,2299c2318,2320
< appendStringInfo(buf, " AND ");
< get_rule_expr((Node *) lfirst(args), context,
< false);
---> appendStringInfo(buf, " AND ");>
get_rule_expr_paren((Node*)
lfirst(args), context,> false, T_BoolExpr);
2301d2321
< appendStringInfoChar(buf, ')');
2305,2306c2325,2326
< appendStringInfoChar(buf, '(');
< get_rule_expr((Node *) lfirst(args), context,
false);
---> get_rule_expr_paren((Node *) lfirst(args),
context,> false, T_BoolExpr);
2310,2311c2330,2331
< get_rule_expr((Node *) lfirst(args), context,
< false);
---> get_rule_expr_paren((Node *)
lfirst(args), context,> false, T_BoolExpr);
2313d2332
< appendStringInfoChar(buf, ')');
2317,2319c2336,2338
< appendStringInfo(buf, "(NOT ");
< get_rule_expr((Node *) lfirst(args), context,
false);
< appendStringInfoChar(buf, ')');
---> appendStringInfo(buf, "NOT ");> get_rule_expr_paren((Node *)
lfirst(args),
context,> false, T_BoolExpr);
2394d2412
< appendStringInfoChar(buf, '(');
2396c2414
< appendStringInfo(buf, ")::%s",
---> appendStringInfo(buf, "::%s",
2536d2553
< appendStringInfoChar(buf, '(');
2538c2555
< appendStringInfo(buf, ")::%s",
---> appendStringInfo(buf, "::%s",
2566d2582
< appendStringInfoChar(buf, '(');
2573c2589
< get_rule_expr(arg1, context, true);
---> get_rule_expr_paren(arg1, context, true, T_OpExpr);
2578c2594
< get_rule_expr(arg2, context, true);
---> get_rule_expr_paren(arg2, context, true, T_OpExpr);
2585a2602,2604> bool need_paren = (nodeTag(arg) == T_OpExpr);> if (need_paren)>
appendStringInfoChar(buf,'(');
2597,2599c2616,2618
< generate_operator_name(opno,
< InvalidOid,
< exprType(arg)));
---> generate_operator_name(opno,> InvalidOid,>
exprType(arg)));
2605,2607c2624,2626
< generate_operator_name(opno,
< exprType(arg),
< InvalidOid));
---> generate_operator_name(opno,> exprType(arg),>
InvalidOid));
2612a2632,2633> if (need_paren)> appendStringInfoChar(buf, ')');
2614d2634
< appendStringInfoChar(buf, ')');
2663c2683
< appendStringInfoChar(buf, '(');
---> appendStringInfo(buf, "CAST(");
2665c2685
< appendStringInfo(buf, ")::%s",
---> appendStringInfo(buf, " AS %s)",
3112a3133> bool need_paren = !IsA(j->rarg, RangeTblRef);
3114d3134
< appendStringInfoChar(buf, '(');
3141a3162,3165>> if (need_paren)> appendStringInfoChar(buf, '(');>
3142a3167,3170>> if (need_paren)> appendStringInfoChar(buf, ')');>
3161c3189
< appendStringInfo(buf, " ON (");
---> appendStringInfo(buf, " ON ");
3163d3190
< appendStringInfoChar(buf, ')');
3166,3167c3193
< appendStringInfoChar(buf, ')');
< /* Yes, it's correct to put alias after the right paren ... */
--->
Andreas Pflug <Andreas.Pflug@web.de> writes:
> here's my proposal for a more economical use of parentheses.
I believe the proposed patch breaks many nontrivial cases (though it's
quite hard to be sure exactly what it does, given a non-contextual diff
:-(). Have you tried running its output back into the system to see
whether the querytree is reconstructed exactly? Don't forget to try
cases where parentheses were used in the original source to force a
non-default evaluation order.
regards, tom lane
Andreas Pflug <Andreas.Pflug@web.de> writes:
> it was a cvs diff, should I upload the whole source?
diff -c is the standard format for patch submissions.
Some people use diff -u, but I find that less readable.
> I wasn't able to force a T_CoerceToDomain, and only got T_RelabelType
> with simple variables.
Try (foo || bar)::varchar to see an example of a relabel on a nontrivial
input expression. For CoerceToDomain, try creating a domain over text
and then coerce to that.
regards, tom lane
Andreas Pflug <Andreas.Pflug@web.de> writes:
> I believe we have to discuss this a little more in-depth.
> Parenthese-usage needs theroretical proof, since not all cases can be
> tested. So I list the assumptions I made.
Quite aside from any errors in this analysis, the thing that is
bothering me about this approach is its fragility. You've got a lot
of case-by-case considerations here that could fall apart after any
minor rearrangement of the parsetree representation. Who's going to
think to re-examine the entire ruleutils logic every time they add a
parse node type?
Also, the penalty for errors seems dire. If ruleutils fails to
parenthesize something that should be parenthesized, when are we going
to find out about it? Not until someone's rule or view malfunctions
after being dumped and reloaded; which will probably mean that the error
has been out in the field for a full release cycle.
I'm not really eager to take such risks just to make the output a little
prettier ...
> T_Var, T_Const, T_Param, T_Aggref, T_ArrayRef, T_FuncExpr,
> T_DistinctExpr, T_SubLink, T_SubPlan, T_FieldSelect, T_NullIfExpr,
> T_NullTest, T_BooleanTest, T_CoerceToDomainValue can be handled as a
> simple argument.
Let's see, how many mistakes?
T_DistinctExpr may need to be parenthesized, since IS has lower precedence
than some operators. For example, assuming someone had defined a +
operator for booleans:bool_var + (a IS DISTINCT FROM b)
Omitting the parens would cause this to parse as(bool_var + a) IS DISTINCT FROM b
which is wrong. (But, if a and b are themselves boolean, the mistake
would pass undetected until someone tracks down why their application is
malfunctioning.)
NullTest and BooleanTest have the identical issue.
Treating CoerceToDomainValue as a unit is risky because the coercion may
not show up in the output at all; you might get just the bare
subexpression which is not necessarily unitary. It might be okay if you
further check that the coercion type is EXPLICIT.
ArrayRef is not always a simple argument. As a counterexample consider
the following (which only started working as of CVS tip, but it works):
regression=# create table t1 (p point[]);
CREATE TABLE
regression=# insert into t1 values(array['(3,4)'::point,'(4,5)','(6,7)']);
INSERT 1409638 1
regression=# select p[2] from t1; p
-------(4,5)
(1 row)
regression=# select (p[2])[0] from t1;p
---4
(1 row)
The parentheses are required here because p[2][0] means something quite
different, viz subscripting in a multi-dimensional point array.
> - T_CoerceToDomain and T_RelabelType:
> Both only take simple arguments, no complex expressions and thus don't
> need parentheses.
Wrong, you simply haven't tried hard enough.
> - T_OperExpr:
> Arguments only need parentheses if they are of type T_OperExpr or
> T_BoolExpr.
I don't think this is entirely correct either. IN subexpressions, for
example, would need to be parenthesized.
The general point here is that getting this right requires extremely
close analysis of the grammar's precedence rules, and any small change
in the grammar could break it.
regards, tom lane
On AIX4.3.2, 7.3.2 version, dump command - pg_dump dbname|gzip >db.gz restore - gunzip -c db.gz|psql dbname It looks like the dump is finished without errors, the size of dump file - -rw-rw-r-- 1 stiadmin apps 182445542 Apr 09 06:53 emrxdbs.gz many free space on the file system. after restore, only a few indexes remain on the tables, the rest of them are not restored, The record counts are correct, though. What's the cause of this losing indexes after restore? (I can always recreate them, but try to understand if this is normal/or expected behavior of pg_dump). Enclosed is the log info of this process (8:30am I issued psql to check if data loading finished or not, see the log) thanks for the help. johnl ================ 2003-04-09 07:05:58 NOTICE: CREATE TABLE will create implicit sequence 'provider_providerid_seq' for SERIAL column'provider.providerid' 2003-04-09 07:05:58 NOTICE: CREATE TABLE will create implicit sequence 'patient_patseq_seq' for SERIAL column 'pat ient.patseq' 2003-04-09 07:05:58 NOTICE: CREATE TABLE will create implicit sequence 'documenttype_doctypeid_seq' for SERIAL col umn 'documenttype.doctypeid' 2003-04-09 07:08:15 LOG: recycled transaction log file 0000000000000000 2003-04-09 07:08:15 LOG: recycled transaction log file 0000000000000001 2003-04-09 07:08:15 LOG: recycled transaction log file 0000000000000002 2003-04-09 07:09:25 LOG: recycled transaction log file 0000000000000003 2003-04-09 07:09:25 LOG: recycled transaction log file 0000000000000004 2003-04-09 07:09:25 LOG: recycled transaction log file 0000000000000005 2003-04-09 07:10:35 LOG: recycled transaction log file 0000000000000008 2003-04-09 07:10:35 LOG: recycled transaction log file 0000000000000006 2003-04-09 07:10:35 LOG: recycled transaction log file 0000000000000007 2003-04-09 07:11:45 LOG: recycled transaction log file 0000000000000009 2003-04-09 07:11:45 LOG: recycled transaction log file 000000000000000A 2003-04-09 07:11:45 LOG: recycled transaction log file 000000000000000B 2003-04-09 07:12:55 LOG: recycled transaction log file 000000000000000E 2003-04-09 07:12:55 LOG: recycled transaction log file 000000000000000C 2003-04-09 07:12:55 LOG: recycled transaction log file 000000000000000D 2003-04-09 07:14:07 LOG: recycled transaction log file 0000000000000011 2003-04-09 07:14:07 LOG: recycled transaction log file 000000000000000F 2003-04-09 07:14:07 LOG: recycled transaction log file 0000000000000010 2003-04-09 07:15:16 LOG: recycled transaction log file 0000000000000014 2003-04-09 07:15:16 LOG: recycled transaction log file 0000000000000012 2003-04-09 07:15:16 LOG: recycled transaction log file 0000000000000013 2003-04-09 07:16:27 LOG: recycled transaction log file 0000000000000017 2003-04-09 07:16:27 LOG: recycled transaction log file 0000000000000015 2003-04-09 07:16:27 LOG: recycled transaction log file 0000000000000016 2003-04-09 07:17:41 LOG: recycled transaction log file 000000000000001A 2003-04-09 07:17:41 LOG: recycled transaction log file 0000000000000018 2003-04-09 07:17:41 LOG: recycled transaction log file 0000000000000019 2003-04-09 07:18:58 LOG: recycled transaction log file 000000000000001D 2003-04-09 07:18:58 LOG: recycled transaction log file 000000000000001B 2003-04-09 07:18:58 LOG: recycled transaction log file 000000000000001C 2003-04-09 07:20:19 LOG: recycled transaction log file 0000000000000020 2003-04-09 07:20:19 LOG: recycled transaction log file 000000000000001E 2003-04-09 07:20:19 LOG: recycled transaction log file 000000000000001F 2003-04-09 07:21:39 LOG: recycled transaction log file 0000000000000023 2003-04-09 07:21:39 LOG: recycled transaction log file 0000000000000021 2003-04-09 07:21:39 LOG: recycled transaction log file 0000000000000022 2003-04-09 07:23:02 LOG: recycled transaction log file 0000000000000026 2003-04-09 07:23:02 LOG: recycled transaction log file 0000000000000024 2003-04-09 07:23:02 LOG: recycled transaction log file 0000000000000025 2003-04-09 07:24:23 LOG: recycled transaction log file 0000000000000029 2003-04-09 07:24:23 LOG: recycled transaction log file 0000000000000027 2003-04-09 07:24:23 LOG: recycled transaction log file 0000000000000028 2003-04-09 07:25:36 LOG: recycled transaction log file 000000000000002C 2003-04-09 07:25:36 LOG: recycled transaction log file 000000000000002A 2003-04-09 07:25:36 LOG: recycled transaction log file 000000000000002B 2003-04-09 07:26:56 LOG: recycled transaction log file 000000000000002F 2003-04-09 07:26:56 LOG: recycled transaction log file 000000000000002D 2003-04-09 07:26:56 LOG: recycled transaction log file 000000000000002E 2003-04-09 07:28:12 LOG: recycled transaction log file 0000000000000032 2003-04-09 07:28:12 LOG: recycled transaction log file 0000000000000030 2003-04-09 07:28:12 LOG: recycled transaction log file 0000000000000031 2003-04-09 07:29:30 LOG: recycled transaction log file 0000000000000035 2003-04-09 07:29:30 LOG: recycled transaction log file 0000000000000033 2003-04-09 07:29:30 LOG: recycled transaction log file 0000000000000034 2003-04-09 07:30:52 LOG: recycled transaction log file 0000000000000038 2003-04-09 07:30:52 LOG: recycled transaction log file 0000000000000036 2003-04-09 07:30:52 LOG: recycled transaction log file 0000000000000037 2003-04-09 07:32:07 LOG: recycled transaction log file 000000000000003B 2003-04-09 07:32:07 LOG: recycled transaction log file 0000000000000039 2003-04-09 07:32:07 LOG: recycled transaction log file 000000000000003A 2003-04-09 07:33:24 LOG: recycled transaction log file 000000000000003E 2003-04-09 07:33:24 LOG: recycled transaction log file 000000000000003C 2003-04-09 07:33:24 LOG: recycled transaction log file 000000000000003D 2003-04-09 07:34:41 LOG: recycled transaction log file 0000000000000041 2003-04-09 07:34:41 LOG: recycled transaction log file 000000000000003F 2003-04-09 07:34:41 LOG: recycled transaction log file 0000000000000040 2003-04-09 07:35:56 LOG: recycled transaction log file 0000000000000044 2003-04-09 07:35:56 LOG: recycled transaction log file 0000000000000042 2003-04-09 07:35:56 LOG: recycled transaction log file 0000000000000043 2003-04-09 07:37:15 LOG: recycled transaction log file 0000000000000047 2003-04-09 07:37:15 LOG: recycled transaction log file 0000000000000045 2003-04-09 07:37:15 LOG: recycled transaction log file 0000000000000046 2003-04-09 07:38:28 LOG: recycled transaction log file 000000000000004A 2003-04-09 07:38:28 LOG: recycled transaction log file 0000000000000048 2003-04-09 07:38:28 LOG: recycled transaction log file 0000000000000049 2003-04-09 07:39:37 LOG: recycled transaction log file 000000000000004D 2003-04-09 07:39:37 LOG: recycled transaction log file 000000000000004B 2003-04-09 07:39:37 LOG: recycled transaction log file 000000000000004C 2003-04-09 07:41:00 LOG: recycled transaction log file 0000000000000050 2003-04-09 07:41:00 LOG: recycled transaction log file 000000000000004E 2003-04-09 07:41:00 LOG: recycled transaction log file 000000000000004F 2003-04-09 07:42:18 LOG: recycled transaction log file 0000000000000053 2003-04-09 07:42:18 LOG: recycled transaction log file 0000000000000051 2003-04-09 07:42:18 LOG: recycled transaction log file 0000000000000052 2003-04-09 07:43:29 LOG: recycled transaction log file 0000000000000056 2003-04-09 07:43:29 LOG: recycled transaction log file 0000000000000054 2003-04-09 07:43:29 LOG: recycled transaction log file 0000000000000055 2003-04-09 07:44:39 LOG: recycled transaction log file 0000000000000059 2003-04-09 07:44:39 LOG: recycled transaction log file 0000000000000057 2003-04-09 07:44:39 LOG: recycled transaction log file 0000000000000058 2003-04-09 07:45:54 LOG: recycled transaction log file 000000000000005C 2003-04-09 07:45:54 LOG: recycled transaction log file 000000000000005A 2003-04-09 07:45:54 LOG: recycled transaction log file 000000000000005B 2003-04-09 07:47:04 LOG: recycled transaction log file 000000000000005F 2003-04-09 07:47:04 LOG: recycled transaction log file 000000000000005D 2003-04-09 07:47:04 LOG: recycled transaction log file 000000000000005E 2003-04-09 07:48:14 LOG: recycled transaction log file 0000000000000062 2003-04-09 07:48:14 LOG: recycled transaction log file 0000000000000060 2003-04-09 07:48:14 LOG: recycled transaction log file 0000000000000061 2003-04-09 07:49:27 LOG: recycled transaction log file 0000000000000065 2003-04-09 07:49:27 LOG: recycled transaction log file 0000000000000063 2003-04-09 07:49:27 LOG: recycled transaction log file 0000000000000064 2003-04-09 07:50:40 LOG: recycled transaction log file 0000000000000068 2003-04-09 07:50:40 LOG: recycled transaction log file 0000000000000066 2003-04-09 07:50:40 LOG: recycled transaction log file 0000000000000067 2003-04-09 07:51:51 LOG: recycled transaction log file 000000000000006B 2003-04-09 07:51:51 LOG: recycled transaction log file 0000000000000069 2003-04-09 07:51:51 LOG: recycled transaction log file 000000000000006A 2003-04-09 07:53:14 LOG: recycled transaction log file 000000000000006E 2003-04-09 07:53:14 LOG: recycled transaction log file 000000000000006C 2003-04-09 07:53:14 LOG: recycled transaction log file 000000000000006D 2003-04-09 07:54:31 LOG: recycled transaction log file 0000000000000071 2003-04-09 07:54:31 LOG: recycled transaction log file 000000000000006F 2003-04-09 07:54:31 LOG: recycled transaction log file 0000000000000070 2003-04-09 07:55:49 LOG: recycled transaction log file 0000000000000074 2003-04-09 07:55:49 LOG: recycled transaction log file 0000000000000072 2003-04-09 07:55:49 LOG: recycled transaction log file 0000000000000073 2003-04-09 07:57:00 LOG: recycled transaction log file 0000000000000077 2003-04-09 07:57:00 LOG: recycled transaction log file 0000000000000075 2003-04-09 07:57:00 LOG: recycled transaction log file 0000000000000076 2003-04-09 07:58:14 LOG: recycled transaction log file 000000000000007A 2003-04-09 07:58:14 LOG: recycled transaction log file 0000000000000078 2003-04-09 07:58:14 LOG: recycled transaction log file 0000000000000079 2003-04-09 07:59:31 LOG: recycled transaction log file 000000000000007D 2003-04-09 07:59:31 LOG: recycled transaction log file 000000000000007B 2003-04-09 07:59:31 LOG: recycled transaction log file 000000000000007C 2003-04-09 08:00:50 LOG: recycled transaction log file 0000000000000080 2003-04-09 08:00:50 LOG: recycled transaction log file 000000000000007E 2003-04-09 08:00:50 LOG: recycled transaction log file 000000000000007F 2003-04-09 08:02:10 LOG: recycled transaction log file 0000000000000083 2003-04-09 08:02:10 LOG: recycled transaction log file 0000000000000081 2003-04-09 08:02:10 LOG: recycled transaction log file 0000000000000082 2003-04-09 08:03:37 LOG: recycled transaction log file 0000000000000086 2003-04-09 08:03:37 LOG: recycled transaction log file 0000000000000084 2003-04-09 08:03:37 LOG: recycled transaction log file 0000000000000085 2003-04-09 08:04:51 LOG: recycled transaction log file 0000000000000089 2003-04-09 08:04:51 LOG: recycled transaction log file 0000000000000087 2003-04-09 08:04:51 LOG: recycled transaction log file 0000000000000088 2003-04-09 08:05:52 LOG: recycled transaction log file 000000000000008C 2003-04-09 08:05:52 LOG: recycled transaction log file 000000000000008A 2003-04-09 08:05:52 LOG: recycled transaction log file 000000000000008B 2003-04-09 08:06:55 LOG: recycled transaction log file 000000000000008F 2003-04-09 08:06:55 LOG: recycled transaction log file 000000000000008D 2003-04-09 08:06:55 LOG: recycled transaction log file 000000000000008E 2003-04-09 08:08:01 LOG: recycled transaction log file 0000000000000092 2003-04-09 08:08:01 LOG: recycled transaction log file 0000000000000090 2003-04-09 08:08:01 LOG: recycled transaction log file 0000000000000091 2003-04-09 08:13:15 LOG: recycled transaction log file 0000000000000095 2003-04-09 08:13:15 LOG: recycled transaction log file 0000000000000093 2003-04-09 08:13:15 LOG: recycled transaction log file 0000000000000094 2003-04-09 08:32:28 LOG: pq_flush: send() failed: Broken pipe 2003-04-09 08:32:28 LOG: pq_recvbuf: unexpected EOF on client connection 2003-04-09 08:33:29 LOG: recycled transaction log file 0000000000000096
Tom Lane wrote: >I believe the proposed patch breaks many nontrivial cases (though it's >quite hard to be sure exactly what it does, given a non-contextual diff >:-(). > Hi Tom, it was a cvs diff, should I upload the whole source? > Have you tried running its output back into the system to see >whether the querytree is reconstructed exactly? Don't forget to try >cases where parentheses were used in the original source to force a >non-default evaluation order. > > I'm quite sure about JOINS, T_BoolExpr and T_OpExpr. I had a lot of view definitions fed in and out, to see if things work as expected. I wasn't able to force a T_CoerceToDomain, and only got T_RelabelType with simple variables. If you can tell me how to force complex args for these cases, I'll check this. As far as I understand, both types always have only one arg. If this is wrong, there definitely has to be an additional check for a simple variable arg. Regards, Andreas