Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version
Peter Eisentraut writes: > On 10.10.23 08:22, Laurenz Albe wrote: >> Apart from that, it is a good idea to use table names that are standard >> SQL identifiers, so that you don't have to double quote them all the time. > FWIW, the Chinese character sequences posted here would be valid > unquoted identifiers if PostgreSQL implemented standard SQL > correctly/completely. I'm pretty sure they're valid unquoted identifiers today, because by and large we'll take any non-ASCII as identifier characters. Conforming to the letter of the spec would reduce, not increase, the set of strings we'll call identifiers. regards, tom lane
Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version
On 10.10.23 08:22, Laurenz Albe wrote: Apart from that, it is a good idea to use table names that are standard SQL identifiers, so that you don't have to double quote them all the time. FWIW, the Chinese character sequences posted here would be valid unquoted identifiers if PostgreSQL implemented standard SQL correctly/completely.
A question about possible recovery inconsistency
Hello.
We had PostgreSQL v11 and used PITR with our database.
But PITR behavior was changed in v13
>https://www.postgresql.org/docs/release/13.0/
>Generate an error if recovery does not reach the specified recovery target
>(Leif Gunnar Erlandsen, Peter Eisentraut)
>Previously, a standby would promote itself upon reaching the end of WAL, even
>if the target was not reached.
I tried to change our scripts to include the time where base backup is
started (also I tried the time when the base backup was finished).
But all my recoveries failed with: `XX000: recovery ended before
configured recovery target was reached`
So we decided to use `recovery_target_name` instead of
`recovery_target_time`. But this attempt also failed with the error
above.
Backup procedure looks like this:
1. Insert data
2. t3=$(date +"%Y-%m-%d %T")
3. psql -c "select pg_create_restore_point( '$t3' )"
4. wal-g backup-push $PGDATA. This tool lever low level API
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP.
't3' value was used for `recoverty_target_name`.
Issue here is that `t3` name was not included into base backup and
following WAL files. Logs looks like this:
xargs -n1 '-I{}' docker exec test-userdb-restore psql -d userdb -U
user_api -c 'INSERT INTO test (num) VALUES ({});'
INSERT 0 1
docker exec test-userdb-restore psql -c 'select
pg_create_restore_point( '\''2023-10-10 14:48:46'\'' )'
pg_create_restore_point
-
0/5000640
(1 row)
wal-g wal-push pg_wal/00010006.0028.backup
/dbdata/postgres/pg_wal:
total 65540
-rw--- 1 postgres postgres 16777216 Oct 10 14:48 00010005
-rw--- 1 postgres postgres 16777216 Oct 10 14:48 00010006
-rw--- 1 postgres postgres 364 Oct 10 14:48
00010006.0028.backup
-rw--- 1 postgres postgres 16777216 Oct 10 14:48 00010007
-rw--- 1 postgres postgres 16777216 Oct 10 14:48 00010008
drwx-- 5 postgres postgres 160 Oct 10 14:48 archive_status
drwx-- 3 root root 96 Oct 10 14:48 walg_data
/dbdata/postgres/pg_wal/archive_status:
total 0
-rw--- 1 postgres postgres 0 Oct 10 14:48 00010005.done
-rw--- 1 root root 0 Oct 10 14:48
00010006.0028.backup.done
-rw--- 1 root root 0 Oct 10 14:48 00010006.done
/dbdata/postgres/pg_wal/walg_data:
total 0
drwx-- 2 postgres postgres 64 Oct 10 14:48 walg_archive_status
/dbdata/postgres/pg_wal/walg_data/walg_archive_status:
total 0
+ docker exec test-userdb-restore bash -c 'cat $PGDATA/pg_wal/$(ls -R
$PGDATA/pg_wal | grep backup | head -n 1)'
START WAL LOCATION: 0/628 (file 00010006)
STOP WAL LOCATION: 0/6000100 (file 00010006)
CHECKPOINT LOCATION: 0/660
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-10-10 14:48:46 UTC
LABEL: 2023-10-10 14:48:46.482532 + UTC m=+0.058620814
START TIMELINE: 1
STOP TIME: 2023-10-10 14:48:47 UTC
STOP TIMELINE: 1
When server was stopped couple of WAL files are created:
+ cd /dbdata/postgres/pg_wal
+ ls -R
.:
00010006.0028.backup
00010007
00010008
00010009
0001000A
archive_status
walg_data
./archive_status:
00010006.0028.backup.done
**Created `recovery.signal` and started server**
+ echo 'recovery_target_name= '\''2023-10-10 14:48:46'\'''
+ echo 'recovery_target_action= '\''promote'\'''
+ touch /dbdata/postgres/recovery.signal
2023-10-10 14:48:51.825 UTC [127] database system was interrupted;
last known up at 2023-10-10 14:48:46 UTC
2023-10-10 14:48:51.985 UTC [127] LOG: starting point-in-time
recovery to "2023-10-10 14:48:46"
2023-10-10 14:48:52.065 UTC [127] LOG: restored log file
"00010006" from archive
2023-10-10 14:48:52.082 UTC [127] LOG: redo starts at 0/628
2023-10-10 14:48:52.141 UTC [127] LOG: consistent recovery state
reached at 0/6000100
2023-10-10 14:48:52.141 UTC [124] LOG: database system is ready to
accept read-only connections
2023-10-10 14:48:52.188 UTC [127] LOG: redo done at 0/6000100 system
usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.10 s
2023-10-10 14:48:52.188 UTC [127] FATAL: recovery ended before
configured recovery target was reached
See full logs in `experiment1.txt` attachment.
Ok. Some name in the past is not included into the base backup which
was done just now.
This is expected, so I created the named restore point after base backup.
Now logs are:
+ docker exec test-userdb-restore psql -c 'select
pg_create_restore_point( '\''2023-10-10 15:07:37'\'' )'
pg_create_restore_point
-
0/790
(1 row)
+ cat 00010006.0028.backup
START WAL LOCATION: 0/628 (file 00010006)
STOP WAL LOCATION: 0/6000100 (file 00010006)
CHECKPOINT LOCATION: 0/600
Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version
Laurenz Albe writes: > On Tue, 2023-10-10 at 15:53 +0900, Tatsuo Ishii wrote: >> Another solution would be, letting the meaning of NAMEDATALEN to be >> number of *characters*, not the number of bytes. This way, you can use >> up to 64 UTF-8 characters. In my understanding MySQL already does this >> way. I know this requires non trivial code modifications to PostgreSQL >> but would be better than to make binaries with random NAMEDATALEN >> values. > Since "name" is a fixed-length data type, that would require the stored > size to increase to accomodate the extra bytes. Wouldn't that change the > storage format and break pg_upgrade? Yeah, the real reason this is unlikely to happen is precisely that "name" is fixed-length. Increasing the standard NAMEDATALEN by 4x, or even 2x, has been proposed and rejected many times before because of the bloat it would cause in places like pg_attribute, pg_proc, in-memory tuple descriptors, etc. The real way forward IMO is to find a way to make "name" variable-length, thus both satisfying people who need a few long names and reducing overhead for everybody. This is difficult to do without breaking mountains of backend code, but there's been some discussions about ways to accomplish that. The most recent thread I could find is https://www.postgresql.org/message-id/flat/CALSd-crdmj9PGdvdioU%3Da5W7P%3DTgNmEB2QP9wiF6DTUbBuMXrQ%40mail.gmail.com regards, tom lane
Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version
On Tue, 2023-10-10 at 15:53 +0900, Tatsuo Ishii wrote: > Another solution would be, letting the meaning of NAMEDATALEN to be > number of *characters*, not the number of bytes. This way, you can use > up to 64 UTF-8 characters. In my understanding MySQL already does this > way. I know this requires non trivial code modifications to PostgreSQL > but would be better than to make binaries with random NAMEDATALEN > values. Since "name" is a fixed-length data type, that would require the stored size to increase to accomodate the extra bytes. Wouldn't that change the storage format and break pg_upgrade? Yours, Laurenz Albe
