Re: [GENERAL] Normal errors codes in serializable transactions

Hi, 40P01 is mentioned in the manual. See A. PostgreSQL Error Codes of Appendixes. I meant mentioned in the manual in the section about concurrency control. Since I alluded to class 40 errors, I think it was safe to assume that I was familiar with Appendix A... Best, Jon -- Sent via

Re: [GENERAL] Normal errors codes in serializable transactions

Hi, In PostgreSQL, 40001 is used for serialization failures due to MVCC issues, and 40P01 is used for serialization failures due to deadlocks.  I think that many years back when PostgreSQL moved to MVCC it was judged important to differentiate between them with different SQLSTATE values

[GENERAL] Normal errors codes in serializable transactions

Hi, The manual mentions that SERIALIZABLE transactions may abort with error 40001, in which case the client application is supposed to retry the transaction.   I've been stress testing an application by issuing lots of concurrent requests, and sure enough, every now and then I get back those

[GENERAL] Best approach for query with optional constraints

Hi, I have a problem with multiple solutions, and my question concerns which solution is preferred given whatever optimizations happen inside the query planner. In a bug tracking system, I have the tables users, bugs and tags. One bug may have one user (the reporter) but multiple tags.  

[GENERAL] Fetching multiple rows in single round trip

Dear postgresql-general, What would be the best way to fetch in a single round trip a set of table rows? To clarify, suppose I have a 'widgets' table with columns 'wid' and 'data',  and I wish to retrieve all rows that belong to the client side array $targets.  Obviously one solution would be to

Re: [GENERAL] Fetching multiple rows in single round trip

Hi, ...WHERE wid = ANY(string_to_array(?,';')) where the ? is a parameter that you replace with a semi-colon delimited listing of widget IDs Performance depends on specifics you have not provided, especially the expected number of widgets you are going to be filtering one. Thanks for

[GENERAL] Let-bindings in SQL statements

Hi, Is it possible to do the equivalent of let-bindings in a pure SQL function? I have a SELECT that invokes now multiple times.  It would be nicer to do it only once and reuse the value.  Something like this: LET right_now = SELECT now () IN SELECT * FROM my_table WHERE right_now = start AND

[GENERAL] Alarm function in PL/pgSQL

Hi, Is it possible to set an alarm within a PL/pgSQL function? By alarm I mean a function which is invoked some defined time in the future, even after the original function has terminated and returned a value to the client. I want an invocation of function FOO to set a state column of a given

Re: [GENERAL] Alarm function in PL/pgSQL

Hi, I don't think this is really possible with postgres PLs generally. Typically what you have to do is have a function that is called on an interval that checks for alarms and runs them. Thanks for the prompt reply. Just to clarify: you are saying that the function that is called with a

[GENERAL] Converting an hstore into a key/value record

Hallo, Suppose I have the following 'scores_t' record: create type scores_t as (item text, score int); What's the cleanest way of converting a hstore into a set of scores_t, with each key mapping to field 'item', and the value to field 'score'? As an example, consider the following

[GENERAL] Short-circuit boolean evaluation

Hi, Does Postgresql perform short-circuit boolean evaluation both in SQL and PL/pgSQL functions? As an example, suppose I have a function called do_stuff which is computationally intensive. In the example below, will it be called for rows for which the first predicate (foobar.id = $1) is false?

[GENERAL] Functions as first-class values

Hi, Is there support in PL/pgSQL for treating functions as first-class values? Consider the pseudo-code simple example below, which illustrates how this feature can be useful. I reckon that this behaviour can be emulated using EXECUTE, but I suspect there are serious performance penalties. Your

[GENERAL] Passing a table as parameter

Hi, Is there any way for a SQL or PL/pgSQL function to receive a table as parameter? As an illustration, consider the dummy example below. Note that functions get_from_data1 and get_from_data2 follow essentially the same pattern; it would be nice to define instead a single polymorphic

Re: [GENERAL] Record with a field consisting of table rows

Hi, A tuple wouldn't be unusual, but you're not asking for a simple tuple here. What you're asking for is a value and a set of tuples. Which is in itself a tuple! This discussion is again veering slightly off-topic, but note that tuples as mathematical objects do not have such arbitrary

Re: [GENERAL] Record with a field consisting of table rows

Hi, Nope, see my reply from yesterday around 20:23 You can return a table instead, with the count added as an extra column. I did see your solution, but note that it does not return a tuple consisting of an integer and a setof (as I wanted), but instead returns a setof of a tuple. I still

Re: [GENERAL] Record with a field consisting of table rows

Hi, No, of course not. A function cannot return different amounts of different return-values in any language I know of. Come on, you make it sound like it's something inconceivable or exotic, even though pretty much any strongly-typed language with a post-1970s type-system will allow a

Re: [GENERAL] Record with a field consisting of table rows

Hi, This thread has veered off-topic, but my original problem still remains. Basically, I want to declare a function that returns a tuple consisting of an integer and a set of table rows. Something like the following: CREATE FUNCTION foobar () RETURNS (int4, SETOF users) Now, if I understand

[GENERAL] Record with a field consisting of table rows

Hi, I am trying to create a PL/pgSQL function whose return type is a tuple consisting of an integer and a list of table rows. I emulate the tuple by defining a record 'page_t' with the two fields; however, the naïve approach of doing a SELECT INTO one the record's fields does not work (see

Re: [GENERAL] Record with a field consisting of table rows

Hi, That is certainly not going to work: that select does not produce an array, it produces a column of user_t (of which SELECT INTO is only gonna take the first, anyway). Untested, but I think you'd have better results with     _page.users := array(select users from users limit 10);