Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Friday, February 6, 2015, Adrian Klaver
wrote:
> On 02/06/2015 10:26 AM, Tim Smith wrote:
>
>> Re:So, you have an input parameter named "session_id" and a query with
>> a column named "session_id" - this is the problem.
>>
>> Well, I'll re-try with a revised function, but surely the database
>> could have come up with a more meaningful and insightful message than
>> the coded incomprehensible error message it did ?I would say its
>> not only user error, its developer error too for creating such
>> confusing error messages !
>>
>
> Well actually you did yourself a disservice by including the EXCEPT code.
> That changed the error message. Taking that code out and running the
> failing function you get:
>
> test-> validateSession('441122','10.11.12.13','abc',3600,3600);
> ERROR: column reference "session_id" is ambiguous
> LINE 2: session_id=session_id and session_ip=client_ip
> ^
> DETAIL: It could refer to either a PL/pgSQL variable or a table column.
> QUERY: select * from app_val_session_vw where
> session_id=session_id and session_ip=client_ip
> CONTEXT: PL/pgsql function
> validatesession(character,inet,character,bigint,bigint)
> line 7 at SQL statement
>
> I would say that is fairly specific:)
The exception block is ok, you want to report the session-id passed (via
raise notice or similar), but you want to use the "RAISE;" form (i.e., no
args) to re-raise the original error.
http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html
David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Fri, Feb 6, 2015 at 10:23 AM, Adrian Klaver wrote: > >> CREATE FUNCTION validateSession(session_id char(64),client_ip >> inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint) >> RETURNS json AS $$ >> > > CREATE OR REPLACE FUNCTION public.validatesession(s_id character, > client_ip inet, user_agent character, forcedtimeout bigint, sessiontimeout > bigint) > RETURNS json > > As an aside, and going from memory, you will note that Adrian kept the "character" type in the function signature but removed the length specifier. PostgreSQL does not store that information and so will not prevent a call from passing in a string longer than 64 characters into the function. This applies to any specification in () following a type declaration (say for numeric or timestamptz) David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Fri, Feb 6, 2015 at 9:55 AM, Tim Smith wrote: > >Unfortunately the function definition is not given and that is where you > are seeing the error. > > To figure this out we will need to see the function. > > Geez, there's just no satisfying some people ! ;-) > > I did actually show you my function in an earlier mail but my > current bodged minimised version looks like this : > > > CREATE FUNCTION validateSession(session_id char(64),client_ip > inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint) > RETURNS json AS $$ > DECLARE > v_now bigint; > v_row app_val_session_vw%ROWTYPE; > BEGIN > v_now := extract(epoch FROM now())::bigint; > select * into strict v_row from app_val_session_vw where > session_id=session_id and session_ip=client_ip; > RETURN row_to_json(v_row); > EXCEPTION > WHEN OTHERS THEN > RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: % > - SQLERRM: %)', session_id,SQLSTATE,SQLERRM > USING HINT = 'Database error occured (sval fail)'; > END; > $$ LANGUAGE plpgsql; > > > Note that I have tried a million and one different versions of the > line "RETURN row_to_json(v_row);" including declaring a JSON type > var and putting hte result into that before returning. But nothing > works, it always comes back with the same session_id nonsense. > So, you have an input parameter named "session_id" and a query with a column named "session_id" - this is the problem. The function never even gets to execute the "RETURN" statement - the exception occurred first - so whatever you were doing there was pointless. On a side note It seems you missed the memo about the "char" type being largely deprecated...and furthermore if I rename the function signature "session_id" to "i_session_id" and replace the corresponding value in the SELECT statement I now get "operator does not exist: bigint = character. So you've setup an input type that differs from your column type. So, yes, it is user error and while it was not due to the view that was all the information you provided at the time. I'm not in the mood to fix these two items (name and type) and find the next oversight. I do suggest that, especially if you do not use "IN/OUT" arguments, you prefix your function argument names with something so that you eliminate the chance that a function variable and a query variable name collide. The main give-away here was the where clause expression "WHERE session_id = session_id" - how would you expect PostgreSQL to know which one is from the table and which one is from the function? The only other option is to pick one of them but in that case you'd simply get a constant TRUE and every row would be returned. David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith wrote: > You're most welcome to look at my view definition view if you don't > believe me > > View definition: > SELECT a.session_id, > a.session_ip, > a.session_user_agent, > a.session_start, > a.session_lastactive, > b.user_id, > b.tenant_id, > b.reseller_id, > b.tenant_name, > b.user_fname, > b.user_lname, > b.user_email, > b.user_phone, > b.user_seed, > b.user_passwd, > b.user_lastupdate, > b.tenant_lastupdate >FROM app_sessions a, > app_users_vw b > WHERE a.user_id = b.user_id; > So that view and definition are correct. So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict. I guess it is possible that: (SELECT v_row FROM v_row) would give that message but I get a "relation v_row does not exist" error when trying to replicate the scenario. It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error. David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith wrote: > > returning more than one row? v_row can only hold one row at a time. > > Absolutley not. (a) My where clause is a primary key (b) I have > checked it manually, it only returns one row > > >You really need to provide error messages > > Yes, well PostgreSQL is being incredibly unhelpful in that respect, it > says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is > ambiguous)" ... but that is an utter lie. There is only one column > called session_id in my view (in both the view output and the > underlying view query, there is only one reference to "session_id") > > PostgreSQL doesn't lie - it just doesn't always give all of the information you need to understand what it is seeing. You have a view definition problem since nowhere in the code you provide should session_id be resolved. A simple: SELECT * FROM my_view; would prove out that theory. If that works then most probably the my_view view that the function sees is different than the one that you think it is seeing. > On 5 February 2015 at 21:57, Adrian Klaver > wrote: > > On 02/05/2015 01:38 PM, Tim Smith wrote: > >> > >> Hi, > >> > >> I have a function that broadly looks like this : > >> > >> create function doStuff() returns json as $$ > >> DECLARE > >> v_row my_view%ROWTYPE; > >> BEGIN > >> select * into strict v_row from my_view where foo=bar; > >> select row_to_json(v_row) from v_row; > > A third problem you will hit, when you fix the syntax, is that the SELECT row_to_json(...) command has no target and thus needs to use PERFORM, not SELECT. David J.
Re: [GENERAL] Subselect with no records results in final empty set
On Thu, Jan 29, 2015 at 1:22 PM, Sterpu Victor wrote: > It works as you sugested, this is the syntax I used: > SELECT * FROM > (SELECT 1 AS t1, 2 AS t2) AS t1 > LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (null) > > Thank you. > > You will notice that everyone responding to you is bottom-posting... Anyway, the use of "ON (null)" is unusual... Also, the fact that your toy model is making considerable use of "CROSS JOIN" is unusual; one common reality of "relational databases" is that usually the things you are going together are "related" and the join conditions reflect those relationships. I'd suggest using actual tables (or CTE/WITH) with multiple rows of data to try and learn how to write queries. The number of times you are going to join together multiple results each only having a single row is slim. David J.
Re: [GENERAL] how to duplicate data for few times by SQL command in PG
On Thursday, January 22, 2015, tsunghan hsieh wrote: > Hi > > I have a table which just has one column as following in Original Table. I > wanna duplicate all of data for few times and with same order as following > in New Table. Is there anyone who can help me? Thanks > > Han > > Original Table > 23 > 45 > 65 > 22 > > New Table > 23 > 23 > 23 > 45 > 45 > 45 > 65 > 65 > 65 > 65 > 22 > 22 > 22 > 22 > Tables do not have order. Cross join. David J.
Re: [GENERAL] ORDER BY in prepared statements
> > > On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries < > bryn.jeffr...@sydney.edu.au> wrote: > >> >> Maybe what we need in ODBC libs and the like is a "protected >> statement" that follows the same construction as a prepared statement but >> additionally checks catalogs to validate identifiers. >> > I'm not sure whether this would actually be a feasible solution to the problem. Note that most frameworks (well, the format solution I outlined at least) for doing identifier replacement safely require that you actually tell the system what is expected to be an identifier and what is expected to be a data value. The general implementation is that, in the case of PostgreSQL, double-quotes will be added to the identifier value if required to make it a valid identifier. Since any injection would rely on supply mandatory quote identifiers this solves the problem quite neatly. The one part I am not positive on is dealing with case-folding when using format's %I placeholder; this seems to be a documentation deficiency though I may just not have found it yet...or reasoned out the logical outcome (which I shouldn't need to do)... Catalog lookups would be expensive to do pro-actively. The goal is to form a safe query for the parser and let the planner deal with any identifiers that end up being invalid either through attempted injection or simply usage errors. David J.
Re: [GENERAL] ORDER BY in prepared statements
On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries wrote: > Paul Jungwirth wrote > > I'm not sure how to make a prepared statement that lets you name a > > column when you execute it. Maybe someone else can chime in if that's > > possible. > > David J. responded > > You cannot. By definition parameters, in this context, are values - not > > identifiers. > > [...] > > In both situations there is no way for the planner to plan and cache a > > single query whose order by column varies. No matter what you do at best > > you can have a single plan for each explicit order by column that you > wish > > to specify. > > That's what I'd figured. The motivation to use prepared statements in > application layers is not so much having a single plan but more the > insulation from SQL injection. The intent of the given ORDER BY example was > to restricts inputs to valid identifiers rather than part of the query > expression. > > Maybe what we need in ODBC libs and the like is a "protected > statement" that follows the same construction as a prepared statement but > additionally checks catalogs to validate identifiers. > > Bryn The canonical way to do this, in reasonably recent PostgreSQL versions, is to wrap your desired dynamic SQL statement in a function. Within that function construct the SQL string with the assistance of the "format(...)" function. That function has specific placeholders for literals and identifiers that will ensure that the constructed SQL string is built in a safe manner. http://www.postgresql.org/docs/9.4/interactive/functions-string.html#FUNCTIONS-STRING-FORMAT Then you call the function and pass in the arguments are value parameters; which the function then converts into either literal or identifiers as instructed to by the format expression. David J.
Re: [HACKERS] [GENERAL] ON_ERROR_ROLLBACK
On Tue, Dec 30, 2014 at 8:54 AM, Adrian Klaver wrote: > On 12/30/2014 07:43 AM, David G Johnston wrote: > >> Tom Lane-2 wrote >> >>> Bernd Helmle < >>> >> >> mailings@ >>> >> >> > writes: >>> --On 29. Dezember 2014 12:55:11 -0500 Tom Lane < >>> >> tgl@.pa >>> >> >> > wrote: >>> Given the lack of previous complaints, this probably isn't backpatching > material, but it sure seems like a bit of attention to consistency > would be warranted here. > >>> Now that i read it i remember a client complaining about this some time ago. I forgot about it, but i think there's value in it to backpatch. >>> >>> Hm. Last night I wrote the attached draft patch, which I was intending >>> to apply to HEAD only. The argument against back-patching is basically >>> that this might change the interpretation of scripts that had been >>> accepted silently before. For example >>> \set ECHO_HIDDEN NoExec >>> will now select "noexec" mode whereas before you silently got "on" mode. >>> In one light this is certainly a bug fix, but in another it's just >>> definitional instability. >>> >>> If we'd gotten a field bug report we might well have chosen to >>> back-patch, >>> though, and perhaps your client's complaint counts as that. >>> >>> Opinions anyone? >>> >> >> -0.5 for back patching >> >> The one thing supporting this is that we'd potentially be fixing scripts >> that are broken but don't know it yet. But the downside of changing >> active >> settings for working scripts - even if they are only accidentally working >> - >> is enough to counter that for me. Being more liberal in our acceptance of >> input is more feature than bug fix even if we document that we accept more >> items. >> > > It is more about being consistent then liberal. Personally I think a > situation where for one variable 0 = off but for another 0 = on, is a bug > > I can sorta buy the consistency angle but what will seal it for me is script portability - the ability to write a script and instructions using the most current release and have it run on previous versions without having to worry about this kind of incompatibility. So, +1 for back patching from me. David J.
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver wrote: > On 12/29/2014 03:56 PM, David Johnston wrote: > >> > So you think psql should issue "COMMIT;" even if it is exiting due to >> > "ON_ERROR_STOP"? >> > > I say yes, if it is a non-SQL error. As Viktor stated, SQL errors abort > the transaction. > > Ok, so we disagree here because that distinction seems arbitrary and decidedly not useful. >> Whether you do or don't can you show me where in the documentation the >> current behavior is described? >> > > Your biggest issue seems to be with --single-transaction and ON_ERROR_STOP > so: > > --single-transaction > > When psql executes a script, adding this option wraps BEGIN/COMMIT > around the script to execute it as a single transaction. > > Therefore: > > BEGIN; > script > COMMIT; > > I would and have agreed with your previous statements that it is not clear > enough that \i is not an SQL command and an error with same is ignored by > the transaction. Outside of that I see no problem. > > That still leaves ambiguity. How about: --single-transaction When psql executes a script using this option it explicitly begins a transaction at session start and commits that transaction at session end. The transaction will commit even if the script is forced to exit early due to ON_ERROR_STOP: and if no SQL errors have occurred all statements prior to the error-inducing psql meta-command will be committed. For this reason it is not recommended to combine this option and ON_ERROR_STOP - instead omit this option and supply the transaction commands yourself. ON_ERROR_STOP [existing wording] As described under the --single-transaction option the commit issued at session end will occur prior to psql exiting and could result in running script being partially committed. David J.
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver wrote: > On 12/29/2014 02:55 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 02:28 PM, David Johnston wrote: >> >> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com> >> <mailto:adrian.klaver@aklaver.__com >> >> <mailto:adrian.kla...@aklaver.com>>>wrote: >> >> On 12/29/2014 09:38 AM, David Johnston wrote: >> >> >> This is one of those glass half full/empty >> situations, >> where it is >> down to the eye of the beholder. I would also say >> this a >> perfect >> example of why tests are written, to see what >> actually happens >> versus what you think happens. >> >> >> If a user of our product needs to run a test to >> determine >> behavior then >> our documentation is flawed - which is the point I am >> making. >> >> >> Still not seeing the flaw in the documentation. >> >> ... >> >> psql does not see any error due to meta-commands or >> SQL as fatal - >> which is why the ON_ERROR_STOP option exists. >> >> >> And ON_ERROR_STOP does not change that. All it does is toggle >> whether psql continues on after an error or stops >> processing commands. >> >> >> >> If it walks and talks like a duck...the fact that ON_ERROR_STOP >> makes >> psql halt processing means that it now treats them like it does >> any >> other fatal error. >> >> >> But it does not: >> >> ON_ERROR_STOP >> >> By default, command processing continues after an error. When >> this variable is set, it will instead stop immediately. In >> interactive mode, psql will return to the command prompt; otherwise, >> >> psql will exit, returning error code 3 to distinguish >> this case from fatal error conditions, which are reported using >> error code 1. >> >> In either case, any currently running scripts (the top-level script, >> if any, and any other scripts which it may have in invoked) will be >> terminated immediately. If the top-level command string contained >> multiple SQL commands, processing will stop with the current command. >> >> >> I am not seeing what point you are trying to make here. psql exits - >> my contention is that it should do so before issuing "COMMIT;" if >> --single-transaction was specified. I really don't care what made psql >> exit - a fatal error or a non-fatal one while running under ON_ERROR_STOP. >> > > I am having trouble keeping up with this line of reasoning: > > "psql does not see any error due to meta-commands or SQL as fatal - which > is why the ON_ERROR_STOP option exists. > " > > " > If it walks and talks like a duck...the fact that ON_ERROR_STOP > makes psql halt processing means that it now treats them like it does any > other fatal error. > > " > "I really don't care what made psql exit.." > > At this point I agree to disagree. > OK - what do we disagree on? This is nit-picking on a few word choices. > psql is a client not an all knowing entity. Not sure it is in its remit to > monitor all possible interactions of database commands and non database > commands. For instance, you have in a script a function written in > plpythonu that sends email and in the same script a line that runs that > function to send an email. Do you expect psql to abort everything if the > receiving email server rejects the message? A contrived example to be sure, > but not entirely out of the realm of possibility and journey done a > tortuous path Not productive - since plpython is outside of its purvue it cannot control that. However, right now if that function raises an error the script should stop and the open transaction should be rolled back (by default). If something is non-transaction and cannot be rolled back (notify, writing to file system, etc...) then that effect remains just like it would in any other situation. But psql does have full control over "\include" and should handle a failure to do so like any other scripting language interpreter would. > Just not seeing it. At this point I have made my arguments. Will be > interested whether others have comments or even care. > So you think psql should issue "COMMIT;" even if it is exiting due to "ON_ERROR_STOP"? Whether you do or don't can you show me where in the documentation the current behavior is described? David J.
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver wrote: > On 12/29/2014 02:28 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 09:38 AM, David Johnston wrote: >> >> >> This is one of those glass half full/empty situations, >> where it is >> down to the eye of the beholder. I would also say this a >> perfect >> example of why tests are written, to see what actually >> happens >> versus what you think happens. >> >> >> If a user of our product needs to run a test to determine >> behavior then >> our documentation is flawed - which is the point I am making. >> >> >> Still not seeing the flaw in the documentation. >> >> ... >> >> psql does not see any error due to meta-commands or SQL as fatal >> - >> which is why the ON_ERROR_STOP option exists. >> >> >> And ON_ERROR_STOP does not change that. All it does is toggle >> whether psql continues on after an error or stops processing commands. >> >> >> If it walks and talks like a duck...the fact that ON_ERROR_STOP makes >> psql halt processing means that it now treats them like it does any >> other fatal error. >> > > But it does not: > > ON_ERROR_STOP > > By default, command processing continues after an error. When this > variable is set, it will instead stop immediately. In interactive mode, > psql will return to the command prompt; otherwise, > > psql will exit, returning error code 3 to distinguish this > case from fatal error conditions, which are reported using error code > 1. > > In either case, any currently running scripts (the top-level script, if > any, and any other scripts which it may have in invoked) will be terminated > immediately. If the top-level command string contained multiple SQL > commands, processing will stop with the current command. > > I am not seeing what point you are trying to make here. psql exits - my contention is that it should do so before issuing "COMMIT;" if --single-transaction was specified. I really don't care what made psql exit - a fatal error or a non-fatal one while running under ON_ERROR_STOP. I can find out the root cause by checking for either a 3 or a 1 but what am I supposed to do with that information? More specifically, what should I do if I see a 3 that I wouldn't do if I see a 1; and vice-versa. As a user I really don't care I just want to know that any changes my script may have performed prior to the error have been rolled back if psql exits with a non-zero status. > >> I believe that if ON_ERROR_STOP causes an abort that the COMMIT >> from >> --single-transaction should not run. That is a behavior >> change. But >> not documenting the known and deterministic interaction between >> the two >> options is a bug. >> >> >> I am not seeing anything in the below that says an ABORT is issued: >> >> >> I was using term in its non-SQL sense: to stop processing and return >> control to the user. >> > > So if is non-SQL why should the transaction care about it? The transaction doesn't - but psql allows me to do non-SQL stuff along side of SQL stuff and I want the entire thing to fail if either the SQL or the non-SQL stuff has a problem. It is incumbent upon psql to make the boundary between the two as invisible as possible and right now it does not do as good a job as it could. >From the standpoint of psql \include should be just as much a part of the transaction as SELECT * FROM tbl - at least when operating in file/script mode. My issue is with psql - how it manages the underlying session/transaction to make that works is its problem and should be an implementation detail I do not have to worry about. Note: This all likely extends to "\!" as well but I haven't gone and explored that dynamic. > > > >> 2) the implications of \include being a client-side mechanic and >> thus, >> invisible to the server, is not well explained. Specifically >> that a >> failure to include is the equivalent of simply omitting the >> statement >> altogether (aside from the psql warning). i.e., if in an actual >> transaction the server will not issue the standard "error has >> occurred, >> you must ROLLBACK." message for any subsequent statements in the &
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver wrote: > On 12/29/2014 09:38 AM, David Johnston wrote: > >> >> This is one of those glass half full/empty situations, where it is >> down to the eye of the beholder. I would also say this a perfect >> example of why tests are written, to see what actually happens >> versus what you think happens. >> >> >> If a user of our product needs to run a test to determine behavior then >> our documentation is flawed - which is the point I am making. >> > > Still not seeing the flaw in the documentation. ... > > >> psql does not see any error due to meta-commands or SQL as fatal - >> which is why the ON_ERROR_STOP option exists. >> > > And ON_ERROR_STOP does not change that. All it does is toggle whether psql > continues on after an error or stops processing commands. If it walks and talks like a duck...the fact that ON_ERROR_STOP makes psql halt processing means that it now treats them like it does any other fatal error. > > > >> I believe that if ON_ERROR_STOP causes an abort that the COMMIT from >> --single-transaction should not run. That is a behavior change. But >> not documenting the known and deterministic interaction between the two >> options is a bug. >> > > I am not seeing anything in the below that says an ABORT is issued: > I was using term in its non-SQL sense: to stop processing and return control to the user. > 2) the implications of \include being a client-side mechanic and thus, >> invisible to the server, is not well explained. Specifically that a >> failure to include is the equivalent of simply omitting the statement >> altogether (aside from the psql warning). i.e., if in an actual >> transaction the server will not issue the standard "error has occurred, >> you must ROLLBACK." message for any subsequent statements in the >> script. This is probably not to the level of a bug but it is related to >> the ON_ERROR_STOP bug. >> > > I could see improving the wording on this, to let the user know that > includes are on them as Viktor already determined and took action on. > > I think you have a typo somewhere here 'cause that sentence fragment (...includes and on them as) makes no sense to me. The overall complaint is that a missing \include file, without ON_ERROR_STOP, ends up being totally ignored even while in non-interactive mode. I get the benefit to that behavior in interactive mode and so being required to use ON_ERROR_STOP in script mode (which is the safest practice anyway) isn't that big a deal as long as in that mode a failure causes an immediate stop without any other SQL being sent to the server and, by extension, the session closing and effecting a rollback in the process if in --single-transaction mode just like that mode promises. I'm not sure why --single-transaction even exists TBH. The script should determine its desired transaction modes and not leave the decision up to the caller. If the script relies on all-or-nothing it should have explicit BEGIN/COMMIT statements. That said it does exist so it should play nicely with ON_ERROR_STOP. It currently does not nor is the not-nice interaction documented anywhere. David J.
Re: [GENERAL] Rollback on include error in psql
Copying -bugs to gain broader attention and opinions. On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver wrote: > On 12/29/2014 08:49 AM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> >> On 12/29/2014 07:59 AM, David Johnston wrote: >> >> >> Anyway, the third undocumented bug is that --single-transactions >> gets to >> send its COMMIT even if ON_ERROR_STOP >> takes hold before the end of the script. I imagined it such >> that only >> if every statement in the "-f
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 9:49 AM, David Johnston wrote: > On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver > wrote: > >> On 12/29/2014 07:59 AM, David Johnston wrote: >> >>> >>> Anyway, the third undocumented bug is that --single-transactions gets to >>> send its COMMIT even if ON_ERROR_STOP >>> takes hold before the end of the script. I imagined it such that only >>> if every statement in the "-f
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver wrote: > On 12/29/2014 07:59 AM, David Johnston wrote: > >> >> Anyway, the third undocumented bug is that --single-transactions gets to >> send its COMMIT even if ON_ERROR_STOP >> takes hold before the end of the script. I imagined it such that only >> if every statement in the "-f
Re: [GENERAL] Rollback on include error in psql
On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver wrote: > On 12/28/2014 05:04 PM, David G Johnston wrote: > > Adrian Klaver-4 wrote > >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: > >>> I include my own scripts. Each of them creates some table or makes some > >>> changes to existing tables. > >> > >> It is hard to say where to go from here without more information. > > > > really? > > Yes. The if, ands and buts for each of the options by themselves much less > in > combination would indicate that an answer is dependent on what is actually > happening in the scripts. We have a fragment of the main script and > not much information as to what is actually happening in the called > scripts. See > below why this is important. > > > > > This seems like a documentation bug (or, at the least worth more > > documentation explanation) at minimum; two of them probably: > > > > 1) it is not documented that "\include" is a valid alias for "\i" > > 2) the implications of \include being a client-side mechanic and thus, > > invisible to the server, is not well explained. Specifically that a > failure > > to include is the equivalent of simply omitting the statement altogether > > (aside from the psql warning). > > Agreed. > > > > > I would suggest an enhancement whereby psql will send a > guaranteed-to-fail > > command to the server upon failing to find an included file - at least in > > non-interactive mode; in interactive mode the warning is likely > sufficient > > though the interplay with auto-commit would be concerning. > > > > > >> The options you are passing to psql all have caveats: > > > > I'm not seeing how any of those caveats are coming into play here. > > > > The ON_ERROR_STOP behavior is actually surprising since psql does indeed > > return 3 but even with single transaction and auto-commit=off any updates > > prior to the include are committed. > > > > This isn't that difficult to test... > > [db] > > CREATE TABLE testtbl (col text PRIMARY KEY); > > INSERT INTO testtbl VALUES ( 'value' ); > > > > [script] > > UPDATE testtbl SET col = 'some other value'; > > \i some_missing_file.sql > > UPDATE testtbl SET col = 'yet another value'; > > [/script] > > > > execute using: > > > > psql --single-transaction --set ON_ERROR_STOP=1 --set AUTOCOMMIT=off -f > >
[GENERAL] psql feature request: --list-conninfo (dump the config info psql would use to connect)
Hi! When psql (libpq) connects it uses a combination of defaults, environment variables, command line arguments, and possibly a pg_service file to figure out where it is going to connect, and how. Specifying the option "--list-conninfo" as an option would cause psql to simply output all of the valid "conninfo" parameters and their current value. It could also indicate whether a matching .pgpass entry was found. By default it would not return the open connection - only dump the data. However, when the "list-conninfo-prompt" option is provided psql would finish the display with a prompt -:"do you wish to connect to this database?". Ideally an option of the form list-conninfo-fields='dbname,username,host,port' could be used to limit the displayed fields - especially useful with the prompt option so that the user does not get a screen full of data to skim. A default config could be designed and made available while in prompt mode (non-prompt mode would list everything) and the -fields option would simply provide an override. I see a use for this in semi-automatic scripts where you wish to add a measure of safety by allowing the user to at least review the connection setup once in the script before using the same connection string on subsequent attempts without prompting. It would also make a useful debugging tool. The interaction of this and various authentication mechanisms would be worth exploring in detail - even if it takes an actual connection attempt to work the details out. While outside the scope of PostgreSQL itself the interaction with tools like pgPool and pgBouncer would be worthwhile - i.e., the ability to pass-through what your intermediary is going to be using to connect. My goal in posting this is to get a reaction and hopefully, if favorable, get this added to the Wiki ToDo area. Its not something I have the ability to code or desire to directly fund but it does seem like a feature that would contribute to new user friendliness and ease-of-use. Thanks! David J.
Re: [GENERAL] Combining two queries
On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco wrote: > Is the intersect any better than what I originally showed? On the ROW > approach, I'm not sure where the context for that is coming from since it > may not be in the intersection. Consider n1 and n2 are NOT friends but they > have >0 mutual friends between them. > > The INTERSECT is a lot more direct about finding mutual friends. The ROW() = ROW() piece is independent of the mutual friends question - it should be put in a WHERE clause and you can test whether a row is returned which, if one is, means the two people are friends. "One Query" does not mean you need to do everything all-at-once. I suggest you make use of CTEs (WITH) subqueries for each distinct calculation you need then join all of the CTE items together in a final query the outputs the data in the format desired. David J.
Re: FW: [GENERAL] SQL rolling window without aggregation
On Monday, December 8, 2014, Huang, Suya wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org ] On Behalf Of David G > Johnston > Sent: Monday, December 08, 2014 1:18 PM > To: pgsql-general@postgresql.org > Subject: Re: FW: [GENERAL] SQL rolling window without aggregation > > Huang, Suya wrote > > It seems like it's not been sent to the SQL group, so I'm trying with > > this group. > > Asked and answered...online archives follow > > > http://postgresql.nabble.com/rolling-window-without-aggregation-td5829344.html#a5829345 > > http://www.postgresql.org/message-id/1417764928965-5829345.p...@n5.nabble.com > > David J. > > > > > > Thanks Dave. I was trying to avoid UNION... > > > Instead of avoiding things get something that works then ask whether there is a better way to do things. At least that way you can provide a working query that others can look at and know exactly what you need. In this case you have a single table and want the result to have more rows than the input - I'm not sure how anything but a union will accomplish that goal. You have to generate those rows somehow. There may be better ways of looking at your problem but you only gave us a toy model to play with. David J.
Fwd: [GENERAL] Array Comparison
Please send replies to the list.
On Friday, December 5, 2014, Ian Harding > wrote:
>
>
> On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> Ian Harding wrote
>> > On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding <
>>
>> > harding.ian@
>>
>> > > wrote:
>> >> I have a function that returns bigint[] and would like to be able to
>> >> compare a bigint to the result.
>>
>> Here are some of your options:
>>
>> http://www.postgresql.org/docs/9.3/interactive/functions-array.html
>> http://www.postgresql.org/docs/9.3/interactive/functions-comparisons.html
>>
>> The direct type-to-type operators are covered in the first link while
>> generic comparison mechanisms - including those the can compare arrays to
>> scalars - are in the second one.
>>
>> There are lots of ways to compare things; e.g., are they equal, is one
>> greater than another and, for multi-valued items, does one contain the
>> other
>> or do they overlap
>>
>>
>> >> select 935::bigint in (select
>> >> fn_descendents('trip'::varchar,61::bigint));
>> >> ERROR: operator does not exist: bigint = bigint[]
>>
>> As shown by the error the application of "IN" simply checks to see if any
>> of
>> the ROWS of the given select match against the left-hand value. That
>> means
>> zero or more evaluations of:
>> bigint = bigint[]
>> which does not makes sense. There is no special evalulation mode for a
>> subquery that only happens to return a single row.
>>
>> Ah. Right. That makes sense.
>
>
>> From the second link above you can express the scalar-to-array comparison
>> you seek through the use of "ANY".
>>
>> bigint = ANY(bigint[])
>>
>> Since your function already returns an array you do not to (and indeed
>> cannot) use a subquery/SELECT. Simply write:
>>
>> 935::bigint = ANY(fn_descendents(...))
>>
>>
>> I sort of figured that out only I fatfingered it to "... IN ANY(..."
>
>
>> >> Hmmm.. This works...
>> >>
>> > select array[935::bigint] <@ (select
>> > fn_descendents('trip'::varchar,61::bigint));
>> >
>> > Still, why?
>>
>> Do you understand the concept of array containment - what it means for an
>> array to contain or be contained by another array? The documentation
>> assumes that concept is known and simply provides the syntax/operators
>> needed to access it.
>>
>>
> Ah, but isn't this the same "There is no special evalulation mode for a
> subquery that only happens to return a single row." from above? I'm asking
> "Is this scalar array contained in the result set of this select for which
> there is no special evaluation mode for the happy coincidence that it only
> has one value? That's what surprised me.
>
>
No. Your query will output one row for every input row the subquery
generates - each row having a true or false value depending on whether the
particular value contains your array constant.
Your initial attempt incorrectly tried to get in the IN to apply to each
element of the only array that was returned but that doesn't work and which
is why the scalar=array comparison failed; the array is never decomposed.
If your replace <@ with IN in this example you would get a single result
(Boolean false in this case) regardless of how many rows the subquery
returns. The IN wraps the subquery expression and makes it into a kind of
scalar while directly using the operator against the subquery causes
multiple evaluations.
See: SELECT generate_series(1,10) - for an idea of how row generating
expressions in the select list behave.
David J.
Re: [GENERAL] Converting xml to table with optional elements
On Fri, Nov 28, 2014 at 10:47 AM, Andrus wrote: > Hi! > > Thank you. > > Instead of defining an xpath for fields define one that captures the xml >> pertaining to the data that would belong to >> a single record.How to create single xpath or xsl which assigns values to >> all columns in Postgres table ? >> > I havent found such sample. Samples which I have found create every column > separately using separate xpath. > > > I don't know - I'm not a heavy user of xpath/xml. I do know that "text()" will not be of help because you have to return an entire node - xml elements included. The textual representation of that node is then to be stored and all the extraction xpath expression executed against it. David J.
Re: [GENERAL] Converting xml to table with optional elements
On Fri, Nov 28, 2014 at 9:40 AM, Andrus wrote: > Hi! > > Thank you. >>Subquery the xpath expression to unnest it and apply a LIMIT 1 > > UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1) > > I used unnest() : > Sorry, I meant to say (SELECT unnest(xpath(tbl.???[...])) LIMIT 1)... > update temprid set > ContactFirstName =unnest(xpath( > > '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text > > Is this OK ? > It may be that the "SELECT" is optional - you should test it. > > > >Note that I do not believe your example code is going to work. As I > mentioned you really want to create a >table of documents and NOT try to > pair up multiple unnested columns. > > How to create table of documents ? > Instead of defining an xpath for fields define one that captures the xml pertaining to the data that would belong to a single record. > > xml contains multiple products and document info. > Code creates table containing one row for every product and adds same > header fields to all rows. > Sounds like you should have two tables... > Whu this will not work ? > > update temprid set ContactFirstName =unnest(xpath( '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text I honestly don't know what the above does or will do in the presence of more than a single row on temprid. Typically UPDATE table1 ... FROM table2 requires a WHERE clause of the form "WHERE table1.id = table2.id"... David J.
Re: [GENERAL] Converting xml to table with optional elements
On Fri, Nov 28, 2014 at 4:17 AM, Andrus wrote:
> Hi!
>
> You have to process this in two passes. First pass you create a table of
>> documents by unnesting the non-optional >Document elements. Second pass you
>> explode each individual row/document on that table into its components.
>>
>
> Thank you. I tried code below. John Smith appears in result as "{"John
> Smith"}"
> How to force it to appear as John Smith ?
>
Subquery the xpath expression to unnest it and apply a LIMIT 1
UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1)
This will cause either the first array element or NULL set to be the given
column's value.
Note that I do not believe your example code is going to work. As I
mentioned you really want to create a table of documents and NOT try to
pair up multiple unnested columns.
David J.
Re: [GENERAL] Range type bounds
On Wednesday, November 26, 2014, Adrian Klaver
wrote:
> On 11/26/2014 12:34 PM, David Johnston wrote:
>
>>
>> I guess what is confusing to me is the transition between the text
>> mode and the constructor mode is not clear. In particular the page
>> starts with examples using the constructor mode but then goes to
>> explanations that actually apply to the text mode before getting
>> back to explaining the constructor mode.
>>
>>
>> They are contained in separate subsections of the documentation...the
>> syntax described in each section only applies to that section. The
>> concept of empty doesn't apply to constructor functions at all.
>>
>
> You get that from this?:
>
> "
>
> 8.17.2. Examples
>
>
Examples do not constitute syntax specifications so while this useful for
understanding it is not enough to generalize from.
> Every non-empty range has two bounds, the lower bound and the upper bound.
> All points between these values are included in the range. An inclusive
> bound means that the boundary point itself is included in the range as
> well, while an exclusive bound means that the boundary point is not
> included in the range.
Use of empty as a value.
>
> The lower bound of a range can be omitted,
Omit is the best concept - implemented by a lack of value in a literal or a
null in a function call.
It gets to be very verbose if we try to anticipate cross-language
>> differences and preemptively explain them away...
>>
>
> Agreed. My example was as a counterpoint to your statement:
>
> "All that said it is taken for granted that you cannot have an empty
> function argument so ('val',) is invalid on its face."
>
> It is not invalid on its face, just for this use case.
Less a use case than a language/system. I do not recall any case where you
can call a function defined in PostgreSQL and leave an argument position
defined but empty. You can skip providing the given position and use
defaults but dangling commas are not allowed. That is taken for granted by
the people writing documentation.
> I am not saying explain all the exceptions, just the rule. In other words
> for the purpose of this function at least two arguments must be provided. I
> realize it does get covered in 8.17.6., but that is after the section I
> quoted at the top which would seem to imply different. Have spent too much
> time on this already, time to actually implement the suggestions:)
>
>
Yes, examples before definition is not all the common in the docs...
David J.
Re: [GENERAL] Range type bounds
> I guess what is confusing to me is the transition between the text mode
> and the constructor mode is not clear. In particular the page starts with
> examples using the constructor mode but then goes to explanations that
> actually apply to the text mode before getting back to explaining the
> constructor mode.
They are contained in separate subsections of the documentation...the
syntax described in each section only applies to that section. The concept
of empty doesn't apply to constructor functions at all.
The part that is problematic is the overloaded use of empty to mean a range
without bounds (a value) and a means to specify an infinite bound (an
input). Using "omitted" for the input case would probably add clarity.
I eventually figured it out. I just thought it might make it easier for
> others to make the distinction clearer.
Suggestions welcomed
>
>> All that said it is taken for granted that you cannot have an empty
>> function
>> argument so ('val',) is invalid on its face. The question becomes whether
>> you should use ('val','') or ('val',NULL). The only place that is
>> answered
>> is a single example. It should be in the body of the text too.
>>
>
> Well I spend my time in Python for the most part so:
>
> def test_fnc(a, b=None):
> print a, b
>
> In [12]: test_fnc('a',)
> a None
>
> I will have to plead ignorance on C.
It gets to be very verbose if we try to anticipate cross-language
differences and preemptively explain them away...
David J.
Re: [HACKERS] Re: [GENERAL] Performance issue with libpq prepared queries on 9.3 and 9.4
On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane wrote:
> David G Johnston writes:
> > Tom Lane-2 wrote
> >> In the meantime, I assume that your real data contains a small
> percentage
> >> of values other than these two? If so, maybe cranking up the statistics
> >> target would help. If the planner knows that there are more than two
> >> values in the column, I think it would be less optimistic about assuming
> >> that the comparison value is one of the big two.
>
> > Is there any value (or can value be added) in creating a partial index of
> > the form:
>
> > archetype IN ('banner','some other rare value')
>
> > such that the planner will see that such a value is possible but
> infrequent
> > and will, in the presence of a plan using a value contained in the
> partial
> > index, refuse to use a generic plan knowing that it will be unable to use
> > the very specific index that the user created?
>
> The existence of such an index wouldn't alter the planner's statistics.
> In theory we could make it do so, but I seriously doubt the cost-benefit
> ratio is attractive, either as to implementation effort or the added
> planning cost.
>
>
>
[adding -general back in...]
While "planner hints" comes to mind...on the SQL side can we extend the
"PREPARE" command with two additional keywords?
PREPARE
name [ ( data_type [, ...] ) ] [
[NO] GENERIC
]
AS statement
I was originally thinking this could attach to EXECUTE and maybe it could
there as well. If EXECUTE is bare whatever the PREPARE used would be in
effect (a bare PREPARE exhibiting the current dynamic behavior). If
EXECUTE and PREPARE disagree execute wins and the current call is
(re-)prepared as requested.
We have introduced intelligence to PREPARE/EXECUTE that is not always
favorable but provide little way to override it if the user has superior
knowledge. The dual role of prepared statements to both prevent
SQL-injection as well as create cache-able generic plans further
complicates things. In effect by supplying NO GENERIC on the PREPARE the
caller is saying they only wish to make use of the SQL-injection aspect of
prepared statements. Adding the EXECUTE piece allows for the same plan to
be used in injection-prevention mode if the caller knows that the
user-supplied value does not play well with the generic plan.
David J.
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
Yes, that is what I was referring to. The Nabble.com website showed them. http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html David J. On Sunday, November 9, 2014, Magnus Hagander wrote: > On Sun, Nov 9, 2014 at 11:37 PM, David Johnston > > wrote: > > > > > > On Sunday, November 9, 2014, Adrian Klaver > > > wrote: > >> > >> On 11/09/2014 10:14 AM, David G Johnston wrote: > >>> > >>> Adrian Klaver-4 wrote > >>>>> > >>>>> Thank you for all comments and suggestions. > >>>> > >>>> > >>>> More comments/suggestions will have to wait until the missing pieces > are > >>>> filled in. > >>> > >>> > >>> I read most of these mailing list emails via Nabble and the pieces you > >>> show > >>> as missing are present in what I am reading. If I go to reply and > quote > >>> the > >>> original message the missing sections are sour rounded by "raw" tags. > >> > >> > >> Hmm, is there a way to make Nabble aware of this and fix it? > >> > >>> > >>> Looking at the official mailing list archive these sections are missing > >>> there. > >>> > >>> > >> > >> > >> -- > >> Adrian Klaver > >> adrian.kla...@aklaver.com > > > > > > I don't know how the OP sent the original e-mail but since I could read > the > > problem areas the question is why other e-mail clients aren't seeing > them... > > I'd be more interested in how *you* could see them - unless you are > just referring to seeing them on nabble.com? > > The original as delivered through the mailinglist is in it's raw form > at > http://www.postgresql.org/message-id/raw/1415506067738-5826230.p...@n5.nabble.com > - which does not contain those parts. And it wasn't event sent as > multipart, so there is not much of ways to misparse it. > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ >
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
On Sunday, November 9, 2014, Adrian Klaver wrote: > On 11/09/2014 10:14 AM, David G Johnston wrote: > >> Adrian Klaver-4 wrote >> >>> Thank you for all comments and suggestions. >>> >>> More comments/suggestions will have to wait until the missing pieces are >>> filled in. >>> >> >> I read most of these mailing list emails via Nabble and the pieces you >> show >> as missing are present in what I am reading. If I go to reply and quote >> the >> original message the missing sections are sour rounded by "raw" tags. >> > > Hmm, is there a way to make Nabble aware of this and fix it? > > >> Looking at the official mailing list archive these sections are missing >> there. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > I don't know how the OP sent the original e-mail but since I could read the problem areas the question is why other e-mail clients aren't seeing them...
Re: [GENERAL] Query optimization
On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo wrote: > > SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9', > 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9, > field10, field11, field12, field13, field14]) as metadata, value7, (select > array((select row(f1, f2) from table2 p where p.f3 = field7))) as > values_array FROM table1 > More generally, you really should table-prefix all column in correlated subqueries. [...] from table2 p where p.f3 = table1.field7 [...] I guess the InitPlan 1 you showed simply scanned table2 and applied the filter which then was fed to InitPlan 2 where the array is built; that array then is inserted into the outer query ~8M times... David J.
Re: [GENERAL] Query optimization
On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane wrote: > Jorge Arevalo writes: > > > This is the result of EXPLAIN ANALYZE > > >QUERY > > PLAN > > > - > > Index Scan using table1_pkey on table1 (cost=67846.38..395773.45 > > rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127 > > loops=1) > >InitPlan 2 (returns $1) > > -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual > > time=7009.063..7009.065 rows=1 loops=1) > >InitPlan 1 (returns $0) > > -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689 > > width=20) (actual time=14.971..5069.840 rows=2537787 loops=1) > >Filter: (f3 = field7) > > Hm. If I'm reading that right, you're building an array containing > 2537787 entries, each of which is a composite datum containing two > columns of unmentioned datatypes. I suspect a big chunk of your > runtime is going into manipulating that array -- PG is not terribly > efficient with big arrays containing variable-width values. > > I'm also a bit confused as to why the planner is saying that the (SELECT > ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that > "field7" in the innermost WHERE clause is not a reference to table1 but a > reference to table2. Is that really what you meant? IOW, are you sure > this query is performing the right calculation in the first place? > > I thought the InitPlan was in place because the planner choose to execute the correlated subquery as a standalone query since it realizes that it is going to have to end up processing the entire table anyway due to the lack of a filter on the outer query. In effect executing "table1 JOIN (table2 subquery) ON (f3 = field7)". David J.
Re: [GENERAL] Query optimization
List preference is to inline post or, at worse, bottom post. Please do not top post. On Wed, Oct 29, 2014 at 11:06 AM, Jorge Arevalo wrote: > Hello David, many thanks for your responses, > > Sorry for not providing the content of the fill_table3_function, but it > just executes 3 insert queries in 3 different tables. And I've checked the > time consuming operation is in this query (by the way, there was a little > mistake in the name of the fields of the inner select, I've corrected it) > > SELECT value1,value2,value3,value4, > value5, hstore(ARRAY['field9', 'field10', 'field11', 'field12', 'field13', > 'field14'], ARRAY[field9, field10, field11, field12, field13, field14) as > metadata, value7, (select array((select row(f1, f2) from table2 p where > p.f3 = field7))) as values_array FROM table1 > > This is the result of EXPLAIN ANALYZE > >QUERY > PLAN > > - > Index Scan using table1_pkey on table1 (cost=67846.38..395773.45 > rows=8419127 width=88) (actual time=7122.704..22670.680 rows=8419127 > loops=1) >InitPlan 2 (returns $1) > -> Result (cost=67846.29..67846.29 rows=1 width=0) (actual > time=7009.063..7009.065 rows=1 loops=1) >InitPlan 1 (returns $0) > -> Seq Scan on table2 p (cost=0.00..67846.29 rows=12689 > width=20) (actual time=14.971..5069.840 rows=2537787 loops=1) >Filter: (f3 = field7) > > So, there's a sequential scan over table2 (in the query to get > values_array), instead of a index scan. Could it be because the SELECT > returns more than approximately 5-10% of all rows in the table? (I've heard > that, under those conditions, a sequential scan is faster than index scan, > because the amount of I/O operations required for each row) > What concerns me here is that the rows estimate on that table2 scan is ~13k while the actual count is ~ 2.5M; you need to run ANALYZE on both tables and see if your get similar results. Though given the need for sequential scan regardless (see next comment) the estimate miss likely doesn't affect actual performance or the plan that is chosen. But it is still worth looking into. > > Anyway, if I understood well, I should try: > > - Avoiding that inner query by using a JOIN instead > I don't know...looking at your explain (and some reasoning) it looks as if it is already doing that for you since there is only a single loop for the InitPlan 1. This is a little beyond my comfort zone but you've now provided a decent amount of information for others to speculate...though it would help to enable various timings as well and try and run the full query (with the function) in a development environment so that the entire routine can be evaluated. > - Return a composite type instead of an array > > Worth looking into but impossible to recommend without knowing what your make believe fields are and are used for. More style than performance since I do not know the relative costs of building up an array and creating a composite.
[GENERAL] Re: How can i monitor exactly what (partition) tables are accessed by a query?
On Friday, September 19, 2014, Alban Hertroys wrote: > On 19 Sep 2014, at 3:50, Robert Nix > > wrote: > > > Thanks, David. > > > > I have read that page many times but clearly I have forgotten this: > > > > • Constraint exclusion only works when the query's WHERE clause > contains constants (or externally supplied parameters). For example, a > comparison against a non-immutable function such asCURRENT_TIMESTAMP cannot > be optimized, since the planner cannot know which partition the function > value might fall into at run time. > > > > I had worked around this "issue" some time ago but I clearly should have > documented _why_ I worked around it in the way I did. > > What may be worth a try is to join against a UNION ALL of your partitions, > with each section of the UNION having an explicirt WHERE clause matching > your partitioning constraints. > The idea there is that such a UNION could provide the explicit constant > WHERE clauses that your JOIN implicitly depends on. > That makes no sense. If you join against partitions instead of the parent then the contents of the where clause on those partition queries is irrelevant. Furthermore, combining a bunch of of queries via union is exactly what PostgreSQL is doing when it executes the original plan - it's just you are doing it manually. I may be getting your thoughts confused here but if so that's mostly due to the lack of any concrete query examples to evaluate. David J.
Re: [GENERAL] Questions on dynamic execution and sqlca
> > > > > - What are the differences among PL/SQL, PL/PGSQL and pgScript. > > > > The first two are languages you write functions in. pgScript is simply > an > > informal way to group a series of statements together and have them > execute > > within a transaction. > > > > AFAICT, this isn't true. Pgscript is a client specific language. There is > a whole description of what it can do in pgadmin's manual. This was > interesting when PostgreSQL didn't have the DO statement. Now that we do, > it's rather pointless. > > > Yeah, I probably should have either researched the answer or just left it alone. I am not all that familiar with pgAdmin - I figured it was just a souped up script runner with maybe a couple of features like variables but otherwise allowing only SQL commands. David J.
Re: [GENERAL] Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr
On Wed, Aug 6, 2014 at 10:08 AM, Jeff Janes wrote: > On Tue, Aug 5, 2014 at 4:30 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> >> Anyway, you should probably experiment with creating a multi-column index >> instead of allowing PostgreSQL to BitmapAnd them together. Likely the >> timestamp will have higher cardinality and so should be listed first in >> the >> index. > > > No, the timestamp should almost certainly come second because it is used > with inequality operators. > > Wouldn't that only matter if a typical inequality was expected to return more rows than a given equality on the other field? Depending on the cardinality of the ID field I would expect a very large range of dates to be required before digging down into ID becomes more effective. My instinct say there are relatively few IDs in play but that they are continually adding new rows. What statistics would the OP have to provide in order to actually make a fact-based determination? David J.
Re: [GENERAL] Re: Feature proposal and discussion: full-fledged column/function equivalence
On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers wrote: > On Fri, Aug 1, 2014 at 12:19 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> Vik Fearing wrote >> >> CREATE testfunction(test) returns int language sql as $$ select 1; $$; >> >> SELECT testfunction FROM test; >> >> >> >> That would allow first-class calculated columns. >> >> >> >> I assume the work is mostly at the parser/grammatical level. Is there >> >> any reason why supporting that would be a bad idea? >> > >> > This is already supported since forever. >> > >> > SELECT test.testfunction FROM test; >> >> More to the point: if you are writing a multiple-relation query and have >> "testfunction" functions defined for at least two of the relations used in >> the query how would the system decide which one to use? >> > > Same way you do it for columns. Throw an error that it is ambiguous. > > I'd rather approach the first-class issue by being able to say: ALTER TABLE test ADD COLUMN testfunction(test) -- maybe with an "AS col_alias"... I do not have anything particularly against your proposal but neither do I find it an overwhelming improvement over "testfunction(test) and test.testfunction" - especially when I can encapsulate them behind a VIEW. > >> SELECT testfunction FROM test JOIN test_extended USING (test_id) >> >> I guess you could allow for the non-ambiguous cases and error out >> otherwise >> but that seems to be adding quite a bit of complexity for little gain. >> > > Hmm. As I see it, there is one possible backwards compatibility issue but > it is almost certainly extraordinarily rare. > > Suppose in your above example, test_extended has a testfunction attribute > but test has a testfunction function. In the current codebase, there is no > parsing ambiguity (the attribute wins because the function is ignored), but > we'd have to throw the same error as if the function were an attribute if > we did this. > > It doesn't seem terribly logically complicated to do this (since it is a > slight extension to the lookup in the system catalogs), and I am having > trouble imagining that there are many cases where these sorts of functions > are added. > > The larger question becomes: > > Would it be more useful to have such functions in the select * result, or > to treat them as hidden columns from that? (I am thinking that can be > decided down the road though if I go through and take this up on -hackers). > >> >> >> If they truly are "first class" members of the table they should probably appear with " SELECT * "; otherwise, and this is simply semantics, you are simply adding yet another syntax to remember to invoke a function since the user will still have to know said function exists. I read "first class" to mean that the fact the value is being derived from a function call is invisible to the user. And this then points leads back to the idea of defining a generated column on the actual table or, in absence of that capability - live with the fact the updateable can accomplish many, if not all, of the same goals today. David J.
Re: [GENERAL] Referencing serial col's sequence for insert
On Tue, Jul 22, 2014 at 9:46 AM, Anil Menon wrote:
> Am a bit confused -which one comes first?
>
> 1) the 'data'||currval('id01_col1_seq') is parsed first : which means it
> takes the current session's currval
> 2) then the insert is attempted which causes a sequence.nextval to be
> performed which means that 'data'||currval('id01_col1_seq')will be
> different from the sequence's value
>
>
If this was the case currval would always emit an error for the first
insert of the session...
or
>
> 1) an insert is attempted which causes a sequence.nextval to be performed
> and then
> 2) the cols are parsed for the insert so the 'data'||currval('id01_col1_seq')
> has the correct value
>
> I observe the latter on my single session notebook instance of postgres.
>
>
And given that it is the logical conclusion why are you confused?
To be honest I totally missed the dual-column nature of the OP. I read it
as simply wishing to use the sequence value in a string instead of, not in
addition to, the "serial" defined column.
I do not know whether the call to nextval in the default will always occur
before any currval expression in the source query...it might make more
sense, for multiple reasons, to simply define a trigger to enforce the
value of "col2". A user-defined trigger will always be evaluated after the
default expression and so you can simply pick off the value assigned to
"col1" and do what you'd like with it. Combined with a constraint you can
remove the entire business rule from user logic and embed it into the
database where it cannot be messed up.
David J.
Re: Re : [GENERAL] Query "top 10 and others"
> > > with QRY as (select C1.country, C1.state, sum(C1.population) > > from places C1 > > group by 1, 2 > >order by 3 DESC > > limit 10) > > > > select * from QRY > > union > > select 'others' as "country", '' as "state", sum(population) > > from places > > where not exists (select 1 from QRY where country = QRY.country and state > > = QRY.state) > > > > (not tested) with QRY as ( SELECT country, state, sum(population) as st_pop FROM places GROUP BY country, state ) , u1 AS ( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC LIMIT 10 ) , u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY WHERE NOT EXISTS ( SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state) ) SELECT * FROM u1 UNION ALL SELECT * FROM u2 ; David J.
Re: [GENERAL] Question regarding DEALLOCATE pdo_stmt_00000001
> > >> 9.3 - On an idle connection the value of query is the last executed query >> - >> which in this case is some form session cleanup command before returning >> the >> connection to the pool. >> >> > So, it is a normal behavior in Postgres. > > One more thing that bothers me, why this idle connection can be stayed > idle for 3 days. Is this a zombie process? > > You said: "I am using PostgreSQL 9.3.2 and PgBouncer." It is PgBouncer that is keeping these connections open and available for immediate usage in its connection pool. David J.
Re: [GENERAL] create temp view from function inside plpgsql function.
Re-posting, see quote David Johnston wrote > > Pavel Stehule wrote >> 2014-04-04 14:16 GMT+02:00 Tjibbe < >> tjibbe@ >> >: >> >>> Thanks that works! >>> >>> Little bit confusing ERROR. >>> >> >> yes, it could be - but hard to fix it, because it is based on cooperation >> two worlds - plpgsql and SQL - and it is sometimes not simple. >> >> When you understand how plpgsql interpret use variables in SQL queries, >> then you understand to this message. Simple rule - never use plpgsql >> variables in DDL > Yet, IIRC, if you had done a CREATE TEMP TABLE instead of view the query > would have worked just fine. The issue is with CREATE VIEW specifically > because the query itself is part of the final content whereas for CREATE > TABLE the query is only used to generate the data which is then stored. > > David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/create-temp-view-from-function-inside-plpgsql-function-tp5798658p5799286.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] check constraint question
Based on your first question a customer id itself is not a valid designator; you have to specify (or link) in the group as well. Not tested but should work: FOREIGN KEY (template, group) REFERENCES customer (cust_id, group) Depends on whether you want to allow cross-group associations if you need a separate group template id. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/check-constraint-question-tp5799252p5799282.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] Remote troubleshooting session connection?
Jim Garrison wrote > Assuming I can pause the driving Java code between queries in a job, is > there any way to connect from PGAdmin (or another tool) and view the state > of tables in the in-progress transaction? If this is not currently > possible, how difficult would it be to do? What you want is "dirty read" semantics which none of the implemented transaction isolation levels permit - so no you cannot go make an uncommitted change and read the results from another session. http://www.postgresql.org/docs/9.1/static/transaction-iso.html That said I thought there was a project out there, probably related to data recovery, that allows one to examine the system without respecting any transactional boundaries. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Remote-troubleshooting-session-connection-tp5798810p5798823.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] window function help
Andy Colson wrote > On 4/3/2014 11:09 AM, David Johnston wrote: >> Andy Colson wrote >>> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: >>>> I'm trying to figure out how to count the number of rows within a fixed >>>> range of the current row value. My table looks like this: >>>> >>>> SELECT chr_pos >>>> FROM mutations_crosstab_9615_99 >>>> WHERE bta = 38 >>>> LIMIT 10 >>>> >>>> chr_pos >>>> 138 >>>> 140 >>>> 163 >>>> 174 >>>> 187 >>>> 187 >>>> 188 >>>> 208 >>>> 210 >>>> 213 >>>> >>> >>> This is the answer I got, which is different than yours, but I think its >>> right. >>> >>> >>>chr_pos | count >>> -+--- >>>138 | 2 >>>140 | 2 >>>163 | 2 >>>174 | 4 >>>187 | 3 >>>188 | 4 >>>208 | 5 >>>210 | 4 >>>212 | 4 >>>213 | 4 >>> (10 rows) >> >> Same concept as mine - but I'm not sure where the "212" came from and you >> did not duplicate the "187" that was present in the original. >> >> The OP wanted to show the duplicate row - which yours does and mine does >> not >> - but depending on how many duplicates there are having to run the same >> effective query multiple times knowing you will always get the same >> result >> seems inefficient. Better to query over a distinct set of values and >> then, >> if needed, join that back onto the original dataset. >> >> David J. >> > > > > > Same concept as mine - but I'm not sure where the "212" came from and > you > > did not duplicate the "187" that was present in the original. > > Ah, data entry error. I didn't even notice. Oops. > > > The OP wanted to show the duplicate row - which yours does and mine > does not > > Did you post a sql statement? I didn't seem to get it. > > > - but depending on how many duplicates there are having to run the same > > Agreed. If there are a lot of dups, we could probably speed this up. > > -Andy My original seems to be held up for some reason... Let me try again: WITH val (value) AS ( VALUES (138),(140),(163),(174),(187),(187),(188),(208),(210),(213) ) SELECT value, (SELECT count(*) FROM val AS valcheck WHERE valcheck.value BETWEEN src.value - 20 AND src.value + 20) FROM ( SELECT DISTINCT value FROM val ) src ORDER BY 1; -- View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-help-tp5798526p5798565.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] window function help
Andy Colson wrote > On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: >> I'm trying to figure out how to count the number of rows within a fixed >> range of the current row value. My table looks like this: >> >> SELECT chr_pos >> FROM mutations_crosstab_9615_99 >> WHERE bta = 38 >> LIMIT 10 >> >> chr_pos >> 138 >> 140 >> 163 >> 174 >> 187 >> 187 >> 188 >> 208 >> 210 >> 213 >> > > This is the answer I got, which is different than yours, but I think its > right. > > > chr_pos | count > -+--- > 138 | 2 > 140 | 2 > 163 | 2 > 174 | 4 > 187 | 3 > 188 | 4 > 208 | 5 > 210 | 4 > 212 | 4 > 213 | 4 > (10 rows) Same concept as mine - but I'm not sure where the "212" came from and you did not duplicate the "187" that was present in the original. The OP wanted to show the duplicate row - which yours does and mine does not - but depending on how many duplicates there are having to run the same effective query multiple times knowing you will always get the same result seems inefficient. Better to query over a distinct set of values and then, if needed, join that back onto the original dataset. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-help-tp5798526p5798542.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] pg_stat_activity
Jeff Janes wrote > On Wed, Apr 2, 2014 at 12:00 PM, Bala Venkat < > akpgeek@ > > wrote: > >> We are using postgres 9.0. When I looked at the pg_stat_activity table. >> >> I have some rows where there is difference of 2 hours between >> backend_start and xact_start >> >> But there is only few milli seconds between xact_start and query_start. >> >> All them have wait as false. >> >> My question is, is the query still executing and also why would there be >> that much time difference between backend_start and xact_start >> > > Say I connect to the database, and then I go to lunch for 2 hours. Then I > come back and start a transaction. > > Or, I connect to the database and run a two-hour query. Then > rollback/commit that, and I start another transaction. > > To know the state of the query, look at the "current_query" field. (In > newer versions, look in "state" field, but that is not in 9.0) > > Cheers, > > Jeff Or more commonly (I think at least) connection pools that keep connections open. Not sure but I don't think a session reset clears the back-end time so this would be the observed behavior. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-stat-activity-tp5798382p5798390.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] Insert zero to auto increment serial column
loc wrote > Setting the serial column to null to auto increment would also > work for me. Can you set it to a literal value DEFAULT? Only helps for the insert case (not copy) but that is the mechanism that is used to specify a column and ask for the default. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Insert-zero-to-auto-increment-serial-column-tp5798318p5798336.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] SQL works but same function is confused
Bui, Michelle P wrote > #variable_conflict use_variable > DECLARE > v_status TEXT; > BEGIN > RETURN QUERY SELECT category, v_status as status, count (tool_id) AS > tool_count > FROM > (SELECT distinct category, tool_id, 'active' as v_status Seriously? Just pick a different alias for the 'active/inactive' column in the sub-query. Problem solved. Or, even smarter, don't even declare the variable since you never actually use it anywhere in the function... The variable_conflict variable should generally be used for backward compatibility and not for newly coded functions. For those just choose names that do not conflict. The exception is for function return names that you want to match existing column names in which case you need to prefix appropriately. You may want to provide your attempt to name the block to see if we can figure why it gave a syntax error. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-works-but-same-function-is-confused-tp5798277p5798298.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] simple update query stuck
Andrew Sullivan-8 wrote > On Tue, Apr 01, 2014 at 01:37:17PM -0700, Si Chen wrote: >> You are right. That was the problem. I tried the query from >> http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT >> transaction that was blocking it. >> >> I restarted postgresql again, and (it seems) everything went back to >> normal. Was there another way to unlock the table then? http://www.postgresql.org/docs/9.2/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE I am curious why your original scan of pg-stat-query did not show this offending session/transaction...did you omit it by chance? > Probably you could have killed one of the queries. But it sounds like > what's happening is that you have multiple queries that are all trying > to update the same rows in a different order. It may be that none of > these is strictly deadlocked, in that no query is waiting on a lock > that another query has, but rather is waiting on a lock that another > query will release only when _it_ gets a lock that another query has > and so on. (Maybe things have gotten better, but in my experience > it's possible to set up a chain of locks such that it doesn't look > like a deadlock to the detector, but the lock chain is such that no > query will ever be able to release.) > > I suspect you need to get your locks in a consistent order or you'll > continue to have this problem. It sounds more like a long-running transaction (or something effectively similar) is blocking updates of the target table through normal locking. To be honest we seem to be lacking enough information, including the query and locks state of the server during the stall, to make an informed guess as to the real cause. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/simple-update-query-stuck-tp5798237p5798261.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] Getting sequence-generated IDs from multiple row insert
Andrew Sullivan-8 wrote >> So currently I've changed my code to use RETURNING and then I'm ordering >> the results based on a secondary column that I know the order of. This >> works, but seems clunky, so I'm wondering if there's a nicer way. > > This is probably what I'd do, assuming that "further processing" isn't > more data transformation. If it _is_, then I'd do the whole thing in > a single step (in the database, once I inserted). If order is an implicit property of the source data then you need to explicitly encode that order during (or before) import. There are numerous ways to implement such but except for extremely simple cases PostgreSQL will not do the appropriate thing automatically in the face of concurrency. Also, do you need sequential IDs or just IDs that are ever increasing? And if the later then tagging the input source will let you distinguish between two different datasets even if their sequences are overlapping. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Getting-sequence-generated-IDs-from-multiple-row-insert-tp5798092p5798107.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] getting the current query from pg_stat_activity
Si Chen-2 wrote > I have two different postgresql servers running slightly [different] > versions. Versions 9.0 and 9.2 are NOT slightly different. These are two MAJOR RELEASES (which allow for API changes) apart (i.e., one major release in between - 9.1) The release notes for 9.2 note this particular change explicitly: http://www.postgresql.org/docs/9.2/interactive/release-9-2.html Section E.9.2.6 Note the presence of the "state" column in the 9.2 schema - you use this to determine if a connection is "idle" instead of looking for "" in a query column which then allows the query column to be report the "last known query" at all times. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/getting-the-current-query-from-pg-stat-activity-tp5798076p5798098.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] Complex query
Leonardo M. Ramé-2 wrote > select lag.id, lag.idtask, lag.code, lag.lg from (select idtask, code, id, > lag(code, -1) over () as lg from tasks_test) as lag First you want to include an ORDER BY in the OVER(...) clause, and probably a PARTITION BY as well. Then you move that to a sub-query (for example): SELECT * FROM tbl WHERE tbl.idtask IN ( SELECT lag.idtask FROM ( lag_query_here ) lag WHERE lag.code = 'T' and lag.lg = 'S' ); David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Complex-query-tp5798061p5798087.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] Complex query
Leonardo M. Ramé-2 wrote > Hi, I'm looking for help with this query. > > Table Tasks: > > IdTask StatusCode StatusName > -- > 1 R Registered > 1 S Started > 1 D Dictated > 1 F Finished > 1 T Transcribed > -- > 2 R Registered > 2 S Started > 2 T Transcribed > 2 F Finished > > As you can see, I have a table containing tasks and statuses. What I > would like to get is the list of tasks, including all of its steps, for > only those tasks where the StatusCode sequence was S followed by T. > > In this example, the query should only return task Nº 2: > > 2 R Registered > 2 S Started > 2 T Transcribed > 2 F Finished > > Can anybody help me with this?. First you need to decide how tell the database that R-S-T-F is ordered and then maybe you can use window functions, specifically "lag(col, -1) over (...)", to determine what the prior row's code is and act accordingly. Put that into a sub-query and return the "IdTask" to the outer query's where clause. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Complex-query-tp5798061p5798068.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] To monitor the number of PostgreSQL database connections?
Nithya Soman wrote
> Hi
>
> Could you please provide any method (query or any logfile) to check
> max connections happened during a time interval in psql DB ?
Only if the time interval desired in basically zero-width (i.e.,
instantaneous). The "pg_stat_activity" view is your friend in this.
You have numerous options, including self-coding, for capturing and
historically reviewing these snapshots and/or setting up monitoring on them.
This presumes you are actually wondering "over any given time period how
many open connections were there"? If your question is actually "In the
given time period did any clients get rejected because {max connections}
were already in use." you can check the PostgreSQL logs for the relevant
error.
Bruce basically said this question while Brian answered the first question.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/To-monitor-the-number-of-PostgreSQL-database-connections-tp5797571p5797608.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] PG choosing nested loop for set membership?
Brian Crowell wrote > Hello, it's me, a Postgres n00b again. I'm dealing with a query that > scans a rather large table (94,000,000 tuples or so) and just picks > out certain rows and sums them: > > select dci.snapshot_time as "time", round(sum(dci.todays_pl)::numeric,0) > as pl > from dbo._pl_data_cache_intraday dci > where dci.snapshot_time between '2014-03-25 > 11:32:40.004552-05'::timestamptz and '2014-03-25 > 12:02:40.015177-05'::timestamptz > and dci.symbol in (select sec.symbol from dbo.security_underliers > sec where sec.ultimate_underlier = 'SPY') > and dci.manager = 'BJC' > and dci.account in (select account from pl2.visible_accounts where > is_fund) > group by dci.snapshot_time > order by dci.snapshot_time; > > For the most part, Postgres is doing the right thing: snapshot_time is > the lead column in all of the table's indexes, so it's able to pick up > the source rows fairly quickly in its index scan. It's also enforcing > "dci.manager = 'BJC'" in the same scan, and does a Hash Semi Join for > "dci.symbol in (...)". > > The trouble comes when enforcing the "dci.account in (...)" search > condition: pl2.visible_accounts is a view that determines which > accounts the current user can see, which, depending on who you are, > can be several hundred or none at all. Postgres estimates the output > of this query as two rows, but in my case, it's actually 240. > > Unfortunately, that leads the query planner to try to think a nested > loop is cheap enough to enforce this, when actually it's really > expensive. > > If I hard-code the results from pl2.visible_accounts, Postgres will do > a hash semi join for me, which is much faster, but then I have to wrap > up this whole query as a function in order to preserve its security > properties. Not only is that the situation I was trying to avoid, it > means I can't use EXPLAIN for my query anymore. > > I've noticed I can also do the really sneaky "dci.account in (select > unnest(array_agg(account)) from pl2.visible_accounts)", which tricks > the estimator into thinking there will be 100 rows. That _really_ > feels like cheating. > > Besides the above, is there anything I can do to get Postgres to do a > hash instead of a nested loop? 1) Try using EXISTS instead of IN 2 - and the one I'd use by default) Use an INNER JOIN SELECT ... FROM ... dci JOIN (SELECT account FROM ... WHERE is_fund) accts USING (account) JOIN (SELECT symbol FROM ... WHERE ... = 'SPY') sec USING (symbol) WHERE ... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PG-choosing-nested-loop-for-set-membership-tp5797457p5797459.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] Passing array of range literals
Glenn Pierce wrote
> I cannot work out how to pass a literal for the array of timerange types.
> '{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
> Also I can not pass NULL for this parameter I get
since NULL can take on any type if you pass it literally you have to specify
the type you need:
NULL::timerange[]
Though I'd suggest passing in an empty array instead:
ARRAY[]::timerange[]
Note this is also the best way to construct the array:
ARRAY['(15:11:21,18:11:21)','(19.11.22,21:12:17]']::timerange[]
Otherwise you need to use double-quotes somewhere. Once you construct an
array as above just cast it to text and let PostgreSQL tell you what the
text representation would look like if you cannot use the ARRAY[] form.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Passing-array-of-range-literals-tp5797031p5797062.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] SQL advice needed
Torsten Förtsch wrote > Hi, > > I have a volatile function that returns multiple rows. It may also > return nothing. Now, I want to write an SQL statement that calls this > function until it returns an empty result set and returns all the rows. > > What's the best (or at least a working) way to achieve what I want? > > I can do it in plpgsql. But that would mean to accumulate the complete > result in memory first, right? I need to avoid that. You are describing procedural logic. If you need intermediate steps before "returns all the rows" then either those intermediate steps stay in memory OR you stick them on a table somewhere and, when your procedure is done, send back a cursor over that, possibly temporary, table. I don't think abusing WITH/RECURSIVE is going to be viable. You should also consider whether you can do what you need using set-logic (i.e., pure SQL). At worse it will be a learning exercise and a performance comparator. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-advice-needed-tp5796431p5796436.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] A user's interpretation (and thoughts) of the WAL replay bug in 9.3
I'm trying to follow the discussion on -hackers and decided I'd try putting everything I'm reading into my own words. It is probable some or even all of the following is simply wrong so please do not go acting on it without other people providing supporting evidence or comments. I am a database user, not a database programmer, but feel I do have a solid ability to understand and learn and enough experience to adequately represent a moderately skilled DBA who might see the release notes and scratch their head. During the application/restoration (replay) of WAL the modification of indexes may not be performed correctly resulting in physical rows/keys not having matching index entries. Any subsequent attempt to add a duplicate of the existing physical key will therefore succeed; thus resulting in a duplicate physical record being present and any future attempt to REINDEX the unique index column(s) to fail. A typical replay scenario would first have a row on the PK side of a relationship updated (though not any of the key columns - or any other indexed columns - since this is hot-related...?). If this update takes sufficiently long (or concurrency is otherwise extremely high) that another transaction attempted to take a lock on the PK (e.g., so that it could validate an FK relationship) then a "tuple-lock" operation is performed and added to the WAL. The reply of this WAL entry caused the locked index row to be effectively invisible. The core alteration in 9.3 that exposed this bug is that in order to improve concurrency updates to rows that did not hit indexes (i.e., hot-update capable) allowed other non-updating transactions to simultaneously acquire a lock sufficient to ensure that the core row elements (those that are indexed) remained unaltered while not caring whether specific non-indexed attributes were altered. Prior to 9.3 update locking was sufficient (i.e. exclusive) to cause the other sessions to wait for a lock and thus never hold one simultaneously. In that situation the WAL replay was effectively serialized with respect to the transactions and the index entry modifications were unnecessary but not incorrect. The most obvious test-and-correct mechanism would be to try and create new indexes and, if the creation fails, manually remove the duplicate rows from the table. A table re-write and/or re-index could only work if no duplicates entries were made (I am not sure of this line of thought...) so, for instance, if the PK column is tied to a sequence then no duplicates would ever have been entered. The unknown, for me, is whether MVCC duplication is impacted in which case any update could introduce a duplicate. Regardless of the duplicate record issue as soon as the replay happens the system cannot find the corresponding entry in the index and so any select queries are immediately at risk of silently returning incorrect results. I suppose any related FK constraints would also be broken and those too would remain invisible until the next forced validation of the constraint. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/A-user-s-interpretation-and-thoughts-of-the-WAL-replay-bug-in-9-3-tp5796432.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] pgpgout/s without swapping--what does it mean?
Kevin Goess wrote > Can anybody help me understand what these statistics are suggesting, > what's > actually going on on this box/in postgresql? What is it writing to disk, > and why? Is it just writing out new/changed rows, or what? Not a clue on the statistics but most likely you are seeing checkpoint activity. At a simplified level all changes to the database are first persisted to disk using WAL (write-ahead-logs). The changes are written to disk, into WAL files, during commit via fsync. The original data files are not affected immediately thus improving performance at the time of commit by instead risking a prolonged delay in situations where an unclean shutdown occurs. However, at some point the WAL files need to be removed and the physical table files updated. This occurs during a checkpoint. A checkpoint basically causes the on-disk files to become baselined to the current reality so that only subsequent WAL files need be applied. There is considerably more to this whole concept than I can go into off the top of my head but in addition to looking at just I/O it would help to look, simultaneously, at what processes are active. HTH David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pgpgout-s-without-swapping-what-does-it-mean-tp5796346p5796355.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] FATAL: the database system is starting up
fluxh wrote > I have the same problem. I have a log like patrick keshishian. I have not > a backup. > > I don't know that do. > > Help me please!!! > > Information: > > - Server with RAID1 on SAS HD Hot-plug. > - Ubuntu Server 12.04 x64. > - Postgresql 9.1. > > When I do "psql" PostgreSQL shows "psql: FATAL: the database system is > starting up" and when I write pg_dump or I try connect with pgAdmin3. You should supply your actual log file and also provide some narrative of what exactly you were doing prior to this condition occurring. Some context as to architecture wouldn't hurt - such as how many and what kinds of applications are connecting to the database, especially those that maintain persistent connections. System commad output like top and ps may also be of value though after you provide the background a more directed Q&A can follow. In short it sounds like you need to force PostgreSQL to shutdown and then start it back up again. If you are using the apt package you'd do this via some invocation of pg_ctlcluster for which there is ample documentation of both it and the underlying pg_ctl command. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-the-database-system-is-starting-up-tp4941646p5795847.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] Re: There is bug in PCS 0.9.26: configure pacemaker resource agent for PG stream replication
Michael Paquier wrote > On Fri, Mar 7, 2014 at 3:32 PM, leo < > dazhoufei@ > > wrote: >>I just complete my HA configuration on Redhat 6.4 enterprise: >> Clusterware: Pacemaker 1.1.8 ( CMAN .0.12.1, corosync 1.4.1 ) >> Resource manager: PCS 0.9.26 >> PostgreSQL 9.3.3.1 >> Detailed configuration follow: >> http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster#PostgreSQL_.28node1_only.29 > As this issue is related to Pacemaker (or PCS, the Pacemaker/Corosync > configuration system AFAIK) and not directly Postgres, you should ask > this question directly to the Pacemaker mailing list: > http://oss.clusterlabs.org/mailman/listinfo/pacemaker > Regards, I just about wrote the same... This is being posted to -general, not -bugs. It reads like an FYI to people who may be using PCS and PostgreSQL and as such is on-topic for -general. A little more preamble saying such would have helped... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/There-is-bug-in-PCS-0-9-26-configure-pacemaker-resource-agent-for-PG-stream-replication-tp5795081p5795159.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] Mysterious DB reset
Israel Brewster-2 wrote > So my question is, aside from someone going in and mucking about in the > wee hours of the morning, what could possibly cause this behavior? What > sort of event could cause all data to be deleted from the table, and the > sequence to be reset? Especially while there is an active connection? > Thanks for any ideas, however wild or off the wall :-) Nothing so far makes this likely but any chance there is some kind of Virtual Machine setup in place where all the changes from a given day are being lost because the VM is resetting back to "factory defaults"? Also, you say you perform daily pg_dumps. Have you tried loading these up and see what their contents are? Ultimately the log files are going to be needed to do any meaningful forensic work though. Any chance you may been debugging the wrong box/database? That is always something worth verifying and usually not the difficult. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Mysterious-DB-reset-tp5794868p5795031.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] SQL question on chunking aggregates
Merlin Moncure-2 wrote > On Tue, Mar 4, 2014 at 1:49 PM, Owen Hartnett < > owen@ > > wrote: > >> It looks like I should be able to use the window function to do this, >> but >> I've been unsuccessful. The following runs, but doesn't seem to have any >> effect: >> >> select owner_id, array_agg(trim(maplot)), array_agg(revallandvalue + >> revalbuildingvalues) from parcel group by owner_id >> window mywindow as (rows between current row and 5 following); >> >> Does anyone have any suggestions on what I should try? >> >> -Owen > > I didn't test it, but something along the lines of: > > select > owner_id, > array_agg(maplot), > array_agg(totalvalues) > from > ( > select > owner_id, > trim(maplot) as maplot, > revallandvalue + revalbuildingvalues as totalvalues, > row_number() over (partition by owner_id) as n > from parcel > ) q > group by owner_id, (n - 1)/5; Yeah, a window cannot work because it cannot be defined to provide disjoint subsets. In most cases multiple invocations of array_agg(...) - at the same level in a query - will see the same row order but that is not something that it is wise to rely upon. Any time you want to have synchronized array_agg(...) calls you should add identical explicit ORDER BY clauses to them; or better yet combine that data into a custom datatype and then store that in the array. The solution is as Merlin presents; you need to use integer division to bucket the rows and then call the array_agg(...) using those groups. I like to keep the bucket ID around in order to capture the original order but as shown it is not a requirement. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-question-on-chunking-aggregates-tp5794680p5794694.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] Efficiently delete rows not referenced by a foreign key
Evan Martin wrote > Hi All, > > I have a database schema where if row is deleted from one table the rows > it references in another table should also be deleted, unless still > referenced by something else. > > Eg. Table A has foreign key to table B. When I delete a row from A I > also want to delete the referenced row in B, unless it's still > referenced by something else (which may be another row in A or in a > completely different table C). > > The way I currently do this is to have an AFTER DELETE FOR EACH ROW > trigger on A, which attempts to delete the row in B, but catches and > ignores a foreign_key_violation exception. This works (the foreign keys > don't have ON DELETE CASCADE), but it's slow when deleting many rows. > > A single query that deletes all the referenced rows in B, if they're not > referenced by A or C, is much faster. The problem with that approach is > it's error-prone to write and maintain. There may be many other tables > that reference B and if they ever change this query has to be updated, > which is a maintenance nightmare. I could try to auto-generate the SQL > for it by finding foreign key constraints referencing B in > information_schema, but that's not a trivial exercise. It also falls > short in a more complicated scenario where I want to delete rows in > multiple tables (A1, A2, ...) that may all reference B. > > Is there an easier way to do this? Postgres obviously knows about all > the foreign keys, so is there any way to get it to do the checking for > me? I mean a way that works in bulk, not one row at a time. > > Regards, > > Evan Basically - if there are no foreign references to a given primary key that key should be deleted? First question I'd ask is: What harm is there in not deleting the row in that situation? If you can use an FK to ensure that such a row cannot be removed if there is at least one related row then aggressively scanning for an removing rows in a batch operation - much like vacuum works - seems to be a desirable option to consider. I do not believe PostgreSQL has the ability to expose the data you need; or to perform such a task in bulk. What would be required is some kind of internal storage of a "reference counter" - but the current FK check basically runs that same single-row lookup check that you would have to code. I'd suggest you do the dynamic, catalog-driven, query. You can add additional infrastructure if you want to make the routine safer but assuming you can rely on foreign keys to avoid invalid deletions that is mostly a luxury. You will know quite quickly if someone introduces a schema change that brakes your code - ideally in your development and/or staging environment. Thus the decision is whether to enforce the rule by adding lots of triggers OR whether to periodically perform a cleanup of the database based upon those rules. Note you can make use of an "after statement" trigger instead of "after each row" if that proves to be more efficient based upon your usage. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Efficiently-delete-rows-not-referenced-by-a-foreign-key-tp5794440p5794445.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] xpath functionerror
sparikh wrote > Thanks David for your quick response. My original objective was to get the > value of a particular node in xml which is stored as text datatype in the > table. > > For example : > > > > - > > - > > > 16 > > > > > I want to extract the value of amperage as '16'. Like that I may have many > nodes in xml want to get the report to show them in different columns. I > used to do this with oracle using xml functions provided. > > Trying to figure out same in postgres. > > Thanks again for your help. And the same goes for PostgreSQL but you need to use the correct data types; xml functions operate on XML instead of TEXT because that way they don't really need to deal with bad text input - the type conversion function takes care of making sure the text is valid XML. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724p5793742.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] xpath functionerror
sparikh wrote > Hi, > > My current database version is "PostgreSQL 9.1.11 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat > 4.4.7-3), 64-bit". > > I am trying to use function xpath in my query and got following error. > > "ERROR: function xpath(unknown, text) does not exist at character 8 > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > I could see the library pgxml.so libray in /usr/pgsql-9.1/lib . Could > someone please advise me what more needs to be done in order to run xpath > queris? > > Thanks in advance. The valid function signature is: xpath(xpath text, xmlcontent xml, [ nsarray text[][] ]) http://www.postgresql.org/docs/9.1/static/functions-xml.html The "unknown" above will be converted to "text" but the "text" above will never be converted to "xml". You must perform this conversion yourself and pass the converted value - now of type xml - to the function. The documentation explains how to perform this conversion. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/xpath-functionerror-tp5793724p5793727.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] JSON vs Text + Regexp Index Searching
David Johnston wrote > > Eliot Gable-4 wrote >> I advocated creating a separate mapping table which >> maps the ID of these records to the other ID we are searching for and >> performing a JOIN on the two tables with appropriate foreign key >> relationships and indices. However, I was ask to instead put the list >> into >> a single column on each row to reduce implementation complexity. >> >> Assuming the list of IDs is in a column on each row as TEXT in the format >> of a JSON array, what is the best way to index the column so I can >> quickly >> find the rows with the given ID? > I recommend benchmarking two implementations: > > 1) id_xref integer[] --on the same table, use "search_id = ANY(id_xref)" > as the WHERE condition > > [...] #1 can be greatly expanded in usefulness by making use of the "intarray" contrib/extension; as Merlin mentioned up-thread. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/JSON-vs-Text-Regexp-Index-Searching-tp5793472p5793495.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] JSON vs Text + Regexp Index Searching
Eliot Gable-4 wrote > I advocated creating a separate mapping table which > maps the ID of these records to the other ID we are searching for and > performing a JOIN on the two tables with appropriate foreign key > relationships and indices. However, I was ask to instead put the list into > a single column on each row to reduce implementation complexity. > > Assuming the list of IDs is in a column on each row as TEXT in the format > of a JSON array, what is the best way to index the column so I can quickly > find the rows with the given ID? I recommend benchmarking two implementations: 1) id_xref integer[] --on the same table, use "search_id = ANY(id_xref)" as the WHERE condition 2) Your multi-table solution but use "EXISTS (SELECT 1 FROM xref_master WHERE search_id = id_xref)" And I'd politely respond that implementation complexity is somewhat less important than performance in an embedded system - not that either of these solutions is considered complex and both can readily be encapsulated into functions to hide any such complexity from the application. I would not introduce the added indirection of storing the values as a single JSON array. Especially if the IDs are integer-based but even if you represent IDs as text anyway. The fact you want to use LIKE/REGEX confuses me but that may be because you are limiting yourself to text. Most cross-ref searches know the exact ID being looked for so pattern matching is not required... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/JSON-vs-Text-Regexp-Index-Searching-tp5793472p5793492.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] problem with query and group by error
Susan Cassidy-3 wrote > Someone said something about > "Yes, except those that are inside the aggregate." > but I don't have an aggregate specified. So every column then... As soon as you add "group by" the rule becomes - every column is either a group determinate or is aggregated. If you simply want to remove duplicates you can write: Select distinct ... From No group by clause required and every output column is used to create an implicit non-duplicated group. I haven't tried to figure out what you are trying to do with this query so I don't know which, if either, form is more correct but grouping without aggregates is unusual and I also find that using distinct is not that common a need of you have a correctly normalized database. IOW you should not use group by or distinct to "make the query work" but only if you know/understand why doing so is necessary. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-query-and-group-by-error-tp5793127p5793140.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] problem with query and group by error
Susan Cassidy-3 wrote > I have a large query: >SELECT distinct on (srs.scan_run_id) srs.scan_run_id, > srs.run_request_number, srs.container_id, srs.manifest_id, > srs.scan_system_name_id, >srs.scan_site_name_id, srs.scan_site_nickname_id, > to_char(srs.start_time, 'MM/DD/YY HH24:MI:SS'), >to_char(srs.stop_time, 'MM/DD/YY HH24:MI:SS'), srs.system_operator, >srs.system_baseline_configuration_file_version_id, > srs.container_contents, srs.container_run_truth_data, >srs.scan_type_id, sty.scan_type, > srs.hardware_version_or_hardware_identifier_id, >srs.software_version_id, srs.operator_notes, >to_char(srs.expiration_date, 'MM/DD/YY HH24:MI:SS'), > srs.scan_outcome_id, > to_char(srs.alarm_time, 'MM/DD/YY HH24:MI:SS'), > srs.alarm_decision_id, srs.material_detected_id, srs.data_access, > ssn.scan_system_name, ssn.simulation, ssitenames.scan_site_name, > ssitenicknames.scan_site_nickname, > hvhi.hardware_version_or_hardware_identifier_name, > sv.software_version, sc.description > from scan_run_summary srs left outer join scan_system_names ssn on > srs.scan_system_name_id = ssn.scan_system_name_id > left outer join scan_site_names ssitenames on > srs.scan_site_name_id = ssitenames.scan_site_name_id > left outer join scan_site_nicknames ssitenicknames on >srs.scan_site_nickname_id = > ssitenicknames.scan_site_nickname_id > left outer join hardware_version_or_hardware_identifiers hvhi on > srs.hardware_version_or_hardware_identifier_id = > hvhi.hardware_version_or_hardware_identifier_id > left outer join software_versions sv on srs.software_version_id = > sv.software_version_id > left outer join scenes sc on srs.container_run_truth_data = > sc.scene_id > left outer join scan_types sty on srs.scan_type_id = > sty.scan_type_id > join scene_thing_instances sti on srs.container_run_truth_data = > sti.scene_id > join scene_things stg on sti.scene_thing_id = stg.scene_thing_id > group by srs.scan_run_id > ; > > > That gives this error: > > ERROR: column "sty.scan_type" must appear in the GROUP BY clause or be > used in an aggregate function > LINE 5:srs.scan_type_id, sty.scan_type, srs.hardware_version... > > I don't see why sty.scan_type should be singled out as requiring a group > by > clause, when there are many other columns specified. > > If I add scan_type to the group by, then it gives the same error, but with > ssn.scan_system_name. > > Am I going to have to specify all the columns in the group by clause? > > I originally had the query without the group by, but I had duplicate rows, > so I added a group by to eliminate them. > > Thanks, > Susan Newer releases (not sure which) are capable of identifying a primary key in a group by and allow you to omit all dependent columns of said primary key. However, as a general rule, every non-aggregated column must appear in the GROUP BY. There is not "default behavior" for columns not appearing in group by nor that have been aggregated. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-query-and-group-by-error-tp5793127p5793128.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] type aliases
James Harper wrote >> >> You probably should define your domain like this: >> >>CREATE DOMAIN myvarchar varchar(42); >> > > That's what I thought, so it won't do what I want. I need to be able to > set the length at the time of declaration. > > So suppose I wanted to implement myvarchar in C. In my _in function, how > do I know how big my column declaration is? Eg if someone tries to insert > 50 characters into my 42 character field, how do I get the declared length > and then tell postgres that the data to be inserted is too big? > > Thanks > > James IMO. You are abusing the type system to implement things that should be defined using CHECK constraints. If indeed you are working with string I would avoid length-limited type mods and rely on checks/constraints. There isn't any viable alternative for numeric scale/precision type modifiers though :( There is no current way for a function body to examine the type modifiers of its input, domain or otherwise. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/type-aliases-tp5792148p5792207.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] Can not upgrade from 9.1 to 9.2 or 9.3, --HELP
bobspero wrote > I went through postgres website since yesterday and can not upgrade my > postgresql from 9.1 to anything greater. At first I did pgupgrade and was > told to install postgres-xc, when I installed it, it removed 9.1 and I > could not log into it. I was able to recover it and when I the steps from > https://wiki.postgresql.org/wiki/Apt I get pgadmin3 : Depends: > libwxbase2.8-0 (>= 2.8.12.1+dfsg) but 2.8.12.1-14ubuntu1.1 is to be > installed > Depends: libwxgtk2.8-0 (>= 2.8.12.1+dfsg) but > 2.8.12.1-14ubuntu1.1 is to be installed. > > Do I just install libwxgtk2.8-0? Are you using the standard Debian PostgreSQL packages or are you using postgres-xc? If you are not using postgres-xc then the system telling you that you need it is unusual enough to stop then and ask questions - that you did not means you should be prepared to restore from backups. If you are using postgres-xc then they are the ones you need to talk with and using the standard base release packages is not going to help since they are different products. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-not-upgrade-from-9-1-to-9-2-or-9-3-HELP-tp5791837p5791860.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] How to distribute budget value to actual rows in Postgresql
Andrus Moor wrote > Budget table contains jobs with loads: > > create temp table budget ( > job char(20) primary key, > load numeric(4,1) not null check (load>0 ) > ); > insert into budget values ( 'programmer', 3 ); > insert into budget values ( 'analyst', 1.5 ); > > Actual table contains actual loads by employees: > > create temp table actual ( > job char(20), > employee char(20), > load numeric(4,1) not null check (load>0 ), > contractdate date, > primary key (job, employee) > ); > > insert into actual values ( 'programmer', 'John', 1, '2014-01-01' ); > -- half time programmer: > insert into actual values ( 'programmer', 'Bill', 0.5, '2014-01-02' ); > > insert into actual values ( 'analyst', 'Aldo', 1, '2014-01-03' ); > insert into actual values ( 'analyst', 'Margaret', 1, '2014-01-04' ); > > Result table should show difference between budget and actual jobs so that > budget load is > distributed to employees in contract date order. sum(...) OVER (ORDER BY) This provides for a cumulative sum calculation using whatever order you desire. > If budget load is greater than sum of job loads, separate budget line with > empty employee > should appear. This is a separate query that would then be added to the budget/actual query via: UNION ALL > In data above, 1.5 programmers are missing and 0.5 analysts are more. > > Result should be > > JobEmployee Budget Actual Difference > > programmer John 1 1 0 > programmer Bill 0.5 0.5 0 > programmer 1.5 0 1.5 > analystAldo 1 1 0 > analystMargaret 0.5 1 -0.5 > > How to create such table in modern Postgresql ? > Can rank function with full join used or other idea ? I don't get how a rank function is going to useful here... > I tried > > select > coalesce(budget.job, actual.job ) as job, > employee, > budget.load as budget, > coalesce(actual.load,0) as actual, > coalesce(budget.load,0)-coalesce( actual.load,0) as difference > from budget full join actual on (job) > order by contractdate > > but this does not distribute budget load to employee rows. My initial reaction is that you will need at least 3 separate sub-queries to accomplish your goal - though it may be that you have to resort to using pl/pgsql and implement procedural logic. Pure SQL will probably be sufficient though. To make this easier to manage you should use CTE/WITH: WITH sub1 AS () , sub2 AS () , sub3 AS () , sub4 AS ( SELECT * FROM sub2 UNION ALL sub3 ) SELECT * FROM sub4; David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-distribute-budget-value-to-actual-rows-in-Postgresql-tp5791170p5791175.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] Dynamic insert into ARRAY? plpgsql
George Ant wrote > Thank you for your Response! > > Inserting a sub-select into the array seems to be the solution that I > want, but it gives me this error--> subquery must return only one column > > Any help? > > Kind Regards, > George Ant In both cases you want to be storing a single composite type column. So your sub-select has to use row(...)::composite_type in the select. Just like your original query did. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791110.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] PL/pgSQL Copy data from one table to another
George Ant wrote > Hey Guys, > > Thank you for your replies! Your suggestions worked fine!! :) Also my code > looks a lot cleaner now! > > Kind Regards, > George Ant. Have you considered just creating a view, or even retrieval functions, the generate these "object forms" on the fly instead of altering your database schema? Using arrays for master-detail relationships is typically considered wrong and is definitely non-relational. Your application should not dictate schema design to this level generally but instead you should add an O-R layer where needed. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PL-pgSQL-Copy-data-from-one-table-to-another-tp5790663p5791096.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] Dynamic insert into ARRAY? plpgsql
alexandros_e wrote > You must a) join the 2 tables on the orderID ... where orderID=15 and then > GROUP BY the result by the order ID and concat the orderlines by a custom > aggregate function like: > http://www.postgresql.org/message-id/db7789b.0309131210.625da...@posting.google.com Maybe back in 2003 when that message was written... The OP did join the tables but tried to create an array literal instead of using a now standard array creation function. And you do not need to specify a specific, single, order if you formulate the group by correctly. You should avoid blindly repeating information, especially if it is more than a few years old. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791094.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] Dynamic insert into ARRAY? plpgsql
George Ant wrote > Hey Guys, > > I have a table(Orders_object_table) of this type: > > CREATE TYPE orders_type AS >(orderid integer, > amount amount_type, > customerid integer, > orderdate date, > orderlines orderlines_type[]); > > and I am trying to insert data from another tables(Orders and Orderlines). > Each Order has many Orderlines but I dont know the number. > > I use this : > > CREATE OR REPLACE FUNCTION Copy_Orders_Data() RETURNS integer as $BODY$ > BEGIN > > INSERT INTO "Orders_object_table" (...,orderlines,...) > SELECT ...,ARRAY[row(ol."OrderlineId", ol."Quantity", > ol."Prod_id")::orderlines_type], ... > FROM "Orders" o > INNER JOIN "Orderlines" ol > ON o."OrderId" = ol."OrderId" > WHERE o."OrderId" >=1 AND o."OrderId" <=12000; > END; > > but it gives me an error. (IT tries to create many rows for each Order > which returns duplicate PK OrderId) > > How can I find how many orderlines have each row, and then insert them in > one row? Each row of the Orders_object_table must have a unique PK OrderId > , and a column with all the Orderlines this Order has. > > Thank you in advance :) > > Kind Regards, > George Ant Use either: Array( sub-select ) or Array_agg( col ) w/ a GROUP BY query David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791092.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] roles inheriting configuration values
Adrian Klaver-3 wrote > On 02/07/2014 11:08 AM, Joe Van Dyk wrote: >> I'd like to have join_collapse_limit=20 for all users that belong to a >> certain group. Is there a way to do that without having to alter all the >> roles that are in that group? > > From what I see in the docs no: > > http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html > > "Whenever the role subsequently starts a new session, the specified > value becomes the session default, overriding whatever setting is > present in postgresql.conf or has been received from the postgres > command line. This only happens at login time; executing SET ROLE or SET > SESSION AUTHORIZATION does not cause new configuration values to be set. " > > Looks like the settings only apply to the role that logs in. This does not, by itself, preclude role-inheritance of variable values. It would simply mean that the inheritance resolution routine would only be resolved at logon. Unlike GRANT/REVOKE variable value inheritance has potential for multiple-inheritance resolution ambiguities - which likely increases cost/benefit equation for anyone looking to implement such a feature. There is like a scripting solution to this problem to at least minimize the burden but I do not recall seeing anything already in place that meets this need. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/roles-inheriting-configuration-values-tp5791011p5791036.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] password-less access, without using pg_hba
Reece Hart wrote > On Thu, Feb 6, 2014 at 6:37 PM, David Johnston < > polobo@ > > wrote: > >> Doubtful. >> > > Yeah, that's what I had assumed too. > > The question is motivated entirely by what I think would make it easier > for > users. In principle it's not difficult to give people a password (as I do > now), but in practice it's a barrier that I'd like to eliminate. > > -Reece If your users are connecting directly to a PostgreSQL database then the presence or absence of a password has no significant impact on usability. They have learned SQL and can interact with databases and likely expect to need a password anyway. Usually developers make things easier by writing software that the users interact with instead of the database... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790966.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] password-less access, without using pg_hba
Reece Hart wrote > I'd like to provide public access, without a password, to a database > hosted > on Amazon RDS. > > I'm familiar with using pg_hba.conf to enable trust (no) authentication > for > a user. pg_hba.conf is not available to DBAs on RDS. > > Is there any other way to achieve password-less login in postgresql? I > tried alter user password NULL. > > Thanks, > Reece Doubtful. You need to give people the correct server ip and user anyway so why not just give them a password at the same time? If you are trying to do some automated scripting there are other, better, solutions than disabling the password requirement. Especially on a public-visible server. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790948.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] subtracting from a date
jvsrvcs wrote > I tried that but get an error > v_start_date date; > v_minus_one_year date; > > I have v_start_date to start with and want to subtract one year and put > into v_minus_one_year > > select v_start_date - interval '1 yr' as v_minus_one_year; > > > > --- I get: > > ERROR: query has no destination for result data The relevant section of the documentation you need to study is: http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html There are two basic ways to assign to variables: variable := expression SELECT expression[s] INTO variable[s] FROM ... || SELECT expression[s] FROM ... INTO variable[s] Your problem is that: SELECT expression AS "variable" FROM ... simply provides an alias for the expression and has nothing to do with any variables in the surrounding program. The "INTO" keyword is needed to avoid ambiguity. pl/pgsql has the unique behavior that using SELECT without INTO results in an error. If you really need to execute a SELECT and ignore the content selected you have to use PERFORM. The error you saw was this behavior in action. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/subtracting-from-a-date-tp5790891p5790923.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] JDBC performance issue
CS DBA wrote > Hi All; > > We have a client running Pentaho to migrate data. > > They recently upgraded Pentaho which upgraded their JDBC driver from 8.4 > (postgresql-8.4-703.jdbc4.jar) to 9.1 (postgresql-8.4-703.jdbc4.jar). > They have a test set which updates 1000 rows, with the old driver it > takes about 30 seconds, but the new driver takes 3x longer. > > This is on PostgreSQL 9.1 > > Thoughts? Good News! This seems to be a re-producible problem. Bad News! Either you/your client will need to discover the bottle-neck or you will need to provide the test set/routine to the public so someone else can try. Also, maybe run the test set against and with 9.2 and/or 9.3 to see if the problem still exists there... Note, I presume you mean "updates 1000s of rows" (i.e., considerably more than 1,000) - 30s to update exactly 1000 rows doesn't seem that impressive but then again no clue what is involved so maybe that is good. Note that means it is unclear if the "UPDATE" phase or some required "SELECT" is mis-behaving which is a critical detail. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/JDBC-performance-issue-tp5790710p5790714.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] Re: Drop all overloads of a function without knowing parameter types
Evan Martin wrote > In a nutshell: I think the difficulty of dropping functions is > inconsistent with the difficulty of dropping other objects and I'd like > to see this inconsistency fixed. > > So I don't agree with the suggestion of matching function names using a > regex, since that's not supported for other types of objects. To explain > the use case a little better: > > I maintain a set of scripts that can create a database from scratch. > Sometimes I also need to update an existing database to the latest > version. For tables this obviously requires separate scripts to preserve > data, but views, rules and triggers can be updated just by using CREATE > OR REPLACE in the DB creation scripts. Functions can /almost/ be updated > this way, but not quite. > Function arguments may change over time. No, they cannot. If the arguments change you are dealing with an entirely new object. And often you end up keeping the old function around for backward-compatibility. The two objects have their own life-cycle that they should be in full control of and not subjected to external objects dropping them because they share the same name. For an analogy how would your scripts deal with. ALTER TABLE table1 RENAME table2; This is exactly what you are effectively doing when you go and change a function signature though there is no "RENAME" action available for functions. > The script that creates them doesn't know and doesn't care /which/ old > version of the function already exists, if any - it just wants to > replace it. > > I'm sure this is not an uncommon scenario. Current options for the user > are: > > 1) Maintain a list of DROP IF EXISTS statements for all function > signatures that ever existed. > 2) Roll their own code to find any existing functions, which is not > simple as the SO thread >> > I mentioned shows. 2 users with over 20K reputation answered and nobody > knew the "oid:regprocedure" trick. > 3) Since yesterday: find Tom Lane's post in this list. > > I'd just like to see an easy to use, reliable and easy to discover way > to do this. The general "execute trick" is good to know, but a user > shouldn't resort to it for something that (from the user's point of > view) is as simple as DROP VIEW or DROP INDEX. > > If nothing else, the manual page for DROP FUNCTION seems like a good > place to document this, since that's the obvious place where anyone > would look to find out how to drop a function. > > Regards, > > Evan > > On 04/02/2014 17:48, Tom Lane wrote: >>> I was writing about some kind of a compromise. >> My point was precisely that a compromise would satisfy nobody. There >> would be a few cases for which it was Exactly The Right Thing, and many >> more for which you'd still need to learn how to do the EXECUTE trick. >> >> I wonder whether we shouldn't address this by adding a few examples >> of that type of trick to the docs. Not sure where, though ... >> >> regards, tom lane >> >> function(text, text) is not the same as function(text, integer). If you no longer are supporting function(text, text) in your current database version you should have a "DROP FUNCTION function(text, text)" command somewhere in your "upgrade routine". This is partly a learning exercise in how things are. DROP FUNCTION function; better continue to fail as not having specified a correct function name since the signature IS part of the name and defines a unique and self-contained database object. However, I'll concede that since functions are the only class of object that allow for "name overloading" providing a built-in ability to "DROP ALL FUNCTION WITH BASE NAME function" - excluding those in pg_catalog - would have value. No regular expressions just a simple name-without-args literal match. If you are doing version controlled upgrades you should not be using this function but during the R&D phase I can imagine it would come in quite handy. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Drop-all-overloads-of-a-function-without-knowing-parameter-types-tp5790367p5790522.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] Re: Drop all overloads of a function without knowing parameter types
Tom Lane-2 wrote > I wonder whether we shouldn't address this by adding a few examples > of that type of trick to the docs. Not sure where, though ... Probably the Wiki would be a better place to put this kind of material. A link to there from "21. Managing Database" would seem to be most appropriate. Adding another section to chapter III is a possibility as well. Something like: "XX. Database Scripting". It could point to the Wiki as well as cross-reference both the "System Catalogs" section and the "PL/pgSQL[...]" section with a brief overview of the kinds of activities that are not provided for by standard internal functions or SQL commands. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Drop-all-overloads-of-a-function-without-knowing-parameter-types-tp5790367p5790515.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] Re: way to custom sort column by fixed strings, then by field's content
Susan Cassidy-3 wrote > I have a column that contains items like > 'absolute root' > 'root 3' > 'root 4' > 'root 5' > 'scene 1' > 'scene 2' > 'scene 3' > > and I would like them to sort in that order. > > I tried: > select sti.description, sc.description from scene_thing_instances sti join > scenes sc on sti.scene_id = sc.scene_id > order by CASE sc.description > when (sc.description = 'absolute root'::text) then 1 > when (sc.description ilike 'root%') then 2 > else 3 >END; > > I was starting with this, and was going to add perhaps another case > statement. > > But it gives me: > ERROR: operator does not exist: text = boolean > LINE 3: when (sc.description = 'absolute root'::text) th... > ^ > HINT: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > > I don't understand this because description is a text column, not boolean, > and certainly 'absolute root'::text is a text string. > > This is 9.2. > > Ideas, anyone? Read the documentation for "CASE": http://www.postgresql.org/docs/9.3/interactive/functions-conditional.html#FUNCTIONS-CASE There are two forms: SF) CASE expression WHEN value THEN result LF) CASE WHEN condition THEN result The first form is a short-hand version for the second form using the common equality condition. Converting from SF to LF results in a condition of the form "expression = value" and thus there must be an equality operator between the type of "expression" and the type of "value". In your example "expression" is a string - description - while "value" is a boolean (string .op. string). This is because you incorrectly repeated the writing of the "expression" in each "WHEN" clause. For your problem you want to explicitly use the long-form so you have much more flexibility in your "conditions" than simple value-equality (i.e., your ilike is not possible in short-form) ORDER BY CASE WHEN sc.desc... = 'absol...' THEN 1 WHEN sc.desc... ilike 'root%' THEN 2 ELSE 3 END This causes the primary sort just like what you want. However, you have not specified how multiple "root" items should sort nor how everything else besides "root" and "absolute root" should sort. To do so you add a second sort expression - in this case just the description column. ORDER BY CASE ... END, sc.description Now all roots will be listed in ascending string order after "absolute root" and everything else will come after "root%" also in ascending string order. Adrian was close but simply reversed the order of the two expressions in the ORDER BY. More importantly, though, he did provide the correct "CASE" syntax. The typo of the column ordering was easily overlooked, and corrected for by the reader IMO, given the data sample in which the whole case part ended up irrelevant. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/way-to-custom-sort-column-by-fixed-strings-then-by-field-s-content-tp5790371p5790398.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] need of a lateral join with record set returning function?
Raphael Bauduin wrote
> Hi,
>
> I'm trying to understand what happens here:
>
> I have atype product defined:
>
> [...]
>
> which I'm trying to use in this query calling json_populate_recordset
>
> =# select q.* from (select json_populate_recordset(null::product,
> event->'products') from events where timestamp>'2014-02-02' and
> type='gallery' limit 1) q;
>json_populate_recordset
> -
> (68,121,F,3,493,17,88753,)
SELECT (q.json_populate_recordset).* FROM ( LIMIT 1) q; will possibly
give you what you desire. the presence of limit will avoid having the
function evaluated multiple-times.
> This query illustrates what I want to achieve:
>
> =# select f.* from
> json_populate_recordset(null::product,'[{"id":80723,"type":41,"brand":41,"price":65.0,"status":3,"price_advantage":1.0,"gender":"M",
> "algorithm":"v1"}]'::json) f;
> price_advantage | type | gender | status | brand | price | id |
> algorithm
> -+--+++---+---+---+---
>1 | 41 | M | 3 |41 |65 | 80723 | v1
>
> I see the difference in the query ( the second working directly on the
> return value of the function), but in the first example, isn"t the inner
> returning a set, from which the outer query can do a select *?
> There is a difference with the second query which I've not identified.
> Anyone caring to enlighten me?
When the function is in the FROM clause it is treated like a table and so
each output value gets its own column on the "table" that is created.
When the function is in the "SELECT-list" it is treated like a composite
type and thus only occupies a single output column. You can manually
de-reference the composite type into a "table" structure using "*" -ON THE
COLUMN- as a separate encapsulating action.
> PS: to get it working, I have to write the query as this:
>
> =# select q.* from (select * from events where timestamp>'2014-02-02' and
> type='gallery') q1 CROSS JOIN LATERAL
> json_populate_recordset(null::product, event->'products') q limit 1;
> price_advantage | type | gender | status | brand | price | id |
> algorithm
> -+--+++---+---+---+---
> 68 | 121 | F | 3 | 493 |17 | 88753 |
>
> What I'm interested is an explanation of why this is needed.
Correct, because now the function is in the FROM-clause and not the
SELECT-list. This is pretty much the reason LATERAL exists - to keep the
function in the FROM-clause while still allowing it to reference columns
like it can when it is part of the SELECT-list.
Without LATERAL you have to put the function in the SELECT-list and make
sure it executes only a single time [i.e., (function_call(...)).* does NOT
work performantly for sundry technical reasons] after which you can, in an
outer-query-layer, expand the composite type into component parts. The main
way to enforce this behavior is to use CTE/WITH:
WITH func_cte AS (
SELECT func_call(...) AS f_result FROM
)
SELECT (func_cte.f_result).* FROM func_cte;
Note the syntax for expanding the column includes the () surrounding the
"table.column" style identifier.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/need-of-a-lateral-join-with-record-set-returning-function-tp5790353p5790366.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] Re: PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC
Tom Lane-2 wrote > Craig Ringer < > craig@ > > writes: >> I just want us to allow, by default, implicit casts FROM text (not TO >> text) using the input function for all PostgreSQL's validated >> non-standard types (and XML due to limited deployment of SQL/XML support >> in client drivers). > > Sorry, that is *just* as dangerous as implicit casts to text were. > It would bite a different set of queries, but not any less painfully. > > I have about zero sympathy for ORMs that aren't willing to address > this issue properly. Yeah, it might be less than trivial, but that > doesn't mean that the right answer is to create semantic hazards > on the server side. Less dangerous since an error will eventually be thrown on non-conforming input but we've still moved what is now a parse-time error into a runtime error. The "correct" solution would seem to be for ORMs to "bind unknown" against the input but only when the supplied string is meant to be a representation of a PostgreSQL type as opposed to being actual string data. The ORM is free to provide the necessary API to distinguish between the two and the caller has to know then database to call the proper method (i.e., not setString if your data intended for anything besides a text/varchar column). I'm not sure how function overloading can be solved no matter which implicit cast procedure methodology you choose; though I haven't ponder it much. The goal should be for the ORM to basically tell PostgreSQL "here's some data, you deal with it. That is exactly what the "unknown" moniker is for. But if they tell us it's a text typed value we believe them. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-specific-datatypes-very-confusing-for-beginners-who-use-wrappers-around-JDBC-tp5789352p5789429.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] SQL State XX000 : XML namespace issue
Panneerselvam Posangu wrote
> to be specific, this is the SQL.
> SELECT to_number((SELECT
> array_to_string((SELECT xpath('/attributes/duration/text()',(SELECT
> XMLPARSE
> (CONTENT
> '
>
>
> 2
>
>
> 2
>
>
> true
>
>
> true
>
>
> 0
>
>
> ')),ARRAY[ARRAY['',
> '']])
>
>
> ),'','')),'9') > 30
> Thank you
> From:
> panneerpps@
> To:
> polobo@
> Subject: RE: [GENERAL] SQL State XX000 : XML namespace issue
> Date: Fri, 10 Jan 2014 16:24:21 +0530
>
>
>
>
> Hi,
> This is the query that we use.
> SELECT to_number((SELECT
> array_to_string((SELECT xpath('/attributes/duration/text()',(SELECT
> XMLPARSE
> (CONTENT p.ATTRIBUTES)),ARRAY[ARRAY['', '']])
>
>
> ),'','')),'9') > 30 from jcp_promotions_b p
> ATTRIBUTES is of type XML
> Thank you
> Panneer
>
>> Date: Thu, 9 Jan 2014 21:44:11 -0800
>> From:
> polobo@
>> To:
> pgsql-general@
>> Subject: Re: [GENERAL] SQL State XX000 : XML namespace issue
>>
>> Panneerselvam Posangu wrote
>> > Hi,
>> > When we run a SQL statement in Postgres 9.2 we get an error.
>> > Error : Could not register XML namespace with name "" and URI "" SQL
>> State
>> > XX000
>> > In the SQL state we use xpath function. Any reason why this is
>> happening..
>> > Thanks,Panneer
>>
>> No. Try providing more detail. Especially the actual statement you are
>> running. And, better, try to supply a minimal and self-contained query
>> that
>> exhibits the same behavior.
>>
>> David J.
>>
>>
Two things of note.
1) you are parsing CONTENT when the documentation says that xpath processing
only works on DOCUMENT.
2) the reason for your error. The XML you provided does not specify a
default namespace. Also per documentation the default namespace in the XML
document must be assigned to a non-empty alias in the namespace array and
that alias needs to be used in the xpath expression.
section 9.14.3 in the 9.3 docs
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SQL-State-XX000-XML-namespace-issue-tp5786103p5786272.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] SQL State XX000 : XML namespace issue
Panneerselvam Posangu wrote > Hi, > When we run a SQL statement in Postgres 9.2 we get an error. > Error : Could not register XML namespace with name "" and URI "" SQL State > XX000 > In the SQL state we use xpath function. Any reason why this is happening.. > Thanks,Panneer No. Try providing more detail. Especially the actual statement you are running. And, better, try to supply a minimal and self-contained query that exhibits the same behavior. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SQL-State-XX000-XML-namespace-issue-tp5786103p5786222.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] Add custom properties to a column's definition (pg_attribute)
Brooke Beacham wrote > (without having to replicate/maintain a table of columns separately from > the system catalog) Just create the friggin' table and wrap whatever logic you want in a view (or functions) so that you at least get usable results/defaults for any columns you haven't added. Any solution you pick has the risk of becoming out-of-sync so tossing out the best solution does more harm than good. In terms of "group" columns you might just consider creating two tables that have a one-to-one relationship to each other (or three, the base table and then group A and group B tables). A lot depends on the why and structure of your model but there is no standard facility for custom column meta-data and though the comment facility can be used for this I much prefer to leave that for human consumption and have something more structured and easily customize-able for meta-data. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Add-custom-properties-to-a-column-s-definition-pg-attribute-tp5786158p5786174.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] argument of CASE/WHEN must not return a set
Sameer Kumar wrote
> On Thu, Jan 9, 2014 at 1:26 AM, George Weaver <
> gweaver@
> > wrote:
>
>> ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
>> development(# , ',')
>
>
> I guess this part of your statement will return 1,2, which is a set
>
> Can you try below:
> SELECT CASE
> WHEN LENGTH(ARRAY_TO_STRING(
> REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
> , ',')
> )
> = LENGTH('12-70510')
>
> THEN cast(ARRAY_TO_STRING(
> REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
> , ',') as varchar(100))
>
> ELSE ''
> END AS "12-70510";
>
> But anyways, I think the best way to do it is the way you have already
> figured (check the plan for both statements once you have sorted out the
> error)
>
>
> Best Regards,
> *Sameer Kumar | Database Consultant*
>
> *ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
> 069533
> M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
> www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz
>
> [image: email patch]
>
> This email may contain confidential, privileged or copyright material and
> is solely for the use of the intended recipient(s).
>
>
> image002.jpg (7K)
> ;
The condition (WHEN) in a case cannot be a set. You have to make the
expression always resolve to a single row/value.
I'd suggest creating a regexp_matches_single(...) function that calls
regexp_matches(...) in a sub-select so that no matches results in null. You
then need to decide how you want to handle multiple matches. This function
will return a single text[] and so can be used in places where you want your
match to only and always return a single result (i.e. non-global behavior).
Note a recent patch was applied yesterday to resolve an ancient undiscovered
bug related to this kind of query as well. Using the above function/behavior
will let you avoid that bug as well.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/argument-of-CASE-WHEN-must-not-return-a-set-tp5785874p5786085.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] Last inserted row id with complex PK
Nelson Green wrote > My apologies, I was not completely clear. I will not know any of the > columns in advance. The most recent insert is the result of user input > from > a web form, so I won't know what project or what user generated the last > insert. That was why I wandered if that information was stored anywhere in > the system. I'd probably add either (or both) a table-level auto-sequence field and a "recordcreationdate" default timestamptz field. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Last-inserted-row-id-with-complex-PK-tp5785863p5785901.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] Planning error in dynamic string creation in plpgsql
Adrian Klaver-3 wrote
>>
>>
>> In the real function I'm writing, the columns to be used in the string
>> being created are pulled from a configuration table, so their types
>> could be anything. So casting the quote_literal() calls is not really an
>> option here.
>>
>> Any help would be appreciated.
>
> Cast before the quote_literal?
>
> Example:
>
> EXECUTE 'SELECT min('||v_col||'::text) as min, max('||v_col||'::text) as
> max FROM test_temp' INTO v_record;
Right idea if the loss of original type in the min/max query is acceptable
(which it should be). But the cast wants to be of the min/max RESULT not
the min/max INPUT.
SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text AS
max_text FROM ...
Min/Max logic wants to be done by the rules for the original type, not
according to string collation rules.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Planning-error-in-dynamic-string-creation-in-plpgsql-tp5785421p5785523.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] returning json data row from json query
john.tiger wrote > select * from users where jsondata->>'username' = 'jdoe' works but > returns field names, etc besides the data row.how can we get json > data back ? > > select row_to_json from users where jsondata->>'username'='jdoe' didn't > work. > > any ideas ? thks. Using what client? There is no way, using SQL, to get a non-table output; but your client might give you ways to ignore or suppress the column name. And row_to_json is a function, you have to pass argument(s) to it to use it. There is documentation explaining how to do so and what the end result will look like. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/returning-json-data-row-from-json-query-tp5785293p5785303.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] is psql treatment of line-endings configurable?
Using psql 9.3 against a 9.0 server. I send queries to the server via a windows based client which uses and sends "\r\n" for the newline. When running this query: SELECT procpid, current_query, client_addr, xact_start, query_start FROM pg_stat_activity The contents of current_query display as: |\r+| || --the client always sends a trailing "\r\n" This is likely to yield a "use psql" type response and while I'd likely forgive superfluous newlines generally the presence of the "\r" is less tolerable (though only mildly annoying in this specific use-case) since psql should at least attempt to be cross-compatible friendly and swallowing up the \r along with the \n doesn't seem like it would be that challenging. Is this just an oversight for a situation few -hackers encounter or is there a conscious decision to leave the "\r" alone in this situation? Aside, does/can psql (or postgres itself in storing current_query) attempt to remove trailing newlines in situations like this? Thanks! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/is-psql-treatment-of-line-endings-configurable-tp5785281.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] problems with debian testing install or documentation
john.tiger wrote
> 4) su - postgres
> > password --> submitted old one, worked
> $ psql
>
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket
> "/var/run/postgresql/.s.PGSQL.5432"?
>
> tried to start server: sudo /etc/init.d/postgresql
>
> sudo] password for postgres: xx
> postgres is not in the sudoers file. This incident will be reported.
>
> 5) checked wiki.debian.org/postgresql - no help
>
> any ideas ?
Don't run sudo when you have su'd to postgres. The Postgres user created
during install is not given sudo privileges. You have to use your
main/admin user.
See this
http://wiki.postgresql.org/wiki/Apt
For the most recent instructions for Debian/Ubuntu via PPA
Quick notes:
These packages allow the various PostgreSQL versions to play well together.
They provide cluster management tools. Two of the most useful are:
pg_lsclusters
pg_ctlcluster
Files go into {base}/{version}/{name} directories which is why they play
well together. A cluster is the combination of version-name
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/problems-with-debian-testing-install-or-documentation-tp5785180p5785186.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] bulk loading table via join of 2 large staging tables
Quick thoughts: On both tables: Convert your date-time varchar fields into a single epoch/integer field. Create an index of that epoch/integer field. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/bulk-loading-table-via-join-of-2-large-staging-tables-tp5784869p5784870.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] Seems like bug in 9.1.3, need to confirm.
David Johnston wrote > Or feel free to peruse the release notes for 9.2, this behavior change > should be documented if intentional. Reading said notes it appears that the "returns NULL" behavior compensates for a concurrent DROP of an existing/known OID. Since your issue is that the object was never physically present, and thus did not have an OID, so the attempt to obtain an OID failed and resulted in an error. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Seems-like-bug-in-9-1-3-need-to-confirm-tp5784296p5784304.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] Seems like bug in 9.1.3, need to confirm.
rummandba wrote > HI, > > I am working on Postgresql 9.1.3. > I executed the following query and got an error: > select relname, pg_size_pretty(pg_table_size(relname::text)) as s from > pg_stat_user_tables order by pg_table_size(relname::text) desc limit 10; > ERROR: relation "tab_20130206" does not exist > > That table does not exist in the db, that's correct. But is it right I am > getting the error or it is a bug? > > Thanks. The documentation for 9.1 (of which you are using an ancient point release) does not define the behavior that occurs in this situation. Beginning in 9.2 the documentation explicitly states: "If an OID that does not represent an existing object is passed as argument to one of the above functions, NULL is returned." My educated guess is that the behavior was changed in 9.2 to make these functions more user-friendly. That kind of change is never back-ported to previous releases so the 9.1 behavior is as-designed and will not be corrected to conform with the newer behavior. In the off-chance the behavior in 9.1.3 was a regression updating to the newest point release (9.1.11) will fix the issue. You should do this anyway - though there have been a spate of point-release bugs cropping up recently so... Or feel free to peruse the release notes for 9.2, this behavior change should be documented if intentional. http://www.postgresql.org/docs/9.2/interactive/functions-admin.html [9.26.6] David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Seems-like-bug-in-9-1-3-need-to-confirm-tp5784296p5784302.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] window function and order by
Torsten Förtsch wrote > Hi, > > assuming I have a query that computes a running sum like this: > > select id, buy_price, sum(buy_price) over (order by id) sum > from fmb >where 202300<=id and id<=202400 >order by id; > > Do I need the ORDER BY clause at the end? Or does the ORDER BY in the > window function already define the order of the result set? It is possible for the final output to be out-of-order e.g. (2, 3, 1) but the running total associated with each row will be correct. IOW, if you were to manually perform an order-by on the result you would get the same result as if you have included the outer (non-window) order-by in the original query. Input: (1, 1), (2, 2), (3, 3) Possible Output: (2, 2, 3), (3, 3, 6), (1, 1, 1) Expected Output: (1,1,1), (2,2,3), (3,3,6) Not Possible: (2, 2, 2), (3,3,5),(1,1,6) If you want the output in a specific order you should specify that order explicitly. By coincidence, with simple queries, you may consistently get the expected results but that is not something to rely upon. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-and-order-by-tp5784285p5784292.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
