Re: "PANIC: could not open critical system index 2662" - twice

[Evgeny Morozov]

> Hmm, I am not certain. The block was filled with zeros from your error
> message, and I think such blocks don't trigger a checksum warning.

OK, so data_checksums=on might not have made any difference in this case?


> So if your disk replaces a valid block with zeros (filesystem check
> after crash?), that could explain what you see.

If by "crash" here you mean the OS crashing - we didn't have that
happen. The OS is on separate disks, which have not reported any errors.

When we first ran into this problem the PG data was on a ZFS RAIDZ (i.e.
RAID5) volume of 3 disks, and for one of them `zpool status -v` reported
read, write and checksum error count > 0, but it also said  "errors: No
known data errors" and the disk status remained "online" (it did not
become "faulted" or "offline"). (Now we have the PG data on a ZFS mirror
volume of 2 new disks, which have not reported any errors.)

I don't know whether ZFS zero-fills blocks on disk errors. As I
understood, ZFS should have been able to recover from disk errors (that
were "unrecoverable" at the hardware level) using the data on the other
two disks (which did not report any errors). Thus, PG should not have
seen any corrupted data (if ZFS was working correctly).
https://unix.stackexchange.com/questions/341614/understanding-the-error-reporting-of-zfs-on-linux
seems to confirm this. Am I misunderstanding something?

Re: "PANIC: could not open critical system index 2662" - twice

[Alban Hertroys]

> On 14 Apr 2023, at 9:38, Evgeny Morozov  wrote:

(…)

> I don't know whether ZFS zero-fills blocks on disk errors. As I
> understood, ZFS should have been able to recover from disk errors (that
> were "unrecoverable" at the hardware level) using the data on the other
> two disks (which did not report any errors). Thus, PG should not have
> seen any corrupted data (if ZFS was working correctly).
> https://unix.stackexchange.com/questions/341614/understanding-the-error-reporting-of-zfs-on-linux
> seems to confirm this. Am I misunderstanding something?

Your problem coincides with a thread at freebsd-current with very similar data 
corruption after a recent OpenZFS import: blocks of all zeroes, but also 
missing files. So, perhaps these problems are related?

Apparently, there was a recent fix for a data corruption issue with the 
block_cloning feature enabled, but people are still seeing corruption even when 
they never enabled that feature.

I couldn’t really find the start of the thread in the archives, so this one 
kind of jumps into the middle of the thread at a relevant-looking point:

https://lists.freebsd.org/archives/freebsd-current/2023-April/003446.html

Regards,

Alban Hertroys
--
There is always an exception to always.

cursor with hold must be save to disk?

[黄宁]

i want to use cursor with hold ,but when I declare a curosr , it takes a
long time to save the result set to disk. can i save the query state in
memory? and fetch forward the next result.

SIze 0 in pg_stat_file, pg_stat_progress_copy

[Marc Millas]

Hi,

on a debian machine, with a postgres 14,2 server

logs in a dedicated  directory (not log)
when logged as superuser, I get:
--pg_stat_file for the current logfile says size 0,
--pg_ls_logdir answers 0 files,
--pg_ls_dir, for the log directory provides postgres.csv, postgres.json,...
list of files
--and when running a copy from the current logfile with a where clause to
choose only the current day logs, the bytes total column is 0

what can provides such a behaviour ?

thanks,


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: cursor with hold must be save to disk?

[Adrian Klaver]

On 4/14/23 04:04, 黄宁 wrote:
i want to use cursor with hold ,but when I declare a curosr , it takes a 
long time to save the result set to disk. can i save the query state in 
memory? and fetch forward the next result.




From the docs:

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

A cursor created with WITH HOLD is closed when an explicit CLOSE command 
is issued on it, or the session ends. In the current implementation, the 
rows represented by a held cursor are copied into a temporary file or 
memory area so that they remain available for subsequent transactions.


So I am going to guess the cursor query is holding a large amount of data.

To get a more specific answer you will need to provide:

1) Postgres version.

2) The complete DECLARE command being used.

3) An indication of the amount of data being retrieved.

4) The actual time for a 'long time'.

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

Re: cursor with hold must be save to disk?

[Laurenz Albe]

On Fri, 2023-04-14 at 19:04 +0800, 黄宁 wrote:
> i want to use cursor with hold ,but when I declare a curosr , it takes a long
> time to save the result set to disk. can i save the query state in memory?
> and fetch forward the next result.

The complete result set has to be materialized.  It only spills to disk if it
is large.  That cannot be avoided.

Yours,
Laurenz Albe

Re: [Extern] Re: Is a logical replication crash recovery safe?

[GF]

Markus,
Yesterday EDB published the (open source) pg_failover_slots extension,
aimed at pushing logical slot information on standbys: without it, that
information is kept on the master only, so you have to rebuild the logical
replication topology on switch/fail-overs.
Obviously, if the promoted server is outdated wrt to the lost master, you
could still have problems with subscriptions, although there some
configuration that tries to address that problem too
(standby_slots_min_confirmed).
For the moment being you can find the documentation at EDB's docs
, and
the sources at github.com/EnterpriseDB/pg_failover_slots. I hope the
extension will make quickly its way to PGDG as prebuilt packages.
Best,
g


On Tue, 24 Jan 2023 at 17:08, Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> > -Ursprüngliche Nachricht-
> > Von: Christophe Pettus 
> > Gesendet: Dienstag, 24. Januar 2023 16:48
> > An: Zwettler Markus (OIZ) 
> > Cc: pgsql-general@lists.postgresql.org
> > Betreff: [Extern] Re: Is a logical replication crash recovery safe?
> >
> >
> >
> > > On Jan 24, 2023, at 07:37, Zwettler Markus (OIZ) <
> markus.zwett...@zuerich.ch>
> > wrote:
> > >
> > > Is a logical replication crash recovery safe?
> >
> > You might want to give more detail about the scenario you are concerned
> about.
> > Changes that are received by a subscriber as part of logical replication
> are WAL-
> > logged, observe transaction semantics, and will be replayed if the
> subscriber
> > crashes and restarts.
> >
>
>
> Sorry. Let me refine.
>
> If I create a logical replication slot and use the replication slot to
> publish some tables.
>
> Will the information in the replication slot be safe for any subscription
> even if a crash recovery happened on the publisher node?
>
>
>

Re: SIze 0 in pg_stat_file, pg_stat_progress_copy

[Marc Millas]

Sorry...
someone did setup the log as a named pipe...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Fri, Apr 14, 2023 at 4:26 PM Marc Millas  wrote:

> Hi,
>
> on a debian machine, with a postgres 14,2 server
>
> logs in a dedicated  directory (not log)
> when logged as superuser, I get:
> --pg_stat_file for the current logfile says size 0,
> --pg_ls_logdir answers 0 files,
> --pg_ls_dir, for the log directory provides postgres.csv,
> postgres.json,... list of files
> --and when running a copy from the current logfile with a where clause to
> choose only the current day logs, the bytes total column is 0
>
> what can provides such a behaviour ?
>
> thanks,
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

[Peter J. Holzer]

On 2023-04-13 10:07:09 -0500, Ron wrote:
> On 4/13/23 09:44, Sebastien Flaesch wrote:
> Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
> into PostgreSQL timestamp?
> 
> I have this kind of JSON data:
> 
> {
>     "PurchaseOrder" : "45",
>     "CreationDate" : "\/Date(167235840)\/",
>     "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
> }
> 
> Warning: Note the backslash before the slashes!

That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
why they even specified that - it seems quite pointless).

> According to JSON spec this is valid JSON and used by AJAX Date format.

It's valid JSON, but for JSON it's just a string, not a date.

Any interpretation is strictly by convention between the sender and the
receiver.


> This looks like "milliseconds since the Unix epoch:
> 
> $ date -d @1672692813.062
> Mon 02 Jan 2023 02:53:33 PM CST
> 
> Thus:
> select to_timestamp(cast(1672692813062 as bigint))::timestamp;

ITYM:

select to_timestamp(1672692813062/1000.0);

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Guidance on INSERT RETURNING order

[Peter J. Holzer]

On 2023-04-11 15:45:59 -0600, Rob Sargent wrote:
> > > Can your client retain a hashmap of md5,data pairings, allowing the
> > > lookup on the way back using the returned data and supplied id?
> > > 
> > When using unique columns or similar, that's something that is done,
> > but if there are no unique columns in the value no match can be done
> > reliably with the source data, since sqlalchemy is a library that
> > allows arbitrary schemas to be generated.

> So you're returned data is not what was sent to the server? Otherwise it
> should generate the same md5, as I understand it.

That's not necessarily the case. There are quite a few data types where
the input value is truncated, rounded or otherwise normalized. So I
don't think you can generally expect to read back exactly the same value
you inserted.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: JSON / ASP.NET AJAX Dates support in PostgreSQL

[Adrian Klaver]

On 4/14/23 9:31 AM, Peter J. Holzer wrote:

On 2023-04-13 10:07:09 -0500, Ron wrote:

On 4/13/23 09:44, Sebastien Flaesch wrote:
 Is there an easy way to convert JSON data containing ASP.NET AJAX Dates
 into PostgreSQL timestamp?

 I have this kind of JSON data:

 {
     "PurchaseOrder" : "45",
     "CreationDate" : "\/Date(167235840)\/",
     "LastChangeDateTime" : "\/Date(1672692813062+0100)\/"
 }

 Warning: Note the backslash before the slashes!


That's a Noop. According to RFC 8259, "\/" is the same as "/" (no idea
why they even specified that - it seems quite pointless).


It is a cheat explained here:

https://weblogs.asp.net/bleroy/dates-and-json


"Our current approach is using a small loophole in the JSON specs. In a 
JSON string literal, you may (or may not) escape some characters. Among 
those characters, weirdly enough, there is the slash character ('/'). 
This is weird because there actually is no reason that I can think of 
why you'd want to do that. We've used it to our benefit to disambiguate 
a string from a date literal.


The new format is "\/Date(1198908717056)\/" where the number is again 
the number of milliseconds since January 1st 1970 UTC. I would gladly 
agree that this is still not super readable, which could be solved by 
using ISO 8601 instead.


The point is that this disambiguates a date literal from a string that 
looks like the same date literal, while remaining pure JSON that will be 
parsed by any standard JSON parser. Of course, a parser that doesn't 
know about this convention will just see a string, but parsers that do 
will be able to parse those as dates without a risk for false positives 
(except if the originating serializer escaped slashes, but I don't know 
of one that does).

"



 According to JSON spec this is valid JSON and used by AJAX Date format.


It's valid JSON, but for JSON it's just a string, not a date.

Any interpretation is strictly by convention between the sender and the
receiver.



This looks like "milliseconds since the Unix epoch:

$ date -d @1672692813.062
Mon 02 Jan 2023 02:53:33 PM CST

Thus:
select to_timestamp(cast(1672692813062 as bigint))::timestamp;


ITYM:

 select to_timestamp(1672692813062/1000.0);

 hp




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

Re: Guidance on INSERT RETURNING order

[John Howroyd]

@PostgreSQL:  Might I ask if this is still being actively considered or
should we repost to another mailing list (perhaps pgsql-hackers or any
other you might suggest)?

Re: Guidance on INSERT RETURNING order

[David G. Johnston]

On Fri, Apr 14, 2023 at 11:42 AM John Howroyd 
wrote:

> @PostgreSQL:  Might I ask if this is still being actively considered or
> should we repost to another mailing list (perhaps pgsql-hackers or any
> other you might suggest)?
>

This is the right place for such a discussion.  Unless you think you've
come up with a design that you want to propose and that, if accepted, you
would then write up a patch for.  For that you'd probably want to move the
discussion to -hackers with an outline of the patch you plan to write.

David J.

Re: Guidance on INSERT RETURNING order

[Federico]

On Fri, 14 Apr 2023 at 21:37, David G. Johnston
 wrote:
>
> On Fri, Apr 14, 2023 at 11:42 AM John Howroyd  
> wrote:
>>
>> @PostgreSQL:  Might I ask if this is still being actively considered or 
>> should we repost to another mailing list (perhaps pgsql-hackers or any other 
>> you might suggest)?
>
>
> This is the right place for such a discussion.  Unless you think you've come 
> up with a design that you want to propose and that, if accepted, you would 
> then write up a patch for.  For that you'd probably want to move the 
> discussion to -hackers with an outline of the patch you plan to write.
>
> David J.
>

Would something like what was proposed by Mike Bayer be considered?

> A new token called "tuple_order" or something
>
>  INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING 
> table.id, inserted.tuple_order
>
> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate 
> the each row delivered by RETURNING to each entry in the VALUES clause, in 
> the order they were stated in that VALUES clause, that is entry (1, 2, 3) 
> would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, etc.

This would allow easy reordering of the RETURNING clause, either
client side or moving the INSERT into a CTE and ordering the outside
select. I also don't think it would have any impact on parallel
processing of the INSERT, since RETURNING could output rows in any
arbitrary order.

Best,
Federico

Re: Guidance on INSERT RETURNING order

[John Howroyd]

A patch for what?  All my testing gives me the same output order as the
declaration order.  Does anyone have an example where this is not the case?

The problem is that SQLAlchemy is an ORM and they need to be sure to match
records from the insert to the relevant code side objects.  This needs to
be efficient as the majority of the python world will use this ORM for
their database needs.  There is no PostgreSQL documentation upon which to
give this assertion.

So what is really needed is for PostgreSQL to commit to this behaviour, or
give guidance on how this might be achieved.

On Fri, 14 Apr 2023 at 19:50, David G. Johnston 
wrote:

> On Fri, Apr 14, 2023 at 11:42 AM John Howroyd 
> wrote:
>
>> @PostgreSQL:  Might I ask if this is still being actively considered or
>> should we repost to another mailing list (perhaps pgsql-hackers or any
>> other you might suggest)?
>>
>
> This is the right place for such a discussion.  Unless you think you've
> come up with a design that you want to propose and that, if accepted, you
> would then write up a patch for.  For that you'd probably want to move the
> discussion to -hackers with an outline of the patch you plan to write.
>
> David J.
>
>

Re: Guidance on INSERT RETURNING order

[David G. Johnston]

On Fri, Apr 14, 2023 at 2:44 PM John Howroyd 
wrote:

> A patch for what?  All my testing gives me the same output order as the
> declaration order.  Does anyone have an example where this is not the case?
>
> The problem is that SQLAlchemy is an ORM and they need to be sure to match
> records from the insert to the relevant code side objects.  This needs to
> be efficient as the majority of the python world will use this ORM for
> their database needs.  There is no PostgreSQL documentation upon which to
> give this assertion.
>
> So what is really needed is for PostgreSQL to commit to this behaviour, or
> give guidance on how this might be achieved.
>
>>
>>
We aren't committing to it though.  It will require newly introduced syntax
that tells the system to behave in the manner so desired.  It makes sense
to attach such syntax to the RETURNING clause itself.  Whether someone can
achieve that in a manner that the project is willing to commit remains to
be seen.

In short, lots of people complain about PostgreSQL's lack of this feature.
None of them are stepping up to fill in the gap and there isn't enough
interest, for many reasons, for the people being complained to to go ahead
and design and implement it themselves.

David J.

Re: Guidance on INSERT RETURNING order

[David G. Johnston]

On Fri, Apr 14, 2023 at 12:47 PM Federico  wrote:

>
> Would something like what was proposed by Mike Bayer be considered?
>
> > A new token called "tuple_order" or something
> >
> >  INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...)
> RETURNING table.id, inserted.tuple_order
> >
> > tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which
> correlate the each row delivered by RETURNING to each entry in the VALUES
> clause, in the order they were stated in that VALUES clause, that is entry
> (1, 2, 3) would be tuple_order 1, entry (4, 5, 6) would be tuple order 2,
> etc.
>
>
Personally, yes, a declarative syntax specifying the desired behavior and
thus giving the system permission to potentially trade optimizations for a
deterministic output is the way forward.  Ideally current and future
optimizations/performance characteristics could be maintained even in the
presence of this option but ultimately the system would be compelled to
return records in the specified order no matter the cost.  For bonus
points, the impact on queries that do not specify this option should be as
close to nothing as possible - though I/O is likely to make some increase
negligible.

David J.

Re: Guidance on INSERT RETURNING order

[Karsten Hilbert]

Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:

> The problem is that SQLAlchemy is an ORM [...]
...
> [...] as the majority of the python world will use this ORM for
> their database needs.

I wouldn't be so sure on this count ...

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Guidance on INSERT RETURNING order

[Adrian Klaver]

On 4/14/23 15:44, Karsten Hilbert wrote:

Am Fri, Apr 14, 2023 at 10:44:19PM +0100 schrieb John Howroyd:


The problem is that SQLAlchemy is an ORM [...]

...

[...] as the majority of the python world will use this ORM for
their database needs.


I wouldn't be so sure on this count ...


+1


Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




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

Re: Guidance on INSERT RETURNING order

[Tom Lane]

Federico  writes:
> Would something like what was proposed by Mike Bayer be considered?

>> A new token called "tuple_order" or something
>> 
>> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING 
>> table.id, inserted.tuple_order
>> 
>> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which correlate 
>> the each row delivered by RETURNING to each entry in the VALUES clause, in 
>> the order they were stated in that VALUES clause, that is entry (1, 2, 3) 
>> would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, etc.

As proposed, I don't think so.  Something over in the RETURNING clause has
exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
and there are several VALUES clauses down inside the SELECT?

There is some prior art in this area, though.  See the more-or-less
SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
which would give you a rock-solid connection between the VALUES rows and
the ordinality-column values, and then you could include that column in
RETURNING.

regards, tom lane

Re: Guidance on INSERT RETURNING order

[David G. Johnston]

On Fri, Apr 14, 2023 at 8:17 PM Tom Lane  wrote:

> Federico  writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...)
> RETURNING table.id, inserted.tuple_order
> >>
> >> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which
> correlate the each row delivered by RETURNING to each entry in the VALUES
> clause, in the order they were stated in that VALUES clause, that is entry
> (1, 2, 3) would be tuple_order 1, entry (4, 5, 6) would be tuple order 2,
> etc.
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?
>
> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.
>
>
I suppose breaking the restriction that only columns present on the
insertion-table can be returned is a possible option that also solves
another infrequent request.

Adding with ordinality to the values clause seems like an independent
feature that has merit on its own for the same reason SRFs have that option.

Ultimately, though, whatever is supplying the rows to the insert is going
to have to supply them in some order, and the returning clause can simply
modify the executor to perform an initial pass over the input data in order
to assign the ordering indexes before allowing for parallelism, and then
sort again on the way out.  User-space does/should not need to create data
for this purpose.  A top-level values query should not need to be specified
with ordinality while subquery ones would be hidden from the mechanism, the
parent query being responsible for any ordering that is desired.

David J.

Re: Guidance on INSERT RETURNING order

[Federico]

On Sat, 15 Apr 2023 at 05:17, Tom Lane  wrote:
>
> Federico  writes:
> > Would something like what was proposed by Mike Bayer be considered?
>
> >> A new token called "tuple_order" or something
> >>
> >> INSERT INTO table (a, b, c) VALUES ((1, 2, 3), (4, 5, 6), ...) RETURNING 
> >> table.id, inserted.tuple_order
> >>
> >> tuple_order would be incrementing values 1, 2, 3, 4, 5, ... which 
> >> correlate the each row delivered by RETURNING to each entry in the VALUES 
> >> clause, in the order they were stated in that VALUES clause, that is entry 
> >> (1, 2, 3) would be tuple_order 1, entry (4, 5, 6) would be tuple order 2, 
> >> etc.
>
> As proposed, I don't think so.  Something over in the RETURNING clause has
> exactly no connection to VALUES.  What do you do if it's INSERT ... SELECT
> and there are several VALUES clauses down inside the SELECT?

I think the tuple order would not be connected to the values, but be
determined by the input order of the rows in the insert. So when using
INSERT ... SELECT the tuple_order value would be determined by the
output of the select, using the same logic as ROW_NUMBER.

> There is some prior art in this area, though.  See the more-or-less
> SQL-standard WITH ORDINALITY option for functions-in-FROM.  It seems to me
> that it could be plausible to attach WITH ORDINALITY to a VALUES clause,
> which would give you a rock-solid connection between the VALUES rows and
> the ordinality-column values, and then you could include that column in
> RETURNING.

Well if returning could output rows that are not in the inserted
table, like mentioned also by Devid Johnston, it would open to
alternatives to tuple_order, like using

  INSERT INTO tbl(data)
  SELECT v.d FROM (VALUES ('x', 1), ('y', 2)) AS v(d, num)
  RETURNING tbl.id, v.num

> regards, tom lane

I'm not familiar with the internals of Postgresql, so I don't know
what would be more complex to implement in this case. I think both
solutions would work equally well from the client point of view.

Thanks for the reply,
 Federico