Re: ERROR: stack depth limit exceeded

[Laurenz Albe]

On Wed, 2025-02-19 at 16:49 +0100, Csányi Pál wrote:
> the trigger function definition is like:
> [...]
> INSERT INTO
>     HAVONKENTI_MEGTAKARITASAINK (EZEN_IDOKOZBEN, MEGTAKARITVA_RSD, KELT)
> [...]
> 
> and the trigger definition is like:
> CREATE OR REPLACE TRIGGER uj_nyugdij_beirasakor_kiold
>     AFTER INSERT
>     ON public.javaink_forgalma
>     FOR EACH ROW
>     EXECUTE FUNCTION public.ket_nyugdij_kozotti_megtakaritasaink();

That does not look circular right away, but perhaps there is
a trigger on HAVONKENTI_MEGTAKARITASAINK, or perhaps there is
some other trigger on javaink_forgalma.

It must be something of that kind.

Yours,
Laurenz Albe

-- 

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, 
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder 
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich 
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are 
confidential and may be privileged or otherwise protected from disclosure 
and solely for the use of the person(s) or entity to whom it is intended. 
If you have received this message in error and are not the intended 
recipient, please notify the sender immediately and delete this message and 
any attachment from your system. If you are not the intended recipient, be 
advised that any use of this message is prohibited and may be unlawful, and 
you must not copy this message or attachment or disclose the contents to 
any other person.

Re: Logical decoding

[Tomas Vondra]

On 2/20/25 06:04, Jethish Jethish wrote:
> Hi everyone,
> 
> Is there is any option to perform logical decoding on an active
> replication slot.
> I'm trying to decode a replication slot but it throughs an error as below.
> 
> ERROR: replication slot "my_sub" is active for PID 2525720

No, not really. The whole point of this error is to prevent exactly
this, pretty much - to only allow a single process doing logical
decoding on a slot.

What are you trying to solve / achieve? Why do you need (or think you
need) logical decoding on an active slot?


regards

-- 
Tomas Vondra

Re: v18 virtual columns

[Ron Johnson]

On Thu, Feb 20, 2025 at 8:07 AM Dominique Devienne 
wrote:

> Hi. I've just read
> https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/
>
> and I'm wondering whether there will be a way to ALTER existing STORED
> generated columns, to be virtual? W/o rewriting the whole table that is.
>

What about dropping the existing stored column then adding the new virtual
column?

You won't get any disk space saving for existing records, but it's not
going to rewrite the table.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: v18 virtual columns

[Dominique Devienne]

On Thu, Feb 20, 2025 at 3:31 PM Ron Johnson  wrote:

> On Thu, Feb 20, 2025 at 8:07 AM Dominique Devienne 
> wrote:
>
>> Hi. I've just read
>> https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/
>>
>> and I'm wondering whether there will be a way to ALTER existing STORED
>> generated columns, to be virtual? W/o rewriting the whole table that is.
>>
>
> What about dropping the existing stored column then adding the new virtual
> column?
>
> You won't get any disk space saving for existing records, but it's not
> going to rewrite the table.
>

Didn't think of that, but yes, I think that would work.
They are GENERATED columns, so the values stored are derived data anyway,
indeed.

I also didn't know DROP COLUMN was smart enough not rewriting the column,
that's good.

Thanks, --DD

#XX000: ERROR: tuple concurrently updated

[Dominique Devienne]

Hi. A tester just tried to restore two custom backups (not official
PostgreSQL ones) concurrently.
The restore process creates a new schema, some roles,and does some
role-to-role grants, before loading the data.

The two restores are independent, i.e. create different non-overlapping
schemas.
But both are "registered" in the same pre-existing schema, SCH1.
And both run within a single transaction, that does everything, DDLs and
DMLs.

The first session was doing a longer bigger restore, and was started first.
While the second session was doing a faster shorter one, and started a few
seconds later, concurrently.

The second session completed OK.
But the first session errors out with:

Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
"SCH2:RW", "SCH2:SU": #XX000: ERROR:  tuple concurrently updated

Thus I'm trying to understand what's going on.
As noted above, SCH1 is the pre-existing schema, while SCH2 is the new one
created by the restore.

The second session that completes normally did a `GRANT USAGE ON SCHEMA
"SCH1", "SCH3" TO "SCH3:RO", "SCH3:RW", "SCH3:SU"` within its own
transaction.

When I research #XX000, it often mentions doing a SELECT FOR UPDATE, but
here I'm doing GRANTs, so I don't think that applies.

Is the issue related to trying to change SCHEMA ACLs for SCH1 concurrently,
in two long running transactions? How am I supposed to resolve this?

I have to confess being a little surprised and blindsided by this issue.
Help understanding what's going on, and how to fix it, would be greatly
appreciated.

Thanks, --DD

Re: #XX000: ERROR: tuple concurrently updated

[Dominique Devienne]

On Thu, Feb 20, 2025 at 4:27 PM Tom Lane  wrote:

> Dominique Devienne  writes:
> > Hi. A tester just tried to restore two custom backups (not official
> > PostgreSQL ones) concurrently.
> > ...
> > The second session completed OK.
> > But the first session errors out with:
>
> > Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
> > "SCH2:RW", "SCH2:SU": #XX000: ERROR:  tuple concurrently updated
>
> > Thus I'm trying to understand what's going on.
>
> Since both restores tried to grant some permissions on SCH1, they
> both had to update SCH1's pg_namespace row (specifically nspacl).
> We have no support for concurrent updates in the catalog-manipulation
> code, so if the second run arrives at that step before the first
> one has committed its pg_namespace change, you get this error.
>

Thanks for confirming Tom.


> > Is the issue related to trying to change SCHEMA ACLs for SCH1
> concurrently,
> > in two long running transactions? How am I supposed to resolve this?
>
> The window is probably too small to hit if each restore is committing
> as it goes, but if you run in --single-transaction mode then this
> isn't surprising.  I'd say don't try to run concurrent restores.
>

First, I'm not in psql, but my own code. And as mentioned, I'm already in
the equivalent,
doing everything (DDLs and DMLs) in a single transaction.

Second, not doing concurrent restores is not an option.

So I need to separate operations related to SCH2,3,..., which are all
independent,
and SCH1 which is shared, as separate long running and short transactions,
respectively.
With perhaps some retry logic on the SCH1, just in case. --DD

DROP ROLE as SUPERUSER

[Dominique Devienne]

Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
did nothing, even with CASCADE, when I was running it as SUPERUSER,
preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do
the REVOKE, which DID something this time, and then I could DROP the role.

That's hardly convenient :). And I was helping someone else who couldn't
figure out how to drop that role. Isn't there a better way?

I thought SUPERUSER was more powerful that than. Why isn't it?

Thanks, --DD

Re: DROP ROLE as SUPERUSER

[David G. Johnston]

On Thursday, February 20, 2025, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, February 20, 2025, Dominique Devienne 
> wrote:
>
>> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
>> did nothing, even with CASCADE, when I was running it as SUPERUSER,
>> preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do
>> the REVOKE, which DID something this time, and then I could DROP the role.
>>
>> That's hardly convenient :). And I was helping someone else who couldn't
>> figure out how to drop that role. Isn't there a better way?
>>
>> I thought SUPERUSER was more powerful that than. Why isn't it?
>>
>
> This has nothing to do with power/permissions.  It is about not specifying
> “granted by” in your SQL command and thus failing to fully and correctly
> specify the single permission you want to revoke.
>

Well, not “single permission” but the ALL only applies to the permission
types, not actually everything for all grantors.

David J.

Re: DROP ROLE as SUPERUSER

[Tom Lane]

"David G. Johnston"  writes:
> On Thursday, February 20, 2025, Dominique Devienne 
> wrote:
>> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
>> did nothing, even with CASCADE, when I was running it as SUPERUSER,
>> preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do
>> the REVOKE, which DID something this time, and then I could DROP the role.

> This has nothing to do with power/permissions.  It is about not specifying
> “granted by” in your SQL command and thus failing to fully and correctly
> specify the single permission you want to revoke.

It used to be that if a superuser issued GRANT/REVOKE, the operation
was silently done as the owner of the affected object.  That was
always a bit of a wart, since among other things it meant that the
object owner could undo it.  Now you have to say "GRANTED BY "
to get that effect.  I'm not entirely sure, but I think this is closer
to what the SQL standard says.

regards, tom lane

Re: #XX000: ERROR: tuple concurrently updated

[Greg Sabino Mullane]

Since you are willing to break the all one transaction rule, and if the
restores were created via pg_dump, you could use the --section argument to
split things up, run the "pre-data" sections serially, and the rest ("data"
and "post-data") concurrently.

-- 
Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: v18 virtual columns

[Adrian Klaver]

On 2/20/25 05:06, Dominique Devienne wrote:
Hi. I've just read 
https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/ 


and I'm wondering whether there will be a way to ALTER existing STORED 
generated columns, to be virtual? W/o rewriting the whole table that is.


Nothing that I see here:

https://www.postgresql.org/docs/devel/sql-altertable.html



Thanks, --DD


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

Re: DROP ROLE as SUPERUSER

[Dominique Devienne]

On Thu, Feb 20, 2025 at 5:05 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Thursday, February 20, 2025, Dominique Devienne 
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> did nothing, even with CASCADE, when I was running it as SUPERUSER,
> >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the
> GRANTOR, do
> >> the REVOKE, which DID something this time, and then I could DROP the
> role.
>
> > This has nothing to do with power/permissions.  It is about not
> specifying
> > “granted by” in your SQL command and thus failing to fully and correctly
> > specify the single permission you want to revoke.
>
> It used to be that if a superuser issued GRANT/REVOKE, the operation
> was silently done as the owner of the affected object.  That was
> always a bit of a wart, since among other things it meant that the
> object owner could undo it.  Now you have to say "GRANTED BY "
> to get that effect.  I'm not entirely sure, but I think this is closer
> to what the SQL standard says.
>

I wasn't aware of GRANTED BY, thanks for that.

But that's not much better. It's basically like the SET ROLE to the GRANTOR
I did.
I guess what I want is GRANTED BY ANYONE! And not have to figure out
GRANTOR(s).

Also, note that GRANTOR is not even the owner of the DATABASE in my case.
--DD

Re: #XX000: ERROR: tuple concurrently updated

[Tom Lane]

Dominique Devienne  writes:
> Hi. A tester just tried to restore two custom backups (not official
> PostgreSQL ones) concurrently.
> ...
> The second session completed OK.
> But the first session errors out with:

> Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
> "SCH2:RW", "SCH2:SU": #XX000: ERROR:  tuple concurrently updated

> Thus I'm trying to understand what's going on.

Since both restores tried to grant some permissions on SCH1, they
both had to update SCH1's pg_namespace row (specifically nspacl).
We have no support for concurrent updates in the catalog-manipulation
code, so if the second run arrives at that step before the first
one has committed its pg_namespace change, you get this error.

> Is the issue related to trying to change SCHEMA ACLs for SCH1 concurrently,
> in two long running transactions? How am I supposed to resolve this?

The window is probably too small to hit if each restore is committing
as it goes, but if you run in --single-transaction mode then this
isn't surprising.  I'd say don't try to run concurrent restores.

regards, tom lane

Re: DROP ROLE as SUPERUSER

[David G. Johnston]

On Thu, Feb 20, 2025 at 9:05 AM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Thursday, February 20, 2025, Dominique Devienne 
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> did nothing, even with CASCADE, when I was running it as SUPERUSER,
> >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the
> GRANTOR, do
> >> the REVOKE, which DID something this time, and then I could DROP the
> role.
>
> > This has nothing to do with power/permissions.  It is about not
> specifying
> > “granted by” in your SQL command and thus failing to fully and correctly
> > specify the single permission you want to revoke.
>
> It used to be that if a superuser issued GRANT/REVOKE, the operation
> was silently done as the owner of the affected object.
>
That is still the case according to the docs (REVOKE):

"If a superuser chooses to issue a GRANT or REVOKE command, the command is
performed as though it were issued by the owner of the affected object."

The docs seem to be missing reasonable exposition regarding "granted by".
The clause isn't even formally mentioned on the page; though I suppose it
is because it is delegated to the GRANT page specification.  Though the
description there says it is basically an ignored compatibility clause -
not something that a superuser can use to make things more explicit than
using SET ROLE (not sure if it can ATM...).

David J.

Re: DROP ROLE as SUPERUSER

[David G. Johnston]

On Thursday, February 20, 2025, Dominique Devienne 
wrote:

> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> did nothing, even with CASCADE, when I was running it as SUPERUSER,
> preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do
> the REVOKE, which DID something this time, and then I could DROP the role.
>
> That's hardly convenient :). And I was helping someone else who couldn't
> figure out how to drop that role. Isn't there a better way?
>
> I thought SUPERUSER was more powerful that than. Why isn't it?
>

This has nothing to do with power/permissions.  It is about not specifying
“granted by” in your SQL command and thus failing to fully and correctly
specify the single permission you want to revoke.

David J.

Re: DROP ROLE as SUPERUSER

[David G. Johnston]

On Thu, Feb 20, 2025 at 9:21 AM Dominique Devienne 
wrote:

> But that's not much better. It's basically like the SET ROLE to the
> GRANTOR I did.
> I guess what I want is GRANTED BY ANYONE! And not have to figure out
> GRANTOR(s).
>

Your stated use case is dropping a role.  Does the combination of reassign
and drop owned not fulfill the requirements?

https://www.postgresql.org/docs/current/role-removal.html

Also, you had to know the role you wanted to drop so you already figured
out the grantor.

David J.

Re: DROP ROLE as SUPERUSER

[Tom Lane]

"David G. Johnston"  writes:
> On Thu, Feb 20, 2025 at 9:05 AM Tom Lane  wrote:
>> It used to be that if a superuser issued GRANT/REVOKE, the operation
>> was silently done as the owner of the affected object.

> That is still the case according to the docs (REVOKE):

[ scratches head ... ]  I thought we had changed that, but nope I'm wrong:

regression=# create user alice;
CREATE ROLE
regression=# create user bob;
CREATE ROLE
regression=# \c - alice
You are now connected to database "regression" as user "alice".
regression=> create table alices_table (f1 int);
CREATE TABLE
regression=> grant select on alices_table to bob;
GRANT
regression=> \dp alices_table
  Access privileges
 Schema | Name | Type  |  Access privileges   | Column privileges | 
Policies 
+--+---+--+---+--
 public | alices_table | table | alice=arwdDxtm/alice+|   | 
|  |   | bob=r/alice  |   | 
(1 row)

regression=> \c - postgres
You are now connected to database "regression" as user "postgres".
regression=# grant update on alices_table to bob;
GRANT
regression=# \dp alices_table
  Access privileges
 Schema | Name | Type  |  Access privileges   | Column privileges | 
Policies 
+--+---+--+---+--
 public | alices_table | table | alice=arwdDxtm/alice+|   | 
|  |   | bob=rw/alice |   | 
(1 row)

regression=# revoke update on alices_table from bob;
REVOKE
regression=# \dp alices_table
  Access privileges
 Schema | Name | Type  |  Access privileges   | Column privileges | 
Policies 
+--+---+--+---+--
 public | alices_table | table | alice=arwdDxtm/alice+|   | 
|  |   | bob=r/alice  |   | 
(1 row)

regression=# revoke select on alices_table from bob;
REVOKE
regression=# \dp alices_table
  Access privileges
 Schema | Name | Type  |  Access privileges   | Column privileges | 
Policies 
+--+---+--+---+--
 public | alices_table | table | alice=arwdDxtm/alice |   | 
(1 row)

So grants and revokes are still being done as the object owner by
default.

Now I'm unclear on exactly what was happening in Dominique's case.
Was the problematic permission granted by somebody other than the
database's owner?

regards, tom lane

v18 virtual columns

[Dominique Devienne]

Hi. I've just read
https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/

and I'm wondering whether there will be a way to ALTER existing STORED
generated columns, to be virtual? W/o rewriting the whole table that is.

Thanks, --DD