No title

On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote:

Hi Volk,

at first sorry for my English.  I use postgresql very often and I really
love it but the syntax for outer join make me sick.
Oracle short notation (+) is also not a best choice at this place but I
recall me, that the Informix have a really good and clear syntax:

Note that the word outer is just noise in pgsql, i.e. it's not needed.
What you've got are left outer, right outer, and full outer joins.
All can be called just left, right, or full joins. Note that inner
joins are just called joins.

select * from a, outer b where a.id = b.id;

select * from a full join b on (a.id=b.id) where ...
select * from a left join b on (a.id=b.id) where ...
select * from a join b on (a.id=b.id) where ...

and so on.

And surely, I would like to see that also in postgresql.

What you get with postgresql is mostly ANSI standard stuff, which
left/right/full outer and inner joins are.

On Mon, Jun 29, 2009 at 5:11 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:

On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote:

Hi Volk,

at first sorry for my English.  I use postgresql very often and I really
love it but the syntax for outer join make me sick.
Oracle short notation (+) is also not a best choice at this place but I
recall me, that the Informix have a really good and clear syntax:

Note that the word outer is just noise in pgsql, i.e. it's not needed.
 What you've got are left outer, right outer, and full outer joins.
All can be called just left, right, or full joins.  Note that inner
joins are just called joins.

select * from a, outer b where a.id = b.id;

select * from a full join b on (a.id=b.id) where ...
select * from a left join b on (a.id=b.id) where ...
select * from a join b on (a.id=b.id) where ...

also,

select * from a join b using(id) where...;

In simple join cases this is usually the best way to go.

merlin

On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote:

Hi Volk,

Note that the word outer is just noise in pgsql, i.e. it's not needed.
What you've got are left outer, right outer, and full outer joins.
All can be called just left, right, or full joins. Note that inner
joins are just called joins.

select * from a, outer b where a.id = b.id;

select * from a full join b on (a.id=b.id) where ...
select * from a left join b on (a.id=b.id) where ...
select * from a join b on (a.id=b.id) where ...

this is only a simple case, but outer can make syntax more clean in complicated joins.
Just try to rewrite query below with left outter joins. I had not found any compact syntax.

-- c *= b *= a =* d =* f
select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

and so on.

And surely, I would like to see that also in postgresql.

What you get with postgresql is mostly ANSI standard stuff, which
left/right/full outer and inner joins are.

And I like that, but each SQL RDMS system and each SQL dialect de facto provide also a lot of standard extensions. So the RDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very sane extensions.

I think so.

______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

On Tue, Jun 30, 2009 at 2:00 AM, Waldemar
Bergstreiser<littlesuspense@web.de> wrote:

On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote:

Hi Volk,

Note that the word outer is just noise in pgsql, i.e. it's not needed.
 What you've got are left outer, right outer, and full outer joins.
All can be called just left, right, or full joins.  Note that inner
joins are just called joins.

select * from a, outer b where a.id = b.id;

select * from a full join b on (a.id=b.id) where ...
select * from a left join b on (a.id=b.id) where ...
select * from a join b on (a.id=b.id) where ...

this is only a simple case, but outer can make syntax more clean in complicated joins.
Just try to rewrite query below with left outter joins. I had not found any compact syntax.

-- c *= b *= a =* d =* f
select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

from a full join b on (a.id=b.id)
full join c on (b.id=c.id)
full join d

and so on. Doesn't seem any lestt compact or readable to me.

What you get with postgresql is mostly ANSI standard stuff, which
left/right/full outer and inner joins are.

And I like that, but each SQL RDMS system and each SQL dialect de facto provide also a lot of standard extensions. So the RDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very sane extensions.

If the standard SQL syntax does the job, I see no reason to include
non-standard syntax from other dbs unless they're obviously cleaner
and simpler, and I really don't see that here. Every thing you add to
the query planner / executor costs something in planning and / or
execution times.

Further, most database engines now support sql standard join syntax,
so there's no great reason to support it for compatibility reasons.

-----Ursprüngliche Nachricht-----
Von: "Scott Marlowe" <scott.marlowe@gmail.com>
Gesendet: 30.06.09 10:17:11
An: Waldemar Bergstreiser <littlesuspense@web.de>
CC: pgsql-general@postgresql.org
Betreff: Re: [GENERAL]

On Tue, Jun 30, 2009 at 2:00 AM, Waldemar
Bergstreiser<littlesuspense@web.de> wrote:

On Mon, Jun 29, 2009 at 2:08 PM, littlesuspense<littlesuspense@web.de> wrote:

Hi Volk,

Note that the word outer is just noise in pgsql, i.e. it's not needed.
 What you've got are left outer, right outer, and full outer joins.
All can be called just left, right, or full joins.  Note that inner
joins are just called joins.

select * from a, outer b where a.id = b.id;

select * from a full join b on (a.id=b.id) where ...
select * from a left join b on (a.id=b.id) where ...
select * from a join b on (a.id=b.id) where ...

this is only a simple case, but outer can make syntax more clean in complicated joins.
Just try to rewrite query below with left outter joins. I had not found any compact syntax.

-- c *= b *= a =* d =* f
select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

from a full join b on (a.id=b.id)
full join c on (b.id=c.id)
full join d

I guess, you don't get it. Probably so

select * from a left outer join b on (a.b_id=b.id) ....

But I don't see any clear way to specify that table C should be outer joined only if we got a row from table B.

and so on. Doesn't seem any lestt compact or readable to me.

What you get with postgresql is mostly ANSI standard stuff, which
left/right/full outer and inner joins are.

And I like that, but each SQL RDMS system and each SQL dialect de facto provide also a lot of standard extensions. So the RDMS user has a choice, to use such extensions or not. And I think postgres can only win if we it have more and very sane extensions.

If the standard SQL syntax does the job, I see no reason to include
non-standard syntax from other dbs unless they're obviously cleaner
and simpler, and I really don't see that here. Every thing you add to
the query planner / executor costs something in planning and / or
execution times.

I am fully agreed with you. My point is, that the syntax with outer is much clearer
and can't be so easy rewritten in standard ANSI SQL.
And I hope this can be implemented in SQL parser so the planner is not affected, and on the other side,
the reduction of overall SQL statement's length can have a positive impact on network traffic and speed of SQL parsing.

Further, most database engines now support sql standard join syntax,
so there's no great reason to support it for compatibility reasons.

This suggestion is not caused by compatibility consideration. It's just a try to take over a good things from oldies RDMS.

______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de

On Tue, Jun 30, 2009 at 9:58 AM, Waldemar
Bergstreiser<littlesuspense@web.de> wrote:

-- c *= b *= a =* d =* f
select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

from a full join b on (a.id=b.id)
full join c on (b.id=c.id)
full join d

I guess, you don't get it. Probably so

I don't get it either. by *= do you mean the Oracle-style outer join?
in which case why is this not just

select * from a,
left outer join b on (a.b_id = b.id)
left outer join c on (b.c_id = c.id)
left outer join d on (a.d_id = d.id)
left outer join f on (d.f_id = f.id)

You can parenthesize it different ways but I think the result in this
case is actually the same.

select * from a left outer join b on (a.b_id=b.id) ....

But I don't see any clear way to specify that table C should be outer joined only if we got a row from table B.

Well that would be the default since if you get no row from b b.c_id
will be null.

--
greg
http://mit.edu/~gsstark/resume.pdf

-- c *= b *= a =* d =* f
select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

from a full join b on (a.id=b.id)
full join c on (b.id=c.id)
full join d

I guess, you don't get it. Probably so

I don't get it either. by *= do you mean the Oracle-style outer join?
in which case why is this not just

select * from a,
left outer join b on (a.b_id = b.id)
left outer join c on (b.c_id = c.id)
left outer join d on (a.d_id = d.id)
left outer join f on (d.f_id = f.id)

The equal expression would be like this:

select * from a
left outer join (b left outer join c on (b.c_id = c.id) ) on (a.b_id = b.id)
left outer join (d left outer join f on (d.f_id = f.id) ) on (a.d_id = d.id)

and that is a double length of original SQL and has not very clear syntax in my opinion.
Additionally it can be very funny if we try to append a join conditions like "c.iso = d.iso"

You can parenthesize it different ways but I think the result in this
case is actually the same.

I'm not sure.

select * from a left outer join b on (a.b_id=b.id) ....

But I don't see any clear way to specify that table C should be outer joined only if we got a row from table B.

Well that would be the default since if you get no row from b b.c_id
will be null.

and if I have a row in table C where c.id is null? A don't know.

I found a good explanation about informix outer joins.

http://savage.net.au/SQL/outer-joins.html

Please take a look at that.

________________________________________________________________
Neu: WEB.DE Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate
für nur 19,99 Euro/mtl.!* http://produkte.web.de/go/02/

Waldemar Bergstreiser <littlesuspense@web.de> writes:

Just try to rewrite query below with left outter joins. I had not found any compact syntax.

select * from a, outer( b, outer c), outer (d, outer f )
where a.b_id = b.id and b.c_id = c.id and a.d_id = d.id and d.f_id = f.id;

This has got pretty much the same problem as Oracle's syntax: there's no
principled way to decide what it *means*. Which join is each of the
WHERE conditions supposed to be attached to, and why? What do you do if
you want a behavior slightly different from whatever the engine decides
it means?

The standard's syntax is a bit more verbose, but at least it's perfectly
clear which conditions are outer-join conditions and which are filters.

regards, tom lane

On Jun 30, 2009, at 4:14 AM, Waldemar Bergstreiser wrote:

I don't get it either. by *= do you mean the Oracle-style outer
join?
in which case why is this not just

select * from a,
left outer join b on (a.b_id = b.id)
left outer join c on (b.c_id = c.id)
left outer join d on (a.d_id = d.id)
left outer join f on (d.f_id = f.id)

<snip>

select * from a left outer join b on (a.b_id=b.id) ....

But I don't see any clear way to specify that table C should be
outer joined only if we got a row from table B.

Well that would be the default since if you get no row from b b.c_id
will be null.

and if I have a row in table C where c.id is null? A don't know.

No, it's perfectly clear as 'NULL = NULL' evaluates to false:

postgres=# select null = null;
?column?
----------

(1 row)

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote:

postgres=# select null = null;
?column?
----------

(1 row)

Actually, it's NULL.

shackle@postgres:5432=# SELECT (NULL = NULL) IS NULL;
?column?
----------
t
(1 row)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Jun 30, 2009 at 1:22 PM, Erik Jones<ejones@engineyard.com> wrote:

and if I have a row in table C where c.id is null? A don't know.

No, it's perfectly clear as 'NULL = NULL' evaluates to false:

postgres=# select null = null;
 ?column?
----------

you can test for that with 'is distinct from':

select null is distinct from null; -- false
select null is distinct from 1; -- true

On Jun 30, 2009, at 11:25 AM, David Fetter wrote:

On Tue, Jun 30, 2009 at 10:22:23AM -0700, Erik Jones wrote:

postgres=# select null = null;
?column?
----------

(1 row)

Actually, it's NULL.

shackle@postgres:5432=# SELECT (NULL = NULL) IS NULL;
?column?
----------
t
(1 row)

Er, yeah, I mispoke, my point was that it doesn't evaluate to TRUE so
the join doesn't hit a match, though...

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

On Tue, Jun 30, 2009 at 01:14:10PM +0200, Waldemar Bergstreiser wrote:

I found a good explanation about informix outer joins.

http://savage.net.au/SQL/outer-joins.html

Please take a look at that.

The syntax appears to make the expression of various idioms difficult;
for example, how would I express the following:

SELECT *
FROM a LEFT JOIN b ON a.id = b.id
WHERE b.id IS NULL OR a.c <> b.d;

Admittedly I don't write code like this very often but, yes, I have used
it on some occasions. I guess I'd have to resort to a subselect?

I believe this is what Tom was referring to when he said that "there's
no principled way to decide what it *means*". For example the semantics
of the above are very different from either of:

SELECT *
FROM a LEFT JOIN b ON a.id = b.id OR a.c <> b.d
WHERE b.id IS NULL;

or:

SELECT *
FROM a LEFT JOIN b ON a.id = b.id AND (b.id IS NULL OR a.c <> b.d)

and I can't think of any other formulations after reading the link you
gave---it only seems to talk about binary operators involving columns
from two tables. Second shouldn't be allowed, but I included it in case
I was missing something.

--
Sam http://samason.me.uk/

On Wed, 2009-07-01 at 09:23 +0200, durumdara wrote:

In this world I was everytime in transaction, because of reads are
also working under transactions.

Just like PostgreSQL. You can't run a query without a transaction in
PostgreSQL; if you don't issue an explicit BEGIN, it'll do an implicit
BEGIN/COMMIT around the statement.

Aha... So if I'm getting out from normal transactions I moved into
implicit autocommit way.

Correct.

Hmmm... Then that is meaning that every statement is in new
transaction context which can makes inconsistency in the views...
For example (pseudo):
select item_head, count(items)
select items

Possible: count(items) <> len(fetchall(items)) if someone committed a
new record into "items" table...

Am I thinking good?

You are. However, this is ALSO true in a transaction by default.
PostgreSQL defaults to the READ COMMITTED isolation level, which means
that statements may see data that was committed by another transaction
after the start of the transaction in which the statement is run, but
before the start of the statement.

If you want to avoid that, you may use the SERIALIZABLE isolation level.
That has its own complications and costs, though, including the need to
be prepared to retry any transaction after a serialization failure.

(Of course, your app should be prepared to retry a transaction ANYWAY
unless you're incredibly sure your code is perfectly free from lock
conflicts etc).

See:

http://www.postgresql.org/docs/8.3/static/transaction-iso.html

Once again, I VERY strongly recommend reading the whole PostgreSQL
manual. It'll teach you a lot about SQL and relational databases in
general as well as PostgreSQL in particular, and is very well written.

So I need:

begin;
select item_head, count(items)
select items
rollback;

to get full consistent data-sets?

That won't protect you from:

TRANSACTION 1 TRANSACTION 2
begin;
begin;
select item_head, count(items)
INSERT INTO items(...)
commit;
select items

"select items" in transaction 1 can see the changes made by transaction
2 after transaction 2 commits in the default READ COMMITTED isolation
level.

See the documentation I linked to above.

And now I know from your mail that isn't true - it will be easier if I
shut down the webserver, make the modifications on PGDB and after that
I restart them all.

You can do that, but YOU SHOULD NOT HAVE TO.

If you have to, your web server or the code running in your web server
is buggy and is holding transactions open longer than it needs them.
Your web server and web app should be able to remain running and retain
connections to the database (so long as they're not idle in a
transaction that's done work). There will be a brief pause while the
ALTER TABLE executes, but that's all.

Make sure there are no connections that're idle in transaction:

select * from pg_stat_activity

If there are, you may need to alter settings in your web server,
connection pooler, or web app.

Yes. If I can make a rollback on it, all of resources released.
Now I search for a way to "force dbutils to it must make a rollback
before it re-move the connection into it's pool", or a way to I can do
this easily from the webserver...

Yes, that's what you need to do.

--
Craig Ringer

Hi!

2009.07.01. 9:43 keltez�ssel, Craig Ringer �rta:

On Wed, 2009-07-01 at 09:23 +0200, durumdara wrote:

Firebird have repeatable read, but PG is not have it. Hmmm... Then that is meaning that every statement is in new
transaction context which can makes inconsistency in the views...
For example (pseudo):
select item_head, count(items)
select items

Possible: count(items)<> len(fetchall(items)) if someone committed a
new record into "items" table...

Am I thinking good?

You are. However, this is ALSO true in a transaction by default.

Sh*t... (* = [o | u ])... :-(

MySQL doc:

REPEATABLE READ
*/_
This is the default isolation level for InnoDB_/*. For consistent
reads, there is an important difference from the READ COMMITTED
isolation level: All consistent reads within the same transaction
read the snapshot established by the first read. This convention
means that if you issue several plain (nonlocking) SELECT statements
within the same transaction, these SELECT statements are consistent
also with respect to each other. See Section 13.2.8.2,

PostgreSQL defaults to the READ COMMITTED isolation level, which means
that statements may see data that was committed by another transaction
after the start of the transaction in which the statement is run, but
before the start of the statement.

Ahhhh... this was I want to avoid.

In the Firebird world I simply used Repeatable Read without concurrency
or other error.

If some updates had conflicts in nonwaiting mode, the FB sent an error
message me to show, we had a problem.
But normally, if someone simply read the tables, or inserted new records
to it, the FB handle this case without errors.

I got errormessage only if two transactions want to do something in same
record in same time...

This is conflict:
tr1: update a set a.code = a.code
tr2: update a set a.name = "al" where a.code = 1

This is not:
tr1: select count(*) a1 from a
tr2: update a set a.name = "al" where a.code = 1
tr2: insert into a ...
tr1: select count(*) a2 from a (a1 = a2)
tr1: select * from a (a1 = a2 = fetched(records))

If you want to avoid that, you may use the SERIALIZABLE isolation level.
That has its own complications and costs, though, including the need to
be prepared to retry any transaction after a serialization failure.

(Of course, your app should be prepared to retry a transaction ANYWAY
unless you're incredibly sure your code is perfectly free from lock
conflicts etc).

Sometimes we need consistent data, this [select count(*) from a <>
fetchall(select * from a)] not good result.

But I'm not sure in SERIALIZABLE mode because I don't know, if I change
the records, or add new records to table a, I can get some errors in any
of the clients, or PG handle this without problems - as Firebird do it,
or I got many errors in the clients.

Data integrity is very important sometimes - for count(*) =
len(fetched(*)), and for querys, sums, subqueries are let equal.

See:

http://www.postgresql.org/docs/8.3/static/transaction-iso.html

Once again, I VERY strongly recommend reading the whole PostgreSQL
manual. It'll teach you a lot about SQL and relational databases in
general as well as PostgreSQL in particular, and is very well written.

Ok, I understand it, I read it, but experience is more and more than the
read.

For example:
I read the apache/fastcgi documentation, but never I think that it is
not working in Windows (as working in Linux)... :-(

Sometimes the people need to release her/his ideas from the idealist
world, because hard to realize.

Possible if I change my default transactions to "serial", it is not
working; and it is better to working with read committed - and with some
little mistakes that client's won't see...

Thanks:
dd