Re: Not able to update some rows in a table
> I have a table with 21 columns. > Primary key is done with 20 of these colums. Oh, okay. What data types do these encompass? > I don't know why, but I'm not able to update some of these records. > I don't understand, it seems that I'm not able to fetch. You mean you can't query them with all 20/21 columns present in the where clause?
Re: Ways to deal with large amount of columns;
As David said, you'd be better off having a table that looks like this (in terms of columns): * MONTH * AGENT * CASHFLOW So your query to get the sum of a single agent would be looking like: select sum(CHASFLOW) where AGENT = 'Agent' and MONTH between values; It might be a little more work to create a GUI for that (or map this model to the existing GUI) but it is much simpler to maintain and work with. On 30.08.2018 11:13, a wrote: > Hi all: > > > I need to make a table contains projected monthly cashflow for multiple > agents (10,000 around). > > > Therefore, the column number would be 1000+. > > > I would need to perform simple aggregate function such as count, sum or > average on each cashflow projected. > > > So if there is anyway of doing this? Will there be anything like define a > macro in C that I can manipulate multiple columns by simple word that > representing them. > > > Thanks so much! > > > Shore >
Information on savepoint requirement within transctions
I'm currently doing a small writeup of a bug fix in our framework which involves savepoints in PostgreSQL (JDBC). However, I have a hard time locating the documentation regarding this. I mean, from what I can extract from various sources, PostgreSQL requires to use savepoints if one wants to continue a transaction after a failed statement, but I can't find where in the documentation that is stated and documented. Can somebody point me to the correct location where this is documented and maybe even explained why that is the case?
Re: Information on savepoint requirement within transctions
Well, no. What I'm looking for is information on how the transactions behave in an error case, and why there is the requirement to have a savepoint in place to be able to continue a transaction after a failed statement. As far as I'm aware neither PostgreSQL nor OS version do matter for this, I'm interested in the general behavior of the database. But as I said, I do find a lot of documentation on transactions in general, but not about their behavior in an error case. Your first link is "kinda" what I'm looking for, because it closes with > Moreover, ROLLBACK TO is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely and starting again. and I'm looking on more information on *that*. On 26.01.2018 15:59, Melvin Davidson wrote: > On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz > wrote: > >> I'm currently doing a small writeup of a bug fix in our framework which >> involves >> savepoints in PostgreSQL (JDBC). However, I have a hard time locating the >> documentation regarding this. I mean, from what I can extract from various >> sources, PostgreSQL requires to use savepoints if one wants to continue a >> transaction after a failed statement, but I can't find where in the >> documentation that is stated and documented. >> >> Can somebody point me to the correct location where this is documented and >> maybe >> even explained why that is the case? > > > You have not specified which version of PostgreSQL you are using (or your > O/S), but is this the documention you are looking for? > > https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html > > https://www.postgresql.org/docs/9.6/static/sql-savepoint.html > > https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html > > https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html >
Re: Information on savepoint requirement within transctions
What I'm looking for is more information/documentation on that topic that I can use as source and link back to (from a blog post). That last paragraph in your first link is exactly what I meant. Let's start to clarify things, put into pseudo code: start transaction insert into A insert into B but fail insert into C commit As far as I'm aware, in most other databases this would work like that, even though one statement has failed, one can continue using this transaction and actually commit the rows in A and C. In PostgreSQL the use of savepoints is required: start transaction insert into A create savepoint insert into B but fail rollback to savepoint insert into C commit Otherwise the transaction is, after the failed statement, in a state in which it can not be used anymore. Is that correct? On 26.01.2018 16:42, Melvin Davidson wrote: > On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz > wrote: > >> Well, no. What I'm looking for is information on how the transactions >> behave in >> an error case, and why there is the requirement to have a savepoint in >> place to >> be able to continue a transaction after a failed statement. >> >> As far as I'm aware neither PostgreSQL nor OS version do matter for this, >> I'm >> interested in the general behavior of the database. But as I said, I do >> find a >> lot of documentation on transactions in general, but not about their >> behavior in >> an error case. >> >> Your first link is "kinda" what I'm looking for, because it closes with >> >> > Moreover, ROLLBACK TO is the only way to regain control of a transaction >> block that was put in aborted state by the system due to an error, short of >> rolling it back completely and starting again. >> >> and I'm looking on more information on *that*. >> >> >> On 26.01.2018 15:59, Melvin Davidson wrote: >>> On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz >> >>> wrote: >>> >>>> I'm currently doing a small writeup of a bug fix in our framework which >>>> involves >>>> savepoints in PostgreSQL (JDBC). However, I have a hard time locating >> the >>>> documentation regarding this. I mean, from what I can extract from >> various >>>> sources, PostgreSQL requires to use savepoints if one wants to continue >> a >>>> transaction after a failed statement, but I can't find where in the >>>> documentation that is stated and documented. >>>> >>>> Can somebody point me to the correct location where this is documented >> and >>>> maybe >>>> even explained why that is the case? >>> >>> >>> You have not specified which version of PostgreSQL you are using (or your >>> O/S), but is this the documention you are looking for? >>> >>> https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html >>> >>> https://www.postgresql.org/docs/9.6/static/sql-savepoint.html >>> >>> https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html >>> >>> https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html >>> >> > Simply put, a SAVEPOINT does not allow you to "continue" a transaction > after an error. > What is does is allow you to commit everything up to the SAVEPOINT. > Everything after > the SAVEPOINT is not committed. There is no such thing as ROLLBACK THEN > CONTINUE, > which is what I think you are looking for. > > Once again, please remember to specify your PostgreSQL version and O/S when > addressing this forum. > It helps to clarify solutions for historical purposes. > >
Re: Information on savepoint requirement within transctions
On 26.01.2018 17:11, David G. Johnston wrote:
> The convention for these lists is to inline or bottom-post. Top-posting
> is discouraged.
Okay, I'll keep it in mind.
> Here's my take, the docs support this but maybe take some interpretation...
>
> A SAVEPOINT begins what is effectively a sub-transaction without ending
> the main transaction. If that sub-transaction fails you can throw it away
> (ROLLBACK TO) and pretend that it didn't happen: you are dropped back to
> the point where the savepoint was issued and the main transaction
> re-engaged.
>
> Its kinda like a try/catch block:
>
> BEGIN:
>
> do_stuff
>
> SAVEPOINT try { lets_fail; this_works; } catch { ROLLBACK TO }
>
> do_more_stuff
>
> COMMIT;
>
> As long as both do_stuff and do_more_stuff succeed when you commit the
> things that they did will persist.
>
> The stuff in lets_fail AND this_works, however, will be discarded because
> of the lets_fail failing and this_works belonging to the same
> sub-transaction.
>
> If do_more_stuff depends on lets_fail or this_works succeeding then
> do_more_stuff will fail and will cause do_stuff to rollback as well.
Thanks for the detailed explanation, that helps a lot. However, I'm still
looking for "official" documentation on their *requirement* in combination with
failing statements. Documentation, bug report, mailing list discussions,
something like that. In particular I'm interested in the questions:
* Why are they required in combination with failing statements (when every
other database does an "automatic savepoint and rollback" for a failed
statement)?
* When was that behavior chosen? Was it always like that? Was it adopted later?
* What is the design decision behind it?
There is a lot of information on what savepoints are and how they work (and also
thanks to you I'm now fairly certain I have good grasp on them), but I fail to
locate documentation on these questions.
Fwd: Re: Information on savepoint requirement within transctions
On 29.01.2018 12:37, Adam Tauno Williams wrote: > It this statement true? I very much feel *not*. This depends on how > you have set AUTO_COMMIT - and it certainly is not true for > transactions of multiple statements. Maybe I should clarify at that point that AUTO_COMMIT is OFF, and yes, that is how it does behave for me. > Informix does not do "automatic savepoint and rollback" - you will > rollback the entire transaction. Okay, not *all* of them, but *most* as it seems.
Re: Information on savepoint requirement within transctions
On 29.01.2018 14:36, David G. Johnston wrote: > Those questions would not be answered in user-facing documentation. You > can explore the git history and search the far-back mailing list archives if > you wish to satisfy your curiosity. For me this is how it works - the only > question for me is whether I should argue that the behavior should be > changed. I do vaguely recall this topic coming up in the recent (couple of > years) past...but changing transaction behavior like this is problematic no > matter how desirable the new state might be to have (and that's debatable). From my point of view, no, it shouldn't be changed. It has always been this way and I find nothing wrong with the approach, it is only something that you need to be aware of, that's all. > It may be worth updating the docs here... I'd vote for that. I would have expected to see this mentioned in the documentation a little bit more prominent than just a single sentence at the end of the transaction tutorial. A short section about how the transaction behaves in an error cases (and what to do) would be nice. > ...but you have received your official answer - I'm nearly positive I'm right > and even if I was mistaken most likely I would have been corrected by now. I > am writing this on a mailing list... > > David J. > Thank you for your time and explanations.
Re: Information on savepoint requirement within transctions
On 29.01.2018 15:11, Alban Hertroys wrote: > IMHO, the burden of explaining that is with those RDBMSes that don't > behave properly: > > If you start a transaction and something goes wrong in the process, > the logical behaviour is to fail - the user will want to rollback to a > sane state, doing any more work is rather pointless because of that. > Allowing a commit at the end is dubious at best. One could argue that automatically "undoing all broken things" (read: reverting back to the state before the failing statement was executed) would be a feature worth having. As far as I recall, that has also been brought up on the mailing list. Though, I don't care particularly about it. I was just interested in the documentation. > That does not exclude PG from documenting this behaviour, but I'd have > a look at the docs for those other vendors whether they perhaps > documented their irregular transactional behaviour ;) Heh, good luck. :) > You didn't mention which RDBMSes behave like what you expected > (probably from experience), but I seem to recall Oracle does odd stuff > like that, as well as issuing a commit to all open transactions when > any DDL happens or treating NULLs and empty literals as the same > thing. Just to say that the "big names" aren't without flaws - they're > kind of hard to fix when users probably depend on their behaviour > though. To reiterate my example (to get rid of any misconceptions): begin transaction insert row #1 insert row #2 (this fails) insert row #3 commit I've tested MySQL/MariaDB, Oracle, H2 and SQLite, all allow to continue a transaction after a failed statement without user interaction (rows #1 and #3 are in the database after committing). PostgresSQL requires the manual rollback to savepoint after a failed statement (obviously stores #1 and #3 in the database if each insert is "wrapped" with a savepoint). MSSQL on the other hand loses the complete state up to the failed statement and allows the user to continue to use the transaction like nothing happened (only #3 is inserted when committing). So, I think we can all agree who's the actually broken one here. ;)
Re: Information on savepoint requirement within transctions
On 29.01.2018 16:33, Tom Lane wrote: > That behavior does exist, and so does documentation for it; you're just > looking in the wrong place. > > Years ago (7.3 era, around 2002) we experimented with a server-side > GUC variable "AUTOCOMMIT", which switched from the implicit-commit- > if-you-don't-say-BEGIN behavior to implicitly-issue-BEGIN-so-you- > have-to-say-COMMIT-explicitly. That was an unmitigated disaster: > flipping the setting broke just about all client applications. After > spending many months trying to fix just the apps we ship with Postgres, > and getting pushback from users whose code broke with the alternate > setting, we gave up and removed the feature. Instead we set project > policy that if you want to modify transactional behavior you have to > do it on the client side, where it doesn't risk breaking other apps. > Institutional memory around here is very long, so any time proposals to > change the server or wire-protocol behavior in this area come up, they > get batted down. > > What we do have though is client-side support for appropriate behaviors. > In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables. > Other interfaces such as JDBC have their own ideas about how this ought > to work. Very interesting. However, I'm talking explicitly about the behavior that occurs when AUTOCOMMIT is switched off and a statement fails. Most curiously, you already did such a feature (of what I was talking about) request in 2007: https://www.postgresql.org/message-id/flat/11539.1177352713%40sss.pgh.pa.us#11539.1177352...@sss.pgh.pa.us
Re: Information on savepoint requirement within transctions
On 30.01.2018 03:07, David G. Johnston wrote: > So, my first pass at this. Nice, thank you. > + These are of particular use for client software to use when executing > + user-supplied SQL statements and want to provide try/catch behavior > + where failures are ignored. Personally, I'd reword this to something like this: > These are of particular use for client software which is executing > user-supplied SQL statements and wants to provide try/catch behavior > with the ability to continue to use the transaction after a failure. Or maybe something like this: > These are of particular use for client software which requires > fine-grained support over failure behavior within a transaction. > They allow to provide a try/catch behavior with the ability > to continue to use a transaction after a failure. Also I'd like to see something like this in the docs at roughly the same position: > If a failure occurs during a transaction, the transaction enters > an aborted state. An aborted or failed transaction cannot be used > anymore to issue more commands, ROLLBACK or ROLLBACK TO must be used > to regain control of the aborted transaction. A commit issued while > the transaction is aborted is automatically converted into a > . I'm not sure about the terminology here, though, because the Transaction Tutorial (https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html) speaks of "aborted" transactions, while you use the term "failed" here.
Re: Information on savepoint requirement within transctions
On 31.01.2018 19:58, David G. Johnston wrote: > Now that I've skimmed the tutorial again I think pointing the reader of > the SQL Commands there to learn how it works in practice is better than > trying to explain it in BEGIN and/or SAVEPOINT. That seems like a good idea, yeah. > I decided to add a title to the part of SAVEPOINTS and introduce the term > "Sub-Transaction" there though I'm not married to it - re-wording it using > only "savepoint" is something that should be tried still. Technically, it *is* a sub-transaction, Savepoints are just the means to do it. I think that a sub-transaction is the concept, Savepoint is the implementation. > A title and a paragraph or two on aborted transaction behavior probably > should be added as well. I'd like that. I might be able to type something up, though I'm currently a little bit short on time, so don't wait for me please. Just to make sure, you have two typos in there, "61: tranasctions" and "106: implment". Also I'd like to take the opportunity to agree with Laurenz here, "pseudo" seems to be misplaced, they *are* sub-transactions.
Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.
We are seeing a quite heavy slow down when using prepared statements in 10.1.
I haven't done some thorough testing, to be honest, but what we are having is a
select from a view (complexity of it should not matter in my opinion), something
like this:
prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and
COLUMNB = $2;
-- Actual test code follows.
-- Takes ~2 seconds.
select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;
-- Takes ~10 seconds.
execute TEST ('N', 35);
Both return the same amount of rows, order of execution does not matter, these
times are reproducible. If the same select statement is executed through JDBC it
takes roughly 6 seconds (execution time only, no data fetched at that point).
I'm a little bit at a loss here. Is such a slow down "expected", did we simply
miss that prepared statements are slower? Or is there something else going on
that we are simply not aware of?
