Re: Overlapping timestamptz ranges with priority
> On 28 Jun 2021, at 0:41, Ray O'Donnell wrote:
>
> Hi all,
>
(…)
> create table bookings (
>booking_id bigint not null,
>booking_time tstzrange not null,
>
>constraint bookings_pk primary key (booking_id)
> );
>
> insert into bookings (booking_id, booking_time) values
> (1, tstzrange('2021-06-20 12:00+01', '2021-06-20 14:00+01', '[)')),
> (2, tstzrange('2021-06-20 13:00+01', '2021-06-20 16:00+01', '[)'));
>
>
> And what I'd like to be able to do is pull out the following:
>
>
> booking_id |slot_time
> +-
> 1 | ["2021-06-20 12:00:00+01","2021-06-20 14:00:00+01")
> 2 | ["2021-06-20 14:00:00+01","2021-06-20 16:00:00+01")
You could probably achieve this by using window function lag() over (order by
booking_id), in combination with a case statement when the range from the
previous row overlaps the current range.
That would only solve the case for immediately subsequent rows though, if you
have multiple rows overlapping you will need to track the first range in that
list.
Another possible route is a recursive CTE, with a similar approach.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
pg_restore disable-triggers current session
Hi, I'm not sure if the disable-triggers option of data-only restore disable the triggers only in the current session or are disabled to everyone who is working in the database at that moment. Thanks. Dolors
Re: Overlapping timestamptz ranges with priority
On 28/06/2021 00:52, Adrian Klaver wrote: On 6/27/21 3:41 PM, Ray O'Donnell wrote: Here's a slightly simplified example: create table bookings ( booking_id bigint not null, booking_time tstzrange not null, constraint bookings_pk primary key (booking_id) ); It seems to me this is missing some reference to what is being booked e.g. room number. Yes, indeed - I left out everything except what was immediately relevant to my problem. The real table is actually for booking aircraft - it's for the local flying club of which I'm a member - so there are columns for aircraft registration, member details, etc. Ray. -- Raymond O'Donnell // Galway // Ireland r...@rodonnell.ie
schema prefixes in default values (was RE: removing "serial" from table definitions).
Marc Mamin writes:
> > Yes, I undersand that serial is just a hint at table creation time, but is
> > there a place in catalog where we can see if the table was created using
> > 'serial' ?
>
> No. Where the docs say "these are equivalent", they mean that very literally.
>
> > The difference we see between the source and target database is that a
> > schema prefix is displayed with the sequence on one side, and not on the
> > other..
>
> This likely has to do with the search_path settings being different in the
> sessions inspecting the two DBs. I do not think it is related to serial-ness
> at all, it's just the normal behavior of regclass_out for the OID constant
> that's the argument of nextval().
>
> regards, tom lane
Hello,
it seems that our problem had nothing to do with serial, but with the way
schema prefixes are handled in column default values.
pg_attrdef.adsrc:
filled when the defaut value is defined. contains a schema prefix only when
required at this creation time. Is constant afterwards.
pg_get_expr(adbin, adrelid)
the returned expession is dynamic: the schema prefix is returned only when
the sequence schema is not part of the current search_path.
This behavior is understandable but it make it uncomfortable to compare table
definitions between different sources.
Moreover a pg_dump->restore might in some cases modify the value of
pg_attrdef.adsrc
best regards,
Marc Mamin
as test:
set search_path='admin';
create table foo1 (n1 serial);
set search_path='oms';
create table admin.foo2 (n2 serial);
select a.attname, ad.adsrc, pg_get_expr(adbin, adrelid)
FROM pg_attribute a
JOIN pg_attrdef ad ON (a.attnum=ad.adnum and a.attrelid=ad.adrelid)
WHERE a.attrelid IN (Select oid from pg_class where relname
in('foo1','foo2'));
n1 nextval('foo1_n1_seq'::regclass)
nextval('admin.foo1_n1_seq'::regclass)
n2 nextval('admin.foo2_n2_seq'::regclass)
nextval('admin.foo2_n2_seq'::regclass)
set search_path='admin';
select a.attname, ad.adsrc, pg_get_expr(adbin, adrelid)
FROM pg_attribute a
JOIN pg_attrdef ad ON (a.attnum=ad.adnum and a.attrelid=ad.adrelid)
WHERE a.attrelid IN (Select oid from pg_class where relname
in('foo1','foo2'));
n1 nextval('foo1_n1_seq'::regclass)
nextval('foo1_n1_seq'::regclass)
n2 nextval('admin.foo2_n2_seq'::regclass)
nextval('foo2_n2_seq'::regclass)
Memory activities to monitor in statistics collector?
Dear community I would like to ask a question regarding system monitoring. I am sorry if this question was asked before. My postgresql is running on Azure database for Postgresql (ver 12) which runs on cloud platform. My goal is to observe memory statistics to see if there are any memory suspicious statistics that might be the reason for query OOM . https://pganalyze.com/docs/log-insights/server/S5 The guide Microsoft has is too general to detect query OOM. https://azure.microsoft.com/ja-jp/blog/best-practices-for-alerting-on-metrics-with-azure-database-for-postgresql-monitoring/ Thus I was thinking if there are any statistics I can monitor. "/proc/*/smaps" seems to be one option but it's not a choice for me. https://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/ Would there be any statistics or any combination of stat items which can lead to query OOM ? https://www.postgresql.org/docs/12/monitoring-stats.html Best Regards, Yu Watanabe -- Yu Watanabe linkedin: www.linkedin.com/in/yuwatanabe1/ twitter: twitter.com/yuwtennis
WARNING: skipping "pg_database" --- only superuser can analyze it
Hi, Good old "analyze;" will run smoothly on 11.5... But on 12.6 will skip and warn on any pg_catalog/information_schema table. Pg_catalog is on Usage only on both databases (11.5 and 12.6). Questions: 1. Has the behavior changed in 12.6? 2.How can I analyze on public tables (No syntax like analyze schema=public) Thanks Danny
Re: schema prefixes in default values (was RE: removing "serial" from table definitions).
Marc Mamin writes: > it seems that our problem had nothing to do with serial, but with the way > schema prefixes are handled in column default values. > pg_attrdef.adsrc: > filled when the defaut value is defined. contains a schema prefix only when > required at this creation time. Is constant afterwards. We removed adsrc a couple of versions back, precisely because it doesn't track subsequent changes. Relying on it has been deprecated for a lot longer than that. > Moreover a pg_dump->restore might in some cases modify the value of > pg_attrdef.adsrc pg_dump hasn't relied on adsrc since PG 7.2. regards, tom lane
Re: WARNING: skipping "pg_database" --- only superuser can analyze it
"Abraham, Danny" writes: > Good old "analyze;" will run smoothly on 11.5... > But on 12.6 will skip and warn on any pg_catalog/information_schema table. Hm, for me there are such warnings on 11.x too. Not as many --- it looks like indexes are mentioned as well in 12.x. But it's never been the case that this was entirely silent for an unprivileged user. > Questions: 1. Has the behavior changed in 12.6? 2.How can I analyze on > public tables (No syntax like analyze schema=public) If you don't want to see the warnings, consider set client_min_messages to error; regards, tom lane
Re: Ubuntu 14.04 (trusty) Postgres 13 deb package
On Sun, Jun 27, 2021 at 10:42 AM Bhavesh Mistry wrote: > Hi All, > > I was trying to install PSQL 13 on Ubuntu 14.04 (trusty) but I could not > find the package. It seems the path has been removed. Can you please tell > me how to build deb package for trusty from psql source? I tried psql 13 > sources, I was able to build but I could not figure out how to build deb > package. I would appreciate any pointers. > > Upgrade your Linux, 14.04 has been EOL for 2 years. > -- > Thanks, > > Bhavesh > >
RE: Re: WARNING: skipping "pg_database" --- only superuser can analyze it
Right to the point ==> set client_min_messages=error; Solved it Thanks -Original Message- From: Tom Lane Sent: Monday, June 28, 2021 5:53 PM To: Abraham, Danny Cc: pgsql-gene...@postgresql.org Subject: [EXTERNAL] Re: WARNING: skipping "pg_database" --- only superuser can analyze it "Abraham, Danny" writes: > Good old "analyze;" will run smoothly on 11.5... > But on 12.6 will skip and warn on any pg_catalog/information_schema table. Hm, for me there are such warnings on 11.x too. Not as many --- it looks like indexes are mentioned as well in 12.x. But it's never been the case that this was entirely silent for an unprivileged user. > Questions: 1. Has the behavior changed in 12.6? 2.How can I analyze on > public tables (No syntax like analyze schema=public) If you don't want to see the warnings, consider set client_min_messages to error; regards, tom lane
Use case stuck due to Partitioning
Hi All, Please find my use case below and suggest the appropriate solution: We have done declarative partitioning over our table A based on some key and further range sub partitioned each partition on the basis of createddate range of 90 days. In the search query to display last 90 day data , I give both column B and createddate which may pick any of the required partitions. But how can I display last 90 days data in which a record which is modified last is also shown at the top but its created date might not lie in the last 90 days range.. Basically, I need to partition my huge table based on some key and also on date to split the data into smaller dataset for faster query . The UI needs to display the latest modified records first but the created date also needs to be given in the query to pick the right partition... Can we do partition differently to achieve this? Should i find the max modified date and give it in the range of the created date. Thanks & Regards
Re: Use case stuck due to Partitioning
Do you have an index on the "updated_at" field and found that the query is too slow? Do you have an example query?
Re: Use case stuck due to Partitioning
Hi Michael, Yes I have a btree index on the *modified_date* column currently. SELECT * FROM partitioned_table where A ='Value' AND created_date >= '2021-03-01 08:16:13.589' and created_date <= '2021-04-02 08:16:13.589' ORDER BY viewpriority desc OFFSET 0 ROWS FETCH NEXT 200 ROWS ONLY; Here viewpriority is basically a long value containing created_date in milliseconds. Issue here is : I want to somehow include *modified_date also in this query to get the records sorted by **modified_date . But that sorting will happen only in the specified created_date range only. I want those latest modified records also whose created_date might not lie in this range.* *Thanks * On Tue, Jun 29, 2021 at 1:22 AM Michael Lewis wrote: > Do you have an index on the "updated_at" field and found that the query is > too slow? Do you have an example query? >
ALTER TABLE ... DETACH PARTITION just sitting there
AWS RDS Postgresql 12.5.
We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.
But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
instance, and validated that nothing else is blocking me.
Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
TABLE... does not work.)
--
Angular momentum makes the world go 'round.
Re: ALTER TABLE ... DETACH PARTITION just sitting there
Ron writes:
> AWS RDS Postgresql 12.5.
> We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
> TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
> one at a time. Whenever it's failed due to a FK constraint (and there are
> many of them!), I dropped the "same month" partition from TABLE_B, and then
> returned and dropped the partition from TABLE_A.
> But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
> table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
> instance, and validated that nothing else is blocking me.
> Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
> TABLE... does not work.)
Perhaps the corresponding TABLE_B partition lacks an index on the
referencing column? I've not looked at how this particular case
is implemented, but typically, lack of such an index is fine
until you try to delete PK-side rows.
regards, tom lane
Re: ALTER TABLE ... DETACH PARTITION just sitting there
On 2021-Jun-28, Ron wrote:
> We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
> TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
> one at a time. Whenever it's failed due to a FK constraint (and there are
> many of them!), I dropped the "same month" partition from TABLE_B, and then
> returned and dropped the partition from TABLE_A.
>
> But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
> table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
> instance, and validated that nothing else is blocking me.
Did you look in pg_locks for ungranted locks?
> Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
> TABLE... does not work.)
Sure, it is possible. Do you have any FKs that are missing indexes in
the referencing side?
--
Álvaro Herrera Valdivia, Chile
really, I see PHP as like a strange amalgamation of C, Perl, Shell
inflex: you know that "amalgam" means "mixture with mercury",
more or less, right?
i.e., "deadly poison"
Re: Use case stuck due to Partitioning
I am unclear exactly what you want to do with modified_date. Can you write pseudo code perhaps? It sounds like you are wanting to union all the results of the query you gave, with the results of a similar query where modified_date >= '2021-03-01 08:16:13.589' and created_at < '2021-03-01 08:16:13.589'. However, if modified date is not null, and is set to the created_date by default, then there seems no reason to check the created_date except if partition pruning at plan time is very important. Are you experiencing an actual performance problem, are you unsure how to write the query for the data you want, or is this premature optimization?
Re: ALTER TABLE ... DETACH PARTITION just sitting there
On 6/28/21 4:16 PM, Tom Lane wrote:
Ron writes:
AWS RDS Postgresql 12.5.
We've got a table partitioned by month range (FOR VALUES FROM ('2011-07-01')
TO (2011-08-01')), and I've been detaching partitions from oldest to newest,
one at a time. Whenever it's failed due to a FK constraint (and there are
many of them!), I dropped the "same month" partition from TABLE_B, and then
returned and dropped the partition from TABLE_A.
But now, after 17 dropped partitions it's just sitting there on "ALTER TABLE
table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
instance, and validated that nothing else is blocking me.
Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
TABLE... does not work.)
Perhaps the corresponding TABLE_B partition lacks an index on the
referencing column? I've not looked at how this particular case
is implemented, but typically, lack of such an index is fine
until you try to delete PK-side rows.
There are seven FK constraints, all in the format of (field_1, field_2).
Two referenced table have a supporting index on both columns, and five have
a supporting index on only field_1.
--
Angular momentum makes the world go 'round.
Re: Use case stuck due to Partitioning
On Mon, Jun 28, 2021 at 2:51 PM Michael Lewis wrote: > I am unclear exactly what you want to do with modified_date. Can you write > pseudo code perhaps? > > I second this. While I'm not all that familiar with partitioning I am readily getting the feeling that whether or not partitioning is used here is immaterial to the question of how best to retrieve most recent data. My take is that when choosing to partition on time its usually best done because the older data becomes irrelevant over time and thus can be readily ignored. If that data doesn't become stale then the benefit of partitioning is lost since you now have meaningful data on all partitions. Partitioning, like indexes, are not some super feature whose presence solves all performance problems. They need to be designed and used in a thoughtful manner. Saying "Basically, I need to partition my huge table based on some key and also on date to split the data into smaller dataset for faster query." doesn't indicate that much designing has taken place - only that there is a belief that "smaller datasets make for faster queries". Partitioning doesn't necessarily make the dataset smaller - it just moves parts around. The queries need to only look at a subset of that main dataset and the query described here doesn't do that - and there isn't a feature of the current partitioning implementation, that I know of (limited experience admittedly), that will overcome this reality of the data. David J.
Re: ALTER TABLE ... DETACH PARTITION just sitting there
On Mon, 2021-06-28 at 16:52 -0500, Ron wrote:
> On 6/28/21 4:16 PM, Tom Lane wrote:
> > Ron writes:
> > > AWS RDS Postgresql 12.5.
> > > We've got a table partitioned by month range (FOR VALUES FROM
> > > ('2011-07-01')
> > > TO (2011-08-01')), and I've been detaching partitions from oldest to
> > > newest,
> > > one at a time. Whenever it's failed due to a FK constraint (and there are
> > > many of them!), I dropped the "same month" partition from TABLE_B, and
> > > then
> > > returned and dropped the partition from TABLE_A.
> > > But now, after 17 dropped partitions it's just sitting there on "ALTER
> > > TABLE
> > > table_a DROP PARTITION table_a_p2011_07;" I'm the only user on this test
> > > instance, and validated that nothing else is blocking me.
> > > Are the FK validations what's causing the apparent "hang"? (EXPLAIN ALTER
> > > TABLE... does not work.)
> >
> > Perhaps the corresponding TABLE_B partition lacks an index on the
> > referencing column? I've not looked at how this particular case
> > is implemented, but typically, lack of such an index is fine
> > until you try to delete PK-side rows.
>
> There are seven FK constraints, all in the format of (field_1, field_2).
> Two referenced table have a supporting index on both columns, and five have
> a supporting index on only field_1.
Those five foreign keys that are not fully indexed may well be your problem.
Yours,
Laurenz Albe
CREATE INDEX ONLY on a partitioned table?
Postgresql 12.5. https://www.postgresql.org/docs/12/ddl-partitioning.html mentions creating indices on children, and then attaching them to a table which previously had CREATE INDEX ... ONLY ON executed. Can I do something similar on an existing table (for example, on a heavily partitioned table, or because I want to see that progress is being made)? -- Angular momentum makes the world go 'round.
