Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 29/06/2021 22:49, Adrian Klaver wrote:

If I'm following correctly then:

1) Under old system there where preset two hour slots over a day period, 
where the period was say 8:00 to 16:00


2) You now want to allow user defined two hour slots over the same 
period, where a slot can't start before 8:00 or end after 16:00.


3) First to file gets the slot.

4) Because of turn around considerations a user can't pick up the 
remaining hour of an overlap. As example in the case of time slots of 
10:00 - 12:00 and 11:00 - 13:00 for a particular plane where the second 
slot was the later one filed the user can't get 12:00 - 13:00.


Sorry for the slow response - I'm only getting to spend intermittent 
time on this project.


Your description isn't far off; however (with reference to your points 
1-4 above):


1. Under the old system, one-hour slots were baked in at a fundamental 
level, though a user could book multiple consecutive 1-hour slots together.


2. I'd now like the database to be agnostic with regard to the slot 
duration. A booking should be defined only by timestamps defining its 
beginning and end; the booking could also span multiple days. The 
concept of a "slot" (a slot being the minimum unit of time which a user 
can book, be it an hour or anything else) will be imposed only at the 
application level... This would allow it to be changed easily if 
required (in other words, slots are a sort of artifial grid view which 
the application imposes on the raw bookings coming from the database).


3. Yes, first to book available time gets it. Anyone else booking for 
the same time is in a queue behind the first in line. Similarly, someone 
making a booking whose time partially overlaps an existing booking will 
effectively finish up with two bookings - one active (first in line for 
the free time) and one queued (behind the already-booked time).


4. Effectively, yes - see my explanation of queued bookings in (3) above.

Thanks for the time and thought you're giving this!

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Doubt on pgbouncer

[Rama Krishnan]

Hi Team,

How can I split read and write queries using pgbouncer

Regards

A.Rama Krishnan


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: Doubt on pgbouncer

[Bruce Momjian]

On Sat, Jul  3, 2021 at 09:05:58PM +0530, Rama Krishnan wrote:
> 
> Hi Team,
> 
> How can I split read and write queries using pgbouncer

No.  I think only PgPool can do that because it parses the SQL.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 27/06/2021 23:41, Ray O'Donnell wrote:

Hi all,

I'm playing with timestamptz ranges for a hobby project. I have a
table with a tstzrange column, in which the timestamps can overlap;
where they do, rows with a higher priority (derived from a bigint
primary key column) should be picked.

What I'd like to do is present a view which shows timestamp ranges at
 the front of the queue, as it were; where ranges overlap, these may
be segments of a range from a particular row. I'm having trouble with
this and would appreciate suggestions.


I've come up with a way of doing it using a function... it's not going 
to be very efficient if the number of rows gets large, due to nested 
loops, but as the system generally keeps only a limited number of 
bookings (no more that a few hundred), I think it'll do - certainly as a 
first run at it.


Firstly, the table structure (as it now stands) on which the function 
will operate:


CREATE TABLE bookings
(
booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
aircraft_reg text NOT NULL,
type_code text NOT NULL,
booking_time tstzrange NOT NULL,
owner_uid text NOT NULL,
owner_name text NOT NULL,

CONSTRAINT bookings_pk PRIMARY KEY (booking_id),

(... foregin keys etc...)
);


And here's the function:

create or replace function get_visible_bookings()
returns setof bookings
language plpgsql
as
$$
declare
  m_rec bookings;
  m_overlapping record;
  m_visible_time tstzrange;
begin
  -- Loop through all bookings on the system, ordered on booking ID.
  -- The booking ID also give the queue priority of the booking:
  -- bookings with a lower ID have a higher priority.
  for m_rec in
select * from bookings order by booking_id
  loop
m_visible_time := m_rec.booking_time;

-- For each booking, check whether there are any with
-- a higher priority and whose times overlap it.
for m_overlapping in
  select booking_id, booking_time from bookings
  where booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
  -- Snip away any overlapping (obscured) time.
  m_visible_time := m_visible_time - m_overlapping.booking_time;
end loop;

-- If any of the current booking's time is still visible,
-- then return the row with what's left of the time.
if not isempty(m_visible_time) then
return next row(m_rec.booking_id, m_rec.aircraft_reg,
  m_rec.type_code, m_visible_time,
  m_rec.owner_uid, m_rec.owner_name);
end if;
  end loop;

  return;
end;
$$;



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Doubt on pgbouncer

[Rama Krishnan]

Hi Bruce

Thank you for the clarification. Can you pls share any links or docs about
my doubt that can be helpful for me further information

On Sat, 3 Jul, 2021, 21:21 Bruce Momjian,  wrote:

> On Sat, Jul  3, 2021 at 09:05:58PM +0530, Rama Krishnan wrote:
> >
> > Hi Team,
> >
> > How can I split read and write queries using pgbouncer
>
> No.  I think only PgPool can do that because it parses the SQL.
>
> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   If only the physical world exists, free will is an illusion.
>
>

Re: Doubt on pgbouncer

[Ben Chobot]

Rama Krishnan wrote on 7/3/21 8:35 AM:


Hi Team,

How can I split read and write queries using pgbouncer


You do it with your application. Make a pgbouncer database for 
read/write queries and point it at your postgresql primary, and then 
make a second pgbouncer database for read-only queries and point it at a 
replica. Have your application connect to the proper database, depending 
upon its needs.


It's not transparent, but it is effective.

Re: Overlapping timestamptz ranges with priority

[Adrian Klaver]

On 7/3/21 9:32 AM, Ray O'Donnell wrote:

On 27/06/2021 23:41, Ray O'Donnell wrote:

Hi all,

I'm playing with timestamptz ranges for a hobby project. I have a
table with a tstzrange column, in which the timestamps can overlap;
where they do, rows with a higher priority (derived from a bigint
primary key column) should be picked.

What I'd like to do is present a view which shows timestamp ranges at
 the front of the queue, as it were; where ranges overlap, these may
be segments of a range from a particular row. I'm having trouble with
this and would appreciate suggestions.


I've come up with a way of doing it using a function... it's not going 
to be very efficient if the number of rows gets large, due to nested 
loops, but as the system generally keeps only a limited number of 
bookings (no more that a few hundred), I think it'll do - certainly as a 
first run at it.


Firstly, the table structure (as it now stands) on which the function 
will operate:


CREATE TABLE bookings
(
     booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
     aircraft_reg text NOT NULL,
     type_code text NOT NULL,
     booking_time tstzrange NOT NULL,
     owner_uid text NOT NULL,
     owner_name text NOT NULL,

     CONSTRAINT bookings_pk PRIMARY KEY (booking_id),

     (... foregin keys etc...)
);


And here's the function:

create or replace function get_visible_bookings()
returns setof bookings
language plpgsql
as
$$
declare
   m_rec bookings;
   m_overlapping record;
   m_visible_time tstzrange;
begin
   -- Loop through all bookings on the system, ordered on booking ID.
   -- The booking ID also give the queue priority of the booking:
   -- bookings with a lower ID have a higher priority.
   for m_rec in
     select * from bookings order by booking_id
   loop
     m_visible_time := m_rec.booking_time;

     -- For each booking, check whether there are any with
     -- a higher priority and whose times overlap it.
     for m_overlapping in
   select booking_id, booking_time from bookings
   where booking_id < m_rec.booking_id
   and booking_time && m_rec.booking_time
     loop
   -- Snip away any overlapping (obscured) time.
   m_visible_time := m_visible_time - m_overlapping.booking_time;
     end loop;

     -- If any of the current booking's time is still visible,
     -- then return the row with what's left of the time.
     if not isempty(m_visible_time) then
 return next row(m_rec.booking_id, m_rec.aircraft_reg,
   m_rec.type_code, m_visible_time,
   m_rec.owner_uid, m_rec.owner_name);
     end if;
   end loop;

   return;
end;
$$;


I'm not sure this is doing what you think it is;

select * from bookings  order by  booking_id;
 booking_id | aircraft_reg | type_code | 
booking_time | owner_uid | owner_name

+--+---+-+---+-
  1 | A1ZX | type1 | ["2021-07-03 
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
  2 | A1ZX | type1 | ["2021-07-03 
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
  3 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
  4 | B2CA | type2 | ["2021-07-03 
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
  5 | B2CA | type2 | ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur



 select * from get_visible_bookings();
 booking_id | aircraft_reg | type_code | 
booking_time | owner_uid | owner_name

+--+---+-+---+
  1 | A1ZX | type1 | ["2021-07-03 
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
  2 | A1ZX | type1 | ("2021-07-03 
14:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
  3 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
  4 | B2CA | type2 | ["2021-07-03 
09:00:00-07","2021-07-03 10:00:00-07") | 2 | wilbur

(4 rows)


The booking_id for aircraft B2CA with booking_time of  ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a 
step missing that accounts for bookings being assigned to a particular 
aircraft.









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

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 03/07/2021 18:59, Adrian Klaver wrote:


I'm not sure this is doing what you think it is;


[...]


  select * from get_visible_bookings();
  booking_id | aircraft_reg | type_code | booking_time | 
owner_uid | owner_name
+--+---+-+---+ 


   1 | A1ZX | type1 | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07"] | 1 | aklaver
   2 | A1ZX | type1 | ("2021-07-03 14:00:00-07","2021-07-03 
16:00:00-07"] | 1 | aklaver
   3 | A1ZX | type1 | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07"] | 1 | aklaver
   4 | B2CA | type2 | ["2021-07-03 09:00:00-07","2021-07-03 
10:00:00-07") | 2 | wilbur
(4 rows)


The booking_id for aircraft B2CA with booking_time of  ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a 
step missing that accounts for bookings being assigned to a particular 
aircraft.


Yes, you're right - I realised that after I sent my last email. The 
inner loop in the function should have matched overlapping bookings by 
aircraft registration:


-- For each booking, check whether there are any with
-- a higher priority and whose times overlap it.
for m_overlapping in
  select booking_id, booking_time from bookings
  where booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
  -- Snip away any overlapping (obscured) time.
  m_visible_time := m_visible_time - m_overlapping.booking_time;
end loop;

When this is corrected, I get what I'm looking for (trying it here with 
your data):


set time zone 'America/Los_Angeles';
SET

select booking_id, aircraft_reg, booking_time from bookings order by 
aircraft_reg, lower(booking_time);


 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)

select booking_id, aircraft_reg, booking_time from 
get_visible_bookings() order by aircraft_reg, lower(booking_time);


 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 14:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ("2021-07-03 12:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)


gfc_booking6_dev=# set time zone 'America/Los_Angeles';
SET
gfc_booking6_dev=# select booking_id, aircraft_reg, booking_time from 
bookings order by aircraft_reg, lower(booking_time);
 booking_id | aircraft_reg |booking_time 


+--+-
 25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03 
14:00:00-07")
 26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03 
16:00:00-07")
 27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04 
12:00:00-07")
 28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03 
12:00:00-07"]
 29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03 
14:00:00-07"]

(5 rows)


I need to play with it a bit more: for example, if a long, 
lower-priority booking is behind a short, higher-priority one such that 
the long one extends both before and after the short one, then the 
range-difference operator will give me an error about a non-contiguous 
result. However, I think I'm heading in the right direction now.


Thanks,

Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: Overlapping timestamptz ranges with priority

[Adrian Klaver]

On 7/3/21 12:16 PM, Ray O'Donnell wrote:

On 03/07/2021 18:59, Adrian Klaver wrote:



The booking_id for aircraft B2CA with booking_time of  ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is 
a step missing that accounts for bookings being assigned to a 
particular aircraft.


Yes, you're right - I realised that after I sent my last email. The 
inner loop in the function should have matched overlapping bookings by 
aircraft registration:


     -- For each booking, check whether there are any with
     -- a higher priority and whose times overlap it.
     for m_overlapping in
   select booking_id, booking_time from bookings
   where booking_id < m_rec.booking_id
   and booking_time && m_rec.booking_time
     loop
   -- Snip away any overlapping (obscured) time.
   m_visible_time := m_visible_time - m_overlapping.booking_time;
     end loop;


Was the above supposed to show the change?



When this is corrected, I get what I'm looking for (trying it here with 
your data):


set time zone 'America/Los_Angeles';
SET

select booking_id, aircraft_reg, booking_time from bookings order by 
aircraft_reg, lower(booking_time);




Pretty sure lower() is not needed, if I'm following this correctly:

https://www.postgresql.org/docs/12/functions-range.html

"The simple comparison operators <, >, <=, and >= compare the lower 
bounds first, and only if those are equal, compare the upper bounds. 
These comparisons are not usually very useful for ranges, but are 
provided to allow B-tree indexes to be constructed on ranges."


In the case where the lower bound  is the same I'm thinking using 
lower() will result in different ordering under different circumstances:


insert into bookings(aircraft_reg, type_code, booking_time, owner_uid, 
owner_name) values ('A1ZX', 'type1', '[07/04/2021 09:00, 07/04/2021 
14:00]', '1', 'aklaver');


insert into bookings(aircraft_reg, type_code, booking_time, owner_uid, 
owner_name) values ('A1ZX', 'type1', '[07/04/2021 09:00, 07/04/2021 
11:00]', '1', 'aklaver');


select * from bookings  order by  aircraft_reg, lower(booking_time);
 booking_id | aircraft_reg | type_code | 
booking_time | owner_uid | owner_name

+--+---+-+---+
  1 | A1ZX | type1 | ["2021-07-03 
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
  2 | A1ZX | type1 | ["2021-07-03 
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
  6 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
  3 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
  7 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
  4 | B2CA | type2 | ["2021-07-03 
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
  5 | B2CA | type2 | ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur



select * from bookings  order by  aircraft_reg, booking_time;
 booking_id | aircraft_reg | type_code | 
booking_time | owner_uid | owner_name

+--+---+-+---+
  1 | A1ZX | type1 | ["2021-07-03 
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
  2 | A1ZX | type1 | ["2021-07-03 
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
  7 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
  3 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
  6 | A1ZX | type1 | ["2021-07-04 
09:00:00-07","2021-07-04 14:00:00-07"] | 1 | aklaver
  4 | B2CA | type2 | ["2021-07-03 
09:00:00-07","2021-07-03 12:00:00-07"] | 2 | wilbur
  5 | B2CA | type2 | ["2021-07-03 
11:00:00-07","2021-07-03 14:00:00-07"] | 2 | wilbur


update bookings set type_code = 'type3' where type_code = 'type1';

select * from bookings  order by  aircraft_reg, booking_time;
 booking_id | aircraft_reg | type_code | 
booking_time | owner_uid | owner_name

+--+---+-+---+
  1 | A1ZX | type3 | ["2021-07-03 
10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
  2 | A1ZX | type3 | ["2021-07-03 
12:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
  7 | A1ZX | type3 | ["2021-07-04 
09:00:00-07","2021-07-04 11:00:00-07"] | 1 | aklaver
  3 | A1ZX | type

Re: Overlapping timestamptz ranges with priority

[Ray O'Donnell]

On 03/07/2021 21:13, Adrian Klaver wrote:

On 7/3/21 12:16 PM, Ray O'Donnell wrote:
Yes, you're right - I realised that after I sent my last email. The 
inner loop in the function should have matched overlapping bookings by 
aircraft registration:


 -- For each booking, check whether there are any with
 -- a higher priority and whose times overlap it.
 for m_overlapping in
   select booking_id, booking_time from bookings
   where booking_id < m_rec.booking_id
   and booking_time && m_rec.booking_time
 loop
   -- Snip away any overlapping (obscured) time.
   m_visible_time := m_visible_time - m_overlapping.booking_time;
 end loop;


Was the above supposed to show the change?


Whoops, sorry, here it is:

for m_overlapping_time in
select booking_id, booking_time from bookings
where aircraft_reg = m_rec.aircraft_reg
  and booking_id < m_rec.booking_id
  and booking_time && m_rec.booking_time
loop
[... etc ...]

select booking_id, aircraft_reg, booking_time from bookings order by 
aircraft_reg, lower(booking_time);




Pretty sure lower() is not needed, if I'm following this correctly:

https://www.postgresql.org/docs/12/functions-range.html

"The simple comparison operators <, >, <=, and >= compare the lower 
bounds first, and only if those are equal, compare the upper bounds. 
These comparisons are not usually very useful for ranges, but are 
provided to allow B-tree indexes to be constructed on ranges."


Ah, good - thanks for pointing that out.

In the case where the lower bound  is the same I'm thinking using 
lower() will result in different ordering under different circumstances:


I see what you mean. It shouldn't matter for our use case; ordering on 
the aircraft registration and time is what counts for us, and the output 
of the function ought to produce well-ordered booking times for each 
aircraft. The other columns are used for display purposes only.


I need to play with it a bit more: for example, if a long, 
lower-priority booking is behind a short, higher-priority one such 
that the long one extends both before and after the short one, then 
the range-difference operator will give me an error about a 
non-contiguous result. However, I think I'm heading in the right 
direction now.


Great. Good luck going forward.


Thanks again for your help - much appreciated!

Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie