Re: [GENERAL] Curosity question regarding LOCK NOWAIT
Hi, 2012-09-22 04:43 keltezéssel, David Johnston írta: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: WAITONLY 50 (milliseconds), when dealing the explicit LOCK TABLE or the SELECT...FOR(SHARE|UPDATE) commands? David J. we have a proposed patch for 9.3 at http://archives.postgresql.org/message-id/50339b3d.8040...@cybertec.at that adds SET lock_timeout = N; to have a time-limited wait for locks. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
Re: [GENERAL] Curosity question regarding LOCK NOWAIT
2012-09-22 07:55 keltezéssel, Josh Kupershmidt írta: On Fri, Sep 21, 2012 at 7:43 PM, David Johnston pol...@yahoo.com wrote: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the SELECT…FOR(SHARE|UPDATE) commands? I think you could do this by issuing SET statement_timeout = 50; and then attempting the LOCK TABLE or SELECT ... FOR UPDATE command, without NOWAIT. You'll either get an ERROR: canceling statement due to statement timeout or the command should succeed. This is not the same as the handling of a timeout waiting for a lock. The statement_timeout may also trigger when returning large result sets without locking, i.e. in a plain SELECT case. Best regards,, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curosity question regarding LOCK NOWAIT
2012-09-22 06:08 keltezéssel, John R Pierce írta:
On 09/21/12 7:43 PM, David Johnston wrote:
Has there been any discussion regarding adding a time-limited version of NOWAIT, say:
“WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the
SELECT…FOR(SHARE|UPDATE) commands?
is this a feature in any other major databases?
Yes, at least Informix has it in the form of SET LOCK MODE TO { NOT WAIT | WAIT
[N] }.
is this in the sql spec?
No.
what do you expect to happen if these timeouts expire? return an error, and abort the
transaction?
--
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 vs 8.4 performance
On 09/21/2012 10:32 PM, salah jubeh wrote: I am running queries sequentially on each machine using a database dumped from a life server , and 9.1 server is much slower than 8.4. https://wiki.postgresql.org/wiki/Slow_Query_Questions More detail needed. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curosity question regarding LOCK NOWAIT
On 22 September 2012 05:08, John R Pierce pie...@hogranch.com wrote: On 09/21/12 7:43 PM, David Johnston wrote: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the SELECT…FOR(SHARE|UPDATE) commands? is this a feature in any other major databases? There may be other/better ways to do this, I'm only a casual user of at least one of these, but here is what I could find for the big three: Oracle: Error if locks can't be obtained immediately: SELECT ... FOR UPDATE NOWAIT Error if locks can't be obtained in time: SELECT ... FOR UPDATE WAIT seconds Skip rows that can't be locked immediately: SELECT ... FOR UPDATE SKIP LOCKED Microsoft SQL Server: Error if locks can't be obtained immediately: SELECT ... FOR UPDATE WITH (NOWAIT) Error if locks can't be obtained in time: SET LOCK_TIMEOUT milliseconds SELECT ... FOR UPDATE Skip rows that can't be locked immediately: SELECT ... FOR UPDATE WITH (READPAST) IBM DB2 Error if locks can't be obtained immediately: SET CURRENT LOCK TIMEOUT NOWAIT SELECT ... FOR UPDATE Error if locks can't be obtained in time: SET CURRENT LOCK TIMEOUT WAIT seconds SELECT ... FOR UPDATE Skip rows that can't be locked immediately: -- currently z/OS version only SELECT ... FOR UPDATE SKIP LOCKED ROWS is this in the sql spec? My understanding is that the SQL 1992 spec doesn't talk about locking directly, it talks about isolation levels and updatability (and I don't have accesss to the 2003 spec to check if that has changed). Although it does standardise FOR UPDATE (updatability clause which is an optional part of a cursor specification), it's not for explicit locking, it specifies that a cursor is updatable. Locking is an implementation matter (and the use of FOR UPDATE outside of a cursor specification, ie in a query specification, may be non-standard anyway). NOWAIT is not an ANSI SQL keyword, and WAIT is a keyword reserved for future use. Regards, Thomas Munro -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2
Is there link to download pldbgapi.dll on postgresql 9.2 x64 on windows Thanks in advance Tuan Hoang Anh On Fri, Sep 14, 2012 at 9:29 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Fabrízio de Royes Mello wrote: Has it moved somewhere else? Look at the pldebugger git repository in [1]. Thanks! Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about permissions on database.
Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name I simple do: GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; and when I do that my idea and what Im trying to do is to give all privileges on for select, insert, update ... using sequences, exec functions to one user, but when I try to select, I receive error message: ERROR: permission denied for relation table_name I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. Im using postgresql 9.2 C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name I simple do: GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; and when I do that my idea and what Im trying to do is to give all privileges on for select, insert, update ... using sequences, exec functions to one user, but when I try to select, I receive error message: ERROR: permission denied for relation table_name I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. Im using postgresql 9.2 You don't want to GRANT on the database. That doesn't do what you think it does. You, however, can do: GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; This is documented clearly here: http://www.postgresql.org/docs/9.2/static/sql-grant.html This is generally a bad idea. You can alternatively make the user a super user: ALTER ROLE your_user WITH SUPERUSER; But this is an even worse idea. If one role owns all the tables in that database, you can make your role a member of that role: GRANT owner_role TO your_role; But are you really sure that your user needs permissions on everything? -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sep 22, 2012, at 16:35, Condor con...@stz-bg.com wrote: Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name I simple do: GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; and when I do that my idea and what Im trying to do is to give all privileges on for select, insert, update ... using sequences, exec functions to one user, but when I try to select, I receive error message: ERROR: permission denied for relation table_name I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. Im using postgresql 9.2 Each object type needs to be handled independently. A database is a distinct object type with its own actions to allow/disallow (mainly connect, usage?). You need to issue similar commands for schemas, tables, functions, etc... Would suggest creating group (i.e., non-login) roles and grant to those then make the user (i.e., login) roles a member of the appropriate group role(s). David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sun, Sep 23, 2012 at 6:47 AM, Ryan Kelly rpkell...@gmail.com wrote: On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name You don't want to GRANT on the database. That doesn't do what you think it does. You, however, can do: GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; This is generally a bad idea. You can alternatively make the user a super user: ALTER ROLE your_user WITH SUPERUSER; But this is an even worse idea. I have a similar situation; I want to have a userid for doing the regular backups, which therefore (for pg_dump) needs SELECT privilege on all tables. Is: GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_userid; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_userid; the right way to do this? (Obviously I need to also do this for every other schema I use.) Alternatively, is there some better way to do backups? I'd rather not snapshot the entire data directory; we burn to disc, so a larger backup requires more media and slower backup/restore process. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On 09/23/2012 04:35 AM, Condor wrote: I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. You probably need to read this: http://stackoverflow.com/questions/11599533/postgresql-8-4-grant-dml-privileges-on-all-tables-to-a-role That while that question is about 8.4 so it doesn't cover ALTER DEFAULT PRIVILEGES (http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html), which is the right way to to solve this going forward. It should be useful, though. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] On Ubuntu 12.04 i do have two psql one of those isn't working
On 2012-09-18, Raymond O'Donnell r...@iol.ie wrote: On 18/09/2012 16:10, Yvon Thoraval wrote: I've found the prob. In my postgresql.conf file the default port is setup to 5433 instead of 5432 as previously... OK - you probably had two versions of PG installed at some point - when you install a second version of PG alongside an existing one on Debian Ubuntu, it automatically puts it on 5433. Debian's pg_upgradecluster will leave you with the new cluster running on 5433. (it's not pg_upgrade, it's some sort of script) ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RFE: Column aliases in WHERE clauses
On 2012-09-18, Rafal Pietrak ra...@zorro.isa-geek.com wrote: Actual Tom's example(1): SELECT 1/x AS inverse FROM data WHERE x 0; extended to (2): SELECT 1/x AS inverse FROM data WHERE x 0 AND 1/x 20; could be written by user as (3): SELECT 1/x AS inverse FROM data WHERE x 0 AND inverse 20; but token/replaced to its form (2) before WHERE evaluation. Macros are confusing: select random()*10 as confusion from generate_series(1,10) where confusion 5; -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump slow on windows
On 2012-09-06, Kobus Wolvaardt kobusw...@gmail.com wrote: Something that is curios is that if a DB takes long, it really takes horribly long like some kind of a lock is holding it. It would sit at a few kb dump size for 20 minutes en then run a bit and get stuck again (as far as we can tell), what we do know is that it is way to slow for some IO or cpu starvation. We have seen a 5GB backup sitting at 1Gb after 12hours and then we stop it. Any siggestions? Can autovacume or lack thereof cause this? It seems noone has been doing any maintenance on the DB (it does look like autovacuum is running), so any suggestions would be nice. perhaps some sort of DML could cause this, I've seen DML hang during dumps turn on logging of slow queries and check the logs after the next speed disaster. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RFE: Column aliases in WHERE clauses
On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts ja...@xnet.co.nz wrote: On 2012-09-18, Rafal Pietrak ra...@zorro.isa-geek.com wrote: Actual Tom's example(1): SELECT 1/x AS inverse FROM data WHERE x 0; extended to (2): SELECT 1/x AS inverse FROM data WHERE x 0 AND 1/x 20; could be written by user as (3): SELECT 1/x AS inverse FROM data WHERE x 0 AND inverse 20; but token/replaced to its form (2) before WHERE evaluation. Macros are confusing: select random()*10 as confusion from generate_series(1,10) where confusion 5; Also you can already do this: CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE AS $$ select case when $1.x = 0 then null else 1/$1.x end; $$; Then it can be used as a macro: SELECT d.inverse FROM data d WHERE d.x 0 AND d.inverse 0.5; Wondering if we want to support something like this, essentially anonymous functions, if we shouldn't extend the WITH clause to support something like WITH FUNCTION for cases where you don't want your macro to persist. I don't know though. Are there cases where you don't want the macro to persist? Best Wishes, Chris Travers
