Can one call pg_total_relation_size over foreign data wrapper?

[Ketan Popat]

Hi Team,

Can one call pg_total_relation_size over foreign data wrapper?

Problem statement - I need to connect to 100s of databases over foreign
data wrapper to collect the size of each table. As a part of that I have to
use pg_total_relation_size over foreign data wrappers.

Alternatively, I would have to create a view and expose that view as a
foreign table, I have tested and it seems working. However, I want to avoid
it unless that is the only and best option.

Other innovative ideas/suggestions are welcome and thanks for your help in
advance!

Thanks,
Ketan

Re: what is the solution like oracle DB's datafile

[Yudianto Prasetyo]

hello,

thanks for all the solutions. I don't think there is a solution like Oracle
DB's datafile in postgresql. LVM is probably the best way if using Linux OS.

Thank You
Yours faithfully

yudianto


Virus-free.
www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Mon, Jan 31, 2022 at 12:19 AM Josef Šimánek 
wrote:

> ne 30. 1. 2022 v 18:13 odesílatel Yudianto Prasetyo
>  napsal:
> >
> > Hello,
> >
> > dafafile this oracle like this example. can be added to another hdd.
> >
> > ALTER TABLESPACE lmtbsb
> > ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
> >
> > ALTER TABLESPACE lmtbsb
> > ADD DATAFILE '/u03/oracle/data/lmtbsb02.dbf' SIZE 1M;
> >
> > ALTER TABLESPACE lmtbsb
> > ADD DATAFILE 'e:\data\lmtbsb02.dbf' SIZE 1M;
> >
> > ALTER TABLESPACE lmtbsb
> > ADD DATAFILE 'f:\data2\lmtbsb02.dbf' SIZE 1M;
> >
> > i understand about that LVM solution. it is true that using this method
> can be done. I'm just asking for a solution at the database level.
>
> Per my understanding, there is no exactly the same feature in
> PostgreSQL itself. As mentioned before, it is most likely by design.
>
> > thank you
> > Yours faithfully
> >
> > yudianto
> >
> > On Sun, Jan 30, 2022 at 9:17 PM Julien Rouhaud 
> wrote:
> >>
> >> On Sun, Jan 30, 2022 at 08:51:02PM +0700, Yudianto Prasetyo wrote:
> >> >
> >> > thanks for other solutions in the operating system section. LVM, RAID
> is
> >> > indeed one solution to this problem.
> >> >
> >> > Maybe there is another solution in the postgresql database like the
> >> > datafile in oracle DB?
> >>
> >> As I said I don't know how datafiles in oracle are working.  All you
> have on
> >> postgres is tablespaces, default tablespaces and moving relations from
> one
> >> tablespaces to another.
> >>
> >> In general, postgres doesn't try to reimplement solution to problems
> that are
> >> nicely solved at the operating system level, so if those datafile are
> >> reimplementing something similar to LVM, then no postgres doesn't have
> >> something like that and probably doesn't want it.
>

Re: pg_try_advisory_lock is waiting?

[Merlin Moncure]

On Fri, Jan 28, 2022 at 6:34 PM Mladen Gogala  wrote:
>
> On 1/28/22 19:08, Tom Lane wrote:
>
> I doubt it.  I think the FOR UPDATE in the sub-select is blocked
> because the other session has an uncommitted update on the row
> it wants to lock.  This command won't reach the pg_try_advisory_lock
> call until that row lock comes free.
>
> Yes, I figured it out, but pg_try_advisory_lock returned TRUE even without 
> "FOR UPDATE" clause in the subquery. Shouldn't it return false because it 
> can't lock the row until the uncommitted update finishes?

advisory locks and row locks are completely distinct and separate.
It's also not a good idea to make any assumptions on order of
operations as to which lock is acquired first using subqueries in that
fashion.

merlin

Re: what is the solution like oracle DB's datafile

[Yudianto Prasetyo]

hello,

thanks for the logical answer. it is true that there is a very big
difference between open source and commercial DB. but I'm grateful to be
able to use postgresql which is quite reliable.

thank you
Yours faithfully

yudianto


Virus-free.
www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Mon, Jan 31, 2022 at 12:10 PM Laurenz Albe 
wrote:

> On Sun, 2022-01-30 at 05:15 +0700, Yudianto Prasetyo wrote:
> > I want to ask why postgresql doesn't create a datafile like it has
> oracle?
> >
> > I'm confused when I have 2 HDD. HDD 1 is used to install the OS and
> postgresql database.
> > when HDD 1 is full. how to increase the capacity of postgresql database
> with HDD 2 (without RAID system)?
> >
> > is there any other way like oracle DB's "add datafile" which can be used
> to add capacity to another HDD?
>
> The difference between Oracle and PostgreSQL here is that Oracle
> implemented its
> own file system and volume manager, while PostgreSQL uses the facilities
> provided
> by the operating system.
>
> The correspondence is not perfect, but you can compare an Oracle
> tablespace to
> a logical volume with a file system and a datafile to a physical volume.
>
> So to get the same thing in PostgreSQL, you have to interact with the
> operating
> system: add a new physical volume to the logical volume where your database
> resides and extend the file system.
>
> To answer the question "why", here are two points:
>
> - PostgreSQL does not have the (wo)manpower to re-invent the wheel on
> everything,
>   so we tend to use existing facilities
>
> - Oracle was developed earlier, and one can argue that in those days file
> systems
>   were not so great, so there was more need to write your own
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Undetected Deadlock

[Michael Harris]

Hi

The undetected deadlock occurred again today and I was able to collect
some more info.

The presentation was very similar to the case I reported previously:
- One backend trying to do a DROP TABLE on a partition of a
partitioned table, waiting for an AccessExclusiveLock on that table
- Another backend trying to do a DELETE on records within a partition
of another table, waiting on an AccessShareLock which is already held
by the first table and already holding a lock on the table that the
first backend is waiting for
- A load of other backends also trying to do a DELETE.

I was able to attach gdb to the backends and I discovered a few things.

First, taking a stack trace of the process doing the DELETE, it looks like this:

#0  0x7fc82a6750bb in epoll_wait (epfd=13, events=0x18696f8,
maxevents=maxevents@entry=1, timeout=timeout@entry=-1) at
../sysdeps/unix/sysv/linux/epoll_wait.c:30
#1  0x007d0c32 in WaitEventSetWaitBlock (nevents=1,
occurred_events=, cur_timeout=-1, set=0x1869698) at
latch.c:1450
#2  WaitEventSetWait (set=0x1869698, timeout=,
occurred_events=, nevents=1, wait_event_info=) at latch.c:1396
#3  0x007d0f94 in WaitLatch (latch=,
wakeEvents=wakeEvents@entry=33, timeout=timeout@entry=0,
wait_event_info=50331648) at latch.c:473
#4  0x007eaea7 in ProcSleep
(locallock=locallock@entry=0x24571d0,
lockMethodTable=lockMethodTable@entry=0xdb2360 )
at proc.c:1361
#5  0x007df419 in WaitOnLock
(locallock=locallock@entry=0x24571d0, owner=owner@entry=0x18b80f8) at
lock.c:1858
#6  0x007e052c in LockAcquireExtended
(locktag=locktag@entry=0x7ffced1d0b80, lockmode=lockmode@entry=1,
sessionLock=sessionLock@entry=false, dontWait=dontWait@entry=false,
reportMemoryError=reportMemoryError@entry=true,
locallockp=locallockp@entry=0x7ffced1d0b78) at lock.c:1100
#7  0x007ddb23 in LockRelationOid (relid=1842227607,
lockmode=1) at lmgr.c:117
#8  0x0050dba5 in relation_open (relationId=1842227607,
lockmode=lockmode@entry=1) at relation.c:56
#9  0x008fc838 in generate_partition_qual (rel=0x56614e8) at
partcache.c:361
#10 generate_partition_qual (rel=0x56614e8) at partcache.c:336
#11 0x007502bf in set_baserel_partition_constraint
(relation=relation@entry=0x56614e8, rel=,
rel=) at plancat.c:2402
~snip~

The lock is being taken out by the function generate_partition_qual
(in partcache.c) which has this comment:

/*
 * Grab at least an AccessShareLock on the parent table.  Must do this
 * even if the partition has been partially detached, because transactions
 * concurrent with the detach might still be trying to use a partition
 * descriptor that includes it.
 */

This is happening during query planning rather than execution, where
it is trying to fetch the partition bound expression.
It explains why this lock is not always required (the result is cached
in the relcache, and anyway it looks to me like this code path is
called depending on factors such as the row estimates).

The second thing was that the DROP process, and all the DELETE
processes, were all waiting inside epoll_wait ultimately called from
ProcSleep as shown above. It is sitting in this section of code
(proc.c line 1361):

(void) WaitLatch(MyLatch, WL_LATCH_SET |
WL_EXIT_ON_PM_DEATH, 0,   <--- Processes sitting
here
 PG_WAIT_LOCK | locallock->tag.lock.locktag_type);
ResetLatch(MyLatch);
/* check for deadlocks first, as that's probably log-worthy */
if (got_deadlock_timeout)

<-- set breakpoint here
{
CheckDeadLock();
got_deadlock_timeout = false;
}

I set a breakpoint on the next line after ResetLatch is called, and it
was never reached. I thought that could be due to GDB attaching to the
process and disrupting the alarm signal that should be sent into the
timeout handler, so I manually sent it a SIGALRM.
At that point the breakpoint was triggered - but the flag
got_deadlock_timeout is not set, so the deadlock check is not
executed.
This led me to believe that the deadlock check timeout wasn't set
properly at all.

To see if the deadlock would be detected if CheckDeadlock() got an
opportunity to run, I manually set got_deadlock_timeout, and sure
enough the deadlock was detected and released.

I concluded that the deadlock detection function is able to detect
these deadlocks, but for some reason the 1sec timeout is not being
correctly scheduled so it never runs.
When it is artificially triggered, it does release the deadlock.

After the release, a new deadlock formed between the DROP process and
another one of the waiting DELETE processes (not an entirely
unexpected outcome).
I will leave it in that state for as long as possible in case anyone
can think of any other info that should be gathered. Luckily it is a
test instance.

Sorry for the slightly long and detailed email - let me know if this
should be moved to