[BUGS] BUG #6612: Functions can be called inside CHECK statements
The following bug has been logged on the website: Bug reference: 6612 Logged by: Ari Entlich Email address: atrig...@ccs.neu.edu PostgreSQL version: 9.1.2 Operating system: Windows? Description: Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it doesn't make any sense to me for it to allow function calls, since functions can perform queries. Additionally, if a function is called from a check constraint and that function executes a query, the change that caused the check constraint to fire does not appear to be "visible" to the query. Therefore, calling functions in check constraints does not have the ability to check whether that change is valid, making it mostly useless. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Broken Pipe Error
I have 2 databases for single user . Both these databases connect to 2 different applications. Configuration for one database is set to maximum 30 connections. Other database is not configured for maximum allowed connections explicitly. Now, I receive a broken pipe error for the application with database without configuration for maximum connections. Please can someone help me?.. The version used for postgres is pgAdmin III 1.8
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 10:33:10AM +, atrig...@ccs.neu.edu wrote: > Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it > doesn't make any sense to me for it to allow function calls, since functions > can perform queries. Additionally, if a function is called from a check > constraint and that function executes a query, the change that caused the > check constraint to fire does not appear to be "visible" to the query. > Therefore, calling functions in check constraints does not have the ability > to check whether that change is valid, making it mostly useless. I fail to see how's that a bug. Using functions in check has uses, and the fact that you don't suit your particular case (or you don't know how to make them suit your case) is not a bug in Pg. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- 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 #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 11:33 AM, wrote: > Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it > doesn't make any sense to me for it to allow function calls, since functions > can perform queries. This is why functions must be marked as one of VOLATILE, STABLE, or IMMUTABLE. Only IMMUTABLE functions can be used in CHECK constraints. It's a feature that expressions including subqueries are automatically detected as not being immutable and automatically barred. Functions do not have this feature and must be manually marked by the user with the correct state. This is a useful escape hatch in cases where an expression can not be proven to be immutable but the user knows that due to the design of his or her application it is in fact immutable -- for instance queries that query from tables that the user is certain will never be modified. The database cannot detect every possible erroneous usage, at least not without being less useful. It's a balancing act of providing the user with as many safety nets as possible without imposing too many restrictions. Too many safety nets and you can't do some things, too few and you spend too much time checking for or debugging problems. -- greg -- 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 #6612: Functions can be called inside CHECK statements
Greg Stark wrote: > Only IMMUTABLE functions can be used in CHECK constraints. > It's a feature that expressions including subqueries are > automatically detected as not being immutable and automatically > barred. It doesn't look like that to me: test=# create function xxx() returns text volatile language plpgsql as $$ begin return 'xxx'; end; $$; CREATE FUNCTION test=# create table x (id int not null primary key, val text check (val <> xxx())); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x" CREATE TABLE test=# insert into x values (1, 'aaa'); INSERT 0 1 test=# insert into x values (2, 'xxx'); ERROR: new row for relation "x" violates check constraint "x_val_check" DETAIL: Failing row contains (2, xxx). Perhaps you're thinking of function usage in index definitions? A CHECK constraint using a volatile function is potentially valid and useful, IMO. Think about a column which is supposed to record the moment of an event which has occurred. It could make sense to ensure that the timestamptz value is < now(); On the other hand, an index entry based on now() is clearly a problem. Otherwise I agree with your response -- this is clearly *not* a bug. -Kevin -- 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] Broken Pipe Error
snehal maniyar wrote: > I have 2 databases for single user . > Both these databases connect to 2 different applications. > > Configuration for one database is set to maximum 30 connections. > Other database is not configured for maximum allowed connections > explicitly. > > Now, I receive a broken pipe error for the application with > database without configuration for maximum connections. > > Please can someone help me?.. > The version used for postgres is pgAdmin III 1.8 You have neither provided any evidence of a bug, nor enough detail to allow anyone to help much. Please read this page and start a new thread on pgsql-general with your request for assistance in figuring out the problem: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- 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 #6612: Functions can be called inside CHECK statements
"Kevin Grittner" writes: > Greg Stark wrote: >> Only IMMUTABLE functions can be used in CHECK constraints. > It doesn't look like that to me: No, we have never enforced that. IIRC the idea has been discussed, but we thought that adding the restriction would break too many existing applications. > A CHECK constraint using a volatile function is potentially valid > and useful, IMO. Think about a column which is supposed to record > the moment of an event which has occurred. It could make sense to > ensure that the timestamptz value is < now(); On the other hand, an > index entry based on now() is clearly a problem. This example is actually stable not volatile, but if for some reason you wanted to use clock_timestamp() then it would be volatile. Probably a more interesting question is whether it'd ever be sane to use a function with side-effects in a check constraint. I find it hard to visualize a case where it wouldn't be saner to put the actions in a trigger, but that doesn't mean someone else might not wish to do it. In practice, the times when check constraints are checked are predictable enough that you should be able to get away with abusing the system like that, if you wished. 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
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner wrote: > A CHECK constraint using a volatile function is potentially valid > and useful, IMO. Think about a column which is supposed to record > the moment of an event which has occurred. It could make sense to > ensure that the timestamptz value is < now(); On the other hand, an > index entry based on now() is clearly a problem. > > Otherwise I agree with your response -- this is clearly *not* a bug. Hm. I suppose it depends on what you think a constraint is. I had always thought it was a guarantee that all the data in the table would meet that constraint. Not just a procedural definition for something to do at certain points in time. But I guess I responded based on my understanding without checking whether it was right. sorry. Hm, but this does raise the question of whether they're the right thing to be basing the partitioning constraint exclusion code on. I'll speculate without checking again that we check the immutability of the constraint before using it in constraint exclusion but that seems a ad-hoc. -- greg -- 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 #6612: Functions can be called inside CHECK statements
Greg Stark writes: > On Wed, Apr 25, 2012 at 4:06 PM, Kevin Grittner > wrote: >> A CHECK constraint using a volatile function is potentially valid >> and useful, IMO. > Hm. I suppose it depends on what you think a constraint is. I had > always thought it was a guarantee that all the data in the table would > meet that constraint. Not just a procedural definition for something > to do at certain points in time. Well, it's a guarantee that the expression evaluated to "true" at the time of insertion or update of every row. If you confine your attention to immutable expressions then you can presume that the expression is true for every row in the table at any time; but I'm with Kevin that there are reasonable use-cases that don't fit into that. > Hm, but this does raise the question of whether they're the right > thing to be basing the partitioning constraint exclusion code on. As long as we only consider expressions that are immutable as usable partition constraints, that's not a problem. > I'll > speculate without checking again that we check the immutability of the > constraint before using it in constraint exclusion but that seems a > ad-hoc. We do, and I don't see why that's ad-hoc. In general the planner has to check the volatility status of any expression it's going to try to reason about. 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
Re: [BUGS] BUG #6602: concurrent psql session clobbers history
Peter Eisentraut writes: > On tor, 2012-04-19 at 15:00 -0400, Tom Lane wrote: >> i+pgb...@avdd.tk writes: >>> A concurrent psql session will overwrite the history of the first. This >>> does not look good: a database tool causing me to lose my data! >> Works okay for me. I suspect you need to take this up with whoever >> packages libreadline for Ubuntu, because it's going to be libreadline's >> problem not ours. > It's probably actually using libedit for the history part. [ after further research... ] Mmm, maybe. I can reproduce the misbehavior on a Mac build using libedit. There is a relevant-looking comment in psql's saveHistory function: * On newer versions of libreadline, truncate the history file as * needed and then append what we've added. This avoids overwriting * history from other concurrent sessions (although there are still * race conditions when two sessions exit at about the same time). If * we don't have those functions, fall back to write_history(). A check of the configure output shows that Apple's libedit lacks append_history(), so that the fallback code path is used. The phrasing of this comment suggests that older versions of libreadline might be lacking as well. There's not a lot we can do about this issue when using a libreadline or libedit that lacks the necessary support functions, I think. 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
Re: [BUGS] BUG #6605: wrong type cast from timestamp to timestamptz
eshkin...@gmail.com writes: > set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz; > SET > timestamptz > > 2011-03-28 02:59:54+04 I've applied a patch for this. Thanks for the report! 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
Re: [BUGS] BUG #6608: SELECT FOR UPDATE not obtaining row exclusive locks in CTEs
duncan.bu...@orionvm.com.au writes: > I found that running a SELECT FOR UPDATE query in a CTE does not block > simultaneous transactions from running the same query. The reason this test case doesn't do anything: > CREATE FUNCTION lock_0(int) returns int as $$ > WITH locked as ( > SELECT 1 FROM foo > WHERE x = $1 > FOR UPDATE) > SELECT 1 > $$ LANGUAGE SQL; is that the CTE is unreferenced. While we force INSERT/UPDATE/DELETE CTEs to be executed even when not referenced, that does not apply to SELECTs; see http://www.postgresql.org/docs/9.1/static/queries-with.html which states "execution of a SELECT is carried only as far as the primary query demands its output". If I change the function to say "WITH ... SELECT * FROM locked" then blocking occurs as expected. 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
Re: [BUGS] BUG #6612: Functions can be called inside CHECK statements
Wow, so I guess I'm pretty much wrong about this... Sorry for the noise guys. I failed to consider different uses for functions, obviously, and it's not possible to automatically detect usages which could cause problems. Perhaps this is more of a bug in the documentation than anything else. I couldn't find any documentation for the behavior for what will happen if a query is executed inside a function which is called from a check statement. I also couldn't find anything about calling function inside check statements in general, but perhaps that's not necessary. I do, however, think it should definitely be documented somewhere that functions cannot be used to get around the lack of check statement subqueries, because the ordering of operations is wrong. Thanks! Ari -- 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 #6613: tablespace data ignored after reinstalling windows
The following bug has been logged on the website: Bug reference: 6613 Logged by: Camil Ghircoias Email address: ghircoiasca...@yahoo.com PostgreSQL version: 9.1.3 Operating system: Windows 7 Description: Had this scenario: OS:Windows 7 Install postgresql 9.1 in Program Files Postgresql data directory was set in d:\mydata\postgresql. Created table space called data in d:\mydata\data. Had 2 databases created in the data tablespace. Reinstalled windows (formatted disk c). Reinstalled postgresql (the same kit) with the data directory set to the old one d:\mydata\postgresql Open pgadmin, connected succesfully to the server, the 2 databases were there but when trying to connect get a message that the folder \pg_tblspc\an oid\pg version..\the database oid was not found. Succeded to make it to work by copying the oid databases folder from d:\mydata\data into d:\mydata\postgresql Even pgadmin shows the tablespace data in d:\mydata\data and the 2 databases had the tablespace data not pg_default postgresql it uses the pg_default tablespace for storing the data, the d:\mydata\data folder I could delete it's files without any problems and postgresql worked. -- 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 #6612: Functions can be called inside CHECK statements
Ari Entlich writes: > I do, however, think it should definitely be documented somewhere that > functions cannot be used to get around the lack of check statement > subqueries, because the ordering of operations is wrong. That statement seems to me to be complete nonsense. You can certainly put a query into a function invoked by CHECK. It may be that there's some particular use-case that this doesn't work for, but that does not justify a blanket statement that it "doesn't work". 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 #6614: pg_dump not working for tables with japanese name
The following bug has been logged on the website: Bug reference: 6614 Logged by: Piyush Merja Email address: piyu...@usindia.com PostgreSQL version: 9.0.0 Operating system: WindowsXp Description: Hello, I have some table that has name in japanese character when I try to take backup from pgAdmin or command prompt its not working. Thanks -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
