Re: Order by not working

[Laurenz Albe]

On Tue, 2021-02-16 at 16:11 -0600, Ron wrote:
> SQL is only intuitive to people who've done programming... :)

SQL is quite counter-intuitive to people who have only done
procedural programming.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: delete old cluster after pg_upgrade with -k option

[Laurenz Albe]

On Wed, 2021-02-17 at 11:39 +0530, Atul Kumar wrote:
> I have upgrade the postgres cluster from 9.5 to 9.6 using pg_upgarde
> utility with -k option.
> 
> Now I just wanted to be confirmed that is it safe to run
> delete_old_cluster.sh file as we have used -k option that must created
> hard links with old cluster.

It is absolutely safe.

It would actually be quite unsafe *not* to delete the old cluster,
because if anybody managed to start it, data corruption would be the
consequence.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

delete old cluster after pg_upgrade with -k option

[Atul Kumar]

Hi,

I have upgrade the postgres cluster from 9.5 to 9.6 using pg_upgarde
utility with -k option.

Now I just wanted to be confirmed that is it safe to run
delete_old_cluster.sh file as we have used -k option that must created
hard links with old cluster.


Suggestions are welcome.

Proposed Update to Japanese Translation of Code of Conduct Policy

[Stacey Haysler]

The PostgreSQL Community Code of Conduct Committee has received a draft of the 
Japanese translation of the Code of Conduct Policy updated August 18, 2020 for 
review.

The English version of the Policy is at:
https://www.postgresql.org/about/policies/coc/

The patch was created by:
Tatsuo Ishii, affiliation: PostgreSQL Enterprise Consortium, SRA OSS, Inc., 
Japan

The patch was reviewed by:
Tetsuo Sakata, affiliation: PostgreSQL Enterprise Consortium, NTT OSS Center
Akira Kurosawa, affiliation: PostgreSQL Enterprise Consortium, NEC Solution 
Innovators, Ltd.

The proposed patch is attached as a file to this message.

If you have any comments or suggestions for the translation, please bring them 
to our attention no later than 5:00 PM PST on Wednesday, February 26, 2021.

Thank you.

Regards,
Stacey

Stacey Haysler
Chair
PostgreSQL Community Code of Conduct Committee



Japanese Translation Diff File.pdf
Description: Adobe PDF document

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Ron]

On 2/16/21 6:19 PM, Tim Cross wrote:

Ron  writes:


On 2/16/21 5:44 PM, Tim Cross wrote:

Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.

In my case, the statements are generated by Ora2Pg, and the DO blocks are
generated by a bash script I wrote.  Input data can be messy, so want to
verify things before running.

Sure, vim is great at highlighting some problems, but certainly not all.

What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
it runs just the parser and then stops,

Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
servers available are mature enough yet, but that is definitely the
objective. Real benefit is that it is editor agnostic. Once your editor
has LSP support, all you need to do is configure the server details and
you get parsing, completion, re-factoring, definition lookup etc.


Naively, it seems that it should be easy to add this to psql.  The EXPLAIN 
verb exists, so it shouldn't be hard to add SYNTAX, which does less work 
than EXPLAIN.



--
Angular momentum makes the world go 'round.

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Tim Cross]

Ron  writes:

> On 2/16/21 5:44 PM, Tim Cross wrote:
>> Given the number, I think I would do the same. A good example of why
>> being 'lazy' can be a virtue. Faster and easier to write a procedure to
>> generate dynamic SQL than write out all those alter statements manually
>> or even write it using a scripting language and ODBC if there is
>> sufficient variation in the statements to make writing it in plsql
>> 'messy'.
>
> In my case, the statements are generated by Ora2Pg, and the DO blocks are
> generated by a bash script I wrote.  Input data can be messy, so want to
> verify things before running.
>
> Sure, vim is great at highlighting some problems, but certainly not all.
>
> What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5:
> it runs just the parser and then stops,

Sounds like exactly what LSP aims to provide. Don't know if the LSP SQL
servers available are mature enough yet, but that is definitely the
objective. Real benefit is that it is editor agnostic. Once your editor
has LSP support, all you need to do is configure the server details and
you get parsing, completion, re-factoring, definition lookup etc.

--
Tim Cross

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Ron]

On 2/16/21 5:44 PM, Tim Cross wrote:

Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.


In my case, the statements are generated by Ora2Pg, and the DO blocks are 
generated by a bash script I wrote.  Input data can be messy, so want to 
verify things before running.


Sure, vim is great at highlighting some problems, but certainly not all.

What I'm really looking for the PostgreSQL version of SQL Server's Ctrl-F5: 
it runs just the parser and then stops,


--
Angular momentum makes the world go 'round.

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Tim Cross]

David G. Johnston  writes:

> On Tue, Feb 16, 2021 at 4:28 PM Tim Cross  wrote:
>
>>
>> David G. Johnston  writes:
>>
>> > On Tue, Feb 16, 2021 at 3:43 PM Ron  wrote:
>> >
>> >>
>> >> How does one go about syntax checking this?
>> >>
>> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
>> in
>> >> similar DO blocks, and want to make sure the statements are clean.)
>> >>
>> >>
>> > Begin a transaction, execute the DO, capture an error if there is one,
>> > rollback the transaction.
>> >
>>
>> As David points out, wrapping the whole thing in a transaction will at
>> least guarantee it all succeeds or it is all rollled back. This can be
>> frustrating if the statements are slow and there are a lot of them as it
>> can result in a very tedious do-run-fix cycle.
>>
>>
> I do presume that someone wanting to test their code in this manner would
> be doing so in a test environment and an empty database.  Which makes the
> execution time very small.
>

True. However, it never ceases to amaze me how many places don't have
such environments. Far too often, my first task when commencing a new
engagement is to sort out environments and procedures to manage change.

> I personally would also solve the "lot of them" problem by using dynamic
> SQL, so one pretty much only has to test the code generator instead of all
> the actual executions - which can simply be confirmed fairly quickly once
> on a test database without the need for transactions.
>

Given the number, I think I would do the same. A good example of why
being 'lazy' can be a virtue. Faster and easier to write a procedure to
generate dynamic SQL than write out all those alter statements manually
or even write it using a scripting language and ODBC if there is
sufficient variation in the statements to make writing it in plsql
'messy'.

--
Tim Cross

Re: Syntax checking DO blocks and ALTER TABLE statements?

[David G. Johnston]

On Tue, Feb 16, 2021 at 4:28 PM Tim Cross  wrote:

>
> David G. Johnston  writes:
>
> > On Tue, Feb 16, 2021 at 3:43 PM Ron  wrote:
> >
> >>
> >> How does one go about syntax checking this?
> >>
> >> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
> in
> >> similar DO blocks, and want to make sure the statements are clean.)
> >>
> >>
> > Begin a transaction, execute the DO, capture an error if there is one,
> > rollback the transaction.
> >
>
> As David points out, wrapping the whole thing in a transaction will at
> least guarantee it all succeeds or it is all rollled back. This can be
> frustrating if the statements are slow and there are a lot of them as it
> can result in a very tedious do-run-fix cycle.
>
>
I do presume that someone wanting to test their code in this manner would
be doing so in a test environment and an empty database.  Which makes the
execution time very small.

I personally would also solve the "lot of them" problem by using dynamic
SQL, so one pretty much only has to test the code generator instead of all
the actual executions - which can simply be confirmed fairly quickly once
on a test database without the need for transactions.

David J.

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Tim Cross]

David G. Johnston  writes:

> On Tue, Feb 16, 2021 at 3:43 PM Ron  wrote:
>
>>
>> How does one go about syntax checking this?
>>
>> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
>> similar DO blocks, and want to make sure the statements are clean.)
>>
>>
> Begin a transaction, execute the DO, capture an error if there is one,
> rollback the transaction.
>

As David points out, wrapping the whole thing in a transaction will at
least guarantee it all succeeds or it is all rollled back. This can be
frustrating if the statements are slow and there are a lot of them as it
can result in a very tedious do-run-fix cycle.

Something which can help is using an editor with good font highlighting
and parsing support. One interesting area I've not yet looked at is the
development of LSP (Language Server Protocol) servers for SQL. I've used
LSP for other languages with great success. The challenge with databases
is that there is enough variation between different vendor
implementations to make accurate parsing and validation tedious to
implement, so most solutions only focus on ANSI compliance. Still, that
can be very useful.

See https://github.com/lighttiger2505/sqls for one example of an LSP
server for SQL and https://microsoft.github.io/language-server-protocol/
for more background on LSP and what it can provide. Many editors,
including VSCode, VI, Emacs, TextMate etc now have some support for LSP.


--
Tim Cross

Re: Syntax checking DO blocks and ALTER TABLE statements?

[David G. Johnston]

On Tue, Feb 16, 2021 at 3:43 PM Ron  wrote:

>
> How does one go about syntax checking this?
>
> (There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in
> similar DO blocks, and want to make sure the statements are clean.)
>
>
Begin a transaction, execute the DO, capture an error if there is one,
rollback the transaction.

David J.

Syntax checking DO blocks and ALTER TABLE statements?

[Ron]

How does one go about syntax checking this?

do $$
begin if exists (select 1 from information_schema.table_constraints
   where constraint_name = 'error_to_web_service_error') then
        raise notice 'EXISTS error_to_web_service_error';
    else
        ALTER TABLE web_service_error
   ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
   REFERENCES error_code(error_id)
   ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
    end if
end $$

(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in 
similar DO blocks, and want to make sure the statements are clean.)


--
Angular momentum makes the world go 'round.

Re: Order by not working

[Ron]

SQL is only intuitive to people who've done programming... :)

Also, since your table names are only composed of lower case and 
underscores, the double quotes are not needed.


On 2/16/21 1:41 PM, Dan Nessett wrote:
Thanks to those who responded. I have solved my problem by noting the 
advice to use a select with order by. In particular, I need to export the 
data to a csv file anyway, so I use the following copy command:


COPY (SELECT household_name, family_list, street_address, city, state, 
zip, phone_list, email_list

    FROM "household_data"
    ORDER BY household_name
    )
TO '/tmp/household_data.csv'
WITH (FORMAT CSV, HEADER);

This works.

Regards,

Dan

On Feb 16, 2021, at 12:35 PM, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:



On Tuesday, February 16, 2021, Dan Nessett > wrote:


Thanks Peter. The listing of the result is from pg-admin 4.30 using
view/edit data applied to the household_data table. In the past this
has always returned the table contents in the ORDR BY sort order. Do
I need to specify some preference in pg_admin to guarantee this?


pgAdmin4 might be keying off of the presence of an index, which this 
table doesn’t have.


David J.




--
Angular momentum makes the world go 'round.

Re: How to return a jsonb list of lists (with integers)

[David G. Johnston]

On Tuesday, February 16, 2021, Alexander Farber 
wrote:
>
> But is it possible in SQL to combine all 3 queries, so that a JSONB list
> of lists is returned?
> So I have to use PL/PgSQL, correct?
>

With liberal usage of CTEs and subqueries writing a single SQL query should
be doable.

David J.

Re: How to return a jsonb list of lists (with integers)

[Alexander Farber]

Thank you, David, with json_build_array() it works for a single query -

SELECT
JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won')  OR
(player2 = in_uid AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);

But is it possible in SQL to combine all 3 queries, so that a JSONB list of
lists is returned?

I cannot use a UNION, because the first two queries return 3 columns, but
the last query returns 7 columns.

So I have to use PL/PgSQL, correct?

Best regards
Alex

Re: How to return a jsonb list of lists (with integers)

[Alexander Farber]

On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis  wrote:

> Aggregate functions work on a single column to summarize many rows into
> fewer rows. You seem to be wanting to combine multiple columns which would
> be done by concatenation or array[column1,column2] or something like that.
>

Ah right, Michael, thanks - that is what I realised just after sending the
mail.

I don't have rows here, but a single row with several columns.

Re: Order by not working

[Dan Nessett]

Thanks to those who responded. I have solved my problem by noting the advice to 
use a select with order by. In particular, I need to export the data to a csv 
file anyway, so I use the following copy command:

COPY (SELECT household_name, family_list, street_address, city, state, zip, 
phone_list, email_list
FROM "household_data" 
ORDER BY household_name
)
TO '/tmp/household_data.csv'
WITH (FORMAT CSV, HEADER);

This works.

Regards,

Dan

> On Feb 16, 2021, at 12:35 PM, David G. Johnston  
> wrote:
> 
> 
> On Tuesday, February 16, 2021, Dan Nessett  > wrote:
> Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit 
> data applied to the household_data table. In the past this has always 
> returned the table contents in the ORDR BY sort order. Do I need to specify 
> some preference in pg_admin to guarantee this?
> 
> pgAdmin4 might be keying off of the presence of an index, which this table 
> doesn’t have.
> 
> David J.

Re: Order by not working

[David G. Johnston]

On Tuesday, February 16, 2021, Dan Nessett  wrote:

> Thanks Peter. The listing of the result is from pg-admin 4.30 using
> view/edit data applied to the household_data table. In the past this has
> always returned the table contents in the ORDR BY sort order. Do I need to
> specify some preference in pg_admin to guarantee this?
>

pgAdmin4 might be keying off of the presence of an index, which this table
doesn’t have.

David J.

Re: Order by not working

[Dan Nessett]

Thanks,

Dan

> On Feb 16, 2021, at 12:11 PM, Ron  wrote:
> 
> What would you tell pgadmin?  "Order this particular query -- out of all the 
> billion queries I might write -- in this particular manner?"
> 
> No, that's not how things work.  Just add an ORDER BY when you query the 
> table.
> 
> On 2/16/21 12:48 PM, Dan Nessett wrote:
>> Thanks Peter. The listing of the result is from pg-admin 4.30 using 
>> view/edit data applied to the household_data table. In the past this has 
>> always returned the table contents in the ORDR BY sort order. Do I need to 
>> specify some preference in pg_admin to guarantee this?
>> 
>> Dan
>> 
>>> On Feb 16, 2021, at 11:34 AM, Peter Coppens  
>>>  wrote:
>>> 
>>> Not sure how you select the household
>>> 
 The result is (only the first column is shown):
 
 household_name
 
 "Garcia"
 "Armstrong"
 "Armstrong"
 "Bauer"
 "Bauer"
 "Berst"
 "Berst"
 "Minch ()"
 "Berst"
 “Besel”
>>> 
>>> but unless you select from the resulting table using again an order by, the 
>>> rows will be returned in an undetermined order. Such is the nature of the 
>>> relational model - there is no order you can rely on when selecting, unless 
>>> you specify it
>>> 
>>> Hth,
>>> 
>>> Peter
>>> 
>> 
>> 
> 
> -- 
> Angular momentum makes the world go 'round.

Re: Order by not working

[Ron]

What would you tell pgadmin?  "Order *this* *particular* query -- out of all 
the billion queries I might write -- in *this particular* manner?"


No, that's not how things work.  Just add an ORDER BY when you query the table.

On 2/16/21 12:48 PM, Dan Nessett wrote:

Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit 
data applied to the household_data table. In the past this has always returned 
the table contents in the ORDR BY sort order. Do I need to specify some 
preference in pg_admin to guarantee this?

Dan


On Feb 16, 2021, at 11:34 AM, Peter Coppens  wrote:

Not sure how you select the household


The result is (only the first column is shown):

household_name

"Garcia"
"Armstrong"
"Armstrong"
"Bauer"
"Bauer"
"Berst"
"Berst"
"Minch ()"
"Berst"
“Besel”


but unless you select from the resulting table using again an order by, the 
rows will be returned in an undetermined order. Such is the nature of the 
relational model - there is no order you can rely on when selecting, unless you 
specify it

Hth,

Peter






--
Angular momentum makes the world go 'round.

Re: How to return a jsonb list of lists (with integers)

[Michael Lewis]

Aggregate functions work on a single column to summarize many rows into
fewer rows. You seem to be wanting to combine multiple columns which would
be done by concatenation or array[column1,column2] or something like that.

Re: How to return a jsonb list of lists (with integers)

[David G. Johnston]

On Tue, Feb 16, 2021 at 11:47 AM Alexander Farber <
alexander.far...@gmail.com> wrote:

> Thank you for any hints
>
>
json_build_array(...)

David J.

Re: Order by not working

[Dan Nessett]

Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit 
data applied to the household_data table. In the past this has always returned 
the table contents in the ORDR BY sort order. Do I need to specify some 
preference in pg_admin to guarantee this?

Dan

> On Feb 16, 2021, at 11:34 AM, Peter Coppens  wrote:
> 
> Not sure how you select the household
> 
>> 
>> The result is (only the first column is shown):
>> 
>> household_name
>> 
>> "Garcia"
>> "Armstrong"
>> "Armstrong"
>> "Bauer"
>> "Bauer"
>> "Berst"
>> "Berst"
>> "Minch ()"
>> "Berst"
>> “Besel”
> 
> 
> but unless you select from the resulting table using again an order by, the 
> rows will be returned in an undetermined order. Such is the nature of the 
> relational model - there is no order you can rely on when selecting, unless 
> you specify it
> 
> Hth,
> 
> Peter
> 

How to return a jsonb list of lists (with integers)

[Alexander Farber]

Good evening,

In 13.2 I have 3 SQL queries, which work well and return integer values.

The values I feed to Google Charts (and currently I switch to Chart.js).

Currently I use the queries by calling 3 different custom stored functions
by my Java servlet.

I would like to convert the functions to 1 function, in SQL or if not
possible, then PL/pgSQL.

The new function should return a JSONB list containing 3 other lists, i.e.
something like:

[ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ]

I think I should use the aggregate function jsonb_agg().

But I can't figure out how to apply it to the 3 queries below, could you
please help me?

CREATE OR REPLACE FUNCTION words_stat_charts(
in_uid  integer,
in_opponent integer
) RETURNS jsonb AS
$func$
-- how to return [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] ?

SELECT
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won')  OR
(player2 = in_uid AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);

SELECT
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won')  OR
(player2 = in_uid AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);

SELECT
SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer
FROM words_scores WHERE uid = in_uid;

$func$ LANGUAGE sql STABLE;

When I try simply wrapping the jsonb_agg() around the 3 columns in the
first query I get the syntax error:

SELECT
JSONB_AGG(
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won')  OR
(player2 = in_uid AND state2 = 'won')  THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);

ERROR:  function jsonb_agg(integer, integer, integer) does not exist
LINE 8: JSONB_AGG(
^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.

Thank you for any hints
Alex

Re: Slow index creation

[Michael Lewis]

What is your concern with it taking 20 hours vs 1 hour? Is this index
re-created on a regular basis?

Would it make any sense to materialize the value of foo(a,b,c) as a
generated column (PG12+ natively, or maintained by a trigger before)? Or
even bar(foo(a,b,c),geom)?

Do you know if parallel_workers are being used?

JIT is available in PG11, it is just off by default. If it is available,
turning it on and trying it seems like the simplest check if it would speed
up the index creation.

Re: Order by not working

[Peter Coppens]

Not sure how you select the household

> 
> The result is (only the first column is shown):
> 
> household_name
> 
> "Garcia"
> "Armstrong"
> "Armstrong"
> "Bauer"
> "Bauer"
> "Berst"
> "Berst"
> "Minch ()"
> "Berst"
> “Besel”


but unless you select from the resulting table using again an order by, the 
rows will be returned in an undetermined order. Such is the nature of the 
relational model - there is no order you can rely on when selecting, unless you 
specify it

Hth,

Peter

Slow index creation

[Paul van der Linden]

Hi,

I have 2 functions:
CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
$func$
DECLARE
retVal text;
BEGIN
SELECT
  CASE
WHEN a='v1' AND b='b1' THEN 'r1'
WHEN a='v1' THEN 'r2'
... snip long list containing various tests on a,b and c
WHEN a='v50' THEN 'r50'
  END INTO retval;
RETURN retVal;
END
$func$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE FUNCTION bar(r text, geom geometry) RETURNS int AS
$func$
DECLARE
retVal int;
BEGIN
SELECT
  CASE
WHEN r='r1' AND st_area(geom)>100 THEN 1
WHEN r='r1' THEN 2
... snip long list containing various tests on r and st_area(geom)
WHEN r='r50' THEN 25
  END INTO retval;
RETURN retVal;
END
$func$ LANGUAGE PLPGSQL IMMUTABLE;

and a large table t (100M+ records) with columns a, b, c and geom running
on PG 11, on spinning disks with 64GB memory and 28 cores.

When I create a simple geom index with CREATE INDEX ON t USING gist(geom)
it finishes in about an hour, but when I create a partial index using these
2 functions
CREATE INDEX ON t USING gist(geom) WHERE bar(foo(a,b,c),geom)<12 it takes
over 20 hours...

Is that because I'm using functions in the WHERE clause, or because my CASE
lists are quite long, or both?
Is there any way to speed up that index creation? Is upgrading to a newer
postgres a viable option (so the JITTER can speed up the functions)?

Paul

Order by not working

[Dan Nessett]

Hello,

I am using "PostgreSQL 9.6.5 on x86_64-apple-darwin, compiled by 
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) 
(LLVM build 2336.11.00), 64-bit"

I am having trouble with a create select statement’s order by clause. The input 
table, “household_complete_data", (1st 10 rows) looks like this (data hidden 
for privacy):

household_name, first_name, street_address, city,   state,  zip,
home_phone, home_email, cell,   personal_email_primary, 
personal_email_secondary

"Armstrong” "”  "x” "”  "”  ""  

"Armstrong” "”  "”  "”  "”  "”  
""  "”  ""
"Bauer” "”  "”  "”  "”  “”  
“"  
"Bauer” "”  "”  "”  "”  "”  
"”  "”  ""
"Berst” "”  "”  "”  "”  "”  
""  
"Berst” "”  "”  "”  "”  "”  
""  
"Berst” "”  "”  "”  "”  "”  
""  
"Berst” "”  "”  "”  "”  "”  
"”  "”  ""
"Berst” "”  "”  "”  "”  "”  
""  “”  ""
"Berst” "”  "”  "”  "”  "2” 
”   “"

To this table I apply the following SQL statement:

CREATE TABLE "household_data" AS 
SELECT household_name,
string_agg(household_complete_data.first_name, ', ') AS family_list,
street_address, city, state, zip,
string_agg(COALESCE(household_complete_data.home_phone, '') || ',' ||   
COALESCE(household_complete_data.cell, ''), ',') AS phone_list, 
string_agg(COALESCE(household_complete_data.home_email, '') || ',' || 
COALESCE(household_complete_data.personal_email_primary, '') || ',' || 
COALESCE(household_complete_data.personal_email_secondary, ''), ',') AS 
email_list
FROM "household_complete_data"
GROUP BY household_name, street_address, city, state, zip
ORDER BY household_name;

The result is (only the first column is shown):

household_name

"Garcia"
"Armstrong"
"Armstrong"
"Bauer"
"Bauer"
"Berst"
"Berst"
"Minch ()"
"Berst"
“Besel"

The ORDER BY clause doesn’t seem to work properly (note: “Minch ()” is an 
entry for the household name that has the first name in parentheses). All 
through the table there are random insertions of rows that are out of order 
with respect to the household_name. This has me stumped. Can anyone give me a 
hint of what might be going wrong?

Regards,

Dan Nessett

Re: pg_stat_user_tables.n_mod_since_analyze persistence?

[Philip Semanchuk]

> On Feb 15, 2021, at 3:55 PM, Tom Lane  wrote:
> 
> Philip Semanchuk  writes:
>> I saw some unexpected behavior that I'm trying to understand. I suspect it 
>> might be a quirk specific to AWS Aurora and I'd like to confirm that.
> 
>> When I restart my local Postgres instance (on my Mac), the values in 
>> pg_stat_user_tables.n_mod_since_analyze are preserved. In other words, if 
>> table foo had n_mod_since_analyze=33 before the reboot, it still has 
>> n_mod_since_analyze=33 after the restart. 
> 
>> When I restart an AWS Aurora instance, the values in 
>> pg_stat_user_tables.n_mod_since_analyze all seem to be reset to 0. 
> 
>> Can anyone confirm (or refute) that the behavior I see on my Mac 
>> (preservation of these values through a restart) is common & expected 
>> behavior?
> 
> Yeah, in PG those stats would be preserved, at least as long as it's
> a clean shutdown.


Thanks, Tom. A colleague pointed me to a blog post by Michael Vitale that 
confirms this bug on AWS and contains more detail:
https://elephas.io/685-2/


Hope this helps someone else
Philip

Re: Turn jit off for slow subquery in Postgres 12

[Michael Lewis]

Either turn it off, or increase jit_above_cost, jit_inline_above_cost,
and/or jit_optimize_above_cost.

Re: PostgreSQL Replication

[Mutuku Ndeti]

Thanks. Is there a free version of BDR?

On Tue, Feb 16, 2021, 5:29 PM Raul Giucich  wrote:

> This article will help you
> https://wiki.postgresql.org/wiki/Multimaster.
>
> El mar., 16 feb. 2021 10:56, Mutuku Ndeti  escribió:
>
>> Hi,
>>
>> Need some advice here. I have an application using PostgreSQL. I need to
>> install it on 2 servers for redundancy purposes and have 2 databases. I
>> need the DBs to replicate to each other, in real-time. Writes can be done
>> on both DBs.
>>
>> Please let me know if this is a feasible setup and the best way to
>> proceed.
>>
>> Thank you
>>
>> --
>> www.agile.co.ke
>>
>>

Re: Replication sequence

[Paolo Saudin]

Thank you!

Il giorno mar 16 feb 2021 alle ore 13:38 Jehan-Guillaume de Rorthais <
j...@dalibo.com> ha scritto:

> On Tue, 16 Feb 2021 13:10:54 +0100
> Paolo Saudin  wrote:
>
> > Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
> >  [...]
> >
> >  [...]
> >  [...]
> >  [...]
> >  [...]
> >  [...]
> >  [...]
> >  [...]
> >
> > Thank you very much!
> > So in case the primary server crashes, and the backup one gets live, it
> > will eventually have different sequences.
>
> No. You will just have a gap in the sequence, on both side.
>
> And if you decided to promote your standby to production, you'll have to
> resync
> the old primary anyway.
>
> > I suppose there is no way to
> > avoid this, isnt'it?
>
> You can't avoid gap.
>
>
>

Re: PostgreSQL Replication

[Raul Giucich]

This article will help you
https://wiki.postgresql.org/wiki/Multimaster.

El mar., 16 feb. 2021 10:56, Mutuku Ndeti  escribió:

> Hi,
>
> Need some advice here. I have an application using PostgreSQL. I need to
> install it on 2 servers for redundancy purposes and have 2 databases. I
> need the DBs to replicate to each other, in real-time. Writes can be done
> on both DBs.
>
> Please let me know if this is a feasible setup and the best way to
> proceed.
>
> Thank you
>
> --
> www.agile.co.ke
>
>

PostgreSQL Replication

[Mutuku Ndeti]

Hi,

Need some advice here. I have an application using PostgreSQL. I need to
install it on 2 servers for redundancy purposes and have 2 databases. I
need the DBs to replicate to each other, in real-time. Writes can be done
on both DBs.

Please let me know if this is a feasible setup and the best way to proceed.

Thank you

-- 
www.agile.co.ke

Re: Replication sequence

[Jehan-Guillaume de Rorthais]

On Tue, 16 Feb 2021 13:10:54 +0100
Paolo Saudin  wrote:

> Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
>  [...]  
> 
>  [...]  
>  [...]  
>  [...]  
>  [...]  
>  [...]  
>  [...]  
>  [...]  
> 
> Thank you very much!
> So in case the primary server crashes, and the backup one gets live, it
> will eventually have different sequences.

No. You will just have a gap in the sequence, on both side.

And if you decided to promote your standby to production, you'll have to resync
the old primary anyway.

> I suppose there is no way to
> avoid this, isnt'it?

You can't avoid gap.

Re: Replication sequence

[Paolo Saudin]

Il giorno mar 16 feb 2021 alle ore 10:51 Jehan-Guillaume de Rorthais <
j...@dalibo.com> ha scritto:

> On Mon, 15 Feb 2021 18:55:14 +0100
> Paolo Saudin  wrote:
>
> > Hi all,
> > I have two servers, a primary and a secondary one with a streaming
> replica
> > setup.
> > Today I noticed that some sequences are not lined-up, the replica ones
> are
> > well ahead, while the records number is the same. How is it possible?
>
> This is because sequences are not WAL logged on every nextval() call, but
> by
> batch of sequence cache+32 values. As standbys are fed with WAL stream from
> primary, their sequences appears jump, then stall, then jump, etc.
>
> See:
>
> https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n52
>
>
> https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n661
>
> I'm not sure if this is documented somewhere... ?
>

Thank you very much!
So in case the primary server crashes, and the backup one gets live, it
will eventually have different sequences. I suppose there is no way to
avoid this, isnt'it?
Paolo

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Thomas Kellerer]

Guy Burgess schrieb am 15.02.2021 um 11:52:
> The mystery now is that the only process logged as touching the
> affected WAL files is postgres.exe (of which there are many separate
> processes). Could it be that one of the postgres.exe instances is
> holding the affected WAL files in use after another postgres.exe
> instance has flagged the file as deleted? (or to put it the other
> way, a postgres.exe instance is flagging the file as deleted while
> another instance still has an open handle to the file)? If it is some
> other process such as the indexer (disabled) or AV (excluded from
> pgdata) is obtaining a handle on the WAL files, it isn't being logged
> by ProcMon.

My first suspect is always the anti-virus on Windows when things like
that happen with Postgres.

Maybe the AV configuration was changed and pgdata is no longer excluded.

Thomas

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Guy Burgess]

On 16/02/2021 12:23 am, Thorsten Schöning wrote:

The mystery now is that the only process logged as touching the
affected WAL files is postgres.exe (of which there are many separate
processes). Could it be that one of the postgres.exe instances is
holding the affected WAL files in use after another postgres.exe
instance has flagged the file as deleted?[...]

I suggest checking your WAL-related and archive/backup settings for
Postgres again. There's e.g. "archive_command" optionally copying WALs
to some other place and postgres.exe would wait until that process has
finished, maybe locking the file to copy itself as well. Or
"archive_timeout" interfering with some other operations or alike.


Thanks Thorsten.  The WAL archive settings are out-of-the-box defaults, 
i.e. disabled: archive_mode = off; archive_command = ''; archive_timeout 
= 0.


I'm not sure there is anything else I can check at this time. The good 
thing is it doesn't seem to cause any problem other than logging "could 
not rename file" warnings, so I might have to park this for now. If I 
find anything else that might offer a new lead I will report back.


Kind regards,

Guy

Re: Replication sequence

[Jehan-Guillaume de Rorthais]

On Mon, 15 Feb 2021 18:55:14 +0100
Paolo Saudin  wrote:

> Hi all,
> I have two servers, a primary and a secondary one with a streaming replica
> setup.
> Today I noticed that some sequences are not lined-up, the replica ones are
> well ahead, while the records number is the same. How is it possible?

This is because sequences are not WAL logged on every nextval() call, but by
batch of sequence cache+32 values. As standbys are fed with WAL stream from
primary, their sequences appears jump, then stall, then jump, etc.

See:
  
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n52

  
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/commands/sequence.c?id=f672df5fdd22dac14c98d0a0bf5bbaa6ab17f8a5#n661

I'm not sure if this is documented somewhere... ?

Re: [LDAPS] Test connection user with ldaps server

[João Gaspar]

Hi all,
thanks for the feedback.

I was able to do it successfully but I didn't understand yet if there is a
bug in pg_hba.conf LDAP link interpretation or a messy domain.

So as I said previously, the ldapsearch is finding correctly the user1 fine
using only the url  dc=company,dc=example,dc=com
ldapsearch -x -H ldaps://serverurl -W -D "cn= user-to-do-
autentication-ldap-connection" -b "dc=company,dc=example,dc=com"
"sAMAccountName=user1"

Before (Can't search):
hostall all 0.0.0.0/0   ldap
 ldapurl="ldaps://serverurl:636/DC=company,DC=example,DC=com?sAMAccountName?sub"
ldapbinddn="user-to-do-autentication-ldap-connection" ldapbindpasswd="
user-ldap-connection password-autentication"


So what I did was add the Organization Unit (OU) and now works fine.

After:
hostall all 0.0.0.0/0   ldap
 
ldapurl="ldaps://serverurl:636/OU=sub-company,DC=company,DC=example,DC=com?sAMAccountName?sub"
ldapbinddn="user-to-do-autentication-ldap-connection" ldapbindpasswd="
user-ldap-connection password-autentication"


Best regards,
João Gaspar


Thomas Munro  escreveu no dia segunda, 15/02/2021
à(s) 22:39:

> On Tue, Feb 16, 2021 at 4:32 AM Laurenz Albe 
> wrote:
> > What I would do is experiment with the "ldapsearch" executable from
> OpenLDAP and see
> > if you can reproduce the problem from the command line.
>
> Also, maybe try doing this as the "postgres" user (or whatever user
> PostgreSQL runs as), just in case there are some environmental
> differences affecting the behaviour.
>

Re: ALTER ROLE ... SET in current database only

[Wolfgang Walther]

Abdul Qoyyuum:
Wouldnt you need to connect to the database first before you can ALTER 
ROLE anything?


Of course, otherwise the notion of "current database" wouldn't make 
sense at all. But that's only before executing the code. I am not 
writing and executing this code at the same time.


In my case I'm seeding a postgres docker container with settings and 
data on first launch. The database name is passed to the container via 
environment variable. But, I'm sure there are other use-cases where code 
should be written once, but be executed in different databases.


Best,

Wolfgang