Re: Two server instances on one server

[Andreas Wagner]

Am Mittwoch, dem 10.04.2024 um 16:41 -0400 schrieb Tom Lane:
> Andreas Wagner  writes:
> > I consider using two instances of the PG server on one node. The
> > node
> > has two processors and each processor has 64GB memory assigned to
> > it.
> > I'd like to
> > * restrict instance one to processor 0 and its memory and
> > * instance two to processor 1 and its memory
> > using numactl (or similar commands regarding cpuset) and
> > partitioning.
> 
> Seems straightforward enough to me, as long as you put the instances
> onto distinct port numbers.  Are you encountering problems?

I don't see a way, yet, to put one half of the partitions on on
instance ond the other half to the other, automatically. Replication
seems to be a 1:1 copy and partitioning seems to stick to one instance.

Regards,
Andreas Wagner

RE: (When) can a single SQL statement return multiple result sets?

[Patrick FICHE]

-Original Message-
From: Jan Behrens  
Sent: Wednesday, April 10, 2024 11:23 PM
To: pgsql-general@lists.postgresql.org
Subject: (When) can a single SQL statement return multiple result sets?

Hello,

While writing a PostgreSQL client library for Lua supporting Pipelining (using 
PQsendQueryParams), I have been wondering if there are any single SQL commands 
that return multiple result sets. It is indeed possible to create such a case 
by using the RULE system:

db=> CREATE VIEW magic AS SELECT;
CREATE VIEW
db=> CREATE RULE r1 AS ON DELETE TO magic
db-> DO INSTEAD SELECT 42 AS "answer";
CREATE RULE
db=> CREATE RULE r2 AS ON DELETE TO magic
db-> DO ALSO SELECT 'Hello' AS "col1", 'World!' AS "col2";
CREATE RULE
db=> DELETE FROM magic; -- single SQL statement!
 answer

 42
(1 row)

 col1  |  col2  
---+
 Hello | World!
(1 row)

DELETE 0

Here, "DELETE FROM magic" returns multiple result sets, even though it is only 
a single SQL statement.

(Note that this isn't possible with rules ON SELECT because it is only allowed 
to create a single SELECT rule on a view.)

The case outlined above seems to be a somewhat special case. I haven't found 
any other way to return multiple results (other than sending several 
semicolon-separated statements, which is not supported by PQsendQueryParams). 
So is there any (other) case where I reasonably should expect several result 
sets returned by PQgetResult (before PQgetResult returns NULL)? Wouldn't it 
make sense to disallow such behavior altogether? And if not, why can't I write 
a stored procedure or function that returns multiple result sets?

These questions are relevant to me because it may have an effect on the API 
design if a statement can return several result sets.

Kind regards,
Jan Behrens
-Original Message-

Hi, you can declare a function which returns multiple CURSORS...

RETURNS SETOF REFCURSOR

Then, in your function, you have to write something like this
DECLARE
rc_1  refcursor;
rc_2  refcursor;
rc_3  refcursor;
...
OPEN rc_1 FOR SELECT ...
OPEN rc_2 FOR SELECT ...
OPEN rc_3 FOR SELECT ...
RETURN NEXT rc_1;
RETURN NEXT rc_2;
RETURN NEXT rc_3;

Regards,

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[Rajan Pandey]

Hi, please ignore my last email about the patch. :/

I have determined that pushdown of custom data types through fdw in fact
does make sense.

Consider this query:
SELECT * FROM customers WHERE 
 ;

To push down the WHERE clause to the foreign servers, both local and
foreign server must have the user_defined database objects (else the query
won't work on the foreign servers).
For built-in types, operators, functions, Postgres knows that pushdown is
safe as the foreign servers the db objects. But for the user_defined db
objects, postgres won't know. Hence, user_defined_type_col needs to be
pushed down explicitly.


On Tue, Apr 9, 2024 at 8:06 PM Rajan Pandey 
wrote:

> Hi team 😃.
>
> In the
> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
>  doc, it
> mentions that Immutable Functions and Hi can be pushed down using
> `extensions` option for foreign server.
>
> But it does not mention TYPE. In the shippable.c
> /lookup_shippable()
> function, I found a comment that indicates that type is also pushed down.
> Hece, I have removed TYPE from the comments, assuming that it does not make
> sense to push down a data type, and only functions and operations can be
> pushed down. Please let me know if my assumption is incorrect. 🙂
>
> I have added my patch file with the mail. Thanks!
>
> On Tue, Apr 9, 2024 at 4:06 PM Laurenz Albe 
> wrote:
>
>> On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote:
>> > I was reading
>> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
>> > and found that it mentions that Immutable Functions and Operators can
>> > be pushed down using `extensions` option for foreign server.
>> >
>> > But it does not mention about TYPE. In
>> the shippable.c/lookup_shippable()
>> > function, I found that type is also pushed down.
>>
>> The comment only says that data types may be shippable, but not that
>> they are actually shipped.  Can you think of a case where a data type
>> would be shipped to a foreign server?  I wrote a foreign data wrapper,
>> and I cannot think of such a case.
>>
>> Perhaps the function comment should be adjusted by removing the
>> parenthesis
>> or changing it to "(operator/function/...)".
>>
>> > Does this require updating the docs? Can I raise a PR to do so? Thank
>> you! :)
>>
>> You would send a patch against the "master" branch to the pgsql-docs list
>> for that.
>>
>> Yours,
>> Laurenz Albe
>>
>
>
> --
> Regards
> Rajan Pandey
>


-- 
Regards
Rajan Pandey

Re: (When) can a single SQL statement return multiple result sets?

[Tom Lane]

Thomas Kellerer  writes:
> Tom Lane schrieb am 11.04.2024 um 01:02:
>> Jan Behrens  writes:
>>> While writing a PostgreSQL client library for Lua supporting
>>> Pipelining (using PQsendQueryParams), I have been wondering if there
>>> are any single SQL commands that return multiple result sets.

>> Right now, I don't think so.

> Hmm, what about functions returning multiple refcursors?

Sure, but let's distinguish between "here's an ugly workaround"
and "it just works".  Aside from being tedious, the refcursor
approach is restrictive: I don't think you can readily make
a refcursor on the result of INSERT/UPDATE/DELETE RETURNING,
nor on utility statements such as EXPLAIN.  (There might be
a way around the former restriction with WITH, but I'm
certain that won't work for EXPLAIN.)

regards, tom lane

Re: subquery plan rows = 1, but it's merge joined instead of index lookup

[Laurenz Albe]

On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote:
> Is there some complexity limit after which the planner starts acting dumb?

Yes, "join_collapse_limit" and "from_collapse_limit".
You can try increasing them.

Yours,
Laurenz Albe

Question on trigger

[veem v]

Hi, We used to use Oracle database in which we had audit triggers(something
as below) mandated for all tables by the control team. Now we are going to
use the postgresql 15.4 database for one of our applications. So,wanted to
understand if there exists any downside of such audit trigger setup for all
the tables? Will it impact the bulk data insert/update/delete OR slowdown
of any of the DML operations significantly (and thus will not be advisable
to use for all tables but selected ones)?

CREATE OR REPLACE TRIGGER TAB_AUD_TRG
  BEFORE DELETE OR INSERT OR UPDATE
  ON tab
  FOR EACH ROW
BEGIN
  IF inserting THEN
:NEW.create_timestamp := systimestamp;
:NEW.create_userid  := sys_context('USERENV','SESSION_USER');
:NEW.update_timestamp := systimestamp;
:NEW.update_userid := sys_context('USERENV','SESSION_USER');
  ELSIF updating THEN
IF  updating('create_userid') OR updating('create_timestamp') THEN
:new.create_userid   := :old.create_userid;
:new.create_timestamp  := :old.create_timestamp;
END IF;
:NEW.update_timestamp := systimestamp;
:NEW.update_userid := sys_context('USERENV','SESSION_USER');
  END IF;
  END;
/

Regards
Veem

Re: subquery plan rows = 1, but it's merge joined instead of index lookup

[Ilya Basin]

Yes! "set join_collapse_limit = 9" was enough to fix the plan, thanks Laurenz.
I will set both to 11.


 Original Message 
From: Laurenz Albe [mailto:laurenz.a...@cybertec.at]
Sent: Thursday, April 11, 2024 at 14:21 UTC
To: ilya Basin; pgsql-general@lists.postgresql.org
Subject: subquery plan rows = 1, but it's merge joined instead of index lookup

On Thu, 2024-04-11 at 15:57 +0300, ilya Basin wrote:
Is there some complexity limit after which the planner starts acting dumb?

Yes, "join_collapse_limit" and "from_collapse_limit".
You can try increasing them.

Yours,
Laurenz Albe

Re: Question on trigger

[Adrian Klaver]

On 4/11/24 07:31, veem v wrote:
Hi, We used to use Oracle database in which we had audit 
triggers(something as below) mandated for all tables by the control 
team. Now we are going to use the postgresql 15.4 database for one of 
our applications. So,wanted to understand if there exists any downside 
of such audit trigger setup for all the tables? Will it impact the bulk 
data insert/update/delete OR slowdown of any of the DML operations 
significantly (and thus will not be advisable to use for all tables but 
selected ones)?


Triggers are overhead in Postgres as they where in Oracle. If they 
didn't cause an issue in Oracle I would suspect that would also be the 
case in Postgres. To confirm you would need to create a test setup and 
run some common operations and see what the overhead is.


Some potential performance improvements:

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

"...a trigger that is marked FOR EACH STATEMENT only executes once for 
any given operation, regardless of how many rows it modifies (in 
particular, an operation that modifies zero rows will still result in 
the execution of any applicable FOR EACH STATEMENT triggers)."


<...>

"The REFERENCING option enables collection of transition relations, 
which are row sets that include all of the rows inserted, deleted, or 
modified by the current SQL statement. This feature lets the trigger see 
a global view of what the statement did, not just one row at a time. 
This option is only allowed for an AFTER trigger that is not a 
constraint trigger; also, if the trigger is an UPDATE trigger, it must 
not specify a column_name list. OLD TABLE may only be specified once, 
and only for a trigger that can fire on UPDATE or DELETE; it creates a 
transition relation containing the before-images of all rows updated or 
deleted by the statement. Similarly, NEW TABLE may only be specified 
once, and only for a trigger that can fire on UPDATE or INSERT; it 
creates a transition relation containing the after-images of all rows 
updated or inserted by the statement."



As example:

https://www.postgresql.org/docs/current/plpgsql-trigger.html

Example 43.7. Auditing with Transition Tables



CREATE OR REPLACE TRIGGER TAB_AUD_TRG
   BEFORE DELETE OR INSERT OR UPDATE
   ON tab
   FOR EACH ROW
BEGIN
       IF inserting THEN
         :NEW.create_timestamp := systimestamp;
         :NEW.create_userid  := sys_context('USERENV','SESSION_USER');
         :NEW.update_timestamp := systimestamp;
         :NEW.update_userid := sys_context('USERENV','SESSION_USER');
       ELSIF updating THEN
         IF  updating('create_userid') OR updating('create_timestamp') THEN
             :new.create_userid   := :old.create_userid;
             :new.create_timestamp  := :old.create_timestamp;
         END IF;
         :NEW.update_timestamp := systimestamp;
         :NEW.update_userid := sys_context('USERENV','SESSION_USER');
       END IF;
   END;
/

Regards
Veem


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