Re: Keeping state in a foreign data wrapper

[Ian Lawrence Barwick]

2020年8月4日(火) 14:54 Stelios Sfakianakis :
> On 4 Aug 2020, at 06:25, Ian Lawrence Barwick  wrote:
>
> 2020年8月4日(火) 1:24 Stelios Sfakianakis :
>
> Hi,
>
>>> I am trying to  implement a FDW in Postgres for accessing a web api and I 
>>> would like to keep information like for example the total number of 
>>> requests submiited. Ideally these data should be kept “per-user” and of 
>>> course with the proper locking to eliminate race conditions. So the 
>>> question I have is how to design such a global (but per user and database) 
>>> state, using the C FDW API of Postgres. I have gone through the API and for 
>>> example I see various fdw_private fields in the query planning structures 
>>> and callbacks but these do not seem to be relevant to my requirements. 
>>> Another possiblity seems to be to use shared memory 
>>> (https://www.postgresql.org/docs/12/xfunc-c.html#id-1.8.3.13.14) but it is 
>>> even less clear how to do it.
>
>
>> Shared memory would probably work; you'd need to load the FDW via
>> "shared_preload_libraries" and have the FDW handler function(s) update
>> shared memory with whatever statistics you want to track. You could
>> then define SQL functions to retrieve the stored values, and possibly
>> persist them over server restarts by storing/retrieving them from a
>> file.
>
>> Look at "pg_stat_statements" for a good example of how to do that kind of 
>> thing.
>
> Thank you Ian for the prompt reply! I will certainly have a look at 
> pg_stat_statements
>
> I also came across mysql_fdw (https://github.com/EnterpriseDB/mysql_fdw) that 
> features a connection pool shared across queries. It uses a hash table with 
> the serverid and userid as lookup key : 
> https://github.com/EnterpriseDB/mysql_fdw/blob/REL-2_5_4/connection.c#L55

This is essentially the same as what "postgres_fdw" and similar FDW
implementations do.

> The hash table is allocated in the cache memory context but it worries me 
> that 1) no locks are used, 2) the "ConnectionHash" variable is declared 
> static so in the multi-process architecture of Postgres could have been the 
> case that multiple copies of this exist when the shared library of mysql_fdw 
> is loaded?

The hash table is specific to each running backend so will only be
accessed by that process.

Pre-loading a shared library just gives the library an opportunity to
set up shared memory etc. You can always try adding one of the FDW
libraries to "shared_preload_libraries" and see what happens
(theoretically nothing).

Regards

Ian Barwick



>
> Best regards
> Stelios
>
>
> Regards
>
> Ian Barwick
>
> --
> Ian Barwick   https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>

Unexplained disk usage in AWS Aurora Postgres

[Chris Borckholder]

Hi!

We are experiencing a strange situation with an AWS Aurora postgres
instance.
The database steadily grows in size, which is expected and normal.
After enabling logical replication, the disk usage reported by AWS metrics
increases much faster then the database size (as seen by \l+ in psql). The
current state is that database size is ~290GB, while AWS reports >640GB
disk usage.
We reached out to AWS support of course, which is ultimately responsible.
Unfortunately they were not able to diagnose this until now.

I checked with the queries from wiki
https://wiki.postgresql.org/wiki/Disk_Usage , which essentially give the
same result.
I tried to check on wal segment file size, but we have no permission to
execute select pg_ls_waldir().
The replication slot is active and it also progresses
(pg_replication_slots.confirmed_flush_lsn increases and is close to
pg_current_wal_flush_lsn).

Can you imagine other things that I could check from within postgres with
limited permissions to diagnose this?

Best Regards
Chris

PostgreSQL transaction aborted on SQL error

[Urko Lekuona]

Hello,

First time writing here, I hope this is the right place to ask this kind of
question. I've been working with PostgreSQL for a while now but i've just
found out that PostgreSQL marks my transaction for ROLLBACK and even stops
the execution of the transaction if an error occurs.

I'm a Java developer and I'm using JDBC to connect to PostgreSQL. I've made
a gist to showcase this behavior (
https://gist.github.com/UrkoLekuona/4be14e099aeec2e7d8ef616886b66fdf/). If
you run it, you'd see that when the unique key constraint is violated, my
transaction is stopped, i.e. the SELECT and DROP statements are not
executed. The thrown exception is org.postgresql.util.PSQLException: ERROR:
current transaction is aborted, commands ignored until end of transaction
block

I've purposely set AutoCommit to false, because in my real life use case
this is not an option. The only workaround I've found for this exception is
setting the connection property *autosave* to ALWAYS, (
https://jdbc.postgresql.org/documentation/head/connect.html).

My question is: is this the correct way of solving this issue? I'd rather
if there was a PostgreSQL flag to disable this behavior and make it work
like other RDBMS do, where if a statement failed, the transaction could
continue without explicitly marking a savepoint and rolling back.

Thanks in advance for your help, it is appreciated.

Urko

Sv: PostgreSQL transaction aborted on SQL error

[Andreas Joseph Krogh]

På tirsdag 04. august 2020 kl. 10:44:36, skrev Urko Lekuona mailto:u...@arima.eu>>: 
Hello, 

First time writing here, I hope this is the right place to ask this kind of 
question. I've been working with PostgreSQL for a while now but i've just found 
out that PostgreSQL marks my transaction for ROLLBACK and even stops the 
execution of the transaction if an error occurs. 

I'm a Java developer and I'm using JDBC to connect to PostgreSQL. I've made a 
gist to showcase this behavior (
https://gist.github.com/UrkoLekuona/4be14e099aeec2e7d8ef616886b66fdf/ 
). If 
you run it, you'd see that when the unique key constraint is violated, my 
transaction is stopped, i.e. the SELECT and DROP statements are not executed. 
The thrown exception is org.postgresql.util.PSQLException: ERROR: current 
transaction is aborted, commands ignored until end of transaction block 

I've purposely set AutoCommit to false, because in my real life use case this 
is not an option. The only workaround I've found for this exception is setting 
the connection propertyautosave to ALWAYS, (
https://jdbc.postgresql.org/documentation/head/connect.html 
). 

My question is: is this the correct way of solving this issue? I'd rather if 
there was a PostgreSQL flag to disable this behavior and make it work like 
other RDBMS do, where if a statement failed, the transaction could continue 
without explicitly marking a savepoint and rolling back. 

Thanks in advance for your help, it is appreciated. 

Urko 


The correct approach is to ROLLBACK the transaction in a "catch-block" instead 
of trying to execute further statements. The java.sql.Connection is "invalid" 
after an SQLException and should be rolled back. 


--
 Andreas Joseph Krogh 

Re: PostgreSQL transaction aborted on SQL error

[Thomas Kellerer]

Urko Lekuona schrieb am 04.08.2020 um 10:44:
> First time writing here, I hope this is the right place to ask this
> kind of question. I've been working with PostgreSQL for a while now
> but i've just found out that PostgreSQL marks my transaction for
> ROLLBACK and even stops the execution of the transaction if an error
> occurs.

Which is exactly how a transaction is defined:

Either all statements are successful or none.


> If you run it, you'd see that when the unique key constraint is
> violated, my transaction is stopped

You can use INSERT ON CONFLICT DOT NOTHING to avoid that.


> make it work like other RDBMS do, where if a statement failed, the
> transaction could continue

Which completely violates the idea of a transaction.

The choices you have are:

* use auto-commit
* make sure your inserts don't throw an error
* use manual savepoints around each statement (don't forget DDL statements!)

Thomas

Re: Unexplained disk usage in AWS Aurora Postgres

[Srinivasa T N]

There may be lot of wal files or the size of log files in pg_log might be
huge.  "du -sh *" of data directory holding the database might help.

Regards,
Seenu.


On Tue, Aug 4, 2020 at 2:09 PM Chris Borckholder <
chris.borckhol...@bitpanda.com> wrote:

> Hi!
>
> We are experiencing a strange situation with an AWS Aurora postgres
> instance.
> The database steadily grows in size, which is expected and normal.
> After enabling logical replication, the disk usage reported by AWS metrics
> increases much faster then the database size (as seen by \l+ in psql). The
> current state is that database size is ~290GB, while AWS reports >640GB
> disk usage.
> We reached out to AWS support of course, which is ultimately responsible.
> Unfortunately they were not able to diagnose this until now.
>
> I checked with the queries from wiki
> https://wiki.postgresql.org/wiki/Disk_Usage , which essentially give the
> same result.
> I tried to check on wal segment file size, but we have no permission to
> execute select pg_ls_waldir().
> The replication slot is active and it also progresses
> (pg_replication_slots.confirmed_flush_lsn increases and is close to
> pg_current_wal_flush_lsn).
>
> Can you imagine other things that I could check from within postgres with
> limited permissions to diagnose this?
>
> Best Regards
> Chris
>
>
>

Re: Keeping state in a foreign data wrapper

[Stelios Sfakianakis]

Thank you again, I have another question in order to make sure I have a clear 
understanding:


> On 4 Aug 2020, at 11:24, Ian Lawrence Barwick  wrote:
> 
> The hash table is specific to each running backend so will only be
> accessed by that process.
> 
> Pre-loading a shared library just gives the library an opportunity to
> set up shared memory etc. You can always try adding one of the FDW
> libraries to "shared_preload_libraries" and see what happens
> (theoretically nothing).
> 

My impression was that since each client (e.g. libpq) connection results in the 
creation of a Postgres process in the backend 
(https://www.postgresql.org/developer/backend/) then this  (mysql) "connection 
pool" hash table is not global per se and shared among the different client / 
users sessions. But that defeats the purpose, no? 

Thank you
Best 
Stelios

Re: Querying PostgreSQL / PostGIS Databases in Python

[Tony Shelver]

We are using python on top of Postgresql / PostGIS, for a vehicle
tracking system.
THis is quite data intensive, and we have some 'interesting' GIS queries
where we see where a vehicle or fleet has stopped within specific areas,
where it has traveled, any incidents along the way and much more.

Postgresql functions are used almost exclusively for DML and queries, as
running individual SQL statements that will return all the records required
to process a complex report was just too slow.  For even simple stuff, we
are an order of magnitude faster than a similar system using the same data
written in Java against Posgresql, but using the java ORM for queries.

All geographic functions are processed in PostGIS, other than the odd
reverse geocoding call which is performed against Google Maps or similar

YMMV.

On Fri, 31 Jul 2020 at 12:50, Allan Kamau  wrote:

> You may write stored procedures using PL/pgSQL,alternatively you may
> write your queries in python.
> You may use psycopg2 to query the DB from Python.
> You may have a mix of the two, it will depend on your preference.
> Ideally you may not want your users running queries against the data by
> connecting to the database directly using database tools psql or pgadmin3
> or pgadmin4.
> This means that having a database access application written in Python to
> restrict the and encapsulate data access may be advisable.
> In this case you may place all the DML statements in python and execute
> them or you may have much of the data access logic written into several
> PL/pgSQL functions, then call these functions via Python.
>
>
> Below is python code illustrating the use of psycopg2. This code has not
> been run so expect some errors.
> Here I am executing an SQL query on a table, you may modify this code to
> execute a PL/pgSQL function.
>
>
> import psycopg2;
> from psycopg2 import sql;
> import psycopg2.extras;
> from psycopg2.extensions import AsIs;
>
>
> db__pg_conn__str="host='host_name_of_computer_hosting_pg_db_service'
> port=5432 dbname='your_pg_db_name' user='your_username'
> password='user_password'";
> db__pg_conn=psycopg2.connect(db__pg_conn__str);
>
> query_table(
> dataset_name
> ,some_value_2
> ,db__pg_conn
> );
>
>
> def query_table(
> dataset_name
> ,some_value_2
> ,db__pg_conn
> ):
> """
> """;
> table__id=-1;
> _sql_query1a="""
> SELECT {}::TEXT AS some_string,a.id AS
> table__id,a.*,clock_timestamp() FROM {}.{} a WHERE a.dataset_name={}::TEXT
> ;
> """;
> sqlSQL1a=None;
> sqlSQL1a=sql.SQL(_sql_query1a);
>
> pg_cursor1a=db__pg_conn.cursor(cursor_factory=psycopg2.extras.DictCursor);
> _sql_query1a_processed=pg_cursor1a.mogrify(
> sqlSQL1a.format(
>
> sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_1']))
>
> ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[0])
>
> ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[1])
>
> ,sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_2']))
> )
> ,{
> 'some_value_1':'ABC'
> ,'some_value_2':dataset_name
> }
> );
>
> _sql_query1a_processed=_sql_query1a_processed.decode().replace("\\'","'");
>
> #LOGGER.info(" '{0}', -- _sql_query1a_processed
> is:'{1}'.".format(datetime.datetime.now().strftime('%Y-%m-%d
> %H:%M:%S.%f')[:-1],_sql_query1a_processed));
> pg_cursor1a.execute(
> _sql_query1a_processed
> );
> rowcount1a=pg_cursor1a.rowcount;
> rows=None;
> rows=pg_cursor1a.fetchall();
> row_cnt=0;
> for row in rows:
> pass;
> row_cnt+=1;
> table__id=row["table__id"];//do something with table__id
> //do something with rows.
> rows=None;
> db__pg_conn.commit();
> sqlSQL1a=None;
> pg_cursor1a=None;
>
>
>
> On Fri, Jul 31, 2020 at 12:30 PM Shaozhong SHI 
> wrote:
>
>> Hi,
>>
>> What is the advantage of querying in Python?
>>
>> Has anyone got much experience?
>>
>> What not just use standard query?
>>
>> What is the rationale for querying in Python?
>>
>> Would the performance be better?
>>
>> Regards,
>>
>> Shao
>>
>

Re: Querying PostgreSQL / PostGIS Databases in Python

[Tony Shelver]

We use Postgresql, python and PostGIS.
Nearly all our data access is via Postgres functions, both DML and
queries.  Our system is a vehicle GPS tracking and business
function analysis system, with quite a lot of data.

Most of our reports require accessing hundreds to 100s of thousands of
records for each vehicle, and all geo transforms (coordinates, areas /
polygons, routes / lines and so on) are done in  PostGIS, and just the
reporting result set is returned to Python for format, some final
calculations and

Our queries run an order of magnitude faster than the same data in a
tracking system written in java on top of Postgres, where the queries are
done via the java ORM system and the GIS processing is done in Java.

This is even more true where the python client is situated on physically
separate servers to the database.

On Tue, 4 Aug 2020 at 14:09, Tony Shelver  wrote:

> We are using python on top of Postgresql / PostGIS, for a vehicle
> tracking system.
> THis is quite data intensive, and we have some 'interesting' GIS queries
> where we see where a vehicle or fleet has stopped within specific areas,
> where it has traveled, any incidents along the way and much more.
>
> Postgresql functions are used almost exclusively for DML and queries, as
> running individual SQL statements that will return all the records required
> to process a complex report was just too slow.  For even simple stuff, we
> are an order of magnitude faster than a similar system using the same data
> written in Java against Posgresql, but using the java ORM for queries.
>
> All geographic functions are processed in PostGIS, other than the odd
> reverse geocoding call which is performed against Google Maps or similar
>
> YMMV.
>
> On Fri, 31 Jul 2020 at 12:50, Allan Kamau  wrote:
>
>> You may write stored procedures using PL/pgSQL,alternatively you may
>> write your queries in python.
>> You may use psycopg2 to query the DB from Python.
>> You may have a mix of the two, it will depend on your preference.
>> Ideally you may not want your users running queries against the data by
>> connecting to the database directly using database tools psql or pgadmin3
>> or pgadmin4.
>> This means that having a database access application written in Python to
>> restrict the and encapsulate data access may be advisable.
>> In this case you may place all the DML statements in python and execute
>> them or you may have much of the data access logic written into several
>> PL/pgSQL functions, then call these functions via Python.
>>
>>
>> Below is python code illustrating the use of psycopg2. This code has not
>> been run so expect some errors.
>> Here I am executing an SQL query on a table, you may modify this code to
>> execute a PL/pgSQL function.
>>
>>
>> import psycopg2;
>> from psycopg2 import sql;
>> import psycopg2.extras;
>> from psycopg2.extensions import AsIs;
>>
>>
>> db__pg_conn__str="host='host_name_of_computer_hosting_pg_db_service'
>> port=5432 dbname='your_pg_db_name' user='your_username'
>> password='user_password'";
>> db__pg_conn=psycopg2.connect(db__pg_conn__str);
>>
>> query_table(
>> dataset_name
>> ,some_value_2
>> ,db__pg_conn
>> );
>>
>>
>> def query_table(
>> dataset_name
>> ,some_value_2
>> ,db__pg_conn
>> ):
>> """
>> """;
>> table__id=-1;
>> _sql_query1a="""
>> SELECT {}::TEXT AS some_string,a.id AS
>> table__id,a.*,clock_timestamp() FROM {}.{} a WHERE a.dataset_name={}::TEXT
>> ;
>> """;
>> sqlSQL1a=None;
>> sqlSQL1a=sql.SQL(_sql_query1a);
>>
>> pg_cursor1a=db__pg_conn.cursor(cursor_factory=psycopg2.extras.DictCursor);
>> _sql_query1a_processed=pg_cursor1a.mogrify(
>> sqlSQL1a.format(
>>
>> sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_1']))
>>
>> ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[0])
>>
>> ,psycopg2.sql.Identifier("my_schema.my_table".split(".")[1])
>>
>> ,sql.SQL(',').join(map(psycopg2.sql.Placeholder,['some_value_2']))
>> )
>> ,{
>> 'some_value_1':'ABC'
>> ,'some_value_2':dataset_name
>> }
>> );
>>
>> _sql_query1a_processed=_sql_query1a_processed.decode().replace("\\'","'");
>>
>> #LOGGER.info(" '{0}', -- _sql_query1a_processed
>> is:'{1}'.".format(datetime.datetime.now().strftime('%Y-%m-%d
>> %H:%M:%S.%f')[:-1],_sql_query1a_processed));
>> pg_cursor1a.execute(
>> _sql_query1a_processed
>> );
>> rowcount1a=pg_cursor1a.rowcount;
>> rows=None;
>> rows=pg_cursor1a.fetchall();
>> row_cnt=0;
>> for row in rows:
>> pass;
>> row_cnt+=1;
>> table__id=row["table__id"];//do something with table__id
>> //do something with rows.
>> rows=None;
>> db__pg_conn.commit();
>> sqlSQL1a=None;
>> pg_cursor1a=None;
>>
>>
>>
>> On Fri, Jul 31, 2020 at 12:30 PM Shaozhong SHI 
>> wrote:
>>
>>> Hi,
>>>
>>> What is the advantage of querying in Python?
>

Re: 12.3 replicas falling over during WAL redo

[Alvaro Herrera]

On 2020-Aug-03, Alvaro Herrera wrote:

> >  lsn  | checksum | flags | lower | upper | special | pagesize |
> > version | prune_xid
> > --+--+---+---+---+-+--+-+---
> >  A0A/99BA11F8 | -215 | 0 |   180 |  7240 |    8176 | 8192
> > |   4 | 0
> > 
> > As I understand what we're looking at, this means the WAL stream was
> > assuming this page was last touched by A0A/AB2C43D0, but the page itself
> > thinks it was last touched by A0A/99BA11F8, which means at least one write
> > to the page is missing?
> 
> Yeah, that's exactly what we're seeing.  Somehow an older page version
> was resurrected.  Of course, this should never happen.

... although, the block should have been in shared buffers, and it is
there that the previous WAL record would have updated -- not necessarily
flushed to disk.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

PostgreSQL-12 replication. Check replication lag

[Mariya Rampurawala]

Hi,

I am working on providing HA for replication, using automation scripts.
My set up consists of two nodes, Master and Slave. When master fails, The slave 
is promoted to master.

In case of sync replication, we do not check if the data is up-to-date on slave.
But in case of async replication, how can we check if the slave is up-to-date.

One way I found from the blogs is to compare the values of 
pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn()

If these values are same, can we say that the slave is up-to-date?

Both these values I will fetch from the slave node. How will the slave ensure 
it is up-to-date, if the network between master and slave is broken?

Regards,
Mariya

Postgres 12 - default value for text column

[Raj Gandhi]

The following alter table with default set to very large text used to work
in Postgres 10 but fails in Postgres 12 with *ERROR:  row is too big: size
12960, maximum size 8160*

create table test (id int);
alter table test1 add column license text DEFAULT   ''


The following two variants works in Postgres 12 without any error:

create table test (id int);
alter table test1 add column license text
alter table test1 alter column license SET DEFAULT   ''

create table test (id int, license text  DEFAULT   '' );

Thanks in advance.

Re: Postgres 12 - default value for text column

[David Rowley]

On Wed, 5 Aug 2020 at 08:36, Raj Gandhi  wrote:
> The following alter table with default set to very large text used to work in 
> Postgres 10 but fails in Postgres 12 with ERROR:  row is too big: size 12960, 
> maximum size 8160

I didn't go to the trouble of debugging this, but I imagine this is
due to "Allow ALTER TABLE to add a column with a non-null default
without doing a table rewrite" mentioned in
https://www.postgresql.org/docs/11/release-11.html

In PG10 the table would have been rewritten when you add a NOT NULL
column with a DEFAULT. From PG11 onwards no rewrite takes place and
the default value is stored in pg_attribute.  Since pg_attribute does
not have a TOAST table, it's not possible to add NOT NULL columns
which have default values that won't fit in a heap page.

> The following two variants works in Postgres 12 without any error:
>
> create table test (id int);
> alter table test1 add column license text
> alter table test1 alter column license SET DEFAULT   ' with size more than 8160 >'

This behaves differently since existing rows won't receive the DEFAULT
value. Only new rows will. PostgreSQL12  does not need to store the
missing value in pg_attribute when you do this. Existing rows will
just have a NULL value for the new column.

> create table test (id int, license text  DEFAULT   ' size more than 8160 >' );

Likewise. No missing value needs to be stored here as no rows exist
that need that value. Rows that are added with no value specified for
the license column will just have the DEFAULT value, which is the one
stored in pg_attrdef.

David

Re: Postgres 12 - default value for text column

[Raj Gandhi]

Great, thanks David for the explanation.

On Tue, Aug 4, 2020 at 4:59 PM David Rowley  wrote:

> On Wed, 5 Aug 2020 at 08:36, Raj Gandhi  wrote:
> > The following alter table with default set to very large text used to
> work in Postgres 10 but fails in Postgres 12 with ERROR:  row is too big:
> size 12960, maximum size 8160
>
> I didn't go to the trouble of debugging this, but I imagine this is
> due to "Allow ALTER TABLE to add a column with a non-null default
> without doing a table rewrite" mentioned in
> https://www.postgresql.org/docs/11/release-11.html
>
> In PG10 the table would have been rewritten when you add a NOT NULL
> column with a DEFAULT. From PG11 onwards no rewrite takes place and
> the default value is stored in pg_attribute.  Since pg_attribute does
> not have a TOAST table, it's not possible to add NOT NULL columns
> which have default values that won't fit in a heap page.
>
> > The following two variants works in Postgres 12 without any error:
> >
> > create table test (id int);
> > alter table test1 add column license text
> > alter table test1 alter column license SET DEFAULT   ' text with size more than 8160 >'
>
> This behaves differently since existing rows won't receive the DEFAULT
> value. Only new rows will. PostgreSQL12  does not need to store the
> missing value in pg_attribute when you do this. Existing rows will
> just have a NULL value for the new column.
>
> > create table test (id int, license text  DEFAULT   ' with size more than 8160 >' );
>
> Likewise. No missing value needs to be stored here as no rows exist
> that need that value. Rows that are added with no value specified for
> the license column will just have the DEFAULT value, which is the one
> stored in pg_attrdef.
>
> David
>

Logical replication from multiple masters - master starvation

[Dan shmidt]

Hi everyone,

I'm using postgres 11.4
I have a configuration in which there's one subscriber (slave) node which 
subscribes to several (4) publisher nodes (master).
I noticed one of the master servers keeps lagging behind. I noticed that the 
replication worker on the master keeps failing on timeouts.
When querying pg_stat_subscription on the slave I noticed that 
last_msg_send_time for this master hardly ever updates.

Could this be starvation?
Is there any special configuration on either side (master/slave) that can help 
reduce that?
What's the logic on the slave that chooses from which subscription to process 
incoming data?
How can I further debug this?

Thanks,
Dan.


Sent from Outlook

Re:ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

[fight . mufasa]

I am guessing you used  pg_pathman or other extensions in addition to postgis?Postgis use SPI_exec()  to execute sql statement  "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1” in each worker progress in postgres parallel query,  pg_pathman will rewrite the query plan by pg_pathman planner hook and GetLatestSnapshot. But in parallel query, the snapshot of each worker progress is the same as leader progress, the worker progress is not necessary to get the latest snapshot, that’s the problem.  I think it’s a bug of pg_pathman, there’s a pg_pathman issue for detail: https://github.com/postgrespro/pg_pathman/issues/215Eric Wong.

On 08/5/2020 11:02，fuzk wrote： 


Dear Sir/MadamI got an error when I execute the following select sentence.Would you please solve the problem for me?Thank you .Alan Fu.postgres=# \set VERBOSITY verbosepostgres=# SELECT round(cast(coalesce(sum(ST_length(geography(geometry)))/1000,0) as NUMERIC),4)||'KM' field_value from had_link;ERROR:  XX000: cannot update SecondarySnapshot during a parallel operationCONTEXT:  SQL statement "SELECT proj4text FROM public.spatial_ref_sys WHERE srid = 4326 LIMIT 1"parallel workerLOCATION:  GetLatestSnapshot, snapmgr.c:387 

Re: 12.3 replicas falling over during WAL redo

[Kyotaro Horiguchi]

At Tue, 4 Aug 2020 09:53:36 -0400, Alvaro Herrera  
wrote in 
> On 2020-Aug-03, Alvaro Herrera wrote:
> 
> > >  lsn  | checksum | flags | lower | upper | special | pagesize |
> > > version | prune_xid
> > > --+--+---+---+---+-+--+-+---
> > >  A0A/99BA11F8 | -215 | 0 |   180 |  7240 |    8176 | 8192
> > > |   4 | 0
> > > 
> > > As I understand what we're looking at, this means the WAL stream was
> > > assuming this page was last touched by A0A/AB2C43D0, but the page itself
> > > thinks it was last touched by A0A/99BA11F8, which means at least one write
> > > to the page is missing?
> > 
> > Yeah, that's exactly what we're seeing.  Somehow an older page version
> > was resurrected.  Of course, this should never happen.
> 
> ... although, the block should have been in shared buffers, and it is
> there that the previous WAL record would have updated -- not necessarily
> flushed to disk.

Yeah. On the other hand, the WAL records shown upthread desn't have a FPW.

> rmgr: Btree   len (rec/tot): 72/72, tx:   76393394, lsn:
> A0A/AB2C43D0, prev A0A/AB2C4378, desc: INSERT_LEAF off 41, blkref #0: rel
> 16605/16613/60529051 blk 6501

> rmgr: Btree   len (rec/tot): 72/72, tx:   76396065, lsn:
> A0A/AC4204A0, prev A0A/AC420450, desc: INSERT_LEAF off 48, blkref #0: rel
> 16605/16613/60529051 blk 6501

There must be a record for the page 6501 conveying FPW after the last
checkpoint.  If it is not found, something wrong on deciding whether
to attach FPW.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center