Re: Parallel Btree index scan

[Rakesh Kumar]

> And what is the difference between index scan and index only scan.

Index scan:  Index lookup for key access plus read from data blocks to satisfy 
the query.

Index only scan: Index lookup for key access plus read from index pages itself 
to satisfy query. In other words, entire query can
be satisfied by index without bothering to go into data pages.  For eg: select 
count(*) from table where col1 = 123. Assuming col1
 is indexed, there is no need to visit data blocks.

READ COMMITTED vs. index-only scans

[Jacek Kołodziej]

Hello everyone,

this is my first post here and I'm starting with asking a question about
data consistency between two consecutive SELECTs using PostgreSQL 9.6.

I'm sorry if that's something that was already discussed - I couldn't find
it either in archives, nor in _general internet_. If it is, I would
appreciate pointing it out.

I have an "append-only" events table - only INSERT and SELECT queries are
issued to it. It has an integer (sequence) ID as a primary key.

I'm issuing following two queries (within the same READ COMMITTED
transaction) to that table:
- first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1 -
I'm saving the result as a "max ID" for the second query
- second query (B): SELECT id, ... FROM events WHERE id > "min ID" AND id
<= "max ID" AND ...
  - I won't dig into what's "min ID" but you can assume it's at most 100
less than "max ID"

Concurrently, rows are being added to that table.

Please note that there's enough data in the index for the first query (A)
to perform an index-only scan. I'm not sure if that's relevant but "B"
query does an index scan because of other conditions I haven't mentioned
but still needs to fetch data from the table's heap.

Here's what happening to me: the "A" query occasionally (in my case: on the
order of tenths per day) returns an ID _higher_ than any ID present in
second query's result (other conditions I haven't specified do _not_ filter
any more rows than "id <= max ID") - as if some entries were visible for
the first query, but not for the second one. This is an inconsistency that
is very problematic for me.

All I can think of is that it might be caused by the index-only-scan and
READ COMMITTED transaction isolation level but from what I gather from
documentation, it should not be possible due to the use of visibility
map[0][1][2].

And yet it happens, likely for some other reason but I can't think of any.
I've tried switching transaction isolation level to REPEATABLE READ (on the
basis that it's an faulty phenomenon occurring during to some bug) but that
didn't help.

Where am I wrong? What am I missing? What information may I provide to help
with investigating this?

[0] https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html
[1] https://www.postgresql.org/docs/9.6/static/storage-vm.html
[2] https://wiki.postgresql.org/wiki/Index-only_scans

-- 
Kind regards,
Jacek Kołodziej
http://kolodziejj.info

Re: READ COMMITTED vs. index-only scans

[Melvin Davidson]

On Wed, Jan 17, 2018 at 8:08 AM, Jacek Kołodziej 
wrote:

> Hello everyone,
>
> this is my first post here and I'm starting with asking a question about
> data consistency between two consecutive SELECTs using PostgreSQL 9.6.
>
> I'm sorry if that's something that was already discussed - I couldn't find
> it either in archives, nor in _general internet_. If it is, I would
> appreciate pointing it out.
>
> I have an "append-only" events table - only INSERT and SELECT queries are
> issued to it. It has an integer (sequence) ID as a primary key.
>
> I'm issuing following two queries (within the same READ COMMITTED
> transaction) to that table:
> - first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1
> - I'm saving the result as a "max ID" for the second query
> - second query (B): SELECT id, ... FROM events WHERE id > "min ID" AND id
> <= "max ID" AND ...
>   - I won't dig into what's "min ID" but you can assume it's at most 100
> less than "max ID"
>
> Concurrently, rows are being added to that table.
>
> Please note that there's enough data in the index for the first query (A)
> to perform an index-only scan. I'm not sure if that's relevant but "B"
> query does an index scan because of other conditions I haven't mentioned
> but still needs to fetch data from the table's heap.
>
> Here's what happening to me: the "A" query occasionally (in my case: on
> the order of tenths per day) returns an ID _higher_ than any ID present in
> second query's result (other conditions I haven't specified do _not_ filter
> any more rows than "id <= max ID") - as if some entries were visible for
> the first query, but not for the second one. This is an inconsistency that
> is very problematic for me.
>
> All I can think of is that it might be caused by the index-only-scan and
> READ COMMITTED transaction isolation level but from what I gather from
> documentation, it should not be possible due to the use of visibility
> map[0][1][2].
>
> And yet it happens, likely for some other reason but I can't think of any.
> I've tried switching transaction isolation level to REPEATABLE READ (on the
> basis that it's an faulty phenomenon occurring during to some bug) but that
> didn't help.
>
> Where am I wrong? What am I missing? What information may I provide to
> help with investigating this?
>
> [0] https://www.postgresql.org/docs/9.6/static/indexes-index-
> only-scans.html
> [1] https://www.postgresql.org/docs/9.6/static/storage-vm.html
> [2] https://wiki.postgresql.org/wiki/Index-only_scans
>
> --
> Kind regards,
> Jacek Kołodziej
> http://kolodziejj.info
>


*>- first query (A): SELECT id FROM events ORDER BY events.id
 DESC LIMIT 1 - I'm saving the result as a "max ID" for
the second query*

*Just a suggestion. The first query is not really needed.*
*You can simply do:*

*second query (B): *







*SELECT id, ...   FROM events  WHERE id > MIN(ID)AND id <= MAX(ID)
AND ...*


*See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
  *

*MAX and MIN functions*



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: READ COMMITTED vs. index-only scans

[Laurenz Albe]

Melvin Davidson wrote:
> You can simply do:
> second query (B): 
> 
> SELECT id
> , ... 
>   FROM events 
>  WHERE id > MIN(ID) 
>AND id <= MAX(ID)
>AND ...

You probably meant to use a window function there:

  max(id) OVER ()
  min(id) OVER ()

Yours,
Laurenz Albe

Re: READ COMMITTED vs. index-only scans

[Francisco Olarte]

On Wed, Jan 17, 2018 at 3:30 PM, Melvin Davidson  wrote:

> Just a suggestion. The first query is not really needed.
> You can simply do:
> second query (B):
>
> SELECT id
> , ...
>   FROM events
>  WHERE id > MIN(ID)
>AND id <= MAX(ID)
>AND ...
>
> See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
> MAX and MIN functions

Are you sure? ( http://sqlfiddle.com/#!17/7805a/3 )

In fact your link, in the first paragraph, points to
https://www.postgresql.org/docs/9.6/static/tutorial-agg.html which,
near the end ( 2nd paragraph from the end, I think its called next to
last in English, but not sure if penultimate is the correct word, like
in Spanish ), states:

"Thus, the WHERE clause must not contain aggregate functions; it makes
no sense to try to use an aggregate to determine which rows will be
inputs to the aggregates."

Francisco Olarte.

Re: READ COMMITTED vs. index-only scans

[Jacek Kołodziej]

17.01.2018 3:30 PM "Melvin Davidson"  napisał(a):



On Wed, Jan 17, 2018 at 8:08 AM, Jacek Kołodziej 
wrote:

> Hello everyone,
>
> this is my first post here and I'm starting with asking a question about
> data consistency between two consecutive SELECTs using PostgreSQL 9.6.
>
> I'm sorry if that's something that was already discussed - I couldn't find
> it either in archives, nor in _general internet_. If it is, I would
> appreciate pointing it out.
>
> I have an "append-only" events table - only INSERT and SELECT queries are
> issued to it. It has an integer (sequence) ID as a primary key.
>
> I'm issuing following two queries (within the same READ COMMITTED
> transaction) to that table:
> - first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1
> - I'm saving the result as a "max ID" for the second query
> - second query (B): SELECT id, ... FROM events WHERE id > "min ID" AND id
> <= "max ID" AND ...
>   - I won't dig into what's "min ID" but you can assume it's at most 100
> less than "max ID"
>
> Concurrently, rows are being added to that table.
>
> Please note that there's enough data in the index for the first query (A)
> to perform an index-only scan. I'm not sure if that's relevant but "B"
> query does an index scan because of other conditions I haven't mentioned
> but still needs to fetch data from the table's heap.
>
> Here's what happening to me: the "A" query occasionally (in my case: on
> the order of tenths per day) returns an ID _higher_ than any ID present in
> second query's result (other conditions I haven't specified do _not_ filter
> any more rows than "id <= max ID") - as if some entries were visible for
> the first query, but not for the second one. This is an inconsistency that
> is very problematic for me.
>
> All I can think of is that it might be caused by the index-only-scan and
> READ COMMITTED transaction isolation level but from what I gather from
> documentation, it should not be possible due to the use of visibility
> map[0][1][2].
>
> And yet it happens, likely for some other reason but I can't think of any.
> I've tried switching transaction isolation level to REPEATABLE READ (on the
> basis that it's an faulty phenomenon occurring during to some bug) but that
> didn't help.
>
> Where am I wrong? What am I missing? What information may I provide to
> help with investigating this?
>
> [0] https://www.postgresql.org/docs/9.6/static/indexes-index-onl
> y-scans.html
> [1] https://www.postgresql.org/docs/9.6/static/storage-vm.html
> [2] https://wiki.postgresql.org/wiki/Index-only_scans
>
> --
> Kind regards,
> Jacek Kołodziej
> http://kolodziejj.info
>


*>- first query (A): SELECT id FROM events ORDER BY events.id
 DESC LIMIT 1 - I'm saving the result as a "max ID" for
the second query*

*Just a suggestion. The first query is not really needed.*
*You can simply do:*

*second query (B): *







*SELECT id, ...   FROM events  WHERE id > MIN(ID)AND id <= MAX(ID)
AND ...*


*See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
  *

*MAX and MIN functions*


Hi Melvin, thank you for the suggestion. Unfortunately it won't do in my
case. Sorry for not providing enough context in the first place.

After making the first query (A), I'm iterating over that table (with LIMIT
100 and increasing OFFSET) - using a query "B" - until another condition is
met; overall, code is supposed to gather a number of rows from the table.
I'm also using the "max ID" for another purpose.



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start

[Brysounds]

Troy,

I am trying to get a windows instance up and running with pgaudit. my
question to you is how did you create the dll for pgaudit? I just cant seem
to create the extension.

Thanks



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

Re: READ COMMITTED vs. index-only scans

[Melvin Davidson]

On Wed, Jan 17, 2018 at 10:45 AM, Jacek Kołodziej 
wrote:

>
>
> 17.01.2018 3:30 PM "Melvin Davidson"  napisał(a):
>
>
>
> On Wed, Jan 17, 2018 at 8:08 AM, Jacek Kołodziej 
> wrote:
>
>> Hello everyone,
>>
>> this is my first post here and I'm starting with asking a question about
>> data consistency between two consecutive SELECTs using PostgreSQL 9.6.
>>
>> I'm sorry if that's something that was already discussed - I couldn't
>> find it either in archives, nor in _general internet_. If it is, I would
>> appreciate pointing it out.
>>
>> I have an "append-only" events table - only INSERT and SELECT queries are
>> issued to it. It has an integer (sequence) ID as a primary key.
>>
>> I'm issuing following two queries (within the same READ COMMITTED
>> transaction) to that table:
>> - first query (A): SELECT id FROM events ORDER BY events.id DESC LIMIT 1
>> - I'm saving the result as a "max ID" for the second query
>> - second query (B): SELECT id, ... FROM events WHERE id > "min ID" AND id
>> <= "max ID" AND ...
>>   - I won't dig into what's "min ID" but you can assume it's at most 100
>> less than "max ID"
>>
>> Concurrently, rows are being added to that table.
>>
>> Please note that there's enough data in the index for the first query (A)
>> to perform an index-only scan. I'm not sure if that's relevant but "B"
>> query does an index scan because of other conditions I haven't mentioned
>> but still needs to fetch data from the table's heap.
>>
>> Here's what happening to me: the "A" query occasionally (in my case: on
>> the order of tenths per day) returns an ID _higher_ than any ID present in
>> second query's result (other conditions I haven't specified do _not_ filter
>> any more rows than "id <= max ID") - as if some entries were visible for
>> the first query, but not for the second one. This is an inconsistency that
>> is very problematic for me.
>>
>> All I can think of is that it might be caused by the index-only-scan and
>> READ COMMITTED transaction isolation level but from what I gather from
>> documentation, it should not be possible due to the use of visibility
>> map[0][1][2].
>>
>> And yet it happens, likely for some other reason but I can't think of
>> any. I've tried switching transaction isolation level to REPEATABLE READ
>> (on the basis that it's an faulty phenomenon occurring during to some bug)
>> but that didn't help.
>>
>> Where am I wrong? What am I missing? What information may I provide to
>> help with investigating this?
>>
>> [0] https://www.postgresql.org/docs/9.6/static/indexes-index-onl
>> y-scans.html
>> [1] https://www.postgresql.org/docs/9.6/static/storage-vm.html
>> [2] https://wiki.postgresql.org/wiki/Index-only_scans
>>
>> --
>> Kind regards,
>> Jacek Kołodziej
>> http://kolodziejj.info
>>
>
>
> *>- first query (A): SELECT id FROM events ORDER BY events.id
>  DESC LIMIT 1 - I'm saving the result as a "max ID" for
> the second query*
>
> *Just a suggestion. The first query is not really needed.*
> *You can simply do:*
>
> *second query (B): *
>
>
>
>
>
>
>
> *SELECT id, ...   FROM events  WHERE id > MIN(ID)AND id <= MAX(ID)
> AND ...*
>
>
> *See https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
>   *
>
> *MAX and MIN functions*
>
>
> Hi Melvin, thank you for the suggestion. Unfortunately it won't do in my
> case. Sorry for not providing enough context in the first place.
>
> After making the first query (A), I'm iterating over that table (with
> LIMIT 100 and increasing OFFSET) - using a query "B" - until another
> condition is met; overall, code is supposed to gather a number of rows from
> the table. I'm also using the "max ID" for another purpose.
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
>
*Oops, my bad. I didn't have my morning coffee yet.*

*Try this instead:*

*WITH minmax AS*
*  (SELECT MIN(id) as min_id,*
*   MAX(id AS max_id)*

* FROM events *

* ) *


*SELECT id, ...   FROM events e,*

*  minmax m*



* WHERE e.id  > m.min_id   AND e.id  <=
m.max_id   AND ...*



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Tom Lane]

a...@novozymes.com (Adam =?utf-8?Q?Sj=C3=B8gren?=) writes:
> Also, the error we are getting is now: "unexpected chunk number 2
> (expected 3) for toast value 1498303849 in pg_toast_10919630", where
> previously we've only seen "unexpected chunk number 0 (expected 1)".

> We are kind of at a loss, so any suggestions on what we could try are
> welcome.

The basic thrust of these messages is "I'm reading what should be
sequentially numbered data chunks for this toast OID, and the sequence
numbers are wrong".  Both of these instances could be explained by
duplicate toast rows (or duplicate index entries pointing at one row),
though of course that would just move to the next question of how it
got that way.  Anyway, you could move the investigation along with
some manual checking into what's in that toast table.  For instance

select chunk_id, chunk_seq, ctid, xmin, xmax, length(chunk_data)
  from pg_toast.pg_toast_10919630
  where chunk_id = 1498303849
  order by 1,2;

might be informative.  If you do see what seem to be duplicate
chunk_seq values, checking whether they're still there in a
seqscan would be good.

regards, tom lane

Re: Extra files in "base" dir not seen in relfilenodes

[Alvaro Herrera]

Tom Lane wrote:

> However, assuming you've gotten that detail right, then any file
> you can't match up with a relfilenode value must be an orphan you
> can just "rm".

Maybe look in pg_buffercache for entries referencing those files before
deleting.  It would be surprising to see any if no catalog points to
those files, but who knows ...  If you break checkpointing, you're not
going to be pleased.

If it turns out that a shared buffer exists for any of those files, what
would be a way to evict them without pain?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Extra files in "base" dir not seen in relfilenodes

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> However, assuming you've gotten that detail right, then any file
>> you can't match up with a relfilenode value must be an orphan you
>> can just "rm".

> Maybe look in pg_buffercache for entries referencing those files before
> deleting.  It would be surprising to see any if no catalog points to
> those files, but who knows ...  If you break checkpointing, you're not
> going to be pleased.

Even if there are any such entries, surely they ought to be clean by
now and thus not a hazard.  Not that a manual "CHECKPOINT" might not
be a good idea.

regards, tom lane

Possible hang in 10.1 with JSON query over partially indexed partitions

[Paul Jones]

Version 10.1, Community version from PGDG repo
OS  RHEL 7.3

I may have discovered a situation in 10.1 where EXECUTEing a PREPARED
statement acting on JSON data in partitioned tables hangs in an
infinite loop for a particular set of data.  Unfortunately, the data is
proprietary, so I did the best I could below to describe what happened.

1) Partitioning is done with an (I hope) IMMUTABLE FUNC
2) Each partition has a partial index on IP address
3) The query looks for logins within a certain small time frame
   from the same IP

I compiled a debug version and took 3 backtraces.  When I first discovered
this, I allowed it to sit for 10-15 minutes before interrupting it.
Similar queries for different addresses return within seconds.

 Setup --

CREATE FUNCTION sesstime(tin JSONB)
RETURNS DATE
AS $$
SELECT (substring(tin->>'timestamp' from 1 for 8))::DATE;
$$ LANGUAGE SQL IMMUTABLE;

DROP TABLE sessparts;
CREATE TABLE sessparts
(
session JSONB
) PARTITION BY RANGE (sesstime(session));

CREATE TABLE sessparts_20171116
PARTITION OF sessparts
FOR VALUES FROM ('2017-11-16') TO ('2017-11-17');

CREATE TABLE sessparts_20171117
PARTITION OF sessparts
FOR VALUES FROM ('2017-11-17') TO ('2017-11-18');


.OTHER PARTITIONS ELIDED FOR BREVITY.


CREATE INDEX ON sessparts_20171116 (CAST(session->>'requestIP' AS INET))
WHERE session->>'requestIP' != ''
AND session->>'requestIP' != '0';

CREATE INDEX ON sessparts_20171117 (CAST(session->>'requestIP' AS INET))
WHERE session->>'requestIP' != ''
AND session->>'requestIP' != '0';


.OTHER PARTITIONS ELIDED FOR BREVITY.


PREPARE fanalq1(INET, TIMESTAMPTZ, INTERVAL) AS
SELECT inetad, aid, count(aid), stddev(td)
FROM (
SELECT $1, aid,
lid,
tstmp,
EXTRACT(EPOCH FROM (tstmp - lag(tstmp) OVER (ORDER BY tstmp)))
FROM (
SELECT session->>'authID' AUTHID,
session->>'loginID' LOGINID,
to_timestamp(session->>'timestamp', 'MMDDHH24MISS') 
tt
FROM sessparts
WHERE sesstime(session) >= ($2 - $3)::DATE
AND sesstime(session) <= $2::DATE
AND to_timestamp(session->>'timestamp', 
'MMDDHH24MISS')
<@ tstzrange($2 - $3, 
$2)
AND session->>'requestIP' != ''
AND session->>'requestIP' != '0'
AND (session->>'requestIP')::INET = $1
AND session->>'isAuthenticationSuccessful' = 'false'
) attempts(aid, lid, tstmp)
) tdiffs(inetad, aid, lid, ts, td)
GROUP BY aid, 1;

 Query --

sessions=# explain EXECUTE fanalq1('206.108.41.102', '2017-11-17 16:23:31-05', 
'60 seconds');



QUERY PLAN  

 
---
 GroupAggregate  (cost=1175004.52..1175004.72 rows=8 width=80)
   Group Key: tdiffs.aid, tdiffs.inetad
   ->  Sort  (cost=1175004.52..1175004.54 rows=8 width=72)
 Sort Key: tdiffs.aid, tdiffs.inetad
 ->  Subquery Scan on tdiffs  (cost=1175004.00..1175004.40 rows=8 
width=72)
   ->  WindowAgg  (cost=1175004.00..1175004.32 rows=8 width=112)
 ->  Sort  (cost=1175004.00..1175004.02 rows=8 width=1400)
   Sort Key: (to_timestamp((sessparts_20171110.session 
->> 'timestamp'::text), 'MMDDHH24MISS'::text))
   ->  Result  (cost=74.23..1175003.88 rows=8 
width=1400)
 ->  Append  (cost=74.23..1175003.76 rows=8 
width=1392)
   ->  Bitmap Heap Scan

Re: READ COMMITTED vs. index-only scans

[Tom Lane]

=?UTF-8?Q?Jacek_Ko=C5=82odziej?=  writes:
> Here's what happening to me: the "A" query occasionally (in my case: on the
> order of tenths per day) returns an ID _higher_ than any ID present in
> second query's result (other conditions I haven't specified do _not_ filter
> any more rows than "id <= max ID") - as if some entries were visible for
> the first query, but not for the second one. This is an inconsistency that
> is very problematic for me.

That sounds problematic to me too, but how certain are you that the "other
conditions you haven't specified" aren't suppressing the last row?  That'd
certainly be the least surprising explanation.  If it isn't that, though,
this surely seems like a bug.

Can you determine whether the row(s) missing in the second query are
freshly committed?  Or have they been there awhile?

> Where am I wrong? What am I missing? What information may I provide to help
> with investigating this?

Probably the best thing to spend time on would be to try to extract a
publishable test case.  It would be really hard to get to the bottom
of an issue like this without having a reproducer.  It's okay if it
takes awhile to reproduce the fault ...

Also, before spending a whole lot of time on this: are you on 9.6.6?
If not, update, just in case this is an already-fixed issue.  The
symptoms don't sound familiar, but I don't want to waste a lot of
time only to find out it's some manifestation of a known bug.

regards, tom lane

RE: READ COMMITTED vs. index-only scans

[Karen Stone]

Please remove me from this list.  Thanks.

Karen Stone| Technical Services| Eldorado |a Division of MphasiS 
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228 
Tel (928) 892 5735 | www.eldoinc.com | www.mphasis.com |kst...@eldocomp.com 


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Wednesday, January 17, 2018 11:56 AM
To: Jacek Kołodziej 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: READ COMMITTED vs. index-only scans

=?UTF-8?Q?Jacek_Ko=C5=82odziej?=  writes:
> Here's what happening to me: the "A" query occasionally (in my case: 
> on the order of tenths per day) returns an ID _higher_ than any ID 
> present in second query's result (other conditions I haven't specified 
> do _not_ filter any more rows than "id <= max ID") - as if some 
> entries were visible for the first query, but not for the second one. 
> This is an inconsistency that is very problematic for me.

That sounds problematic to me too, but how certain are you that the "other 
conditions you haven't specified" aren't suppressing the last row?  That'd 
certainly be the least surprising explanation.  If it isn't that, though, this 
surely seems like a bug.

Can you determine whether the row(s) missing in the second query are freshly 
committed?  Or have they been there awhile?

> Where am I wrong? What am I missing? What information may I provide to 
> help with investigating this?

Probably the best thing to spend time on would be to try to extract a 
publishable test case.  It would be really hard to get to the bottom of an 
issue like this without having a reproducer.  It's okay if it takes awhile to 
reproduce the fault ...

Also, before spending a whole lot of time on this: are you on 9.6.6?
If not, update, just in case this is an already-fixed issue.  The symptoms 
don't sound familiar, but I don't want to waste a lot of time only to find out 
it's some manifestation of a known bug.

regards, tom lane

Bad performance when inserting many data simultanously

[hmidi slim]

Hi,
I'm creating an application( microservice architecture using dokcer
containers) and I need to save a huge number of data in multiple tables in
the same time.
I have a table provider which has the different columns: name, status,
address, contact.
The table establishment contains: name, status, address, provider_id
(foreign key references to the table provider)
The table product contains: name, type,..., establishment_id(foreign key
references to the table establishment).
I have about 3 objects to insert and I'm trying to save them using
batchs ( I save 1000 objects at time).
The process of the insertion took about 10 minutes. So I'm facing a problem
of performance here and I want to know if the process of saving huge amount
of data in postgress took a lot of ressources in background? Does this due
to bad conception of the app?

Re: READ COMMITTED vs. index-only scans

[Jacek Kołodziej]

Hi Tom,

On Wed, Jan 17, 2018 at 7:56 PM, Tom Lane  wrote:

> =?UTF-8?Q?Jacek_Ko=C5=82odziej?=  writes:
> > Here's what happening to me: the "A" query occasionally (in my case: on
> the
> > order of tenths per day) returns an ID _higher_ than any ID present in
> > second query's result (other conditions I haven't specified do _not_
> filter
> > any more rows than "id <= max ID") - as if some entries were visible for
> > the first query, but not for the second one. This is an inconsistency
> that
> > is very problematic for me.
>
> That sounds problematic to me too, but how certain are you that the "other
> conditions you haven't specified" aren't suppressing the last row?  That'd
> certainly be the least surprising explanation.  If it isn't that, though,
> this surely seems like a bug.
>
> Yes, I'm fairly sure of that. When I execute that same "B" query again
some time afterwards, it returns all expected rows - I mean, also these
that were "included" in original "A" query and that were "missing" in "B"
one first time around.


> Can you determine whether the row(s) missing in the second query are
> freshly committed?  Or have they been there awhile?
>
> Depends on what would be considered "fresh", usually it's on the order of
miliseconds or seconds.


> > Where am I wrong? What am I missing? What information may I provide to
> help
> > with investigating this?
>
> Probably the best thing to spend time on would be to try to extract a
> publishable test case.  It would be really hard to get to the bottom
> of an issue like this without having a reproducer.  It's okay if it
> takes awhile to reproduce the fault ...
>
> I'd certainly love to have a working repro. I won't be able to do it for
the next few days but I'll work on this right after the weekend.


> Also, before spending a whole lot of time on this: are you on 9.6.6?
> If not, update, just in case this is an already-fixed issue.  The
> symptoms don't sound familiar, but I don't want to waste a lot of
> time only to find out it's some manifestation of a known bug.
>
> regards, tom lane
>

I'm using 9.6.5; I'm not administrating it so it might take some time
before updating but once it's done, I'll get back with whether that fixed
the situation. In the meantime, when trying to reproduce it locally, I'll
use both 9.6.5 and 9.6.6 to see whether it makes any difference.

Thank you very much for the suggestions.


-- 
Kind regards,
Jacek Kołodziej
http://kolodziejj.info

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Michael Paquier]

On Wed, Jan 17, 2018 at 12:16:19PM -0500, Tom Lane wrote:
> The basic thrust of these messages is "I'm reading what should be
> sequentially numbered data chunks for this toast OID, and the sequence
> numbers are wrong".  Both of these instances could be explained by
> duplicate toast rows (or duplicate index entries pointing at one row),
> though of course that would just move to the next question of how it
> got that way.

Good point here. This could be a consequence of freeze-the-dead whose
fix will be available in the next round of minor releases.
--
Michael


signature.asc
Description: PGP signature