Re: Array vs Temporary table vs Normal Table + truncate at end

[Laurenz Albe]

On Mon, 2023-09-04 at 18:42 +0200, Lorusso Domenico wrote:
> I prepared a function that build a set of records of type (myComplexType).
> 
> That works, but I've to use this record set in different situations.
> 
> One of them require to scan multiple times the function results:
>    1. comparing with another table (myProducteAttributeTable) to determine if 
> I need to delete old record
>    2. check if the recordset contains attribute never registered before
>    3. in this case insert in appropriate table (myAttribute), retrieve 
> attribute uid and type
>    4. finally update first table (myProducteAttributeTable) with new 
> information coming from the recordset (with a merge)
> 
> So, which is the best approach?
> 
>  * Store result in an array and use in each subsequent query the unnest (with 
> a complex record type?)
>  * Create temporary table on commit delete, with the same structure? But in 
> this case I can't declare a
>cursor for the table, and I can't use update where current of (for point 3)
>  * Create normal table and truncate as part of execution, but what happens in 
> case of parallel execution?

I'd say it depends.

If the set is rather smallish, arrays may be the best way.

If the set is large (or the rows themselves are large), and the function is not 
called all the time,
I'd probably go with a temporary table.

If the set is large and the function called all the time, I'd try to use a 
persistent table to
avoid catalog table bloat.  If several function invocations need to store their 
record set in
the same table, you could make the backend process ID part of the primary key.

Yours,
Laurenz Albe

Re: createuser unexpectedly creates superuser with createdb and createrole

[Laurenz Albe]

On Tue, 2023-09-05 at 00:03 +0200, Erik Wienhold wrote:
> On 04/09/2023 03:42 CEST Tom Lane  wrote:
> > I think the last hunk of this is plenty sufficient, and the earlier
> > ones just add noise.
> 
> Done.

Looks good to me.

Yours,
Laurenz Albe

Re: createuser unexpectedly creates superuser with createdb and createrole

[Erik Wienhold]

On 04/09/2023 03:42 CEST Tom Lane  wrote:

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

Done.

--
ErikFrom 7728fcab53c8e06c781f7347532cc8619799d8ff Mon Sep 17 00:00:00 2001
From: Erik Wienhold 
Date: Mon, 4 Sep 2023 23:50:33 +0200
Subject: [PATCH v2] Document implied privileges of createuser --superuser

Commit 8ae0d476a9 added option --superuser to createuser.  In contrast
to CREATE ROLE, createuser also grants CREATEDB and CREATEROLE to new
superusers.  This commit documents the implied privileges.
---
 doc/src/sgml/ref/createuser.sgml | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 5c34c62342..03efeebe16 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -275,7 +275,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: rollback to savepoint issue

[David G. Johnston]

On Monday, September 4, 2023, Erik Wienhold  wrote:

> On 04/09/2023 11:51 CEST Lorusso Domenico  wrote:
>
> > I can't use the savepoint and rollback to savepoint clause.
> > I've found some similar problems around on the web, but I can't catch the
> > good way to proceed.
> >
> > What I'm trying to do is:
> >   * compare new set of attribute with older
> >   * if some attributes are part of old set and not in the new one: delete
> > the old
> >   * but if parameter "on_misisng_delete" is false rollback delete command
> > and rais exception
> > The original code in embedded in a function, but the problem is the same:
>
> Transaction control is not possible in functions.  Only in procedures
> (CALL)
> and DO blocks.
>

Then explain why the original savepoint command wasn’t a syntax, or
runtime, error?  Plus, the error is syntax, usually when you try something
that exists but is disallowed the system gives you some kind of invalid
state exception at runtime,

David J.

Array vs Temporary table vs Normal Table + truncate at end

[Lorusso Domenico]

Hello guys,
I prepared a function that build a set of records of type (myComplexType).

That works, but I've to use this record set in different situations.

One of them require to scan multiple times the function results:

   1. comparing with another table (myProducteAttributeTable) to determine
   if I need to delete old record
   2. check if the recordset contains attribute never registered before
   3. in this case insert in appropriate table (myAttribute), retrieve
   attribute uid and type
   4. finally update first table (myProducteAttributeTable) with new
   information coming from the recordset (with a merge)


So, which is the best approach?


   - Store result in an array and use in each subsequent query the unnest
   (with a complex record type?)
   - Create temporary table on commit delete, with the same structure? But
   in this case I can't declare a cursor for the table, and I can't use update
   where current of (for point 3)
   - Create normal table and truncate as part of execution, but what
   happens in case of parallel execution?

Re: rollback to savepoint issue

[Dominique Devienne]

On Mon, Sep 4, 2023 at 4:22 PM Dominique Devienne 
wrote:

> On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold  wrote:
>
>> On 04/09/2023 11:51 CEST Lorusso Domenico  wrote:
>> Transaction control is not possible in functions.  Only in procedures
>> (CALL)
>> and DO blocks.
>>
>> > ERROR: syntax error at or near "to"
>> > LINE 41: rollback to savepoint deleteAttribute;
>>
>> Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1]
>>
>> [1]
>> https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS
>
>
> Hi Erik. And experts at large. What's the underlying mechanism though? An
> implicit SAVEPOINT?
> [...] Thanks for any insights. --DD
>

(I guess I should have done a little more research before posting...)

I think Laurenz' old post answers most of my question. I probably read it
before too!

Different devs work on different parts of our (large) system / apps, and we
don't all use
SAVEPOINTs. I don't, but one of my colleagues does. Thus my (hasty)
questions above.
Sorry for the noise. --DD

[1]:
https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

Re: rollback to savepoint issue

[Erik Wienhold]

On 04/09/2023 16:56 CEST David G. Johnston  wrote:

> On Monday, September 4, 2023, Erik Wienhold  wrote:
>
> > On 04/09/2023 11:51 CEST Lorusso Domenico  wrote:
> >
> >  > The original code in embedded in a function, but the problem is the same:
> >
> >  Transaction control is not possible in functions. Only in procedures (CALL)
> >  and DO blocks.
>
> Then explain why the original savepoint command wasn’t a syntax, or runtime,
> error?

I don't need to because CREATE {FUNCTION | PROCEDURE} already fails because of
ROLLBACK TO SAVEPOINT.  And without a function to execute there can't be any
runtime error because of SAVEPOINT.  My point was about transaction control in
plpgsql in general.

> Plus, the error is syntax, usually when you try something that exists
> but is disallowed the system gives you some kind of invalid state exception
> at runtime,

--
Erik

Re: rollback to savepoint issue

[Adrian Klaver]

On 9/4/23 07:56, David G. Johnston wrote:
On Monday, September 4, 2023, Erik Wienhold > wrote:


On 04/09/2023 11:51 CEST Lorusso Domenico mailto:domenico@gmail.com>> wrote:

 > I can't use the savepoint and rollback to savepoint clause.
 > I've found some similar problems around on the web, but I can't
catch the
 > good way to proceed.
 >
 > What I'm trying to do is:
 >   * compare new set of attribute with older
 >   * if some attributes are part of old set and not in the new
one: delete
 >     the old
 >   * but if parameter "on_misisng_delete" is false rollback delete
command
 >     and rais exception
 > The original code in embedded in a function, but the problem is
the same:

Transaction control is not possible in functions.  Only in
procedures (CALL)
and DO blocks.


Then explain why the original savepoint command wasn’t a syntax, or 
runtime, error?  Plus, the error is syntax, usually when you try 
something that exists but is disallowed the system gives you some kind 
of invalid state exception at runtime,


Maybe this?:

https://www.postgresql.org/docs/current/sql-do.html

"If DO is executed in a transaction block, then the procedure code 
cannot execute transaction control statements. Transaction control 
statements are only allowed if DO is executed in its own transaction."




David J.



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

Re: rollback to savepoint issue

[Tom Lane]

Dominique Devienne  writes:
> Hi Erik. And experts at large. What's the underlying mechanism though? An
> implicit SAVEPOINT?

Yes, savepoints and plpgsql exception blocks are built on the same
"subtransaction" infrastructure.

> Which means extra explicit round-trips to the server to establish and "move
> along" the savepoint,
> per command. Which I think is "expensive". So would anonymous DO blocks be
> a better solution?

In general, moving logic to the server side is an effective way of
removing network round trips.  That could be DO blocks, functions,
or procedures.  If you're going to execute the same code over and
over within a session, a function or procedure will probably be
a better choice than re-issuing identical DO blocks.

regards, tom lane

Re: [EXTERNAL] Oracle FDW version

[Adam Lee]

That warning is misleading while creating extensions. Check the runtime linking 
of oracle_fdw.so,
probably some dependencies are missing. Greenplum failed to detect such issues 
and errored out
at a wrong place.

Since it’s an issue only happens on Greenplum, please continue the discussion 
off the mailing list.

From: Jethro Elmer Sanidad 
Date: Monday, September 4, 2023 at 13:17
To: Adam Lee 
Cc: Adrian Klaver , Ian Lawrence Barwick 
, Christophe Pettus , 
umair.sha...@gmail.com , 
pgsql-general@lists.postgresql.org 
Subject: Re: [EXTERNAL] Oracle FDW version
!! External Email
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=#

Re: rollback to savepoint issue

[Dominique Devienne]

On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold  wrote:

> On 04/09/2023 11:51 CEST Lorusso Domenico  wrote:
> Transaction control is not possible in functions.  Only in procedures
> (CALL)
> and DO blocks.
>
> > ERROR: syntax error at or near "to"
> > LINE 41: rollback to savepoint deleteAttribute;
>
> Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1]
>
> [1]
> https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS


Hi Erik. And experts at large. What's the underlying mechanism though? An
implicit SAVEPOINT?

The reason I'm asking, is that we are using explicit SAVEPOINTs in client
C/C++ code,
to "emulate" per-Statement "isolation", like other DBs have (Oracle,
SQLite), instead of
having to ROLLBACK the "whole" transaction.

Which means extra explicit round-trips to the server to establish and "move
along" the savepoint,
per command. Which I think is "expensive". So would anonymous DO blocks be
a better solution?
If BEGIN/EXCEPTION uses an "implicit" / "hidden" SAVEPOINT, would that
still be better then?

And if we switch to anon DO+EXCEPTION blocks, is it just as easy to bind to
the inner SQL command(s)?

Sorry if my questions are a little vague. This is an area that's still
fuzzy for me, I'm not versed in pgPL/SQL.

Finally, I've read "horror stories" about SAVEPOINTs, with
performance-cliffs when using them,
because if I recall correctly, there's only room in pages (?) to deal with
just a few efficiently,
before additional "storage" must be allocated somewhere else, leading to
contention. Does that
apply to that BEGIN+EXCEPTION mechanism too?

Thanks for any insights. --DD

Re: rollback to savepoint issue

[Erik Wienhold]

On 04/09/2023 11:51 CEST Lorusso Domenico  wrote:

> I can't use the savepoint and rollback to savepoint clause.
> I've found some similar problems around on the web, but I can't catch the
> good way to proceed.
>
> What I'm trying to do is:
>   * compare new set of attribute with older
>   * if some attributes are part of old set and not in the new one: delete
> the old
>   * but if parameter "on_misisng_delete" is false rollback delete command
> and rais exception
> The original code in embedded in a function, but the problem is the same:

Transaction control is not possible in functions.  Only in procedures (CALL)
and DO blocks.

> ERROR: syntax error at or near "to"
> LINE 41: rollback to savepoint deleteAttribute;

Use BEGIN ... EXCEPTION ... END instead of manual savepoints. [1]

[1] 
https://www.postgresql.org/docs/current/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS

--
Erik

rollback to savepoint issue

[Lorusso Domenico]

Hello guys,
I can't use the savepoint and rollback to savepoint clause.
I've found some similar problems around on the web, but I can't catch the
good way to proceed.

What I'm trying to do is:

   - compare new set of attribute with older
   - if some attributes are part of old set and not in the new one: delete
   the old
   - but if parameter "on_misisng_delete" is false rollback delete command
   and rais exception

The original code in embedded in a function, but the problem is the same:
ERROR:  syntax error at or near "to"
LINE 41:   rollback to savepoint deleteAttribute;
^

SQL state: 42601
Character: 768


Code extracted:
do $body$
declare
on_missing_delete  boolean=false;
_i integer;
_vAttributeName text='paperi';
_importo integer= 5000;
begin
savepoint deleteAttribute;

execute format($$
with ru as (
update  public.%I as q
set is_closed=true
, modify_user_id='process:definitionUpdate'
where q.importo > $1
returning 1
)
select count(*) from ru
;$$
, _vAttributeName)
using _importo
into _i;
--> If I can't delete and there are row to delete raise excpetion
if not on_missing_delete  and _i > 0 then
--> undo deletion
rollback to savepoint deleteAttribute;

raise exception 'New attributes list foresees to remove % attribute(s), but
parameter "on missing delete" is false.', _i;
else
release savepoint deleteAttribute;
end if;

rollback;
end;
$body$;


I know I can modify the code, but I wish to understand why I can't use
rollback to save point

Re: Question on Partition key

[David Rowley]

On Sun, 3 Sept 2023 at 23:52, veem v  wrote:
> 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?

The query planner does not do any subtracting of values which are the
target of the statistics.  There are comparisons, but comparing a DATE
or an INT are equally as cheap.

To me, the design with the PART_DATE_YM_NM INT column looks very
strange.  Why bother partitioning by RANGE when there's just a single
value? The partition pruning done for LIST partitioning will work
equally as well when given ranges of values.  Also, don't they ever
want to store the day of the month anywhere in the table?  The INT
partitioned table won't allow that, but the DATE one will.

Several jobs ago in a land far far away, I worked with someone who
would tell engineers to not use EXISTs clauses in their SQLs as
"they're not optimised very well". I questioned him about this and as
it turned out, some version of Oracle once didn't optimise these very
well and when he learned this, he took that knowledge and seemingly
applied it to all versions of all RDBMSs in the universe.  Rather
bizarre, but perhaps that's what's going on here too.

David