csr.description
(no output again after a failed query)
> Basically, it's like doing \d on a cursor. Unfortunately, it's not
> interactive,
> one has to do some pythong programming in order do to that. Unfortunately, it
> is not possible to just "describe the cu
system - I'd just clone the production system again to create a new test
system and see if the problem happens there, too.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stros
d in the master to affect the worker processes as
consistently as you are seeing. I don't know if it is even possible for
a non-standard rounding mode to persist for any length of time, but if
it is it would certainly account for weird rounding errors.
hp
--
_ | Peter J. Holzer
quot;
(sometimes withe process id attached). You can then use a debugger to
inspect this file and investigate the cause of the crash.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, &
Scan on facttable_imf_ifs_pkey
(cost=0.00..2.07 rows=1 width=0)
║ Index Cond: (macrobondtimeseries IS NULL)
╚═══╝
hp
--
_ | Peter J. Holzer
On 2017-11-28 09:35:33 -0500, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > I noticed that an update was taking a long time and found this:
> > [ crappy plan for join on IS NOT DISTINCT ]
>
> Yeah, there's no optimization smarts at all for IS [NOT] DIST
On 2017-11-28 20:48:24 +0100, Laurenz Albe wrote:
> Peter J. Holzer wrote:
> > I noticed that an update was taking a long time and found this:
> >
> > UPDATE public.facttable_imf_ifs p
> > SET [...lots of columns...]
> > FROM cleansin
inserts within a DO block or
> wrapping each 'thing' insert in it's own DO block?
I would suggest getting rid of the do block entirely if that is
possible. Just create lots of insert statements. You can get the current
value of a sequence with currval('sequence_name').
On 2017-11-30 08:43:32 -0600, Ted Toth wrote:
> Date: Thu, 30 Nov 2017 08:43:32 -0600
> From: Ted Toth
> To: "Peter J. Holzer"
> Cc: pgsql-general@lists.postgresql.org
> Subject: Re: large numbers of inserts out of memory strategy
>
> On Thu, Nov 30, 2017 at
On 2017-11-30 14:27:58 -0600, Ted Toth wrote:
> On Thu, Nov 30, 2017 at 11:40 AM, Peter J. Holzer wrote:
> > On 2017-11-30 08:43:32 -0600, Ted Toth wrote:
> >> One thing that is unclear to me is when commits occur while using psql
> >> would you know where in the doc
ular OS.
https://www.postgresql.org/download/linux/suse/?
> Or run a supported OS; that one looks pretty old.
SLES 12 is the current version of SLES.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
|
this?
(Maybe with a different pglogical.conflict_resolution setting, but if it
was a conflict, I should see something in the logs, right?)
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | m
On 2017-12-07 14:41:31 +0100, Peter J. Holzer wrote:
> We are testing bidirectional replication with pglogical:
[...]
> We have configured bidirectional replication on several tables. In
> general that seems to work fine. When a row is inserted/updated/
> deleted on one node, it is r
of 100 involved? (Is should be, because that's what
"cent" means, but it's missing in percent_rank()). If such a function
existed, I would avoid it.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we hav
e
whole is 1, it's not a percentage (and yes, percent_rank() is misnamed).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www
sn't prevent you
from removing a row from the target table.
The only way I can think of is with an intersection table.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | |
On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer wrote:
> >...Is there a way to
> >enforce foreign key constraints on the members of an array?
> >At insert time you can check with a trigger of course, and maybe there
&g
included into Postgres!
>
> Actually, I think the op may be referring to a MULTI COLUMN FK array
I see no reason to assume that. He explicitely asked about an array of
foreign keys, and there is no array here.
hp
--
_ | Peter J. Holzer| we build much bigger, better dis
On 2017-12-23 14:40:13 -0500, Melvin Davidson wrote:
> On Sat, Dec 23, 2017 at 2:27 PM, Peter J. Holzer wrote:
> >If you do not understand something, please ask.
>
> Your response is inappropriate and offensive.
I apologize for my tone. I should have calmed down before repl
which has an A record. So that might
work on some platforms.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Ander
#x27;s version draws attention
to the fact that enabling CRCs may cause extra disk writes, which rings
a much louder alarm bell for me. (When are those hint-bits set? Does this
happen often when otherwise no write would have been necessary? I have
no idea so I guess I'd better measure it!)
║ 18/0113 ║
║ 18/0212 ║
║ 180/2010 ║
║ 23/4567 ║
╚══╝
(4 rows)
It might be possible to define a custom collation for that, but in a
case like this I would either split this field into two integer fields
or use a function-based index.
hp
--
_ | Peter J. Holzer| we build much
On 2018-01-14 13:20:05 +0100, Francisco Olarte wrote:
> On Sun, Jan 14, 2018 at 12:14 PM, Peter J. Holzer wrote:
> > On 2018-01-12 11:08:39 +0100, Francisco Olarte wrote:
> >> C collation is like sorting raw bytes, it doesn't event sort
> >> upper/lower case corre
pecially between the 2nd
and 3rd). But in your case it is just the opposite.
> [cleardot]
Sending Webbugs to a mailinglist?
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@
, so a single client might use more than one core).
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Ande
ql -c '\copy ...'
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
tgreSQL model. If the programmer wants to tolerate and error,
they have to handle it explicitely (with a savepoint or even a full
transaction).
I can't really think of a reason why the MSSQL behaviour might be
useful, but I'm sure that they had a use-case in mind when they designed
this
gt; > They allow to provide a try/catch behavior with the ability
> > to continue to use a transaction after a failure.
I agree. The goal isn't to ignore the error but to handle it.
hp
--
_ | Peter J. Holzer| we build much bigger, better disasters now
|_|_) |
on both PostgreSQL and MariaDB to get a list of tables.
(That said, it looks like both PostgreSQL and MariaDB include additional
columns beyond those mandated by the standard - you can't rely on those,
of course. And some databases like Oracle don't even have an information
schema.)
On 2018-02-13 16:06:43 -0700, David G. Johnston wrote:
> On Tue, Feb 13, 2018 at 3:57 PM, Peter J. Holzer wrote:
>
> (That said, it looks like both PostgreSQL and MariaDB include additional
> columns beyond those mandated by the standard - you can't rely on those,
>
On 2024-10-05 15:40:06 -0700, Adrian Klaver wrote:
> On 10/5/24 15:25, Peter J. Holzer wrote:
> > On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> > > "Peter J. Holzer" writes:
> > > > Again, I'm not arguing for such a change, but I'm wondering if
On 2024-10-05 09:59:00 -0700, Adrian Klaver wrote:
> On 10/5/24 02:14, Peter J. Holzer wrote:
> > On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> > > "Peter J. Holzer" writes:
> > > Admittedly, that would normally not be a very long interval if BEGIN
> >
t yourself (which you
probably have to anyway because for me "so large it has to be streamed"
implies "at least possibly larger than 1 GB").
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at
wn
processes since a *very* long time ago. Possibly even before Ubuntu even
existed. So I'm somewhat sceptical about that. Some other Unixes were
more permissive. I don't know what camp MacOS falls into.
hp
--
_ | Peter J. Holzer| Story must make more sense than realit
7;t get mysql to work like postgresql, and any tips to speed up this
query on postgresql (which is all you can expect on a postgresql mailing
list) probably won't work on mysql.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
|
On 2024-09-25 18:09:44 -0400, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > On 2024-09-25 13:53:30 -0700, Christophe Pettus wrote:
> >> This might well be a failure of imagination on my part, but when would
> >> it pragmatically matter that the snapshot is
On 2024-09-27 18:37:35 -0400, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > As you can see, adding the primary key takes just as much time as
> > creating the unique index. So it doesn't look like PostgreSQL is able to
> > take advantage of the existing ind
On 2024-10-05 17:03:08 -0400, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > Again, I'm not arguing for such a change, but I'm wondering if recording
> > transaction_timestamp just after the snapshot might be a safe change or
> > whether that might b
I'm using Thunderbird. If you have better software, tell me please.
I'm using (neo)mutt, but these days the limitations of a text-only
mailer can be quite noticeable.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |
possible. For me it's much better to get all the mails through
the list (so I can use the List-ID header to filter them into the
appropriate folder) and live with the extra copies in my inbox. I would
prefer to not get those extra copies, but there is nothing the list can
do about them, that'
like this issue, or the parent one that everyone is linking to this.
That seems to be just about the way it is reported, not the behaviour.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Char
porary files, depending on your
work_mem settings), but I wouldn't expect them to happen just before the
configured limit is reached. So I'd double check the logs if there are
any errors.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) |
y usual distributions. It's now in both Debian and Ubuntu,
so that will change.
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
On 2024-09-21 20:55:13 +0530, Lok P wrote:
> On Sat, Sep 21, 2024 at 8:07 PM Peter J. Holzer wrote:
[... lots of code elided. method2 used 1 insert per row, method3 1
insert for 50 rows ...]
> On my laptop, method2 is about twice as fast as method3. But if I
> connect to a da
cording to the rules of their language and C collation is in most
cases very different.
hp
[1] I actually have LC_COLLATE=POSIX set in the shell. But I'm not
normal.
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h.
from the target table that attempt to match data_source`
rows" for me sort of sounds like those columns have to have a counterpart
in the data_source, which k1 hasn't. Also maybe the order is the wrong
way around? "Match rows in the target to rows in the data_source" wo
On 2024-09-21 15:06:45 +0530, veem v wrote:
> On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer wrote:
>
> On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote:
> > On 9/20/24 1:01 PM, veem v wrote:
> > > Able to reproduce this deadlock graph as below. Now my
>
bout twice as fast as method3. But if I
connect to a database on the other side of the city, method2 is now more
than 16 times faster than method3 . Simply because the delay in
communication is now large compared to the time it takes to insert those
rows.
hp
--
_ | Peter J. Holzer
ally
triggers the snapshot.
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
me: 5051.584 ms (00:05.052)
hjp=> alter table t add primary key(i);
ALTER TABLE
Time: 5222.788 ms (00:05.223)
As you can see, adding the primary key takes just as much time as
creating the unique index. So it doesn't look like PostgreSQL is able to
take advantage of the existing index (w
On 2024-09-19 20:12:13 +0200, Paul Foerster wrote:
> Hi Peter,
> > On 19 Sep 2024, at 19:43, Peter J. Holzer wrote:
> >
> > I wrote a small script[1] which prints all unicode code points and a few
> > selected[2] longer strings in order. If you run that before and af
ccur. So an application designed for serializable
would have some kind of retry logic already in place.
SO that leads as to another solution:
Retry each batch (possibly after reducing the batch size) until it
succeeds.
hp
--
_ | Peter J. Holzer| Story must make more sense
ndexes on text (etc.) columns
just to be sure.
hp
[1] https://git.hjp.at:3000/hjp/pgcollate
[2] The selection is highly subjective and totally unscientific.
Additions are welcome.
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
On 2024-10-12 09:02:37 -0700, Adrian Klaver wrote:
> On 10/12/24 03:17, Peter J. Holzer wrote:
> > On 2024-10-11 21:21:16 -0700, Adrian Klaver wrote:
> > > On 10/11/24 20:10, admin@iseki.space wrote:
> > > > I found. Maybe we should reply to the mailing list only. O
7;t exist. You
should create that before restoring the backup. Or could fix the errors
after the fact but for that you need to understand what went wrong.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at
restore a 25 GB database, so
that's what I'd try first. It's simple and you can easily test it
without disruption.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross,
On 2025-02-07 09:22:13 +0100, Michał Kłeczek wrote:
>
>
> On 6 Feb 2025, at 22:03, Peter J. Holzer wrote:
>
> On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>
>
> I might see what you want to point out. E.g. the table is COLOURS. The
>
magically change color just because you changed some text in the
database. So that change simply doesn't make sense and shouldn't be done
as part of a maintenance release. Confusing a few people who just happen
to open the dropdown in the wrong second is the least of your problems.
ng similar working?
Yes. Cleaning up stuff is probably one of the most frequent uses of
cron.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writ
om what I
> can tell from @Laurenz's response above we have the names of the logs
> customised to posgtres-%d-%m-%y.
Earlier you wrote that the pattern was actually
«postgresql-%Y-%m-%d.log». «find ... -name "*.log"» would find that but
of course not «posgtres-%d-%m-%y».
t; account, you should still be able to locally connect to PG.
True. But the client may not be on the same machine.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Cr
strong passwords, use a second factor. Or maybe
replace passwords with some other method (public keys, FIDO, ...)
altogether (in fact, I'd do that for system accounts).
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |
On 2024-12-16 10:37:59 -0500, Ron Johnson wrote:
> On Mon, Dec 16, 2024 at 10:19 AM Peter J. Holzer wrote:
>
> On 2024-12-16 09:17:25 -0500, Ron Johnson wrote:
> > Local (socket-based) connections are typically peer-authenticated
> > (meaning that authenticatio
illion attempts (so the limit doesn't help either).
OTOH, the limit gives an attacker a very simple way to deny the service to
the legitimate used: Just enter a bogus password three times and boom -
account locked. (That threat can be mitigated by applying the limit per
IP address - but the a
one = false
ORDER BY id
FOR NO KEY UPDATE
2) Check whether the id you got first is the smallest of all.
3) If it isn't, rollback and start over.
4) If it is, you have now locked all the rows with the same lock_id and
can continue.
The advisory lock isn't needed then.
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
; | uniq -c | sort -n
to find the processes with the most open files (but be aware that lsof
reports file descriptors for each thread, so any multi-threaded programs
will be vastly inflated)
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
. It's not
really "number of seconds since 1970-01-01", but "number of days since
1970-01-01 times 86400 plus number of seconds in the current day".
So you can't use epoch to detect leap seconds.
And I don't think PostgreSQL keeps track of leap seconds int
That might
work, but it probably also shouldn't do it by default.
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
On 2025-01-12 17:59:20 -0500, Tom Lane wrote:
> "Peter J. Holzer" writes:
> > The web framework Django will automatically and transparently rehash any
> > password with the currently preferred algorithm if it isn't stored that
> > way already.
>
> Re
7;t really a faster way to do what Veem wants. There may
however be less disruptive way: He could create a new column with the
new values (which takes at least as long but can be done in the
background) and then switch it over and drop the old column.
hp
--
_ | Peter J. Holzer
ure should only be enabled by a GUC.
Additional question: Do current clients (especially the ODBC client)
even support AuthenticationCleartextPassword by default?
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at
new version
2) Drop the new (empty) database
3) Invoke pg_upgradecluster (see man-page for details)
4) Check that everything is ok
5) Drop old database and uninstall old version.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) |
On 2025-04-20 08:28:22 -0700, Adrian Klaver wrote:
> On 4/20/25 02:10, Peter J. Holzer wrote:
> > I've just read Laurenz' blog post about the differences between Oracle
> > and PostgreSQL[1].
> >
> > One of the differences is that something like
>
especially for large tables.
So, is there a better way?
hjp
[1]
https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresql/
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hj
you want to do updates in a
predictable order. For example to prevent deadlocks.
hjp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | h
t happens automatically with the
default settings.
> Even I enable it now, I can't figure out that error.
Just search the logs for the string "ERROR".
> By any chance, if I get that statement, what should I do?
1) Read the error message.
2) Figure out what caused the error
order which can change, I guess?
Anyway, I don't have a pressing need for this, as I said I was just
curious.
hjp
[1] Mostly in MySQL I think, since it didn't have recursive queries of
any kind.
--
_ | Peter J. Holzer| Story must make mo
dfbsspec.ssa stands along in the view
> definition, Postgres does the right thing; when the exact same query
> stands in a UNION ALL with other tables, Postgres doesn't use the
> index. Hu?
It is obviously not the exact same query if one of them need to
901 - 978 of 978 matches
Mail list logo
