Re: timestamp arithmetics in C function
Hi Adrian,
thanks for the encouragement. If anyone can comment on the code, I'd
still be very interested.
Cheers,
Lutz
On Thursday, 06.09.2018 06:27:14 Adrian Klaver wrote:
> On 09/03/2018 09:11 AM, Lutz Gehlen wrote:
> > Hello all,
> >
> > unfortunately, I have so far not received a reply to my question
> > below. I am well aware that no one has an obligation to reply; I
> > was just wondering whether I phrased my question badly or
> > whether there is anything else I could do to improve it.
>
> Caveat, I am not a C programmer so I cannot comment on the
> correctness of the code. The question and it's phrasing look
> alright to me though. Your most recent post landed on a
> holiday(Labor Day) here in the States and therefore may have got
> lost in the return to work on Tuesday. Hopefully someone more
> knowledgeable then I will see this and comment on the C portion
> of your post.
>
> > Thanks for your help and best wishes,
> > Lutz
> >
> > On Friday, 10.08.2018 09:05:40 Lutz Gehlen wrote:
> >> Hello all,
> >>
> >> I am trying to implement a C function that accepts a date
> >> ("date"
> >> in the sense of a type of information, not a postgres datatype)
> >> as parameter (among others) and returns a certain point in
> >> time.
> >> (The background is to calculate the time of dawn and dusk at
> >> the
> >> given date.) Ideally, I would like to accept a timestamp value
> >> and return another timestamp as result. I have implemented the
> >> function, but I would like to ask advice on whether my
> >> implementation is the recommended way to achieve this.
> >>
> >> To get started - since this is my first attempt at a C function
> >> in postgres - I implemented a function that accepts the date
> >> as three separate int32 values for year, month, and day and
> >> returns the time of dawn as a float8 for the minutes since
> >> midnight (this is what the implemented algorithm internally
> >> returns, anyway):
> >>
> >>
> >> PG_FUNCTION_INFO_V1(dawn_utc);
> >>
> >> Datum dawn_utc(PG_FUNCTION_ARGS) {
> >>
> >>float8 lat = PG_GETARG_FLOAT8(0);
> >>float8 lon = PG_GETARG_FLOAT8(1);
> >>int32 year = PG_GETARG_INT32(2);
> >>int32 month= PG_GETARG_INT32(3);
> >>int32 day = PG_GETARG_INT32(4);
> >>float8 solar_depression = PG_GETARG_FLOAT8(5);
> >>
> >>// postgres-independent computation goes here
> >>float8 dawn_utc = calc_dawn_utc
> >>
> >> (lat, lon, year, month, day, solar_depression);
> >>
> >>PG_RETURN_FLOAT8(dawn_utc);
> >>
> >> }
> >>
> >>
> >> This works fine. However, it would be more convenient if the
> >> function would accept a date or timestamp value and return a
> >> timestamp. So I modified the first part of the function like
> >> this, based on code snippets I found in the postgres source
> >> code:
> >>
> >>
> >> PG_FUNCTION_INFO_V1(dawn_utc);
> >>
> >> Datum dawn_utc(PG_FUNCTION_ARGS) {
> >>
> >>float8 lat = PG_GETARG_FLOAT8(0);
> >>float8 lon = PG_GETARG_FLOAT8(1);
> >>Timestamp timestamp = PG_GETARG_TIMESTAMP(2);
> >>float8 solar_depression = PG_GETARG_FLOAT8(3);
> >>
> >>struct pg_tm tt;
> >>struct pg_tm *tm = &tt;
> >>fsec_t fsec;
> >>
> >>if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) !=
> >>0)
> >>
> >> ereport(ERROR,
> >>
> >> (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
> >>
> >> errmsg("timestamp out of range")));
> >>
> >>// postgres-independent computation goes here
> >>float8 dawn_utc = calc_dawn_utc
> >>
> >> (lat, lon, tm->tm_year, tm->tm_mon, tm->tm_mday,
> >>
> >> solar_depression;
> >>
> >>
> >> For the second part of the function, I now have to add the
> >> calculated number of minutes to the date portion of the
> >> timestamp
> >> variable. One has to be aware that depending on the geographic
> >> location dawn_utc can possibly be negative or larger than 1440
> >> (i.e. 24h). I am not sure whether I should construct an
> >> interval
> >> value from the number of minutes and add that to the timestamp.
> >> I
> >> have not figured out how to do this, but decided to calculate a
> >> new timestamp in a more fundamental way:
> >>
> >>
> >>
> >>tm->tm_sec = 0;
> >>tm->tm_min = 0;
> >>tm->tm_hour = 0;
> >>Timestamp result;
> >>if (tm2timestamp(tm, 0, NULL, &result) != 0)
> >>
> >> ereport(ERROR,
> >>
> >> (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
> >>
> >> errmsg("timestamp out of range")));
> >>
> >> #ifdef HAVE_INT64_TIMESTAMP
> >>
> >>/* timestamp is microseconds since 2000 */
> >>result += dawn_utc * USECS_PER_MINUTE;
> >>
> >> #else
> >>
> >>/* timestamp is seconds since 2000 */
> >>result += dawn_utc * (double) SECS_PER_MINUTE;
> >>
> >> #endif
> >>
> >
Re: survey: pg_stat_statements total_time and entry deallocation
Here is some data from our production. I hope it can be of any use to you.
- System 1:
hiper=> select
bucket
,count(*) entries
,max(calls) max_calls
,round(sum(total_time)) total_time
,round((100*sum(total_time)/avg(total_total_time))::numeric,2) pct_time
,round(sum(rows)) "rows"
,round((100*sum(rows)/avg(total_rows))::numeric,2) pct_rows
from
(
select
ntile(20) over (order by calls) bucket
,calls
,total_time
,sum(total_time) over () total_total_time
,rows
,sum(rows) over () total_rows
from pg_stat_statements
) stmts
group by rollup(bucket) order by bucket;
bucket | entries | max_calls | total_time | pct_time | rows | pct_rows
+-+---++--++--
1 | 245 | 71 | 4745479 | 0.38 | 640677 | 0.04
2 | 245 | 96 | 22151762 | 1.76 | 236827 | 0.01
3 | 245 | 122 | 9028387 | 0.72 | 297861 | 0.02
4 | 244 | 167 | 4711705 | 0.38 | 328928 | 0.02
5 | 244 | 228 | 9490670 | 0.76 | 337712 | 0.02
6 | 244 | 305 | 7296024 | 0.58 | 273740 | 0.02
7 | 244 | 394 | 35828651 | 2.85 | 1140064 | 0.07
8 | 244 | 540 | 34180388 | 2.72 | 1313171 | 0.08
9 | 244 | 711 | 29748121 | 2.37 | 865894 | 0.05
10 | 244 | 989 | 12864432 | 1.02 | 1665529 | 0.10
11 | 244 | 1507 | 4009346 | 0.32 | 1295032 | 0.08
12 | 244 | 2511 | 13444734 | 1.07 | 4711699 | 0.30
13 | 244 | 4567 | 401096681 | 31.94 | 3386595 | 0.21
14 | 244 | 8086 | 4750899 | 0.38 | 8236002 | 0.52
15 | 244 | 13356 | 19875345 | 1.58 | 6040996 | 0.38
16 | 244 | 22454 | 23361859 | 1.86 | 16906926 | 1.06
17 | 244 | 59660 | 68633113 | 5.46 | 40170089 | 2.52
18 | 244 | 141667 | 59768727 | 4.76 | 76054887 | 4.77
19 | 244 | 431946 | 330488976 | 26.31 | 213238961 | 13.38
20 | 244 | 170978486 | 160486607 | 12.78 | 1216933189 | 76.34
| 4883 | 170978486 | 1255961906 | 100.00 | 1594074779 | 100.00
(21 rows)
hiper=> select current_setting('pg_stat_statements.max');
current_setting
-
5000
(1 row)
Re: PG8.3->10 migration data differences
Hello,
We would like to migrate from 8.3 to 10 version. We've hundreds databases
with different structures. That reason we're working on an automatic data
comparing process.
I've found the following storage settings:
- pg83: Date/time type storage: 64-bit integers
- pg10: Date/time type storage: 64-bit integers
When I running the following select from psql (Select '09/10/18
07:10:25.110'::timestamp;)
The results are same:
-pg83: 2018-09-10 07:10:25.11
-pg10: 2018-09-10 07:10:25.11
When I select it from a table (Select v.entry_timestamp from t_vis v):
The results are different:
- pg83: 2015-08-28 21:25:07.70
- pg10: 2015-08-28 21:25:07.7
The field type:
entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
Our difference every time the last 0 values. The time values are same, but
our field based data comparing mechanism every time mark it as error.
thx,
Csaba
2018-09-10 16:21 GMT+02:00 Adrian Klaver :
> On 9/10/18 6:43 AM, Csaba Ragasits wrote:
>
>> I think I found the solution.
>>
>> When I set this parameter on the pg10 client connection, the pg10 REAL
>> value format same as the pg83 value:
>>
>> SET extra_float_digits = 0;
>>
>> Interesting, because the default value is 0 in the postgresql.conf:
>> #extra_float_digits = 0 # min -15, max 3
>>
>> Do you have any ideas how can I format the miliseconds too?
>>
>
> select version();
>version
>
>
> PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
> 4.8.5, 64-bit
>
> test=# select '09/10/18 07:10:25.100'::timestamp;
>timestamp
> ---
> 2018-09-10 07:10:25.1
> (1 row)
>
> test=# select '09/10/18 07:10:25.111'::timestamp;
> timestamp
> -
> 2018-09-10 07:10:25.111
>
> Milliseconds will display if they are significant.
>
>
> If you want to format the output:
>
> test=# select to_char('09/10/18 07:10:25.100'::timestamp, 'MM/DD/YY HH:MI:
> SS.MS');
> to_char
> ---
> 09/10/18 07:10:25.100
> (1 row)
>
>
>
>> Thx,
>> Csaba
>>
>> 2018-09-10 12:00 GMT+02:00 Peter J. Holzer > hjp-pg...@hjp.at>>:
>>
>> On 2018-09-10 11:57:42 +0300, Achilleas Mantzios wrote:
>> > On 10/09/2018 11:22, Csaba Ragasits wrote:
>> > Hello,
>> > > We would like to migrate our old databases to new
>> postgres server,
>> > with the simple backup-restore process. The process run fine
>> > without errors but when we're checking the source and the
>> > migrated datas as field level, we found the following
>> differences:
>> [...]
>> > > Field type: REAL
>> > pg93: 2.2
>> > pg10: 2.2005
>> > > > Those have to do with rounding. Precision for real is 6
>> decimal
>> > digits. Your difference is on the 8-th digit.
>>
>> Nitpick: Precision is 24 binary digits which is *about* 6 decimal
>> digits. The number which is actually stored (on both pg93 and pg10) is
>> actually 2.200476837158203125 (1.00011001100110011001101 * 2^1 in
>> binary). Apparently Pg9.3 rounds this to "2.2" on output while Pg10
>> prints the more precise (but still not exact) "2.2005".
>>
>> (I would argue that the Pg9.3 output is better, since it represents
>> the
>> same value in fewer digits, but always printing the minimum number of
>> digits necessary is surprisingly difficult.)
>>
>> 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
Re: PG8.3->10 migration data differences
Then fix your field-based data comparing mechanism. On 09/11/2018 03:41 AM, Csaba Ragasits wrote: Hello, We would like to migrate from 8.3 to 10 version. We've hundreds databases with different structures. That reason we're working on an automatic data comparing process. I've found the following storage settings: - pg83: Date/time type storage: 64-bit integers - pg10: Date/time type storage: 64-bit integers When I running the following select from psql (Select '09/10/18 07:10:25.110'::timestamp;) The results are same: -pg83: 2018-09-10 07:10:25.11 -pg10: 2018-09-10 07:10:25.11 When I select it from a table (Select v.entry_timestamp from t_vis v): The results are different: - pg83: 2015-08-28 21:25:07.70 - pg10: 2015-08-28 21:25:07.7 The field type: entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL, Our difference every time the last 0 values. The time values are same, but our field based data comparing mechanism every time mark it as error. thx, Csaba -- Angular momentum makes the world go 'round.
Why order by column not using index with distinct keyword in select clause?
I have define a simple B Tree index on column *country* for users table. I
don’t understand why the order by column not using the index scan when using
*distinct* keyword in the select clause. Can anyone explain what is happening
here?
aruprakshit=# \d users;
Table "public.users"
Column | Type | Collation | Nullable |
Default
+---+---+--+---
city | character varying | | |
last_name | character varying(50) | | |
country| character varying(50) | | |
sequence | integer | | |
first_name | character varying(50) | | |
state | character varying(50) | | |
email | character varying | | |
id | smallint | | not null |
nextval('users_id_seq'::regclass)
Indexes:
"users_pk" PRIMARY KEY, btree (id)
aruprakshit=# explain analyze select distinct country from users order by
country asc;
QUERY PLAN
Sort (cost=283.19..283.85 rows=263 width=11) (actual time=10.525..10.555
rows=263 loops=1)
Sort Key: country
Sort Method: quicksort Memory: 38kB
-> HashAggregate (cost=269.99..272.62 rows=263 width=11) (actual
time=8.469..8.521 rows=263 loops=1)
Group Key: country
-> Seq Scan on users (cost=0.00..244.99 rows= width=11) (actual
time=0.022..3.428 rows= loops=1)
Planning time: 0.358 ms
Execution time: 10.634 ms
(8 rows)
aruprakshit=# explain analyze select country from users order by country asc;
QUERY PLAN
-
Index Only Scan using country on users (cost=0.29..886.27 rows= width=11)
(actual time=0.083..7.581 rows= loops=1)
Heap Fetches:
Planning time: 0.118 ms
Execution time: 8.332 ms
(4 rows)
aruprakshit=# explain analyze select * from users order by country asc;
QUERY PLAN
Index Scan using country on users (cost=0.29..886.27 rows= width=73)
(actual time=0.015..8.432 rows= loops=1)
Planning time: 0.213 ms
Execution time: 9.086 ms
(3 rows)
aruprakshit=#
Thanks,
Arup Rakshit
a...@zeit.io
Re: PG8.3->10 migration data differences
On 9/11/18 1:41 AM, Csaba Ragasits wrote: Hello, We would like to migrate from 8.3 to 10 version. We've hundreds databases with different structures. That reason we're working on an automatic data comparing process. I've found the following storage settings: - pg83: Date/time type storage: 64-bit integers - pg10: Date/time type storage: 64-bit integers When I running the following select from psql (Select '09/10/18 07:10:25.110'::timestamp;) The results are same: -pg83: 2018-09-10 07:10:25.11 -pg10: 2018-09-10 07:10:25.11 When I select it from a table (Select v.entry_timestamp from t_vis v): The results are different: - pg83: 2015-08-28 21:25:07.70 - pg10: 2015-08-28 21:25:07.7 Actually the result is the same: select '2015-08-28 21:25:07.70'::timestamp = '2015-08-28 21:25:07.7'::timestamp; ?column? -- t The formatting is different. The field type: entry_timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL, Our difference every time the last 0 values. The time values are same, but our field based data comparing mechanism every time mark it as error. As Ron stated it should not. thx, Csaba -- Adrian Klaver adrian.kla...@aklaver.com
Re: Why order by column not using index with distinct keyword in select clause?
Arup Rakshit writes: > I have define a simple B Tree index on column *country* for users table. I > don’t understand why the order by column not using the index scan when using > *distinct* keyword in the select clause. Can anyone explain what is happening > here? Doesn't look complicated to me: the cost of the hashagg+sort plan is 283.85, while the cost of the indexscan plans is 886.27, and adding a Uniq node on top of them would have been even more. The critical point here seems to be that the hashagg node is able to get rid of so many rows that the cost of sorting what remains is low. Also notable is that the IOS isn't giving you any advantage over a plain indexscan, which apparently is because none of the table's pages are marked all-visible. If the table is static then a VACUUM would help that. regards, tom lane
Re: Why order by column not using index with distinct keyword in select clause?
On Tue, 11 Sep 2018 at 13:56, Arup Rakshit wrote: > I have define a simple B Tree index on column *country* for users table. I > don’t understand why the order by column not using the index scan when > using *distinct* keyword in the select clause. Can anyone explain what is > happening here? > Bear in mind that index skip scans aren't yet implemented, which (unless I've missed the point) seems to be what you're expecting to help here. https://wiki.postgresql.org/wiki/Loose_indexscan Geoff
Re: scram-sha-256 authentication broken in FIPS mode
On 11/09/2018 05:02, Michael Paquier wrote: > Hence, intrinsically, we are in contradiction with the upstream docs. I > have worked on the problem with the patch, which works down to OpenSSL > 0.9.8, and should fix your issue. This is based on what you sent > previously, except that I was not able to apply what was sent, so I > reworked the whole. Alessandro, does this fix your problems? I would > like to apply that down to v10 where SCRAM has been introduced. I recommend letting this bake in the master branch for a while. There are a lot weirdly patched and alternative OpenSSL versions out there that defy any documentation. Of course, we should also see if this actually fixes the reported problem. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: scram-sha-256 authentication broken in FIPS mode
On Tue, Sep 11, 2018 at 04:32:27PM +0200, Peter Eisentraut wrote: > I recommend letting this bake in the master branch for a while. There > are a lot weirdly patched and alternative OpenSSL versions out there > that defy any documentation. Good point. Such things have bitten in the past. Okay, then let's do something about sha2_openssl.c only on HEAD for now then, which I am fine to finish wrapping. > Of course, we should also see if this actually fixes the reported problem. It seems to me that addressing FIPS concerns on Windows and getting our hashing functions plugged with OpenSSL correctly are two separate issues. The second one also says that we are in the grey based on OpenSSL docs, which worryies me. And EVP_DigestInit is used in pgcrypto for ages, where I don't recall seeing reports about that. -- Michael signature.asc Description: PGP signature
Table cannot be partiotioned using domain in argument
Hi,
This query works fine:
create table t1
(d date)
PARTITION BY RANGE (d);
CREATE TABLE t1_p1 PARTITION OF t1 FOR VALUES FROM ('2000-01-01') TO
('2019-01-01');
Same query, but now (using domain):
CREATE DOMAIN ddate
AS date;
create table t1
(d ddate)
PARTITION BY RANGE (d);
CREATE TABLE t1_p1 PARTITION OF t1 FOR VALUES FROM ('2000-01-01') TO
('2019-01-01');
The follow error occur:
SQL Error [42804]: ERROR: specified value cannot be cast to type ddate for
column "d"
Detalhe: The cast requires a non-immutable conversion.
Dica: Try putting the literal value in single quotes.
Posição: 55
I cannot use domain in this case?
Thanks in advance!
--
Att
Márcio
Re: Table cannot be partiotioned using domain in argument
=?iso-8859-1?Q?M=E1rcio_Ant=F4nio_Sepp?= writes: > The follow error occur: > SQL Error [42804]: ERROR: specified value cannot be cast to type ddate for > column "d" > Detalhe: The cast requires a non-immutable conversion. > I cannot use domain in this case? Nope. The problem is suggested, if not exactly clearly explained, by the error message: casting a literal to ddate isn't a guaranteed fixed process. For example, suppose you created this table and then did alter domain ddate add check (value > '2020-01-01'); thereby rendering the partition bound values illegal for the domain. What would you expect to happen then? We might at some point work out plausible semantics for this situation, but it hasn't been done yet. regards, tom lane
Re: Table cannot be partiotioned using domain in argument
Hi and thanks for answer, Nope. The problem is suggested, if not exactly clearly explained, by the error message: casting a literal to ddate isn't a guaranteed fixed process. Wasn't clear enought to me.For example, suppose you created this table and then did alter domain ddate add check (value > '2020-01-01'); thereby rendering the partition bound values illegal for the domain. What would you expect to happen then?Not sure. Maybe check constraint error when insert/update a row?!?! I think this should happen before new record to be routed to the correct partition (in this case there is no partition created to this interval). We might at some point work out plausible semantics for this situation, but it hasn't been done yet. regards, tom lane Ok to me. I'll use date instead of domain.Thank you so much Tom.
