How to execute the sql file in PSQL

[Mark]

Stackoverflow question link:
https://stackoverflow.com/questions/64210281/using-psql-executing-sql-format-file-shows-permission-denied-on-windows-platform


I followed one PostgreSQL tutorial step by step. One session to use PSQL to
execute sql files to create a new database in PostgreSQL.

   1.  copy  paste the sql file within "C:\Program Files\PostgreSQL\12"
   directory.
   2.  execute the following code  \i  C:\\Program
   Files\\PostgreSQL\\12\\demo-big-en-20170815.sql

Failed
The message shows  C:: Permission denied
then tried  \ir  "C:\\Program
Files\\PostgreSQL\\12\\demo-big-en-20170815.sql"
Failed
The message shows unrecognized win32 error code: 123"C:/Program
Files/PostgreSQL/12/createdatabasesupertest.sql: Invalid argument

SQL file probably was wrong. SO I tried to create a simple sql file (create
a new database). Then  I follow the above mentioned step to  execute the
file on windows PSQL.  But  it failed the same way just like the above
mentioned.

So basically my PSQL can not run script files.  What should i do?

Re: How to execute the sql file in PSQL

[Mark]

Actually, a single quotation will work.

 \ir  'C:\\Program Files\\PostgreSQL\\12\\demo-big-en-20170815.sql'

But I don't know why a single quotation will work.

It would be very helpful if you guys can explain to me.


On Mon, Oct 5, 2020 at 9:31 PM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 10/5/20 7:55 AM, Mark wrote:
> >> I followed one PostgreSQL tutorial step by step. One session to use
> PSQL
> >> to execute sql files to create a new database in PostgreSQL.
> >> 1.   copy  paste the sql file within "C:\Program Files\PostgreSQL\12"
> >> directory.
> >> 2.   execute the following code \i  C:\\Program
> >> Files\\PostgreSQL\\12\\demo-big-en-20170815.sql
> >>
> >> Failed
> >> The message shows C:: Permission denied
>
> > The issue is the user you are running psql as does not have permissions
> > to read the file. This is a OS permissions thing. Either run psql as a
> > user that can read the file or change the permissions on the file to
> > those that allow the psql user to read it.
>
> ... or more likely, put the SQL file in a saner place.  Dropping random
> files into a program directory can only lead to trouble.  You should
> treat such directories as read-only unless you know exactly what you
> are doing.
>
> It seems likely to me that this failure stems from PG being installed
> with permissions settings that prevent it from reading/modifying its own
> executables, which is good solid security practice.
>
> (If the tutorial actually told you to do that, the tutorial's author
> is utterly clueless.)
>
> regards, tom lane
>

Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi All,

I'm seeing some very slow queries and it looks like the query planner is
deciding to do a 'Nested Loop Left Join'
which is slow.  When I SET enable_nestloop=OFF for testing it does a 'Hash
Left Join' which is much faster.

I think  I can see the cause of the problem in the examples below. I can't
make sense of the statistics being written
by ANALYSE or the planners row estimates.

Can anyone help me understand

- Why the row estimate I get in the query below for school_id = 36 is 1 ?
   ( I expect it to be higher)
- Why does '1' appear in the most_common_vals when it is actually the least
common value.
- Why doesn't 36 appear in the most_common_vals (it is more common than 1)
- Does the analyse output below mean that it only scanned 51538 of 65463
rows in the table? Is school_id 36 just being missed in the sample? (This
happens when the analyse is repeated )

Any help with understanding what's happening here would be much
appreciated.

I hope I've provided enough information below.

Thanks,

Mark

db=> explain analyse select * from common_student  where school_id = 36 ;
 QUERY
PLAN

 Index Scan using idx_common_student_sid on common_student
(cost=0.41..8.39 rows=1 width=385) (actual time=0.264..1.691 rows=1388
loops=1)
   Index Cond: (school_id = 36)
 Planning time: 0.087 ms
 Execution time: 2.706 ms
(4 rows)

db=> select tablename,attname,most_common_vals,histogram_bounds,n_distinct
from pg_stats where attname='school_id'  and tablename='common_stude
nt';
   tablename|  attname  |
  most_common_vals
  | histogram_bounds | n_distinct
+---++--+
 common_student | school_id |
{79,60,25,61,59,69,86,77,64,33,56,78,58,81,41,97,22,67,38,23,3,72,92,93,48,24,96,26,75,90,70,52,51,21,14,91,83,54,85,11,68,94,53,88,1}
|  | 45
(1 row)

db=> select count(distinct(school_id)) from common_student ;
 count
---
55
(1 row)

db=> alter table common_student alter column school_id set statistics
10;
WARNING:  lowering statistics target to 1
ALTER TABLE
db=> analyse verbose common_student(school_id);
INFO:  analyzing "public.common_student"
INFO:  "common_student": scanned 7322 of 7322 pages, containing 65463 live
rows and 49026 dead rows; 51538 rows in sample, 65463 estimated total rows
ANALYZE
db=> select tablename,attname,most_common_vals,histogram_bounds,n_distinct
from pg_stats where attname='school_id'  and tablename='common_stude
nt';
   tablename|  attname  |
  most_common_vals
  | histogram_bounds | n_distinct
+---++--+
 common_student | school_id |
{79,60,25,61,59,69,86,77,64,33,56,78,58,81,41,97,22,67,38,23,3,72,92,93,48,24,96,26,75,90,70,52,51,21,14,91,83,54,85,11,68,94,53,88,1}
|  | 45
(1 row)

db=> explain analyse select * from common_student  where school_id = 36 ;
 QUERY
PLAN

 Index Scan using idx_common_student_sid on common_student
(cost=0.41..8.39 rows=1 width=385) (actual time=0.542..4.022 rows=1388
loops=1)
   Index Cond: (school_id = 36)
 Planning time: 0.334 ms
 Execution time: 6.542 ms
(4 rows)

db=> select school_id, count(*) from common_student  group by school_id
order by count(*)  limit 6 ;
 school_id | count
---+---
 1 |50
88 |   161
53 |   252
94 |   422
31 |   434
68 |   454
(6 rows)

dvpjxbzc=> select school_id, count(*) from common_student where school_id =
36  group by school_id ;
 school_id | count
---+---
36 |  1388
(1 row)

db=> explain analyse select * from common_student  where school_id = 1 ;
QUERY PLAN

--
 Bitmap Heap Scan on common_student  (cost=4.91..243.76 rows=64 width=385)
(actual time=0.128..0.267 rows=50 loops=1)
   Recheck Cond: (school_id = 1)
   Heap Blocks: exact=16
   ->  Bitmap Index Scan on "unique common_student"  (cost=0.00..4.90
rows=64 width=0) (a

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Virendra,

Thanks for the reply.

You must have missed it, but I've already done that (see my original
email). The stats target for that column is already set to 1.

db=> SELECT attstattarget FROM pg_attribute WHERE attrelid =
'public.common_student'::regclass AND attname = 'school_id';
 attstattarget
---
 1
(1 row)

Mark

On Fri, 21 Dec 2018 at 18:39 Kumar, Virendra 
wrote:

> Hi Mark,
>
>
>
> Can you try setting up stats target for school_id column and analyze table
> and see where it takes, something like:
>
> --
>
> ALTER table common_student ALTER COLUMN school_id SET STATISTICS 1;
>
> ANALYZE common_stundent;
>
>
>
> Regards,
>
> Virendra
>
>
>
> *From:* Mark [mailto:mwchamb...@gmail.com]
> *Sent:* Friday, December 21, 2018 11:39 AM
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* Query planner / Analyse statistics bad estimate rows=1 with
> maximum statistics 1 on PostgreSQL 10.2
>
>
>
> Hi All,
>
>
>
> I'm seeing some very slow queries and it looks like the query planner is
> deciding to do a 'Nested Loop Left Join'
>
> which is slow.  When I SET enable_nestloop=OFF for testing it does a 'Hash
> Left Join' which is much faster.
>
>
>
> I think  I can see the cause of the problem in the examples below. I can't
> make sense of the statistics being written
>
> by ANALYSE or the planners row estimates.
>
>
>
> Can anyone help me understand
>
>
>
> - Why the row estimate I get in the query below for school_id = 36 is 1 ?
>( I expect it to be higher)
>
> - Why does '1' appear in the most_common_vals when it is actually the
> least common value.
>
> - Why doesn't 36 appear in the most_common_vals (it is more common than 1)
>
> - Does the analyse output below mean that it only scanned 51538 of 65463
> rows in the table? Is school_id 36 just being missed in the sample? (This
> happens when the analyse is repeated )
>
>
>
> Any help with understanding what's happening here would be much
> appreciated.
>
>
>
> I hope I've provided enough information below.
>
>
>
> Thanks,
>
>
>
> Mark
>
>
>
> db=> explain analyse select * from common_student  where school_id = 36 ;
>
>  QUERY
> PLAN
>
>
> 
>
>  Index Scan using idx_common_student_sid on common_student
> (cost=0.41..8.39 rows=1 width=385) (actual time=0.264..1.691 rows=1388
> loops=1)
>
>Index Cond: (school_id = 36)
>
>  Planning time: 0.087 ms
>
>  Execution time: 2.706 ms
>
> (4 rows)
>
>
>
> db=> select tablename,attname,most_common_vals,histogram_bounds,n_distinct
> from pg_stats where attname='school_id'  and tablename='common_stude
>
> nt';
>
>tablename|  attname  |
>   most_common_vals
>   | histogram_bounds | n_distinct
>
>
> +---++--+
>
>  common_student | school_id |
> {79,60,25,61,59,69,86,77,64,33,56,78,58,81,41,97,22,67,38,23,3,72,92,93,48,24,96,26,75,90,70,52,51,21,14,91,83,54,85,11,68,94,53,88,1}
> |  | 45
>
> (1 row)
>
>
>
> db=> select count(distinct(school_id)) from common_student ;
>
>  count
>
> ---
>
> 55
>
> (1 row)
>
>
>
> db=> alter table common_student alter column school_id set statistics
> 10;
>
> WARNING:  lowering statistics target to 1
>
> ALTER TABLE
>
> db=> analyse verbose common_student(school_id);
>
> INFO:  analyzing "public.common_student"
>
> INFO:  "common_student": scanned 7322 of 7322 pages, containing 65463 live
> rows and 49026 dead rows; 51538 rows in sample, 65463 estimated total rows
>
> ANALYZE
>
> db=> select tablename,attname,most_common_vals,histogram_bounds,n_distinct
> from pg_stats where attname='school_id'  and tablename='common_stude
>
> nt';
>
>tablename|  attname  |
>   most_common_vals
>   | histogram_bounds | n_distinct
>
>
> +---++--+--

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Jeff,

Thanks for your help. That is exactly what is happening.

I have a long running job which deletes all of the common_student table and
then repopulates it. It takes long time to load all the other data and
commit the transaction. I didn't think the delete inside the transaction
would have any effect until it is commited or rolled back.

I will have to rewrite the application so it updates the existing rows
rather than deleting all and then inserting.

Thanks again for helping me understand what's happening here.

Proof:

db=> explain analyze select * from common_student where school_id = 36;
  QUERY PLAN

--
 Bitmap Heap Scan on common_student  (cost=88.30..3846.49 rows=1533
width=384) (actual time=4.852..7.065 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..87.91
rows=1533 width=0) (actual time=4.817..4.817 rows=1388 loops=1)
 Index Cond: (school_id = 36)
 Planning time: 0.097 ms
 Execution time: 8.084 ms
(7 rows)

db=> /* At this point I have started a long running transaction that
deletes all of common_student for school_id 36  */ ;

db=> analyse verbose common_student(school_id);
INFO:  analyzing "public.common_student"
INFO:  "common_student": scanned 7322 of 7322 pages, containing 65431 live
rows and 8060 dead rows; 56818 rows in sample, 65431 estimated total rows
ANALYZE
db=> explain analyze select * from common_student where school_id = 36;
 QUERY
PLAN

 Index Scan using idx_common_student_sid on common_student
(cost=0.41..8.43 rows=1 width=384) (actual time=0.017..1.498 rows=1388
loops=1)
   Index Cond: (school_id = 36)
 Planning time: 0.098 ms
 Execution time: 2.583 ms
(4 rows)

db=> /* At this point I have killed the long running transaction that
deletes all of common_student for school_id 36  */ ;
db=> vacuum analyze common_student;
VACUUM
db=> explain analyze select * from common_student where school_id = 36;
  QUERY PLAN

--
 Bitmap Heap Scan on common_student  (cost=79.17..3357.79 rows=1388
width=383) (actual time=0.088..1.302 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..78.83
rows=1388 width=0) (actual time=0.077..0.077 rows=1388 loops=1)
 Index Cond: (school_id = 36)
 Planning time: 0.327 ms
 Execution time: 2.311 ms
(7 rows)


On Sun, 23 Dec 2018 at 02:57 Jeff Janes  wrote:

>
>> - Does the analyse output below mean that it only scanned 51538 of 65463
>> rows in the table? Is school_id 36 just being missed in the sample? (This
>> happens when the analyse is repeated )
>>
>
> Is there a transaction which had deleted all of school_id=36, and then was
> just left open indefinitely without either committing or rolling back?
>
> That would explain it, and I don't know of anything else that could.  The
> deleted but not committed tuples are still live, but don't get sampled.
>
> Cheers,
>
> Jeff
>

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Maxim,

Thanks for your help.

Jeff has pointed me in the right direction here, it seems that the rows=1
is due to a long running transaction which deletes all of common_student
for school_id 36 and then repopulates it.

I was unaware that the delete inside the transaction would affect the
VACUUM ANALYSE. As I said to Jeff I will have to rewrite the application to
update the rows if this is the normal behaviour.

Here are the queries:


db=> explain analyze select * from common_student where school_id = 36;
  QUERY PLAN

--
 Bitmap Heap Scan on common_student  (cost=88.30..3846.49 rows=1533
width=384) (actual time=4.852..7.065 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..87.91
rows=1533 width=0) (actual time=4.817..4.817 rows=1388 loops=1)
 Index Cond: (school_id = 36)
 Planning time: 0.097 ms
 Execution time: 8.084 ms
(7 rows)


db=> /* At this point I have started a long running transaction that
deletes all of common_student for school_id 36  */ ;

db=> analyse verbose common_student(school_id);
INFO:  analyzing "public.common_student"
INFO:  "common_student": scanned 7322 of 7322 pages, containing 65431 live
rows and 8060 dead rows; 56818 rows in sample, 65431 estimated total rows
ANALYZE
db=> explain analyze select * from common_student where school_id = 36;
 QUERY
PLAN

 Index Scan using idx_common_student_sid on common_student
(cost=0.41..8.43 rows=1 width=384) (actual time=0.017..1.498 rows=1388
loops=1)
   Index Cond: (school_id = 36)
 Planning time: 0.098 ms
 Execution time: 2.583 ms
(4 rows)

db=> create table test_table AS SELECT * from common_student;
SELECT 65431
db=> vacuum analyze test_table;
VACUUM
db=> explain analyze select * from test_table where school_id = 36;
   QUERY PLAN

-
 Seq Scan on test_table  (cost=0.00..3848.89 rows=1381 width=384) (actual
time=10.105..22.680 rows=1388 loops=1)
   Filter: (school_id = 36)
   Rows Removed by Filter: 64043
 Planning time: 0.390 ms
 Execution time: 23.767 ms
(5 rows)

db=> drop table test_table;
DROP TABLE
db=> create table test_table AS SELECT * from common_student ORDER BY
school_id;
SELECT 65431
db=> vacuum analyze test_table;
VACUUM
db=> explain analyze select * from test_table where school_id = 36;
   QUERY PLAN


 Seq Scan on test_table  (cost=0.00..3850.89 rows=1341 width=382) (actual
time=5.674..27.585 rows=1388 loops=1)
   Filter: (school_id = 36)
   Rows Removed by Filter: 64043
 Planning time: 0.264 ms
 Execution time: 28.643 ms
(5 rows)

db=> explain analyze select * from test_table where school_id = 36;
   QUERY PLAN

-
 Seq Scan on test_table  (cost=0.00..3850.89 rows=1341 width=382) (actual
time=20.848..43.272 rows=1388 loops=1)
   Filter: (school_id = 36)
   Rows Removed by Filter: 64043
 Planning time: 0.068 ms
 Execution time: 44.423 ms
(5 rows)

db=> /* At this point I have killed the long running transaction that
deletes all of common_student for school_id 36  */ ;
db=> vacuum analyze common_student;
VACUUM
db=> explain analyze select * from common_student where school_id = 36;
  QUERY PLAN

--
 Bitmap Heap Scan on common_student  (cost=79.17..3357.79 rows=1388
width=383) (actual time=0.088..1.302 rows=1388 loops=1)
   Recheck Cond: (school_id = 36)
   Heap Blocks: exact=67
   ->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..78.83
rows=1388 width=0) (actual time=0.077..0.077 rows=1388 loops=1)
 Index Cond: (school_id = 36)
 Planning time: 0.327 ms
 Execution time: 2.311 ms
(7 rows)

On Sun, 23 Dec 2018 at 15:28 Maxim Boguk  wrote:

> Hi Mark,
>
> It's look very weird.
> Can you try something like this (check that you have enough disk space for
> second copy of common_student before):
>
> create table test_table AS SELECT * from common_student;
> Vacu

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Ron,

Yes, my process will commit the transaction (or roll it back) eventually.
It's the window where
one transaction has deleted all the rows (before committing) and an ANALYSE
has ran.

The deleted rows won't make it into the sample even though the transaction
has NOT been commited.

During this time I will get bad row estimates on rows for that ID. You can
see this in the example below with two
database connections (labelled 1 and 2).

I would have expected the DELETE to have no effect until it was committed.

connection 1=> create table test_table(school_id integer, note text);
CREATE TABLE
connection 1=> insert into test_table(school_id, note) SELECT
g.id,md5(random()::text)
FROM generate_series(1,2) as g(id);
INSERT 0 2
connection 1=> insert into test_table(school_id, note) SELECT
g.id,md5(random()::text)
FROM generate_series(1,2) as g(id);
INSERT 0 2
connection 1=> insert into test_table(school_id, note) SELECT
g.id,md5(random()::text)
FROM generate_series(1,2) as g(id);
INSERT 0 2
connection 1=> select * from test_table ;
 school_id |   note
---+--
 1 | 0e08cf3990a04f0e943584517c564d31
 2 | 96bf83ae5f8eb9342e8408b1ac25cb14
 1 | f8fd943012edfe42a03a421df660bc33
 2 | cd5d7ff0abca61f18857df9b21d234e0
 1 | 60d731f430cb68c7285ddbcd9186eaa0
 2 | 635e6c9cf305147ad8684213f0a9299c
(6 rows)

connection 1=> analyse verbose test_table ;
INFO:  analyzing "public.test_table"
INFO:  "test_table": scanned 1 of 1 pages, containing 6 live rows and 0
dead rows; 6 rows in sample, 6 estimated total rows
ANALYZE
connection 1=> explain analyse select * from test_table where school_id = 2
;
 QUERY PLAN

-
 Seq Scan on test_table  (cost=0.00..1.07 rows=3 width=37) (actual
time=0.011..0.015 rows=3 loops=1)
   Filter: (school_id = 2)
   Rows Removed by Filter: 3
 Planning time: 0.164 ms
 Execution time: 0.043 ms
(5 rows)

connection 2=> BEGIN ;
BEGIN
connection 2=> delete from test_table where school_id = 2 ;
DELETE 3
connection 2=> /* This connection is now idle */


connection 1=> analyse verbose test_table ;
INFO:  analyzing "public.test_table"
INFO:  "test_table": scanned 1 of 1 pages, containing 6 live rows and 0
dead rows; 3 rows in sample, 6 estimated total rows
ANALYZE
connection 1=> explain analyse select * from test_table where school_id = 2
;
 QUERY PLAN

-
 Seq Scan on test_table  (cost=0.00..1.07 rows=1 width=37) (actual
time=0.009..0.014 rows=3 loops=1)
   Filter: (school_id = 2)
   Rows Removed by Filter: 3
 Planning time: 0.095 ms
 Execution time: 0.039 ms
(5 rows)


On Wed, 2 Jan 2019 at 14:04 Ron  wrote:

> But Jeff said "left open indefinitely without either committing or rolling
> back".  Your process is committing the transaction.
>
>
> On 1/2/19 6:15 AM, Mark wrote:
>
> Hi Jeff,
>
> Thanks for your help. That is exactly what is happening.
>
> I have a long running job which deletes all of the common_student table
> and then repopulates it. It takes long time to load all the other data and
> commit the transaction. I didn't think the delete inside the transaction
> would have any effect until it is commited or rolled back.
>
> I will have to rewrite the application so it updates the existing rows
> rather than deleting all and then inserting.
>
> Thanks again for helping me understand what's happening here.
>
> Proof:
>
> db=> explain analyze select * from common_student where school_id = 36;
>   QUERY PLAN
>
>
> --
>  Bitmap Heap Scan on common_student  (cost=88.30..3846.49 rows=1533
> width=384) (actual time=4.852..7.065 rows=1388 loops=1)
>Recheck Cond: (school_id = 36)
>Heap Blocks: exact=67
>->  Bitmap Index Scan on idx_common_student_sid  (cost=0.00..87.91
> rows=1533 width=0) (actual time=4.817..4.817 rows=1388 loops=1)
>  Index Cond: (school_id = 36)
>  Planning time: 0.097 ms
>  Execution time: 8.084 ms
> (7 rows)
>
> db=> /* At this point I have started a long running transaction that
> deletes all of common_student for school_id 36  */ ;
>
> db=> analyse verbose common_student(school_id);
> INFO:  analyzing "public.common_student"
> INFO:  "common_student": scanned 7322 of 732

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Ron,

I tried my test_table example below using swapping 'BEGIN' for:

=> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

It made no difference to the behaviour.

On Wed, 2 Jan 2019 at 15:45 Ron  wrote:

> Try using SERIALIZABLE transactions instead of the default READ COMMITTED.
>
>
> On 1/2/19 9:28 AM, Mark wrote:
>
> Hi Ron,
>
> Yes, my process will commit the transaction (or roll it back) eventually.
> It's the window where
> one transaction has deleted all the rows (before committing) and an
> ANALYSE has ran.
>
> The deleted rows won't make it into the sample even though the transaction
> has NOT been commited.
>
> During this time I will get bad row estimates on rows for that ID. You can
> see this in the example below with two
> database connections (labelled 1 and 2).
>
> I would have expected the DELETE to have no effect until it was committed.
>
> connection 1=> create table test_table(school_id integer, note text);
> CREATE TABLE
> connection 1=> insert into test_table(school_id, note) SELECT 
> g.id,md5(random()::text)
> FROM generate_series(1,2) as g(id);
> INSERT 0 2
> connection 1=> insert into test_table(school_id, note) SELECT 
> g.id,md5(random()::text)
> FROM generate_series(1,2) as g(id);
> INSERT 0 2
> connection 1=> insert into test_table(school_id, note) SELECT 
> g.id,md5(random()::text)
> FROM generate_series(1,2) as g(id);
> INSERT 0 2
> connection 1=> select * from test_table ;
>  school_id |   note
> ---+--
>  1 | 0e08cf3990a04f0e943584517c564d31
>  2 | 96bf83ae5f8eb9342e8408b1ac25cb14
>  1 | f8fd943012edfe42a03a421df660bc33
>  2 | cd5d7ff0abca61f18857df9b21d234e0
>  1 | 60d731f430cb68c7285ddbcd9186eaa0
>  2 | 635e6c9cf305147ad8684213f0a9299c
> (6 rows)
>
> connection 1=> analyse verbose test_table ;
> INFO:  analyzing "public.test_table"
> INFO:  "test_table": scanned 1 of 1 pages, containing 6 live rows and 0
> dead rows; 6 rows in sample, 6 estimated total rows
> ANALYZE
> connection 1=> explain analyse select * from test_table where school_id =
> 2 ;
>  QUERY PLAN
>
>
> -
>  Seq Scan on test_table  (cost=0.00..1.07 rows=3 width=37) (actual
> time=0.011..0.015 rows=3 loops=1)
>Filter: (school_id = 2)
>Rows Removed by Filter: 3
>  Planning time: 0.164 ms
>  Execution time: 0.043 ms
> (5 rows)
>
> connection 2=> BEGIN ;
> BEGIN
> connection 2=> delete from test_table where school_id = 2 ;
> DELETE 3
> connection 2=> /* This connection is now idle */
>
>
> connection 1=> analyse verbose test_table ;
> INFO:  analyzing "public.test_table"
> INFO:  "test_table": scanned 1 of 1 pages, containing 6 live rows and 0
> dead rows; 3 rows in sample, 6 estimated total rows
> ANALYZE
> connection 1=> explain analyse select * from test_table where school_id =
> 2 ;
>  QUERY PLAN
>
>
> -
>  Seq Scan on test_table  (cost=0.00..1.07 rows=1 width=37) (actual
> time=0.009..0.014 rows=3 loops=1)
>Filter: (school_id = 2)
>Rows Removed by Filter: 3
>  Planning time: 0.095 ms
>  Execution time: 0.039 ms
> (5 rows)
>
>
> On Wed, 2 Jan 2019 at 14:04 Ron  wrote:
>
>> But Jeff said "left open indefinitely without either committing or
>> rolling back".  Your process is committing the transaction.
>>
>>
>> On 1/2/19 6:15 AM, Mark wrote:
>>
>> Hi Jeff,
>>
>> Thanks for your help. That is exactly what is happening.
>>
>> I have a long running job which deletes all of the common_student table
>> and then repopulates it. It takes long time to load all the other data and
>> commit the transaction. I didn't think the delete inside the transaction
>> would have any effect until it is commited or rolled back.
>>
>> I will have to rewrite the application so it updates the existing rows
>> rather than deleting all and then inserting.
>>
>> Thanks again for helping me understand what's happening here.
>>
>> Proof:
>>
>> db=> explain analyze select * from common_student where school_id = 36;
>>   QUERY PLAN
>>
>>
>> ---

Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2

[Mark]

Hi Tom,

Thanks for your reply.

Am I correct in my understanding that any row that has been modified (i.e.
UPDATE) is in state HEAPTUPLE_INSERT_IN_PROGRESS so it will not be included
in the sample?

I'm going to rework the application so there is less time between the
DELETE and the COMMIT so I will only see the problem if ANALYZE runs during
this smaller time window. Looks like your patch will help if this happens.

Then again, it also seems no one has had a problem with its current
behaviour (for 11 years!).

Thanks,

Mark

On Wed, 2 Jan 2019 at 16:11 Tom Lane  wrote:

> Mark  writes:
> > I have a long running job which deletes all of the common_student table
> and
> > then repopulates it. It takes long time to load all the other data and
> > commit the transaction. I didn't think the delete inside the transaction
> > would have any effect until it is commited or rolled back.
> > I will have to rewrite the application so it updates the existing rows
> > rather than deleting all and then inserting.
>
> Hmm ... I'm not sure if that will actually make things better.  The root
> of the issue is what analyze.c does with DELETE_IN_PROGRESS tuples:
>
>  * We count delete-in-progress rows as still live,
> using
>  * the same reasoning given above; but we don't bother
> to
>  * include them in the sample.
>
> The "reasoning given above" is a reference to what happens for
> INSERT_IN_PROGRESS tuples:
>
>  * Insert-in-progress rows are not counted.  We assume
>  * that when the inserting transaction commits or
> aborts,
>  * it will send a stats message to increment the proper
>  * count.  This works right only if that transaction
> ends
>  * after we finish analyzing the table; if things
> happen
>  * in the other order, its stats update will be
>  * overwritten by ours.  However, the error will be
> large
>  * only if the other transaction runs long enough to
>  * insert many tuples, so assuming it will finish
> after us
>  * is the safer option.
>
> Now the problem with this, from your perspective, is that *neither*
> INSERT_IN_PROGRESS nor DELETE_IN_PROGRESS tuples are included in
> ANALYZE's data sample.  So a long-running update transaction will
> cause all the rows it changes to be excluded from the sample until
> commit.  This seems like a bad thing, and it definitely means that
> adjusting your app as you're contemplating won't help.
>
> In order to handle the bulk-update scenario sanely, it seems like
> we ought to allow one of INSERT_IN_PROGRESS and DELETE_IN_PROGRESS tuples
> to be included.  And it looks like, for consistency with the row-counting
> logic, the one that's included needs to be DELETE_IN_PROGRESS.  This
> is a slightly annoying conclusion, because it means we're accumulating
> stats that we know are likely to soon be obsolete, but I think sampling
> INSERT_IN_PROGRESS tuples instead would lead to very strange results
> in some cases.
>
> In short, I think we want to do this to the DELETE_IN_PROGRESS logic:
>
> if
> (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(targtuple.t_data)))
> deadrows += 1;
> else
> +   {
> +   sample_it = true;
> liverows += 1;
> +   }
>
> with suitable adjustment of the adjacent comment.
>
> Thoughts?
>
> regards, tom lane
>

postgresql 9.5 has ocuuered OOM

[mark]

I have set shared_buffers is 1/4 of memory.
work_mem is 2% of memory.
max_connections is 50.
momery size is 16GB.
postgresql process used over 70% of memory and occuered OOM.
what should I do to deal with this problem?

Re:Re: postgresql 9.5 has ocuuered OOM

[mark]

Hi Tomas,Uwe,David G
Thanks for your reply.


>So, did a single process use 70% of memory, or all postgres processes
>combined?

all postgres processes use over  70% of memory.
>If just a single process, it might be a poor plan choice (e.g. hash
>aggregate may easily cause that).


because delete clause used a lot of memory .

delete clause is below:

DELETE  FROM test WHERE testid in (select r_id from test1 where p_id_id in ( 
select re_id from ap_en where link = $1))

delete from  test  where test1_id = $1 AND id = $2

because delete clause is using select condition. maybe It make memory useage 
high.

if I decrease the work_mem size,It will affect delete clause  execution 
efficiency,

I want the session unit to set work_mem size.

the OS level (cgconfig)  to set all postgres processes memory usage.

How about this setting ?




Regards,

Mark









At 2017-12-21 00:24:35, "Tomas Vondra"  wrote:
>On 12/20/2017 04:08 PM, mark wrote:
>> I have set shared_buffers is 1/4 of memory.
>> work_mem is 2% of memory.
>> max_connections is 50.
>
>That means if you have all 50 connections active, they may easily
>consume 100% of memory, because 50 * 2 is 100. It's even easier if the
>connections are executing complex queries, because each query may use
>multiple work_mem buffers. So 2% seems a bit too high.
>
>> momery size is 16GB.
>> postgresql process used over 70% of memory and occuered OOM.
>
>So, did a single process use 70% of memory, or all postgres processes
>combined?
>
>If just a single process, it might be a poor plan choice (e.g. hash
>aggregate may easily cause that).
>
>If all processes combined, then perhaps it's due to work_mem being too high.
>
>> what should I do to deal with this problem?
>> 
>
>Hard to say, until you provide enough information.
>
>regards
>
>-- 
>Tomas Vondra  http://www.2ndQuadrant.com
>PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

postgresql9.5 partition table

[mark]

I defined a table using 1000 partition tables.
When I deleted or updated this table. It used a lot of memory and postmaster 
process is killed by OS. When I defined the same table using 100 partition 
tables. It can normally be updated and deleted. what is reason of this?

Re: postgresql9.5 partition table

[mark]

Hi Herrera
Thanks for your reply.
I will try it in postgresql10.
Regards,
Mark

发自网易邮箱手机版


在2017年12月29日 21:46，Alvaro Herrera 写道：
mark wrote:
> I defined a table using 1000 partition tables.
> When I deleted or updated this table. It used a lot of memory and
> postmaster process is killed by OS. When I defined the same table
> using 100 partition tables. It can normally be updated and deleted.
> what is reason of this?

Postgres 9.5 is not prepared to deal with thousands of partitions.

You can try the improved partitioning with the new syntax introduced in
Postgres 10 -- you may have better luck there.  Please test it.
If it still doesn't work, let us know in detail what you tested and how
it failed.

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

best way to write large data-streams quickly?

[Mark Moellering]

Everyone,

We are trying to architect a new system, which will have to take several
large datastreams (total of ~200,000 parsed files per second) and place
them in a database.  I am trying to figure out the best way to import that
sort of data into Postgres.

I keep thinking i can't be the first to have this problem and there are
common solutions but I can't find any.  Does anyone know of some sort
method, third party program, etc, that can accept data from a number of
different sources, and push it into Postgres as fast as possible?

Thanks in advance,

Mark Moellering

Re: best way to write large data-streams quickly?

[Mark Moellering]

On Mon, Apr 9, 2018 at 12:01 PM, Steve Atkins  wrote:

>
> > On Apr 9, 2018, at 8:49 AM, Mark Moellering  com> wrote:
> >
> > Everyone,
> >
> > We are trying to architect a new system, which will have to take several
> large datastreams (total of ~200,000 parsed files per second) and place
> them in a database.  I am trying to figure out the best way to import that
> sort of data into Postgres.
> >
> > I keep thinking i can't be the first to have this problem and there are
> common solutions but I can't find any.  Does anyone know of some sort
> method, third party program, etc, that can accept data from a number of
> different sources, and push it into Postgres as fast as possible?
>
> Take a look at http://ossc-db.github.io/pg_bulkload/index.html. Check the
> benchmarks for different situations compared to COPY.
>
> Depending on what you're doing using custom code to parse your data and
> then do multiple binary COPYs in parallel may be better.
>
> Cheers,
>   Steve
>
>
>
(fighting google slightly to keep from top-posting...)

Thanks!

How long can you run COPY?  I have been looking at it more closely.  In
some ways, it would be simple just to take data from stdin and send it to
postgres but can I do that literally 24/7?  I am monitoring data feeds that
will never stop and I don't know if that is how Copy is meant to be used or
if I have to let it finish and start another one at some point?

Thanks for everyones' help and input!

Mark Moellering

Join condition parsing puzzle

[Mark Jeffcoat]

I'm looking at a query generated by SQLAlchemy. It works; Postgres is
perfectly happy to run it, and it gives answers that make sense to the
guy who wrote it. But I don't understand why it works.

Stripped way down ...

CREATE VIEW relation_a (id_c, id_v)
AS VALUES (1, 20),  (2, 21), (3, 22);

CREATE VIEW relation_b (id_c, id_v, id_p)
AS VALUES (1, 20, 300), (2, 21, 301);

CREATE VIEW relation_c (id_p)
AS VALUES (301);

SELECT *
FROM relation_a
LEFT JOIN relation_b
JOIN relation_c
ON (relation_c.id_p = relation_b.id_p)
ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);


I would have claimed before seeing this example that it wasn't even
grammatical; I thought the only legal place to write the ON clause was
immediately after the JOIN. Apparently not.

How should I read this query?  I'd appreciate any help understanding this.

-- 
Mark Jeffcoat
Austin, TX

Re: Join condition parsing puzzle

[Mark Jeffcoat]

On Thu, Aug 23, 2018 at 4:51 PM, Tom Lane  wrote:
>
> Mark Jeffcoat  writes:

> > I would have claimed before seeing this example that it wasn't even
> > grammatical; I thought the only legal place to write the ON clause was
> > immediately after the JOIN. Apparently not.
>
> You read it as
>
> SELECT *
> FROM
>   relation_a
>   LEFT JOIN (relation_b
>  JOIN relation_c
>  ON (relation_c.id_p = relation_b.id_p))
>   ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = 
> relation_b.id_v);
>
> There's no other valid way to parenthesize it, so that's what
> the parser does.



Thank you very much for your help, Tom. In retrospect, I see I'd
over-generalized the rule that sub-selects in the from clause require
an alias.

Clear now.

-- 
Mark Jeffcoat
Austin, TX

Re: Code of Conduct plan

[Mark Kirkwood]

On 15/09/18 08:17, Tom Lane wrote:

Yeah, this.  The PG community is mostly nice people, AFAICT.  I'll be
astonished (and worried) if the CoC committee finds much to do.  We're
implementing this mostly to make newcomers to the project feel that
it's a safe space.


Agreed. However I think the all-of-life clause gives an open door to 
potential less than well intentioned new members joining up to extend a 
SJW agenda. So in fact the unintended consequence of this may be a 
*less* safe place for some existing members - unless all of their social 
media utterances are agreeable to the angry militant left.



It's also worth reminding people that this is v1.0 of the CoC document.
We plan to revisit it in a year or so, and thereafter as needed, to
improve anything that's causing problems or not working well.


+1, At least this means we can address the above if it emerges as a problem

regards
Mark


regards, tom lane

PostgreSQl, PHP and IIS

[Mark Williams]

I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS.

 

I am trying to get this to work following any advice I have been able to
find on the web, but with no success. My configuration so far is as below.

 

PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll.

 

Both these dlls are located in the correct extensions directory according to
phpinfo.

 

According to phpinfo the Configuration File Path is "C:\Windows" and the
Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". I have the
same php.ini file at both locations.

 

I have copied the version of libpq.dll from the lib folder of the postgresql
installation to the root folder of the PHP installation.

 

I have added the bin folder of the postgresql installation to the Windows
system search path.

 

Phpinfo shows that pdo_pgsql extension is loaded but not pgsql.

 

I have also tried copying the dependent dlls (ssleay32.dll, libeay32.dll,
libintl-8.dll, libiconv-2.dll) from the postgresql bin folder to the php
root folder).

 

Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an
undefined function call error is displayed by php when I try and run any pg
php functions.

 

Can anyone please shed any light on what more I need to do?

 

Regards,

 

Mark

RE: PostgreSQl, PHP and IIS

[Mark Williams]

"Fatal error: Call to undefined function pg_connect".

Obviously, that is to be expected if pgsql module is not loading.

__

-Original Message-
From: Adrian Klaver  
Sent: 21 September 2018 23:02
To: Mark Williams ;
pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQl, PHP and IIS

On 9/21/18 10:35 AM, Mark Williams wrote:
> I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS.
> 
> I am trying to get this to work following any advice I have been able 
> to find on the web, but with no success. My configuration so far is as
below.
> 
> PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll.
> 
> Both these dlls are located in the correct extensions directory 
> according to phpinfo.
> 
> According to phpinfo the Configuration File Path is "C:\Windows" and 
> the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini". 
> I have the same php.ini file at both locations.
> 
> I have copied the version of libpq.dll from the lib folder of the 
> postgresql installation to the root folder of the PHP installation.
> 
> I have added the bin folder of the postgresql installation to the 
> Windows system search path.
> 
> Phpinfo shows that pdo_pgsql extension is loaded but not pgsql.
> 
> I have also tried copying the dependent dlls (ssleay32.dll, 
> libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin 
> folder to the php root folder).
> 
> Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an 
> undefined function call error is displayed by php when I try and run 
> any pg php functions.
> 
> Can anyone please shed any light on what more I need to do?

So what is the error you are getting?

Or to put it another way, what is not happening?

> 
> Regards,
> 
> Mark
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

RE: PostgreSQl, PHP and IIS

[Mark Williams]

Thanks for the suggestions.

I don't think there is any dependencies in the lib folder, but added it anyway 
and it made no difference.

I tried "Process Explorer" (having previously tried "Dependency Walker") PE 
revealed one or two more dependencies I was previously unaware of. Added these 
to the root PHP folder, but still got me no further. I have even added 
Postgres.EXE to the php root folder. 

In desperation I tried adding all the postgres files to the PHP Ext folder. 
Unsurprisingly that did not yield results either.

Does anyone know of a step by step guide for installing php and postgres on 
windows server? So I can doublecheck to see if there is anything I have missed. 
I can only assume I have made some simple mistake as it surely can't be this 
difficult to set up.

Cheers,

Mark

__

-Original Message-
From: Adrian Klaver  
Sent: 22 September 2018 16:45
To: Mark Williams ; pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQl, PHP and IIS

On 9/22/18 3:06 AM, Mark Williams wrote:
> "Fatal error: Call to undefined function pg_connect".
> 
> Obviously, that is to be expected if pgsql module is not loading.

Went back over your previous post and saw:

"I have copied the version of libpq.dll from the lib folder of the postgresql 
installation to the root folder of the PHP installation.

I have added the bin folder of the postgresql installation to the Windows 
system search path.
"

I would say add the lib/ of the Postgres install to the search path.

> 
> __
> 
> -Original Message-
> From: Adrian Klaver 
> Sent: 21 September 2018 23:02
> To: Mark Williams ; 
> pgsql-general@lists.postgresql.org
> Subject: Re: PostgreSQl, PHP and IIS
> 
> On 9/21/18 10:35 AM, Mark Williams wrote:
>> I have PHP v7.2.7 and PostgreSQL v10 with Windows IIS.
>>
>> I am trying to get this to work following any advice I have been able 
>> to find on the web, but with no success. My configuration so far is 
>> as
> below.
>>
>> PHP.ini extension list includes both php_pdo_pgsql.dll and php_pgsql.dll.
>>
>> Both these dlls are located in the correct extensions directory 
>> according to phpinfo.
>>
>> According to phpinfo the Configuration File Path is "C:\Windows" and 
>> the Loaded Configuration File is "C:\Program Files\PHP\v7.2\php.ini".
>> I have the same php.ini file at both locations.
>>
>> I have copied the version of libpq.dll from the lib folder of the 
>> postgresql installation to the root folder of the PHP installation.
>>
>> I have added the bin folder of the postgresql installation to the 
>> Windows system search path.
>>
>> Phpinfo shows that pdo_pgsql extension is loaded but not pgsql.
>>
>> I have also tried copying the dependent dlls (ssleay32.dll, 
>> libeay32.dll, libintl-8.dll, libiconv-2.dll) from the postgresql bin 
>> folder to the php root folder).
>>
>> Phpinfo show php_pdo_pgsql is enabled, but not php_pgsql.dll and an 
>> undefined function call error is displayed by php when I try and run 
>> any pg php functions.
>>
>> Can anyone please shed any light on what more I need to do?
> 
> So what is the error you are getting?
> 
> Or to put it another way, what is not happening?
> 
>>
>> Regards,
>>
>> Mark
>>
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

RE: PostgreSQl, PHP and IIS

[Mark Williams]

Hi thanks,

Sadly PHP and IIS isn't the issue. It’s when you bring Postgres into the fold 
that I am getting a problem. But thanks.

Mark

__

-Original Message-
From: Adrian Klaver  
Sent: 23 September 2018 15:36
To: Mark Williams ; pgsql-general@lists.postgresql.org; 
dan...@manitou-mail.org
Subject: Re: PostgreSQl, PHP and IIS

On 9/23/18 3:54 AM, Mark Williams wrote:
> Thanks for the suggestions.
> 
> I don't think there is any dependencies in the lib folder, but added it 
> anyway and it made no difference.
> 
> I tried "Process Explorer" (having previously tried "Dependency Walker") PE 
> revealed one or two more dependencies I was previously unaware of. Added 
> these to the root PHP folder, but still got me no further. I have even added 
> Postgres.EXE to the php root folder.
> 
> In desperation I tried adding all the postgres files to the PHP Ext folder. 
> Unsurprisingly that did not yield results either.
> 
> Does anyone know of a step by step guide for installing php and postgres on 
> windows server? So I can doublecheck to see if there is anything I have 
> missed. I can only assume I have made some simple mistake as it surely can't 
> be this difficult to set up.

Maybe this?:

https://docs.microsoft.com/en-us/iis/application-frameworks/install-and-configure-php-on-iis/install-and-configure-php

> 
> Cheers,
> 
> Mark
> 
> __
> 
> -Original Message-
> From: Adrian Klaver 
> Sent: 22 September 2018 16:45
> To: Mark Williams ; pgsql-general@lists.postgresql.org
> Subject: Re: PostgreSQl, PHP and IIS
> 
> On 9/22/18 3:06 AM, Mark Williams wrote:
>> "Fatal error: Call to undefined function pg_connect".
>>
>> Obviously, that is to be expected if pgsql module is not loading.
> 
> Went back over your previous post and saw:
> 
> "I have copied the version of libpq.dll from the lib folder of the postgresql 
> installation to the root folder of the PHP installation.
> 
> I have added the bin folder of the postgresql installation to the Windows 
> system search path.
> "
> 
> I would say add the lib/ of the Postgres install to the search path.
> 
>>



-- 
Adrian Klaver
adrian.kla...@aklaver.com

row_to_json white space

[Mark Lybarger]

Hi,

The row_to_json seems to strip white space:

select ROW_to_json(q) from (select 'fooa   bar' as TEST) q;

any help on how to preserve?

Thanks!
-mark-

Re: jsonb_set() strictness considered harmful to data

[Mark Felder]

On Fri, Oct 18, 2019, at 12:37, Ariadne Conill wrote:
> Hello,
> 
> I am one of the primary maintainers of Pleroma, a federated social
> networking application written in Elixir, which uses PostgreSQL in
> ways that may be considered outside the typical usage scenarios for
> PostgreSQL.
> 
> Namely, we leverage JSONB heavily as a backing store for JSON-LD
> documents[1].  We also use JSONB in combination with Ecto's "embedded
> structs" to store things like user preferences.
> 
> The fact that we can use JSONB to achieve our design goals is a
> testament to the flexibility PostgreSQL has.
> 
> However, in the process of doing so, we have discovered a serious flaw
> in the way jsonb_set() functions, but upon reading through this
> mailing list, we have discovered that this flaw appears to be an
> intentional design.[2]
> 
> A few times now, we have written migrations that do things like copy
> keys in a JSONB object to a new key, to rename them.  These migrations
> look like so:
> 
>update users set info=jsonb_set(info, '{bar}', info->'foo');
> 
> Typically, this works nicely, except for cases where evaluating
> info->'foo' results in an SQL null being returned.  When that happens,
> jsonb_set() returns an SQL null, which then results in data loss.[3]
> 
> This is not acceptable.  PostgreSQL is a database that is renowned for
> data integrity, but here it is wiping out data when it encounters a
> failure case.  The way jsonb_set() should fail in this case is to
> simply return the original input: it should NEVER return SQL null.
> 
> But hey, we've been burned by this so many times now that we'd like to
> donate a useful function to the commons, consider it a mollyguard for
> the real jsonb_set() function.
> 
> create or replace function safe_jsonb_set(target jsonb, path
> text[], new_value jsonb, create_missing boolean default true) returns
> jsonb as $$
> declare
>   result jsonb;
> begin
>   result := jsonb_set(target, path, coalesce(new_value,
> 'null'::jsonb), create_missing);
>   if result is NULL then
> return target;
>   else
> return result;
>   end if;
> end;
> $$ language plpgsql;
> 
> This safe_jsonb_set() wrapper should not be necessary.  PostgreSQL's
> own jsonb_set() should have this safety feature built in.  Without it,
> using jsonb_set() is like playing russian roulette with your data,
> which is not a reasonable expectation for a database renowned for its
> commitment to data integrity.
> 
> Please fix this bug so that we do not have to hack around this bug.
> It has probably ruined countless people's days so far.  I don't want
> to hear about how the function is strict, I'm aware it is strict, and
> that strictness is harmful.  Please fix the function so that it is
> actually safe to use.
> 
> [1]: JSON-LD stands for JSON Linked Data.  Pleroma has an "internal
> representation" that shares similar qualities to JSON-LD, so I use
> JSON-LD here as a simplification.
> 
> [2]: 
> https://www.postgresql.org/message-id/flat/qfkua9$2q0e$1...@blaine.gmane.org
> 
> [3]: https://git.pleroma.social/pleroma/pleroma/issues/1324 is an
> example of data loss induced by this issue.
> 
> Ariadne
>

This should be directed towards the hackers list, too.

What will it take to change the semantics of jsonb_set()? MySQL implements safe 
behavior here. It's a real shame Postgres does not. I'll offer a $200 bounty to 
whoever fixes it. I'm sure it's destroyed more than $200 worth of data and 
people's time by now, but it's something.


Kind regards,



-- 
  Mark Felder
  ports-secteam & portmgr alumni
  f...@freebsd.org

Re: jsonb_set() strictness considered harmful to data

[Mark Felder]

On Mon, Oct 28, 2019, at 08:52, Andrew Dunstan wrote:
> 
> For release 13+, I have given some more thought to what should be done.
> I think the bar for altering the behaviour of a function should be
> rather higher than we have in the present case, and the longer the
> function has been sanctioned by time the higher the bar should be.
> However, I think there is a case to be made for providing a non-strict
> jsonb_set type function. To advance th4e discussion, attached is a POC
> patch that does that. This can also be done as an extension, meaning
> that users of back branches could deploy it immediately. I've tested
> this against release 12, but I think it could go probably all the way
> back to 9.5. The new function is named jsonb_ set_lax, but I'm open to
> bikeshedding.
> 
> 

Thank you Andrew, and I understand the difficulty in making changes to 
functions that already exist in production deployments. An additional function 
like this would be helpful to many.


-- 
  Mark Felder
  ports-secteam & portmgr alumni
  f...@freebsd.org

Re: Remote Connection Help

[Mark Johnson]

As I recall, if the listening address is set to '*' but is showing
localhost, then the problem you describe is likely due to missing an IPv6
address in pg_hba.conf.  For me, I just added a line to pg_hba.conf like
this:
hostall all ::1/128 md5

So, even though my client app is on the db server and the connection string
has an IPv4 address the connection request still gets to PostgreSQL as IPv6
and fails until I added the line shown above.

Did your netstat output have two lines for the port numbers used by
PostgreSQL or just one of them?  My computer has two like this,
$ netstat -nlt | grep 5432
tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN
tcp6   0  0 ::1:5432:::*LISTEN



On Thu, Nov 21, 2019 at 1:41 PM Jason L. Amerson 
wrote:

> Yes "listen_addresses" is not commented. I did notice when I did the
> netstat, for tcp, it was all "127.0.0.1" on various ports including 5432
> but I have a listing for tcp6 that has my static IP using port 32305. Would
> that make a difference?
>
> Jason L. Amerson
>
> -Original Message-
> From: Tom Lane 
> Sent: Thursday, November 21, 2019 01:18 PM
> To: Jason L. Amerson 
> Cc: 'Steve Crawford' ; 'Adrian Klaver' <
> adrian.kla...@aklaver.com>; 'PostgreSQL' <
> pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
>
> "Jason L. Amerson"  writes:
> > I connected to PostgreSQL locally. I ran “show listen_addresses;” and it
> returned “localhost.” I ran “show port;” and it returned “5432.” I am now
> confused. I edited the “postgresql.conf” file and change the setting to
> ‘*’. Then I restarted the server with “service postgresql restart.” I was
> in root since I had to edit the config files. I thought maybe I edited the
> wrong file, like maybe there were two in two different locations or
> something. I ran “show confg_file;” and it returned
> “/usr/local/psql/data/postgresql.conf.” That is the same file I edited from
> the start. To be sure, I edited the file by using “nano
> /usr/local/psql/data/postgresql.conf.” I went down and found that I did
> have it as “listen_addresses = ‘*’ yet when I run “show listen_addresses”,
> it shows “localhost.” I am confused. When I run “netstat -nlt”, the results
> show that it is listening to “127.0.0.1:5432.”
>
> According to what you wrote here, you did everything right, so it's
> something you failed to mention.
>
> One thing I'm wondering is whether you removed the comment symbol (#) from
> the listen_addresses line when you edited it.  As installed,
> postgresql.conf is pretty much all comments.
>
> You might get more insight from
>
> select * from pg_settings where name = 'listen_addresses';
>
> particularly the source, sourcefile, sourceline fields.
>
> regards, tom lane
>
>
>
>

RE: UPDATE many records

[Mark Zellers]

Just out of curiosity, what kind of trigger are you using, a row level trigger 
or a statement level trigger?  If you are using a row level trigger, see if you 
can achieve your requirements using a statement level trigger instead.  I’m 
relatively new to Postgres, so there could be some limit that I’m not aware of, 
but my understanding is that you have access to the old and new values of the 
updated rows in the after statement trigger.  It would likely be much more 
performant to do your operation once after the statement is done rather than 
firing a trigger on every changed row.

Regards,

Mark Z.


From: Israel Brewster 
Sent: Monday, January 6, 2020 1:24 PM
To: Michael Lewis 
Cc: Rob Sargent ; Alban Hertroys ; 
Christopher Browne ; pgsql-generallists.postgresql.org 

Subject: Re: UPDATE many records


On Jan 6, 2020, at 11:54 AM, Michael Lewis 
mailto:mle...@entrata.com>> wrote:

I’m thinking it might be worth it to do a “quick” test on 1,000 or so records 
(or whatever number can run in a minute or so), watching the processor 
utilization as it runs. That should give me a better feel for where the 
bottlenecks may be, and how long the entire update process would take. I’m 
assuming, of course, that the total time would scale more or less linearly with 
the number of records.

I think that depends on how your identify and limit the update to those 1000 
records. If it is using a primary key with specific keys in an array, probably 
close to linear increase because the where clause isn't impactful to the 
overall execution time. If you write a sub-query that is slow, then you would 
need to exclude that from the time. You can always run explain analyze on the 
update and rollback rather than commit.
So a test run on 9,299 records took about 7 seconds to complete (EXPLAIN 
ANALYZE output at https://explain.depesz.com/s/lIYn if it matters), during 
which time I did see a postmaster process consuming 100% CPU. Upping the test 
to 20,819 records took about 16.5 seconds, so that looks relatively linear to 
me. Also, CPU bound. So by my calculations, doing all 50M records would take 
around 10 hours.

One potentially significant note: most of the execution time is spent in a 
trigger. This trigger is actually what’s doing the REAL update that I need to 
happen. If it would make a difference, I could easily pull the trigger code out 
to a separate function that I just call directly (with triggers temporarily 
disabled). My thinking is that calling a function is calling a function, and 
the fact that it is currently called via a trigger rather than direct is of 
little consequence, but I’m willing to be corrected on that :-)

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

RE: UPDATE many records

[Mark Zellers]

You don’t tell us if other users will be concurrently changing any of the 
records involved.  If you could guarantee that the table won’t be changed, you 
might be better off doing a CREATE TABLE table_new as SELECT … FROM table_old, 
dropping table_old, and finally renaming table_new.   Given the way Postgres 
handles updates, I would think that might perform significantly better.  Even 
if you did the work in batches (create a new table, insert/select from the old 
table, drop, rename), that could well be better.  Especially if you re-create 
the indexes after all the data is moved.



From: Israel Brewster 
Sent: Monday, January 6, 2020 10:36 AM
To: pgsql-general@lists.postgresql.org
Subject: UPDATE many records

Thanks to a change in historical data, I have a need to update a large number 
of records (around 50 million). The update itself is straight forward, as I can 
just issue an "UPDATE table_name SET changed_field=new_value();" (yes, 
new_value is the result of a stored procedure, if that makes a difference) 
command via psql, and it should work. However, due to the large number of 
records this command will obviously take a while, and if anything goes wrong 
during the update (one bad value in row 45 million, lost connection, etc), all 
the work that has been done already will be lost due to the transactional 
nature of such commands (unless I am missing something).

Given that each row update is completely independent of any other row, I have 
the following questions:

1) Is there any way to set the command such that each row change is committed 
as it is calculated?
2) Is there some way to run this command in parallel in order to better utilize 
multiple processor cores, other than manually breaking the data into chunks and 
running a separate psql/update process for each chunk? Honestly, manual 
parallelizing wouldn’t be too bad (there are a number of logical segregations I 
can apply), I’m just wondering if there is a more automatic option.
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory
Geophysical Institute - UAF
2156 Koyukuk Drive
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

Shared buffer hash table corrupted

[Mark Fletcher]

Hi All,

Running 9.6.15, this morning we got a 'shared buffer hash table corrupted'
error on a query. I reran the query a couple hours later, and it completed
without error. This is running in production on a Linode instance which
hasn't seen any config changes in months.

I didn't find much on-line about this. How concerned should I be? Would you
move the instance to a different physical host?

Thanks,
Mark

Re: Shared buffer hash table corrupted

[Mark Fletcher]

On Fri, Feb 21, 2020 at 2:53 PM Tom Lane  wrote:

>
> Personally, I'd restart the postmaster, but not do more than that unless
> the error recurs.
>

Thanks for the response. I did restart the postmaster yesterday. Earlier
this morning, a query that normally completes fine started to error out
with 'invalid memory alloc request size 18446744073709551613'. Needless to
say our database isn't quite that size. This query was against a table in a
different database than the one that had the corruption warning yesterday.
Restarting the postmaster again fixed the problem. For good measure I
restarted the machine as well.

I need to decide what to do next, if anything. We have a hot standby that
we also run queries against, and it hasn't shown any errors. I can switch
over to that as the primary. Or I can move the main database to a different
physical host.

Thoughts appreciated.

Thanks,
Mark

Re: Shared buffer hash table corrupted

[Mark Fletcher]

On Sat, Feb 22, 2020 at 9:34 AM Tom Lane  wrote:

>
> Um.  At that point I'd agree with your concern about developing hardware
> problems.  Both of these symptoms could be easily explained by dropped
> bits in PG's shared memory area.  Do you happen to know if the server
> has ECC RAM?
>
> Yes, it appears that Linode uses ECC and other server grade hardware for
their machines.

Thanks,
Mark

What do null column values for pg_stat_progress_vacuum mean?

[Mark Haylock]

Hi,

We have an autovacuum process that has been running for almost 27 hours:

SELECT * FROM pg_stat_activity WHERE pid = 11731;
-[ RECORD 1 ]+---
datid| 16385
datname  | database_name
pid  | 11731
usesysid |
usename  |
application_name |
client_addr  |
client_hostname  |
client_port  |
backend_start| 2020-03-04 23:40:14.828138+00
xact_start   | 2020-03-04 23:40:14.849367+00
query_start  | 2020-03-04 23:40:14.849367+00
state_change | 2020-03-04 23:40:14.849368+00
wait_event_type  |
wait_event   |
state| active
backend_xid  |
backend_xmin | 3801997676
query| autovacuum: VACUUM public.responses
backend_type | autovacuum worker

A row shows up in pg_stat_progress_vacuum, but it contains null values
for every column.

SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731;
-[ RECORD 1 ]--+---
pid| 11731
datid  | 16385
datname| d2j496215lfs41
relid  |
phase  |
heap_blks_total|
heap_blks_scanned  |
heap_blks_vacuumed |
index_vacuum_count |
max_dead_tuples|
num_dead_tuples|

I see nothing in the documentation to suggest that this is an expected
state - what does it mean?

Thanks,
Mark.

Re: What do null column values for pg_stat_progress_vacuum mean?

[Mark Haylock]

Sorry I've failed to mention which postgres version this is with:
PostgreSQL 10.11.

On Fri, Mar 6, 2020 at 3:39 PM Mark Haylock  wrote:
>
> Hi,
>
> We have an autovacuum process that has been running for almost 27 hours:
>
> SELECT * FROM pg_stat_activity WHERE pid = 11731;
> -[ RECORD 1 ]+---
> datid| 16385
> datname  | database_name
> pid  | 11731
> usesysid |
> usename  |
> application_name |
> client_addr  |
> client_hostname  |
> client_port  |
> backend_start| 2020-03-04 23:40:14.828138+00
> xact_start   | 2020-03-04 23:40:14.849367+00
> query_start  | 2020-03-04 23:40:14.849367+00
> state_change | 2020-03-04 23:40:14.849368+00
> wait_event_type  |
> wait_event   |
> state| active
> backend_xid  |
> backend_xmin | 3801997676
> query| autovacuum: VACUUM public.responses
> backend_type | autovacuum worker
>
> A row shows up in pg_stat_progress_vacuum, but it contains null values
> for every column.
>
> SELECT * FROM pg_stat_progress_vacuum WHERE pid = 11731;
> -[ RECORD 1 ]--+---
> pid| 11731
> datid  | 16385
> datname| d2j496215lfs41
> relid  |
> phase  |
> heap_blks_total|
> heap_blks_scanned  |
> heap_blks_vacuumed |
> index_vacuum_count |
> max_dead_tuples|
> num_dead_tuples|
>
> I see nothing in the documentation to suggest that this is an expected
> state - what does it mean?
>
> Thanks,
> Mark.



-- 
Mark Haylock - Developer
Sydney | Christchurch | Auckland | Boulder
e: m...@trineo.com | w: trineo.com | ph: +64 3 377 4001

Best method to display table information in predefined formats

[Mark Bannister]

I am converting an application to postgresql.  On feature I have is
functions that return custom displays of a table row.  For instance the
company display function might display just the company name, or company
name + other information.  It may also call other displays, for
instance, address or phone numbers which in turn have display
functions.  What is returned depends on context and other parameters. 
The return value is typically displayed the ui in views of the table. 
For instance a listing of purchase orders could list PO number, and use
the company display function to list the company information.  Depending
on the display it could show just the company name or complete details
of the company (in the context of the PO).

There is also a generic function that will display per-defined default
fields so that a custom function is not required for each table.

What is the best way to reproduce this and take advantage of postgresql
caching?  These functions can be called a lot.

-- 

Mark B

Re: Best method to display table information in predefined formats

[Mark Bannister]

On 4/8/2020 10:28 AM, Adrian Klaver wrote:
> On 4/8/20 6:39 AM, Mark Bannister wrote:
>> I am converting an application to postgresql.  On feature I have is
>> functions that return custom displays of a table row.  For instance
>> the company display function might display just the company name, or
>> company name + other information.  It may also call other displays,
>> for instance, address or phone numbers which in turn have display
>> functions.  What is returned depends on context and other
>> parameters.  The return value is typically displayed the ui in views
>> of the table.  For instance a listing of purchase orders could list
>> PO number, and use the company display function to list the company
>> information.  Depending on the display it could show just the company
>> name or complete details of the company (in the context of the PO).
>>
>> There is also a generic function that will display per-defined
>> default fields so that a custom function is not required for each table.
>>
>> What is the best way to reproduce this and take advantage of
>> postgresql caching?  These functions can be called a lot.
>
> I am not understanding what you are after, especially this part:
>
> "The return value is typically displayed the ui in views of the table"
>
> Are you saying the functions are called to fill in fields in a UI form
> or to populate a database side view?
>
>>
>> -- 
>>
>> Mark B
>>
>
>

Yes.  That's correct.

-- 

Mark B

serial + db key, or guid?

[Mark Phillips]

Given four instances of posgres, each with a database, each instance receiving 
new data, and desiring a data “merge” a la BDR or similar multiple database 
solutions, my team has been discussing the pros and cons of generating unique 
keys in each table.

1. create a unique “database” id for each database, add a column to all tables 
for this id value and combine that with a serial id
2. use guid from pg functions
3. create id ranges for each database, e.g. db1 gets 1 to 1M, db2 gets 1M+1 to 
2M, and so on

All get us to the finish line, but each has its drawbacks. 

Advice, cautionary tales, suggestions and such will be warmly received.

 - Mark

Re: serial + db key, or guid?

[Mark Phillips]

Thank you for the reply.

The article is a good one. I posed the question on the chance things had 
evolved since 2012, specifically as it relates to postgres.

> On Aug 10, 2020, at 3:21 PM, Christophe Pettus  wrote:
> 
> 
> 
>> On Aug 10, 2020, at 15:19, Mark Phillips  wrote:
>> Advice, cautionary tales, suggestions and such will be warmly received.
> 
> Here's one solution a company found for this; it seems to work very well:
> 
>   https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c
> 
> --
> -- Christophe Pettus
>   x...@thebuild.com
> 

Re: serial + db key, or guid?

[Mark Phillips]

Thanks to everyone who replied. All helpful. I learned and have new ideas to 
work with.

> On Aug 11, 2020, at 10:42 AM, Adam Brusselback  
> wrote:
> 
> I mentioned this in another email thread yesterday about a similar topic, but 
> I'd highly suggest if you do go the UUID route, do not use the standard UUID 
> generation functions, they all suck for database use (v1 also sucks).
> 
> I use: https://pgxn.org/dist/sequential_uuids/ 
>  written by Thomas Vondara (writeup 
> here: https://2ndquadrant.com/en/blog/sequential-uuid-generators/ 
>  )
> 
> I don't mind having a time component correlated with my UUID's because it's 
> simply not a threat model that matters for my use case, so I use the time 
> based variant. It helped me immensely with FPW and 
> write amplification when I switched from V4 UUIDs. It is still not as fast as 
> an int, but it is much much better than random UUIDs.
> 
>  

Re: Handling time series data with PostgreSQL

[Mark Johnson]

I think the OP may be referring to Oracle's Temporal Validity feature.
This type of feature has yet to be implemented in PostgreSQL (see
https://www.postgresql.org/docs/13/unsupported-features-sql-standard.html
item T181).

Temporal Validity allows you to add a time dimension to any table, and only
display rows of data that are valid for the requested time period.
Oracle's implementation of Temporal Validity uses the PERIOD FOR clause in
CREATE TABLE, ALTER TABLE, and SELECT statements as illustrated below:

CREATE TABLE EMPLOYEE
(
   IDNUMBER PRIMARY KEY,
   TAX_IDVARCHAR2(10),
   HIRE_DATE TIMESTAMP,
   TERM_DATE TIMESTAMP,
   PERIOD FOR EMP_VALID_TIME (HIRE_DATE, TERM_DATE)
);

SELECT * FROM EMPLOYEE
   VERSIONS PERIOD FOR EMP_VALID_TIME
   BETWEEN TO_TIMESTAMP('06-OCT-2013', 'DD-MON-')
   AND TO_TIMESTAMP('31-OCT-2013', 'DD-MON-');

   ID TAX_ID HIRE_DATETERM_DATE
- -- --
--
1 123456789  06-OCT-13 12.00.00.00 AM   07-NOV-15 12.00.00.00 AM
2 222456789  07-OCT-13 12.00.00.00 AM
4 44400
5 505050505  30-OCT-13 12.00.00.00 AM   31-OCT-13 12.00.00.00 AM
6 666999666  30-SEP-13 12.00.00.00 AM   31-DEC-13 12.00.00.00 AM

The above feature requires Oracle 12 or higher.  SQL Server 2016 and later
also support it.  In earlier releases of each DBMS we tried to accomplish
the same by adding pairs of timestamp columns to each table and then
writing our own code to handle row filtering.  Partitioning isn't needed.
Certainly partitioning by range could be used, but it would still require
some manual efforts.

-Mark

On Wed, Oct 7, 2020 at 10:41 AM Stephen Frost  wrote:

> Greetings,
>
> * Jayaram (jairam...@gmail.com) wrote:
> > So, Do we need the timescaleDB as mandatory to handle time series data?
> Is
> > there any way to handle hourly to days,months,yearly data with PGSQL
> alone
> > without timescale addon?
>
> Certainly there is and a lot of people do it- what isn't clear is what
> it is you feel is missing from PG when it comes to handling time series
> data..?  Generally speaking there's concerns about PG's ability to
> handle lots of partitions (which comes from there being very large
> amounts of data being stored), but v12 and v13 have made great
> improvements in that area and it's not nearly an issue any longer (and
> performs better in quite a few cases than extensions).
>
> > Ours is a new project and we are unsure about whether we should have both
> > timescaleDB and PGSQL or PGSQL alone is capable of handling this time
> > series data by tuning the right indexes.etc..
>
> Partitioning and index tuning in PG (look at using BRIN if you haven't
> already...) is important when you get to larger data volumes.
>
> Thanks,
>
> Stephen
>

Re: Hot backup in PostgreSQL

[Mark Johnson]

User managed backups in PostgreSQL work very similar to what you know from
Oracle.  You first place the cluster in backup mode, then copy the database
files, and lastly take the cluster out of backup mode.  The first and last
steps are done using functions pg_start_backup('label',false,false) and
pg_stop_backup(false, false). [1].

If you use a utility supplied with PostgreSQL such as pg_basebackup, it
does these steps for you.  If you are using a specific non-PostgreSQL
utility (i.e., Dell Networker or IBM Tivoli) see their documentation for
specifics.

[1]
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP.


On Thu, Oct 22, 2020 at 11:14 AM Adam Brusselback 
wrote:

> > how to do "hot backup" (copying files) while database running?
> As others have shown, there are ways to do this with PG's internal tooling
> (pg_basebackup).
>
> However, I would highly recommend you use an external backup tool like
> pgbackrest [1] to save yourself the pain of implementing things incorrectly
> and ending up with non-viable backups when you need them most. I'm not
> affiliated with them at all, but have just used pgbackrest in production
> for years now with great results.  It takes care of PITR, and manages
> backup retention (and associated WAL retention). Those can be a bit of a
> pain to do manually otherwise.
>
> Just my $0.02, hope it helps!
>
> 1. https://pgbackrest.org/
>

How to set up a schema default date to '2020-01-01'?

[mark armon]

How to set up a schema default date (now) to '2020-01-01'?  Whatever
timezone would be OK.

Re: How to set up a schema default date to '2020-01-01'?

[mark armon]

like I create a schema: test, I want the default date to 2020-01-01, so
when I do

select test.now;

the result is 2020-01-01


On Mon, Nov 9, 2020 at 9:44 PM David G. Johnston 
wrote:

> On Mon, Nov 9, 2020 at 9:08 AM mark armon <1994hej...@gmail.com> wrote:
>
>>
>> How to set up a schema default date (now) to '2020-01-01'?  Whatever
>> timezone would be OK.
>>
>
> What is a "schema default" (date or otherwise)?
>
> David J.
>

Re: Discovering postgres binary directory location

[Mark Johnson]

On any given server there could be zero, one, or many PGHOME/bin
locations.  The OP wants to identify all of them.  The default location
used by package-based installers is different from the default location of
software built from source, and when building from source you can specify a
non-default location.  My server has PG 9.6, 12.1, and 13 from RPM and also
12.1 from source.

The solution might be as easy as finding all installations of a core
PostgreSQL executable like pg_ctl.  I would not search for psql since you
will find one or more links in system folders and so your monitoring tool
would need some logic to filter out the links.  The word postgres is both
an executable and a user directory name.  Hence I am using pg_ctl in my
example.  This example was written on CentOS 7.7.

# find / -name pg_ctl
/usr/pgsql-13/bin/pg_ctl
/usr/local/pgsql/bin/pg_ctl
/usr/pgsql-12/bin/pg_ctl
/usr/pgsql-9.6/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl
/root/Downloads/postgresql-12.1/src/bin/pg_ctl/pg_ctl
You have mail in /var/spool/mail/root

Notice above my server has three RPM installs and one source code install.
Also notice the results contain two false positives (in the Downloads
directory) and also a message about mail.  You'll have to figure out how to
separate the good and bad results.

To strip off the file name and only return the directory, you can used the
-printf option as shown below or if not supported on your system use a host
command like sed or awk.  This does not remove false positives or system
messages.

find / -name 'pg_ctl' -printf "%h\n"
/usr/pgsql-13/bin
/usr/local/pgsql/bin
/usr/pgsql-12/bin
/usr/pgsql-9.6/bin
/root/Downloads/postgresql-12.1/src/bin
/root/Downloads/postgresql-12.1/src/bin/pg_ctl
You have mail in /var/spool/mail/root

Careful when stripping out the false positives.  PostgreSQL installed from
source can be pretty much anywhere including a Downloads directory, /tmp,
and so on.  In my case, the Downloads directory has a src sub-directory,
which tells me it is just a staging area for source code.

-Mark

On Thu, Nov 12, 2020 at 1:22 AM Paul Förster 
wrote:

> Hi Raul, hi Adrian,
>
> > On 11. Nov, 2020, at 23:26, Adrian Klaver 
> wrote:
> >
> > On 11/11/20 2:22 PM, Raul Kaubi wrote:
> >> Hi
> >> CentOS 7
> >> Postgres 9 to 12
> >> I am looking ways to universally discover postgresql binary directory
> for monitoring purpose.
> >> For example postgres 12, it is: */usr/pgsql-12*
> >
> > pg_config --bindir
> > /usr/local/pgsql12/bin
>
> or by query:
>
> postgres=# select setting from pg_config where name = 'BINDIR';
>  setting
> -
>  /data/postgres/12.4/bin
> (1 row)
>
> Cheers,
> Paul
>
>

Re: pg_dump - how to force to show timestamps in client log

[Mark Johnson]

This all sounds like a previous discussion on pg hackers about a progress
meter for pg_dump.  Search the archives for that discussion.  Also, search
the web for something like "pg_dump progress meter" and you'll get a few
suggestions like pipe to pv, although that does not appear to work with all
of the file formats supported by pg_dump.

What do you see in pg_stat_activity?  It's been a while since I tried to
monitor a running pg_dump.

 Also, if you redirect the output to a file then doesn't the file's
timestamp get updated each time something happens.  (That's what I used to
do with Oracle before they added timestamps).

On Fri, Nov 20, 2020 at 5:00 PM Ron  wrote:

> On 11/20/20 3:39 PM, Adrian Klaver wrote:
> > On 11/20/20 1:00 PM, Ron wrote:
> >> On 11/20/20 2:56 PM, Adrian Klaver wrote:
> >>> On 11/20/20 10:01 AM, Durumdara wrote:
>  Hello!
> 
>  We need to log the pg_dump's state.
>  What objects are in copy, and what are the starting and ending times.
> 
>  But when I try to redirect the output, the result doesn't have
> timestamps.
> 
>  PG 11, on Windows.
> 
>  As I see the -v option isn't enough to see the starting times.
> 
>  For example:
> 
>  2020-11-19 12:00:01.084 Dump table content table1
>  2020-11-19 12:03:12.932 Dump table content table2
>  ...
>  etc.
> >>>
> >>>
> >>> If you are redirecting to a file it have the creation time that you
> can
> >>> use. Internally times don't really matter for the objects as the dump
> is
> >>> based on a snapshot. Said snapshot is based on visible transactions
> not
> >>> time. So for practical purposes they all occur at the same 'time'.
> >>
> >> It makes all the difference when monitoring the progress of a backup.
> >>
> >
> > With -v you will get running list of objects dumped, just not the time.
> > The time is only of value relative to the following. Progress will only
> be
> > measurable by determining what is left to run and the time for each
> > object. Not sure that is feasible as you would have to pre-run the dump
> to
> > get information about the number of objects and an estimate of the data
> > quantity involved and the effect of each on the other. I could see that
> > estimate getting worse the bigger the data set(and hence the more you
> > cared) got. Because at some point the load on the machine would affect
> the
> > output speed of the dump.
>
> By knowing the sizes of the tables, and how long it takes to takes the
> first
> "some" tables, then one can forecast how long it takes to backup the whole
> database.
>
> --
> Angular momentum makes the world go 'round.
>
>
>

limit of data type character varying

[Mark Phillips]

Ran into a problem with SymmetricDS when attempting to sync two postgres 12 
databases. 

The problem occurs when Symmetric builds the XML for a table that has the 
character varying datatype with an undefined length. SymmetricDS creates in the 
xml character field that is 2,147,483,647 length. Subsequently, when 
SymmetricDS attempts to create the table on the client, an error is raised that 
says the character varying field exceeds the maximum length. 

The actual maximum length supported by postgresql 12 is 10,485,760. We dug into 
the postgres code and found a limit in the config with a comment dating it to 
the pg 8.5 era. Being the simple folk that we are, we changed the setting to 
the higher value. Unfortunately, pg 12 raised some errors of its own.

This seems like a bug in SymmetricDS, which ought to set the maximum length to 
that supported by the target database.

On the other hand, one could argue that a higher limit in postgres is sensible 
these days. 

Additional Information  
1. symmetricds version 3.12.4
2. Postgresql Version 12
3. OS's Windows 10, Mac Catalina and SME Server

Regards,

Mark Phillips
Mophilly Technology Inc.
Telephone: (619) 296-0114
On the web at http://www.mophilly.com

pgdump version mismatch error. server version: 13.0; pg_dump version: 12.4

[mark armon]

OS: Windows
issue:
https://stackoverflow.com/questions/14168920/how-to-fix-pg-dump-version-mismatch-errors

pg_dump: error: server version: 13.0; pg_dump version: 12.4
pg_dump: error: aborting because of server version mismatch

-
-
-
-

Re: pgdump version mismatch error. server version: 13.0; pg_dump version: 12.4

[mark armon]

where to get pg_dump 13.0?

On Sat, Nov 28, 2020 at 9:47 PM Adrian Klaver 
wrote:

> On 11/28/20 8:12 AM, mark armon wrote:
> >
> > OS: Windows
> > issue:
> >
> https://stackoverflow.com/questions/14168920/how-to-fix-pg-dump-version-mismatch-errors
> > <
> https://stackoverflow.com/questions/14168920/how-to-fix-pg-dump-version-mismatch-errors
> >
> >
> > |pg_dump: error: server version: 13.0; pg_dump version: 12.4 pg_dump:
> > error: aborting because of server version mismatch |
> >
>
> You are trying to dump a Postgres 13.0 version instance using the 12.4
> version of pg_dump. That will not work as the error says. You need to
> find and use the 13.0 version of pg_dump  and use that. FYI, it will
> work the other way e.g. a newer version of pg_dump can dump an older
> version of Postgres.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

ts_parse reports different between MacOS, FreeBSD/Linux

[Mark Felder]

Hello,

We have an application whose test suite fails on MacOS when running the search 
tests on unicode characters.

I've narrowed it down to the following:

macos=# select * from ts_parse('default','天');
 tokid | token
---+---
12 | 天
(1 row)

freebsd=# select * from ts_parse('default','天');
 tokid | token
---+---
 2 | 天
(1 row)


This has been bugging me for a while, but it's a test our devs using MacOS just 
ignores for now as we know it passes our CI/CD pipeline on FreeBSD/Linux. It 
seems if anyone is shipping an app on MacOS and bundling Postgres they're going 
to have a bad time with searching.


Please let me know if there's anything I can do to help. Will gladly test 
patches.



Thanks,



-- 
  Mark Felder
  ports-secteam & portmgr alumni
  f...@freebsd.org

Re: Dynamic procedure execution

[Mark Johnson]

Don't you have to select into a variable and then return the variable to
the client per [1]?

Consider the following example from my Oracle system:

beginning code ...
  V_SQL := 'SELECT COUNT(*) FROM ' || V_TAB;
  EXECUTE IMMEDIATE V_SQL INTO V_CNT;
ending code ...

[1]
https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
.


On Tue, Dec 29, 2020 at 11:40 AM Adrian Klaver 
wrote:

> On 12/28/20 10:34 PM, Muthukumar.GK wrote:
>
> Pleas do not top post, the style on this list is bottom/inline posting.
> > Hi Adrian Klaver,
> >
> > Sorry for typo mistake. Instead of writing lengthy query, I had written
> > it simple. Actually my main concept is to bring result set with multiple
> > rows (using select query) with help of dynamic query.
> >
> > When calling that procedure in Pgadmin4 window, simply getting the
> > message as ‘ CALL Query returned successfully in 158 msec’.
> >
> > FYI, I have implemented simple dynamic query for UPDATE and DELETE rows.
> > It is working fine without any issues.
> >
> > Please let me know is there any way of getting result set using dynamic
> > query.
> >
> > _Issue with dynamic select:-_
> >
> > __
> >
> > CREATE OR REPLACE Procedure sp_select_dynamic_sql(
> >
> >  keyvalue integer)
> >
> >  LANGUAGE 'plpgsql'
> >
> > AS $BODY$
> >
> > Declare v_query text;
> >
> > BEGIN
> >
> >  v_query:= 'select * from Los_BankInfo '
> >
> >  || ' where pk_id = '
> >
> >  || quote_literal(keyvalue);
> >
> >  execute v_query;
> >
> >  END;
> >
> > $BODY$;
> >
> > _Execuion__ of Proc:-_
> >
> > CALL sp_select_dynamic_sql (11);
> >
> > _Output:-_
> >
> > CALL
> >
> > Query returned successfully in 158 msec.
>
> See here:
>
>
> https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE
>
> "
> 42.6.2. Returning from a Procedure
>
> A procedure does not have a return value. A procedure can therefore end
> without a RETURN statement. If you wish to use a RETURN statement to
> exit the code early, write just RETURN with no expression.
>
> If the procedure has output parameters, the final values of the output
> parameter variables will be returned to the caller.
> "
>
> So use a function and follow the docs here:
>
>
> https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>
> in particular:
>
> "42.6.1.2. RETURN NEXT and RETURN QUERY"
>
> >
> > _Working fine with Dynamic UPDATE and DELETE Statement :-_
> >
> > __
> >
> > _UPDATE:-_
> >
> > __
> >
> > CREATE OR REPLACE Procedure sp_Update_dynamic_sql(
> >
> >  newvalue varchar(10),
> >
> >  keyvalue integer)
> >
> >  LANGUAGE 'plpgsql'
> >
> > AS $BODY$
> >
> > Declare v_query text;
> >
> > BEGIN
> >
> >  v_query:= 'update Los_BankInfo set approverid'
> >
> >  || ' = '
> >
> >  || quote_literal(newvalue)
> >
> >  || ' where pk_id = '
> >
> >  || quote_literal(keyvalue);
> >
> >  execute v_query;
> >
> >  END;
> >
> > $BODY$;
> >
> > --CALL sp_Update_dynamic_sql (john,10);
> >
> > _DELETE:-_
> >
> > __
> >
> > CREATE OR REPLACE Procedure sp_Delete_dynamic_sql(
> >
> >  keyvalue integer)
> >
> >  LANGUAGE 'plpgsql'
> >
> > AS $BODY$
> >
> > Declare v_query text;
> >
> > BEGIN
> >
> >  v_query:= 'delete from Los_BankInfo '
> >
> >  || ' where pk_id = '
> >
> >  || quote_literal(keyvalue);
> >
> >  execute v_query;
> >
> >  END;
> >
> > $BODY$;
> >
> > --CALL sp_Delete_dynamic_sql(10);
> >
> >
> >
> > Regards
> >
> > Muthu
> >
> >
> > On Mon, Dec 14, 2020, 8:54 PM Adrian Klaver  > > wrote:
> >
> > On 12/13/20 9:59 PM, Muthukumar.GK wrote:
> >  > Hi team,
> >  >
> >  > When I am trying to implement belwo dynamic concept in postgreSql,
> >  > getting some error. Kindly find the below attached program and
> > error.
> >  > Please advise me what is wrong here..
> >  >
> >  > CREATE OR REPLACE PROCEDURE DynamicProc()
> >  >
> >  > AS $$
> >  >
> >  > DECLARE v_query TEXT;
> >  >
> >  > C1 refcursor := 'result1';
> >  >
> >  > begin
> >  >
> >  > v_query := '';
> >  >
> >  > v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';
> >  >
> >  > EXECUTE (v_query);
> >  >
> >  > END;
> >  >
> >  > $$
> >  >
> >  > Language plpgsql;
> >  >
> >  >   Calling procedure :-
> >  >
> >  > 
> >  >
> >  > CALL DynamicProc();
> >  >
> >  > FETCH ALL IN "result1";
> >  >
> >  >
> >  > Error :-
> >  >
> >  > --
> >  >
> >  > ERROR: syntax error at or near "OPEN"
> >  >
> >  > LINE 1: OPEN C1 FOR S

FTS and tri-grams

[Mark Phillips]

Some years ago we implemented FTS. At the time, we also added a column with the 
tsvector data type. We assumed we needed that for FTS to work.

Later, we added tri-gram support. At the time, we assumed tri-gram complimented 
FTS.

More recently, we have run into a problem with replicating the tsvector column. 

We now wonder if tri-gram alone can achieve a fast full text search result. If 
so, then we could the tsvector column be dropped?

1. Is FTS required for tri-gram to work?
2. Are these independent of each other?
3. Is tri-gram alone sufficient for a “full text search” feature?

Any thoughts, experiences or other thoughts greatly appreciated.

 - Mark Phillips

Re: Postgres Analog of Oracle APPEND hint

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

Build Postgres On AIX

[Mark Hill]

Hi All,

First time poster so I hope this is the appropriate group in which to ask this 
question.   Please redirect me as needed.

I'm building Postgres 14.2 on AIX.   Apparently the arc4random function is not 
available in the AIX system libraries.  The build
fails when it tries to build postgresql-14.2/contrib/uuid-ossp/uuid-ossp.c 
because of the use of arc4random in that source
file.

I can build the uuid source from www.ossp.org<http://www.ossp.org> and then 
link against those libraries and the build succeeds but is there any other
way to get uuid-ossp.c to compile and link without using a 3rd party lib?

Thanks, Mark

RE: Build Postgres On AIX

[Mark Hill]

Postgres will not build on AIX with either of the following options to the 
configure script:
--with-uuid=e2fs
--with-uuid=ossp

so I was using --with-uuid=bsd which does work except for the arc4random 
undefined symbol error I was getting.

I removed the --with-uuid=bsd option to the configure script and everything 
builds including the contrib subdir except
the uuid-ossp subdir like we want.

Heres the configure command:
   ./configure \
  --prefix="$BUILD_DIR/pgsql-$POSTGRES_VERSION" \
  --without-readline \
  --without-zlib \
  --with-openssl \
  --with-includes="$BUILD_DIR"/openssl/include/openssl/ \
  --with-libraries="$BUILD_DIR"/openssl/lib/ \
  "$ADDITIONAL_FLAGS"

However, when I try to create a database I'm getting an out of memory error 
(see below.)   Any suggestions?

Thanks, Mark

---

Command:   ${postgresDir}/bin/initdb -D ./data -U dbmsowner -W



TopMemoryContext: 75328 total in 5 blocks; 33264 free (32 chunks); 42064 used
  TopTransactionContext: 524288 total in 7 blocks; 106872 free (26 chunks); 
417416 used
  TableSpace cache: 8192 total in 1 blocks; 5064 free (0 chunks); 3128 used
<.>
  Relcache by OID: 8192 total in 1 blocks; 1760 free (0 chunks); 6432 used
  CacheMemoryContext: 2097152 total in 9 blocks; 808960 free (10 chunks); 
1288192 used
index info: 2048 total in 2 blocks; 808 free (0 chunks); 1240 used: 
pg_description_o_c_o_index
relation rules: 16384 total in 5 blocks; 4840 free (0 chunks); 11544 used: 
pg_replication_slots
relation rules: 16384 total in 5 blocks; 4544 free (1 chunks); 11840 used: 
pg_statio_all_sequences
relation rules: 49152 total in 9 blocks; 7160 free (3 chunks); 41992 used: 
pg_statio_all_indexes
<...etc...>
Grand total: 3779872 bytes in 241 blocks; 1220984 free (137 chunks); 255 
used
2022-06-13 23:20:12.911 EDT [15270042] FATAL:  out of memory
2022-06-13 23:20:12.911 EDT [15270042] DETAIL:  Failed on request of size 8192 
in memory context "ExprContext".
2022-06-13 23:20:12.911 EDT [15270042] STATEMENT:  SELECT 
pg_import_system_collations('pg_catalog');


-Original Message-
From: Tom Lane  
Sent: Tuesday, May 31, 2022 11:20 AM
To: Mark Hill 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Build Postgres On AIX

[You don't often get email from t...@sss.pgh.pa.us. Learn why this is important 
at https://aka.ms/LearnAboutSenderIdentification ]

EXTERNAL

Mark Hill  writes:
> I'm building Postgres 14.2 on AIX.   Apparently the arc4random function is 
> not available in the AIX system libraries.  The build
> fails when it tries to build 
> postgresql-14.2/contrib/uuid-ossp/uuid-ossp.c because of the use of 
> arc4random in that source file.

AFAICS arc4random is only referenced in the "BSD" code paths, so you must have 
tried to specify --with-uuid=bsd.  It's not too surprising that that didn't 
work.  As per the docs, your other alternatives are to use the OSSP library or 
the e2fsprogs library.  Or you could just not build uuid-ossp; it's fairly 
vestigial these days, now that we offer gen_random_uuid() in core.

regards, tom lane

How to Build NLS Files

[Mark Hill]

I'm building Postgres 14.5 on Windows and want to build the nls files that go 
in share/locale.

According to this 
page<https://www.postgresql.org/docs/current/install-windows-full.html> I need 
the gettext software:

Gettext is required to build with NLS support, and can be downloaded from 
http://gnuwin32.sourceforge.net<http://gnuwin32.sourceforge.net/>. Note that 
binaries, dependencies and developer files are all needed.

I've downloaded the Binaries, Dependencies, Developer files, and Sources zip 
files from this page:   http://gnuwin32.sourceforge.net/packages/gettext.htm
and unzipped in C:\gettext.   Is that sufficient or do I need to build the 
source and/or anything else?

I'm building Postgres via the msvc tools in postgresql-14.5/src/tools/msvc with 
the build.bat script.  The build succeeds until I try to build
the nls files by adding the following to the config.pl script:
$main::config->{'nls'}="C:/gettext";
my $nls =   $main::config->{'nls'};

Anybody have experience with this?

Thanks, Mark

Logical replication/publication question

[Mark Fletcher]

Hi,

We're migrating from 9.6 to 14, using pglogical. We have several logical
slots on the 9.6 instance implementing a change data capture pattern. For
the migration, we plan on recreating the slots on the 14 instance, without
taking a snapshot of the data. When the migration happens, we will simply
start using the slots on the 14 instance (with the understanding that the
LSNs won't match between the 9.6 and 14 instances).

In testing, we have this working, but there was a wrinkle and I'd like to
know if my understanding is correct. On the 9.6 instance, when creating the
replication slots, we would use the START_REPLICATION SLOT command, and
then immediately take a snapshot, and it has worked great for years.

On a 14 instance, receiving changes from the 9.6 instance via pglogical, if
we recreate the logical slot, but not take a snapshot, no changes are
propagated, and there are no errors. However, if we first issue a 'CREATE
PUBLICATION' command, then changes are propagated to the slot as expected.

We never issued a 'CREATE PUBLICATION' command on the 9.6 instance. My
guess is that the act of taking a snapshot twittled whatever bits were
necessary to propagate changes through the slot. By not taking a snapshot
on the 14 instance, that doesn't happen, hence the need for the 'CREATE
PUBLICATION' command. Alternatively, something was changed between 9.6 and
14 that now requires the new command (I looked through the docs and could
find no mention, however).

Is my understanding correct? Am I missing something? I just want to make
sure I'm not screwing something up.

Thanks,
Mark

Column value derived from generated column in INSERT?

[Mark Raynsford]

Hello!

I'd like to be able to do something sort of analogous to this:

  create table t (
x integer not null generated always as identity, 
y integer not null
  );

  insert into t (y) values (t.x * 2);

In the real project, the "t.x * 2" expression is obviously something
a lot more complex, but I think it illustrates the point: I'd like to
be able to refer to the generated value of a column within the INSERT
statement that's going to cause it to be generated. Is there a way to
do this with a single statement right now?

I can think of various ways to do it with multiple statements, but a
single statement would be preferable.

-- 
Mark Raynsford | https://www.io7m.com

Re: Column value derived from generated column in INSERT?

[Mark Raynsford]

On 2022-10-19T11:58:07 -0700
"David G. Johnston"  wrote:

> On Wed, Oct 19, 2022 at 10:36 AM Mark Raynsford 
> wrote:
> 
> >   insert into t (y) values (t.x * 2);
> >
> > I can think of various ways to do it with multiple statements, but a
> > single statement would be preferable.
> >
> >  
> No, by extension of the documented constraint: "The generation expression
> can refer to other columns in the table, but not other generated columns."
> 

Hello!

Just want to confirm that I wasn't misunderstood. The documentation in
CREATE TABLE has the sentence you quoted above, and unless I'm
misunderstanding that's saying that the expression used to generate
values in GENERATED (ALWAYS AS) columns can't refer to other GENERATED
columns. That's fine, but that's not what I was asking. In the table
above, `x` is generated without references to other columns, but for
the non-GENERATED `y` value, I want to refer to the value that `x` will
have when I calculate a value for the `y` column in the INSERT
statement.

If that's not doable, that's fine, I just want to be sure. :)

-- 
Mark Raynsford | https://www.io7m.com

Re: Column value derived from generated column in INSERT?

[Mark Raynsford]

On 2022-10-19T12:43:31 -0700
Adrian Klaver  wrote:
>
> HINT:  There is an entry for table "t", but it cannot be referenced from 
> this part of the query.
>
> HINT:  There is a column named "x" in table "t", but it cannot be 
> referenced from this part of the query.

Yes, I saw those, hence asking on the list if there was a way to do it.

I'll handle it with multiple statements.

-- 
Mark Raynsford | https://www.io7m.com

EXPLAIN ANALYZE does not return accurate execution times

[Mark Mizzi]

As an example, let's take the following simple table:

CREATE TABLE unary(a VARCHAR);
-- simple way to make table large
ALTER TABLE unary
ALTER COLUMN a SET STORAGE EXTERNAL;

-- insert one million large rows
INSERT INTO unary
SELECT repeat('a', 8000)
FROM generate_series(0, 10);

-- update planner statistics on the unary table.
ANALYZE unary;

When I run

EXPLAIN ANALYZE SELECT * FROM unary;

I get the following result:

 Seq Scan on unary  (cost=0.00..1637.01 rows=11 width=18) (actual
time=0.009..6.667 rows=11 loops=1)
 Planning Time: 0.105 ms
 Execution Time: 8.565 ms

On the other hand, the following command

time sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null

returns after 17s with:

sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null  0.01s user
0.01s system 0% cpu 16.912 total

I am running Postgres 14 (installed via apt) on Ubuntu 22.04. All settings
are default.
The machine is a Dell Vostro 7500.

All commands are being run locally, so I don't think this is a network
bandwidth issue. What's going on?

Re: EXPLAIN ANALYZE does not return accurate execution times

[Mark Mizzi]

Hi, thanks for your reply.
So to confirm, EXPLAIN ANALYZE does not detoast rows? The original goal of
these queries was to see the effect of fetching from toast tables on query
performance.

On Thu, 27 Oct 2022 at 15:43, Tom Lane  wrote:

> Mark Mizzi  writes:
> > When I run
>
> > EXPLAIN ANALYZE SELECT * FROM unary;
>
> > I get the following result:
>
> >  Seq Scan on unary  (cost=0.00..1637.01 rows=11 width=18) (actual
> > time=0.009..6.667 rows=11 loops=1)
> >  Planning Time: 0.105 ms
> >  Execution Time: 8.565 ms
>
> > On the other hand, the following command
>
> > time sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null
>
> > returns after 17s with:
> > sudo -u postgres psql -c "SELECT * FROM unary" -o /dev/null  0.01s user
> > 0.01s system 0% cpu 16.912 total
>
> The main thing actual execution does that EXPLAIN does not is
> format the data and send it off to the client.  There are a
> number of possible bottlenecks involved there -- TOAST fetching,
> data formatting, network traffic, or client processing.  Watching
> this example in "top", I see psql consuming near 100% CPU, meaning
> that the problem is with psql's code to make a nicely-aligned
> ASCII table out of the result.  This isn't too surprising: that
> code was never meant to operate on resultsets that are too large
> for human consumption.  You could use a different formatting rule,
> or switch to COPY.
>
> As an example, using
>
> psql -c '\pset format unaligned' -c "SELECT * FROM unary" -o /dev/null
>
> this example drops from ~16s to ~1.7s on my machine.
>
> regards, tom lane
>

uuid-ossp source or binaries for Windows

[Mark Hill]

I've downloaded the PostgreSQL 14.7 source and building it on Windows 64bit and 
32bit.

I'm using the Visual Studio tools in the src/tools/msvc folder.

I'm trying to build with the uuid extension but it looks like I need uuid-ossp 
installed in order
to get it to work.

The source download referenced in the Postgresql doc here, 
https://www.postgresql.org/docs/current/uuid-ossp.html#id-1.11.7.58.6
this source download,  ftp://ftp.ossp.org/pkg/lib/uuid/uuid-1.6.2.tar.gz, is 
Unix-specific as far as I can tell.

Where can I find uuid-ossp for Windows, 32 and 64 bit, either the source so I 
can build it or
prebuilt libraries?

Thanks, Mark

RE: uuid-ossp source or binaries for Windows

[Mark Hill]

Hey Daniel,

Thanks for getting back to me.

I think the issue I'm having is that my build of Postgres is missing uuid 
pieces needed by our users.

They're executing the command:   CREATE EXTENSION "uuid-ossp"

and getting the error

ERROR:  could not open extension control file 
"/share/extension/uuid-ossp.control"

The only file matching "*uuid*" in my build of Postgres is:   
/include/server/utils/uuid.h

I should have in addition: 
/include/uuid.h
/lib/uuid-ossp.dll
/share/extension/uuid-ossp--1.1.sql
/share/extension/uuid-ossp.control
/share/extension/uuid-ossp--unpackaged--1.0.sql
/share/extension/uuid-ossp--1.0--1.1.sql

I need a Windows-specific install of uuid-ossp for the Postgres build to use, 
for both 32bit and 64bit Windows.

Thanks, Mark

-Original Message-
From: Daniel Gustafsson  
Sent: Wednesday, March 15, 2023 3:16 PM
To: Mark Hill 
Cc: pgsql-general@lists.postgresql.org; Ken Peressini ; 
Michael King 
Subject: Re: uuid-ossp source or binaries for Windows

EXTERNAL

> On 15 Mar 2023, at 19:31, Mark Hill  wrote:
>
> I've downloaded the PostgreSQL 14.7 source and building it on Windows 64bit 
> and 32bit.
>
> I'm using the Visual Studio tools in the src/tools/msvc folder.
>
> I'm trying to build with the uuid extension but it looks like I need 
> uuid-ossp installed in order to get it to work.

Do you need the extension specifically or does the built-in generator function 
do what you need?

> The source download referenced in the Postgresql doc here, 
> https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.
> postgresql.org%2Fdocs%2Fcurrent%2Fuuid-ossp.html%23id-1.11.7.58.6&data
> =05%7C01%7CMark.Hill%40sas.com%7C5acf51786dd5440ea0ed08db2589a9fd%7Cb1
> c14d5c362545b3a4309552373a0c2f%7C0%7C0%7C638145045990073139%7CUnknown%
> 7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJX
> VCI6Mn0%3D%7C3000%7C%7C%7C&sdata=TSRqdrvImMLf6Pr8XWqRSUkCWUDaAjFtziykz
> Czt5Sc%3D&reserved=0 this source download,  
> https://nam02.safelinks.protection.outlook.com/?url=ftp%3A%2F%2Fftp.ossp.org%2Fpkg%2Flib%2Fuuid%2Fuuid-1.6.2.tar.gz&data=05%7C01%7CMark.Hill%40sas.com%7C5acf51786dd5440ea0ed08db2589a9fd%7Cb1c14d5c362545b3a4309552373a0c2f%7C0%7C0%7C638145045990073139%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=ry3iJshaFPSegaIrmaJzA0%2BIKgEfXbJwmasBA8ZdWQ8%3D&reserved=0,
>  is Unix-specific as far as I can tell.
>
> Where can I find uuid-ossp for Windows, 32 and 64 bit, either the 
> source so I can build it or prebuilt libraries?

I don't know windows at all, but uuid-ossp.dll is provided in the EDB packages 
(looking at the binary zip bundle) so it's clearly available to be built.
Maybe someone from EDB can chime in with pointers for building on Windows so we 
can update the docs accordingly?

--
Daniel Gustafsson

Re: TEXT column > 1Gb

[Mark Dilger]

> On Apr 12, 2023, at 7:59 AM, Joe Carlson  wrote:
> 
> The use case is genomics. Extracting substrings is common. So going to 
> chunked storage makes sense.

Are you storing nucleotide sequences as text strings?  If using the simple 
4-character (A,C,G,T) alphabet, you can store four bases per byte.  If using a 
nucleotide code 16-character alphabet you can still get two bases per byte.  An 
amino acid 20-character alphabet can be stored 8 bases per 5 bytes, and so 
forth.  Such a representation might allow you to store sequences two or four 
times longer than the limit you currently hit, but then you are still at an 
impasse.  Would a factor or 2x or 4x be enough for your needs? 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Oracle vs PG

[Mark Fletcher]

A Twitter thread from someone who talked with the reporter (also read
Werner's statement referenced in the first tweet):

https://twitter.com/andy_pavlo/status/1055154039606910976

Mark

db-connections (application architecture)

[Mark Moellering]

So, I am working on some system designs for a web application, and I wonder
if there is any definitive answer on how to best connect to a postgres
database.

I could have it so that each time a query, or set of queries, for a
particular request, needs to be run, a new connection is opened, queries
are run, and then connection is closed / dropped.

OR, I could create a persistent connection that will remain open as long as
a user is logged in and then any queries are run against the open
connection.

I can see how, for only a few (hundreds to thousands) of users, the latter
might make more sense but if I need to scale up to millions, I might not
want all of those connections open.

Any idea of how much time / overhead is added by opening and closing a
connection everytime?

Any and all information is welcome.

Thanks in advance

-- Mark M

Re: db-connections (application architecture)

[Mark Moellering]

Oh, excellent.  I knew I was about to reinvent the wheel.
Sometimes, there are just too many new things to keep up on.

Thank you so much!

On Thu, Nov 15, 2018 at 10:16 AM Adrian Klaver 
wrote:

> On 11/15/18 7:09 AM, Mark Moellering wrote:
> > So, I am working on some system designs for a web application, and I
> > wonder if there is any definitive answer on how to best connect to a
> > postgres database.
> >
> > I could have it so that each time a query, or set of queries, for a
> > particular request, needs to be run, a new connection is opened, queries
> > are run, and then connection is closed / dropped.
> >
> > OR, I could create a persistent connection that will remain open as long
> > as a user is logged in and then any queries are run against the open
> > connection.
> >
> > I can see how, for only a few (hundreds to thousands) of users, the
> > latter might make more sense but if I need to scale up to millions, I
> > might not want all of those connections open.
> >
> > Any idea of how much time / overhead is added by opening and closing a
> > connection everytime?
> >
> > Any and all information is welcome.
>
> Connection pooling?
>
> In no particular order:
>
> https://pgbouncer.github.io/
>
> http://www.pgpool.net/mediawiki/index.php/Main_Page
>
> >
> > Thanks in advance
> >
> > -- Mark M
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

ERROR: found multixact XX from before relminmxid YY

[Mark Fletcher]

Hi,

Starting yesterday morning, auto vacuuming of one of our postgresql 9.6.10
(CentOS 7) table's started failing:

ERROR:  found multixact 370350365 from before relminmxid 765860874
CONTEXT:  automatic vacuum of table "userdb.public.subs"

This is about as plain and simple a table as there is. No triggers or
foreign keys, I'm not using any extensions. It has about 2.8M rows. I have
not done any consistency checks, but nothing strange has manifested in
production.

Reading the various discussions about this error, the only solution I found
was here:

https://www.postgresql.org/message-id/CAGewt-ukbL6WL8cc-G%2BiN9AVvmMQkhA9i2TKP4-6wJr6YOQkzA%40mail.gmail.com

But no other reports of this solving the problem. Can someone verify that
if I do the mentioned fix (and I assume upgrade to 9.6.11) that will fix
the problem? And that it doesn't indicate table corruption?

Thanks,
Mark

Re: ERROR: found multixact XX from before relminmxid YY

[Mark Fletcher]

On Fri, Dec 28, 2018 at 4:49 PM Tom Lane  wrote:

>
> Yeah, SELECT FOR UPDATE should overwrite the broken xmax value and thereby
> fix it, I expect.  However, I don't see anything in the release notes
> suggesting that we've fixed any related bugs since 9.6.10, so if this
> just appeared then we've still got a problem :-(.  Did anything
> interesting happen since your last successful autovacuum on that table?
> Database crashes, WAL-related parameter changes, that sort of thing?
>
> The last autovacuum of that table was on Dec 8th, the last auto analyze
was Dec 26. There have been no schema changes on that particular table,
database crashes or WAL-related parameter changes since then. We've done
other schema changes during that time, but otherwise the database has been
stable.

Thanks,
Mark

Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

Hi,

This command, which generates a JSON object as output, has some escaped
data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)

C:\\Portable\\curl\\curl.exe -k "
https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'

I use the COPY command to pull it into a temp table like so:

COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "
https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";';

However copy eats those backslashes. I need to use quote_literal() but
that's a syntax error. For some reason the COPY command doesn't allow for
ESCAPE to work with programs, only CSV.

I tried using WITH BINARY but I get the error message: "COPY file signature
not recognized"

Does anyone know how to make COPY FROM PROGRAM take the output literally?

Thanks,
  Mark
ᐧ

Re: Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

Thanks Rob,

Since I'm on Windows and Windows batch sucks I just ended up doing the JSON
parsing with node.js

To be honest this whole affair with COPY FROM program seems like a bug to
me though.

On Mon, Dec 31, 2018 at 1:59 PM Rob Sargent  wrote:

>
>
> On Dec 31, 2018, at 10:36 AM, Mark Mikulec  wrote:
>
> Hi,
>
> This command, which generates a JSON object as output, has some escaped
> data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
>
> C:\\Portable\\curl\\curl.exe -k "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "'
>
> I use the COPY command to pull it into a temp table like so:
>
> COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "';
>
> However copy eats those backslashes. I need to use quote_literal() but
> that's a syntax error. For some reason the COPY command doesn't allow for
> ESCAPE to work with programs, only CSV.
>
> I tried using WITH BINARY but I get the error message: "COPY file
> signature not recognized"
>
> Does anyone know how to make COPY FROM PROGRAM take the output literally?
>
> Thanks,
>   Mark
> ᐧ
>
> Can you pipe the curl output through sed s,\\,,g
>
> ᐧ

Re: Can't quote_literal with COPY FROM PROGRAM

[Mark Mikulec]

I changed it to be just the single float value I needed to extract out of
the JSON object, but originally it was a text column that held the entire
JSON object.
ᐧ

On Mon, Dec 31, 2018 at 3:52 PM Adrian Klaver 
wrote:

> On 12/31/18 9:36 AM, Mark Mikulec wrote:
> > Hi,
> >
> > This command, which generates a JSON object as output, has some escaped
> > data with backslashes: (see line 91 here: https://pastebin.com/D4it8ybS)
> >
> > C:\\Portable\\curl\\curl.exe -k
> > "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted";'
> >
> > I use the COPY command to pull it into a temp table like so:
> >
> > COPY temp_maps_api from program 'C:\\Portable\\curl\\curl.exe -k
> > "
> https://maps.googleapis.com/maps/api/directions/json?etcVariablesDeleted
> "';
>
> So temp_maps_api has a single JSON field?
>
> >
> > However copy eats those backslashes. I need to use quote_literal() but
> > that's a syntax error. For some reason the COPY command doesn't allow
> > for ESCAPE to work with programs, only CSV.
> >
> > I tried using WITH BINARY but I get the error message: "COPY file
> > signature not recognized"
> >
> > Does anyone know how to make COPY FROM PROGRAM take the output literally?
> >
> > Thanks,
> >Mark
> > ᐧ
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Showing table comments with psql

[Mark Jeffcoat]

I'm creating table and view comments with "COMMENT ON", and I can find
the comment in pg_description, but I can't find a way to show the
table comments using psql.

$ psql --version
psql (PostgreSQL) 11.1 (Debian 11.1-1+b2)

I'd expect to see table comments by using \d+, and found an old post
on this list where \dd worked; neither work for me today. Am I looking
in the right place? Is this a regression?

Thank you.


postgres=# create table test_table (col1 integer);
CREATE TABLE

postgres=# comment on table test_table is 'this is a table comment';
COMMENT

postgres=# \d+ test_table
Table "public.test_table"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats
target | Description
+-+---+--+-+-+--+-
 col1   | integer |   |  | | plain   |  |

postgres=# \dd test_table
 Object descriptions
 Schema | Name | Object | Description
+--++-
(0 rows)

postgres=# select * from pg_description where objoid = (select oid
from pg_class where relname = 'test_table');
 objoid | classoid | objsubid |   description
+--+--+-
 714760 | 1259 |0 | this is a table comment
(1 row)

-- 
Mark Jeffcoat
Austin, TX

Re: Showing table comments with psql

[Mark Jeffcoat]

On Fri, Jan 4, 2019 at 11:00 AM Pavel Stehule  wrote:

> postgres=#  create table test_table (col1 integer);
> CREATE TABLE
> postgres=# comment on table test_table is 'this is a table comment';
> COMMENT
> postgres=# \dt+
>  List of relations
> ┌┬┬───┬───┬─┬─┐
> │ Schema │Name│ Type  │ Owner │  Size   │   Description   │
> ╞╪╪═══╪═══╪═╪═╡
> │ public │ test_table │ table │ pavel │ 0 bytes │ this is a table comment │
> └┴┴───┴───┴─┴─┘
> (1 row)
>

I was so close! That works; thank you very much for your help, Pavel.

-- 
Mark Jeffcoat
Austin, TX

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 9:02 AM github kran  wrote:

>
>> Hi Postgres Team,
>>
>> I have an application using RDS Aurora Postgresql 9.6 version having 4 TB
>> of DB size. In this DB we have a table PRODUCT_INFO with around  1 million
>> rows and table size of 1 GB.
>> We are looking for a implementation where we want to pull the data in
>> real time for every 5 seconds from the DB ( Table mentioned above) and send
>> it to IOT topic whenever an event occurs for a product. ( event is any new
>> product information or change in the existing
>> product information.).
>>
>>
It's unclear whether you want to do table scans or if you're just looking
for changes to the database. If you're looking just for changes, consider
implementing something using logical replication. We have a logical
replication system set up to stream changes from the database into an
elastic search cluster, and it works great.

Mark

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 10:10 AM github kran  wrote:

> Mark - We are currently on 9.6 version of postgres and cant use this
> feature of logical replication.Answering to your question we are looking
> for any changes in the data related to a specific table ( changes like any
> update on a timestamp field
> OR any new inserts happened in the last 5 seconds for a specific product
> entity).
> Any other alternatives ?.
>
> The feature was added in 9.4 (I think). We are on 9.6 and it works great.
Not sure about RDS Aurora, however.

Mark

Re: Pulling data from Postgres DB table for every 5 seconds.

[Mark Fletcher]

On Wed, Jan 9, 2019 at 12:58 PM github kran  wrote:

>
> Mark - just curious to know on the logical replication. Do you think I can
> use it for my use case where i need to publish data to a subscriber when
> there is a change in the data updated for a row or any new inserts
> happening on the table. Intention
> is to send this data in Json format by collecting this modified data in
> real time to a subscriber.
>
> From what you've said, it's a great use case for that feature. The one
thing to note is that you will have to code up a logical replication
client. If I can do it, pretty much anyone can, but it might take some time
to get things right. I wrote about some of what I found when developing our
client a year ago here:
https://wingedpig.com/2017/09/20/streaming-postgres-changes/

We ended up just using the included test output plugin that comes with the
postgresql distribution. And we didn't end up streaming to Kafka or
anything else first. We just take the data and insert it into our
elasticsearch cluster directly as we get it.

Mark

Re: Barman disaster recovery solution

[Mark Fletcher]

On Wed, Feb 27, 2019 at 1:39 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

>
> Hello, as promised here is my blog :
>
> https://severalnines.com/blog/current-state-open-source-backup-management-postgresql
>
>
Nice blog post. If you're aiming for a comprehensive run down of tools, I
suggest including wal-g. We've been using it since it was released (and its
predecessor wal-e for years before that) and it's been great. We currently
use it to back up a replica to S3, and it has no issues doing that. In more
recent versions, it supports delta backups, which decrease the time/load
required for a backup immensely in our case.

Cheers,
Mark

Re: Where **not** to use PostgreSQL?

[Mark Moellering]

I wish more people would ask this question, to me, it is the true mark of
experience.  In general, I think of PostgreSQL as the leading Relational
Database.  The farther you get away from relational data and relational
queries, the more I would say, you should look for other products or
solutions.  But if you want to store relational data and then run queries
over it, then stick with PostgreSQL.

My 2 scents..

Mark

On Thu, Feb 28, 2019 at 8:28 AM Nicolas Grilly 
wrote:

> On Thu, Feb 28, 2019 at 2:12 PM Chris Travers 
> wrote:
>
>> Where I did this on the TB scale, we had some sort of ranking but it was
>> not based on ts_rank.
>>
>> On the PB scale systems I work on now, it is distributed, and we don't
>> order in PostgreSQL (or anywhere else, though if someone wants to write to
>> disk and sort, they can do this I guess)
>>
>
> Thanks!
>
>>

Non-pausing table scan on 9.6 replica?

[Mark Fletcher]

Hi All,

On a 9.6 streaming replica, we do table scans for stats and other things.
During these scans, the replication is paused (the 'recovering' postgres
process has 'waiting' appended to it). We're not using transactions with
these scans. Is there anything we can do to prevent the pausing?

Thanks,
Mark

Re: Non-pausing table scan on 9.6 replica?

[Mark Fletcher]

Thank you for responding to my email.

On Tue, Mar 5, 2019 at 9:20 PM Andreas Kretschmer 
wrote:

>
> have you set ```max_standby_streaming_delay``? The default is 30
> seconds, which means that this will be the maximum time allowed for a
> replication lag caused by a conflicting query.
>

Yes, we've bumped that up a lot.


> You can use ``hot_standby_feedback = on``, but the downside will be more
> bloat on the tables.
>
> I'm not sure I understand. I'm not worried about the query being cancelled
on the replica. max_standby_streaming_delay fixes that for us. I'm worried
about the streaming replication being paused while this table scan is
running. If the table scan takes several minutes, then the replica becomes
several minutes out of sync with the master. I'd prefer that not to happen
and I'm wondering if there's a way to do that.

Thanks,
Mark

Re: Non-pausing table scan on 9.6 replica?

[Mark Fletcher]

Andreas, Sameer,

Thank you for replying. I did not understand the purpose of
hot_standby_feedback, and your explanations helped. I turned it on, and the
pausing stopped.

Thanks,
Mark

Re: TPC-DS queries

[Mark Johnson]

I found this error in queries generated from templates query36.tpl,
query70.tpl, and query86.tpl.  The problem is, lochierarchy is an alias
defined in the SELECT statement, and the alias isn't being recognized in
the CASE statement.  PostgreSQL does not allow a column alias to be
referenced in a CASE statement, you have to use the actual column name.
Modify each of the queries throwing errors, and replace the lochierarchy
alias with the actual column name you see in the SELECT statement.
-Mark



On Mon, Mar 11, 2019 at 4:00 AM Tatsuo Ishii  wrote:

> > Hi,
> >
> > I think that the sql is not valid. Based on the order by
> > documentation, a column label cannot be used in an expression.
> >
> > fromhttps://www.postgresql.org/docs/11/queries-order.html
> >  > Note that an output column name has to stand alone, that is, it
> > cannot be used in an expression.
>
> Thanks. Yes, you are correct. The line should be something like:
>
>,case when grouping(i_category)+grouping(i_class) = 0 then i_category
> end
>
> > Regards
> > s.
> >
> > On 11.03.2019 06:30, Tatsuo Ishii wrote:
> >> I played with TPC-DS and found some of them can't be executed because
> >> of SQL errors and I am not sure why.
> >>
> >> For example with query 36:
> >>
> >> select
> >>  sum(ss_net_profit)/sum(ss_ext_sales_price) as gross_margin
> >> ,i_category
> >> ,i_class
> >> ,grouping(i_category)+grouping(i_class) as lochierarchy
> >> ,rank() over (
> >>  partition by grouping(i_category)+grouping(i_class),
> >>  case when grouping(i_class) = 0 then i_category end
> >>  order by sum(ss_net_profit)/sum(ss_ext_sales_price) asc) as
> >>  rank_within_parent
> >>   from
> >>  store_sales
> >> ,date_dim   d1
> >> ,item
> >> ,store
> >>   where
> >>  d1.d_year = 2000
> >>   and d1.d_date_sk = ss_sold_date_sk
> >>   and i_item_sk  = ss_item_sk
> >>   and s_store_sk  = ss_store_sk
> >>   and s_state in ('TN','TN','TN','TN',
> >>   'TN','TN','TN','TN')
> >>   group by rollup(i_category,i_class)
> >>   order by
> >> lochierarchy desc
> >>,case when lochierarchy = 0 then i_category end -- line 25 is here.
> >>,rank_within_parent
> >>limit 100;
> >> psql:query_0.sql:1935: ERROR:  column "lochierarchy" does not exist
> >> LINE 25:   ,case when lochierarchy = 0 then i_category end
> >>
> >> I have follwed the instruction here.
> >> https://ankane.org/tpc-ds
> >>
> >> PostgreSQL is master branch HEAD. For me, the SQL above looks to be
> >> valid.
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
> >
>
>

Migrating an application with Oracle temporary tables

[Mark Zellers]

One of the issues I have run into in migrating from Oracle to PostgreSQL is the 
difference in temporary table behavior.  I've come up with what I think is a 
cunning plan, but I wonder if anyone with more PostgreSQL understanding can 
shoot holes in it.

Oracle defines their temporary tables statically, such that the first use in a 
session instantiates a private instance of the table.  That implies that you 
need to jump through flaming hoops in order to be able to use temporary tables 
in stored procedures and functions.

My idea was to create a "prototype" table (which will never have any rows in 
it) and, when I need to use the temporary table, create the temporary table 
such that it hides the prototype.  I have a simple example that seems to work, 
although before I invest more effort in using it for real work, I'd like to get 
a second opinion as to whether this approach is going to fail in a more complex 
scenario.


drop function if exists bar;
drop table if exists foo;

create table FOO(X INTEGER not null constraint PTCC_FOO check (X is NULL));
commit;

create function bar() returns INTEGER
as
$$
DECLARE
   result INTEGER;
begin
select SUM(X) into result from FOO ;
return result;
end;
$$ language plpgsql;

commit;

create temporary table FOO(X INTEGER) on commit drop;
alter table FOO add constraint PK_FOO primary KEY(X);
insert into FOO(X) VALUES(1);
insert into FOO(X) VALUES(2);
insert into FOO(X) VALUES(3);

select 'Expect to see 6' as Expectation, BAR();
commit;


create temporary table FOO(X INTEGER) on commit drop;
alter table FOO add constraint PK_FOO primary key (X);
insert into FOO(X) VALUES(1);
insert into FOO(X) VALUES(2);
insert into FOO(X) VALUES(3);
insert into FOO(X) VALUES(4);
select 'Expect to see 10' as Expectation, BAR();
rollback;

create temporary table FOO(X INTEGER) on commit drop;
insert into FOO(X) VALUES(1);
insert into FOO(X) VALUES(2);
select 'Expect to see 3' as Expectation, BAR();
commit;


What I am not clear on is what the rules are as to when a function/procedure is 
effectively recompiled.  Is there a danger that. assuming the temporary table 
is created for a session that one session might see another session's data due 
to the procedure having effectively compiled the temporary table into its 
definition?


While this approach does have the disadvantage of requiring the application to 
define the temporary table before using it (which could be as simple as using 
`CREATE TABLE AS SELECT * FROM prototype_table`), it seems simpler and 
potentially more performant than the approach I found here: 
https://www.codeproject.com/Articles/1176045/Oracle-style-global-temporary-tables-for-PostgreSQ

It is also in direct opposition to this post I found: 
https://www.cybertec-postgresql.com/en/using-temporary-tables-the-way-they-should-not-be-used/

So far, I have not found a case where, as long as I don't read or write to the 
permanent table, I get the wrong results from the above approach.  It allows me 
to minimize the impact on my application (basically, it means that at the start 
of any transaction that might need a certain temporary table, I need to 
manually create it.  The number of places I would need to do that is relatively 
finite, so I'm willing to take that hit, in exchange for not having to use 
dynamic SQL to refer to my temporary tables.

RE: Migrating an application with Oracle temporary tables

[Mark Zellers]

Laurenz,

I thought I needed the prototype table to be able to define functions and 
procedures that refer to the temporary table but do not create it.

Perhaps my assumption that I need the table to exist (whether as a temporary 
table or as a permanent table) in order to define the function/procedure is 
incorrect.  I'll take a look at that.

I did find a scenario where this approach does run into trouble.  That is, if 
the function/procedure is executed against the permanent table and then you go 
to run it against a temporary table.  In that case, I do get the wrong answer, 
and I haven't yet figured out how to reset that without dropping the procedure 
and re-defining it.  For my purposes, that is "good enough" -- I can promise 
not to run such procedures against the temporary table.

Thanks for the reply,

Mark Z.




-Original Message-
From: Laurenz Albe  
Sent: Thursday, May 2, 2019 1:00 AM
To: Mark Zellers ; 
pgsql-general@lists.postgresql.org
Subject: Re: Migrating an application with Oracle temporary tables

Mark Zellers wrote:
> One of the issues I have run into in migrating from Oracle to PostgreSQL is 
> the difference in temporary table behavior.
> I've come up with what I think is a cunning plan, but I wonder if anyone with 
> more PostgreSQL understanding can shoot holes in it.
>  
> Oracle defines their temporary tables statically, such that the first use in 
> a session instantiates a private instance of the table.
> That implies that you need to jump through flaming hoops in order to be able 
> to use temporary tables in stored procedures and functions.
>  
> My idea was to create a "prototype" table (which will never have any 
> rows in it) and, when I need to use the temporary table, create the 
> temporary table such that it hides the prototype.  I have a simple 
> example that seems to work, although before I invest more effort in using it 
> for real work, I'd like to get a second opinion as to whether this approach 
> is going to fail in a more complex scenario.
[...] 
> What I am not clear on is what the rules are as to when a function/procedure 
> is effectively recompiled.
> Is there a danger that. assuming the temporary table is created for a 
> session that one session might see another session's data due to the 
> procedure having effectively compiled the temporary table into its definition?

There is no danger of that, because a function is parsed whenever it is 
executed, and certainly different database sessions never share any "compiled 
version" of the function or query execution plans.

What does get cached are the execution plans of SQL statements in a PL/pgSQL 
function, but only across different calls in the same database session.

This does not seem to cause problems in your case (the plans are probably 
invalidated; I am not certain), but you can avoid that behavior by using 
dynamic SQL (EXECUTE 'SELECT ...').

> While this approach does have the disadvantage of requiring the 
> application to define the temporary table before using it (which could 
> be as simple as using `CREATE TABLE AS SELECT * FROM 
> prototype_table`), it seems simpler and potentially more performant 
> than the approach I found here: 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.codeproject.c
> om_Articles_1176045_Oracle-2Dstyle-2Dglobal-2Dtemporary-2Dtables-2Dfor
> -2DPostgreSQ&d=DwIDaQ&c=DS6PUFBBr_KiLo7Sjt3ljp5jaW5k2i9ijVXllEdOozc&r=
> WK2G_319M5jcG-UdecAitIGR-UzTlASAsP7ybqXtD2Y&m=gYKOaSte_-ZRnU2RgctTQfAn
> J9BhFhxowJBy7upH7KE&s=I_x0KCEfHgBXOoSRYXpAehb5uRodwY11-m6JRYf9A_Y&e=
>
> It is also in direct opposition to this post I found: 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.cybertec-2Dpo
> stgresql.com_en_using-2Dtemporary-2Dtables-2Dthe-2Dway-2Dthey-2Dshould
> -2Dnot-2Dbe-2Dused_&d=DwIDaQ&c=DS6PUFBBr_KiLo7Sjt3ljp5jaW5k2i9ijVXllEd
> Oozc&r=WK2G_319M5jcG-UdecAitIGR-UzTlASAsP7ybqXtD2Y&m=gYKOaSte_-ZRnU2Rg
> ctTQfAnJ9BhFhxowJBy7upH7KE&s=OJVABOenTvrP2J1ubWXbaMayh1AVLOp2ieQ4HeacV
> 0I&e=

In this case, the "trouble and pain" behavior is what you explicitly want, so 
don't worry.

> So far, I have not found a case where, as long as I don’t read or 
> write to the permanent table, I get the wrong results from the above 
> approach.  It allows me to minimize the impact on my application 
> (basically, it means that at the start of any transaction that might need a 
> certain temporary table, I need to manually create it.  The number of places 
> I would need to do that is relatively finite, so I’m willing to take that 
> hit, in exchange for not having to use dynamic SQL to refer to my temporary 
> tables.

The biggest problem I see with your approach is that dropping temporary tables 
causes 

Re: schema change tracking

[Mark Fletcher]

On Thu, May 16, 2019 at 9:41 AM Benedict Holland <
benedict.m.holl...@gmail.com> wrote:

>
> I need a tool that can track schema changes in a postgesql database, write
> scripts to alter the tables, and store those changes in git. Are there
> tools that exist that can do this?
>
> We ended up rolling our own. We do schema dumps and then use
https://www.apgdiff.com/ to diff them. For our relatively simple schemas,
it's worked fine. One thing to note, apgdiff doesn't support `ALTER TABLE
ONLY [a-z\.]+ REPLICA IDENTITY FULL;` lines, which we just remove before
diffing.

Cheers,
Mark

Re: Installation Issue

[Mark Atlantic]

From: Adrian Klaver 
Sent: Sunday, July 16, 2023 11:35 AM
To: Baskar Muthu ; pgsql-general@lists.postgresql.org 

Subject: Re: Installation Issue

On 7/15/23 07:30, Baskar Muthu wrote:
> Hi sir/mam
>
> I installed postre - sql but it is not connected with the server. I
> tried many times but it's not working and it shows a 'connecting server
> error'.

This needs more information:

1) OS and version.

2) Postgresql version.

3) How was Postgresql installed?

5) Is the Postgresql server running?

6) What client are you connecting with and the connection parameters?

7) Is the client remote to the server?

8) The complete error message.


>
> Please resolve this issue and alternate ideas please let me know.
>
> Thanks & Regards
> Baskar Muthu

--
Adrian Klaver
adrian.kla...@aklaver.com

pg*.dll in psqlODBC have no version numbers

[Mark Hill]

We download the ODBC source from http://ftp.postgresql.org and build it 
on-site, 13.02. in this case.

A colleague noticed that the following files in the psqlODBC MSI for Windows 
have no version numbers:
pgenlist.dll
pgenlista.dll
pgxalib.dll

Does anyone know if that is be design or just an oversight?

Btw, the same holds for ODBC 12.02..

Thanks, Mark

walreceiver fails on asynchronous replica [SEC=UNOFFICIAL]

[Mark Schloss]

UNOFFICIAL

Hello,

We have the following setup in our DEV environment -

- Primary/Replica using asynchronous streaming replication
- Servers run Ubuntu
  Linux 5.15.0-88-generic #98-Ubuntu SMP Mon Oct 2 15:18:56 UTC 
2023 x86_64 x86_64 GNU/Linux
- Postgres version
  postgres (PostgreSQL) 13.13 (Ubuntu 13.13-1.pgdg22.04+1)

The replica has been running for many weeks and then the walreceiver failed 
with the following -

<2024-02-22 14:12:34.731 AEDT [565228]: [60136-1] user=,db= > DETAIL:  Last 
completed transaction was at log time 2024-02-22 14:12:00.327411+11.
<2024-02-22 14:17:34.822 AEDT [565228]: [60137-1] user=,db= > LOG:  
restartpoint starting: time
<2024-02-22 14:17:34.935 AEDT [565228]: [60138-1] user=,db= > LOG:  
restartpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 
0 recycled; write=0.106 s, sync=0.002 s, total=0.114 s; sync files=1, 
longest=0.002 s, average=0.002 s; distance=4 kB,
estimate=14 kB
<2024-02-22 14:17:34.935 AEDT [565228]: [60139-1] user=,db= > LOG:  recovery 
restart point at 6/B0ACB188
<2024-02-22 14:17:34.935 AEDT [565228]: [60140-1] user=,db= > DETAIL:  Last 
completed transaction was at log time 2024-02-22 14:15:00.457984+11.
<2024-02-22 14:20:23.383 AEDT [565227]: [7-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:23.383 AEDT [565231]: [6-1] user=,db= > FATAL:  terminating 
walreceiver process due to administrator command
<2024-02-22 14:20:23.385 AEDT [565227]: [8-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:23.385 AEDT [565227]: [9-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:23.385 AEDT [565227]: [10-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:28.390 AEDT [565227]: [11-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:33.394 AEDT [565227]: [12-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:38.394 AEDT [565227]: [13-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:43.398 AEDT [565227]: [14-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:48.402 AEDT [565227]: [15-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-22 14:20:53.406 AEDT [565227]: [16-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0


The replica was restarted and the walreceiver immediately failed again with -

2024-02-23 07:50:05.591 AEDT [1957117]: [2-1] user=,db= > LOG:  entering 
standby mode
<2024-02-23 07:50:05.607 AEDT [1957117]: [3-1] user=,db= > LOG:  redo starts at 
6/B0ACB188
<2024-02-23 07:50:05.607 AEDT [1957117]: [4-1] user=,db= > LOG:  consistent 
recovery state reached at 6/B0ACC360
<2024-02-23 07:50:05.607 AEDT [1957117]: [5-1] user=,db= > LOG:  invalid record 
length at 6/B0ACC360: wanted 24, got 0
<2024-02-23 07:50:05.609 AEDT [1957114]: [5-1] user=,db= > LOG:  database 
system is ready to accept read only connections
<2024-02-23 07:50:05.637 AEDT [1957121]: [1-1] user=,db= > LOG:  started 
streaming WAL from primary at 6/B000 on timeline 5
<2024-02-23 07:50:05.696 AEDT [1957117]: [6-1] user=,db= > LOG:  invalid magic 
number  in log segment 0005000600B0, offset 0
<2024-02-23 07:50:05.697 AEDT [1957121]: [2-1] user=,db= > FATAL:  terminating 
walreceiver process due to administrator command
<2024-02-23 07:50:05.798 AEDT [1957117]: [7-1] user=,db= > LOG:  invalid magic 
number  in log segment 0005000600B0, offset 0
<2024-02-23 07:50:05.798 AEDT [1957117]: [8-1] user=,db= > LOG:  invalid magic 
number  in log segment 0005000600B0, offset 0
<2024-02-23 07:50:06.215 AEDT [1957125]: [1-1] user=[unknown],db=[unknown] > 
LOG:  connection received: host=[local]


Running a pg_waldump against the WAL file on the primary server produces -

  deveng >pg_waldump 0005000600B0
  pg_waldump: fatal: WAL segment size must be a power of two 
between 1 MB and 1 GB, but the WAL file "0005000600B0" header 
specifies 0 bytes

The same error is returned when using the WAL in pg_wal or the WAL that has 
been archived locally on the primary server.

We also stream WAL to a barman backup server. Running pg_waldump against the 
WAL file on the barman server errors with -

  pg_waldump: fatal: error in WAL record at 6/B0ACC328: invalid 
record length at 6/B0ACC360: wanted 24, got 0

The primary cluster Postgres log does not show any unusual messages at the time 
of the original walreceiver failure -

<2024-02-22 14:20:00.279 AEDT [2408502]: [2-1] 
user=crmportaluser,db=reportingentity > LOG:  connection authorized: 
user=crmportaluser database=reportingentity SSL enabled (protocol=TLSv1.3, c
ipher=TLS_AES_256_GCM_SHA384, bits=25

RE: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL]

[Mark Schloss]

r barman server shows -

pg_waldump 00010008
  

rmgr: XLOGlen (rec/tot):114/   114, tx:  0, lsn: 
0/08000E68, prev 0/08000E30, desc: CHECKPOINT_ONLINE redo 0/8000E30; tli 1; 
prev tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 
in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; 
oldest running xid 498; online
rmgr: Standby len (rec/tot): 54/54, tx:  0, lsn: 
0/08000EE0, prev 0/08000E68, desc: RUNNING_XACTS nextXid 499 latestCompletedXid 
497 oldestRunningXid 498; 1 xacts: 498
rmgr: Databaselen (rec/tot): 42/42, tx:498, lsn: 
0/08000F18, prev 0/08000EE0, desc: CREATE copy dir 1663/1 to 1663/16451
rmgr: Standby len (rec/tot): 54/54, tx:  0, lsn: 
0/08000F48, prev 0/08000F18, desc: RUNNING_XACTS nextXid 499 latestCompletedXid 
497 oldestRunningXid 498; 1 xacts: 498
rmgr: XLOGlen (rec/tot):114/   114, tx:  0, lsn: 
0/08000F80, prev 0/08000F48, desc: CHECKPOINT_ONLINE redo 0/8000F48; tli 1; 
prev tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 
in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; 
oldest running xid 498; online
pg_waldump: fatal: error in WAL record at 0/8000F80: invalid record 
length at 0/8000FF8: wanted 24, got 0


Some observations from these clusters

- Both primary clusters continued to write and close the WAL files 
(00010008 and 0009000100AC) that the replicas were 
streaming at the time of the walreceiver failure
- The walreceiver on each replica was terminated when attempting to write the 
commit transaction 
- The walreceiver on the barman server did not fail but the WAL file does not 
contain the commit transaction
- The walreceiver on the barman server continued to receive streamed WAL for 
subsequent WAL files  
- The commit (0/08000FF8 and 1/AC000D78) lsn did not get streamed to either the 
replicas or barman server 

Thanks in advance.






UNOFFICIAL

-Original Message-
From: Kyotaro Horiguchi  
Sent: Monday, 26 February 2024 7:27 PM
To: Mark Schloss 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: walreceiver fails on asynchronous replica [SEC=UNOFFICIAL] 
[EXTERNAL]

At Fri, 23 Feb 2024 04:04:03 +, Mark Schloss  
wrote in 
> <2024-02-23 07:50:05.637 AEDT [1957121]: [1-1] user=,db= > LOG:  started 
> streaming WAL from primary at 6/B000 on timeline 5
> <2024-02-23 07:50:05.696 AEDT [1957117]: [6-1] user=,db= > LOG:  invalid 
> magic number  in log segment 0005000600B0, offset 0

This appears to suggest that the WAL file that the standby fetched was
zero-filled on the primary side, which cannot happen by a normal
operation. A preallocated WAL segment can be zero-filled but it cannot
be replicated under normal operations.

> <2024-02-22 14:20:23.383 AEDT [565231]: [6-1] user=,db= > FATAL:  terminating 
> walreceiver process due to administrator command

This may suggest a config reload with some parameter changes.

One possible scenario matching the log lines could be that someone
switched primary_conninfo to a newly-restored primary. However, if the
new primary had older data than the previously connected primary,
possibly leading to the situation where the segment 0..5..6..B0 on it
was a preallocated one that was filled with zeros, the standby could
end up fetching the zero-filled WAL segment (file) and might fail this
way. If this is the case, such operations should be avoided.

Unfortunately, due to the lack of a reproducer or detailed operations
that took place there, the best I can do now is to guess a possible
scenario as described above. I'm not sure how come the situation
actually arose.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

**
Please  note  that  your  email address  is known to  AUSTRAC  for the
purposes  of  communicating with you.  The information  transmitted in
this  e-mail is  for the  use of  the intended  recipient only and may
contain confidential and/or legally  privileged  material. If you have
received  this information  in error you must not disseminate, copy or
take  any  action on  it and we  request that you delete all copies of
this transmission together with attachments and notify the sender.

This footnote also confirms that this email message has been swept for
the presence of computer viruses.
**

RE: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL]

[Mark Schloss]

UNOFFICIAL
Hello,

Thanks for looking at this. I think I complicated things by including barman. I 
was just wanting to point out each primary streams to two locations - the 
walreceiver on the replica and the walreciver used by barman. We think the 
reason the barman WAL-receiver didn't fail is because there is no apply of the 
WAL in Barman but the Standby is applying and it's WAL-receiver got terminated, 
so the barman server can be taken out of this picture completely, they were 
just there as a by-product in trying to determine the effect.  We are only 
interested in the killing of the standby wal-receiver and that the pg_waldump 
showed the failing lsn was a commit.

Thanks






UNOFFICIAL

-Original Message-
From: Kyotaro Horiguchi  
Sent: Wednesday, 28 February 2024 5:09 PM
To: Mark Schloss 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: walreceiver fails on asynchronous replica [EXTERNAL] 
[SEC=UNOFFICIAL]

At Wed, 28 Feb 2024 03:23:37 +, Mark Schloss  
wrote in 
> Thank you for your reply. I can confirm there were no changes made to the 
> config of the replica.

Okay.

> Is there any significance in the parameters in the commit record -
> 'inval msgs: catcache 21; sync'.

I think it is not relevant.

> - The walreceiver on the barman server did not fail but the WAL file does not 
> contain the commit transaction

I don't have detailed knowledge of barman's behavior, but it seems to
be quite normal for barman to write out only on receiving a commit
record. What I don't understand here is how those WAL files on the
barman server are related to the failed replicas.

>From the information you provided, I guess that the replicas somehow
obtain the currently-written WAL file from the barman server at a
certain time through a route unknown to me, but you seem to know
that. I think that relationship has not been explained here.

Could you explain the routes and timings that WAL files are copied
between the servers?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

**
Please  note  that  your  email address  is known to  AUSTRAC  for the
purposes  of  communicating with you.  The information  transmitted in
this  e-mail is  for the  use of  the intended  recipient only and may
contain confidential and/or legally  privileged  material. If you have
received  this information  in error you must not disseminate, copy or
take  any  action on  it and we  request that you delete all copies of
this transmission together with attachments and notify the sender.

This footnote also confirms that this email message has been swept for
the presence of computer viruses.
**

SSL error on install of PEM during Posgres install

[mark bradley]

I am getting the following error message during install of PEM while installing 
Posgres.

[cid:da2b6d5e-c3b0-430a-98e0-dac72d0aba23]

How can I fix this?

PEM install error

[mark bradley]

While installing PostgreSQL I am getting this error message during the PEM 
server portion.  Do I need the PEM server?  If so, what is the solution?

Thanks!

[cid:b414e51e-598a-44bf-951a-ce754c0fa77d]

Re: ignore tablespace in schema definition queries

[Mark Johnson]

The solution depends on how you are creating the tables.

For example: the pg_restore has option  —-no-tablespaces. With this option,
all objects will be created in whichever tablespace is the default during
restore.  The pg_dump has similar.

If you are running CREATE TABLE statements that have hard-coded
tablespaces, then maybe pass your scripts through the sed or awk utility to
replace the name with pg_default just prior to execution.

Or, go ahead and create all possible tablespaces before running the CREATE
TABLE statements since each tablespace is just metadata not files like in
Oracle or SQL Server.

On Sat, Apr 3, 2021 at 6:59 AM Joao Miguel Ferreira <
joao.miguel.c.ferre...@gmail.com> wrote:

> Hello all,
>
> I have a big set of migration queries (that I do not control) that I must
> run on my automatic test database, in order to set ip up and run tests.
> These queries create all sorts of things like indexes, tables, and so. But
> they also include the specification of the tablespace they expect to use
> (tablespace_000, tablespace_001, up to tablespace_999). This would require
> me to setup hundreds of tablespaces before I can start the migration
> process, and run the tests.
>
> Is there a way to tell the postgres server to ignore that part and just
> use some default tablespace? My present situation is that I can not bring
> the test database to a usable point because many migration queries fail due
> to the tablespace they need has not been created. My problem is that I
> would like to avoid creating them.
>
> Thanks
> João
>
> --
- Mark

Re: Struggling with EXCLUDE USING gist

[Mark Dilger]

> On Jun 4, 2021, at 9:47 AM, Laura Smith  
> wrote:
> 
> CREATE TABLE test (
> t_val text not null,
> t_version text unique not null default gen_random_uuid() ,
> t_range tstzrange not null default tstzrange('-infinity','infinity'),
> EXCLUDE USING gist (t_val WITH=, t_range WITH &&) DEFERRABLE INITIALLY 
> DEFERRED
> );



> INSERT INTO test(t_val) values(p_val);

This will insert a t_range of ('-infinity','infinity'), won't it?  Wouldn't you 
want to instead insert with t_range starting around now() rather than starting 
at -infinity?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Struggling with EXCLUDE USING gist

[Mark Dilger]

> On Jun 4, 2021, at 11:55 AM, Laura Smith  
> wrote:
> 
> That seems to have done the trick. Thanks again Mark

Glad to hear it.  Good luck.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Do table-level CHECK constraints affect the query optimizer?

[Mark Dilger]

> On Jun 29, 2021, at 10:33 AM, Ron  wrote:
> 
> Prod is brand new.  Loaded on Saturday; we saw this problem on Sunday during 
> pre-acceptance.  Thus, while running ANALYZE was top of the list of Things To 
> Do, running VACUUM was low.
> 
> Is that a mistaken belief?

You might want to run VACUUM FREEZE and then retry your test query using 
EXPLAIN.  See if it switches to an index only scan after the VACUUM FREEZE.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Do table-level CHECK constraints affect the query optimizer?

[Mark Dilger]

> On Jun 29, 2021, at 11:02 AM, Ron  wrote:
> 
> What's an IOS?

An Index Only Scan.  See 
https://www.postgresql.org/docs/14/indexes-index-only-scans.html
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

version 0 calling convention

[Mark Lybarger]

i have some sql functions written in c code using version 0 calling
convention.  it's working fine on postgresql 9.6, but i'm migrating to
v13.  has this calling convention been removed?

my function creation give an error,

SQL Error [42883]: ERROR: could not find function information for function
"test_func"
  Hint: SQL-callable functions need an accompanying
PG_FUNCTION_INFO_V1(funcname).

Re: [E] Regexp_replace bug / does not terminate on long strings

[Mark Dilger]

> On Aug 20, 2021, at 9:52 AM, Tom Lane  wrote:
> 
> "a*" is easy.  "(a*)\1" is less easy --- if you let the a* consume the
> whole string, you will not get a match, even though one is possible.
> In general, backrefs create a mess in what would otherwise be a pretty
> straightforward concept :-(.

The following queries take radically different time to run:

\timing
select regexp_replace(
  repeat('someone,one,one,one,one,one,one,', 60),
  '(?<=^|,)([^,]+)(?:,\1)+(?=$|,)',
  '\1', -- replacement
  'g'  -- apply globally (all matches)
);

Time: 16476.529 ms (00:16.477)

select regexp_replace(
  repeat('someone,one,one,one,one,one,one,', 60),
  '(?<=^|,)([^,]+)(?:,\1){5}(?=$|,)',
  '\1', -- replacement
  'g'  -- apply globally (all matches)
);

Time: 1.452 ms

The only difference in the patterns is the + vs. the {5}.  It looks to me like 
the first pattern should greedily match five ",one" matches and be forced to 
stop since ",someone" doesn't match, and the second pattern should grab the 
five ",one" matches it was told to grab and not try to grab the ",someone", but 
other than that, they should be performing the same work.  I don't see why the 
performance should be so different.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [E] Regexp_replace bug / does not terminate on long strings

[Mark Dilger]

> On Aug 20, 2021, at 12:51 PM, Miles Elam  wrote:
> 
> Unbounded ranges seem like a problem.

Seems so.  The problem appears to be in regcomp.c's repeat() function which 
handles {1,SOME} differently than {1,INF}

> Seems worth trying a range from 1 to N where you play around with N to find 
> your optimum performance/functionality tradeoff. {1,20} is like '+' but 
> clamps at 20.

For any such value (5, 20, whatever) there can always be a string with more 
repeated words than the number you've chosen, and the call to regexp_replace 
won't do what you want.  There is also an upper bound at work, because values 
above 255 will draw a regex compilation error.  So it seems worth a bit of work 
to determine why the regex engine has bad performance in these cases.

It sounds like the OP will be working around this problem by refactoring to 
call regexp_replace multiple times until all repeats are eradicated, but I 
don't think such workarounds should be necessary.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company