Re: PSQL = Yes ... JDBC = no ??

[Peter J. Holzer]

On 2023-09-03 20:19:44 -0400, Amn Ojee Uw wrote:
> I see now, any suggestions as work around in JDBC?

Implement the functionality in Java.

You could do it directly:

execute
SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT FROM 
pg_database WHERE datname = 'jme_test_database')
read the result
for each row returned
execute it as SQL

But that would be silly.

Instead you would do it like this:

execute
SELECT datname FROM pg_database WHERE datname = 'jme_test_database'
read the result
if the result is empty:
execute
CREATE DATABASE jme_test_database'

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: [EXTERNAL] Oracle FDW version

[Jethro Elmer Sanidad]

Hello,

I already installed your extension. As of now, I'm having issues in
creating the oracle_fdw. Please advise. Thanks!

postgres=# create extension oracle_fdw;
ERROR:  query plan with multiple segworker groups is not supported
HINT:  likely caused by a function that reads or modifies data in a
distributed table
postgres=#

On Fri, Sep 1, 2023 at 11:05 AM Jethro Elmer Sanidad <
jethroelmersani...@gardenia.com.ph> wrote:

> Hello,
>
> I already installed your extension. As of now, I'm having issues in
> creating the oracle_fdw. Please advise. Thanks!
>
> postgres=# create extension oracle_fdw;
> ERROR:  query plan with multiple segworker groups is not supported
> HINT:  likely caused by a function that reads or modifies data in a
> distributed table
> postgres=#
>
> On Thu, Aug 24, 2023 at 3:24 PM Adam Lee  wrote:
>
>> The original poster was building against Greenplum 6, it’s expected to
>> fail.
>>
>>
>>
>> Try this https://github.com/adam8157/oracle_fdw_greenplum, it’s not
>> based on the lasts oracle_fdw, but likely easy to rebase.
>>
>>
>>
>> *From: *Adrian Klaver 
>> *Date: *Thursday, August 24, 2023 at 15:19
>> *To: *Jethro Elmer Sanidad , Ian
>> Lawrence Barwick 
>> *Cc: *Christophe Pettus , umair.sha...@gmail.com <
>> umair.sha...@gmail.com>, pgsql-general@lists.postgresql.org <
>> pgsql-general@lists.postgresql.org>
>> *Subject: *Re: [EXTERNAL] Oracle FDW version
>>
>> !! External Email
>>
>> On 8/22/23 23:10, Jethro Elmer Sanidad wrote:
>> > Hello,
>> >
>> > Can you confirm in this email that our current version of PostgreSQL
>> > (9.2.24) is not compatible with any of oracle_fdw versions released? And
>> > you are recommending an upgrade? Thanks!
>>
>> In your first post you said:
>>
>> "Can you provide us download links for oracle_fdw for psql (PostgreSQL)
>> 9.4.24. ..."
>>
>> 1) Are you on 9.2 or 9.4?
>>
>> 2) Where did you get Postgres from?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>>
>>
>>
>>
>> !! External Email: This email originated from outside of the
>> organization. Do not click links or open attachments unless you recognize
>> the sender.
>>
>
>
> --
>
> [image: image.png] 
>
> *Jethro Elmer T. Sanidad*
> Management Information Systems
> O: +63 2 8889 8890 loc. 1354 | M: +63 9686809253
> Gardenia Bakeries Philippines Incorporated | Laguna International
> Industrial Park (LIIP) | Binan 4024 | Laguna
>


-- 

[image: image.png] 

*Jethro Elmer T. Sanidad*
Management Information Systems
O: +63 2 8889 8890 loc. 1354 | M: +63 9686809253
Gardenia Bakeries Philippines Incorporated | Laguna International
Industrial Park (LIIP) | Binan 4024 | Laguna

-- 






















*CONFIDENTIALITY NOTICE:* This email is
confidential 
and subject to legal rights of Gardenia Bakeries (Philippines),
Inc. 
(“GBPI”). If you received this email by error, you must not use or
disclose 
any information in it and immediately notify the sender by return
email and 
permanently delete this email (and all attachments) without any use
of its 
contents. To the extent legally permitted, GBPI has no liability of any
kind arising out of or in connection with any virus transmitted by this 
email,
attachments, and/or any errors or omissions in content including 
transmissions
through unauthorised use or tampering of email system and/or 
the integrity of the email
being compromised. Any personal statements or
opinions in this communication are those of the individual sender and do 
not
reflect the views of GBPI. GBPI will never consent to
or authorize the 
publication of defamatory statements or infringement of
intellectual 
property. Only individuals authorized by GBPI’s Board of Directors
may sign 
and/or accept proposals, contracts, or agreements as well as waive any
legal right of GBPI. Any personal information in this email must be handled 
in
accordance with the Data Privacy Act of 2012 of the Philippines and its
implementing rules and regulations.

Re: createuser unexpectedly creates superuser with createdb and createrole

[Tom Lane]

Erik Wienhold  writes:
> Anyway, I prepared a patch for the docs.

I think the last hunk of this is plenty sufficient, and the earlier
ones just add noise.

regards, tom lane

Re: createuser unexpectedly creates superuser with createdb and createrole

[Erik Wienhold]

On 30/08/2023 03:21 CEST Laurenz Albe  wrote:

> I am somewhat surprised too, but it has been like that since commit 8ae0d476a9
> in 2005.

Yeah, unlikely to find out why after 18 years.

> The code is pretty clear about that:
>
> if (superuser == TRI_YES)
> {
> /* Not much point in trying to restrict a superuser */
> createdb = TRI_YES;
> createrole = TRI_YES;
> }
>
> I would say that changing that long standing behavior would cause more harm
> than benefit.

Sure, but it sounds like a reasonable change for a future major release.

> First, as the code says, it doesn't make a lot of difference.  And who knows,
> perhaps someone somewhere creates superusers, later changes them to 
> NOSUPERUSER
> and expects CREATEDB and CREATEROLE to be set after that.

Just realized that the bootstrap user has all attributes even though not needed
as a superuser.  Maybe that's the reason for createuser's behavior.  But why
only CREATEDB and CREATEROLE then?

> If anything, we could add something to the documentation.

Anyway, I prepared a patch for the docs.  But I'm not sure if the description
should still read "There is no effective difference between creating users via
this utility and via other methods for accessing the server."

--
ErikFrom 57309068a0c279f06fe85532e45e2a6813f72d32 Mon Sep 17 00:00:00 2001
From: Erik Wienhold 
Date: Mon, 4 Sep 2023 02:54:40 +0200
Subject: [PATCH] Document privileges createuser grants to superusers

Commit 8ae0d476a9 added option --superuser to createuser.  In contrast
to CREATE ROLE, createuser also grants CREATEDB and CREATEROLE to new
superusers.  This commit documents this feature.
---
 doc/src/sgml/ref/createuser.sgml | 10 +++---
 1 file changed, 7 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 5c34c62342..73c5ae9d77 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -56,6 +56,9 @@ PostgreSQL documentation
SQL command CREATE ROLE.
There is no effective difference between creating users via
this utility and via other methods for accessing the server.
+   But note that createuser also grants
+   CREATEDB and CREATEROLE
+   to superusers.
   
 
  
@@ -120,7 +123,7 @@ PostgreSQL documentation
   

 The new user will not be allowed to create databases.  This is the
-default.
+default, except when --superuser is also specified.

   
  
@@ -265,7 +268,7 @@ PostgreSQL documentation
   

 The new user will not be allowed to create new roles.  This is the
-default.
+default, except when --superuser is also specified.

   
  
@@ -275,7 +278,8 @@ PostgreSQL documentation
   --superuser
   

-The new user will be a superuser.
+The new user will be a superuser.  Also implies --createdb
+and --createrole.

   
  
-- 
2.42.0

Re: PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

I see now, any suggestions as work around in JDBC?

Thanks!!

On 9/3/23 8:12 p.m., David G. Johnston wrote:

On Sunday, September 3, 2023, Amn Ojee Uw  wrote:

Are you saying that JDBC cannot handle or process \gexec, since it
is PG-15 exclusive?


psql is a client application present in all versions of PostgreSQL.  
It has a bunch of features related to executing SQL queries.  The 
stuff that it is documented to do are only doable by it in many cases, 
and in all cases are done locally, not by the server.  You cannot send 
those meta-commands to the server, it has no clue what to do with 
them.  And since you are using JDBC you by definition aren’t using psql.


David J.

Re: PSQL = Yes ... JDBC = no ??

[David G. Johnston]

On Sunday, September 3, 2023, Amn Ojee Uw  wrote:
>
> Are you saying that JDBC cannot handle or process \gexec, since it is
> PG-15 exclusive?
>

psql is a client application present in all versions of PostgreSQL.  It has
a bunch of features related to executing SQL queries.  The stuff that it is
documented to do are only doable by it in many cases, and in all cases are
done locally, not by the server.  You cannot send those meta-commands to
the server, it has no clue what to do with them.  And since you are using
JDBC you by definition aren’t using psql.

David J.

Re: PSQL = Yes ... JDBC = no ??

[Adrian Klaver]

On 9/3/23 15:25, Amn Ojee Uw wrote:


PD:

Are you saying that JDBC cannot handle or process \gexec, since it is 
PG-15 exclusive?





I am saying \gexec is a command unique to the client program psql, it 
has no meaning outside of the program. In other words it is not an SQL 
command and JDBC will not accept it.


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

Re: PSQL = Yes ... JDBC = no ??

[Maciek Sakrejda]

On Sun, Sep 3, 2023, 16:25 Amn Ojee Uw  wrote:

> Are you saying that JDBC cannot handle or process \gexec, since it is
> PG-15 exclusive?
>

JDBC cannot handle our process \gexec since it is _psql_ exclusive. It's a
command interpreted and evaluated by that client specifically, not by the
Postgres server.

>

Re: PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

Thanks Adrian, I did read the link you suggested.
I was had at the Introduction title, since it makes this mention 
"/Alternatively, input can be from a file or from command line 
arguments./" since the input would be from the back end of the database, 
as I am using Java DBC to access the database.


However, I have not been able to discern the correlation that exist 
between the*JDBC statement *and the definition of *gexec* [/Sends the 
current query buffer to the server, then treats each column of each row 
of the query's output (if any) as an SQL statement to be executed./]


I guess it is that I am so new to the world of PG-15 that I just cannot 
understand or get your point. My reality is that esoteric definition go 
right over my head, since I am a newbie.


Would you be able to make the appropriate corrections to the 
PostgreSQL-15 schema passed to the JDBC Statement.executeUpdate() method?


Thanks in advance.

PD:

Are you saying that JDBC cannot handle or process \gexec, since it is 
PG-15 exclusive?


On 9/3/23 12:04 p.m., Adrian Klaver wrote:

On 9/3/23 09:00, Amn Ojee Uw wrote:

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a 
parameter in a JDBC method?


The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.

//CREATE DATABASE IF NOT EXISTS
//~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**



\gexec is a psql specific meta-command.

See Meta-Commands here:

https://www.postgresql.org/docs/current/app-psql.html




Any help would be very much appreciated.

Thanks in advance.

Re: PSQL = Yes ... JDBC = no ??

[Adrian Klaver]

On 9/3/23 09:00, Amn Ojee Uw wrote:

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a parameter 
in a JDBC method?


The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.

//CREATE DATABASE IF NOT EXISTS
//~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**



\gexec is a psql specific meta-command.

See Meta-Commands here:

https://www.postgresql.org/docs/current/app-psql.html




Any help would be very much appreciated.

Thanks in advance.



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

PSQL = Yes ... JDBC = no ??

[Amn Ojee Uw]

Hello!

This issue really puzzles me beyond intrigue.

Why would this schema:
*SELECT 'CREATE DATABASE jme_test_database' WHERE NOT EXISTS (SELECT 
FROM pg_database WHERE datname = 'jme_test_database')\gexec*
work when entered at the psql prompt, but not when passed as a parameter 
in a JDBC method?


The bellow snip demonstrates the the creation of a String object with 
same character string used in the PSQL#.

//CREATE DATABASE IF NOT EXISTS
//~~
*var s = new ***myString*(SELECT 'CREATE DATABASE jme_test_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 
'jme_test_database')\gexec);**

**
**stmt.executeUpdate(s.toString());*

However, this time PostgreSQL-15 complains with an error message saying :

*Exception in thread "main" org.postgresql.util.PSQLException: ERROR: 
syntax error at or near "\"**

**  Position: 122

*

For better clarification, I'd like to show the code to create the schema 
inside Java/JDBC.

 snip 

 */
   ...

    var sv = myString();
    ...

    public final myString getCreateDatabase(myString s){
    this.sv.setData("SELECT 'CREATE DATABASE ");
    this.sv.append(s.toString());
    this.sv.append("\'");
    this.sv.append("WHERE NOT EXISTS (SELECT FROM pg_database WHERE 
datname = ");

    this.sv.append("\'");
    this.sv.append(s.toString());
    this.sv.append("\'");
    this.sv.append(")\\gexec");

    return sv;
    }


Any help would be very much appreciated.

Thanks in advance.

Re: Question on Partition key

[veem v]

Thank you so much for the clarification.

Actually team have used similar partitioning strategy on integer columns in
past. So they are inclined towards that. I will still, double check with
others if any business restrictions exists. But as you already mentioned,
it's not good in terms of data quality perspective. I agree to this point.

Additionally, is it true that optimizer will also get fooled on getting the
math correct during cardinality estimates, as because there is a big
difference between , comparing or substracting, two date values VS two
number values. And storing the dates in the number columns will pose this
problem for the optimizer. Is my understanding correct here?

On Sun, 3 Sept, 2023, 2:02 pm Deep,  wrote:

> Have your friends also mentioned how it is going to help to convert date
> field to integer !???
>
> On Sun, Sep 3, 2023 at 3:51 AM Erik Wienhold  wrote:
>
>> On 03/09/2023 00:35 CEST veem v  wrote:
>>
>> > We are trying to create a monthly range partition table , partitioned on
>> > column PART_DATE. This will hold Orders and part_date is nothing but
>> invoice
>> > date. Some Team mates are asking to use the "PART_DATE" column as data
>> type
>> > "INTEGER" with "MM" format [...]
>>
>> Why do your team mates favor integer over date?
>>
>> > Want to know experts' views on this. If the data type of the partition
>> key
>> > matters here or not?
>>
>> Both integer and date are stored as 4 bytes.  There should be no
>> difference
>> regarding index size.  I don't know if the data type makes a difference in
>> partition pruning performance in this case, but I'd be surprised if it
>> were
>> the case.
>>
>> > Or if there is any downside of each approach in future?
>>
>> The downside of integer is that it allows invalid dates (e.g. 202313)
>> unless
>> you also add check constraints.  But then just use date if you want to
>> store
>> dates.  You get input validation and can use the date operators and
>> functions
>> that Postgres offers.
>>
>> --
>> Erik
>>
>>
>>

Re: Question on Partition key

[Deep]

Have your friends also mentioned how it is going to help to convert date
field to integer !???

On Sun, Sep 3, 2023 at 3:51 AM Erik Wienhold  wrote:

> On 03/09/2023 00:35 CEST veem v  wrote:
>
> > We are trying to create a monthly range partition table , partitioned on
> > column PART_DATE. This will hold Orders and part_date is nothing but
> invoice
> > date. Some Team mates are asking to use the "PART_DATE" column as data
> type
> > "INTEGER" with "MM" format [...]
>
> Why do your team mates favor integer over date?
>
> > Want to know experts' views on this. If the data type of the partition
> key
> > matters here or not?
>
> Both integer and date are stored as 4 bytes.  There should be no difference
> regarding index size.  I don't know if the data type makes a difference in
> partition pruning performance in this case, but I'd be surprised if it were
> the case.
>
> > Or if there is any downside of each approach in future?
>
> The downside of integer is that it allows invalid dates (e.g. 202313)
> unless
> you also add check constraints.  But then just use date if you want to
> store
> dates.  You get input validation and can use the date operators and
> functions
> that Postgres offers.
>
> --
> Erik
>
>
>