Re: create batch script to import into postgres tables
I am happy to help with testing the bash script and producing a documentation. Regards, Shao On Sat, 20 Jun 2020 at 02:38, David G. Johnston wrote: > On Friday, June 19, 2020, pepevo wrote: > >> But everything can run by script on the server, right? >> > > Separation of concerns. The server with the database cluster should > probably not be running application code. Application code can be run > other machine, “admin” machine is one label. Though for development it > shouldn’t matter so long as the application is configurable. Configure it > for local during development and when in production it pulls production > configuration. > > David J. > >
Is this a bug in pg_current_logfile() on Windows?
Hello, I noticed the following strage output when running Postgres 12.3 (not psql) on Windows postgres=# select pg_current_logfile(); pg_current_logfile pg_log/postgresql-2020-07-08.log\r (1 row) Note the "\r" at the end of the file name. This does not happen when running Postgres on Linux. Is this intended for some strange reason? Or a bug or a technical limitation? Regards Thomas
Re: Is this a bug in pg_current_logfile() on Windows?
On 7/8/20 6:05 AM, Thomas Kellerer wrote: Hello, I noticed the following strage output when running Postgres 12.3 (not psql) on Windows postgres=# select pg_current_logfile(); pg_current_logfile pg_log/postgresql-2020-07-08.log\r (1 row) Note the "\r" at the end of the file name. This does not happen when running Postgres on Linux. Is this intended for some strange reason? Or a bug or a technical limitation? I'm guessing the difference between Unix line ending: \n and Windows: \r\n Regards Thomas -- Adrian Klaver adrian.kla...@aklaver.com
Re: Is this a bug in pg_current_logfile() on Windows?
On 7/8/20 6:45 AM, Adrian Klaver wrote:
On 7/8/20 6:05 AM, Thomas Kellerer wrote:
Hello,
I noticed the following strage output when running Postgres 12.3 (not
psql) on Windows
postgres=# select pg_current_logfile();
pg_current_logfile
pg_log/postgresql-2020-07-08.log\r
(1 row)
Note the "\r" at the end of the file name.
This does not happen when running Postgres on Linux.
Is this intended for some strange reason?
Or a bug or a technical limitation?
I'm guessing the difference between Unix line ending:
\n
and Windows:
\r\n
From source(backend/utils/adt/misc.c):
nlpos = strchr(log_filepath, '\n');
if (nlpos == NULL)
{
/* Uh oh. No newline found, so file content is corrupted. */
elog(ERROR,
"missing newline character in \"%s\"",
LOG_METAINFO_DATAFILE);
break;
}
*nlpos = '\0';
if (logfmt == NULL || strcmp(logfmt, log_format) == 0)
{
FreeFile(fd);
PG_RETURN_TEXT_P(cstring_to_text(log_filepath));
}
Regards
Thomas
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Hello Raf, Tom, After many tests of proposal solutions, I am not able to solve the issue. Seems that It is a bug ? Le jeu. 2 juil. 2020 à 01:59, Tom Lane a écrit : > raf writes: > > FOUTE K. Jaurès wrote: > >> The result of df -i > >> > >> Sys. de fichiers Inœuds IUtil. ILibre IUti% Monté sur > >> udev 3065149433 30647161% /dev > >> tmpfs 3072780665 30721151% /run > >> /dev/sdb259973632 356029 596176031% / > >> tmpfs 3072780 7 30727731% /dev/shm > >> tmpfs 3072780 10 30727701% /run/lock > >> tmpfs 3072780 17 30727631% /sys/fs/cgroup > >> /dev/sdb1 0 00 - /boot/efi > >> tmpfs 3072780 19 30727611% /run/user/108 > >> tmpfs 3072780 5 30727751% /run/user/1001 > >> tmpfs 3072780 5 30727751% /run/user/1000 > > > So that's not it. It would take ~60 million > > files to fill up your / inode table. I can't > > think of another explanation for that error > > message if df without -i also shows free space. > > I'm going to take a shot in the dark and ask if the root file system > is XFS. It doesn't take too much googling to find out that XFS has a > reputation for reporting ENOSPC when there seems to be more than enough > room left. Apparently, it can do so as a result of fragmentation problems > even though the disk as a whole has lots of space --- for one cautionary > example see this thread: > > https://www.spinics.net/lists/linux-xfs/msg22856.html > > typo-ishly titled "ENSOPC on a 10% used disk". It looks like the XFS crew > installed a fix for the underlying bug ... but that thread is from 2018 > and you're running a 2015 Ubuntu release. > > Or, to cut to the chase: maybe updating to a less hoary kernel would help. > If you are stuck with this Ubuntu release for some reason, consider using > a less bleeding-edge-at-the-time file system. > > regards, tom lane > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
BigSerial and txid issuance
Hi Everyone, We’re trying to solve a problem that relies on BigSerial and txid (as returned by txid_current() ) values being issued consistently. Is it possible for two transactions to interleave their issuance of these two variables? For example: Schema: CREATE TABLE EXAMPLE( offset bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL, txid BIGINT NOT NULL DEFAULT 1 ); Insert statement: "INSERT INTO EVENTS (txid) VALUES (txid_current());"; Prior to transactions executing offset is 10 and txid is 1000. Transaction 1 and 2 occur concurrently. Is it possible for transaction 1 to be issued txid 1001 and offset 12 and transaction 2 to be issued txid 1002 and offset 11? Any help would be much appreciated. Regards, Adam This is a confidential email. Tesco may monitor and record all emails. The views expressed in this email are those of the sender and not Tesco. Tesco Stores Limited Company Number: 519500 Registered in England Registered Office: Tesco House, Shire Park, Kestrel Way, Welwyn Garden City, AL7 1GA VAT Registration Number: GB 220 4302 31
Re: BigSerial and txid issuance
On Wed, Jul 8, 2020 at 8:18 AM Yorwerth, Adam wrote: > Is it possible for two transactions to interleave their issuance of these > two variables? > > > > Is it possible for transaction 1 to be issued txid 1001 and offset 12 and > transaction 2 to be issued txid 1002 and offset 11? > Given all of the disclaimers about serial value issuance you should assume that it is possible. David J.
Re: BigSerial and txid issuance
On 7/8/20 7:09 AM, Yorwerth, Adam wrote: Hi Everyone, We’re trying to solve a problem that relies on BigSerial and txid (as returned by txid_current() ) values being issued consistently. Is it possible for two transactions to interleave their issuance of these two variables? For example: Schema: CREATE TABLE EXAMPLE( offset bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL, txid BIGINT NOT NULL DEFAULT 1 ); Insert statement: "INSERT INTO EVENTS (txid) VALUES (txid_current());"; Prior to transactions executing offset is 10 and txid is 1000. Transaction 1 and 2 occur concurrently. Is it possible for transaction 1 to be issued txid 1001 and offset 12 and transaction 2 to be issued txid 1002 and offset 11? Well IDENTITY is backed by a SEQUENCE and: https://www.postgresql.org/docs/12/sql-createsequence.html "Unexpected results might be obtained if a cache setting greater than one is used for a sequence object that will be used concurrently by multiple sessions. Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object's last_value accordingly. Then, the next cache-1 uses of nextval within that session simply return the preallocated values without touching the sequence object. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in “holes” in the sequence. Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially. Also, last_value will reflect the latest value reserved by any session, whether or not it has yet been returned by nextval." Any help would be much appreciated. Regards, Adam This is a confidential email. Tesco may monitor and record all emails. The views expressed in this email are those of the sender and not Tesco. Tesco Stores Limited Company Number: 519500 Registered in England Registered Office: Tesco House, Shire Park, Kestrel Way, Welwyn Garden City, AL7 1GA VAT Registration Number: GB 220 4302 31 -- Adrian Klaver adrian.kla...@aklaver.com
Re: Is this a bug in pg_current_logfile() on Windows?
Thomas Kellerer writes: > I noticed the following strage output when running Postgres 12.3 (not psql) > on Windows > postgres=# select pg_current_logfile(); > pg_current_logfile > > pg_log/postgresql-2020-07-08.log\r > (1 row) > Note the "\r" at the end of the file name. Yeah, that seems like a bug. I think the reason is that syslogger.c does this when writing the log metafile: fh = fopen(LOG_METAINFO_DATAFILE_TMP, "w"); ... #ifdef WIN32 /* use CRLF line endings on Windows */ _setmode(_fileno(fh), _O_TEXT); #endif while misc.c only does this when reading the file: fd = AllocateFile(LOG_METAINFO_DATAFILE, "r"); Somehow, the reading file is being left in binary mode and thus it's failing to convert \r\n back to plain \n. Now the weird thing about that is I'd have expected "r" and "w" modes to imply Windows text mode already, so that I'd have figured that _setmode call to be a useless no-op. Apparently on some Windows libc implementations, it's not. How was your installation built exactly? regards, tom lane
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
=?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= writes: > After many tests of proposal solutions, I am not able to solve the issue. > Seems that It is a bug ? I agree. But it's not *our* bug: Postgres is merely reporting what the OS told it. File a kernel bug with your OS vendor. regards, tom lane
Re: Is this a bug in pg_current_logfile() on Windows?
Tom Lane schrieb am 08.07.2020 um 18:41: Somehow, the reading file is being left in binary mode and thus it's failing to convert \r\n back to plain \n. Now the weird thing about that is I'd have expected "r" and "w" modes to imply Windows text mode already, so that I'd have figured that _setmode call to be a useless no-op. Apparently on some Windows libc implementations, it's not. How was your installation built exactly? That's the build from EnterpriseDB https://www.enterprisedb.com/download-postgresql-binaries
pg_dump / pg_restore option
Hello all! I'm experimenting with options to upgrade databases from older (v9.x) to more recent (probably v11) versions of PostgreSql and, partitioning some large tables during the process. The idea was: 1) To make a dump of the old database, wich I've done with the custom format 2) To restore the section "pre-data" on the new instalation 3) To make the desired changes, creating the partitions structures (quite simple: by ranges of "year" columns) 4) To restore the section "data" 5) To restore the section "post-data" It all went well until step 5, but the creation of FOREIGN KEYS on the partitioned tables raised errors, because of the "ALTER TABLE ONLY ADD CONSTRAINT..." syntax of pg_dump output file. As it is needed that the partitions do exist in the previous step, that "ONLY" clause creates a situation that needs some more scripts to overcome. I've checked that the pg_dump v11 generates different versions of commands for partitioned and non-partitioned tables. Is there a parameter to suppress that keyword, or some other option I coud use? Thanks in advance! Regards Luiz Hugo Ronqui
Re: pg_dump / pg_restore option
On 7/8/20 12:27 PM, Luiz Hugo Ronqui wrote: Hello all! I’m experimenting with options to upgrade databases from older (v9.x) to more recent (probably v11) versions of PostgreSql and, partitioning some large tables during the process. The idea was: 1)To make a dump of the old database, wich I’ve done with the custom format 2)To restore the section “pre-data” on the new instalation 3)To make the desired changes, creating the partitions structures (quite simple: by ranges of “year” columns) 4)To restore the section “data” 5)To restore the section “post-data” It all went well until step 5, but the creation of FOREIGN KEYS on the partitioned tables raised errors, because of the “ALTER TABLE ONLY ADD CONSTRAINT...” syntax of pg_dump output file. As it is needed that the partitions do exist in the previous step, that “ONLY” clause creates a situation that needs some more scripts to overcome. I’ve checked that the pg_dump v11 generates different versions of commands for partitioned and non-partitioned tables. Is there a parameter to suppress that keyword, or some other option I coud use? Why not just restore the dump file as is and then partition the tables? Thanks in advance! Regards Luiz Hugo Ronqui -- Adrian Klaver adrian.kla...@aklaver.com
