Re: event trigger clarification

[Marc Millas]

Thanks !
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Wed, Aug 30, 2023 at 8:44 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wednesday, August 30, 2023, Marc Millas  wrote:
>
>> Hi,
>> the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL 
>> commands
>> executed by each user action, when invoked in a function attached to a
>> ddl_command_end event trigger."
>> When some ddl command is executed within a block, I would like to know if
>> the event trigger fires when the line is executed or at commit time.
>>
>
> https://www.postgresql.org/docs/current/event-trigger-definition.html
>
> There is nothing there about event execution being able to be deferred.
>
> David J.
>
>
>

Re: event trigger clarification

[Marc Millas]

Thanks !
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Wed, Aug 30, 2023 at 6:18 PM Erik Wienhold  wrote:

> > On 30/08/2023 17:12 CEST Marc Millas  wrote:
> >
> > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL
> > commands executed by each user action, when invoked in a function
> attached
> > to a ddl_command_end event trigger."
> > When some ddl command is executed within a block, I would like to know
> if the
> > event trigger fires when the line is executed or at commit time.
>
> The event trigger fires just before[1]/after[2] executing a complete
> command.
> So not at commit time.
>
> [1]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/tcop/utility.c;h=6b0a8652622b26887ea2ccd15ced3300e951c5dc#l1120
> [2]
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/tcop/utility.c;h=6b0a8652622b26887ea2ccd15ced3300e951c5dc#l1923
>
> --
> Erik
>

Re: pg_visible_in_snapshot clarification

[Luca Ferrari]

On Mon, Aug 28, 2023 at 8:34 PM Mike Roest  wrote:
> I think this has to do with the pg_current_snapshot not showing the 
> transaction in the xip_list which appears to be empty on both C1 (in the 
> transaction) and C3 on the replica.  However C2 pg_current_snapshot() does 
> show C1 xactid as in progress in the xip_list.

Effectively there is something that I don't understand too.
I've reproduced the experiment, with two connections on the primary
and one on the standby (PostgreSQL 15).

First connection at the primary:

testdb=*> INSERT INTO t( t )
SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() ||
' SNAP = ' || pg_current_snapshot();
INSERT 0 1
testdb=*> SELECT * FROM t;
id |  t
+--
 8 | XID = 875 PID = 1151 SNAP = 875:875:
(1 row)


Meanwhile, second connection to the primary:

testdb=> BEGIN;
BEGIN
testdb=*> INSERT INTO t( t )
SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() ||
' SNAP = ' || pg_current_snapshot();
INSERT 0 1
testdb=*> SELECT * FROM t;
id |  t
+--
 9 | XID = 876 PID = 1200 SNAP = 875:875:
(1 row)

Meanwhile, third connection to the standby (physical replication with a slot):

% psql -U luca -h venkman -p 6432 testdb
psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1), server 15.3)
Type "help" for help.

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
+-
f  | 875:875:
(1 row)


So far so good, then commit the second connection (on the primary)
with xid 876, and on the standby:

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
+-
t  | 875:877:

Then I rollback the first connection (xid 875) and again, on the standby:

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
+-
t  | 877:877:


The latter result appears normal to me, since 875 is consolidated. But
why is 875 visible when 876 commits and 875 does not?
The same does not happen with only connections to the primary, that is
not involving the replica node. Reproducing the same experiment, the
third connections sees always a false against the first transaction
(not commit) before and after the commit of the second transactions:

testdb=> SELECT pg_visible_in_snapshot( '877'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
+-
f  | 877:879:877
(1 row)

-- second transacction 879 commits

testdb=> SELECT pg_visible_in_snapshot( '877'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
+-
f  | 877:880:877
(1 row)

What I see, however, is that the snapshot is different in the case of
local transacctions. I suspect that somehow the list of active
transactions is not propagated to the replica xip, that is therefore
forced to look into the commit status.
But I would like to get a better explanation.

Luca

pg_dump/pg_restore and the magic of the search_path

[Arthur Bazin]

Hi everyone !

I have a good question on pg_dump/pg_restore and the search_path.

Consider that we have a function in the public schema witch is named
my_function_in_public.

In PG11 this table  :
CREATE TABLE public.test_dump (
 id TEXT DEFAULT my_function_in_public()
);
When you dump this table with the pg11 binaries, you obtain this script :
CREATE TABLE public.test_dump (
 id TEXT DEFAULT public.my_function_in_public()
);
=> the schema prefix have been added to the function by pg_dump.

In PG13, the same table :
CREATE TABLE public.test_dump (
 id TEXT DEFAULT my_function_in_public()
);
When you dump this table with the pg13 binaries, you obtain this script :
CREATE TABLE public.test_dump (
 id TEXT DEFAULT my_function_in_public()
);
=> the schema prefix have not been added.

Ok I understand that there is some modifications on how the dump is
generated.

Now, if you try to restore the dump :
 - PG11 to PG11 no problem
 - PG11 (exported with dump from PG11) to PG13 : no problem
 - PG11 (exported with dump from PG13) to PG13 : no problem
 - PG13 to PG13 : no problem

=> But PG13 to PG11 : problem : the function is not find because it is not
prefixed. Seems legit.

What I don't understand is why PG13 to PG13 works ? If I look in this dump,
we can see the search path is set to '' (empty) and the function isn't
prefixed.
So how can it find where the function is ?
Does PG13 consider that when there is no prefix, we need to use "public" ?

Thank you for your lights on this.
Arthur Bazin

Re: pg_dump/pg_restore and the magic of the search_path

[Erik Wienhold]

> On 31/08/2023 17:08 CEST Arthur Bazin  wrote:
>
> Consider that we have a function in the public schema witch is named
> my_function_in_public.
>
> In PG11 this table :
> CREATE TABLE public.test_dump (
>  id TEXT DEFAULT my_function_in_public()
> );
> When you dump this table with the pg11 binaries, you obtain this script :
> CREATE TABLE public.test_dump (
>  id TEXT DEFAULT public.my_function_in_public()
> );
> => the schema prefix have been added to the function by pg_dump.
>
> In PG13, the same table :
> CREATE TABLE public.test_dump (
>  id TEXT DEFAULT my_function_in_public()
> );
> When you dump this table with the pg13 binaries, you obtain this script :
> CREATE TABLE public.test_dump (
>  id TEXT DEFAULT my_function_in_public()
> );
> => the schema prefix have not been added.

Are you sure that my_function_in_public was created in schema public on pg13?
I cannot reproduce this on 13.12.  However, I can reproduce it when creating
that function in pg_catalog instead of public.  The dump does not include
pg_catalog.my_function_in_public though.

--
Erik

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 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

-- 






















*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.