Using random() in update produces same random value for all

[Alex Magnum]

Hi,
i am trying to update a table with some random dates but that does not seem
to work.

UPDATE table
   SET last_update=now()::date-(SELECT (random() * 5)::INTEGER + 1)

The updated field is always set to the same. Is there a way to make it
random for every record?

I could run it through a function but I wonder if there is s simpler way.

Thanks for any help on this

Alex

Re: Using random() in update produces same random value for all

[Ken Tanzer]

On Sun, Jan 14, 2018 at 2:01 AM, Alex Magnum  wrote:

> Hi,
> i am trying to update a table with some random dates but that does not
> seem to work.
>
> UPDATE table
>SET last_update=now()::date-(SELECT (random() * 5)::INTEGER + 1)
>
> The updated field is always set to the same. Is there a way to make it
> random for every record?
>
> I could run it through a function but I wonder if there is s simpler way.
>
>
I verified this with a SELECT, not an UPDATE, but I think leaving this as a
simple expression will do what you want.  Just leave out the SELECT:

 UPDATE table
   SET last_update=now()::date-((random() * 5)::INTEGER + 1)

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: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

[Peter J. Holzer]

On 2018-01-10 11:39:21 -0800, Andres Freund wrote:
> On 2018-01-09 20:51:17 -0500, Stephen Frost wrote:
> > * Andreas Joseph Krogh (andr...@visena.com) wrote:
> > > Aha, so enabling CRC causes hint-bits to be written causing extra 
> > > WAL-logging, 
> > > which woudn't be the case without CRC enabled?
> > > Thanks for pointing that out.
> > 
> > Yes, having checksums enabled forces logging of hint bits.  You can
> > enable wal_log_hints independently too, without having checksums, to see
> > what kind of an impact it'll have on your environment.
> > 
> > A useful documentation update might be:
> > 
> > ---
> > With checksums enabled, wal_log_hints 
> > will be enabled and each page read or write will involve calculating the
> > checksum for the page.
> > ---
> > 
> > I'd probably just replace the "Enabling checksums may incur a noticeable
> > performance penalty" with the above, as it should be clear that doing
> > more work implies an impact on performance and that avoids the whole
> > question of trying to characterize in a general way something that can't
> > be generalized (as it's workload dependent).
> 
> -1. I think this is underplaying the cost.

I disagree. At least two people in this thread interpreted "noticable"
as "measurable but negligible" (which I personally find a bit
surprising). Computing the CRC basically means reading the whole page
from RAM which should be fast compared to a transfer to or from disk. So
it is easy to disregard this sentence as "was probably written when a
Pentium II was new and never updated". Stephen's version draws attention
to the fact that enabling CRCs may cause extra disk writes, which rings
a much louder alarm bell for me. (When are those hint-bits set? Does this
happen often when otherwise no write would have been necessary? I have
no idea so I guess I'd better measure it!)

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: String comparison problem in select - too many results

[Peter J. Holzer]

On 2018-01-12 11:08:39 +0100, Francisco Olarte wrote:
> C collation is like sorting raw bytes, it doesn't event sort
> upper/lower case correctly

Now you are falling into the same trap as Durumdara, calling an
unintended sort order "not correct" ;-).

C collation is certainly not what a "normal user" expects. It is
therefore wrong for many applications (e.g., you couldn't use it to
sort a telephone book), but it might be correct for others (e.g., it you
need a stable, unambiguous sort order but don't care much about the
order itself).

(By coincidence, I also stumbled over the fact that the en_US.UTF-8
collation ignores punctuation characters and spaces at least in the
first pass - took me a minute or so to figure out why I got an
"inconsistent" (read: unexpected and not obvious for me) sort order.)

Lexicographers are interested in sorting single words. Therefore they
aren't interested in punctuation. They may also not be very interested
in accents, because most languages have few words which differ only by
an accent (and it the dictionary is printed on paper, the user will
easily be able to scan up and down a few centimeters and find the right
entry without knowing whether "à" is sorted before or after "á").

Somebody preparing an address list should care about punctuation: You
would probably not expect to find "Smith-Jones, Amanda" between "Smith,
John" and "Smith, Julia". And you probably want to sort "23 Main Street"
before "180 Main Street".

Which brings us back to Durumdara's example: I don't know his
application, so I don't know what "normal users" of his application
would expect, but to me those values look like two numbers separated by
a slash. So I would expect '23/4567' to be sorted between '18/0212' and
'180/2010', but of course the C collation doesn't do that:

=> select * from foo order by t collate "C";
╔══╗
║t ║
╟──╢
║ 18/0113  ║
║ 18/0212  ║
║ 180/2010 ║
║ 23/4567  ║
╚══╝
(4 rows)

It might be possible to define a custom collation for that, but in a
case like this I would either split this field into two integer fields
or use a function-based index.

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

Is ORDER BY in sub-query preserved when outer query is only projection?

[Andreas Joseph Krogh]

Hi.
 
I have this query:
 
SELECT q.* FROM (
  SELECT comp.id, comp.name
  FROM company comp JOIN req r ON r.company_id = comp.id
  ORDER BY LOWER(comp.name) ASC
) AS q
ORDER BY r.status ASC
 
What I'm trying to do here is to order by some status (which may be only 1 of 
3 values, for instance OPEN, IN_PROGRESS, CLOSED), then order by company-name 
so I get results for each status sorted by company-name.
 
Is this kind of sort stable, can I assume the pre-sorted result's order is 
preserved so I achieve what I want?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Re: String comparison problem in select - too many results

[Francisco Olarte]

On Sun, Jan 14, 2018 at 12:14 PM, Peter J. Holzer  wrote:
> On 2018-01-12 11:08:39 +0100, Francisco Olarte wrote:
>> C collation is like sorting raw bytes, it doesn't event sort
>> upper/lower case correctly
>
> Now you are falling into the same trap as Durumdara, calling an
> unintended sort order "not correct" ;-).

Well, had you quoted / read a little farther:
>>
C collation is like sorting raw bytes, it doesn't event sort
upper/lower case correctly ( Do not know how you do it in HU, but in
ES we sort aA before bB, while C locale normally sorts AB..ab.. It's
what non-locale aware programs use because it's dirt cheap to
implement, just sort the data as unsigned byte arrays
lexicographically. And in fact, IIRC, utf-8 is dessigned in a way that
this works too ( You can sort by raw bytes and you get the data sorted
lexicographically sorted by code points ).
<<

You'll see I was trying to define "correctly" somehow. English is not
my native language and it seems I obviously failed at it, I'll try to
do it better next time.

> C collation is certainly not what a "normal user" expects. It is
> therefore wrong for many applications (e.g., you couldn't use it to
> sort a telephone book), but it might be correct for others (e.g., it you
> need a stable, unambiguous sort order but don't care much about the
> order itself).

stable / unambiguous is shared by a la lot of collation methods, but I
see what you try to say.


> (By coincidence, I also stumbled over the fact that the en_US.UTF-8
> collation ignores punctuation characters and spaces at least in the
> first pass - took me a minute or so to figure out why I got an
> "inconsistent" (read: unexpected and not obvious for me) sort order.)

Nearly all the locale-aware sorts do funny things with
punctuation/spaces. I've found sort more and more surprissing since I
started ( with the electromechanical IBM card sorters, those )

> Lexicographers are interested in sorting single words. Therefore they
> aren't interested in punctuation. They may also not be very interested
> in accents, because most languages have few words which differ only by
> an accent

We have loads of them in spanish, but they are normally easy and many
of them come from verbs conjugation which does not o into the
dictionary  ...

> (and it the dictionary is printed on paper, the user will
> easily be able to scan up and down a few centimeters and find the right
> entry without knowing whether "à" is sorted before or after "á").

and we have none of this, I only know French doing it.

> Somebody preparing an address list should care about punctuation: You
> would probably not expect to find "Smith-Jones, Amanda" between "Smith,
> John" and "Smith, Julia". And you probably want to sort "23 Main Street"
> before "180 Main Street".


> Which brings us back to Durumdara's example: I don't know his
> application, so I don't know what "normal users" of his application
> would expect, but to me those values look like two numbers separated by
> a slash. So I would expect '23/4567' to be sorted between '18/0212' and
> '180/2010', but of course the C collation doesn't do that:

He does not seem to want this. As all his examples use the same prefix
I think he just want to extract a small range of keys with a common
prefix . I've had this problems when "augmenting" a part-number code
to be product-part, and using things like "between xxx- and xxx-zz" to
get all parts for product xxx ( it was changed to dual fields at the
next iteration, but sometimes you need these things for the interim ).

I mean, the fact that they are both numbers doesn't mean he wants /
need numerical ordering on them, for many purposes just collapsing
prefixes is enough.

>
> => select * from foo order by t collate "C";
> ╔══╗
> ║t ║
> ╟──╢
> ║ 18/0113  ║
> ║ 18/0212  ║
> ║ 180/2010 ║
> ║ 23/4567  ║
> ╚══╝
> (4 rows)
>
> It might be possible to define a custom collation for that, but in a
> case like this I would either split this field into two integer fields
> or use a function-based index.

Yep, but he may have a temporary problem. C collation puts all the
"prefixes" together, which normally is good enough.

Francisco Olarte.

Re: Is ORDER BY in sub-query preserved when outer query is only projection?

[Francisco Olarte]

Andreas:

On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh
 wrote:
> SELECT q.* FROM (
>   SELECT comp.id, comp.name
>   FROM company comp JOIN req r ON r.company_id = comp.id
>   ORDER BY LOWER(comp.name) ASC
> ) AS q
> ORDER BY r.status ASC
>
> What I'm trying to do here is to order by some status (which may be only 1 of 
> 3 values, for instance OPEN, IN_PROGRESS, CLOSED), then order by company-name 
> so I get results for each status sorted by company-name.
>
> Is this kind of sort stable, can I assume the pre-sorted result's order is 
> preserved so I achieve what I want?

I doubt it is mandated to be stable. But IIRC you can sort by a
non-returned field, so you should be able to do it in just one query (
http://sqlfiddle.com/#!17/aaa62/3 )

I would try

 SELECT comp.id, comp.name
 FROM company comp JOIN req r ON r.company_id = comp.id
 ORDER BY  r.status ASC, LOWER(comp.name) ASC

Francisco Olarte.

Sv: Re: Is ORDER BY in sub-query preserved when outer query is only projection?

[Andreas Joseph Krogh]

På søndag 14. januar 2018 kl. 13:30:29, skrev Francisco Olarte <
fola...@peoplecall.com >:
Andreas:

 On Sun, Jan 14, 2018 at 1:03 PM, Andreas Joseph Krogh
  wrote:
 > SELECT q.* FROM (
 >   SELECT comp.id, comp.name
 >   FROM company comp JOIN req r ON r.company_id = comp.id
 >   ORDER BY LOWER(comp.name) ASC
 > ) AS q
 > ORDER BY r.status ASC
 >
 > What I'm trying to do here is to order by some status (which may be only 1 
of 3 values, for instance OPEN, IN_PROGRESS, CLOSED), then order by 
company-name so I get results for each status sorted by company-name.
 >
 > Is this kind of sort stable, can I assume the pre-sorted result's order is 
preserved so I achieve what I want?

 I doubt it is mandated to be stable. But IIRC you can sort by a
 non-returned field, so you should be able to do it in just one query (
 http://sqlfiddle.com/#!17/aaa62/3 )

 I would try

  SELECT comp.id, comp.name
  FROM company comp JOIN req r ON r.company_id = comp.id
  ORDER BY  r.status ASC, LOWER(comp.name) ASC

 Francisco Olarte.
 
Thanks, but my real query is more complex and I need to sort on a custom 
coposite type so I think I need an outer query for that, see my post 
here: http://www.postgresql-archive.org/ORDER-BY-custom-type-td6000437.html
 
Do you see any solution sorting on a composite type without using an outer 
query?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: String comparison problem in select - too many results

[Peter J. Holzer]

On 2018-01-14 13:20:05 +0100, Francisco Olarte wrote:
> On Sun, Jan 14, 2018 at 12:14 PM, Peter J. Holzer  wrote:
> > On 2018-01-12 11:08:39 +0100, Francisco Olarte wrote:
> >> C collation is like sorting raw bytes, it doesn't event sort
> >> upper/lower case correctly
> >
> > Now you are falling into the same trap as Durumdara, calling an
> > unintended sort order "not correct" ;-).
> 
> Well, had you quoted / read a little farther:

Sorry. I had hoped that adding a winking smiley would make it clear that
this was just a friendly jab, not a serious criticism.

> >>
> C collation is like sorting raw bytes, it doesn't event sort
> upper/lower case correctly ( Do not know how you do it in HU, but in
> ES we sort aA before bB, while C locale normally sorts AB..ab.. It's
> what non-locale aware programs use because it's dirt cheap to
> implement, just sort the data as unsigned byte arrays
> lexicographically. And in fact, IIRC, utf-8 is dessigned in a way that
> this works too ( You can sort by raw bytes and you get the data sorted
> lexicographically sorted by code points ).
> <<
> 
> You'll see I was trying to define "correctly" somehow. English is not
> my native language and it seems I obviously failed at it, I'll try to
> do it better next time.

English isn't my native language either, so the failure may be on my
side (actually, I don't think a shared native language is a guarantee
for successful communication either).

I did read that and I didn't assume that you thought that there is one
and only one correct way to sort for each language, but I did think it
was slightly amusing that you used the word "correct" after berating
Durumdara for using the word "wrong".

Anyway, what I wanted to communicate is that the correct sort order
depends on the application. I agree that case should almost never be a
primary criterium, but even there might be some exceptions (I prefer
C collation for filenames, because it puts Changes, README and TODO at
the front, but I'm aware that this is mostly because I started to use
Unix in the 80's). But punctuation, spaces, ... those might have to be
treated very differently.

And so I'm not very happy with opaque collation identifiers like
"de_AT.UTF-8". What does that mean? Who decides what the correct sort
order is in Austria, and is this even the same on Linux and Windows?

Often the details don't matter. Whether digits are sorted before or
after letters, whether punctuation is ignored or considered (and if the
latter, how), as long as the order is internally consistent and not too
far off the users' expectations, the users can deal with it (and
probably won't even notice that the order is slightly different in
say the company directory and the city's phonebook). But sometimes such
details do matter and then you have to explicitely order items.


> > C collation is certainly not what a "normal user" expects. It is
> > therefore wrong for many applications (e.g., you couldn't use it to
> > sort a telephone book), but it might be correct for others (e.g., it you
> > need a stable, unambiguous sort order but don't care much about the
> > order itself).
> 
> stable / unambiguous is shared by a la lot of collation methods, but I
> see what you try to say.

I'm worried that something like "de_AT.UTF-8" is not stable. Somebody
might decide that ignoring whitespace wasn't such a good idea after all
and "fix" it. 

Unicode TR#10 actually warns about this:

| Collation order is not fixed.
|
|   Over time, collation order will vary: there may be fixes needed as
|   more information becomes available about languages; there may be new
|   government or industry standards for the language that require
|   changes; and finally, new characters added to the Unicode Standard
|   will interleave with the previously-defined ones. This means that
|   collations must be carefully versioned.

... and if I remember correctly there have been cases where PostgreSQL
indexes where unusable after an upgrade because the collation had
changed.

> I've found sort more and more surprissing since I started ( with the
> electromechanical IBM card sorters, those )

I fully agree with this.

> > Which brings us back to Durumdara's example: I don't know his
> > application, so I don't know what "normal users" of his application
> > would expect, but to me those values look like two numbers separated by
> > a slash. So I would expect '23/4567' to be sorted between '18/0212' and
> > '180/2010', but of course the C collation doesn't do that:
> 
> He does not seem to want this.

He didn't mention it. We don't know yet whether he doesn't want it or
just didn't think of it yet :-).

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: psql format result as markdown tables

[Vick Khera]

How does this work for you? I use this to get tables to insert into my
wiki, which are basically the format you want. I just delete the extra
lines I don't want at the end.

vk=> SELECT * FROM (values(1,2),(3,4)) as t;
 column1 | column2
-+-
   1 |   2
   3 |   4
(2 rows)

Time: 37.888 ms
vk=> \pset border 2
Border style is 2.
vk=> SELECT * FROM (values(1,2),(3,4)) as t;
+-+-+
| column1 | column2 |
+-+-+
|   1 |   2 |
|   3 |   4 |
+-+-+
(2 rows)

For you it looks like you need to change the "+" to "|" and it will work
and delete the first and last lines. I don't know if you can change that
with some other \pset setting.

On Sat, Jan 13, 2018 at 4:50 AM, Nicolas Paris  wrote:

> Hello
>
> I wonder if someone knows how to configure psql to output results as
> markdown tables.
> Then instead of :
>
> SELECT * FROM (values(1,2),(3,4)) as t;
>  column1 | column2
> -+-
>1 |   2
>3 |   4
>
> Get the result as :
> SELECT * FROM (values(1,2),(3,4)) as t;
> | column1 | column2|
> |-||-
> |   1 |   2|
> |   3 |   4|
>
> Thanks by advance
>
>

RE: Missing WAL file after running pg_rewind

[Dylan Luong]

The content of the history file 0006.history is:

$ more 0006.history
1   2CE/8A98no recovery target specified
2   2CE/FF974EF0no recovery target specified
3   2CF/5198no recovery target specified
4   2D1/C90ACD88no recovery target specified
5   383/C0790E50no recovery target specified

Here are the last few archive file of the old timeline (old master):

0005038300BD
0005038300BE
0005038300BF
0005038300C0
0005038300C1

Here are the first few archive files of the new timeline (new master):
0005038300C0.partial
0006038300C0
0006038300C1
0006038300C2
0006038300C3

Looks like it has folked at C0. 
But why is the new slave asking for 0006038300BE on timeline during 
the restore after the pg_rewind? And not from C0?

Dylan


-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
Sent: Saturday, 13 January 2018 9:04 PM
To: Dylan Luong 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Missing WAL file after running pg_rewind

On Fri, Jan 12, 2018 at 09:44:25PM +, Dylan Luong wrote:
> The file  exist in the archive directory of the old master but it is 
> for the previous timeline, ie 5 and not 6, ie 
> 0005038300BE. Can I just rename the file to 6 timeline? Ie 
> 0006038300BE

What are the contents of the history file for this new timeline? You are 
looking at 0006.history which should be archived as well. You could do that 
assuming that WAL has forked on this segment at promotion as both segments 
would have the same contents up to the point where WAL has forked.
--
Michael

Re: Missing WAL file after running pg_rewind

[Michael Paquier]

On Mon, Jan 15, 2018 at 12:13:46AM +, Dylan Luong wrote:
> The content of the history file 0006.history is:

Please do not top-post. This breaks the logic of the thread.

> $ more 0006.history
> 1   2CE/8A98no recovery target specified
> 2   2CE/FF974EF0no recovery target specified
> 3   2CF/5198no recovery target specified
> 4   2D1/C90ACD88no recovery target specified
> 5   383/C0790E50no recovery target specified

So indeed the timeline has forked at 0005038300C0, which is
what the .partial file also means.

> Here are the last few archive file of the old timeline (old master):
> 
> 0005038300BD
> 0005038300BE
> 0005038300BF
> 0005038300C0
> 0005038300C1
> 
> Here are the first few archive files of the new timeline (new master):
> 0005038300C0.partial
> 0006038300C0
> 0006038300C1
> 0006038300C2
> 0006038300C3
> 
> Looks like it has folked at C0. 
> But why is the new slave asking for 0006038300BE on
> timeline during the restore after the pg_rewind? And not from C0?

The rewound standby needs to recover from the point of the last
checkpoint where timeline forked. So I can guess that this record is 
located in segment 0005038300BE. Now I find strange that the
standby requests for a segment on the new timeline 5, while it should
start at TLI 5. Are you sure that the standby had the means been able to
fetch segment 0005038300BE? Something looks weird from your
operational point of view with your archives..
--
Michael


signature.asc
Description: PGP signature