Re: [GENERAL] stack builder
Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons. On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/01/2015 06:22 AM, Ramesh T wrote: Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add*application stack builder* to existed postgres. Assuming you are talking about an install done using the EDB installer: http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table% 20of%20Contents.htm 4 Using Stack Builder -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] stack builder
Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add* application stack builder* to existed postgres.
Re: [GENERAL] stack builder
I had installed on Linux.re-install postgres for stack builder ..? cron is their ,but I am new to this cron and is this method for job schedulers in postgres. On Wed, Apr 1, 2015 at 8:39 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/01/2015 07:59 AM, Ramesh T wrote: Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons. You cannot, StackBuilder is not part of pgAdmin3, it is another application entirely. How did you install Postgres and what OS? On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 04/01/2015 06:22 AM, Ramesh T wrote: Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add*application stack builder* to existed postgres. Assuming you are talking about an install done using the EDB installer: http://www.enterprisedb.com/__docs/en/9.3/pginstguide/Table% __20of%20Contents.htm http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table% 20of%20Contents.htm 4 Using Stack Builder -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL]
Hi , How to create job in pgAgent.Where I need to place script in pgAgent..any help Advanced thanks...
Re: [GENERAL] Serializable transaction restart/re-execute
Hi Bill, thanks for the quick reply. I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and should use BEGIN/END blocks and EXCEPTIONs. Did you check the URL I mentioned? I have the code I used there: CREATE OR REPLACE FUNCTION myretest() RETURNS integer AS $$ DECLARE tries integer := 5; BEGIN WHILE TRUE LOOP BEGIN -- nested block for exception RETURN mytest(); EXCEPTION WHEN SQLSTATE '40001' THEN IF tries 0 THEN tries := tries - 1; RAISE NOTICE 'Restart! % left', tries; ELSE RAISE EXCEPTION 'NO RESTARTS LEFT'; END IF; END; END LOOP; END $$ LANGUAGE plpgsql; But it doesn't work.. Every iteration fails with serialization_failure probably because the outer transaction is not rolled back and I'm not sure how to write this in a way I can roll it back and still have control of the LOOP.. I find it hard to believe that PGSQL has this amazing serializable isolation method but not a standard way to take advantage of it to automatically restart the failed transactions... -Original Message- From: Bill Moran [mailto:wmo...@potentialtech.com] Sent: 3 de abril de 2015 23:07 To: Filipe Pina Cc: Postgresql General Subject: Re: [GENERAL] Serializable transaction restart/re-execute On Fri, 3 Apr 2015 15:35:14 +0100 Filipe Pina filipe.p...@impactzero.pt wrote: Hello, I come from a GTM background and once of the transactional features there are the ?Transaction Restarts?. Transaction restart is when we have two concurrent processes reading/writing to the same region/table of the database, the last process to commit will ?see? that the database is not the same as it was when the transaction started and goes back to the beginning of the transactional code and re-executes it. The closest I found to this in PGSQL is the Serializable transaction isolation mode and it does seem to work well except it simply throws an error (serialization_failure) instead of restarting. I?m trying to make use of this exception to implement restartable functions and I have all the examples and conditions mentioned here in a question in SO (without any answer so far?): http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-s erialization-failure http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for- serialization-failure So basically I have two questions: - the restartable ?wrapper? function never gets its ?DB view? refreshed once it restarts, I assume it?s because of the outter transaction (at function level) so it never re-reads the new values and keeps failing with serialization_failure.. Any way to solve this? - the ideal would be to be able to define this at database level so I wouldn?t have to implement wrappers for all functions.. Implementing a ?serialization_failure? generic handler that would simply re-call the function that threw that exception (up to a number of tries). Is this possible without going into pgsql source code? I suspect that savepoints will accomplish what you want: http://www.postgresql.org/docs/9.4/static/sql-savepoint.html -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: could not access status of transaction 283479860
We started getting the following error on some transactions on our database (when against particular rows run against the table). PGError: ERROR: could not access status of transaction 283479860 DETAIL: Could not open file pg_multixact/members/4D6D: No such file or directory. We don't know what happen or how to recover. Any advise? We are running PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit pg_controldata . pg_control version number:937 Catalog version number: 201306121 Database system identifier: 6049974226855122843 Database cluster state: in production pg_control last modified: Wed 01 Apr 2015 05:53:33 PM EDT Latest checkpoint location: 6D77/6D729948 Prior checkpoint location:6D77/6BBC9038 Latest checkpoint's REDO location:6D77/6BE0F0E0 Latest checkpoint's REDO WAL file:00016D77006B Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/2713180814 Latest checkpoint's NextOID: 1709773115 Latest checkpoint's NextMultiXactId: 318596530 Latest checkpoint's NextMultiOffset: 2059555326 Latest checkpoint's oldestXID:1746597315 Latest checkpoint's oldestXID's DB: 16415 Latest checkpoint's oldestActiveXID: 2713180814 Latest checkpoint's oldestMultiXid: 54760341 Latest checkpoint's oldestMulti's DB: 16415 Time of latest checkpoint:Wed 01 Apr 2015 05:50:01 PM EDT Fake LSN counter for unlogged rels: 0/1 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location:0/0 Backup end location: 0/0 End-of-backup record required:no Current wal_level setting:hot_standby Current max_connections setting: 600 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 -- Thank You, Pweaver (pwea...@panjiva.com)
[GENERAL] pgAgent
Hi, job_name = 'DELETE_NAMES' ,job_type = 'PLSQL_BLOCK' ,job_action = 'BEGIN DELETE_NAMES; END;' ,start_date = sysdate ,repeat_interval = 'FREQ=DAILY' ,enabled = TRUE); it is oracle .. .And i installed pgAgent On postgres .. how above oracle code can convert to postgres and place it in pgagent for daily schedule any help..?
Re: [GENERAL]
The link is good. But What I am expecting the following link..Created Using pgAgent. http://www.postgresonline.com/journal/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html in above link process they placed location of the script file at STEP DEFINTION TAB creation process.. same way is there a chance to place creation of table or delete statements in pgAgent process..? my aim to create job is delete some null data from table daily On Fri, Apr 3, 2015 at 3:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi http://stackoverflow.com/questions/4477301/creating-jobs-and-schedules-programatically-with-pgagent regards Pavel Stehule 2015-04-03 11:27 GMT+02:00 Ramesh T rameshparnandit...@gmail.com: Hi , How to create job in pgAgent.Where I need to place script in pgAgent..any help Advanced thanks...
Re: [GENERAL] ERROR: could not access status of transaction 283479860
Pweaver (Paul Weaver) wrote: We started getting the following error on some transactions on our database (when against particular rows run against the table). PGError: ERROR: could not access status of transaction 283479860 DETAIL: Could not open file pg_multixact/members/4D6D: No such file or directory. We don't know what happen or how to recover. Any advise? This looks like you overran pg_multixact/members files by wrapping around the 32 bit counter (NextMultiOffset in your pg_controldata output); a cleanup process later removed files that were still needed. This is a Postgres bug, I fear. Recovery is a bit complicated. I see that your report was made 5 days ago -- have you had any progress on getting things fixed since then? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- 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] pgAgent
Here you are:
do $$
declare
job_id int;
begin
/* add a job and get its id: */
insert into
pgagent.pga_job (
jobjclid
, jobname
)
values
(
1 /*1=Routine Maintenance*/
, 'DELETE_NAMES' /* job name */
)
returning
jobid
into
job_id;
/* add a step to the job: */
insert into
pgagent.pga_jobstep (jstjobid, jstname, jstkind, jstcode,
jstdbname)
values
(
job_id
, 'DELETE_NAMES' /* step name */
, 's'/* sql step */
, 'BEGIN DELETE_NAMES; END;' /* the sql to run */
, 'somedatabase' /* the name of the database
to run the step against */
);
/* add a schedule to the job. This one runs every day at midnight: */
insert into pgagent.pga_schedule (
jscjobid
, jscname
, jscdesc
, jscminutes
, jschours
, jscweekdays
, jscmonthdays
, jscmonths
, jscenabled
, jscstart
, jscend)
values(
job_id
, 'schedule name'
, ''
,
'{t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'
, '{t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'
, '{f,f,f,f,f,f,f}'
, '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'
, '{f,f,f,f,f,f,f,f,f,f,f,f}'
, true
, '2015-04-06 00:00:00'
, NULL
);
end $$;
On Thu, Apr 2, 2015 at 12:25 PM, Champion Always c2627...@gmail.com wrote:
Hi,
job_name = 'DELETE_NAMES'
,job_type = 'PLSQL_BLOCK'
,job_action = 'BEGIN DELETE_NAMES; END;'
,start_date = sysdate
,repeat_interval = 'FREQ=DAILY'
,enabled = TRUE);
it is oracle ..
.And i installed pgAgent On postgres ..
how above oracle code can convert to postgres and place it in pgagent for
daily schedule
any help..?
Re: [GENERAL] ERROR: could not access status of transaction 283479860
We figured that out. We were afraid that there was corrupt data due the counter overflow so we reverted to a backup right before the overflow. We submitted a bug this morning (bug #12990) to the Postgres bugs mailing list which has more information and questions. On Mon, Apr 6, 2015 at 2:17 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Pweaver (Paul Weaver) wrote: We started getting the following error on some transactions on our database (when against particular rows run against the table). PGError: ERROR: could not access status of transaction 283479860 DETAIL: Could not open file pg_multixact/members/4D6D: No such file or directory. We don't know what happen or how to recover. Any advise? This looks like you overran pg_multixact/members files by wrapping around the 32 bit counter (NextMultiOffset in your pg_controldata output); a cleanup process later removed files that were still needed. This is a Postgres bug, I fear. Recovery is a bit complicated. I see that your report was made 5 days ago -- have you had any progress on getting things fixed since then? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Thank You, Pweaver (pwea...@panjiva.com)
[GENERAL] Postgres fails to start
Hi All, I have experienced that Postgres services was not able to start, I tried from Services and command, and both were failed. Here are the logs I got, please help. (I am using postgres 9.1 and there are plenty disk space left). 2015-04-07 09:01:28 EST LOG: database system was interrupted while in recovery at 2015-04-07 08:53:41 EST 2015-04-07 09:01:28 EST HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2015-04-07 09:01:28 EST FATAL: the database system is starting up 2015-04-07 09:01:28 EST LOG: database system was not properly shut down; automatic recovery in progress 2015-04-07 09:01:28 EST LOG: redo starts at CD1/3C1414D0 2015-04-07 09:01:29 EST FATAL: the database system is starting up 2015-04-07 09:01:30 EST FATAL: the database system is starting up 2015-04-07 09:01:31 EST FATAL: the database system is starting up 2015-04-07 09:01:32 EST FATAL: the database system is starting up 2015-04-07 09:01:33 EST FATAL: the database system is starting up 2015-04-07 09:01:34 EST FATAL: the database system is starting up 2015-04-07 09:01:35 EST FATAL: the database system is starting up 2015-04-07 09:01:36 EST FATAL: the database system is starting up 2015-04-07 09:01:37 EST FATAL: the database system is starting up 2015-04-07 09:01:38 EST FATAL: the database system is starting up 2015-04-07 09:01:39 EST FATAL: the database system is starting up 2015-04-07 09:01:40 EST FATAL: the database system is starting up 2015-04-07 09:01:41 EST FATAL: the database system is starting up 2015-04-07 09:01:43 EST FATAL: the database system is starting up 2015-04-07 09:01:43 EST LOG: unrecognized win32 error code: 1392 2015-04-07 09:01:43 EST CONTEXT: xlog redo split_r: rel 1663/2008723533/2107262657 left 375921, right 379817, next 376394, level 0, firstright 95 2015-04-07 09:01:43 EST FATAL: could not extend file base/2008723533/2107262657.2: Invalid argument 2015-04-07 09:01:43 EST HINT: Check free disk space. 2015-04-07 09:01:43 EST CONTEXT: xlog redo split_r: rel 1663/2008723533/2107262657 left 375921, right 379817, next 376394, level 0, firstright 95 2015-04-07 09:01:44 EST LOG: startup process (PID 2652) exited with exit code 1 2015-04-07 09:01:44 EST LOG: aborting startup due to startup process failure Regards, Haiming Zhang Software Engineer If you are not an authorised recipient of this e-mail, please contact me at Redflex immediately by return phone call or by email. In this case, you should not read, print, retransmit, store or act in reliance on this e-mail or any attachments, and should destroy all copies of them. This e-mail and any attachments are confidential and may contain privileged information and/or copyright material of Redflex or third parties. You should only retransmit, distribute or commercialise the material if you are authorised to do so. This notice should not be removed.
Re: [GENERAL] Postgres fails to start
On Tue, Apr 7, 2015 at 10:46 AM, Haiming Zhang haiming.zh...@redflex.com.au wrote: Thank you for replying. The file was there, is that ok to remove the corrupted file to recover postgres? base/2008723533/2107262657.2 Be careful here, I would recommend taking a file-level snapshot before going on and do perhaps-stupid things. As that's a btree right split, perhaps you could recover your data by ignoring this index... Unfortunately, I only have a backup on February. Is there a way I can recover it without losing the recent data? What is lost is lost. A good backup strategy is essential. -- Michael -- 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] Postgres fails to start
On Tue, Apr 7, 2015 at 9:25 AM, Haiming Zhang haiming.zh...@redflex.com.au wrote: 2015-04-07 09:01:43 EST LOG: unrecognized win32 error code: 1392 [...] In Windows world, 1392 = ERROR_FILE_CORRUPT = the file or directory is corrupted and unreadable: https://msdn.microsoft.com/en-us/library/windows/desktop/ms681385%28v=vs.85%29.aspx So first check your file system, and be sure that you have a backup around. -- Michael -- 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] Postgres fails to start
Hi Michael, Thank you for replying. The file was there, is that ok to remove the corrupted file to recover postgres? base/2008723533/2107262657.2 Unfortunately, I only have a backup on February. Is there a way I can recover it without losing the recent data? Thanks and regards, Haiming -Original Message- From: Michael Paquier [mailto:michael.paqu...@gmail.com] Sent: Tuesday, 7 April 2015 11:36 AM To: Haiming Zhang Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres fails to start On Tue, Apr 7, 2015 at 9:25 AM, Haiming Zhang haiming.zh...@redflex.com.au wrote: 2015-04-07 09:01:43 EST LOG: unrecognized win32 error code: 1392 [...] In Windows world, 1392 = ERROR_FILE_CORRUPT = the file or directory is corrupted and unreadable: https://msdn.microsoft.com/en-us/library/windows/desktop/ms681385%28v=vs.85%29.aspx So first check your file system, and be sure that you have a backup around. -- Michael If you are not an authorised recipient of this e-mail, please contact me at Redflex immediately by return phone call or by email. In this case, you should not read, print, retransmit, store or act in reliance on this e-mail or any attachments, and should destroy all copies of them. This e-mail and any attachments are confidential and may contain privileged information and/or copyright material of Redflex or third parties. You should only retransmit, distribute or commercialise the material if you are authorised to do so. This notice should not be removed. -- 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] Serializable transaction restart/re-execute
Hi Bill, thanks for the quick reply. I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and should use BEGIN/END blocks and EXCEPTIONs. Did you check the URL I mentioned? I have the code I used there: CREATE OR REPLACE FUNCTION myretest() RETURNS integer AS $$ DECLARE tries integer := 5; BEGIN WHILE TRUE LOOP BEGIN -- nested block for exception RETURN mytest(); EXCEPTION WHEN SQLSTATE '40001' THEN IF tries 0 THEN tries := tries - 1; RAISE NOTICE 'Restart! % left', tries; ELSE RAISE EXCEPTION 'NO RESTARTS LEFT'; END IF; END; END LOOP; END $$ LANGUAGE plpgsql; But it doesn't work.. Every iteration fails with serialization_failure probably because the outer transaction is not rolled back and I'm not sure how to write this in a way I can roll it back and still have control of the LOOP.. I find it hard to believe that PGSQL has this amazing serializable isolation method but not a standard way to take advantage of it to automatically restart the failed transactions... On Fri, Apr 3, 2015 at 11:07 PM, Bill Moran wmo...@potentialtech.com wrote: On Fri, 3 Apr 2015 15:35:14 +0100 Filipe Pina filipe.p...@impactzero.pt wrote: Hello, I come from a GTM background and once of the transactional features there are the ?Transaction Restarts?. Transaction restart is when we have two concurrent processes reading/writing to the same region/table of the database, the last process to commit will ?see? that the database is not the same as it was when the transaction started and goes back to the beginning of the transactional code and re-executes it. The closest I found to this in PGSQL is the Serializable transaction isolation mode and it does seem to work well except it simply throws an error (serialization_failure) instead of restarting. I?m trying to make use of this exception to implement restartable functions and I have all the examples and conditions mentioned here in a question in SO (without any answer so far?): http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure So basically I have two questions: - the restartable ?wrapper? function never gets its ?DB view? refreshed once it restarts, I assume it?s because of the outter transaction (at function level) so it never re-reads the new values and keeps failing with serialization_failure.. Any way to solve this? - the ideal would be to be able to define this at database level so I wouldn?t have to implement wrappers for all functions.. Implementing a ?serialization_failure? generic handler that would simply re-call the function that threw that exception (up to a number of tries). Is this possible without going into pgsql source code? I suspect that savepoints will accomplish what you want: http://www.postgresql.org/docs/9.4/static/sql-savepoint.html -- Bill Moran
Re: [GENERAL] Serializable transaction restart/re-execute
Filipe Pina filipe.p...@impactzero.pt wrote: I come from a GTM background and once of the transactional features there are the “Transaction Restarts”. Transaction restart is when we have two concurrent processes reading/writing to the same region/table of the database, the last process to commit will “see” that the database is not the same as it was when the transaction started and goes back to the beginning of the transactional code and re-executes it. The closest I found to this in PGSQL is the Serializable transaction isolation mode and it does seem to work well except it simply throws an error (serialization_failure) instead of restarting. Right, serializable transactions provide exactly what you are looking for as long as you use some framework that starts the transaction over when it receives an error with a SQLSTATE of 40001 or 40P01. I’m trying to make use of this exception to implement restartable functions and I have all the examples and conditions mentioned here in a question in SO (without any answer so far…): http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure Perhaps once we've sorted out the issue here I can post an answer there for the benefit of anyone finding the SO question. So basically I have two questions: - the restartable “wrapper” function never gets its “DB view” refreshed once it restarts, I assume it’s because of the outter transaction (at function level) so it never re-reads the new values and keeps failing with serialization_failure.. Any way to solve this? In PostgreSQL a function always runs in the context of a transaction. You can't start a new transaction within the context of a wrapper function. That would require a slightly different feature, which is commonly called a stored procedure -- something which doesn't exist in PostgreSQL. Therefore, you need to put the logic to manage the restart into code which submits the transaction to the database. Fortunately, there are many connectors for that -- Java, perl, python, tcl, ODBC, etc. There is even a connector for making a separate connection to a PostgreSQL database within PostgreSQL procedural language, which might allow you to do something like what you want: http://www.postgresql.org/docs/current/static/dblink.html - the ideal would be to be able to define this at database level so I wouldn’t have to implement wrappers for all functions.. I have seen this done in various client frameworks. Clearly it is a bad idea to spread this testing around to all locations where the application is logically dealing with the database, but there are many good reasons to route all database requests through one accessor method (or at least a very small number of them), and most frameworks provide a way to deal with this at that layer. (For example, in Spring you would want to create a transaction manager using dependency injection.) Implementing a “serialization_failure” generic handler that would simply re-call the function that threw that exception (up to a number of tries). Is this possible without going into pgsql source code? Yes, but only from the client side of a database connection -- although that client code. That probably belongs in some language you are using for your application logic, but if you really wanted to you could use plpgsql and dblink. It's hard for me to see a case where that would actually be a good idea, but it is an option. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Serializable transaction restart/re-execute
On Mon, 6 Apr 2015 10:41:25 +0100 Filipe Pina fop...@impactzero.pt wrote: Hi Bill, thanks for the quick reply. I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and should use BEGIN/END blocks and EXCEPTIONs. Did you check the URL I mentioned? Yes, I did: http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure ... But it doesn't work.. Every iteration fails with serialization_failure probably because the outer transaction is not rolled back and I'm not sure how to write this in a way I can roll it back and still have control of the LOOP.. Probably one of your issues is that there is no such thing as an outer transaction. There's just a transaction. There is no nesting of transactions, so the belief that there is an outer transaction that can somehow be manipulated indepently of some other transaction is leading you to try things that will never work. I wasn't aware that SAVEPOINTs didn't work in pl/pgsql, thanks for educating me on that point. I find it hard to believe that PGSQL has this amazing serializable isolation method but not a standard way to take advantage of it to automatically restart the failed transactions... I've been over this ground before. You're thinking in such a micro case that you haven't realized the inherent difficulty of restarting large transactions with lots of data modification. An RDBMS may have many tables updated within a transaction, and transactions may do data processing completely outside of the database, which means the only way to ensure consistency is to notify the controlling process of the problem so it can decide how best to respond. So ... I dug into your problem a little more, and I think the problem is that you're trying too hard to replicate GTM design paradigms instead of learning the way that PostgreSQL is designed to work. If I were creating the functions you describe, I would ditch the second one and simply have this: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ BEGIN update account set balance = balance+10 where id=1 RETURNING balance; END $$ LANGUAGE SQL; of course, it's unlikely that you'll ever want to wrap such a simple query in a function, so I'm supposing that you'd want to do something else with the old value of balance before updating it, in which case: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ DECLARE cc integer; BEGIN SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE; RAISE NOTICE 'Balance: %', cc; perform pg_sleep(3); update account set balance = cc+10 where id=1 RETURNING balance INTO cc; return cc; END $$ LANGUAGE plpgsql; The FOR UPDATE ensures that no other process can modify the row while this one is sleeping. Now, I understand that you want to don't want to do row locking, but this is (again) an insistance on your part of trying to force PostgreSQL to do things the way GTM did instead of understanding the RDBMS way of doing things. Unlearn. Keep in mind that mytest() might be called as part of a much larger transaction that does many other things, and you can't simply roll that back and restart it within mytest() since mytest() doesn't know everything else that happened. In you're case, you're trying to look at mytest() as something that will always be used in a specific way where the aforementioned problem won't be encountered, but you can not guarantee that, and it doesn't hold true for all functions. In general, it's inappropriate for a function to be able to manipulate a transaction beyond aborting it. And the abort has to bubble up so that other statements involved in the transaction are also notified. -- Bill Moran -- 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] Serializable transaction restart/re-execute
Thank you very much for such detailed response. Indeed I'm thinking too much GTM instead of actually changing the mindset, but the problem with LOCKs (which are also available in GTM) is that the developer does have to remind to lock what they want to use for update and if they don't, integrity/consistency issues might come up (or even data loss which would be worse..). Serializable isolation would make sure developers don't need to remember that while keeping that consistency (though losing some performance) and also they wouldn't have to worry about deadlocks (as serializable use soft locks). But I guess strong code review and code re-design (where needed) should be able have an even better solution, that's true. From what I have read so far, I can't find a way to cause this restarts (besides patching pgsql itself which I wouldn't know where to start). Thanks once again. On Mon, Apr 6, 2015 at 12:42 PM, Bill Moran wmo...@potentialtech.com wrote: On Mon, 6 Apr 2015 10:41:25 +0100 Filipe Pina fop...@impactzero.pt wrote: Hi Bill, thanks for the quick reply. I had read about SAVEPOINTs but I also read I cannot use them in PLPGSQL and should use BEGIN/END blocks and EXCEPTIONs. Did you check the URL I mentioned? Yes, I did: http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure ... But it doesn't work.. Every iteration fails with serialization_failure probably because the outer transaction is not rolled back and I'm not sure how to write this in a way I can roll it back and still have control of the LOOP.. Probably one of your issues is that there is no such thing as an outer transaction. There's just a transaction. There is no nesting of transactions, so the belief that there is an outer transaction that can somehow be manipulated indepently of some other transaction is leading you to try things that will never work. I wasn't aware that SAVEPOINTs didn't work in pl/pgsql, thanks for educating me on that point. I find it hard to believe that PGSQL has this amazing serializable isolation method but not a standard way to take advantage of it to automatically restart the failed transactions... I've been over this ground before. You're thinking in such a micro case that you haven't realized the inherent difficulty of restarting large transactions with lots of data modification. An RDBMS may have many tables updated within a transaction, and transactions may do data processing completely outside of the database, which means the only way to ensure consistency is to notify the controlling process of the problem so it can decide how best to respond. So ... I dug into your problem a little more, and I think the problem is that you're trying too hard to replicate GTM design paradigms instead of learning the way that PostgreSQL is designed to work. If I were creating the functions you describe, I would ditch the second one and simply have this: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ BEGIN update account set balance = balance+10 where id=1 RETURNING balance; END $$ LANGUAGE SQL; of course, it's unlikely that you'll ever want to wrap such a simple query in a function, so I'm supposing that you'd want to do something else with the old value of balance before updating it, in which case: CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $$ DECLARE cc integer; BEGIN SELECT INTO cc balance FROM account WHERE id = 1 FOR UPDATE; RAISE NOTICE 'Balance: %', cc; perform pg_sleep(3); update account set balance = cc+10 where id=1 RETURNING balance INTO cc; return cc; END $$ LANGUAGE plpgsql; The FOR UPDATE ensures that no other process can modify the row while this one is sleeping. Now, I understand that you want to don't want to do row locking, but this is (again) an insistance on your part of trying to force PostgreSQL to do things the way GTM did instead of understanding the RDBMS way of doing things. Unlearn. Keep in mind that mytest() might be called as part of a much larger transaction that does many other things, and you can't simply roll that back and restart it within mytest() since mytest() doesn't know everything else that happened. In you're case, you're trying to look at mytest() as something that will always be used in a specific way where the aforementioned problem won't be encountered, but you can not guarantee that, and it doesn't hold true for all functions. In general, it's inappropriate for a function to be able to manipulate a transaction beyond aborting it. And the abort has to bubble up so that other statements involved in the transaction are also notified. -- Bill Moran
Re: [GENERAL] Serializable transaction restart/re-execute
Hi Kevin, thank you very much for reply. We plan to have a middleware/gateway in our full solution so we could have the restart logic there but that would only apply to external interface calls. We plan to have a few backend processes that we want to run directly in pgsql and those would not have restarts.. dblink does sound like a decent option/workaround but I'm guessing everything points toward focusing on locks instead of relying on some hacky serializable failure restart implementation.. If you post this reply in the SO post I found quite helpful and insightful and I'll definitely accept it as answer. If you have the time to elaborate on a working example using dblink it would definitely by a nice bonus :) Thank you once again On Mon, Apr 6, 2015 at 3:22 PM, Kevin Grittner kgri...@ymail.com wrote: Filipe Pina filipe.p...@impactzero.pt wrote: I come from a GTM background and once of the transactional features there are the “Transaction Restarts”. Transaction restart is when we have two concurrent processes reading/writing to the same region/table of the database, the last process to commit will “see” that the database is not the same as it was when the transaction started and goes back to the beginning of the transactional code and re-executes it. The closest I found to this in PGSQL is the Serializable transaction isolation mode and it does seem to work well except it simply throws an error (serialization_failure) instead of restarting. Right, serializable transactions provide exactly what you are looking for as long as you use some framework that starts the transaction over when it receives an error with a SQLSTATE of 40001 or 40P01. I’m trying to make use of this exception to implement restartable functions and I have all the examples and conditions mentioned here in a question in SO (without any answer so far…): http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure Perhaps once we've sorted out the issue here I can post an answer there for the benefit of anyone finding the SO question. So basically I have two questions: - the restartable “wrapper” function never gets its “DB view” refreshed once it restarts, I assume it’s because of the outter transaction (at function level) so it never re-reads the new values and keeps failing with serialization_failure.. Any way to solve this? In PostgreSQL a function always runs in the context of a transaction. You can't start a new transaction within the context of a wrapper function. That would require a slightly different feature, which is commonly called a stored procedure -- something which doesn't exist in PostgreSQL. Therefore, you need to put the logic to manage the restart into code which submits the transaction to the database. Fortunately, there are many connectors for that -- Java, perl, python, tcl, ODBC, etc. There is even a connector for making a separate connection to a PostgreSQL database within PostgreSQL procedural language, which might allow you to do something like what you want: http://www.postgresql.org/docs/current/static/dblink.html - the ideal would be to be able to define this at database level so I wouldn’t have to implement wrappers for all functions.. I have seen this done in various client frameworks. Clearly it is a bad idea to spread this testing around to all locations where the application is logically dealing with the database, but there are many good reasons to route all database requests through one accessor method (or at least a very small number of them), and most frameworks provide a way to deal with this at that layer. (For example, in Spring you would want to create a transaction manager using dependency injection.) Implementing a “serialization_failure” generic handler that would simply re-call the function that threw that exception (up to a number of tries). Is this possible without going into pgsql source code? Yes, but only from the client side of a database connection -- although that client code. That probably belongs in some language you are using for your application logic, but if you really wanted to you could use plpgsql and dblink. It's hard for me to see a case where that would actually be a good idea, but it is an option. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
