JOINS example
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/tutorial-join.html Description: The data is incomplete for results on all possible joins, given as an exercise. Help the student by introducing a temporary fake city, with no matching weather data. With that the left, right and full join will truly show a student different results. Thanks, francis
self join
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/tutorial-join.html Description: The example of self join shows two resulting records. I have checked the input data. There are three output records with lower lo and higher high temperatures We seem to be missing, in the answer, a third record: ( (San Francisco, 46, 50) , (Hayward, 54, 37 ))
Typo
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/sql-createfunction.html Description: I have noted that it seems not convenient. " Writing SECURITY DEFINER Functions Safely " https://www.postgresql.org/docs/11/sql-createfunction.html
Re: Typo
On 4/10/19 12:30 PM, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/sql-createfunction.html > Description: > > I have noted that it seems not convenient. > " > Writing > SECURITY DEFINER > Functions Safely > " So it appears what's being generated is: Writing SECURITY DEFINER Functions Safely where on pgweb, the CSS on the "" block has no margins on the left and right. Adding in the margins fixing the spacing in a large view screen, but it looks funky on mobile. I could probably come up with some sort of solution that works and is still responsive. Jonathan signature.asc Description: OpenPGP digital signature
Re: 30.3. Asynchronous Commit
On Wed, Mar 27, 2019 at 12:25:34PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.6/wal-async-commit.html > Description: > > How do async commits and unlogged tables relate? You might add a paragraph > on clarifying this relation. > https://www.postgresql.org/docs/9.6/wal-async-commit.html Async commit controls the WAL writes for _logged_ tables. Unlogged tables don't generate WAL, since they are not crash safe, and hence are not affected by async commit. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: 30.3. Asynchronous Commit
Hi, On 2019-04-10 16:06:10 -0400, Bruce Momjian wrote: > On Wed, Mar 27, 2019 at 12:25:34PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/9.6/wal-async-commit.html > > Description: > > > > How do async commits and unlogged tables relate? You might add a paragraph > > on clarifying this relation. > > https://www.postgresql.org/docs/9.6/wal-async-commit.html > > Async commit controls the WAL writes for _logged_ tables. Unlogged > tables don't generate WAL, since they are not crash safe, and hence are > not affected by async commit. That's not quite true, I think. Async commit is a question that only matters at commit time. Logged/Unlogged only matters insofar as it determines whether WAL has been written up ot that point. Currently we *always* perform an async commit if a transaction didn't write any WAL, even if an xid has been assigned (but we'll log a commit record). But even transactions that only write to unlogged tables can easily have WAL assigned - e.g. hot pruning in catalog tables or during reads from user defined logged tables; or enough subtransactions were used that an xid assigment record had to be written; or some logged table (including catalog tables) was accessed, and a hit bit had been set, and wal_log_hint bits were set, and a lot of other reasons. Thus I think it's wrong to say that async commit doesn't mean anything for unlogged tables. I'd also say that it's not correct to say that async commit really controls WAL writes at all. It's just whether we *flush* the commit record, or not. Greetings, Andres Freund
Re: self join
Em qua, 10 de abr de 2019 às 09:23, PG Doc comments form
escreveu:
>
> The example of self join shows two resulting records.
> I have checked the input data.
> There are three output records with lower lo and higher high temperatures
>
> We seem to be missing, in the answer, a third record:
> ( (San Francisco, 46, 50) , (Hayward, 54, 37 ))
>
No, it is not. See src/tutorial/basics.source. I reproduce some
commands of that file above. Note that that self join returns only 2
records.
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
INSERT INTO weather
VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities
VALUES ('San Francisco', '(-194.0, 53.0)');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
SELECT W1.city, W1.temp_lo, W1.temp_hi,
W2.city, W2.temp_lo, W2.temp_hi
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
and W1.temp_hi > W2.temp_hi;
--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
