Re: Locking a table read-only temporarilty

[Laurenz Albe]

On Thu, 2022-03-31 at 15:20 -0400, Digimer wrote:
> I've been looking up locking, and perhaps I'm being dense, but I'm struggling 
> to figure out
> how to create a lock to make a table read-only for a short period of time.
>  I want to set a table to be read-only, so that any other clients that try to 
> UPDATE
> or INSERT will hold until the lock is released. I've been reading;
> https://www.postgresql.org/docs/10/explicit-locking.html
>   But I'm not sure which lock would be what I want, and I don't see how to 
> release
> the lock when finished. Could someone hit me with a clue-stick?

You'd have to start a transaction and

  LOCK tab IN SHARE MODE;

Commit the transaction to release the lock.

However, it is a bad idea to keep transactions with high locks open for a long 
time.
Your real problem might have a better answer.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

PostgreSQL on focal and llvm version

[Christoph Berg]

Re: hubert depesz lubaczewski
> Soo... plot thickens.
> 
> Looks that pg 12 supplied by pgdg required libllvm9:
> 
> =$ apt-cache show postgresql-12 | grep -E '^(Package|Version|Depends):'
> Package: postgresql-12
> Version: 12.9-2.pgdg20.04+1
> Depends: ..., libllvm9 (>= 1:9~svn298832-1~), ...
> 
> Package: postgresql-12
> Version: 12.9-0ubuntu0.20.04.1
> Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ...
> 
> Package: postgresql-12
> Version: 12.2-4
> Depends: ..., libllvm10 (>= 1:9~svn298832-1~), ...
> 
> 
> Newer pg12 (12.10) from pgdg also depends on llvm9. Perhaps changing the deb 
> to
> use/depend-on llvm10 would solve the problem, for now I'm not sure how to do
> it. Reached to Christoph about it.

Thanks for spotting that. The problem turned out me being not smart
enough to determine the newest llvm version installed; the
not-quite-correct Makefile code failed on "9" < "10".

I have now fixed that problem. Updated postgresql-{11,12,13,14}
packages using libllvm10 instead of libllvm9 are available in
focal-pgdg-testing.

Since this is potentially a breaking change, I'll not push these live
immediately but will give people a chance to test these until next
week.

Christoph

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 8:48 AM Daniele Varrazzo 
wrote:

> > On 3/31/22 18:22, Daniele Varrazzo wrote:
> > > Are arm packages available at all? If so, what is the right procedure
> > > to install them?
>
> On Fri, 1 Apr 2022 at 06:07, Adrian Klaver 
> wrote:
> >
> >  From here:
> >
> > https://apt.postgresql.org/pub/repos/apt/dists/
> >
> > I see them in Debian:
> >
> > Buster on up
>
> Ah, gotcha. Buster is more recent than Stretch.
>

Stretch also goes end of life on June 30 2022, so just a few months away.
You definitely shouldn't be using that.

Buster is also considered the "oldstable" version. You should probably be
using bullseye.  (I assume you're talking about some generic binaries and
not the DEB packages of course -- DEB packages should be built on their
corresponding platform)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Does PGDG apt repository support ARM64?

[Daniele Varrazzo]

On Fri, 1 Apr 2022 at 16:28, Magnus Hagander  wrote:

> Stretch also goes end of life on June 30 2022, so just a few months away. You 
> definitely shouldn't be using that.

The platform is part of the Python binary packages build chain; they
are purposely on the old side of the spectrum in order to create
binaries which can work with as many Linux distributions as possible,
making use of core libraries ABI compatibility. The rationale is
available at https://peps.python.org/pep-0513/

-- Daniele

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 4:52 PM Daniele Varrazzo 
wrote:

> On Fri, 1 Apr 2022 at 16:28, Magnus Hagander  wrote:
>
> > Stretch also goes end of life on June 30 2022, so just a few months
> away. You definitely shouldn't be using that.
>
> The platform is part of the Python binary packages build chain; they
> are purposely on the old side of the spectrum in order to create
> binaries which can work with as many Linux distributions as possible,
> making use of core libraries ABI compatibility. The rationale is
> available at https://peps.python.org/pep-0513/


Ah yeah, that makes sense. Then buster is likely the better choice, yeah.
(But you do want to get off stretch before it goes into unsupported land)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Does PGDG apt repository support ARM64?

[Daniele Varrazzo]

On Fri, 1 Apr 2022 at 17:00, Magnus Hagander  wrote:
>
> On Fri, Apr 1, 2022 at 4:52 PM Daniele Varrazzo  
> wrote:
>>
>> On Fri, 1 Apr 2022 at 16:28, Magnus Hagander  wrote:
>>
>> > Stretch also goes end of life on June 30 2022, so just a few months away. 
>> > You definitely shouldn't be using that.
>>
>> The platform is part of the Python binary packages build chain; they
>> are purposely on the old side of the spectrum in order to create
>> binaries which can work with as many Linux distributions as possible,
>> making use of core libraries ABI compatibility. The rationale is
>> available at https://peps.python.org/pep-0513/
>
> Ah yeah, that makes sense. Then buster is likely the better choice, yeah.  
> (But you do want to get off stretch before it goes into unsupported land)

TBH if the PGDG team stops publishing packages for Stretch in 3 months
it would be an immense pain in the neck, at least until the build
chain gets updated (which might make the task of building packages
easier, but it regularly leaves a slice of users unable to use binary
packages. See https://github.com/psycopg/psycopg/issues/124 for a
typical case).

Please remember your Python friends!

-- Daniele

Re: Does PGDG apt repository support ARM64?

[Magnus Hagander]

On Fri, Apr 1, 2022 at 5:08 PM Daniele Varrazzo 
wrote:

> On Fri, 1 Apr 2022 at 17:00, Magnus Hagander  wrote:
> >
> > On Fri, Apr 1, 2022 at 4:52 PM Daniele Varrazzo <
> daniele.varra...@gmail.com> wrote:
> >>
> >> On Fri, 1 Apr 2022 at 16:28, Magnus Hagander 
> wrote:
> >>
> >> > Stretch also goes end of life on June 30 2022, so just a few months
> away. You definitely shouldn't be using that.
> >>
> >> The platform is part of the Python binary packages build chain; they
> >> are purposely on the old side of the spectrum in order to create
> >> binaries which can work with as many Linux distributions as possible,
> >> making use of core libraries ABI compatibility. The rationale is
> >> available at https://peps.python.org/pep-0513/
> >
> > Ah yeah, that makes sense. Then buster is likely the better choice,
> yeah.  (But you do want to get off stretch before it goes into unsupported
> land)
>
> TBH if the PGDG team stops publishing packages for Stretch in 3 months
> it would be an immense pain in the neck, at least until the build
> chain gets updated (which might make the task of building packages
> easier, but it regularly leaves a slice of users unable to use binary
> packages. See https://github.com/psycopg/psycopg/issues/124 for a
> typical case).
>
> Please remember your Python friends!
>

I'm pretty sure they will. However, there is apt-archive that you can
switch to when that happens.  It won't get you any updates, but you will at
least still be able to get the "latest that were. See
https://apt-archive.postgresql.org/.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

LISTEN - ERROR: could not access status of transaction 3021015672

[Aleš Zelený]

Hello,

I've found in our server ( PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit ) following error:

2022-03-31 09:29:58 CEST [15490]: [1-1]
user=app,db=main,host=192.168.1.231,app=[unknown] ERROR:  could not access
status of transaction 3021015672
2022-03-31 09:29:58 CEST [15490]: [2-1]
user=app,db=main,host=192.168.1.231,app=[unknown] DETAIL:  Could not open
file "pg_xact/0B41": Adresář nebo soubor neexistuje.
2022-03-31 09:29:58 CEST [15490]: [3-1]
user=app,db=main,host=192.168.1.231,app=[unknown] STATEMENT:  LISTEN
"CONFIG"

Since we have enabled track_commit_timestamp, I' tried:
prematch=# show track_commit_timestamp ;
 track_commit_timestamp

 on
(1 row)


prematch=# select * from pg_xact_commit_timestamp('3021015672');
 pg_xact_commit_timestamp
--

(1 row)

Since this was not helpful, searching in the archives, I've found the
thread:

"BUG #16961: Could not access status of transaction"
https://www.postgresql.org/message-id/flat/VE1PR03MB531295B1BDCFE422441B15FD92499%40VE1PR03MB5312.eurprd03.prod.outlook.com#7e36d1fdca921b5292e92c7017984ffa

But it looks not to have any result.
Unfortunately, I have no isolated test case, so I'd like to ask for advice
on what set of information to collect if it happened next time, to be able
to supply some valuable bug reports.
If it is a known configuration issue or an application logic issue, any
advice is welcome (the scenario described in the abovementioned thread is
not our application case).

Thanks, Ales

Re: Does PGDG apt repository support ARM64?

[Adrian Klaver]

On 3/31/22 23:48, Daniele Varrazzo wrote:

On 3/31/22 18:22, Daniele Varrazzo wrote:

Are arm packages available at all? If so, what is the right procedure
to install them?


On Fri, 1 Apr 2022 at 06:07, Adrian Klaver  wrote:


  From here:

https://apt.postgresql.org/pub/repos/apt/dists/

I see them in Debian:

Buster on up


Ah, gotcha. Buster is more recent than Stretch.


Yeah I should have gone with the numbers:

Debian
10+

Ubuntu
20.04+, except 21.10



-- Daniele



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

Re: weird issue with occasional stuck queries

[Adam Scott]

If you get a chance, showing the `top` output might be useful as well. For
instance if you are low on memory, it can slow down the allocation of
buffers.   Another thing to look at is `iostat -x -y` and look at disk util
%.  This is an indicator, but not definitive, of how much disk access is
going on.  It may be your drives are just saturated although your IOWait
looks ok in your attachment.

That wait event according to documentation is "Waiting to access the
multixact member SLRU cache."  SLRU = segmented least recently used cache

Do you have a query that is a "select for update" running somewhere?

If your disk is low on space `df -h` that might explain the issue.

Is there an ERROR: multixact  something in your postgres log?

Adam






On Fri, Apr 1, 2022 at 6:28 AM spiral  wrote:

> Hey,
>
> I'm having a weird issue where a few times a day, any query that hits a
> specific index (specifically a `unique` column index) gets stuck for
> anywhere between 1 and 15 minutes on a LWLock (mostly
> MultiXactOffsetSLRU - not sure what that is, I couldn't find anything
> about it except for a pgsql-hackers list thread that I didn't really
> understand).
> Checking netdata history, these stuck queries coincide with massive
> disk read; we average ~2MiB/s disk read and it got to 40MiB/s earlier
> today.
>
> These queries used to get stuck for ~15 minutes at worst, but I turned
> down the query timeout. I assume the numbers above would be worse if I
> let the queries run for as long as they need, but I don't have any logs
> from before that change and I don't really want to try that again as it
> would impact production.
>
> I asked on the IRC a few days ago and got the suggestion to increase
> shared_buffers, but that doesn't seem to have helped at all. I also
> tried deleting and recreating the index, but that seems to have changed
> nothing as well.
>
> Any suggestions are appreciated since I'm really not sure how to debug
> this further. I'm also attaching a couple screenshots that might be
> useful.
>
> spiral
>

How long does iteration over 4-5 million rows usually take?

[Shaozhong SHI]

I have a script running to iterate over 4-5 million rows.  It keeps showing
up in red in PgAdmin.  It remains active.

How long does iteration over 4-5 million rows usually take?

Regards,

David

Re: How long does iteration over 4-5 million rows usually take?

[Adrian Klaver]

On 4/1/22 18:34, Shaozhong SHI wrote:


I have a script running to iterate over 4-5 million rows.  It keeps 
showing up in red in PgAdmin.  It remains active.


How long does iteration over 4-5 million rows usually take?


Given that there is no real information provided in the problem 
description there is no chance for an answer more detailed then; as long 
as it takes.




Regards,

David



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

Re: How long does iteration over 4-5 million rows usually take?

[Ron]

On 4/1/22 20:34, Shaozhong SHI wrote:


I have a script running to iterate over 4-5 million rows.  It keeps 
showing up in red in PgAdmin.  It remains active.


How long does iteration over 4-5 million rows usually take?


What /*exactly*/ are you doing?


--
Angular momentum makes the world go 'round.