partition table slow planning

[Jatinder Sandhu]

We encounter a issue when we do query on partition table directly with
proper partition key provide. postgres able to find problem partition but
when I do explain plan it showing 95% spend on planning the execution .
Here is example
itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination
='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
itinerary-# ;

  QUERY PLAN
---
 Append  (cost=0.29..13.79 rows=11 width=1024) (actual time=0.033..0.037
rows=1 loops=1)
   ->  Index Scan using itinerary_101_destination_departure_date_idx on
itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
time=0.033..0.036 rows=1 loops=1)
 Index Cond: (((destination)::text = 'GRJ'::text) AND
((departure_date)::text = '2020-01-01'::text))
 Filter: (month_day = 101)

* Planning Time: 51.677 ms* Execution Time: 0.086 ms


When  I do query on directly on the partition table it is quite fast
itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE destination
='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
itinerary-# ;

 QUERY PLAN
-
 Index Scan using itinerary_101_destination_departure_date_idx on
itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
time=0.043..0.048 rows=1 loops=1)
   Index Cond: (((destination)::text = 'GRJ'::text) AND
((departure_date)::text = '2020-01-01'::text))
   Filter: (month_day = 101)

* Planning Time: 0.191 ms* Execution Time: 0.074 ms
(5 rows)

itinerary=#

*Can we know why this is happening?*

Re: Default ordering option

[Cyril Champier]

Thanks for your answers.
Unfortunately the update trick only seems to work under certain conditions.

I do this to shuffle my patients table:
UPDATE "patients"
SET "updated_at" = NOW()
WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY
random() LIMIT 1)

Then indeed, this query returns different order:
SELECT *
FROM "patients"

But this one (because it use an index?) always returns values in the same
order:
SELECT "id"
FROM "patients"



And for the other suggestion, I cannot blindly add 'ORDER BY random()' to
every select,
because of the incompatibility with distinct and union, and the way we use
our orm.


On Wed, Jul 24, 2019 at 3:54 AM Ian Barwick 
wrote:

> On 7/24/19 2:23 AM, Adrian Klaver wrote:
> > On 7/23/19 8:43 AM, Cyril Champier wrote:
> >> Hi,
> >>
> >> In this documentation <
> https://www.postgresql.org/docs/9.1/queries-order.html>, it is said:
> >>
> >> If sorting is not chosen, the rows will be returned in an
> >> unspecified order. The actual order in that case will depend on the
> >> scan and join plan types and the order on disk, but it must not be
> >> relied on.
> >>
> >>
> >> I would like to know if there is any way to change that to have a
> "real" random behaviour.
> >>
> >> My use case:
> >> At Doctolib, we do a lot of automatic tests.
> >> Sometimes, people forgot to order their queries. Most of the time,
> there is no real problem on production. Let say, we display a user list
> without order.
> >> When a developer writes a test for this feature, he will create 2 users
> A and B, then assert that they are displayed "[A, B]".
> >> 99% of the time the test will be ok, but sometimes, the displayed list
> will be "[B,A]", and the test will fail.
> >>
> >> One solution could be to ensure random order with an even distribution,
> so that such failing test would be detected quicker.
> >>
> >> Is that possible? Maybe with a plugin?
> >
> > Not that I know of.
> >
> > A possible solution given below:
> >
> > test_(postgres)> insert into t1 values (1, 'dog'), (2, 'cat'), (3,
> 'fish');
> > INSERT 0 3
> >
> > test_(postgres)> select * from t1 ;
> >   a |  b
> > ---+--
> >   1 | dog
> >   2 | cat
> >   3 | fish
> > (3 rows)
> >
> > test_(postgres)> update  t1 set b = 'dogfish' where  a =1;
> > UPDATE 1
> >
> > test_(postgres)> select * from t1 ;
> >   a |b
> > ---+-
> >   2 | cat
> >   3 | fish
> >   1 | dogfish
> > (3 rows)
> >
> > An UPDATE reorders the rows. Maybe throw an UPDATE into the test after
> creating the users to force an 'out of order' result?
>
> An UPDATE without changing any values should have the same effect, e.g. :
>
>  UPDATE t1 SET b = b WHERE a = 1;
>
> Something like this
>
>  WITH x AS (SELECT * FROM t1 ORDER BY a DESC)
>  UPDATE t1 t
> SET a = t.a
>FROM x
>   WHERE t.a = x.a
>
> would shuffle the rows into reverse order, which might be enough to catch
> out any missing ORDER BY (this assumes nothing else will touch the table
> and reorder it before the test is run).
>
> You could also order by RANDOM() but there would be a chance the rows would
> end up in sequential order.
>
> Regards
>
>
> Ian Barwick
>
> --
>   Ian Barwick   https://www.2ndQuadrant.com/
>   PostgreSQL Development, 24x7 Support, Training & Services
>

Re: partition table slow planning

[Imre Samu]

>*Can we know why this is happening?*

Please give us - more info about your system:
- PG version?
- number of partitions?
- any other important?

for example - in PG 11.2 Changes:
"Improve planning speed for large inheritance or partitioning table groups
(Amit Langote, Etsuro Fujita)"
https://www.postgresql.org/docs/current/release-11-2.html

Imre

Jatinder Sandhu  ezt írta (időpont:
2019. júl. 24., Sze, 9:22):

>
>
> We encounter a issue when we do query on partition table directly with
> proper partition key provide. postgres able to find problem partition but
> when I do explain plan it showing 95% spend on planning the execution .
> Here is example
> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination
> ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
> itinerary-# ;
>
> QUERY PLAN
>
> ---
>  Append  (cost=0.29..13.79 rows=11 width=1024) (actual time=0.033..0.037
> rows=1 loops=1)
>->  Index Scan using itinerary_101_destination_departure_date_idx on
> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
> time=0.033..0.036 rows=1 loops=1)
>  Index Cond: (((destination)::text = 'GRJ'::text) AND
> ((departure_date)::text = '2020-01-01'::text))
>  Filter: (month_day = 101)
>
> * Planning Time: 51.677 ms* Execution Time: 0.086 ms
>
>
> When  I do query on directly on the partition table it is quite fast
> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE destination
> ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
> itinerary-# ;
>
>  QUERY PLAN
>
> -
>  Index Scan using itinerary_101_destination_departure_date_idx on
> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
> time=0.043..0.048 rows=1 loops=1)
>Index Cond: (((destination)::text = 'GRJ'::text) AND
> ((departure_date)::text = '2020-01-01'::text))
>Filter: (month_day = 101)
>
> * Planning Time: 0.191 ms* Execution Time: 0.074 ms
> (5 rows)
>
> itinerary=#
>
> *Can we know why this is happening?*
>
>
>
>
>
>

Re: Query plan: SELECT vs INSERT from same select

[Alban Hertroys]

> On 23 Jul 2019, at 22:29, Alexander Voytsekhovskyy  
> wrote:
> 
> I have quite complicated query:
> 
> SELECT axis_x1, axis_y1, SUM(delivery_price)  as v_1 FROM (
> SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, 
> clients.id_client as axis_y1, delivery_data.amount * production_price.price * 
> groups.discount as delivery_price
> 
> FROM delivery_data 
> JOIN client_tt ON (client_tt.id_client_tt = delivery_data.id_client_tt) 
> JOIN clients ON (client_tt.id_client = clients.id_client) 
> JOIN production ON (production.id = delivery_data.id_product) 
> JOIN groups ON (groups.id = delivery_data.delivery_group_id AND 
> client_tt.id_group = groups.id AND groups.id = clients.id_group) 

Are client_tt.id_group and clients.id_group ever different from each other? It 
looks like you might have redundant information there, but... If they are 
guaranteed to be the same then you don’t need the JOIN to clients, which would 
both remove a JOIN and reduce the complexity of the JOIN condition on groups.

Or (assuming the group id’s are indeed supposed to be equal), you could
 JOIN clients ON (client_tt.id_client = clients.id_client AND 
client_tt.id_group = clients.id_group)
instead of putting that condition within the JOIN condition on groups.

I don’t think either option will make a huge difference (the first probably 
more than the second, as it reduces an entire join), but it could be enough to 
help the database figure out a better plan.

> LEFT JOIN production_price on (delivery_data.id_product = 
> production_price.id_production AND groups.price_list_id = 
> production_price.price_list_id AND delivery_data.delivery_date BETWEEN 
> production_price.date_from AND production_price.date_to) 
> 
> WHERE delivery_data.delivery_date between '2019-03-01' AND '2019-06-30' 
> AND delivery_data.delivery_group_id IN (...short list of values...) 
> AND delivery_data.id_product IN ()) AS tmpsource 

You don’t have a price if your goods weren’t produced in the delivery window 
you set? Or do you have goods that get delivered without having a price?

You seem to be using this query for a report on nett sales by month, but I have 
my doubts whether that LEFT JOIN, and especially the condition on the 
production date window, is really what you want: Your formula for 
delivery_price includes the price column from that LEFT JOIN, so you’re going 
to get 0 values when there is no production_price record in your 
delivery-window, resulting in a SUM that’s too low if the product was produced 
before (or after, but that seems unlikely) the delivery window.

> WHERE TRUE 

This line is unnecessary.

> GROUP BY GROUPING SETS ((axis_x1, axis_y1), (axis_x1), (axis_y1), ())

Apparently (I’m new to these statements), CUBE (axis_x1, axis_y1) is a 
shorthand for the above. They seem to have been introduced at the same time (in 
9.6?). See: 
https://www.postgresql.org/docs/11/queries-table-expressions.html#QUERIES-GROUPING-SETS

> It runs well, took 1s and returns 4000 rows.

I won’t go into the performance issue ash this point, other more knowledgeable 
people already did.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

postgres 9.5 DB corruption

[Thomas Tignor]

Hello postgres community,
Writing again to see if there are insights on this issue. We have had 
infrequent but recurring corruption since upgrading from postgres 9.1 to 
postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually 
performs a mixture of DML, primarily inserts and updates on two specific 
tables, with no single op being suspect. In the past, corruption events have 
produced encoding errors on COPY operations (invalid byte sequence for encoding 
"UTF8"). More recently, they have caused segmentation faults. We were able to 
take a cold backup after a recent event. SELECTing the corrupted data on our 
cold backup yields the following stack. Any info on a solution or how to 
proceed towards a solution would be much appreciated.
Thanks in advance.

(gdb) where
#0  pglz_decompress (source=source@entry=0xa617904 "0", slen=8139, 
dest=dest@entry=0x4268e028 "", rawsize=808452096) at pg_lzcompress.c:745
#1  0x080f3079 in toast_decompress_datum (attr=0xa6178fc) at tuptoaster.c:2210
#2  0x080f3716 in heap_tuple_untoast_attr (attr=0xa6178fc) at tuptoaster.c:183
#3  0x08440955 in pg_detoast_datum_packed (datum=) at fmgr.c:2270
#4  0x084145bf in text_to_cstring (t=0x7592fd2a) at varlena.c:176
#5  0x0843e874 in FunctionCall1Coll (flinfo=flinfo@entry=0xa614738, 
collation=collation@entry=0, arg1=arg1@entry=1972567338) at fmgr.c:1297
#6  0x0843fef8 in OutputFunctionCall (flinfo=0xa614738, val=1972567338) at 
fmgr.c:1950
#7  0x080bf84b in printtup (slot=0xa613bf4, self=0xa60d714) at printtup.c:359
#8  0x08220f9a in ExecutePlan (dest=0xa60d714, direction=, 
numberTuples=0, sendTuples=, operation=CMD_SELECT, 
planstate=0xa613974, estate=0xa6138ec) at execMain.c:1574
#9  standard_ExecutorRun (queryDesc=0xa6134e4, direction=ForwardScanDirection, 
count=0) at execMain.c:337
#10 0x08332c1b in PortalRunSelect (portal=portal@entry=0xa6114dc, 
forward=forward@entry=1 '\001', count=0, count@entry=2147483647, 
dest=dest@entry=0xa60d714) at pquery.c:942
#11 0x08333fa7 in PortalRun (portal=portal@entry=0xa6114dc, 
count=count@entry=2147483647, isTopLevel=isTopLevel@entry=1 '\001', 
dest=dest@entry=0xa60d714, altdest=altdest@entry=0xa60d714, 
completionTag=completionTag@entry=0xffd5d71c "")
    at pquery.c:786
#12 0x08330ba8 in exec_simple_query (query_string=0xa5f1754 "select * from 
ams.alert_attribute_bak;") at postgres.c:1096
#13 PostgresMain (argc=1, argv=0xa53dbbc, dbname=0xa53daec "ams", 
username=0xa53dadc "akamai") at postgres.c:4049
#14 0x080b53af in BackendRun (port=0xa584b78) at postmaster.c:4312
#15 BackendStartup (port=0xa584b78) at postmaster.c:3986
#16 ServerLoop () at postmaster.c:1705
#17 0x082d0dd7 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0xa53d2a8) 
at postmaster.c:1313
#18 0x080b68eb in main (argc=3, argv=0xa53d2a8) at main.c:228
(gdb)

Tom    :-)

Re: partition table slow planning

[Jatinder Sandhu]

 PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit'
Total number of partition is 367

Parent table defination

   Table "public.itinerary"
  Column   |Type | Collation | Nullable |
Default
---+-+---+--+-
 flight_query  | character varying(50)   |   | not null |
 origin| character varying(5)|   | not null |
 destination   | character varying(5)|   | not null |
 departure_date| character varying(10)   |   | not null |
 month_day | integer |   | not null |
 journeys  | character varying(10485760) |   | not null |
 origin_metro  | character varying(5)|   |  |
 destination_metro | character varying(5)|   |  |
Partition key: LIST (month_day)



On Wed, Jul 24, 2019 at 5:16 AM Imre Samu  wrote:

> >*Can we know why this is happening?*
>
> Please give us - more info about your system:
> - PG version?
> - number of partitions?
> - any other important?
>
> for example - in PG 11.2 Changes:
> "Improve planning speed for large inheritance or partitioning table groups
> (Amit Langote, Etsuro Fujita)"
> https://www.postgresql.org/docs/current/release-11-2.html
>
> Imre
>
> Jatinder Sandhu  ezt írta (időpont:
> 2019. júl. 24., Sze, 9:22):
>
>>
>>
>> We encounter a issue when we do query on partition table directly with
>> proper partition key provide. postgres able to find problem partition but
>> when I do explain plan it showing 95% spend on planning the execution .
>> Here is example
>> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination
>> ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
>> itinerary-# ;
>>
>> QUERY PLAN
>>
>> ---
>>  Append  (cost=0.29..13.79 rows=11 width=1024) (actual time=0.033..0.037
>> rows=1 loops=1)
>>->  Index Scan using itinerary_101_destination_departure_date_idx on
>> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
>> time=0.033..0.036 rows=1 loops=1)
>>  Index Cond: (((destination)::text = 'GRJ'::text) AND
>> ((departure_date)::text = '2020-01-01'::text))
>>  Filter: (month_day = 101)
>>
>> * Planning Time: 51.677 ms* Execution Time: 0.086 ms
>>
>>
>> When  I do query on directly on the partition table it is quite fast
>> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE destination
>> ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
>> itinerary-# ;
>>
>>  QUERY PLAN
>>
>> -
>>  Index Scan using itinerary_101_destination_departure_date_idx on
>> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
>> time=0.043..0.048 rows=1 loops=1)
>>Index Cond: (((destination)::text = 'GRJ'::text) AND
>> ((departure_date)::text = '2020-01-01'::text))
>>Filter: (month_day = 101)
>>
>> * Planning Time: 0.191 ms* Execution Time: 0.074 ms
>> (5 rows)
>>
>> itinerary=#
>>
>> *Can we know why this is happening?*
>>
>>
>>
>>
>>
>>

-- 
Jatinder Sandhu | Database Administrator
+1-905-460-7955 | 145 King Street West, Toronto, ON M5H 1J8

*Book @ FlightNetwork * | Check out our
*Blog*  | Like us on *Facebook
*

Re: Default ordering option

[Adrian Klaver]

On 7/24/19 1:45 AM, Cyril Champier wrote:

Thanks for your answers.
Unfortunately the update trick only seems to work under certain conditions.

I do this to shuffle my patients table:
UPDATE "patients"
SET "updated_at" = NOW()
WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY 
random() LIMIT 1)


Then indeed, this query returns different order:
SELECT *
FROM "patients"

But this one (because it use an index?) always returns values in the 
same order:

SELECT "id"
FROM "patients"


Hmm, I don't see that:

test=# \d t1
  Table "public.t1"
 Column |   Type| Collation | Nullable | Default
+---+---+--+-
 a  | integer   |   | not null |
 b  | character varying |   |  |
Indexes:
"t1_pkey" PRIMARY KEY, btree (a)


test=# select * from t1;
 a |b
---+-
 2 | cat
 3 | fish
 1 | dogfish
(3 rows)

test=# select a from t1;
 a
---
 2
 3
 1
(3 rows)

Are you sure there is nothing going on between the first and second 
queries e.g. ROLLBACK?






And for the other suggestion, I cannot blindly add 'ORDER BY random()' 
to every select,
because of the incompatibility with distinct and union, and the way we 
use our orm.



Are you talking about the production or test queries above?



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

Re: postgres 9.5 DB corruption

[Adrian Klaver]

On 7/24/19 7:38 AM, Thomas Tignor wrote:

Hello postgres community,

Writing again to see if there are insights on this issue. We have had 
infrequent but recurring corruption since upgrading from postgres 9.1 to 
postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually 
performs a mixture of DML, primarily inserts and updates on two specific 
tables, with no single op being suspect. In the past, corruption events 
have produced encoding errors on COPY operations (invalid byte sequence 
for encoding "UTF8"). More recently, they have caused segmentation 
faults. We were able to take a cold backup after a recent event. 
SELECTing the corrupted data on our cold backup yields the following 
stack. Any info on a solution or how to proceed towards a solution would 
be much appreciated.


More information would be useful:

1) Schema of the tables.

2) Source of the data.




Thanks in advance.


(gdb) where
#0  pglz_decompress (source=source@entry=0xa617904 "0", slen=8139, 
dest=dest@entry=0x4268e028 "", rawsize=808452096) at pg_lzcompress.c:745
#1  0x080f3079 in toast_decompress_datum (attr=0xa6178fc) at tuptoaster.c:2210
#2  0x080f3716 in heap_tuple_untoast_attr (attr=0xa6178fc) at tuptoaster.c:183
#3  0x08440955 in pg_detoast_datum_packed (datum=) at fmgr.c:2270
#4  0x084145bf in text_to_cstring (t=0x7592fd2a) at varlena.c:176
#5  0x0843e874 in FunctionCall1Coll (flinfo=flinfo@entry=0xa614738, 
collation=collation@entry=0, arg1=arg1@entry=1972567338) at fmgr.c:1297
#6  0x0843fef8 in OutputFunctionCall (flinfo=0xa614738, val=1972567338) at 
fmgr.c:1950
#7  0x080bf84b in printtup (slot=0xa613bf4, self=0xa60d714) at printtup.c:359
#8  0x08220f9a in ExecutePlan (dest=0xa60d714, direction=, 
numberTuples=0, sendTuples=, operation=CMD_SELECT, 
planstate=0xa613974, estate=0xa6138ec) at execMain.c:1574
#9  standard_ExecutorRun (queryDesc=0xa6134e4, direction=ForwardScanDirection, 
count=0) at execMain.c:337
#10 0x08332c1b in PortalRunSelect (portal=portal@entry=0xa6114dc, 
forward=forward@entry=1 '\001', count=0, count@entry=2147483647, 
dest=dest@entry=0xa60d714) at pquery.c:942
#11 0x08333fa7 in PortalRun (portal=portal@entry=0xa6114dc, count=count@entry=2147483647, 
isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0xa60d714, 
altdest=altdest@entry=0xa60d714, completionTag=completionTag@entry=0xffd5d71c 
"")
     at pquery.c:786
#12 0x08330ba8 in exec_simple_query (query_string=0xa5f1754 "select * from 
ams.alert_attribute_bak;") at postgres.c:1096
#13 PostgresMain (argc=1, argv=0xa53dbbc, dbname=0xa53daec "ams", username=0xa53dadc 
"akamai") at postgres.c:4049
#14 0x080b53af in BackendRun (port=0xa584b78) at postmaster.c:4312
#15 BackendStartup (port=0xa584b78) at postmaster.c:3986
#16 ServerLoop () at postmaster.c:1705
#17 0x082d0dd7 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0xa53d2a8) 
at postmaster.c:1313
#18 0x080b68eb in main (argc=3, argv=0xa53d2a8) at main.c:228
(gdb)



Tom    :-)



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

Re: postgres 9.5 DB corruption

[Adrian Klaver]

On 7/24/19 7:38 AM, Thomas Tignor wrote:

Hello postgres community,

Writing again to see if there are insights on this issue. We have had 
infrequent but recurring corruption since upgrading from postgres 9.1 to 
postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually 
performs a mixture of DML, primarily inserts and updates on two specific 
tables, with no single op being suspect. In the past, corruption events 
have produced encoding errors on COPY operations (invalid byte sequence 
for encoding "UTF8"). More recently, they have caused segmentation 
faults. We were able to take a cold backup after a recent event. 
SELECTing the corrupted data on our cold backup yields the following 
stack. Any info on a solution or how to proceed towards a solution would 
be much appreciated.


Thanks in advance.



In my previous post when I referred to table schema I mean that to 
include associated schema like triggers, constraints, etc. Basically 
what is returned by \d in psql.



Tom    :-)



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

Re: Default ordering option

[Cyril Champier]

Indeed, you are right, I do my test in pure sql and via ruby ActiveRecord,
and I must had been confused,
the behaviour is correct in sql, it must have been a cache thing in
ActiveRecord that prevented the reordering.
But meanwhile, I tested on our whole CI, and it took twice the normal time
with updates to shuffle DB :(

For the union, I speak about production code like this:
"select count(*) from (#{directory_doctors_query_sql} union all
#{profiles_query_sql}) as doctors"
In the to_sql, we cannot detect that we will be injected into a union.
So I cannot blindly add the random in the to_sql method.







On Wed, Jul 24, 2019 at 4:48 PM Adrian Klaver 
wrote:

> On 7/24/19 1:45 AM, Cyril Champier wrote:
> > Thanks for your answers.
> > Unfortunately the update trick only seems to work under certain
> conditions.
> >
> > I do this to shuffle my patients table:
> > UPDATE "patients"
> > SET "updated_at" = NOW()
> > WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY
> > random() LIMIT 1)
> >
> > Then indeed, this query returns different order:
> > SELECT *
> > FROM "patients"
> >
> > But this one (because it use an index?) always returns values in the
> > same order:
> > SELECT "id"
> > FROM "patients"
>
> Hmm, I don't see that:
>
> test=# \d t1
>Table "public.t1"
>   Column |   Type| Collation | Nullable | Default
> +---+---+--+-
>   a  | integer   |   | not null |
>   b  | character varying |   |  |
> Indexes:
>  "t1_pkey" PRIMARY KEY, btree (a)
>
>
> test=# select * from t1;
>   a |b
> ---+-
>   2 | cat
>   3 | fish
>   1 | dogfish
> (3 rows)
>
> test=# select a from t1;
>   a
> ---
>   2
>   3
>   1
> (3 rows)
>
> Are you sure there is nothing going on between the first and second
> queries e.g. ROLLBACK?
>
> >
> >
> >
> > And for the other suggestion, I cannot blindly add 'ORDER BY random()'
> > to every select,
> > because of the incompatibility with distinct and union, and the way we
> > use our orm.
> >
> Are you talking about the production or test queries above?
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Default ordering option

[Adrian Klaver]

On 7/24/19 8:22 AM, Cyril Champier wrote:
Indeed, you are right, I do my test in pure sql and via ruby 
ActiveRecord, and I must had been confused,
the behaviour is correct in sql, it must have been a cache thing in 
ActiveRecord that prevented the reordering.
But meanwhile, I tested on our whole CI, and it took twice the normal 
time with updates to shuffle DB :(


Well there extra operations so that is to be expected. The question is 
whether everything needs to be shuffled? It would seem you only need to 
do that for those tests that are expecting a set order. I went back and 
read your original post and am somewhat confused about that test. You said:


"Sometimes, people forgot to order their queries. Most of the time, 
there is no real problem on production"


If order is not an issue in the production code why test for it in the 
test code?




For the union, I speak about production code like this:
"select count(*) from (#{directory_doctors_query_sql} union all 
#{profiles_query_sql}) as doctors"

In the to_sql, we cannot detect that we will be injected into a union.
So I cannot blindly add the random in the to_sql method.


The below would not be affected by an order by in any case as the 
count() would be the same:


"select count(*) from (#{directory_doctors_query_sql} union all 
#{profiles_query_sql}) as doctors"


If you did want to use order by random() could you not just tack it on 
the end?:


"... as doctors order by random()"












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

Re: after restore the size of the database is increased

[Alexey Bashtanov]

Hi all,
this should be trivial, but if I dump and restore the very same
database the restored one is bigger than the original one.
I did vacuumed the database foo, then dumped and restored into bar,
and the latter, even when vacuumed, remains bigger then the original
one.
No other activity was running on the cluster.



I can also think of toast data rearranged differently after dump-restore,
accidentally in such a way that it's packed into pages more efficiently.

Not that the probability of such a behavior is very high though.

Best regards,
  Alexey

Running concurrent txns and measuring the timings in Postgres

[Souvik Bhattacherjee]

Hi,

Is there a standard procedure to execute two or more concurrent txns at the
same time? I understand that if we want to run concurrent txns, we need to
execute them from different psql sessions. But how do we make sure that
they begin execution almost at the same time.

Also, I'm interested in measuring the time taken across all executing txns,
i.e. the time from the start of the earliest txns till the end of the last
txn.

Best,
-SB

Sequential scan with composite type in primary key

[gtreguier]

Hello,

With this schema:
  CREATE TYPE item AS (
name text,
date date
  );
  CREATE TABLE item_comment (
item item,
user_id text,
comment text,
CONSTRAINT item_comment_pk PRIMARY KEY (item, user_id)
  );

And this query:
  EXPLAIN SELECT * FROM item_comment WHERE item = ('', '2019-07-24');
  -- OK: Bitmap Index Scan
Postgresql uses the primary key index.

But with this query:
EXPLAIN SELECT * FROM item_comment WHERE (item).name = '';
-- KO: Seq Scan
Postgresql does a full table scan.

Should I inline the composite type ?
  CREATE TABLE item_comment (
name text,
date date,
user_id text,
comment text,
CONSTRAINT item_comment_pk PRIMARY KEY (name, date, user_id)
  );
Or is there a better way (without creating another index) ?

Thanks.

Re: Running concurrent txns and measuring the timings in Postgres

[Adrian Klaver]

On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:

Hi,

Is there a standard procedure to execute two or more concurrent txns at 
the same time? I understand that if we want to run concurrent txns, we 
need to execute them from different psql sessions. But how do we make 
sure that they begin execution almost at the same time.


Well different sessions be they psql or some other client. That would be 
the difficulty, determining what is submitting the transaction.




Also, I'm interested in measuring the time taken across all executing 
txns, i.e. the time from the start of the earliest txns till the end of 
the last txn.


It would help to know what problem you are trying to solve?



Best,
-SB



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

Re: Too slow to create new schema and their tables, functions, triggers.

[PegoraroF10]

I did not have vacuumed or reindexed my database for last 30 days and that
was my problem.
It works fine if I do a reindex database before adding that new schema. 
Well, I´ll try just reindexing system before adding a new schema to see if
it works.





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Too slow to create new schema and their tables, functions, triggers.

[Adrian Klaver]

On 7/24/19 11:33 AM, PegoraroF10 wrote:

I did not have vacuumed or reindexed my database for last 30 days and that
was my problem.


Autovacuum should be dealing with this.

Do you have it throttled in some manner?


It works fine if I do a reindex database before adding that new schema.
Well, I´ll try just reindexing system before adding a new schema to see if
it works.





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html






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

Re: Running concurrent txns and measuring the timings in Postgres

[Souvik Bhattacherjee]

> It would help to know what problem you are trying to solve?

Multiple txns are inserting tuples into a table concurrently. Wanted to
measure
the total time taken to complete the insertion process. Some txns overlap
with
others on the tuples they insert. Duplicate tuples are not inserted.

On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver 
wrote:

> On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:
> > Hi,
> >
> > Is there a standard procedure to execute two or more concurrent txns at
> > the same time? I understand that if we want to run concurrent txns, we
> > need to execute them from different psql sessions. But how do we make
> > sure that they begin execution almost at the same time.
>
> Well different sessions be they psql or some other client. That would be
> the difficulty, determining what is submitting the transaction.
>
> >
> > Also, I'm interested in measuring the time taken across all executing
> > txns, i.e. the time from the start of the earliest txns till the end of
> > the last txn.
>
> It would help to know what problem you are trying to solve?
>
> >
> > Best,
> > -SB
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: partition table slow planning

[Imre Samu]

> PostgreSQL 11.3 ...   Total number of partition is 367  Partition
key: LIST

As I know:
in PG11 "Declarative Partitioning Best Practices"
*... " The query planner is generally able to handle partition hierarchies
with up to a few hundred partitions fairly well, provided that typical
queries allow the query planner to prune all but a small number of
partitions. Planning times become longer and memory consumption becomes
higher as more partitions are added." *
*... **"in this case, it may be better to choose to partition by HASH and
choose a reasonable number of partitions rather than trying to partition by
LIST"  *
*... "Never assume that more partitions are better than fewer partitions
and vice-versa."*

https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES


In PG12 - it is more optimal:

*Changes:  "Improve performance of many operations on partitioned tables
(Amit Langote, David Rowley, Tom Lane, Álvaro Herrera) Allow tables with
thousands of child partitions to be processed efficiently by operations
that only affect a small number of partitions."  *
https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.5
see more:
https://www.postgresql.org/message-id/flat/9d7c5112-cb99-6a47-d3be-cf1ee6862...@lab.ntt.co.jp


Imre





Jatinder Sandhu  ezt írta (időpont:
2019. júl. 24., Sze, 16:40):

>  PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit'
> Total number of partition is 367
>
> Parent table defination
>
>Table "public.itinerary"
>   Column   |Type | Collation | Nullable |
> Default
>
> ---+-+---+--+-
>  flight_query  | character varying(50)   |   | not null |
>  origin| character varying(5)|   | not null |
>  destination   | character varying(5)|   | not null |
>  departure_date| character varying(10)   |   | not null |
>  month_day | integer |   | not null |
>  journeys  | character varying(10485760) |   | not null |
>  origin_metro  | character varying(5)|   |  |
>  destination_metro | character varying(5)|   |  |
> Partition key: LIST (month_day)
>
>
>
> On Wed, Jul 24, 2019 at 5:16 AM Imre Samu  wrote:
>
>> >*Can we know why this is happening?*
>>
>> Please give us - more info about your system:
>> - PG version?
>> - number of partitions?
>> - any other important?
>>
>> for example - in PG 11.2 Changes:
>> "Improve planning speed for large inheritance or partitioning table
>> groups (Amit Langote, Etsuro Fujita)"
>> https://www.postgresql.org/docs/current/release-11-2.html
>>
>> Imre
>>
>> Jatinder Sandhu  ezt írta (időpont:
>> 2019. júl. 24., Sze, 9:22):
>>
>>>
>>>
>>> We encounter a issue when we do query on partition table directly with
>>> proper partition key provide. postgres able to find problem partition but
>>> when I do explain plan it showing 95% spend on planning the execution .
>>> Here is example
>>> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination
>>> ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
>>> itinerary-# ;
>>>
>>>   QUERY PLAN
>>>
>>> ---
>>>  Append  (cost=0.29..13.79 rows=11 width=1024) (actual time=0.033..0.037
>>> rows=1 loops=1)
>>>->  Index Scan using itinerary_101_destination_departure_date_idx on
>>> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
>>> time=0.033..0.036 rows=1 loops=1)
>>>  Index Cond: (((destination)::text = 'GRJ'::text) AND
>>> ((departure_date)::text = '2020-01-01'::text))
>>>  Filter: (month_day = 101)
>>>
>>> * Planning Time: 51.677 ms* Execution Time: 0.086 ms
>>>
>>>
>>> When  I do query on directly on the partition table it is quite fast
>>> itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE
>>> destination ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
>>> itinerary-# ;
>>>
>>>QUERY PLAN
>>>
>>> -
>>>  Index Scan using itinerary_101_destination_departure_date_idx on
>>> itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
>>> time=0.043..0.048 rows=1 loops=1)
>>>Index Cond: (((destination)::text = 'GRJ'::text) AND
>>> ((departure_date)::text = '2020-01-01'::text))
>>>Filter: (month_day = 101)
>>>
>>> * Planning Time: 0.191 ms* Execution Time: 0.074 ms
>>> (5 rows)
>>>
>>> itinerary=#
>>>
>>> *Can we know why this is happening?*
>>>
>>>
>>>
>>>
>>>
>>>
>
> --
> Jatinder

Re: partition table slow planning

[Jatinder Sandhu]

Thanks Imre
On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu,  wrote:

> > PostgreSQL 11.3 ...   Total number of partition is 367  Partition
> key: LIST
>
> As I know:
> in PG11 "Declarative Partitioning Best Practices"
> *... " The query planner is generally able to handle partition hierarchies
> with up to a few hundred partitions fairly well, provided that typical
> queries allow the query planner to prune all but a small number of
> partitions. Planning times become longer and memory consumption becomes
> higher as more partitions are added." *
> *... **"in this case, it may be better to choose to partition by HASH and
> choose a reasonable number of partitions rather than trying to partition by
> LIST"  *
> *... "Never assume that more partitions are better than fewer partitions
> and vice-versa."*
>
> https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES
>
>
> In PG12 - it is more optimal:
>
> *Changes:  "Improve performance of many operations on partitioned tables
> (Amit Langote, David Rowley, Tom Lane, Álvaro Herrera) Allow tables with
> thousands of child partitions to be processed efficiently by operations
> that only affect a small number of partitions."  *
> https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.5.5
> see more:
> https://www.postgresql.org/message-id/flat/9d7c5112-cb99-6a47-d3be-cf1ee6862...@lab.ntt.co.jp
>
>
> Imre
>
>
>
>
>
> Jatinder Sandhu  ezt írta (időpont:
> 2019. júl. 24., Sze, 16:40):
>
>>  PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
>> compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit'
>> Total number of partition is 367
>>
>> Parent table defination
>>
>>Table "public.itinerary"
>>   Column   |Type | Collation | Nullable |
>> Default
>>
>> ---+-+---+--+-
>>  flight_query  | character varying(50)   |   | not null |
>>  origin| character varying(5)|   | not null |
>>  destination   | character varying(5)|   | not null |
>>  departure_date| character varying(10)   |   | not null |
>>  month_day | integer |   | not null |
>>  journeys  | character varying(10485760) |   | not null |
>>  origin_metro  | character varying(5)|   |  |
>>  destination_metro | character varying(5)|   |  |
>> Partition key: LIST (month_day)
>>
>>
>>
>> On Wed, Jul 24, 2019 at 5:16 AM Imre Samu  wrote:
>>
>>> >*Can we know why this is happening?*
>>>
>>> Please give us - more info about your system:
>>> - PG version?
>>> - number of partitions?
>>> - any other important?
>>>
>>> for example - in PG 11.2 Changes:
>>> "Improve planning speed for large inheritance or partitioning table
>>> groups (Amit Langote, Etsuro Fujita)"
>>> https://www.postgresql.org/docs/current/release-11-2.html
>>>
>>> Imre
>>>
>>> Jatinder Sandhu  ezt írta (időpont:
>>> 2019. júl. 24., Sze, 9:22):
>>>


 We encounter a issue when we do query on partition table directly with
 proper partition key provide. postgres able to find problem partition but
 when I do explain plan it showing 95% spend on planning the execution .
 Here is example
 itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destination
 ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
 itinerary-# ;

   QUERY PLAN

 ---
  Append  (cost=0.29..13.79 rows=11 width=1024) (actual
 time=0.033..0.037 rows=1 loops=1)
->  Index Scan using itinerary_101_destination_departure_date_idx on
 itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
 time=0.033..0.036 rows=1 loops=1)
  Index Cond: (((destination)::text = 'GRJ'::text) AND
 ((departure_date)::text = '2020-01-01'::text))
  Filter: (month_day = 101)

 * Planning Time: 51.677 ms* Execution Time: 0.086 ms


 When  I do query on directly on the partition table it is quite fast
 itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary_101 WHERE
 destination ='GRJ' AND departure_date = '2020-01-01' AND month_day = 101
 itinerary-# ;

QUERY PLAN

 -
  Index Scan using itinerary_101_destination_departure_date_idx on
 itinerary_101  (cost=0.29..13.73 rows=11 width=1024) (actual
 time=0.043..0.048 rows=1 loops=1)
Index Cond: (((destination)::text = 'GRJ'::text) AND
 ((departure_date)::text = '2020-01-01'::text))
>

Re: Running concurrent txns and measuring the timings in Postgres

[Rob Sargent]

> On Jul 24, 2019, at 1:22 PM, Souvik Bhattacherjee  wrote:
> 
> > It would help to know what problem you are trying to solve?
> 
> Multiple txns are inserting tuples into a table concurrently. Wanted to 
> measure 
> the total time taken to complete the insertion process. Some txns overlap 
> with 
> others on the tuples they insert. Duplicate tuples are not inserted.
> -- 
> adrian.kla...@aklaver.com 
Start both/all clients at approximately the same time, each firing a 
transaction at some believable interval. (Or separate threads with separate db 
connections.)  This should generate the concurrency load I think you’re looking 
for. You can easily time the iteration; actual details on server side would 
likely involve turning on maximum logging, with client identifier, and 
analyzing the logs.

Re: Running concurrent txns and measuring the timings in Postgres

[Adrian Klaver]

On 7/24/19 12:22 PM, Souvik Bhattacherjee wrote:

 > It would help to know what problem you are trying to solve?

Multiple txns are inserting tuples into a table concurrently. Wanted to 
measure
the total time taken to complete the insertion process. Some txns 
overlap with

others on the tuples they insert. Duplicate tuples are not inserted.


The duplicate elimination is being handled by ON CONFLICT or some custom 
process in the code generating the transactions?


If the transactions are being created from a single app/script could you 
not just use 'timing' to mark the beginning of the transactions and the 
end and record that somewhere(db table and/or file)?




On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver > wrote:


On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:
 > Hi,
 >
 > Is there a standard procedure to execute two or more concurrent
txns at
 > the same time? I understand that if we want to run concurrent
txns, we
 > need to execute them from different psql sessions. But how do we
make
 > sure that they begin execution almost at the same time.

Well different sessions be they psql or some other client. That
would be
the difficulty, determining what is submitting the transaction.

 >
 > Also, I'm interested in measuring the time taken across all
executing
 > txns, i.e. the time from the start of the earliest txns till the
end of
 > the last txn.

It would help to know what problem you are trying to solve?

 >
 > Best,
 > -SB


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Default ordering option

[Peter Eisentraut]

On 2019-07-23 17:43, Cyril Champier wrote:
> In this documentation
> , it is said:
> 
> If sorting is not chosen, the rows will be returned in an
> unspecified order. The actual order in that case will depend on the
> scan and join plan types and the order on disk, but it must not be
> relied on.
> 
> 
> I would like to know if there is any way to change that to have a "real"
> random behaviour.

It might be an interesting exercise to implement this as a post-parsing
hook.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Running concurrent txns and measuring the timings in Postgres

[Souvik Bhattacherjee]

> The duplicate elimination is being handled by ON CONFLICT or some custom
> process in the code generating the transactions?

Yes, we used ON CONFLICT for that. Thanks btw.

> If the transactions are being created from a single app/script could you
> not just use 'timing' to mark the beginning of the transactions and the
> end and record that somewhere(db table and/or file)?

So did you mean to say that I need to get the timestamps of the
beginning/end
of the txn since \timing only produces elapsed time?  Surely that would
solve the
problem but I'm not sure how to get that done in Postgres.

I wanted to check to see if there are simpler ways to get this done in
Postgres
before trying out something similar to Rob's suggestion or yours.

On Wed, Jul 24, 2019 at 4:12 PM Adrian Klaver 
wrote:

> On 7/24/19 12:22 PM, Souvik Bhattacherjee wrote:
> >  > It would help to know what problem you are trying to solve?
> >
> > Multiple txns are inserting tuples into a table concurrently. Wanted to
> > measure
> > the total time taken to complete the insertion process. Some txns
> > overlap with
> > others on the tuples they insert. Duplicate tuples are not inserted.
>
> The duplicate elimination is being handled by ON CONFLICT or some custom
> process in the code generating the transactions?
>
> If the transactions are being created from a single app/script could you
> not just use 'timing' to mark the beginning of the transactions and the
> end and record that somewhere(db table and/or file)?
>
> >
> > On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver  > > wrote:
> >
> > On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:
> >  > Hi,
> >  >
> >  > Is there a standard procedure to execute two or more concurrent
> > txns at
> >  > the same time? I understand that if we want to run concurrent
> > txns, we
> >  > need to execute them from different psql sessions. But how do we
> > make
> >  > sure that they begin execution almost at the same time.
> >
> > Well different sessions be they psql or some other client. That
> > would be
> > the difficulty, determining what is submitting the transaction.
> >
> >  >
> >  > Also, I'm interested in measuring the time taken across all
> > executing
> >  > txns, i.e. the time from the start of the earliest txns till the
> > end of
> >  > the last txn.
> >
> > It would help to know what problem you are trying to solve?
> >
> >  >
> >  > Best,
> >  > -SB
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Running concurrent txns and measuring the timings in Postgres

[Adrian Klaver]

On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:

 > The duplicate elimination is being handled by ON CONFLICT or some custom
 > process in the code generating the transactions?

Yes, we used ON CONFLICT for that. Thanks btw.

 > If the transactions are being created from a single app/script could you
 > not just use 'timing' to mark the beginning of the transactions and the
 > end and record that somewhere(db table and/or file)?

So did you mean to say that I need to get the timestamps of the 
beginning/end
of the txn since \timing only produces elapsed time?  Surely that would 
solve the

problem but I'm not sure how to get that done in Postgres.

I wanted to check to see if there are simpler ways to get this done in 
Postgres

before trying out something similar to Rob's suggestion or yours.



Well it depends on the part you have not filled in, what client(s) you 
are using and how the transactions are being generated?




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

Re: Running concurrent txns and measuring the timings in Postgres

[Souvik Bhattacherjee]

>Well it depends on the part you have not filled in, what client(s) you
> are using and how the transactions are being generated?

Using a psql client and txns are generated manually at this point. Each txn
is
stored separately in a .sql file and are fired from different psql
sessions, if that
helps.

On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver 
wrote:

> On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:
> >  > The duplicate elimination is being handled by ON CONFLICT or some
> custom
> >  > process in the code generating the transactions?
> >
> > Yes, we used ON CONFLICT for that. Thanks btw.
> >
> >  > If the transactions are being created from a single app/script could
> you
> >  > not just use 'timing' to mark the beginning of the transactions and
> the
> >  > end and record that somewhere(db table and/or file)?
> >
> > So did you mean to say that I need to get the timestamps of the
> > beginning/end
> > of the txn since \timing only produces elapsed time?  Surely that would
> > solve the
> > problem but I'm not sure how to get that done in Postgres.
> >
> > I wanted to check to see if there are simpler ways to get this done in
> > Postgres
> > before trying out something similar to Rob's suggestion or yours.
> >
>
> Well it depends on the part you have not filled in, what client(s) you
> are using and how the transactions are being generated?
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Running concurrent txns and measuring the timings in Postgres

[Adrian Klaver]

On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote:

 >Well it depends on the part you have not filled in, what client(s) you
 > are using and how the transactions are being generated?

Using a psql client and txns are generated manually at this point. Each 
txn is
stored separately in a .sql file and are fired from different psql 
sessions, if that

helps.



A quick demo:

psql -d production -U postgres -c "\timing" -c "select line_id, category 
 from avail_headers order by line_id;"


Timing is on.
Time: 0.710 ms

On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver > wrote:


On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:
 >  > The duplicate elimination is being handled by ON CONFLICT or
some custom
 >  > process in the code generating the transactions?
 >
 > Yes, we used ON CONFLICT for that. Thanks btw.
 >
 >  > If the transactions are being created from a single app/script
could you
 >  > not just use 'timing' to mark the beginning of the
transactions and the
 >  > end and record that somewhere(db table and/or file)?
 >
 > So did you mean to say that I need to get the timestamps of the
 > beginning/end
 > of the txn since \timing only produces elapsed time?  Surely that
would
 > solve the
 > problem but I'm not sure how to get that done in Postgres.
 >
 > I wanted to check to see if there are simpler ways to get this
done in
 > Postgres
 > before trying out something similar to Rob's suggestion or yours.
 >

Well it depends on the part you have not filled in, what client(s) you
are using and how the transactions are being generated?



-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Running concurrent txns and measuring the timings in Postgres

[Souvik Bhattacherjee]

I got this thing running and hopefully works as expected. The txns are
stored in insert_txn1.sql, insert_txn2.sql, ...
Please let me know if you find any issues with this.
Script is attached.



On Wed, Jul 24, 2019 at 5:11 PM Adrian Klaver 
wrote:

> On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote:
> >  >Well it depends on the part you have not filled in, what client(s) you
> >  > are using and how the transactions are being generated?
> >
> > Using a psql client and txns are generated manually at this point. Each
> > txn is
> > stored separately in a .sql file and are fired from different psql
> > sessions, if that
> > helps.
> >
>
> A quick demo:
>
> psql -d production -U postgres -c "\timing" -c "select line_id, category
>   from avail_headers order by line_id;"
>
> Timing is on.
> Time: 0.710 ms
>
> > On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver  > > wrote:
> >
> > On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:
> >  >  > The duplicate elimination is being handled by ON CONFLICT or
> > some custom
> >  >  > process in the code generating the transactions?
> >  >
> >  > Yes, we used ON CONFLICT for that. Thanks btw.
> >  >
> >  >  > If the transactions are being created from a single app/script
> > could you
> >  >  > not just use 'timing' to mark the beginning of the
> > transactions and the
> >  >  > end and record that somewhere(db table and/or file)?
> >  >
> >  > So did you mean to say that I need to get the timestamps of the
> >  > beginning/end
> >  > of the txn since \timing only produces elapsed time?  Surely that
> > would
> >  > solve the
> >  > problem but I'm not sure how to get that done in Postgres.
> >  >
> >  > I wanted to check to see if there are simpler ways to get this
> > done in
> >  > Postgres
> >  > before trying out something similar to Rob's suggestion or yours.
> >  >
> >
> > Well it depends on the part you have not filled in, what client(s)
> you
> > are using and how the transactions are being generated?
> >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


concur_txn_timing.sql
Description: application/sql

Re: Running concurrent txns and measuring the timings in Postgres

[Adrian Klaver]

On 7/24/19 2:24 PM, Souvik Bhattacherjee wrote:

I got this thing running and hopefully works as expected. The txns are
stored in insert_txn1.sql, insert_txn2.sql, ...
Please let me know if you find any issues with this.
Script is attached.




I'm hardly a BASH guru so someone else will need to comment, but on the 
surface it looks good to me.




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

rename table between schema with one command

[Alex]

for example we have table t1 under schema s1.  can I rename it to s2.t2
with one command.

currently I can do:

alter table s1.t1 set schema s2;
alter table s2.t1 rename to t2.

Re: Request for resolution || Support

[jay chauhan]

Hi Thomas, David/Team,

Thanks you for your response. However we need your confirmation whether my
Error/issue as mentioned below will be resolved if we upgrade our
PostgreSQL Version.

<>
*David response: *Use a newer version
*Tomas response:* Yeah, you should use release 11 for a new project.

My Issue while migrating procedure/function from Oracle to PostgreSQL:
Error-1)
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function
icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text)
line 486 at SQL statement
SQL state: 0A000
*David Response on it :* Rewrite your code as instructed

Error-2)
ERROR:  schema "utl_http" does not exist
LINE 38: L_HTTP_REQUEST UTL_HTTP.REQ;
^
SQL state: 3F000
Character: 1785
*Thomas response: *That's an Oracle thing for doing HTTP requests from
PL/SQL.  To do that from plpgsql you could try an extension like this one:
https://github.com/pramsey/pgsql-http
Or you could write your own function in Python or .  That's what I'd probably do.
https://www.postgresql.org/docs/11/plpython-funcs.html


*Need your urgent help.*

Regards,
Jay Chauhan
+918802766181


On Mon, Jul 22, 2019 at 3:53 AM Thomas Munro  wrote:

> On Mon, Jul 22, 2019 at 8:49 AM David G. Johnston
>  wrote:
> > On Sunday, July 21, 2019, jay chauhan  wrote:
> >> < x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat
> 4.8.5-4), 64-bit">>
> >
> > Use a newer version
>
> Yeah, you should use release 11 for a new project.
>
> https://www.postgresql.org/support/versioning/
>
> >> Error-2)
> >> ERROR:  schema "utl_http" does not exist
> >
> > Insufficient data provided to even guess
>
> That's an Oracle thing for doing HTTP requests from PL/SQL.  To do
> that from plpgsql you could try an extension like this one:
>
> https://github.com/pramsey/pgsql-http
>
> Or you could write your own function in Python or  favourite PL>.  That's what I'd probably do.
>
> https://www.postgresql.org/docs/11/plpython-funcs.html
>
> Google should help you find examples of people using plpython to
> access the various Python HTTP modules.
>
> --
> Thomas Munro
> https://enterprisedb.com
>

Re: Request for resolution || Support

[Guyren Howe]

Another option would be an app that is constantly connected to Postgres using 
LISTEN/NOTIFY.
On Jul 24, 2019, 22:34 -0700, jay chauhan , wrote:
> Hi Thomas, David/Team,
>
> Thanks you for your response. However we need your confirmation whether my 
> Error/issue as mentioned below will be resolved if we upgrade our PostgreSQL 
> Version.
>
> < compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit">>
> David response: Use a newer version
> Tomas response: Yeah, you should use release 11 for a new project.
>
> My Issue while migrating procedure/function from Oracle to PostgreSQL:
> Error-1)
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT:  PL/pgSQL function 
> icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 
> 486 at SQL statement
> SQL state: 0A000
> David Response on it : Rewrite your code as instructed
>
> Error-2)
> ERROR:  schema "utl_http" does not exist
> LINE 38:     L_HTTP_REQUEST UTL_HTTP.REQ;
>                             ^
> SQL state: 3F000
> Character: 1785
> Thomas response: That's an Oracle thing for doing HTTP requests from PL/SQL.  
> To do that from plpgsql you could try an extension like this one:
> https://github.com/pramsey/pgsql-http
> Or you could write your own function in Python or  favourite PL>.  That's what I'd probably do.
> https://www.postgresql.org/docs/11/plpython-funcs.html
>
>
> Need your urgent help.
>
> Regards,
> Jay Chauhan
> +918802766181
>
>
> > On Mon, Jul 22, 2019 at 3:53 AM Thomas Munro  wrote:
> > > On Mon, Jul 22, 2019 at 8:49 AM David G. Johnston
> > >  wrote:
> > > > On Sunday, July 21, 2019, jay chauhan  wrote:
> > > >> < > > >> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red 
> > > >> Hat 4.8.5-4), 64-bit">>
> > > >
> > > > Use a newer version
> > >
> > > Yeah, you should use release 11 for a new project.
> > >
> > > https://www.postgresql.org/support/versioning/
> > >
> > > >> Error-2)
> > > >> ERROR:  schema "utl_http" does not exist
> > > >
> > > > Insufficient data provided to even guess
> > >
> > > That's an Oracle thing for doing HTTP requests from PL/SQL.  To do
> > > that from plpgsql you could try an extension like this one:
> > >
> > > https://github.com/pramsey/pgsql-http
> > >
> > > Or you could write your own function in Python or  > > favourite PL>.  That's what I'd probably do.
> > >
> > > https://www.postgresql.org/docs/11/plpython-funcs.html
> > >
> > > Google should help you find examples of people using plpython to
> > > access the various Python HTTP modules.
> > >
> > > --
> > > Thomas Munro
> > > https://enterprisedb.com

Re: rename table between schema with one command

[David G. Johnston]

On Wednesday, July 24, 2019, Alex  wrote:

> for example we have table t1 under schema s1.  can I rename it to s2.t2
> with one command.
>
> currently I can do:
>
> alter table s1.t1 set schema s2;
> alter table s2.t1 rename to t2.
>

No.  AFAIK alter table is the obly sql command that can do those things and
the syntax does not provide a way to combine the two into a single
executable command.

Out of curosity, why do you ask?

David J.