Lock acquisition for partition table when setting generic plan

Hi all, I have a question. I did the following. Even though I accessed one partition table(test2 table), I also acquired locks on other partition tables(test1 table). I expected to acquire locks on the parent table(test table) and the partition table to access(test2 table). Why does this happen

Re: Multiple Aggregations Order

I also thought about that. The 'issue', is that when you call array_agg in a row/type, it casts the entire thing in a string. But some of the aggregation fields are jsonb fields, which are also casted to strings, with extra escapes. Then, I'm also using Django, which seems to mess the string even m

Re: Lock leaking out of Transaction?

On Wed, 2020-01-15 at 10:42 +1100, James Sewell wrote: > I am trying to chase down a locking issue - it looks like a materialized view > refresh is being > held up by a relation lock which is held by an out of transaction session. > My understanding was that > this was not possible (see SQL outp

Re: Fwd: Postgresql Data corruption

On Tue, Jan 14, 2020 at 02:34:04PM -0800, Adrian Klaver wrote: > On 1/14/20 12:44 PM, Tulqin Navruzov wrote: >> but can logged in with another user and trying to select from some >> tables , showing this message : >> >> ERROR:  catalog is missing 11 attribute(s) for relid 113971 >> >> Could you h

Lock leaking out of Transaction?

Hi all, I am trying to chase down a locking issue - it looks like a materialized view refresh is being held up by a relation lock which is held by an out of transaction session. My understanding was that this was not possible (see SQL output below). The locking session is making progress (I can

Re: Fwd: Postgresql Data corruption

On 1/14/20 12:44 PM, Tulqin Navruzov wrote: Hi team Could you help us to sort this out please? We had some hardware problems with Datacenter and could not using postgresql from restored snapshots it showing "Structure needs to be cleaning " during startup , on centos7 did xfs_repair and pos

Re: Postgresql Data corruption

Can't help you personally right now due to work, but try these guys: https://pgexperts.com/services/emergency_help/ On Tue, Jan 14, 2020 at 4:08 PM Rene Romero Benavides < rene.romer...@gmail.com> wrote: > On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov < > tulqin.navru...@finnetlimited.com> wrot

Re: Postgresql Data corruption

On Tue, Jan 14, 2020 at 2:55 PM Tulqin Navruzov < tulqin.navru...@finnetlimited.com> wrote: > > > Hi team > > Could you help us to sort this out please? > > We had some hardware problems with Datacenter and could not using > postgresql from restored snapshots it showing "Structure needs to be > cl

Re: Multiple Aggregations Order

João Haas schrieb am 14.01.2020 um 18:26: I'm working on a query where I need to fetch information from a table along with some data from a many-to-many connection table in a single query. My idea is to do an outer join with the connection query and aggregate the needed data in multiple 'array_ag

Re: Multiple Aggregations Order

I'm aiming to serialize some graph data into a JSON format, and some of the data needed for the serialization is in the relation tables, like, "this node connects to this other node in this way". These are served to IOT devices and the data changes a lot, so there's a ton of requests and caching is

Fwd: Postgresql Data corruption

Hi team Could you help us to sort this out please? We had some hardware problems with Datacenter and could not using postgresql from restored snapshots it showing "Structure needs to be cleaning " during startup , on centos7 did xfs_repair and postgresql started successfully. But could not select

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

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 se

Re: Multiple Aggregations Order

On Tuesday, January 14, 2020, João Haas wrote: > > SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind) > > Create a custom type (using row(...) might work...?) with the relevant fields and “...array_agg((child_id, kind)::custom_type order by ...”? David J.

Re: Multiple Aggregations Order

"handle this aggregated data later in code" What is your end goal though? Also, approx how many rows in these tables? Can you share an example query and plan? What version are you using? >

Multiple Aggregations Order

Hi there, I'm working on a query where I need to fetch information from a table along with some data from a many-to-many connection table in a single query. My idea is to do an outer join with the connection query and aggregate the needed data in multiple 'array_agg's, and then handle this aggrega

Re: Worse performance with higher work_mem?

Israel Brewster writes: > On Jan 13, 2020, at 3:46 PM, Rob Sargent wrote: >> I don’t see that you’ve updated the statistics? > U….no. I know nothing about that :-) Just do a manual ANALYZE on the table, if you don't want to wait around for autovacuum to get to it. r

Re: Worse performance with higher work_mem?

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 tim

Re: Worse performance with higher work_mem?

> > 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 mailto:ijbrews...@alaska.edu>> >>> w

Re: Error retrieving PostgreSQL DB information with Coturn

Thank you very much Adrian! I feel ashamed for such a silly mistake coturn=# \dt List of relations Schema | Name | Type | Owner +--+---+--- public | admin_user | table | turn public | allowed_peer_ip | table | t

Re: Worse performance with higher work_mem?

> On Jan 13, 2020, at 6:34 PM, Dilip Kumar wrote: > > On Tue, Jan 14, 2020 at 5:29 AM Israel Brewster > wrote: >> >> I was working on diagnosing a “slow” (about 6 second run time) query: >> >> SELECT >>to_char(bucket,'-MM-DD"T"HH24:MI:SS') as dat

Re: WaitForMultipleObjects in C Extension

On Tue, 14 Jan 2020 10:35:09 +0300, ?lyas Derse wrote: > Hi guys I need your experiences. I made an Extension in C. When I stop the >query on PostgreSQL,I want to control my extension's result with use cases >in C.So I think , I should use WaitForMultipleObjects. Not really understanding this.

Re: Error retrieving PostgreSQL DB information with Coturn

On 1/14/20 8:33 AM, Marco Ippolito wrote: In order to understand how to use Postgresql-11 with Coturn, https://github.com/coturn/coturn , I created a postgresql-11 dabatase using /usr/local/share/turnserver/schema.sql  :     CREATE TABLE turnusers_lt (         realm varchar(127) default '',

Error retrieving PostgreSQL DB information with Coturn

In order to understand how to use Postgresql-11 with Coturn, https://github.com/coturn/coturn , I created a postgresql-11 dabatase using /usr/local/share/turnserver/schema.sql : CREATE TABLE turnusers_lt ( realm varchar(127) default '', name varchar(512), hmackey char(

Re: WaitForMultipleObjects in C Extension

On Tue, Jan 14, 2020 at 1:39 AM İlyas Derse wrote: > > Hi guys I need your experiences. I made an Extension in C. When I stop the > query on PostgreSQL,I want to control my extension's result with use cases in > C.So I think , I should use WaitForMultipleObjects. Is there a like a > SignalObje

Re: How to log pg_terminate_backend() calls

"Zwettler Markus (OIZ)" writes: > We see some "FATAL: terminating connection due to administrator command" > error messages in the Postgres logfile. > We assume someone did pg_terminate_backend() calls. > How can we log such calls, especially who did the call? There's no built-in facility for t

Is it possible to replicate through an http proxy?

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

ECPG call interface && filename

Hello, Actually, the ecpg pre-compiler resolves ESQL/C statements like EXEC SQL SELECT into a function call to the executer in the ecpglib as: ECPGdo(__LINE__, ...); where __LINE__ is later substituted by the C-precompiler by the current line number as an integer. The purpose is

How to log pg_terminate_backend() calls

We see some "FATAL: terminating connection due to administrator command" error messages in the Postgres logfile. We assume someone did pg_terminate_backend() calls. How can we log such calls, especially who did the call?