any wait event for a commit in sync replication?

[qihua wu]

We are using sync replication, if a session runs an insert, and then
commit, the client is actually waiting for commit to complete, but looks
like this wait doesn't show in pg_stat_activity.

In one session I inserted a row (auto commit), it hangs there.

example=# insert into test_timteout select '1';
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have
been replicated to the standby.
INSERT 0 1
example=# select pg_backend_pid();
 pg_backend_pid

  19325
(1 row)


During the hung period.
postgres=# select * from pg_stat_activity where pid=19325;
(0 rows)

postgres=#


If there is no event for such commit, anyway to find out such session which
pending on commit(sync replication)

Re: How is timeout implemented in postgresql?

[David G. Johnston]

On Mon, Jan 30, 2023 at 6:36 PM qihua wu  wrote:

> I tried to find out how timeout, such as statement_timeout,  works in
> source code, But there are so many places it appears in the code, anybody
> could show me some clue about where is the entry point for timeout
> implementation?
>

https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/timeout.c

David J.

How is timeout implemented in postgresql?

[qihua wu]

I tried to find out how timeout, such as statement_timeout,  works in
source code, But there are so many places it appears in the code, anybody
could show me some clue about where is the entry point for timeout
implementation?

Re: Sequence vs UUID

[Ron]

And populate that column with UUIDs generated by the gen_random_uuid() function.

(Requires v13.)

On 1/30/23 13:46, Adrian Klaver wrote:

On 1/30/23 11:43, veem v wrote:
Thank You So much for the details. I am a bit new to postgres. And these 
test results I picked were from a dev system. If I understand it 
correctly, do you mean these settings(usage of C locale or "native" 
16-byte uuid) which you mentioned should be there in a production system 
  and thus we should test the performance of the UUID vs sequence on a 
similar setup? Or say if this sort of degradation of UUID performance is 
not expected then , how to get these settings tweaked ON, so as to see 
the best string type or UUID performance, can you please guide me here?


No what is being said is change:

source_id varchar(36)

to

source_id uuid

as i:

https://www.postgresql.org/docs/current/datatype-uuid.html



On Mon, 30 Jan 2023 at 22:18, Tom Lane > wrote:


    Dominique Devienne mailto:ddevie...@gmail.com>> writes:
 > On Mon, Jan 30, 2023 at 5:11 PM veem v mailto:veema0...@gmail.com>> wrote:
 >> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36)
    PRIMARY KEY, Name varchar(20) );

 > Maybe if you used a "native" 16-byte uuid, instead of its textual
 > serialization with dashes (36 bytes + length overhead), the gap would
 > narrow.

    Yeah, especially if your database is not using C locale. The
    strcoll or ICU-based comparisons done on string types can be
    enormously more expensive than the memcmp() used for binary
    types like native uuid.

                         regards, tom lane





--
Born in Arizona, moved to Babylonia.

Re: Sequence vs UUID

[Adrian Klaver]

On 1/30/23 11:43, veem v wrote:
Thank You So much for the details. I am a bit new to postgres. And these 
test results I picked were from a dev system. If I understand it 
correctly, do you mean these settings(usage of C locale or "native" 
16-byte uuid) which you mentioned should be there in a production system 
  and thus we should test the performance of the UUID vs sequence on a 
similar setup? Or say if this sort of degradation of UUID performance is 
not expected then , how to get these settings tweaked ON, so as to see 
the best string type or UUID performance, can you please guide me here?


No what is being said is change:

source_id varchar(36)

to

source_id uuid

as i:

https://www.postgresql.org/docs/current/datatype-uuid.html



On Mon, 30 Jan 2023 at 22:18, Tom Lane > wrote:


Dominique Devienne mailto:ddevie...@gmail.com>> writes:
 > On Mon, Jan 30, 2023 at 5:11 PM veem v mailto:veema0...@gmail.com>> wrote:
 >> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36)
PRIMARY KEY, Name varchar(20) );

 > Maybe if you used a "native" 16-byte uuid, instead of its textual
 > serialization with dashes (36 bytes + length overhead), the gap would
 > narrow.

Yeah, especially if your database is not using C locale.  The
strcoll or ICU-based comparisons done on string types can be
enormously more expensive than the memcmp() used for binary
types like native uuid.

                         regards, tom lane



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

Re: Sequence vs UUID

[veem v]

Thank You So much for the details. I am a bit new to postgres. And these
test results I picked were from a dev system. If I understand it correctly,
do you mean these settings(usage of C locale or "native" 16-byte uuid)
which you mentioned should be there in a production system  and thus we
should test the performance of the UUID vs sequence on a similar setup? Or
say if this sort of degradation of UUID performance is not expected then ,
how to get these settings tweaked ON, so as to see the best string type or
UUID performance, can you please guide me here?

On Mon, 30 Jan 2023 at 22:18, Tom Lane  wrote:

> Dominique Devienne  writes:
> > On Mon, Jan 30, 2023 at 5:11 PM veem v  wrote:
> >> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) PRIMARY KEY,
> Name varchar(20) );
>
> > Maybe if you used a "native" 16-byte uuid, instead of its textual
> > serialization with dashes (36 bytes + length overhead), the gap would
> > narrow.
>
> Yeah, especially if your database is not using C locale.  The
> strcoll or ICU-based comparisons done on string types can be
> enormously more expensive than the memcmp() used for binary
> types like native uuid.
>
> regards, tom lane
>

Re: How could I elog the tupleTableSlot to the fronted terminal?

[Alvaro Herrera]

On 2023-Jan-30, jack...@gmail.com wrote:

> For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot,
> 
> And Now I want to get the real data , that's 1, and then use elog() func
> to print it. Could you give me some codes to realize that? futhermore,
> what If the data type is text or other types? What do I need to change?

Maybe have a look at the 'debugtup()' function.  It doesn't do exactly
what you want, but it may inspire you to write the code you need.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"All rings of power are equal,
But some rings of power are more equal than others."
 (George Orwell's The Lord of the Rings)

Re: Sequence vs UUID

[Tom Lane]

Dominique Devienne  writes:
> On Mon, Jan 30, 2023 at 5:11 PM veem v  wrote:
>> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) PRIMARY KEY, Name 
>> varchar(20) );

> Maybe if you used a "native" 16-byte uuid, instead of its textual
> serialization with dashes (36 bytes + length overhead), the gap would
> narrow.

Yeah, especially if your database is not using C locale.  The
strcoll or ICU-based comparisons done on string types can be
enormously more expensive than the memcmp() used for binary
types like native uuid.

regards, tom lane

Re: Sequence vs UUID

[Dominique Devienne]

On Mon, Jan 30, 2023 at 5:11 PM veem v  wrote:
> CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) PRIMARY KEY, Name 
> varchar(20) );

Maybe if you used a "native" 16-byte uuid, instead of its textual
serialization with dashes (36 bytes + length overhead), the gap would
narrow.

Re: Sequence vs UUID

[veem v]

Was trying to test the performance for simple read/write for the bigint vs
UUID. What we see is , ~3 times performance degradation while joining on
bigint vs UUID columns. Also even just generation of sequence vs bigint
itself is degrading by ~3times too. Also even insert performance on same
table for ~10million rows is ~1min 39sec for bigint vs ~3minute 11 sec in
case of UUID. Is such extent of degradation in performance this expected
for UUID?

CREATE TABLE test1_UUID ( id bigint,source_id varchar(36) PRIMARY KEY, Name
varchar(20) );
CREATE TABLE test2_UUID (id bigint,source_id varchar(36) PRIMARY KEY,Name
varchar(20) );

CREATE TABLE test1_bigint ( id bigint PRIMARY KEY, source_id varchar(36) ,
Name varchar(20));
CREATE TABLE test2_bigint ( id bigint PRIMARY KEY, source_id varchar(36) ,
Name varchar(20));

Loaded same 10million rows.

explain Analyze select * from test1_bigint a , test2_bigint b where a.id =
b.id

Merge Join (cost=12.31..875534.52 rows=1021 width=100) (actual
time=0.042..6974.575 rows=1000 loops=1)
  Merge Cond: (a.id = b.id)
  -> Index Scan using test1_bigint_pkey on test1_bigint a
(cost=0.43..362780.75 rows=1021 width=50) (actual time=0.020..2070.079
rows=1000 loops=1)
  -> Index Scan using test2_bigint_2_pkey on test2_bigint b
(cost=0.43..362780.75 rows=1021 width=50) (actual time=0.019..2131.086
rows=1000 loops=1)
Planning Time: 0.207 ms
Execution Time: 7311.210 ms

set enable_seqscan=off;

explain Analyze select * from test1_UUID a , test2_UUID b where a.source_id
= b.source_id;

Merge Join (cost=2.75..2022857.05 rows=1021 width=100) (actual
time=0.043..21954.213 rows=1000 loops=1)
  Merge Cond: ((a.source_id)::text = (b.source_id)::text)
  -> Index Scan using test1_uuid_pkey on test1_UUID a (cost=0.56..936420.18
rows=1021 width=50) (actual time=0.022..7854.143 rows=1000 loops=1)
  -> Index Scan using test2_uuid_2_pkey on test2_UUID b
(cost=0.56..936437.90 rows=1021 width=50) (actual time=0.017..7971.187
rows=1000 loops=1)
Planning Time: 0.516 ms
Execution Time: 22292.801 ms

**

create sequence myseq cache 32767;

select count(nextval('myseq') ) from generate_series(1,1000)
 1 row retrieved starting from 1 in 4 s 521 ms (execution: 4 s 502 ms,
fetching: 19 ms)

 select count(gen_random_uuid()) from generate_series(1,1000)
 1 row retrieved starting from 1 in 11 s 145 ms (execution: 11 s 128 ms,
fetching: 17 ms)



On Mon, 30 Jan, 2023, 4:59 pm veem v,  wrote:

> I have a question, As i understand here, usage wise there are multiple
> benefits of UUID over sequences like, in case of distributed app where we
> may not be able to rely on one point generator like sequences, in case of
> multi master architecture, sharding.
>
> If we just look in terms of performance wise, the key advantage of
> sequence is that for read queries, because of the storage size it will be
> smaller and thus it will cache more index rows and so will be beneficial
> during read queries and should also be beneficial even on joins because of
> its smaller size. Also fetching a value from sequence is cheaper than
> calculating the UUIDS. But the downside is during write operation, it can
> be a point of contention in case of concurrent data load as every incoming
> request will try to modify same table/index page/block. But as its
> mentioned in this blog (
> https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/), state
> this UUID can be made sequential so even this can be sequential using
> prefix-timestamp etc. However isn't it that making the UUID sequential will
> again actually be a disadvantage and can be contention point for this
> unique index as each incoming write will now fight for same block/page
> while doing concurrent data load and will contend for the same table block
> or say one side of the index branch/leaf block etc, whereas in case of
> random UUIDs the write was spreading across multiple blocks so there was no
> contention on any specific blocks? Please correct if my understanding is
> wrong?
>
>
>
> On Sun, 29 Jan, 2023, 10:33 am Miles Elam, 
> wrote:
>
>> On Sat, Jan 28, 2023 at 8:02 PM Ron  wrote:
>> >
>> > Then it's not a Type 4 UUID, which is perfectly fine; just not random.
>>
>> Yep, which is why it really should be re-versioned to UUIDv8 to be
>> pedantic. In everyday use though, almost certainly doesn't matter.
>>
>> > Also, should now() be replaced by clock_timestamp(), so that it can be
>> > called multiple times in the same transaction?
>>
>> Not necessary. Instead of 122 bits of entropy, you get 106 bits of
>> entropy and a new incremented prefix every minute. now() vs
>> clock_timestamp() wouldn't make a substantive difference. Should still
>> be reasonably safe against the birthday paradox for more than a
>> century when creating more than a million UUIDs per second.
>>
>>
>>

How could I elog the tupleTableSlot to the fronted terminal?

[jack...@gmail.com]

For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot,

And Now I want to get the real data , that's 1, and then use elog() func
to print it. Could you give me some codes to realize that? futhermore,
what If the data type is text or other types? What do I need to change?
--
jack...@gmail.com

Re: alter table impact on view

[Marc Millas]

A bit sad

Thanks..

Le lun. 30 janv. 2023 à 13:53, David Rowley  a écrit :

> On Tue, 31 Jan 2023 at 01:14, Marc Millas  wrote:
> > But if I alter table to change a column that is a varchar 20 into a
> varchar 21
> > postgres refuse saying that it cannot due to the return rule... using
> said column
> >
> > why ?? as the view is not a materialized object, the impact of the
> length of a column of an underlying table do change the description of the
> view, clearly, but I dont see where the difficulty is "hidden". Can someone
> enlighten me?
>
> Primarily because nobody has written the required code.
>
> In [1], which is now quite old, there was some discussion about
> various aspects of making this better. Perhaps changing the typmod is
> easier than changing the type completely, but we still don't have any
> code for it. So for now, you're just stuck manually dropping and
> recreating your views.
>
> David
>
> [1]
> https://www.postgresql.org/message-id/603c8f070807291912x37412373q7cd7dc36dd55a...@mail.gmail.com
>

Re: Does statement_timeout apply to commit?

[Laurenz Albe]

On Mon, 2023-01-30 at 17:10 +0800, qihua wu wrote:
> I have a patroni cluster running in sync mode and at least 2 nodes should be
> synced for commit. Now I brought down one node, so only one slave is in sync,
> and then write a java program to write the data to primary in autocommit = 
> false
> mode, I set statement_timeout to 10, hopping the jave will throw exception
> after 10 seconds after I send a commit command to postgresq, but after 10 
> seconds,
> it didn't throw any exception, so looks like statement_timeout doesn't apply 
> to
> commit. Is that theory right?.

I assume you are talking about synchronous replication.

static void
CommitTransaction(void)
{
[...]

/* Prevent cancel/die interrupt while cleaning up */
HOLD_INTERRUPTS();

[...]

if (!is_parallel_worker)
{
/*
 * We need to mark our XIDs as committed in pg_xact.  This is where we
 * durably commit.
 */
latestXid = RecordTransactionCommit();
}

[...]

RESUME_INTERRUPTS();
}


static TransactionId
RecordTransactionCommit(void)
{

[...]

/*
 * Wait for synchronous replication, if required. Similar to the decision
 * above about using committing asynchronously we only want to wait if
 * this backend assigned an xid and wrote WAL.  No need to wait if an xid
 * was assigned due to temporary/unlogged tables or due to HOT pruning.
 *
 * Note that at this stage we have marked clog, but still show as running
 * in the procarray and continue to hold locks.
 */
if (wrote_xlog && markXidCommitted)
SyncRepWaitForLSN(XactLastRecEnd, true);

[...]
}

So yes, it looks like you are right.

Yours,
Laurenz Albe

Re: How to control pg_catalog results for each users?

[Ron]

On 1/30/23 02:41, Laurenz Albe wrote:

On Mon, 2023-01-30 at 14:00 +0900, hirose shigeo(廣瀬 繁雄 □SWC○ACT) wrote:

all users can get the all of table name , table structure and other
information from pg_catalog, which is considered a security problem.

The belief that restricting that will improve security goes by the name
of "security by obscurity", which is usually not considered robust.


Your description sounds a whole lot like PostgreSQL's row level security.

--
Born in Arizona, moved to Babylonia.

Re: alter table impact on view

[David Rowley]

On Tue, 31 Jan 2023 at 01:14, Marc Millas  wrote:
> But if I alter table to change a column that is a varchar 20 into a varchar 21
> postgres refuse saying that it cannot due to the return rule... using said 
> column
>
> why ?? as the view is not a materialized object, the impact of the length of 
> a column of an underlying table do change the description of the view, 
> clearly, but I dont see where the difficulty is "hidden". Can someone 
> enlighten me?

Primarily because nobody has written the required code.

In [1], which is now quite old, there was some discussion about
various aspects of making this better. Perhaps changing the typmod is
easier than changing the type completely, but we still don't have any
code for it. So for now, you're just stuck manually dropping and
recreating your views.

David

[1] 
https://www.postgresql.org/message-id/603c8f070807291912x37412373q7cd7dc36dd55a...@mail.gmail.com

alter table impact on view

[Marc Millas]

Hello,

to my understanding, if I do alter table rename column, Postgres change the
name of the said column, and modify the views using that table so that they
keep working (NOT oracle behaviour..)
fine.
But if I alter table to change a column that is a varchar 20 into a varchar
21
postgres refuse saying that it cannot due to the return rule... using said
column

why ?? as the view is not a materialized object, the impact of the length
of a column of an underlying table do change the description of the view,
clearly, but I dont see where the difficulty is "hidden". Can someone
enlighten me?

thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: Sequence vs UUID

[veem v]

I have a question, As i understand here, usage wise there are multiple
benefits of UUID over sequences like, in case of distributed app where we
may not be able to rely on one point generator like sequences, in case of
multi master architecture, sharding.

If we just look in terms of performance wise, the key advantage of sequence
is that for read queries, because of the storage size it will be smaller
and thus it will cache more index rows and so will be beneficial during
read queries and should also be beneficial even on joins because of its
smaller size. Also fetching a value from sequence is cheaper than
calculating the UUIDS. But the downside is during write operation, it can
be a point of contention in case of concurrent data load as every incoming
request will try to modify same table/index page/block. But as its
mentioned in this blog (
https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/), state
this UUID can be made sequential so even this can be sequential using
prefix-timestamp etc. However isn't it that making the UUID sequential will
again actually be a disadvantage and can be contention point for this
unique index as each incoming write will now fight for same block/page
while doing concurrent data load and will contend for the same table block
or say one side of the index branch/leaf block etc, whereas in case of
random UUIDs the write was spreading across multiple blocks so there was no
contention on any specific blocks? Please correct if my understanding is
wrong?



On Sun, 29 Jan, 2023, 10:33 am Miles Elam, 
wrote:

> On Sat, Jan 28, 2023 at 8:02 PM Ron  wrote:
> >
> > Then it's not a Type 4 UUID, which is perfectly fine; just not random.
>
> Yep, which is why it really should be re-versioned to UUIDv8 to be
> pedantic. In everyday use though, almost certainly doesn't matter.
>
> > Also, should now() be replaced by clock_timestamp(), so that it can be
> > called multiple times in the same transaction?
>
> Not necessary. Instead of 122 bits of entropy, you get 106 bits of
> entropy and a new incremented prefix every minute. now() vs
> clock_timestamp() wouldn't make a substantive difference. Should still
> be reasonably safe against the birthday paradox for more than a
> century when creating more than a million UUIDs per second.
>
>
>

Does statement_timeout apply to commit?

[qihua wu]

I have a patroni cluster running in sync mode and at least 2 nodes should
be synced for commit. Now I brought down one node, so only one slave is in
sync, and then write a java program to write the data to primary in
autocommit = false mode, I set statement_timeout to 10, hopping the jave
will throw exception after 10 seconds after I send a commit command to
postgresq, but after 10 seconds, it didn't throw any exception, so looks
like statement_timeout doesn't apply to commit. Is that theory right?.

Re: Postgres upgrade 12 - issues with OIDs

[Venkata B Nagothi]

Thanks all for the ideas, we have chosen to resolve this using Logical
Replication as we cannot use any other methods due to various constraints.

Regards,

Venkata B N
Database Consultant



On Mon, Nov 28, 2022 at 11:16 AM David Rowley  wrote:

> On Mon, 28 Nov 2022 at 12:46, Venkata B Nagothi  wrote:
> > Coming back to this thread after a while.. we have to remove OID on a 6
> TB (5 TB of indexes) table and ALTER TABLE is gonna block the table and is
> gonna take hours...
>
> You may want to look into exploiting table inheritance for this.
> Something like:
>
> create table tab (a int, b int) with oids; -- the existing table
>
> begin; -- do make the following atomic
> alter table tab rename to old_tab;
> create table tab (a int, b int) without oids; -- new version of the
> table, without oids
> alter table old_tab inherit tab; -- make it so querying the new table
> also gets rows from the old table.
> commit;
>
> -- do this a bunch of times over the course of a few days until
> old_tab is empty.
> with del as (delete from old_tab where a in (select a from old_tab
> limit 1000) returning *) insert into tab select * from del;
>
> you can then drop the old table.
>
> You'll need to think carefully about unique constraints and any other
> constraints which are on the table in question. You'll want to do a
> lot of testing before committing to doing this too.
>
> David
>

Question on Logical Replication

[Venkata B Nagothi]

I have got a question on Logical Replication - we are using PostgreSQL
version 11.

We are operating in a high transaction environment - so, would like to know
if disabling and enabling subscription is gonna miss any transactions ?

Meaning - if a subscription is disabled and enabled after a few hours, the
replication is gonna resume from the time when subscription was disabled ?
We just wanna make sure transactions do not go missing.

Regards,

Venkata B N
Database Consultant

Re: How to control pg_catalog results for each users?

[Laurenz Albe]

On Mon, 2023-01-30 at 14:00 +0900, hirose shigeo(廣瀬 繁雄 □SWC○ACT) wrote:
> all users can get the all of table name , table structure and other 
> information from pg_catalog, which is considered a security problem.

The belief that restricting that will improve security goes by the name
of "security by obscurity", which is usually not considered robust.

Yours,
Laurenz Albe