[SQL] Multiple simultaneous queries on single connection
Hello list, We want to access a postgres database with multiple queries / result sets that are read simultaneously (hence async). The documentation says explicitly that no new PQsendQuery can be send on the same channel before the pqgetresults has returned null. This means we need to use multiple connections. The problem is that multiple connections (from the same application instance) do not share the same MVCC snapshot. Is there a way to have two or more connections see each others uncommitted things? Any thoughts? Yeb Havinga -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple simultaneous queries on single connection
Hello Craig, list Is there a way to have two or more connections see each others uncommitted things? Not at present. You should be able to use explicitly declared cursors and FETCH to interleave requests for results from one or more queries in the same transation using the one connection, but only one FETCH may be active at a time. Thanks for your info. I verified with a pl/pgsql function that from a pl/pgsql perspective, this is a good solution. However, our client application needs to access the postgres backend with libpq. I'm currently looking at PQsendPrepare / PQSendQueryPrepared together with what the exec_execute_message does. Each of these has a portal name as parameter. It would be nice if PQgetResult could also provide a portal name to fetch a result from. The question is how to multiplex data from different portals on the tcp/ip connection, or maybe have more than one tcp/ip connection per PQconn object when more than one portal is active at the same time. -- Yeb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] selecting rows tagged with "a" but not "b"
Andreas Kretschmer wrote: 8q5tmky...@sneakemail.com <8q5tmky...@sneakemail.com> wrote: Hi, I have a two tables: article articleID, name, content tags articleID, tag I want to find all articles that are tagged with "a" but not "b" how do I do this? select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; select a.* from article left join tags t on a.articleID=t.articleID where t.tag = 'a' where not exists (select * from tags t2 where t2.articleID=a.articleID and t2.tag = 'b'); Yeb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Howto have a unique restraint on UPPER (textfield)
Andreas wrote:
is there a way to define a unique restraint on UPPER (textfield)?
psql throws a syntax error because of the upper() function.
The third section of the create index command at
http://www.postgresql.org/docs/8.4/interactive/sql-createindex.html
describes a function based index with as example the function
upper! :-)
postgres=# create table aap (a text);
CREATE TABLE
postgres=# create unique index ai on aap (upper(a));
CREATE INDEX
postgres=# insert into aap values ('aap');
INSERT 0 1
postgres=# insert into aap values ('aaP');
ERROR: duplicate key value violates unique constraint "ai"
regards,
Yeb Havinga
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Achilleas Mantzios wrote: You could also consider the genealogical approach, e.g. The parents of any node to the root, i.e. the path of any node to the root are depicted as parents[0] : immediate parent parents[1] : immediate parent of the above parent What I have more than one parent? regards, Yeb Havinga -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Achilleas Mantzios wrote: Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: Achilleas Mantzios wrote: You could also consider the genealogical approach, e.g. The parents of any node to the root, i.e. the path of any node to the root are depicted as parents[0] : immediate parent parents[1] : immediate parent of the above parent What I have more than one parent? Then it is no longer neither a tree, nor a hierarchical structure, but rather a graph. This a totally different problem. My question was actually an attempt to point at the inability of what you call the 'genealogical approach' database design to store information of more than one parent. regards, Yeb Havinga -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Achilleas Mantzios wrote: Στις Wednesday 07 April 2010 23:33:07 ο/η Yeb Havinga έγραψε: Achilleas Mantzios wrote: Στις Wednesday 07 April 2010 11:06:44 ο/η Yeb Havinga έγραψε: Achilleas Mantzios wrote: You could also consider the genealogical approach, e.g. The parents of any node to the root, i.e. the path of any node to the root are depicted as parents[0] : immediate parent parents[1] : immediate parent of the above parent What I have more than one parent? Then it is no longer neither a tree, nor a hierarchical structure, but rather a graph. This a totally different problem. My question was actually an attempt to point at the inability of what you call the 'genealogical approach' database design to store information of more than one parent. Are you suggesting that we should change our definition of trees ADT, just because it does not fit the mere detail that humans have two parents? Or are you just suggesting that the "genealogical" term is inaccurate? The latter, but rethinking it, why would genealogical be a bad word when applied to graph algorithm 'stuff' when words like parent, child, ancestor, sibling are common use. When I read 'genealogical' I had only the connotation 'family relations' in mind. I suspect that if looking at the definition of the word genealogy alone, it could very well include the study of single parent transitive relationships. However, not exclusively, so yes, IMHO something called the genealogical approach should not preclude polyhierarchies. regards Yeb Havinga -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Table Design for Hierarchical Data
Leif Biberg Kristensen wrote: On Monday 12. April 2010 16.57.38 Rob Sargent wrote: Believe me: "ego-ma-pa" will correctly define genealogical relationships (at least among humans). Yes, but a family tree is not a hierarchical tree as defined in database theory. Believe me: I'm a genealogist. The last sentence is almost like the 'proof by authority' from 36 methods of mathematical proof, see e.g. http://jwilson.coe.uga.edu/EMT668/EMAT6680.F99/Challen/proof/proof.html. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Using SKYLINE command on PostgreSQL
On Sun, Aug 8, 2010 at 5:35 AM, Craig Ringer wrote: > > The masters thesis page does contain a patch against PostgreSQL 8.3: > http://skyline.dbai.tuwien.ac.at/ > That page has a nice web page and examples where queries can be run. 'The skyline operator' is described in http://www.dbis.ethz.ch/research/publications/38.pdf. It reminds me a bit of the staircase join operator, something on my personal wish list for postgres as well (latest patch seems to have got lost on the internet). > > They say they're porting the patch to 8.4devel, but I suspect that since > 8.4 has been out for ages and 9.0 is almost released, they've probably > lost interest and dropped the work. > I looked a bit in the latest patch. There's a lot of FIXME's and TODO's in it. It adds at least one plan node, so it's probably hard to rewrite into contrib form. That'll probably make it harder to get accepted for mainline postgres, also because the skyline queries can be translated into 'normal' sql (referenced pdf, section 3.1), though the authors claim the performance using skyline is better. Since 8.3 CTE's have been introduced and maybe that makes writing these kinds of queries easier. Also the knngist ordering of 'interesting points' might be a solution for problems in the same ballpark. regards, Yeb Havinga
Re: [SQL] what's wrong in this procedure?
On 2011-02-25 19:33, Adrian Klaver wrote:
On 02/25/2011 09:46 AM, Camaleon wrote:
This error is returned Erro de SQL:
ERROR: column "Aguardando Pagto" does not exist at character 352>>>
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS '
SELECT o.data_fim, sum(t.num_itens * t.valor) AS total,
count(t.*) AS transacoes
FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
WHERE o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em
análise", "Aprovado", "Completo")
GROUP BY o.data_fim;
'
language 'sql';
the column is t.status and not "Aguardando Pagto";
what's wrong ? thanks
Try single quotes, 'Aguardando Pagto'
and also
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS $$
instead of
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS '
together with the corresponding
$$
language 'sql';
instead of
'
language 'sql';
at the end
regards,
Yeb Havinga
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
