[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, 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 | 2013
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=, proc=) 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=) 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=, 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=, itemIsDone=, isnull=, values=, econtext=, targetlist=) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/executor/execQual.c:5221 #8 ExecProject (projInfo=, isDone=) 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=, direction=, numberTuples=, sendTuples=, operation=, planstate=, estate=) 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=, 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=, count=, isTopLevel=, dest=, altdest=, completionTag=) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/pquery.c:788 #15 0x7f0138abfe0d in exec_simple_query (query_string=) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/postgres.c:1046 ---Type to continue, or q to quit--- #16 0x7f0138ac0e00 in PostgresMain (argc=, argv=, dbname=0x7f013a4bf6a8 "app_test", username=) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/tcop/postgres.c:3959 #17 0x7f0138a79cf3 in BackendRun (port=) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:3614 #18 BackendStartup (port=) 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=, argv=0x7f013a4be190) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/postmaster/postmaster.c:1127 #21 0x7f0138a1838b in main (argc=5, argv=0x7f013a4be170) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/main/main.c:199 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
