Re: [GENERAL] wal_retrieve_retry_interval

[Michael Paquier]

On Mon, May 29, 2017 at 3:58 PM, Ludovic Vaugeois-Pepin
 wrote:
> < 2017-05-29 22:42:17.026 CEST > DEBUG:  switched WAL source from
> archive to stream after failure
> ... (15 seconds later) ...
> < 2017-05-29 22:42:32.042 CEST > DEBUG:  switched WAL source from
> stream to archive after failure

Ludovic, is there some event happening between those two logs?
Something that could explain a longer delay is the time it takes for a
WAL receiver to be considered as started (see WALRCV_STARTUP_TIMEOUT).
-- 
Michael


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

[GENERAL] PGConf.ASIA and VISA

[Tatsuo Ishii]

Hi all,

The largest PostgreSQL conference in Japan "PGConf.ASIA" will be held
in December 4th to 6th this year.

http://www.pgconf.asia/EN/2017/

If you are planning to join and you need a visa to enter Japan, please
contact me as soon as possible. I am in charge of issuing invitation
letters etc. as one of the organizers of the conference.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] could not fdatasync log file: Input/output error

[Michael Paquier]

On Mon, Oct 16, 2017 at 11:47 PM, said assemlal  wrote:
> Just before we restart the server today, I found only one line as:
>
> PANIC:  could not fdatasync log file 000101760083: Input/output
> error
> the database system is in recovery mode

Ouch. I would not trust this host at this point, this looks like a
file system or a disk issue. Before doing anything you should stop the
database, and make a cold copy of the data folder on which you could
work on if you don't have a live backup. This wiki page is wise on the
matter:
http://wiki.postgresql.org/wiki/Corruption
-- 
Michael


-- 
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] REASSIGN OWNED simply doesn't work

[Alvaro Herrera]

David G. Johnston wrote:

> ​You could at least fix the documentation bug since this superuser-only
> restriction doesn't show up and is in fact contradicted by the sentence
> ​"REASSIGN OWNED requires privileges on both the source role(s) and the
> target role."  The error message that comes back seems like it could be
> improved as well.

alvherre=# create role owner1;
CREATE ROLE
alvherre=# create role owner2;
CREATE ROLE
alvherre=# create role sam login;
CREATE ROLE
alvherre=# grant owner1 to sam;
GRANT ROLE
alvherre=# grant owner2 to sam;
GRANT ROLE
alvherre=# set session authorization owner1;
SET
alvherre=> create table owner1_table ();
CREATE TABLE
alvherre=> \q
RESET

$ psql alvherre -U sam

alvherre=> reassign owned by owner1 to owner2;
REASSIGN OWNED
alvherre=> \d 
  Listado de relaciones
 Esquema │Nombre│ Tipo  │ Dueño  
─┼──┼───┼
 public  │ owner1_table │ tabla │ owner2


> The word "privileges" there seems odd too, wouldn't "membership" be more
> appropriate?
> 
> https://www.postgresql.org/docs/10/static/sql-reassign-owned.html

I can change that.

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


-- 
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] REASSIGN OWNED simply doesn't work

[Sam Gendler]

I wasn't under the impression that open source meant "totally unsupported,
undocumented, and fix it yourself if it's broken." If your attitude is
going to be "it's good enough for my needs so I'll just rudely dismiss any
problems with it," why merge it at all?

And for what it is worth, postgresql doesn't require superuser privilege to
drop a role. It only requires superuser to drop another superuser,
according to the documentation. And the documentation and every reference
to best practices specifically recommends NOT setting up production
databases so that ownership is by a superuser, and especially don't use
superuser for application and ad-hoc access to the db, so it seems entirely
likely that anyone following best practices would NOT be using REASSIGN
OWNED on a superuser except in the specific case of converting a db which
was originally owned and accessed by superuser to a db that has no
superuser requirements at all - that's actually what I am trying to do, but
in the specific use case of an RDS database, so even my 'master user' isn't
a postgres superuser. And if the owner isn't superuser, any user with
createrole can drop it - unless they need to reassign privileges, first,
since they'll have to reassign ownership item by item to do that unless
they want DROP OWNED to actually drop the objects rather than just removing
permissions, since drop owned actually drops the object if it is executed
on behalf of the owner. In short, it may meet your needs, but the
documentation is incorrect and the functionality is very much incomplete
regardless of your own needs.

Fundamentally, the REASSIGNED OWNED command is very useful, but only if it
works in contexts other than reassigning away from a superuser.  Anyone
wanting to manage a database while providing minimal privileges to
individual users is likely to require its use, eventually.  And if you do
decide to address that, there would be a very useful extension of existing
privilege assignment commands which would allow me to assign a privilege to
a role on every object for which some other role already has a privilege.
That would allow me to much more easily add a group of users to an existing
database - give the new group role all the same privileges as some other
group, then just modify the few spots where that role requires different
access.  I'm not sure what to call such a command, but I do know I'd find
it useful.  If a developer on my team has create access (in staging and
test, at least, if not in production), it is entirely possible that they
could accidentally create tables without remembering to elevate their role
first, in which case reassign owned will come in handy for non-superuser
roles - cleaning up their mistake.

--sam


On Fri, Oct 13, 2017 at 12:39 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Oct 13, 2017 at 6:04 AM, Alvaro Herrera 
> wrote:
>
>> Sam Gendler wrote:
>> > psql 9.6.3 on OS X.
>> >
>> > I'm dealing with a production database in which all db access has been
>> made
>> > by the same user - the db owner, which isn't actually a superuser
>> because
>> > the db runs on amazon RDS - amazon retains the superuser privilege for
>> its
>> > own users and makes non-superuser role with createrole and createdb
>> > privileges for use as the primary role by the AWS account.
>>
>> It's true that REASSIGN OWNED is limited to a very particular scenario.
>> It was written to support the specific case of wanting to drop a role,
>> and that can only be done by a superuser, so why would it matter that
>> REASSIGN OWNED itself could not be run by a superuser?
>>
>
> ​You could at least fix the documentation bug since this superuser-only
> restriction doesn't show up and is in fact contradicted by the sentence
> ​"REASSIGN OWNED requires privileges on both the source role(s) and the
> target role."  The error message that comes back seems like it could be
> improved as well.
>
> The word "privileges" there seems odd too, wouldn't "membership" be more
> appropriate?
>
> https://www.postgresql.org/docs/10/static/sql-reassign-owned.html
>
> David J.
>
>

Re: [GENERAL] time series data

[Khalil Khamlichi]

Thanks a lot Jeremy, we ended up integrating the code you provided
into our software (just before you patent it) :)

Best regards,

Kkh

On Tue, Oct 3, 2017 at 7:58 PM, Schneider  wrote:
> On Mon, Oct 2, 2017 at 10:27 AM, Khalil Khamlichi
>  wrote:
>> we have records like this
>>
>> ccdb1=# select user_name, agent_status, event_time from cc_events ;
>>
>>  user_name | agent_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:08:00
>>  user1 | ready| 2017-01-01 10:10:00
>>  user1 | talking  | 2017-01-01 10:12:00
>>  user1 | after_call   | 2017-01-01 10:15:00
>>  user1 | paused   | 2017-01-01 10:17:00
>>  user1 | ready| 2017-01-01 10:25:00
>>  user1 | talking  | 2017-01-01 10:26:00
>> (9 rows)
>>
>>
>> so user1 was READY at  2017-01-01 10:00:00 then he received a call
>> that he attended at 2017-01-01 10:02:00 and so on ...
>> so user1 was ready for 2 minutes, then he was talking for 6 minutes
>> then he was in after_call (doing after call work)  for 2 minutes and
>> this is the kind of information we want to query.
>>
>> my solution so far that I came with, is in my table I have 1 more
>> field : end_time
>> so when an event comes in and before the insert I do :
>> update cc_events set end_time = current_timestamp  where user_name =
>> 'user_of_event' and end_time is null;
>>
>> then I insert new event leaving the end_time as null so that next
>> event will update it and so on.
>>
>> its working fine, I have the start and end times for each event, its
>> not too painful to query (sum(end-start) while grouping by user_name,
>> agent_status), but its one more update on the table and also limited
>> in what you can query about,
>>
>> I know this must be a common problem in every software that deals with
>> events, so I suppose something is already built-in in postgres to deal
>> with it.
>
> Khalil, changing your schema is one solution with certain benefits -
> but it's definitely not necessary when you have the power of
> PostgreSQL at your fingertips.  You can solve your problem without
> changing anything at all.  :)
>
> All you need is a window function:
> https://www.postgresql.org/docs/9.6/static/tutorial-window.html
>
> Here's an example which I just now tested on 9.6.3, 9.5.7, 9.4.12 and
> 9.3.17 (all the latest minors currently supported on RDS).  You can
> try these queries on your own system; they should work anywhere.
>
> First, I added a second user to your data set to make sure we were
> handling that case correctly.
>
> ==
> create table cc_events (user_name varchar(8), agent_status
> varchar(20), event_time timestamp);
>
> insert into cc_events values
> ('user1', 'ready', '2017-01-01 10:00:00'),
> ('user1', 'talking', '2017-01-01 10:02:00'),
> ('user2', 'ready', '2017-01-01 10:04:00'),
> ('user2', 'talking', '2017-01-01 10:05: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'),
> ('user2', 'after_call', '2017-01-01 10:18:00'),
> ('user1', 'paused', '2017-01-01 10:20:00'),
> ('user2', 'paused', '2017-01-01 10:21:00');
>
> select * from cc_events order by user_name, event_time;
>
> ==
> here's a basic window function in action:
>
> ==
> select user_name, agent_status, event_time,
>   lead(event_time) over (partition by user_name order by event_time)
> next_event_time
> from cc_events order by event_time;
>
>  user_name | agent_status | event_time  |   next_event_time
> ---+--+-+-
>  user1 | ready| 2017-01-01 10:00:00 | 2017-01-01 10:02:00
>  user1 | talking  | 2017-01-01 10:02:00 | 2017-01-01 10:07:00
>  user2 | ready| 2017-01-01 10:04:00 | 2017-01-01 10:05:00
>  user2 | talking  | 2017-01-01 10:05:00 | 2017-01-01 10:18:00
>  user1 | after_call   | 2017-01-01 10:07:00 | 2017-01-01 10:08:00
>  user1 | ready| 2017-01-01 10:08:00 | 2017-01-01 10:10:00
>  user1 | talking  | 2017-01-01 10:10:00 | 2017-01-01 10:15:00
>  user1 | after_call   | 2017-01-01 10:15:00 | 2017-01-01 10:20:00
>  user2 | after_call   | 2017-01-01 10:18:00 | 2017-01-01 10:21:00
>  user1 | paused   | 2017-01-01 10:20:00 |
>  user2 | paused   | 2017-01-01 10:21:00 |
>
> ==
> and now we just add one more column which does the subtraction to
> calculate the duration:
>
> ==
> select user_name, agent_status, event_time,
>   lead(event_time) over (partition by user_name order by event_time)
> next_event_time,
>   (lead(event_time) over (partition by user_name order by event_time))
> - event_time as duration
> from cc_events 

Re: [GENERAL] Delete Duplicates with Using

[Igal @ Lucee.org]

FYI,

On 10/16/2017 8:58 AM, Igal @ Lucee.org wrote:




test=*# with keep as (select max(ctid) as ctid from dubletten group 
by c1,c2,c3) delete from dubletten where ctid not in (select ctid 
from keep);




I like this solution, but would using a subquery be much slower than 
the implicit join of `using`?  My tables are not big in 
Postgres-standards, so it's probably not an issue, but I'm trying to 
learn as much as I can about Postgres now that I'm getting ready to 
move it to production.


I was able to compare the performance on a table with about 350k rows, 
with duplicates over 7 columns and no indices.


The GROUP BY solution with the subquery (though I was using a simpler 
version of it without a CTE), was taking over 30 seconds so I killed the 
request.


I then ran the USING version which completed in 16 seconds and deleted 
39 rows.


Best,

Igal Sapir
Lucee Core Developer
Lucee.org 

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

[Seamus Abshere]

> > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> > I'd bet on the last one, especially since you found that the problem
> > was a page-level lock.  Did you look to see which relation the page
> > lock was in?

On Mon, Oct 16, 2017, at 12:34 PM, Seamus Abshere wrote:
> The specific relation varies, but it appears to always be compound GIN
> index on (jsonb, text)

This is definitely GIN fastupdate. I turned off fastupdate and the
blocks go away. I have a feeling, however, that my UPDATEs will actually
get slower (testing now).

I'm most interested in the fastest UPDATEs possible, even if reads
suffer or similar UPDATEs take wildly different amounts of time.

Should I crank maintenance_work_mem and gin_pending_list_limit  way up,
and autovacuum thresholds way down?


-- 
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

[Igal @ Lucee.org]

Andreas,

On 10/15/2017 11:53 PM, Andreas Kretschmer wrote:
other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) 
are identical)


test=*# select * from dubletten ;
 c1 | c2 | c3
++
  1 |  1 |  1
  1 |  1 |  1
  1 |  2 |  3
  2 |  3 |  4
  3 |  4 |  5
  4 |  5 |  5
  5 |  5 |  5
  5 |  5 |  5
(8 Zeilen)

test=*# with keep as (select max(ctid) as ctid from dubletten group by 
c1,c2,c3) delete from dubletten where ctid not in (select ctid from 
keep);;

DELETE 2
test=*# select * from dubletten ;
 c1 | c2 | c3
++
  1 |  1 |  1
  1 |  2 |  3
  2 |  3 |  4
  3 |  4 |  5
  4 |  5 |  5
  5 |  5 |  5
(6 Zeilen)

test=*#

Regards, Andreas



I like this solution, but would using a subquery be much slower than the 
implicit join of `using`?  My tables are not big in Postgres-standards, 
so it's probably not an issue, but I'm trying to learn as much as I can 
about Postgres now that I'm getting ready to move it to production.


Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org 

[GENERAL] ORDER with CASE and Random for each case

[Alex Magnum]

Hi,

If have a view that I would like to sort where I divide the return in 3
different groups.
These 3 groups then should have a random sort order each.
As I am I using it with an offset, and limit, the randomness should be the
same.

For example:
SELECT user_id, age  FROM view_users
ORDER BY CASE WHEN age < 20 THEN 1
  WHEN age < 50 THEN 2
  ELSE 3
 END
OFFSET 0  LIMIT 20;

If I have for each age group 30 users. I want these 3 groups to be ordered
randomly but during the paging maintain the order.

The way I would do it now is to use setseed() and a union of 3 selects with
the 3 conditions and random() in each of the three.

Is there a better and more efficient way to do it in one query?

Thanks for any suggestions

A

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

[Seamus Abshere]

> Seamus Abshere  writes:
> > * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...

> On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> I'd bet on the last one, especially since you found that the problem
> was a page-level lock.  Did you look to see which relation the page
> lock was in?

The specific relation varies, but it appears to always be compound GIN
index on (jsonb, text)

Can I like decrease the fillfactor or something to make these
"collisions" less frequent?


-- 
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] Using Substitution Variables In PostgreSQL

[Osahon Oduware]

Thanks for the information.

On Mon, Oct 16, 2017 at 3:27 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware 
> wrote:
>
>> Hi All,
>>
>> I wanted to find out how to use a substitution variable in an SQL
>> statement that would cause the user to be prompted for a value. Something
>> similar to the ampersand (&&) in ORACLE.
>>
>> For example, given the SQL statement below:
>> SELECT ,,
>> FROM 
>> WHERE  = 35
>>
>> I want the user to be prompted for the value in the WHERE (filter)
>> clause, e.g.
>> SELECT ,,
>> FROM 
>> WHERE  = ?
>>
>> I would be glad if someone could point me in the right direction.
>>
>
> ​That would be a client-side feature.  The only client supported on this
> list is psql.  psql does not have this capability.  It does have the
> "\prompt" ​meta-command which will serve in this specific case.
>
> \prompt 'Enter a value for varname' varname
>
> SELECT ... WHERE  = :'varname';
>
> https://www.postgresql.org/docs/10/static/app-psql.html
>
> David J.
>
>

Re: [GENERAL] could not fdatasync log file: Input/output error

[said assemlal]

Just before we restart the server today, I found only one line as:

PANIC:  could not fdatasync log file 000101760083: Input/output
error
the database system is in recovery mode


On Mon, Oct 16, 2017 at 10:43 AM said assemlal 
wrote:

> Hi,
>
> The postgresql crashed on friday due to IO errors. It seems that the
> filesystem puked.
>
> PANIC: could not fdatasync log file 000101760077: Input/output
> error
> LOG: database system was interrupted; last known up at 2017-10-13 15:26:28
> EDT
> WARNING: terminating connection because of crash of another server process
> FATAL: the database system is in recovery mode
>
> Some errors from the kernel given by the sysadmin:
>
> Buffer I/O error on device dm-3, logical block 2900794
> lost page write due to I/O error on dm-3
> Buffer I/O error on device dm-3, logical block 2900795
> lost page write due to I/O error on dm-3
> Buffer I/O error on device dm-3, logical block 2900796
> lost page write due to I/O error on dm-3
> Buffer I/O error on device dm-3, logical block 2900797
> lost page write due to I/O error on dm-3
>
> So we have restarted the server, everything seems to be fine. Should I
> perform other tests ? Or Should I use a backup to restore the database ?
>
> Thanks for your advice.
> Saïd
>

[GENERAL] could not fdatasync log file: Input/output error

[said assemlal]

Hi,

The postgresql crashed on friday due to IO errors. It seems that the
filesystem puked.

PANIC: could not fdatasync log file 000101760077: Input/output
error
LOG: database system was interrupted; last known up at 2017-10-13 15:26:28
EDT
WARNING: terminating connection because of crash of another server process
FATAL: the database system is in recovery mode

Some errors from the kernel given by the sysadmin:

Buffer I/O error on device dm-3, logical block 2900794
lost page write due to I/O error on dm-3
Buffer I/O error on device dm-3, logical block 2900795
lost page write due to I/O error on dm-3
Buffer I/O error on device dm-3, logical block 2900796
lost page write due to I/O error on dm-3
Buffer I/O error on device dm-3, logical block 2900797
lost page write due to I/O error on dm-3

So we have restarted the server, everything seems to be fine. Should I
perform other tests ? Or Should I use a backup to restore the database ?

Thanks for your advice.
Saïd

Re: [GENERAL] Using Substitution Variables In PostgreSQL

[David G. Johnston]

On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware 
wrote:

> Hi All,
>
> I wanted to find out how to use a substitution variable in an SQL
> statement that would cause the user to be prompted for a value. Something
> similar to the ampersand (&&) in ORACLE.
>
> For example, given the SQL statement below:
> SELECT ,,
> FROM 
> WHERE  = 35
>
> I want the user to be prompted for the value in the WHERE (filter) clause,
> e.g.
> SELECT ,,
> FROM 
> WHERE  = ?
>
> I would be glad if someone could point me in the right direction.
>

​That would be a client-side feature.  The only client supported on this
list is psql.  psql does not have this capability.  It does have the
"\prompt" ​meta-command which will serve in this specific case.

\prompt 'Enter a value for varname' varname

SELECT ... WHERE  = :'varname';

https://www.postgresql.org/docs/10/static/app-psql.html

David J.

Re: [GENERAL] Using Substitution Variables In PostgreSQL

[Achilleas Mantzios]

On 16/10/2017 17:08, Osahon Oduware wrote:

Hi All,

I wanted to find out how to use a substitution variable in an SQL statement that would 
cause the user to be prompted for a value. Something similar to the ampersand 
(&&) in ORACLE.

For example, given the SQL statement below:
    SELECT ,,
    FROM 
    WHERE  = 35

I want the user to be prompted for the value in the WHERE (filter) clause, e.g.
    SELECT ,,
    FROM 
    WHERE  = ?

I would be glad if someone could point me in the right direction.


Just write a bash script that asks for values and then use the -v feature of 
psql .


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
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] Force SSL connection

[Tom Lane]

rakeshkumar464  writes:
> In PG 9.6 or PG 10, is there a way to force only SSL based connections coming
> from pgadmin or dbeaver.

I think you could set that up with a custom pg_hba.conf entry or two.
Something like

hostnossl ... pgadmin ... reject

before the line that would normally accept the connection.

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

[GENERAL] Using Substitution Variables In PostgreSQL

[Osahon Oduware]

Hi All,

I wanted to find out how to use a substitution variable in an SQL statement
that would cause the user to be prompted for a value. Something similar to
the ampersand (&&) in ORACLE.

For example, given the SQL statement below:
SELECT ,,
FROM 
WHERE  = 35

I want the user to be prompted for the value in the WHERE (filter) clause,
e.g.
SELECT ,,
FROM 
WHERE  = ?

I would be glad if someone could point me in the right direction.

[GENERAL] Force SSL connection

[rakeshkumar464]

In PG 9.6 or PG 10, is there a way to force only SSL based connections coming
from pgadmin or dbeaver.




--
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] Non-overlapping updates blocking each other

[Tom Lane]

Seamus Abshere  writes:
> I hesitate to share my query and indexes because it makes this question
> seem more esoteric than I think it really is... but here we go.
> * Version 9.6.3.
> * I don't have any foreign key constraints.
> * I don't use serializable.
> * My update query is very careful to stay in an id range. [1]
> * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...

I'd bet on the last one, especially since you found that the problem
was a page-level lock.  Did you look to see which relation the page
lock was in?

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

[Alvaro Herrera]

Did you try using SELECT FOR NO KEY UPDATE instead of SELECT FOR UPDATE?

However:

Seamus Abshere wrote:

> My current theory is that, since the table is not clustered by id, rows
> with very distant ids get stored in the same page, and the whole page is
> locked during an update.

But we only lock one heap page at a time, not many, so it's hard to see
how that would deadlock.

> Or something.

Given your reluctance to share more details, it seems you'd have to do
with that explanation.

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


-- 
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

[Seamus Abshere]

On 2017-10-14 16:32:33 Tom Lane wrote:
> 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

hi Tom,

I hesitate to share my query and indexes because it makes this question
seem more esoteric than I think it really is... but here we go.

* Version 9.6.3.
* I don't have any foreign key constraints.
* I don't use serializable.
* My update query is very careful to stay in an id range. [1]
* I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...

My current theory is that, since the table is not clustered by id, rows
with very distant ids get stored in the same page, and the whole page is
locked during an update. Or something.

[1] Update SQL:
https://gist.github.com/seamusabshere/d04dad259e383c13f5559241d2fcad70

[2] Indexes:
https://gist.github.com/seamusabshere/acba364b97e1dd221a589b1aaf22bddb

Thanks,
Seamus


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

[GENERAL] Postgres 10 manual breaks links with anchors

[Thomas Kellerer]

I don't know if this is intentional, but the Postgres 10 manual started to use 
lowercase IDs as anchors in the manual.

So, if I have e.g.: the following URL open in my browser:

   
https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently

I cannot simply switch to an older version by replacing "current" with e.g. 
"9.5" because in the 9.5 manual the anchor was all uppercase, and the URL would 
need to be: 

   
https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Is this intentional? 

This also makes "cleaning" up links in e.g. StackOverflow that point to 
outdated versions of the manual a bit more cumbersome. 

Regards
Thomas
 



-- 
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

[Andreas Kretschmer]

Am 14.10.2017 um 08:20 schrieb 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).




other solution, using the CTID-column: (rows with (1,1,1) and (5,5,5) 
are identical)


test=*# select * from dubletten ;
 c1 | c2 | c3
++
  1 |  1 |  1
  1 |  1 |  1
  1 |  2 |  3
  2 |  3 |  4
  3 |  4 |  5
  4 |  5 |  5
  5 |  5 |  5
  5 |  5 |  5
(8 Zeilen)

test=*# with keep as (select max(ctid) as ctid from dubletten group by 
c1,c2,c3) delete from dubletten where ctid not in (select ctid from keep);;

DELETE 2
test=*# select * from dubletten ;
 c1 | c2 | c3
++
  1 |  1 |  1
  1 |  2 |  3
  2 |  3 |  4
  3 |  4 |  5
  4 |  5 |  5
  5 |  5 |  5
(6 Zeilen)

test=*#

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



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