date:20210225

Re: PostgreSQL Licensing Question for pg_crypto and tablefunc extensions

2021-02-25 Thread Tom Lane

Rumpi Gravenstein  writes:
> I am new to PostgreSQL and am unclear on how licensing works for PostgreSQL
> extensions.  Are pg_crypto and tablefunc licensed with the PostgreSQL
> community edition or do PostgreSQL extensions fall under a separate
> license?  I've looked for documentation on this and haven't found anything
> on-point.  Is there a link that describes how each extension is licensed?

Everything in contrib/ is considered to be under the same license as the
rest of the distribution.  (A few of them have their own copyright text,
but it's not substantially different in meaning from the main copyright
notice.  This is also true of bits of the core server, actually.)

Extensions you get from elsewhere might have different copyrights though.

regards, tom lane

PostgreSQL Licensing Question for pg_crypto and tablefunc extensions

2021-02-25 Thread Rumpi Gravenstein

All,

Postgres 13.1

I am new to PostgreSQL and am unclear on how licensing works for PostgreSQL
extensions.  Are pg_crypto and tablefunc licensed with the PostgreSQL
community edition or do PostgreSQL extensions fall under a separate
license?  I've looked for documentation on this and haven't found anything
on-point.  Is there a link that describes how each extension is licensed?

-- 
Rumpi Gravenstein

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber

Thank you for the explanation, David

On Thu, Feb 25, 2021 at 9:49 PM David Rowley  wrote:

>
> Since your foreign keys perform a cascade delete on the tables
> referencing the tables you're deleting from, any records in those
> referencing tables will be deleted too.  You must also look at those
> referencing tables and see what references those and index the
> column(s) which are referencing.
>
>

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Pavel Stehule

čt 25. 2. 2021 v 22:33 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> Thank you, Pavel!
>
> I didn't even think about trying to "explain analyze" deletion of just 1
> record -
>
> On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule 
> wrote:
>
>> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
>>> alexander.far...@gmail.com> napsal:
>>>
 The question is why does the command take days (when I tried last time):
 delete from words_games where created < now() - interval '12 month';


>>> postgres=# explain analyze delete from words_games where gid = 44877;
>>>
>>> create index on words_scores(mid);
>>>
>>
> I have also added:
>
>  create index on words_puzzles(mid);
>
> and then the result if finally good enough for my nightly cronjob:
>
>  explain analyze delete from words_games where created < now() - interval
> '12 month';
> QUERY PLAN
>
>
> --
>  Delete on words_games  (cost=0.00..49802.33 rows=104022 width=6) (actual
> time=2121.475..2121.476 rows=0 loops=1)
>->  Seq Scan on words_games  (cost=0.00..49802.33 rows=104022 width=6)
> (actual time=0.006..85.908 rows=103166 loops=1)
>  Filter: (created < (now() - '1 year'::interval))
>  Rows Removed by Filter: 126452
>  Planning Time: 0.035 ms
>  Trigger for constraint words_chat_gid_fkey on words_games: time=598.444
> calls=103166
>  Trigger for constraint words_moves_gid_fkey on words_games:
> time=83745.244 calls=103166
>  Trigger for constraint words_scores_gid_fkey on words_games:
> time=30638.420 calls=103166
>  Trigger for constraint words_puzzles_mid_fkey on words_moves:
> time=15426.679 calls=3544242
>  Trigger for constraint words_scores_mid_fkey on words_moves:
> time=18546.115 calls=3544242
>  Execution Time: 151427.183 ms
> (11 rows)
>
> There is one detail I don't understand in the output of "explain analyze"
> - why do the lines
>
> "Trigger for constraint words_scores_mid_fkey on words_moves:
> time=1885.372 calls=4"
>
> completely disappear after adding the index? Are those the "ON DELETE
> CASCADE" triggers?
>

these triggers are RI triggers


>
> Aren't they called after the index has been added?
>

it should be called every time

Pavel


> Best regards
> Alex
>

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber

Thank you, Pavel!

I didn't even think about trying to "explain analyze" deletion of just 1
record -

On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule 
wrote:

> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
>> alexander.far...@gmail.com> napsal:
>>
>>> The question is why does the command take days (when I tried last time):
>>> delete from words_games where created < now() - interval '12 month';
>>>
>>>
>> postgres=# explain analyze delete from words_games where gid = 44877;
>>
>> create index on words_scores(mid);
>>
>
I have also added:

 create index on words_puzzles(mid);

and then the result if finally good enough for my nightly cronjob:

 explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN

--
 Delete on words_games  (cost=0.00..49802.33 rows=104022 width=6) (actual
time=2121.475..2121.476 rows=0 loops=1)
   ->  Seq Scan on words_games  (cost=0.00..49802.33 rows=104022 width=6)
(actual time=0.006..85.908 rows=103166 loops=1)
 Filter: (created < (now() - '1 year'::interval))
 Rows Removed by Filter: 126452
 Planning Time: 0.035 ms
 Trigger for constraint words_chat_gid_fkey on words_games: time=598.444
calls=103166
 Trigger for constraint words_moves_gid_fkey on words_games: time=83745.244
calls=103166
 Trigger for constraint words_scores_gid_fkey on words_games:
time=30638.420 calls=103166
 Trigger for constraint words_puzzles_mid_fkey on words_moves:
time=15426.679 calls=3544242
 Trigger for constraint words_scores_mid_fkey on words_moves:
time=18546.115 calls=3544242
 Execution Time: 151427.183 ms
(11 rows)

There is one detail I don't understand in the output of "explain analyze" -
why do the lines

"Trigger for constraint words_scores_mid_fkey on words_moves: time=1885.372
calls=4"

completely disappear after adding the index? Are those the "ON DELETE
CASCADE" triggers?

Aren't they called after the index has been added?

Best regards
Alex

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Pavel Stehule

čt 25. 2. 2021 v 22:02 odesílatel Pavel Stehule 
napsal:

> Hi
>
> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
> alexander.far...@gmail.com> napsal:
>
>> Pavel, thank you for asking!
>>
>> I have put the anonymized dump of my database at:
>>
>> http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB
>> download)
>>
>> The question is why does the command take days (when I tried last time):
>>
>> delete from words_games where created < now() - interval '12 month';
>>
>>
> I tried to remove just only one row
>
> postgres=# explain analyze delete from words_games where gid = 44877;
>
> ┌┐
> │ QUERY PLAN
>   │
>
> ╞╡
> │ Delete on words_games  (cost=0.42..8.44 rows=0 width=0) (actual
> time=0.268..0.270 rows=0 loops=1)  │
> │   ->  Index Scan using words_games_pkey on words_games  (cost=0.42..8.44
> rows=1 width=6) (actual time=0.205..0.209 rows=1 loops=1) │
> │ Index Cond: (gid = 44877)
>│
> │ Planning Time: 0.328 ms
>│
> │ Trigger for constraint words_chat_gid_fkey on words_games: time=0.215
> calls=1  │
> │ Trigger for constraint words_moves_gid_fkey on words_games: time=0.240
> calls=1 │
> │ Trigger for constraint words_scores_gid_fkey on words_games: time=0.103
> calls=1│
> │ Trigger for constraint words_puzzles_mid_fkey on words_moves:
> time=56.099 calls=4  │
> │ Trigger for constraint words_scores_mid_fkey on words_moves:
> time=22536.280 calls=4│
> │ Execution Time: 22593.293 ms
>   │
>
> └┘
> (10 rows)
>
> looks so you miss index
>
> create index on words_scores(mid);
>

postgres=# explain analyze delete from words_games where gid = 183154;
┌┐
│ QUERY PLAN
  │
╞╡
│ Delete on words_games  (cost=0.42..8.44 rows=0 width=0) (actual
time=0.369..0.371 rows=0 loops=1)  │
│   ->  Index Scan using words_games_pkey on words_games  (cost=0.42..8.44
rows=1 width=6) (actual time=0.283..0.288 rows=1 loops=1) │
│ Index Cond: (gid = 183154)
  │
│ Planning Time: 0.230 ms
 │
│ Trigger for constraint words_chat_gid_fkey on words_games: time=0.131
calls=1  │
│ Trigger for constraint words_moves_gid_fkey on words_games: time=1.329
calls=1 │
│ Trigger for constraint words_scores_gid_fkey on words_games: time=1.704
calls=1│
│ Trigger for constraint words_puzzles_mid_fkey on words_moves: time=4.068
calls=51  │
│ Trigger for constraint words_scores_mid_fkey on words_moves: time=5.304
calls=51   │
│ Execution Time: 13.037 ms
 │
└┘
(10 rows)

Regards

Pavel


> Regards
>
> Pavel
>
>

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Pavel Stehule

Hi

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> Pavel, thank you for asking!
>
> I have put the anonymized dump of my database at:
>
> http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB
> download)
>
> The question is why does the command take days (when I tried last time):
>
> delete from words_games where created < now() - interval '12 month';
>
>
I tried to remove just only one row

postgres=# explain analyze delete from words_games where gid = 44877;
┌┐
│ QUERY PLAN
  │
╞╡
│ Delete on words_games  (cost=0.42..8.44 rows=0 width=0) (actual
time=0.268..0.270 rows=0 loops=1)  │
│   ->  Index Scan using words_games_pkey on words_games  (cost=0.42..8.44
rows=1 width=6) (actual time=0.205..0.209 rows=1 loops=1) │
│ Index Cond: (gid = 44877)
 │
│ Planning Time: 0.328 ms
 │
│ Trigger for constraint words_chat_gid_fkey on words_games: time=0.215
calls=1  │
│ Trigger for constraint words_moves_gid_fkey on words_games: time=0.240
calls=1 │
│ Trigger for constraint words_scores_gid_fkey on words_games: time=0.103
calls=1│
│ Trigger for constraint words_puzzles_mid_fkey on words_moves: time=56.099
calls=4  │
│ Trigger for constraint words_scores_mid_fkey on words_moves:
time=22536.280 calls=4│
│ Execution Time: 22593.293 ms
  │
└┘
(10 rows)

looks so you miss index

create index on words_scores(mid);

Regards

Pavel

Re: Code of Conduct: Hebrew Translation for Review

2021-02-25 Thread Valeria Kaplan

Had a read through, no comments.
Thank you, Michael and Emil!

On Thu, Feb 25, 2021 at 6:54 PM Stacey Haysler  wrote:

> The PostgreSQL Community Code of Conduct Committee has received a draft of
> the Hebrew translation of the Code of Conduct Policy updated August 18,
> 2020 for review.
>
> The English version of the Policy is at:
> https://www.postgresql.org/about/policies/coc/
>
> The patch was created by:
> Michael Goldberg
>
> The patch was reviewed by:
> Emil Shkolnik
>
> The proposed translation is attached to this message in various formats.
>
> If you have any comments or suggestions for the translation, please bring
> them to our attention no later than 5:00 PM PST on Thursday, March 4, 2021.
>
> Thank you.
>
> Regards,
> Stacey
>
> Stacey Haysler
> Chair
> PostgreSQL Community Code of Conduct Committee
>
>
>
>

Re: Deleting takes days, should I add some index?

2021-02-25 Thread David Rowley

On Fri, 26 Feb 2021 at 02:06, Alexander Farber
 wrote:
> However the deletion still takes forever and I have to ctrl-c it:
>
> # delete from words_games where created < now() - interval '12 month';
>
> Do you please have any further suggestions?
>
> When I try to prepend "explain analyze" to the above query, then in the 
> production database it also lasts forever.

EXPLAIN with ANALYZE executes the query. So it'll probably to take just as long.

Since your foreign keys perform a cascade delete on the tables
referencing the tables you're deleting from, any records in those
referencing tables will be deleted too.  You must also look at those
referencing tables and see what references those and index the
column(s) which are referencing.

Here's a simplified example that's easier to understand than your case.

Setup:
create table t1 (id int primary key);
create table t2 (id int primary key, t1_id int not null references t1
on update cascade on delete cascade);
create index on t2 (t1_id);
create table t3 (id int primary key, t2_id int not null references t2
on update cascade on delete cascade);

So I have 2 levels of reference.  t2 -> t1 and t3 -> t2.
If I remove a row from t1 then PostgreSQL must perform: DELETE FROM t2
WHERE t1_id = ;

Luckily I indexed t2(t1_id), so that should be fast.

Since t3 references t2, the database must also perform: DELETE FROM t3
WHERE t2_id = ; for the row that gets
removed from t2.

Unfortunately, I forgot to index t3(t2_id).

Let me insert some data and see how the lack of index effects performance:

insert into t1 select x from generate_Series(1,100) x;
insert into t2 select x,x from generate_Series(1,100) x;
insert into t3 select x,x from generate_Series(1,100) x;

Delete 100 records.

delete from t1 where id <= 100;
DELETE 100
Time: 8048.975 ms (00:08.049)

Pretty slow.

create index on t3 (t2_id);
CREATE INDEX

(truncate t1 cascade and reinsert the data)

delete from t1 where id <= 100;
DELETE 100
Time: 5.151 ms

Better.

So, you need to follow each of the "Referenced by" from the table
you're deleting from. In the \d output, just ignore the tables
mentioned in "Foreign-key constraints:".  Those are only checked on
INSERT/UPDATE and must already contain a proper unique constraint and
therefore index.

David

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Rumpi Gravenstein

My use case involves complicated joins on source tables in one schema
loading a target table in the same or a different schema.

On Thu, Feb 25, 2021 at 11:41 AM Rob Sargent  wrote:

> On 2/25/21 9:26 AM, Rumpi Gravenstein wrote:
> > Unfortunately, I am not looking to load from an external source.  My
> > process is moving data from source PostgreSQL tables to target
> > PostgreSQL tables.
> >
> Are you trying to duplicate the source tables in the target tables?  If
> so, there are replication tools for this.  If not, temporary tables in
> the target db (possible loaded with copy) and smallish batches of
> inserts from those to target might be an option.
>
>
>
>

-- 
Rumpi Gravenstein

Re: getting tables list of other schema too

2021-02-25 Thread Francisco Olarte

Atul.

Due to your top posting style and not being a native english speaker
I'm unable to understand your question.

As all the quotes at the bottom  seemed to belong to me, I'm assuming
you referred to some of my postings.

As the last one said I tried to point a thing after checking some
docs, then I noticed some error in my part and tried to tell everybody
to ignore my previous post.

On Wed, Feb 24, 2021 at 3:16 PM Atul Kumar  wrote:
>
> I am sorry but I am not clear from your response, as I have created
> another instance with same version 9.6 but there no system schema or
> its tables are visible.

Regarding these, I do not know what a "system schema" is May be
"public". Note my post talked about "user cretaed" vs "system" ( I?ve
normally observed those are things like information schema vies,
pg_class an similar tables, which are always there after DB creation
and are normally needed for the server to work )  OBJECTS. Also,
naming an schema "sys" does not make it a system schema.

Francisco Olarte

Code of Conduct: Hebrew Translation for Review

2021-02-25 Thread Stacey Haysler

The PostgreSQL Community Code of Conduct Committee has received a draft of the Hebrew translation of the Code of Conduct Policy updated August 18, 2020 for review.The English version of the Policy is at:https://www.postgresql.org/about/policies/coc/The patch was created by:Michael GoldbergThe patch was reviewed by:Emil ShkolnikThe proposed translation is attached to this message in various formats.If you have any comments or suggestions for the translation, please bring them to our attention no later than 5:00 PM PST on Thursday, March 4, 2021.Thank you.
Regards,StaceyStacey HayslerChairPostgreSQL Community Code of Conduct Committee


ï»¿×§×× ××ª× ××××ª

××§×××
×¤×¨×××§× PostgreSQL ××ª××× ××××××ª ××§×× ×××¢×××× ×©×× 
×, ×××××©××× ×××× ××× ××××§×¦××¢××× ×©× ××§×××× 
×©×× ×. ×× × ××¦×¤×× ××× ×××©×ª×ª×¤×× ×××ª× ×× ××¦××¨× 
××§×¦××¢××ª, ××¤×¢×× ×× ××××¡ ××¡××¡× ××××¢× ×××× 
××¨×¡×× ×××©××ª×¤×× ×©× ×××× ×, ×¢× ×××× ×××× ××× 
×××©×ª×ª×¤×× ××××¤×ª××× ×©×× ×.
××¦××¨× ×× ××××¨× × ××ª ×§×× ×××ª× ××××ª, ××¦×¨×× ××× 
××¨××§×¦×× ××ª×× ××§×××× ×××©×ª×ª×¤××ª ××¢××××ª 
××¤×¨×××§× ×××¢××××ª ××§×××× ××××. ×§×× ×× ×× ×¡× 
×××¡××ª ××ª ×× ×¡××× ×××× ××¨××§×¦×× ××× ×××¨× 
××§××××, ××× ×× ××××¨ ××ª×§××× ×××¡××¨×ª ××ª×©×ª××ª ×©× 
postgresql.org ×× ××, ×× ×¢×× ××× ×§×× ××ª× ××××ª ×××¨, 
×©××§×× ×§×××××ª (×××× ×§×× ××ª× ××××ª ×©× ×× ×¡).

××××× ×××ª× ××××ª × ×××ª×
×¤×¨×××§× PostgreSQL ×¤×ª×× ×××©×ª×ª×¤××ª ××× ××× ×¢× ×¢× ××× 
××¢×××× ×¢× PostgreSQL, ××× ×§×©×¨ ××¨××ª ×× ××¡××× ×©××× 
×¢× ××ª××× ×, ×× ×¢× ××× ×××××× ××××¤× ××××. ×× × 
××¢××××× ×¤××ª×× ××ª×¨××× ××× ×××©×ª×ª×¤×× ×©×× ×, 
×××× ××¨×§×¢ ×©××× ××©×¨ ××××.
×× × ××¢××××× ×××× ××¢××¨×¨ ×××©×× ×××× × ×××× 
××ª××× × ××§×××× ××, ××¦×× ×× ×××× ×××××× ×× 
××¤×©×¨××× ××¤××ª××. ××××§×× ×©× ××××× ×× ×©×× × ×¦×¨×× 
×××××ª ××§×× ××××× ××××××, ××¤×¨×××§××× 
××§××××ª××× ×××ª×©×ª××ª ××§×©××¨×× ××××. 
××ª×§×¤××ª ××¢×××ª ×××¤× ×××©× ×××¢×¨××ª ×©××××××ª ×××× 
×××¤××× ×× ×××©××× ××× × ××§×××××ª ××× ×××¨×©×. 
×××××××ª ××××¤××× ×× ×××©××× ××××××ª, ×× ××× × 
×××××××ª ××××, ×××¢, ×××¦× ××××× ×× ×××¡×××¨×, ××ª, 
××××¨ ×× × ×××× ××× ××ª.
××ª× ××××××ª × ××¡×¤××ª ×©×××××ª ××¤×¨× ×©× ×§×× ××ª× 
××××ª ×× ××××××ª, ×× ××× × ×××××××ª ××××××× 
×××××××ª × ×× ×¤×¨××× ×× ×§×××¦××ª, ××××× ×××× 
××§×¦××¢××ª, ×§××××ª××ª ×/×× ×××× ××¤×¨×××§×, ×ª×©×××ª ×× 
×× ×¨×¦××× ××¢××ª ×××¤× ××× × ××©×× ×¦××¨×, ×× ××ª× 
××××ª ×©××× ×©×¢×××× ××¤×××¢ ××©×× ×××× ×©× ×¤×¨×××§× 
PostgreSQL ××¡××¨×× ××¢×¦××¨ ××ª× ××××ª ×× × ×××ª× ××©×××× 
× ××¨×© ××¢×©××ª ××.

×ª××××
××× ××, ××¡××¨ ××ª××××ª ××××× ×× ×× ××× ×××¢×× 
×ª××× × ×ª××ª ×§×× ××ª× ××××ª ××, ×× ××× ×××ª×¢×§×© ×¢× 
××§××¨×ª ×ª××× × ×©×××. ××ª×××× ×¢××××, ××× ×××ª×¨, 
××§×× ××ª ×××ª ×××¦××¨××ª ×××××ª:
â ×××©× ××ª×§×¤××ª ×××©×××ª (×¦××××¨×××ª ×× ×¤×¨××××ª),
â ×¤×¢××××ª ×©×××ª×¨××ª ×ª××ª ××¡××××¡ ×××§×¦××¢× ×/×× 
××¡××××¡ ××ª×¢×¡××§×ª× ×©× ×××× ××× ××¢×¡××§××, ×××¨×× 
××¢××××, ××§××××ª×× ×× ××§××××,
â ×¤×¢××××ª ××××××××ª ×¢× ×¤×¨××××ª×, ××¨××××ª× ××××¤× 
××ª, ×¨××××ª×, ×××ª× ×/×× ××©×¤××ª× ×©× ××××.
×¤×¢××××ª ×ª×××× ××××¤×× ××××ª× ××¨× ××× ×× ××¤×¨× 
×××¨×ª ×©× ×§×× ××ª× ××××ª ××.

××¢××ª ×§×× ×××ª× ××××ª
×¦×××ª ××××× ××× × ×××¢××ª ×§×× ××ª× ××××ª ××§×××ª 
×××§××¨×ª ×× ××ª××× ××ª, ×××"×¨ ××××ª× ×××¢××. ×× ×××¨ 
××§×××× ×¨×©×× ×××ª× ×× ×××××ª ×××¨ ××××¢××, ×××¢× 
×××¨× ×¦×××ª ×××××. ××××ª ××¦×××ª ××××× ××¤×§× ×¢× 
××××¢××, ×××¨× ×¦×××ª ××××× ×× ×××× ×¨×©××× ×××××ª 
×××¨×× ××××¢××, ×¢× ×× ×ª ××× ××¢ × ×××× ×¢× ××× ××. 
×¨×©×××ª ××××¨×× ××××¢×× ×ª××× ×××× × ××¦×¤××× ××× 
×¨××¢ ×× ××ª× ××¨×××ª ×××ª× ×××
××××¨××ª ××××¢×× ×ª××××© ×¢× ××¡××¡ ×©× ×ª×. ×¦×××ª 
××××× ×× ××"×¨ ××××¢×× ×××¨××× ×¢× ×ª××¨××× 
××¤×ª××× ×××¡×××¨× ×©× ×ª×××× ××××¨×ª ××××¨××ª ××©× ×ª× 
××¨× ×¢×¨××¦× ××ª×§×©××¨×ª ××§××××ª×××

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber

Pavel, thank you for asking!

I have put the anonymized dump of my database at:

http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB download)

The question is why does the command take days (when I tried last time):

delete from words_games where created < now() - interval '12 month';

serializability and unique constraint violations

2021-02-25 Thread Jonathan Amsterdam

I found that I can get a "duplicate key value violates unique constraint"
error under certain conditions which I don't think the documentation
describes. I don't know if this is a documentation bug, a product bug, or
if I'm holding something wrong.

My table is created with

 CREATE TABLE  paths (

id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
path TEXT NOT NULL

 );

 ALTER TABLE paths ADD CONSTRAINT paths_path_key UNIQUE (path);
CREATE INDEX idx_paths_path_id ON paths(path, id);

I create a serializable transaction that first selects a path from the
table, and then inserts it only if it is missing. When I run several of
these transactions concurrently, I see the constraint violation error.
(Complete Go program at
https://gist.github.com/jba/87f95951103aba67794eea04ba307b8c.)

The docs (https://www.postgresql.org/docs/13/transaction-iso.html) are
clear that unique constraint violations can violate serializability: "it is
possible to see unique constraint violations caused by conflicts with
overlapping Serializable transactions even after explicitly checking that
the key isn't present before attempting to insert it". However, the next
sentence is: "This can be avoided by making sure that all Serializable
transactions that insert potentially conflicting keys explicitly check if
they can do so first," which I do.

When I remove the index, everything works as documented.

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Rob Sargent


On 2/25/21 9:26 AM, Rumpi Gravenstein wrote:
Unfortunately, I am not looking to load from an external source.  My 
process is moving data from source PostgreSQL tables to target 
PostgreSQL tables.


Are you trying to duplicate the source tables in the target tables?  If 
so, there are replication tools for this.  If not, temporary tables in 
the target db (possible loaded with copy) and smallish batches of 
inserts from those to target might be an option.

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Tom Lane

Rumpi Gravenstein  writes:
> Unfortunately, I am not looking to load from an external source.  My
> process is moving data from source PostgreSQL tables to target PostgreSQL
> tables.

The hints in

https://www.postgresql.org/docs/current/populate.html

would still largely apply, though of course not the advice to use COPY.

regards, tom lane

Re: Extension intarray and null values

2021-02-25 Thread Tom Lane

Eric Brison  writes:
> yes i a have a specific query with many rows and big int array These data
> not contains null values. And in this case , i use the specific GIN  index.
> The query time decrease from 30s to 100ms with the index.

My point is that you can also make a gin index on an integer array
using the built-in array opclass.  It might be a shade slower than
intarray, but maybe it's 120ms instead of 100, and it'll handle
nulls correctly.

regards, tom lane

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Rumpi Gravenstein

Unfortunately, I am not looking to load from an external source.  My
process is moving data from source PostgreSQL tables to target PostgreSQL
tables.

On Thu, Feb 25, 2021 at 10:36 AM Mark Johnson  wrote:

> Since INSERT /*+APPEND*/ is generally used when bulk loading data into
> Oracle from external files you should probably look at the PostgreSQL COPY
> command (https://www.postgresql.org/docs/13/sql-copy.html) and additional
> utilities like pg_bulkload (https://github.com/ossc-db/pg_bulkload)   .
>
> On Thu, Feb 25, 2021 at 9:45 AM Rumpi Gravenstein 
> wrote:
>
>> All,
>>
>> Using PostgreSQL 13.1
>>
>> I am new to PostgreSQL transitioning from Oracle.  One of the many Oracle
>> tricks I learned is that large inserts can be sped up by adding the direct
>> path load hint /*+APPEND*/ .  I am faced with having to perform many large
>> inserts (100K->100M rows) in my PostgreSQL database.
>>
>> My questions are:
>>
>>- Is there something comparable within the PostgreSQL community
>>edition product?
>>- Are my only options to un-log the table and tune instance memory
>>parameters?
>>
>> I've googled for this and can't find a definitive statement on this
>> point.
>>
>> --
>> Rumpi Gravenstein
>>
>

-- 
Rumpi Gravenstein

Re: Server hangs on pg_repack

2021-02-25 Thread Roman Liverovskiy

Thanks, I will try it, but it is not an answer on my question.

On Thu, Feb 25, 2021, 19:38 Michael Lewis  wrote:

> Why not use reindex concurrently?
>

Re: PostgreSQL URI

2021-02-25 Thread Paul Förster

Hi Tom,

> On 25. Feb, 2021, at 16:43, Tom Lane  wrote:
> 
> Experimenting, it does let you omit the host and specify a port:
> 
> $ psql -d postgresql://:5433
> psql: error: could not connect to server: No such file or directory
>Is the server running locally and accepting
>connections on Unix domain socket "/tmp/.s.PGSQL.5433"?
> 
> So the original syntax diagram is not wrong.  We could add brackets
> to clarify the repeatable part:
> 
> postgresql://[user[:password]@][[host][:port][,...]][/dbname][?param1=value1&...]
> 
> but I'm less sure that that's an improvement.

hmm, the following indeed connects me to the primary, leaving out the host part 
completely:

$ psql -d postgresql://:5432,:5433/postgres?target_session_attrs=read-write
psql (13.2, server 12.6)
Type "help" for help.

postgres=# select user, current_setting('data_directory');
   user   |   current_setting
--+--
 postgres | /data/pg01/cdb01b/db
(1 row)

remark: cdb01a currently is replica and cdb01b is currently primary of a local 
Patroni test cluster, replicating between /data/pg01/cdb01a/db and 
/data/pg01/cdb01b/db.

So, my suggestion is:

postgresql://[user[:password]@][[host][:port]][,...][/dbname][?param1=value1&...]

Still, I think that it's an improvement, because it makes clear that not only 
the port, but also the host may be repeated.

Cheers,
Paul

Re: Extension intarray and null values

2021-02-25 Thread Eric Brison

yes i a have a specific query with many rows and big int array These data
not contains null values. And in this case , i use the specific GIN  index.
The query time decrease from 30s to 100ms with the index.

I found that i can use the "arraycontains" builtin function for int[] with
null values. But i'm not sure that this function name is safe in all
postgresql version (>= 10)

Eric.

Le jeu. 25 févr. 2021 à 16:06, Tom Lane  a écrit :

> Eric Brison  writes:
> > I use tables with several "int[]" columns.
> > I use the "@>" operator to filter my data.
> > To increase speed , i install the "intarray" extension. Now queries are
> > very fast with the specific indexes (i use "gin__int_ops").
> > But, in few columns, i have null values in my intarray and i cannot use
> the
> > "@>" operator.
>
> Do you really need to use intarray, rather than the built-in gin array
> indexing features?
>
> Yeah, the intarray implementation is probably a shade faster, but it's
> faster precisely because it doesn't handle cases like null entries.
> If you're in need of that, my suggestion is to ditch intarray.
>
> regards, tom lane
>

-- 

*Eric Brison - Responsable R*
8 av Yves Brunaud - 31770 Colomiers
T : +33 5 31 61 55 23
www.anakeen.com

Ce message, avec ses pièces jointes, est privé et peut contenir des
informations confidentielles ou privilégiées qui ne
doivent pas être diffusés, exploités ou copiés sans autorisation d'Anakeen.
Si vous avez reçu ce message par erreur, veuillez le signaler
à l'expéditeur et le détruire ainsi que les pièces jointes. Les messages
électroniques étant susceptibles d'altération, Anakeen décline toute
responsabilité si ce message a été altéré, déformé ou falsifié.

This message, with its attachments, is private and may contain confidential
or privileged information that may be protected by law; they should not be
distributed, used or copied without authorization of Anakeen. If you have
received this email in error, please notify the sender and delete this
message and its attachments. As emails may be altered, Anakeen is not
liable for messages that have been modified, changed or falsified.

Re: PostgreSQL URI

2021-02-25 Thread Tom Lane

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
>> On 25. Feb, 2021, at 16:22, Tom Lane  wrote:
>> Hmm.  Maybe
>> postgresql://[user[:password]@][host[:port][,...]][/dbname][?param1=value1&...]
>> ?  Seems like that would clarify how much you can repeat.

> yes, that looks better, thanks.

Experimenting, it does let you omit the host and specify a port:

$ psql -d postgresql://:5433
psql: error: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

So the original syntax diagram is not wrong.  We could add brackets
to clarify the repeatable part:

postgresql://[user[:password]@][[host][:port][,...]][/dbname][?param1=value1&...]

but I'm less sure that that's an improvement.

regards, tom lane

Re: Server hangs on pg_repack

2021-02-25 Thread Michael Lewis

Why not use reindex concurrently?

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Mark Johnson

Since INSERT /*+APPEND*/ is generally used when bulk loading data into
Oracle from external files you should probably look at the PostgreSQL COPY
command (https://www.postgresql.org/docs/13/sql-copy.html) and additional
utilities like pg_bulkload (https://github.com/ossc-db/pg_bulkload)   .

On Thu, Feb 25, 2021 at 9:45 AM Rumpi Gravenstein 
wrote:

> All,
>
> Using PostgreSQL 13.1
>
> I am new to PostgreSQL transitioning from Oracle.  One of the many Oracle
> tricks I learned is that large inserts can be sped up by adding the direct
> path load hint /*+APPEND*/ .  I am faced with having to perform many large
> inserts (100K->100M rows) in my PostgreSQL database.
>
> My questions are:
>
>- Is there something comparable within the PostgreSQL community
>edition product?
>- Are my only options to un-log the table and tune instance memory
>parameters?
>
> I've googled for this and can't find a definitive statement on this
> point.
>
> --
> Rumpi Gravenstein
>

Re: Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Jayadevan M

>
> Using PostgreSQL 13.1
>
> I am new to PostgreSQL transitioning from Oracle.  One of the many Oracle
> tricks I learned is that large inserts can be sped up by adding the direct
> path load hint /*+APPEND*/ .  I am faced with having to perform many large
> inserts (100K->100M rows) in my PostgreSQL database.
>
> My questions are:
>
>- Is there something comparable within the PostgreSQL community
>edition product?
>- Are my only options to un-log the table and tune instance memory
>parameters?
>
>
> I remember trying this some time ago. It is not part of the PG community
edition. Still, worth a look -
https://ossc-db.github.io/pg_bulkload/pg_bulkload.html


Regards,
Jayadevan

Re: PostgreSQL URI

2021-02-25 Thread Paul Förster

Hi Tom,

> On 25. Feb, 2021, at 16:22, Tom Lane  wrote:
> 
> Hmm.  Maybe
> 
> postgresql://[user[:password]@][host[:port][,...]][/dbname][?param1=value1&...]
> 
> ?  Seems like that would clarify how much you can repeat.

yes, that looks better, thanks.

Cheers,
Paul

Re: PostgreSQL URI

2021-02-25 Thread Tom Lane

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
> I suspected this already. Still the position of the closing angle bracket 
> behind the "host" part in the syntax is IMHO wrong in the doc.

Hmm.  Maybe

postgresql://[user[:password]@][host[:port][,...]][/dbname][?param1=value1&...]

?  Seems like that would clarify how much you can repeat.

regards, tom lane

Re: PostgreSQL URI

2021-02-25 Thread Paul Förster

Hi Tom,

> On 25. Feb, 2021, at 16:09, Tom Lane  wrote:
> 
> =?utf-8?Q?Paul_F=C3=B6rster?=  writes:
>> in 
>> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING 
>> it says that the syntax for a PostgreSQL URI is:
> 
>> postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]
> 
>> What I don't understand is the [,...] part, i.e. optionally repeating 
>> argument.
> 
> You can repeat the host[:port] part, no more.

I suspected this already. Still the position of the closing angle bracket 
behind the "host" part in the syntax is IMHO wrong in the doc.

It currently says:
postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]

But shouldn't it say instead:
postgresql://[user[:password]@][host[:port]][,...][/dbname][?param1=value1&...]

Thanks very much.

Cheers,
Paul

Re: PostgreSQL URI

2021-02-25 Thread Tom Lane

=?utf-8?Q?Paul_F=C3=B6rster?=  writes:
> in 
> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING 
> it says that the syntax for a PostgreSQL URI is:

> postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]

> What I don't understand is the [,...] part, i.e. optionally repeating 
> argument.

You can repeat the host[:port] part, no more.

regards, tom lane

Re: Extension intarray and null values

2021-02-25 Thread Tom Lane

Eric Brison  writes:
> I use tables with several "int[]" columns.
> I use the "@>" operator to filter my data.
> To increase speed , i install the "intarray" extension. Now queries are
> very fast with the specific indexes (i use "gin__int_ops").
> But, in few columns, i have null values in my intarray and i cannot use the
> "@>" operator.

Do you really need to use intarray, rather than the built-in gin array
indexing features?

Yeah, the intarray implementation is probably a shade faster, but it's
faster precisely because it doesn't handle cases like null entries.
If you're in need of that, my suggestion is to ditch intarray.

regards, tom lane

Re: converting text to bytea

2021-02-25 Thread Pavel Stehule

čt 25. 2. 2021 v 16:01 odesílatel Tom Lane  napsal:

> Yambu  writes:
> > Is there a reason why i'm getting text when i run the below
> > select convert_to('some_text', 'UTF8')i get back 'some_text'
>
> You must have bytea_output set to "escape".
>


yes

set bytea_output TO escape ;
postgres=# select convert_to('žlutý kůň', 'UTF8') ;
┌───┐
│  convert_to   │
╞═══╡
│ \305\276lut\303\275 k\305\257\305\210 │
└───┘
(1 row)


> regards, tom lane
>

Server hangs on pg_repack

2021-02-25 Thread Roman Liverovskiy

Hello.
I have postgresql 12 with a 3.0 GB database with a table containing 10
millions of rows, this table also has 4 indexes.
I have an AWS EC2 server with two AMD EPYC 7571 cores, 2 GB of RAM and SSD
disk.
Because of index bloating I use pg_repack.
When I use default postgresql.conf file I have no issues, but when I tune
postgresql.conf and call pg_repack --only-indexes for my table, my server
hangs and I can not connect to the server until reboot from AWS console.

What is wrong in my postgresql.conf?

My tuned parameters are:

max_stack_depth = 5MB

random_page_cost = 1.1

# Connectivity
max_connections = 100
superuser_reserved_connections = 3

# Memory Settings
shared_buffers = 350MB
effective_cache_size = 500MB
maintenance_work_mem = 384MB
work_mem = 2MB

# Monitoring
shared_preload_libraries = 'pg_stat_statements,pg_repack'

# Checkpointing:
checkpoint_timeout  = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# WAL writing
wal_buffers = -1
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB

# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0

-- 
Faithfully yours, Roman I. Liverovskiy

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Pavel Stehule

čt 25. 2. 2021 v 14:36 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> Hi Pavel,
>
> trying to follow your advice "You should check so all foreign keys have an
> index" I look at the table where I want to delete older records:
>
> # \d words_games
>   Table "public.words_games"
>   Column  |   Type   | Collation | Nullable |
>Default
>
> --+--+---+--+--
>  gid  | integer  |   | not null |
> nextval('words_games_gid_seq'::regclass)
>  created  | timestamp with time zone |   | not null |
>  finished | timestamp with time zone |   |  |
>  player1  | integer  |   | not null |
>  player2  | integer  |   |  |
>  played1  | timestamp with time zone |   |  |
>  played2  | timestamp with time zone |   |  |
>  state1   | text |   |  |
>  state2   | text |   |  |
>  reason   | text |   |  |
>  hint1| text |   |  |
>  hint2| text |   |  |
>  score1   | integer  |   | not null |
>  score2   | integer  |   | not null |
>  chat1| integer  |   | not null |
>  chat2| integer  |   | not null |
>  hand1| character(1)[]   |   | not null |
>  hand2| character(1)[]   |   | not null |
>  pile | character(1)[]   |   | not null |
>  letters  | character(1)[]   |   | not null |
>  values   | integer[]|   | not null |
>  bid  | integer  |   | not null |
>  diff1| integer  |   |  |
>  diff2| integer  |   |  |
>  open1| boolean  |   | not null | false
>  open2| boolean  |   | not null | false
> Indexes:
> "words_games_pkey" PRIMARY KEY, btree (gid)
> "words_games_created_idx" btree (created)
> "words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
> 'infinity'::timestamp with time zone))
> "words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
> 'infinity'::timestamp with time zone))
> Check constraints:
> "words_games_chat1_check" CHECK (chat1 >= 0)
> "words_games_chat2_check" CHECK (chat2 >= 0)
> "words_games_check" CHECK (player1 <> player2)
> "words_games_score1_check" CHECK (score1 >= 0)
> "words_games_score2_check" CHECK (score2 >= 0)
> Foreign-key constraints:
> "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
> ON DELETE CASCADE
> "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
> words_users(uid) ON DELETE CASCADE
> "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
> words_users(uid) ON DELETE CASCADE
> Referenced by:
> TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
> REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
> (gid) REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
> (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>
> You are probably talking about the section:
>
> Foreign-key constraints:
> "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
> ON DELETE CASCADE
> "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
> words_users(uid) ON DELETE CASCADE
> "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
> words_users(uid) ON DELETE CASCADE
>
> The first table words_boards only has 4 records, so I ignore it.
>
> The second table words_users already has an index on the uid, because that
> column is the primary key:
>
> #  \d words_users
> Table "public.words_users"
>Column|   Type   | Collation | Nullable |
>Default
>
> -+--+---+--+--
>  uid | integer  |   | not null |
> nextval('words_users_uid_seq'::regclass)
>  created | timestamp with time zone |   | not null |
>  visited | timestamp with time zone |   | not null |
>  ip  | inet |   | not null |
>  fcm | text |   |  |
>  apns| text |   |  |
>  adm | text |   |  |
>  motto   | text |   |

Re: converting text to bytea

2021-02-25 Thread Tom Lane

Yambu  writes:
> Is there a reason why i'm getting text when i run the below
> select convert_to('some_text', 'UTF8')i get back 'some_text'

You must have bytea_output set to "escape".

regards, tom lane

PostgreSQL URI

2021-02-25 Thread Paul Förster

Hi,

in https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING 
it says that the syntax for a PostgreSQL URI is:

postgresql://[user[:password]@][host][:port][,...][/dbname][?param1=value1&...]

What I don't understand is the [,...] part, i.e. optionally repeating argument.

I know that this is a valid URI:
postgresql://user1:pass1@host1:port1,host2:port2/dbname?target_session_attrs=read-write

But is the following valid?
postgresql://user1:pass1@host1:port1,user2:pass2@host2:port2/dbname?target_session_attrs=read-write

i.e. can (or should) the user[:pass] part be repeated, and possibly can even be 
different, if I provide more than one host:port information? Or is the 
user[:pass] part definitely a one-time only argument and must not appear a 
second time? I don't know how to read this repetition [,...] syntax. A repeat 
syntax usually means providing more parameters like the one immediately before 
that, which in this case, is the port.

I think, it should be more correct this way (note the angle bracket):
postgresql://[user[:password]@][host[:port]][,...][/dbname][?param1=value1&...]

I tried this with psql but failed miserably, though both user1:pass1 and 
user2:pass2 exist on both databases.

The documentation also claims that any of the parts is optional except the 
postgresql:// part. This means, specifying a port without a host would be 
perfectly fine, which IMHO makes no sense.

Can someone enlighten me? Is this just a misleading line to me in the 
documentation?

Cheers,
Paul

Postgres Analog of Oracle APPEND hint

2021-02-25 Thread Rumpi Gravenstein

All,

Using PostgreSQL 13.1

I am new to PostgreSQL transitioning from Oracle.  One of the many Oracle
tricks I learned is that large inserts can be sped up by adding the direct
path load hint /*+APPEND*/ .  I am faced with having to perform many large
inserts (100K->100M rows) in my PostgreSQL database.

My questions are:

   - Is there something comparable within the PostgreSQL community edition
   product?
   - Are my only options to un-log the table and tune instance memory
   parameters?

I've googled for this and can't find a definitive statement on this point.

-- 
Rumpi Gravenstein

Extension intarray and null values

2021-02-25 Thread Eric Brison

Hello,

I use tables with several "int[]" columns.
I use the "@>" operator to filter my data.

To increase speed , i install the "intarray" extension. Now queries are
very fast with the specific indexes (i use "gin__int_ops").

But, in few columns, i have null values in my intarray and i cannot use the
"@>" operator.
Because i have the error "ERROR:  array must not contain nulls".

The documentation says that the standard operator "@>" is overrided by the
intarray extension  when arguments are int[].

Do you have a solution, to rename the "@>(int{], int[])" to use it only
when it is possible and to prevent the override of the current behavior ?

Or if you know, how use the default @> operator instead of the override.

Thank you,
Eric.

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber

Hi Pavel,

trying to follow your advice "You should check so all foreign keys have an
index" I look at the table where I want to delete older records:

# \d words_games
  Table "public.words_games"
  Column  |   Type   | Collation | Nullable |
   Default
--+--+---+--+--
 gid  | integer  |   | not null |
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone |   | not null |
 finished | timestamp with time zone |   |  |
 player1  | integer  |   | not null |
 player2  | integer  |   |  |
 played1  | timestamp with time zone |   |  |
 played2  | timestamp with time zone |   |  |
 state1   | text |   |  |
 state2   | text |   |  |
 reason   | text |   |  |
 hint1| text |   |  |
 hint2| text |   |  |
 score1   | integer  |   | not null |
 score2   | integer  |   | not null |
 chat1| integer  |   | not null |
 chat2| integer  |   | not null |
 hand1| character(1)[]   |   | not null |
 hand2| character(1)[]   |   | not null |
 pile | character(1)[]   |   | not null |
 letters  | character(1)[]   |   | not null |
 values   | integer[]|   | not null |
 bid  | integer  |   | not null |
 diff1| integer  |   |  |
 diff2| integer  |   |  |
 open1| boolean  |   | not null | false
 open2| boolean  |   | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

You are probably talking about the section:

Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE

The first table words_boards only has 4 records, so I ignore it.

The second table words_users already has an index on the uid, because that
column is the primary key:

#  \d words_users
Table "public.words_users"
   Column|   Type   | Collation | Nullable |
 Default
-+--+---+--+--
 uid | integer  |   | not null |
nextval('words_users_uid_seq'::regclass)
 created | timestamp with time zone |   | not null |
 visited | timestamp with time zone |   | not null |
 ip  | inet |   | not null |
 fcm | text |   |  |
 apns| text |   |  |
 adm | text |   |  |
 motto   | text |   |  |
 vip_until   | timestamp with time zone |   |  |
 grand_until | timestamp with time zone |   |  |
 elo | integer  |   | not null |
 medals  | integer  |   | not null |

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Pavel Stehule

čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> Hello, revisiting an older mail on the too long deletion times (in
> PostgreSQL 13.2)...
>
> I have followed the advices here, thank you -
>
> On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge 
> wrote:
>
>> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera  a
>> écrit :
>>
>>> On 2020-Nov-27, Alexander Farber wrote:
>>>
>>> > Referenced by:
>>> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY
>>> (gid)
>>> > REFERENCES words_games(gid) ON DELETE CASCADE
>>> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
>>> (gid)
>>> > REFERENCES words_games(gid) ON DELETE CASCADE
>>> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
>>> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>>>
>>> Make sure you have indexes on the gid columns of these tables.  Delete
>>> needs to scan them in order to find the rows that are cascaded to.
>>>
>>>
>> An index on words_games(finished) and words_moves(played) would help too.
>>
>>
> and have now the following indices in my database:
>
> CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
> CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
> CREATE INDEX ON words_games(created),
> CREATE INDEX ON words_chat(created),
> CREATE INDEX ON words_moves(uid, action, played);
> CREATE INDEX ON words_moves(gid, played);
> CREATE INDEX ON words_moves(played);
> CREATE INDEX ON words_moves(uid);
> CREATE INDEX ON words_moves(gid);
> CREATE INDEX ON words_social(uid, stamp);
> CREATE INDEX ON words_geoip USING SPGIST (block);
> CREATE INDEX ON words_scores(LENGTH(word), mid);
> -- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
> CREATE INDEX ON words_scores(gid);
> CREATE INDEX ON words_scores(uid);
> CREATE INDEX ON words_chat(gid);
>
> However the deletion still takes forever and I have to ctrl-c it:
>
> # delete from words_games where created < now() - interval '12 month';
>
> Do you please have any further suggestions?
>
> When I try to prepend "explain analyze" to the above query, then in the
> production database it also lasts forever.
>
> In an empty dev database the output does not help much -
>
> # explain analyze delete from words_games where created < now() - interval
> '12 month';
>  QUERY PLAN
>
> 
>  Delete on words_games  (cost=0.00..40.34 rows=1 width=6) (actual
> time=0.132..0.132 rows=0 loops=1)
>->  Seq Scan on words_games  (cost=0.00..40.34 rows=1 width=6) (actual
> time=0.131..0.131 rows=0 loops=1)
>  Filter: (created < (now() - '1 year'::interval))
>  Rows Removed by Filter: 137
>  Planning Time: 0.150 ms
>  Execution Time: 0.143 ms
> (6 rows)
>

Postgres newer use index on small tables

DELETE can be slow due ref integrity check or triggers. You should check so
all foreign keys have an index.

Regards

Pavel



> Below are the words_games and the "referenced by" tables -
>
> # \d words_games
>   Table "public.words_games"
>   Column  |   Type   | Collation | Nullable |
>Default
>
> --+--+---+--+--
>  gid  | integer  |   | not null |
> nextval('words_games_gid_seq'::regclass)
>  created  | timestamp with time zone |   | not null |
>  finished | timestamp with time zone |   |  |
>  player1  | integer  |   | not null |
>  player2  | integer  |   |  |
>  played1  | timestamp with time zone |   |  |
>  played2  | timestamp with time zone |   |  |
>  state1   | text |   |  |
>  state2   | text |   |  |
>  reason   | text |   |  |
>  hint1| text |   |  |
>  hint2| text |   |  |
>  score1   | integer  |   | not null |
>  score2   | integer  |   | not null |
>  chat1| integer  |   | not null |
>  chat2| integer  |   | not null |
>  hand1| character(1)[]   |   | not null |
>  hand2| character(1)[]   |   | not null |
>  pile | character(1)[]   |   | not null |
>  letters  | character(1)[]   |   | not null |
>  values   | integer[]|   | not null |
>  bid  | integer  |   | not null |
>  diff1| integer  |   |  |
>  diff2| integer  |   |  |
>

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber

Hello, revisiting an older mail on the too long deletion times (in
PostgreSQL 13.2)...

I have followed the advices here, thank you -

On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge 
wrote:

> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera  a
> écrit :
>
>> On 2020-Nov-27, Alexander Farber wrote:
>>
>> > Referenced by:
>> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
>> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>>
>> Make sure you have indexes on the gid columns of these tables.  Delete
>> needs to scan them in order to find the rows that are cascaded to.
>>
>>
> An index on words_games(finished) and words_moves(played) would help too.
>
>
and have now the following indices in my database:

CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(created),
CREATE INDEX ON words_chat(created),
CREATE INDEX ON words_moves(uid, action, played);
CREATE INDEX ON words_moves(gid, played);
CREATE INDEX ON words_moves(played);
CREATE INDEX ON words_moves(uid);
CREATE INDEX ON words_moves(gid);
CREATE INDEX ON words_social(uid, stamp);
CREATE INDEX ON words_geoip USING SPGIST (block);
CREATE INDEX ON words_scores(LENGTH(word), mid);
-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
CREATE INDEX ON words_scores(gid);
CREATE INDEX ON words_scores(uid);
CREATE INDEX ON words_chat(gid);

However the deletion still takes forever and I have to ctrl-c it:

# delete from words_games where created < now() - interval '12 month';

Do you please have any further suggestions?

When I try to prepend "explain analyze" to the above query, then in the
production database it also lasts forever.

In an empty dev database the output does not help much -

# explain analyze delete from words_games where created < now() - interval
'12 month';
 QUERY PLAN

 Delete on words_games  (cost=0.00..40.34 rows=1 width=6) (actual
time=0.132..0.132 rows=0 loops=1)
   ->  Seq Scan on words_games  (cost=0.00..40.34 rows=1 width=6) (actual
time=0.131..0.131 rows=0 loops=1)
 Filter: (created < (now() - '1 year'::interval))
 Rows Removed by Filter: 137
 Planning Time: 0.150 ms
 Execution Time: 0.143 ms
(6 rows)

Below are the words_games and the "referenced by" tables -

# \d words_games
  Table "public.words_games"
  Column  |   Type   | Collation | Nullable |
   Default
--+--+---+--+--
 gid  | integer  |   | not null |
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone |   | not null |
 finished | timestamp with time zone |   |  |
 player1  | integer  |   | not null |
 player2  | integer  |   |  |
 played1  | timestamp with time zone |   |  |
 played2  | timestamp with time zone |   |  |
 state1   | text |   |  |
 state2   | text |   |  |
 reason   | text |   |  |
 hint1| text |   |  |
 hint2| text |   |  |
 score1   | integer  |   | not null |
 score2   | integer  |   | not null |
 chat1| integer  |   | not null |
 chat2| integer  |   | not null |
 hand1| character(1)[]   |   | not null |
 hand2| character(1)[]   |   | not null |
 pile | character(1)[]   |   | not null |
 letters  | character(1)[]   |   | not null |
 values   | integer[]|   | not null |
 bid  | integer  |   | not null |
 diff1| integer  |   |  |
 diff2| integer  |   |  |
 open1| boolean  |   | not null | false
 open2| boolean  |   | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp

Re: Batch update million records in prd DB

2021-02-25 Thread Yi Sun

Hi Michael,

Thank you for your reply

We found that each loop take time is different, it will become slower and
slower, as our table is big table and join other table, even using index
the last 1000 records take around 15 seconds, will it be a problem? Will
other concurrent update have to wait for 15 second until lock release?

Thanks and best regards

Michael Lewis  于2021年2月24日周三 下午11:47写道：

> Of course it will impact a system using that table, but not significant I
> expect and the production system should handle it. If you are committing
> like this, then you can kill the script at any time and not lose any work.
> The query to find the next IDs to update is probably the slowest part of
> this depending on what indexes you have.
>

Re: converting text to bytea

2021-02-25 Thread Yambu

Hi

Is there a reason why i'm getting text when i run the below

select convert_to('some_text', 'UTF8')i get back 'some_text'

regards

On Mon, Feb 22, 2021 at 9:09 AM Pavel Stehule 
wrote:

> Hi
>
> po 22. 2. 2021 v 7:37 odesílatel Yambu  napsal:
>
>> Hello
>>
>> This sounds simple, but im not getting the results when i cast text to
>> bytea like this first_name::bytea . Is there another way to do this?
>>
>
> You should to use convert_to function
>
>
> https://www.postgresql.org/docs/current/functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS
>
> Regards
>
> Pavel
>
>
>> regards
>>
>

42 matches

Mail list logo