RE: Strange error in Windows 10 Pro

[Igor Neyman]

On system hard drive search for the file called 
bitrock_installer_.log.
You should find it in Users\\AppData\Local\Temp.
This log should provide you with more specific details explaining why data 
folder is empty.

Regards,
Igor

From: Dale Seaburg [mailto:kg...@verizon.net]
Sent: Saturday, April 21, 2018 4:09 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Strange error in Windows 10 Pro


WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.

Thanks Adrian for the suggestion of running the installer with Admin rights.  
Unfortunately, I get the same results.  It appears that all of the folders 
within C:\Program Files\PostgreSQL\9.6 path are created, and populated, BUT, 
when the items in the data folder are to be created, or copied into, it leaves 
an error message as noted previously.  The data folder is empty.

It's almost as if the PC is missing a critical .dll needed in the data folder 
filling function (my guess).

Again, I am at a loss as to what to do.

Dale

On 4/20/2018 11:13 PM, Dale Seaburg wrote:
Oops, my mistake.  I'll let this serve the list with what I've tried so far.  
Thanks, Adrian for the reminder.

I hope tomorrow to visit the customer and try the Admin user method of 
installing.

Dale


On 4/20/2018 11:03 PM, Adrian Klaver wrote:

On 04/20/2018 07:52 PM, Dale Seaburg wrote:

Please also reply to list.
Ccing list to put it front of more eyes.


Thanks, Adrian, for suggestion(s).


On 4/20/2018 9:35 PM, Adrian Klaver wrote:

On 04/20/2018 07:16 PM, Dale Seaburg wrote:

I am attempting to install a fresh copy of postgresql-9.6.8-2-windows-x86 on a 
new DELL PC with Windows 10 Pro.  It

This was downloaded from where?
downloaded from https://www.postgresql.org/download/windows/, selecting to use 
the installer pointed to near the beginning of that page.  The actual website 
that contained the installer file was: 
"https://www.enterprisedb.com/thank-you-downloading-postgresql?anid=209611";.



gets near the end of the install when the message says it is attempting to 
start the the database server.   There's a long pause, followed by an error 
message: "Failed to load SQL modules into the database cluster". Using File 
Explorer, i notice the 9.6\base\ folder is empty?  Has anyone else seen this 
before?  I have no clue where to look for the issue.

You are running as Admin user?
I can't say that I was.  Will check this next time (maybe tomorrow) when I am 
customer's site.  I didn't even think about right-clicking on the installer and 
selecting run-as-admin.  I know I did not deliberately use Admin user on the 
test PC mentioned below, and it installed with no problems.





As a double-check on a different PC with Windows 10 Pro, I get no error 
message, and the database is installed correctly.

Dale Seaburg

RE: Strange error in Windows 10 Pro

[Igor Neyman]

From: Dale Seaburg [mailto:kg...@verizon.net]
Sent: Monday, April 23, 2018 10:10 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Strange error in Windows 10 Pro

Thanks to Moreno and Igor for the Event Viewer suggestions.  Here are a few 
lines of log file where they differ between a good install and a bad incomplete 
install.  The good install was from my Shop PC with Windows 10 Pro.

BTW, I had success with another Dell PC with Win 10 Pro about 4 months ago.  
This problem appears to be a one-off.
Good Install:
Called AclCheck(C:\Program Files\PostgreSQL\9.6\data)
Called IsVistaOrNewer()...
'winmgmts' object initialized...
Version:10.
MajorVersion:10
Executing icacls to ensure the SHOP-PC\Dale account can read the path 
C:\Program Files\PostgreSQL\9.6\data
Executing batch file 'rad0510A.bat'...
processed file: C:\Program Files\PostgreSQL\9.6\data
Successfully processed 1 files; Failed processing 0 files

Bad Install:
Called AclCheck(D:\PostgreSQL\9.6\data)
Called IsVistaOrNewer()...
'winmgmts' object initialized...
Version:10.
MajorVersion:10
Executing icacls to ensure the WINDOWS-6BEGVO1\don king account can read the 
path D:\PostgreSQL\9.6\data
Executing batch file 'rad6DBC7.bat'...

Notice the last four lines of the Good Install vs the last two lines of the Bad 
Install.  There is no indication of processing, or whether it was successful or 
not in the bad install.

As you can see, I even took Igor's suggestion to install in a non-system 
(Program Files) path, to no success.

Again, not sure what to do.  Open for suggestions...  I'm almost ready to call 
Dell and complain about a "bad" Win 10 Pro install.

Dale

Dale,
The problem isn’t in “bad” Win10 Pro install.
The problem is that Win10 on this machine is  configured differently from Win10 
machine where your install worked fine.
In Windows there is a “default” association between file types/extensions and 
programs that execute these files by default.  Batch files like 'rad6DBC7.bat'  
 shouldn’t have any association, Win OS knows that this type should be executed 
by OS, like .exe types.  But sometimes Windows users change it, and associate 
.bat files with Notepad (or some other text editor) in order to be able to edit 
batch scripts. By the way, batch file names are different in “good” and “bad” 
installations because those are temporary files created by vbs scripts that are 
running during install.
So, in order to fix this you need to dissociate .bat type/extension from 
Notepad, or whatever text editor it is associated on the “bad” Windows. If you 
don’t know where to look for this feature in Win OS, ask someone who’s more 
familiar with windows environment.
Pretty sure that’s the solution of your problem.
Regards,
Igor Neyman

RE: Insert data if it is not existing

[Igor Neyman]

From: tango ward [mailto:tangowar...@gmail.com]
Sent: Thursday, May 24, 2018 8:16 PM
To: Adrian Klaver 
Cc: David G. Johnston ; 
pgsql-generallists.postgresql.org 
Subject: Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:38 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:
On 05/23/2018 06:03 PM, tango ward wrote:


Updated my code to this:

curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))

If I remove SELECT statement, I will get an error message: error : 
psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 12: WHERE NOT EXISTS

Trying to coordinate with Lead Dev about adding Index On The Fly

I tried to figure how to make this work and could not, so I led you down a 
false path.




--
Adrian Klaver
adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>

What I tried is
'''INSERT INTO my_table(name, age)
SELECT %s, %s,
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', 23, 
'Scott'))

How about:

'''INSERT INTO my_table(name, age)
VALUES( %s, %s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', 23, 
'Scott'))

Regards,
Igor Neyman

RE: Question on disk contention

[Igor Neyman]

> Why isn't the OS caching the disk blocks, and why isn't Postgres using the 
> cached data?
It does, but the cache is for each connection/job. They are not shared.
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!



That is simply not true: shared_buffers have this name for a reason. What’s not 
shared is work_mem used for sorting, etc…
Also OS cache is shared too.

Regards,
Igor Neyman

RE: Monitor repl slot size

[Igor Neyman]

From: Nicola Contu [mailto:nicola.co...@gmail.com]
Sent: Friday, July 13, 2018 6:19 AM
To: pgsql-general@lists.postgresql.org
Cc: Alessandro Aste 
Subject: Monitor repl slot size

Hello,
we used to monitor the replication slot size on postgres 9.6.6 with the 
following query:

SELECT pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn) FROM 
pg_replication_slots WHERE slot_name = 'SLOT NAME';

We are moving to postgres 10.4 and we saw the pg_xlog_location_diff is not 
there anymore.


I know we can re-create it following this link : 
https://github.com/DataDog/integrations-core/issues/907


but, is there any better way to do it? Any replacement for that function on 
postgres 10?


Thanks a lot,
Nicola

I’m compare current_wal_lsn to confirmed_flush_lsn:

SELECT confirmed_flush_lsn, pg_current_wal_lsn(), (pg_current_wal_lsn() - 
confirmed_flush_lsn) AS lsn_distance  -- returned as NUMERIC
   FROM pg_catalog.pg_replication_slots
   WHERE slot_name = 'Slot NAME';

Regards,
Igor Neyman

RE: extracting the sql for a function

[Igor Neyman]

-Original Message-
From: Paul Tilles [mailto:paul.til...@noaa.gov] 
Sent: Thursday, August 23, 2018 1:18 PM
To: pgsql-gene...@postgresql.org
Subject: extracting the sql for a function

WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.

Using postgres Version 9.5

I can extract the sql for a table using

pg_dump  -d database_name  -s  -t  table_name  -f  table_name.sql

Is there something equivalent for extracting the sql for a function from the 
database?


Paul Tilles

__

select prosrc from pg_proc where proname = 'function_name';

You can pronamespace to WHERE clause to specify schema.

Regards,
Igor Neyman

RE: Regrading brin_index on required column of the table

[Igor Neyman]

From: Durgamahesh Manne [mailto:maheshpostgr...@gmail.com]
Sent: Wednesday, September 19, 2018 9:43 AM
To: PostgreSQL mailing lists 
Subject: Regrading brin_index on required column of the table


WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.
Hi
Respected postgres community members

I have created BRIN index on few columns of the table without any issues. But i 
am unable to create BRIN index on one column of the table as i got error listed 
below


[local]:6263 postgres@huawei=# CREATE INDEX brin_idx on huawei using brin 
("dFetch");
ERROR:  data type boolean has no default operator class for access method "brin"
HINT:  You must specify an operator class for the index or define a default 
operator class for the data type.

 below is the column description:
Column datatype   collationnullable   defaultstorage

 dFetchboolean false
plain



so please help in creating of the BRIN index on above column of the table .



Regards

Durgamahesh Manne

Why would you want BRIN index on Boolean-type column?
What kind of interval will you specify?

Regards,
Igor Neyman

RE: Variable constants ?

[Igor Neyman]

-Original Message-
From: Tom Lane  
Sent: Thursday, August 15, 2019 6:13 PM
To: stan 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Variable constants ?

stan  writes:
> Failing a better way is there some way I can limit this table to only 
> allow one row to exist?

I was recently reminded of a cute trick for that: make a unique index on a 
constant.

regression=# create table consts(f1 int, f2 int); CREATE TABLE regression=# 
create unique index consts_only_one on consts((1)); CREATE INDEX regression=# 
insert into consts values(1,2); INSERT 0 1 regression=# insert into consts 
values(3,4);
ERROR:  duplicate key value violates unique constraint "consts_only_one"
DETAIL:  Key ((1))=(1) already exists.

Shepard's nearby advice about keeping some history is probably a better plan 
though.

regards, tom lane


Here is another trick to keep a table to just one row:

reg#create table test(c1 int check(c1=1) default 1, c2 int);
CREATE TABLE
reg#create unique index one_row on test(c1);
CREATE INDEX
reg#insert into test (c2) values(3);
INSERT 01
reg# insert into test (c2) values(4);
ERROR:  duplicate key value violates unique constraint "one_row"
DETAIL:  Key (c1)=(1) already exists.
SQL state: 23505

Regards,
Igor Neyman

PG12

[Igor Neyman]

Main page (https://www.postgresql.org/) announces new release, but Downloads 
for Windows page (https://www.postgresql.org/download/windows/) doesn't list 
PG12.
Any clarification?

Regards,
Igor Neyman

RE: PG12

[Igor Neyman]

From: Igor Neyman [mailto:iney...@perceptron.com]
Sent: Thursday, October 03, 2019 12:27 PM
To: pgsql-general@lists.postgresql.org
Subject: PG12

Main page (https://www.postgresql.org/) announces new release, but Downloads 
for Windows page (https://www.postgresql.org/download/windows/) doesn't list 
PG12.
Any clarification?

Regards,
Igor Neyman

Ok, sorry for noise. EnterpriseDB has it.

Igor Neyman

RE: Weird seqscan node plan

[Igor Neyman]

From: Игорь Выскорко [mailto:vyskorko.i...@yandex.ru]
Sent: Tuesday, November 26, 2019 4:13 AM
To: Andrei Zhidenkov 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Weird seqscan node plan

26.11.2019, 16:02, "Andrei Zhidenkov" 
mailto:andrei.zhiden...@n26.com>>:
How many tables do you have in your query? If too many, in your case “Genetic 
Query Optiomiation” might be used 
(https://www.postgresql.org/docs/10/geqo-pg-intro.html).

On 26. Nov 2019, at 03:19, Игорь Выскорко 
mailto:vyskorko.i...@yandex.ru>> wrote:

Hi all!
I'm confused by planner choice: seqscan but not index scan when index scan cost 
is much cheaper.
1st plan: https://explain.depesz.com/s/Cti#l8
2nd plan (with "set enable_seqscan = off"): https://explain.depesz.com/s/qn0I#l8

Look at 8th row: this nested loop decided to join the second node by using 
seqscan (1st plan) when index is available (2nd plan). Index scan is much 
cheaper (0.430 over 257.760).

What am I missing?

And thanks for any reply!


Hm... about 12 tables.
I tried to disable geqo (set geqo = off;)  plan didn't change. But thanks for 
your try )

Version I'm using (if matter):
select version();
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 
4.9.2, 64-bit

Try increasing the following parameters to 14 (or even 16, if you are not sure 
about number of tables involved):

geqo_threshold = 14
from_collapse_limit = 14
join_collapse_limit = 14

“about 12” is too close to default limit, 12.

Regards,
Igor Neyman

RE: calculating the MD5 hash of role passwords in C

[Igor Neyman]

-Original Message-
From: Matthias Apitz [mailto:g...@unixarea.de] 
Sent: Wednesday, January 22, 2020 2:41 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: calculating the MD5 hash of role passwords in C



.


--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 
Public GnuPG key: http://www.unixarea.de/key.pub

Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland!
Germany out of NATO! NATO out of Germany! Peace with Russia!
¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!

_

I don't think that political slogans in your signature are appropriate for this 
forum.

RE: calculating the MD5 hash of role passwords in C

[Igor Neyman]

-Original Message-
From: Matthias Apitz [mailto:g...@unixarea.de] 
Sent: Wednesday, January 22, 2020 3:05 PM
To: Igor Neyman 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: calculating the MD5 hash of role passwords in C
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ 
> +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
>
> Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit 
> Russland!
> Germany out of NATO! NATO out of Germany! Peace with Russia!
> ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
>
> __
> ___
>
> I don't think that political slogans in your signature are appropriate for 
> this forum.
>

This is technical just a signature and normally I delete it when posting to 
groups, I forgot it in this case.

Said that, in any case, you are free to "think" whatever you want, as I am free 
to write whatever I think. And you are free to just ignore it.

matthias

_

So, I'm free to "think", but you are free to write. Interesting distinction...

Igor N.

RE: How to retrieve the partition info for each partition table?

[Igor Neyman]

From: Yuxia Qiu [mailto:yuxiaq...@gmail.com]
Sent: Wednesday, October 24, 2018 1:29 PM
To: pgsql-general@lists.postgresql.org
Subject: How to retrieve the partition info for each partition table?

HI,

   I have created some partition table, as example bellow:
CREATE TABLE public.measurement
(
city_id integer NOT NULL,
logdate date NOT NULL,
peaktemp integer,
unitsales integer
) PARTITION BY RANGE (logdate) ;

CREATE TABLE public.measurement_y2006m02 PARTITION OF public.measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE public.measurement_y2006m03 PARTITION OF public.measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

I know how to get a list partition tables for above example by calling :
SELECT i.inhrelid::regclass AS child
FROM   pg_inherits i
WHERE  i.inhparent = 'public.measurement'::regclass;


My question is: how to get the value boundary for each partition table like 
bellow?
measurement_y2006m02   FROM ('2006-02-01') TO ('2006-03-01')

measurement_y2006m03   FROM ('2006-03-01') TO ('2006-04-01')



Thanks,
Yuxia

Take a look at relpartbound in pg_class.

Regards,
Igor Neyman

RE: How to retrieve the partition info for each partition table?

[Igor Neyman]

From: Yuxia Qiu [mailto:yuxiaq...@gmail.com]
Sent: Wednesday, October 24, 2018 1:29 PM
To: 
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Subject: How to retrieve the partition info for each partition table?

HI,

   I have created some partition table, as example bellow:
CREATE TABLE public.measurement
(
city_id integer NOT NULL,
logdate date NOT NULL,
peaktemp integer,
unitsales integer
) PARTITION BY RANGE (logdate) ;

CREATE TABLE public.measurement_y2006m02 PARTITION OF public.measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE public.measurement_y2006m03 PARTITION OF public.measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

I know how to get a list partition tables for above example by calling :
SELECT i.inhrelid::regclass AS child
FROM   pg_inherits i
WHERE  i.inhparent = 'public.measurement'::regclass;

My question is: how to get the value boundary for each partition table like 
bellow?
measurement_y2006m02   FROM ('2006-02-01') TO ('2006-03-01')
measurement_y2006m03   FROM ('2006-03-01') TO ('2006-04-01')

Thanks,
Yuxia

Better yet:

SELECT relname, pg_get_expr(relpartbound, oid) FROM pg_class WHERE relpartbound 
IS NOT NULL;

Regards,
Igor Neyman

RE: GIN Index for low cardinality

[Igor Neyman]

From: Ozz Nixon 
Sent: Friday, October 26, 2018 12:50 PM
To: jeff.ja...@gmail.com
Cc: spl...@ya.ru; srkrish...@aol.com; pgsql-general@lists.postgresql.org
Subject: Re: GIN Index for low cardinality

Jeff,
   Great info! Your example on Mr. Mrs. Miss, etc. is there a good rule of 
thumb that if the data is under "x"KB an index is overhead not help? I am not 
worried about space, more interested in performance.

I think, partial/conditional indexes: …. Where greeting = ‘…’ – may help.

Regards,
Igor Neyman

RE: BDR and PostgreSQL 12 and Windows support

[Igor Neyman]

-Original Message-
From: Ravi Krishna [mailto:srkrish...@icloud.com] 
Sent: Friday, November 16, 2018 9:28 AM
To: Thomas Kellerer 
Cc: pgsql-general 
Subject: Re: BDR and PostgreSQL 12 and Windows support

WARNING: This email originated from outside of Perceptron! Please be mindful of 
PHISHING and MALWARE risks.

>
> Andrew Smith schrieb am 16.11.2018 um 11:01:
>> Are there any core features at the
>> moment that are Linux only?
>
> JIT, introduced in Postgres 11, comes to mind
>
A better question should be, are there any production users of PG on Windows :-)
__

Not funny for those who HAS TO run PG on Windows.
Let's not start "OS wars" here. Windows is one of the platforms Postgres 
supports.

RE: How to watch for schema changes

[Igor Neyman]

-Original Message-
From: Igor Korot [mailto:ikoro...@gmail.com] 
Sent: Monday, December 03, 2018 8:29 AM
To: Adrian Klaver 
Cc: pgsql-general 
Subject: Re: How to watch for schema changes

...

And executing LISTEN will also work for ODBC connection, right?

Thank you.

___

It's been years since we dealt with this problem, so the details are fuzzy.

All applications in the package we develop connect to PG using ODBC, but one 
app that's using LISTEN is connecting to PG through native interface libpq.dll, 
ODBC didn't work for that purpose, at least at the time.

Regards,
Igor Neyman

RE: How to watch for schema changes

[Igor Neyman]

-Original Message-
From: Igor Korot [mailto:ikoro...@gmail.com] 
Sent: Tuesday, December 04, 2018 11:07 AM
To: Igor Neyman 
Cc: Adrian Klaver ; pgsql-general 

Subject: Re: How to watch for schema changes

Igor,

On Tue, Dec 4, 2018 at 8:20 AM Igor Neyman  wrote:
>
>
> -Original Message-
> From: Igor Korot [mailto:ikoro...@gmail.com]
> Sent: Monday, December 03, 2018 8:29 AM
> To: Adrian Klaver 
> Cc: pgsql-general 
> Subject: Re: How to watch for schema changes
>
> ...
>
> And executing LISTEN will also work for ODBC connection, right?
>
> Thank you.
>
> ___
>
> It's been years since we dealt with this problem, so the details are fuzzy.
>
> All applications in the package we develop connect to PG using ODBC, but one 
> app that's using LISTEN is connecting to PG through native interface 
> libpq.dll, ODBC didn't work for that purpose, at least at the time.

I will try it and report back.
Out of curiosity - when was the last time you tried?

Thank you.
_

PG release 8.4.

RE: simple division

[Igor Neyman]

From: Martin Mueller 
Sent: Tuesday, December 4, 2018 3:30 PM
To: pgsql-general 
Subject: simple division

I have asked this question before and apologize for not remembering it.  How do 
you do simple division in postgres and get 10/4 with decimals?

This involves cast and numeric in odd ways that are not well explained in the 
documentation. For instance, you’d expect an example in the Mathematical 
Functions. But there isn’t.

The documentation of string functions is exemplary. The documentation of 
mathematical less so. Remember that it may be used by folks like me whose math 
is shaky. The MySQL documentation is better on this simple operation.


-
Martin Mueller
Professor emeritus of English and Classics
Northwestern University

There is nothing odd about:

select (12345678.1234/32.5678)::numeric(10,4);

Regards,
Igor Neyman

RE: Can anyone please provide me list of customers using postgreSQL

[Igor Neyman]

From: Ramamoorthi, Meenakshi [mailto:meenakshi.ramamoor...@cpuc.ca.gov]
Sent: Tuesday, January 15, 2019 2:42 PM
To: pgsql-general 
Subject: Can anyone please provide me list of customers using postgreSQL

Dear folks:


1)  Can someone please send me a link of all companies using PostgreSQL ?

2)  Both government and private companies using PostgreSQL

3)  Any security issues found earlier and the steps taken for resolution or 
how it was mitigated.

4)  Advantages of PostgreSQL compared to other databases like Oracle, MySQL 
etc.


Thanks and best regards
Meenakshi Ramamoorthi


Some are listed here:

https://stackshare.io/postgresql/in-stacks

Or, just do google search.

Regards,
Igor Neyman

RE: How to set parameters in 'options'?

[Igor Neyman]

-Original Message-
From: Stefan Keller [mailto:sfkel...@gmail.com] 
Sent: Tuesday, January 29, 2019 9:40 AM
To: Postgres General 
Subject: How to set parameters in 'options'?

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text) returns void as $$
  create server _server
foreign data wrapper postgres_fdw
options (host _host, port _port, dbname _dbname);
-- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?

:Stefan

P.S. Actually I'll put this code in a plggsql function later on.
__

Use dynamic sql:
https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards,
Igor Neyman

RE: Connection refused (0x0000274D/10061)

[Igor Neyman]

From: Sourav Majumdar [mailto:souravmajumdar2...@gmail.com]
Sent: Tuesday, June 18, 2019 4:06 AM
To: Adrian Klaver 
Cc: Ray O'Donnell ; pgsql-gene...@postgresql.org
Subject: Re: Connection refused (0x274D/10061)

Hii, I have checked the logged file. I am attaching it with this mail. PFA

On Fri, Jun 14, 2019 at 1:58 AM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:
On 6/13/19 9:56 AM, Sourav Majumdar wrote:
> Hi,
> I have tried many time for setup postgreSQL for my local host. Hence I
> have tried to reInstall the one click app from
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads -  
> Windows
> x86-64  -  PostgreSQL Version 11.3. When installing using default port
> 5432, Its giving an error "Database Cluster Installation Failed". Please
> advice how to resolve the issue. I am struggling to setup this
> POSTGRESQL from more than a week.
>
> The error screenshot is attached. PFA
>

Sounds like a permissions problem to me.

See:

https://www.enterprisedb.com/docs/en/11.0/PG_Inst_Guide_v11/PostgreSQL_Installation_Guide.1.13.html#<https://www.enterprisedb.com/docs/en/11.0/PG_Inst_Guide_v11/PostgreSQL_Installation_Guide.1.13.html>

to find the log of the install process. It will probably have more
information.


--
Adrian Klaver
adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>

_

From the log that you attached:

Failed to remove inherited ACLs on (C:\PostgreSQL\data)
WScript.Network initialized...
strParentOfDataDirC:\PostgreSQL
logged in userMajumdar\Its Majumdar
Called AclCheck(C:\PostgreSQL\data)
Called IsVistaOrNewer()...
'winmgmts' object initialized...
Version:6.3
MajorVersion:6
Executing icacls to ensure the Majumdar\Its Majumdar account can read the path 
C:\PostgreSQL\data
Executing batch file 'rad3A6BD.bat'...
'icacls' is not recognized as an internal or external command,
operable program or batch file.


Installation script doesn’t find on your machine ICACLS, Windows utility that 
is being used by installation script to grant proper permissions.
Something’s wrong with your Windows OS.

Regards,
Igor Neyman

RE: Updating a pre-10 partitioned table to use PG 10 partitioning

[Igor Neyman]

-Original Message-
From: Alban Hertroys [mailto:haram...@gmail.com]
Sent: Thursday, January 11, 2018 10:41 AM
To: Postgres General 
Subject: Updating a pre-10 partitioned table to use PG 10 partitioning

I'm trying to update an existing table that was created in PG9.6 using the old 
approach with manual inheritance and check constraints to make use of the new 
approach in PG 10 using 'partitioned by', 'attach partition', etc.

I ran into some how-to's on the internet, but they all assume you start 
partitioning from a new table instead of modifying an existing table into 
partitions. It seems to me that would be a fairly common requirement, no?

I realise that I skip over the fact that I'm not attempting to partition an 
unpartitioned table, but instead attempt to update the existing partitioning to 
the new approach.

Currently the table in question really only has 1 partition filtered on a type 
column (we plan to create 1 partition per type), but this table is already 
1.5GB on disk and has a number of indexes and views associated to it. Creating 
a new table out of it and recreating all the related stuff seems a bit 
roundabout...

It would be nice if I could update the table to use PG 10 partitioning using 
just a simple alter table, but I can't seem to find the right syntax.

What is a good approach here?

__

Not in one step, but it's doable.
Here is what I do when switching from inheritance to declarative partitioning.

1. Alter all partitions to "NO INHERIT:

 ALTER  NO INHERIT 

2. Create new partitioned table  just like old "parent" table with different 
name using "PARTITION BY ..." clause.

3. Drop old "parent" table.

4. Rename new partitioned table giving it .

5. Finally, "old" partitions to new partitioned table (previously "parent"):

  ALTER TABLE < parent_table_name > ATTACHE PARTITION 
 FOR VALUES ...


Regards,
Igor Neyman

RE: Dynamic PL/pgSQL select query: value association propblem

[Igor Neyman]

Hi all

I would like to have a generic trigger function that compares on insert if 
there is already a record in the table with the very same values. Using 
PL/pgSQL ( I am not bound to that) I know the insert record structure from the 
new record and I can build a select query dynamically from the catalogue, but I 
do not know to associate the new record values to the corresponding columns. An 
example Table T has columns Q and L, in that order. If I create an insert 
trigger function I have the new values in new.L and new.Q. From the catalogue I 
can create a the select query S_QUERY:
select count(*) > 0 from T where A = $1 and B = $2. But when I want to EXECUTE 
S_QUERY USING it fails because I cannot use something like NEW[1].

Is there a way to convert the record type into an array type? Or is there even 
a way to do it more directly like WHERE T.RECORD = NEW?

Kind regards Thiemo

--
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF



Why not let the database do this job for you?
Just create a proper UNIQUE constraint on the columns you are interested in, 
and catch a "duplicate key" exception in your app.

Regards,
Igor Neyman