"David G. Johnston" writes:
> On Tue, Mar 9, 2021 at 4:50 PM Tom Lane wrote:
>> Would it be better
>> to turn the para into a bulleted list, which we could introduce with
>> "The key differences are:" ?
> Indeed, reworking the rest of the paragr
t, peer, or something else in which
the password isn't actually relevant.
regards, tom lane
"David G. Johnston" writes:
> On Tue, Mar 9, 2021 at 5:18 PM Tom Lane wrote:
>> v2 attached.
> Thank you. That reads much better, aside from maybe a couple of missing
> commas.
> - Certain function attributes such as strictness don't apply to
> +
like
CASE WHEN col1 IS NULL THEN ... ELSE ... END
Although I think the particular thing you're doing here would
be better solved with COALESCE(col1, 'z').
regards, tom lane
lixing
things somehow? Kind of a weak theory, but nothing else comes to mind.
regards, tom lane
ng like
SELECT e.key, e.value FROM t_reading_hstore AS t, each(t.value) AS e;
(Writing LATERAL is optional.)
regards, tom lane
lter or drop
existing triggers, it's unlikely that anyone will want to expend effort
in that direction.
regards, tom lane
rsions of the spec list bigint
as code 25 (cf. table 7 in part 3 SQL/CLI). If you wanted to send in
a patch that'd make that work, we'd likely accept it.
regards, tom lane
WAL beyond. So I think your
problem is exactly that this slot isn't advancing. What's
using it?
regards, tom lane
e may be a way to have a slot that's not actually holding back
WAL cleanup while doing nothing, but I don't know what it is.
regards, tom lane
levant standards using both interpretations).
I think that our internal form uses positive-is-west, a/k/a POSIX rules,
but I'm too lazy to go check right now.
regards, tom lane
to do this with
a run-time version check, instead of statically compiling it?
regards, tom lane
Michael Paquier writes:
> On Wed, Mar 17, 2021 at 11:44:48AM -0400, Tom Lane wrote:
>> Oh! That's an interesting theory; it'd explain why this broke recently,
>> because we didn't use to use that function. But how do you draw that
>> conclusion from this stac
a large fraction of the
table (which the planner is accurately estimating), there's not a lot
of daylight between the estimated costs of seqscan and index-only
scan to begin with. I'm not surprised that it'd prefer the former
if the table isn't recently vacuumed.
regards, tom lane
ining the log might help debug what the program is doing wrong.
regards, tom lane
realizes that everybody else has already left and
> destroyed the memory segment.
If the error only appeared in the log, and wasn't reported to the
client, then this theory seems pretty plausible.
regards, tom lane
e join is designed to access just a small part of the table.
> Please help us in identifying the solution.
Please read
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
searched the doc but found nothing.)
There's a specific discussion of the DST-boundary issue on
this page:
https://www.postgresql.org/docs/current/functions-datetime.html
(search for the first mention of America/Denver). Not sure about
how well the months and years cases are documented.
regards, tom lane
y maps to stderr of their own accord,
and you could maybe get some insight as to what's leaking.
This'd also reduce the severity of the problem when it does
happen.
regards, tom lane
nce in the primary key index, resulting in
inefficiency in searches and insertions. If the composite key you
mention has some actual relationship to your application's usage
patterns, it could be winning as a result of better locality of
access to that index.
regards, tom lane
ls. There's not much of that in the core backend
(although if you use ispell text search dictionaries, maybe [1] is
relevant), so my suspicions would next fall on any extensions you
might be using.
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgres
ot very good
about saying whose privileges were checked.
regards, tom lane
Stephan Knauss writes:
> On 31.03.2021 20:24, Tom Lane wrote:
>> Based on nearby threads, it occurs to me to ask whether you have JIT
>> enabled, and if so whether turning it off helps. There seems to be
>> a known leak of the code fragments generated by that in some cases.
eck firewalls etc.
regards, tom lane
x27;::timestamptz;
timestamptz
2020-10-01 02:00:00+02
(1 row)
regression=# select '2020-10-01 00:00:00+00'::timestamptz + interval '1 month';
?column?
--------
2020-11-01 02:00:00+01
(1 row)
which looks to me like the addition did exactly what it's supposed to,
ie same local time 1 month later.
regards, tom lane
hat series was 9.5.25,
so you *really* oughta think about an update.
regards, tom lane
t error you're seeing, either.
FWIW, I tried executing a fragment like the above in a loop,
and it seemed fine.
regards, tom lane
sane value, and if so, why it is that
something else is blocking your query for longer than that.
Looking into pg_locks might help identify what the "something else"
is.
regards, tom lane
es ago. Maybe you'd have better results
with the current release (10.16).
regards, tom lane
nd up physically smaller than JSON, resulting in less work
to push the toasted datum out to disk. This'd depend a lot on your
formatting habits for JSON, of course. But in any case, it'd be worth
comparing pg_column_size() results to see what's up with that.
regards, tom lane
initdb.
Yeah, that's corrupt :-(. Probably if you try pg_controldata you'll
find that pg_control is a complete mess.
regards, tom lane
Dmitry Dolgov <9erthali...@gmail.com> writes:
> On Wed, Apr 14, 2021 at 10:26:23AM -0400, Tom Lane wrote:
>> My own guess about this, without having tried to reproduce it, is that
>> JSONB might end up physically smaller than JSON, resulting in less work
>> to push th
There were security fixes to
restrict the search path that VACUUM/ANALYZE runs with, which breaks
functions that aren't careful about that.
regards, tom lane
re all nominally supported, some are old, crufty, and
deprecated to various extents. xml2, in particular, might go away
someday.
regards, tom lane
parallelized hash join by any chance? That's new in v11
if memory serves, so it'd be interesting to see if disabling
enable_parallel_hash changes anything.
Anyway, I'd counsel updating to current (11.11), and then if you can
still reproduce the problem, try to reduce it to a self-contained
test case.
regards, tom lane
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes:
> Is it possible to emit csvlog to standard output/error?
No. We assume that random third-party code might write plain text messags
to stderr, so trying to impose a format on that wouldn't be very
successful.
rega
es proper
that does that. So I'm wondering if you have any nonstandard code
in there, such as unusual extensions. A badly-written event trigger
could perhaps do it too.
This being Windows, a certain amount of suspicion has to be directed
towards bogus antivirus code, too.
regards, tom lane
dead man switch I referred to, and the commit message is quite
interesting:
Author: Tom Lane
Branch: master Release: REL8_4_BR [969d7cd43] 2009-05-05 19:59:00 +
Install a "dead man switch" to allow the postmaster to detect cases where
a backend has done exit(0) or exi
of that sort in libpq, but I'm less
sure about the server.
regards, tom lane
Matthias Apitz writes:
> El día domingo, abril 25, 2021 a las 11:51:45a. m. -0400, Tom Lane escribió:
>> Hmph. So why wasn't this visible in the tcpdump trace?
> It was visible. That's why I detected it while looking up the TCP
> packages before the UPDATE:
> 10:57:
Karsten Hilbert writes:
> Am Sun, Apr 25, 2021 at 01:21:25PM -0400 schrieb Tom Lane:
>> Still, the apparent message length is then "RP-2" (0x52502d32)
>> which is bigger than 1GB so I don't quite understand why you didn't
>> get an "out of memory
hat you're looking at is such a lock that is no longer associated
with a specific transaction. I have to disclaim knowing much of
anything about the SSI code, though.
regards, tom lane
#x27;ing the startup process to
see what it's doing.
Also, what PG version is that exactly?
regards, tom lane
ly touch
the catalogs, so it's mostly impervious to that problem; but you'll need
to test your applications.
regards, tom lane
rn is TIDs for tuples that
contain both "label" and "person". Whether they have the relationship
of "person" being the value of a "label" tag can't be told without
looking at the actual JSON value.
regards, tom lane
idn't show us the rest of the command, but it looks like
this view contains a reference to pg_class.relhasoids. As
Bruce noted, that column no longer exists, so you're not going
to be able to transparently upgrade this view.
I don't know what this view is from, though the schema name
"repack" is suggestive. Possibly you could drop whatever it's
a part of, and then reinstall an updated version after upgrading?
regards, tom lane
costs for parallel queries
are far too optimistic --- we've seen enough complaints about parallelism
making things slower to make that clear. However, proposing some new
numbers without gathering some hard evidence to support them is not
helpful.
regards, tom lane
;
Actually it's sufficient to write
select * from t where a > (select date_trunc('day', '2021-04-01'::timestamptz))
Postgres interprets that as an uncorrelated sub-select, so it's only done
once per outer query. I think that these days, the CTE form would be
flattened into that anyway (without MATERIALIZED).
regards, tom lane
lt-in function, that could well be a large enough number to
change the plan choice. (You could experiment with altering the
COST property to see where the plan changes.)
regards, tom lane
a default selectivity estimate
(notice the fairly bad rowcount estimate). I'm a bit surprised that
that would end in choosing an indexscan over a seqscan, but that
might be a consequence of the small random_page_cost you're using.
regards, tom lane
st [1].
If the OP is in the habit of just hitting the power switch
at lunchtime, I'd say he's got to stop doing that. Telling
Windows to shut down will add a few seconds, but greatly
reduce the risk of problems (and not only for Postgres).
If he *did* tell Windows to shut down, then there&#x
eep_segments is
zero. There are generally better ways to do what it does.
regards, tom lane
m
suspecting is extra is the LEFT JOIN, not the IN.) I'd also
counsel losing the ORDER BY in the sub-select, as that's either
going to be ignored altogether or force a probably-useless sort,
not to mention creating an optimization barrier.
regards, tom lane
equence.c ) and a 'restart' occurs.
Well, 32 is the worst case, but yes this is behaving as intended.
> Are there any plans to fix this issue
No. If you must have gapless values, don't use sequences.
regards, tom lane
enalty is noticeable in
your usage.
regards, tom lane
[1] https://www.postgresql.org/message-id/1032535.1620789957%40sss.pgh.pa.us
oop (whether that makes sense is another
> question) , then this still produces the error:
Ugh. Thanks for the test case!
regards, tom lane
124
(1 row)
then there's no way that that statement fails on integer overflow.
What I'm wondering about is the next line:
> unix_time = (new_time + rand_int)::BIGINT;
You've not shown us the declaration of unix_time ...
regards, tom lane
olumn, so that
the INSERT command received by the remote server lacks that
column and the default gets applied. Probably too messy though.
regards, tom lane
Bharath Rupireddy writes:
> On Fri, May 14, 2021 at 8:23 PM Tom Lane wrote:
>> One conceivable workaround is to do your insertions through a
>> foreign table that doesn't even have the serial column, so that
>> the INSERT command received by the remote server lacks that
specific than a syntax error would
be nice ...)
regards, tom lane
* later merging or final output ordering, OR the index has a useful
* predicate, OR an index-only scan is possible.
If none of those cases apply, an indexscan is guaranteed to be worse
than a seqscan, so we don't consider it.
regards, tom lane
cription” command. If no one does so the amount of time spent
> confirming the subscriptions table is empty is trivial.
It looks like it won't be started if you set
max_logical_replication_workers = 0.
regards, tom lane
"David G. Johnston" writes:
> On Monday, May 17, 2021, Tom Lane wrote:
>> It looks like it won't be started if you set
>> max_logical_replication_workers = 0.
> I was wondering about that. Would you mind posting a link to the code
> where that is checked?
be 100% sure that these log entries
are from the same process. But I bet they are. The standard
walreceiver definitely does things this way.
In short, I think there's nothing to see here.
regards, tom lane
ce then.
Oh, that's because pg_stat_activity continues to show the last plain-SQL
query executed by the session.
I think we recently changed things so that replication commands
would be shown in pg_stat_activity too, but evidently you're running
a version older than that.
regards, tom lane
ng the former, but the latter seems like
an implementation detail.
regards, tom lane
s those as display offsets in zones where there's not any
widely-used-on-the-ground abbreviation.
regards, tom lane
o
see statistics, which can easily result in a worse plan.
regards, tom lane
14beta1 because array_append's parameter are now
> (anycompatiblearray, anycompatible) while it used to be (anyarray,
> anyelement).
Yeah, you'll probably need to drop that aggregate and then recreate it
after upgrading.
regards, tom lane
t happen. It goes to parsing every time I execute
Perhaps "set plan_cache_mode = force_generic_plan" would help you.
Bear in mind that this is likely to be a net loss overall.
regards, tom lane
se that's in use, which is an extremely common
situation given that we recommend use of --with-system-tzdata.
regards, tom lane
the table proper.
A plausible theory here is that TRUNCATE leaks some storage associated
with an index's relcache entry, but not any for a plain table.
regards, tom lane
[1] https://www.postgresql.org/docs/current/storage-toast.html
either on the grounds that it's temp
or that it's new in the current transaction; but the index code path
didn't get the same TLC.
Alternatively, maybe it's a bug that there's no message for the table.
regards, tom lane
r of messages,
maybe we should just figure on issuing a full cache reset rather than
continuing to track individual inval events. This'd not only bound
the storage required for pending invals, but very possibly be more
efficient when the time comes to actually process them.
regards, tom lane
d or some other way to identify VIPs accurately, is
ORDER BY is_vip DESC, last_name, first_name
relying on the fact that bool TRUE > bool FALSE.
regards, tom lane
sunderstand, there is no plan for a REFRESH.
EXPLAIN isn't bright about that, but if you enable auto_explain,
it will log the plan for a REFRESH's query.
regards, tom lane
ybe this won't be workable from a performance standpoint. But if
they're rare, or you can tweak things to make them so, think about it.
Retries can be a lot simpler, more robust, and even more performant
than trying to get to a provably-deadlock-free implementation.
regards, tom lane
n it
DETAIL: constraint t2_f2_fkey on table t2 depends on table t1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop table t1 cascade;
NOTICE: drop cascades to constraint t2_f2_fkey on table t2
DROP TABLE
Could you enlarge on what problem you saw, specifically?
regards, tom lane
bidule || '() RETURNS void language
> plpgsql AS $fnc$ BEGIN END; $fnc$ ';
This is not terribly well explained in the existing docs. I tried
to improve the explanation awhile ago in HEAD:
https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
regards, tom lane
mittee has gifted us with
a pretty asymmetric language, plus there are various implementation
constraints that are not that easy to get rid of.
regards, tom lane
COPYRIGHT file. And even if somebody were doing that,
I doubt how much we'd care.
We do care more about the Postgres *trademarks*, which is why Bruce
is pointing you to the organization that owns those. But a trademark
violation is an entirely different animal from a copyright violation.
regards, tom lane
rdsdba.
> Is this expected?
rds_superuser is not a thing that exists in community Postgres.
I'd suggest taking this question up with the Amazon RDS folk.
regards, tom lane
gest experimenting in a scratch database before doing it with
irreplaceable data.
Keep in mind also that pg_dump/pg_upgrade will absolutely not
reproduce this configuration for you.
regards, tom lane
27;t think it can do anything with non-constant inputs,
as we have in the OP's case.
regards, tom lane
ne would just get deleted instead.
But the steady-state behavior is to just rotate them around.)
regards, tom lane
ntly from user
tables, AFAIR.
regards, tom lane
ut the 9.6 directory first not last.
(3) If that still doesn't work, you need to configure the system's
dynamic linker to look there. You really want to do that anyway,
as messing with LD_LIBRARY_PATH all the time is no fun.
See "man ldconfig".
regards, tom lane
s with
the actual WAL write position? (pg_controldata would give you
at least a rough approximation of that, i.e. the WAL write
position as of the most recent checkpoint. I think you can
get a more up-to-date result from one or another system view,
but I don't remember which.)
regards, tom lane
case, postgresql.auto.conf is not hand-maintained; it
is part of the cluster data, so it belongs in the data directory.
regards, tom lane
amples do seem to work in v12 and up.
Since we don't consider this a supported case, I doubt anyone would
be interested in trying to back-port whatever rearrangement allowed
it to work.
regards, tom lane
Rich Shepard writes:
> Must be something else,
Are you sure the app is connecting to the right database?
regards, tom lane
ve got there is that Unix-socket connections work, but
TCP connections do not. Check the server's listen_addresses
setting. If that says to allow connections on these IP addresses,
next check your kernel firewall.
regards, tom lane
Rich Shepard writes:
> On Mon, 7 Jun 2021, Tom Lane wrote:
>> Are you sure the app is connecting to the right database?
> The problem source is postgres telling me it cannot connect to the database
> but I can do so directly using psql:
If the app is indeed failing to connect at
Rich Shepard writes:
> On Mon, 7 Jun 2021, Tom Lane wrote:
>> What you've got there is that Unix-socket connections work, but TCP
>> connections do not. Check the server's listen_addresses setting. If that
>> says to allow connections on these IP addresses, nex
is issuing this command. You need to get a more up-to-date
copy that knows what to do instead.
regards, tom lane
in the days
> before 99% of users read their email via clients which hide quoted content
> unless it is explicitly expanded.
Yeah, gmail is aggressively unfriendly for this purpose. But if you
can't be bothered to trim your quotes, I'd actually much rather that
you top-post.
regards, tom lane
his problem statement. I doubt there are many
practical applications where you wouldn't also wish to exclude holidays,
for somebody's definition of holidays. Of course, that makes it a lot
messier since you need a source of data for that.
regards, tom lane
st/benefit ratio seems completely untenable.
The usual recommendation is that you shorten long values by hashing
them, eg create a unique index on md5(my_long_column).
regards, tom lane
reading
their messages right away.
regards, tom lane
QL standard. But I'm insufficiently
caffeinated to want to go digging for that.)
regards, tom lane
2201 - 2300 of 2962 matches
Mail list logo
