date:20210603

=?iso-2022-jp?B?GyRCNVdMZyEhMCYbKEI=?=  writes:
> I would like to set my own character code conversion as DEFAULT CONVERSION.

That's not an area of functionality that ever got fleshed out completely.
What you're thinking about sounds like it'd work, but I'd strongly
suggest experimenting in a scratch database before doing it with
irreplaceable data.

Keep in mind also that pg_dump/pg_upgrade will absolutely not
reproduce this configuration for you.

regards, tom lane

Re: Noinheritance with superuser

saket bansal  writes:
> I have an RDS instance with the below configuration.
> create role role1 login noinheritance;
> grant rds_superuser to rdsdba;
> grant rdsdba to role1

> In this case role1 is able to do all administrative operations, without the
> need of switching to rdsdba.
> Is this expected?

rds_superuser is not a thing that exists in community Postgres.
I'd suggest taking this question up with the Amazon RDS folk.

regards, tom lane

Please tell me about character code conversion.

2021-06-03 Thread 久門　愛

Hi,
Please tell me about character code conversion.
I am currently using Postgres 13.3.
I would like to set my own character code conversion as DEFAULT CONVERSION.
Since there is a CONVERSION provided by Postgres by default, the following
error will occur when executing CREATE CONVERSION.
-
postgres=# CREATE DEFAULT CONVERSION pg_catalog.myconv_sjis_to_utf8 FOR
'SJIS' TO 'UTF8' FROM myconv_sjis_to_utf8;
ERROR:  default conversion for SJIS to UTF8 already exists 
postgres=#
-

As a method to change the conversion provided by default, execute the
following SQL statement and after executing CREATE CONVERSION
I am trying to update the system catalog with an UPDATE statement. Is there
any problem with this method?
-
CREATE CONVERSION pg_catalog.myconv_sjis_to_utf8 FOR 'SJIS' TO 'UTF8' FROM
myconv_sjis_to_utf8; 
UPDATE pg_conversion SET condefault='f' WHERE conname='sjis_to_utf8'; 
UPDATE pg_conversion SET condefault='t' WHERE conname='myconv_sjis_to_utf8';
-

Thanks
Ai

Noinheritance with superuser

2021-06-03 Thread saket bansal

I have an RDS instance with the below configuration.

create role role1 login noinheritance;
grant rds_superuser to rdsdba;
grant rdsdba to role1

In this case role1 is able to do all administrative operations, without the
need of switching to rdsdba.
Is this expected?
I do not find any document which says that inheritance does not work with
roles which have superuser role granted to it. Note that rdsdba is not a
superuser in itself, but only has a role which is superuser.

-- 

Thanks in advance.

Re: Dropping dependent tables

2021-06-03 Thread Tiffany Thang

On Thu, Jun 3, 2021 at 10:18 AM Tom Lane  wrote:

> Tiffany Thang  writes:
> > I would like to write a SQL statement to drop all the tables owned by me
> > but a problem I’m struggling with is with referential integrity. The
> > statement I have now to generate the drop statements is
>
> > select 'drop table '||tablename||' cascade;' from pg_tables where
> > tableowner='';
>
> > The generated SQLs above might attempt to drop the parent tables first
> > before the child and to be able to drop all the tables, I had to run the
> > SQL script in multiple iterations. Not very clean.
>
> Uh ... it's not clear to me why that wouldn't work.  CASCADE should
> be able to take care of foreign keys:
>
> postgres=# create table t1 (f1 int primary key);
> CREATE TABLE
> postgres=# create table t2 (f2 int references t1);
> CREATE TABLE
> postgres=# drop table t1;
> ERROR:  cannot drop table t1 because other objects depend on it
> DETAIL:  constraint t2_f2_fkey on table t2 depends on table t1
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
> postgres=# drop table t1 cascade;
> NOTICE:  drop cascades to constraint t2_f2_fkey on table t2
> DROP TABLE
>
> Could you enlarge on what problem you saw, specifically?
>
> regards, tom lane
>

Thanks Tom for your response. I tried it again and I was not able to
reproduce the issue.

Obviously I did something incorrectly previously.

Thanks.

Tiff

Re: possible license violations

On Thu, Jun  3, 2021 at 06:08:42PM -0400, Tom Lane wrote:
> Bruce Momjian  writes:
> > On Thu, Jun  3, 2021 at 09:31:15PM +, tom.beacon wrote:
> >> What is the best contact with whom to discuss possible violations of the 
> >> pgsql
> >> license?
> 
> > Uh, good question, and I could not find the answer easily.  I would
> > report it to the owners of the Postgres trademark:
> 
> > https://www.postgresql.org/about/policies/trademarks/
> > bo...@lists.postgres.ca
> 
> A point worth making here is that the Postgres *license* is so lax
> that it's basically impossible to violate, unless maybe by redistributing
> the code sans COPYRIGHT file.  And even if somebody were doing that,
> I doubt how much we'd care.

I have received private reports of our COPYRIGHT not being properly
included in distributions so I am sensitive to those possible
violations, and I assume the trademark holders would deal with those as
well.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

inner subprograms ... Was: syntax question

2021-06-03 Thread Bryn Llewellyn

> br...@momjian.us wrote:
> 
> Oh, I thought he wanted to declare a function inside the function that could 
> be called only by that function, like private functions in Oracle packages 
> can do.  Yes, you can create a function that defines a function that can be 
> called later.  I guess you could also create a function that _conditionally_ 
> creates a function that it can call itself too.  My point is that you can't 
> create a function that has function scope — they all have schema scope.

I’ve heard that EDB’s version of PostgreSQL supports inner subprograms 
(declared and defined within a DECLARE section) to any depth of nesting—and 
packages too. Is this true?

I worked at Oracle HQ for the longest time. Not a day goes by, when I need to 
write PL/pgSQL code, that I don’t miss these two constructs. I wish that (a 
future version of) vanilla PG could bring support for them.

Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

2021-06-03 Thread Bryn Llewellyn

Thanks, as ever, Tom, for the quick response. I’ll summarize your explanation 
as “Yes, there is indeed a logical parsing paradox”. Or, as you said, as “the 
SQL Standard committee—the gift that keeps on giving”.

> Tom wrote:
> 
>> Bryn wrote:
>> 
>> The "at time zone" clause that can decorate a timetsamp[tz] value seems to 
>> allow an argument that’s an arbitrary expression that yields a value whose 
>> data type is "interval".
> 
> AT TIME ZONE is part of the SQL expression syntax, thus it's unsurprising 
> that its arguments can be arbitrary subexpressions.
> 
>> set time zone '-7 hours'::interval;
>> brings a "42601: syntax error".
> 
> The SET statement, on the other hand, most definitely does not accept 
> expressions of any kind; only simple literals.  That's in line with most 
> other utility commands in Postgres, but there are particularly good reasons 
> to be wary of trying to generalize SET.  Cases such as SET TRANSACTION 
> ISOLATION LEVEL have to be able to execute without a transaction context at 
> all.
> 
> Yeah, it's asymmetric :-(.  But the SQL committee has gifted us with a pretty 
> asymmetric language, plus there are various implementation constraints that 
> are not that easy to get rid of.

Re: possible license violations

Bruce Momjian  writes:
> On Thu, Jun  3, 2021 at 09:31:15PM +, tom.beacon wrote:
>> What is the best contact with whom to discuss possible violations of the 
>> pgsql
>> license?

> Uh, good question, and I could not find the answer easily.  I would
> report it to the owners of the Postgres trademark:

>   https://www.postgresql.org/about/policies/trademarks/
>   bo...@lists.postgres.ca

A point worth making here is that the Postgres *license* is so lax
that it's basically impossible to violate, unless maybe by redistributing
the code sans COPYRIGHT file.  And even if somebody were doing that,
I doubt how much we'd care.

We do care more about the Postgres *trademarks*, which is why Bruce
is pointing you to the organization that owns those.  But a trademark
violation is an entirely different animal from a copyright violation.

regards, tom lane

Re: possible license violations

On Thu, Jun  3, 2021 at 09:31:15PM +, tom.beacon wrote:
> What is the best contact with whom to discuss possible violations of the pgsql
> license?

Uh, good question, and I could not find the answer easily.  I would
report it to the owners of the Postgres trademark:

https://www.postgresql.org/about/policies/trademarks/
bo...@lists.postgres.ca

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

Bryn Llewellyn  writes:
> The "at time zone" clause that can decorate a timetsamp[tz] value seems
> to allow an argument that’s an arbitrary expression that yields a value
> whose data type is "interval".

AT TIME ZONE is part of the SQL expression syntax, thus it's unsurprising
that its arguments can be arbitrary subexpressions.

> set time zone '-7 hours'::interval;
> brings a "42601: syntax error".

The SET statement, on the other hand, most definitely does not accept
expressions of any kind; only simple literals.  That's in line with most
other utility commands in Postgres, but there are particularly good
reasons to be wary of trying to generalize SET.  Cases such as
SET TRANSACTION ISOLATION LEVEL have to be able to execute without
a transaction context at all.

Yeah, it's asymmetric :-(.  But the SQL committee has gifted us with
a pretty asymmetric language, plus there are various implementation
constraints that are not that easy to get rid of.

regards, tom lane

Re: arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

On 6/3/21 2:40 PM, Bryn Llewellyn wrote:

The "at time zone" clause that can decorate a timetsamp[tz] value seems to allow an
argument that’s an arbitrary expression that yields a value whose data type is
"interval". Here’s a contrived exotic example:

select '2021-05-21 12:00:00 UTC'::timestamptz at time zone
('2015-05-21 17:00:00'::timestamp - '2015-05-21 17:00:00'::timestamp) +
make_interval(mins=>-30) -
'30 minutes'::interval*2;

It runs without error and gives the answer that I'd expect.

You can also supply a value whose data type is "interval" when you set the session's timezone. But you must
use the special "set time zone" syntax rather than the general "set timezone =" (or "to")
syntax. This works:

set time zone interval '-7 hours';

Moreover, the minus sign has the meaning that ordinary mortals (as opposed to
native POSIX speakers) expect. That's nice. But even this tiny spelling change:

set time zone '-7 hours'::interval;

brings a "42601: syntax error".

The asymmetry harms usability. And it means that careful reference doc ends up
voluminous, tortuous and off-putting. Nobody likes to have to study and remember
whimsical rules that seem to have no logical justification. >

Not sure how the below is any of the above:

https://www.postgresql.org/docs/13/sql-set.html

"TIME ZONE

SET TIME ZONE value is an alias for SET timezone TO value. The
syntax SET TIME ZONE allows special syntax for the time zone
specification. Here are examples of valid values:

'PST8PDT'

The time zone for Berkeley, California.
'Europe/Rome'

The time zone for Italy.
-7

The time zone 7 hours west from UTC (equivalent to PDT).
Positive values are east from UTC.

INTERVAL '-08:00' HOUR TO MINUTE

The time zone 8 hours west from UTC (equivalent to PST).
LOCAL
DEFAULT

Set the time zone to your local time zone (that is, the
server's default value of timezone).

Timezone settings given as numbers or intervals are internally
translated to POSIX timezone syntax. For example, after SET TIME ZONE
-7, SHOW TIME ZONE would report <-07>+07.

See Section 8.5.3 for more information about time zones.
"

Am I failing to see that there's a logical parsing paradox that means that arbitrary "interval"
expressions are acceptable as the argument of "at time zone" but not as the argument of "set
time zone"?

Meanwhile, I'm writing a "set_timezone() procedure" with a "text" overload (that will check against
a list of approved values) and an "interval" overload that will check that the value is in a sensible range*
and generate the acceptable syntax to execute "set time zone" dynamically.

* sensible range for "interval" values from this:

select '~names' as "view", max(utc_offset), min(utc_offset) from
pg_timezone_names
union all
select '~abbrevs' as "view", max(utc_offset), min(utc_offset) from
pg_timezone_abbrevs
order by 1;

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

Re: syntax question

no pb: I am french, so quite skilled on that topic :-)
there is only 50 bottles of various malt on the presentoir close to my desk
so I must stay reasonnable :-)



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 11:17 PM Adrian Klaver 
wrote:

> On 6/3/21 1:01 PM, Marc Millas wrote:
> > thanks Adrian, exactly what I was missing :-)
> >
> > about knowing if I should...
> > We have to create a set of triggers (insert, update, delete) within a
> > huge set of tables. and that list of tables, and structure of them  can
> > be customized, maintained, ...
> > so we were looking for a standard script to automatize the building of
> > the whole thing, taking list of columns  and constraints (for PK)
> > directly from pg_catalog.
> > Now it works :-)
> >
> > but.. why do you ask that question ? is there any king of hidden wolf we
> > didnt see ?
>
> See David Johnston's answer. Nested quoting will drive you to drink(or
> drink more):)
>
>
> >
> >
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com 
> >
> >
> >
> > On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver  > > wrote:
> >
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >  > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >  >> Hi,
> >  >>
> >  >> within a function, I want to create another function.
> >  >> no pb.
> >  >> but if I write:
> >  >> declare bidule text;
> >  >> begin
> >  >> bidule:='myfunc';
> >  >> create function bidule() ...
> >  >>
> >  >>
> >  >> it does create a function named bidule and not myfunc.
> >  >> so I am obviously missing something too obvious.
> >  >
> >  > You can't create functions inside of functions;  same for
> procedures.
> >  >
> >
> > Sure you can:
> >
> > CREATE OR REPLACE FUNCTION public.test_fnc()
> >RETURNS void
> >LANGUAGE plpgsql
> > AS $function$
> > DECLARE
> >bidule text;
> > BEGIN
> > bidule:='myfunc';
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> > END;
> >
> >
> > $function$
> >
> > select test_fnc();
> >test_fnc
> > --
> >
> >\df myfunc
> >   List of functions
> >Schema |  Name  | Result data type | Argument data types | Type
> > ++--+-+--
> >public | myfunc | void | | func
> >
> >
> > Whether you should is another question.
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

arbitrary "interval" expression OK with "at time zone" but not with "set time zone" — Why?

2021-06-03 Thread Bryn Llewellyn

The "at time zone" clause that can decorate a timetsamp[tz] value seems to 
allow an argument that’s an arbitrary expression that yields a value whose data 
type is "interval". Here’s a contrived exotic example:

select '2021-05-21 12:00:00 UTC'::timestamptz at time zone
  ('2015-05-21 17:00:00'::timestamp - '2015-05-21 17:00:00'::timestamp) +
  make_interval(mins=>-30) -
  '30 minutes'::interval*2;

It runs without error and gives the answer that I'd expect.

You can also supply a value whose data type is "interval" when you set the 
session's timezone. But you must use the special "set time zone" syntax rather 
than the general "set timezone =" (or "to") syntax. This works:

set time zone interval '-7 hours';

Moreover, the minus sign has the meaning that ordinary mortals (as opposed to 
native POSIX speakers) expect. That's nice. But even this tiny spelling change:

set time zone '-7 hours'::interval;

brings a "42601: syntax error".

The asymmetry harms usability. And it means that careful reference doc ends up 
voluminous, tortuous and off-putting. Nobody likes to have to study and 
remember whimsical rules that seem to have no logical justification.

Am I failing to see that there's a logical parsing paradox that means that 
arbitrary "interval" expressions are acceptable as the argument of "at time 
zone" but not as the argument of "set time zone"?

Meanwhile, I'm writing a "set_timezone() procedure" with a "text" overload 
(that will check against a list of approved values) and an "interval" overload 
that will check that the value is in a sensible range* and generate the 
acceptable syntax to execute "set time zone" dynamically.


* sensible range for "interval" values from this:

select '~names'   as "view",  max(utc_offset), min(utc_offset) from 
pg_timezone_names
union all
select '~abbrevs' as "view",  max(utc_offset), min(utc_offset) from 
pg_timezone_abbrevs
order by 1;

Re: BUG #17046: Upgrade postgres 11 to 13 version


On 6/3/21 5:53 AM, Ram Pratap Maurya wrote:

Hi Team,

I am creating new server and installed postgres -13 and take PG_BACKUP  form 
current PRD server (PG version 11) and restore DB on PG-13 server.
can you suggest if we doing upgrade by this activity there is any issue.


When doing this you should use the later version(13) of pg_dump to dump 
the earlier version, are you doing that? If you are then you have 
eliminated one possible issue.


You should also read the Release Notes section of version 12 and 13:

https://www.postgresql.org/docs/12/release.html
https://www.postgresql.org/docs/13/release.html

to see what things have changed that may affect your current set up.




Regards,
Ram Pratap.

-Original Message-
From: David Rowley [mailto:dgrowle...@gmail.com]
Sent: 03 June 2021 18:01
To: Ram Pratap Maurya; PostgreSQL mailing lists
Subject: Re: BUG #17046: Upgrade postgres 11 to 13 version

On Fri, 4 Jun 2021 at 00:22, PG Bug reporting form  
wrote:

i am creating new server and installed postgres -13 and take PG_BACKUP
form current PRD server (PG version 11) and restore DB on PG-13
server. can you suggest if we doing upgrade by this activity there is any issue.


The form you've used is for reporting bugs yet, this does not seem to be a bug 
report.

If you're looking for general help and advice then you should consider asking 
on one of the mailing lists.

https://www.postgresql.org/list/

pgsql-general might be the best fit.

David




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

possible license violations

2021-06-03 Thread tom.beacon

What is the best contact with whom to discuss possible violations of the pgsql 
license?

Sent with [ProtonMail](https://protonmail.com) Secure Email.

LZ4 missing in pg14-beta1 Windows build, OLD VS/compiler used

2021-06-03 Thread Hans Buschmann


I tried to test a customer case of using bytea columns with the new lz4 
compression.

But lz4 support is not included in the standard binaries downloadable through 
the PostreSQL Website (from EDB).

For easy testing with windows this should be enabled in the upcoming releases 
of pg14: not everybody is willing or capable of self-compiling a Windows 
distribution…

I also noticed that VS2017 is still used for pg14.

After two years it should be better to distribute the Windows version compiled 
with Visual Studio 2019

Environment:

select version ();
version
---
 PostgreSQL 14beta1, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)

Thanks for investigating

Hans Buschmann

RE: BUG #17046: Upgrade postgres 11 to 13 version

2021-06-03 Thread Ram Pratap Maurya

Hi Team,

I am creating new server and installed postgres -13 and take PG_BACKUP  form 
current PRD server (PG version 11) and restore DB on PG-13 server. 
can you suggest if we doing upgrade by this activity there is any issue.

Regards,
Ram Pratap.

-Original Message-
From: David Rowley [mailto:dgrowle...@gmail.com] 
Sent: 03 June 2021 18:01
To: Ram Pratap Maurya; PostgreSQL mailing lists
Subject: Re: BUG #17046: Upgrade postgres 11 to 13 version

On Fri, 4 Jun 2021 at 00:22, PG Bug reporting form  
wrote:
> i am creating new server and installed postgres -13 and take PG_BACKUP 
> form current PRD server (PG version 11) and restore DB on PG-13 
> server. can you suggest if we doing upgrade by this activity there is any 
> issue.

The form you've used is for reporting bugs yet, this does not seem to be a bug 
report.

If you're looking for general help and advice then you should consider asking 
on one of the mailing lists.

https://www.postgresql.org/list/

pgsql-general might be the best fit.

David

Re: syntax question

On 6/3/21 1:01 PM, Marc Millas wrote:

thanks Adrian, exactly what I was missing :-)

about knowing if I should...
We have to create a set of triggers (insert, update, delete) within a 
huge set of tables. and that list of tables, and structure of them  can 
be customized, maintained, ...
so we were looking for a standard script to automatize the building of 
the whole thing, taking list of columns  and constraints (for PK) 
directly from pg_catalog.

Now it works :-)

but.. why do you ask that question ? is there any king of hidden wolf we 
didnt see ?

See David Johnston's answer. Nested quoting will drive you to drink(or 
drink more):)

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver > wrote:

On 6/3/21 12:01 PM, Bruce Momjian wrote:
 > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
 >> Hi,
 >>
 >> within a function, I want to create another function.
 >> no pb.
 >> but if I write:
 >> declare bidule text;
 >> begin
 >> bidule:='myfunc';
 >> create function bidule() ...
 >>
 >>
 >> it does create a function named bidule and not myfunc.
 >> so I am obviously missing something too obvious.
 >
 > You can't create functions inside of functions;  same for procedures.
 >

Sure you can:

CREATE OR REPLACE FUNCTION public.test_fnc()
   RETURNS void
   LANGUAGE plpgsql
AS $function$
DECLARE
       bidule text;
BEGIN
bidule:='myfunc';
EXECUTE  'create function ' ||  bidule || '() RETURNS void language
plpgsql AS $fnc$ BEGIN END; $fnc$ ';
END;

$function$

select test_fnc();
   test_fnc
--

   \df myfunc
                          List of functions
   Schema |  Name  | Result data type | Argument data types | Type
++--+-+--
   public | myfunc | void             |                     | func

Whether you should is another question.

-- 
Adrian Klaver

adrian.kla...@aklaver.com 

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

Re: index unique

Hi Paul,

obviously the bounding box is not a perfect choice. we also think of one of
the "centers" point, but quite similar non uniqueness.

so, if no "tech" solution, we continue to work with the business to try to
find an appropriate PK

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 10:57 PM Paul Ramsey 
wrote:

> Primary key is going to be a BTREE index. I'm surprised you require the
> geometry in order to achieve uniqueness?
> You can't put the geometry into a BTREE because it's too large.
> You could add a column and stick the MD5 hash of the geometry there, and
> use that as the last piece of uniqueness?
> If the bbox of the geometry is "good enough" for your key, that kind of
> speaks to the idea that maybe your geometry doesn't need to be part of the
> PK? It's possible for different geometries to have the same bbox...
> ATB,
> P
>
> > On Jun 3, 2021, at 1:51 PM, Marc Millas  wrote:
> >
> > Hi,
> > postgres 12 with postgis.
> > on a table we need a primary key and to get a unique combinaison, we
> need 3 columns of that table:
> > 1 of type integer,
> > 1 of type text,
> > 1 of type geometry
> >
> > creating the PK constraint doesn work: (even with our current small data
> set)
> > ERROR:  index row size 6072 exceeds btree version 4 maximum 2704 for
> index "xxx_spkey"
> > DETAIL:  Index row references tuple (32,1) in relation "xxx".
> > HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
> > Consider a function index of an MD5 hash of the value, or use full text
> indexing.
> >
> > ok. we can do this.
> > but if so, we need to create a gist index on the geometry column to do
> any topology request.
> > so 2 indexes containing this single column.
> >
> > if we install extension btree_gist, no pb to create an index on all 3
> columns.
> > but as gist does not support unicity, this index cannot be used for the
> PK.
> >
> > OK, we may try to use a function to get the bounding box around the
> geometry objects and use the result into a btree index
> >
> > Any idea (I mean: another idea !) to tackle this ?
> > Or any critic on the "solution" ??
> >
> > thanks,
> >
> >
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com
> >
>
>

Re: syntax question

2021-06-03 Thread Guyren Howe

I know it would be non-standard, but I would love to see Postgres support the 
likes of nested functions.

I know that would be non-standard, but Postgres has lots of non-standard 
features that make it more like a real programming language and considerably 
more productive.
On Jun 3, 2021, 12:34 -0700, Bruce Momjian , wrote:
> On Thu, Jun 3, 2021 at 03:21:15PM -0400, Tom Lane wrote:
> > Adrian Klaver  writes:
> > > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> > > > On Thu, Jun 3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> > > > > within a function, I want to create another function.
> >
> > > > You can't create functions inside of functions; same for procedures.
> >
> > > Sure you can:
> >
> > Yeah. The actual problem here is that Marc is expecting variable
> > substitution to occur within a utility (DDL) statement, which it
> > doesn't. The workaround is to build the command as a string and
> > use EXECUTE, as Adrian illustrated:
> >
> > > EXECUTE 'create function ' || bidule || '() RETURNS void language
> > > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> >
> > This is not terribly well explained in the existing docs. I tried
> > to improve the explanation awhile ago in HEAD:
> >
> > https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
>
> Oh, I thought he wanted to declare a function inside the function that
> could be called only by that function, like private functions in Oracle
> packages can do. Yes, you can create a function that defines a function
> that can be called later. I guess you could also create a function that
> _conditionally_ creates a function that it can call itself too. My
> point is that you can't create a function that has function scope ---
> they all have schema scope.
>
> --
> Bruce Momjian  https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
>

Re: index unique

2021-06-03 Thread Paul Ramsey

Primary key is going to be a BTREE index. I'm surprised you require the 
geometry in order to achieve uniqueness?
You can't put the geometry into a BTREE because it's too large. 
You could add a column and stick the MD5 hash of the geometry there, and use 
that as the last piece of uniqueness?
If the bbox of the geometry is "good enough" for your key, that kind of speaks 
to the idea that maybe your geometry doesn't need to be part of the PK? It's 
possible for different geometries to have the same bbox...
ATB,
P

> On Jun 3, 2021, at 1:51 PM, Marc Millas  wrote:
> 
> Hi,
> postgres 12 with postgis.
> on a table we need a primary key and to get a unique combinaison, we need 3 
> columns of that table:
> 1 of type integer,
> 1 of type text,
> 1 of type geometry
> 
> creating the PK constraint doesn work: (even with our current small data set)
> ERROR:  index row size 6072 exceeds btree version 4 maximum 2704 for index 
> "xxx_spkey"
> DETAIL:  Index row references tuple (32,1) in relation "xxx".
> HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of the value, or use full text 
> indexing.
> 
> ok. we can do this.
> but if so, we need to create a gist index on the geometry column to do any 
> topology request.
> so 2 indexes containing this single column.
> 
> if we install extension btree_gist, no pb to create an index on all 3 columns.
> but as gist does not support unicity, this index cannot be used for the PK.
> 
> OK, we may try to use a function to get the bounding box around the geometry 
> objects and use the result into a btree index
> 
> Any idea (I mean: another idea !) to tackle this ?
> Or any critic on the "solution" ??
> 
> thanks,
> 
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>

Re: syntax question

I take note of this.
thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 10:23 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Jun 3, 2021 at 1:02 PM Marc Millas  wrote:
>
>> about knowing if I should...
>> We have to create a set of triggers (insert, update, delete) within a
>> huge set of tables. and that list of tables, and structure of them  can be
>> customized, maintained, ...
>> so we were looking for a standard script to automatize the building of
>> the whole thing, taking list of columns  and constraints (for PK) directly
>> from pg_catalog.
>> Now it works :-)
>>
>> but.. why do you ask that question ? is there any king of hidden wolf we
>> didnt see ?
>>
>
> Having done this (building a - limited - code generator framework using
> bash+psql+plpgsql) I will say that doing so using pl/pgsql, while appealing
> from "no extra tooling needed" perspective, doesn't play to pl/pgsql's
> strengths.  Using a different language to generate SQL script files, which
> can then be executed, is probably a better way to go - if you have a
> different language you can build upon (i.e., not a shell scripting language
> like bash).
>
> In particular, plpgsql nested strings are not fun to work with in any
> significant volume.
>
> David J.
>
>

index unique

Hi,
postgres 12 with postgis.
on a table we need a primary key and to get a unique combinaison, we need 3
columns of that table:
1 of type integer,
1 of type text,
1 of type geometry

creating the PK constraint doesn work: (even with our current small data
set)



*ERROR:  index row size 6072 exceeds btree version 4 maximum 2704 for index
"xxx_spkey"DETAIL:  Index row references tuple (32,1) in relation
"xxx".HINT:  Values larger than 1/3 of a buffer page cannot be
indexed.Consider a function index of an MD5 hash of the value, or use full
text indexing.*

*ok. we can do this.*
*but if so, we need to create a gist index on the geometry column to do any
topology request.*
*so 2 indexes containing this single column.*

*if we install extension btree_gist, no pb to create an index on all 3
columns.*
*but as gist does not support unicity, this index cannot be used for the
PK.*

*OK, we may try to use a function to get the bounding box around the
geometry objects and use the result into a btree index*

*Any idea (I mean: another idea !) to tackle this ?*
*Or any critic on the "solution" ??*

*thanks,*


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: syntax question

2021-06-03 Thread David G. Johnston

On Thu, Jun 3, 2021 at 1:02 PM Marc Millas  wrote:

> about knowing if I should...
> We have to create a set of triggers (insert, update, delete) within a huge
> set of tables. and that list of tables, and structure of them  can be
> customized, maintained, ...
> so we were looking for a standard script to automatize the building of the
> whole thing, taking list of columns  and constraints (for PK) directly from
> pg_catalog.
> Now it works :-)
>
> but.. why do you ask that question ? is there any king of hidden wolf we
> didnt see ?
>

Having done this (building a - limited - code generator framework using
bash+psql+plpgsql) I will say that doing so using pl/pgsql, while appealing
from "no extra tooling needed" perspective, doesn't play to pl/pgsql's
strengths.  Using a different language to generate SQL script files, which
can then be executed, is probably a better way to go - if you have a
different language you can build upon (i.e., not a shell scripting language
like bash).

In particular, plpgsql nested strings are not fun to work with in any
significant volume.

David J.

Re: syntax question

thanks Adrian, exactly what I was missing :-)

about knowing if I should...
We have to create a set of triggers (insert, update, delete) within a huge
set of tables. and that list of tables, and structure of them  can be
customized, maintained, ...
so we were looking for a standard script to automatize the building of the
whole thing, taking list of columns  and constraints (for PK) directly from
pg_catalog.
Now it works :-)

but.. why do you ask that question ? is there any king of hidden wolf we
didnt see ?


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 9:11 PM Adrian Klaver 
wrote:

> On 6/3/21 12:01 PM, Bruce Momjian wrote:
> > On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >> Hi,
> >>
> >> within a function, I want to create another function.
> >> no pb.
> >> but if I write:
> >> declare bidule text;
> >> begin
> >> bidule:='myfunc';
> >> create function bidule() ...
> >>
> >>
> >> it does create a function named bidule and not myfunc.
> >> so I am obviously missing something too obvious.
> >
> > You can't create functions inside of functions;  same for procedures.
> >
>
> Sure you can:
>
> CREATE OR REPLACE FUNCTION public.test_fnc()
>   RETURNS void
>   LANGUAGE plpgsql
> AS $function$
> DECLARE
>   bidule text;
> BEGIN
> bidule:='myfunc';
> EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> END;
>
>
> $function$
>
> select test_fnc();
>   test_fnc
> --
>
>   \df myfunc
>  List of functions
>   Schema |  Name  | Result data type | Argument data types | Type
> ++--+-+--
>   public | myfunc | void | | func
>
>
> Whether you should is another question.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: syntax question

good reading, thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Jun 3, 2021 at 9:21 PM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >>> within a function, I want to create another function.
>
> >> You can't create functions inside of functions;  same for procedures.
>
> > Sure you can:
>
> Yeah.  The actual problem here is that Marc is expecting variable
> substitution to occur within a utility (DDL) statement, which it
> doesn't.  The workaround is to build the command as a string and
> use EXECUTE, as Adrian illustrated:
>
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
>
> This is not terribly well explained in the existing docs.  I tried
> to improve the explanation awhile ago in HEAD:
>
>
> https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
>
> regards, tom lane
>

Re: syntax question

On Thu, Jun  3, 2021 at 03:21:15PM -0400, Tom Lane wrote:
> Adrian Klaver  writes:
> > On 6/3/21 12:01 PM, Bruce Momjian wrote:
> >> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> >>> within a function, I want to create another function.
> 
> >> You can't create functions inside of functions;  same for procedures.
> 
> > Sure you can:
> 
> Yeah.  The actual problem here is that Marc is expecting variable
> substitution to occur within a utility (DDL) statement, which it
> doesn't.  The workaround is to build the command as a string and
> use EXECUTE, as Adrian illustrated:
> 
> > EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
> > plpgsql AS $fnc$ BEGIN END; $fnc$ ';
> 
> This is not terribly well explained in the existing docs.  I tried
> to improve the explanation awhile ago in HEAD:
> 
> https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL

Oh, I thought he wanted to declare a function inside the function that
could be called only by that function, like private functions in Oracle
packages can do.  Yes, you can create a function that defines a function
that can be called later.  I guess you could also create a function that
_conditionally_ creates a function that it can call itself too.  My
point is that you can't create a function that has function scope ---
they all have schema scope.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: max_connections

2021-06-03 Thread Vijaykumar Jain

and this one as well. just to keep it in the thread.

Improving Postgres Connection Scalability: Snapshots - Microsoft Tech
Community



On Fri, 4 Jun 2021 at 00:14, Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:

> ok, running too many connections without recycling has an overhead.
> no wonder pgbouncer is all over as a sidecar :)
>
> In case anyone is interested, this blog is a great read.
> Analyzing the Limits of Connection Scalability in Postgres - Microsoft
> Tech Community
> 
>
>
> 
>
>
> On Sun, 30 May 2021 at 20:19, Vijaykumar Jain <
> vijaykumarjain.git...@gmail.com> wrote:
>
>> I have a two dumb questions.
>>
>> 1)
>> I know the max_connections value change requires a restart.
>>
>> I also read a thread, which says why it is the case, assuming it still
>> holds true.
>>
>> Jean Arnaud  writes:
>> > I'm looking for a way to change the "max_connections" parameter without
>> > restarting the PostGreSQL database.
>>
>> There is none. That's one of the parameters that determines shared
>> memory array sizes, and we can't change those on-the-fly.
>>
>> regards, tom lane
>>
>> Does that mean, if I set max_connections to 1000 users, but only intend
>> to use 10 at max (via conn limit per role settings), what would be the perf
>> difference compared to max_connections = 100 , but still using only 10 at
>> max.
>>
>> 2)
>> can i for test purposes.compile src with a xid max value to 2^16 etc ?
>> like with uint16
>>
>> Basically, I was trying to simulate xid wraparound, by creating an open
>> transaction and an inactive replication slot, and then running a huge
>> parallel loops to bump txid_current() to see what happens at 2B crossover.
>> it takes forever on my slow laptop.
>> I do not see these in test scenarios, or could not find any.
>>
>> as always, ignore if not relevant, or already discussed.
>> I am preparing for interviews, hence asking myself dumb questions :)
>>
>> --
>> Thanks,
>> Vijay
>> Mumbai, India
>>
>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>


-- 
Thanks,
Vijay
Mumbai, India

Re: syntax question

Adrian Klaver  writes:
> On 6/3/21 12:01 PM, Bruce Momjian wrote:
>> On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
>>> within a function, I want to create another function.

>> You can't create functions inside of functions;  same for procedures.

> Sure you can:

Yeah.  The actual problem here is that Marc is expecting variable
substitution to occur within a utility (DDL) statement, which it
doesn't.  The workaround is to build the command as a string and
use EXECUTE, as Adrian illustrated:

> EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
> plpgsql AS $fnc$ BEGIN END; $fnc$ ';

This is not terribly well explained in the existing docs.  I tried
to improve the explanation awhile ago in HEAD:

https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL

regards, tom lane

Re: syntax question


On 6/3/21 12:01 PM, Bruce Momjian wrote:

On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:

Hi,

within a function, I want to create another function.
no pb.
but if I write:
declare bidule text;
begin
bidule:='myfunc';
create function bidule() ...


it does create a function named bidule and not myfunc.
so I am obviously missing something too obvious.


You can't create functions inside of functions;  same for procedures.



Sure you can:

CREATE OR REPLACE FUNCTION public.test_fnc()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
 bidule text;
BEGIN
bidule:='myfunc';
EXECUTE  'create function ' ||  bidule || '() RETURNS void language 
plpgsql AS $fnc$ BEGIN END; $fnc$ ';

END;


$function$

select test_fnc();
 test_fnc
--

 \df myfunc
List of functions
 Schema |  Name  | Result data type | Argument data types | Type
++--+-+--
 public | myfunc | void | | func


Whether you should is another question.


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

Re: syntax question

On Thu, Jun  3, 2021 at 08:58:03PM +0200, Marc Millas wrote:
> Hi,
> 
> within a function, I want to create another function.
> no pb.
> but if I write:
> declare bidule text;
> begin
> bidule:='myfunc';
> create function bidule() ...
> 
> 
> it does create a function named bidule and not myfunc.
> so I am obviously missing something too obvious.

You can't create functions inside of functions;  same for procedures.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

syntax question

Hi,

within a function, I want to create another function.
no pb.
but if I write:
declare bidule text;
begin
bidule:='myfunc';
create function bidule() ...


it does create a function named bidule and not myfunc.
so I am obviously missing something too obvious.

can someone help ?
thanks



Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: max_connections

2021-06-03 Thread Vijaykumar Jain

ok, running too many connections without recycling has an overhead.
no wonder pgbouncer is all over as a sidecar :)

In case anyone is interested, this blog is a great read.
Analyzing the Limits of Connection Scalability in Postgres - Microsoft Tech
Community





On Sun, 30 May 2021 at 20:19, Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:

> I have a two dumb questions.
>
> 1)
> I know the max_connections value change requires a restart.
>
> I also read a thread, which says why it is the case, assuming it still
> holds true.
>
> Jean Arnaud  writes:
> > I'm looking for a way to change the "max_connections" parameter without
> > restarting the PostGreSQL database.
>
> There is none. That's one of the parameters that determines shared
> memory array sizes, and we can't change those on-the-fly.
>
> regards, tom lane
>
> Does that mean, if I set max_connections to 1000 users, but only intend to
> use 10 at max (via conn limit per role settings), what would be the perf
> difference compared to max_connections = 100 , but still using only 10 at
> max.
>
> 2)
> can i for test purposes.compile src with a xid max value to 2^16 etc ?
> like with uint16
>
> Basically, I was trying to simulate xid wraparound, by creating an open
> transaction and an inactive replication slot, and then running a huge
> parallel loops to bump txid_current() to see what happens at 2B crossover.
> it takes forever on my slow laptop.
> I do not see these in test scenarios, or could not find any.
>
> as always, ignore if not relevant, or already discussed.
> I am preparing for interviews, hence asking myself dumb questions :)
>
> --
> Thanks,
> Vijay
> Mumbai, India
>


-- 
Thanks,
Vijay
Mumbai, India

Re: Dropping dependent tables