SV: to_timestamp function

[Gustavsson Mikael]

Thanks for fast reply! 

I'll forward the answer to my developers.

kr
Mikael Gustavsson

Från: Tom Lane [t...@sss.pgh.pa.us]
Skickat: den 20 mars 2019 17:33
Till: Gustavsson Mikael
Kopia: pgsql-general@lists.postgresql.org
Ämne: Re: to_timestamp function

Gustavsson Mikael  writes:
> So my question is, is it intentional that to_timestamp is stricter than cast 
> to timestamp?

Yes.  The point of using that function at all is to be strict about the
input format, so being strict about the field values seems to make
sense along with that.  An independent argument for it is mentioned in
the commit message (d3cd36a13):

Historically, something like to_date('2009-06-40','-MM-DD') would
return '2009-07-10' because there was no prohibition on out-of-range
month or day numbers.  This has been widely panned, and it also turns
out that Oracle throws an error in such cases.  Since these functions
are nominally Oracle-compatibility features, let's change that.

regards, tom lane

Re: Performance of ByteA: ascii vs binary

[Peter J. Holzer]

On 2019-03-20 13:20:57 +0100, Thomas Güttler wrote:
> 
> 
> Am 19.03.19 um 20:37 schrieb Peter J. Holzer:
> > On 2019-03-18 15:33:17 +0100, Thomas Güttler wrote:
> > > I did some benchmarking and in my setup there was major
> > > performance difference.
> > > 
> > > I tested a ByteA column.
> > > 
> > > If I used ascii data the tests took 52 seconds.
> > > If I used random binary data the test took 250 seconds.
> > > 
> > > binary data is (roughly) five times slower than ascii data?
> > > 
> > > Is this a know fact, or is there something wrong with my benchmark?
> > > 
> > > I used Python and psycopg2.
> > 
> > I don't see this here (Debian 9, Python 3,5, psycopg2 2.7.6, PostgreSQL
> > 9.5).
> > 
> > I modified my bench-bytea script (https://github.com/hjp/blob-bench) to
> > restrict the byte values to printable ASCII (32 .. 126). There was
> > absolutely no difference, as the attached graph shows.
> 
> Strange. I saw a big difference.
> What did you test?
> I tested inserts.

The graph with the quantiles was for selects. For inserts I made only a
scatterplot and there didn't seem to be any difference either. I'll
check the quantiles, too, but I expect to see at most a small
difference. A large difference as the one you saw would show up clearly
in a scatterplot, too.

My test ist in github (see URL above). Do see the same difference with
my test on your system?

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: Performance of ByteA: ascii vs binary

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2019-03-20 13:20:57 +0100, Thomas Güttler wrote:
>> Strange. I saw a big difference.
>> What did you test?
>> I tested inserts.

> The graph with the quantiles was for selects.

Hmm, so there are two different code paths being considered here
-- the OP is apparently talking about the speed of bytea_in,
while the rest of us have been thinking about bytea_out.

The question about whether the text representation is hex or
"escape" style still applies, though.

regards, tom lane

Re: Performance of ByteA: ascii vs binary

[Adrian Klaver]

On 3/21/19 6:49 AM, Tom Lane wrote:

"Peter J. Holzer"  writes:

On 2019-03-20 13:20:57 +0100, Thomas Güttler wrote:

Strange. I saw a big difference.
What did you test?
I tested inserts.



The graph with the quantiles was for selects.


Hmm, so there are two different code paths being considered here
-- the OP is apparently talking about the speed of bytea_in,
while the rest of us have been thinking about bytea_out.

The question about whether the text representation is hex or
"escape" style still applies, though.


Yes, it would be nice to see the test code.



regards, tom lane





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

RE: Postgres 9.6 Slave Creation

[Eric Katchan]

Hello, a little follow up...

We attempted the same method one more time with no success.  Today we used  
pg_basebackup.
Successfully created slave.

Eric

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Thomas Munro]

On Thu, Mar 21, 2019 at 5:07 PM Tom Lane  wrote:
> Thomas Munro  writes:
> > If someone out there is not enabling any of that stuff
> > because their system doesn't like threads, they can use
> > --disable-thread-safety to avoid the effects of this change.
>
> No, that's nonsense; --disable-thread-safety only affects what happens
> on the frontend side.

That's exactly what I'm talking about changing.  With the patch, BE's
LDAP library variant would also be controlled by that configure
switch, so it would always match the FE.  Almost all users would
continue to choose libldap_r.so for the FE, so they'd start getting
that in the BE too (if they didn't already due to distro-supplied
symlinks).  People using --disable-thread-safety would continue to get
libldap.so for FE and BE, as they do today.

-- 
Thomas Munro
https://enterprisedb.com

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Tom Lane]

Thomas Munro  writes:
> On Thu, Mar 21, 2019 at 5:07 PM Tom Lane  wrote:
>> Thomas Munro  writes:
>>> If someone out there is not enabling any of that stuff
>>> because their system doesn't like threads, they can use
>>> --disable-thread-safety to avoid the effects of this change.

>> No, that's nonsense; --disable-thread-safety only affects what happens
>> on the frontend side.

> That's exactly what I'm talking about changing.  With the patch, BE's
> LDAP library variant would also be controlled by that configure
> switch, so it would always match the FE.  Almost all users would
> continue to choose libldap_r.so for the FE, so they'd start getting
> that in the BE too (if they didn't already due to distro-supplied
> symlinks).  People using --disable-thread-safety would continue to get
> libldap.so for FE and BE, as they do today.

Ah, I see.  Seems reasonable.

I still wish we could confirm this fixes the reported problem before
we pull the trigger.

regards, tom lane

Materialized view breaks pg_restore

[David Wheeler]

Hi,

We’re regularly having an issue when restoring dumps of our databases like this


 [exec] CREATE DATABASE "testRestore";
 [exec] pg_restore: [archiver (db)] Error while PROCESSING TOC:
 [exec] pg_restore: [archiver (db)] Error from TOC entry 15728; 0 43798 
MATERIALIZED VIEW DATA fact_tax dbowner@smile-DEV_2019-03-22T09-32-13.338
 [exec] pg_restore: [archiver (db)] could not execute query: ERROR:  
relation "basic" does not exist
 [exec] LINE 1: SELECT chargegst from basic where uid = _account
 [exec]   ^
 [exec] QUERY:  SELECT chargegst from basic where uid = _account
 [exec] CONTEXT:  PL/pgSQL function ar.categorise_gst(integer,integer,date) 
line 7 at IF
 [exec] Command was: REFRESH MATERIALIZED VIEW cube02.fact_tax;


The issue is that there’s a mat view that refers to a plpgsql function that 
refers to a table in the public schema, but it’s not qualified. When we create 
the materialized view, and when we refresh it, the table is in the search path. 
But when restoring from a dump, it’s not.

Is this the desired behaviour? This is an issue for us because 
pg_dump/pg_restore is part of our disaster recovery process, so if we find this 
problem during restore it will mean more downtime.

PG version 9.5.14. I’m attempting to find out now if it’s an issue in more 
recent versions also.


TIA

David Wheeler
Software developer


[cid:2C4D0888-9F8B-463F-BD54-2B60A322210C]


E dwhee...@dgitsystems.com
D +61 3 9663 3554  W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.

Re: Materialized view breaks pg_restore

[Adrian Klaver]

On 3/21/19 8:15 PM, David Wheeler wrote:

Hi,

We’re regularly having an issue when restoring dumps of our databases 
like this


  [exec] CREATE DATABASE "testRestore";
  [exec] pg_restore: [archiver (db)] Error while PROCESSING TOC:
  [exec] pg_restore: [archiver (db)] Error from TOC entry 15728; 0 43798 
MATERIALIZED VIEW DATA fact_tax dbowner@smile-DEV_2019-03-22T09-32-13.338
  [exec] pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
"basic" does not exist
  [exec] LINE 1: SELECT chargegst from basic where uid = _account
  [exec]   ^
  [exec] QUERY:  SELECT chargegst from basic where uid = _account
  [exec] CONTEXT:  PL/pgSQL function 
ar.categorise_gst(integer,integer,date) line 7 at IF
  [exec] Command was: REFRESH MATERIALIZED VIEW cube02.fact_tax;


The issue is that there’s a mat view that refers to a plpgsql function 
that refers to a table in the public schema, but it’s not qualified. 
When we create the materialized view, and when we refresh it, the table 
is in the search path. But when restoring from a dump, it’s not.


Is this the desired behaviour? This is an issue for us because 
pg_dump/pg_restore is part of our disaster recovery process, so if we 
find this problem during restore it will mean more downtime.


Yes:

https://www.postgresql.org/about/news/1834/

The link in the above to the explanation is broken. The correct link is:

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d2aed664ee8271fd6c721ed0aa10168cda112ea

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5770172cb0c9df9e6ce27c507b449557e5b45124


PG version 9.5.14. I’m attempting to find out now if it’s an issue in 
more recent versions also.



TIA

*David Wheeler
**Software developer
*




E dwhee...@dgitsystems.com 
D +61 3 9663 3554  W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.





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

Column lookup in a row performance

[Павлухин Иван]

Hi PostgreSQL Community,

I am learning deeply how tuples are organized and column values are
accessed in different databases. As far as undertood postgres does not
store all column positions in a tuple (e.g. in header or footer). In
contrast MySQL InnoDB stores column lengths in a record header [1].
>From the first glance it seems that a postgres format can have a
significant performance penalty when accessing a single column which
is located after multiple variable-length columns because searching a
column value position in a row requires multiple jumps. And in InnoDB
a position of a particular column can be found right after reading a
header.

I found several related threads in pgsql-hackers archives [2,3]
describing significant performance wins in a prototype.

Does anyone know why the format is still the same? Perhaps InnoDB and
similar formats are not so good, are they?

Please respond if you have the clue!

[1] 
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-compact
[2] 
https://www.postgresql.org/message-id/flat/c58979e50702201307w64b12892uf8dfc3d8bf117ec0%40mail.gmail.com
[3] https://www.postgresql.org/message-id/flat/87irj16umm.fsf%40enterprisedb.com

-- 
Best regards,
Ivan Pavlukhin