Re: Install different directory issues

[Justin]

Hi Thomas

did you make sure the search paths and the paths in the config files were
updated to find postgresql files.   I've seen this not get updated by the
install scripts

On Wed, Nov 27, 2019 at 2:20 PM Thomas Carter  wrote:

> I installed using the Postgres Installer package available on the Postgres
> site. With the system setup, I had to install to /app. I choose to use the
> installer because yum wouldn’t allow for me to choose a different install
> destination.
>
> I’m not sure if I would have the same issue right now if I used rpm
> —prefix.
>
> Tom Carter
>
> > On Nov 27, 2019, at 2:00 PM, Adrian Klaver 
> wrote:
> >
> > On 11/27/19 10:49 AM, Thomas Carter wrote:
> >> I installed Postgres to /app and now psql command not working under
> Postgres user along with not knowing how to start the service like with a
> traditional systemctl.
> >
> > Installed using source, package, other and from where?
> >
> >> RHEL7
> >> Postgres 10.11
> >> Thanks for any help!
> >> --
> >> Tom Carter
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
>
>
>

Re: Install different directory issues

[Justin]

I'm not that knowledgeable of  Redhat  paths
here is how i would start
first find the daemon or systemd  postgresql.service files.  on debian
these are located in /etc/init.d or /etc/systemdif the files exist
modify them to the correct paths if they do not exist the install went very
side-ways  uninstall and and try again and read help files regarding the
command line switches to change the install path

If the the files are and paths are correct the something else went wrong.
either failed to register the daemon or initdb failed which will cause the
service to fail to start.

try to  start postgres from the command prompt.  it will throw errors
giving you an idea what the problem is

on debian logs are located in /var/log/postgresql/versionnumber/

On Wed, Nov 27, 2019 at 3:58 PM Thomas Carter  wrote:

> Hi Justin,
> I expect this is the case since the traditional install will have
> everything “fall” into the right place. How would I correct since I’ve
> installed to the /app directory?
>
> Thanks for your help.
>
> On Wed, Nov 27, 2019 at 2:26 PM Justin  wrote:
>
>> Hi Thomas
>>
>> did you make sure the search paths and the paths in the config files were
>> updated to find postgresql files.   I've seen this not get updated by the
>> install scripts
>>
>> On Wed, Nov 27, 2019 at 2:20 PM Thomas Carter 
>> wrote:
>>
>>> I installed using the Postgres Installer package available on the
>>> Postgres site. With the system setup, I had to install to /app. I choose to
>>> use the installer because yum wouldn’t allow for me to choose a different
>>> install destination.
>>>
>>> I’m not sure if I would have the same issue right now if I used rpm
>>> —prefix.
>>>
>>> Tom Carter
>>>
>>> > On Nov 27, 2019, at 2:00 PM, Adrian Klaver 
>>> wrote:
>>> >
>>> > On 11/27/19 10:49 AM, Thomas Carter wrote:
>>> >> I installed Postgres to /app and now psql command not working under
>>> Postgres user along with not knowing how to start the service like with a
>>> traditional systemctl.
>>> >
>>> > Installed using source, package, other and from where?
>>> >
>>> >> RHEL7
>>> >> Postgres 10.11
>>> >> Thanks for any help!
>>> >> --
>>> >> Tom Carter
>>> >
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.kla...@aklaver.com
>>>
>>>
>>> --
> Tom Carter
>

Re: what causes xact_rollback for a database in pg_stat_database to increment?

[Justin]

Hi Hu

Log_statement = all  can miss some statements sent to  Postgresql  from the
manual https://www.postgresql.org/docs/current/runtime-config-logging.html
*Note*

*Statements that contain simple syntax errors are not logged even by the
log_statement = all setting, because the log message is emitted only after
basic parsing has been done to determine the statement type. In the case of
extended query protocol, this setting likewise does not log statements that
fail before the Execute phase (i.e., during parse analysis or planning).
Set log_min_error_statement to ERROR (or lower) to log such statements.*

Trying cranking up Log_min_error_Statement

Also you can monitor the number of transactions being created from PG10+
there is

txid_status()

https://www.2ndquadrant.com/en/blog/postgresql-10-transaction-traceability/




On Wed, Dec 4, 2019 at 3:49 PM Hu, Patricia  wrote:

> We’ve been seeing over 200k rollbacks being recorded for a database at
> certain hour overnight but can’t see the corresponding ROLLBACK statements
> being recorded in postgresql db logs, even though our log_statement is set
> to ALL.
>
>
>
> I did some tests and confirmed that xact_rollback count increments in
> scenarios below and am looking further at those.
>
> 1.   a ROLLBACK is issued explicitly or implicitly(if a transaction
> or pl/pgsql block/function raises an exception or was aborted)
>
> 2.   it doesn’t reflect the number of records being rolled back
>
> 3.   even if ROLLBACK statement results in WARNING like “there is no
> transaction in progress”, it increments
>
>
>
> Does anyone know on top of his/her head any (other) explanation for such
> unaccounted for rollbacks?
>
>
>
> Thanks,
>
> Patricia
>
>
> Confidentiality Notice:: This email, including attachments, may include
> non-public, proprietary, confidential or legally privileged information. If
> you are not an intended recipient or an authorized agent of an intended
> recipient, you are hereby notified that any dissemination, distribution or
> copying of the information contained in or transmitted with this e-mail is
> unauthorized and strictly prohibited. If you have received this email in
> error, please notify the sender by replying to this message and permanently
> delete this e-mail, its attachments, and any copies of it immediately. You
> should not retain, copy or use this e-mail or any attachment for any
> purpose, nor disclose all or any part of the contents to any other person.
> Thank you.
>

Re: Schema Copy

[Justin]

Hi Sonam

As long as the edited sql script  has been changed from  oldschema.tables
or oldschema.functions etc...  to newschema.functions, newschema.functios
etc...

This does not move  data

On Thu, Dec 5, 2019 at 5:07 AM Sonam Sharma  wrote:

> Can someone please help in schema copy in same database ?
>
> I have taken db dump of current schema and in the ddl file , I have edited
> it with new schema.
> Will this work?
>

Re: Schema Copy

[Justin]

Hi Sonam

If you want a copy the data do a Select * into  (newschema.table) from
oldschema.table https://www.postgresql.org/docs/9.1/sql-selectinto.html

If you want to just move the table

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

Alter Table oldschema.table Set Schema  newschema

On Thu, Dec 5, 2019 at 9:31 AM Sonam Sharma  wrote:

> Hi Justin,
>
> What can be done to move the data..
>
> On Thu, Dec 5, 2019, 7:57 PM Justin  wrote:
>
>> Hi Sonam
>>
>> As long as the edited sql script  has been changed from
>> oldschema.tables  or oldschema.functions etc...  to newschema.functions,
>> newschema.functios etc...
>>
>> This does not move  data
>>
>> On Thu, Dec 5, 2019 at 5:07 AM Sonam Sharma  wrote:
>>
>>> Can someone please help in schema copy in same database ?
>>>
>>> I have taken db dump of current schema and in the ddl file , I have
>>> edited it with new schema.
>>> Will this work?
>>>
>>

Re: Date created for tables

[Justin]

Hi Tom

can't we get access to this information in a backwards way by using
pg_xact_commit_timestamp()  then query the system catalog tables xmin entry
for the relevant object???

this requires turning on pg_xact_commit_timestamp
https://www.postgresql.org/docs/current/runtime-config-replication.html

will not show the creation date as it will be lost after an update and
vacuum ...





On Thu, Dec 5, 2019 at 12:10 PM Chloe Dives 
wrote:

> Having moved to PostgreSQL from Oracle a few years ago I have been
> generally very impressed by Postgres, but there are a few things that I
> still miss. One of those is being able to see the created and last modified
> dates for database objects.
>
>
>
> Is this something that has been considered for implementation?
>
>
>
> Thanks in advance,
>
> Chloe
>
>
>
>
>

Re: upgrade and migrate

[Justin]

Off topic but food for thought given the jump in versions 8.3 to (9.6 or
greater.)

List of major changes i can think of to watch out for that can bite without
warning..

SQL operators~=~   for Like were drop in 8.4 => for hstore was drop in 9.0
in 9.1 standard_conforming_string is  ON by default   previous the default
was Off..
9.4 affected  arrays.
9.5 operator precedence was changed  this affects output and near
impossible to find all the code it can affect..
10 removed contrib/tsearch2 (added to the core in 8.3)

There are allot more minor changes that can bit

Has a test machine been setup and run yet??  just dumping the schema and
restore to later version will point out several issues with custom
data-types,  plpgsql, other PL languages and any missing extensions...








On Fri, Dec 6, 2019 at 11:16 AM Julie Nishimura 
wrote:

> Thank you everybody for your suggestions.
> So, to summarize - we can run pg_upgrade from 8.3 to 9.4 (in place), fix
> app related issues (if any), then migrate to a version more recent than
> 9.6, either through pg_basebackup or through logical replication (if we
> would upgrade to version 10).
>
> First step requires downtime. Second does not. Correct?
>
> -Julie
>
> --
> *From:* Tom Lane 
> *Sent:* Wednesday, December 4, 2019 5:28 AM
> *To:* Stephen Frost 
> *Cc:* Laurenz Albe ; Michael Paquier <
> mich...@paquier.xyz>; Julie Nishimura ;
> pgsql-general@lists.postgresql.org ;
> pgsql-general 
> *Subject:* Re: upgrade and migrate
>
> Stephen Frost  writes:
> > * Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> >> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3.
> >> I would upgrade to a version more recent than 9.6.
>
> > So...  there's a bit of history here.  pg_upgrade in 9.4 actually does
> > support upgrading from 8.3.X.  Support for upgrading from 8.3 was
> > removed in 2209b3923a7afe0b6033ecfea972219df252ca8e.
>
> Yeah.  Also note that 8.3 to 9.6-or-newer is going to be a pretty
> huge jump in terms of minor compatibility issues (have you read
> all the relevant release notes?).  So there's something to be said
> for breaking this down into two steps: update to 9.4, test/fix
> your applications against that, then make a second jump to something
> current.  Each of those jumps could be handled by the respective
> version of pg_upgrade.  I concur with Laurenz's advice that stopping
> at 9.6 is probably not your best choice for a migration today.
>
> regards, tom lane
>

Re: Query with correlated join having slow performance

[Justin]

Hi Saket

The first filter condition seems to be duplicated   it appears this can be
simplified from

and ( pdtaltrelt0_.status_typ_dbky=102
and ( pdtaltrelt0_.rule_status_typ_dbky is null )
or pdtaltrelt0_.status_typ_dbky in ( 19 )
or pdtaltrelt0_.status_typ_dbky in (20 )
 )
and ( pdtaltrelt0_.status_typ_dbky in (19 , 20)
   or pdtaltrelt0_.status_typ_dbky=102
   and (pdtaltrelt0_.rule_status_typ_dbky is null)
)
TO

and (
(pdtaltrelt0_.status_typ_dbky = 102 and pdtaltrelt0_.rule_status_typ_dbky
is null)
or pdtaltrelt0_.status_typ_dbky in (19, 20)
)

The Explain shows the filter seq filter like so
 Filter: (
 ((status_typ_dbky = ANY ('{19,20}'::bigint[]))
   OR ((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
  )
 AND
 (((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
OR (status_typ_dbky = 19)
OR (status_typ_dbky = 20)
)
)

I can not see the difference between above/below the AND  other than the
order of operations...



On Mon, Dec 9, 2019 at 1:33 PM saket bansal  wrote:

> Hi Postgres Experts,
>
> Please help me on a query tuning.
> Postgres verson: 11.5
> This database has been migrated from oracle 12c to postgres. In Oracle
> query executes in 2-3 secs, but in postgres it hangs forever.  There are no
> transactions at this time, I am stuck at first run after migration.
>
> My analysis:
>
> I have done vacuum full , analyze , even with 100% samples using a much
> higher value of default_statistics_target.
> Also tried different hints using pg_hint_plan extension. Overall cost
> reduces, but actual run hangs forever.
> I think problem is with correlated subquery join condition.
> If "and pdtaltrelt*%*_.tenant_dkby=pdtaltrelt0_.tenant_dkby" condition is
> removed from both subqueries, result comes in secs(I understand that would
> be skipping correlated join)
>
>  SQL> select count(*) from pdtalt_rel_to_tenant_rel;
> 267216
>
> SQL> select count(distinct tenant_dkby) from pdtalt_rel_to_tenant_rel;
>  3
>
> Table DDLs , query plan and parameter configuration available at below git
> link:
> https://github.com/bansalsaket/PG_correlated_subquery_slowness
>
> I have 16 GB , 4 CPU , rhel 7 machine.
>
> Thanks for help in advance, let me know if any additional information is
> required
>
>

Re: tcp keep alive don't work when the backend is busy

[Justin]

Hi Oner

It appears that you looking for a way to detect and kill of idle
connections or process that are running for a long time  Correct??

If that is the case use statement_timeout setting and then use Pg_Agent and
this script to kill off idle connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'Database_Name'
AND pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)'
, 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

Statement_Timeout can be set per session/connection

On Tue, Dec 10, 2019 at 7:53 AM Олег Самойлов  wrote:

> According to the documentation
> https://www.postgresql.org/docs/12/runtime-config-connection.html
> A backend must check connection to the client by tcp_keepalive messages.
> (Config option tcp_keepalives_idle).
>
> But this is don't work if the backend is busy.
>
> Simple example:
>
> psql localhost
>
> set tcp_keepalives_idle=1;
> do $$begin loop perform pg_sleep(1);end loop;end;$$;
>
> In other terminal kill -9 the psql on the first terminal.
>
> select * from pg_stat_activity where state='active';
> And we will see that the backend is still active and busy.
>
> The more realistic example. In the real code one of the loops, due to bug
> with asynchronous communication, come to the infinite loop. And occupy a
> backend and locks for a two week after the client was killed, before we
> detected this.
>
>

Re: server will not start (Debian)

[Justin]

will the service start from the command line without creating the stats
directory???

to my understanding after every shutdown pg_stat_tmp  is deleted and
recreated during startup

starting manually, yet the service will not normally points to a
credentials or path issue
https://www.postgresql.org/docs/current/runtime-config-statistics.html

On Tue, Dec 10, 2019 at 10:32 AM stan  wrote:

> On Tue, Dec 10, 2019 at 08:55:02AM -0500, Justin wrote:
> > Hi Stan
> >
> > Check security make sure V12 postgres  has the correct credentials
> >
> OK,
>
> postgres@stantest:/var/run/postgresql$ ls -ld
> drwxrwsr-x 2 postgres postgres 40 Dec 10 08:35 .
>
> Looks correct to me.
>
> This sundirectory 12-main.pg_stat_tmp keeps getting deleted.
>
> If I create it by hand as user psotgres, the DB starts up when I run:
>
> /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c
> config_file=/etc/postgresql/12/main/postgresql.conf
>
> But tring to start it with /etc/ini.d/postgresql start fails
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>

Re: Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

[Justin]

Hi Ityas

Advisory locks do not act like the locks in MSSQL,  Postgresql will ignore
advisory locks for other transactions,  its up the applications layer to
poll/ obey/ enforce advisory locks

You can do the type of locking as  in MSSQL with Postgresql but its avoided
in practice as that is the point of MVCC,  allow the database to handle
locking/updating of resources.  Odds of Deadlocks go way down...

Postgresql has always run in the optional MSSQL Snap Shot Isolation Mode
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

I think MSSQL is now the only major database still shipped with MVCC turned
off..
https://www.postgresql.org/docs/current/mvcc-intro.html

On Fri, Dec 13, 2019 at 9:06 AM Andrei Zhidenkov 
wrote:

> I’m not sure what these function exactly do in MSSQL but what in Postgres
> you probably can use “Advisory Locks” (
> https://www.postgresql.org/docs/9.4/explicit-locking.html) in order to
> achive this.
>
> On 13. Dec 2019, at 13:31, İlyas Derse  wrote:
>
> sp_getapplock
>
>
>

Re: Race condition while creating a new partition

[Justin]

Hi Andrei,

My gut reactions is Yes this is a deadlock caused by a race condition,  the
error from psycopg2 tells us  that.  Question becomes what is causing these
two process to collide, are both processes 33 and 37 python code,  As both
are trying to access the same resource 16453  i would assume both sending
the same command Create Table Partition.  Are these two connections from
different computers or the same computer using multi threading??

What does Postgresql Log show what is going on??

On Mon, Dec 16, 2019 at 5:32 AM Andrei Zhidenkov 
wrote:

> I think that I’ve got a deadlock (which is handled by `exception when
> others` statements). But the problem is it occurs too fast. Is it possible
> to get a deadlock faster than deadlock_timeout? It’s set to 1s (default
> value) but it looks like I get it immidiately. Error message I’m getting
> after removing the second exception handling is the following:
>
> psycopg2.errors.DeadlockDetected: deadlock detected
> DETAIL:  Process 33 waits for AccessExclusiveLock on relation 16453 of
> database 16384; blocked by process 37.
> Process 37 waits for AccessExclusiveLock on relation 16453 of database
> 16384; blocked by process 33.
> HINT:  See server log for query details.
> CONTEXT:  SQL statement "
> CREATE TABLE IF NOT EXISTS
> prov_level_1.log_*__2019_12_16_10_25_46 PARTITION OF prov_level_1.log_*
> FOR VALUES FROM ('2019-12-16 10:25:46+00') TO ('2019-12-16
> 10:25:47+00');
> "
>
> > On 15. Nov 2019, at 11:49, Andrei Zhidenkov 
> wrote:
> >
> > We use this code in order to automatically create new partitions for a
> partitioned table (Postgres 10.6):
> >
> > begin
> >  insert into ;
> > exception when undefined_table then
> >  begin
> >
> >-- A concurrent txn has created the new partition
> >exception when others then end;
> >-- Insert data into the new partition
> >insert into ;
> >  end;
> > end;
> >
> > As far as I understand we should always have a new partition created
> either in current or in concurrent transaction but today we faced the
> problem when we failed to insert data due to a partition nonexistence for a
> small period of time. Have I missed something?
> >
> > Thank you.
> >
> > —
> >
> > With best regards, Andrei Zhidenkov.
>
>
>
>

Re: Race condition while creating a new partition

[Justin]

Hi Andrei

General speaking any DDL (Create, Alter  Drop .etc) commands  issue
exclusive locks automatically, so anything this transaction touches starts
getting exclusive locks.  Assuming this is a multi-threading app these two
threads are sending commands all but at the same time.  The Exclusive lock
has not been committed yet as its waiting for a previous insert to finish,
and the Exclusive lock is blocking the insert  as its transaction started
prior to the Insert.

the wait queue is pretty long 84, 80, 82, 83, 87, 88, 85, 86.
The time stamps are only 2 ms apart,  lots of commands being sent in at
nearly the same time...

Here are my  suggestions
Tweak  the Python app to make sure all the threads have committed their
transactions place a lock on all the other threads, Then issue a DDL
command, when the command is committed, then unlock the other threads.
That should kill off the race condition deadlock..

Post all the SQL code that is creating this exclusive lock,  we may be able
re-write the SQL code to reduce the time and/or resources this Exclusive
Lock is creating.

Pyscopg2 default mode is to require issuing a Commit it does not
automatically commit transaction.  If running in auto-commit OFF try
turning this  ON for DDL commands may fix the issue.  It may also be that
DDL command takes far longer than 1 second to complete...

Re: Tuple concurrency issue in large objects

[Justin]

I have a question reading through this email chain.   Does Large Objects
table using these functions work like normal MVCC where there can be two
versions of a large object in pg_largeobject .  My gut says no as
moving/copying potentially 4 TB of data would kill any IO.

I can not find any documentation discussing how these functions actually
work with respect to Transaction Isolation, MVCC and Snapshots??

On Wed, Dec 18, 2019 at 10:05 AM Daniel Verite 
wrote:

> Shalini wrote:
>
> > Could you also please state the reason why is it happening in case
> > of large objects? Because concurrent transactions are very well
> > handled for other data types, but the same is not happening for
> > lobs. Is it because the fomer are stored in toast table and there is
> > no support for concurrent txns in pg_largeobject table?
>
> Keeping in mind that large objects are not a datatype, but rather a
> functionality that is built on top of the bytea and oid datatypes plus
> a set of functions, I wouldn't say that concurrent writes would be
> better handled if you had a table: document(id serial, contents bytea)
> with "contents" being indeed toastable.
>
> To illustrate with a basic example: transactions Tx1 and Tx2
> want to update the contents of the same document concurrently,
> with this order of execution:
>
> Tx1: begin
> Tx1: update document set contents=... where id=...
> Tx2: begin
> Tx2: update the same document (gets blocked)
> Tx1: commit
> Tx2: commit
>
> If using the read committed isolation level, Tx2 will be put to wait
> until Tx1 commits, and then the update by Tx1 will be overwritten by
> Tx2. That's a well known anomaly known as a "lost update", and
> probably not what you want.
>
> If using a better isolation level (repeatable read or serializable),
> the update by Tx2 will be rejected with a serialization failure,
> which, to me, seems the moral equivalent of the "Tuple concurrently
> updated" error you're reporting with large objects.
> When this occurs, your application can fetch the latest value in a new
> transaction and see how it can apply its change to the new value,
> unless another conflict arises and so on.
>
> In short, the best the database can do in case of conflicting writes
> is to inform the application. It can't know which write should be
> prioritized or if the changes should be merged before being written.
>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>
>
>

Re: Tuple concurrency issue in large objects

[Justin]

I now see what is causing this specific issue...

The update and row versions is happening on 2kb chunk at a time,  That's
going to make tracking what other clients are doing a difficult task.

All the clients would have to have some means to notify all the other
clients that an update occurred in this chunk, which could cause total
reload of the data if the update spilled into adjoining rows,
The notifications and re-fetching of data to keep the clients in sync is
going to make this a Network Chatty app.

Maybe  adding a bit to the documentation stating "row versions occurs every
X chunks"

On Wed, Dec 18, 2019 at 11:12 AM Tom Lane  wrote:

> Justin  writes:
> > I have a question reading through this email chain.   Does Large Objects
> > table using these functions work like normal MVCC where there can be two
> > versions of a large object in pg_largeobject .
>
> Yes, otherwise you could never roll back a transaction that'd modified
> a large object.
>
> > My gut says no as
> > moving/copying potentially 4 TB of data would kill any IO.
>
> Well, it's done on a per-chunk basis (normally about 2K per chunk),
> so you won't do that much I/O unless you're changing all of a 4TB
> object.
>
> regards, tom lane
>

Re: Tuple concurrency issue in large objects

[Justin]

I agree  completely,

I do not think Postgresql is a good fit for Shalini based on the
conversation so far

tracking Concurrency is going to be a killer...  But i see the temptation
to use a DB for this as the updates are ACID less likely to corrupted data
for X reason

On Wed, Dec 18, 2019 at 12:12 PM Tom Lane  wrote:

> Justin  writes:
> > I now see what is causing this specific issue...
> > The update and row versions is happening on 2kb chunk at a time,  That's
> > going to make tracking what other clients are doing a difficult task.
>
> Yeah, it's somewhat unfortunate that the chunkiness of the underlying
> data storage becomes visible to clients if they try to do concurrent
> updates of the same large object.  Ideally you'd only get a concurrency
> failure if you tried to overwrite the same byte(s) that somebody else
> did, but as it stands, modifying nearby bytes might be enough --- or
> not, if there's a chunk boundary between.
>
> On the whole, though, it's not clear to me why concurrent updates of
> sections of large objects is a good application design.  You probably
> ought to rethink how you're storing your data.
>
> regards, tom lane
>

Re: unanalyze a foreign table

[Justin]

I do not know of way to undo an analyze once its committed.  I do not know
the danger in deleting an entry in pg_statistic

What you can do in the future is make copy of the Statics for this table,
analyze, if it negatively affect results put the copy back.

Another option is to do

begin ;
ANALYZE my_problem_table ;
explain select my_problem_query;
rollback ;

Foreign tables are not be default analyzed so the statistics should have
been empty or no entry,  unless it was previously analyzed.

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

On Sun, Dec 22, 2019 at 2:22 PM Jeff Janes  wrote:

> I did a manual ANALYZE of a foreign table, to see if it would make a
> troublesome query better.  It did, but it also made some other queries that
> were previously fine to become far worse.  Is there a way to undo the
> analyze?  I can muck around in the catalogs like below, but seems really
> grotty.
>
> delete from pg_statistic where starelid=418911;
>
> The other option seems to be doing a `drop foreign table ... cascade`, but
> then recreating all the cascaded drops is quite annoying and error prone.
>
> I currently solved it by re-cloning my performance testing server from
> upstream, but that also is not very convenient.  Is directly manipulating
> the catalogs really the best way?
>
> Cheers,
>
> Jeff
>

Re: When should parameters be passed as text v. binary?

[Justin]

As noted by Adrian what is the USE CASE

As a general rule one wants to use the format the data is being stored in.
every time data is cast to another type its going to eat those all so
precious CPU cycles.  (all the horror of electrons turned into infrared
beams)

converting Bytea type to a string encoded in Base64 adds 30% overhead.
converting an integer tor ASCII can add allot of overhead.

The answer is it depends on the USE CASE if casting adds any benefit.  my
gut tells me it will not add any benefiet



On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth 
wrote:

> > "Paula" == Paula Kirsch  writes:
>
>  Paula> I'm just trying to understand the trade-offs between sending
>  Paula> everything always as text, all integer parameters as binary,
>  Paula> floats as binary, etc.
>
> For passing data from client to server, there's no particular reason not
> to use the binary format for any data type that you understand (and
> where you're passing the data type oid explicitly in the query, rather
> than just leaving it as unknown).
>
> For results, things are harder, because libpq is currently
> all-or-nothing about result type formats, and if you start using
> extension types then not all of them even _have_ a binary format. And to
> decode a binary result you need to know the type, and have code to
> handle every specific type's binary format.
>
> --
> Andrew (irc:RhodiumToad)
>
>
>

Re: UPDATE many records

[Justin]

There are several ways to actually do this

If you have Postgresql 11 or higher we now have Create Procedure  that
allows committing transactions,  one draw back is it can not parallel from
inside the procedure
https://www.postgresql.org/docs/11/sql-createprocedure.html
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11

If its an older version then Python Script or other scripting language to
iterates over the data say 10 to 20K will do what you want

for i in list of IDs
begin ;
"UPDATE table_name SET changed_field=new_value()  where ID @> int4range(i,
i+1);
commit;


To create parallel process simple Python script or other scripting language
can be used to create many connections working the data in parallel  but
given the simple update it will NOT help in performance,  this  will be
Hard disk IO bound,  not process bound where parallelization  helps



On Mon, Jan 6, 2020 at 1:36 PM Israel Brewster 
wrote:

> Thanks to a change in historical data, I have a need to update a large
> number of records (around 50 million). The update itself is straight
> forward, as I can just issue an "UPDATE table_name SET
> changed_field=new_value();" (yes, new_value is the result of a stored
> procedure, if that makes a difference) command via psql, and it should
> work. However, due to the large number of records this command will
> obviously take a while, and if anything goes wrong during the update (one
> bad value in row 45 million, lost connection, etc), all the work that has
> been done already will be lost due to the transactional nature of such
> commands (unless I am missing something).
>
> Given that each row update is completely independent of any other row, I
> have the following questions:
>
> 1) Is there any way to set the command such that each row change is
> committed as it is calculated?
> 2) Is there some way to run this command in parallel in order to better
> utilize multiple processor cores, other than manually breaking the data
> into chunks and running a separate psql/update process for each chunk?
> Honestly, manual parallelizing wouldn’t be too bad (there are a number of
> logical segregations I can apply), I’m just wondering if there is a more
> automatic option.
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
>

Re: UPDATE many records

[Justin]

As you have access to Procedure,   you can create a loop then issue an
Begin Update Commit
so something like this should work plpgsql

declare
 icount int = 0;
 new_count int = 0;

begin

select count(*) into icount from mytable;

loop
  begin ;
Update mytable set myvalue = newvalue() where id  between new_count
and new_count+  ;
   commit;
new_count = new_count + 10,000;
   if new_count > icount  then
  break
   end if;
end loop;
end;


I am going to put caveat into this,  if newvalue() function is complex and
takes allot of "CPU cycles to do its thing" then parallelism would help,
unless this function looks at the table being updated it can really
complicate things as the parallel functions would be looking at stale
records which could be bad...






On Mon, Jan 6, 2020 at 3:07 PM Israel Brewster 
wrote:

> Good information. I did forget to mention that I am using PostgreSQL 11.5.
> I also was not aware of the distinction between PROCEDURE and FUNCTION, so
> I guess I used the wrong terminology there when stating that new_value is
> the result of a stored procedure. It’s actually a function.
>
> So would your suggestion then be to create a procedure that loops through
> the records, calculating and committing each one (or, as in your older
> Postgres example, batches of 10k to 20k)?
>
> Good point on the HD I/O bound vs processor bound, but wouldn’t that
> depend on how complicated the actual update is? Still, there is a good
> chance you are correct in that statement, so that aspect is probably not
> worth spending too much time on.
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
> On Jan 6, 2020, at 10:05 AM, Justin  wrote:
>
> There are several ways to actually do this
>
> If you have Postgresql 11 or higher we now have Create Procedure  that
> allows committing transactions,  one draw back is it can not parallel from
> inside the procedure
> https://www.postgresql.org/docs/11/sql-createprocedure.html
>
> https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11
>
> If its an older version then Python Script or other scripting language to
> iterates over the data say 10 to 20K will do what you want
>
> for i in list of IDs
> begin ;
> "UPDATE table_name SET changed_field=new_value()  where ID @> int4range(i,
> i+1);
> commit;
>
>
> To create parallel process simple Python script or other scripting
> language can be used to create many connections working the data in
> parallel  but given the simple update it will NOT help in performance,
> this  will be Hard disk IO bound,  not process bound where parallelization
> helps
>
>
>
> On Mon, Jan 6, 2020 at 1:36 PM Israel Brewster 
> wrote:
>
>> Thanks to a change in historical data, I have a need to update a large
>> number of records (around 50 million). The update itself is straight
>> forward, as I can just issue an "UPDATE table_name SET
>> changed_field=new_value();" (yes, new_value is the result of a stored
>> procedure, if that makes a difference) command via psql, and it should
>> work. However, due to the large number of records this command will
>> obviously take a while, and if anything goes wrong during the update (one
>> bad value in row 45 million, lost connection, etc), all the work that has
>> been done already will be lost due to the transactional nature of such
>> commands (unless I am missing something).
>>
>> Given that each row update is completely independent of any other row, I
>> have the following questions:
>>
>> 1) Is there any way to set the command such that each row change is
>> committed as it is calculated?
>> 2) Is there some way to run this command in parallel in order to better
>> utilize multiple processor cores, other than manually breaking the data
>> into chunks and running a separate psql/update process for each chunk?
>> Honestly, manual parallelizing wouldn’t be too bad (there are a number of
>> logical segregations I can apply), I’m just wondering if there is a more
>> automatic option.
>> ---
>> Israel Brewster
>> Software Engineer
>> Alaska Volcano Observatory
>> Geophysical Institute - UAF
>> 2156 Koyukuk Drive
>> Fairbanks AK 99775-7320
>> Work: 907-474-5172
>> cell:  907-328-9145
>>
>>
>

Re: UPDATE many records

[Justin]

What was the HD wait time ?  What tool is being use to monitor the server
resources??

It appears based on this information there is allot more going on than a
simple Update command

Moving code out of the trigger  probably not  going to improve performance,
unless there is allot of code  that does not need to be processed for this
update or code touching other tables

Study the trigger identify what has to run, pull that code out, then
disable the trigger.Move the necessary code to a new function for
Updating..

On Mon, Jan 6, 2020 at 4:24 PM Israel Brewster 
wrote:

>
> On Jan 6, 2020, at 11:54 AM, Michael Lewis  wrote:
>
> I’m thinking it might be worth it to do a “quick” test on 1,000 or so
>> records (or whatever number can run in a minute or so), watching the
>> processor utilization as it runs. That should give me a better feel for
>> where the bottlenecks may be, and how long the entire update process would
>> take. I’m assuming, of course, that the total time would scale more or less
>> linearly with the number of records.
>>
>
> I think that depends on how your identify and limit the update to those
> 1000 records. If it is using a primary key with specific keys in an array,
> probably close to linear increase because the where clause isn't impactful
> to the overall execution time. If you write a sub-query that is slow, then
> you would need to exclude that from the time. You can always run explain
> analyze on the update and rollback rather than commit.
>
> So a test run on 9,299 records took about 7 seconds to complete (EXPLAIN
> ANALYZE output at https://explain.depesz.com/s/lIYn if it matters),
> during which time I did see a postmaster process consuming 100% CPU. Upping
> the test to 20,819 records took about 16.5 seconds, so that looks
> relatively linear to me. Also, CPU bound. So by my calculations, doing all
> 50M records would take around 10 hours.
>
> One potentially significant note: most of the execution time is spent in a
> trigger. This trigger is actually what’s doing the REAL update that I need
> to happen. If it would make a difference, I could easily pull the trigger
> code out to a separate function that I just call directly (with triggers
> temporarily disabled). My thinking is that calling a function is calling a
> function, and the fact that it is currently called via a trigger rather
> than direct is of little consequence, but I’m willing to be corrected on
> that :-)
>
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
>

Re: UPDATE many records

[Justin]

system monitor i like is glances,  have to have python installed to run,
it can run in command console or present a web interface.  its a very nice
to  get a quick detail few what is going on
https://nicolargo.github.io/glances/

just monitoring the system CPU utilization does not give enough information
to state if the system is CPU bound or IO bound or network IO bound.

on simple selects assuming the data is in shared buffers its trivial
event,  if the select has to go to disk to get the data then its not
trivial especially if its a big table.  One thing that can happen is the
update and the selects in the trigger are pushing data in and out of shared
memory causing the disk to thrash which explain does not report.

 Explain does not peer into function, triggers, etc   select myfunction(),
column list, from mytable.  or update mytable set column = myfunction() ,
it will not report what happened inside just the total time it took to run.
looking at the explain the Update only took 0.6 second while the trigger
took 6.5

rotate_tilt_data: time=6560.401 calls=9299

I'm a little confused is this trigger function going to be around after
this update?  If yes it should be worth looking into

If its a one time run or every 12 months who cares,  Start the update on
friday night,  go in on Saturday to check it

On Mon, Jan 6, 2020 at 5:38 PM Israel Brewster 
wrote:

>
> On Jan 6, 2020, at 12:49 PM, Justin  wrote:
>
> What was the HD wait time ?  What tool is being use to monitor the server
> resources??
>
>
> No idea on the HD wait time - how would I best monitor that? That said,
> this machine does have NVMe drives, so the speed should be fairly high/wait
> time fairly low. It’s also running as a VM, which could affect things, but
> is a bit of a moot point as far as this update goes. As far as monitoring
> server resources, I was just using top.
>
> It appears based on this information there is allot more going on than a
> simple Update command
>
>
> Depending on your definition of “simple update” of course, very true. As I
> stated in the original message, the actual update value is the result of a
> function. The psql command is a simple update, but the function does a bit
> of stuff (primarily trigonometry). According to the EXPLAIN ANALYZE, about
> .7 ms of stuff per record, which of course is most of the runtime. It is
> entirely possible that the function could be optimized to run more quickly.
>
>
> Moving code out of the trigger  probably not  going to improve
> performance, unless there is allot of code  that does not need to be
> processed for this update or code touching other tables
>
>
> One SELECT query on another table to get some values I need to use for the
> calculation. No code that is not needed for the update. Given the nature of
> this bulk update, I *could* make a separate function that simply takes
> those values as parameters, since the same value will be applied to a lot
> of records. I’d just have to be careful about how I applied the update, so
> rows get processed with the correct values. I’m not convinced it would be
> worth it though - might shave a few hours off the total execution time
> (assuming that SELECT is expensive - EXPLAIN ANLYZE shows an index scan, on
> a table with only 12,761 rows, which seems to be about as simple as it
> gets), but I doubt it would be enough for me to feel comfortable simply
> running the update as one monolithic unit.
>
> Study the trigger identify what has to run, pull that code out, then
> disable the trigger.Move the necessary code to a new function for
> Updating..
>
>
> Sure. But I feel we are getting a bit off track. Optimizing the runtime of
> the update is great, but this is a one-off (hopefully) event. I want to
> accomplish it as quickly as possible, of course, but at the same time it
> doesn’t make sense to spend a lot of time optimizing every component of the
> query. The main purpose of the question was honestly for my sanity, to
> reduce the likelihood of having it run for several hours only to error out
> due to bad data or whatever and have to start over from the top. Running in
> parallel simply seemed to be a no-brainer option to make it go quicker,
> assuming CPU bound updating. Optimizations that are going to take work are
> probably not worth it. We can wait for the data to be updated.
>
> Thanks again!
>
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
>
>
> On Mon, Jan 6, 2020 at 4:24 PM Israel Brewster 
> wrote:
>
>>
>> On Jan 6, 2020, at 11:54 AM, Michael Lewis  wrote:
>>
>> I’m thinking it might be worth it to do a “

Re: Encrypted connection SQL server fdw

[Justin]

By loading data meaning this is a one time deal or only used to refresh
data stored in the postgresql database???

A possible solution would be to setup a vpn tunnel, or ipsec connection to
server.  then run FDW through that connection.  Not idea and will slow
things down.

The other option is to use postgresql ODBC on the MSSQL side to pushed from
MSSQL to Postgresql .

On Fri, Jan 10, 2020 at 6:54 AM Soni M  wrote:

> Hi All,
> currently I'm trying to configure postgres (on centos) to load data from
> MS SQL server (on windows server) on an encrypted connection.
>
> Trying with tds_fdw, but found that it doesn't support encrypted
> connection.
>
> Is there any solution to this?
>
> Thanks,
>
> Soni.
>

Re: Worse performance with higher work_mem?

[Justin]

Updating the stats can be done via vacuum or analyze command,

https://www.postgresql.org/docs/12/sql-analyze.html.   To just analyze a
table typically does not take much time. and can be scheduled to run so the
stats update instead of waiting on auto-vacuum to deal with it  which could
be some time on an insert only table

Seeing the difference in speed between first run with low work memory vs
high work memory with parallelization,  I suspect the temp tables never
actually got written to disk they just hung out in the OS IO cache.

The query in all examples is hung up doing Index scan and running the avg()
aggregate.

Maybe you can look at creating summary table for time periods to work
against,  maybe a Weekly or Daily summary of these values could cut down on
the number of records being processed.  It would not affect the result




On Tue, Jan 14, 2020 at 12:08 PM Israel Brewster 
wrote:

>
> On Jan 13, 2020, at 3:46 PM, Rob Sargent  wrote:
>
>
>
> On Jan 13, 2020, at 5:41 PM, Israel Brewster 
> wrote:
>
> On Jan 13, 2020, at 3:19 PM, Tom Lane  wrote:
>
> Israel Brewster  writes:
>
> In looking at the explain analyze output, I noticed that it had an
> “external merge Disk” sort going on, accounting for about 1 second of the
> runtime (explain analyze output here: https://explain.depesz.com/s/jx0q <
> https://explain.depesz.com/s/jx0q>). Since the machine has plenty of RAM
> available, I went ahead and increased the work_mem parameter. Whereupon the
> query plan got much simpler, and performance of said query completely
> tanked, increasing to about 15.5 seconds runtime (
> https://explain.depesz.com/s/Kl0S ),
> most of which was in a HashAggregate.
> How can I fix this? Thanks.
>
>
> Well, the brute-force way not to get that plan is "set enable_hashagg =
> false".  But it'd likely be a better idea to try to improve the planner's
> rowcount estimates.  The problem here seems to be lack of stats for
> either "time_bucket('1 week', read_time)" or "read_time::date".
> In the case of the latter, do you really need a coercion to date?
> If it's a timestamp column, I'd think not.  As for the former,
> if the table doesn't get a lot of updates then creating an expression
> index on that expression might be useful.
>
>
> Thanks for the suggestions. Disabling hash aggregates actually made things
> even worse: (https://explain.depesz.com/s/cjDg), so even if that wasn’t a
> brute-force option, it doesn’t appear to be a good one. Creating an index
> on the time_bucket expression didn’t seem to make any difference, and my
> data does get a lot of additions (though virtually no changes) anyway
> (about 1 additional record per second). As far as coercion to date, that’s
> so I can do queries bounded by date, and actually have all results from
> said date included. That said, I could of course simply make sure that when
> I get a query parameter of, say, 2020-1-13, I expand that into a full
> date-time for the end of the day. However, doing so for a test query didn’t
> seem to make much of a difference either:
> https://explain.depesz.com/s/X5VT
>
> So, to summarise:
>
> Set enable_hasagg=off: worse
> Index on time_bucket expression: no change in execution time or query plan
> that I can see
> Get rid of coercion to date: *slight* improvement. 14.692 seconds instead
> of 15.5 seconds. And it looks like the row count estimates were actually
> worse.
> Lower work_mem, forcing a disk sort and completely different query plan:
> Way, way better (around 6 seconds)
>
> …so so far, it looks like the best option is to lower the work_mem, run
> the query, then set it back?
> ---
>
>
> I don’t see that you’ve updated the statistics?
>
>
> U….no. I know nothing about that :-)
>
> Some research tells me that a) it should happen as part of the autovacuum
> process, and that b) I may not be running autovacuum enough, since it is a
> large table and doesn’t change often. But I don’t really know.
>
> ---
> Israel Brewster
> Software Engineer
> Alaska Volcano Observatory
> Geophysical Institute - UAF
> 2156 Koyukuk Drive
> Fairbanks AK 99775-7320
> Work: 907-474-5172
> cell:  907-328-9145
>
>

Re: Is it possible to replicate through an http proxy?

[Justin]

Another solution to the problem instead of logical replication would be
utilize wal_shipping  and have the edge servers replay the wal using the
restore_command

The wal files can be downloaded from from HTTP server via a proxy and
placed on the edge servers wal_archive directory to be replayed

see
https://www.postgresql.org/docs/12/runtime-config-wal.html#GUC-RESTORE-COMMAND
and
https://www.postgresql.org/docs/12/warm-standby.html

this is twisting  the warm_standby and restore functions into odd shapes
This  gives up  the fine control that logical replication provides and its
not as seamless

On Tue, Jan 14, 2020 at 7:48 AM Iban Rodriguez  wrote:

> Hi all,
>
> for a project we are working on we have the following requirements:
> * PostgreSQL server on cloud
> * Lot of edge devices that need access to DB
> * Edges will only execute SELECT queries. No modification allowed on them.
> * Internet access on edges can be lost and data must be accessible while
> connection is reestablished.
>
> Our first approach to solve this was to setup a postgreSQL server on each
> edge and configure a logical replication from cloud server. This way our
> clients always connect to local server who automatically syncs with cloud
> server while Internet connection is up and also automatically reconnects
> when Internet connection is recovered after connection lost.
>
> However, we facing a new requirement that we do not know how to solve.
> This requirement is that access to Internet from edge devices must be done
> through a http proxy server. Reading documentation we have not been able to
> find a way to create a replication with this limitation. The questions we
> would like to ask are:
> * Is it possible to create a logical replication with this limitation?
> * Is there an alternative solution to our problem bearing in mind this
> limitation?
>
> Thank you very much!
>

Re: Can I do this?

[Justin]

Hi Stan

in you code sample  there are "("  mis-matched,   "MAX("  matches to  "=
project_key)";

it should be

MAX(NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric)

I do exactly what you do, and you are correct sequences are not a good fit

I typically do something like this for

select 'username' || 'more text' || Max(count)+1::text from
myreport_counter_table group by userName, report_id where userName = 'the
user ' and report_id = 12



On Thu, Jan 16, 2020 at 7:28 AM stan  wrote:

> I am trying to create a function to automatically create a reference value
> when a record is inserted into a table. I want the reference value to
> consist of the user that is doing the insert, plus a couple of dates, plus
> a sequence number, where the sequence number will increment every time a
> given user inserts a record. because this sequence number is user specific,
> my first  thought is not to use a set of sequences for it, but to do this
> by
> selecting the maximum sequence number that user has entered in the past.
>
> So, I have a function that gets all the data, and concatenates it into a
> string with the exception of the sequence. For that component, I have the
> following test select that works.
>
>
> SELECT NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric AS
> result
> FROM
> expense_report_instance
> WHERE
> /* NEW.project_key */ 123  = project_key;
>
> But, when I add the requisite MAX clause, I get a syntax error.
>
> SELECT MAX(NULLIF(regexp_replace(report_no, '\D','','g'), '')::numeric
> FROM
> expense_report_instance
> WHERE
> /* NEW.project_key */ 123  = project_key);
>
> Is there a way I can make this work?
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

Re: Can I drop a NOT NUL constrain on an existing table?

[Justin]

Alter table mytable drop constraint  name_of_constraint

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

On Thu, Jan 16, 2020 at 9:43 AM stan  wrote:

> I see how to do this if it is a "dcleared" constraint, but this was just
> defined in the table createion as inL
>
>  report_no   varchar UNIQUE ,
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

Re: Can I do this?

[Justin]

it does not,  but the odds the same user will run this command by this id
in two different sessions at the same time are very low.

this type of code exist  for PO,SO, Invoices, to assign the next line item
# in many apps.


On Thu, Jan 16, 2020 at 10:40 AM Michael Nolan  wrote:

>
>
> On Thu, Jan 16, 2020 at 6:28 AM stan  wrote:
>
>> I am trying to create a function to automatically create a reference value
>> when a record is inserted into a table. I want the reference value to
>> consist of the user that is doing the insert, plus a couple of dates, plus
>> a sequence number, where the sequence number will increment every time a
>> given user inserts a record. because this sequence number is user
>> specific,
>> my first  thought is not to use a set of sequences for it, but to do this
>> by
>> selecting the maximum sequence number that user has entered in the past.
>>
>> What happens if two transactions by/for the same user are committed at
> around the same point in time?  The advantage of sequences is they're
> guaranteed to be unique, I'm not sure counting the number of previous
> records and adding one will always assure that.
> --
> Mike Nolan
>

Re: Is there a GoTo ?

[Justin]

Hi İlyas

As noted by other there is no GOTO

you can move the goto code  into the else statement

or move that code into a new function call and return  that function

or just return null in the else if that is all the it is doing

CREATE OR REPLACE FUNCTION test(i integer) RETURNS integer AS
$$BEGINIF i<0 THEN RETURN i + 1;ELSE
  return second_test( i );END IFRETURN null;END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION second_test(i integer) RETURNS integer AS
$$BEGIN  return abs(i);  END;$$ LANGUAGE plpgsql;


 I know TSQL and PL/SQL have this  but look at all the gotchas when
constructing code with GOTO's



On Thu, Jan 16, 2020 at 5:53 AM İlyas Derse  wrote:

> In this function I have to GOTO to a label1, but GOTO keyword is not
> working, can you please help me in getting the way from which I am able to
> jump from a particular code to label.
>  Thanks...
>
> CREATE OR REPLACE FUNCTION test(i integer) RETURNS integer AS $$BEGINIF i<0 
> THEN RETURN i + 1;ELSE
>   GOTO label1;END IF<>RETURN null;END;$$ LANGUAGE plpgsql;
>
>

Re: @@TRANCOUNT ?

[Justin]

Postgresql does not support Transaction in the same way mssql,   it  does
support nesting transactions in a limited way from version 11+
https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver15
https://www.postgresql.org/docs/current/sql-begin.html
https://wiki.postgresql.org/wiki/Autonomous_subtransactions
https://www.postgresql.org/docs/11/sql-createprocedure.html
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11


>From version 11 and up we have Procedures  it supports commit and rollback
transaction inside a PROCEDURE and must be called with CALL,  there are no
savepoints or transaction naming support...

There are work around this problem using PlPython, or FDW to callback to
postgresql creating a new session to create independent Transactions..







On Wed, Jan 22, 2020 at 7:59 AM İlyas Derse  wrote:

> What's Prostgres' equivalent of select @@trancount
> ?  Do you have an
> idea ?
>

Re: calculating the MD5 hash of role passwords in C

[Justin]

Not sure what your after  but  here is more information regarding how to
store passwords in Postgresql,  not related to database roles but for
storing passwords for things like websites...

https://www.postgresql.org/docs/current/pgcrypto.html
section F.25.2.XXX


On Wed, Jan 22, 2020 at 2:41 PM Matthias Apitz  wrote:

> El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph
> Moench-Tegeder escribió:
>
> > ## Matthias Apitz (g...@unixarea.de):
> >
> > > sisis71=# select rolname, rolpassword from pg_authid where rolname =
> 'sisis';
> > >  rolname | rolpassword
> > > -+-
> > >  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5
> > >
> > > I know the clear text password of the role, it is simple 'sisis123',
> how
> > > could I calculate the above MD5 hash from the clear text password, for
> > > example in C? Which salt is used for the crypt(3) function?
> >
> > The documentation on pg_authid has the details:
> > "The MD5 hash will be of the user's password concatenated to their user
> name."
> > https://www.postgresql.org/docs/12/catalog-pg-authid.html
>
> Thanks to all who replied.
>
> This is still not exactly what I was looking for. But has an interesting
> detail (salting the role password by adding the role name to it). An
> implementation with UNIX crypt(3) for MD5 would need an additional salt
> like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to
> look is the implementation of the PostgreSQL's md5() function.
>
> Thanks again
>
> matthias
>
>
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
> Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit
> Russland!
> Germany out of NATO! NATO out of Germany! Peace with Russia!
> ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
>
>
>

Re: sensible configuration of max_connections

[Justin]

Hi Chris Withers

As stated each connection uses X amount of resources and its very easy to
configure Postgresql where even small number of connections will each up
all the RAM

WorkMem is the biggest consumer of resources  lets say its set to 5 megs
per connection at 1000 connections that 5,000 megs that can be allocated.

Connection pooler  may or may not work depends on how security is laid out
in the Application if this is a valid option...   If the application
understands how to keep tenants out of each other data with no leaks then
yes,  if the each tenant is assigned shema or specific database connection
pooler can still work but the configuration is going to difficult,

On Fri, Feb 7, 2020 at 7:50 AM Chris Ellis  wrote:

> Hi Chris
>
> On Fri, 7 Feb 2020, 08:36 Chris Withers,  wrote:
>
>> Hi All,
>>
>> What's a sensible way to pick the number to use for max_connections?
>>
>
> Sensible in this context is some what variable.  Each connection in
> PostgreSQL will be allocated a backend process.  These are not the lightest
> weight of things.
>
> Each connection takes up space in shared memory, as mentioned in the
> manual.
>
>
>> I'm looking after a reasonable size multi-tenant cluster, where the
>> master handles all the load and there's a slave in case of hardware
>> failure in the master.
>> The machine is used to host what I suspect are mainly django
>> applications, so lots of short web requests, not sure how much, if any,
>> django's orm does connection pooling.
>>
>> I arbitrarily picked 1000 for max_connections and haven't had any
>> problems, but onboarding another app that handles a few million requests
>> per day  on Monday and thinking about potential problems related to the
>> number of available connections.
>>
>> What's "too much" for max_connections? What happens when you set it to
>> high? What factors affect that number?
>>
>
> When sizing max_connections you need to trade off how many connections
> your application will use at peak vs how much RAM and CPU you have.
>
> Each connection is capable of allocating work_mem and has a stack etc.
>
> As such you don't want max_connections to be able to run your system out
> of RAM.
>
> Given your situation I'd very seriously look at connection pooling using
> PgBouncer or similar.  That way you can run with a far smaller
> max_connections and still cope with applications configured with large
> usually idle connection pools.
>
>
>> cheers,
>>
>> Chris
>>
>
> Regards,
> Chris Ellis
>
>>

Re: Fwd: sensible configuration of max_connections

[Justin]

On Fri, Feb 7, 2020 at 1:56 PM Sam Gendler 
wrote:

> Benchmarks, at the time, showed that performance started to fall off due
> to contention if the number of processes got much larger.  I imagine that
> the speed of storage today would maybe make 3 or 4x core count a pretty
> reasonable place to start.  There will be a point of diminishing returns
> somewhere, but you can probably construct your own benchmarks to determine
> where that point is likely to be for your workload.
>

I wonder if anyone has run benchmark like that lately?  Doing such a
benchmark maybe worth while given that so much is now running either in the
cloud or running in a VM or some other kind of Container. all this
abstraction from the hardware layer surely has had to have an impact on the
numbers and rules of thumb...

I still run on real hardware and spinning disk.

Re: How to restore roles without changing postgres password

[Justin]

pg_dumpall creates an SQL file which is just a simple text file

you can then edit sql removing postgres user from  the file

This can be automated in a script that searches the generated sql file for
the postgres user  replacing it with a blank/empty line or adds -- to the
bringing of the line which comments it out.


On Tue, Feb 11, 2020 at 5:27 PM Andrus  wrote:

> Hi!
>
> How to create backup script which restores all roles and role memberships
> from other server without changing postgres user password.
>
> I tried shell script
>
> PGHOST=example.com
> PGUSER=postgres
> PGPASSWORD=mypass
> export PGHOST  PGPASSWORD  PGUSER
> pg_dumpall --roles-only --file=globals.sql
> psql -f globals.sql postgres
>
> but this changes user postgres  password also.
> How to restore roles so that postgres user password is not changed on
> restore.
>
> Script runs on Debian 10 with Postgres 12
> Server from where it reads users runs on Debian Squeeze with Postgres 9.1
>
> Andrus
>
>
>
>

Re: How to restore roles without changing postgres password

[Justin]

HI Tom

Not a bad idea,  would want to extend this to all the roles on the server
not just postgres

I've  edited the global dump many times  removing/editing table spaces,
comment old users, etc..


On Tue, Feb 11, 2020 at 5:46 PM Tom Lane  wrote:

> "Andrus"  writes:
> > How to create backup script which restores all roles and role
> memberships
> > from other server without changing postgres user password.
>
> [ shrug... ]  Edit the command(s) you don't want out of the script.
> This seems like a mighty random requirement to expect pg_dump to
> support out-of-the-box.
>
> I wonder though if there's a case for making that easier by breaking
> up the output into multiple ALTER commands.  Right now you get
> something like
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
> REPLICATION BYPASSRLS PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> but perhaps we could make it print
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER;
> ALTER ROLE postgres WITH INHERIT;
> ALTER ROLE postgres WITH CREATEROLE;
> ALTER ROLE postgres WITH CREATEDB;
> ALTER ROLE postgres WITH LOGIN;
> ALTER ROLE postgres WITH REPLICATION;
> ALTER ROLE postgres WITH BYPASSRLS;
> ALTER ROLE postgres WITH PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> That would make scripted edits a bit easier, and it'd also make the
> output a bit more cross-version portable, eg if you try to load the
> latter into a version without BYPASSRLS, the rest of the commands
> would still work.
>
> regards, tom lane
>
>
>

Re: JIT on Windows with Postgres 12.1

[Justin]

Here is a link to build  LLVM on windows

http://llvm.org/docs/GettingStartedVS.html

On Wed, Feb 12, 2020 at 2:55 PM Tom Lane  wrote:

> Andres Freund  writes:
> > On 2020-02-12 09:39:19 +0100, Josef Šimánek wrote:
> >> I'm not aware of any PG 12 Windows installer with JIT/LLVM enabled.
>
> > It's not currently supported by the windows buildsystem. I don't
> > personally have the expertise to change that (I've not worked on windows
> > after ~2003). So somebody would have to step in to make that work,
> > first.
>
> First you would need either a Windows-native LLVM, or a JIT provider
> based on Microsoft's compiler.  I don't know if the former exists
> (it would not be our job to create it).  I'm not sure if the latter
> is possible, but for sure it'd be a large task if it is possible.
>
> regards, tom lane
>
>
>

Re: How to fix 0xC0000005 exception in Postgres 9.0

[Justin]

This error is really common and is caused by windows via  UAC, SFC,
Firewarll, AV etc..  for it spontaneously to appear means system config
change happened or update occurred.

Common sources  windows updates, anti-viruses changes,  firewall changes.

If its not the above 4 figuring out what causing this is near impossible
as something is trying to access a shared  memory resource that does not
have access rights to it or app that is accessing it is has a bug and
corrupting PostgreSQL memory.

Given this is localhost connection,  start looking at firewall or AV.

On Thu, Feb 20, 2020 at 3:35 PM Andrus  wrote:

> Hi!
>
> Every time when user tries to log on from same virtual computer where
> Postgreql resides, Postgres terminates with 0xC005 exception. Log is
> below.
>
> About 65 users are accessing this server over VPN. They can use server
> normally.
> Logging from localhost using pgadmin 4 with user postgres also works.
>
> Only any attempt to log on from localhost with user dbadmin from psqlODBC
> client causes this exception.
>
> It has worked normally for many years but now suddenly stopped working for
> localhost.
>
> How to fix this ?
>
> Server:
>
> PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit
>
> OS:
>
> Windows server 2008 R2
> Version 6.1 Build 7601
>
> Andrus.
>
> Postgres log:
>
> 2020-02-20 15:44:51 EET   LOG:  server process (PID 3788) was terminated
> by
> exception 0xC005
> 2020-02-20 15:44:51 EET   HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2020-02-20 15:44:51 EET   LOG:  terminating any other active server
> processes
> 2020-02-20 15:44:51 EET andrus mydatabase WARNING:  terminating connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET andrus mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET andrus mydatabase HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET kati mydatabase WARNING:  terminating connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET kati mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET kati mydatabase HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET julia mydatabase WARNING:  terminating connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET julia mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET julia mydatabase HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET reinpuu mydatabase WARNING:  terminating
> connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET reinpuu mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET reinpuu mydatabase HINT:  In a moment you should
> be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET kokka mydatabase WARNING:  terminating connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET kokka mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET kokka mydatabase HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET myll mydatabase WARNING:  terminating connection
> because of crash of another server process
> 2020-02-20 15:44:51 EET myll mydatabase DETAIL:  The postmaster has
> commanded this server process to roll back the current transaction and
> exit,
> because another server process exited abnormally and possibly corrupted
> shared memory.
> 2020-02-20 15:44:51 EET myll mydatabase HINT:  In a moment you should be
> able to reconnect to the database and repeat your command.
> 2020-02-20 15:44:51 EET   WARNING:  terminating connection because of
> crash
> of another server process
> 2020-02-20 15:44:51 EET   DETAIL:  The postmaster has commanded this
> server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> 2020-02-20 15:44:51

Re: How to fix 0xC0000005 exception in Postgres 9.0

[Justin]

having chased this error several times over the last 20 years,  if its not
windows update, AV update, firewall config change, UAC or a driver update
it is nearly impossible to to figure out what is causing it.

One can try to run sysinternal app such as process explorer or depends to
look over the files see if there are recent  changes
https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer

 Process explorer i have seen crash  postgresql when it tries to monitor
the internal workings so it can be very dangerous to use,

I have learned  if the root of the problem can not be quickly identified in
an hour or two,  its faster to just start from scratch,  format the server,
install windows, and recover from backups.  That is how frustrating this
error can be to track ..





On Thu, Feb 20, 2020 at 6:17 PM Ron  wrote:

> On 2/20/20 4:50 PM, Adrian Klaver wrote:
>
> On 2/20/20 2:48 PM, Ron wrote:
>
> On 2/20/20 4:35 PM, Adrian Klaver wrote:
>
> On 2/20/20 2:29 PM, Andrus wrote:
>
> Hi!
>  >Then what changes were done recently, in particular ODBC-related.
> Same psqlODBC driver 09.00.0101 has worked 10 years.
> Previously I logged into this server long time ago and then it worked.
> Windows update history shows lot of updates all called
> Security Intelligence Update for Microsoft Endpoint Protection - KB
> 2461484
> with different version numbers.
>
>
> So did the issue show up after the above updates?
>
>
> What is ntstatus.h?  Would looking there for 0xC005 give a clue to the
> problem (and thus solution)?
>
>
>
> https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-erref/596a1078-e883-4972-9bbc-49e60bebca55
>
> Good luck on gaining any useful information:)
>
>
> *The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could
> not be %s.*
>
> If he recently patched Windows, that might be the cause.
>
> --
> Angular momentum makes the world go 'round.
>

Re: How to fix 0xC0000005 exception in Postgres 9.0

[Justin]

is this happening from any client or just a specific client running ODBC?
are the clients running AV if so are the AV versions the same?

Given this is killing a Linux server,  sounds like ODBC is sending  back
garabage data to the server crashing it.

There are several settings in OBDC, to change how text columns are
processed, play around with those settings see if that helps it
https://odbc.postgresql.org/docs/config.html

As you have it narrowed down to a table,  try querying only a few records
at a time to see if you can identify the specific Record(s) that may be the
issue.
SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0 limit 100

and try querying the columns that do not contain the suspect data that
could be causing this

On Fri, Feb 21, 2020 at 12:01 PM Andrus  wrote:

> Hi!
>
> >Realized I should have been clearer. By pre-libpq I meant this:
> >https://odbc.postgresql.org/docs/release.html
> >psqlODBC 09.05.0100 Release
> >Changes:
> > Use libpq for all communication with the server
> > Previously, libpq was only used for authentication. Using it for all
> > communication lets us remove a lot of duplicated code. libpq is now
> > required for building or using libpq.
>
> I upgraded psqlodbc driver to 12.1 version but problem persists.
> After server is manually started, application works.
>
> I added log_statement = 'all' . Log before crash is:
>
> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: SELECT
> drop_table('temptulemus')
> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: create temp table
> temptulemus as SELECT * FROM andmetp  ;select * from temptulemus limit 0
> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select n.nspname,
> c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
> a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid,
> pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then
> t.typbasetype
> else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from
> (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
> c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a
> on
> (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join
> pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d
> on
> a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by
> n.nspname, c.relname, attnum
> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select
> COUNT(*)::int
> from temptulemus
> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: ;SELECT * FROM
> temptulemus offset 0 limit 900
> 2020-02-21 18:46:40 EET   LOG:  server process (PID 6000) was terminated
> by
> exception 0xC005
> 2020-02-21 18:46:40 EET   HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2020-02-21 18:46:40 EET   LOG:  terminating any other active server
> processes
>
> So command which causes crash is
>
> SELECT * FROM temptulemus offset 0 limit 900
>
> As shown in lines before this is data from andmetp table. This table
> contains text type column. This column may contain data like 

Re: How to fix 0xC0000005 exception in Postgres 9.0

[Justin]

forgot to say publish the Linux logs it may have more details what is going
on

On Fri, Feb 21, 2020 at 12:27 PM Justin  wrote:

> is this happening from any client or just a specific client running ODBC?
> are the clients running AV if so are the AV versions the same?
>
> Given this is killing a Linux server,  sounds like ODBC is sending  back
> garabage data to the server crashing it.
>
> There are several settings in OBDC, to change how text columns are
> processed, play around with those settings see if that helps it
> https://odbc.postgresql.org/docs/config.html
>
> As you have it narrowed down to a table,  try querying only a few records
> at a time to see if you can identify the specific Record(s) that may be the
> issue.
> SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0 limit
> 100
>
> and try querying the columns that do not contain the suspect data that
> could be causing this
>
> On Fri, Feb 21, 2020 at 12:01 PM Andrus  wrote:
>
>> Hi!
>>
>> >Realized I should have been clearer. By pre-libpq I meant this:
>> >https://odbc.postgresql.org/docs/release.html
>> >psqlODBC 09.05.0100 Release
>> >Changes:
>> > Use libpq for all communication with the server
>> > Previously, libpq was only used for authentication. Using it for
>> all
>> > communication lets us remove a lot of duplicated code. libpq is now
>> > required for building or using libpq.
>>
>> I upgraded psqlodbc driver to 12.1 version but problem persists.
>> After server is manually started, application works.
>>
>> I added log_statement = 'all' . Log before crash is:
>>
>> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: SELECT
>> drop_table('temptulemus')
>> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: create temp table
>> temptulemus as SELECT * FROM andmetp  ;select * from temptulemus limit 0
>> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select n.nspname,
>> c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
>> a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid,
>> pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then
>> t.typbasetype
>> else 0 end, t.typtypmod, c.relhasoids, '', c.relhassubclass from
>> (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid =
>> c.relnamespace and c.oid = 28203181) inner join pg_catalog.pg_attribute a
>> on
>> (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join
>> pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d
>> on
>> a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by
>> n.nspname, c.relname, attnum
>> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: select
>> COUNT(*)::int
>> from temptulemus
>> 2020-02-21 18:46:40 EET mydbadmin mydb LOG:  statement: ;SELECT * FROM
>> temptulemus offset 0 limit 900
>> 2020-02-21 18:46:40 EET   LOG:  server process (PID 6000) was terminated
>> by
>> exception 0xC005
>> 2020-02-21 18:46:40 EET   HINT:  See C include file "ntstatus.h" for a
>> description of the hexadecimal value.
>> 2020-02-21 18:46:40 EET   LOG:  terminating any other active server
>> processes
>>
>> So command which causes crash is
>>
>> SELECT * FROM temptulemus offset 0 limit 900
>>
>> As shown in lines before this is data from andmetp table. This table
>> contains text type column. This column may contain data like 

Re: How to fix 0xC0000005 exception in Postgres 9.0

[Justin]

Yes publish the ODBC logs

seeing the Linux logs tells us the ODBC client crashing NOT the client
process.  if the linux  postgrsql client process crashes it typically will
cause the postgresql postmaster  to panic and restart as it has to assume
it corrupt share memory.



On Fri, Feb 21, 2020 at 1:25 PM Andrus  wrote:

> Hi!
>
> >forgot to say publish the Linux logs it may have more details what is
> going
> >on
>
> Linux server has older application client which replicates all data one
> from
> andmetp table using select * from andmetp
> I tried several times. Linux log contains
>
> 2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
>

This is a big hint here that is started to spool the data back to the
client and the client crashed...


> 2020-02-21 16:18:50 EET mydbadmin mydb FATAL:  connection to client lost
> 2020-02-21 16:18:50 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 2020-02-21 17:19:08 EET mydbadmin mydb LOG:  could not send data to
> client:
> Connection reset by peer
>

this tells us the client dropped out not the server..

2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
> 2020-02-21 17:19:08 EET mydbadmin mydb FATAL:  connection to client lost
> 2020-02-21 17:19:08 EET mydbadmin mydb STATEMENT:  SELECT * FROM andmetp
>
> It looks like only connection was terminated.  “dmesg -T” does not show
> postgres crash. Maybe postgres main process is not killed in Linux.
>
> > is this happening from any client or just a specific client running ODBC?
>
> This happene if Windows 2008 server if logged in same same server from RDP
>
RDP should not be causing an issue

> client and in my development windows 10 workstation which logs to linux
> server over VPN
>

This being run over VPN does add some complexity to this as it can cause
corrupt data packets


>
> >are the clients running AV if so are the AV versions the same?
>
> In windows 2008 server antivirus was turned off like I wrote.  In my devel
> workstation I use Microsoft antivirus coming with windows 10.
>
> >Given this is killing a Linux server,  sounds like ODBC is sending  back
> >garabage data to the server crashing it.
>
> I can try publish odbc log if this helps.
>

Yes that was going to be next suggestion turn the log to full, run the
command see what happens

>
> Application replicateds some tables at startup to local disk.
> For tables with 5000 or more rows odbc connection hangs if there is big
> TCP
> packet loss.
> So we changed  replication command
>
>
This is a hint something is wrong with the Network,  TCP packet lose
should just request packet again.

> select * from mytable
>
> to commands
>
> select drop_table(‘temptulemus’);
> create temp table  temptulemus as select * from mytable;
>
> select * from mytable offset 0 limit 900;
> select * from mytable offset 900 limit 900;
> select * from mytable offset 1800 limit 900;
> ...
>
> etc.
>
> In this case data was also retrieved on poor connections.
> Maybe there is some better solution for this.
>

If the connections are bad enough and starts corrupting data it will cause
all kinds of weird issues.

>
> >There are several settings in OBDC, to change how text columns are
> >processed, play around with those settings see if that helps it
> >https://odbc.postgresql.org/docs/config.html
>
> Application needs certain settings. Maybe it is better try to upgrade to
> Postgres 12.2 first.
>
> >As you have it narrowed down to a table,  try querying only a few records
> >at a time to see if you can identify the specific Record(s) that may be
> the
> >issue.
> >SELECT * FROM temptulemus where temptulemus.unique_id >1  offset 0 limit
> >100
> >and try querying the columns that do not contain the suspect data that
> >could be causing this
>
> andmetp table contains 584 record in linux server. I tried script to read
> data from this table every time one more row
>
>
Hello FOXPRO CODE



> for i=1 to 600
> ? i
> StartTextMerge()
> TEXT TEXTMERGE NOSHOW
> select * from andmetp limit <>
> ENDTEXT
> IF !TExec()
>   RETURN .f.
>   ENDIF
> endfor
>
> this worked without error.
>

This is shaping up more and more like Networking Problem corrupting data,
bad router or port


>
> Andrus.
>
>

Re: Connections dropping while using Postgres backend DB with Ejabberd

[Justin]

Hi Dipanjan

Please do not post to all the postgresql mailing list lets keep this on one
list at a time,  Keep this on general list

Am i reading this correctly 10,000 to 50,000 open connections.
Postgresql really is not meant to serve that many open connections.
Due to design of Postgresql  each client connection can use up to the
work_mem of 256MB plus  additional for parallel processes.  Memory will be
exhausted long before 50, connections is reached

I'm not surprised Postgresql and the server is showing issues long before
10K connections is reached.  The OS is probably throwing everything to the
swap file and see connections dropped or time out.

Should be using a connection pooler  to service this kind of load so the
Postgresql does not exhaust resources just from the open connections.
https://www.pgbouncer.org/


On Tue, Feb 25, 2020 at 11:29 AM Dipanjan Ganguly 
wrote:

> Greetings,
>
> I was trying to use postgresql database as a backend with Ejabberd XMPP
> server for load test (Using TSUNG).
>
> Noticed, while using Mnesia the  “simultaneous users and open TCP/UDP
> connections”  graph in Tsung report is showing consistency, but while using
> Postgres, we see drop in connections during 100 to 500 seconds of runtime,
> and then recovering and staying consistent.
>
> I have been trying to figure out what the issue could be without any
> success. I am kind of a noob in this technology, and hoping for some help
> from the good people from the community to understand the problem and how
> to fix this. Below are some details..
>
> · Postgres server utilization is low ( Avg load 1, Highest Cpu
> utilization 26%, lowest freemem  9000)
>
>
>
> Tsung  graph:
> [image: image.png]
>Graph 1: Postgres 12 Backen
> [image: image.png]
>
>   Graph 2: Mnesia backend
>
>
> · Ejabberd Server: Ubuntu 16.04, 16 GB ram, 4 core CPU.
>
> · Postgres on remote server: same config
>
> · Errors encountered during the same time:
>  error_connect_etimedout (same outcome for other 2 tests)
>
> · *Tsung Load:  *512 Bytes message size, user arrival rate 50/s,
> 80k registered users.
>
> · Postgres server utilization is low ( Avg load 1, Highest Cpu
> utilization 26%, lowest freemem  9000)
>
> · Same tsung.xm and userlist used for the tests in Mnesia and
> Postgres.
>
> *Postgres Configuration used:*
> shared_buffers = 4GB
> effective_cache_size = 12GB
> maintenance_work_mem = 1GB
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> default_statistics_target = 100
> random_page_cost = 4
> effective_io_concurrency = 2
> work_mem = 256MB
> min_wal_size = 1GB
> max_wal_size = 2GB
> max_worker_processes = 4
> max_parallel_workers_per_gather = 2
> max_parallel_workers = 4
> max_parallel_maintenance_workers = 2
> max_connections=5
>
>
> Kindly help understanding this behavior.  Some advice on how to fix this
> will be a big help .
>
>
>
> Thanks,
>
> Dipanjan
>

Re: Connections dropping while using Postgres backend DB with Ejabberd

[Justin]

Hi Dipanjan

If the connections are not being closed and left open ,  you should see
50,000 processes running on the server because postgresql creates/forks a
new process for each connection

Just having that many processes running will  exhaust resources,  I would
confirm that the process are still running.
you can use the command

ps aux |wc -l

to get a count on the number of processes
Beyond just opening the connection are there any actions such as Select *
from sometable being fired off to measure performance?

Attempting to open and leave 50K connections open should exhaust the server
resources long before reaching 50K

Something is off here I would be looking into how this test actually works,
how the connections are opened, and commands it sends to Postgresql



On Tue, Feb 25, 2020 at 2:12 PM Dipanjan Ganguly 
wrote:

> Hi Justin,
>
> Thanks for your insight.
>
> I agree with you completely, but as mentioned in my previous email, the
> fact that Postgres server resource utilization is less *"( Avg load 1,
> Highest Cpu utilization 26%, lowest freemem  9000)*" and it recovers at a
> certain point then consistently reaches close to 50 k , is what confusing
> me..
>
> Legends from the Tsung report:
> users
> Number of simultaneous users (it's session has started, but not yet
> finished).connectednumber of users with an opened TCP/UDP connection
> (example: for HTTP, during a think time, the TCP connection can be closed
> by the server, and it won't be reopened until the thinktime has expired)
> I have also used pgcluu to monitor the events. Sharing the stats 
> below..*Memory
> information*
>
>- 15.29 GB Total memory
>- 8.79 GB Free memory
>- 31.70 MB Buffers
>- 5.63 GB Cached
>- 953.12 MB Total swap
>- 953.12 MB Free swap
>- 13.30 MB Page Tables
>- 3.19 GB Shared memory
>
> Any thoughts ??!!  🤔🤔
>
> Thanks,
> Dipanjan
>
>
> On Tue, Feb 25, 2020 at 10:31 PM Justin  wrote:
>
>> Hi Dipanjan
>>
>> Please do not post to all the postgresql mailing list lets keep this on
>> one list at a time,  Keep this on general list
>>
>> Am i reading this correctly 10,000 to 50,000 open connections.
>> Postgresql really is not meant to serve that many open connections.
>> Due to design of Postgresql  each client connection can use up to the
>> work_mem of 256MB plus  additional for parallel processes.  Memory will be
>> exhausted long before 50, connections is reached
>>
>> I'm not surprised Postgresql and the server is showing issues long before
>> 10K connections is reached.  The OS is probably throwing everything to the
>> swap file and see connections dropped or time out.
>>
>> Should be using a connection pooler  to service this kind of load so the
>> Postgresql does not exhaust resources just from the open connections.
>> https://www.pgbouncer.org/
>>
>>
>> On Tue, Feb 25, 2020 at 11:29 AM Dipanjan Ganguly 
>> wrote:
>>
>>> Greetings,
>>>
>>> I was trying to use postgresql database as a backend with Ejabberd XMPP
>>> server for load test (Using TSUNG).
>>>
>>> Noticed, while using Mnesia the  “simultaneous users and open TCP/UDP
>>> connections”  graph in Tsung report is showing consistency, but while using
>>> Postgres, we see drop in connections during 100 to 500 seconds of runtime,
>>> and then recovering and staying consistent.
>>>
>>> I have been trying to figure out what the issue could be without any
>>> success. I am kind of a noob in this technology, and hoping for some help
>>> from the good people from the community to understand the problem and how
>>> to fix this. Below are some details..
>>>
>>> · Postgres server utilization is low ( Avg load 1, Highest Cpu
>>> utilization 26%, lowest freemem  9000)
>>>
>>>
>>>
>>> Tsung  graph:
>>> [image: image.png]
>>>Graph 1: Postgres 12 Backen
>>> [image: image.png]
>>>
>>>   Graph 2: Mnesia backend
>>>
>>>
>>> · Ejabberd Server: Ubuntu 16.04, 16 GB ram, 4 core CPU.
>>>
>>> · Postgres on remote server: same config
>>>
>>> · Errors encountered during the same time:
>>>  error_connect_etimedout (same outcome for other 2 tests)
>>>
>>> · *Tsung Load:  *512 Bytes message size, user arrival rate
>>> 50/s, 80k registered users.
>>>
>>> · Postgres server utilization is low ( Avg load 1, Highest Cpu
>>> utili

Re: Postgresql 12.x on Windows (vs Linux)

[Justin]

Hi Robert

I've used Postgresql on windows for years.  Yes there are performance
differences between windows and linux and the gap has gotten bigger with
JIT

Common performance hits
Shared Buffers
JIT not supported
Windows can be aggressive  flushing  its disk cache,
https://docs.microsoft.com/en-us/windows-server/administration/performance-tuning/subsystem/cache-memory-management/

Running Postgresql using HyperV had big performance hit in my setup it was
30% hit between the VM vs the Hosted OS.

Switch to Linux to get JIT and saw significant improvement on specific
queries,  for general use it was marginal improvement   once the hardware
difference was factored out

On Mon, Mar 2, 2020 at 9:42 AM Robert Ford  wrote:

> I am aware that this might be a broad question, but I am not expecting *very
> *specific answers either:
>
> When it come to running a modern PostgreSQL server, which serves say 1 TB
> of data, are there substantial differences in performance between Windows
> Server 2019 and Linux today?  I know there are some issues with
> shared_buffers and windows, but does it manifest in real performance issues?
>
> I have searched the web for this question, but is surprised to find very
> few concrete benchmarks on this.  Anyone with real world experience on this
> topic?
>
> best regards
> RF
>

Re: Performance Problem

[Justin]

taking a quick glance at config file I do not see any modifications to any
key settings

shared_buffers,
efffecttive cache size
work_mem

meaning the server is running at the default settings which results in
horrible performance

here is a website that gives suggested config changes based on server
hardware.  https://pgtune.leopard.in.ua/#/

That said to get help on slow queries  need to describe the  tables in
question, post the query in question along with explain/analyze results

On Wed, Mar 4, 2020 at 8:17 PM Kordexa Stroken  wrote:

>  Hello iam working on a project and using Postgresql 10.My main server 2 x
> E5 2680 v3 Cpu NVMe M.2 500 Gb Disk 32 GB Ram and my postgresql server is a
> VM(VirtualBox) Centos7 16GB ram Postgresql 10 Db size 4 GB and my solution
> is N-Tier Architecture & 40 online user . My postgresql.conf here:
>
> https://pastebin.com/Y8ybSxq4
>
> I have performance problem with postgresql.What is wrong ? I can not find
> what is problem.
>

Re: Rules versus triggers

[Justin]

Hi Stan

Rules actual are able to rewrite the SQL query sent to  postgresql.  Most
everyone suggestion is avoid rules.

Triggers are just like every other databases Triggers firing off code for
Insert/Update/Delete/Truncate event
https://www.postgresql.org/docs/current/sql-createtrigger.html

On Sat, Mar 7, 2020 at 6:56 AM stan  wrote:

> Could someone give me a brief description of the intended functionally, and
> how the 2 features work of rules, versus triggers? It appears to me that
> they are simply 2 different ways to set up triggers, but I am certain that
> is just because of my lack of knowledge.
>
> Thank you.
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

Re: Rules versus triggers

[Justin]

Yes a rule can rewrite query or replace the query

Read through Depesz  post about rules and the weird side affects that can
occurr
https://www.depesz.com/2010/06/15/to-rule-or-not-to-rule-that-is-the-question/



On Sat, Mar 7, 2020 at 9:57 AM stan  wrote:

> On Sat, Mar 07, 2020 at 09:47:39AM -0500, Justin wrote:
> > Hi Stan
> >
> > Rules actual are able to rewrite the SQL query sent to  postgresql.  Most
> > everyone suggestion is avoid rules.
> >
> > Triggers are just like every other databases Triggers firing off code for
> > Insert/Update/Delete/Truncate event
> > https://www.postgresql.org/docs/current/sql-createtrigger.html
> >
>
> So, the RULE can actually rewrite the query, instead of replacing it,
> which is what I m doing in the function, correct?
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>

Re: Who mades the inserts?

[Justin]

Hi DD

By default Postgresql does not collect this level of detail information to
tell you which database has a high load at X point in time.

You can infer  which database has this high load without increasing logging


Select * from pg_stat_database   this dumps total inserts, update, scans
etc...

To track which database has the highest load  dump this to a table
select * into table compare_db_stat from pg_stat_database

after another high load event occurs compare this table to the current
pg_stat_database results.

To identify which tables are being hit use pg_stat_all_tables

select * from  pg_stat_all_tables order by n_tup_ins desc, n_tup_upd desc

then dump the results to a table with this command
  Select * into table Stats_Compare from pg_stat_all_tables

after the high load has occurred compare the two tables to see specifically
what values have changed

The draw back with this  approach pg_stats_all_tables only shows the
results for the current database,  each database must be done
independently

The other option is alter the log settings to record the SQL statements,
wait for event to happen,  then review the logs.
The big draw with this approach is the log files get BIG FAST..

Additional resources to review.
https://www.postgresql.org/docs/9.2/monitoring-stats.html
https://www.postgresql.org/docs/10/catalog-pg-database.html
https://wiki.postgresql.org/wiki/Disk_Usage
https://wiki.postgresql.org/wiki/Monitoring



On Mon, Mar 9, 2020 at 8:53 AM Durumdara  wrote:

> Dear Members!
>
> We have more than 200 databases in a server.
> The PGAdmin's dashboard shows me 4500 inserts periodically.
>
> I want to know which database(s) causes this.
>
> Do you know any query which can show me the inserts per databases?
>
> And I don't know it works as TPS query? So I need to make differents
> between measured values in two time point?
>
> Like here:
>
> https://dba.stackexchange.com/questions/35940/how-many-queries-per-second-is-my-postgres-executing
>
> select * from  (
> SELECT current_timestamp, datname, sum(xact_commit+xact_rollback) db FROM
> pg_stat_database
> group by current_timestamp, datname
> ) t order by db desc
>
> Thank you for any advance!
>
> Best regards
>DD
>

Re: Force WAL cleanup on running instance

[Justin]

Question everyone isn't this a problem with the order of operations?

switching the wal files  then running checkpoint means the Checkpoint can
cross wal files,  so the previous wal file can not be deleted???

To my understanding the order operations should be

Checkpoint
which  flushes everything to disk, then
pg_switch_wal()

which creates an empty wal file and the previous wal can be deleted?

http://www.interdb.jp/pg/pgsql09.html#_9.7.
https://www.postgresql.org/docs/current/wal-configuration.html

Or am i missing something?

On Wed, Mar 11, 2020 at 11:45 AM Simon Riggs  wrote:

> On Wed, 11 Mar 2020 at 08:59, Torsten Krah  wrote:
>
>> Hi,
>>
>> I am building a docker image with a postgresql 12.2 instance and while
>> doing so and importing a dump and running some update scripts wal size
>> is increasing.
>>
>> When finished I don't need all those wal files anymore and tried to
>> force the daemon to clean them up and tried this:
>>
>> select pg_switch_wal();
>> CHECKPOINT;
>>
>> and did wait for a minute.
>>
>> Sometimes it works and wal files are cleaned and moved away so my image
>> size is way smaller - but it does not happen always in that minute.
>>
>> So is there a way to tell postgres to force the housekeeping of the wal
>> stuff via a statement / command line tool?
>> In a "normal" running instance it just takes care of itself and it will
>> happen sooner or later and it doesn't really matter when that will
>> happen - but with my docker image which is automatically build it would
>> be nice to have a deterministic way of trigger that to reduce the final
>> size image.
>
>
> The size of the task varies, so sometimes takes longer than 60s, depending
> upon your hardware.
>
> --
> Simon Riggshttp://www.2ndQuadrant.com/
> 
> PostgreSQL Solutions for the Enterprise
>

Re: Need help debugging slow logical replication

[Justin]

On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com 
wrote:

> Hi there,
>
> I am using PG 14.14 on both primary and secondary DB on AWS, setup
> using a logical replication, I'm having trouble with huge replication
> lag.
>
> My setup is as follows:
>
> P1 - physical - P1-R
>   | (logical)
> P2 - physical - P2-R
>
>
> The lag between P1 & P1-R , P2 & P2-R are both minimal , less than
> 10seconds , but I'm seeing several hours of lag between P1 & P2  .We
> currently have 1 pub/sub that covers about 100 tables.
>
> Here is the output from P1 showing there is a Lag of at least 80GB
> (keep growing)
>
> > SELECT now() AS CURRENT_TIME,
>slot_name,
>active,
>active_pid,confirmed_flush_lsn,
>pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> confirmed_flush_lsn)) AS diff_size,
>pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS
> diff_bytes
> FROM pg_replication_slots
> WHERE slot_type = 'logical';
> -[ RECORD 1 ]---+---
> current_time| 2023-02-07 23:26:00.733+00
> slot_name   | upgrade
> active  | t
> active_pid  | 5180
> confirmed_flush_lsn | 26B09/8C08C610
> diff_size   | 81 GB
> diff_bytes  | 86573472240
>
> Here is what pg_stat_replication shows: note that the write_lag is
> very high: we previously had to set wal_sender_timeout to 0, otherwise
> the logical replication work keep exiting and fail.
>
> > select * from pg_stat_replication;
> -[ RECORD 1 ]+--
> pid  | xxx
> usesysid | xxx
> usename  | dev
> application_name | upgrade_target
> client_addr  | 10.xxx
> client_hostname  |
> client_port  | 27404
> backend_start| 2023-02-07 23:02:39.228572+00
> backend_xmin | 2909692747
> state| catchup
> sent_lsn | 26B09/8C08C610
> write_lsn| 26B09/840514C0
> flush_lsn| 26B09/840514C0
> replay_lsn   | 26B09/840514C0
> write_lag| 00:07:03.60362
> flush_lag| 00:07:03.60362
> replay_lag   | 00:07:03.60362
> sync_priority| 0
> sync_state   | async
> reply_time   | 2023-02-07 23:22:08.245066+00
>
> So, the problem seems to be like P2 takes a very long time to apply a
> chunk,  Mow, my question is why?  From AWS performance insight, it
> looks like there is only 1 process running on P2, that is 100% blocked
> by CPU. I don't really understand why it is being blocked by the CPU,
> and any help in further debugging is much appreciated.
>
> Cheers.
>

Hi Sir,

What is the replica identity being used on the tables?  Are any of the
tables using  REPLICA IDENTITY FULL ?

How many tables are being replicated?

Re: Need help debugging slow logical replication

[Justin]

On Tue, Feb 7, 2023 at 8:07 PM sunyuc...@gmail.com 
wrote:

> Hi Justin:
>
> - i checked that I have 2 tables using replication identity FULL, but
> one table is empty and one table has only 1 row
> - 7 tables using index
> - overall I have ~100 tables in the publication:  But I do find that
> some tables doesn't have PK , but using replica identity default, I'm
> abit confused since the doc mentioned that that means it is using PK,
> does that mean it is using FULL?
>
> Cheers.
>
> On Tue, Feb 7, 2023 at 4:20 PM Justin  wrote:
> >
> >
> > On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com 
> wrote:
> >>
> >> Hi there,
> >>
> >> I am using PG 14.14 on both primary and secondary DB on AWS, setup
> >> using a logical replication, I'm having trouble with huge replication
> >> lag.
> >>
> >> My setup is as follows:
> >>
> >> P1 - physical - P1-R
> >>   | (logical)
> >> P2 - physical - P2-R
> >>
> >>
> >> The lag between P1 & P1-R , P2 & P2-R are both minimal , less than
> >> 10seconds , but I'm seeing several hours of lag between P1 & P2  .We
> >> currently have 1 pub/sub that covers about 100 tables.
> >>
> >> Here is the output from P1 showing there is a Lag of at least 80GB
> >> (keep growing)
> >>
> >> > SELECT now() AS CURRENT_TIME,
> >>slot_name,
> >>active,
> >>active_pid,confirmed_flush_lsn,
> >>pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> >> confirmed_flush_lsn)) AS diff_size,
> >>pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS
> diff_bytes
> >> FROM pg_replication_slots
> >> WHERE slot_type = 'logical';
> >> -[ RECORD 1 ]---+---
> >> current_time| 2023-02-07 23:26:00.733+00
> >> slot_name   | upgrade
> >> active  | t
> >> active_pid  | 5180
> >> confirmed_flush_lsn | 26B09/8C08C610
> >> diff_size   | 81 GB
> >> diff_bytes  | 86573472240
> >>
> >> Here is what pg_stat_replication shows: note that the write_lag is
> >> very high: we previously had to set wal_sender_timeout to 0, otherwise
> >> the logical replication work keep exiting and fail.
> >>
> >> > select * from pg_stat_replication;
> >> -[ RECORD 1 ]+--
> >> pid  | xxx
> >> usesysid | xxx
> >> usename  | dev
> >> application_name | upgrade_target
> >> client_addr  | 10.xxx
> >> client_hostname  |
> >> client_port  | 27404
> >> backend_start| 2023-02-07 23:02:39.228572+00
> >> backend_xmin | 2909692747
> >> state| catchup
> >> sent_lsn | 26B09/8C08C610
> >> write_lsn| 26B09/840514C0
> >> flush_lsn| 26B09/840514C0
> >> replay_lsn   | 26B09/840514C0
> >> write_lag| 00:07:03.60362
> >> flush_lag| 00:07:03.60362
> >> replay_lag   | 00:07:03.60362
> >> sync_priority| 0
> >> sync_state   | async
> >> reply_time   | 2023-02-07 23:22:08.245066+00
> >>
> >> So, the problem seems to be like P2 takes a very long time to apply a
> >> chunk,  Mow, my question is why?  From AWS performance insight, it
> >> looks like there is only 1 process running on P2, that is 100% blocked
> >> by CPU. I don't really understand why it is being blocked by the CPU,
> >> and any help in further debugging is much appreciated.
> >>
> >> Cheers.
> >
> >
> > Hi Sir,
> >
> > What is the replica identity being used on the tables?  Are any of the
> tables using  REPLICA IDENTITY FULL ?
> >
> > How many tables are being replicated?
>

Setting REPLICA IDENTITY FULL causes FULL TABLE SCANS for UPDATES and
DELETES.  REPLICA IDENTITY FULL should only be used on INSERT only or
tables that see very few  updates and deletes.  REPLICA IDENTITY FULL will
cause replaying of the WAL to be very slow if there are many
updates/deletes for those tables.

REPLICA IDENTITY DEFAULT   will only use primary keys,  if the publisher
includes those tables, the subscriber when  replaying the WAL will stop
throwing an error not knowing how to replay the UPDATE/DELETE.

Logical Replication is most likely broken at this point.

I suggest stopping logical replication and correcting tables that don't
have qualifying indexes for logical replication by creating the
necessary indexes and avoid using replica identity full.  Then restart
logical replication from the beginning.

Re: Trigger questions

[Justin]

On Thu, May 4, 2023 at 9:49 AM DAVID ROTH  wrote:

> 1) Can I create a trigger on a view?
> 2) Do triggers cascade?
>
> Say I have an insert trigger on a table.
> And, I have an insert trigger on a view that references this table
> If I do an insert on the view, will both triggers fire?
>

Can not have triggers on Views,  Views use  RULES  which are DO INSTEAD.
https://www.postgresql.org/docs/current/rules.html

Yes if you have an INSERT/UPDATE/DELETE rule on a view  that inserts into a
table then that table's triggers will be executed.

Please note RULES should  be avoided beyond the use case for VIEWS.  RULES
are executed very early in the query tree; it is not trivial to write rules
on Tables.

Thanks

Re: Logical replication claims to work, not working - new tables

[Justin]

On Sat, Jan 20, 2024, 5:43 PM Chris Angelico  wrote:

> PostgreSQL 15 on Debian, both ends of replication.
>
> I'm doing logical replication in a bit of a complex setup. Not sure
> how much of this is relevant so I'll give you a lot of detail; sorry
> if a lot of this is just noise.
>
> * Bidirectional alternating-master replication. Since I'm still on PG
> 15, the replication is up in one direction, down in the other, rather
> than actually being simultaneous.
> * Replication is defined as "for all tables". All relevant tables are
> in a single schema, "stillebot".
> * Replication was working fine on initial deployment, including a swap
> of master/slave.
> * One table was created without a primary key, and subsequently
> altered to have "replica identity full".
> * Replication is not working for this table (stillebot.settings) but
> is working for other tables.
>
> I tried restarting Postgres on the slave and monitoring
> /var/log/postgresql/postgresql-15-main.log and it all seemed happy.
> According to pg_subscription_rel, all tables are in their
> "replication" phase:
>
> select srsubstate,srsublsn,relname from pg_subscription_rel join
> pg_class on srrelid=oid;
>  srsubstate | srsublsn | relname
> +--+--
>  r  |  | user_followed_categories
>  r  |  | config
>  r  |  | config_exportable
>  r  |  | commands
>  r  |  | settings
> (5 rows)
>
> If I make a change to user_followed_categories, it works fine. If I
> make a change to settings, it does not replicate.
>
> The stillebot.config table seems somewhat desynchronized - rows are
> missing - but if I INSERT a new row into it, it gets properly
> replicated.
>
> How can I dig into this to determine what's going on? Have I broken
> things by creating a table without a primary key? (It's a singleton
> table, will only ever have one row in it; could add a meaningless PK
> if it helps the replication.) Are there other logs to inspect?
>
> Thanks in advance!
>
> ChrisA
>
>
When using replica set to full this kicks off  a  full table scan for each
update or delete this is very expensive.  If there are no errors being
reported you will find it is working but hung doing full scans.  Inserts
are just appended to end of heap.


You can copy the replication slot on the primary to peak at which
transactions LR is hung on to confirm.

Adding a primary key will fix this issue.  Note PG 16 can use indexes to
find qualifying rows when a table's replica is set to full.

>
>

Re: Partitioning options

[Justin]

Hi Sud,

Would not look at HASH partitioning as it is very expensive to add or
subtract the number of partitions.

Would probably look at a nested partitioning using  customer ID using range
or list of IDs then  by transaction date,  Its easy to add  partitions and
balance the partitions segments.

Keep in mind that SELECT queries being used on the partition must  use the
partitioning KEY in the WHERE clause of the query or performance will
suffer.

Suggest doing a query analysis before deploying partition to confirm the
queries WHERE clauses matched the planned partition rule.  I suggest that
80% of the queries of the executed queries must match the partition rule if
not don't deploy partitioning or change  all the queries in the
application to match the partition rule


On Thu, Feb 8, 2024 at 3:51 PM Greg Sabino Mullane 
wrote:

> Out of curiosity, As OP mentioned that there will be Joins and also
>> filters on column Customer_id column , so why don't you think that
>> subpartition by customer_id will be a good option? I understand List
>> subpartition may not be an option considering the new customer_ids gets
>> added slowly in the future(and default list may not be allowed) and also OP
>> mentioned, there is skewed distribution of data for customer_id column.
>> However what is the problem if OP will opt for HASH subpartition on
>> customer_id in this situation?
>>
>
> It doesn't really gain you much, given you would be hashing it, the
> customers are unevenly distributed, and OP talked about filtering on the
> customer_id column. A hash partition would just be a lot more work and
> complexity for us humans and for Postgres. Partitioning for the sake of
> partitioning is not a good thing. Yes, smaller tables are better, but they
> have to be smaller targeted tables.
>
> sud wrote:
>
> 130GB of storage space as we verified using the "pg_relation_size"
>> function, for a sample data set.
>
>
> You might also want to closely examine your schema. At that scale, every
> byte saved per row can add up.
>
> Cheers,
> Greg
>
>

Re: Partitioning options

[Justin]

Hi Marc,

Nested partitioning still allows for simple data deletion by dropping the
table that falls in that date range.

Probably thinking of partitioning by multicolomn rules which is very
complex  to set up

On Fri, Feb 9, 2024, 10:29 AM Marc Millas  wrote:

>
>
>
> On Thu, Feb 8, 2024 at 10:25 PM Justin  wrote:
>
>> Hi Sud,
>>
>> Would not look at HASH partitioning as it is very expensive to add or
>> subtract the number of partitions.
>>
>> Would probably look at a nested partitioning using  customer ID using
>> range or list of IDs then  by transaction date,  Its easy to add
>> partitions and balance the partitions segments.
>>
>
>  I'll not do that because, then, when getting rid of obsolete data, you
> must delete a huge number of records, and vacuum each partition.
> if partitioning by date, you will ease greatly the cleaning, by just
> getting rid of obsolete partitions which is quite speedy.( no delete, no
> vacuum, no index updates, ...)
> Marc
>
>
>> Keep in mind that SELECT queries being used on the partition must  use
>> the partitioning KEY in the WHERE clause of the query or performance will
>> suffer.
>>
>> Suggest doing a query analysis before deploying partition to confirm the
>> queries WHERE clauses matched the planned partition rule.  I suggest that
>> 80% of the queries of the executed queries must match the partition rule if
>> not don't deploy partitioning or change  all the queries in the
>> application to match the partition rule
>>
>>
>> On Thu, Feb 8, 2024 at 3:51 PM Greg Sabino Mullane 
>> wrote:
>>
>>> Out of curiosity, As OP mentioned that there will be Joins and also
>>>> filters on column Customer_id column , so why don't you think that
>>>> subpartition by customer_id will be a good option? I understand List
>>>> subpartition may not be an option considering the new customer_ids gets
>>>> added slowly in the future(and default list may not be allowed) and also OP
>>>> mentioned, there is skewed distribution of data for customer_id column.
>>>> However what is the problem if OP will opt for HASH subpartition on
>>>> customer_id in this situation?
>>>>
>>>
>>> It doesn't really gain you much, given you would be hashing it, the
>>> customers are unevenly distributed, and OP talked about filtering on the
>>> customer_id column. A hash partition would just be a lot more work and
>>> complexity for us humans and for Postgres. Partitioning for the sake of
>>> partitioning is not a good thing. Yes, smaller tables are better, but they
>>> have to be smaller targeted tables.
>>>
>>> sud wrote:
>>>
>>> 130GB of storage space as we verified using the "pg_relation_size"
>>>> function, for a sample data set.
>>>
>>>
>>> You might also want to closely examine your schema. At that scale, every
>>> byte saved per row can add up.
>>>
>>> Cheers,
>>> Greg
>>>
>>>

Re: Partitioning options

[Justin]

On Sun, Feb 18, 2024 at 5:20 PM Alec Lazarescu  wrote:

> "Would probably look at a nested partitioning"
>
> I'm not the original poster, but I have a schema with nested
> (composite) partitions and I do run into some significant
> inefficiencies compared to flat partitions in various schema metadata
> operations (queries to get the list of tables, creating foreign keys,
> etc.) in tables with 1,000+ total partitions.
>
> One example:
> https://www.postgresql.org/message-id/CAE%2BE%3DSQacy6t_3XzCWnY1eiRcNWfz4pp02FER0N7mU_F%2Bo8G_Q%40mail.gmail.com
>
> Alec
>
>
>
Hi Alec,

would need to see the DDL of the partitions and the queries accessing these
partitions to have an opinion

Thank you
Justin

Re: Local replication "slot does not exist" after initial sync

[Justin]

On Sun, Feb 25, 2024 at 1:11 PM Mike Lissner 
wrote:

> Sorry, two more little things here. The publisher logs add much, but
> here's what we see:
>
> STATEMENT: START_REPLICATION SLOT
> "pg_20031_sync_17418_7324846428853951375" LOGICAL F1D0/346C6508
> (proto_version '2', publication_names '"compass_publication2"')
> ERROR: replication slot "pg_20031_sync_17402_7324846428853951375" does not
> exist
>
> And I thought that maybe there'd be some magic in the REFRESH command on
> the subscriber, so I tried that:
>
> alter subscription xyz refresh publication;
>
>
> To nobody's surprise, that didn't help. :)
>
>
> On Sun, Feb 25, 2024 at 10:00 AM Mike Lissner <
> mliss...@michaeljaylissner.com> wrote:
>
>> Hi, I set up logical replication a few days ago, but it's throwing some
>> weird log lines that have me worried. Does anybody have experience with
>> lines like the following on a subscriber:
>>
>> LOG: logical replication table synchronization worker for subscription
>> "compass_subscription", table "search_opinionscitedbyrecapdocument" has
>> started
>> ERROR: could not start WAL streaming: ERROR: replication slot
>> "pg_20031_sync_17418_7324846428853951375" does not exist
>> LOG: background worker "logical replication worker" (PID 1014) exited
>> with exit code 1
>>
>> Slots with this kind of name (pg_xyz_sync_*) are created during the
>> initial sync, but it seems like the subscription is working based on a
>> quick look in a few tables.
>>
>> I thought this might be related to running out of slots on the publisher,
>> so I increased both max_replication_slots and max_wal_senders to 50 and
>> rebooted so those would take effect. No luck.
>>
>> I thought rebooting the subscriber might help. No luck.
>>
>> When I look in the publisher to see the slots we have...
>>
>> SELECT * FROM pg_replication_slots;
>>
>> ...I do not see the one that's missing according to the log lines.
>>
>> So it seems like the initial sync might have worked properly (tables have
>> content), but that I have an errant process on the subscriber that might be
>> stuck in a retry loop.
>>
>> I haven't been able to fix this, and I think my last attempt might be a
>> new subscription with copy_data=false, but I'd rather avoid that if I can.
>>
>> Is there a way to fix or understand this so that I don't get the log
>> lines forever and so that I can be confident the replication is in good
>> shape?
>>
>> Thank you!
>>
>>
>> Mike
>>
>
Hi Mike
This happens when there is an error with another sync worker,  PG has
issues when this happens, the Logical Replication Main Worker  attempts to
sync another table, creates a slots, then tries to sync that table,  then
errors out as it is out of sync workers,  then attempts to sync another
table. the Subscriber is then stuck in a loop  using up all the slots on
the publisher,.  The subscriber continues to cycle through all the tables
that are not status r in the pg_subscription_rel table.  Once there no
slots available on the publisher the subscriber attempts to create slots,
fails and returns the above errors.

These are not critical errors however it is difficult to clean   up as
every time starting and stopping the subscriber it immediately retries to
initial sync the tables it has slots for.  So it keeps using  up all the
publisher slots.

You need to look through the logs looking for the initial error that kicked
this off,  it can be a duplicate key or the subscriber or publisher
disconnected due to wal_reciever_timeout or wal_sender_timerout.

I typical disable all the timeouts as it does not take much to cause the
subscriber and publisher to think the connection has timed out.

A tell tale sign of this is the pg_subscription_rel status will be d for a
bunch of tables.  There should only be few tables that are status d,

I only attempt to clean this up when working on big databases and a lot of
tables have already been sync.

Need to figure out the initial cause of the problem, correct that error and
attempt an initial sync again.

  .

Re: Storing and comparing columns of cryptographic hashes?

[Justin]

On Mon, Apr 8, 2024 at 10:08 AM Josh Triplett  wrote:

>
>
> - Is there a way to tell postgresql "this column contains cryptographic
>   hashes, so you can do hash joins using any subset of the bits, without
>   having to hash them again"? If not, should there be?
>

if you know the specific subset of the bits from the hash value ahead of
time, you can create an index on a  function to extract the subset of bits
ahead of time by putting them in an index.  Consider

create table dd2 (id integer, hash_b bytea);  --create example table
insert into dd2 values (1, '/x1234568'::bytea);  --throw a record at it
create index on dd2 (substr ( hash_b,1,5));  -- create btree index on
subset of value
select * from dd2 WHERE substr(hash_b,1,5) = '/x123';  --this query will
use the index once there enough data to justify an index scan


> - Is `bit(256)` the right type to use to store 32-byte hash values with
>   no additional overhead?
>

Keep them in the bytea format,  can easily cast from bytea to hex for
debugging purposes.

select encode(hash_b, 'hex') from dd2

The Larger the data size the slower all operations are best to keep them in
same data type instead of casting in and out different data types


> - What would be the simplest way, given an input array of hashes (which
>   I may have to pass in as an array and use `unnest`), to filter out all
>   the values that already exist, *and* generate a corresponding bitmap
>   in the same order for present/not-present for the entire array (to
>   send back to the user)? Filtering seems easy enough, but generating
>   the bitmap less so.
>

If you have unique index on the bytea hash,  you can unnest the array to a
table, then insert values with ON CONFLICT

CREATE unique index on dd2(hash_b);
INSERT into dd2 (hash_b)
(SELECT * from unnest(array['/x1234568','/x12345','/x12346', '/x12347',
'/x12348' ]::bytea[]))
ON CONFLICT (hash_b) DO NOTHING
RETURNING hash_b  -- this will  return the rows that got inserted

Then you can compare the inserted vs the inputted array to know what is
present vs not present.


> - Does it make more sense to store the values as one row per value, or
>   as one row per group of values? I know that postgresql can store an
>   entire array in one column; could that efficiently support operations
>   like "tell me which of these objects don't exist in any array in this
>   column" or "for all of these objects, tell me all the group-id values
>   for rows containing them"?
>

Putting them into arrays to keep them grouped together adds additional
overhead to unnest for searching or join operations.  If the data is going
to be large and have to scan over large data sets with  arrays containing
many hash values thats allot of data processing.  Arrays can be indexed but
have to use GIN index, which has many drawbacks compared to btree.  None of
the above queries are possible with GIN indexes or using array
columns without a lot more code.

Arrays are not data sets if  the design needs to access a specific  hash
value for update,delete, append new values, an array probably not the best
solution.
Hope this helps
Justin

Re: replication slot "pg_1015733_sync_1014718_7358407382484881476" does not exist

[Justin]

Hi Avi,

Based on the slot name this is an initial sync worker being created by the
Logical Replication supervisor.  Subscriber started an initial sync either
failed to create the slot and now thinks it exists and keeps trying to drop
it on the publisher or another process dropped the slot on the publisher
before the sync was completed .

To fix this first identify the table involved so you know which table is
causing the issue. The name of the  table can be figured from the slot
name  pg_1015733_sync_1014718_7358407382484881476;  the number 1014718 is
the oid of the table on the subscriber.

I suspect the table  1014718 is empty and not being replicated

Step to clean this up
1: remove the table from the publication
2: refresh the subscription on the subscriber

This typically fixes it.

If the table is empty then can simply add the table back to the publication
and refresh the subscription.  If not need to truncate the table watch out
for foreign key constraints on the table.  If foreign key constraints  can
drop them then truncate the table or using the replication role session
variable to truncate the table or use some other means to catch the table
backup and refresh the subscription with the copy_data = FALSE  set.

If that does not work will need to be manually cleaned up to avoid a full
resync of all the tables.
Dropping the replication origin see the
function pg_replication_origin_drop().  If the table is not removed the
subscription PG will recreate the origin.  The only way to remove a table
from a subscription SAFELY is refresh the subscription after the table been
removed from the publication

https://www.postgresql.org/docs/current/catalog-pg-replication-origin.html
https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-REPLICATION


On Wed, Apr 17, 2024 at 11:17 AM Avi Weinberg  wrote:

> Fixed a typo…
>
>
>
> Hi Experts,
>
>
>
> For a second time in the past few months I'm getting the following errors
> in Postgres log.  Last time it was solved when I reset all Postgres pods.
> Now reset no longer helps.  Logical replication is not working even after I
> performed the reset.
>
> Any ideas what is wrong?
>
>
>
> *From:* Avi Weinberg 
> *Sent:* Wednesday, April 17, 2024 6:01 PM
> *To:* pgsql-generallists.postgresql.org <
> pgsql-general@lists.postgresql.org>
> *Subject:* [External] replication slot
> "pg_1015733_sync_1014718_7358407382484881476" does not exist
>
>
>
> Hi Experts,
>
>
>
> For a second time in the past few months I'm getting the following errors
> in Postgres log.  Last time it was solved when I reset all Postgres pods.
> Now reset no longer helps.  Logical replication is now working even after I
> performed the reset.
>
> Any ideas what is wrong?
>
>
>
>
>
> ERROR:  replication slot "pg_1015733_sync_1014718_7358407382484881476"
> does not exist
>
> STATEMENT:  DROP_REPLICATION_SLOT
> pg_1015733_sync_1014718_7358407382484881476 WAIT
>
> ERROR:  replication slot "pg_1015733_sync_1014956_7358407382484881476"
> does not exist
>
> STATEMENT:  DROP_REPLICATION_SLOT
> pg_1015733_sync_1014956_7358407382484881476 WAIT
>
> 192 ERROR:  replication slot "pg_927671_sync_927281_7358103111795036228"
> does not exist
>
> START_REPLICATION SLOT "pg_927671_sync_927281_7358103111795036228" LOGICAL
> 0/AC888F00 (proto_version '3', publication_names '"aaa","bbb"')
>
> replication slot "pg_1015733_sync_1015375_7358407382484881476" does not
> exist
>
> DROP_REPLICATION_SLOT pg_1015733_sync_1015375_7358407382484881476 WAIT
>
>
>
>
>
> Thanks
>
> IMPORTANT - This email and any attachments is intended for the above named
> addressee(s), and may contain information which is confidential or
> privileged. If you are not the intended recipient, please inform the sender
> immediately and delete this email: you should not copy or use this e-mail
> for any purpose nor disclose its contents to any person.
> IMPORTANT - This email and any attachments is intended for the above named
> addressee(s), and may contain information which is confidential or
> privileged. If you are not the intended recipient, please inform the sender
> immediately and delete this email: you should not copy or use this e-mail
> for any purpose nor disclose its contents to any person.
>

Re: Questions on logical replication

[Justin]

On Sat, Jun 8, 2024 at 1:41 PM Koen De Groote  wrote:

> What I'm trying to do is upgrade a PG11 database to PG16, using logical
> replication.
>
> The PG11 has an active and a standby, there are a handful of databases. On
> particular one has a few tables just over 100GB, then a few 100 tables near
> 1GB.
>
> What I'd do is start a publication with no tables and add them 1 at a
> time, refreshing subscription each time.
>
> This might take a long time, so my main questions relate to potential
> network issues or various situations where the instance receiving the
> logical replication, suddenly stop being able to receive.
>
> Resyncing, and the effects of WAL buildup, are my main concern.
>
> Accidentally sent a mail to only your email, sorry for that.
>
> Regards,
> Koen De Groote
>
>>
>>
This approach does not prevent WAL build up.

The WAL build up occurs during the initial sync worker once that table is
synced the WAL is replayed and released.   The parent worker then become
responsible for replaying the WAL for that table

The WAL build up is during the initial sync of the data by table NOT during
the entire synce of all the tables that have been published.

For 1 gb table the initial sync will be very fast so I doubt any individual
table will cause any significant WAL build up to put the publisher at risk
of of crashing

Once a table becomes synced the main subscriber worker keeps the WAL
replayed.  If there are any errors during the replay of WAL such as missing
indexes for Replica Identities during an Update or Delete  this will cause
the main subscriber worker slot on the publisher to start backing up WAL
files. If there are missing replica identities the affected tables will
have to be dropped from the publication and subscription refreshed.  The
WAL  file is already written with incorrect information so the table on the
subscriber table is most likely not in recoverable state.

I suggest confirming all tables have replica identities or primary keys
before going any further.With PG 11 avoid REPLICA IDENTITY FULL as this
causes full table scan on the subscriber for PG 15 and eariler.  PG 16 on
the subsciber can use a different unique index that has NOT NULL for all
participating columns if the publisher is using  Replicate Identity FULL on
the published table

One must understand the above before deploying logical replication.

Hope this helps

Re: Questions on logical replication

[Justin]

On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote  wrote:

> > If there are any errors during the replay of WAL such as missing indexes
> for Replica Identities during an Update or Delete  this will cause the main
> subscriber worker slot on the publisher to start backing up WAL files
>
> And also if the connection breaks, from what I understand, is that
> correct? Anything that stops the subscription, including disabling the
> subscription, is that right?
>

Yes to all


> > I suggest confirming all tables have replica identities or primary keys
> before going any further.
>
> Yes, I am aware of this. I made me a small script that prints which tables
> I have added to the publication and are done syncing, and which are
> currently not being replicated.
>


>
> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan on
> the subscriber for PG 15 and earlier.
>
> I'm also aware of this. My plan is to create a publication with no tables,
> and add them 1 by 1, refreshing the subscriber each time.
>

Why?  what benefit does this provide you??   Add all the tables when
creating the publication and be done with it...  I get this when trying to
understand how this all works on test boxes, but for production NO idea
what you're trying to accomplish


> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>
Good

Re: Questions on logical replication

[Justin]

On Thu, Jun 13, 2024 at 6:01 AM Koen De Groote  wrote:

> > Why?  what benefit does this provide you??   Add all the tables when
> creating the publication and be done with it...  I get this when trying to
> understand how this all works on test boxes, but for production NO idea
> what you're trying to accomplish
>
> Adding all tables at once means adding the gigantic tables as well. Disk
> IO and Network traffic are a serious concern, increased CPU usage affecting
> queries of the live system, as well as transaction wraparound.
>
> Initial sync can be a serious concern, depending on the size of the table.
>

The number of initial  sync workers can be controlled
via max_sync_workers_per_subscription
see https://www.postgresql.org/docs/current/logical-replication-config.html


if you want to do one table at a time just set sync workers to 1.

If bandwidth is a problem  either from the disk or network, direct the
network traffic from the subscriber through a proxy or firewall to throttle
the network speed.  Slowing the copy will cause the WAL to build up  on the
publisher

CPU load on the publisher is very low its actually hard to see it doing
anything as its just reading the disk, streaming it to the subscriber..

For large tables with lots of indexes for the copy to complete as fast as
possible to prevent WAL build up, drop indexes.  For me the WAL build up
has only been an issue when dealing with multi-TB sized tables when it
takes several days to copy the data  for one table.

One trick is to remove all the indexes during the initial sync except for
the primary key so the subscriber has less work to do.


> Here's a nice guide where people did a logical replication upgrade,
> explaining why they did it this way:
> https://knock.app/blog/zero-downtime-postgres-upgrades
>

The blog suggests overly complicated things.  only doing 100GB chunks of
data at one time.  Maybe  if the publisher was scarce on resources or the
table is multi-TB in size it requires days to weeks to copy...

If the publisher is so low on resources that Logical Replication is
problematic  one can create a binary replica, promote it and convert it to
logical replication skipping the initial sync.  Then upgrade that server.
There is a minor outage required to convert a binary replica to a logical
replica.  I've done it in under 30 seconds.




>
> On Wed, Jun 12, 2024 at 7:01 PM Justin  wrote:
>
>>
>>
>> On Tue, Jun 11, 2024 at 5:43 PM Koen De Groote  wrote:
>>
>>> > If there are any errors during the replay of WAL such as missing
>>> indexes for Replica Identities during an Update or Delete  this will cause
>>> the main subscriber worker slot on the publisher to start backing up WAL
>>> files
>>>
>>> And also if the connection breaks, from what I understand, is that
>>> correct? Anything that stops the subscription, including disabling the
>>> subscription, is that right?
>>>
>>
>> Yes to all
>>
>>
>>> > I suggest confirming all tables have replica identities or primary
>>> keys before going any further.
>>>
>>> Yes, I am aware of this. I made me a small script that prints which
>>> tables I have added to the publication and are done syncing, and which are
>>> currently not being replicated.
>>>
>>
>>
>>>
>>> > With PG 11 avoid REPLICA IDENTITY FULL as this causes full table scan
>>> on the subscriber for PG 15 and earlier.
>>>
>>> I'm also aware of this. My plan is to create a publication with no
>>> tables, and add them 1 by 1, refreshing the subscriber each time.
>>>
>>
>> Why?  what benefit does this provide you??   Add all the tables when
>> creating the publication and be done with it...  I get this when trying to
>> understand how this all works on test boxes, but for production NO idea
>> what you're trying to accomplish
>>
>>
>>> I'm not planning on using "REPLICA IDENTITY FULL" anywhere.
>>>
>> Good
>>
>

Re: --frokbackend process

[Justin]

Hi Satyajit:

Can't tell what is going on from the task manager list.  Looks like a
normal task list for PostgreSQL.  Keep in mind PG is a process based
application NOT a threaded  application.  Meaning postgresql starts/forks a
new process for every connection.  This means we can see each sessions
activity from the task manager.

Need to look at the postgresql logs to see why PG may be hung or possibly
query  pg_stat_activity 

On Wed, Jun 26, 2024 at 1:06 PM satyajit patra <
satyajit.pa...@sunknowledge.com> wrote:

> Dear Team,
>
> I am using postgres database in windows 10 but suddenly we are facing some
> hang issue while starting the cluster and also, we observe few below
> process in the task manager. Is it bad impact to the database ?
>
>

Re: Logical Replication Delay

[Justin]

Hi Ramakrishna,

4GB of WAL generated per minute is a lot.  I would expect the replay on the
subscriber to lag behind because it is a single process.  PostgreSQL 16 can
create parallel workers for large transactions, however if there is a flood
of small transactions touching many tables the single LR worker is going to
fall behind.

The only option is split the LR into multiple  Publications and
Subscriptions as a single worker can't keep up.

What is the justification to not split the tables across multiple
Publications and Subscriptions

Additional items to check

Make sure the Subscriber is using binary mode, this avoids an encoding
step.
https://www.postgresql.org/docs/current/sql-createsubscription.html

Avoid the use of IDENTITY SET TO FULL on the publisher, if you do use
IDENTITY FULL make sure the subscriber table identity is set to a
qualifying unique index.  In previous versions of PG the publisher and
subscriber identities had to match...

IDENTITY SET TO FULL increase the size of the WAL and the work the
publisher and subscriber has to do.

Hope this helps.



On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m  wrote:

> Hi Team,
>
> We have configured bidirectional replication (but traffic can only flow in
> one direction) between two data centers (distance: 1000 km, maximum Network
> latency: 100 ms) with an application TPS (transactions per second) of 700
> at maximum.
>
> We are fine with handling up to 500 TPS without observing any lag between
> the two data centers. However, when TPS increases, we notice a lag in WAL
> files of over 100 GB (initially, it was 1 TB, but after tuning, it was
> reduced to 100 GB). During peak times, WAL files are generated at a rate of
> 4 GB per minute.
>
> All transactions (Tx) take less than 200 ms, with a maximum of 1 second at
> times (no long-running transactions).
>
> *Here are the configured parameters and resources:*
>
>- *OS*: Ubuntu
>- *RAM*: 376 GB
>- *CPU*: 64 cores
>- *Swap*: 32 GB
>- *PostgreSQL Version*: 16.4 (each side has 3 nodes with Patroni and
>etcd configured)
>- *DB Size*: 15 TB
>
> *Parameters configured on both sides:*
> Name
> Setting Unit
>
>
> log_replication_commands off
> logical_decoding_work_mem 524288 kB
> max_logical_replication_workers 16
> max_parallel_apply_workers_per_subscription  2
> max_replication_slots 20
> max_sync_workers_per_subscription 2
> max_wal_senders 20
> max_worker_processes 40
> wal_level logical
> wal_receiver_timeout 60 ms
> wal_segment_size 1073741824 B
> wal_sender_timeout 60 ms
>
> *Optimizations applied:*
>
>1. Vacuum freeze is managed during off-hours; no aggressive vacuum is
>triggered during business hours.
>2. Converted a few tables to unlogged.
>3. Removed unwanted tables from publication.
>4. Partitioned all large tables.
>
> *Pending:*
>
>1. Turning off/tuning autovacuum parameters to avoid triggering during
>business hours.
>
> *Not possible: *We are running all tables in a single publication, and it
> is not possible to separate them.
>
> I would greatly appreciate any suggestions you may have to help avoid
> logical replication delays, whether through tuning database or operating
> system parameters, or any other recommendations
>
> --
> Thanks & Regards,
> Ram.
>

Re: Logical Replication Delay

[Justin]

Hi Ramakrishna,

I am not following the reasoning on not separating the tables into
different publications and subscriptions.  I set up logical replication all
the time in many different environments, one of the audits I perform before
deploying LR is looking at pg_stat_all_tables and WAL creation rate.  If it
is above a certain threshold the tables are split up into different
publications and subscriptions.

If the concern is FK constraints being violated  LR does not validate
those, it's not a concern.

If the concerns are triggers,  the trigger has to be seto ALWAYS or REPLICA
meaning LR will execute it.  I can see an LR worker being AHEAD of other
workers trying to reference ROWs that do not exist yet.  Which can be dealt
with by making sure the Triggers that reference other tables are in the
same publication and subscription.

 Thanks
Justin

On Mon, Sep 23, 2024 at 12:32 AM Ramakrishna m  wrote:

> Hi Justin,
>
> Thank you for your suggestions and detailed insights.
>
> Due to certain business constraints, we are unable to split the tables
> into parallel publications. All of the tables involved are primary key
> tables, which adds further complexity to separating them into multiple
> publications.
>
> That said, we truly appreciate your recommendations regarding the use of
> binary mode and reviewing the use of IDENTITY SET TO FULL. We will ensure
> that the subscriber is operating in binary mode and will recheck the
> identity setup to minimize WAL size and overhead.
>
> Regards,
> Ram.
>
> On Sun, 22 Sept 2024 at 01:32, Justin  wrote:
>
>> Hi Ramakrishna,
>>
>> 4GB of WAL generated per minute is a lot.  I would expect the replay on
>> the subscriber to lag behind because it is a single process.  PostgreSQL 16
>> can create parallel workers for large transactions, however if there is a
>> flood of small transactions touching many tables the single LR worker is
>> going to fall behind.
>>
>> The only option is split the LR into multiple  Publications and
>> Subscriptions as a single worker can't keep up.
>>
>> What is the justification to not split the tables across multiple
>> Publications and Subscriptions
>>
>> Additional items to check
>>
>> Make sure the Subscriber is using binary mode, this avoids an encoding
>> step.
>> https://www.postgresql.org/docs/current/sql-createsubscription.html
>>
>> Avoid the use of IDENTITY SET TO FULL on the publisher, if you do use
>> IDENTITY FULL make sure the subscriber table identity is set to a
>> qualifying unique index.  In previous versions of PG the publisher and
>> subscriber identities had to match...
>>
>> IDENTITY SET TO FULL increase the size of the WAL and the work the
>> publisher and subscriber has to do.
>>
>> Hope this helps.
>>
>>
>>
>> On Sat, Sep 21, 2024 at 3:08 PM Ramakrishna m  wrote:
>>
>>> Hi Team,
>>>
>>> We have configured bidirectional replication (but traffic can only flow
>>> in one direction) between two data centers (distance: 1000 km, maximum
>>> Network latency: 100 ms) with an application TPS (transactions per second)
>>> of 700 at maximum.
>>>
>>> We are fine with handling up to 500 TPS without observing any lag
>>> between the two data centers. However, when TPS increases, we notice a lag
>>> in WAL files of over 100 GB (initially, it was 1 TB, but after tuning, it
>>> was reduced to 100 GB). During peak times, WAL files are generated at a
>>> rate of 4 GB per minute.
>>>
>>> All transactions (Tx) take less than 200 ms, with a maximum of 1 second
>>> at times (no long-running transactions).
>>>
>>> *Here are the configured parameters and resources:*
>>>
>>>- *OS*: Ubuntu
>>>- *RAM*: 376 GB
>>>- *CPU*: 64 cores
>>>- *Swap*: 32 GB
>>>- *PostgreSQL Version*: 16.4 (each side has 3 nodes with Patroni and
>>>etcd configured)
>>>- *DB Size*: 15 TB
>>>
>>> *Parameters configured on both sides:*
>>> Name
>>> Setting Unit
>>>
>>>
>>> log_replication_commands off
>>> logical_decoding_work_mem 524288 kB
>>> max_logical_replication_workers 16
>>> max_parallel_apply_workers_per_subscription  2
>>> max_replication_slots 20
>>> max_sync_workers_per_subscription 2
>>> max_wal_senders 20
>>> max_worker_processes 40
>>> wal_level logical
>>> wal_receiver_timeout 60 ms
>>> wal_segment_size 1073741824 B
>>> wal_sender_timeout 60 ms
>>>
>>&

Re: Synchronize the dump with a logical slot with --snapshot

[Justin]

On Sat, Sep 28, 2024 at 1:04 AM Durgamahesh Manne 
wrote:

> Hi Team
>
> Can anyone respond to my question from respected team members ?
>
> Durga Mahesh
>
> On Thu, Sep 26, 2024 at 2:23 AM Durgamahesh Manne <
> maheshpostgr...@gmail.com> wrote:
>
>> Hi Team
>>
>> --snapshot=snapshotname
>> (Use the specified synchronized snapshot when making a dump of the
>> database
>>
>> This option is useful when needing to synchronize the dump with a logical
>> replication slot) as per the pgdg
>>
>> How do we synchronize the dump with a logical replication slot with
>> --snapshot?
>>
>> I am using the postgresql 14 version which supports only
>> pg_create_logical_replication_slot. How to generate a internal snapshot
>> with it ?
>>
>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>
>> example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
>>   slot_name | consistent_point | snapshot_name | output_plugin
>> -+--+-+---
>>  lsr_sync_01 | 0/C000110 | 0003-0002-1 | pgoutput
>>
>> Regards,
>>
>> Durga Mahesh
>>
>>
>>
>> On Fri, 20 Sept, 2024, 01:27 Durgamahesh Manne, <
>> maheshpostgr...@gmail.com> wrote:
>>
>>> Hi Team
>>>
>>> --snapshot=*snapshotname*
>>>
>>> (Use the specified synchronized snapshot when making a dump of the
>>> database
>>>
>>> This option is useful when needing to synchronize the dump with a
>>> logical replication slot) as per the pgdg
>>>
>>> How do we synchronize the dump with a logical replication slot
>>> with --snapshot?
>>>
>>> I am using the postgresql 14 version which supports only
>>> pg_create_logical_replication_slot. How to generate a snapshot with it ?
>>>
>>> Below CREATE_REPLICAION_SLOT not supported by postgresql 14
>>>
>>> example osdb_lsr=# CREATE_REPLICATION_SLOT lsr_sync_01 LOGICAL pgoutput;
>>>   slot_name  | consistent_point |snapshot_name| output_plugin
>>> -+--+-+---
>>>  lsr_sync_01 | 0/C000110| 0003-0002-1 | pgoutput
>>>
>>> Regards,
>>>
>>> Durga Mahesh
>>>
>>

Hi Durgamahesh,

I am not sure what you are after with matching pg_dump and replication slot
together unless you are trying to get a dump to handle the initial data
sync. There is not a benefit to doing that as the WAL is going to build up
on the publisher...

You have to create a snapshot using the export function

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

https://www.postgresql.org/docs/17/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

Then you can create the logical replication slot with using that slotname
option
https://www.postgresql.org/docs/17/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME
and no sync option.

Then you tell pg_dump to use that snapshot name snapshot with this option
--snapshot=snapshotname

https://www.postgresql.org/docs/current/app-pgdump.html

Once pg_restore is done on the destination , you can create a subscription
using that slotname option probably and specify copy_data = false.

Keep in mind the WAL will build up during this process,  not sure what the
benefit would be just allowing logical replication to do the initial sync.


Thanks
Justin

Re: Suddenly all queries moved to seq scan

[Justin]

On Wed, Nov 20, 2024 at 9:09 AM Sreejith P  wrote:

>
>
>
> Queries were taking 20 ms started taking 60 seconds. So have done SQL
> analyse to understand about query plan. There we found that query planner
> taking seq scan instead in index scan.
>
> I would like to add one ore point.  A delete query were running in DB from
> 2 days for deleting around 80 million records.


This can cause this specific problem where the number of dead tuples and
lack of autovacuum running can cause the statistics to favor a sequential
scan over an index scan.  Taking into account the length of time the delete
took it would hold a number of datapages and tuples in a lock state, which
can lead to blocking queries and prevent autovacuum/analyze.

It is best to do bulk deletes in batches and have a rest period between
batches to allow autovacuum and analyze to keep up..  Doing  deletes in
batches reduces the number of resources being consumed.

Re: Need help in logical replication

[Justin]

Hi Divyansh,

Go to the subscriber and look for errors in the PostgreSQL logs.

When creating a subscription the default action is to sync the tables. Is
the subscriber table empty??

Thank you,

On Mon, Jan 13, 2025 at 7:30 AM Divyansh Gupta JNsThMAudy <
ag1567...@gmail.com> wrote:

> Hii PostgreSQL Community,
>
> I am setting up logical replication between two clusters in the same
> subnet group. I’ve created a publication on the primary and a subscription
> on the secondary, and the replication slot has been created. However, the
> slot remains inactive, and changes aren’t being applied on the subscriber.
>
> I am using AWS RDS for that
>
> Could you please help me identify the possible cause and resolution for
> this issue?
>
> Thank you in advance!
>
> [image: image.png]
> [image: image.png]
>
> CREATE PUBLICATION logical_replication_test
>
> FOR TABLE dbo.logical_rep_test;
>
> CREATE SUBSCRIPTION logical_replication_test_su
>
> CONNECTION 'conn_strig'
>
> PUBLICATION logical_replication_test;
>
>

Re: find replication slots that "belong" to a publication

[Justin]

On Fri, Apr 4, 2025 at 4:58 AM Willy-Bas Loos  wrote:

> Hi!
>
> I'm looking for a way to find out if there are still replication slots
> active for a publication before dropping the publication in an automated
> way. The idea is that the publication is thought not to be needed any
> longer, but we want to make sure.
>
> I'm having trouble finding a link between a publication, the subscriptions
> and the replication slots. Especially when you don't want to make
> assumptions about any subscriber nodes, so you are restricted to the
> publisher node.
>
> The best I could find was a query listed in pg_stat_activity that lists
> the slot name and the publication name:
> START_REPLICATION SLOT "my_slot" LOGICAL 5DD1/3E56D360 (proto_version '1',
> publication_names '"my_publication"')
>
> I don't like the idea of using string manipulation on such query strings
> to get the information I need. Postgres must have a way to compose this
> query.
> Can anyone tell me a way to find replication slots that belong to a
> publication?
>
> --
> Willy-Bas Loos
>

Hi Willy-Bas,

Logical replication slots appear in the views pg_stat_replication and
pg_replication_slots.  Both views have the information you are looking for,
the difference is pg_stat_replication shows only the active slots.  Keep in
mind Temporary Slots only live for the length of the session that created
it; the slot will appear in both views.

The bigger issue I think you are trying to address is when can a slot be
dropped safely.  Once a logical replication slot is dropped there is no
recovery of the slot's lsn position. Probably the best way to decide if a
slot has been abandoned is how far behind it is. The pg_wal_lsn_diff  can
be used to figure out how far behind a slot is

https://www.postgresql.org/docs/13/view-pg-replication-slots.html
https://www.postgresql.org/docs/13/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP

Hope this answers your question


Justin

Re: find replication slots that "belong" to a publication

[Justin]

I can't think of a way to link publication to a replication slot  I
agree using pg_state_activity is the only way to do that however you don't
know if the subscriber is momentary disconnected due network error or
disconnected due to an error in replication  such as duplicated key

SELECT true from pg_stat_activity where query ilike (SELECT '%' ||
pubname::text || '%' from pg_publication);

PG will prevent dropping a publication that are in use.  How PG knows that
I don't know

The publication is used to publish the list of tables that are published
and the subscriber checks pg_pub_rel to make sure it has the
necessary tables to start receiving  data

It is not necessary to have publication to create a logical replication
slot, which PG will stream all data changes.  Several tools create logical
replication slots with no publication..


On Mon, Apr 7, 2025 at 4:44 PM Adrian Klaver 
wrote:

> On 4/7/25 13:32, Willy-Bas Loos wrote:
> > Hi Laurenz,
> >
> > Thanks for answering!
> > I find it very strange, because the publication is needed to make a
> > subscription, which makes the slot.
>
>  From here:
>
>
> https://www.postgresql.org/docs/current/logical-replication-subscription.html
>
> "A subscription defines the connection to another database and set of
> publications (one or more) to which it wants to subscribe."
>
> and here:
>
> "PUBLICATION publication_name [, ...]
>
>  Names of the publications on the publisher to subscribe to.
> "
>
> Finding the subscriptions for a given publication and deleting those
> slots may break the subscription on the receiving side if it is looking
> for data from more then one publication.
>
> > Thanks for looking into it and helping me understand.
> >
> > Cheers!
> > Willy-Bas Loos
> >
> >
> > On Mon, Apr 7, 2025 at 3:31 PM Laurenz Albe  > > wrote:
> >
> > On Mon, 2025-04-07 at 12:16 +0200, Willy-Bas Loos wrote:
> >  > My question is not so much about "can i drop a certain
> > replication slot",
> >  > more about "does this publication still have any replication
> slots?".
> >  > Or, if you will: "what's the publication for this replication
> slot?".
> >  >
> >  > I've double checked the views that you suggested, and I found
> > that I can relate
> >  > the WAL sender processes to replication slots through
> > pg_replication_slots.active_pid .
> >  > I've also looked into replication origins.
> >  >
> >  > But I can't find a link to the publication. And that's what I
> > need to know.
> >
> > I don't think that there is a connection between a publication and a
> > replication slot.  That connection is only made when a subscriber
> > connects
> > and runs the START_REPLICATION command [1] and specifies the
> "pgoutput"
> > plugin with the "publication_names" option [2].
> >
> > I don't think you can see that information reflected in a system view
> > on the primary.  You'd have to query "pg_subscription" on the
> standby.
> >
> > Yours,
> > Laurenz Albe
> >
> >
> >   [1]:
> >
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL
> <
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL
> >
> >   [2]:
> >
> https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS
> <
> https://www.postgresql.org/docs/current/protocol-logical-replication.html#PROTOCOL-LOGICAL-REPLICATION-PARAMS
> >
> >
> >
> >
> > --
> > Willy-Bas Loos
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Logical decoding

[Justin]

On Thu, Feb 20, 2025 at 12:04 AM 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
>


Hi Jethish,

You can by copying the LR slot

SELECT  pg_copy_logical_replication_slot ( '', --source slot
'peek', --destination slot name
true,  --lets make a temporary slot, so we don't have clean up after
ourselves
'test_decoding' --plugin name can be any LR plugin this one let's us see
what is going on
)

Then peek at what the slot is doing

SELECT * FROM pg_logical_slot_peek_changes('peek' , --name of the slot
NULL, --lsn number to start from  can jump ahead,
1 ); --how many transaction to get back from the query this will return
multiple rows for each row affect by the transaction.

select pg_drop_replication_slot()  --how we drop the slot

circular wait not triggering deadlock ?

[Justin Pryzby]

 | eric_enodeb_cell_metrics  | relation   |16402 |  948644697 | 
 |   | |   | |   |  | 
28/1118276 | 28357 | AccessShareLock  | f   | f
 f   | eric_enodeb_cell_metrics  | relation   |16402 |  948644697 | 
 |   | |   | |   |  | 29/830016 
 | 21846 | AccessExclusiveLock  | f   | f
 f   | eric_enodeb_cell_20180304 | relation   |16402 | 1113966172 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | f   | f
 t   | eric_enodeb_cell_201609   | relation   |16402 |  193318232 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_201610   | relation   |16402 |  193318241 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_201611   | relation   |16402 |  206004215 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_201612   | relation   |16402 |  232980573 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_201701   | relation   |16402 |  259721731 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_201702   | relation   |16402 |  291841516 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_20170301 | relation   |16402 |  384321707 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_20170302 | relation   |16402 |  385254657 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
 t   | eric_enodeb_cell_20170303 | relation   |16402 |  386180686 | 
 |   | |   | |   |  | 
16/38159763|   530 | ShareUpdateExclusiveLock | t   | f
...

ts=# SELECT granted, relation::regclass, * FROM pg_locks WHERE pid=20488 ORDER 
BY 1,2,3;
-[ RECORD 1 ]--+--
granted| f
locktype   | virtualxid
virtualxid | 22/4317099
virtualtransaction | 19/28401734
pid| 20488
mode   | ShareLock
granted| f
fastpath   | f

ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks 
WHERE virtualxid='22/4317099' ORDER BY 1,2,3;
 granted |  pid  | mode  | virtualtransaction | virtualxid 
-+---+---++
 f   | 20488 | ShareLock | 19/28401734| 22/4317099
 t   |  6471 | ExclusiveLock | 22/4317099 | 22/4317099

[pryzbyj@database ~]$ ps -O lstart -C reindex,pg_repack
  PID  STARTED S TTY  TIME COMMAND
20038 Wed Mar  7 01:00:00 2018 S ?00:00:00 /bin/sh 
/usr/local/sbin/reindex
20484 Wed Mar  7 01:00:14 2018 S ?00:00:00 /bin/sh 
/usr/local/sbin/reindex
20485 Wed Mar  7 01:00:14 2018 S ?00:00:00 /usr/pgsql-10/bin/pg_repack 
-E warning --no-kill-backend -d ts -i eric_enodeb_cell_20180304_site_idx -Ss 
oldindex

Note, I believe last night our backup job would've run for longer and processed
(many) more tables than usual, looping around pg_dump --snapshot.

Is it a bug that this isn't caught by a deadlock detector and cancelled?

Thanks,
Justin

Re: circular wait not triggering deadlock ?

[Justin Pryzby]

On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > Running pg10.2, I have a handful of maintenance jobs run in the middle of 
> > the
> > night, which appear to have gotten stuck waiting on each other..
> 
> > ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM 
> > pg_locks WHERE virtualxid='22/4317099' ORDER BY 1,2,3;
> >  granted |  pid  | mode  | virtualtransaction | virtualxid 
> > -+---+---++
> >  f   | 20488 | ShareLock | 19/28401734| 22/4317099
> >  t   |  6471 | ExclusiveLock | 22/4317099 | 22/4317099
> 
> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> What's that one doing?

Um, I thought I had kept track of all two pids but looks not..

query| SELECT pg_export_snapshot();

So that explains that.
I already killed one proc, but..

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, 
virtualxid FROM pg_locks WHERE pid=17248 ORDER BY 1;
 granted |relation |  pid  |  mode   | 
virtualtransaction | virtualxid
-+-+---+-++
 f   | eric_enodeb_cell_metrics| 17248 | AccessShareLock | 
27/1755026 |

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, 
virtualxid FROM pg_locks WHERE relation='eric_enodeb_cell_metrics'::regclass 
ORDER BY 1; --pid=17248  virtualxid='22/4317099' ORDER BY 1,2,3;
 granted | relation |  pid  |   mode   | 
virtualtransaction | virtualxid
-+--+---+--++
 f   | eric_enodeb_cell_metrics | 22961 | AccessShareLock  | 
31/1337307 |
 f   | eric_enodeb_cell_metrics | 17248 | AccessShareLock  | 
27/1755026 |
 f   | eric_enodeb_cell_metrics | 28357 | AccessShareLock  | 
28/1118276 |
 f   | eric_enodeb_cell_metrics | 21846 | AccessExclusiveLock  | 
29/830016  |
 f   | eric_enodeb_cell_metrics | 16592 | AccessExclusiveLock  | 
18/38156962|
 f   | eric_enodeb_cell_metrics | 18941 | AccessShareLock  | 
32/838769  |
 t   | eric_enodeb_cell_metrics |   530 | ShareUpdateExclusiveLock | 
16/38159763|
(7 rows)

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid 
IN(530,17248);
   530 | active | ALTER TABLE eric_enodeb_cell_metrics* ALTER start_time SET 
STATISTICS 400
 17248 | active | SELECT c.tableoid, c.oid, c.relname, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(
CASE WHEN c.relkind = 'S' 

ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks 
WHERE relation='eric_enodeb_cell_20180304'::regclass ;
 granted |  pid  |   mode   | virtualtransaction | virtualxid
-+---+--++
 t   | 20488 | ShareUpdateExclusiveLock | 19/28401734|
 f   |   530 | ShareUpdateExclusiveLock | 16/38159763|

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid=20488;
  pid  | state  |   
left
---++---
 20488 | active | CREATE INDEX CONCURRENTLY index_1113966210 ON 
eric_enodeb_cell_20180304 USING btree (site_id) WITH (fillfactor='100') 
TABLESPACE oldindex

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, 
virtualxid, virtualtransaction FROM pg_locks WHERE pid=20488 ORDER BY 1;
 granted |  pid  | relation  |  locktype  |virtualxid   
 | virtualxid  | virtualtransaction 
-+---+---++--+-+
 f   | 20488 |   | virtualxid | ShareLock   
 | 22/4317099  | 19/28401734

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, 
virtualtransaction FROM pg_locks WHERE virtualxid='22/4317099' ORDER BY 1;
 granted |  pid  | relation |  locktype  |  virtualxid   | virtualtransaction 
-+---+--++---+
 f   | 20488 |  | virtualxid | ShareLock | 19/28401734
 t   |  6471 |  | virtualxid | ExclusiveLock | 22/4317099
(2 rows)

So...I gather ALT

Re: circular wait not triggering deadlock ?

[Justin Pryzby]

On Thu, Mar 08, 2018 at 03:05:36PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> >> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> >> What's that one doing?
> 
> > Um, I thought I had kept track of all two pids but looks not..
> > query| SELECT pg_export_snapshot();
> 
> pg_export_snapshot doesn't block for other transactions, though.
> Further down, you have output that confirms that:
> 
> >   6471 | idle in transaction | psql  | SELECT pg_export_snapshot();
> 
> That SELECT is the most recently completed command, not the current one.
> So now the question is what the connected application is waiting for
> before committing.

I believe it does an idle loop around sleep(1), until all the pg_dump
--snapshot children are done.

Justin

relkind='p' has no pg_stat_user_tables

[Justin Pryzby]

I (finally) realized that my script for ANALYZEing parents of table hierarchies
every month or so was looping around the same parent tables every night due to
no stats for date of last last analysis.

I guess that's deliberate/known and maybe related to relkind='p' having no
relfilenode.

Is there any good workaround other than making stampfiles or making my own
"last analyzed" table?

Thanks,
Justin

Re: relkind='p' has no pg_stat_user_tables

[Justin Pryzby]

On Thu, May 03, 2018 at 07:44:24AM -0700, Adrian Klaver wrote:
> On 05/03/2018 07:14 AM, Justin Pryzby wrote:
> >I (finally) realized that my script for ANALYZEing parents of table 
> >hierarchies
> >every month or so was looping around the same parent tables every night due 
> >to
> >no stats for date of last last analysis.
> 
> Would help to see the script.

I reproduced it more simply than the 300 line script:

postgres=# CREATE TABLE t(i int)PARTITION BY RANGE(i);
postgres=# CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (9);
postgres=# INSERT INTO t1 VALUES(1),(2);
postgres=# ANALYZE VERBOSE t;
postgres=# SELECT * FROM pg_stat_user_tables WHERE relname='t';
(0 rows)
postgres=# SELECT 1 FROM pg_statistic WHERE starelid='t'::regclass;
?column? | 1

Justin

Re: relkind='p' has no pg_stat_user_tables

[Justin Pryzby]

On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote:
> On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> >>https://www.postgresql.org/docs/10/static/sql-createtable.html
> >>
> >>"A partitioned table is divided into sub-tables (called partitions), which
> >>are created using separate CREATE TABLE commands. The partitioned table is
> >>itself empty. A data row inserted into the table is routed to a partition
> >>based on the value of columns or expressions in the partition key. ... "
> >
> >Yeah, but I think Justin has a valid question from the POV of the user:
> >how can we figure out if we need to re-run analyze on a partitioned
> >table, if the time of last analyze is not stored anywhere?
> 
> I agree. The only thing I can think of is, that knowing :
> 
> ANALYZE VERBOSE t;
> 
> walks the inheritance tree, look at the pg_stat_user_tables for one of the
> children for the last time analyzed.

I think I can make this work for my purposes:

SELECT MIN(GREATEST(last_analyze,last_autoanalyze))
FROM pg_stat_user_tables psut
JOIN pg_inherits i
ON i.inhrelid=psut.relid
WHERE i.inhparent=...

I was about to say that it's perhaps more correct for relkind='r' parents, too.

But actually, it looks like for relkind='p', ANALYZE populates stats on child
tables in addition to the parent.  For relkind='r', the behavior (introduced in
PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent
(both "inherited" stats including children, and "ONLY" stats for the
potentially-nonempty parent).

I guess ability to update child tables' stats is a nice feature, but I'm
surprised.  I wonder if that was a deliberate/documented change ?

Justin

Re: relkind='p' has no pg_stat_user_tables

[Justin Pryzby]

On Thu, May 03, 2018 at 11:15:19AM -0700, Adrian Klaver wrote:
> On 05/03/2018 10:38 AM, Justin Pryzby wrote:
> >On Thu, May 03, 2018 at 09:31:12AM -0700, Adrian Klaver wrote:
> >>On 05/03/2018 09:20 AM, Alvaro Herrera wrote:
> >>>>https://www.postgresql.org/docs/10/static/sql-createtable.html
> >>>>
> >>>>"A partitioned table is divided into sub-tables (called partitions), which
> >>>>are created using separate CREATE TABLE commands. The partitioned table is
> >>>>itself empty. A data row inserted into the table is routed to a partition
> >>>>based on the value of columns or expressions in the partition key. ... "
> >>>
> >>>Yeah, but I think Justin has a valid question from the POV of the user:
> >>>how can we figure out if we need to re-run analyze on a partitioned
> >>>table, if the time of last analyze is not stored anywhere?
> >>
> >>I agree. The only thing I can think of is, that knowing :
> >>
> >>ANALYZE VERBOSE t;
> >>
> >>walks the inheritance tree, look at the pg_stat_user_tables for one of the
> >>children for the last time analyzed.
> >
> >I think I can make this work for my purposes:
> >
> >SELECT MIN(GREATEST(last_analyze,last_autoanalyze))
> >FROM pg_stat_user_tables psut
> >JOIN pg_inherits i
> >ON i.inhrelid=psut.relid
> >WHERE i.inhparent=...
> >
> >I was about to say that it's perhaps more correct for relkind='r' parents, 
> >too.
> >
> >But actually, it looks like for relkind='p', ANALYZE populates stats on child
> >tables in addition to the parent.  For relkind='r', the behavior (introduced 
> >in
> >PG9.0 as I recall) is that ANALYZE on parent creates stats only for parent
> >(both "inherited" stats including children, and "ONLY" stats for the
> >potentially-nonempty parent).
> >
> >I guess ability to update child tables' stats is a nice feature, but I'm
> >surprised.  I wonder if that was a deliberate/documented change ?
> 
> I was with you until I got to the above. You seem to be comparing apples and
> oranges unless I am missing something.

Yes, I was surprised about the difference between ANALYZE relkind_p
and relkind_r.

But I see that's a documented behavior I'd missed until now:

https://www.postgresql.org/docs/current/static/sql-analyze.html
|If the specified table is a partitioned table, both the inheritance statistics
|of the partitioned table as a whole and statistics of the individual partitions
|are updated.

Thanks,
Justin

postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

I recall seeing various discussions hoping that it'd been finally fixed - Just
wanted to report that this has happened now under postgres 10.4.

It looks like this is not related to: 0408e1ed599b06d9bca2927a50a4be52c9e74bb9
which is for "unexpected chunk number" (?)

Note that this is on the postgres database, which I think is where I saw it on
one of our internal VMs in the past (althought my memory indicates that may
have affected multiple DBs).  In the immediate case, this is customer's centos6
VM running under qemu/KVM: the same configuration as our internal VM which had
this issue (I just found a ticket dated 2016-10-06).

In case it helps:
 - the postgres database has a few things in it, primarily imported CSV logs.
   On this particular server, there's actually a 150GB table with old CSV logs
   from an script I fixed recently to avoid saving many lines than intended
   (something like for each session_id every session_line following an
   error_severity!='LOG')
 - I also have copies of pg_stat_bgwriter, pg_settings, and an aggregated copy
   of pg_buffercache here.
 - nagios: some scripts loop around all DBs; some maybe connect directly to
   postgres (for example, to list DBs).  However, I don't think check_postgres
   probably doesn't connect to postgres DB.

I'll defer fixing this for awhile in case someone wants me to save a copy of
the relation/toast/index.  From last time, I recall this just needs the right
combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
needing to realize the right combination of affected DB(s).

Thanks,
Justin

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> Justin Pryzby  writes:
> > I'll defer fixing this for awhile in case someone wants me to save a copy of
> > the relation/toast/index.  From last time, I recall this just needs the 
> > right
> > combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
> > needing to realize the right combination of affected DB(s).
> 
> If you could come up with such a sequence that causes the problem
> reproducibly, that would be of huge interest, and probably lead to
> a fix promptly.  But I don't think that we can do much by looking
> at the post-mortem state --- once the toast rows are gone, they're
> gone, especially if the table's been vacuumed since.

This is unlikely to allow reproducing it, but for sake of completeness here's a
fuller log.  I'll try to trigger on another DB.

postgres=# SELECT log_time, database, session_id, left(message,99) FROM 
postgres_log WHERE log_time BETWEEN '2018-05-19 07:49:01' AND '2018-05-19 
07:50' AND (database IS NULL OR database='postgres') ORDER BY 1 ;
 2018-05-19 07:49:02.232-06 |  | 5afbc238.382f | checkpoint complete: 
wrote 32175 buffers (6.1%); 0 WAL file(s) added, 0 removed, 8 recycled; write=
 2018-05-19 07:49:02.261-06 | postgres | 5b002b4e.65f2 | statement: SHOW 
server_version
 2018-05-19 07:49:02.278-06 | postgres | 5b002b4e.65f7 | statement: SELECT 
pg_get_indexdef('jrn_postgres_log_log_time_idx'::regclass)
 2018-05-19 07:49:02.29-06  | postgres | 5b002b4e.65f9 | statement: SELECT 1 
FROM information_schema.tables WHERE table_name='postgres_log' LIMIT 1
 2018-05-19 07:49:02.311-06 | postgres | 5b002b4e.65fb | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log'
 2018-05-19 07:49:02.324-06 | postgres | 5b002b4e.65fd | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_unique_idx'
 2018-05-19 07:49:02.338-06 | postgres | 5b002b4e.65ff | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_log_time_idx'
 2018-05-19 07:49:02.353-06 | postgres | 5b002b4e.6601 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_error_severity_idx'
 2018-05-19 07:49:02.37-06  | postgres | 5b002b4e.6603 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_message_system_idx'
 2018-05-19 07:49:02.39-06  | postgres | 5b002b4e.6605 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_error_message_idx'
 2018-05-19 07:49:02.405-06 | postgres | 5b002b4e.6607 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_duration_idx'
 2018-05-19 07:49:02.422-06 | postgres | 5b002b4e.6609 | statement: SELECT 1 
FROM pg_class WHERE relname='jrn_postgres_log_quotedquoted_idx'
 2018-05-19 07:49:02.464-06 | postgres | 5b002b4e.6619 | statement: SELECT 1 
FROM pg_class WHERE relname='postgres_log_2018_05_19_0700'
 2018-05-19 07:49:02.482-06 | postgres | 5b002b4e.661c | statement: COPY 
postgres_log_2018_05_19_0700 FROM 
'/var/log/postgresql/postgresql-2018-05-19_074617
 2018-05-19 07:49:04.711-06 | postgres | 5b002b50.6627 | statement: SELECT 1 
FROM pg_class WHERE relname='postgres_log_2018_05_19_0700'
 2018-05-19 07:49:04.724-06 | postgres | 5b002b50.662a | statement: COPY 
postgres_log_2018_05_19_0700 FROM 
'/var/log/postgresql/postgresql-2018-05-19_074643
 2018-05-19 07:49:06.803-06 | postgres | 5b002b52.6637 | statement: SELECT 
pg_get_indexdef('jrn_postgres_log_duration_idx'::regclass)
 2018-05-19 07:49:06.837-06 | postgres | 5b002b52.6639 | statement: SELECT 
inhrelid::regclass::text FROM pg_inherits i LEFT JOIN pg_constraint c ON 
i.inhrel
 2018-05-19 07:49:06.867-06 | postgres | 5b002b52.663b | statement: SELECT 
inhrelid::regclass::text FROM pg_inherits WHERE 
inhparent='postgres_log'::regclas
 2018-05-19 07:49:06.918-06 | postgres | 5b002b52.6641 | statement: SELECT 
log_time

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 11:24:57AM -0500, Justin Pryzby wrote:
> On Sat, May 19, 2018 at 11:08:23AM -0400, Tom Lane wrote:
> > Justin Pryzby  writes:
> > > I'll defer fixing this for awhile in case someone wants me to save a copy 
> > > of
> > > the relation/toast/index.  From last time, I recall this just needs the 
> > > right
> > > combination of REINDEX/VACUUM/ANALYZE, and the only complication was me
> > > needing to realize the right combination of affected DB(s).
> > 
> > If you could come up with such a sequence that causes the problem
> > reproducibly, that would be of huge interest, and probably lead to
> > a fix promptly.  But I don't think that we can do much by looking
> > at the post-mortem state --- once the toast rows are gone, they're
> > gone, especially if the table's been vacuumed since.
> 
> This is unlikely to allow reproducing it, but for sake of completeness here's 
> a
> fuller log.  I'll try to trigger on another DB.

Did not take long...

[pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT datname 
FROM pg_database WHERE datallowconn"`; do for t in pg_statistic pg_attrdef 
pg_constraint; do echo "$db.$t..."; PGOPTIONS=-cstatement_timeout='9s' psql $db 
-qc "VACUUM FULL $t"; done; done; done

...
postgres.pg_statistic...
postgres.pg_attrdef...
postgres.pg_constraint...
template1.pg_statistic...
template1.pg_attrdef...
template1.pg_constraint...
ts.pg_statistic...
ERROR:  canceling statement due to statement timeout
ts.pg_attrdef...
ts.pg_constraint...
postgres.pg_statistic...
ERROR:  missing chunk number 0 for toast value 3372855171 in pg_toast_2619

I'm running this again on another DB, but I wonder if that's enough for anyone
else to reproduce it with some consistency ?  I think that took something like
10min before failing.

Justin

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> Hm, so was the timeout error happening every time through on that table,
> or just occasionally, or did you provoke it somehow?  I'm wondering how
> your 9s timeout relates to the expected completion time.

I did not knowingly provoke it :)

Note that my script's non-artificial failure this morning, vac full of
pg_statistic DIDN'T timeout but the relation before it (pg_attrdef) DID.  I
guess the logs I sent earlier were incomplete.

I don't know if it times out every time..but I'm thinking timeout is
implicated, but I don't see how a time of on a previous command can cause an
error on a future session, for a non-"shared" relation.

However, I see this happened (after a few hours) on one server where I was
looping WITHOUT timeout.  So hopefully they have the same root cause and
timeout will be a good way to help trigger it.

postgres.pg_statistic...
ERROR:  missing chunk number 0 for toast value 615791167 in 
pg_toast_2619
Sat May 19 17:18:03 EDT 2018

I should have sent the output from my script:

<

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> Justin Pryzby  writes:
> > [pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT 
> > datname FROM pg_database WHERE datallowconn"`; do for t in pg_statistic 
> > pg_attrdef pg_constraint; do echo "$db.$t..."; 
> > PGOPTIONS=-cstatement_timeout='9s' psql $db -qc "VACUUM FULL $t"; done; 
> > done; done
> 
> > ...
> > postgres.pg_statistic...
> > postgres.pg_attrdef...
> > postgres.pg_constraint...
> > template1.pg_statistic...
> > template1.pg_attrdef...
> > template1.pg_constraint...
> > ts.pg_statistic...
> > ERROR:  canceling statement due to statement timeout
> > ts.pg_attrdef...
> > ts.pg_constraint...
> > postgres.pg_statistic...
> > ERROR:  missing chunk number 0 for toast value 3372855171 in pg_toast_2619
> 
> Hm, so was the timeout error happening every time through on that table,
> or just occasionally, or did you provoke it somehow?  I'm wondering how
> your 9s timeout relates to the expected completion time.

Actually statement_timeout isn't essential for this (maybe it helps to triggers
it more often - not sure).

Could you try:
time sh -ec 'while :; do time psql postgres -c "VACUUM FULL VERBOSE 
pg_toast.pg_toast_2619"; psql postgres -c "VACUUM FULL VERBOSE pg_statistic"; 
done'; date

Three servers experienced error within 30min, but one server didn't fail until
12h later, and a handful others still haven't failed..

Does this help at all ?
 2018-05-24 21:57:49.98-03  | 5b075f8d.1ad1 | LOG| pryzbyj   | 
postgres | statement: VACUUM FULL VERBOSE pg_toast.pg_toast_2619
 2018-05-24 21:57:50.067-03 | 5b075f8d.1ad1 | INFO   | pryzbyj   | 
postgres | vacuuming "pg_toast.pg_toast_2619"
 2018-05-24 21:57:50.09-03  | 5b075f8d.1ad1 | INFO   | pryzbyj   | 
postgres | "pg_toast_2619": found 0 removable, 408 nonremovable row versions in 
99 pages
 2018-05-24 21:57:50.12-03  | 5b075f8e.1ada | LOG| pryzbyj   | 
postgres | statement: VACUUM FULL VERBOSE pg_statistic
 2018-05-24 21:57:50.129-03 | 5b075f8e.1ada | INFO   | pryzbyj   | 
postgres | vacuuming "pg_catalog.pg_statistic"
 2018-05-24 21:57:50.185-03 | 5b075f8e.1ada | ERROR  | pryzbyj   | 
postgres | missing chunk number 0 for toast value 3382957233 in pg_toast_2619

Some thing; this server has autovacuum logging, although it's not clear to me
if that's an essential component of the problem, either:
 2018-05-24 21:16:39.856-06 | LOG   | 5b078017.7b99 | pryzbyj   | postgres | 
statement: VACUUM FULL VERBOSE pg_toast.pg_toast_2619
 2018-05-24 21:16:39.876-06 | LOG   | 5b078010.7968 |   |  | 
automatic vacuum of table "postgres.pg_toast.pg_toast_2619": index scans: 1 
   +
|   |   |   |  | 
pages: 0 removed, 117 r
 2018-05-24 21:16:39.909-06 | INFO  | 5b078017.7b99 | pryzbyj   | postgres | 
vacuuming "pg_toast.pg_toast_2619"
 2018-05-24 21:16:39.962-06 | INFO  | 5b078017.7b99 | pryzbyj   | postgres | 
"pg_toast_2619": found 0 removable, 492 nonremovable row versions in 117 pages
 2018-05-24 21:16:40.025-06 | LOG   | 5b078018.7b9b | pryzbyj   | postgres | 
statement: VACUUM FULL VERBOSE pg_statistic
 2018-05-24 21:16:40.064-06 | INFO  | 5b078018.7b9b | pryzbyj   | postgres | 
vacuuming "pg_catalog.pg_statistic"
 2018-05-24 21:16:40.145-06 | ERROR | 5b078018.7b9b | pryzbyj   | postgres | 
missing chunk number 0 for toast value 765874692 in pg_toast_2619

Or this one?

postgres=# SELECT log_time, database, user_name, session_id, left(message,999) 
FROM postgres_log WHERE (log_time>='2018-05-24 19:56' AND log_time<'2018-05-24 
19:58') AND (database='postgres' OR database IS NULL OR user_name IS NULL OR 
user_name='pryzbyj') AND message NOT LIKE 'statement:%' ORDER BY 1;

log_time   | 2018-05-24 19:56:35.396-04
database   | 
user_name  | 
session_id | 5b075131.3ec0
left   | skipping vacuum of "pg_toast_2619" --- lock not available

...

log_time   | 2019-05-24 19:57:35.78-04
database   | 
user_name  | 
session_id | 5b07516d.445e
left   | automatic vacuum of table "postgres.pg_toast.pg_toast_2619": index 
scans: 1
   : pages: 0 removed, 85 remain, 0 skipped due to pins, 0 skipped 
frozen
   : tuples: 1 removed, 348 remain, 0 are dead but not yet removable, 
oldest xmin: 63803106
   : buffer usage: 179 hits, 4 misses, 87 dirtied
   : avg read rate: 1.450 MB/s, avg write rate: 31.531 MB/s
   : system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s

log_time   | 2018-05-24 19:57:35.879-04
database   | postgres
user_name  | pryzbyj
session_id | 5b07516f.447f
left   | missing chunk number 0 for toast value 624341680 in pg_toast_2619

log_time   | 2018-05-24 19:57:44.332-04
database   |
user_name  |
session_id | 5af9fda3.70d5
left   | checkpoint starting: time

Justin

\d t: ERROR: XX000: cache lookup failed for relation

[Justin Pryzby]

Is that considered an actionable problem?

Encountered while trying to reproduce the vacuum full pg_statistic/toast_2619
bug; while running a loop around VAC FULL and more in another session:
[1]-  Running { time sh -ec 'while :; do psql --port 5678 
postgres -qc "VACUUM FULL pg_toast.pg_toast_2619"; psql --port 5678 postgres 
-qc "VACUUM FULL pg_statistic"; done'; date; } &
[2]+  Running time while :; do
psql postgres --port 5678 -c "INSERT INTO t SELECT i FROM 
generate_series(1,99) i"; sleep 1; for a in `seq 999`;
do  
psql postgres --port 5678 -c "ALTER TABLE t ALTER i TYPE int USING 
i::int"; sleep 1; psql postgres --port 5678 -c "ALTER TABLE t ALTER i TYPE 
bigint"; sleep 1;
done; psql postgres --port 5678 -c "TRUNCATE t"; sleep 1;
done &

[pryzbyj@kgfs66telsadb ~]$ psql --port 5678 postgres -x
psql (11beta1)
Type "help" for help.
...
postgres=# \set VERBOSITY verbose 
postgres=# \d t
ERROR:  XX000: cache lookup failed for relation 8096742
LOCATION:  flatten_reloptions, ruleutils.c:11065

Justin

notes from transition to relkind='p'

[Justin Pryzby]

Before I forget any more, this is a brain of issues/considerations/concerns
with our (partial) transition to partitioned tables over the last ~9 months.  I
believe these are all documented behaviors, but could be seen by users as a
gratuitious/unexpected change or rough edge and the differences could perhaps
be mitigated.  I realize there's maybe no good time or way to change most of
these, but maybe the list will be helpful to somebody in avoiding unexpected
transitional issues.

 . DROP TABLE on a parent no longer fails without CASCADE (dependencies of
   relkind='p' are deptype='a' "soft" dependencies)
   8b4d582d279d784616c228be58af1e39aa430402
 . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r
   only updates stats for parent); it's unclear if there's any reason why it
   wasn't always done this way(?).  I end up having to conditionize processing
   based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3
 . The docs say: if detaching/re-attach a partition, should first ADD CHECK to
   avoid a slow ATTACH operation.  Perhaps DETACHing a partition could
   implicitly CREATE a constraint which is usable when reATTACHing?
 . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc).  Maybe
   the view could do the needed CASE ... (SELECT min() FROM pg_inherits JOIN 
psut)
 . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to
   suggest one would want to avoid that; but consider: we ended up adding both
   shell and python logic to parse the table name to allow detaching and
   reattaching partitions.  I think it'd be a nice if the bounds were inferred
   if there was a single constraint on the partition key.
 . ALTER TABLE ATTACH has reversed order of child vs parent relative to
   NO/INHERIT.
 . And actually, having both ALTER TABLE DE/TACH vs NO/INHERIT is itself messy:
   we ended up having branches (both shell and python) to handle both cases (at
   least for a transitional period, but probably we'll need to continue
   handling both into the indeterminate future).

Cheers,
Justin

Re: notes from transition to relkind='p'

[Justin Pryzby]

On Sun, Jun 03, 2018 at 12:13:49PM -0700, Adrian Klaver wrote:
> On 06/01/2018 03:14 PM, Justin Pryzby wrote:
> >Before I forget any more, this is a brain of issues/considerations/concerns

Should have said brain DUMP

Keep in mind, I've phrased these as notes, maybe useful to someone else, maybe
not an issue to most people, and maybe not possible or worth changing behavior
for, since these are already in last year's published release.

> >with our (partial) transition to partitioned tables over the last ~9 months. 
> > I
> >believe these are all documented behaviors, but could be seen by users as a
> >gratuitious/unexpected change or rough edge and the differences could perhaps
> >be mitigated.  I realize there's maybe no good time or way to change most of
> >these, but maybe the list will be helpful to somebody in avoiding unexpected
> >transitional issues.
> 
> It is not clear from above what you are transitioning from.

Transitioning from inheritence based partitioning with empty relkind='r'
parents to PG10 declarative partitioning.

> >  . ANALYZE relkind_p also updates stats of child (whereas ANALYZE relkind_r
> >only updates stats for parent); it's unclear if there's any reason why it
> >wasn't always done this way(?).  I end up having to conditionize 
> > processing
> >based on relkind. 3c3bb99330aa9b4c2f6258bfa0265d806bf365c3
> 
> Because there is a clear partition dependency in the declarative
> partitioning scheme. In the INHERIT scheme(which I assume is what you are
> talking about) the partitioning is optional.

I see your point - but AFAICT there's no reason why ANALYZE relkind_r couldn't
have always acted "recursively" on any children, or if there's any reason it
couldn't conceivably have been changed in v10, for consistency, if that was
determined to outweigh the disruptiveness/inconvenience of changing an existing
behavior.

> >  . The docs say: if detaching/re-attach a partition, should first ADD CHECK 
> > to
> >avoid a slow ATTACH operation.  Perhaps DETACHing a partition could
> >implicitly CREATE a constraint which is usable when reATTACHing?
> 
> I personally would not like that. If I wanted to maintain the partition
> constraint I would keep the table in the partition. If I was DETACHing it
> then it would be to get rid of it or have it exist as a stand alone table.
> If I where to keep it deciding what constraints to maintain should be up to
> me.

Noted; for us, the reasons we uninherit/detach (and then inherit/attach) are:

 - to avoid massive space use and long, disruptive table lock while executing a
   typo-promoting ALTER.
 - when ALTERing to a new tablespace, to avoid locking parent table, which is
   more disruptive than losing visibility of a partition's tuples;

> >  . relkind_p has no entry in pg_stat_user_tables (last_analyze, etc).  Maybe
> >the view could do the needed CASE ... (SELECT min() FROM pg_inherits 
> > JOIN psut)
> >  . ALTER TABLE ATTACH requires specifying bounds: Maybe it sounds naive to
> >suggest one would want to avoid that; but consider: we ended up adding 
> > both
> >shell and python logic to parse the table name to allow detaching and
> >reattaching partitions.  I think it'd be a nice if the bounds were 
> > inferred
> >if there was a single constraint on the partition key.
> 
> The above I am not sure how you envision that working, especially the
> inferring the key part. Having the program guess at what I want a partition
> to be constrained by is something I would need fully explained to me.

I mean the partition bounds of an partition of a relkind='p' could (perhaps) be
changed into a CHECK constraint at DETACH time, avoiding full table scan and
long lock if it's later re-ATTACHED.  If implemented, it could be requested
rather than strictly implied..

ALTER TABLE p DETACH r ADD CONSTRAINT c;
...
ALTER TABLE p ATTACH r FOR VALUES INFERRED BY CONSTRAINT c;
-- must be a check constraint, must be a "simple" list or range with a single
column, etc.  Maybe not feasible for hash partitioning?

Justin

Re: Code of Conduct plan

[Justin Clift]

On 4 Jun 2018, at 17:59, Joshua D. Drake  wrote:
> On 06/03/2018 04:08 PM, Gavin Flower wrote:

>> I might possibly say that "I'm the master in this area" when talking to 
>> someone on a technical subject.  In the sense that I'm better at that 
>> particular skill, but some hypersensitive American could get their knickers 
>> in a twist (notice, that in this context, no gender is implied -- also in 
>> using that that expression "get their knickers in a twist" could offend some 
>> snowflake) claiming that I'm suggesting that whoever 
> 
> "snowflake", I find that term hilarious others find it highly offensive. 
> Which is correct?

Like most things, it depends on context. ;)


>> I'm talking to is my slave!  I heard of an American university that doesn't 
>> want people to use the term master, like in an MSc, because of the history 
>> of slavery.
> 
> The PostgreSQL project already has this problem, note we don't use the terms 
> Master and Slave in reference to replication anymore.
> 
>> I've used the expressions "sacrifice a willing virgin" and "offering my 
>> first born to the gods" as ways to ensure success of resolving a technical 
>> issue.  The people I say that to, know what I mean -- and they implicitly 
>> know that I'm not seriously suggesting such conduct.  Yet, if I wrote that 
>> publicly, it is conceivable that someone might object!
> 
> Yes and that is a problem. We need to have some simple barrier of acceptance 
> that we are all adults here (or should act like adults). Knowing your 
> audience is important.
> 
>> Consider a past advertising campaign in Australia to sell government Bonds.  
>> They used two very common hand gestures that are very Australian.  Bond 
>> sales dropped.  On investigation, they found the bonds were mainly bought by 
>> old Greek people, who found the gestures obscene. The gestures?  Thumbs up, 
>> and the okay gesture formed by touching the thumb with the next finger -- 
>> nothing sexually suggestive to most Australians, but traditional Greeks 
>> found them offensive.
> 
> Using Australia as an example, my understanding is that the word c**t is part 
> of nomenclature but in the states the word is taboo and highly frowned upon.

Yes.  Us Aussie's often use the word "cunt".  Again, depends on context. :)

Personally... I don't think I've used it more than 5 times in total, in the 
years I've been in the UK.

Those times I did, it was _definitely_ not in a politically correct fashion.  
Nor online. YMMV.


>> Be very careful in attempting to codify 'correct' behaviour!
> 
> Correct. I think one way to look at all of this is, "if you wouldn't say it 
> to your boss or a client don't say it here". That too has problems but 
> generally speaking I think it keeps the restrictions rational.

Possibly a weird viewpoint, but I personally have a different way of looking at 
the CoC thing.

From my observations of people so far, it seems like there are two main 
GROUPings (pun intended :>)
of people:

* Those who like and want rules for everything.  "For without rules how will 
people know what to do?"
* Those who don't like nor want rules for everything.  "Stop trying to control 
me!  Let me work out an optimal approach myself!"

It's a scale thing, not black and white.

Personally, I'm somewhere near the middle (it varies slightly over time).

My point being, that when some threshold of "too many rules" is reached the 
people in the Community
who _don't_ like excess rules will leave.  Conversely, people who _need_ rules 
in order to feel
comfortable will start to stick around.

Neither group is intrinsically right nor wrong.  They just operate internally 
differently, and
have different needs.

Adding a CoC will change the quantity-of-fules mix _slightly_, depending on how 
in-your-face people
are with it.

Our Community will naturally adjust it's makeup over time to reflect this 
change.

Mentioning the above, as I hope we're going into this "eyes wide open". ;)

+ Justin

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Re: Code of Conduct plan

[Justin Clift]

On 4 Jun 2018, at 18:24, Justin Clift  wrote:

> Adding a CoC will change the quantity-of-fules mix _slightly_, depending on 
> how in-your-face people
> are with it.

s/quantity-of-fules/quantity-of-rules/

Interesting typo though. :)

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Re: Microsoft buys GitHub, is this a threat to open-source

[Justin Clift]

On 4 Jun 2018, at 18:31, Rich Shepard  wrote:
> On Mon, 4 Jun 2018, Joshua D. Drake wrote:
> 
>> No but it does show why using non open source platforms for open source
>> projects is an inherently bad idea.
> 
> Joshua,
> 
>  Sourceforge seems to be out of favor, too,  so are there any open source
> platforms that provide services that sourceforge and github do?

Both GitLab and BitBucket are commonly suggested.

Neither seems great, but when push comes to shove "they'll do". :)

For people that are ok with standing up their own servers, there are more
options.  Gitea (Open Source GitHub clone) is pretty good:

  https://gitea.io

It's also very efficient resource wise (unlike GitLab), so can run effectively
on tiny hardware.  Even Raspberry Pi level can do a decent job for small scale
stuff.

Naturally, anyone with team-sized needs would run it on appropriate hardware. ;)

+ Justin

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Re: Code of Conduct plan

[Justin Clift]

On 2018-06-04 22:26, Alvaro Herrera wrote:

On 2018-Jun-05, Gavin Flower wrote:


If we are all adults, then we don't need a CoC.


"We're all adults" is wishful thinking.  Some old people are just kids
who aged but didn't actually mature.


Also to point out... there is the occasional teen who does meaningful
stuff with Open Source.

So, "we are all adults here" might not actually be 100% correct. :D

+ Justin

Re: Microsoft buys GitHub, is this a threat to open-source

[Justin Clift]

On 2018-06-07 12:47, Vik Fearing wrote:

On 07/06/18 13:46, Thiemo Kellner wrote:

Zitat von Achilleas Mantzios :

Who hasn't missed sourceforge ? or ... freshmeat while we'are at it 
:)


I am sticking to sourceforge still. I never understood what people 
made

leave it.


For many people, this is why sourceforge died:
https://en.wikipedia.org/wiki/SourceForge#Project_hijackings_and_bundled_malware


Yeah.  The Wikipedia page mentions mentions 2015, however DICE (the new 
owners

of SourceForge at the time) introduced it a few years earlier.

This is one of the earlier calls to action about the problem:

  
http://blog.gluster.org/how-far-the-once-mighty-sourceforge-has-fallen/


SourceForge were *really* pissed at me for writing that.  As in, whinge
to my employer about me, threaten to get law people involved, etc.

They didn't get very far thankfully. :)

Anyway, we seem to be fairly off topic now...

+ Justin

Re: Code of Conduct plan

[Justin Clift]

On 2018-06-08 09:46, Simon Riggs wrote:


Would it not be better to consider arbitration as the first step in
dispute resolution?


This bit sounds like it'd need to be on a case-by-case basis.

It's pretty easy to imagine scenarios where arbitration wouldn't be
appropriate.

Whether or not they come about in the PG Community or not is a
different matter.

My point being that arbitration isn't necessarily automatically the
right direction.

I'd probably leave it up to the CoC team/people to figure it out. :)

+ Justin

ALTER TABLE SET (toast.asdf) is not rejected

[Justin Pryzby]

I noticed that this is accepted:

postgres=# ALTER TABLE t SET (toast.asdf=128);
ALTER TABLE

I thought since "toast" was a core namespace, it would've been rejected?

I recall having read a discussion about verifying these ... I wasn't able
to find what I was thinking of, but found this one.
https://www.postgresql.org/message-id/flat/20090114144332.GF24156%40alvh.no-ip.org

Justin

Re: ALTER TABLE SET (toast.asdf) is not rejected ... if a table has no toast

[Justin Pryzby]

On Mon, Jun 11, 2018 at 11:47:59AM -0400, Alvaro Herrera wrote:
> On 2018-Jun-11, Justin Pryzby wrote:
> 
> > I noticed that this is accepted:
> > 
> > postgres=# ALTER TABLE t SET (toast.asdf=128);
> > ALTER TABLE
> > 
> > I thought since "toast" was a core namespace, it would've been rejected?
> > 
> > I recall having read a discussion about verifying these ... I wasn't able
> > to find what I was thinking of, but found this one.
> > https://www.postgresql.org/message-id/flat/20090114144332.GF24156%40alvh.no-ip.org

> 
> Uh.  ISTM that this was lost during the 9.4 cycle, because that command *is*
> rejected in 9.3.
> alvherre=# ALTER TABLE t SET (toast.asdf=128);
> ERROR:  unrecognized parameter "asdf"

I'm assuming your "t" has a toast table.  I think this is properly rejected
when toast exists.

It looks like we were maybe both (partially?) fooled by behavior that's been
discussed multiple times over the years: SET (toast.*) is ignored for tables
which don't currently have any TOAST.
https://www.postgresql.org/message-id/flat/20090211194311.GP8924%40alvh.no-ip.org

Maybe all's well, but I suggest there's maybe an question which hasn't been
previously been disambiguated and raised: if a table has no toast, should SET
(toast.JUNK) be ignored, or rejected ?

BTW maybe I was originally thinking of this:
https://www.postgresql.org/message-id/flat/c4d71df2-9e0e-3912-dc81-9a72e080c238%40lab.ntt.co.jp#c4d71df2-9e0e-3912-dc81-9a72e080c...@lab.ntt.co.jp

Justin

pg_stat_activity.query_start in the future?

[Justin Pryzby]

I'm buried and not having any good ideas how to diagnose this or what else to
send, so here it is.  Feel free to tell me why I'm the one whose confused..

postgres=# SELECT pid, now(), query_start, state, query FROM pg_stat_activity 
WHERE pid=27757;
  pid  |  now  |  query_start  | state  
|query
---+---+---++-
 27757 | 2018-08-17 11:10:16.568429-04 | 2018-08-17 10:17:52.814282-04 | active 
| autovacuum: VACUUM ANALYZE public.eric_enodeb_cell_20180815
(1 row)

postgres=# \! ps -O lstart 27757
  PID  STARTED S TTY  TIME COMMAND
27757 Fri Aug 17 08:53:20 2018 S ?00:11:56 postgres: autovacuum worker 
process   xton
postgres=# \! date
Fri Aug 17 11:10:58 EDT 2018

Justin

OOM with many sorts

[Justin Pryzby]

One of our instances was killed:

| Out of memory: Kill process 27670 (postmaster) score 381 or sacrifice child
| Killed process 27670 (postmaster) total-vm:13207796kB, anon-rss:5736kB, 
file-rss:0kB, shmem-rss:12421696kB

The VM has: 32GB RAM, shared_buffers=12GB, work_mem=128MB 
Running only pg11.3

Trying to diagnose the issue by running a query on a narrower range of dates,
it looks to me that's due to many sorts nodes per worker.

Part of the query plan looks like this:

 [...]
 ->  Finalize GroupAggregate (actual rows=1788 loops=1)
   Group Key: huawei_umts_ucell_20181217.sect_id, 
(to_char(huawei_umts_ucell_20181217.start_time, '-MM'::text))
   ->  Gather Merge (actual rows=56921 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 ->  Sort (actual rows=18974 loops=3)
   Sort Key: huawei_umts_ucell_20181217.sect_id, 
(to_char(huawei_umts_ucell_20181217.start_time, '-MM'::text))
   Sort Method: quicksort  Memory: 36499kB
   Worker 0:  Sort Method: quicksort  Memory: 40275kB
   Worker 1:  Sort Method: quicksort  Memory: 40263kB
   ->  Parallel Append (actual rows=18974 loops=3)
 ->  Partial GroupAggregate (actual rows=1783 
loops=1)
   Group Key: 
huawei_umts_ucell_20181217.sect_id, 
(to_char(huawei_umts_ucell_20181217.start_time, '-MM'::text))
   ->  Sort (actual rows=42792 loops=1)
 Sort Key: 
huawei_umts_ucell_20181217.sect_id, 
(to_char(huawei_umts_ucell_20181217.start_time, '-MM'::text))
 Worker 0:  Sort Method: quicksort  
Memory: 44997kB
 ->  Parallel Seq Scan on 
huawei_umts_ucell_20181217 (actual rows=42792 loops=1)
   Filter: ((start_time >= 
'2018-12-01 00:00:00'::timestamp without time zone) AND (start_time < 
'2019-01-01 00:00:00'::timestamp without time zone))
 ->  Partial GroupAggregate (actual rows=1783 
loops=1)
   Group Key: 
huawei_umts_ucell_20181216.sect_id, 
(to_char(huawei_umts_ucell_20181216.start_time, '-MM'::text))
   ->  Sort (actual rows=42792 loops=1)
 Sort Key: 
huawei_umts_ucell_20181216.sect_id, 
(to_char(huawei_umts_ucell_20181216.start_time, '-MM'::text))
 Worker 1:  Sort Method: quicksort  
Memory: 44997kB
 ->  Parallel Seq Scan on 
huawei_umts_ucell_20181216 (actual rows=42792 loops=1)
   Filter: ((start_time >= 
'2018-12-01 00:00:00'::timestamp without time zone) AND (start_time < 
'2019-01-01 00:00:00'::timestamp without time zone))

 [...many partial GroupAggregate/Sort repeated here 
for ~200 child tables...]

This isn't the first time I've had to reduce work_mem on a parallel query to
avoid OOM, but it seems unreasonable to be concerned with 50MB sorts.

It looks like the results of each Sort node stay in RAM, during processing of
additional sort nodes (is that required?)

I SET max_parallel_workers_per_gather=0 and dumped mem context:
...
PortalContext: 203948152 total in 25756 blocks; 176600 free (319 chunks); 
203771552 used:
...
Grand total: 1918118632 bytes in 30716 blocks; 192472208 free (3685 chunks); 
1725646424 used

The total shows ~1700MB but the largest context is only 200MB.
"Caller tuples" accounts for 1400MB.

awk '/Caller tuples/{s+=$3}END{print s/1024^3}' /tmp/mem-context
1.44043

I'm attaching full plan and mem context.

It doesn't seem to be changed under pg12b2, which is concerning since PG12
advertizes that it gracefully supports "thousands" of partitions.  It seems to
me that would require tiny work_mem, which would be devastating to some
workloads.  This is not a contrived test case, it's one of our reports run
across ~10 months of history with weekly granularity.

Thanks in advance for any advice.

Justin

QUERY PLAN  
  
--
 GroupAggregate (actual rows=277 loops=1)
   Group Key: sites.site_office, sites.site_

Re: postgres=# VACUUM FULL pg_statistic => ERROR: missing chunk number 0 for toast value .. in pg_toast_2619

[Justin Pryzby]

Moving this old thread to -hackers
https://www.postgresql.org/message-id/flat/20180519142603.GA30060%40telsasoft.com

I wanted to mention that this seems to still be an issue, now running pg11.5.

log_time   | 2019-08-30 23:20:00.118+10
user_name  | postgres
database   | ts
session_id | 5d69227e.235
session_line   | 1
command_tag| CLUSTER
session_start_time | 2019-08-30 23:19:58+10
error_severity | ERROR
sql_state_code | XX000
message| unexpected chunk number 1 (expected 0) for toast value 
2369261203 in pg_toast_2619
query  | CLUSTER pg_statistic USING 
pg_statistic_relid_att_inh_index
application_name   | psql

Note that my original report was for "missing" chunk during "VACUUM FULL", and
the current error is "unexpected chunk" during CLUSTER.  I imagine that's
related issue.  I haven't seen this in awhile (but stopped trying to reproduce
it long ago).  A recently-deployed update to this maintenance script is
probably why it's now doing CLUSTER.

On Fri, May 25, 2018 at 08:49:50AM -0500, Justin Pryzby wrote:
> On Sat, May 19, 2018 at 02:39:26PM -0400, Tom Lane wrote:
> > Justin Pryzby  writes:
> > > [pryzbyj@database ~]$ while :; do for db in `psql postgres -Atc "SELECT 
> > > datname FROM pg_database WHERE datallowconn"`; do for t in pg_statistic 
> > > pg_attrdef pg_constraint; do echo "$db.$t..."; 
> > > PGOPTIONS=-cstatement_timeout='9s' psql $db -qc "VACUUM FULL $t"; done; 
> > > done; done
> > 
> > > ...
> > > postgres.pg_statistic...
> > > postgres.pg_attrdef...
> > > postgres.pg_constraint...
> > > template1.pg_statistic...
> > > template1.pg_attrdef...
> > > template1.pg_constraint...
> > > ts.pg_statistic...
> > > ERROR:  canceling statement due to statement timeout
> > > ts.pg_attrdef...
> > > ts.pg_constraint...
> > > postgres.pg_statistic...
> > > ERROR:  missing chunk number 0 for toast value 3372855171 in pg_toast_2619
> > 
> > Hm, so was the timeout error happening every time through on that table,
> > or just occasionally, or did you provoke it somehow?  I'm wondering how
> > your 9s timeout relates to the expected completion time.
> 
> Actually statement_timeout isn't essential for this (maybe it helps to 
> triggers
> it more often - not sure).
> 
> Could you try:
> time sh -ec 'while :; do time psql postgres -c "VACUUM FULL VERBOSE 
> pg_toast.pg_toast_2619"; psql postgres -c "VACUUM FULL VERBOSE pg_statistic"; 
> done'; date
> 
> Three servers experienced error within 30min, but one server didn't fail until
> 12h later, and a handful others still haven't failed..
> 
> Does this help at all ?
>  2018-05-24 21:57:49.98-03  | 5b075f8d.1ad1 | LOG| pryzbyj   | 
> postgres | statement: VACUUM FULL VERBOSE pg_toast.pg_toast_2619
>  2018-05-24 21:57:50.067-03 | 5b075f8d.1ad1 | INFO   | pryzbyj   | 
> postgres | vacuuming "pg_toast.pg_toast_2619"
>  2018-05-24 21:57:50.09-03  | 5b075f8d.1ad1 | INFO   | pryzbyj   | 
> postgres | "pg_toast_2619": found 0 removable, 408 nonremovable row versions 
> in 99 pages
>  2018-05-24 21:57:50.12-03  | 5b075f8e.1ada | LOG| pryzbyj   | 
> postgres | statement: VACUUM FULL VERBOSE pg_statistic
>  2018-05-24 21:57:50.129-03 | 5b075f8e.1ada | INFO   | pryzbyj   | 
> postgres | vacuuming "pg_catalog.pg_statistic"
>  2018-05-24 21:57:50.185-03 | 5b075f8e.1ada | ERROR  | pryzbyj   | 
> postgres | missing chunk number 0 for toast value 3382957233 in pg_toast_2619
> 
> Some thing; this server has autovacuum logging, although it's not clear to me
> if that's an essential component of the problem, either:
>  2018-05-24 21:16:39.856-06 | LOG   | 5b078017.7b99 | pryzbyj   | postgres | 
> statement: VACUUM FULL VERBOSE pg_toast.pg_toast_2619
>  2018-05-24 21:16:39.876-06 | LOG   | 5b078010.7968 |   |  | 
> automatic vacuum of table "postgres.pg_toast.pg_toast_2619": index scans: 1   
>  +
> |   |   |   |  | 
> pages: 0 removed, 117 r
>  2018-05-24 21:16:39.909-06 | INFO  | 5b078017.7b99 | pryzbyj   | postgres | 
> vacuuming "pg_toast.pg_toast_2619"
>  2018-05-24 21:16:39.962-06 | INFO  | 5b078017.7b99 | pryzbyj   | postgres | 
> "pg_toast_2619": found 0 removable, 492 nonremovable row versions in 117 pages
>  2018-05-24 21:16:40.025-06 | LOG   | 5b078018.7b9b | pryzbyj   | 

v12 and pg_restore -f-

[Justin Pryzby]

This is a "heads up" for others upgrading to v12.  I found a solution for our
use case, but it'd be easy to miss this, even if you read the release notes.

I saw this and updated our scripts with pg_restore -f-
https://www.postgresql.org/docs/12/release-12.html
|In pg_restore, require specification of -f - to send the dump contents to 
standard output (Euler Taveira)
|Previously, this happened by default if no destination was specified, but that 
was deemed to be unfriendly.

What I didn't realize at first is that -f- has no special meaning in v11 - it
just writes a file called ./-  And it's considered untennable to change
behavior of v11.

In our use, that was being piped to sed, which then saw nothing on its stdin
and just exits..  I changed our script to use pg_restore -f /dev/stdout, which
seems to be portable across postgres versions for the OS distribution we're
running.  Unfortunately, I can't think of anything portable across *OS* or
useful to include in documentation.  In the worst case, someone might need to
call pg_restore differently based on its version.

Justin

Fwd: PG12 autovac issues

[Justin King]

Apologies, I accidentally sent this to the pgsql-admin list initially
but intended it go here:

We have a database that isn't overly large (~20G), but gets incredibly
frequent updates.  Here's an example table:

feedi=# select * from pg_stat_all_tables where schemaname =
'production' and relname = 'tita';
relid = 16786
schemaname = production
relname = tita
seq_scan = 23365
seq_tup_read = 403862091
idx_scan = 26612759248
idx_tup_fetch = 19415752701
n_tup_ins = 24608806
n_tup_upd = 4207076934
n_tup_del = 24566916
n_tup_hot_upd = 4073821079
n_live_tup = 79942
n_dead_tup = 71969
n_mod_since_analyze = 12020
last_vacuum = 2020-03-17 15:35:19.588859+00
last_autovacuum = 2020-03-17 21:31:08.248598+00
last_analyze = 2020-03-17 15:35:20.372875+00
last_autoanalyze = 2020-03-17 22:04:41.76743+00
vacuum_count = 9
autovacuum_count = 135693
analyze_count = 9
autoanalyze_count = 495877

As you can see in this table, there are only ~80K rows, but billions
of updates.  What we have observed is that the frozenxid reaches the
200M mark fairly quickly because of the amount of activity.  What is
interesting is that this happens with the 'postgres' and 'template1'
databases as well and there is absolutely no activity in those
databases.

When the 'postgres' and/or 'template1' databases hit the
freeze_max_age, there are cases where it kicks off an aggressive
autovac of those tables which seems to prevent autovacs from running
elsewhere.  Oddly, this is not consistent, but that condition seems to
be required.  We have observed this across multiple PG12 servers (dev,
test, staging, production) all with similar workloads.

$ grep -i vacuum /var/log/postgresql/postgres.log | cut -b 1-9 | uniq -c
 17 Mar 17 06
 34 Mar 17 07
 31 Mar 17 08
 31 Mar 17 09
 30 Mar 17 10
 34 Mar 17 11
 33 Mar 17 12
 19 Mar 17 13
 40 Mar 17 15
 31 Mar 17 16
 36 Mar 17 17
 34 Mar 17 18
 35 Mar 17 19
 35 Mar 17 20
 33 Mar 17 21

As you can see above, we end up having around ~33 autovac/hr, and
about 13:30 today, they stopped until we ran a "vacuum freeze verbose
analyze;" against the 'postgres' database (around 15:30) which then
caused the autovacs to resume running against the "feedi" database.

I'm completely perplexed as to what is happening and why it suddenly
started when we moved from PG10 > PG12.  The configs and workload are
essentially the same between versions.  We realize we could simply
increase the autovacuum_freeze_max_age, but that doesn't seem to
actually resolve anything -- it just pushes the problem out.  Has
anyone seen anything similar to this?

Thanks very much for the consideration.

Justin King
http://flightaware.com/

Re: Fwd: PG12 autovac issues

[Justin King]

On Tue, Mar 17, 2020 at 5:39 PM Adrian Klaver  wrote:
>
> On 3/17/20 3:22 PM, Justin King wrote:
> > Apologies, I accidentally sent this to the pgsql-admin list initially
> > but intended it go here:
> >
> > We have a database that isn't overly large (~20G), but gets incredibly
> > frequent updates.  Here's an example table:
> >
> > feedi=# select * from pg_stat_all_tables where schemaname =
> > 'production' and relname = 'tita';
> > relid = 16786
> > schemaname = production
> > relname = tita
> > seq_scan = 23365
> > seq_tup_read = 403862091
> > idx_scan = 26612759248
> > idx_tup_fetch = 19415752701
> > n_tup_ins = 24608806
> > n_tup_upd = 4207076934
> > n_tup_del = 24566916
> > n_tup_hot_upd = 4073821079
> > n_live_tup = 79942
> > n_dead_tup = 71969
> > n_mod_since_analyze = 12020
> > last_vacuum = 2020-03-17 15:35:19.588859+00
> > last_autovacuum = 2020-03-17 21:31:08.248598+00
> > last_analyze = 2020-03-17 15:35:20.372875+00
> > last_autoanalyze = 2020-03-17 22:04:41.76743+00
> > vacuum_count = 9
> > autovacuum_count = 135693
> > analyze_count = 9
> > autoanalyze_count = 495877
> >
> > As you can see in this table, there are only ~80K rows, but billions
> > of updates.  What we have observed is that the frozenxid reaches the
> > 200M mark fairly quickly because of the amount of activity.  What is
> > interesting is that this happens with the 'postgres' and 'template1'
> > databases as well and there is absolutely no activity in those
> > databases.
> >
> > When the 'postgres' and/or 'template1' databases hit the
> > freeze_max_age, there are cases where it kicks off an aggressive
> > autovac of those tables which seems to prevent autovacs from running
> > elsewhere.  Oddly, this is not consistent, but that condition seems to
> > be required.  We have observed this across multiple PG12 servers (dev,
> > test, staging, production) all with similar workloads.
>
> Is there anything in postgres and template1 besides what was created at
> init?

There is nothing in there at all besides system tables created at init.

>
> What are your settings for autovacuum?:
>
> https://www.postgresql.org/docs/12/runtime-config-autovacuum.html

Here are the settings, these are the only ones that are not set to
default with the exception of a few tables that have been overridden
with a different value due to lots of updates and few rows:

autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 8
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 2500
vacuum_cost_limit = 1000

We want fairly aggressive autovacs to keep table bloat limited -- the
application latency suffers if it has to wade through dead tuples and
staying near realtime is important in our environment.

** Also, it should be noted that the autovacuum_analyze_threshold is
probably an incorrect value, we likely intended that to be 250 and
just have now realized it after poking more at the configuration.

>
> >
> > $ grep -i vacuum /var/log/postgresql/postgres.log | cut -b 1-9 | uniq -c
> >   17 Mar 17 06
> >   34 Mar 17 07
> >   31 Mar 17 08
> >   31 Mar 17 09
> >   30 Mar 17 10
> >   34 Mar 17 11
> >   33 Mar 17 12
> >   19 Mar 17 13
> >   40 Mar 17 15
> >   31 Mar 17 16
> >   36 Mar 17 17
> >   34 Mar 17 18
> >   35 Mar 17 19
> >   35 Mar 17 20
> >   33 Mar 17 21
> >
> > As you can see above, we end up having around ~33 autovac/hr, and
> > about 13:30 today, they stopped until we ran a "vacuum freeze verbose
> > analyze;" against the 'postgres' database (around 15:30) which then
> > caused the autovacs to resume running against the "feedi" database.
> >
> > I'm completely perplexed as to what is happening and why it suddenly
> > started when we moved from PG10 > PG12.  The configs and workload are
> > essentially the same between versions.  We realize we could simply
> > increase the autovacuum_freeze_max_age, but that doesn't seem to
> > actually resolve anything -- it just pushes the problem out.  Has
> > anyone seen anything similar to this?
> >
> > Thanks very much for the consideration.
> >
> > Justin King
> > http://flightaware.com/
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com