[GENERAL] Where to find development builds of pg for windows
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
Re: [GENERAL] Delete Duplicates with Using
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
Re: [GENERAL] time series data
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] time series data
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. >
[GENERAL] Non-overlapping updates blocking each other
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] Non-overlapping updates blocking each other
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.
Re: [GENERAL] Non-overlapping updates blocking each other
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
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
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
