Re: [SQL] how to perform minus (-) operation in a dynamic query
Hi, Anoop G wrote: vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; Simply use a real minus sign '-', and not a hyphen '–'. (Try copy'n'pasting from this email, if nothing else works ;-) ) Regards Markus -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to perform minus (-) operation in a dynamic query
"Anoop G" <[EMAIL PROTECTED]> writes: > ERROR: syntax error at or near "\226" at character 18 > QUERY: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * comm/100) as > flt_claim > CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement > LINE 1: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * comm/100) > as... I'm not sure what character \226 is, but it's not a minus sign ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
I didnt no the thread would become a postgresVSoracle thing. I just lost couple of thousand rows and could not retrieve them back, so i wanted to know if postgres had some way to get it back. Iam just a few days expereinced in postgres hence iam still discovering its features. No intention of comparing the two technologies..just trying find a solution and ended up comparing because i had worked in oracle before and very well knewit provide a rollback option for queries. I dont see anything wrong in knowing what features oracle has. Peace Sam On 7/12/08, Lewis Cunningham <[EMAIL PROTECTED]> wrote: > > --- On Sat, 7/12/08, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > What I would appreciate as regards Oracle's flashback > > technology would > > have been a link to a well written review showing the warts > > as well as > > the beauty. I've found that Oracle stuff sounds good > > on paper, and > > turns into a giant maintenance nightmare upon deployment. > > But that's > > just what I've seen looking over Oracle DBA shoulders > > in the past. > > Oracle-base is a site I trust and use. Tim writes very good articles and > this is one he did recently covering flashback in 11g. The example on > flashback transaction is the best I've seen. > > > http://www.oracle-base.com/articles/11g/FlashbackAndLogminerEnhancements_11gR1.php > > > Lewis R Cunningham > > An Expert's Guide to Oracle Technology > http://blogs.ittoolbox.com/oracle/guide/ > > Postgres Forums > http://postgres.enterprisedb.com/forum.do > > > > > >
Re: [SQL] Rollback in Postgres
On Mon, Jul 14, 2008 at 9:20 AM, samantha mahindrakar <[EMAIL PROTECTED]> wrote: > I didnt no the thread would become a postgresVSoracle thing. I just lost > couple of thousand rows and could not retrieve them back, so i wanted to > know if postgres had some way to get it back. Iam just a few days > expereinced in postgres hence iam still discovering its features. > No intention of comparing the two technologies..just trying find a > solution and ended up comparing because i had worked in oracle before and > very well knewit provide a rollback option for queries. > I dont see anything wrong in knowing what features oracle has. Much like the processes that make up postgresql, the discussion can fork in any number of directions. :) I just lost a months worth of stats data myself, so join the club. It wasn't critical data, but it would have been nice to have kept around... -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
> I just lost a months worth of stats data myself, so join the club. It > wasn't critical data, but it would have been nice to have kept > around... I also think there could be a TODO item in it. If vacuum instead of removing items, somehow stashed them away in a storage limited archive it would be possible to do a SELECT...AS OF TIMESTAMP. The idea is of course to be able to retrieve rows that really are deleted, but are still on disk as non-vacuumed or vacuumed and not removed completely. And it would also take a 2. stage vacuumer to keep the storage within its limits. I don't say it's an important feature, but it would come in handy for people who really really need it. And perhaps a developer wouldn't mind scratching this itch some time in the future. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, Jul 14, 2008 at 12:59 PM, Kaare Rasmussen <[EMAIL PROTECTED]> wrote: > I also think there could be a TODO item in it. If vacuum instead of removing > items, somehow stashed them away in a storage limited archive it would be > possible to do a SELECT...AS OF TIMESTAMP. This sounds a lot like the functionality that a temporal data model would give you. In this model you never delete tuples from your database, your only insert and update tuples that are valid for specific periods of time. If you want to contribute development time, I would check out postgresql's temporal db project on PGfoundry. This project is just getting started and could benefit from a lot of development help. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
Kaare Rasmussen escribió: > I don't say it's an important feature, but it would come in handy for people > who really really need it. And perhaps a developer wouldn't mind scratching > this itch some time in the future. It would need to be enabled beforehand, and most people I've seen for which "it would come in handy" wouldn't have enabled it. (FWIW this feature used to exist in the Berkeley code, under the cool name "time travel", and was removed a long time ago.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 21:59 +0200, Kaare Rasmussen wrote: > > I just lost a months worth of stats data myself, so join the club. It > > wasn't critical data, but it would have been nice to have kept > > around... > > I also think there could be a TODO item in it. If vacuum instead of removing > items, somehow stashed them away in a storage limited archive it would be > possible to do a SELECT...AS OF TIMESTAMP. > > The idea is of course to be able to retrieve rows that really are deleted, > but > are still on disk as non-vacuumed or vacuumed and not removed completely. And > it would also take a 2. stage vacuumer to keep the storage within its limits. I've got the design all worked out for this. The "only" thing we need is a VACUUM that will remove unseen data from within the middle of the sum-of-all-snapshots, if there is a gap. At the moment we never remove rows beyond global xmin, but we could iff the transactions at xmin promise never to update data. That should go on the TODO list as a precursor. Some discussion required :-) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
> which "it would come in handy" wouldn't have enabled it. (FWIW this > feature used to exist in the Berkeley code, under the cool name "time > travel", and was removed a long time ago.) No, it didn't AFAIK. Timetravel kept all tuples in the database with all indexes and constraints active at all time. That's not the case with the flashback technology. You put aside some storage space that you don't need for something else. When that space is spent, tuples start dropping off the edge. I've talked to people who was very much happy with this feature. Mostly DBA's recovering from their own stupid mistakes of course :-) But yes, it has to be enabled, and yes it has to have a performance cost somehow, but people are requesting it, and somehow I don't think Oracle developed the feature just for fun. If you plug into Postgres' vacuum it would be rather cheap to make, I recon. I wouldn't worry about query speed as I guess that the use cases for retrieving already deleted rows don't aren't performance dependant. -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
> This sounds a lot like the functionality that a temporal data model > would give you. In this model you never delete tuples from your > database, your only insert and update tuples that are valid for > specific periods of time. Isn't this exactly what Alvaro describes? The time travel feature that was removed because it made Postgres too slow to use in production? -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, Jul 14, 2008 at 1:38 PM, Kaare Rasmussen <[EMAIL PROTECTED]> wrote: > Isn't this exactly what Alvaro describes? The time travel feature that was > removed because it made Postgres too slow to use in production? No, I imagine that time travel was built into the Postgresql architecture and would work automatically with transaction ids and tuple ids. On the other hand, temporal tables/schemes are implemented by the data modeller. Also the associated temporal operations on the data would be handled by client DML designed to simulate temporal data operations. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
--- On Mon, 7/14/08, Kaare Rasmussen <[EMAIL PROTECTED]> wrote: > But yes, it has to be enabled, and yes it has to have a > performance cost > somehow, but people are requesting it, and somehow I AFAIK, It is built from undo so there is no ADDITIONAL overhead. It just saves the undo that is created anyway for any DML anyway. That undo is already on disk. Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ Database Wisdom http://databasewisdom.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
Lewis Cunningham wrote: > --- On Mon, 7/14/08, Kaare Rasmussen <[EMAIL PROTECTED]> wrote: > > > But yes, it has to be enabled, and yes it has to have a > > performance cost > > somehow, but people are requesting it, and somehow I > > AFAIK, It is built from undo so there is no ADDITIONAL overhead. It > just saves the undo that is created anyway for any DML anyway. That > undo is already on disk. Which means it doesn't work for us, because we don't have UNDO (we only have REDO). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 22:38 +0200, Kaare Rasmussen wrote: > > This sounds a lot like the functionality that a temporal data model > > would give you. In this model you never delete tuples from your > > database, your only insert and update tuples that are valid for > > specific periods of time. > > Isn't this exactly what Alvaro describes? The time travel feature that was > removed because it made Postgres too slow to use in production? Similar. Performance is the issue to be solved with row removal, yes. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
Kaare Rasmussen <[EMAIL PROTECTED]> writes: > But yes, it has to be enabled, and yes it has to have a performance cost > somehow, but people are requesting it, and somehow I don't think Oracle > developed the feature just for fun. No, they developed it for marketing. Keep in mind that Oracle has six thousand full-time developers and an already extremely mature database. Stuff that they see fit to add is not necessarily going to be on our radar screen in the foreseeable future. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Kaare Rasmussen <[EMAIL PROTECTED]> writes: >> But yes, it has to be enabled, and yes it has to have a performance cost >> somehow, but people are requesting it, and somehow I don't think Oracle >> developed the feature just for fun. > > No, they developed it for marketing. No, they developed it because it was needed. In addition to knowing quite a bit about the design and implementation of this feature, I've been a production Oracle DBA and can speak from experience. In fact, one of the primary reasons for creating this feature was for the very purpose of why the original poster needed it, human-induced disasters/mistakes. While flashback does give you the ability to perform temporal-related queries, it was designed to allow recovery of individual database objects (or the entire database itself) to a certain point in time, thereby giving DBAs the ability to undo changes (intentional or otherwise). > Keep in mind that Oracle has six thousand full-time developers and an > already extremely mature database. True. > Stuff that they see fit to add is not necessarily going to be on our radar > screen in the foreseeable future. Agreed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to perform minus (-) operation in a dynamic query
Hai all, Thanks to all , I got the answer, actualy I am write the function in openoffice.org and paste it to a .sql file that is the reason for the error. thanks Anoop On Mon, Jul 14, 2008 at 7:32 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anoop G" <[EMAIL PROTECTED]> writes: > > ERROR: syntax error at or near "\226" at character 18 > > QUERY: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * > comm/100) as > > flt_claim > > CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute > statement > > LINE 1: SELECT mf,sf,(mf \226 mf * comm /100) \226 (sf \226 sf * > comm/100) as... > > I'm not sure what character \226 is, but it's not a minus sign ... > >regards, tom lane >
[SQL] COPY equivalent for updates
Is there a COPY equivalent for updates? eg I've create table t1 ( id int primary key, col1 int, col2 int, col3 varchar(32) ); and a CSV file 10,4,5,"abc" 13,7,3,"def" 18,12,77,"ghi" I'd like to UPDATE t1 (col1, col2, col3) from file with @1 as primary key; or UPDATE t1 (col1, col2, col3) from file where @1=id; sort of... Otherwise what is the fastest approach? I can think of 2 approaches: 1)load a temp table with COPY update t1 set col1=temp_t1.col1, col2=temp_t1.col2 where t1.id=temp_t1.id; 2) use awk to generate update statements. Supposing I could neglect the awk execution time, will COPY + UPDATE be faster than executing a list of UPDATE? Considering I've to deal with a where clauses anyway... when (and if) should I create an index on the id of temp_t1? t1 will contain 700-1M records while I may update a maximum of 20K a time. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 22:54 -0400, Jonah H. Harris wrote: > On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > Kaare Rasmussen <[EMAIL PROTECTED]> writes: > >> But yes, it has to be enabled, and yes it has to have a performance cost > >> somehow, but people are requesting it, and somehow I don't think Oracle > >> developed the feature just for fun. > > > > No, they developed it for marketing. > > No, they developed it because it was needed. I agree such improvements would be welcomed. I'm pretty sure they sat around saying we can already do that some other way at first, until the requests started to pile up. > > Stuff that they see fit to add is not necessarily going to be on our radar > > screen in the foreseeable future. I'm not clear on why there should be an inherent delay. I think PostgreSQL adoption is mostly held back by operational features, like performance management, locking, backup. But we're mainly constrained on people's time, i.e. money. And AFAICS nothing like this is going to happen in this release. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
