Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
Craig Ringer cr...@postnewspapers.com.au writes: I've been working in psql a lot recently, and have started to wonder why statements with syntax errors or other problems that render them unexecutable terminate the transaction. Well, the obvious reason is that it's hard to tell what the user meant, so bailing is the safest response. I understand why statements that raise errors during their execution terminate a transaction, So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. regards, tom lane -- 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] How to include Tablefunc as an extension
I read through the Postgres doc and many Google results, but it seems still unclear to me how to include additional packages into my postgres database. I see that there are a few installed under /usr/local/pgsql-9.1/share/extension/ (I am on Lion and installed the Kyngchaos libs). But as I want to install crosstab now, I downloaded the source code for postgres, run a make/install - and now wonder what to do with it. I could eventually load the .sql file as usual; but it seems this should be now avoided, and loaded as an extension. I tried to copy three files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and then load it from PgAdmin, but this results in an error message (could not access file $libdir/tablefunc). This should do the trick: CREATE EXTENSION crosstab; Thanks a lot. Yes, that's the command I used when it launches the above error message. The question is more about: where do I need to place the tablefunc files (and which ones) in order to execute successfully that command? Thanks for any hints. -- 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] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 02:20:57AM -0400, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I've been working in psql a lot recently, and have started to wonder why statements with syntax errors or other problems that render them unexecutable terminate the transaction. Well, the obvious reason is that it's hard to tell what the user meant, so bailing is the safest response. I understand why statements that raise errors during their execution terminate a transaction, So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. +1. I hate tools that try to read your mind. They invariably fail at that. The current behaviour is 100% correct and unambiguous. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- 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] How to include Tablefunc as an extension
On Mon, Jun 18, 2012 at 11:34 PM, Stefan Schwarzer stefan.schwar...@unep.org wrote: I read through the Postgres doc and many Google results, but it seems still unclear to me how to include additional packages into my postgres database. I see that there are a few installed under /usr/local/pgsql-9.1/share/extension/ (I am on Lion and installed the Kyngchaos libs). But as I want to install crosstab now, I downloaded the source code for postgres, run a make/install - and now wonder what to do with it. I could eventually load the .sql file as usual; but it seems this should be now avoided, and loaded as an extension. I tried to copy three files (--1.0.sql, --unpackaged.sql and the normal .sql) to the folder and then load it from PgAdmin, but this results in an error message (could not access file $libdir/tablefunc). This should do the trick: CREATE EXTENSION crosstab; Thanks a lot. Yes, that's the command I used when it launches the above error message. The question is more about: where do I need to place the tablefunc files (and which ones) in order to execute successfully that command? make install should copy files including $libdir/tablefunc. If not, you should take a look at the output of the make command. Make sure your PATH points to pg_config that your database is running with. Thanks, -- Hitoshi Harada -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgstat wait timeout : permission denied
Hi All my configuration: (-) postgres 9.1 (-) windows 2003 (-) 4 Xeon CPUs (-) 16GB of RAM I'm getting a lot of errors in my postgres log file: --- 2012-06-19 09:31:06 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:31:38 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:31:38 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:31:58 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:31:58 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:32:58 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:32:58 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:33:38 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:33:38 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:33:41 CESTLOG: 42501: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: Permission denied --- and that's really odd: I mean, when postgres starts, it creates the file pg_stat_tmp/pgstat.stat, so it means that it has all the permissions to access to that folder. But, nevertheless, after some time it shows the error above. And these are repeadtly shown in the postgres log file. Tried to change the folder/file permissions to everyone full control, but nothing changes. Do you have some hints/idea? Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Error message psql: could not connect to server: No such file or directory
I guess this is a typical user error. I searched around to find the solution, but in vain. I just upgraded to Lion, and used Kyngchaos libraries for installation of Postgres. Try to get running postgres, and I get this: /usr/local/pgsql-9.1/bin/psql -U postgres psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? In my limited understanding, it seems that Postgres is not running. When I use: ps auxw | grep post out comes only the grep post, but no postgres process. When I use: sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist it says: org.postgresql.postgres: Already loaded and displays in the ps list then: user935 0.0 0.1 2493888 7296 ?? Ss3:24PM 0:00.12 /System/Library/PrivateFrameworks/DiskImages.framework/Resources/diskimages-helper -uuid 9EFB9424-7971-4A8B-9D73-B93BD7F1DB5F -post-exec 4 (which looks a bit strange to me (DiskImages ??)) I am somewhat lost (and worse is that it worked the other day, before re-installing Lion anew.). I need unfortunately to come back to this issue. I (again) re-installed Lion from scratch, and finally got everything working. The Postgres was running, I uploaded a couple of dumped SQL files. And then re-started the machine for another reason. And suddenly it says again the it can't connect to the server. I tried /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US and /usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile start and sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist … but nothing happens. I don't have the feeling that it's a problem with the postgres and _postgres users (as it has worked before already). Can anyone give me a hint how I can figure out where the problem lies and what I can do about it? Why did it work before I restarted the computer and why does it not work anymore? Thanks a lot for any hints! -- 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] Fine-grained replication?
On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? Binary Replication? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial Thanks, Paul Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- 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] pgstat wait timeout : permission denied
2012-06-19 09:33:38 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:33:38 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:33:41 CESTLOG: 42501: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: Permission denied That looks like anti-virus software getting in the way. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please make it easy to drop a database that is in use
Hi All, When I'm developing against a PostgreSQL database I often drop and re-create it and I often find that the drop fails, because it's in use by other users. This is really annoying, especially when I know full well there are no other users - it's just me. I'm aware of the workaround: use *pg_terminate_backend* to kill existing connections, but there are two problems with that. Firstly, it's just a hassle, for something that should be a very simple operation. (I'm not even writing SQL for it normally, just pressing Delete in pgAdmin.) Secondly, pg_terminate_backend requires superuser rights. If I'm not a superuser, but I am the owner of the database, it doesn't seem right that another user should be able to prevent me from dropping /my/ database. I'd really like to see PostgreSQL directly support dropping a database, regardless of who is using it - something like DROP DATABASE ... CASCADE. (Although CASCADE wouldn't be the appropriate word here. Maybe DROP DATABASE ... TO_HELL_WITH_USERS?) Evan
Re: [GENERAL] Fine-grained replication?
On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? If you want fine grained replication, it means you won't be able to use log shipping and streaming replication. If you don't want trigger based replication, you won't have many options still available. pgPool comes to mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it isn't really replication). So, nope, sorry. BTW, what's the issue with trigger-based replication? it really helps a lot in many cases. -- Guillaume http://www.postgresql.fr http://dalibo.com -- 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] pgstat wait timeout : permission denied
hmm, interesting idea. I'll check, it will be a bit difficult because it is centrally managed by administrators, but I will see how to temporary disable it. Thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/pgstat-wait-timeout-permission-denied-tp5713236p5713255.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Fine-grained replication?
Paul Jones wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? I'm not sure what you want, because pgreplay is certainly not a replication tool, unless replication means something quite different to you than it does to me. Can you be more specific? Yours, 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
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Fine-grained replication?
On Τρι 19 Ιουν 2012 13:09:28 Guillaume Lelarge wrote: On Mon, 18 Jun 2012 14:49:32 -0700 (PDT), Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? If you want fine grained replication, it means you won't be able to use log shipping and streaming replication. If you don't want trigger based replication, you won't have many options still available. pgPool comes to mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it isn't really replication). So, nope, sorry. BTW, what's the issue with trigger-based replication? it really helps a lot in many cases. Totally agree, for us, a heavily modified version of (what initially was) DBmirror has made the job more than well. Also, our replication is the most fine-grained situation i can think of. (even rows being lazily replicated based on the FK dependency graph, and many other nice tricks) - Achilleas Mantzios IT DEPT -- 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] Fine-grained replication?
On Τρι 19 Ιουν 2012 12:00:49 Sergey Konoplev wrote: On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? Binary Replication? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial Alright, but the OP wrote about fine-grained. IMHO log-based and fine-grained seem a liitle but mutually conflicting. Thanks, Paul Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general - Achilleas Mantzios IT DEPT -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT INTO create new table, how to turn off this feature?
Hi, Is it possible to turn off future SELECT INTO that create new table? I use it on pl/pgsql function, and I have mamy mistakes with variable name in SQL thanks Wojtek -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get no. of commits/rollbacks by application on the database?
Hi All, How can we figure out no. of commit/rollback happend on the database by application ? As per my testing what I observed, even if your database is idle than in pg_stat_database (xact_commit/xact_rollback) columns keep increasing. This means autovacuum/autoanalyze count also included in these column. If am a DBA, how to know exact application or user commits/rollbacks happened on the database without enabling logs. Please advice. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?
On 19/06/2012 14:05, aasat wrote: Hi, Is it possible to turn off future SELECT INTO that create new table? I use it on pl/pgsql function, and I have mamy mistakes with variable name in SQL No, it's not possible. What problems are you running into? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Feature discussion: Should syntax errors abort a transaction?
On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] SELECT INTO create new table, how to turn off this feature?
Sorry, my mistake, Postgres 9.0 for unexists variables throwing error myvariable is not a known variable -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267p5713279.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Error message psql: could not connect to server: No such file or directory
On 06/19/2012 01:32 AM, Stefan Schwarzer wrote: I need unfortunately to come back to this issue. I (again) re-installed Lion from scratch, and finally got everything working. The Postgres was running, I uploaded a couple of dumped SQL files. And then re-started the machine for another reason. And suddenly it says again the it can't connect to the server. I tried /usr/local/pgsql-9.1/bin/initdb -U postgres -D /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US and /usr/local/pgsql-9.1/bin/pg_ctl -D /usr/local/pgsql-9.1/data/ -l logfile start and sudo launchctl load /Library/LaunchDaemons/org.postgresql.postgres.plist … but nothing happens. I don't have the feeling that it's a problem with the postgres and _postgres users (as it has worked before already). Can anyone give me a hint how I can figure out where the problem lies and what I can do about it? Why did it work before I restarted the computer and why does it not work anymore? Thanks a lot for any hints! When troubleshooting I am firm believer in working from the known to the unknown. In that vein: 1) Is there /usr/local/pgsql-9.1/data/? a) If so are there subdirectories in it indicating cluster was created? i.e. base/ global/ pg_clog/, etc b) If not is there a Postgres data directory anywhere on machine? 2) When you say nothing happens does that mean nothing is written to log file and/or terminal? No process shows up in process list? 3) If something does happen in, terms of error messages, what are they? 4) Have you looked at system (not Postgres specific) logs to see if this a system issue? 5) What user are you running the above commands as? a) What user is associated with the Postgres data directory(if it exists)? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 03:35:19PM +0200, Rafal Pietrak wrote: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. Good. One looks forward to your fully-worked-out AI/ESP patch that gets this right every time. While you're at it, I suggest fixing these obvious mistakes: SELECT SELECT 'text'; SELECT 'text; SELECT INSERT 'text' INTO column; INSERT 'text' INTO 'column'; And so on. Every one of these is a boiled down example of a stupid think-o I have made more than once. This is what the command buffer is for. If you really want your input system to provide fairly complete syntax checking for you, however, I will point out that psql's \e command will happily drop you into the editor of your choice. If you want an editor that knows more about what you want than you do, I think you will find it is spelled emacs. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Feature discussion: Should syntax errors abort a transaction?
Rafal Pietrak ra...@zorro.isa-geek.com writes: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. You are probably thinking that we could behave differently if the error was detected by the lexer, or perhaps the lexer + grammar, rather than later on. But those boundaries are purely implementation artifacts, and the division of labor isn't always obvious, especially to people not steeped in the innards of PG. Users are going to be confused (and unhappy) if some errors roll back their transaction while other not-obviously-different ones don't. As an example, suppose you fat-finger '-' for '=' in UPDATE: UPDATE tab SET col - 42 WHERE ... This is going to draw a grammar error. But make the same mistake a few tokens later: UPDATE tab SET col = 42 WHERE key - 42; and now you will get a pretty late-stage parse analysis failure, since it'll bleat that the argument of WHERE isn't boolean. Users are definitely not going to understand why the former doesn't kill their transaction but the latter does. Or, if we solve that problem by saying that no parse-analysis failure kills the transaction, where does that stop? The boundaries between parse analysis, planning, and execution are even squishier and more arbitrary (from a naive user's standpoint) than the ones earlier in the process. regards, tom lane -- 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] Feature discussion: Should syntax errors abort a transaction?
Hm, sorry but I still can not get into that argument. Take your example 3 (COMINT in place of COMMIT) How should the DB know that (and how) to safely recover from such error? You need to tell - and there are tools to do so right available. In an interactive session: - use autocommit=on to indicate that any statement surely will not invalidate any previous one Then the problem is non-existent - if you need transactional grouping of statements: you may envelope each statement with transactional sub structure (e.g. SAVEPOINTRELEASE) to indicate to the DB that only the inner most level of transaction is at stake and the environment outside that statement may cope with errors. Agreed, this is unexpected if coming from a DB that treats syntax errors differently. (May be sometimes there will be a mode with interactive tools that provide such enveloping implicitly (if requested by user)) In a non-interactive session it is more obvious. What should happen when after the failed COMMIT above the session is to be terminated? The pending transaction is to be terminated anyway. Moreover, of a syntax error happens with a statement (e.g. some update) and a later statement is assuming it had succeeded and will ruin your data if not, would you still appreciate the DB to simply ignore the error (logging a message of course) and later on happily commit inconsistent data? I'm sure, there will be loud outcry if such would be possible by mere syntax error handling. If your application is prepared to handle syntax errors during run, then use available tools, if not (and most application likely will not provide such logic), accept the need for testing your applications. Any reaction for a transactional system has to guarantee consistency even for the price of convenience. Thus, convenience may cost some extra effort. At the end, I read the complaint as a suggestion to maintainers of interactive tools to build such interactive convenience into their tools. But do not detect evidence for this to be a feature of the DB in the first place. Rainer On 19.06.2012 15:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Feature discussion: Should syntax errors abort a transaction?
There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... Tom Lane t...@sss.pgh.pa.us escreveu: Rafal Pietrak ra...@zorro.isa-geek.com writes: The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. I beg to disagree. Typos can manifest themselves as execution errors just as well as syntax errors. You are probably thinking that we could behave differently if the error was detected by the lexer, or perhaps the lexer + grammar, rather than later on. But those boundaries are purely implementation artifacts, and the division of labor isn't always obvious, especially to people not steeped in the innards of PG. Users are going to be confused (and unhappy) if some errors roll back their transaction while other not-obviously-different ones don't. As an example, suppose you fat-finger '-' for '=' in UPDATE: UPDATE tab SET col - 42 WHERE ... This is going to draw a grammar error. But make the same mistake a few tokens later: UPDATE tab SET col = 42 WHERE key - 42; and now you will get a pretty late-stage parse analysis failure, since it'll bleat that the argument of WHERE isn't boolean. Users are definitely not going to understand why the former doesn't kill their transaction but the latter does. Or, if we solve that problem by saying that no parse-analysis failure kills the transaction, where does that stop? The boundaries between parse analysis, planning, and execution are even squishier and more arbitrary (from a naive user's standpoint) than the ones earlier in the process. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Feature discussion: Should syntax errors abort a transaction?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 ... ON_ERROR_ROLLBACK=interactive provides a helper for that in psql. Savepoints are overhead, though, and I don't understand why they're required for statements that don't even parse. Other have handled the latter part of the above already (short version: error is the only sane response to a non-parsing statement), but as to the first part, the overhead is really not that high. Yes, psql will create and remove a savepoint around each statement, but this is a very lightweight action, especially if you are using psql in interactive mode. In other words, we already have an elegant and lightweight approach to the described problem. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201206191146 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk/gn30ACgkQvJuQZxSWSsgekgCfcoBq2VjCitjrpK9CrSMFob0Y YF8An3Qp/OQjAcRsEBahE5OIbFzEEZX/ =hHAn -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] db server processes hanging around
hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr
Re: [GENERAL] Smaller multiple tables or one large table?
Hi all, I am curious if there is a significant speed up with doing this if most of the queries run against it are going to be table wide. I won't drop the data and the data won't really grow. Do I get better speedup with one large table and large indexes or many small tables with many small indexes? Thanks, ~Ben On Sat, Jun 16, 2012 at 2:13 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi Benedict, Il 15/06/12 20:58, Benedict Holland ha scritto: The tables would have to be specified with a table pk constraint falling between two ranges. A view would then be created to manage all of the small tables with triggers handling insert and update operations. Select would have to be view specific but that is really cheap compared to updates. That should have the additional benefit of only hitting a specific table(s) with an update. Basically, I don't see how this particular configuration breaks and if PostgreSQL already has the ability to do this as it seems very useful to manage very large data sets. What you are looking for is called 'partitioning' (horizontal partitioning). I suggest that you read this chapter: http://www.postgresql.org/**docs/9.1/static/ddl-**partitioning.htmlhttp://www.postgresql.org/docs/9.1/static/ddl-partitioning.html Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartolini@**2ndQuadrant.it | www.2ndQuadrant.it
Re: [GENERAL] Fine-grained replication?
I should have been more specific about why I'm interested in this. The idea is not so much the replication but being able to use the replication information to make business decisions based on what is observed happening to the data, or being able to selectively repair data if there are problems. XReader certainly shows a lot of promise, but it's not quite here yet. Specific answers to respondents below... From: Sergey Konoplev sergey.konop...@postgresql-consulting.com Date: Tue, 19 Jun 2012 13:00:49 +0400 On Tue, Jun 19, 2012 at 1:49 AM, Paul Jones p...@cmicdo.com wrote: Is anyone aware of other non-trigger-based, fine-grained replication tools for PostgreSQL along the lines of the XReader http://wiki.postgresql.org/wiki/XReader or pgreplay http://pgreplay.projects.postgresql.org/? Binary Replication? http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial Sergey Konoplev Thanks! This is good info. On Tue, Jun 19, 2012 at 12:34:02PM +0200, Albe Laurenz wrote: I'm not sure what you want, because pgreplay is certainly not a replication tool, unless replication means something quite different to you than it does to me. Can you be more specific? Yes, as explained above. So pgreplay in this context would be more likely to be used to repair something. Date: Tue, 19 Jun 2012 12:09:28 +0200 From: Guillaume Lelarge guilla...@lelarge.info If you want fine grained replication, it means you won't be able to use log shipping and streaming replication. If you don't want trigger based replication, you won't have many options still available. pgPool comes to mind but, AFAIK, it isn't fine grained (and, to be honest, I would say it isn't really replication). So, nope, sorry. BTW, what's the issue with trigger-based replication? it really helps a lot in many cases. The worry with trigger-based replication is that it could become a performance bottleneck for heavy loads. We tried out Slony just to see how it worked and it did its job just fine for what we did with it. Paul Jones -- 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] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- 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] db server processes hanging around
On 06/19/2012 09:29 AM, Mark Rostron wrote: hi we are running out of database connections. we are using pg 9.0.6 on linux centos 5.7 64bit. we are not using any go-between connection pools such as pgbouncer or pgpool - connections occur directly from client to database. the connection setup on the client (java) is default, only providing (user,password,dbhost,dbname). we have about 10 developers developing java thru IDEA who start/stop the local tomcat server frequently. i have observed that tomcat doesn't disconnect from pg cleanly when they cycle, and the server processes persist for a long time. I have had them reduce their local connection factory pool size to 1 (this helped) and increased our max_connection value to 1000. yet the problem persists. I have noticed that the server processes do die after some time - due to inactivity? we are looking for a way to control server processes better than we are doing now. thnx for your time. mr I am unaware of any system setting like max_connection_idle_time (though it might be a useful addition). I have not had to mess with tcp_keepalive settings but you might be able to alter those (perhaps at the OS instead of PostgreSQL) to reduce the delay before the backend terminates. But this won't work for socket connections. You could hack together a tailored solution by having cron run a script that would query pg_stat_activity for queries equal to IDLE and with a backend_start age greater than whatever you find reasonable and then execute pg_terminate_backend() on those PIDs. You could even have a table of developer IP addresses and only terminate those processes. Alternately, if Tomcat connected to a different port you could only kill those. Cheers, Steve -- 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] bytea insert difference between 8.3 and 9.x
Merlin Moncure-2 wrote m_connection.prepare(INSERT INTO foo(x) VALUES($1)) (bytea, pqxx::prepare::treat_binary); I have the same problem. I can't get accsess to pqxx::prepare::treat_binary. And m_connection.prepare(...) two arguments only. What i mist? -- View this message in context: http://postgresql.1045698.n5.nabble.com/bytea-insert-difference-between-8-3-and-9-x-tp4840946p5713320.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Feature discussion: Should syntax errors abort a transaction?
And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Special ORDER BY
How can I order a result set by a special order of a column. For example, let's say I have this query SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740) Now I want to order the result set using c.etiqueta but with the order of the values as you see in the IN clause. How can I do that? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] Special ORDER BY
You can use something like "CASE WHEN" .. and use a number to order your data... like this SELECT *, CASE WHEN c.etiqueta = 245 THEN 1 WHEN c.etiqueta = 130 THEN 2 WHEN c.etiqueta = 240 THEN 3 WHEN c.etiqueta = 243 THEN 4 WHEN c.etiqueta = 246 THEN 5 WHEN c.etiqueta = 490 THEN 6 WHEN c.etiqueta = 630 THEN 7 WHEN c.etiqueta = 730 THEN 8 WHEN c.etiqueta = 740 THEN 9 end as newcolum FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740) ORDER BY newcolum Em 19/06/2012 16:31, Martín Marqués escreveu: How can I order a result set by a special order of a column. For example, let's say I have this query SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740) Now I want to order the result set using c.etiqueta but with the order of the values as you see in the IN clause. How can I do that?
Re: [GENERAL] Special ORDER BY
Helo This is provided that the list is constant SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) JOIN (values(245,1),(130,2), (240,3), (243,4), (246,5), (490,6), (630,7), (730,8), (740,9)) as d(dato,orden) on (c.etiqueta =d.dato) ORDRE BY d.orden On Tue, Jun 19, 2012 at 2:31 PM, Martín Marqués martin.marq...@gmail.comwrote: How can I order a result set by a special order of a column. For example, let's say I have this query SELECT * FROM subcampo sc JOIN campo c ON (c.codigo = sc.campo) WHERE c.etiqueta IN (245, 130, 240, 243, 246, 490, 630, 730, 740) Now I want to order the result set using c.etiqueta but with the order of the values as you see in the IN clause. How can I do that? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Cordialmente, Ing. Hellmuth I. Vargas S. Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 08:27 +1200, Gavin Flower wrote: [] I would be be extremely concerned about any move to allow syntax errors not to abort a transaction. Me too. But it's a nuicence for interractive session when transactions breakes due to syntax error - still, may be as Rainer Pruy said earlier, this may be a suggestion to maintainers of interactive tools. Even minor syntax errors may indicate that something much more serious is wrong. No. We are talking about an interactive session - someone just have misstyped something; it's a one time event. PL/1 was designed to tolerate various errors and guess what the programmer intended, it would make assumptions and act on them – a good way to hide serious programming errors. A language that is too forgiving encourages sloppy thinking. This is dangerous grounds :) - without going too far I'd say, there is also ADA (rigorious) and perl (sloopy). statistically, anything I installed, that's written in perl is ways more stable, then enything else. But I'd also say, that I prefere tools (programming languages, operating systems, IDE, etc), that help me from makeing errors. [---] I would far rather a compiler pull me up for minor violations, than an obvious error not picked up until I came to test the program. The compiler is not perfect and some errors will slip through. However, the sooner errors are detected, the less likely an error will cause bad problems in production. On the other hand I find it more tedious then it pays off, when current CC force me to explicitly typecast every pointer I write, because: type don't match. But that's not the point here. The point is, that sometimes we need regorious, and sometimes we need sloopy. Like, when we start a project, we need to scetch, then we need to tighten the shoelaces. At least for me it works that way. And we are talking about interractive psql breaking transaction because of syntax error - almost always this is a one time typo. I'd prefere it to be a bit more sloopy, then deployed SQL application (e.g. non-interactive session). The greater the size and complexity of code, the more important this all becomes. Mind you, even very simple SQL SELECT's might have results used to make critical business decisions - so even then, sloppy habits should be discouraged. Hmmm, years ago I has told, that UNIX is sloopy (does not guarantee anything to a process: neither time to dysk when writing, nor CPU time, nor even IRQ response time), so it will not prevail. It did. And it runs critical systems. As postgres is my favourite database for its ease of use (to the point where I dont try applications which only run on its closest free-couterpart: mysql :), there is always room for improvements (my personal wishlist for postgres is currently 11 points long and keeping transaction on syntax errors is even beyond that list). -R -- 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] Feature discussion: Should syntax errors abort a transaction?
But that data was supposed to get transferred into another table first! Data shouldn't just disappear like that. If you want that kind of behaviour use a different db that likes to throw your data away when it shouldn't. On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter edsonrich...@hotmail.com wrote: And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error. -- To understand recursion, one must first understand recursion. -- 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] Feature discussion: Should syntax errors abort a transaction?
I like the current behavior. Having been pleasantly surprised that this is how Pg operates, it is very helpful when I'm working on scripts or batches such as for creating or populating schemas. If it dies part way through, I know I can just fix the problem and rerun the whole thing, without having to first undo or skip the earlier portions. Also, rollback for everything is much more deterministic. -- Darren Duncan -- 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] Feature discussion: Should syntax errors abort a transaction?
On 20/06/12 01:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that SELECT 1/0; should terminate a transaction, but SELECT 1//0; should not? How about ROLBACK;? It gets pretty squishy pretty fast when you try to decide which sorts of errors are more important than others. When put that way, it seems blindingly obvious. You have a talent for making a devastating point very succinctly. I'd humbly disagree. Not to drag this discussiong any further, just to make a point that the other approach is also blindingly obvious. Only the other way around. The point is, that SQL syntax errors are so obviusly different from execution errors, that noting this distinction should not raise any ambiguity. In Tom's example ROLBACK: 1. should not break the transaction 2. should only raise NOTICE: syntax error 2.1. in case this was issued from command line - user can always ROLTAB to see what's next. 2.2. in case of a compiled program sending a ROLBACK to the backend hack, the programmer should know better. 3. and BTW: what about rolling back a tediously cooked sequence of statements finished by COMINT? Things are not so obvious. And frankly, if not for the TAB I'd have case (3) so often, that it would have driven me crasy. -R -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ I would be be extremely concerned about any move to allow syntax errors not to abort a transaction. Even minor syntax errors may indicate that something much more serious is wrong. PL/1 was designed to tolerate various errors and guess what the programmer intended, it would make assumptions and act on them – a good way to hide serious programming errors. A language that is too forgiving encourages sloppy thinking. A bit like in chess, if you don't follow the dictum of 'touch a piece move it' in social play (it is the rule in match and tournament play), then your level of skill in Chess is unlikely to improve much. I coach Chess at my son's school and I used to be Director-of-Play for Chess tournaments. I remember learning C many years ago, very unforgiving. However, the discipline imposed was very beneficial to improving my programming skills. I would far rather a compiler pull me up for minor violations, than an obvious error not picked up until I came to test the program. The compiler is not perfect and some errors will slip through. However, the sooner errors are detected, the less likely an error will cause bad problems in production. The greater the size and complexity of code, the more important this all becomes. Mind you, even very simple SQL SELECT's might have results used to make critical business decisions - so even then, sloppy habits should be discouraged. I would be very reluctant to hire any developer who had the mind set of seriously wanting something like psql to be forgiving of any kind of error - as it suggests that they are more careless than normal, and lack the attitude to be reliably rigorous.
Re: [GENERAL] pgstat wait timeout : permission denied
2012/6/19 Alban Hertroys haram...@gmail.com: 2012-06-19 09:33:38 CESTWARNING: 01000: pgstat wait timeout 2012-06-19 09:33:38 CESTLOCATION: backend_read_statsfile, .\src\backend\postmaster\pgstat.c:3807 2012-06-19 09:33:41 CESTLOG: 42501: could not rename temporary statistics file pg_stat_tmp/pgstat.tmp to pg_stat_tmp/pgstat.stat: Permission denied That looks like anti-virus software getting in the way. Or the Windows Firewall. regards -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br -- 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] Feature discussion: Should syntax errors abort a transaction?
According to documentation, TRUNCATEis transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Regards, Edson Richter. Em 19/06/2012 18:58, Scott Marlowe escreveu: But that data was supposed to get transferred into another table first! Data shouldn't just disappear like that. If you want that kind of behaviour use a different db that likes to throw your data away when it shouldn't. On Tue, Jun 19, 2012 at 1:09 PM, Edson Richter edsonrich...@hotmail.com wrote: And I will be pleased that data is gone! I really did not expect anything but this. If I need such tolerant behavior, then this shall be a feature of my special app, not a feature of the database... If the developer does not know how to write sql, then is time to learn. If the problem is the dynamic generated Sql, then I must write more test cases to cover these new scenarios. But IMHO, database must fail always (syntax or not...). Regards, Edson Scott Marlowe scott.marl...@gmail.com escreveu: On Tue, Jun 19, 2012 at 8:50 AM, Edson Richter edsonrich...@hotmail.com wrote: There is also the case of dynamically generated sql statements based on user selection... being syntax or not, I would never want half job done. Thia is the purpose of transactions: or all or nothing... This this this, and again, this. Imagine: begin; insert into tableb selcet * from tableb; truncate tableb; commit; What should happen when we get to the error on the second line? Keep going? Boom, data gone because of a syntax error.
Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?
On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter edsonrich...@hotmail.com wrote: According to documentation, TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Yes but the discussion was that the syntax error SHOULDN'T cause a roll back, and I was giving an example of when a transaction should have rolled back but wouldn't have if syntax errors didn't cause rollback. In a different vein, the issue of interactive versus scripted is something I don't want to take chances on getting wrong. If I'm in the psql terminal and type \i /tmp/somesqlile.sql is that interactive or scripted? How can psql know? Should it know? Can I trust it to make the right decision of interactive versus scripted each time? I generally put more than two lines of sql in a text file, edit it, and throw at begin; on it. run it with \i and then commit or rollback as needed. It documents what you did so you can check it in somewhere, and makes it repeatable. -- 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] Feature discussion: Should syntax errors abort a transaction?
Em 19/06/2012 22:26, Scott Marlowe escreveu: On Tue, Jun 19, 2012 at 6:19 PM, Edson Richter edsonrich...@hotmail.com wrote: According to documentation, TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit. You will find this description at following page: http://www.postgresql.org/docs/9.0/static/sql-truncate.html So, when you have the syntax error on second line, then transaction is rolled back (cannot proceed: and that's why Syntax Errors should be treated as any other error) and your data is safe. Yes but the discussion was that the syntax error SHOULDN'T cause a roll back, and I was giving an example of when a transaction should have rolled back but wouldn't have if syntax errors didn't cause rollback. In a different vein, the issue of interactive versus scripted is something I don't want to take chances on getting wrong. If I'm in the psql terminal and type \i /tmp/somesqlile.sql is that interactive or scripted? How can psql know? Should it know? Can I trust it to make the right decision of interactive versus scripted each time? I generally put more than two lines of sql in a text file, edit it, and throw at begin; on it. run it with \i and then commit or rollback as needed. It documents what you did so you can check it in somewhere, and makes it repeatable. AFAIK, psql open one connection to database - and the transaction is connection related (two different connections does not share a transaction). I really mean AFAIK. At this point, someone else with more internals knowledge can give some light here. My argument was pro syntax error should rollback to make things safe... :-). Assuming psql is working with only one connection, even in interactive mode, the transaction should remains valid. Regards, -- 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] How to get no. of commits/rollbacks by application on the database?
On 06/19/2012 09:08 PM, Raghavendra wrote: How can we figure out no. of commit/rollback happend on the database by application ? If am a DBA, how to know exact application or user commits/rollbacks happened on the database without enabling logs. This sounds like one of those what are you trying to achieve questions, where you need to step back and ask why you're trying to collect that info and what you want it for. First, how do you define application? Any client that connects to a given database? Any client that sets the application_name GUC to a particular value? Any client from a given host? etc. Application can mean a lot of different things. It sounds like you might simply mean work done on a connection that isn't internal to the database system's bookkeeping, which is a bit easier, but I'm not sure. Second, why? What does the transaction count tell you? How will you account for work done by PgAgent (if used), via dblink, etc? I guess I'm unsure what you're trying to accomplish. -- 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
