pg_upgrade -c cannot be run if old cluster is running

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/15/pgupgrade.html
Description:

https://www.postgresql.org/docs/15/pgupgrade.html tells
"You can use pg_upgrade --check to perform only the checks, even if the old
server is still running."
I tried (upgrade from postgres 10 to postgres 15):
/usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-10/bin/ -B /usr/pgsql-15/bin -d 
/var/lib/pgsql/10/data  -D /mnt/pgdata/pgdirbc15/ --link -c
Log tells:
-
  pg_upgrade run on Wed Sep  4 09:08:31 2024
-

command: "/usr/pgsql-10/bin/pg_ctl" -w -l
"/mnt/pgdata/pgdirbc15/pg_upgrade_output.d/20240904T090831.750/log/pg_upgrade_server.log"
-D "/var/lib/pgsql/10/data" -o "-p 50432 -b  -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql'"
start >>
"/mnt/pgdata/pgdirbc15/pg_upgrade_output.d/20240904T090831.750/log/pg_upgrade_server.log"
2>&1
waiting for server to start2024-09-04 12:08:32.179 CEST [12197] FATAL: 
lock file "postmaster.pid" already exists
2024-09-04 12:08:32.179 CEST [12197] HINT:  Is another postmaster (PID
25407) running in data directory "/mnt/pgdata/pgdir"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.


I tried the without --link, same error.
It seems pg_upgrade -c requires the old server to be stopped.

Re: pg_upgrade -c cannot be run if old cluster is running

[Tom Lane]

PG Doc comments form  writes:
> https://www.postgresql.org/docs/15/pgupgrade.html tells
> "You can use pg_upgrade --check to perform only the checks, even if the old
> server is still running."

I tested this case, and it seems to work for me.  pg_upgrade will try
to start a server in the old data directory, but if that fails it will
assume that there's a live server to run the checks against.

> waiting for server to start2024-09-04 12:08:32.179 CEST [12197] FATAL: 
> lock file "postmaster.pid" already exists
> 2024-09-04 12:08:32.179 CEST [12197] HINT:  Is another postmaster (PID
> 25407) running in data directory "/mnt/pgdata/pgdir"?
>  stopped waiting
> pg_ctl: could not start server
> Examine the log output.

What I suspect happened here is some confusion about where the lock
file is, leading to not recognizing that there was possibly an old
live server.  You didn't supply enough detail to reproduce the
problem, but perhaps check whether the old and new Postgres versions
were built with similar options.

regards, tom lane

Behavior of ON DELETE CASCADE in CTEs

[Kirk Parker]

It appears that in With-queries (CTE) the ON CASCADE DELETE deletions
happen at the very end of the entire statement. I have two questions about
this:

(1) Is this a correct observation?
(2) Is this intentional behavior, or only an accident of the implementation?

I can't find anything in the docs covering this aspect.  It's useful
behavior but of course I don't want to continue using it, if it could
change.


Let me try a simplified example derived from our actual code.

"cust_roa_detail" references "order_line_items", with ON DELETE CASCADE
specified.  I have verified that this is all defined correctly--deleting a
single row in order_line_items does in fact remove the referencing rows.

"cust_roa_detail" also references "cust_charge".  When a row in the former
goes away, we want to remove its effects from the balance columns in the
latter. This is what the "charge_restore" section does.


with line_delete as
  (
  delete from order_line_items where line_id = :lineid returning line_id,
amount
  ),
pend_restore as
  (
  select cust_charge_id, sum(amount) as amt, sum(discount) as disc,
sum(adjustment) as adj from cust_roa_detail where line_id = (select line_id
from line_del) group by 1
  ),
charge_restore as
  (
  update cust_charge set paid = paid - (select amt from pend_restore),
discount = discount - (select disc from pend_restore),
adjustment = adjustment - (select adj from pend_restore)
  where id = (select cust_charge_id from pend_restore)
  returning id, paid, discount, adjustment
  ), 
select * from line_delete;


The above came from rearranging a previous version of the with-query. It
seems to work fine, but on reviewing it I was surprised to notice that the
row(s) that are cascade-deleted by "line_delete" still are found by the
subsequent "pend_restore" query.  Whereas if you issue these two queries
separately, of course the 2nd one retrieves nothing.

It seems sensible that the ON CASCADE DELETE actions would take place at
the very end of the compound statement; but it's quite another to rely on
that behavior if it's merely an artifact of the current implementation.

And the relevance to this current list is: if it IS intended behavior, can
it be documented somewhere?

Re: Behavior of ON DELETE CASCADE in CTEs

[Tom Lane]

Kirk Parker  writes:
> It appears that in With-queries (CTE) the ON CASCADE DELETE deletions
> happen at the very end of the entire statement. I have two questions about
> this:

> (1) Is this a correct observation?
> (2) Is this intentional behavior, or only an accident of the implementation?

I believe it's required by SQL spec.  Or more accurately, the spec
says that constraint-triggered actions happen either at the end of the
statement or the end of the transaction, depending on whether you set
them as "deferred".

regards, tom lane

Re: Behavior of ON DELETE CASCADE in CTEs

[David G. Johnston]

On Wednesday, September 4, 2024, Kirk Parker  wrote:

>
> And the relevance to this current list is: if it IS intended behavior, can
> it be documented somewhere?
>

It’s follows from this paragraph:


https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

The sub-statements in WITH are executed concurrently with each other and
with the main query. Therefore, when using data-modifying statements in WITH,
the order in which the specified updates actually happen is unpredictable.
All the statements are executed with the same *snapshot* (see Chapter 13
), so they cannot “see” one
another's effects on the target tables. This alleviates the effects of the
unpredictability of the actual order of row updates, and means that
RETURNING data is the only way to communicate changes between
different WITH sub-statements
and the main query.

David J.

Re: Behavior of ON DELETE CASCADE in CTEs

[Kirk Parker]

>   The sub-statements in WITH are executed concurrently

That much I did get from the docs.  Given each sub-statement is qualified
by the RETURNING results of the previous one, that should at least
guarantee the completeness of each query regardless of the order in which
individual rows are affected.

Thanks, this has been helpful -- as I reread the page you pointed to, this
jumped out at me:

>All the statements are executed with the same *snapshot* (see
Chapter 13 ), so they
cannot “see” one another's effects on the target tables.

That I think specifically covers my question, and Tom Lane's note about
end-of-statement was also helpful.