Re: [BUGS] BUG #8198: ROW() literals not supported in an IN clause
On Wednesday, June 05, 2013 5:34 AM Rafał Rzepecki wrote:
On Tue, Jun 4, 2013 at 12:35 PM, Amit Kapila amit.kap...@huawei.com
wrote:
On Saturday, June 01, 2013 9:37 PM
Row type literals constructed with ROW() cause an error when used in
an IN clause (string literals casted appropriately are allowed).
This
is especially problematic since many client libraries use these
literals to pass values of row-type arguments, hence making it
impossible to use them in IN-clause queries.
If I'm right, the proper fix would be (patch 0001; caution, not
tested):
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1203,10 +1203,9 @@ transformAExprIn(ParseState *pstate, A_Expr *a)
Node *rexpr = (Node *) lfirst(l);
Node *cmp;
- if (haveRowExpr)
+ if (haveRowExpr IsA(lexpr, RowExpr))
{
- if (!IsA(lexpr, RowExpr) ||
- !IsA(rexpr, RowExpr))
+ if (!IsA(rexpr, RowExpr))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg(arguments of row IN must all
be row expressions),
Since the restriction seems a rather arbitrary (at least I fail to see
any reason for it), it can be removed altogether (patch 0002, not
tested as well):
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1203,20 +1203,12 @@ transformAExprIn(ParseState *pstate, A_Expr *a)
Node *rexpr = (Node *) lfirst(l);
Node *cmp;
- if (haveRowExpr)
- {
- if (!IsA(lexpr, RowExpr) ||
- !IsA(rexpr, RowExpr))
- ereport(ERROR,
-
(errcode(ERRCODE_SYNTAX_ERROR),
- errmsg(arguments of row IN must
all be row expressions),
-
parser_errposition(pstate, a-location)));
+ if (IsA(lexpr, RowExpr) IsA(rexpr, RowExpr))
cmp = make_row_comparison_op(pstate,
a-name,
(List *)
copyObject(((RowExpr *) lexpr)-args),
((RowExpr *) rexpr)-args,
a-location);
- }
else
cmp = (Node *) make_op(pstate,
a-
name,
I had tried, both your patches have passed all regression tests (tested on
Windows). I feel fixing it in a way similar to your Patch-1 would be
more appropriate as with Patch-1 it can generate meaningful error message for
some cases like below:
postgres=# select * from the_table where ROW('abc','def') in
(row('foo','bar')::the_row,12);
ERROR: arguments of row IN must all be row expressions
LINE 1: select * from the_table where ROW('abc','def') in (row('foo'...
With Regards,
Amit Kapila.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8210: UTF8 column names corrupted by server
The following bug has been logged on the website: Bug reference: 8210 Logged by: Martin Schaefer Email address: martin.schae...@cadcorp.com PostgreSQL version: 9.2.1 Operating system: Windows 8 Description: The following code: const wchar_t *strName = Lid_äß; wstring strCreate = wstring(Lcreate table test_umlaut() + strName + L integer primary key); PGconn *pConn = PQsetdbLogin(, , NULL, NULL, dev503, postgres, **); if (!pConn) FAIL; if (PQsetClientEncoding(pConn, UTF-8)) FAIL; PGresult *pResult = PQexec(pConn, drop table test_umlaut); if (pResult) PQclear(pResult); pResult = PQexec(pConn, ToUtf8(strCreate.c_str()).c_str()); if (pResult) PQclear(pResult); pResult = PQexec(pConn, select * from test_umlaut); if (!pResult) FAIL; if (PQresultStatus(pResult)!=PGRES_TUPLES_OK) FAIL; if (PQnfields(pResult)!=1) FAIL; const char *fName = PQfname(pResult,0); ShowW(Name: , strName); ShowA(in UTF8: , ToUtf8(strName).c_str()); ShowA(from DB: , fName); ShowW(in UTF16: , ToWide(fName).c_str()); PQclear(pResult); PQreset(pConn); (ShowA/W call OutputDebugStringA/W, and ToUtf8/ToWide use WideCharToMultiByte/MultiByteToWideChar with CP_UTF8.) generates this output: Name: id_äß in UTF8: id_äß from DB: id_ã¤ãÿ in UTF16: id_??? The back-end treats the name as if it were in ANSI encoding, not in UTF-8, when it lower-cases the name. The resulting column name is corrupted. I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8211: Syntax error when creating index on expression
The following bug has been logged on the website: Bug reference: 8211 Logged by: Andrey Cizov Email address: aci...@gmail.com PostgreSQL version: 9.2.3 Operating system: Windows Description: CREATE INDEX heuristic ON foos (1 / (a + b)) causes: ERROR: syntax error at or near 1 LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b)) ^ ** Error ** ERROR: syntax error at or near 1 SQL state: 42601 Character: 33 While: CREATE INDEX heuristic ON foos ((1 / (a + b))) Execution successful -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8211: Syntax error when creating index on expression
On Wed, Jun 5, 2013 at 8:24 AM, aci...@gmail.com wrote: Bug reference: 8211 Logged by: Andrey Cizov Email address: aci...@gmail.com PostgreSQL version: 9.2.3 Operating system: Windows Description: CREATE INDEX heuristic ON foos (1 / (a + b)) causes: ERROR: syntax error at or near 1 LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b)) ^ ** Error ** ERROR: syntax error at or near 1 SQL state: 42601 Character: 33 While: CREATE INDEX heuristic ON foos ((1 / (a + b))) Execution successful Did you look at the docs? http://www.postgresql.org/docs/current/static/sql-createindex.html expression An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.
Re: [BUGS] BUG #8211: Syntax error when creating index on expression
aci...@gmail.com writes: CREATE INDEX heuristic ON foos (1 / (a + b)) causes: ERROR: syntax error at or near 1 This is not a bug. You need an extra pair of parentheses around the expression, ie CREATE INDEX heuristic ON foos ((1 / (a + b))) http://www.postgresql.org/docs/9.2/static/sql-createindex.html points this out both in the syntax diagram and the text. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8212: Feature request: View original value of current_user in function with SECURITY DEFINER set
The following bug has been logged on the website: Bug reference: 8212 Logged by: Geoff Montee Email address: geoff.mon...@gmail.com PostgreSQL version: 9.2.4 Operating system: Linux Description: When a user calls a function that has SECURITY DEFINER set, the value of current_user is changed to the name of the role that defined the function. For some use cases, it would be useful to be able to obtain the original value of current_user from within the function. Specifically, this would make sense in trigger functions used for auditing, where recording only session_user may not be sufficient. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8211: Syntax error when creating index on expression
bricklen wrote expression An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call. So in fact the example provided: CREATE INDEX ON films ((lower(title))); could be written as: CREATE INDEX ON films (lower(title)); The example expression has yet one additional pair of () that are not required per the syntax since lower(...) is a function call. Extra () never hurt I suppose... I don't see this come up too often on the list but I will agree that it is unexpected to require the extra set of (). An example using an actual expression with the extra () and then the function call example without - to explicitly show when/why they can be omitted in the examples as well as in the text - is an idea worth considering. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8211-Syntax-error-when-creating-index-on-expression-tp5758030p5758040.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8213: Set-valued function error in union
The following bug has been logged on the website: Bug reference: 8213 Logged by: Eric Soroos Email address: eric-postgre...@soroos.net PostgreSQL version: 9.0.13 Operating system: Ubuntu 10.04, 32bit Description: This has been replicated on 9.2.4 and HEAD by ilmari_ and johto. erics@dev:~/trunk/sql$ psql -a -h 192.168.10.249 -f pg_bug_report.sql \set VERBOSITY verbose \set echo all select version(); version PostgreSQL 9.0.13 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit (1 row) -- this fails. I'd expect it to succeed. select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date ) as foo where dt now()+'15 days'::interval; psql:pg_bug_report.sql:13: ERROR: 0A000: set-valued function called in context that cannot accept a set LOCATION: ExecMakeFunctionResult, execQual.c:1733 -- this succeeds, but returns a timestamp select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day') as dt union select 2, now()::date ) as foo where dt now()+'15 days'::interval; id | dt +- 1 | 2013-06-05 00:00:00 1 | 2013-06-06 00:00:00 1 | 2013-06-07 00:00:00 1 | 2013-06-08 00:00:00 1 | 2013-06-09 00:00:00 1 | 2013-06-10 00:00:00 1 | 2013-06-11 00:00:00 1 | 2013-06-12 00:00:00 1 | 2013-06-13 00:00:00 1 | 2013-06-14 00:00:00 1 | 2013-06-15 00:00:00 1 | 2013-06-16 00:00:00 1 | 2013-06-17 00:00:00 1 | 2013-06-18 00:00:00 1 | 2013-06-19 00:00:00 1 | 2013-06-20 00:00:00 2 | 2013-06-05 00:00:00 (17 rows) --this also succeeds, without the where clause select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date ) as foo; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 1 | 2013-06-21 1 | 2013-06-22 1 | 2013-06-23 1 | 2013-06-24 1 | 2013-06-25 1 | 2013-06-26 1 | 2013-06-27 1 | 2013-06-28 1 | 2013-06-29 1 | 2013-06-30 1 | 2013-07-01 1 | 2013-07-02 1 | 2013-07-03 1 | 2013-07-04 1 | 2013-07-05 2 | 2013-06-05 (32 rows) --this also succeeds, without the union select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt ) as foo where dt now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 (16 rows) -- this is the workaround. select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union all select 2, now()::date ) as foo where dt now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 2 | 2013-06-05 (17 rows) -- this is another workaround: begin; BEGIN create temp view gs as select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day') as dt; CREATE VIEW create temp view container as select id, dt::date from gs union select 2, now()::date; CREATE VIEW select * from container where dt now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 | 2013-06-14 1 | 2013-06-15 1 | 2013-06-16 1 | 2013-06-17 1 | 2013-06-18 1 | 2013-06-19 1 | 2013-06-20 2 | 2013-06-05 (17 rows) rollback; ROLLBACK -- another workaround select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date offset 0 ) as foo where dt now()+'15 days'::interval; id | dt + 1 | 2013-06-05 1 | 2013-06-06 1 | 2013-06-07 1 | 2013-06-08 1 | 2013-06-09 1 | 2013-06-10 1 | 2013-06-11 1 | 2013-06-12 1 | 2013-06-13 1 |
Re: [BUGS] BUG #8213: Set-valued function error in union
eric-postgre...@soroos.net writes: -- this fails. I'd expect it to succeed. select id, dt from (select 1 as id, generate_series(now()::date, now()::date + '1 month'::interval, '1 day')::date as dt union select 2, now()::date ) as foo where dt now()+'15 days'::interval; psql:pg_bug_report.sql:13: ERROR: 0A000: set-valued function called in context that cannot accept a set Fascinating. This has been broken at least since 7.4 --- surprising nobody noticed before. We need to fix allpaths.c so it realizes it's unsafe to push down a WHERE condition into a set operation when there are set-returning functions in the tlist of any arm of the set operation. Right now, you're getting this plan: HashAggregate (cost=20.09..30.10 rows=1001 width=0) - Append (cost=0.03..15.09 rows=1001 width=0) - Result (cost=0.03..5.05 rows=1000 width=0) One-Time Filter: ((generate_series(((now())::date)::timestamp without time zone, ((now())::date + '1 mon'::interval), '1 day'::interval))::date (now() + '15 days'::interval)) - Result (cost=0.01..0.03 rows=1 width=0) One-Time Filter: ((now())::date (now() + '15 days'::interval)) and of course trying to evaluate a filter that contains a SRF is pretty nonsensical (or even if you think it could be well-defined, it's not implemented). Shouldn't be too hard to fix though. I'm thinking of moving most of the detection logic for this into subquery_is_pushdown_safe, and having it return an additional flag array that says this output column is unsafe to reference in quals at all. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #8214: SIGSEGV in PyEval_EvalFrameEx
The following bug has been logged on the website: Bug reference: 8214 Logged by: Miron Cuperman Email address: m+p...@tradehill.com PostgreSQL version: 9.2.4 Operating system: Debian 6.0 (squeeze) Description: This is an intermittent crash during a ~5 minute unit test run. My intuition is that raising an uncaught exception in python tends to trigger this, but I could be wrong. Packages: ii pgdg-keyring2012.1 keyring for apt.postgresql.org ii postgresql-9.2 9.2.4-1.pgdg60+1 object-relational SQL database, version 9.2 server ii postgresql-9.2-dbg 9.2.4-1.pgdg60+1 debug symbols for postgresql-9.2 ii postgresql-client-9.2 9.2.4-1.pgdg60+1 front-end programs for PostgreSQL 9.2 ii postgresql-client-common141.pgdg60+1 manager for multiple PostgreSQL client versions ii postgresql-common 141.pgdg60+1 PostgreSQL database-cluster manager ii postgresql-contrib 9.2+141.pgdg60+1 additional facilities for PostgreSQL (supported version) ii postgresql-contrib-9.2 9.2.4-1.pgdg60+1 additional facilities for PostgreSQL ii postgresql-plpython3-9.29.2.4-1.pgdg60+1 PL/Python 3 procedural language for PostgreSQL 9.2 Stack trace from core: #0 0x7f012b5b1997 in ?? () from /usr/lib/libpython3.1.so.1.0 #1 0x7f012b615045 in PyEval_EvalFrameEx () from /usr/lib/libpython3.1.so.1.0 #2 0x7f012b5a6068 in ?? () from /usr/lib/libpython3.1.so.1.0 #3 0x7f012b5824ab in PyIter_Next () from /usr/lib/libpython3.1.so.1.0 #4 0x7f012ba53ab7 in PLy_exec_function (fcinfo=optimized out, proc=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build-py3/../src/pl/plpython/plpy_exec.c:108 #5 0x7f012ba544c4 in plpython3_call_handler (fcinfo=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build-py3/../src/pl/plpython/plpy_main.c:236 #6 0x7f01389e98b7 in ExecMakeFunctionResult (fcache=0x7f013ac5f9a0, econtext=optimized out, isNull=0x7f013ac603f8 p\345K:\001\177, isDone=0x7f013ac60510) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execQual.c:1794 #7 0x7f01389e4c6e in ExecTargetList (isDone=optimized out, itemIsDone=optimized out, isnull=optimized out, values=optimized out, econtext=optimized out, targetlist=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execQual.c:5221 #8 ExecProject (projInfo=optimized out, isDone=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execQual.c:5436 #9 0x7f01389fce7b in ExecResult (node=0x7f013ac5f6a0) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/nodeResult.c:155 #10 0x7f01389e4218 in ExecProcNode (node=0x7f013ac5f6a0) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execProcnode.c:372 #11 0x7f01389e300a in ExecutePlan (dest=optimized out, direction=optimized out, numberTuples=optimized out, sendTuples=optimized out, operation=optimized out, planstate=optimized out, estate=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execMain.c:1395 #12 standard_ExecutorRun (queryDesc=0x7f013acc5660, direction=731641472, count=0) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execMain.c:303 #13 0x7f0138ac2837 in PortalRunSelect (portal=0x7f013a4f5920, forward=optimized out, count=0, dest=0x7f013a581a78) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/pquery.c:944 #14 0x7f0138ac3c80 in PortalRun (portal=optimized out, count=optimized out, isTopLevel=optimized out, dest=optimized out, altdest=optimized out, completionTag=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/pquery.c:788 #15 0x7f0138abfe0d in exec_simple_query (query_string=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/postgres.c:1046 ---Type return to continue, or q return to quit--- #16 0x7f0138ac0e00 in PostgresMain (argc=optimized out, argv=optimized out, dbname=0x7f013a4bf6a8 app_test, username=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/postgres.c:3959 #17 0x7f0138a79cf3 in BackendRun (port=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:3614 #18 BackendStartup (port=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:3304 #19 ServerLoop () at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:1367 #20 0x7f0138a7cc8c in PostmasterMain (argc=optimized out, argv=0x7f013a4be190) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:1127 #21 0x7f0138a1838b in main
