Re: Unable to install postgresql and pgadmin

[Adrian Klaver]

On 1/9/24 03:33, Innocent Ememe wrote:

Reply to list also.
Ccing list.


Hello
Thank you for your email, I’m getting the software from the official 
PostgreSQL.org website. I used to download command to install, my issues 


If you mean here:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

that is the EDB Website. They build a installer for the Postgres server 
as well as additional packages such as pgAdmin.



lies with the stack builder actually. Once the PostgreSQL is done 
downloading the stack builder starts downloading and then quits 
automatically. The message is usually it has “stacker builder has quit 
automatically we will contact apple”


That is something you will need to take up with EDB as that is their 
code. I don't know how to contact them directly. My suggestion would be 
to post here:


https://www.postgresql.org/list/pgadmin-support/

There is likely to be more folks there that could point you in the right 
direction.




That’s why I wanted to find out if the software was compatible with my 
system.


Cheers
Innocent

On Mon, 8 Jan 2024 at 16:42, Adrian Klaver > wrote:


On 1/8/24 00:23, Innocent Ememe wrote:
 > Good morning,
 >
 > Hey I’ve been trying to install the postgresql and pgadmin
software on
 > my Mac book for the past three days now and have been faced with
some
 > issues and restrictions, that won’t allow the software to run. I
wanted
 > to ask if your software is compatible with my MacBook Air 2012,  iOS
 > version 10.15.

Where are you getting the software from?

Show the commands you are using to install.

What are the error messages you are getting?


 > Looking forward to your reply
 >
 > Cheers
 > Innocent
 >

-- 
Adrian Klaver

adrian.kla...@aklaver.com 



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

Re: ERROR: invalid byte sequence for encoding UTF8: 0x00

[Tom Lane]

Laurenz Albe  writes:
> On Tue, 2024-01-09 at 17:48 +0530, Kiran K V wrote:
>> I have a table with bytea column and trying to load the data using copy 
>> command.
>> But Copy command is failing with
>> "ERROR: invalid byte sequence for encoding UTF8: 0x00.
>> Why postgresql is failing to load when data contains 0x00.
>> How to resolve this error ? any workaround to load the data with 0x00 values 
>> ?

> This is not about loading data into a "bytea", but into a "text", "varchar" or
> "char" column.  Check again.

I am guessing that the OP tried to put a literal NUL character
into the COPY data.  A bytea value in COPY data needs to be
text-encoded as per one of the alternatives explained in the
manual.  Alternatively, you could jump through the hoops involved
in sending binary COPY data.

regards, tom lane

Re: ERROR: invalid byte sequence for encoding UTF8: 0x00

[Laurenz Albe]

On Tue, 2024-01-09 at 17:48 +0530, Kiran K V wrote:
> I have a table with bytea column and trying to load the data using copy 
> command.
> But Copy command is failing with
> "ERROR: invalid byte sequence for encoding UTF8: 0x00.
> Why postgresql is failing to load when data contains 0x00.
> How to resolve this error ? any workaround to load the data with 0x00 values ?

This is not about loading data into a "bytea", but into a "text", "varchar" or
"char" column.  Check again.

Yours,
Laurenz Albe

Re: postgresql custom variable in pg_settings table

[jian he]

On Tue, Jan 9, 2024 at 3:38 PM Yi Sun  wrote:
>
> Hello,
>
>
>
> But can not select it from pg_settings, as we use pgwatch2 to monitor, to 
> avoid pgwatch2 script change prefer to use pg_setting not current_setting() 
> function, is it possible to get the custom variable from pg_setting please? 
> Thanks

the following minimum c function doing that.
you can use LOAD (https://www.postgresql.org/docs/16/sql-load.html) to
load it, which will make it session persistent.
to make it always persistent, you need to make it in the
shared_preload_libraries.
of course, you can define more Custom Variables.

#include "postgres.h"
#include "funcapi.h"
#include "fmgr.h"
#include "utils/guc.h"
PG_MODULE_MAGIC;
static int test_guc = 2;
void
_PG_init(void)
{
/* Define custom GUC variables. */
DefineCustomIntVariable("patroni.count",
"test .",
"Valid range is 1 .. 11.",
_guc,
2,
1,
11,
PGC_USERSET,
0,
NULL,
NULL,
NULL);
MarkGUCPrefixReserved("patroni");
}

ERROR: invalid byte sequence for encoding UTF8: 0x00

[Kiran K V]

Hi,

I have a table with bytea column and trying to load the data using copy
command.  But Copy command is failing with "ERROR: invalid byte sequence
for encoding UTF8: 0x00. Why postgresql is failing to load when data
contains 0x00. How to resolve this error ? any workaround to load the data
with 0x00 values ?

Please advise. Thank you very much.

Regards,
Kiran

Order of multicolumn gist index

[Paul van der Linden]

I have the following query:
SELECT *
FROM polygons
WHERE zoomlevel <= {zoom} AND st_intersects(way,{tileboundary})
For any given tile according to the openstreetmap tiles.
So zoomlevel is from 0..14 and the number of polygons in each level is roughly 
exponential.

Postgres doc (https://www.postgresql.org/docs/current/indexes-multicolumn.html) 
states that
"A GiST index will be relatively ineffective if its first column has only a few 
distinct values, even if there are many distinct values in additional columns."

So I am trying to figure out the difference between the column order in the 
index.
Created following indices:
CREATE INDEX polygon_minzoom_geo ON polygons USING gist (minzoom,way)
CREATE INDEX polygon_minzoom_geo2 ON polygons USING gist (way,minzoom)
And did a test for a zoom=9 tile:
SELECT *
FROM polygons
WHERE zoomlevel <= 9 AND st_intersects(way,'SRID=3857;POLYGON((547900 
6653078,547900 6574807,626172 6574807,626172 6653078,547900 6653078))')

After running that query with either one of the indices disabled (BEGIN; DROP 
INDEX etc) I get the following explain results:
Using (minzoom,way):

Result  (cost=0.42..228992.47 rows=229000 width=113) (actual 
time=149.483..1471.819 rows=42463 loops=1)
  Buffers: shared hit=352653
  ->  ProjectSet  (cost=0.42..4572.47 rows=229000 width=88) (actual 
time=149.461..1234.048 rows=42463 loops=1)
Buffers: shared hit=274281
->  Index Scan using polygon_minzoom_geo on polygons  
(cost=0.42..3283.20 rows=229 width=264) (actual time=149.409..955.849 
rows=42463 loops=1)
  Index Cond: ((minzoom <= 9) AND (way && 
'010320110F01000500BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry))
  Filter: ((minzoom <= 9) AND (way && 
'010320110F01000500BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry))
  Rows Removed by Filter: 345
  Buffers: shared hit=274025
Planning Time: 7.220 ms
Execution Time: 1494.267 ms


Using (way,minzoom):

Result  (cost=0.42..228992.47 rows=229000 width=113) (actual 
time=178.747..1715.135 rows=42463 loops=1)
  Buffers: shared hit=350570
  ->  ProjectSet  (cost=0.42..4572.47 rows=229000 width=88) (actual 
time=178.731..1436.764 rows=42463 loops=1)
Buffers: shared hit=272198
->  Index Scan using polygon_minzoom_geo2 on polygons  
(cost=0.42..3283.20 rows=229 width=264) (actual time=178.683..1118.691 
rows=42463 loops=1)
  Index Cond: ((way && 
'010320110F01000500BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)
 AND (minzoom <= 9))
  Filter: ((way && 
'010320110F01000500BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)
 AND (minzoom <= 9))
  Rows Removed by Filter: 345
  Buffers: shared hit=271942
Planning Time: 9.427 ms
Execution Time: 1742.729 ms

So all in all not really a big difference.
Is this situation somehow special and thus the remark in the documentation not 
applicable here, or am I missing something in the analysis that would show up 
the difference?

Paul

P.S. when replying, please include me too

Re: postgresql custom variable in pg_settings table

[Achilleas Mantzios - cloud]

On 1/9/24 09:38, Yi Sun wrote:


Hello,

We custom set variable

Added patroni.nodes_count = 2 in postgresql.conf

postgres=# show patroni.nodes_count;
 patroni.nodes_count
-
 2
(1 row)

postgres=# select current_setting('patroni.nodes_count');
 current_setting
-
 2
(1 row)

But can not select it from pg_settings, as we use pgwatch2 to monitor, 
to avoid pgwatch2 script change prefer to use pg_setting not 
current_setting() function, is it possible to get the custom variable 
from pg_setting please? Thanks


You may look into pg_file_settings :

select setting from pg_file_settings  where name = 'patroni.nodes_count';



Best Regards
SY