Re: [GENERAL] Non-overlapping updates blocking each other

[Tom Lane]

Melvin Davidson  writes:
> On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer 
> wrote:
>> Since when does Postgres lock the whole table during an update?

> When the optimizer thinks it needs to do a TABLE SCAN!

Sorry, but that's nonsense.

More likely explanations for the OP's problem involve foreign key
constraints that cause two different row updates to need to lock
the same referenced row, or maybe he's using some index type that
has greater locking demands than a btree, or he's using serializable
mode and fell foul of one of its approximations as to which rows
the update depends on, or perhaps some other corner case.  We'd need
more info about the schema and the Postgres version to tell for sure.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Non-overlapping updates blocking each other

[Melvin Davidson]

On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer 
wrote:

> Melvin Davidson schrieb am 14.10.2017 um 17:32:
>
>> More than likely, the optimizer has determined that a table scan is best,
>> in which case it will use a table lock.
>> That means one updates will be blocking each other.
>>
>
> Since when does Postgres lock the whole table during an update?
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


>Since when does Postgres lock the whole table during an update?
When the optimizer thinks it needs to do a TABLE SCAN!

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

Re: [GENERAL] Non-overlapping updates blocking each other

[Thomas Kellerer]

Melvin Davidson schrieb am 14.10.2017 um 17:32:

More than likely, the optimizer has determined that a table scan is best, in 
which case it will use a table lock.
That means one updates will be blocking each other.


Since when does Postgres lock the whole table during an update?





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Non-overlapping updates blocking each other

[Melvin Davidson]

On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere  wrote:

> hi,
>
> I've got 2 updates on non-overlapping uuid (primary key) ranges. For
> example:
>
> UPDATE [...] WHERE id BETWEEN 'ff00----' AND
> 'ff0f----'
> and
> UPDATE [...] WHERE id BETWEEN 'f8c0----' AND
> 'f8ff----'
>
> Yet one blocks the other one. How is this possible?
>
> Thanks,
> Seamus
>
> --
> Seamus Abshere, SCEA
> https://www.faraday.io
> https://github.com/seamusabshere
> https://linkedin.com/in/seamusabshere
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Have you done and EXPLAIN on each of the updates?
More than likely, the optimizer has determined that a table scan is best,
in which case it will use a table lock.
That means one updates will be blocking each other.

You can also execute the following query and check the wait_event_type to
verify.

SELECT c.datname,
   c.pid as pid,
   c.client_addr,
   c.usename as user,
   c.query,
   c.wait_event,
   c.wait_event_type,
  l.pid as blocked_by,
   c.query_start,
   current_timestamp - c.query_start as duration
  FROM pg_stat_activity c
  LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
  LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
  LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
  LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
 WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
 query_start;

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

[GENERAL] Non-overlapping updates blocking each other

[Seamus Abshere]

hi,

I've got 2 updates on non-overlapping uuid (primary key) ranges. For
example:

UPDATE [...] WHERE id BETWEEN 'ff00----' AND
'ff0f----'
and
UPDATE [...] WHERE id BETWEEN 'f8c0----' AND
'f8ff----'

Yet one blocks the other one. How is this possible?

Thanks,
Seamus

--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] time series data

[Khalil Khamlichi]

Thanks, I'll check it out.

Sent via mobile, please forgive typos and brevity

On Oct 14, 2017 3:23 PM, "Joshua D. Drake"  wrote:

> On 10/01/2017 01:17 AM, Khalil Khamlichi wrote:
>
>> Hi everyone,
>>
>
> Take a look at TimescaleDB they have an extension to Postgres that makes
> this awesome (and yes its free and open source).
>
> jD
>
>
>> I have a data stream of a call center application coming in  to postgres
>> in this format :
>>
>> user_name, user_status, event_time
>>
>> 'user1', 'ready', '2017-01-01 10:00:00'
>> 'user1', 'talking', '2017-01-01 10:02:00'
>> 'user1', 'after_call', '2017-01-01 10:07:00'
>> 'user1', 'ready', '2017-01-01 10:08:00'
>> 'user1', 'talking', '2017-01-01 10:10:00'
>> 'user1', 'after_call', '2017-01-01 10:15:00'
>> 'user1', 'paused', '2017-01-01 10:20:00'
>> ...
>> ...
>>
>> so as you see each new insert of an "event" is in fact the start_time of
>> that event and also the end_time of the previous one so should be used to
>> calculate the duration of this previous one.
>>
>> What is the best way to get user_status statistics like total duration,
>> frequency, avg ...etc , does any body have an experience with this sort of
>> data streams ?
>>
>>
>> Thanks in advance.
>>
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>

Re: [GENERAL] time series data

[Joshua D. Drake]

On 10/01/2017 01:17 AM, Khalil Khamlichi wrote:

Hi everyone,


Take a look at TimescaleDB they have an extension to Postgres that makes 
this awesome (and yes its free and open source).


jD



I have a data stream of a call center application coming in  to postgres 
in this format :


user_name, user_status, event_time

'user1', 'ready', '2017-01-01 10:00:00'
'user1', 'talking', '2017-01-01 10:02:00'
'user1', 'after_call', '2017-01-01 10:07:00'
'user1', 'ready', '2017-01-01 10:08:00'
'user1', 'talking', '2017-01-01 10:10:00'
'user1', 'after_call', '2017-01-01 10:15:00'
'user1', 'paused', '2017-01-01 10:20:00'
...
...

so as you see each new insert of an "event" is in fact the start_time of 
that event and also the end_time of the previous one so should be used 
to calculate the duration of this previous one.


What is the best way to get user_status statistics like total duration, 
frequency, avg ...etc , does any body have an experience with this sort 
of data streams ?



Thanks in advance.



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Delete Duplicates with Using

[legrand legrand]

DELETE FROM table_with_duplicates AS T1 USING table_with_duplicates AS T2
WHERE 
T1.column_1 = T2.column_1
AND T1.column_2 = T2.column_2
AND T1.column_3 = T2.column_3
AND T1.row_num < T2.row_num




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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Where to find development builds of pg for windows

[legrand legrand]

Hello,

Using PG mainly on windows, I would have liked to test new releases /
development versions before they are available in 
https://www.postgresql.org/download/windows/

Are there some build farms or places where I could find "build snapshots for
windows" without having to build them by myself ?

Regards
PAscal



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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Delete Duplicates with Using

[Igal @ Lucee.org]

Hello,

I run the SQL query below to delete duplicates from a table.  The 
subquery is used to identify the duplicated rows (row_num is a BIGSERIAL 
column).


/** delete older copies of duplicates */
DELETE FROM table_with_duplicatesAS T
WHERE row_num IN (
    SELECT     T1.row_num
    FROM    table_with_duplicates  AS T1
        JOIN table_with_duplicates AS T2
            ON         T1.column_1 = T2.column_1
                AND T1.column_2 = T2.column_2
                AND T1.column_3 = T2.column_3
                AND T1.row_num < T2.row_num
);

Can anyone tell me how to rewrite that query to use the USING clause and 
hopefully remove the subquery?


The documentation mentions USING but there is no example and the only 
examples I found online are very trivial.


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org