Re: [GENERAL] #Personal#: Reg: Multiple queries in a transaction
David G Johnston wrote Medhavi Mahansaria wrote Hi Bill, Thanks! But savepoint concept will not work for me as desired. Is there any other way apart from SAVEPOINT that can be incorporated. I am not using a script. I am writing a c++ program. My problem is that I have 2 cases: Case 1: When Q2 fails (we delete the error), i want to continue to Q3 and commit changes done by Q1 and Q3 once Q3 has executed successfully. Case 2: When Q2 fails, I want it to throw an error. and rollback the changes made by Q1 and not proceed to Q3 at all. Note: This is just a small example. I need a solution for an entire application which follows the same concept across multiple queries. How can I incorporate this? Forgo transactions or use savepoints. Those are your tools. If you cannot find a way to solve your problem with those tools you either need to choose, or build, a different toolbox or explain your actual problem in greater detail so that others can see if there are solutions you are overlooking. Or redefine your problem. David J. You might be able to write the code in pl/pgsql and just call it from your application. You have a bit more options for flow control in that compared to pure SQL. http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING David J. -- View this message in context: http://postgresql.nabble.com/Personal-Reg-Multiple-queries-in-a-transaction-tp5838427p5838540.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] select where true, or select where input = '$var'
i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. 1 select * 2 from table 3 if input = '' then 4 where true 5 else 6 where input = '$sanitized_variable' 7 end if; (syntax error at 3) i also looked at 'case' but i don't think it applies here. http://www.postgresql.org/docs/9.3/static/functions-conditional.html http://dba.stackexchange.com/questions/41067/getting-select-to-return-a-constant-value-even-if-zero-rows-match -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] rollback in C functions
Hello, i have created a function (in C) that receives an array that contains tuples of ID's and values. The function is to execute updates on each ID assigning the value, but if one of these operation fails (does not meet certain criteria) inside the function i would like to rollback and leave everything untouched, in case other ID;s were already updated previously, and come back to the caller and inform about it. I have read all over that it is not posible to do rollback inside a function because each function is executed inside a transaction so inside the function you dont have control over BEGIN/ROLLBACK, but i m sure there is a way to do this, can anyone please give me a hint how this is accomplished ? thank you!!!
Re: [GENERAL] select where true, or select where input = '$var'
i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. I think you can just use OR: SELECT * FROMtable WHERE (input = '' OR input = ?) This is assuming that `input` is a column in your table and ? is the user input, based on the query you provided. But are you sure that's what you mean? Also, if the `input` column can contain nulls you might also want: SELECT * FROMtable WHERE (input IS NULL OR input = '' OR input = ?) Paul -- 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 where true, or select where input = '$var'
On 2/19/2015 12:39 PM, zach cruise wrote: i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. (metalanguage) if input is provided, then query(SELECT stuff FROM table WHERE whatever = $INPUT) else, query(SELECT stuff FROM table) in other words, make the decision as to what query to execute OUTSIDE of sql by invoking different queries based on your application's 'input'. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] rollback in C functions
The function is to execute updates on each ID assigning the value, but if one of these operation fails (does not meet certain criteria) inside the function i would like to rollback and leave everything untouched, in case other ID;s were already updated previously, and come back to the caller and infor Hi, I think you want ereport(), here is an example: http://www.postgresql.org/docs/9.3/static/xfunc-c.html Bye, Chris. -- 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] rollback in C functions
Hi Thanks Chris, yes i have already tested ereport and even made up my own sql state to report and error but from the application i can not access this error code directly (through the PQexec,PQresultErrorField,etc functions because the PGresult returns NULL) but i have to register a callback function that will be called with this error code with the idea to format the error message so i could intercept this and do something, but this does not allow me to have direct access to the flow where this is all happening .. i hope it is clear ... On 19 February 2015 at 15:02, Chris Mair ch...@1006.org wrote: The function is to execute updates on each ID assigning the value, but if one of these operation fails (does not meet certain criteria) inside the function i would like to rollback and leave everything untouched, in case other ID;s were already updated previously, and come back to the caller and infor Hi, I think you want ereport(), here is an example: http://www.postgresql.org/docs/9.3/static/xfunc-c.html Bye, Chris.
Re: [GENERAL] select where true, or select where input = '$var'
On 19 Feb 2015, at 21:39, zach cruise zachc1...@gmail.com wrote:
i want to select based on input, but if input is not provided or if
input is empty, then i want to select all rows.
1 select *
2 from table
3 if input = '' then
4 where true
5 else
6 where input = '$sanitized_variable'
7 end if;
(syntax error at 3)
Well yeah, SQL doesn't have an if-statement and you don't need one here:
select *
from table
where ('$sanitized_variable' = '' and input is null)
or ('$sanitized_variable' '' and input = '$sanitized_variable');
That can be shortened, but I think the message is clearer this way.
Question though, when do you consider input empty? Is that when input = '' or
when input is null?
In the latter case, what's the correct behaviour when '$sanitized_variable' =
''?
Cheers.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find 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
Re: [GENERAL] Failure loading materialized view with pg_restore
On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote: Brian Sutherland br...@vanguardistas.net writes: If I run this set of commands against PostgreSQL 9.4.1 I pg_restore throws an error with a permission problem. Why it does so is a mystery to me, given that the user performing the restore is a superuser: The same thing would happen without any dump and restore: regression=# create user nobody; CREATE ROLE regression=# CREATE TABLE x (y int); CREATE TABLE regression=# CREATE MATERIALIZED VIEW myview AS select * from x; SELECT 0 regression=# ALTER TABLE myview OWNER TO nobody; ALTER TABLE regression=# REFRESH MATERIALIZED VIEW myview; ERROR: permission denied for relation x User nobody does not have permission to read table x, so the REFRESH fails, because the view's query executes as the view's owner. If you grant select permission for the user nobody on x, pg_restore still fails even though a REFRESH succeeds: # superuser creates database and materialized view createuser -s super createdb --username super orig psql --username super -c select 'USING:' || version(); orig psql --username super -c 'CREATE TABLE x (y int);' orig psql --username super -c 'CREATE MATERIALIZED VIEW myview AS select * from x' orig # change the owner of the view to myview and grant SELECT to nobody createuser -S nobody psql --username super -c 'GRANT SELECT ON x TO nobody' orig psql --username super -c 'ALTER TABLE myview OWNER TO nobody;' orig # refresh does work if you are nobody psql --username nobody -c 'REFRESH MATERIALIZED VIEW myview;' orig # dump and reload pg_dump --username super --format c -f dump.dump orig createdb copied # pg_restore errors pg_restore --username super -d copied dump.dump I guess I provided a too-minimal example... -- Brian Sutherland -- 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] #Personal#: Reg: Multiple queries in a transaction
On Thu, Feb 19, 2015 at 11:11:59AM +0530, Medhavi Mahansaria wrote: But savepoint concept will not work for me as desired. I don't see why not. Case 1: When Q2 fails (we delete the error), i want to continue to Q3 and commit changes done by Q1 and Q3 once Q3 has executed successfully. So, Q1; SAVEPOINT foo; Q2; if error then ROLLBACK TO SAVEPOINT FOO; Q3; COMMIT or ROLLBACK; else COMMIT; Case 2: When Q2 fails, I want it to throw an error. and rollback the changes made by Q1 and not proceed to Q3 at all. Q1; SAVEPOINT foo; Q2; if error then ROLLBACK; These both work. The problem is, I think, that you have different rules for when Q2 fails, and without knowing your exact circumstances I suspect we can't say much more. Indeed, however, it sounds to me like you think these are in the same workflow, but they're not. 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] #Personal#: Reg: Multiple queries in a transaction
On Thu, 19 Feb 2015 11:12:38 +0530 Medhavi Mahansaria medhavi.mahansa...@tcs.com wrote: Hi Bill, Thanks! But savepoint concept will not work for me as desired. Why not? The scenerio you describe below can be perfectly implemented using savepoints. Describe in more detail, please, why savepoints won't accomplish it, otherwise I'm not sure I can offer any better suggestions. Is there any other way apart from SAVEPOINT that can be incorporated. I am not using a script. I am writing a c++ program. My problem is that I have 2 cases: Case 1: When Q2 fails (we delete the error), i want to continue to Q3 and commit changes done by Q1 and Q3 once Q3 has executed successfully. Case 2: When Q2 fails, I want it to throw an error. and rollback the changes made by Q1 and not proceed to Q3 at all. Note: This is just a small example. I need a solution for an entire application which follows the same concept across multiple queries. How can I incorporate this? Thanks Regards Medhavi Mahansaria Tata Consultancy Services Limited Unit-VI, No.78, 79 83, L-Centre, EPIP Industrial Estate, Whitefield Bangalore - 560066,Karnataka India Ph:- +91 80 67253769 Cell:- +91 9620053040 Mailto: medhavi.mahansa...@tcs.com Website: http://www.tcs.com Experience certainty. IT Services Business Solutions Consulting -Bill Moran wmo...@potentialtech.com wrote: - To: Medhavi Mahansaria medhavi.mahansa...@tcs.com From: Bill Moran wmo...@potentialtech.com Date: 02/18/2015 09:23PM Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] #Personal#: Reg: Multiple queries in a transaction On Wed, 18 Feb 2015 20:36:45 +0530 Medhavi Mahansaria medhavi.mahansa...@tcs.com wrote: I need to execute a series of queries in a transaction, say Q1, Q2, Q3. Q1 - success Q2 - Failed Q3 - Success My issue is that after Q2 fails all the queries that follow give error E RROR: current transaction is aborted, commands ignored until end of transaction block I want to move ahead in the transaction and execute Q3 also even though Q2 was a failure. Can you please suggest a way to do so in PostgreSQL 9.3. I believe savepoints are what you want: http://www.postgresql.org/docs/9.3/static/sql-savepoint.html Create a savepoint prior to each query, then decide how to proceed based on the success status of that query. For example, in the scenario you describe above: BEGIN SAVEPOINT q1 Q1 - success RELEASE SAVEPOINT q1 SAVEPOINT q2 Q2 - failure ROLLBACK TO SAVEPOINT q2 SAVEPOINT q3 Q3 - success RELEASE SAVEPOINT q3 COMMIT In which case Q1 and Q3 would successfully be committed. -- Bill Moran =-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you -- 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] Some indexing advice for a Postgres newbie, please?
On 02/19/2015 10:19 AM, brian wrote: On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian. Thanks. The purpose of the field is purely as a check against the user feeding the same data in twice. Once I've constructed it, I never pull the field apart again. It had to be done this way, as otherwise the boolean statement to check for uniqueness was horrendous. Brian. Then B. Dunavant's suggestion is probably best. Certainly easiest. How (else) does your app or reporting query this data? That could also effect your choice.
Re: [GENERAL] postgresql93-9.3.5: deadlock when updating parent table expected?
Thanks, Alvaro, Yes indeed. I have a test that causes the deadlock almost immediately. I have upgraded to 9.3.6 and have been running for a few hours now w/o deadlock errors observed. Dmitry From: Alvaro Herrera [alvhe...@2ndquadrant.com] Sent: Wednesday, February 18, 2015 6:19 AM To: Dmitry O Litvintsev Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql93-9.3.5: deadlock when updating parent table expected? Dmitry O Litvintsev wrote: Hi, I recently updated to postgresql93-9.3.5 (from 9.2.9). I see frequent deadlocks when updating parent table in insert into child table. There is foreign key constraint between child table and parent table. Parent table is updated on by trigger in insert into child table. So pretty much standard thing. Is it expected to deadlock? This is probably caused by a bug that was fixed in 9.3.6: Author: Alvaro Herrera alvhe...@alvh.no-ip.org Branch: master [0e5680f47] 2014-12-26 13:52:27 -0300 Branch: REL9_4_STABLE Release: REL9_4_1 [0e3a1f71d] 2014-12-26 13:52:27 -0300 Branch: REL9_3_STABLE Release: REL9_3_6 [048912386] 2014-12-26 13:52:27 -0300 Grab heavyweight tuple lock only before sleeping We were trying to acquire the lock even when we were subsequently not sleeping in some other transaction, which opens us up unnecessarily to deadlocks. In particular, this is troublesome if an update tries to lock an updated version of a tuple and finds itself doing EvalPlanQual update chain walking; more than two sessions doing this concurrently will find themselves sleeping on each other because the HW tuple lock acquisition in heap_lock_tuple called from EvalPlanQualFetch races with the same tuple lock being acquired in heap_update -- one of these sessions sleeps on the other one to finish while holding the tuple lock, and the other one sleeps on the tuple lock. Per trouble report from Andrew Sackville-West in http://www.postgresql.org/message-id/20140731233051.GN17765@andrew-ThinkPad-X230 His scenario can be simplified down to a relatively simple isolationtester spec file which I don't include in this commit; the reason is that the current isolationtester is not able to deal with more than one blocked session concurrently and it blocks instead of raising the expected deadlock. In the future, if we improve isolationtester, it would be good to include the spec file in the isolation schedule. I posted it in http://www.postgresql.org/message-id/20141212205254.gc1...@alvh.no-ip.org Hat tip to Mark Kirkwood, who helped diagnose the trouble. -- Á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] select where true, or select where input = '$var'
sorry, corrected below: 1 select * 2 from table 3 if '$sanitized_variable' = '' then -- $variable not provided or undefined or empty, 4 where true -- so select every row/record 5 else 6 where input = '$sanitized_variable' -- variable provided or defined or not-empty, so select only matching rows/records where input is a column/field 7 end if; On 2/19/15, John R Pierce pie...@hogranch.com wrote: On 2/19/2015 12:39 PM, zach cruise wrote: i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. something unclear here, is INPUT a variable in your application program, or is it a field in the table? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] rollback in C functions
Juan Pablo L wrote: Hello, i have created a function (in C) that receives an array that contains tuples of ID's and values. Why are you writing a C function? Sounds like you could accomplish the same with a plpgsql function, with much less effort. The function is to execute updates on each ID assigning the value, but if one of these operation fails (does not meet certain criteria) inside the function i would like to rollback and leave everything untouched, in case other ID;s were already updated previously, and come back to the caller and inform about it. Do you want previous updates to remain in place, or do you want to roll them back too? This is not clear. -- Á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] rollback in C functions
Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc On 19 February 2015 at 15:57, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Juan Pablo L wrote: Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour) Ah, so ereport() is exactly what you want, like Chris Mair said. Assuming you wrote it correctly, you should see the ERROR line in the server logs (set log_message_verbosity=verbose in postgresql.conf to see the full details such as the sqlstate etc). Did you notice you must add an extra ( before errmsg and other sub-calls within the ereport call? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [GENERAL] select where true, or select where input = '$var'
On 2/19/2015 12:39 PM, zach cruise wrote: i want to select based on input, but if input is not provided or if input is empty, then i want to select all rows. something unclear here, is INPUT a variable in your application program, or is it a field in the table? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] rollback in C functions
Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour) On 19 February 2015 at 15:34, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Juan Pablo L wrote: Hello, i have created a function (in C) that receives an array that contains tuples of ID's and values. Why are you writing a C function? Sounds like you could accomplish the same with a plpgsql function, with much less effort. The function is to execute updates on each ID assigning the value, but if one of these operation fails (does not meet certain criteria) inside the function i would like to rollback and leave everything untouched, in case other ID;s were already updated previously, and come back to the caller and inform about it. Do you want previous updates to remain in place, or do you want to roll them back too? This is not clear. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [GENERAL] rollback in C functions
Juan Pablo L wrote: Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour) Ah, so ereport() is exactly what you want, like Chris Mair said. Assuming you wrote it correctly, you should see the ERROR line in the server logs (set log_message_verbosity=verbose in postgresql.conf to see the full details such as the sqlstate etc). Did you notice you must add an extra ( before errmsg and other sub-calls within the ereport call? -- Á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] rollback in C functions
On 2/19/2015 1:41 PM, Juan Pablo L wrote: Hi, i want previous updates to rollback ... like nothing happened (a normal begin/rollback behaviour) so thrown an exception. The actual rollback has to be invoked by the client application program, which should catch the error thrown by the query that causes the exception. In pl/pgsql this would be easy, RAISE SQLSTATE 'string'; but i'm not sure how you'd do this in a C function. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] rollback in C functions
On 2/19/2015 2:02 PM, Juan Pablo L wrote: Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc a query that triggers ereport(ERROR,) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] rollback in C functions
Thank you, i will try this, honestly i was checking if PGResult is NULL, when i trigger the exception i always get NULL so i did not any further but i will try this . On 19 February 2015 at 16:22, John R Pierce pie...@hogranch.com wrote: On 2/19/2015 2:02 PM, Juan Pablo L wrote: Thank you Alvaro, i m afraid ereport seems to be the way, that it is complicated to catch this error code in the code of the caller. cause you have to use a callback etc etc a query that triggers ereport(ERROR,) should return a PGresult* that you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back the SQLSTATE code. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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 where true, or select where input = '$var'
zach cruise wrote sorry, corrected below: 1 select * 2 from table 3 if '$sanitized_variable' = '' then -- $variable not provided or undefined or empty, 4 where true -- so select every row/record 5 else 6 where input = '$sanitized_variable' -- variable provided or defined or not-empty, so select only matching rows/records where input is a column/field 7 end if; You cannot directly put that kind of logic in SQL. You can normalize the input so that if it is not provided or undefined you convert it into empty and then write a single query that recognizes the empty input as being the select-all form and anything non-empty input as being a limited form. SELECT ... FROM ... WHERE ($1::text = '') OR ($1::text = input_col) COALESCE(...) could also possibly be useful... David J. -- View this message in context: http://postgresql.nabble.com/select-where-true-or-select-where-input-var-tp5838612p5838638.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] rollback in C functions
i tried this but the call to PQresultErrorField(PGresult, PG_DIAG_SQLSTATE)
is returning NULL, this is what trigger the exception in the function code:
ereport(ERROR,(errcode(ERRCODE_SQL_ROUTINE_EXCEPTION),errmsg(Plan with id
%s does not allow balance with id %s,plan_id,in_balanceid)));
and this is the caller code:
if(PQresultStatus(pg_res) == PGRES_FATAL_ERROR)
{
char *t = PQresultErrorField(pg_res,PG_DIAG_SQLSTATE);
log_debug([C%03dH%03d] PQres returned NULL:
%s,handler-my_connection-id,handler-id,t);
}
the above call log_debug show that t is NULL. am i doing something wrong ?
thanks!
On 19 February 2015 at 16:27, Juan Pablo L jpablolorenze...@gmail.com
wrote:
Thank you, i will try this, honestly i was checking if PGResult is NULL,
when i trigger the exception i always get NULL so i did not any further but
i will try this .
On 19 February 2015 at 16:22, John R Pierce pie...@hogranch.com wrote:
On 2/19/2015 2:02 PM, Juan Pablo L wrote:
Thank you Alvaro, i m afraid ereport seems to be the way, that it is
complicated to catch this error code in the code of the caller. cause you
have to use a callback etc etc
a query that triggers ereport(ERROR,) should return a PGresult* that
you pass to PQresultStatus(), which should indicate PGRES_FATAL_ERROR, so
you then call PQresultErrorField(PGresult, PG_DIAG_SQLSTATE) to get back
the SQLSTATE code.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
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] Array string casts with SELECT but not SELECT DISTINCT
Ken Tanzer wrote
ag_reach_test= INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
ERROR: column my_array is of type character varying[] but expression is
of type text
LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
^
HINT: You will need to rewrite or cast the expression.
It's easy enough to add a cast, but I was curious if this was expected and
desired behavior. Thanks.
The select resolves the distinct by converting the unknown into a text so
when it gets to the insert it is already typed in the incompatible type.
Without distinct the select leaves the value as an unknown and then passing
it to the insert coerces it to the expected array.
It's a bottom-up evaluation plan instead of top-down one. Both have merit
but it definitely seems easier to implement the bottom-up version and coerce
only when needed with the immediately available information instead of
trying to skip around between layers.
David J.
--
View this message in context:
http://postgresql.nabble.com/Array-string-casts-with-SELECT-but-not-SELECT-DISTINCT-tp5838663p5838667.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] stored procedure variable names
2015-02-20 1:57 GMT+01:00 inspector morse inspectormors...@gmail.com: In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting user_id instead of p_user_id. Is there any plan to add a character to differentiate between variables? No, and I don't think so it is necessary in this moment (so I am against a introduction new prefix) a) PostgreSQL safely solves conflicts between plpgsql and SQL - what Oracle doesn't b) Usual prefix for plpgsql variables is _ - I don't see a difference between @,?,: Regards Pavel Stehule
[GENERAL] Array string casts with SELECT but not SELECT DISTINCT
Hi. Here's a boiled down example of something that caught me by surprise:
ag_reach_test= CREATE TEMP TABLE foo (my_array varchar[]);
CREATE TABLE
ag_reach_test= INSERT INTO foo (my_array) SELECT '{TEST}';
INSERT 0 1
ag_reach_test= SELECT my_array[1],array_length(my_array,1) FROM foo;
my_array | array_length
--+--
TEST |1
(1 row)
ag_reach_test= INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
ERROR: column my_array is of type character varying[] but expression is
of type text
LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';
^
HINT: You will need to rewrite or cast the expression.
It's easy enough to add a cast, but I was curious if this was expected and
desired behavior. Thanks.
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ http://agency-software.org/*
*https://agency-software.org/demo/client
https://agency-software.org/demo/client*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
agency-general-requ...@lists.sourceforge.net?body=subscribe to
learn more about AGENCY or
follow the discussion.
Re: [GENERAL] stored procedure variable names
On 02/19/2015 04:57 PM, inspector morse wrote: In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; First Postgres does not have stored procedures, but user defined functions, so the above is a no-op right from the start. Second I have no idea where you are pulling get_user_for_editing.* from? Third, which of the Postgres procedural languages are you having an issue with? Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting user_id instead of p_user_id. Is there any plan to add a character to differentiate between variables? In what procedural language? -- Adrian Klaver adrian.kla...@aklaver.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] Some indexing advice for a Postgres newbie, please?
Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- 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] Some indexing advice for a Postgres newbie, please?
You should consider a BitString. http://www.postgresql.org/docs/9.4/static/datatype-bit.html On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- 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] Some indexing advice for a Postgres newbie, please?
On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian.
Re: [GENERAL] Some indexing advice for a Postgres newbie, please?
On Thu, Feb 19, 2015 at 2:14 PM, Brian Dunavant br...@omniti.com wrote: You should consider a BitString. http://www.postgresql.org/docs/9.4/static/datatype-bit.html On Thu, Feb 19, 2015 at 11:10 AM, brian br...@meadows.pair.com wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. -- 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 I suggest the bytea type.
Re: [GENERAL] Issue dumping schema using readonly user
Thank you all so much for the feedback. At this point, I'm convinced that the issue is more complicated than I originally thought :) FWIW, my use case is for a company internal database. I open the database up to all users by simply having a readonly user that anyone can use to connect to the database and run queries. Some tables have sensitive data in them that I would prefer not to allow users to simply have access to via this account. However, that said, there are internal people that we want to empower to develop our internal tool and so the idea is that they can take a database dump using the readonly user to replicate the schema in their own instance. I realize this usage may be quite specific to our company, but it seemed like the issue (as an outsider) was very simple. Just want to reiterate my thanks for taking the time to look into this issue and consider it. This was my first interaction with the Postgres community and it was an overwhelmingly good one! - Dan On Wed, Feb 18, 2015 at 6:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: This is the standard mistake about pg_dump, which is to imagine that it depends only on userspace operations while inspecting schema info. It doesn't; it makes use of things like ruleutils.c which operate on latest available data rules. There's two different points here- the first is the whole discussion around why pg_dump is depending on the backend for bits and pieces but not everything, but the second is- aren't the accesses from ruleutils.c now using an MVCC snapshot? Yeah, they're using *an* MVCC snapshot. But it's not the transaction snapshot, it's one that postdates all sinval traffic the backend has received. Robert's changes to get rid of SnapshotNow didn't really affect this issue at all. (To clarify: I'm worried about all the stuff that involves syscache consultations; those queries executed via SPI are not the issue.) It now strikes me that it might be possible to use Andreas' logical decoding infrastructure to allow pg_dump's backend to operate with a historical catalog snapshot, which perhaps could resolve this problem. But there's no such logic there today, and I have no idea what the disadvantages might be. Certainly there's a comment about that happening for pg_get_constraintdef_worker(), and other parts appear to go through SPI, but not everything does. Yeah, Jan originally had a plan of making ruleutils operate exclusively through SPI, but that lasted probably about a month and a half before people started using syscache-accessing shortcuts. I think we really would be best off to eliminate the SPI usage there altogether; it has little effect except to waste cycles and mislead the credulous into thinking ruleutils operates in userspace. regards, tom lane
Re: [GENERAL] Fwd: Data corruption after restarting replica
Hi Adrian, On Wed, Feb 18, 2015 at 10:25 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 02/16/2015 02:44 AM, Novák, Petr wrote: Hello, sorry for posting to second list, but as I've received no reply there, I'm trying my luck here. Thanks Petr -- Forwarded message -- From: Novák, Petr nov...@avast.com Date: Tue, Feb 10, 2015 at 12:49 PM Subject: Data corruption after restarting replica To: pgsql-b...@postgresql.org Hi all, we're experiencing data corruption after switching streamed replica to primary. This is not the first time I've encountered this issue, so I'l try to describe it in more detail. For this particular cluster we have 6 servers in two datacenters (3 in each). There are two instances running on each server, each with its own port and datadir. On the first two servers in each datacenter one instance is primary and the other is replica for the primary from the other server. Third server holds two offsite replicas from the other datacenter (for DR purposes) Each replica was set up by taking pg_basebackup from primary (pg_basebackup -h hostname -p 5430 -D /data2/basebackup -P -v -U user -x -c fast). Then directories from initdb were replaced with the ones from basebackup (only the configuration files remained) and the replica started and was successfully connected to primary. It was running with no problem keeping up with the primary. We were experiencing some connection problem between the two datacenters, but replication didn't break. Then we needed to take one datacenter offline due to hardware maintenance. So I've switched the applications down, verified that no more clients were connected to primary, then shut the primary down and restarted replica without recovery.conf and the application were started using the new db with no problem. Other replica even successfully reconnected to this new primary. What other replica? Each primary has two replicas. One is in the same datacenter as primary (as a hot standby, should the primary server fail) and other is in another datacenter (for DR purposes). I've restarted the DR replica as the new primary and the hot standby replica reconected to it. Few hours from the switch lines appeared in the server log (which didn't appear before), indicating a corruption: ERROR: index account_username_key contains unexpected zero page at block 1112135 ERROR: right sibling's left-link doesn't match: block 476354 links to 1062443 instead of expected 250322 in index account_pkey ..and many more reporting corruption in several other indexes. What happened to the primary you shut down? It has been reinstalled, as its role has been moved to another server. But its logs didn't contain the index errors. The issue was resolved by creating new indexes and dropping the affected ones, although there were already some duplicities in the data, that has to be resolved, as some of the indexes were unique. This particular case uses Postgres 9.1.14 on both primary and replica. But I've experienced similar behavior on 9.2.9. OS Centos 6.6 in all cases. This may mean, that there can be something wrong with our configuration or the replication setup steps, but I've set up another instance using the same steps with no problem. Fsync related setting are at their defaults. Data directories are on RAID10 arrays, with BBUs. Filesystem is ext4 mounted with nobarrier option. Database is fairly large ~120GB with several 50mil+ tables, lots of indexes and FK constraints. It is mostly queried, updates/inserts/deletes are only several rows/s. Any help will be appreciated. Petr Novak System Engineer Avast s.r.o. -- Adrian Klaver adrian.kla...@aklaver.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] Fwd: Data corruption after restarting replica
Hi Dinesh On Wed, Feb 18, 2015 at 11:01 PM, dinesh kumar dineshkuma...@gmail.com wrote: Hi, On Mon, Feb 16, 2015 at 2:44 AM, Novák, Petr nov...@avast.com wrote: Hello, sorry for posting to second list, but as I've received no reply there, I'm trying my luck here. Thanks Petr -- Forwarded message -- From: Novák, Petr nov...@avast.com Date: Tue, Feb 10, 2015 at 12:49 PM Subject: Data corruption after restarting replica To: pgsql-b...@postgresql.org Hi all, we're experiencing data corruption after switching streamed replica to primary. This is not the first time I've encountered this issue, so I'l try to describe it in more detail. For this particular cluster we have 6 servers in two datacenters (3 in each). There are two instances running on each server, each with its own port and datadir. On the first two servers in each datacenter one instance is primary and the other is replica for the primary from the other server. Third server holds two offsite replicas from the other datacenter (for DR purposes) Each replica was set up by taking pg_basebackup from primary (pg_basebackup -h hostname -p 5430 -D /data2/basebackup -P -v -U user -x -c fast). Then directories from initdb were replaced with the ones from basebackup (only the configuration files remained) and the replica started and was successfully connected to primary. It was running with no problem keeping up with the primary. We were experiencing some connection problem between the two datacenters, but replication didn't break. Then we needed to take one datacenter offline due to hardware maintenance. So I've switched the applications down, verified that no more clients were connected to primary, then shut the primary down and restarted replica without recovery.conf and the application were started using the new db with no problem. Other replica even successfully reconnected to this new primary. Before restarting replica, did you make sure that, all master transactions applied to replication node. Yes. May we know, why did you restarted replica without recovery.conf. Do you want to maintain the same timeline for the xlogs. Or any specific other reasons. ?? Exactly, to preserve the xlog timeline. Regards, Dinesh manojadinesh.blogspot.com Few hours from the switch lines appeared in the server log (which didn't appear before), indicating a corruption: ERROR: index account_username_key contains unexpected zero page at block 1112135 ERROR: right sibling's left-link doesn't match: block 476354 links to 1062443 instead of expected 250322 in index account_pkey ..and many more reporting corruption in several other indexes. The issue was resolved by creating new indexes and dropping the affected ones, although there were already some duplicities in the data, that has to be resolved, as some of the indexes were unique. This particular case uses Postgres 9.1.14 on both primary and replica. But I've experienced similar behavior on 9.2.9. OS Centos 6.6 in all cases. This may mean, that there can be something wrong with our configuration or the replication setup steps, but I've set up another instance using the same steps with no problem. Fsync related setting are at their defaults. Data directories are on RAID10 arrays, with BBUs. Filesystem is ext4 mounted with nobarrier option. Database is fairly large ~120GB with several 50mil+ tables, lots of indexes and FK constraints. It is mostly queried, updates/inserts/deletes are only several rows/s. Any help will be appreciated. Petr Novak System Engineer Avast s.r.o. -- 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] Issue dumping schema using readonly user
On 02/19/2015 01:46 AM, Daniel LaMotte wrote: Thank you all so much for the feedback. At this point, I'm convinced that the issue is more complicated than I originally thought :) FWIW, my use case is for a company internal database. I open the database up to all users by simply having a readonly user that anyone can use to connect to the database and run queries. Some tables have sensitive data in them that I would prefer not to allow users to simply have access to via this account. However, that said, there are internal people that we want to empower to develop our internal tool and so the idea is that they can take a database dump using the readonly user to replicate the schema in their own instance. I realize this usage may be quite specific to our company, but it seemed like the issue (as an outsider) was very simple. FYI, using pgAdmin(http://www.pgadmin.org/) might be a solution. I just tried it. I logged in as readonly and looked at the mytable_is_not_readonly table. pgAdmin threw a permissions error, but still showed the CREATE TABLE script in the SQL pane. Could be a way to let your users get at the schema definitions. Just want to reiterate my thanks for taking the time to look into this issue and consider it. This was my first interaction with the Postgres community and it was an overwhelmingly good one! - Dan -- Adrian Klaver adrian.kla...@aklaver.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] Some indexing advice for a Postgres newbie, please?
On Thu, 19 Feb 2015 09:30:57 -0700, you wrote: On 02/19/2015 09:10 AM, brian wrote: Hi folks, I have a single-user application which is growing beyond the fixed-format data files in which it currently holds its data, I need a proper database as the backend. The front end is written using Lazarus and FreePascal under Linux, should anyone feel that makes a difference. The database will need to grow to around 250,000 records. My problem is with the data field which is the (unique) key. It's really a single 192-bit integer (it holds various bits of bitmapped data) which I currently hold as six 32-bit integers, but can convert if needed when transferring the data. How would you advise that I hold this field in a Postgres database, given the requirement for the whole thing to be a unique key? The first 64 bits change relatively infrequently, the last 128 bits will change with virtually every record. The last 128 bits will ALMOST be unique in themselves, but not quite. :( Thanks, Brian. If your application understands/parses/makes use of the data in those 192 bites, I would reload with an additional unique id field. For the intended number of rows of data a sequence would be fine, though I'm partial to UUIDs. Alternatively map the 192 bytes to two fields and make a unique key of both of them. Third alternative would be to use a binary BitString a suggested by Brian. Thanks. The purpose of the field is purely as a check against the user feeding the same data in twice. Once I've constructed it, I never pull the field apart again. It had to be done this way, as otherwise the boolean statement to check for uniqueness was horrendous. Brian. -- 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] Failure loading materialized view with pg_restore
Brian Sutherland br...@vanguardistas.net writes: On Wed, Feb 18, 2015 at 10:34:33AM -0500, Tom Lane wrote: User nobody does not have permission to read table x, so the REFRESH fails, because the view's query executes as the view's owner. If you grant select permission for the user nobody on x, pg_restore still fails even though a REFRESH succeeds: Oooh. Yeah: the problem is that pg_dump dumps the REFRESH before it dumps the ACLs for the tables: -- -- Name: x; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE x ( y integer ); ALTER TABLE x OWNER TO postgres; -- -- Name: myview; Type: MATERIALIZED VIEW; Schema: public; Owner: nobody; Tablespace: -- CREATE MATERIALIZED VIEW myview AS SELECT x.y FROM x WITH NO DATA; ALTER TABLE myview OWNER TO nobody; -- -- Data for Name: x; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY x (y) FROM stdin; \. -- -- Name: myview; Type: MATERIALIZED VIEW DATA; Schema: public; Owner: nobody -- REFRESH MATERIALIZED VIEW myview; -- -- Name: x; Type: ACL; Schema: public; Owner: postgres -- REVOKE ALL ON TABLE x FROM PUBLIC; REVOKE ALL ON TABLE x FROM postgres; GRANT ALL ON TABLE x TO postgres; GRANT SELECT ON TABLE x TO nobody; We need to rethink the ordering rules here. I believe that not dumping ACLs till late is an intentional choice to avoid corner cases with regular tables (eg what if user has revoked INSERT on a table), but it doesn't work so well for matviews. One possible avenue to a fix is to also postpone the assignment of the matview's owner, but I'm not sure that that's a great idea from a security standpoint. A possibly safer idea is just to put all REFRESHes after all ACL updates. If things fail then, well, they'd have failed anyway. 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] BDR Monitoring, missing pg_stat_logical_decoding view
Thank you, that was helpful. In the pg_replication_slots view, I see that xmin is always NULL, is that expected? I'm not sure how to measure the BDR update latency without this xmin value. If I run pg_get_transaction_committime(catalog_xmin), sometimes I get what looks like a default time stamp of 1999-12-31 16:00:00-08, is that expected? Thanks, Steve Boyle -Original Message- From: Andres Freund [mailto:and...@2ndquadrant.com] Sent: Wednesday, February 18, 2015 5:19 AM To: Steve Boyle Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] BDR Monitoring, missing pg_stat_logical_decoding view Hi, On 2015-02-17 22:37:43 +, Steve Boyle wrote: I'm trying to setup replication monitoring for BDR, following the doc here: https://wiki.postgresql.org/wiki/BDR_Monitoring My BDR installs seem to be missing the pg_stat_logical_decoding view. Is there something specific I need to do to install/create that view? It has been renamed since - it's part of postgresql 9.4 and named pg_replication_slots. It seems most of the page refers to it by the correct name, just a subsection doesn't... Sorry for that. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, 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] stored procedure variable names
inspector morse inspectormors...@gmail.com writes: In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin   select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin   select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting user_id instead of p_user_id. Is there any plan to add a character to differentiate between variables? Not that I'm aware of but please submit a patch or do not hesitate to run any of those other platforms where things are not such a mess :-) -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] stored procedure variable names
In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting user_id instead of p_user_id. Is there any plan to add a character to differentiate between variables?
Re: [GENERAL] stored procedure variable names
Yeah, I'm using plpgsql. Actually nevermind on this. I was able to patch my data access utility so it adds a prefix when calling the stored function and then remove it again before returning for front end processing. On Thu, Feb 19, 2015 at 8:44 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 02/19/2015 04:57 PM, inspector morse wrote: In all other DBMS, the variable names have a distinctive character to differentiate between variables and column names: Example: SQL Server uses @ MySql uses ? Oracle uses : Firebirdsql uses : It makes it easier to write and manage queries especially in stored procedures. Just compare the below: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select username into @username from users where user_id = @user_id; end; to this mess: create stored procedure get_user_for_editing(user_id int, out username varchar) begin select u.username into get_user_for_editing.username from users u where get_user_for_editing.user_id = get_user_for_editing.user_id; end; First Postgres does not have stored procedures, but user defined functions, so the above is a no-op right from the start. Second I have no idea where you are pulling get_user_for_editing.* from? Third, which of the Postgres procedural languages are you having an issue with? Prefixing the variables (ex: p_user_id) makes the application code harder to write as we have a lot of dynamic code that is expecting user_id instead of p_user_id. Is there any plan to add a character to differentiate between variables? In what procedural language? -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] stored procedure variable names
inspector morse inspectormors...@gmail.com writes: Is there any plan to add a character to differentiate between variables? No. You're free to use a naming convention yourself, of course, but we're not going to break every stored procedure in sight in order to impose one. 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
