Re: User documentation vs Official Docs

[Tim Cross]

Our University provides access to a Linux server for any student (not just
those in data science etc)  or staff member and that computer has Postgres
available for anyone who want to use it. The server is also accessible
remotely (80% of our student base is remote/on-line). You also get a shell
account and can install any software which can be installed and run from
that account.  At another University I do some work for, they have moved to
a virtual environment, where students are able to spin up a virtual
computer on demand and have full access to install whatever software they
like (though there are some constraints on what can be setup to 'persist'
across instances. You could install PG, but I'm not sure if it would be
restored next time you spin up hyour virtual server).

>From personal experience, I can say that when I was a student, a $60 book
was very difficult to justify/afford and I greatly valued on-line resources
at that time.  I made extensive use of the library, but obtaining specific
books was not as easy as asking for them - the library has limited space
and can only maintain collections on a demand basis, so you were unlikely
to get a book just based on request.

A further aspect about on-line resources not yet mentioned is the
accessibility aspect. As a blind programmer, I know the huge benefits of
electronic resources compered to dead trees!

Tim


On Fri, 20 Jul 2018 at 11:03, Melvin Davidson  wrote:

>
>
> On Thu, Jul 19, 2018 at 8:54 PM, Adrian Klaver 
> wrote:
>
>> On 07/19/2018 05:43 PM, Melvin Davidson wrote:
>>
>>>
>>>
>>>
>>
>>>
>>>  > Then again people might use shared, university or library computers
>>> Would you please be so kind as to inform us which university or library
>>> allows users to install software on a _shared_ computer.
>>>
>>
>> Pretty sure Ken was referring to looking up documentation, not running
>> Postgres.
>>
>>
>>> BTW, since you mention library, that is an excellent way to have the
>>> books ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
>>> someone to request the library purchase the book, to be used for shared
>>> learning.
>>>
>>>
>>> --
>>> *Melvin Davidson**
>>> Maj. Database & Exploration Specialist**
>>> Universe Exploration Command – UXC***
>>> Employment by invitation only!
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
> > Pretty sure Ken was referring to looking up documentation, not running
> Postgres.
> That does not correlate. To have the need to look up documentation implies
> that the user has a computer running PostgreSQL.
> As universities DO NOT ALLOW software to be installed on shared computers,
> and this is the case especially in a library, it implies
> the user has their own computer. As libraries allow users/citizens to
> request books be purchased >at no cost to the user/citizen, the
> argument that someone cannot afford a book is now a moot point.
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>


-- 
regards,

Tim

--
Tim Cross

Re: User documentation vs Official Docs

[Rob Sargent]

On 07/19/2018 06:58 PM, Adrian Klaver wrote:

On 07/19/2018 05:54 PM, Adrian Klaver wrote:

On 07/19/2018 05:43 PM, Melvin Davidson wrote:








 > Then again people might use shared, university or library computers
Would you please be so kind as to inform us which university or 
library allows users to install software on a _shared_ computer.


Pretty sure Ken was referring to looking up documentation, not 
running Postgres.


Or on computer lab machines.



And using psql on such available devices.  Hopefully this new set of 
docs will have something for that usage too?




BTW, since you mention library, that is an excellent way to have the 
books ordered and shared.>FOR FREE<.  AFAIK, all that is required is 
for
someone to request the library purchase the book, to be used for 
shared learning.



--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!

Re: User documentation vs Official Docs

[Ken Tanzer]

On Thu, Jul 19, 2018 at 5:43 PM Melvin Davidson 
wrote:

>
> > Then again people might use shared, university or library computers
> Would you please be so kind as to inform us which university or library
> allows users to install software on a _shared_ computer.
>
> Well, just sticking to a quick Google, the Western world and my home town,
the University of Washington appears to do so, though this is from 2013:

https://courses.washington.edu/info445/docs/handbook.pdf


> BTW, since you mention library, that is an excellent way to have the books
> ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
> someone to request the library purchase the book, to be used for shared
> learning.
>
> Probably depends on the library and their level of funding

BTW, I think more tutorials and recipes are a great idea, though am
agnostic as to the forum/format used.

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: User documentation vs Official Docs

[Melvin Davidson]

On Thu, Jul 19, 2018 at 8:54 PM, Adrian Klaver 
wrote:

> On 07/19/2018 05:43 PM, Melvin Davidson wrote:
>
>>
>>
>>
>
>>
>>  > Then again people might use shared, university or library computers
>> Would you please be so kind as to inform us which university or library
>> allows users to install software on a _shared_ computer.
>>
>
> Pretty sure Ken was referring to looking up documentation, not running
> Postgres.
>
>
>> BTW, since you mention library, that is an excellent way to have the
>> books ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
>> someone to request the library purchase the book, to be used for shared
>> learning.
>>
>>
>> --
>> *Melvin Davidson**
>> Maj. Database & Exploration Specialist**
>> Universe Exploration Command – UXC***
>> Employment by invitation only!
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

> Pretty sure Ken was referring to looking up documentation, not running
Postgres.
That does not correlate. To have the need to look up documentation implies
that the user has a computer running PostgreSQL.
As universities DO NOT ALLOW software to be installed on shared computers,
and this is the case especially in a library, it implies
the user has their own computer. As libraries allow users/citizens to
request books be purchased >at no cost to the user/citizen, the
argument that someone cannot afford a book is now a moot point.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/19/2018 05:54 PM, Adrian Klaver wrote:

On 07/19/2018 05:43 PM, Melvin Davidson wrote:








 > Then again people might use shared, university or library computers
Would you please be so kind as to inform us which university or 
library allows users to install software on a _shared_ computer.


Pretty sure Ken was referring to looking up documentation, not running 
Postgres.


Or on computer lab machines.





BTW, since you mention library, that is an excellent way to have the 
books ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
someone to request the library purchase the book, to be used for 
shared learning.



--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: User documentation vs Official Docs

[Adrian Klaver]

On 07/19/2018 05:43 PM, Melvin Davidson wrote:








 > Then again people might use shared, university or library computers
Would you please be so kind as to inform us which university or library 
allows users to install software on a _shared_ computer.


Pretty sure Ken was referring to looking up documentation, not running 
Postgres.




BTW, since you mention library, that is an excellent way to have the 
books ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
someone to request the library purchase the book, to be used for shared 
learning.



--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC***
Employment by invitation only!



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: User documentation vs Official Docs

[Melvin Davidson]

On Thu, Jul 19, 2018 at 8:09 PM, Ken Tanzer  wrote:

> On Thu, Jul 19, 2018 at 11:35 AM Melvin Davidson 
> wrote:
>
>> >> Politely tell them to buy some of the many well written books that are
>> available on these very topics...
>> >Fair enough but what about those that cant afford it? I think us in the
>> Western World tend to forget that by >far the majority of users cant afford
>> a latte from Starbucks let alone a 60.00 USD dead tree.
>>
>> Seriously? So someone can afford a $500 computer ( in addition to
>> accessories ), but they wouldn't be able to afford a $60 book (which BTW,
>> there are several for lesser amount) ?
>>
>>
> Seriously?  With global median household income at less than $200 / week
> (and ranging by country from $2 to $400)[1], you can't understand that $60
> could be a barrier for lots of people? And computers can be had for far
> less than $500, even before you get to used or refurbished.  Then again
> people might use shared, university or library computers.  You've nicely
> illustrated Josh's point which you yourself quoted:  "I think us in the
> Western World tend to forget that by far the majority of users cant afford
> a latte from Starbucks let alone a 60.00 USD dead tree."
>
> My comment, BTW, applies to all versions of Postgresql, whether current,
> unsupported or yet-to-be-developed.
>
> Cheers,
> Ken
>
>
> [1] https://news.gallup.com/poll/166211/worldwide-median-
> household-income-000.aspx
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

> Then again people might use shared, university or library computers
Would you please be so kind as to inform us which university or library
allows users to install software on a _shared_ computer.

BTW, since you mention library, that is an excellent way to have the books
ordered and shared.>FOR FREE<.  AFAIK, all that is required is for
someone to request the library purchase the book, to be used for shared
learning.


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: User documentation vs Official Docs

[Ken Tanzer]

On Thu, Jul 19, 2018 at 11:35 AM Melvin Davidson 
wrote:

> >> Politely tell them to buy some of the many well written books that are
> available on these very topics...
> >Fair enough but what about those that cant afford it? I think us in the
> Western World tend to forget that by >far the majority of users cant afford
> a latte from Starbucks let alone a 60.00 USD dead tree.
>
> Seriously? So someone can afford a $500 computer ( in addition to
> accessories ), but they wouldn't be able to afford a $60 book (which BTW,
> there are several for lesser amount) ?
>
>
Seriously?  With global median household income at less than $200 / week
(and ranging by country from $2 to $400)[1], you can't understand that $60
could be a barrier for lots of people? And computers can be had for far
less than $500, even before you get to used or refurbished.  Then again
people might use shared, university or library computers.  You've nicely
illustrated Josh's point which you yourself quoted:  "I think us in the
Western World tend to forget that by far the majority of users cant afford
a latte from Starbucks let alone a 60.00 USD dead tree."

My comment, BTW, applies to all versions of Postgresql, whether current,
unsupported or yet-to-be-developed.

Cheers,
Ken


[1]
https://news.gallup.com/poll/166211/worldwide-median-household-income-000.aspx

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: User documentation vs Official Docs

[Tim Cross]

Peter J. Holzer  writes:

> On 2018-07-18 08:09:35 +1000, Tim Cross wrote:
>> If using web widgets to author content on the wiki is the main
>> impediment for contributing content, maybe we should see if the wiki
>> provides alternative access methods. I've used wikis in the past which
>> allowed users to upload content via xmlrpc, api etc. Perhaps something
>> similar could be made available for those making significant
>> contributions or to a select few 'curators' who could accept content
>> from others.
>
> There are also browser plugins like It's all text, textern, wasavi, etc.
> which allow the user to use a real text editor instead of a text area.
>
> hp

+1. Should have remember that option given I have such a plugin myself
and use it often!

-- 
Tim Cross

Re:

[Peter Geoghegan]

On Thu, Jul 19, 2018 at 11:43 AM, Torsten Förtsch
 wrote:
> is there a way to find if a certain page in a data file is referenced by a
> btree index?
>
> I found a few completely empty pages in one of my tables. I am not sure if
> that's corruption or just bloat. Now I am thinking I could use an index, the
> PK for instance, and see if it references these pages.

Ir'a probably not serious, but you may want to try amcheck's
heapallindexed check. You'll have to use the non-contrib packages for
that right now, though, but those are available from the PGDG repos.


-- 
Peter Geoghegan

[no subject]

[Torsten Förtsch]

Hi,

is there a way to find if a certain page in a data file is referenced by a
btree index?

I found a few completely empty pages in one of my tables. I am not sure if
that's corruption or just bloat. Now I am thinking I could use an index,
the PK for instance, and see if it references these pages.

Thanks,
Torsten

Re: functions with side effect

[Torsten Förtsch]

On Thu, Jul 19, 2018 at 8:10 PM Tom Lane  wrote:

> =?UTF-8?Q?Torsten_F=C3=B6rtsch?=  writes:
> > I know that. My question was about the execution order of f1 and f2 in
> > "SELECT f1(), f2()". In theory they can be executed in any order. But
> since
> > the side effect in nextval determines the result of currval, I am asking
> if
> > that order is well-defined or considered an implementation detail like
> in C.
>
> The current implementation evaluates select-list items left to right.
> I doubt we'd be eager to change that, since there are surely many
> applications that depend on that behavior, whether it's formally specified
> or not.  But elsewhere in a query than the select target list, there are
> no guarantees, and there's lots of precedent for whacking around the
> evaluation order in e.g. WHERE.
>
> I'd be a little more wary with examples like your other one:
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> since there's an additional unspecified question there, which is
> whether the planner will "flatten" the sub-select.  To put it more
> clearly, you'd be taking big risks with
>
> SELECT y, x FROM (VALUES (nextval('s'), currval('s'))) t(x, y);
>
> Right now it seems the nextval is done first, but I would not want to bet
> on that staying true in the future.  [ experiments some more ... ]
> Actually, looks like we have a rule against flattening sub-selects whose
> targetlists contain volatile functions, so maybe you'd get away with that
> for the indefinite future too.
>

 Thanks, this was actually a part of an insert statement I found in our
code. Something like

INSERT INTO ...
VALUES (nextval(), ..., 'string' || currval())

Just to be on the safe side I changed it to

INSERT INTO ...
SELECT next.id, ..., 'string' || next.id
FROM nextval() next(id)

Re: User documentation vs Official Docs

[Melvin Davidson]

>> Politely tell them to buy some of the many well written books that are
available on these very topics...
>Fair enough but what about those that cant afford it? I think us in the
Western World tend to forget that by >far the majority of users cant afford
a latte from Starbucks let alone a 60.00 USD dead tree.

Seriously? So someone can afford a $500 computer ( in addition to
accessories ), but they wouldn't be able to afford a $60 book (which BTW,
there are several for lesser amount) ?

I have been monitoring this thread for many days now. It originally started
as a request for users to write a separate user tutorial. But a tutorial
for what? That was never stated.
What exactly is missing from the official documentation? Isn't it the
purpose of pgsql-general to answer user questions? Why is there such a
great need to create yet a third form to guide
PostgreSQL users, which would only end up being included in a google search
anyway, with no guarantee of priority?

Re: functions with side effect

[Tom Lane]

=?UTF-8?Q?Torsten_F=C3=B6rtsch?=  writes:
> I know that. My question was about the execution order of f1 and f2 in
> "SELECT f1(), f2()". In theory they can be executed in any order. But since
> the side effect in nextval determines the result of currval, I am asking if
> that order is well-defined or considered an implementation detail like in C.

The current implementation evaluates select-list items left to right.
I doubt we'd be eager to change that, since there are surely many
applications that depend on that behavior, whether it's formally specified
or not.  But elsewhere in a query than the select target list, there are
no guarantees, and there's lots of precedent for whacking around the
evaluation order in e.g. WHERE.

I'd be a little more wary with examples like your other one:

SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;

since there's an additional unspecified question there, which is
whether the planner will "flatten" the sub-select.  To put it more
clearly, you'd be taking big risks with

SELECT y, x FROM (VALUES (nextval('s'), currval('s'))) t(x, y);

Right now it seems the nextval is done first, but I would not want to bet
on that staying true in the future.  [ experiments some more ... ]
Actually, looks like we have a rule against flattening sub-selects whose
targetlists contain volatile functions, so maybe you'd get away with that
for the indefinite future too.

regards, tom lane

Re: User documentation vs Official Docs

[Peter J. Holzer]

On 2018-07-19 11:43:18 -0600, Rob Sargent wrote:
> On 07/19/2018 11:04 AM, Peter J. Holzer wrote:
> > On 2018-07-18 08:09:35 +1000, Tim Cross wrote:
> > > If using web widgets to author content on the wiki is the main
> > > impediment for contributing content, maybe we should see if the wiki
> > > provides alternative access methods. I've used wikis in the past which
> > > allowed users to upload content via xmlrpc, api etc. Perhaps something
> > > similar could be made available for those making significant
> > > contributions or to a select few 'curators' who could accept content
> > > from others.
> > There are also browser plugins like It's all text, textern, wasavi, etc.
> > which allow the user to use a real text editor instead of a text area.
> > 
> Keep in mind that Chrome broke "It's all text" compatibility, at least with
> emacs and now it's done via "Edit with Emacs".  This in my experience is a
> step backwards from "It's all text".

Oh, Chrome, too?

Firefox also broke compatibility with a lot of add-ons recently ("It's
all text" among them). So I switched to textern, which was a bit more
complicated to set up, but otherwise works almost the same.

But yeah, browser add-ons have a certain tendency to succumb to bit-rot,
so they are nice tools for a user but not something a service provider
should depend on.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: User documentation vs Official Docs

[Rob Sargent]

On 07/19/2018 11:04 AM, Peter J. Holzer wrote:

On 2018-07-18 08:09:35 +1000, Tim Cross wrote:

If using web widgets to author content on the wiki is the main
impediment for contributing content, maybe we should see if the wiki
provides alternative access methods. I've used wikis in the past which
allowed users to upload content via xmlrpc, api etc. Perhaps something
similar could be made available for those making significant
contributions or to a select few 'curators' who could accept content
from others.

There are also browser plugins like It's all text, textern, wasavi, etc.
which allow the user to use a real text editor instead of a text area.

 hp

Keep in mind that Chrome broke "It's all text" compatibility, at least 
with emacs and now it's done via "Edit with Emacs".  This in my 
experience is a step backwards from "It's all text".

Re: functions with side effect

[Adrian Klaver]

On 07/19/2018 09:43 AM, Torsten Förtsch wrote:
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver > wrote:


On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
 > Hi,
 >
 > assuming
 >
 > SELECT nextval('s'), currval('s');
 >
 > or
 >
 > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
 >
 > is there any guarantee that the 2 output values are the same?

Assuming you are only working in single session:

https://www.postgresql.org/docs/10/static/functions-sequence.html

"currval

      Return the value most recently obtained by nextval for this
sequence in the current session. (An error is reported if nextval has
never been called for this sequence in this session.) Because this is
returning a session-local value, it gives a predictable answer whether
or not other sessions have executed nextval since the current
session did."


I know that. My question was about the execution order of f1 and f2 in 
"SELECT f1(), f2()". In theory they can be executed in any order. But 
since the side effect in nextval determines the result of currval, I am 
asking if that order is well-defined or considered an implementation 
detail like in C.




To eliminate plan caching:

DO
$$
DECLARE
rs record;
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE 'SELECT  nextval($1), currval($1)' INTO rs USING 
'order_test';

RAISE NOTICE 'Currval is %', rs.currval;
END LOOP;
END$$;

It still works over multiple runs, even when bumping LOOP counter to 
100,000.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: functions with side effect

[Pavel Luzanov]

Very interesting question.

postgres=# create sequence s;
CREATE SEQUENCE
postgres=# select currval('s'), nextval('s');
ERROR:  currval of sequence "s" is not yet defined in this session
postgres=# select nextval('s'), currval('s');
 nextval | currval
-+-
   1 |   1

We see different result with different order of functions.
So the question is: in which order expressions evaluated.
And I don't think that we can rely on this order.

Moreover, according to SQL standard[1]:
"If there are multiple instances of s specifying 
the same sequence generator within a
single SQL-statement, all those instances return the same value for a 
given row processed by that SQL-statement."


But in fact nextval return new value each time:

postgres=# select nextval('s'), nextval('s') from generate_series (1,3);
 nextval | nextval
-+-
   2 |   3
   4 |   5
   6 |   7

[1] http://www.wiscorp.com/sql_2003_standard.zip
    5WD-02-Foundation-2003-09.pdf
        4.21.2 Operations involving sequence generators

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 19.07.2018 19:43, Torsten Förtsch wrote:
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
> Hi,
>
> assuming
>
> SELECT nextval('s'), currval('s');
>
> or
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> is there any guarantee that the 2 output values are the same?

Assuming you are only working in single session:

https://www.postgresql.org/docs/10/static/functions-sequence.html

"currval

     Return the value most recently obtained by nextval for this
sequence in the current session. (An error is reported if nextval has
never been called for this sequence in this session.) Because this is
returning a session-local value, it gives a predictable answer
whether
or not other sessions have executed nextval since the current
session did."


I know that. My question was about the execution order of f1 and f2 in 
"SELECT f1(), f2()". In theory they can be executed in any order. But 
since the side effect in nextval determines the result of currval, I 
am asking if that order is well-defined or considered an 
implementation detail like in C.

Re: functions with side effect

[Adrian Klaver]

On 07/19/2018 09:43 AM, Torsten Förtsch wrote:
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver > wrote:


On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
 > Hi,
 >
 > assuming
 >
 > SELECT nextval('s'), currval('s');
 >
 > or
 >
 > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
 >
 > is there any guarantee that the 2 output values are the same?

Assuming you are only working in single session:

https://www.postgresql.org/docs/10/static/functions-sequence.html

"currval

      Return the value most recently obtained by nextval for this
sequence in the current session. (An error is reported if nextval has
never been called for this sequence in this session.) Because this is
returning a session-local value, it gives a predictable answer whether
or not other sessions have executed nextval since the current
session did."


I know that. My question was about the execution order of f1 and f2 in 
"SELECT f1(), f2()". In theory they can be executed in any order. But 
since the side effect in nextval determines the result of currval, I am 
asking if that order is well-defined or considered an implementation 
detail like in C.




Can't answer definitively, but:

create sequence order_test;

DO
$$
DECLARE
rs record;
BEGIN
FOR i IN 1..1000 LOOP
SELECT INTO rs nextval('order_test'), currval('order_test');
RAISE NOTICE 'Currval is %', rs.currval;
END LOOP;
END$$;

has not failed over multiple runs.

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: User documentation vs Official Docs

[Peter J. Holzer]

On 2018-07-18 08:09:35 +1000, Tim Cross wrote:
> If using web widgets to author content on the wiki is the main
> impediment for contributing content, maybe we should see if the wiki
> provides alternative access methods. I've used wikis in the past which
> allowed users to upload content via xmlrpc, api etc. Perhaps something
> similar could be made available for those making significant
> contributions or to a select few 'curators' who could accept content
> from others.

There are also browser plugins like It's all text, textern, wasavi, etc.
which allow the user to use a real text editor instead of a text area.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: functions with side effect

[Torsten Förtsch]

On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver 
wrote:

> On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
> > Hi,
> >
> > assuming
> >
> > SELECT nextval('s'), currval('s');
> >
> > or
> >
> > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
> >
> > is there any guarantee that the 2 output values are the same?
>
> Assuming you are only working in single session:
>
> https://www.postgresql.org/docs/10/static/functions-sequence.html
>
> "currval
>
>  Return the value most recently obtained by nextval for this
> sequence in the current session. (An error is reported if nextval has
> never been called for this sequence in this session.) Because this is
> returning a session-local value, it gives a predictable answer whether
> or not other sessions have executed nextval since the current session did."


I know that. My question was about the execution order of f1 and f2 in
"SELECT f1(), f2()". In theory they can be executed in any order. But since
the side effect in nextval determines the result of currval, I am asking if
that order is well-defined or considered an implementation detail like in C.

Re: functions with side effect

[Adrian Klaver]

On 07/19/2018 07:15 AM, Torsten Förtsch wrote:

Hi,

assuming

SELECT nextval('s'), currval('s');

or

SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;

is there any guarantee that the 2 output values are the same?


Assuming you are only working in single session:

https://www.postgresql.org/docs/10/static/functions-sequence.html

"currval

Return the value most recently obtained by nextval for this 
sequence in the current session. (An error is reported if nextval has 
never been called for this sequence in this session.) Because this is 
returning a session-local value, it gives a predictable answer whether 
or not other sessions have executed nextval since the current session did."




Thanks,
Torsten



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: cache lookup failed for attribute 1 of relation XXXXXX

[Alessandro Aste]

Thanks much, I'll keep my eyes open today night hoping it will not happen
again.

On Thu, Jul 19, 2018 at 5:39 PM, Tom Lane  wrote:

> [ please keep the list cc'd for the archives' sake ]
>
> Alessandro Aste  writes:
> > Hello Tom, thanks for your reply:
> >  SELECT * FROM pg_class  WHERE OID = 2223152859 ;
> > (0 rows)
> > I'm not aware of any DDL at that time.
>
> Hm.  Well, that OID was definitely there when pg_dump looked, and
> it's not there now, so something changed --- though we can't prove
> it changed concurrently.
>
> In any case, I'd bet that if we ran this to ground it would prove to be a
> concurrent-DDL issue.  pg_dump tries to protect itself against concurrent
> DDL, but for assorted architectural reasons the protection is not 100%;
> sometimes you can get odd failures like this, essentially due to "clock
> skew" between pg_dump's view of the catalogs and the server's view of the
> catalogs.  As long as it works on retry, I wouldn't worry too much about
> it.
>
> regards, tom lane
>

Re: cache lookup failed for attribute 1 of relation XXXXXX

[Tom Lane]

[ please keep the list cc'd for the archives' sake ]

Alessandro Aste  writes:
> Hello Tom, thanks for your reply:
>  SELECT * FROM pg_class  WHERE OID = 2223152859 ;
> (0 rows)
> I'm not aware of any DDL at that time.

Hm.  Well, that OID was definitely there when pg_dump looked, and
it's not there now, so something changed --- though we can't prove
it changed concurrently.

In any case, I'd bet that if we ran this to ground it would prove to be a
concurrent-DDL issue.  pg_dump tries to protect itself against concurrent
DDL, but for assorted architectural reasons the protection is not 100%;
sometimes you can get odd failures like this, essentially due to "clock
skew" between pg_dump's view of the catalogs and the server's view of the
catalogs.  As long as it works on retry, I wouldn't worry too much about
it.

regards, tom lane

Re: cache lookup failed for attribute 1 of relation XXXXXX

[Tom Lane]

Alessandro Aste  writes:
> pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for
> attribute 1 of relation 2223152859

> pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname
> AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef,
> t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident,
> t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred,
> c.tableoid AS contableoid, c.oid AS conoid,
> pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname
> FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS
> tablespace, t.reloptions AS indreloptions FROM pg_catalog.pg_index i JOIN
> pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN
> pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid =
> c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid =
> '2223152859'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
> indexname

Hmm ...

> Index with that indexrelid does not exists
> SELECT * FROM pg_catalog.pg_index WHERE indexrelid = '2223152859';
> (0 rows)

You sure?  The failed query was looking for indrelid not indexrelid.
That implies that 2223152859 is/was the table not the index.  In
any case it'd be better to look for that OID in pg_class.

If that table is in fact gone, one possible theory is that it was
dropped concurrently with the pg_dump run.  Then possibly
pg_get_indexdef() would see the table as already gone when the
surrounding query had found it in the catalogs, which could easily
result in the reported error.  However, pg_dump shouldn't be poking
into the indexes of a table that it doesn't have AccessShareLock on,
so I'm not quite sure how we could end up with this result.  Maybe
the true explanation is more complicated, like an ALTER DROP COLUMN
that committed just as pg_dump was starting.  Have you got records
of any DDL being done at that time?

regards, tom lane

Re: functions with side effect

[Laurenz Albe]

Torsten Förtsch wrote:
> assuming 
> 
> SELECT nextval('s'), currval('s');
> 
> or
> 
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
> 
> is there any guarantee that the 2 output values are the same?

You can use at EXPLAIN (VERBOSE) output to see if it will work
in this special case, but I wouldn't depend on it in general.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

functions with side effect

[Torsten Förtsch]

Hi,

assuming

SELECT nextval('s'), currval('s');

or

SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;

is there any guarantee that the 2 output values are the same?

Thanks,
Torsten

Incorrect description of the WITH CHECK in the row security can lead to the security issue

[Олег Самойлов]

Hi all. This is a documentation issue, I already sent to pgsql-docs, but there 
is not reply.

https://www.postgresql.org/message-id/152637961531.27212.188002690528452...@wrigleys.postgresql.org

I'll try here. 

PostgreSQL 10 (in 11 the same
https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html

cite
To use a different policy for rows that are being added to the table
compared to those rows that are visible, the WITH CHECK clause can be used.
This policy would allow all users to view all rows in the users table, but
only modify their own:

CREATE POLICY user_policy ON users
USING (true)
WITH CHECK (user_name = current_user);
end cite

This is is wrong description. Every one can steal other row with such
policy. Lets demonstrate.

You are now connected to database "olleg" as user "olleg".
olleg(at)[local]:9700/olleg
=> create table users (user_name text primary key, description text);
CREATE TABLE
olleg(at)[local]:9700/olleg
=> ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE
olleg(at)[local]:9700/olleg
=> grant all on users to public;
GRANT
=> CREATE POLICY user_policy ON users
-> USING (true)
-> WITH CHECK (user_name = current_user);
CREATE POLICY
olleg(at)[local]:9700/olleg
=> insert into users (user_name) values ('olleg');
INSERT 0 1
olleg(at)[local]:9700/olleg
=> set role postgres;
SET
olleg(at)[local]:9700/olleg
=# create user test with password 'test' login;
CREATE ROLE
=# \c olleg test localhost 9700
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
bits: 256, compression: off)
You are now connected to database "olleg" as user "test" on host "localhost"
at port "9700".
test(at)localhost:9700/olleg
=> select * from users;
 user_name | description 
---+-
 olleg | 
(1 row)

test(at)localhost:9700/olleg
=> update users set user_name='test', description='a rude hack';
UPDATE 1
test(at)localhost:9700/olleg
=> select * from users;
 user_name | description 
---+-
 test  | a rude hack
(1 row)

The right statement to not allow modify rows by other user will be

CREATE POLICY user_policy ON users
USING (user_name = current_user)
WITH CHECK (user_name = current_user);
end cite

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Chris]

Hi Dmitry，

I think this a wonderful idea, but it will be tough. Share my experience:
—dbeaver: 
It is for multi-platform so it is just for use, no particular function, 
also there is more bugs(our company had changed some of them).
dbeaver is likely the most open source app form pg now, I know more 
people use it.
—pgadmin4:
I don’t like web client for database, I used it and it is good for 
there is simple monitor-windows.
For now, I used jetbrains’s product:datagrip, it is also coded by java,but is 
better for dbeaver.

Best Wishes,
Chris


> 在 2018年7月17日，上午6:21，Tim Cross  写道：
> 
> 
> Dmitry Igrishin mailto:dmit...@gmail.com>> writes:
> 
>> пн, 16 июл. 2018 г. в 1:14, Tim Cross :
>> 
>>> 
>>> Your idea to make it integrate with user's preferred editor is a good
>>> idea as editors are like opinions and certain anatomical parts -
>>> everyone has one! Finding an appropriate API to do this will be a
>>> challenge.
>>> 
>> I see two options here: the core of the tool acts as a long-lived server or
>> as a short-lived
>> console application which communicates with the editor's plugin via
>> stdin/stdout.
>> Btw, what the text editor do you prefer? :-)
>> 
> 
> Most of the time, I use Emacs on either Linux or macOS. With the support
> it has for running a psql process, it works pretty well for most
> things. There are pretty reasonable packages for writing SQL and
> 'static' completion. Getting things setup can take a bit of effort, but
> once it is working, it tends to work pretty well.
> 
> The two areas where it lacks are dynamic completion i.e. completing on
> objects the user has created such as table names and column
> names/function names etc. and decent result formatting. 
> 
>>> 
>>> I seem to remember reading somewhere that Oracle was going to remove
>>> swing from the core java library. I've always been a little disappointed
>>> with Java UIs and found they don't give the cross-platform support that
>>> Java originally promised, plus OSX/macOS has not made Java as welcome as
>>> it use to be. If you do choose Java, it will need to work under openJDK
>>> as this is what most Linux users will have installed.
>>> 
>> For now, the possible options for the GUI part are Qt, wxWidgets or FLTK,
>> or even Electron.
> 
> I would look at either Qt or even Electron (I believe visual code is
> written using Electron, which is the other editor I use from time to
> time).
> 
> There was an Emacs project called Eclaim (I think) which interfaced with
> Eclipse services in order to provide dynamic completion when doing
> Java. That could be worth checking out for ideas to borrow.
> 
> Tim
> 
> -- 
> Tim Cross