where clauses including timstamptz and intervals
Hello all
Are these two queries exactly eqivalent? The table is partitioned on r_time,
which is a timestamptz. The explain plans are not exactly the same. The first
wants to scan a partition more than the latter.
select f.xx from f
where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
select f.xx from f
where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
Regards Niels Jespersen
Re: ERROR : invalid transaction termination : PostgreSQL v12
Try removing security definer ... It should work. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: where clauses including timstamptz and intervals
On Fri, Apr 09, 2021 at 07:24:54AM +, Niels Jespersen wrote:
> Hello all
>
> Are these two queries exactly eqivalent? The table is partitioned on
> r_time, which is a timestamptz. The explain plans are not exactly the
> same. The first wants to scan a partition more than the latter.
>
> select f.xx from f
> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
>
> select f.xx from f
> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
It depends on timezone.
For example, in my timezone:
$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'),
'2020-11-01 00:00:00+00'::timestamptz;
?column?│ timestamptz
┼
2020-11-01 02:00:00+01 │ 2020-11-01 01:00:00+01
(1 row)
Please note that there is 1 hour difference.
The reason is that somewhere in there we change time due to daylight
savings.
If I'd set timezone to UTC, suddently it's the same:
$ set timezone=UTC;
SET
$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month') =
'2020-11-01 00:00:00+00'::timestamptz;
?column?
──
t
(1 row)
As usual - time is more complicated than one could expect.
Best regards,
depesz
Who altered the database?
Dear Members! We have a server. Because of high usage we can't log DDL-s. There are too many temporary tables created by users, so it would slow down the server. A database owner changed. What can we do to get info about the change (who did it, from what IP, when it happened) in the future? As I see we can't define precisely what we want to log... All or nothing. Do you know any tricks to do it? For example: DB level trigger, or other thing? Thank you for the answer! Best regards dd
Re: Is replacing transactions with CTE a good idea?
This discussion really questioned my understanding of concurrency in PostgreSQL, thanks a lot. I gave the corresponding part of the doc some more read, and I’m now in the option that insolation level has no effect on CTEs, but please correct me if I’m wrong. If notionally all queries execute at the same time, even if they are executed in read committed, they behave like repeatable read. This should also be true for serializable, since the anomalies that isolation level tries to address won’t occur in a CTE. @Bruce The gotchas you mentions are really interesting, I have a follow up question if you don’t mind: CREATE foo(n int); CREATE bar(n int REFERENCES foo(n)); WITH t AS ( INSERT INTO foo(n) VALUES(1) ) INSERT INTO bar(n) VALUES(1); Is the CTE guaranteed to success or it’s actually unspecified? I ran it a couple times without issues, but I can’t be sure. If it’s unspecified any idea how should I correct it? > On Apr 6, 2021, at 2:41 AM, Bruce Momjian wrote: > > On Mon, Apr 5, 2021 at 02:32:36PM -0400, Dave Cramer wrote: >> On Mon, 5 Apr 2021 at 14:18, Bruce Momjian wrote: >> I think we are in agreement. My point was that WITH queries don't change the >> isolation semantics. > > My point is that when you combine individual queries in a single WITH > query, those queries run together with snaphot behavior as if they were > in a repeatable-read multi-statement transaction. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. >
Re: Is replacing transactions with CTE a good idea?
On Fri, Apr 9, 2021 at 11:05:34PM +0800, Glen Huang wrote: > This discussion really questioned my understanding of concurrency in > PostgreSQL, thanks a lot. > > I gave the corresponding part of the doc some more read, and I’m now > in the option that insolation level has no effect on CTEs, but please > correct me if I’m wrong. Yes, isolation only controls whether a new snapshot is computed _between_ queries in a multi-statement transaction. Single queries always use a single snapshot, except for maintenance commands like VACUUM. > If notionally all queries execute at the same time, even if they are > executed in read committed, they behave like repeatable read. This > should also be true for serializable, since the anomalies that > isolation level tries to address won’t occur in a CTE. You mean multiple queries in a single CTE, yes, they are like repeatable read. > @Bruce The gotchas you mentions are really interesting, I have a > follow up question if you don’t mind: > > CREATE foo(n int); CREATE bar(n int REFERENCES foo(n)); WITH t AS ( > INSERT INTO foo(n) VALUES(1) ) INSERT INTO bar(n) VALUES(1); > > Is the CTE guaranteed to success or it’s actually unspecified? I ran > it a couple times without issues, but I can’t be sure. If it’s > unspecified any idea how should I correct it? Uh, the SELECT manual page explains that non-SELECT queries in a CTE do behave unusually: The primary query and the WITH queries are all (notionally) executed at the same time. This implies that the effects of a data-modifying statement in WITH cannot be seen from other parts of the query, other than by reading its RETURNING output. If two such data-modifying statements attempt to modify the same row, the results are unspecified. and the quoted paragraph suggests that your query should not work. However, you are not referencing the foo table directly, but via referential integrity check, which I guess does work. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: where clauses including timstamptz and intervals
On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
On Fri, Apr 09, 2021 at 07:24:54AM +, Niels Jespersen wrote:
Hello all
Are these two queries exactly eqivalent? The table is partitioned on
r_time, which is a timestamptz. The explain plans are not exactly the
same. The first wants to scan a partition more than the latter.
select f.xx from f
where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
select f.xx from f
where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
It depends on timezone.
For example, in my timezone:
$ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'),
'2020-11-01 00:00:00+00'::timestamptz;
?column?│ timestamptz
┼
2020-11-01 02:00:00+01 │ 2020-11-01 01:00:00+01
(1 row)
Please note that there is 1 hour difference.
The reason is that somewhere in there we change time due to daylight
savings.
That looks like a flaw in how "month" is calculated. Whether or not October
is 744 hours (31 days x 24 hours/day) or 743 hours (subtracting the "fall
back" hour), one month from 01-Oct-2020 is still 01-Nov-2020.
--
Angular momentum makes the world go 'round.
Re: Who altered the database?
On 4/9/21 6:14 AM, Durumdara wrote: Dear Members! We have a server. Because of high usage we can't log DDL-s. There are too many temporary tables created by users, so it would slow down the server. A database owner changed. What can we do to get info about the change (who did it, from what IP, when it happened) in the future? As I see we can't define precisely what we want to log... All or nothing. Do you know any tricks to do it? For example: DB level trigger, or other thing? pg_audit? -- Angular momentum makes the world go 'round.
Re: where clauses including timstamptz and intervals
Ron writes:
> On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
>> For example, in my timezone:
>>
>> $ select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month'),
>> '2020-11-01 00:00:00+00'::timestamptz;
>> ?column?│ timestamptz
>> ┼
>> 2020-11-01 02:00:00+01 │ 2020-11-01 01:00:00+01
>> (1 row)
>>
>> Please note that there is 1 hour difference.
>> The reason is that somewhere in there we change time due to daylight
>> savings.
> That looks like a flaw in how "month" is calculated. Whether or not October
> is 744 hours (31 days x 24 hours/day) or 743 hours (subtracting the "fall
> back" hour), one month from 01-Oct-2020 is still 01-Nov-2020.
No, it's more likely because the calculation was done in some time
zone other than UTC, which (somewhat confusingly) is what the starting
value was specified in. Assuming Hubert meant EU rules, we have
regression=# set timezone = 'Europe/Paris';
SET
regression=# select '2020-10-01 00:00:00+00'::timestamptz;
timestamptz
2020-10-01 02:00:00+02
(1 row)
regression=# select '2020-10-01 00:00:00+00'::timestamptz + interval '1 month';
?column?
2020-11-01 02:00:00+01
(1 row)
which looks to me like the addition did exactly what it's supposed to,
ie same local time 1 month later.
regards, tom lane
Re: Who altered the database?
On Fri, 9 Apr 2021, 4:44 pm Durumdara, wrote: > Dear Members! > > We have a server. > Because of high usage we can't log DDL-s. There are too many temporary > tables created by users, so it would slow down the server. > A database owner changed. > > What can we do to get info about the change (who did it, from what IP, > when it happened) in the future? > > As I see we can't define precisely what we want to log... All or nothing. > > Do you know any tricks to do it? > > For example: DB level trigger, or other thing? > > Thank you for the answer! > > Best regards > > dd > > > You can use different options in log_line_prefix parameter in postgresql.conf You can also log connection and disconnection, each has separate parameter in configuration file. Regards, Ganesh Korde. >
Re: where clauses including timstamptz and intervals
Ron:
On Fri, Apr 9, 2021 at 5:53 PM Ron wrote:
> >> Are these two queries exactly eqivalent? The table is partitioned on
> >> r_time, which is a timestamptz. The explain plans are not exactly the
> >> same. The first wants to scan a partition more than the latter.
> >>
> >> select f.xx from f
> >> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> >>and f.r_time < ('2020-10-01 00:00:00+00'::timestamptz + interval '1
> >> month');
> >>
> >> select f.xx from f
> >> where f.r_time >= '2020-10-01 00:00:00+00'::timestamptz
> >>and f.r_time < ('2020-11-01 00:00:00+00'::timestamptz);
> > It depends on timezone.
> That looks like a flaw in how "month" is calculated. Whether or not October
> is 744 hours (31 days x 24 hours/day) or 743 hours (subtracting the "fall
> back" hour), one month from 01-Oct-2020 is still 01-Nov-2020.
I've encountered these thing before. What is probably happening is
that your CLIENT TIMEZONE is DST aware.
When you add the interval 1st PG takes the tstz, and converts it to
internal. No problem here, it is a gmt one. The it parses the
interval, again no problem, intervals do not depend on DST.
But then you ask to add them, and timezone + interval is time zone
sensitive. And, counter intuitively to many, tstz does not store
timezone, it marks an instant in time, it just signals that
manipulation to the timezone must be TZ aware, so it does it in your
current timezone.
You probably have ( and if not, you should consider having ) your
partitions defined on GMT, so the time jump at the starty of the month
make it take one more ( are you in Britain/Portugal/Canary islands,
which are +0 in winter +1 in summer, IIRC ? )
If you want to scan exact partitions by specifying arithmetic limits,
set timezone to whichever tz your partitions are partitioned with, or
just name the partitions. If you just want to scan a date range (
thats's what I do a lot ), just trust pg to do the right thing.
Remember, specifying +00 in the input does not make the arithmetic
work in that tz, it works in your tz. I think you could achieve the
desired result by temporarily switching to timestamp without,
something like
('2020-10-01 00:00:00+00'::timestamptz at timezone 'GMT' + interval '1
month') at timezone 'GMT'
IIRC this converts to tstz to ts, then adds there, then converts back
to tstz, my results are:
cdrs=> show timezone;
TimeZone
--
UTC
(1 row)
cdrs=> select ('2020-10-01 00:00:00+00'::timestamptz + interval '1 month');
?column?
2020-11-01 00:00:00+00
(1 row)
cdrs=> set timezone to 'Europe/Madrid';
SET
cdrs=> select ('2020-10-01 00:00:00+00'::timestamptz), ('2020-10-01
00:00:00+00'::timestamptz + interval '1 month');
timestamptz |?column?
+
2020-10-01 02:00:00+02 | 2020-11-01 02:00:00+01
(1 row)
* diferent UTC hours, same local. (02 but +02 and +01)
cdrs=> select ('2020-10-01 00:00:00+00'::timestamptz), ('2020-10-01
00:00:00+00'::timestamptz at time zone 'utc'+ interval '1 month') at
time zone 'utc';
timestamptz |timezone
+
2020-10-01 02:00:00+02 | 2020-11-01 01:00:00+01
(1 row)
* Now same UTC hours, different local. ( 02 + 02, 01 + 01 )
If I had monthly partitions rolling at 00 utc, 1st one will nibble the
first hour of an extra one.
TSTZ is hard.
Francisco Olarte.
SV: where clauses including timstamptz and intervals
>-Oprindelig meddelelse-
>Fra: Tom Lane
>Emne: Re: where clauses including timstamptz and intervals
>
>Ron writes:
>> On 4/9/21 5:24 AM, hubert depesz lubaczewski wrote:
>>> For example, in my timezone:
>>>
>>> (1 row)
>>>
>>> Please note that there is 1 hour difference.
>>> The reason is that somewhere in there we change time due to daylight
>>> savings.
>
>> That looks like a flaw in how "month" is calculated. Whether or not
>> October is 744 hours (31 days x 24 hours/day) or 743 hours
>> (subtracting the "fall back" hour), one month from 01-Oct-2020 is still
>> 01-Nov-2020.
>
>No, it's more likely because the calculation was done in some time zone other
>than UTC, which (somewhat confusingly) is what the starting value was
>specified in. Assuming Hubert meant EU rules, we have
>
>regression=# set timezone = 'Europe/Paris'; SET regression=# select
>'2020-10-01 00:00:00+00'::timestamptz;
> timestamptz
>
> 2020-10-01 02:00:00+02
>(1 row)
>
>regression=# select '2020-10-01 00:00:00+00'::timestamptz + interval '1 month';
>?column?
>
> 2020-11-01 02:00:00+01
>(1 row)
>
>which looks to me like the addition did exactly what it's supposed to, ie same
>local time 1 month later.
>
> regards, tom lane
>
I hope this sums it up.
select ('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1
month') as c1,
'2020-11-01 00:00:00+00'::timestamptz at time zone 'utc'
as c2,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'utc' + interval '1
month') =
('2020-11-01 00:00:00+00'::timestamptz at time zone 'utc')
as c3,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels' +
interval '1 month') as c4,
'2020-11-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels'
as c5,
('2020-10-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels' +
interval '1 month') =
('2020-11-01 00:00:00+00'::timestamptz at time zone 'Europe/Brussels')
as c6;
returns
c1 | c2 | c3 | c4 |
c5 | c6
-+-++-+-+
2020-11-01 00:00:00 | 2020-11-01 00:00:00 | t | 2020-11-01 02:00:00 |
2020-11-01 01:00:00 | f
(1 row)
The answer to my original problem behind my original question which is how to
query using utc timing in order to hit exactly the (utc created) partition,
without changing the session timezone, would be like this:
select f.* from f
where f.r_time at time zone 'utc' >= '2020-10-01 00:00:00+00'::timestamptz at
time zone 'utc'
and f.r_time at time zone 'utc' < ('2020-10-01 00:00:00+00'::timestamptz at
time zone 'utc' + interval '1 month');
I think maybe it's better to just change the session timezone when this need
arises.
Thank you all for feedback.
Regards Niels
issue during installation of postgresql binary zip
Hi, I am trying to install a binary zip into my different machines using a batch file, it works on some machines but giving me this error on other machines. *Windows Event log:* Faulting application name: initdb.exe, version: 13.0.1.20314, time stamp: 0x5faa5f0f Faulting module name: ucrtbase.dll, version: 10.0.14393.3659, time stamp: 0x5e9140a1 Exception code: 0xc409 Fault offset: 0x0006c9e8 Faulting process id: 0x579c Faulting application start time: 0x01d72d5bc0a912a0 Faulting application path: C:\pgsql\bin\initdb.exe Faulting module path: C:\WINDOWS\System32\ucrtbase.dll Report Id: x Faulting package full name: Faulting package-relative application ID: any help is highly appreciated. I am using this script @ECHO OFF @SET PATH="%~dp0\bin";%PATH% @SET PGDATA=%~dp0\datad @SET PGDATABASE=postgres @SET PGUSER=postgres @SET PGPORT=5432 @SET PGLOCALEDIR=%~dp0\share\locale ECHO. IF EXIST datad ( ECHO This instance already initialized. ECHO. ) ELSE ( ECHO First run, wait for initializing. "%~dp0\bin\initdb" -U postgres -A trust ECHO. ) "%~dp0\bin\pg_ctl" -D "%~dp0/datad" -l logfile start "%~dp0\bin\pg_ctl" register -N "postgSQLSer" -D C:/pgsql/datad"
Re: Multiple LDAP Servers for ldap Authentication
On Thu Dec, 20, 2018 at 9:17 PM Kumar, Virendra wrote: > I figured it out, this is how it works: > -- > hostall all0.0.0.0/0 > ldap ldapserver=server1.com ldapserver=server2.com ldapprefix=PROD01\ > > So documentation need some update. > Just FYI I tried out this method on my setup, and it did not work. Postgres (I tried on v. 10 and v. 12) will always pick the last "ldapserver=" tag that it parses. Avaro's format (ldapserver="server1 server2") works for me. To be clear: # does not work: host all all 0.0.0.0\/0 ldap ldapserver=ldap-service1 ldapserver=ldap-service2 ldaptls=1 ldapprefix="cn=" ldapsuffix=", dc=example, dc=org\" ldapport=389 # this works: host all all 0.0.0.0/0 ldap ldapserver="ldap-service1 ldap-service2" ldaptls=1 ldapprefix="cn=" ldapsuffix=", dc=example, dc=org" ldapport=389 For anyone who comes across this in the future, I have also compiled as short YouTube video to demonstrate the behavior of the two formats: https://youtu.be/kjlwwfHdpWg --Richard > Regards, > Virendra > > -Original Message- > From: Alvaro Herrera [mailto:alvhe...@2ndquadrant.com] > Sent: Thursday, December 20, 2018 3:25 PM > To: Kumar, Virendra > Cc: pgsql-general@lists.postgresql.org > Subject: Re: Multiple LDAP Servers for ldap Authentication > > On 2018-Dec-20, Kumar, Virendra wrote: > > > Comman separated doesn't work as well. > > Please separate by a comma and a space, not just a comma. My reading of > the OpenLDAP source code, and some quick experiments comparing failure > patterns, suggest that that exact combination may work. (OpenLDAP is > not exactly well commented.) I think one problem you may or may not hit > is the PostgreSQL authentication timeout expiring sooner than OpenLDAP > is willing to try the second server. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > > This message is intended only for the use of the addressee and may contain > information that is PRIVILEGED AND CONFIDENTIAL. > > If you are not the intended recipient, you are hereby notified that any > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please erase all copies of the > message > and its attachments and notify the sender immediately. Thank you. > > > >
